MySQL phpMyAdmin madness

Posted by joy

Yesterday I entered into a tarpit of corrupted configuration files and such. What appears to have happened was that my Cpanel install was upgraded and there are some functions on Cpanel which refer back to my .htaccess. At the time, it appeared that my .htaccess file was horribly corrupted, in particular one rewrite rule which affected my wordpress install and upon some editing, voila — I broke links to my older posts. In essence, I still had my front page, but none of the links to my archived posts or comments worked.

Since I had been itching to fix my Wordpress install (some of the files were from my original .72 install), I figured that this would be a good time as any to back up the database and to reinstall Wordpress from scratch. So, that is what I did. There was even a HOWTO on backing up your database using phpMyAdmin on the WordPress FAQ, and I followed those instructions.

The backup went fine, except initially I had some problems opening up the resulting backup file called localhost-sql If I was to do it again, I would definitely export my database not only in SQL, but also .CSV and .XML, just to be sure. Anyway, I was able to open the resulting .SQL file as text, and all was good. I also inadvertently exported my previous movable type database too, but hey.

By the time I went to bed last night, I had the data but I was banging my head trying to figure out how to restore it within the new database, since I was getting SQL error messages telling me that the database was already created. I knew that, but what was going on?

This morning I found this article on Dev Shed about backing up and restoring MySQL databases complete with explicit instructions and screenshots. It was helpful to see the screenshots, but I was still seeing stupid error messages.

It was only after a few more unfruitful tries of restoring my database did I figure out where my problem was. It appears that my export of the database inserted an unneeded SQL command into the header of the export file.

# Database : `my_database`
CREATE DATABASE `my_database`;
USE my_database;

And yes, I did realize that the “CREATE DATABASE” statement was incorrect since my database already existed, but I was initially thinking that I needed an INSERT or UPDATE command. I attempted those, no dice.

As best as I figure it, if you are merely trying to upload data into an empty database using the phpMyAdmin SQL tab > “Location of the textfile” option, the SQL statement at the top of your exported text file is redundant and should be omitted.

One Response to “MySQL phpMyAdmin madness”

  1. Arthur Says:

    Sounds like you forgot to check the ‘drop table’ checkbox in [your-favourite-SQL-Admin] program?

    If I remember correctly, both MySQL and Postgres (do those Wordpress guys support the [ok, I’m subjective] far superior Postgres?) ‘dump’ programs have a commandline switch that explicitely adds a ‘drop table’ line before each ‘create object’ statement.