MySQL 5.5 and transaction management

Announced at MySQL Sunday was the Release Candidate edition of MySQL 5.5.6. Also noted by Geert where he points out the default storage engine is now InnoDB.

However, for those from a background other then MySQL there is still a gotcha.

mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

Unlike Oracle for example, the default autocommit is on.

Doing some other boundary conditions, it is no longer possible to disable InnoDB on startup which you would of course expect.

$ bin/mysqld_safe --skip-innodb &
$ tail error.log

101003 15:33:32 [Note] Plugin 'InnoDB' is disabled.
101003 15:33:32 [ERROR] Unknown/unsupported storage engine: InnoDB
101003 15:33:32 [ERROR] Aborting

MyISAM however can’t be removed and can’t be disabled. This is a question I’ve been asked by Oracle resources.

101003 15:34:55 [ERROR] /Users/rbradfor/mysql/mysql-5.5.6-rc-osx10.5-x86_64/bin/mysqld: ambiguous option '--skip-myisam' (--skip-myisam-block-size)
101003 15:34:55 [ERROR] Parsing options for plugin 'MyISAM' failed.
101003 15:34:55 [ERROR] Failed to initialize plugins.
101003 15:34:55 [ERROR] Aborting
mysql> select table_schema, table_name from information_schema.tables where engine='myisam';
+--------------------+---------------------------+
| table_schema       | table_name                |
+--------------------+---------------------------+
| information_schema | COLUMNS                   |
| information_schema | EVENTS                    |
| information_schema | PARAMETERS                |
| information_schema | PARTITIONS                |
| information_schema | PLUGINS                   |
| information_schema | PROCESSLIST               |
| information_schema | ROUTINES                  |
| information_schema | TRIGGERS                  |
| information_schema | VIEWS                     |
| mysql              | columns_priv              |
| mysql              | db                        |
| mysql              | event                     |
| mysql              | func                      |
| mysql              | help_category             |
| mysql              | help_keyword              |
| mysql              | help_relation             |
| mysql              | help_topic                |
| mysql              | host                      |
| mysql              | ndb_binlog_index          |
| mysql              | plugin                    |
| mysql              | proc                      |
| mysql              | procs_priv                |
| mysql              | servers                   |
| mysql              | tables_priv               |
| mysql              | time_zone                 |
| mysql              | time_zone_leap_second     |
| mysql              | time_zone_name            |
| mysql              | time_zone_transition      |
| mysql              | time_zone_transition_type |
| mysql              | user                      |
+--------------------+---------------------------+
30 rows in set (0.06 sec)

Tags: ,

4 Responses to “MySQL 5.5 and transaction management”

  1. Even with innodb being used as the default and only engine MyISAM is still used for some internal operations like temporary tables and such so at this point its not possible to disable it all together as you have found out.

  2. Sergei Golubchik says:

    Eh, I think InnoDB still can be disabled – unlike MyISAM it is not needed for internal server operation. See, for MyISAM the option –skip-myisam is not even recognized by the server (and not shown in –help). On the other hand, –skip-innodb is recognized – that is InnoDB can be disabled, as expected.

    You get “Unknown/unsupported storage engine: InnoDB” error, which probably means that MySQL cannot set the default storage engine. But it can be trivially fixed with –default-storage-engine=myisam after which –skip-innodb should perfectly work too. Although I didn’t try it myself :)

  3. You can still disable InnoDB, but you need to tell the server what the other default engine is:
    ./msyqld –skip-innodb –default-storage-engine=myisam

  4. Mark R says:

    The default storage engine for permanent user tables is InnoDB, but I think the MyISAM core bits are still there – system tables have to be MyISAM and so do internally-created temporary tables.

    User temporary tables can use whatever engine you want of course.