Mysql – Weird error on creating table and selecting rows from table

existsMySQLtable

I have db and I am trying to create table called "sales_invoice_grid"

I am having bellow scenarios:

error 1: while creating the table "sales_invoice_grid" manually, it gives the message that it already exists.

error 2: When select rows from "sales_invoice_grid" it gives the message that, the table is not exist

enter image description here

enter image description here

Best Answer

Your problem arises from suffering metadata corruption, in particular, a difference between MySQL/filesystem contents and InnoDB internal data dictionary, which are stored separately.

I cannot give you concrete advice without knowing more about the filesystem, MySQL and InnoDB point of view, and also looking at your error log, but a general "catch all" solution I have most successfully applied in the past is to move/backup all the good data away* (e.g. dump, rename to a different database, backup orphan files), *and then drop the database, which will sync the InnoDB data dictionary and MySQL/filesystem state (to non-existence), then recreate and move back/reimport the original tables. The RENAME TABLE should be almost an instant operation, so it should not take a lot of time. Any operation will not work on the problematic table- that is likely to be lost unless the .ibd file still exists can be backup up before dropping the database.

To prevent this from happening in the future, you probably want to upgrade to MySQL 8, where data dictionary is unified on InnoDB storage, and this duality should be more rare.

Related Question