The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

InnoDB keep as default, when the default engine is MyISAM

Discussion in 'Database Discussions' started by ITGabs, Feb 25, 2014.

  1. ITGabs

    ITGabs Well-Known Member

    Joined:
    Jul 30, 2013
    Messages:
    81
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    I checked this setup from the beginning and MyISAM is the default in all the settings that I saw
    screeshot.25-02-2014 05.13.29.jpg

    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
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,694
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
  3. Nick57

    Nick57 Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    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;
     
  4. gohighvoltage

    gohighvoltage Member

    Joined:
    Jan 8, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    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?
     
  5. gohighvoltage

    gohighvoltage Member

    Joined:
    Jan 8, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    All my current databases are myisam. I just upgraded to 5.6. Are you saying I should add

    Code:
    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!! ;)
     
  6. Nick57

    Nick57 Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    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.
     
  7. ITGabs

    ITGabs Well-Known Member

    Joined:
    Jul 30, 2013
    Messages:
    81
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    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.
     
  8. ITGabs

    ITGabs Well-Known Member

    Joined:
    Jul 30, 2013
    Messages:
    81
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    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?
     
  9. Nick57

    Nick57 Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    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 ;-)
     
  10. ITGabs

    ITGabs Well-Known Member

    Joined:
    Jul 30, 2013
    Messages:
    81
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    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.

    Code:
    [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

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

    Code:
    .....
    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

    Code:
    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
     
    #10 ITGabs, Mar 7, 2014
    Last edited: Mar 7, 2014
  11. Nick57

    Nick57 Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    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
     
  12. Nick57

    Nick57 Well-Known Member

    Joined:
    Jul 19, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    I would not recommend to do a hotswap, it is more then enough risky to swap from innodb to myisam
     
  13. ITGabs

    ITGabs Well-Known Member

    Joined:
    Jul 30, 2013
    Messages:
    81
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    Root Administrator
    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.
     
  14. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,694
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    This is documented here:

    Remove support for InnoDB

    Thank you.
     
Loading...

Share This Page