Locking timeout caused by auto-creation affects original DML

Reproduce

--source include/have_partition.inc

--source include/have_innodb.inc

set timestamp= unix_timestamp('2000-01-01 00:00:00');

create or replace table t1 (x int primary key) with system versioning engine innodb

partition by system_time interval 1 hour auto;

insert into t1 values (1);

start transaction;

insert into t1 values (2);

--connect con1, localhost, root

set lock_wait_timeout= 1;

set innodb_lock_wait_timeout= 1;

set timestamp= unix_timestamp('2000-01-01 01:00:01');

--error 0, ER_LOCK_WAIT_TIMEOUT

update t1 set x= x + 122 where x = 1;

--disconnect con1

--connection default

select * from t1;

# cleanup

drop table t1;

Result

select * from t1;

x

1

2

Expected

select * from t1;

x

2

123

Notes

The reasons for this are:

• Do not disrupt main business process (the history is auxiliary
  service);

• Consequences are non-fatal (history is not lost, but comes into wrong
  partition; fixed by partitioning rebuild);

• There is more freedom for application to fail in this case or
  not: it may read warning info and find corresponding error number.

• While non-failing command is easy to handle by an application and
  fail it, the opposite is hard to handle: there is no automatic
  actions to fix failed command and retry, DBA intervention is
  required and until then application is non-functioning.

Side notes

Research have been made against standard replication for both variants unversioned -> versioned and versioned -> unversioned. It appears that both are not affected by the problem above.

unversioned -> versioned generally is not affected because slave has one execution thread. Though Galera is affected because of multiple execution threads. Standard replication may be affected only in custom cases.

versioned -> unversioned is not affected because master error means slave does not execute this command.