MyISAM to InnoDB

[raymondu999]

Hi all.

I'm doing a databasing course in university this year. Now the thing is, in MySQL Community Server for Mac, and MAMP as well, the databases default to MyISAM, when instead I really need InnoDB to be the default. Is there any way I can enable this? Thanks

 

[SrWebDeveloper]

Beyond the help given below, visit this page for full details including dealing with buffer size setup and runaway rollback situations.

Generally, to convert a non-InnoDB table to use InnoDB use ALTER TABLE:

Example:
ALTER TABLE t1 ENGINE=InnoDB;

Important:
Do not convert MySQL system tables in the mysql database (such as user or host) to the InnoDB type. This is an unsupported operation. The system tables must always be of the MyISAM type.

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 ... ENGINE=INNODB, or create an empty InnoDB table with identical definitions and insert the rows with INSERT INTO ... SELECT * FROM ....

[source: MySQL 5.0 Ref Manual]

I'd export the current MyISAM tables into simple .sql files and use that for re-insertion if you opt to do that method. Maybe use phpMyAdmin to simplify the export process, plus you have emergency backup.

-jim

 

[raymondu999]

Ok, maybe I was being a bit unclear. What I meant was that I wanted to set InnoDB sort of as "default" so that everytime I initialise a new table, it runs on the InnoDB engine. Is that possible?

 

[SrWebDeveloper]

Sure is.

Edit my.cnf to include:

default-table-type = INNODB

Or, when you startup mysqld add this parameter:

--default-table-type=innodb

Or, when using phpMyAdmin, simply select it from the pulldown when adding a new table.

-jim

 

[raymondu999]

Mind pointing me in the direction where I can find this my.cnf file?

 

[SrWebDeveloper]

Mind pointing me in the direction where I can find this my.cnf file?

The file my.cnf is the default MySQL preferences file which contains directives and settings that control the behavior of the daemon at runtime.

The default location/filename on *nix systems is usually /etc/my.cnf

Please consult this page for details on Windows and other Unix setups involving this file if you don't have the file in that location. Beyond the help on that page, if you run *nix use the "locate my.cnf" command to find the file on the server, assuming it exists. Otherwise, create it in whatever directory MYSQL_HOME environment variable points to then chmod it to 644 and chown it to the same user/group associated with MySQL on your server. If you end up creating it, do so with a standard text editor as follows:

[mysqld]
default-table-type = INNODB

Plus any other preferences you wish (use the link in this reply for help).

Then restart mysql. I want to re-emphasize the important notes I said in my original reply, and encourage a backup, always.

-jim

 

[raymondu999]

I'm not really on a "*nix" system per se. I'm running on Leopard. I'm using the native MySQL Community Server as opposed to MAMP. Where can I find this file?

 

[SrWebDeveloper]

I'm not really on a "*nix" system per se. I'm running on Leopard. I'm using the native MySQL Community Server as opposed to MAMP. Where can I find this file?

Based on a few web pages out there where people installed MySQL on Leopard I've seen this path frequently:

/opt/local/etc/mysql5/my.cnf

Beyond that you can use the locate command in terminal mode on your Mac and I already gave you a link which outlines where that file gets installed based on platform and info on how to create the file.

Because you didn't install MAMP, much of the tweaking of MySQL for advanced setup like this is done via shell (Mac Terminal). FYI.

Please note, for the record, your original request is not novice level - changing the default table format is a rather advanced thing to do, and extremely dangerous in the hands of someone who is inexperienced in such matters. Trying to be as polite as possible and not disrespectful, I found it surprising you didn't know what my.cnf was and also that you never thought to use the locate command in Mac terminal mode. My original response and overall tone assumed you were not novice based on the advanced nature of your request. I want to emphasize it is time to sit down and actually read the MySQL manual - something I don't recommend often since that software virtually installs itself with common, sensible defaults, across nearly all platforms. But when you start getting into the advanced features, it requires a lot more reading of boring docs and maybe some additional online research (Google) to ensure what you want to do is done without destroying a database or performance issues affecting queries. Many folks here run MAMP, so this forum focuses mostly on that setup as you may have noticed.

I say all this *after* I posted links and help in numerous prior replies - I am not just brushing you off telling you to "RTFM" as many others might do, you have been helped.

Nothing personal intended by any of this, honest. Thanks and take care. You got it from here!

-jim

 

[raymondu999]

No worries there none taken

THe thing is I'm just a university year 1 student, taking a basic databasing course. The course is really held with more focus towards Windows platforms, with step-by-step guides if you're using Windows, but I (and several other Mac die-hards) simply refuse to touch Windows