TheGarage Tech Blog

Last night I upgraded the MySQL database behind this site from version 4 to 5. I needed to test the process before I upgrade the Gulf Coast Texas Magazine site. If I dig around in the documentation at 1and1 I could probably tell from which version to which, but the options on the setup menu are MySQL 4 or MySQL 5.  Suffice to say the version 4  was low enough that the Simple:Press forum plugin for WordPress wouldn’t install. Even if I don’t ultimately use the plugin, I simply have to look under the hood of that bad boy.

The worst thing about the whole MySQL upgrade process — which involves dumping the schema and data from the version 4 database into a SQL file and then running the SQL dump on a new, empty MySQL version 5 database — was that 1and1 had difficulty managing the upload of the SQL dump file and then running the SQL. When I uploaded the file it took forever and finally reported complete but there were no tables — still an empty database. I tried it twice. Took forever twice. Twice no tables.

So I manually split the SQL dump file into several little chunks and built the version 5 database a wee bit at a time. How ridiculous is that when the entire process should have taken five minutes, ten at the most, but your hosting service provider sucks so bad that the job required a couple of hours. Hard for a developer to make money that wy.

It says in the 1and1 documentation:

Note: If the database export does not work correctly you can try to export one table at a time. Select the table from the left side of the MySQL Administration screen and click Export.

Lame. So I guess I’m not surprised. How hard could it be to simply make the feature work?

The faq’s that I found confirming that there is no automatic process to upgrade a database are hard to find on the 1and1 search, you need to use another, real  search engine to find the information. You can check these if interested in the 1and1 MySQL upgrade process –> Export, Import

But here is the real complaint. After the long ordeal descibed above I finally had the entire database loaded so I edited my wp-config to point to the new database and it’s like nothing ever happened… Except that there seems to be a bunch of funny capital A’s with little hats  all throughout the text of every post. And that sucks.

If you ever need to access the system wide schema info for a MySQL server, check out this very nice diagram of the MySQL Information_Schema.

What can you do with this information? Well, lots of stuff. For example you can get a list of tables and columns for a particular database, like

SELECT table_name, column_name, data_type [,...]
FROM information_schema.columns
WHERE table_schema = dbname;

Which could be handy for any number of reasons, such as seeing if a table or exists or if a column exists in a table, etc.