cancel
Showing results for 
Search instead for 
Did you mean: 

Hostopia Database Migrations

Superuser
Superuser
Posts: 9,056
Thanks: 507
Fixes: 44
Registered: 06-04-2007

Hostopia Database Migrations

Database migrations from ccgi to Hostopia are being done onto a MySQL Version 4.1.x server rather than Version 5.x. This means that an existing Wordpress 3.1.4 installation (the latest with native support for MySQL 4.x) cannot be updated directly to a later release.
It is possible to create a MySQL 5.x database,, install Wordpress 3.8.1 (the current release) on it then move the 4.1.x tables across. I'm in the process of doing this. However just wondering if that's what users were expecting to have to do?
David
David
13 REPLIES
Community Veteran
Posts: 767
Registered: 29-10-2008

Re: Hostopia Database Migrations

So you had just a MySQL4 DB after migration? I had both 4 and 5. The v5 was a bit broken, but the WP tables were actually okay. It sounds like some (most?) users have working MySQL5 DBs.
Gabe
Superuser
Superuser
Posts: 9,056
Thanks: 507
Fixes: 44
Registered: 06-04-2007

Re: Hostopia Database Migrations

The story is a bit complex, see
Quote from: spraxyt
Quote from: spraxyt
For me one database is named <username>_<visp>_<url using _ separators>
and the other (much larger one) <username>_<visp>_2_<url...>

I've sorted out what database ..._2_... is. It actually belongs to someone else who I cooperate with and some of my files access that table database. This is an unusual situation which neither Plusnet nor Hostopia would be aware of. I've deleted that copy from my storage.

Both databases were MySQL 4.
I've since created a MySQL 5 version of my own database and imported the tables into that without problems. But still to make use of the MySQL 5 version.
David
David
Community Gaffer
Community Gaffer
Posts: 12,966
Thanks: 753
Fixes: 70
Registered: 04-04-2007

Re: Hostopia Database Migrations

Quote from: spraxyt
Database migrations from ccgi to Hostopia are being done onto a MySQL Version 4.1.x server rather than Version 5.x. This means that an existing Wordpress 3.1.4 installation (the latest with native support for MySQL 4.x) cannot be updated directly to a later release.

I think this is the exception rather than the rule David. I'm still chatting to Hostopia about it. My account only had a single MySQL5 database after migration and I have multiple Wordpress installs (amongst other stuff) uploaded.

Bob Pullen
Plusnet Products Team
If I've been helpful then please give thanks ⤵

Community Gaffer
Community Gaffer
Posts: 12,966
Thanks: 753
Fixes: 70
Registered: 04-04-2007

Re: Hostopia Database Migrations

Quote from: spraxyt
I've since created a MySQL 5 version of my own database and imported the tables into that without problems. But still to make use of the MySQL 5 version.

Can you detail what steps you took to do this David?
The word I'm getting from Hostopia is that they struggled to import your MySQL4 database to a MySQL5 instance (presumably the result of differences between the two versions). Did you import the data from the MySQL4 Hostopia instance or the instance that's still on the Plusnet servers?
@Gabe, I'm similarly interested in what happened with your account. You mentioned to me a while back:
[quote author="Gabe"]... a random-looking selection of 8 tables, all belonging to smf, seem to be broken. As it happens, I've migrated exactly this db to another host using mysql5, so I know it shouldn't cause problems.
We're trying to work out what's breaking these MySQL5 imports. Especially given the fact that having a MySQL4 database causes the application-based phpMyAdmin install to fail too. Would you mind sharing the name of these 8 tables?

Bob Pullen
Plusnet Products Team
If I've been helpful then please give thanks ⤵

Superuser
Superuser
Posts: 9,056
Thanks: 507
Fixes: 44
Registered: 06-04-2007

Re: Hostopia Database Migrations

Quote from: Bob
Quote from: spraxyt
I've since created a MySQL 5 version of my own database and imported the tables into that without problems. But still to make use of the MySQL 5 version.

Can you detail what steps you took to do this David?
The word I'm getting from Hostopia is that they struggled to import your MySQL4 database to a MySQL5 instance (presumably the result of differences between the two versions). Did you import the data from the MySQL4 Hostopia instance or the instance that's still on the Plusnet servers?

