ALTER TABLE ... ENCRYPTION='y' is possible on TokuDB tables && it is possible to alter an encrypted InnoDB table to TokuDB

[Shahriyar Rzayev]

Here is the simple case:

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: 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=1000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
1 row in set (0.00 sec)

mysql> alter table sbtest1 engine=tokudb;
Query OK, 1000000 rows affected (30.25 sec)
Records: 1000000 Duplicates: 0 Warnings: 0

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

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

mysql> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: 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=TokuDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 MAX_ROWS=1000000 COMPRESSION='zlib' ENCRYPTION='y'
1 row in set (0.01 sec)

I can even update tablespace to general tablespace with tokudb enabled table:

mysql> create tablespace s1 add datafile 's1.ibd' engine=innodb;
Query OK, 0 rows affected (0.01 sec)

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

[Roel Van de Paar (roel11)]

Please triage. Is the table corrupted, ... etc.

[Roel Van de Paar (roel11)]

And if the table is not corrupted (looks like it), will mysqldump etc. etc. work (various interactions possible)

[Laurynas Biveinis (laurynas-biveinis)]

Roel, where do you see " it is possible to alter an encrypted InnoDB table to TokuDB" ?

Roel, Shahriyar, is there any evidence of data corruption?

[Ramesh Sivaraman (rameshvs02)]

Hi Laurynas,

Able to alter encrypted InnoDB to TokuDB

FYI

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS,ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS like '%ENCRYPTION=%' and TABLE_NAME='sbtest3';
+--------------+------------+---------------------------------+--------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS | ENGINE |
+--------------+------------+---------------------------------+--------+
| test | sbtest3 | max_rows=1000000 ENCRYPTION="Y" | InnoDB |
+--------------+------------+---------------------------------+--------+
1 row in set (0.00 sec)

mysql> alter table sbtest3 engine=tokudb;
seQuery OK, 10000 rows affected (1.56 sec)
Records: 10000 Duplicates: 0 Warnings: 0

mysql> select * from sbtest3 limit 1;
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 3604 | 33372830822-03501423578-08969431761-47532028179-18722774420-04690175960-18290564915-56375796601-16428909243-71092217288 | 88325381893-08946707666-06349126590-49241819740-70925111027 |
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> delete from sbtest3 where id=1;
Query OK, 1 row affected (0.04 sec)

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS,ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS like '%ENCRYPTION=%' and TABLE_NAME='sbtest3';
+--------------+------------+---------------------------------+--------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS | ENGINE |
+--------------+------------+---------------------------------+--------+
| test | sbtest3 | max_rows=1000000 ENCRYPTION="Y" | TokuDB |
+--------------+------------+---------------------------------+--------+
1 row in set (0.00 sec)

mysql>

[Shahriyar Rzayev (rzayev-sehriyar)]

Possible result is:

Just create empty table:

