Rows missing after online conversion from MyISAM to InnoDB

We have a reasonably small database that we wanted to convert from MyISAM to InnoDB. Being database noobs, we just converted (using alter table) without even taking the site down.

Now that the conversion is done, a lot of intermittent rows seem to be missing. Is this possibly due to operations during conversion? Or is the issue somewhere else?

Performing an ALTER to change storage engines won't make rows disappear. However, let me offer some advice since you said you're 'database noobs' in your question.

When modifying existing schema or doing anything that could affect data, here's some basic advice:

• Make a backup first.

• Have a change plan.

• Test your plan on an offline host.

• Have a test plan to compare before and after data.

• Schedule and take a downtime.

• Take a backup and snapshot immediately after your downtime goes into effect and you verify traffic has stopped.

• If you're running MYISAM, use 'CHECK TABLE' to evaluate what you're dealing with before you ALTER.

• Copy the table locally in addition to your backup, just in case.

• Proceed with caution, enable "--show warnings" and other output so you have the full picture as you make your changes.

• If the data is important to you, hire a DBA, even if just to consult during the migration so you have a seasoned veteran by your side.

There's probably a lot more I could get into, but the above will provide you with options when something goes wrong.

As far as your missing data/rows, there's no way to know w/o a "before/after" snapshot to compare. You can compare against your latest backup to at least verify that much.

StackExchange Database Administrators Q#5119, answer score: 20