navigation bar top
navigation bar logonavigation item HOMEnavigation item SOFTWAREnavigation item ABOUT MEnavigation item PICTURESnavigation item FUNnavigation item LINKSnavigation bar tail
navigation bar bottomnavigation bar highlight line sectionnavigation bar highlight line sectionnavigation bar highlight line sectionnavigation bar highlight line sectionnavigation bar highlight line sectionnavigation bar highlight line sectionnavigation bar highlight line tail
Software > Mac OS X Packages > MySQL > InnoDB Configuration

Using InnoDB Tables

Introduction

What are InnoDB tables? From the MySQL Manual:

InnoDB provides MySQL with a transaction-safe table handler with commit, rollback, and crash recovery capabilities. InnoDB does locking on row level and also provides an Oracle-style consistent non-locking read in SELECTs. These features increase multiuser concurrency and performance. There is no need for lock escalation in InnoDB, because row level locks in InnoDB fit in very small space. InnoDB tables support FOREIGN KEY constraints as the first table type in MySQL.

Configuration

To enable InnoDB tables, you have to create a configuration file for the MySQL database server.

The following is a minimal file that will enable the InnoDB tables. Note that it's just that, minimal and not optimized at all. If you're going to use this for anything other than test purposes, be sure to look at the manual and fine-tune all the InnoDB parameters.

# mysqld configuration file for InnoDB tables
#
[mysqld]
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = innodb1:50M;innodb2:50M

You have to create a file called my.cnf in the directory /usr/local/mysql/data/ with these lines in it. Then you have to re-start the database server, either by rebooting the machine or killing the mysqld processes and starting them again afterwards.

If the server does not come up again, there might be a problem with the new configuration file. Try to remove it and start the server without it. Also look at the .err files in the /usr/local/mysql/data/ directory for error messages.

Creating Tables

Once the server is running, you can create InnoDB tables. Assuming we entered the test database with the command mysql test in the shell, try these statements at the mysql> prompt:

mysql> CREATE TABLE Blah (id INT4, name TEXT) TYPE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

Now we can use some of the cool features, like transactions:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Blah VALUES (1, 'Marc');
Query OK, 1 rows affected (0.00 sec)

mysql> SELECT * FROM Blah;
+------+------+
| id   | name |
+------+------+
|    1 | Marc |
+------+------+
1 row in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM Blah;
Empty set (0.00 sec)

You can see that the effects of the INSERT statement were rolled back. If you had tried this on a non-InnoDB table, you would get an error message like this:

ERROR 1196: Warning: Some non-transactional changed tables couldn't be rolled back

Additional Information

21. March 2005