Can't change Mrg_MyIsam Tables

I'm using sqlYog to do db dev on a windows system. and it seems that all my tables are Mrg_MyIsam and I can't change them to Innodb. not even with the alter table command. When I export the database to file it says it's creating all the tables with the engine=Innodb setting but I'm just not seeing that.

Any reason why?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UmeshMySQL Principle Technical Support EngineerCommented:

take a look at here..

[Taken from]

16.7.2 Converting MyISAM Tables to InnoDB
Important: You should not convert MySQL system tables in the mysql database (such as user or host) to the InnoDB type. The system tables must always be of the MyISAM type.

If you want all your (non-system) tables to be created as InnoDB tables, you can, starting from the MySQL 3.23.43, add the line default-table-type=innodb to the [mysqld] section of your `my.cnf' or `my.ini' file.

InnoDB does not have a special optimization for separate index creation the way the MyISAM storage engine does. Therefore, it does not pay to export and import the table and create indexes afterward. The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table. That is, use ALTER TABLE ... TYPE=INNODB, or create an empty InnoDB table with identical definitions and insert the rows with INSERT INTO ... SELECT * FROM ....

If you have UNIQUE constraints on secondary keys, starting from MySQL 3.23.52, you can speed up a table import by turning off the uniqueness checks temporarily during the import session: SET UNIQUE_CHECKS=0; For big tables, this saves a lot of disk I/O because InnoDB can then use its insert buffer to write secondary index records in a batch.

To get better control over the insertion process, it might be good to insert big tables in pieces:

INSERT INTO newtable SELECT * FROM oldtable
   WHERE yourkey > something AND yourkey <= somethingelse;

After all records have been inserted, you can rename the tables.

During the conversion of big tables, you should increase the size of the InnoDB buffer pool to reduce disk I/O. Do not use more than 80% of the physical memory, though. You can also increase the sizes of the InnoDB log files and the log files.

Make sure that you do not fill up the tablespace: InnoDB tables require a lot more disk space than MyISAM tables. If an ALTER TABLE runs out of space, it will start a rollback, and that can take hours if it is disk-bound. For inserts, InnoDB uses the insert buffer to merge secondary index records to indexes in batches. That saves a lot of disk I/O. In rollback, no such mechanism is used, and the rollback can take 30 times longer than the insertion.

In the case of a runaway rollback, if you do not have valuable data in your database, it may be advisable to kill the database process rather than wait for millions of disk I/O operations to complete. For the complete procedure, see section 16.9.1 Forcing Recovery.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jayrodAuthor Commented:
nope.. i've tried all of that. sorry if I didn't mention that. I drop all the tables in my database and recreated the database and all tables had the innodb option. yet they're still not innodb
Are your table type still Mrg_MyIsam?
This is a MERGE table type, means it is a "logical" table which consists of 1 or more "physical" MyISAM tables.
To find the physical table name, do a SHOW CREATE TABLE tablename;
You should see
) TYPE=MRG_MYISAM UNION (table1, table2, ...)
The table1, table2, ... are the "real" MYISAM tables you have. If you have only 1 then, you can DROP the MRG_MYISAM table and use ALTER TABLE to change the table1 to InnoDB and rename it if necessary.

Can you generate the CREATE TABLE scripts into a file from sqlYog?
If can, then you can modify the file with InnoDB and execute it using mysql < file.sql
jayrodAuthor Commented:
language      CREATE TABLE `language` (\
  `languageId` int(11) NOT NULL auto_increment,\
  `shortCode` varchar(5) NOT NULL default '',\
  `languageName` varchar(50) NOT NULL default '',\
  PRIMARY KEY  (`languageId`),\
  UNIQUE KEY `shortCode` (`shortCode`),\
  UNIQUE KEY `languageName` (`languageName`)\

It says it's innodb.. so maybe my sqlyog tool is to blame?
jayrodAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.