Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4(EOL), 10.5, 10.6, 10.11, 11.4, 11.7(EOL)
-
None
Description
In the test case below, the query
DELETE HISTORY FROM t BEFORE SYSTEM_TIME @@timestamp |
when it is executed as a plain statement, causes an error 4079 ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION; but if it is executed as a prepared statement where @@timestamp comes as a parameter, then instead of an error it only produces a warning 1292 ER_TRUNCATED_WRONG_VALUE.
The discrepancy may seem minor, but it can cause a trouble when it happens in a replication context: being executed (with a warning), it gets written into the binary log – of course, not as a prepared statement with a parameter, but as a resulting statement – and thus it causes an error on the replica.
Note: Use of @@timestamp is not important here, any constant or variable with a fractional part leads to the same problem; I'm using @@timestamp to make the test case more realistic: while BEFORE SYSTEM_TIME 12.3 looks artificial, using @@timestamp there is a fair mistake which is easy to make, at least I myself did it a number of times.
create table t (a int) with system versioning; |
insert into t values (1),(2); |
|
|
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
|
delete history from t before system_time @@timestamp; |
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
|
execute immediate "delete history from t before system_time @@timestamp"; |
|
|
execute immediate "delete history from t before system_time ?" using @@timestamp; |
|
10.5 7f55c610608d45a0958f502a8b428c6d37f86692 |
delete history from t before system_time @@timestamp; |
ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME' |
execute immediate "delete history from t before system_time @@timestamp"; |
ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME' |
execute immediate "delete history from t before system_time ?" using @@timestamp; |
Warnings:
|
Warning 1292 Truncated incorrect datetime value: '1731785506.669454' |
Test case demonstrating the effect on replication:
--source include/have_binlog_format_mixed.inc
|
--source include/master-slave.inc
|
|
|
create table t (a int) with system versioning; |
insert into t values (1),(2); |
|
|
execute immediate "delete history from t before system_time ?" using @@timestamp; |
|
|
--sync_slave_with_master
|
|
|
# Cleanup
|
--connection master
|
drop table t; |
--source include/rpl_end.inc |
Last_Errno 4079
|
Last_Error Error 'Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'' on query. Default database: 'test'. Query: 'delete history from t before system_time 1731785600.849373'
|
SHOW BINLOG EVENTS IN 'master-bin.000001';
|
Log_name Pos Event_type Server_id End_log_pos Info
|
master-bin.000001 4 Format_desc 1 256 Server ver: 10.5.28-MariaDB-debug-log, Binlog ver: 4
|
master-bin.000001 256 Gtid_list 1 285 []
|
master-bin.000001 285 Binlog_checkpoint 1 329 master-bin.000001
|
master-bin.000001 329 Gtid 1 371 GTID 0-1-1
|
master-bin.000001 371 Query 1 483 use `test`; create table t (a int) with system versioning
|
master-bin.000001 483 Gtid 1 525 BEGIN GTID 0-1-2
|
master-bin.000001 525 Query 1 624 use `test`; insert into t values (1),(2)
|
master-bin.000001 624 Query 1 701 COMMIT
|
master-bin.000001 701 Gtid 1 743 BEGIN GTID 0-1-3
|
master-bin.000001 743 Query 1 876 use `test`; delete history from t before system_time 1731785600.849373
|
master-bin.000001 876 Query 1 957 COMMIT
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Fix Version/s | 11.5 [ 29506 ] |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 11.4 [ 29301 ] | |
| Fix Version/s | 11.7 [ 29815 ] | |
| Affects Version/s | 10.5 [ 23123 ] | |
| Affects Version/s | 10.6 [ 24028 ] | |
| Affects Version/s | 10.11 [ 27614 ] | |
| Affects Version/s | 11.4 [ 29301 ] | |
| Affects Version/s | 11.7 [ 29815 ] | |
| Affects Version/s | 11.5(EOL) [ 29506 ] | |
| Assignee | Elena Stepanova [ elenst ] | Oleksandr Byelkin [ sanja ] |
| Description |
{{delete history from t before system_time 1714569105}} causes an error, however the same executed as a parameterized PS works (with a warning) on the primary, gets written into binlog and makes replication abort.
Reproducible, needs finalizing. {code:sql} --source include/master-slave.inc create table t (a int) with system versioning; insert into t values (1),(2); execute immediate "delete history from t before system_time ?" using @@timestamp; --sync_slave_with_master {code} |
In the test case below, the query
{code:sql} DELETE HISTORY FROM t BEFORE SYSTEM_TIME @@timestamp {code} when it is executed as a plain statement, causes an *error* 4079 {{ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION}}; but if it is executed as a prepared statement where {{@@timestamp}} comes as a parameter, then instead of an error it only produces a *warning* 1292 {{ER_TRUNCATED_WRONG_VALUE}}. The discrepancy may seem minor, but it can cause a trouble when it happens in a replication context: being executed (with a warning), it gets written into the binary log -- of course, not as a prepared statement with a parameter, but as a resulting statement -- and thus it causes an error on the replica. _Note: Use of {{@@timestamp}} is not important here, any constant or variable with a fractional part leads to the same problem; I'm using {{@@timestamp}} to make the test case more realistic: while {{BEFORE SYSTEM_TIME 12.3}} looks artificial, using {{@@timestamp}} there is a fair mistake which is easy to make, at least I myself did it a number of times._ {code:sql} create table t (a int) with system versioning; insert into t values (1),(2); --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION delete history from t before system_time @@timestamp; --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION execute immediate "delete history from t before system_time @@timestamp"; execute immediate "delete history from t before system_time ?" using @@timestamp; {code} {code:sql|title=10.5 7f55c610608d45a0958f502a8b428c6d37f86692} delete history from t before system_time @@timestamp; ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME' execute immediate "delete history from t before system_time @@timestamp"; ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME' execute immediate "delete history from t before system_time ?" using @@timestamp; Warnings: Warning 1292 Truncated incorrect datetime value: '1731785506.669454' {code} Test case demonstrating the effect on replication: {code:sql} --source include/have_binlog_format_mixed.inc --source include/master-slave.inc create table t (a int) with system versioning; insert into t values (1),(2); execute immediate "delete history from t before system_time ?" using @@timestamp; --sync_slave_with_master # Cleanup --connection master drop table t; --source include/rpl_end.inc {code} {noformat} Last_Errno 4079 Last_Error Error 'Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'' on query. Default database: 'test'. Query: 'delete history from t before system_time 1731785600.849373' {noformat} {noformat} SHOW BINLOG EVENTS IN 'master-bin.000001'; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 4 Format_desc 1 256 Server ver: 10.5.28-MariaDB-debug-log, Binlog ver: 4 master-bin.000001 256 Gtid_list 1 285 [] master-bin.000001 285 Binlog_checkpoint 1 329 master-bin.000001 master-bin.000001 329 Gtid 1 371 GTID 0-1-1 master-bin.000001 371 Query 1 483 use `test`; create table t (a int) with system versioning master-bin.000001 483 Gtid 1 525 BEGIN GTID 0-1-2 master-bin.000001 525 Query 1 624 use `test`; insert into t values (1),(2) master-bin.000001 624 Query 1 701 COMMIT master-bin.000001 701 Gtid 1 743 BEGIN GTID 0-1-3 master-bin.000001 743 Query 1 876 use `test`; delete history from t before system_time 1731785600.849373 master-bin.000001 876 Query 1 957 COMMIT {noformat} |
| Summary | [Draft] Replication aborts upon DELETE HISTORY in prepared statement | Parameterized PS converts error to warning, causes replication problems |
| Fix Version/s | 11.7(EOL) [ 29815 ] |