Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. Can I assume that the conversion went as intended?
  2. Are there any obvious checks I should perform to ensure that integrity is not compromized?
  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?
share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

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_new 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

Login to mysql as root@localhost. Do the conversion, then

service mysql restart

GIVE IT A TRY !!!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.