Using my server and database credentials at mysql.plus.net I logged on to ccgi phpMyAdmin.
I selected my database and went to the Export tab.
I left all the tables pre-selected along with the SQL export type.
For the Options I left SQL Compatibility Mode as None but selected "Add DROP TABLE" (simpler if an import needs to be repeated).
I left the other options at their default values. This meant "Add CREATE PROCEDURE / FUNCTION", "Add into comments",  "Use delayed inserts" and "Use ignore inserts" were NOT selected.
Export type I left as INSERT.
I selected "Save as file" and set the File name template to "__DB__-%Y-%m-%d" with "remember template" selected.
I selected Compression "zipped" then clicked the Go button, waited a couple of minutes then my browser download dialogue opened and I saved the file on my computer (the filename ends .sql.zip).
On my Hostopia account I used MySQL Manager to create a new user and mysql5 database.
I then selected phpMyAdmin and this successfully configured itself to access my empty mysql5 database. The configuration file shows it 'knows' about my mysql4 database but thinks it's on the mysql5 server.
I logged into phpMyAdmin, selected my mysql5 database (the only one listed) and went to the Import tab.
I browsed to find the .sql.zip file on my computer and left 'character set of the file' as utf-8.
I deselected 'Allow partial import'
Format I left as SQL along with SQL compatibility mode NONE and I left "Do not use AUTO_INCREMENT for zero values" selected.
Then clicked the Go button. The file uploaded with content imported into my mysql5 database without problems.
I've since imported a table dumped from elsewhere that came to me as an uncompressed .sql file. I compressed that using 7-Zip to reduce its size (by 94%), renamed the archive to the required .sql.zip style and imported that file successfully. This file contains utf-8 characters in &#1234; entity style which display OK in my Firefox browser.
Rather a long explanation but I happened to have noted down the detailed steps. Cheesy
David
David
Community Gaffer
Community Gaffer
Posts: 12,966
Thanks: 753
Fixes: 70
Registered: 04-04-2007

Re: Hostopia Database Migrations

Thanks David, that's very helpful indeed Smiley

Bob Pullen
Plusnet Products Team
If I've been helpful then please give thanks ⤵

Superuser
Superuser
Posts: 9,056
Thanks: 507
Fixes: 44
Registered: 06-04-2007

Re: Hostopia Database Migrations

I've done a visual comparison of the database dump file used by Hostopia with the uncompressed version of mine. The former was produced by MySQL Dump whereas mine was from phpMyAdmin.SQL Dump. The latter sets the options

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

… and all table and column names are enclosed in back-ticks, though I don't think any names contain spaces or  non-ascii characters so this shouldn't matter. The INSERT commands produced by phpMyAdmin dump includes the column names. Again this shouldn't matter as the INSERTs are complete rows, though multi-row from phpMyAdmin.
The main difference is perhaps that binary BLOB data data from MySQL Dump is character serialised whereas that from phpMyAdmin is hexadecimal.
It isn't obvious (to me at least) why any of these differences should cause import problems. Huh
David
David
phonse
Newbie
Posts: 3
Registered: 30-07-2007

Re: Hostopia Database Migrations

FAO Bob Pullen
Bob a lot has gone wrong with 2 websites on my CGI space:-
1) phpMy Admin
Installed OK and  works OK but does not demand a username /psw - insecure!
I can see two databases : An older one with 45 tables.  An up-to-date one with 46 tables.
One of my two websites uses several of the tables.
Most of them are unusable with the message :- "Table created with different vesion of MySQL - cannot read".
The most recent table is readable but is 3 weeks out of date AND is NOT present in any of the two databases that I can see with phpMyAdmin Huh
I can deduce this from the data visible visible on the website.
How can this be ? On the original CGI platform only one user database was possible if I remember rightly. Now there are two and the website does not seem to be pointing at either.
These sites have been working faultlessly for 7 or eight years. I need to get them mended ASAP.

2) MySQL manager - OK but allows new password and then entry. Does'nt seem secure to me.
3) FTP manager - I set up passwords and user but FileZilla my favoured FTP client just hangs when connection is attempted.
4) Web Analytics -  Nothing there at all.
5) File manager  -  database directory has two folders with the same names as the 2 databases seen with phpMy Admin but they are empty ?Huh
    There are 2 ".sql" files in the private directory. One is too long for the edit function to cope with. Are these the transfer files used bt Hostopia ?Huh
Bit of a mess I feel.  Would welcome some help.

Community Gaffer
Community Gaffer
Posts: 12,966
Thanks: 753
Fixes: 70
Registered: 04-04-2007

Re: Hostopia Database Migrations

Sorry to have caused you problems
Quote from: phonse
1) phpMy Admin
Installed OK and  works OK but does not demand a username /psw - insecure!

It should prompt you for a username/password to access the phpMyAdmin install, that's as secure as you want to make it. It will not prompt you for individual database passwords.
Quote from: phonse
I can see two databases : An older one with 45 tables.  An up-to-date one with 46 tables.

Not sure why that is. Every account I've seen so far has one. I can only assume Hostopia had some issues with the first and created another.
Quote from: phonse
One of my two websites uses several of the tables.
Most of them are unusable with the message :- "Table created with different vesion of MySQL - cannot read".

I'd need better visibility of this to look into it further i.e. specific files, URL's etc.
Quote from: phonse
The most recent table is readable but is 3 weeks out of date AND is NOT present in any of the two databases that I can see with phpMyAdmin Huh
I can deduce this from the data visible visible on the website.

