[MDEV-27076] Slow log table corruption using only SET commands and SELECT Created: 2021-11-17  Updated: 2023-11-28

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

Type: Bug Priority: Major
Reporter: Roel Van de Paar Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 1
Labels: corruption, slow_query_log, timestamp, timezones


 Description   

Testcase may be slightly sporadic.

SET GLOBAL slow_query_log=ON;
 
SET GLOBAL log_output='TABLE';
 
SET slow_query_log=ON;
 
SET long_query_time=0.000001;
 
SET @@time_zone="+01:00";
 
SET TIMESTAMP=1;
 
SET @@time_zone='+02:00';
 
SELECT * FROM mysql.slow_log;

Leads to:

10.8.0 bc57ff7cf79504a198d4752b4748340a4afd886c (Optimized)

 
10.8.0-opt>SELECT * FROM mysql.slow_log;
 
ERROR 1194 (HY000): Table 'slow_log' is marked as crashed and should be repaired

10.8.0 bc57ff7cf79504a198d4752b4748340a4afd886c (Debug)

 
2021-11-18 10:23:49 4 [ERROR] mysqld: Table 'slow_log' is marked as crashed and should be repaired

Bug confirmed present in:
MariaDB: 10.2.42 (dbg), 10.2.42 (opt), 10.3.33 (dbg), 10.3.33 (opt), 10.4.23 (dbg), 10.4.23 (opt), 10.5.14 (dbg), 10.5.14 (opt), 10.6.6 (dbg), 10.6.6 (opt), 10.7.2 (dbg), 10.7.2 (opt), 10.8.0 (dbg), 10.8.0 (opt)

Bug (or feature/syntax) confirmed not present in:
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.36 (dbg), 5.7.36 (opt), 8.0.27 (dbg), 8.0.27 (opt)

 Comments   
Comment by Roel Van de Paar [ 2022-10-15 ]

Additional testcase

SET GLOBAL log_output='TABLE', SESSION time_zone='+00:00';
 
SET GLOBAL log_queries_not_using_indexes=TRUE, GLOBAL slow_query_log=ON, SESSION slow_query_log=ON, SESSION log_slow_filter=DEFAULT;
 
SET TIMESTAMP=1000;
 
CREATE TABLE t (c INT) ENGINE=InnoDB;
 
DELETE FROM t;
 
SET SESSION time_zone='+01:00';
 
SELECT * FROM mysql.slow_log;

Leads to:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug)

 
ERROR 1194 (HY000) at line 7 in file: 'in.sql': Table 'slow_log' is marked as crashed and should be repaired

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug)

 
2022-10-15 16:08:32 4 [ERROR] mysqld: Table 'slow_log' is marked as crashed and should be repaired

Bug confirmed present in:
MariaDB: 10.3.37 (dbg), 10.3.37 (opt), 10.4.27 (dbg), 10.4.27 (opt), 10.5.18 (dbg), 10.5.18 (opt), 10.6.10 (dbg), 10.6.10 (opt), 10.7.6 (dbg), 10.7.6 (opt), 10.8.5 (dbg), 10.8.5 (opt), 10.9.3 (dbg), 10.9.3 (opt), 10.10.2 (dbg), 10.10.2 (opt), 10.11.0 (dbg), 10.11.0 (opt)

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