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,936 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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 …
Creating and Managing Databases with phpMyAdmin in cPanel.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

809 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