• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1757
  • Last Modified:

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?
  • 3
2 Solutions

take a look at here..


[Taken from mysql.com]

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.

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:

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now