How to enable Innodb storage engine ?

What is a storage Engine?

Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employ different storage mechanisms, indexing facilities, locking levels and ultimately provide a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.

You can configure the mysql database to use Innodb Storage engine . Just follow following simple steps for this.

1) Open your file /etc/my.cnf.

2) Comment the line

skip-inndob

3)Add:

default-table-type=innodb

4)Now save the file and restart the mysql service on the server.

5) Now check if the engine has changed with the command:

[root@server~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.0.92-community MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show engines;

+————+———+—————————————————————-+

| Engine | Support | Comment |

+————+———+—————————————————————-+

| MyISAM | YES | Default engine as of MySQL 3.23 with great performance |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys |

| BerkeleyDB | NO | Supports transactions and page-level locking |

This should now change the storage engine for mysql on the server

Converting table to InnoDB

Converting table to InnoDB

Converting table to InnoDB: “The used table type doesn’t support FULLTEXT indexes”

Somtimes you may want to change the Mysql table from MyISAM to InnoDB engine to setup foreign keys, to use row level locks, improve performace etc.

The conversion of the MyISAM table to InnoDB is easy however, if the table is setup with “FULLTEXT indexes”, it cannot be converted as this feature is not supported in InnoDB.

If a table is setup with “FULLTEXT indexes”, the conversion of table to InnoDB will result in “The used table type doesn’t support FULLTEXT indexes” error message.

mysql> ALTER TABLE test ENGINE=InnoDB;

ERROR 1214: The used table type doesn’t support FULLTEXT indexes

The solution is to remove “FULLTEXT indexes” from the table before converting to InnoDB. To check if the table is setup with FULLTEXT indexes, execute:

mysql> show create table test;

————————

| Table | Create Table

————————

| test | CREATE TABLE `test` (

`col_name` varchar(10) DEFAULT NULL,

FULLTEXT KEY `keyname` (`col_name`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

If FULLTEXT is setup, the output of the above command will display a line as follows:

FULLTEXT KEY `keyname` (`col_name`)

Now, remove “FULLTEXT” indexes from the table:

mysql> ALTER TABLE test DROP INDEX keyname;

Now, this table can be converted to InnoDB using the following command:

mysql> ALTER TABLE test ENGINE=InnoDB;

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0