[MDEV-33214] Table is getting rebuild with ALTER TABLE ADD COLUMN Created: 2024-01-10  Updated: 2024-01-16

Status: Confirmed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3, 11.4, 10.5.21
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2, 11.3

Type: Bug Priority: Major
Reporter: Nilnandan Joshi Assignee: Thirunarayanan Balathandayuthapani
Resolution: Unresolved Votes: 0
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.



 Comments   
Comment by Hartmut Holzgraefe [ 2024-01-10 ]

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 ...

Comment by Marko Mäkelä [ 2024-01-10 ]

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)?

Comment by Nilnandan Joshi [ 2024-01-11 ]

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)

Comment by Marko Mäkelä [ 2024-01-11 ]

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;

Generated at Thu Feb 08 10:37:14 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.