CREATE TABLE `sbtest3` (
  `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=TokuDB DEFAULT CHARSET=utf8 MAX_ROWS=1000000 COMPRESSION='zlib' ENCRYPTION='Y'

Try to convert engine to Innodb:

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

From error log:

2016-03-15 12:37:58 0x7f3ed5432700 InnoDB: Assertion failure in thread 139907842647808 in file mem0mem.ic line 157
InnoDB: Failing assertion: block->magic_n == MEM_BLOCK_MAGIC_N

/opt/percona-5.7.11-4/bin/mysqld(my_print_stacktrace+0x38)[0x188f208]
/opt/percona-5.7.11-4/bin/mysqld(handle_fatal_signal+0x411)[0xe743de]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10340)[0x7f3ed3b9a340]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x39)[0x7f3ed2fdbcc9]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7f3ed2fdf0d8]
/opt/percona-5.7.11-4/bin/mysqld[0x1b55228]
/opt/percona-5.7.11-4/bin/mysqld[0x1c2bc6c]
/opt/percona-5.7.11-4/bin/mysqld[0x1c2bf7a]
/opt/percona-5.7.11-4/bin/mysqld(dict_mem_table_free(dict_table_t*)+0x1c0)[0x1c2d1b5]
/opt/percona-5.7.11-4/bin/mysqld(create_table_info_t::create_table_def()+0x1083)[0x191794d]
/opt/percona-5.7.11-4/bin/mysqld(create_table_info_t::create_table()+0xa8)[0x19035dc]
/opt/percona-5.7.11-4/bin/mysqld(ha_innobase::create(char const*, TABLE*, st_ha_create_information*)+0x1a0)[0x19041d6]
/opt/percona-5.7.11-4/bin/mysqld(handler::ha_create(char const*, TABLE*, st_ha_create_information*)+0x74)[0xf0fecc]
/opt/percona-5.7.11-4/bin/mysqld(ha_create_table(THD*, char const*, char const*, char const*, st_ha_create_information*, bool, bool)+0x25e)[0xf10ba1]
/opt/percona-5.7.11-4/bin/mysqld(mysql_alter_table(THD*, char const*, char const*, st_ha_create_information*, TABLE_LIST*, Alter_info*)+0x1eff)[0x15d492c]
/opt/percona-5.7.11-4/bin/mysqld(Sql_cmd_alter_table::execute(THD*)+0x59d)[0x175149f]
/opt/percona-5.7.11-4/bin/mysqld(mysql_execute_command(THD*, bool)+0x70ad)[0x1535701]
/opt/percona-5.7.11-4/bin/mysqld(mysql_parse(THD*, Parser_state*)+0x6b8)[0x15379cf]
/opt/percona-5.7.11-4/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xc34)[0x152bf7d]
/opt/percona-5.7.11-4/bin/mysqld(do_command(THD*)+0x520)[0x152ae5b]
/opt/percona-5.7.11-4/bin/mysqld(handle_connection+0x2b0)[0x1676b01]
/opt/percona-5.7.11-4/bin/mysqld(pfs_spawn_thread+0x170)[0x1cf83a5]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8182)[0x7f3ed3b92182]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f3ed309f47d]

[Laurynas Biveinis (laurynas-biveinis)]

Shahriyar, how do tables look after restart after the crash above?

What happens if one starts with a MyISAM, not TokuDB table?

[Shahriyar Rzayev (rzayev-sehriyar)]

Table remains with original engine. Also I should note that, can not reproduce this crash with MySQL 5.7.11(optimized build)
Teset PS 5.7.11-4 is is debug-valgrind build.

Well, I can't reproduce this with MyISAM:

mysql> show create table sbtest2\G
*************************** 1. row ***************************
       Table: sbtest2
Create Table: 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'
1 row in set (0,00 sec)

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

Reported this weird situation as:

http://bugs.mysql.com/bug.php?id=80739 (myisam)

Also see -> http://bugs.mysql.com/bug.php?id=80731 (myisam)

The same assertion is here with MEMORY engine too:

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'

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

Bu I am able to convert MEMORY to TokuDB:

mysql> alter table sbtest2 engine=tokudb;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

[Shahriyar Rzayev (rzayev-sehriyar)]

Could reproduce with MySQL 5.7.11 debug-valgrind build.
Reported as my 100th bug :P :P

http://bugs.mysql.com/bug.php?id=80745

[Roel Van de Paar (roel11)]

Congrats Shako on the 100th :)

[Roel Van de Paar (roel11)]

My thought re: data corruption was more about "corrupted SHOW CREATE TABLE output" - i.e. possibly resulting in failures of mysqldump, and 3rd party tools etc.

It seems possible to get SHOW CREATE TABLE to show that the table is encrypted whereas it's not;

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS,ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS like '%ENCRYPTION=%' and TABLE_NAME='sbtest3';
+--------------+------------+---------------------------------+--------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS | ENGINE |
+--------------+------------+---------------------------------+--------+
| test | sbtest3 | max_rows=1000000 ENCRYPTION="Y" | InnoDB |
+--------------+------------+---------------------------------+--------+
1 row in set (0.00 sec)

mysql> alter table sbtest3 engine=tokudb;
seQuery OK, 10000 rows affected (1.56 sec)
Records: 10000 Duplicates: 0 Warnings: 0

mysql> select * from sbtest3 limit 1;
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 3604 | 33372830822-03501423578-08969431761-47532028179-18722774420-04690175960-18290564915-56375796601-16428909243-71092217288 | 88325381893-08946707666-06349126590-49241819740-70925111027 |
+----+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> delete from sbtest3 where id=1;
Query OK, 1 row affected (0.04 sec)

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS,ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS like '%ENCRYPTION=%' and TABLE_NAME='sbtest3';
+--------------+------------+---------------------------------+--------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS | ENGINE |
+--------------+------------+---------------------------------+--------+
| test | sbtest3 | max_rows=1000000 ENCRYPTION="Y" | TokuDB |
+--------------+------------+---------------------------------+--------+
1 row in set (0.00 sec)

[Laurynas Biveinis (laurynas-biveinis)]

Marking as a copy of the upstream bug. If upstream fixes this first, this will have to be re-verified once the upstream bug is closed, whether any changes in the TokuDB SE are required.

[George Ormond Lorch III (gl-az)]

https://tokutek.atlassian.net/browse/DB-966

[George Ormond Lorch III (gl-az)]

Upstream notes fixed in 5.7.13, can we re-confirm/triage and either update this with the current problem or close this issue, Roel, Shako?

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-973