Skip to main content

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

Comments

13 comments

Date Votes
  • 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.
    0
  • 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;
    0
  • 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?
    0
  • 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! [COLOR="silver">- - - Updated - - - Just want to make sure I don't mess anything up!! ;)
    0
  • 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.
    0
  • 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.
    0
  • 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?
    0
  • 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 ;-)
    0
  • 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. [url=http://www.webmasterworld.com/databases_sql_mysql/4194585.htm]"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
    0
  • 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
    0
  • Nick57
    I would not recommend to do a hotswap, it is more then enough risky to swap from innodb to myisam
    0
  • 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? [url=http://stackoverflow.com/questions/1270944/mysql-innodb-not-releasing-disk-space-after-deleting-data-rows-from-table]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? [url=http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21.3 Troubleshooting InnoDB Data Dictionary Operations Ha! how to recover a InnoDB table? [url=http://dba.stackexchange.com/questions/6268/how-to-recover-an-innodb-table-whose-files-were-moved-around]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.
    0
  • cPanelMichael
    [quote="ITGabs, post: 1715331">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.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post