asked on

Any way to make InnoDB the default engine for creating tables in a 3rd party controlled MySQL db?

I had the problem that a bunch of tables in my database got created using the MyISAM engine, which rendered my foreign keys useless. With the help of an expert on this site, I have successfully converted my MyISAM tables to engine=InnoDB. The database was created by my ISP and I do not have any kind of control over it, other than what I can set or unset within my own database.

Is there any way to set a database-level parameter or setting such that the:
CREATE TABLE XYZZY (id int, col1 int);
defaults to engine=InnoDB without the need to explicitly add the engine=InnoDB parameter to the end of each create table statement?

Rurne

Unfortunately, I haven't seen any option to use engine=InnoDb in a create/alter database statement. However, several options do exist.

1. ALTER TABLESPACE allows you to specify which engine is default for a particular data file within the database. If you're not familiar with tablespaces, I recommend against this action.
2. Starting mysql with --default-storage-engine=InnoDb. This becomes the default for the life of the current mysqld instance. If you want this to become the default for the server, add `default-storage-engine = InnoDb` (sans backticks) to your my.cnf file.
3. execute 'SET ENGINE = InnoDb' at the command prompt. If you are accessing MySQL directly through a SSH session and are doing this on the command line, this will set the default engine for the life of your session. Once you log out and log back in, it will be reset to MyISAM until you run this command again.
4. Alter tables upon creation as you have been. A bit inefficient, but it provides more granular control.

Matthew Kelly

I am not sure if there is a default setting. I had the same issue on a shared hosting server and just put the "ENGINE=InnoDb" after each CREATE TABLE statement like you suggested.

There are command lines way of doing it:
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html

Search for the word "default" and keep going until you see info on setting the default storage engine.

CREATE TABLE `userlevel_db` (
  `userLevel` int(11) NOT NULL auto_increment,
  `userLevelName` varchar(20) default NULL,
  `userLevelDesc` text,
  PRIMARY KEY  (`userLevel`)
) ENGINE=InnoDb DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Open in new window

Tomas Helgi Johannsson

Hi!

You can set the default storage engine by setting it either on startup by executing the command (adding the following option to the startup command )
mysqld --default-storage-engine=MyISAM
or you could set the default storage engine in the my.ini (my.cnf) file

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_storage_engine
http://dev.mysql.com/doc/refman/5.0/en/server-options.html
http://dev.mysql.com/doc/refman/5.0/en/option-files.html

Regards,
Tomas Helgi

ACSIPaul

ASKER

Thanks everybody.

This is a shared hosting environment where the database engine is hosted by the hosting company. I do not have access to the MySQL startup parameters or any of the configuration files. I only have access within my own database/schema. That's why I was hoping for a setting within the database.

Rurne

Well, as I said, you can enter "SET ENGINE = InnoDb" at the command prompt to set your default for the current session (Doesn't work if you're using a 3rd-party interface like phpMyAdmin). However, this is a server-side config option, so if you can't control the tablespace files or reboot the MySQL daemon, there is no way of establishing the default engine for the database.

ACSIPaul

ASKER

Hi Rurne,

I tried "SET ENGINE=InnoDB;", but I get the error:
ERROR 1193 (HY000): Unknown system variable 'engine'

ASKER CERTIFIED SOLUTION

Rurne

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ACSIPaul

ASKER

Thank you Rurne. I guess I'll just have to remember to either always set the engine when I connect, or include the engine=InnoDB on the create table statement. Bummer. Anytime I'm left remembering, there is always once or twice where I forget. I love set 'em and forget 'em options, but alas, I learned something new today. Thank you.