[MDEV-21693] ALGORITHM=INSTANT does not work for partitioned tables Created: 2020-02-09  Updated: 2020-08-25  Resolved: 2020-02-28

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Partitioning, Storage Engine - InnoDB
Affects Version/s: 10.4.7, 10.4.12, 10.4
Fix Version/s: 10.4.13

Type: Bug Priority: Major
Reporter: Valerii Kravchuk Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-11369 Instant add column for InnoDB Closed
relates to MDEV-13134 Introduce ALTER TABLE attributes ALGO... Closed
relates to MDEV-15562 Instant DROP COLUMN or changing the o... Closed

 Description   

KB article (https://mariadb.com/kb/en/innodb-online-ddl-operations-with-the-instant-alter-algorithm/#alter-table-modify-column) does not list any restrictions related to partitioned tables, while in reality ALGORITHM=INSTANT can not be applied to them.

Consider the following primitive example:

MariaDB [test]> create table tpa(id int, c1 varchar(10)) partition by range(id) (partition p1 values less than (10), partition p2 values less than (1000));
 
Query OK, 0 rows affected (0,310 sec)
 
 
 
MariaDB [test]> ALTER TABLE tpa MODIFY COLUMN c1 VARCHAR(30), LOCK=NONE, ALGORITHM=INSTANT;
 
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY
 
MariaDB [test]> ALTER TABLE tpa remove partitioning;                            
Query OK, 0 rows affected (0,757 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> ALTER TABLE tpa MODIFY COLUMN c1 VARCHAR(30), LOCK=NONE, ALGORITHM=INSTANT;
 
Query OK, 0 rows affected (0,076 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.4.12-MariaDB |
 
+-----------------+
 
1 row in set (0,000 sec)

As soon as remove partitioning, ALTER works as expected.

So, please, either document this limitation or fix the bug in the code that does not allow to do INSTANT alters for partitioned tables even in a simplest case like presented above.

 Comments   
Comment by Marko Mäkelä [ 2020-02-25 ]

It is possible that MDEV-13134 failed to adjust some code in ha_partition.
A new change in 10.4 is that some restrictions regarding extending VARCHAR columns was lifted. But I think that we should test and potentially fix 10.3 as well.

Note: For MDEV-11369 and MDEV-15562, I think that we must invoke handler::check_if_supported_inplace_alter() on each partition. Depending on the history of the data files, some partitions might support an instant ADD/DROP column operation, while others would require that the table be rebuilt. Currently, the underlying code in ha_innobase assumes that each partition will be handled in the same way. Basically, if some partitions return HA_ALTER_INPLACE_INSTANT while others return something else, then we should set the FORCE flag (ALTER_RECREATE_TABLE) and execute handler::check_if_supported_inplace_alter() on each partition again.

Comment by Thirunarayanan Balathandayuthapani [ 2020-02-26 ]

Patch is in bb-10.4-MDEV-21693

Comment by Marko Mäkelä [ 2020-02-26 ]

The patch looks good to me. kevg, because it is based on your code changes, please take a look.

thiru, please file&fix a separate bug to ensure that starting with 10.3, if one partition needs to be rebuilt, a rebuild of all partitions will be forced. I think that ha_innobase::commit_inplace_alter_table() assumes that all partitions will be treated in a similar way.

Comment by Eugene Kosov (Inactive) [ 2020-02-26 ]

I've added comments here https://github.com/MariaDB/server/commit/55ffda5600eac9ec76041cf7c3ceeefd55b55af4

OK to push after adding more tests.

Comment by Matthias Leich [ 2020-02-26 ]

Results of RQG test campaign with broad functional coverage for InnoDB (conf/mariadb/InnoDB_standard.cc)
on origin/bb-10.4-MDEV-21693 55ffda5600eac9ec76041cf7c3ceeefd55b55af4 2020-02-26T15:49:49+05:30
compiled with debug and claiming to be a 10.4.13-MariaDB-debug-log
STATISTICS Frequency – extra_info
STATISTICS 1 – 'STATUS_ALARM' occurs in other trees too, extreme rare in general
STATISTICS 1 – 'STATUS_ALARM--MDEV-21564-Rebirth'
occurs in other trees too. Per JIRA MDEV-21564 is closed. But tests other than the one in JIRA replay roughly the same assert.
STATISTICS 1 – 'STATUS_DATABASE_CORRUPTION--TBR-215'
occurs in other trees too, to be reported when getting into focus or becoming frequent enough
STATISTICS 1 – 'STATUS_ENVIRONMENT_FAILURE--MDEV-21564-Rebirth'
STATUS_ENVIRONMENT_FAILURE and STATUS_ALARM are the same
STATISTICS 1408 – 'STATUS_OK'
STATISTICS 1 – 'STATUS_SERVER_CRASHED' crash outside InnoDB code
STATISTICS 4 – 'STATUS_SERVER_CRASHED--MDEV-16686'
STATISTICS 10 – 'STATUS_SERVER_CRASHED--MDEV-17843'
STATISTICS 1 – 'STATUS_SERVER_CRASHED--MDEV-19130'
STATISTICS 17 – 'STATUS_SERVER_CRASHED--MDEV-19555'
STATISTICS 1 – 'STATUS_SERVER_CRASHED--MDEV-20618'
STATISTICS 1 – 'STATUS_SERVER_CRASHED--TBR-56'
occurs in other trees too, to be reported when getting into focus or becoming frequent enough
STATISTICS 222 – 'batch_limit'
stopped by RQG tools because assigned runtime for RQG testing campaign reached
STATISTICS 17 – 'rqg_limit' (weakness in the RQG tools)

IMHO

  • the results above are acceptable
  • some tests which stresses especially partitioned tables and DDL with ALGORITHM=INSTANT could be derived
    from conf/mariadb/table_stress_innodb_nocopy.yy but does not exist in the moment
Generated at Thu Feb 08 09:09:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.