[MDEV-26581] Table 't' is marked as crashed and should be repaired on UPDATE and sql_mode changes Created: 2021-09-10  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.9
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Roel Van de Paar Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: not-10.10+

Issue Links:
Relates
relates to MDEV-11691 CSV: Table marked as crashed and shou... Confirmed

 Description   

Slightly alike to MDEV-11691, but this one is not in upstream and the SQL required is different.

SET sql_mode='';
 
CREATE TABLE t0 (a TIMESTAMP) ENGINE=CSV;
 
INSERT INTO t0 VALUES (0);
 
RENAME TABLE t0 TO t;
 
SET sql_mode='traditional';
 
UPDATE t SET a=0;

Leads to:

10.7.0 1bc82aaf0a7746c0921a94034aff2d51f0d75cd0 (Debug)

 
2021-09-10  8:26:50 4 [ERROR] mysqld: Table 't' is marked as crashed and should be repaired



 Comments   
Comment by Oleksandr Byelkin [ 2023-09-27 ]

correct test case

SET sql_mode='';
 
CREATE TABLE t0 (a TIMESTAMP) ENGINE=CSV;
 
INSERT INTO t0 VALUES (0);
 
RENAME TABLE t0 TO t;
 
SET sql_mode='traditional';
 
--error ER_TRUNCATED_WRONG_VALUE
 
UPDATE t SET a=0;

Comment by Roel Van de Paar [ 2023-09-27 ]

The previously seen corruption is no longer present. However, versions up to 10.9 now give ERROR 1292, and 10.10 and onwards show ERROR 1178:

10.4.32 47f0135d7aba57d160d1d95ff7f1f6b7bb07c2fb (Debug)

 
10.4.32-dbg>UPDATE t SET a=0;
 
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t`.`a` at row 1

...

10.9.8 3e0009dc3a771e4dbf2fa4a4cf87e750453fb2eb (Debug)

 
10.9.8-dbg>UPDATE t SET a=0;
 
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column `test`.`t`.`a` at row 1

10.10.7 11c69177e9eebaab65984ee06e7b2873b3067d5f (Debug)

 
10.10.7-dbg>CREATE TABLE t0 (a TIMESTAMP) ENGINE=CSV;
 
ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

...

10.11.6 e39ed5d76fea4b6247db0db8d878cb2209e44455 (Debug)

 
10.11.6-dbg>CREATE TABLE t0 (a TIMESTAMP) ENGINE=CSV;
 
ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

...

11.3.0 905c3d61e18ae6222d0d195c43d335046eec65d9 (Debug)

 
11.3.0-dbg>CREATE TABLE t0 (a TIMESTAMP) ENGINE=CSV;
 
ERROR 1178 (42000): The storage engine for the table doesn't support nullable column

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