InnoDB keep as default, when the default engine is MyISAM

[ITGabs]

I checked this setup from the beginning and MyISAM is the default in all the settings that I saw


but for some strange reason all the new tables created in the database are InnoDB as default.

I did a fast check and I have this problem in almost every new database, all the new WordPress installation are a mess between InnoDB and MyISAM.

I just notice from other threads that some plugins or addons are forcing innoDB as default, I am not using Fantastico or software addons, the WordPress installations I did it by my self with ssh.

My second request or question is how can I fix the mess that I have now? there is a way to change all the database tables that are innoDB to MyISAM?

Thanks

 

[cPanelMichael]

Hello

Out of curiosity, is there any particular reason you prefer not to use InnoDB? In MySQL 5.5 and higher, InnoDB is the default storage engine. There is a good document on it's advantages here:

MySQL - InnoDB Default

Thank you.

 

[Nick57]

For simple reason that many cms are not having set correct relationships in tables, it will break with InnoDB.
Perhaps in a few years time when everything is more ready for InnoDb it might be benificial for the moment it breaks to many websites!!!

For the moment put this in your /etc/my.cnf

#
default-storage-engine=MYISAM
#


restart mysql after change.

To fix tables already wrong you could try: ALTER TABLE table_name ENGINE=MYISAM;

 

[gohighvoltage]

Re: Turn off Innodb in Mysql 5.6 and default to myisam

All my databases are myisam. If leaving innodb on with mysql 5.6, does this only effect when new databases are created? So should I just leave it alone?

 

[gohighvoltage]

All my current databases are myisam. I just upgraded to 5.6. Are you saying I should add

default-storage-engine=MYISAM

To my my.cnf file to prevent issues with my current databases?

Also, this is just setting the default engine to myisam, but innodb will still work? Just curious cause some mail programs only work on innodb.

Thanks in advance!

- - - Updated - - -

Just want to make sure I don't mess anything up!!

 

[Nick57]

The new setting is only working after you restart mysql and only effective for new database/tables created.

One remark, if the software to create a new DB with tables is NOT expliciet saying what to use it will 'default to InnoDB.
Hence you need to add this rule in the my.cnf to avoid the standard InnoDB.

Again, this is only for new db installations, nothing will be touched what already is in place.

 

[ITGabs]

Hi Michael,

Thanks for the document, I read it and all the features look great, with "full text search"

I had a lot of problems in the past with InnoDB "lock tables", and the performance was always bad (around 2005), I had only few projects with transactional databases design, and after Oracle bought InnoDB or MySQL I am a bit skeptic about they neutrality and point of view.
So InnoDB for me is looking for a very old and bad past.

Now I am somewhat involved into NoSQL or big table projects that solved many of the MySQL problems in general, distributed databases, clusters, mirroring, replication, fast search etc. so is another personal reason to avoid the relational databases.

Another good and simple good reason is just to avoid layers of unnecessary complexity, I am using and learning a lot about cPanel features, but these things about hardcoded configurations and perl incompatibilities... the only feature that I need for MySQL is just stability, and really I don't want to have problems or time waste in the future just for something that is just one line of code somewhere into the cPanel configuration, if some of the users want to test the new InnoDB features in their WordPress is their decision, for me is just to follow a good standard, if it's working don't touch it, and keep everything in the same language (engine in this case) as possible.

 

[ITGabs]

Thanks Nick57,

The first thing that I did was check my /etc/my.cnf I just can't find where is the setting that set as default as InnoDB

About:
ALTER TABLE table_name ENGINE=MYISAM;

do you have a script to do that in all the databases in the server?

 

[Nick57]

You need to add that setting as I have explained, then restart mysql.

Sorry, no script, don't want to be responsible for your databases ;-)

 

[ITGabs]

I am digging into the servers and I did a lot of testing just to close this task

Apparently in some cPanel update there was a hardcoded InnoDB setting that overwrite my own default "MyISAM" but now it's back to normal, I created new tables, I did a new wordpress instalation and everything is "MyISAM", just to left clear and in response to Nick57 from the beginning one of the first things that I did was setup /etc/my.cnf with default-storage-engine=MyISAM, actually this is what I have now.

[mysqld]
innodb_file_per_table=1
default-storage-engine=MyISAM
open_files_limit=2794
local-infile=0
~
"/etc/my.cnf" 5L, 100C

I thought that the wordpress install could be the problem too but there is no InnoDB settings and I found a website that have his own code and the first sql import have no settings for the engine but all the backups and the production bd is InnoDB, but since the server is now working as I expect only left to fix all the InnoDB problems.

I hope this is useful for someone

mysqlcheck -A --auto-repair

this will fix all the problems in all the tables/databases, and is quite good because list all the tables of the server in a easy format, and it is required for the migration from InnoDB to MyISAM

