Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.7, 10.4.12, 10.4(EOL)
-
None
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.
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
MDEV-11369 Instant add column for InnoDB
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
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.
I've added comments here https://github.com/MariaDB/server/commit/55ffda5600eac9ec76041cf7c3ceeefd55b55af4
OK to push after adding more tests.
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
It is possible that
MDEV-13134failed 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-11369andMDEV-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.