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 not enabled by default?

Discussion in 'General Discussion' started by kazar, Jul 23, 2008.

  1. kazar

    kazar Active Member

    Joined:
    May 18, 2008
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    NYC/Earth
    cPanel Access Level:
    Root Administrator
    Hi there --

    I'm quite new to all of this so sorry if this is a "dumb question" ...

    I am not doing great at moving my old phpbb database to my new server, and I"m thinking of trying to use InnoDB storage engine rather than MyISAM which is the default on my new WHM/Cpanel server. In phpMyAdmin I notice that innoDB is not even enabled on my server (and I'm pretty surprised that it is not).

    How do I enable innoDB? I don't see any way in WHM or in phpMyAdmin (but it's probably staring me in the face). And how would i make innoDB the default storage engine?

    TIA!

    kazar
     
  2. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    You can enable InnoDB by accessing your server via SSH as root ( or another account then use su or sudo to gain root access). Once that is done, check /etc/my.cnf for an entry like:

    Code:
    skip-inndob
    
    If such an entry exists, remove or comment out the entry and restart MySQL.

    If such an entry does not exist, then there are other problems for which we will need more information.


    Once InnoDB is enabled, you can make it the default table type by specifying the following in /etc/my.cnf:

    Code:
    default-table-type=innodb
    
    InnoDB out performs MyISAM, once properly configured, but comes with a slightly higher cost of administrator overhead.
     
    #2 cPanelKenneth, Jul 24, 2008
    Last edited by a moderator: Mar 14, 2012
  3. kazar

    kazar Active Member

    Joined:
    May 18, 2008
    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    NYC/Earth
    cPanel Access Level:
    Root Administrator
    Thank you so much for the crystal clear instructions. After I edited the my.cnf file mysql would not stop from the command line using mysqld_safe stop (it told me "A mysqld process already exists") but it did restart from WHM.

    I'm back in biz!! Thanks again.

    kazar
     
  4. Ashioni

    Ashioni Member

    Joined:
    Apr 6, 2009
    Messages:
    13
    Likes Received:
    0
    Trophy Points:
    1
    This was very helpful.
     
  5. lbeachmike

    lbeachmike Well-Known Member

    Joined:
    Dec 27, 2001
    Messages:
    313
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Long Beach, NY
    cPanel Access Level:
    Root Administrator
    Hi there -

    I came across this thread because I have a hosting client seeking to host Magento, which lists InnoDB storage engine as a requirement.

    My servers currently host existing clients using MyISAM.

    Is there any impact on existing users/databases/scripts if I enable InnoDB as well?

    It was mentioned that there is a higher cost of Admin overhead - can anybody clarify?

    Thanks.

    Mike
     
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    I wouldn't suggest making InnoDB the default on a machine for one user requiring it to be enabled. If it is disabled in the /etc/my.cnf with that "skip-innodb" line, simply commenting out or removing that line, then restarting MySQL will enable InnoDB to allow the user to utilize it.

    As for a higher cost associated with InnoDB, if InnoDB tables corrupt, then MySQL itself will refuse to start. InnoDB corruption can be very difficult to resolve. If you are going to enable InnoDB where it isn't already, I highly suggest moving to a per-table space (file per table) for InnoDB tables, since the default for InnoDB tables is to be stored at /var/lib/mysql/ibdata1 file as a shared file, so all user tables are stored in that one file in that default setup.

    I have a guide on how to switch to using innodb_file_per_table instead so that you can avoid one user's table corrupting causing all of MySQL to refuse to start:

    innodb_file_per_table - Converting to Per Table Data for InnoDB - cPanel Forums
     
  7. lbeachmike

    lbeachmike Well-Known Member

    Joined:
    Dec 27, 2001
    Messages:
    313
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Long Beach, NY
    cPanel Access Level:
    Root Administrator
    Thanks for these details.

    So, if I understand correctly, this would allow me to enable InnoDB on the server, but then only this individual user would be utilizing InnoDB - and if there was any database corruption, it would only impact the individual user.

    Is this correct? In other words, does this only mitigate the risk, or completely eliminate the risk of other users being impacted?

    Also, will the code itself call for using InnoDB tables, or does something additional need to be done if it is not the server default?

    Also, in the Magento forum, I saw people discussing running Magento with MyISAM tables - in fact, many were arguing the performance benefits. I suppose this is a better question for that forum - but, does SQL care which format is used? Or, does the code care? In other words, is the database format entirely transparent to the script itself? In the end it is just reading and writing data to tables, so I'm not clear on the importance of satisfying this particular prerequisite.

    Thanks.

    mrk
     
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Using the innodb_file_per_table setting will mitigate the likelihood of InnoDB corruption impacting any other users. If one user's table corrupts, it will impact that individual user's database. If InnoDB has other corruption such as log corruption, it could still cause MySQL to go down, so you aren't entirely getting rid of the risk of using InnoDB. Thankfully, log corruption can be resolved by moving the logs for them to recreate, and it should be a far lower risk overall.

    As for needing to do anything else to enable it, you only have to ensure it's not skipped, then if you do want to use innodb_file_per_table, follow the instructions at the guide I made. You shouldn't need to do anything else to make it available. I would not suggest making it default at all.

    I don't know about the benefits of using MyISAM over InnoDB for Magento. I've never used that application. There are performance benefits for certain types of tables for InnoDB and, obviously, MySQL must see InnoDB itself as a benefit or MySQL 5.5 wouldn't be making it the default table engine type.
     
  9. lbeachmike

    lbeachmike Well-Known Member

    Joined:
    Dec 27, 2001
    Messages:
    313
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Long Beach, NY
    cPanel Access Level:
    Root Administrator
    Thanks for all of the extremely helpful details.

    I am not at all interested in making this the default and not sure if it is disabled or not on my servers as of right now but will check that out.

    I'll check with Magneto to see if MyISAM can be supported.

    Was not aware MySQL 5.5 was seeking to go in a new direction with the default.

    Again, thanks for all of the extremely helpful and very timely information.

    You guys are the very best.

    Mike
     
  10. egillette

    egillette Well-Known Member

    Joined:
    Jan 5, 2010
    Messages:
    68
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Orlando, FL
    cPanel Access Level:
    DataCenter Provider
    Wow. . .this post was extremely helpful!

    I have to say the benefits of using InnoDB as opposed to MyISAM seem substantial.

    To me it makes sense to move to it, since my client who is using it, needs faster load times on READS not necessarily the writes which occur far less frequently.

    I'll keep you all posted and let you know how it turns out, as I'm converting him (he uses Magento), and another client who also gets a substantial amount of traffic (using WordPress) -- hopefully the performance benefits will outweigh the "costs" (I use that term loosely. . .)

    Oh! I should also mention that converting the tables was easy using MySQLFront (Windows front-end for MySQL databases). . .

    - Eric Gillette
     
    #10 egillette, Feb 1, 2011
    Last edited: Feb 1, 2011
  11. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    If the application requires features such as foreign key constraints or ACID compliance then the table type is very important. The MyISAM table type will allow the tables to be created with the features but the features will be ignored.
     
  12. stoney2222

    stoney2222 Registered

    Joined:
    Mar 11, 2011
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    I've done this set up... even added

    default-storage-engine=innodb
    default-table-type=innodb

    into the my.cnf file then restarted mysql by using:

    /etc/init.d/mysql start

    But, when i go into phpmyadmin, the innoDB option is nowhere to be found in storage types under operations for each specific table type. Not really sure where to go next, can somebody help me please?
     
  13. egillette

    egillette Well-Known Member

    Joined:
    Jan 5, 2010
    Messages:
    68
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Orlando, FL
    cPanel Access Level:
    DataCenter Provider
    Hey, I believe it's an option in PHPMyAdmin you have to turn it on PHPMyAdmin's config.

    Can you convert any of your existing tables from MyISAM to InnoDB format using the query:

    ALTER TABLE 'table_name' ENGINE=InnoDB;

    Try that and if it converts the table to InnoDB then your MySQL install supports it, and we can narrow it down to being an option within PHPMyAdmin that needs to be turned on. . .

    Let me know.
     
  14. stoney2222

    stoney2222 Registered

    Joined:
    Mar 11, 2011
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    Yea I had already tried that, turned out the my.cnf had a lot of white space, and I needed to scroll down, there were some other options related to innoDB that I had needed to uncomment. After uncommenting some more innoDB stuff (can't tell you what because I kind of trialed and errored it), it worked : ).

    Thanks for the reply though : ).
     
  15. naveednexus

    naveednexus Registered

    Joined:
    Sep 8, 2011
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    i am facing the same problem, how i will enable the InnDB from Phpmyadmin , i have checked the my.cnd file but it does not exist. then i checked the my.cnf file but it does not allow me to open it , it shows permission denied.

    kindly show me the way how it will check that InnDB support is enabled on server or not.
     
  16. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    You need root access to the server to modify /etc/my.cnf. If you lack root access then you will need to open a support ticket with your web host provider.
     
  17. egillette

    egillette Well-Known Member

    Joined:
    Jan 5, 2010
    Messages:
    68
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Orlando, FL
    cPanel Access Level:
    DataCenter Provider
    ** Are you accessing your my.cnf file as root on your system or are you at least using sudo??

    (i.e. sudo vi /etc/my.cnf or sudo nano/pico /etc/my.cnf)

    If not, that would explain the permission denied issue (you have to be root or su to root).

    Let me know. . .
     
  18. hostmedic

    hostmedic Well-Known Member

    Joined:
    Apr 30, 2003
    Messages:
    559
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Washington Court House, Ohio, United States
    cPanel Access Level:
    DataCenter Provider
    how to check if innodb is enabled:

    There are two ways to verify if innodb is supported - i will skip how to check via phpmyadmin...

    If you are in the command line already simply execute the following

    Code:
    mysql -u root -p 
    place in your root password

    Now Type in
    Code:
    show engines; 
    It should show something like this

    Notice under InnoDB - it shows YES in my example.

    This example shows it is off

    I hope that helps.
     
  19. hostmedic

    hostmedic Well-Known Member

    Joined:
    Apr 30, 2003
    Messages:
    559
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Washington Court House, Ohio, United States
    cPanel Access Level:
    DataCenter Provider
    If your innodb is still not working - but you have it enabled - this could also be due to a log file issue.

    FIRST STOP MYSQL BEFORE DOING THESE STEPS:

    Code:
    service mysql stop
    now go to the mysql directory

    Code:
    cd /var/lib/mysql
    
    MOVE THE ibdata1, ib_logfile0 and ib_logfile1 files to a .bak like this

    Code:
    mv ibdata1 ibdata1.bak
    mv ib_logfile0 ib_logfile0.bak 
    ib_logfile1 ib_logfile1.bak
    Now restart Mysql and be patient.
    Code:
    service mysql restart
    Now check for Innodb support as shown before:

    Code:
    mysql -u root -p 
    enter password

    Now at the prompt mysql> type

    Code:
    show engines;

    Two important disk-based resources managed by the InnoDB storage engine are its tablespace data files and its log files. If you specify no InnoDB configuration options (as is the case with most MySQL installs and the default on cPanel), MySQL creates an auto-extending 10MB data file named ibdata1 and two 5MB log files named ib_logfile0 and ib_logfile1 in the MySQL data directory

    Innodb can Fail hard if those files have become corrupt.

    Best wishes in your trouble shooting.
     
  20. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Actually, it's a lot easier than logging into MySQL command line:

    Code:
    mysqladmin var | grep have_innodb
    This would return:

    Code:
    root@host [/home/cpfoo4]# mysqladmin var | grep have_innodb
    | have_innodb                             | YES        |
    If it is disabled, it would return:

    Code:
    root@host [/home/cpfoo4]# mysqladmin var | grep have_innodb
    | have_innodb                             | DISABLED |
     
Loading...

Share This Page