MySQL Bugs: #98523: ERROR 1846 (0A000) at line 1: ALGORITHM=INPLACE is not supported. Reason: Canno

Bug #98523	ERROR 1846 (0A000) at line 1: ALGORITHM=INPLACE is not supported. Reason: Canno
Submitted:	8 Feb 2020 20:55	Modified:	6 May 2020 18:14
Reporter:	John K	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	DDL, INDEX, innodb, inplace

Description:
SQL: create index industry on core (industry) algorithm=inplace
RESPONSE:  ERROR 1846 (0A000) at line 1: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED

Verified on two different servers

How to repeat:
create a table, compress it, try add an index

In case it matters: industry is of type "enum" !
Table is non partitioned, autoincremend primary key

Hi,

Thanks for the report. Can we see the whole create table you are trying to alter.

Hi,

I understand that you need to modify the test case to hide IP but I need to have a way to reproduce the problem before I can decide if it's a bug or not as you are surely informed that we do have some limitations here in place as described here: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-limitations.html

Now I filled the sample table you provided with 50,000 rows and

mysql [localhost:8018] {msandbox} (test) > select count(*) from core;
+----------+
| count(*) |
+----------+
|    50000 |
+----------+
1 row in set (0.03 sec)

mysql [localhost:8018] {msandbox} (test) > create index industry on core (industry) algorithm=inplace;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:8018] {msandbox} (test) >

as you can see I'm unable to reproduce the issue.

Can you try to provide us with more data in order to reproduce the problem?

kind regards

Can you confirm the bug with the test case I've sent ?

Further refined the test case:
CREATE TABLE `bad_test` (
bad set('12345678901234567890','12345678901234567890','1234567890123456789012') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED;

mysql> alter table bad_test add test2 int null after bad, algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

CREATE TABLE `good_test` (
good set('12345678901234567890','12345678901234567890','123456789012345678901') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED;

alter table good_test add test2 int null after good, algorithm=inplace;
Query OK, 0 rows affected, 1 warning (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 1

As ridiculous as it might sound, as soon as count of characters combined in SET keys are higher than 61 the entire table is blocked permanently from INPLACE or INSTANT operations.

It does not seem to matter how many keys you have in SET, as soon as you pass that barrier the table is 'damaged'

Hi,

mysql [localhost:8018] {msandbox} (test) > CREATE TABLE `bad_test` (
    -> bad set('12345678901234567890','12345678901234567890','1234567890123456789012') DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED;
ERROR 1291 (HY000): Column 'bad' has duplicated value '12345678901234567890' in SET
mysql [localhost:8018] {msandbox} (test) >

What is your SQL_MODE setting?

mysql [localhost:8018] {msandbox} (test) > show variables like 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

mysql [localhost:8018] {msandbox} (test) >

All best
Bogdan

mysql [localhost:8018] {msandbox} (test) > set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8018] {msandbox} (test) > show tables;
Empty set (0.00 sec)

mysql [localhost:8018] {msandbox} (test) > CREATE TABLE `bad_test` (
    -> bad set('12345678901234567890','12345678901234567890','1234567890123456789012') DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql [localhost:8018] {msandbox} (test) > show warnings;
+-------+------+-----------------------------------------------------------------+
| Level | Code | Message                                                         |
+-------+------+-----------------------------------------------------------------+
| Note  | 1291 | Column 'bad' has duplicated value '12345678901234567890' in SET |
+-------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:8018] {msandbox} (test) > alter table bad_test add test2 int null after bad, algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
mysql [localhost:8018] {msandbox} (test) >

Hi,

SQL_MODE is not relevant here (just for the test case so we can ignore it). 

The size of the set makes the algo different and when larger you hit the limitation "The table is copied when creating an index on a TEMPORARY TABLE." as a temp table is created for that set/enum

kind regards
Bogdan

Yes, that seems a significant unexpected problem with SET.
It takes multiple days in my case to do a table copy as mysql only uses one cpu core during the process (reducing 2000mb/sec IO down to 30-80) and it takes storage space one might not have available.

My recommendation is to handle that limitation of SET as a bug, solve it so as set type column doesn't block the entire table from having secondary indexes created without full table re-write.

Alternatively that limitation of 62 characters of combined SET values needs to be added on the SET manual page and on the INPLACE manual page.
It's a serious limitation

Hi John,

If you agree I can convert this report to "feature request" and try to push it up the chain.

kind regards
Bogdan

I personally will remove the SET datatype from all our tables, the limitation is a show-stopped now that they are so big.

I am quite sure it's a bug, not a feature:
https://dev.mysql.com/doc/refman/8.0/en/set.html
If you look here, there is no word about a 62 character limitation or change in behaviour or ddl support. 
Such limitations are usually around 64kiB (like with enum)

Regarding DDL:
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-limitations.html
Not a word that says that under any condition a secondary index can not be created INPLACE.

This just wasn't noticed because most people are not aware if a database operation is INPLACE/INSTANT or COPY. A 1 MiB wordpress table is going to copy quickly.
It only starts to become an issue when a database has larger amounts data stored.

It's your decision, but for the quality of mysql it needs to be either fixed or the limitation needs to be put into the manuals.
Maybe multi-value json indexes will make SET obsolete one day, sadly they don't support characters yet. Until then SET is the only index-able multi value column mysql offers.

I suppose that's it ?

Hi,

Dev team agreed it is a bug.

stay safe
Bogdan

Posted by developer:
 
Fixed in 8.0.21.

ALTER TABLE on a SET column that had a display width greater than 255
was not done in place, even if otherwise possible.