[MDEV-23516] can't drop table for SYSTEM VERSIONING Created: 2020-08-20  Updated: 2020-08-30  Resolved: 2020-08-30

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.5.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: hiller1 Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

MariaDB 10.5.5


 Description    

CREATE TABLE `t4` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `name` varchar(100) DEFAULT NULL,
 
  `ts` timestamp(6) GENERATED ALWAYS AS ROW START,
 
  `te` timestamp(6) GENERATED ALWAYS AS ROW END,
 
  PRIMARY KEY (`id`,`te`),
 
  PERIOD FOR SYSTEM_TIME (`ts`, `te`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING


mysql> set system_versioning_alter_history = 'KEEP';
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> alter table t4 DROP PERIOD FOR SYSTEM_TIME;
 
ERROR 4125 (HY000): Wrong parameters for `t4`: missing 'DROP COLUMN `ts`, DROP COLUMN `te`'
 
mysql> 
mysql> ALTER TABLE t4 DROP SYSTEM VERSIONING;
 
ERROR 4125 (HY000): Wrong parameters for `t4`: missing 'DROP COLUMN `ts`'
 
mysql>



 Comments   
Comment by Sergei Golubchik [ 2020-08-30 ]

This is correct. In your first ALTER statement you tried to drop the PERIOD only. It would result in the invalid table definition, with columns GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END but without a PERIOD. In your second ALTER statement you tried to drop the system versioning, while keeping the period. It is invalid too.

You need to drop system versioning, the period, and both generated columns in one statement. To succeed your ALTER TABLE must transition from one valid table structure to another valid table structure. The error message told you what ALTER TABLE operations were missing.

Generated at Thu Feb 08 09:22:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.