CODEWITHSUNDEEP
mysqldatabaseinsertcreate-tablesequelpro
James Baker
James Baker

Reputation: 333

MySQL said: Table doesn't exist in Sequel Pro

I have created a GameCenter type database in sequel pro using root, but for some reason every now and again I get the following errors when connecting to the database and trying to access the tables:

An error occurred while retrieving the information for
table 'Achievement'. Please try again.

MySQL said: Table 'gamecenter2.achievement'
doesn't exist

I have looked around and most people say it is a problem that occurs when copying a database from one place to another however I am not doing that.

In terminal using MySQL the database and tables all show up but I get the same error when trying to access a table's contents:

SHOW COLUMNS FROM Achievement;
ERROR 1146 (42S02): Table 'gamecenter2.achievement' doesn't exist

Any help with this would be really appreciated as running the CREATE TABLES and INSERT over and over is getting quite tiresome!

Upvotes: 1

Views: 3782

Answers (1)

Jeremy Kidwell
Jeremy Kidwell

Reputation: 210

I've had the same problem recently (and same error message in sequel pro) and have since discovered that it is because certain types of MySQL databases (i.e. those using InnoDB) store the actual data for individual tables outside in a file named "ibdata1" (you can read more on this here: https://dba.stackexchange.com/questions/15531/why-does-innodb-store-all-databases-in-one-file). Thus, if you restore individual folders for your database(s) using time machine, you are actually just restoring empty database architectures without the good stuff inside. If you haven't also restored that "ibdata1" file along with the appropriate "ib_logfile" files it won't be able to find the data. The solution is to make sure you've also restored those files (or a recent good copy).

Do also make sure you stop the mysql service before restoring anything, i.e. "mysql.server stop" from the command line (or "launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist" if you've installed mysql using homebrew).

Upvotes: 3

Related Questions