神刀安全网

Interesting actions on MySQL’s storage engines :)

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 :stuck_out_tongue:

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

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

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.

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Interesting actions on MySQL’s storage engines :)

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