innoDB not enabled by default?

[kazar]

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

 

[cPanelKenneth]

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

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:

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:

default-table-type=innodb

InnoDB out performs MyISAM, once properly configured, but comes with a slightly higher cost of administrator overhead.

 

[kazar]

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

 

[Ashioni]

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.cnd for an entry like:

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:

default-table-type=innodb

InnoDB out performs MyISAM, once properly configured, but comes with a slightly higher cost of administrator overhead.

This was very helpful.

 

[lbeachmike]

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

 

[cPanelTristan]

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

 

[lbeachmike]

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

 

[cPanelTristan]

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.

 

[lbeachmike]

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

 

[egillette]

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

 

[cPanelKenneth]

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.

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.

 

[stoney2222]

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?

 

[egillette]

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.

 

[stoney2222]

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

 

[naveednexus]

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.

 

[cPanelKenneth]

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.

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.

 

[egillette]

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.

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

 

[hostmedic]

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

mysql -u root -p

place in your root password

Now Type in

show engines;

It should show something like this

mysql> show engines;
+------------+---------+----------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+----------------------------------------------------------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | NO | Supports transactions and page-level locking |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | YES | Example storage engine |
| ARCHIVE | YES | Archive storage engine |
| CSV | YES | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | YES | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| ISAM | NO | Obsolete storage engine |
+------------+---------+----------------------------------------------------------------+

Notice under InnoDB - it shows YES in my example.

This example shows it is off

mysql> show engines;
+------------+----------+----------------------------------------------------------------+
| Engine | Support | Comment |
+------------+----------+----------------------------------------------------------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | NO | Supports transactions and page-level locking |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | YES | Example storage engine |
| ARCHIVE | YES | Archive storage engine |
| CSV | YES | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | YES | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| ISAM | NO | Obsolete storage engine |
+------------+----------+----------------------------------------------------------------+

I hope that helps.

 

[hostmedic]

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:

service mysql stop

now go to the mysql directory

cd /var/lib/mysql

MOVE THE ibdata1, ib_logfile0 and ib_logfile1 files to a .bak like this

mv ibdata1 ibdata1.bak
mv ib_logfile0 ib_logfile0.bak 
ib_logfile1 ib_logfile1.bak

Now restart Mysql and be patient.

service mysql restart

Now check for Innodb support as shown before:

mysql -u root -p

enter password

Now at the prompt mysql> type

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.

 

[cPanelTristan]

Actually, it's a lot easier than logging into MySQL command line:

mysqladmin var | grep have_innodb

This would return:

[email protected] [/home/cpfoo4]# mysqladmin var | grep have_innodb
| have_innodb                             | YES        |

If it is disabled, it would return:

[email protected] [/home/cpfoo4]# mysqladmin var | grep have_innodb
| have_innodb                             | DISABLED |