3

Any idea why there is '1146 - table doesn't exists' on 'CREATE TABLE IF NOT EXISTS...' statement. It actually happened only on one of the slaves (there are two in total):

Last_SQL_Error: Error 'Table 'db_name.table_name' doesn't exist' on query. Default database: 'db_name'. Query: 'CREATE TABLE IF NOT EXISTS `table_name` (
          `step_id` int(11) NOT NULL,
          `item_id` int(11) NOT NULL,
          `item_type` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
          `date_attached` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (`step_id`,`item_id`,`item_type`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'

EDIT:

I've re-synced this table but I'm not sure why this happened.

130802  9:32:03 [ERROR] Cannot find or open table db_name/table_name from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.
  • 2
    What specific MySQL Server versions are running on each of the 3 machines? Is the slave still stopped right now, and does its error log look clean otherwise, and are both slaves directly connected to the master (as opposed to one slave cascaded from the other slave?) – Michael - sqlbot Aug 2 '13 at 12:11
  • I've updated my question with error from the logs. – HTF Aug 2 '13 at 13:38
1

The error itself explains what the problem is. There is a .frm file on the filesystem for the table. Go to your MySQL data directory (it's usually /var/lib/mysql on a linux box) and take a backup of the .frm file and remove it from that location. You should then be able to create the table on your slave.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.