Interesting actions on MySQL’s storage engines :)

By | March 21, 2016

Did you ever think about altering MyISAM table to use Innodb tablespace?
Well that’s weird but you can run those alter statement without any error:

Sample table:

mysql> show create table sbtest1;

| sbtest1 | CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=685441 DEFAULT CHARSET=utf8 MAX_ROWS=1000000 |

Alter engine:

mysql> alter table sbtest1 engine=myisam;
Query OK, 685440 rows affected (19.06 sec)
Records: 685440 Duplicates: 0 Warnings: 0

Use encryption with MyISAM 😛

mysql> alter table sbtest1 encryption='y';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Use page compression:

mysql> alter table sbtest1 compression='zlib';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Use Innodb tablespace file:

mysql> alter table sbtest1 tablespace=innodb_file_per_table;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

Well in fact it is not going to use these features for sure. It is just kind of fun.

Related BUG report -> http://bugs.mysql.com/bug.php?id=80731

Well, you can even create a MEMORY table with encryption and compression enabled:

CREATE TABLE `sbtest2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) /*!50100 TABLESPACE `s1` */ ENGINE=MEMORY DEFAULT CHARSET=utf8 MAX_ROWS=1000000 COMPRESSION='zlib' ENCRYPTION='y';

If you try to convert this table to InnoDB:

mysql> alter table sbtest2 engine=innodb;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Lovely SIGSEGV 🙂


Program terminated with signal SIGSEGV, Segmentation fault.
#0 0x00007fb1d67ea621 in __pthread_kill (threadid=, signo=11) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:61
61 ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c: No such file or directory.
(gdb) bt
#0 0x00007fb1d67ea621 in __pthread_kill (threadid=, signo=11) at ../nptl/sysdeps/unix/sysv/linux/pthread_kill.c:61
#1 0x000000000186b1d9 in my_write_core (sig=11) at /opt/mysql-5.7.11/mysys/stacktrace.c:247
#2 0x0000000000e6bb52 in handle_fatal_signal (sig=11) at /opt/mysql-5.7.11/sql/signal_handler.cc:220
#3
#4 0x00000000018f2fa7 in ut_allocator::deallocate_trace (this=0x7fb1936eed90, pfx=0x4d1ffffffe8) at /opt/mysql-5.7.11/storage/innobase/include/ut0new.h:726
#5 0x00000000018f0e4c in ut_allocator::deallocate (this=0x7fb1936eed90, ptr=0x4d200000000 , n_elements=0)
at /opt/mysql-5.7.11/storage/innobase/include/ut0new.h:402
#6 0x0000000001bf6cef in dict_mem_table_free (table=0x7fb148921890) at /opt/mysql-5.7.11/storage/innobase/dict/dict0mem.cc:212
#7 0x00000000018ef159 in create_table_info_t::create_table_def (this=0x7fb1936ef2e0) at /opt/mysql-5.7.11/storage/innobase/handler/ha_innodb.cc:10044
#8 0x00000000018db93a in create_table_info_t::create_table (this=0x7fb1936ef2e0) at /opt/mysql-5.7.11/storage/innobase/handler/ha_innodb.cc:11567
#9 0x00000000018dc534 in ha_innobase::create (this=0x7fb148920840, name=0x7fb1936f2174 "./test/#sql-12bf_2", form=0x7fb1936efad0, create_info=0x7fb1936f24f0)
at /opt/mysql-5.7.11/storage/innobase/handler/ha_innodb.cc:11917
#10 0x0000000000ef1c9e in handler::ha_create (this=0x7fb148920840, name=0x7fb1936f2174 "./test/#sql-12bf_2", form=0x7fb1936efad0, info=0x7fb1936f24f0)
at /opt/mysql-5.7.11/sql/handler.cc:4819
#11 0x0000000000ef22b9 in ha_create_table (thd=0x7fb148000b70, path=0x7fb1936f2174 "./test/#sql-12bf_2", db=0x7fb148004a10 "test", table_name=0x7fb1936f1920 "#sql-12bf_2",
create_info=0x7fb1936f24f0, update_create_info=false, is_temp_table=false) at /opt/mysql-5.7.11/sql/handler.cc:4978
#12 0x00000000015a8374 in mysql_alter_table (thd=0x7fb148000b70, new_db=0x7fb148004a10 "test", new_name=0x0, create_info=0x7fb1936f24f0, table_list=0x7fb14891d820,
alter_info=0x7fb1936f2440) at /opt/mysql-5.7.11/sql/sql_table.cc:9572
#13 0x00000000017300e7 in Sql_cmd_alter_table::execute (this=0x7fb14800e5b0, thd=0x7fb148000b70) at /opt/mysql-5.7.11/sql/sql_alter.cc:316
#14 0x000000000150f2f5 in mysql_execute_command (thd=0x7fb148000b70, first_level=true) at /opt/mysql-5.7.11/sql/sql_parse.cc:4806
#15 0x00000000015112e2 in mysql_parse (thd=0x7fb148000b70, parser_state=0x7fb1936f41d0) at /opt/mysql-5.7.11/sql/sql_parse.cc:5518
#16 0x0000000001506394 in dispatch_command (thd=0x7fb148000b70, com_data=0x7fb1936f4dd0, command=COM_QUERY) at /opt/mysql-5.7.11/sql/sql_parse.cc:1428
#17 0x00000000015052ed in do_command (thd=0x7fb148000b70) at /opt/mysql-5.7.11/sql/sql_parse.cc:996
#18 0x00000000016439af in handle_connection (arg=0x48dd210) at /opt/mysql-5.7.11/sql/conn_handler/connection_handler_per_thread.cc:301
#19 0x0000000001cc0a3f in pfs_spawn_thread (arg=0x4b30a30) at /opt/mysql-5.7.11/storage/perfschema/pfs.cc:2192
#20 0x00007fb1d67e5182 in start_thread (arg=0x7fb1936f5700) at pthread_create.c:312
#21 0x00007fb1d5cf247d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

Also see related BUG report -> http://bugs.mysql.com/bug.php?id=80745

Same thing is here for MyISAM:

CREATE TABLE `sbtest2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) /*!50100 TABLESPACE `s1` */ ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000 COMPRESSION='zlib'

