Tags: innodb

Getting Innodb to work

By admin on Jun 19, 2008 | In Formula 1 - More advanced stuff, Highway Maintenance - DBA stuff | Send feedback »

In order to enforce referential integrity and set up foreign keys, you need to convert your tables to using the Innodb storage engine (or create them using this to begin with on the storage engine). The syntax to convert a table to this storage is very straightforward:

ALTER TABLE orders ENGINE=INNODB;

However when I tried to do this, warnings were created. When I used "SHOW WARNINGS;" Mysql informed me that the tables were still using the MyISAM engine. Also, when I used the SHOW ENGINES command, I found out that the Innodb engine was disabled.

In order to enable Innodb (which is often disabled by default on Apache set-ups) the standard advice is to edit my.cnf file. The first part is to Comment out the skip-innodb line by using #skip-innodb
Also it's supposed to help by making sure the following lines are present:

Code:

innodb_data_home_dir = /opt/lampp/var/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/lampp/var/mysql/
innodb_log_arch_dir = /opt/lampp/var/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

However there is often more than one instance of this file my.cnf. Mine were in the directories /etc/mysql/ (often its in the /etc directory) and also in /var/lib/mysql/ Be warned - its exact location can differ on different set-ups so be sure to find the global version and the local one. Read this for further guidance:
mysql manual

Do this, restart your Apache and Mysql server, and you should be able to proceed.

However in my case, on looking at SHOW ENGINES, it seemed as though Innodb was still disabled. After much head-scratching and internet surfing I found out that if you have a zombie mysql process in the background, then Mysql never fully gets restarted. I should have realised this from the warning message "Another MySQL daemon is already running" I kept receiving. You can detect this on Unix based systems by performing this command:
ps -ef | grep mysql

Luckily, the problem was diagnosed here:
forum link

The solution turned out to be fairly straight-forward - Kill the zombie process, and then restart Apache and Mysql. You also have to put the correct path to your mysql.sock in your my.cnf files (see link again).

After doing all that I was successful - I can now convert tables to the Innodb engine.

Tags: innodb, mysql daemon, storage engine