Changing from MyISAM to InnoDB in MYSQL DB

[questorfla]

Given the following statements in a "template.sql" used as the starting point for building a MySQL DB.

Code: Text [Select]

1. DROP TABLE IF EXISTS `cat_permissions`;
2. CREATE TABLE IF NOT EXISTS `cat_permissions` (
3.   `category_id` int(11) NOT NULL default '0',
4.   `user_id` int(11) NOT NULL default '0',
5.   `permission` enum('View') NOT NULL default 'View',
6.   PRIMARY KEY  (`category_id`,`user_id`)
7. ) TYPE=MyISAM;

If I change the 'Type' to "Type=InnoDB" ...        When I run the install, I just wondered what the chances of it working?   I would like to convert a DB from MySQL to InnoDB and not real sure about the consequences of the making the change.  Either that, of find out why this won't run under MySQL Vesion 5.5, or any version since 5.1

[Stoic Joker]

They're both MySQL database types, one is just transactional. A transactional db is something you either need (for certain type of data processing) or you don't (because it has more overhead).

IIRC the handling of NULL changed around then, I had a few intranet sites that needed some quick tweaking when I upgraded the MySQL db engine because of the way I had (miss)used NULL on a few columns.

[questorfla]

That could be it as the error I got was something about invalid values.  What I changed was the TYPE=MyISAM to ENGINE=MYISAM as I had been told of that change.  but I knew there had to be more than just that. And it is a long jump from 5.1 to 5.5.  Additionally I ran into some issues in PHP handlers.  Way over my capabilities in that department.  That is why I gave up Software support of Hardware support.
But lately even hardware has begun to morph at a rate faster than I can keep up with it.
At least I feel a little better to know I am not alone in my despair.

[Shades]

Are you hung up on database names or can you use whatever database?

With the latter, you could make a database "<your db name>_ISAM", recreate all tables & columns from "<your db name>" into "<your db name>_ISAM", then transfer/replicate the data and point your application(s) to use "<your db name>_ISAM"?

This way you are creating a test environment where you can see if the engine change is beneficial or not (to current the work flow).

Really consider the vastly improved MySQL Workbench 6.2.4 as your assistant in this task (Oracle provides Community (free) and commercial editions). The Community edition (which I have a bit of personal experience with) helped me out of a situation with my mediawiki setup and showed me that its wizard for replicating MySQL databases is very competent while very easy to use.

The look and feel of MySQL Workbench has improved a lot, making it much(!) more useful than previous incarnations of this software. Now this software is practically similar to the software that comes with full-fledged database solutions such as those from Oracle and Microsoft.