[MDEV-33214] Table is getting rebuild with ALTER TABLE ADD COLUMN - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.5.21, 10.4(EOL), 10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4
Fix Version/s: 10.4.34, 10.5.25, 10.11.8, 10.6.18, 11.0.6, 11.1.5, 11.2.4
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Labels:
None

Description

By default, ALTER TABLE ADD COLUMN should be instant and it should not rebuild the table but when we add column with VARCHAR(5000) and charset is UTF8 then it is rebuilding the table.

MariaDB [test]> CREATE TABLE nil_test(id int, name varchar(10)) DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci;

Query OK, 0 rows affected (0.109 sec)

MariaDB [test]> INSERT INTO nil_test VALUES (1, 'abc'), (2, 'def');

Query OK, 2 rows affected (0.003 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> quit

Bye

[root@centos8 test]# ls -al nil*

-rw-rw---- 1 mysql mysql   486 Dec 20 10:03 nil_test.frm

-rw-rw---- 1 mysql mysql 98304 Dec 20 10:03 nil_test.ibd

..

[root@centos8 test]# date

Wed Dec 20 10:04:06 IST 2023

[root@centos8 test]# mysql

MariaDB [(none)]> use test

Database changed

MariaDB [test]> ALTER TABLE nil_test ADD COLUMN address varchar(5000), ADD COLUMN city varchar(20);

Query OK, 0 rows affected (0.026 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> quit

Bye

[root@centos8 test]# ls -al nil*

-rw-rw---- 1 mysql mysql 15596 Dec 20 10:04 nil_test.frm

-rw-rw---- 1 mysql mysql 98304 Dec 20 10:04 nil_test.ibd

If I use varchar(1000) for the address then it doesn't rebuild.

[root@centos8 test]# ls -al nil*

-rw-rw---- 1 mysql mysql   569 Dec 20 10:06 nil_test.frm

-rw-rw---- 1 mysql mysql 98304 Dec 20 10:04 nil_test.ibd

I can see only .frm file's time and size is updated but not .ibd.

Attachments

Activity

Ascending order - Click to sort in descending order

Hartmut Holzgraefe added a comment - 2024-01-10 13:52 - edited

The threshold at which INSTANT adding a VARCHAR is no longer possible is at 2027/2028 characters when using utf8mb4 and 2703/2704 characters with utf8mb3; or at around 8110 bytes in both cases.

So it looks to be related to the max. row length, when the max column size exceeds that then INSTANT is no longer possible?

I could not find this limitation being documented anywhere ...

Hartmut Holzgraefe added a comment - 2024-01-10 13:52 - edited The threshold at which INSTANT adding a VARCHAR is no longer possible is at 2027/2028 characters when using utf8mb4 and 2703/2704 characters with utf8mb3; or at around 8110 bytes in both cases. So it looks to be related to the max. row length, when the max column size exceeds that then INSTANT is no longer possible? I could not find this limitation being documented anywhere ...

Marko Mäkelä added a comment - 2024-01-10 15:16

I do not think that your methodology for detecting a table rebuild is correct. The file timestamp could also change due to page flushing during any DML workload. You should check if the file inode changed. That you might do by creating a hard link to the file (ln nil_test.ibd hardlink) before the ALTER TABLE and then checking if the link count of both files remains 2. If not, the original nil_test.ibd file was deleted and a new one created.

I tested the following:

--source include/have_innodb.inc

CREATE TABLE t1(id INT, name VARCHAR(10)) ENGINE=InnoDB;

INSERT INTO t1 VALUES(1,'abc'),(2,'def');

SELECT space INTO @s FROM information_schema.innodb_sys_tables

WHERE name='test/t1';

ALTER TABLE t1 ADD (address VARCHAR(5000), city VARCHAR(20)), ALGORITHM=NOCOPY;

SELECT space=@s FROM information_schema.innodb_sys_tables WHERE name='test/t1';

DROP TABLE t1;

The second SELECT statement is returning 1, showing that the tablespace ID did not change. If I replace ALGORITHM=NOCOPY with FORCE, then the SELECT will return 0. The tablespace ID would change on any rebuild, unless you are using innodb_file_per_table=0 and the table is located in the system tablespace (not in an .ibd file).

I tested this both on a 11.x development branch as of today, and on a build of the mariadb-10.5.21 release tag. For the latter, this is the result:

mariadb-10.5.21
CREATE TABLE t1(id INT, name VARCHAR(10)) ENGINE=InnoDB;
INSERT INTO t1 VALUES(1,'abc'),(2,'def');
SELECT space INTO @s FROM information_schema.innodb_sys_tables
WHERE name='test/t1';
ALTER TABLE t1 ADD (address VARCHAR(5000), city VARCHAR(20)), ALGORITHM=NOCOPY;
SELECT space=@s FROM information_schema.innodb_sys_tables WHERE name='test/t1';
space=@s
1
DROP TABLE t1;

I think that we need more information from the original environment where this happened. The contents of information_schema.innodb_sys_tables and information_schema.innodb_sys_tablespaces for this table would be a start. Is instant ADD COLUMN prevented by innodb_instant_alter_column_allowed=never (~~MDEV-20590~~)?

Marko Mäkelä added a comment - 2024-01-10 15:16 I do not think that your methodology for detecting a table rebuild is correct. The file timestamp could also change due to page flushing during any DML workload. You should check if the file inode changed. That you might do by creating a hard link to the file ( ln nil_test.ibd hardlink ) before the ALTER TABLE and then checking if the link count of both files remains 2. If not, the original nil_test.ibd file was deleted and a new one created. I tested the following: --source include/have_innodb.inc CREATE TABLE t1(id INT , name VARCHAR (10)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, 'abc' ),(2, 'def' ); SELECT space INTO @s FROM information_schema.innodb_sys_tables WHERE name = 'test/t1' ; ALTER TABLE t1 ADD (address VARCHAR (5000), city VARCHAR (20)), ALGORITHM=NOCOPY; SELECT space =@s FROM information_schema.innodb_sys_tables WHERE name = 'test/t1' ; DROP TABLE t1; The second SELECT statement is returning 1, showing that the tablespace ID did not change. If I replace ALGORITHM=NOCOPY with FORCE , then the SELECT will return 0. The tablespace ID would change on any rebuild, unless you are using innodb_file_per_table=0 and the table is located in the system tablespace (not in an .ibd file). I tested this both on a 11.x development branch as of today, and on a build of the mariadb-10.5.21 release tag. For the latter, this is the result: mariadb-10.5.21 CREATE TABLE t1(id INT, name VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES(1,'abc'),(2,'def'); SELECT space INTO @s FROM information_schema.innodb_sys_tables WHERE name='test/t1'; ALTER TABLE t1 ADD (address VARCHAR(5000), city VARCHAR(20)), ALGORITHM=NOCOPY; SELECT space=@s FROM information_schema.innodb_sys_tables WHERE name='test/t1'; space=@s 1 DROP TABLE t1; I think that we need more information from the original environment where this happened. The contents of information_schema.innodb_sys_tables and information_schema.innodb_sys_tablespaces for this table would be a start. Is instant ADD COLUMN prevented by innodb_instant_alter_column_allowed=never ( MDEV-20590 )?

Nilnandan Joshi added a comment - 2024-01-11 05:14 - edited

Hi marko, I think you were not able to reproduce it because you were using latin1 and not utf8. I've tested with both and found out that if you create table with utf8 (DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci ) then table is getting rebuild.

CREATE TABLE t1(id INT, name VARCHAR(10)) ENGINE=InnoDB;

INSERT INTO t1 VALUES(1,'abc'),(2,'def');

SELECT * FROM information_schema.innodb_sys_tables

WHERE name='test/t1';

ALTER TABLE t1 ADD (address VARCHAR(5000), city VARCHAR(20)), ALGORITHM=NOCOPY;

SELECT * FROM information_schema.innodb_sys_tables WHERE name='test/t1';

SELECT table_schema, table_name, table_type, engine, row_format, table_collation FROM information_schema.tables WHERE table_schema= 'test' and table_name='t1';

DROP TABLE t1;

CREATE TABLE t1(id INT, name VARCHAR(10)) DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci ENGINE=InnoDB;

INSERT INTO t1 VALUES(1,'abc'),(2,'def');

SELECT * FROM information_schema.innodb_sys_tables

WHERE name='test/t1';

ALTER TABLE t1 ADD (address VARCHAR(5000), city VARCHAR(20));

SELECT * FROM information_schema.innodb_sys_tables WHERE name='test/t1';

SELECT table_schema, table_name, table_type, engine, row_format, table_collation FROM information_schema.tables WHERE table_schema= 'test' and table_name='t1';

DROP TABLE t1;

MariaDB [test]> CREATE TABLE t1(id INT, name VARCHAR(10)) DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci ENGINE=InnoDB;

Query OK, 0 rows affected (0.056 sec)

MariaDB [test]> INSERT INTO t1 VALUES(1,'abc'),(2,'def');

Query OK, 2 rows affected (0.040 sec)

Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM information_schema.innodb_sys_tables

    -> WHERE name='test/t1';

+----------+---------+------+--------+-------+------------+---------------+------------+

| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |

+----------+---------+------+--------+-------+------------+---------------+------------+

|       67 | test/t1 |   33 |      5 |    53 | Dynamic    |             0 | Single     |

+----------+---------+------+--------+-------+------------+---------------+------------+

1 row in set (0.003 sec)

MariaDB [test]> ALTER TABLE t1 ADD (address VARCHAR(5000), city VARCHAR(20));

Query OK, 0 rows affected (0.149 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT * FROM information_schema.innodb_sys_tables WHERE name='test/t1';

+----------+---------+------+--------+-------+------------+---------------+------------+

| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |

+----------+---------+------+--------+-------+------------+---------------+------------+

|       68 | test/t1 |   33 |      7 |    54 | Dynamic    |             0 | Single     |

+----------+---------+------+--------+-------+------------+---------------+------------+

1 row in set (0.001 sec)

MariaDB [test]> SELECT table_schema, table_name, table_type, engine, row_format, table_collation FROM information_schema.tables WHERE table_schema= 'test' and table_name='t1';

+--------------+------------+------------+--------+------------+-----------------+

| table_schema | table_name | table_type | engine | row_format | table_collation |

+--------------+------------+------------+--------+------------+-----------------+

| test         | t1         | BASE TABLE | InnoDB | Dynamic    | utf8_general_ci |

+--------------+------------+------------+--------+------------+-----------------+

1 row in set (0.002 sec)

MariaDB [test]> DROP TABLE t1;

Query OK, 0 rows affected (0.009 sec)

Nilnandan Joshi added a comment - 2024-01-11 05:14 - edited Hi marko , I think you were not able to reproduce it because you were using latin1 and not utf8. I've tested with both and found out that if you create table with utf8 (DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci ) then table is getting rebuild. CREATE TABLE t1(id INT, name VARCHAR(10)) ENGINE=InnoDB; INSERT INTO t1 VALUES(1,'abc'),(2,'def'); SELECT * FROM information_schema.innodb_sys_tables WHERE name='test/t1'; ALTER TABLE t1 ADD (address VARCHAR(5000), city VARCHAR(20)), ALGORITHM=NOCOPY; SELECT * FROM information_schema.innodb_sys_tables WHERE name='test/t1'; SELECT table_schema, table_name, table_type, engine, row_format, table_collation FROM information_schema.tables WHERE table_schema= 'test' and table_name='t1'; DROP TABLE t1; CREATE TABLE t1(id INT, name VARCHAR(10)) DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci ENGINE=InnoDB; INSERT INTO t1 VALUES(1,'abc'),(2,'def'); SELECT * FROM information_schema.innodb_sys_tables WHERE name='test/t1'; ALTER TABLE t1 ADD (address VARCHAR(5000), city VARCHAR(20)); SELECT * FROM information_schema.innodb_sys_tables WHERE name='test/t1'; SELECT table_schema, table_name, table_type, engine, row_format, table_collation FROM information_schema.tables WHERE table_schema= 'test' and table_name='t1'; DROP TABLE t1; MariaDB [test]> CREATE TABLE t1(id INT, name VARCHAR(10)) DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci ENGINE=InnoDB; Query OK, 0 rows affected (0.056 sec) MariaDB [test]> INSERT INTO t1 VALUES(1,'abc'),(2,'def'); Query OK, 2 rows affected (0.040 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test]> SELECT * FROM information_schema.innodb_sys_tables -> WHERE name='test/t1'; +----------+---------+------+--------+-------+------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+---------+------+--------+-------+------------+---------------+------------+ | 67 | test/t1 | 33 | 5 | 53 | Dynamic | 0 | Single | +----------+---------+------+--------+-------+------------+---------------+------------+ 1 row in set (0.003 sec) MariaDB [test]> ALTER TABLE t1 ADD (address VARCHAR(5000), city VARCHAR(20)); Query OK, 0 rows affected (0.149 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> SELECT * FROM information_schema.innodb_sys_tables WHERE name='test/t1'; +----------+---------+------+--------+-------+------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+---------+------+--------+-------+------------+---------------+------------+ | 68 | test/t1 | 33 | 7 | 54 | Dynamic | 0 | Single | +----------+---------+------+--------+-------+------------+---------------+------------+ 1 row in set (0.001 sec) MariaDB [test]> SELECT table_schema, table_name, table_type, engine, row_format, table_collation FROM information_schema.tables WHERE table_schema= 'test' and table_name='t1'; +--------------+------------+------------+--------+------------+-----------------+ | table_schema | table_name | table_type | engine | row_format | table_collation | +--------------+------------+------------+--------+------------+-----------------+ | test | t1 | BASE TABLE | InnoDB | Dynamic | utf8_general_ci | +--------------+------------+------------+--------+------------+-----------------+ 1 row in set (0.002 sec) MariaDB [test]> DROP TABLE t1; Query OK, 0 rows affected (0.009 sec)

Marko Mäkelä added a comment - 2024-01-11 12:19 - edited

niljoshi, thank you. The following will not rebuild the table:

--source include/have_innodb.inc

CREATE TABLE t1(id INT, name VARCHAR(10)) ENGINE=InnoDB

DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

INSERT INTO t1 VALUES(1,'abc'),(2,'def');

SELECT space INTO @s FROM information_schema.innodb_sys_tables

WHERE name='test/t1';

SET STATEMENT innodb_strict_mode=OFF FOR

ALTER TABLE t1 ADD (address VARCHAR(5000), city VARCHAR(20)), ALGORITHM=NOCOPY;

SELECT space=@s FROM information_schema.innodb_sys_tables WHERE name='test/t1';

DROP TABLE t1;

If I comment out the line SET STATEMENT innodb_strict_mode=OFF FOR, then it will fail as follows:

10.6 d06b6de3050180ec2f96ef00963d1beab8e1b47a

mysqltest: At line 9: query 'ALTER TABLE t1 ADD (address VARCHAR(5000), city VARCHAR(20)), ALGORITHM=NOCOPY' failed: ER_ALTER_OPERATION_NOT_SUPPORTED (1845): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE

The reason for the failure is that the maximum length of the column (5000 characters*3 bytes/character = 15000 characters) is more than the record payload size (somewhere around 8000 bytes for the default innodb_page_size=16k). If I execute this with innodb_page_size=32k, then it will work. The maximum record size (for any innodb_page_size) is 16383 bytes.

I think that this is a bug in instant_alter_column_possible(), because as far as the .ibd file is concerned, the operation should be equivalent to the following, which will pass:

ALTER TABLE t1 ADD (address TEXT, city VARCHAR(20)), ALGORITHM=NOCOPY;

Marko Mäkelä added a comment - 2024-01-11 12:19 - edited niljoshi , thank you. The following will not rebuild the table: --source include/have_innodb.inc CREATE TABLE t1(id INT , name VARCHAR (10)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE =utf8_general_ci; INSERT INTO t1 VALUES (1, 'abc' ),(2, 'def' ); SELECT space INTO @s FROM information_schema.innodb_sys_tables WHERE name = 'test/t1' ; SET STATEMENT innodb_strict_mode= OFF FOR ALTER TABLE t1 ADD (address VARCHAR (5000), city VARCHAR (20)), ALGORITHM=NOCOPY; SELECT space =@s FROM information_schema.innodb_sys_tables WHERE name = 'test/t1' ; DROP TABLE t1; If I comment out the line SET STATEMENT innodb_strict_mode=OFF FOR , then it will fail as follows: 10.6 d06b6de3050180ec2f96ef00963d1beab8e1b47a mysqltest: At line 9: query 'ALTER TABLE t1 ADD (address VARCHAR(5000), city VARCHAR(20)), ALGORITHM=NOCOPY' failed: ER_ALTER_OPERATION_NOT_SUPPORTED (1845): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE The reason for the failure is that the maximum length of the column (5000 characters*3 bytes/character = 15000 characters) is more than the record payload size (somewhere around 8000 bytes for the default innodb_page_size=16k ). If I execute this with innodb_page_size=32k , then it will work. The maximum record size (for any innodb_page_size ) is 16383 bytes. I think that this is a bug in instant_alter_column_possible() , because as far as the .ibd file is concerned, the operation should be equivalent to the following, which will pass: ALTER TABLE t1 ADD (address TEXT, city VARCHAR (20)), ALGORITHM=NOCOPY;

Thirunarayanan Balathandayuthapani added a comment - 2024-03-14 14:04

https://github.com/MariaDB/server/pull/3127

Thirunarayanan Balathandayuthapani added a comment - 2024-03-14 14:04 https://github.com/MariaDB/server/pull/3127

Marko Mäkelä added a comment - 2024-03-15 12:17

Looks OK. Thank you.

Marko Mäkelä added a comment - 2024-03-15 12:17 Looks OK. Thank you.

People

Assignee:: Thirunarayanan Balathandayuthapani

Reporter:: Nilnandan Joshi

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2024-01-10 13:15

Updated:: 2024-07-07 19:20

Resolved:: 2024-03-15 12:35

Git Integration

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

MariaDB Server