Mysql – Timeout on ALTER TABLE table ENGINE=InnoDB – is table still converted

innodbmyisamMySQLmysql-5.5

I'm converting tables in an old MYISAM database to InnoDB to increase robustness in case of power loss etc.

I'm using Mysql 5.5.x

Before doing the production system, I am doing a trial run on a copy of the DB on a test machine – just in case.

I backed up the tables with mysqldump using this information.

Some tables take more than the timeout (600sec) to convert, and so I get an error 2013:

ALTER TABLE voice.members ENGINE=InnoDB Error Code: 2013. Lost connection to MySQL server during query

When I check the table later, it's listed as InnoDB.

show table status in voice like "members";

Here are my questions:

Can I assume that the conversion went as intended?
Are there any obvious checks I should perform to ensure that integrity is not compromized?
Should I enforce that the DB is single user before conversion, or is my only worry that response time might be bad while conversion is ongoing + loss of work in case of a restore from the initial backup?

Best Answer

QUESTION #1

Can I assume that the conversion went as intended?

Never assume a conversion until you test it. Thank you for backing it up in advance.

QUESTION #2

Are there any obvious checks I should perform to ensure that integrity is not compromised?

For any given table mydb.mytable you converted to InnoDB, please check two things

TABLE COUNT

Take a count of all the rows

SELECT COUNT(1) FROM mydb.mytable;

Run the same count on the MyISAM backup.

CHECKSUM

Run a checksum of the whole table

CHECKSUM TABLE mydb.mytable;

Run the same checksum on the MyISAM backup.

SUMMARY ON QUESTION #2

You should get the same count and checksum values for both the InnoDB and MyISAM tables.

SUGGESTION FOR QUESTION #2

If you would like to manage the InnoDB conversion in stages, try doing this next time

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ENGINE=InnoDB;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Use the table, test it in staging, kick the tires.

If you are satisfied the table is fine, drop the old table

DROP TABLE mydb.mytable_old;

or retain it as a backup

ALTER TABLE mydb.mytable_old RENAME mydb.mytable_myisam;

QUESTION #3

Should I enforce that the DB is single user before conversion, or is my only worry that response time might be bad while conversion is ongoing + loss of work in case of a restore from the initial backup?

If you want convert a live MyISAM table to InnoDB, see my post Converting live MyISAM table to InnoDB

If you are very skeptical or fearful of data loss, your idea of single user access is fine.

You could just restart mysql like this

service mysql restart --skip-networking

service mysql restart

GIVE IT A TRY !!!

I have very bad news for you.

You should not have deleted the ibdata1 file. Here is why:

ibdata1 contains four type of information:

table metadata
MVCC data
data pages (with innodb_file_per_table enabled)
index pages (with innodb_file_per_table enabled)

Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, ... ibdata1.

I also have very good news for you along with some bad news.

It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer's web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).

Anyway, here a past post I made in the DBA StackExchange. I answered another question whose root cause was the mixing up of ITSIDs.

To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.

I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.

UPDATE 2011-10-17 06:19 EDT

Here is your original innodb configuration from your question:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000

Please notice that innodb_log_file_size is there twice. Look carefully...

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000

The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That's why InnoDB was missing from show engines;. Mystery solved !!!

UPDATE 2011-10-17 11:07 EDT

The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What's interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:

110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).

For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.

Mysql – InnoDB table vs MEMORY table for a thesql environment with many inserts

Clearly, it will be faster and more efficient to write/read to/from memory. In your case, losing the data is not a big issue as you stated. However, you need to be careful about the memory usage and limit the number of rows stored.