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.

MySQL: Changing password and finding user permissions

Changing password can be done through the mysql command:

If you have no root password (which is how MySQL comes out of the box) then type the following:

shell> $MYSQLDIR/mysqladmin -u root password newpassword

now if you have a password and want to change it:

shell> $MYSQLDIR/mysqladmin -u root -p password newpassword
Enter password: oldpassword

There drawback to this technique is that your new password is saved in the shell history buffer in clear text which is undesirable but I have not found a better way yet.


Once you have changed the password and you want to see what users exists with what password you can see that by logging into the special "mysql" database as root:

shell> $MYSQLDIR/mysql -u root -p mysql
Enter Password:

Now when you can see all the tables in the special "mysql" database:

mysql> show tables;

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)

Look into the user table to see the list of interesting fields as there are a lot of them:

mysql>  describe user;
mysql> describe user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     |         |       |
| x509_issuer           | blob                              | NO   |     |         |       |
| x509_subject          | blob                              | NO   |     |         |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.05 sec)

Here is a query that would tell you user have permissions to certain operations on your database from different host:

mysql> select host, user, select_priv, insert_priv, delete_priv, create_priv, drop_priv, alter_priv from user;
+----------------+------+-------------+-------------+-------------+-------------+-----------+------------+
| host           | user | select_priv | insert_priv | delete_priv | create_priv | drop_priv | alter_priv |
+----------------+------+-------------+-------------+-------------+-------------+-----------+------------+
| localhost      | root | Y           | Y           | Y           | Y           | Y         | Y          |
| AliPower.local | root | Y           | Y           | Y           | Y           | Y         | Y          |
| AliPower.local |      | N           | N           | N           | N           | N         | N          |
| localhost      |      | N           | N           | N           | N           | N         | N          |
+----------------+------+-------------+-------------+-------------+-------------+-----------+------------+
4 rows in set (0.00 sec)

It seems the blank user name is there to allow to run mysql shell without a user.

Results of MySQL sql-bench run-all-tests

AliPower:/usr/local/mysql/sql-bench rizvi$ sudo perl run-all-tests
Benchmark DBD suite: 2.15
Date of test: 2005-11-19 1:04:10
Running tests on: Darwin 8.2.0 Power Macintosh
Arguments:
Comments:
Limits from:
Server version: MySQL 5.0.15 standard log
Optimization: None
Hardware:

