drop table if exists t1;
set @save_sql_mode=@@sql_mode;
set sql_mode = "STRICT_ALL_TABLES";
Warnings:
Warning	3135	'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
CREATE TABLE `t1` (                                         
`id` int(11) NOT NULL,                                              
`c1` int(11) NOT NULL,                                          
`c2` varchar(100) DEFAULT NULL,                                     
`c3` int(11) DEFAULT NULL,                                          
`c4` int(11) NOT NULL DEFAULT '5',                                           
`c5` varchar(100) DEFAULT NULL,                                     
PRIMARY KEY (`id`),                                                 
KEY `idx_c4` (`c4`),                                                
KEY `idx_c5` (`c5`)                                                 
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
insert into t1 values(1,1,'abc',1,1,'abc');
insert into t1 values(2,2,'cde',2,2,'cde');
insert into t1 values(3,3,'xyz',3,3,'xyz');
alter table t1 drop primary key, add primary key(c1,c4), ALGORITHM  = INPLACE, LOCK=DEFAULT;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `c1` int(11) NOT NULL,
  `c2` varchar(100) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) NOT NULL DEFAULT '5',
  `c5` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`c1`,`c4`),
  KEY `idx_c4` (`c4`),
  KEY `idx_c5` (`c5`)
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
select * from t1;
id	c1	c2	c3	c4	c5
1	1	abc	1	1	abc
2	2	cde	2	2	cde
3	3	xyz	3	3	xyz
alter table t1 drop primary key, add primary key(c4),ALGORITHM  = INPLACE, LOCK=DEFAULT;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `c1` int(11) NOT NULL,
  `c2` varchar(100) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) NOT NULL DEFAULT '5',
  `c5` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`c4`),
  KEY `idx_c4` (`c4`),
  KEY `idx_c5` (`c5`)
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
insert into t1 values(4,4,null,4,4,null);
select * from t1;
id	c1	c2	c3	c4	c5
1	1	abc	1	1	abc
2	2	cde	2	2	cde
3	3	xyz	3	3	xyz
4	4	NULL	4	4	NULL
alter table t1 drop primary key, add primary key(c5),ALGORITHM  = INPLACE, LOCK=DEFAULT;
ERROR 22004: Invalid use of NULL value
delete from t1 where id=4;
select * from t1;
id	c1	c2	c3	c4	c5
1	1	abc	1	1	abc
2	2	cde	2	2	cde
3	3	xyz	3	3	xyz
alter table t1 drop primary key, add primary key(c5),ALGORITHM  = INPLACE, LOCK=DEFAULT;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `c1` int(11) NOT NULL,
  `c2` varchar(100) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) NOT NULL DEFAULT '5',
  `c5` varchar(100) NOT NULL,
  PRIMARY KEY (`c5`),
  KEY `idx_c4` (`c4`),
  KEY `idx_c5` (`c5`)
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
select * from t1;
id	c1	c2	c3	c4	c5
1	1	abc	1	1	abc
2	2	cde	2	2	cde
3	3	xyz	3	3	xyz
drop table t1;
CREATE TABLE `t1` (                                         
`id` int(11) NOT NULL,                                              
`c1` int(11) NOT NULL,                                          
`c2` varchar(100) DEFAULT NULL,                                     
`c3` int(11) DEFAULT NULL,                                          
`c4` int(11) NOT NULL DEFAULT '5',                                           
`c5` varchar(100) DEFAULT NULL,                                     
KEY `idx_c4` (`c4`),                                                
UNIQUE KEY `idx_c5` (`c5`)
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
insert into t1 values(1,1,'abc',1,1,'abc');
insert into t1 values(2,2,'cde',2,2,'cde');
insert into t1 values(3,3,'xyz',3,3,'xyz');
alter table t1 add primary key idx_pk(id),ALGORITHM  = INPLACE, LOCK=DEFAULT;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `c1` int(11) NOT NULL,
  `c2` varchar(100) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) NOT NULL DEFAULT '5',
  `c5` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_c5` (`c5`),
  KEY `idx_c4` (`c4`)
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
select * from t1;
id	c1	c2	c3	c4	c5
1	1	abc	1	1	abc
2	2	cde	2	2	cde
3	3	xyz	3	3	xyz
ALTER TABLE t1 RENAME INDEX idx_c4 TO key_c4, RENAME INDEX idx_c5 TO key_c5, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `c1` int(11) NOT NULL,
  `c2` varchar(100) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) NOT NULL DEFAULT '5',
  `c5` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_c5` (`c5`),
  KEY `key_c4` (`c4`)
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
ALTER TABLE t1 ADD COLUMN c6 CHAR(1), RENAME INDEX key_c4 TO idx_c4, RENAME INDEX key_c5 TO idx_c5, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `c1` int(11) NOT NULL,
  `c2` varchar(100) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) NOT NULL DEFAULT '5',
  `c5` varchar(100) DEFAULT NULL,
  `c6` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_c5` (`c5`),
  KEY `idx_c4` (`c4`)
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
alter table t1 drop primary key, ALGORITHM  = INPLACE, LOCK=DEFAULT;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: With INPLACE DDL, XEngine only allows that DROP PRIMARY KEY is combined with ADD PRIMARY KEY. Try ALGORITHM=COPY.
alter table t1 drop primary key;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `c1` int(11) NOT NULL,
  `c2` varchar(100) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) NOT NULL DEFAULT '5',
  `c5` varchar(100) DEFAULT NULL,
  `c6` char(1) DEFAULT NULL,
  UNIQUE KEY `idx_c5` (`c5`),
  KEY `idx_c4` (`c4`)
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
select * from t1;
id	c1	c2	c3	c4	c5	c6
1	1	abc	1	1	abc	NULL
2	2	cde	2	2	cde	NULL
3	3	xyz	3	3	xyz	NULL
drop table t1;
set sql_mode = @save_sql_mode;