[MDEV-28727] ALTER TABLE ALGORITHM=NOCOPY does not work after upgrade - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.4.25, 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
Fix Version/s: 10.4.26, 10.5.17, 10.6.9, 10.7.5, 10.8.4, 10.9.2
Component/s: Data Definition - Alter Table
Labels:
- rr-profile-analyzed
- upgrade

Description

Here is the repro step.

1) install 10.1.4 using binary tarballs.
2) create table

CREATE TABLE `pet4` ( `build_time` double(18,7) DEFAULT NULL, KEY `idx1` (`build_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 row_format=compact;

3) upgrade the instance with 10.4.25. This can be done by yum/rpm install.
4) run alter against table created in #2.

MariaDB [test]> ALTER TABLE pet4 ADD `i1` INTEGER, algorithm=nocopy, lock=none;

ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE

#4 works without error, when create is created with v10.4.25.

MariaDB [test]> CREATE TABLE `pet5` (

    ->   `build_time` double(18,7) DEFAULT NULL,

    ->   KEY `idx1` (`build_time`)

    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

Query OK, 0 rows affected (0.009 sec)

MariaDB [test]> ALTER TABLE pet5 ADD `i1` INTEGER, algorithm=nocopy, lock=none;

Query OK, 0 rows affected (0.004 sec)

Records: 0  Duplicates: 0  Warnings: 0

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

10.1.48.tar.gz
656 kB
2022-06-03 22:53
10.4.25-after-alter.tar.gz
333 kB
2022-06-03 22:53
10.4.25-before-alter.tar.gz
330 kB
2022-06-03 22:53
CS0404325.tar.gz
664 kB
2022-06-03 22:16

Issue Links

causes

MDEV-29481 mariadb-upgrade prints confusing statement

Closed

is caused by

MDEV-20704 An index on a double column erroneously uses prefix compression

Closed

relates to

MDEV-16291 Allow ALGORITHM=NOCOPY for most ALTER TABLE in InnoDB

Open

MDEV-28822 Table from older version requires table rebuild when adding column to table with multi-column index

Closed

MDEV-18827 Create utility to parse frm files and print their DDL (Full-time project - potential part-time)

Open

MDEV-29495 Generalize can_convert_xxx() hook engine API to cover any arbitrary data type

Closed

SAMU-272 Loading...

is blocked by: SAMU-1 Loading...

mentioned in: Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...

(2 relates to, 1 is blocked by, 17 mentioned in)

Activity

Ascending order - Click to sort in descending order

View 8 older comments

Aleksey Midenkov added a comment - 2022-07-20 17:28

Since there is no direct knowledge that only DOUBLE is affected (~~MDEV-20704~~ was about FIELDFLAG_BLOB sideffect) I'm going to fix it in most generic way:

1. if FRM version is prior ~~MDEV-20704~~ fix CHECK TABLE fails with error:

test.pet4       check   error   Table rebuild required. Please do "ALTER TABLE `pet4` FORCE" or dump/reload to fix it!

2. mysqlcheck --auto-repair (and mysql_upgrade) when sees such table issues ALTER TABLE FORCE.

Aleksey Midenkov added a comment - 2022-07-20 17:28 Since there is no direct knowledge that only DOUBLE is affected ( MDEV-20704 was about FIELDFLAG_BLOB sideffect) I'm going to fix it in most generic way: 1. if FRM version is prior MDEV-20704 fix CHECK TABLE fails with error: test.pet4 check error Table rebuild required. Please do "ALTER TABLE `pet4` FORCE" or dump/reload to fix it! 2. mysqlcheck --auto-repair (and mysql_upgrade) when sees such table issues ALTER TABLE FORCE.

Aleksey Midenkov added a comment - 2022-07-22 01:49

Please review bb-10.4-midenok2

Aleksey Midenkov added a comment - 2022-07-22 01:49 Please review bb-10.4-midenok2

Oleksandr Byelkin added a comment - 2022-07-29 12:20

OK to push

Oleksandr Byelkin added a comment - 2022-07-29 12:20 OK to push

Marko Mäkelä added a comment - 2022-08-16 09:00

Did I understand the fix correctly that it actually continues to require such tables to be rebuilt when upgrading the server? Is there any way to avoid unnecessarily rebuilding the data of the table, and to adjust or tolerate old-format .frm files?

Marko Mäkelä added a comment - 2022-08-16 09:00 Did I understand the fix correctly that it actually continues to require such tables to be rebuilt when upgrading the server? Is there any way to avoid unnecessarily rebuilding the data of the table, and to adjust or tolerate old-format .frm files?

Marko Mäkelä added a comment - 2022-09-09 11:06 - edited

I can still reproduce the reported problem with the following test:

--source include/have_innodb.inc

CREATE TABLE pet4 (build_time double(18,7) DEFAULT NULL, KEY idx1 (build_time))

ENGINE=InnoDB;

let $datadir=`select @@datadir`;

FLUSH TABLES;

remove_file $datadir/test/pet4.frm;

copy_file std_data/pet4.frm $datadir/test/pet4.frm;

ALTER TABLE pet4 ADD i1 INTEGER, ALGORITHM=INSTANT;

DROP TABLE pet4;

This is with the pet4.frm from CS0404325.tar.gz:

tar xzf CS0404325.tar.gz  ./CS0404325/pet4.frm

mv CS0404325/pet4.frm mysql-test/std_data/

10.4 9a8faeea142ea6f575419799c9439f4673971573
mysqltest: At line 10: query 'ALTER TABLE pet4 ADD i1 INTEGER, ALGORITHM=INSTANT' failed: 1845: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE

I intend to fix this in ~~MDEV-29481~~. As part of that, the previous ‘fix’ would be reverted.

Marko Mäkelä added a comment - 2022-09-09 11:06 - edited I can still reproduce the reported problem with the following test: --source include/have_innodb.inc CREATE TABLE pet4 (build_time double (18,7) DEFAULT NULL , KEY idx1 (build_time)) ENGINE=InnoDB; let $datadir=` select @@datadir`; FLUSH TABLES; remove_file $datadir/test/pet4.frm; copy_file std_data/pet4.frm $datadir/test/pet4.frm; ALTER TABLE pet4 ADD i1 INTEGER , ALGORITHM=INSTANT; DROP TABLE pet4; This is with the pet4.frm from CS0404325.tar.gz : tar xzf CS0404325.tar.gz ./CS0404325/pet4.frm mv CS0404325/pet4.frm mysql-test/std_data/ 10.4 9a8faeea142ea6f575419799c9439f4673971573 mysqltest: At line 10: query 'ALTER TABLE pet4 ADD i1 INTEGER, ALGORITHM=INSTANT' failed: 1845: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE I intend to fix this in MDEV-29481 . As part of that, the previous ‘fix’ would be reverted.

People

Assignee:: Aleksey Midenkov

Reporter:: Allen Lee (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 10 Start watching this issue

Dates

Created:: 2022-06-02 04:12

Updated:: 2024-12-09 17:18

Resolved:: 2022-08-02 09:49

Git Integration

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

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration