Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
2,967 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 26

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

963 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