I copied MySQL .MYD, .MYI and .frm files to another server and now phpMyAdmin tries to search a non-existent table

4 Views

OK, so I had a website project I was running locally on my computer using MAMP. Long story short, the computer is out for repairs and I need to figure out how to move the project from my backup disk to an online webserver ASAP. That’s easily done for the HTML, PHP, CSS and JS files, but moving over the MySQL data files is more of a puzzle for me. I was able to find the .MYD, .MYI and .frm files, and when I copied them to the server (I created a new database before copying over the files) it seemed to work at first. I see all the tables and data in phpMyAdmin. Then I ran into this oddity: whenever I do a global database search in PMA, PMA attempts to search a non-existent table, causing MySQL to bark. 

SHOW FULL FIELDS FROM  `wp_ai1ec_logging` FROM  `foobar` ;
#1146 - Table 'foobar.wp_ai1ec_logging' doesn't exist

I tried searching the information_schema to see if something was out of sync:

Read More
SELECT * FROM  `information_schema`.`TABLES`
WHERE  `TABLE_NAME` LIKE  'wp_ai1ec_logging'

but I came up with nothing. So I went back to the data files and found this curious fact: there was a wp_ai1ec_logging.frm file, but no corresponding wp_ai1ec_logging.MYD or wp_ai1ec_logging.MYI files. What could this mean? I don’t know what these file types mean, but it seems like every table has a .frm, .MYD and .MYI file EXCEPT this table for some reason. I had these files backed up to my Time Capsule using Time Machine (I had to connect it to another Mac to restore the files), so my guess is either the last backup went wrong or (more likely) I made some mistake in restoring the files. I’ve never had to restore Time Capsule files to another Mac and I found it to be WAY more tricky than restoring files to my own computer, so I’m allowing for the possibility that I did it wrong somehow. But assuming the backup was correct and I restored correctly, what else could explain this?

And in case you need to know, this project uses WordPress with Timely’s All in One Event Calendar plugin (http://time.ly/), so that’s what “ai1ec” means. I’m not sure if this table is critical to the current version of the plugin or I should just remove that .frm file and forget about it.

Post Views: 4

Related posts

Leave a Reply

2 comments

  1. I’m betting that your WP install is older. I don’t think that WP specifies a storage engine but the default for MySQL used to be MyISAM, which is what your MYD and MYI files are for. MyISAM is purely a file storage system.

    Starting with MySQL 5.0, the default was changed to InnoDB, which does not have those files. So if your WP database was on an older version of MySQL and you upgraded and then installed this plugin it’s possible that it installed as InnoDB. Either that or your plugin specified an engine when it installed itself.

    Either way, you’ll have to dump the table out and import it that way. You can’t just raw copy InnoDB database files.

  2. As well as the files being readable by the MySQL user, the directory containing the .MYI files needs to be read, write and executable by the MySQL user. On my system this was achieved by:

    chown -R mysql:mysql /var/lib/mysql/dbname
chmod -R 660 /var/lib/mysql/dbname
chown mysql:mysql /var/lib/mysql/dbname
chmod 700 /var/lib/mysql/dbname