Sounds to me like your website is reading from something you're unable to see via the SQL administration tools. I've had a look at the connection file in your plh/includes directory and noticed that the hostname was incorrect, it read sqlc51.megasqlservers.eu rather than sql5c51a.megasqlservers.eu which is what you can see in the MySQL Manager. I've corrected this and reset the MySQL password to match the one in the connection file.
Hopefully your site now reflects what you can see using phpMyAdmin?
Once we've some consistency, I can request a more recent cut of your database for you.
Quote from: phonse
How can this be ? On the original CGI platform only one user database was possible if I remember rightly. Now there are two and the website does not seem to be pointing at either.
These sites have been working faultlessly for 7 or eight years. I need to get them mended ASAP.

You can create multiple databases on the new platform. I'm confused as to why you have two though.
Quote from: phonse
2) MySQL manager - OK but allows new password and then entry. Does'nt seem secure to me.

It's as secure as your Control Panel password.
Quote from: phonse
3) FTP manager - I set up passwords and user but FileZilla my favoured FTP client just hangs when connection is attempted.

Have you tried both active and passive modes?
Quote from: phonse
4) Web Analytics -   Nothing there at all.

I wouldn't expect there to be until your site starts generating some traffic (and even then, there's a delay).
Quote from: phonse
5) File manager  -  database directory has two folders with the same names as the 2 databases seen with phpMy Admin but they are empty ?Huh
There are 2 ".sql" files in the private directory. One is too long for the edit function to cope with. Are these the transfer files used bt Hostopia ?Huh

Yes I believe they may be.

Bob Pullen
Plusnet Products Team
If I've been helpful then please give thanks ⤵

Superuser
Superuser
Posts: 9,056
Thanks: 507
Fixes: 44
Registered: 06-04-2007

Re: Hostopia Database Migrations

Quote from: phonse
5) File manager  -  database directory has two folders with the same names as the 2 databases seen with phpMy Admin but they are empty ?Huh

Those folders are not as empty as File Manager makes them look. The contents are your live MySQL database files which MySQL accesses and updates when SQL commands are executed. Consequently user tinkering in there could have drastic consequences on database functionality. So File Manager deliberately doesn't show them to you; whilst FTP (for example) will, keep out is the safest motto.
Quote from: phonse
3) FTP manager - I set up passwords and user but FileZilla my favoured FTP client just hangs when connection is attempted.

Have you tried setting a Master FTP password (FTP Manager bottom left) and using that with FileZilla? Your site URL (eg ccgi.username.plus.com) is used for both FTP username and hostname.
Master FTP account login allows access to all directories.
David
David
Community Gaffer
Community Gaffer
Posts: 12,966
Thanks: 753
Fixes: 70
Registered: 04-04-2007

Re: Hostopia Database Migrations

Quote from: spraxyt
Quote from: phonse
5) File manager  -  database directory has two folders with the same names as the 2 databases seen with phpMy Admin but they are empty ?Huh

Those folders are not as empty as File Manager makes them look. The contents are your live MySQL database files which MySQL accesses and updates when SQL commands are executed. Consequently user tinkering in there could have drastic consequences on database functionality. So File Manager deliberately doesn't show them to you; whilst FTP (for example) will, keep out is the safest motto.

Ouch, my advise could have had some nasty consequences in which case. Appreciate the clarification David, I'll be honest, I didn't actually realise they were live copies of your database content Embarrassed

Bob Pullen
Plusnet Products Team
If I've been helpful then please give thanks ⤵

phonse
Newbie
Posts: 3
Registered: 30-07-2007

Re: Hostopia Database Migrations

Hi Bob Hi David
Just read your comments.  Thank you.
FTP Manager :  Works OK with username same as FTP target name as described  by David.
Database directory :  Point taken.
Wrong server :  Thanks Bob. I have now changed all the other MySQL server references in the website code and everything is working OK. The older database table that I could not see with phpMyAdmin was obviously on the other (wrong) server.
One of the tables was corrupted but I rescued it from the stuff still on the PlusNet server and imported it successfully.
Comment 1 ::. Having done migrations of code and data much bigger than this during my working life using aliases/pointers and environment descriptors I am very surprised to find that this migration has involved changes/edits to code. Just asking for trouble I think.
Comment 2 :: The platform looks to be a much better than what we had before and seems to be well integrated.  No lockdown anymore. Hurrah !
Superuser
Superuser
Posts: 9,056
Thanks: 507
Fixes: 44
Registered: 06-04-2007

Re: Hostopia Database Migrations

It's interesting that you discovered a corrupted table in your migrated database. My migrated database has several corrupted tables. One of these stopped a forum from working.
However I imported a new database from my own backup and that's OK. I switched the forum to this and it works fine.
David
David