But unlike with MEMORY there is no segmentation fault:

mysql> alter table sbtest2 engine=innodb;
ERROR 1112 (42000): Table '#sql-45d7_2' uses an extension that doesn't exist in this MySQL version

Really? The latest MySQL 5.7.11 does not have these features? Related report -> http://bugs.mysql.com/bug.php?id=80739

Again I should note the importance of BUG reports one more time. The BUG database is great place to learn and share.
It is also the soul of Open Source project.

4 thoughts on “Interesting actions on MySQL’s storage engines :)

    1. shahriyar Post author

      Thanks for comment 🙂
      Really, the Explain Plans can be weird to understand
      http://bugs.mysql.com/bug.php?id=68814

      mysql> explain select count(*) from sales\G
      *************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: sales
      type: index
      possible_keys: NULL
      key: sales_cust_idx
      key_len: 4
      ref: NULL
      rows: 2489938
      Extra: Using index
      1 row in set (0.00 sec)

      Second query:

      mysql> explain select count(*) from sales where sales_id>0\G
      *************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: sales
      type: range
      possible_keys: PRIMARY
      key: PRIMARY
      key_len: 4
      ref: NULL
      rows: 1244969
      Extra: Using where; Using index
      1 row in set (0.00 sec)

      See the difference on rows. So, the best way to optimize query is to put ‘where column_name > 0’ 😛

      Reply
  1. Justin Swanhart

    The TABLESPACE option is an NDB option, setting TABLESPACE=innodb_file_per_table should not do anything at all even on an InnoDB table.

    This strange behavior you are seeing is a legacy of the FRM nightmare that should hopefully one day go away when MySQL has a real data dictionary (hey someday there might even be working constraints!)

    Reply
    1. shahriyar Post author

      Well, with recent MySQL 5.7 you can run ‘alter table sbtest1 tablespace=s1’ (s1 is general tablespace) and if you want to convert back from general to innodb_file_per_table tablespace you can run ‘alter table sbtest1 tablespace=innodb_file_per_table’:

      https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html

      🙂 I hope not only this nightmare will go away but also, ‘alter’ statements will be crash resistant:
      http://bugs.mysql.com/bug.php?id=80183

      And as I know these huge changes will be in MySQL 5.8

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *