Auto-create: If XA transaction is rolled back the created partitions are kept empty and not reused

-- source include/have_partition.inc

-- source include/have_sequence.inc

-- source include/have_innodb.inc

SET @save_persistent=@@GLOBAL.innodb_stats_persistent;

SET GLOBAL innodb_stats_persistent=0;

set @@system_versioning_alter_history = keep;

create table t2 (

  x int,

  y int

) engine=InnoDB with system versioning partition by system_time interval 1 minute auto;

insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5);

delete from t2 where x=1;

sleep 62;

update t2 set x=10 where y=4;

select * from t2 partition (p0);

select * from t2 partition (p1);

select partition_name, table_rows from information_schema.partitions where table_name = 't2';

XA START 'xid';

sleep 62;

update t2 set y=213 where x=2;

sleep 62;

update t2 set y=919 where x=5;

sleep 62;

update t2 set y=2828 where x=5;

select * from t2 partition (p0);

select * from t2 partition (p1);

select * from t2 partition (p2);

select * from t2 partition (p3);

select * from t2 partition (p4);

select partition_name, table_rows from information_schema.partitions where table_name = 't2';

XA END 'xid';

XA ROLLBACK 'xid';

select * from t2 partition (p0);

select * from t2 partition (p1);

select * from t2 partition (p2);

select * from t2 partition (p3);

select * from t2 partition (p4);

select partition_name, table_rows from information_schema.partitions where table_name = 't2';

sleep 62;

update t2 set y=9009 where x=2;

select * from t2 partition (p0);

select * from t2 partition (p1);

select * from t2 partition (p2);

select * from t2 partition (p3);

select * from t2 partition (p4);

select partition_name, table_rows from information_schema.partitions where table_name = 't2';

drop table t2;

SET GLOBAL innodb_stats_persistent=@save_persistent;

SET @save_persistent=@@GLOBAL.innodb_stats_persistent;

SET GLOBAL innodb_stats_persistent=0;

set @@system_versioning_alter_history = keep;

create table t2 (

x int,

y int

) engine=InnoDB with system versioning partition by system_time interval 1 minute auto;

insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5);

delete from t2 where x=1;

update t2 set x=10 where y=4;

select * from t2 partition (p0);

x	y

1	1

select * from t2 partition (p1);

x	y

4	4

select partition_name, table_rows from information_schema.partitions where table_name = 't2';

partition_name	table_rows

p0	1

p1	1

pn	4

XA START 'xid';

update t2 set y=213 where x=2;

update t2 set y=919 where x=5;

update t2 set y=2828 where x=5;

select * from t2 partition (p0);

x	y

1	1

select * from t2 partition (p1);

x	y

4	4

select * from t2 partition (p2);

x	y

2	2

select * from t2 partition (p3);

x	y

5	5

select * from t2 partition (p4);

x	y

5	919

select partition_name, table_rows from information_schema.partitions where table_name = 't2';

partition_name	table_rows

p0	1

p1	1

p2	1

p3	1

p4	1

pn	4

XA END 'xid';

XA ROLLBACK 'xid';

select * from t2 partition (p0);

x	y

1	1

select * from t2 partition (p1);

x	y

4	4

select * from t2 partition (p2);

x	y

select * from t2 partition (p3);

x	y

select * from t2 partition (p4);

x	y

select partition_name, table_rows from information_schema.partitions where table_name = 't2';

partition_name	table_rows

p0	1

p1	1

p2	0

p3	0

p4	0

pn	4

update t2 set y=9009 where x=2;

select * from t2 partition (p0);

x	y

1	1

select * from t2 partition (p1);

x	y

4	4

select * from t2 partition (p2);

x	y

select * from t2 partition (p3);

x	y

select * from t2 partition (p4);

x	y

select partition_name, table_rows from information_schema.partitions where table_name = 't2';

partition_name	table_rows

p0	1

p1	1

p2	0

p3	0

p4	0

p5	1

pn	4

drop table t2;

SET GLOBAL innodb_stats_persistent=@save_persistent;