[MDEV-29754] Unexpected ER_NOT_FORM_FILE or ER_VERS_FIELD_WRONG_TYPE upon creating system-versioned table under mysql56_temporal_format=0 Created: 2022-10-10  Updated: 2023-07-20  Resolved: 2023-07-20

Status: Closed
Project: MariaDB Server
Component/s: Temporal Types, Versioned Tables
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Aleksey Midenkov
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-28545 MyISAM reorganize partition corrupt o... Closed
Relates
relates to MDEV-16542 Fix ALTER TABLE FORCE to upgrade temp... Closed
relates to MDEV-28545 MyISAM reorganize partition corrupt o... Closed

 Description   

Apparently timestamp-based versioning is incompatible with pre-mysql56_temporal_format. It's probably all right, but the errors produced due to this incompatibility are rather confusing and inconsistent.

SET @mysql56= @@mysql56_temporal_format;
 
SET GLOBAL mysql56_temporal_format= 0;
 
CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
 
CREATE TABLE t2 LIKE t1;
 
 
 
# Cleanup
 
DROP TABLE IF EXISTS t1, t2;
 
SET GLOBAL mysql56_temporal_format= @mysql56;

10.4 5f26f500

 
mysqltest: At line 4: query 'CREATE TABLE t2 LIKE t1' failed: 1033: Incorrect information in file: './test/t2.frm'

So, t1 gets created without a complaint, but t2 with supposedly same structure fails.
Reproducible on 10.4+. 10.3 creates t2 all right, although what will happen to it afterwards, I can't tell.

If ROW START/END columns are defined explicitly, the table creation fails right away, but the error it produces is confusing.

SET @mysql56= @@mysql56_temporal_format;
 
SET GLOBAL mysql56_temporal_format= 0;
 
CREATE TABLE t1 (a INT, s TIMESTAMP(6) AS ROW START, e TIMESTAMP(6) AS ROW END, PERIOD FOR SYSTEM_TIME(s,e)) WITH SYSTEM VERSIONING;
 
 
 
# Cleanup
 
DROP TABLE IF EXISTS t1;
 
SET GLOBAL mysql56_temporal_format= @mysql56;


mysqltest: At line 3: query 'CREATE TABLE t1 (a INT, s TIMESTAMP(6) AS ROW START, e TIMESTAMP(6) AS ROW END, PERIOD FOR SYSTEM_TIME(s,e)) WITH SYSTEM VERSIONING' failed: 4110: `s` must be of type TIMESTAMP(6) for system-versioned table `t1`

This variation is reproducible on 10.3+.
Generated at Thu Feb 08 10:11:02 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.