MySQL migration: MyISAM to InnoDB > MySQL Tip

Very good article. Looks like something I may someday need.

i. there's a COMMIT. where's the ROLLBACK?
ii. how about handling foreign keys?

not nitpicking. just honestly asking how
you handled them, if you did.

i. COMMIT and ROLLBACK always come in pairs. The former would be pointless without the later.

ii. Yes, they're there. That's one of the points for using InnoDB.

Not to nitpick, just honestly asking why you couldn't be bothered to look on the MySQL site before implying FUD.

i was asking about the author's experience
with respect to using mysql and his
corresponding code changes. that's the point of
the article and not strictly mysql usage,
which i could easily have looked up in the their
site.

the COMMIT/ROLLBACK pair's impact on the code
is the point. not their existence.

same with how he handled foerign keys in
the code.

the problem with you mysql churchgoers
is that you identify anything short of an
obvious question as FUD against mysql,
which is not.

go eat some shit, wiseass.

I considered going into more detail about the changes in the PHP code, but wanted to limit the article to the MySQL conversion mechanics. Otherwise, it would have made the article way too long.

In my case, I added ROLLBACK to each "or die()" statement when calling MySQL from PHP. In my testing, InnoDB will do a rollback automatically if the script fails anywhere between the BEGIN/START and COMMIT, but it is always good form to be explicit.

Since MyISAM doesn't handle foreign keys, the application code already dealt with cascade deletes. It would be more efficient and safer to let InnoDB handle that, but it was beyond the scope of my initial conversion. I may go back and make those changes in the future. Again, I didn't go into foreign keys to keep the article succinct.

Good questions.

The easy way to convert a myisam table to innodb is:

<tt>alter table mytable type=innodb;</tt>

Google for "mysql convert myisam to innodb". Or <a href="http://dev.mysql.com/doc/mysql/en/converting-tables-to-innodb.html" title="mysql.com">Click here</a mysql.com>.

Hi,


  I'm sorry to say so, but your article is rather incomplete. Someone already said that only one ALTER statement would do the conversion. Your method, though has the benefit of having a backup. Also, as someone said, you forgot to talk about the rollback, that is as important as the commit. In my opinion, an introduction to transactions would have been welcomed.


  I think it is incomplete since you failed to enumerate all the benefits of InnoDB and explaining the consequences of such a change.


  InnoDB use an innovative lock mechanism, while MyISAM uses table locks. It also implements all four levels of isolation and referential integrity. You can get much better concurrency with InnoDB, since it user row-level locks and a version-control-like mechanism (called MVCC to make sure no action is uselessly stopped by a lock. Of course, this increases the CPU overhead. There are also other differences (indexes, for example).


  For the question of disk usage, I am curious to see where you got your numbers? Directly from file sizes? I hope not.


  Backups more difficult? Not really, as you can still use mysqldump just like you did with MyISAM tables, but you can't do hot backups without the software sold by InnoDB, and its, in my opinion, reasonably priced (by this, I mean that if you have the volume to need hot backups absolutely, the 1300USD$ for the perpetual licence won't scare you off).


  Also, you should have warned the readers that they may loose functionnality when switching to InnoDB. Count()s will be slower and full-text indexing is not supported in InnoDB. One must make sure this will not affect the design or performance ot the application.

Although I totally jive with the migration from MyISAM to InnoDB, I can see a potential problem with such a migration.

Consider this scenario: I have an application with its own built-in search engine that employs the MyISAM table's native FULLTEXT index types for keyword searches. Conversion of the tables from MyISAM to InnoDB would cause fatal MySQL errors as long as the FULLTEXT indexes are in place, however, removing them would also cause fatal errors with the SELECT MATCH () AGAINST () queries that employ the FULLTEXT indexes.

What would you do in such a situation like this if it were asked to ensure ACID integrity of the table structures via conversion to InnoDB in light of this roadblock?

Thanx
Phil

AFAIK, you would have to leave the tables that use the FULLTEXT feature as MyISAM. You could convert the other tables to InnoDB. The only other solution I can think of is to move the text search into your application code which would be difficult and probably slower. Each storage engine has advantages and disadvantages. Good luck!

Types Of MYSQl

Tremendously useful guide, just what i was looking for, thank you!

We have a drupal intranet serving just under 6000 people with about 100 authors, and MyISAM just don't cut the mustard

Thanks again.

Thanks a lot

I have followed this steps and I does not have encountered any errors, but when I dump again the migrated database (w/ type=InnoDB) then, when I was checking the dumped file the type I saw again was type=MyISAM instead of TYPE=InnoDB..... Please help me, what's should I do?...

thank you! did the trick for me. save me a lot of time.

However it's helpful guide please consider the fact, that your foreign keys declarations will be lost. MyISAM ignores F-keys declarations upon creation of tables and if you haven't original database schema, you're probably lost. That's pity.

I'm going to try it - but like phil above - i have a full text index used for searches. I think instead of this I will be moving the fulltext search to another table.