Recently I upgraded the MySQL server and I enabled InnoDB and then I was not able to start the MySQL Server.

The next step was to check the mysql logs and I saw the error:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5256780 bytes
InnoDB: than specified in the .cnf file 0 1077645824 bytes!
[ERROR] Plugin ‘InnoDB’ init function returned error.
[ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting

The solution for this error is:

Remove the ib_logfile0 and ib_logfile1 files located in /var/lib/mysql.

rm /var/lib/mysql/ib_logfile0
rm /var/lib/mysql/ib_logfile1 Continue Reading...

I just enabled InnoDB on my  MySQL server and I’ve made the necessary changes into my.cnf. When I restarted the MySQL server I got the following error in the log:

InnoDB: The InnoDB memory heap is disabled

After  some checking I’ve found that I need to set the value of innodb_use_sys_malloc to 0. After I added  this entry innodb_use_sys_malloc =  0 into the my.cnf and restarted the MySQL Server the error was gone.

NOTE: More info here: www.innodb.com/doc/innodb_plugin-1.0-doc-A4.pdf (see page 38)

There are many discussions about this topic and an endless debate between which database engine to choose InnoDB or MyISAM.

Both options are practical and efficient  the oldest and the default used is usually MyISAM  and in most cases we can change our engine when we want or need. Now I come with this question:

What engine is more efficient?

MyISAM:

  • The structure of the tables in MyISAM are simpler and is most recommended for users.
  • MyISAM allows table locking.
  • With MyISAM you get high speed on SELECT because they don’t have integrity checks.

InnoDB:

  • Support for transaction type ACID (Atomicity, Consistency, Isolation and Durability) that guarantees the integrity of the tables.
  • Record locking.
  • Recovery of unexpected problems through the transaction log.

Continue Reading…

This job can be done in many ways like dumping all databases and replacing myisam with innodb in all created tables. However this solution can take very long time.

The simplest way is to use maatkit package. I will show you how to convert your MySQL databases from MyISAM to InnoDB using maatkit:

Step1. Install maatkit package:

apt-get install maatkit

Step2. Now we need to find a list with all your databases. You can do that using the following command:

mysql -u root --password=your_password --skip-column-names -e "show databases;" > databases.list

Step3. Now convert all your MySQL databases from MyISAM to InnoDB

for databases in `cat databases.list`; do /usr/bin/mk-find $databases --engine MyISAM --exec "ALTER TABLE %D.%N ENGINE=INNODB" --print; done

This command will extract all database names from databases.list file and send that name to mk-find  which is executing ”ALTER TABLE” command on all of its tables to convert their storage engine from MyISAM to InnoDB.

Enjoy!

Page 1 of 11