MySQL Error 1033: Incorrect Information in File

If you’ve ever been plagued by an error 1033 issue in MySQL (replication will show it as well), then I might be able to help you out. The error reads something like, “Incorrect information in file: ‘./mydb/table.frm’. I classify this as another one of MySQLs cryptic error messages. Here is how I determined that this was my problem.

Googling around got me an answer, but I had to read a bunch of different responses to piece together the answer. Essentially this issue (in my case) was a result of the InnoDB engine not loading up when MySQL was restarted. Therefore when MySQL tried to read the frm file (table description) which was written for an InnoDB table with the MyISAM reader, it didn’t like it. Since MyISAM is the fallback engine, it went to that and the table became unusable.

Last_Errno: 1033
Last_Error: Error 'Incorrect information in file: './st/table.frm'' on query. Default database: 'mydb'. Query: 'INSERT INTO `table` (`id`,`col1`) VALUES (1,'foobar')'
# or
mysql> REPAIR TABLE table;
+-------------+--------+----------+----------------------------------------------------+
| Table       | Op     | Msg_type | Msg_text                                           |
+-------------+--------+----------+----------------------------------------------------+
| mydb.table  | repair | Error    | Incorrect information in file: './mydb/table.frm'  |
| mydb.table  | repair | error    | Corrupt                                            |
+-------------+--------+----------+----------------------------------------------------+
2 rows in set (0.02 sec)

I already knew my table table is an InnoDB table. To be sure that this was the issue, I simply checked to see which engines were loaded (removed some for brevity).

mysql> SHOW ENGINES;
+------------+----------+----------------------------------------------------------------+
| Engine     | Support  | Comment                                                        |
+------------+----------+----------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | DISABLED | Supports transactions, row-level locking, and foreign keys     |
| CSV        | YES      | CSV storage engine                                             |
+------------+----------+----------------------------------------------------------------+

So here I notice that InnoDB is disabled. (Note: I skipped the step where I check my my.cnf to make sure the skip-innodb line in the [mysqld] section was commented out. I already knew it was, but if you are unsure, check.) So I pop over to the error log and I see this:

InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
091222 15:21:55  InnoDB: Unable to open the first data file
InnoDB: Error in opening ./ibdata1
091222 15:21:55  InnoDB: Operating system error number 11 in a file operation.
InnoDB: Error number 11 means 'Resource temporarily unavailable'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/operating-system-error-codes.html
 79InnoDB: Could not open or create data files.

This says to me that it is likely that the MySQL restart didn’t go as well as the initscript would have liked me to believe. So I see what files are open and what’s running:

[root@db mysql]# lsof | grep ibdata1
COMMAND     PID         USER   FD      TYPE             DEVICE        SIZE       NODE NAME
mysqld    24574        mysql    4uW     REG                8,3  5018484736   61538308 /var/lib/mysql/ibdata1
[root@db5 mysql]# ps ax | grep mysqld
24536 pts/0    S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql/ --pid-file=/var/lib/mysql//db.example.com.pid
24574 pts/0    Sl     7:58 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql/ --user=mysql --pid-file=/var/lib/mysql//db.example.com.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
26635 pts/0    S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql/ --pid-file=/var/lib/mysql//db.example.com.pid
26666 pts/0    Sl     0:06 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql/ --user=mysql --pid-file=/var/lib/mysql//db.example.com.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock

Well look at that, 2 versions of MySQL running and ibdata1 is being held open by one of them. So now I do the ugly thing and kill the mysqld process that holding the file lock and then restart MySQL:

[root@db mysql]# kill -9 24574
[root@db mysql]# ps ax | grep mysqld
24536 pts/0    S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql/ --pid-file=/var/lib/mysql//db.example.com.pid
27051 pts/0    Rl     0:02 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql/ --user=mysql --pid-file=/var/lib/mysql//db.example.com.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
27075 pts/0    S+     0:00 grep mysqld
[root@db mysql]# /etc/init.d/mysql restart
MySQL manager or server PID file could not be found!       [FAILED]
Starting MySQL...............                              [  OK  ]

So back over to MySQL:

mysql> SHOW ENGINES;
+------------+----------+----------------------------------------------------------------+
| Engine     | Support  | Comment                                                        |
+------------+----------+----------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | YES      | Supports transactions, row-level locking, and foreign keys     |
| CSV        | YES      | CSV storage engine                                             |
+------------+----------+----------------------------------------------------------------+

There it is. Now you should be able to start up replication again (if that was the issue). Or if you didn’t discover this issue with replication, you should just be able to use your DB like normal.

Posted in MySQL. Tags: , . 1 Comment »
  • ian

    Thank you Eric… this helped me a lot to solve my problem 🙂