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,953 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 25

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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

Myth Busting: MongoDB Scalability (it scales!)

I was talking with one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. He mentioned to me that several customers have been telling him that “MongoDB doesn’t scale!” MongoDB’s scalability was in question?

My response was, “Is that a joke?"

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

628 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