.....
rachnzt1_wp.wp_options                             OK
rachnzt1_wp.wp_photocrati_albums                   OK
rachnzt1_wp.wp_photocrati_ecommerce_settings       OK
rachnzt1_wp.wp_photocrati_galleries                OK
travelth_wp.wp_links                               OK
travelth_wp.wp_options                             OK
travelth_wp.wp_orderdata                           OK
....

This is a useful query to see all the tables and their respective Engines

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES;
+----------------------+----------------------------------------------+--------------------+
| TABLE_SCHEMA         | TABLE_NAME                                   | ENGINE             |
+----------------------+----------------------------------------------+--------------------+
| information_schema   | CHARACTER_SETS                               | MEMORY             |
| information_schema   | CLIENT_STATISTICS                            | MEMORY             |
| information_schema   | COLLATIONS                                   | MEMORY             |
| information_schema   | COLLATION_CHARACTER_SET_APPLICABILITY        | MEMORY             |
| information_schema   | COLUMNS                                      | MyISAM             |
| information_schema   | COLUMN_PRIVILEGES                            | MEMORY             |
| information_schema   | INDEX_STATISTICS                             | MEMORY             |
| information_schema   | ENGINES                                      | MEMORY             |
| information_schema   | EVENTS                                       | MyISAM             |
| information_schema   | FILES                                        | MEMORY             |
.....

I am thinking how and when I will change the engines for the sites that I know that should be MyISAM

We are using cPanel to avoid mixing servers with our main development and small marketing campaigns, SEO etc, so I am only in charge of the security or some special or complex stuff, there are a lot of small outsourcing companies that usually release these sites or designs and later people not so related to IT update the contents so it is usual that these sites are overloaded with shitty plugins, sometimes hacked, but nothing more than that, nothing critical, maybe I will do in the weekend some testing and later with all the rest, but still I was looking for recommendations or warnings about change the engine and I found this thread interesting about a heavy load database.

"hot switch" from MyISAM to InnoDB. Is this dangerous? Databases forum at WebmasterWorld

BTW!! in wherever setting or query you need to write exactly MyISAM (case sensitive) sometimes MYISAM just not work or produce strange bugs

 

[Nick57]

I had same settings, but an update from cPanel overruled it, as you can see in the nightly updates, it 'tries' to fix any errors in the /etc/my.cnf file, but no idea what 'criteria' they use as being 'corrupt'

[20140307.063004] 23% complete
[20140307.063004] Processing: Checking CloudLinux installation
[20140307.063004] - Processing command `/usr/local/cpanel/bin/cloudlinux_update`
[20140307.063004] Processing: Checking for corrupt my.cnf and fixing if needed
[20140307.063004] - Processing command `/usr/local/cpanel/scripts/autorepair mysqlconfopenfileslimit`
[20140307.063004] [18578] Requesting script ... Done

 

[Nick57]

I would not recommend to do a hotswap, it is more then enough risky to swap from innodb to myisam

 

[ITGabs]

Finally InnoDB still suck, is a big mess, I have locked tables again, entire ghost databases in the idb files, almost 80% of the mysql.err file about that and an incredible size in that ibdata1

when I tried to optimize or looking for command to fix the problems again the solutions are a waste of time things that in myisam are straight forward or just one command in InnoDB are tasks table by table, every database deleting or moving critical files, backups, long mysql shutdowns, restoring data and users.

Check the best solutions to any problem (and check the comments):

ibdata1 getting bigger and bigger?
MySQL innodb not releasing disk space after deleting data rows from table - Stack Overflow
What happens is, that even if you drop the database, innodb will still not release disk space. I had to export, stop mysql, remove the files manually, start mysql, create database and users, and then import. Thank god I only had 200MB worth of rows, but it spared 250GB of innodb file. Fail by design. Dec 25 '09 at 20:55 -Gilm

Entire ghost databases flooding the mysql.err log and deleted long time ago?
MySQL :: MySQL 5.5 Reference Manual :: 14.21.3 Troubleshooting InnoDB Data Dictionary Operations

Ha! how to recover a InnoDB table?
mysql - How to Recover an InnoDB table whose files were moved around - Database Administrators Stack Exchange
A irony but this is what I feel "This question would be incredibly helpful to those out there that have shot themselves in feet with AK47s unintentionally hosting InnoDB tables." -RolandoMySQLDBA Sep 28 '11 at 17:29

I want to completely turn off InnoDB from cPanel. and be sure that will never come back

Another question about turn off InnoDB redirect to this one that I started, could someone help I want to be sure that will not block a core feature, and know what third parties like horde will be affected.

 

[cPanelMichael]

I want to completely turn off InnoDB from cPanel. and be sure that will never come back

Another question about turn off InnoDB redirect to this one that I started, could someone help I want to be sure that will not block a core feature, and know what third parties like horde will be affected.

This is documented here:

Remove support for InnoDB

Thank you.