[MDEV-31601] Some ALTER TABLE .. fail when they worked before, and with a wrong error message - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: N/A
Fix Version/s: 11.2.1
Component/s: Data Definition - Alter Table
Labels:
None

Description

CREATE TABLE t (a INT);

INSERT INTO t VALUES (1),(2);

ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;

DROP TABLE t;

ALTER here can have actual operations before ALGORITHM, I just over-simplified it to allow ALTER be executed multiple times without intermediate changes.

In the above example, before ~~MDEV-16329~~ ALTER works with ALGORITHM=NOCOPY and ALGORITHM=INSTANT (and of course with COPY and DEFAULT), while fails with ALGORITHM=INPLACE:

11.2 2867894ac6ca23fc2f82cfad2dd510351f597325
MariaDB [test]> ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;
Query OK, 2 rows affected (0.044 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> ALTER TABLE t ALGORITHM=INPLACE, ORDER BY a;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
MariaDB [test]>
MariaDB [test]> ALTER TABLE t ALGORITHM=INSTANT, ORDER BY a;
Query OK, 2 rows affected (0.056 sec)
Records: 2 Duplicates: 0 Warnings: 0

This is in itself weird, and may be a bug.

After ~~MDEV-16329~~ the behavior changed. If the above was indeed a bug, then functionally the change may be correct, but it leaves us with a wrong error message:

bb-11.2-oalter adcf5dfa8d646b9fbd2bb1a7a4f5a3bb8a7e659c
MariaDB [test]> ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
MariaDB [test]>
MariaDB [test]> ALTER TABLE t ALGORITHM=INPLACE, ORDER BY a;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
MariaDB [test]>
MariaDB [test]> ALTER TABLE t ALGORITHM=INSTANT, ORDER BY a;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY

Regardless which algorithm is specified, it complains about INPLACE.

Or, if the old behavior was valid and on whatever reason INPLACE was indeed not applicable, but NOCOPY and INSTANT were, then the new behavior is functionally a regression.

Attachments

Issue Links

is caused by

MDEV-16329 Engine-independent online ALTER TABLE

Closed

relates to

MDEV-30906 Variable alter_algorithm=COPY takes precedence over explicit ALGORITHM clause

Stalled

Activity

Ascending order - Click to sort in descending order

Elena Stepanova created issue - 2023-07-02 13:15

Elena Stepanova made changes - 2023-07-02 13:15

Field	Original Value	New Value
Link		This issue is caused by ~~MDEV-16329~~ [ ~~MDEV-16329~~ ]

Elena Stepanova made changes - 2023-07-02 13:17

Description

{code:sql}
CREATE TABLE t (a INT);
INSERT INTO t VALUES (1),(2);
ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;

DROP TABLE t;
{code}

_ALTER here can have actual operations before ALGORITHM, I just over-simplified it to allow ALTER be executed multiple times without intermediate changes._

In the above example, *before ~~MDEV-16329~~* ALTER works with ALGORITHM=NOCOPY and ALGORITHM=INSTANT (and of course with COPY and DEFAULT), while fails with {{ALGORITHM=INPLACE}}:
{code:sql|title=11.2 2867894ac6ca23fc2f82cfad2dd510351f597325}
MariaDB [test]> ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;
Query OK, 2 rows affected (0.044 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> ALTER TABLE t ALGORITHM=INPLACE, ORDER BY a;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
MariaDB [test]>
MariaDB [test]> ALTER TABLE t ALGORITHM=INSTANT, ORDER BY a;
Query OK, 2 rows affected (0.056 sec)
Records: 2 Duplicates: 0 Warnings: 0
{code}

This is in itself weird, and may be a bug in itself.

After ~~MDEV-16329~~ the behavior changed. If the above was indeed a bug, then functionally the change may be correct, but it leaves us with a wrong error message:

{code:sql|title=bb-11.2-oalter adcf5dfa8d646b9fbd2bb1a7a4f5a3bb8a7e659c}
MariaDB [test]> ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
MariaDB [test]>
MariaDB [test]> ALTER TABLE t ALGORITHM=INPLACE, ORDER BY a;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
MariaDB [test]>
MariaDB [test]> ALTER TABLE t ALGORITHM=INSTANT, ORDER BY a;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
{code}

Regardless which algorithm is specified, it complains about INPLACE.

Or, if the old behavior was valid and on whatever reason INPLACE was indeed not applicable, but NOCOPY and INSTANT were, then the new behavior is functionally a regression.

{code:sql}
CREATE TABLE t (a INT);
INSERT INTO t VALUES (1),(2);
ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;

DROP TABLE t;
{code}

_ALTER here can have actual operations before ALGORITHM, I just over-simplified it to allow ALTER be executed multiple times without intermediate changes._

In the above example, *before ~~MDEV-16329~~* ALTER works with ALGORITHM=NOCOPY and ALGORITHM=INSTANT (and of course with COPY and DEFAULT), while fails with {{ALGORITHM=INPLACE}}:
{code:sql|title=11.2 2867894ac6ca23fc2f82cfad2dd510351f597325}
MariaDB [test]> ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;
Query OK, 2 rows affected (0.044 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> ALTER TABLE t ALGORITHM=INPLACE, ORDER BY a;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
MariaDB [test]>
MariaDB [test]> ALTER TABLE t ALGORITHM=INSTANT, ORDER BY a;
Query OK, 2 rows affected (0.056 sec)
Records: 2 Duplicates: 0 Warnings: 0
{code}

This is in itself weird, and may be a bug.

After ~~MDEV-16329~~ the behavior changed. If the above was indeed a bug, then functionally the change may be correct, but it leaves us with a wrong error message:

{code:sql|title=bb-11.2-oalter adcf5dfa8d646b9fbd2bb1a7a4f5a3bb8a7e659c}
MariaDB [test]> ALTER TABLE t ALGORITHM=NOCOPY, ORDER BY a;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
MariaDB [test]>
MariaDB [test]> ALTER TABLE t ALGORITHM=INPLACE, ORDER BY a;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
MariaDB [test]>
MariaDB [test]> ALTER TABLE t ALGORITHM=INSTANT, ORDER BY a;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
{code}

Regardless which algorithm is specified, it complains about INPLACE.

Or, if the old behavior was valid and on whatever reason INPLACE was indeed not applicable, but NOCOPY and INSTANT were, then the new behavior is functionally a regression.

Elena Stepanova made changes - 2023-07-03 16:33

Summary

ALTER TABLE .. ORDER BY fails when it worked before, and with a wrong error message

Some ALTER TABLE .. fail when they worked before, and with a wrong error message

Elena Stepanova added a comment - 2023-07-03 16:38 - edited

Same happens upon ENGINE change.

CREATE TABLE t (a INT) ENGINE=Aria;

ALTER TABLE t ENGINE=MyISAM, ALGORITHM=NOCOPY;

SHOW CREATE TABLE t;

DROP TABLE t;

bb-11.2-oalter adcf5dfa8d6
mysqltest: At line 2: query 'ALTER TABLE t ENGINE=MyISAM, ALGORITHM=NOCOPY' failed: ER_ALTER_OPERATION_NOT_SUPPORTED (1845): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY

and upon ALTER on a temporary table

CREATE TEMPORARY TABLE t (f INT);

ALTER TABLE t FORCE, ALGORITHM=NOCOPY;

mysqltest: At line 2: query 'ALTER TABLE t FORCE, ALGORITHM=NOCOPY' failed: ER_ALTER_OPERATION_NOT_SUPPORTED (1845): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY

Elena Stepanova added a comment - 2023-07-03 16:38 - edited Same happens upon ENGINE change. CREATE TABLE t (a INT ) ENGINE=Aria; ALTER TABLE t ENGINE=MyISAM, ALGORITHM=NOCOPY; SHOW CREATE TABLE t; DROP TABLE t; bb-11.2-oalter adcf5dfa8d6 mysqltest: At line 2: query 'ALTER TABLE t ENGINE=MyISAM, ALGORITHM=NOCOPY' failed: ER_ALTER_OPERATION_NOT_SUPPORTED (1845): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY and upon ALTER on a temporary table CREATE TEMPORARY TABLE t (f INT ); ALTER TABLE t FORCE , ALGORITHM=NOCOPY; mysqltest: At line 2: query 'ALTER TABLE t FORCE, ALGORITHM=NOCOPY' failed: ER_ALTER_OPERATION_NOT_SUPPORTED (1845): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY

Nikita Malyavin made changes - 2023-07-04 16:46

Link

This issue relates to MDEV-30906 [ MDEV-30906 ]

Nikita Malyavin made changes - 2023-07-05 20:46

Status

Open [ 1 ]

In Progress [ 3 ]

Nikita Malyavin made changes - 2023-07-05 20:46

Assignee	Nikita Malyavin [ nikitamalyavin ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik added a comment - 2023-07-07 17:02

6e6e950c8ab is ok to push

Sergei Golubchik added a comment - 2023-07-07 17:02 6e6e950c8ab is ok to push

Sergei Golubchik made changes - 2023-07-07 17:02

Assignee	Sergei Golubchik [ serg ]	Nikita Malyavin [ nikitamalyavin ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Nikita Malyavin made changes - 2023-07-10 14:31

Status

Stalled [ 10000 ]

In Testing [ 10301 ]

Sergei Golubchik made changes - 2023-08-16 09:33

Fix Version/s		11.2.1 [ 29034 ]
Fix Version/s	11.2 [ 28603 ]
Resolution		Fixed [ 1 ]
Status	In Testing [ 10301 ]	Closed [ 6 ]

People

Assignee:: Nikita Malyavin

Reporter:: Elena Stepanova

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2023-07-02 13:15

Updated:: 2023-08-16 09:33

Resolved:: 2023-08-16 09:33

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration