1

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

  • find and show the mysql error log contents. – danblack May 15 at 5:28
  • In common this situation occures when file_per_table=1 and table file was removed. Check if the sales_invoice_grid.ibd file exists in database folder. If exists - remove and re-import its tablespace, if not - restore it from backup. – Akina May 15 at 5:42
  • Please read why you should not post screenshots of code or errors. – mustaccio May 15 at 12:51
  • Looking at the lame Comments, I see that the table's Comment disagrees with the table name. – Rick James May 15 at 17:48
0

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.

  • @rohankatkar - But first, what version are you running? If it is 8.0.xx, run to bugs.mysql.com and file a bug report; this could be the tip of a dangerous iceberg. – Rick James May 15 at 17:51
  • He/she is using 5.6.40, see above screenshot. – jynus May 15 at 17:54

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.