MySQL: Creating InnoDB tables, converting tables and verifying

InnoDB tables support transactions and foriegn keys and are widely used in large MySQL enviroments like slashdot.

I wanted to be able to create innodb tables so I read the relevant section of the manual on innodb

My /etc/my.cnf file (that I copied from $MYSQLDIR/support-files/my-medium.cnf) already had the innodb configuration commented I just uncommented the following lines:

    119 # Uncomment the following if you are using InnoDB tables
    120 # rizvi - uncommented 2005-11-19
    121 innodb_data_home_dir = /usr/local/mysql/data/
    122 innodb_data_file_path = ibdata1:10M:autoextend
    123 innodb_log_group_home_dir = /usr/local/mysql/data/
    124 innodb_log_arch_dir = /usr/local/mysql/data/
    125 # You can set .._buffer_pool_size up to 50 - 80 %
    126 # of RAM but beware of setting memory usage too high
    127 innodb_buffer_pool_size = 16M
    128 innodb_additional_mem_pool_size = 2M
    129 # Set .._log_file_size to 25 % of buffer pool size
    130 innodb_log_file_size = 5M
    131 innodb_log_buffer_size = 8M
    132 innodb_flush_log_at_trx_commit = 1
    133 innodb_lock_wait_timeout = 50

I created one table to test it out using the example given in the manual:

CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;

It seems using TYPE instead of ENGINE is allowed but is effectively deprecated and not the recommended way.

It seems it is fairly easy to convert an existing table to innodb (or any other supported engines) here is the example from the manual:

ALTER TABLE t ENGINE = INNODB;

Now that I had created a table I wanted to verify that it was created properly as a INNODB tables instead of the default MyISAM. I tried this:

mysql> show full tables;
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| customers      | BASE TABLE |
| ino_test       | BASE TABLE |
| testaa         | BASE TABLE |
| testad         | BASE TABLE |
+----------------+------------+
4 rows in set (0.00 sec)

It was strange and misleading as the two pre-existing tables starting with test (MyISAM) were shown as the same type as the new customer INNODB tables. I thought may be I missed a config thing and I made sure I had restared mysqld after the config change.

Finally I figured out the right command to see detailed table information:

mysql> show table status;
+-----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+----------------------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment              |
+-----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+----------------------+
| customers | InnoDB |      10 | Compact    |    1 |          16384 |       16384 |                 0 |        16384 |         0 |           NULL | 2005-11-19 23:15:51 | NULL                | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 4096 kB |
| ino_test  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |                 0 |            0 |         0 |           NULL | 2005-11-20 00:34:18 | NULL                | NULL       | latin1_swedish_ci |     NULL |                | InnoDB free: 4096 kB |
| testaa    | MyISAM |      10 | Fixed      |    7 |             69 |         483 | 19421773393035263 |         1024 |         0 |           NULL | 2005-11-19 01:01:54 | 2005-11-19 01:01:54 | NULL       | latin1_swedish_ci |     NULL |                |                      |
| testad    | MyISAM |      10 | Dynamic    |    1 |             20 |          20 |   281474976710655 |         1024 |         0 |           NULL | 2005-11-19 01:02:02 | 2005-11-19 01:02:06 | NULL       | latin1_swedish_ci |     NULL |                |                      |
+-----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+----------------------+
4 rows in set (0.11 sec)

That was gratifying to see the two tables I created were showing up rightly as INNODB tables.

Next up is experimenting with the transactions with INNODB tables.