MySQL Bugs: #81644: Unclear error message while doing online alter with encrypted table

Bug #81644	Unclear error message while doing online alter with encrypted table
Submitted:	30 May 2016 12:53	Modified:	1 Jul 2016 12:33
Reporter:	Shahriyar Rzayev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.7.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Regardless of Encryption enabled or disabled on table it impossible to change auto_increment with algorithm=inplace:

Enabling encryption:

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

mysql> alter table sbtest1 auto_increment=30, algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Invalid encryption option.. Try ALGORITHM=COPY.

Disabling encryption:

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

mysql> alter table sbtest1 auto_increment=30, algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Invalid encryption option.. Try ALGORITHM=COPY.

"Reason: Invalid encryption option" -> false reason.

How to repeat:
See description

Suggested fix:
Maybe improving error message will be an option. But the best thing will be if we could run this with unencrypted table.

Hello Shahriyar,

Thank you for the report.

Thanks,
Umesh

-- 5.7.12

[root@cluster-repo ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.12 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  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=100001 DEFAULT CHARSET=latin1 MAX_ROWS=1000000;
Query OK, 0 rows affected (0.03 sec)

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

mysql> alter table sbtest1 auto_increment=30, algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Invalid encryption option.. Try ALGORITHM=COPY.

Fixed as of the upcoming 5.7.13, 8.0.0 releases along with bug#80386.

Shouldn't that be 5.7.14?

based on release notes it is already fixed:
https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-13.html#mysqld-5-7-13-bug

InnoDB: Online DDL operations like ALTER TABLE ... ADD INDEX were not permitted for tables created with the ENCRYPTION attribute. (Bug #22897921)

And From -> #80386

[23 Mar 2016 7:09] Daniel T Price

Fixed as of the upcoming 5.7.13, 5.8.0 release, and here's the changelog
entry:

Online DDL operations like ALTER TABLE ... ADD INDEX were not permitted
for tables created with the ENCRYPTION attribute.

(Under the heading of Bug#22897921)

5.7.13 is not upcoming anymore :)