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 Ӓ entity style which display OK in my Firefox browser.
Rather a long explanation but I happened to have noted down the detailed steps.
David
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
… 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 ischaracter serialised whereas that from phpMyAdmin is hexadecimal.
It isn't obvious (to me at least) why any of these differences should cause import problems.
David
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
It isn't obvious (to me at least) why any of these differences should cause import problems.
David
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
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 ?
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 ?
Bit of a mess I feel. Would welcome some help.
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
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 ?
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 ?
Bit of a mess I feel. Would welcome some help.
Sorry to have caused you problems
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.
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.
I'd need better visibility of this to look into it further i.e. specific files, URL's etc.
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.
You can create multiple databases on the new platform. I'm confused as to why you have two though.
It's as secure as your Control Panel password.
Have you tried both active and passive modes?
I wouldn't expect there to be until your site starts generating some traffic (and even then, there's a delay).
Yes I believe they may be.
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
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 ?
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 ?
Yes I believe they may be.
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 ?
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
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 ?
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
