Saari Development

Ali Rizvi's Technical Blog as a Professional Software Development Engineer

MySQL: Creating InnoDB tables, converting tables and verifying

with 7 comments

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.

Advertisements

Written by imsaar

November 20, 2005 at 8:37 am

Posted in general

7 Responses

Subscribe to comments with RSS.

  1. Hi Ali. I’ve been looking for fax software information and came across your site. I was really after fax software related info but I came across your site and found it a good posting even though MySQL: Creating InnoDB tables, converting tables and verifying was’nt exactly what I was after. Thanks for the read

    Dave

    November 23, 2005 at 1:26 pm

  2. Good Day Ali.I am searching for information on small business software when I came across your site. Although MySQL: Creating InnoDB tables, converting tables and verifying was’nt exactly what I was looking for it got my attention and made an interesting read. I was really after information relating to small business software. However I’m glad I stopped by – thanks for the read.

    Dave

    November 24, 2005 at 5:38 pm

  3. Hi there Ali. I am looking for recent information on free software and found your site. It makes an interesting read. I see that I found it when looking for free software related information and I’m glad I stopped by and had a read – thanks for taking the time to put the info together – great post = have a nice day.

    Dave

    November 25, 2005 at 2:03 pm

  4. Hello Ali. I was looking for some information on spy software and came across your site. Not really what I was after but I found it very interesting. I was looking for spy software related information. Glad I found your site even though it was not exactly what I was after. Keep up the good posting – thanks

    Dave

    November 26, 2005 at 8:39 pm

  5. Hi there Ali. I am looking for recent information on buy and download software and found your site. It makes an interesting read. I see that I found it when looking for buy and download software related information and I’m glad I stopped by and had a read – thanks for taking the time to put the info together – great post = have a nice day.

    Dave

    November 28, 2005 at 7:50 pm

  6. Hi there Ali. I am looking for recent information on software downloads and found your site. It makes an interesting read. I see that I found it when looking for software downloads related information and I’m glad I stopped by and had a read – thanks for taking the time to put the info together – great post = have a nice day.

    Dave

    November 29, 2005 at 5:45 pm

  7. Good Day Ali.I am searching for information on when I came across your site. Although MySQL: Creating InnoDB tables, converting tables and verifying was’nt exactly what I was looking for it got my attention and made an interesting read. I was really after information relating to . However I’m glad I stopped by – thanks for the read.

    Dave

    November 30, 2005 at 5:09 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: