0

Context

We have a MySQL 5.6.28 server with a lot of schemas on it. After a crash, we have some schema where a table x exists, and other schemas where it's an orphan table.

On a non-corrupted schema, doing a SELECT * FROM x returns the rows in x. Dropping the table works.

On a corrupted schema, doing a SELECT * FROM x or doing a DROP TABLE or doing an ALTER TABLE returns Unknown table x. Doing a CREATE TABLE x (...) Engine=InnoDB return Error 1813 Tablespace for table 'my_corrupted_schema.x' exists. Please DISCARD the tablespace before IMPORT. Doing a CREATE TABLE x (...) Engine=HEAP|MyISAM or even creating a view CREATE VIEW x AS SELECT 1 works. Doing an ALTER TABLE x ENGINE=InnoDB on the HEAP or MyISAM table fails with Error 1050 Table './my_corrupted_schema/x' already exists Looking at information_schema.INNODB_SYS_TABLES or information_schema.INNODB_SYS_TABLESPACES or information_schema.INNODB_SYS_FOREIGN_COLUMNS we see that the table x is there. Dropping the Heap|MyISAM table or the view works fine.

Now comes the funny part:

We looked at the information_schema.TABLES. We do

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CREATE_TIME, ENGINE 
FROM information_schema.`tables` AS t 
WHERE t.table_name = 'x';

And we see only x tables in non-corrupted schemas (11 rows, matching the 11 schemas where a SELECT * FROM x works). Looks very like orphans table.... But then, we do:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, /*CREATE_TIME, */ENGINE 
FROM information_schema.`tables` AS t 
WHERE t.table_name = 'x';

And we see x tables in all schemas (905 rows), including corrupted ones...?!

How is that possible?! How can we fix that without having to drop and recreate every schema? It seems that CREATE_TIME is NULLABLE in information_schema.TABLES (which makes little sense IMO but anyway) but how can including that column in our select change the returned rows?! How can we fix these tables x in the corrupted schemas?


Edit: After testings, including any of the commented out columns in the SELECT below will return only non-corrupted tables, while leaving all commented out columns will return all schemas, including those where the table is corrupted... What's the common point between all these columns?

SELECT 
    TABLE_CATALOG, --  OK
    TABLE_SCHEMA, -- OK
    TABLE_NAME, -- OK
    TABLE_TYPE, -- OK
    ENGINE, -- OK
    VERSION, -- OK
--  ROW_FORMAT, -- BAD
--  TABLE_ROWS, -- BAD
--  AVG_ROW_LENGTH, -- BAD
--  DATA_LENGTH, -- BAD
--  MAX_DATA_LENGTH, -- BAD
--  INDEX_LENGTH, -- BAD
--  DATA_FREE, -- BAD
--  AUTO_INCREMENT, -- BAD
--  CREATE_TIME, -- BAD
--  UPDATE_TIME, -- BAD
--  CHECK_TIME, -- BAD
    TABLE_COLLATION, -- OK
--  CHECKSUM, -- BAD
    CREATE_OPTIONS, -- OK
    TABLE_COMMENT -- OK
FROM information_schema.`tables` AS t 
WHERE t.TABLE_NAME = 'x'
3
  • By "BAD", do you mean "null"? Are you talking about corrupt tables of one Engine? Or multiple Engines? A power failure usually leaves MyISAM tables in need of a REPAIR.
    – Rick James
    Mar 30, 2018 at 22:59
  • It's InnoDB tables, and lines marked with BAD means that if I put this line in the SELECT then I only got the non-corrupted tables. So if I SELECT ...VERSION FROM information_schema... then I got all tables, and if I SELECT ...VERSION, ROW_FORMAT FROM information_schema... then I only get the non-corrupted tables.
    – Xenos
    Apr 4, 2018 at 14:37
  • The information_schema is not a set of "real" tables, but information that is gathered as needed. The "OK" things come from one place; the "BAD" things come from a corrupted place.
    – Rick James
    Apr 5, 2018 at 1:56

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.