Can't change Mrg_MyIsam Tables

Posted on 2004-11-03
Last Modified: 2012-08-14
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?
Question by:jayrod
    LVL 26

    Accepted Solution


    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.

    LVL 3

    Author Comment

    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
    LVL 8

    Assisted Solution

    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
    LVL 3

    Author Comment

    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?
    LVL 3

    Author Comment


    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    I have been using r1soft Continuous Data Protection ( for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
    Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video discusses moving either the default database or any database to a new volume.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now