S h o r t S t o r i e s

// Tales from software development

Archive for September 2013

MySQL Error Code: 1034. 2 when fixing table

leave a comment »

After restoring a database and applying some changes to it, I started getting this error when I tried to run an INSERT statement:

Error Code: 1034. 2 when fixing table

The server error log showed the same error but with different text:

130912 9:27:08 [Warning] Warning: Enabling keys got errno 2 on vitality.cached_information_schema_columns, retrying

The INSERT statement seemed simple enough:

INSERT INTO cached_information_schema_columns(TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
SELECT DATABASE(), C.TABLE_NAME, C.COLUMN_NAME FROM information_schema.columns AS C
LEFT JOIN information_schema.tables AS T ON C.TABLE_NAME=T.TABLE_NAME
WHERE C.TABLE_SCHEMA=DATABASE()
AND T.TABLE_SCHEMA=DATABASE()
AND T.TABLE_TYPE='Base Table';

and, curiously, the SELECT statement executed successfully on its own.

Fortunately Rob had seen something similar before and suggested that I checked the Views tab of the schema display in MySQL Administrator. There were five views that had a red message in the Description column indicating that the view referenced invalid tables or columns or functions.
After dropping these views and recreating them with corrections where required, the INSERT statement executed successfully.

We’re not quite sure what the problem is but it appears that the invalid views are causing a problem with the schema query (SELECT statement) when it’s used in the INSERT statement. The schema query executes successfully on its own and I can also execute INSERT statements with VALUE clauses but the combination of INSERT INTO and SELECT always fails until the problem with the views is resolved.

Advertisements

Written by Sea Monkey

September 12, 2013 at 8:00 pm

Posted in Development

Tagged with