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.
1 2 3 4 5 6 7 8 9 10 11 | 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).
1 2 3 4 5 6 7 8 9 | 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:
1 2 3 4 5 6 7 8 9 10 | 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:
1 2 3 4 5 6 7 8 | [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:
1 2 3 4 5 6 7 8 | [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:
1 2 3 4 5 6 7 8 9 | 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.
