Solved

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

Posted on 2008-06-17
8
2,928 Views
Last Modified: 2010-04-21
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?
0
Comment
Question by:ACSIPaul
8 Comments
 
LVL 9

Expert Comment

by:Rurne
ID: 21804735
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.
0
 
LVL 18

Expert Comment

by:Matthew Kelly
ID: 21808174
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

0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
ID: 21810982
  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
0
 
LVL 4

Author Comment

by:ACSIPaul
ID: 21814427
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.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Expert Comment

by:Rurne
ID: 21814816
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.
0
 
LVL 4

Author Comment

by:ACSIPaul
ID: 21815207
Hi Rurne,

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

0
 
LVL 9

Accepted Solution

by:
Rurne earned 500 total points
ID: 21815337
Ugh.  My bad.  It should be `storage_engine` not `ENGINE`.  This is what happens the first week you stop drinking coffee. :)

More info here:
http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html
http://dev.mysql.com/doc/refman/6.0/en/storage-engine-setting.html
0
 
LVL 4

Author Closing Comment

by:ACSIPaul
ID: 31467985
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

13 Experts available now in Live!

Get 1:1 Help Now