Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.6, 10.3(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4
Description
when trying to add a column to a big innodb table we get an error saying the column type can not be changed with ALGORITHM=INSTANT, but we are trying to add a column.
MariaDB [regressiondb]> show create table testAndSuite_status\G
|
*************************** 1. row ***************************
|
Table: testAndSuite_status
|
Create Table: CREATE TABLE `testAndSuite_status` (
|
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`topo_id` int(10) unsigned NOT NULL,
|
`vmTopo_id` int(10) unsigned NOT NULL DEFAULT 0,
|
`extraKey_id` int(10) NOT NULL DEFAULT 0,
|
`testAndSuite_id` int(10) unsigned NOT NULL,
|
`status` enum('PASSED','FAILED','SKIPPED','UNKNOWN','OUT','GHOST') COLLATE latin1_bin NOT NULL DEFAULT 'GHOST',
|
`rotten` tinyint(1) NOT NULL DEFAULT 0,
|
`duration` varchar(45) COLLATE latin1_bin DEFAULT NULL,
|
`regressRun_id_Pass` int(11) DEFAULT NULL,
|
`regressRun_id_Fail` int(11) DEFAULT NULL,
|
`origin` enum('scanResult','scanGash','admin','gui','expand') COLLATE latin1_bin NOT NULL,
|
`registered` tinyint(1) NOT NULL DEFAULT 0,
|
`changedDate` timestamp NOT NULL DEFAULT current_timestamp(),
|
`remarks` varchar(140) COLLATE latin1_bin DEFAULT NULL,
|
`passCount` int(11) NOT NULL DEFAULT 0,
|
`failCount` int(11) NOT NULL DEFAULT 0,
|
`bossKey` tinyint(1) NOT NULL,
|
`aggregate` int(2) NOT NULL DEFAULT 0 COMMENT '0=normal, 2=agg, 1=pointing to agg',
|
PRIMARY KEY (`id`),
|
KEY `testAndSuite` (`testAndSuite_id`),
|
KEY `topo` (`topo_id`),
|
KEY `regressRun_id_Fail` (`regressRun_id_Fail`),
|
KEY `regressRun_id_Pass` (`regressRun_id_Pass`),
|
KEY `rotten` (`rotten`),
|
KEY `extrakey` (`extraKey_id`),
|
KEY `vmTopo_id` (`vmTopo_id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=2717022889 DEFAULT CHARSET=latin1 COLLATE=latin1_bin
|
1 row in set (0.000 sec)
|
|
|
MariaDB [regressiondb]> alter table testAndSuite_status ADD master_tas_id INT(11) UNSIGNED NULL DEFAULT NULL, ALGORITHM=INSTANT;
|
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY
|
|
|
MariaDB [regressiondb]> select VERSION();
|
+--------------------+
|
| VERSION() |
|
+--------------------+
|
| 10.4.6-MariaDB-log |
|
+--------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [regressiondb]> show variables like '%row_format%';
|
+---------------------------+---------+
|
| Variable_name | Value |
|
+---------------------------+---------+
|
| innodb_default_row_format | dynamic |
|
+---------------------------+---------+
|
1 row in set (0.002 sec)
|
Attachments
Issue Links
- relates to
-
MDEV-11424 Instant ALTER TABLE of failure-free record format changes
-
- Closed
-
-
-
- Closed
-
- mentioned in
-
Page Failed to load
Activity
I am also having this happen, in an amazon rds instance with version 10.4.18. It would be nice to know if there's something about the particular table or replication or something that causes this behavior?
After doing (in a restored snapshot) `alter table tablename force;`, I can add columns without locking. Is there anything I can do to make it work in my main db?
This seems to be a problem with (some, but not all, and I would love to know what the underlying problem is) tables built (or last fully altered) under an earlier version of mariadb/innodb.
{{MariaDB [shiftboard_com_2]> alter online table coverage_snapshot add publish_date_utc datetime;
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Cannot change column type. Try LOCK=SHARED
MariaDB [shiftboard_com_2]> alter table coverage_snapshot force;
Query OK, 459586 rows affected (1 min 33.09 sec)
Records: 459586 Duplicates: 0 Warnings: 0
MariaDB [shiftboard_com_2]> alter online table coverage_snapshot add publish_date_utc datetime;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [shiftboard_com_2]> select version();
---------------------
| version() |
---------------------
| 10.4.13-MariaDB-log |
---------------------
1 row in set (0.00 sec)}}
If that is correct, is there a way to determine which tables will have this problem?
Sorry, I had overlooked this bug report. According to the error message it looks like the data type of the changedDate timestamp column could be changed. Data type changes (other than some changes on VARCHAR length or collation) cannot be supported by the native ALTER TABLE implementation in InnoDB.
Unfortunately, in MySQL 5.6, when "microsecond timestamps" were implemented, no new SQL keyword was added for this. Instead, there is a parameter mysql56_temporal_format that specifies which data type the keyword timestamp is referring to. It is rather messy, because by executing multiple ALTER TABLE…ADD…timestamp…, ALGORITHM=COPY with different values of that parameter, you could create a table that contains multiple timestamp values.
Similar to MDEV-15225, I think that the proper fix would be to introduce SQL keywords for the pre-MySQL 5.6 data types and not silently change any underlying data types to ‘new’ format on ALTER TABLE. I am reassigning this to our data type expert for consideration.
Any update on this one?
its blocking us at the moment