alter-table: Total time: 49 wallclock secs ( 0.14 usr 0.05 sys + 0.00 cusr 0.00 csys = 0.19 CPU)
ATIS: Total time: 99 wallclock secs (22.24 usr 1.19 sys + 0.00 cusr 0.00 csys = 23.43 CPU)
big-tables: Total time: 112 wallclock secs (13.17 usr 2.98 sys + 0.00 cusr 0.00 csys = 16.15 CPU)
connect: Total time: 511 wallclock secs (93.54 usr 40.86 sys + 0.00 cusr 0.00 csys = 134.40 CPU)
create: Total time: 302 wallclock secs (12.95 usr 3.32 sys + 0.00 cusr 0.00 csys = 16.27 CPU)
insert: ^A^A^H^HTotal time: 2860 wallclock secs (706.16 usr 90.47 sys + 0.00 cusr 0.00 csys = 796.63 CPU)
select: Total time: 1554 wallclock secs (94.04 usr 8.91 sys + 0.00 cusr 0.00 csys = 102.95 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time: 20 wallclock secs ( 4.09 usr 0.96 sys + 0.00 cusr 0.00 csys = 5.05 CPU)

All 9 test executed successfully

Totals per operation:
Operation seconds usr sys cpu tests
alter_table_add 21.00 0.03 0.01 0.04 100
alter_table_drop 20.00 0.03 0.01 0.04 91
connect 52.00 13.47 3.87 17.34 10000
connect+select_1_row 72.00 15.01 4.48 19.49 10000
connect+select_simple 57.00 14.28 4.31 18.59 10000
count 42.00 0.07 0.00 0.07 100
count_distinct 35.00 0.69 0.10 0.79 1000
count_distinct_2 37.00 0.71 0.10 0.81 1000
count_distinct_big 56.00 10.01 0.23 10.24 120
count_distinct_group 37.00 2.63 0.13 2.76 1000
count_distinct_group_on_key 45.00 0.78 0.09 0.87 1000
count_distinct_group_on_key_parts 38.00 2.63 0.12 2.75 1000
count_distinct_key_prefix 29.00 0.62 0.08 0.70 1000
count_group_on_key_parts 27.00 1.42 0.08 1.50 1000
count_on_key 437.00 23.66 3.19 26.85 50100
create+drop 57.00 3.73 0.89 4.62 10000
create_MANY_tables 80.00 2.73 0.61 3.34 10000
create_index 4.00 0.00 0.00 0.00 8
create_key+drop 73.00 3.92 0.95 4.87 10000
create_table 0.00 0.01 0.01 0.02 31
delete_all_many_keys 77.00 0.02 0.00 0.02 1
delete_big 0.00 0.00 0.00 0.00 1
delete_big_many_keys 77.00 0.01 0.00 0.01 128
delete_key 4.00 0.56 0.27 0.83 10000
delete_range 17.00 0.00 0.00 0.00 12
drop_index 4.00 0.01 0.00 0.01 8
drop_table 0.00 0.00 0.00 0.00 28
drop_table_when_MANY_tables 34.00 1.17 0.39 1.56 10000
insert 139.00 16.62 7.40 24.02 350768
insert_duplicates 30.00 4.33 1.86 6.19 100000
insert_key 150.00 14.83 4.42 19.25 100000
insert_many_fields 41.00 0.76 0.18 0.94 2000
insert_select_1_key 7.00 0.00 0.00 0.00 1
insert_select_2_keys 10.00 0.00 0.00 0.00 1
min_max 26.00 0.04 0.01 0.05 60
min_max_on_key 47.00 21.64 2.93 24.57 85000
multiple_value_insert 7.00 0.63 0.02 0.65 100000
once_prepared_select 54.00 12.57 2.67 15.24 100000
order_by_big 52.00 29.66 0.76 30.42 10
order_by_big_key 54.00 29.68 0.89 30.57 10
order_by_big_key2 49.00 29.25 0.75 30.00 10
order_by_big_key_desc 56.00 29.65 0.92 30.57 10
order_by_big_key_diff 56.00 29.61 0.77 30.38 10
order_by_big_key_prefix 47.00 28.92 0.68 29.60 10
order_by_key2_diff 7.00 2.69 0.08 2.77 500
order_by_key_prefix 4.00 1.46 0.06 1.52 500
order_by_range 5.00 1.47 0.06 1.53 500
outer_join 91.00 0.01 0.00 0.01 10
outer_join_found 86.00 0.00 0.00 0.00 10
outer_join_not_found 85.00 0.01 0.00 0.01 500
outer_join_on_key 68.00 0.01 0.00 0.01 10
prepared_select 89.00 28.69 4.03 32.72 100000
select_1_row 52.00 8.10 3.33 11.43 100000
select_1_row_cache 54.00 7.51 3.19 10.70 100000
select_2_rows 92.00 9.87 3.82 13.69 100000
select_big 46.00 29.48 0.69 30.17 80
select_big_str 36.00 8.18 10.00 18.18 10000
select_cache 296.00 6.03 0.78 6.81 10000
select_cache2 297.00 6.13 0.78 6.91 10000
select_column+column 30.00 5.07 2.16 7.23 100000
select_diff_key 1.00 0.20 0.03 0.23 500
select_distinct 25.00 4.26 0.17 4.43 800
select_group 99.00 3.19 0.25 3.44 2911
select_group_when_MANY_tables 58.00 1.39 0.48 1.87 10000
select_join 4.00 1.25 0.06 1.31 100
select_key 161.00 60.41 8.83 69.24 200000
select_key2 160.00 60.61 8.63 69.24 200000
select_key2_return_key 141.00 56.93 8.14 65.07 200000
select_key2_return_prim 151.00 59.07 8.41 67.48 200000
select_key_prefix 182.00 62.99 9.28 72.27 200000
select_key_prefix_join 25.00 11.19 0.28 11.47 100
select_key_return_key 146.00 56.97 8.30 65.27 200000
select_many_fields 71.00 12.41 2.79 15.20 2000
select_range 264.00 20.34 0.64 20.98 410
select_range_key2 26.00 6.93 0.74 7.67 25010
select_range_prefix 28.00 7.25 0.78 8.03 25010
select_simple 32.00 6.16 2.86 9.02 100000
select_simple_cache 34.00 5.89 2.83 8.72 100000
select_simple_join 5.00 1.50 0.07 1.57 500
update_big 23.00 0.00 0.00 0.00 10
update_of_key 38.00 3.58 1.53 5.11 50000
update_of_key_big 36.00 0.07 0.03 0.10 501
update_of_primary_key_many_keys 38.00 0.04 0.02 0.06 256
update_with_key 100.00 13.41 6.40 19.81 300000
update_with_key_prefix 41.00 8.91 3.00 11.91 100000
wisc_benchmark 5.00 2.44 0.22 2.66 114
TOTALS 5489.00 928.49 147.93 1076.42 3425950
AliPower:/usr/local/mysql/sql-bench rizvi$

MySQL Post Installation

Here is the command again for running mysql server:
sudo /usr/local/mysql/bin/mysqld_safe --user=mysql &

The documentation would tell you to change the root password immediately but wait until you are through the section about installing Perl support (DBI and DBD::mysql) and run the MYSQLDIR /sql-bench perl run-all-tests.

Both the CPAN installation and run-all-tests expect a blank password for root user.

If you have already changed your password you have two options:
1. force install DBD::mysql (on perl -MCPAN -e shell) OR
2. change your root password back to blank using :-> mysqladmin -u root -p password ''

The test under sql-bench are very processor intensive and take some time so be patient with them. (you might need to do sudo perl run-all-tests).

Setting up MySQL

It seems like a trivial thing but I had never installed or administered my own database so MySQL was definitely a first for me.

The motivation was to start developing my first Rails app on my powerbook.

I will add some random notes here and clean them up later:

I installed MySQL 5.0 from binary installation for Mac OS X.

MySQL 5.0 HTML Documentation was helpful. I also downloaded the pdf version.

Installed directory: /user/local/mysql called MYSQLDIR hereon

Copied MYSQLDIR/support-files/my-medium.cnf to /etc/my.cnf

added these two lines in my ~/.profile

alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin

AliPower:/usr/local/mysql rizvi$ sudo scripts/mysql_install_db --user=mysql
Password:
Installing all prepared tables
051119 0:25:47 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql-standard-5.0.15-osx10.4-powerpc/data/ is case insensitive
Fill help tables
051119 0:25:47 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql-standard-5.0.15-osx10.4-powerpc/data/ is case insensitive

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h AliPower.local password 'new-password'
See the manual for more instructions.

NOTE: If you are upgrading from a MySQL <= 3.22.10 you should run
the ./bin/mysql_fix_privilege_tables. Otherwise you will not be
able to use the new GRANT command!

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; perl run-all-tests

Please report any problems with the ./bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com
AliPower:/usr/local/mysql rizvi$