[MDEV-29813] REPLACE/IGNORE does not work with historical records in InnoDB - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: N/A
Fix Version/s: 10.11.1
Component/s: Storage Engine - InnoDB, Versioned Tables
Labels:
None

Description

I've seen somewhere in the review exchange a note about REPLACE which should work as possible and fail if not, don't remember anything about IGNORE, and can't find anything in the patch tests. Anyway, it's strange that the result is different for different engines, but if that's expected, please feel free to close, just check that the tests have it.

For InnoDB tables, INSERT IGNORE, REPLACE, LOAD IGNORE, LOAD REPLACE fail when duplicate key situation occurs (and fail with a rather unfortunate ER_FOREIGN_DUPLICATE_KEY_WITH_CHILD_INFO (not included in the test case, it's meant to be run with --force --force to get the complete result, and with --mysqld=--default-storage-engine=....

--source include/have_innodb.inc

# To make all engines equal in this regard

SET sql_mode='STRICT_ALL_TABLES';

# Create an outfile with two identical historical records

create or replace table t (a int) with system versioning;

set system_versioning_insert_history= on;

insert into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');

select a,row_start,row_end into outfile 'f' from t for system_time all;

# Run with default engine of your choice

create or replace table t (a int primary key) with system versioning;

load data infile 'f' replace into table t (a,row_start,row_end);

select a,row_start,row_end from t for system_time all;

create or replace table t (a int primary key) with system versioning;

load data infile 'f' ignore into table t (a,row_start,row_end);

select a,row_start,row_end from t for system_time all;

create or replace table t (a int primary key) with system versioning;

replace into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');

select a,row_start,row_end from t for system_time all;

create or replace table t (a int primary key) with system versioning;

insert ignore into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');

select a,row_start,row_end from t for system_time all;

# Cleanup

--let $datadir= `select @@datadir`

--remove_file $datadir/test/f

drop table t;

With InnoDB:

bb-10.11-MDEV-16546 32090722c7
MariaDB [test]> load data infile 'f' replace into table t (a,row_start,row_end);
ERROR 1761 (23000): Foreign key constraint for table 't', record '1' would lead to a duplicate entry in table 't', key 'PRIMARY'
MariaDB [test]> select a,row_start,row_end from t for system_time all;
Empty set (0.001 sec)
...
MariaDB [test]> load data infile 'f' ignore into table t (a,row_start,row_end);
ERROR 1761 (23000): Foreign key constraint for table 't', record '1' would lead to a duplicate entry in table 't', key 'PRIMARY'
MariaDB [test]> select a,row_start,row_end from t for system_time all;
Empty set (0.001 sec)
...
MariaDB [test]> replace into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
ERROR 1761 (23000): Foreign key constraint for table 't', record '1' would lead to a duplicate entry in table 't', key 'PRIMARY'
MariaDB [test]> select a,row_start,row_end from t for system_time all;
Empty set (0.001 sec)
...
MariaDB [test]> insert ignore into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
ERROR 1761 (23000): Foreign key constraint for table 't', record '1' would lead to a duplicate entry in table 't', key 'PRIMARY'
MariaDB [test]> select a,row_start,row_end from t for system_time all;
Empty set (0.001 sec)

With MyISAM:

MariaDB [test]> load data infile 'f' replace into table t (a,row_start,row_end);

Query OK, 3 rows affected (0.001 sec)

Records: 2  Deleted: 1  Skipped: 0  Warnings: 0

MariaDB [test]> select a,row_start,row_end from t for system_time all;

+---+----------------------------+----------------------------+

| a | row_start                  | row_end                    |

+---+----------------------------+----------------------------+

| 1 | 2022-01-01 00:00:00.000000 | 2023-01-01 00:00:00.000000 |

| 1 | 2022-01-01 00:00:00.000000 | 2022-10-17 19:25:03.949777 |

+---+----------------------------+----------------------------+

...

MariaDB [test]> load data infile 'f' ignore into table t (a,row_start,row_end);

Query OK, 1 row affected, 1 warning (0.001 sec)

Records: 2  Deleted: 0  Skipped: 1  Warnings: 1

MariaDB [test]> select a,row_start,row_end from t for system_time all;

+---+----------------------------+----------------------------+

| a | row_start                  | row_end                    |

+---+----------------------------+----------------------------+

| 1 | 2022-01-01 00:00:00.000000 | 2023-01-01 00:00:00.000000 |

+---+----------------------------+----------------------------+

...

MariaDB [test]> replace into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');

Query OK, 3 rows affected (0.001 sec)

Records: 2  Duplicates: 1  Warnings: 0

MariaDB [test]> select a,row_start,row_end from t for system_time all;

+---+----------------------------+----------------------------+

| a | row_start                  | row_end                    |

+---+----------------------------+----------------------------+

| 1 | 2022-01-01 00:00:00.000000 | 2023-01-01 00:00:00.000000 |

| 1 | 2022-01-01 00:00:00.000000 | 2022-10-17 19:25:04.048847 |

+---+----------------------------+----------------------------+

...

MariaDB [test]> insert ignore into t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');

Query OK, 1 row affected, 1 warning (0.002 sec)

Records: 2  Duplicates: 1  Warnings: 1

MariaDB [test]> select a,row_start,row_end from t for system_time all;

+---+----------------------------+----------------------------+

| a | row_start                  | row_end                    |

+---+----------------------------+----------------------------+

| 1 | 2022-01-01 00:00:00.000000 | 2023-01-01 00:00:00.000000 |

+---+----------------------------+----------------------------+

Attachments

Issue Links

is caused by

MDEV-16546 System versioning setting to allow history modification

Closed

MDEV-23644 Assertion on evaluating foreign referential action for self-reference in system versioned table

Closed

relates to

MDEV-25644 UPDATE not working properly on transaction precise system versioned table

Closed

Activity

Ascending order - Click to sort in descending order

Sergei Golubchik added a comment - 2022-10-17 21:31 - edited

MyISAM behaves correctly. InnoDB is wrong because of the following code in row0ins.cc, function row_ins_duplicate_error_in_clust():

          if (cursor->index->table->versioned()

              && entry->vers_history_row())

                  ulint trx_id_len;

                  byte *trx_id = rec_get_nth_field(

                          rec, offsets, n_unique,

                          &trx_id_len);

                  ut_ad(trx_id_len == DATA_TRX_ID_LEN);

                  if (trx->id == trx_read_trx_id(trx_id)) {

                          err = DB_FOREIGN_DUPLICATE_KEY;

Sergei Golubchik added a comment - 2022-10-17 21:31 - edited MyISAM behaves correctly. InnoDB is wrong because of the following code in row0ins.cc , function row_ins_duplicate_error_in_clust() : if (cursor->index->table->versioned() && entry->vers_history_row()) { ulint trx_id_len; byte *trx_id = rec_get_nth_field( rec, offsets, n_unique, &trx_id_len); ut_ad(trx_id_len == DATA_TRX_ID_LEN); if (trx->id == trx_read_trx_id(trx_id)) { err = DB_FOREIGN_DUPLICATE_KEY; } }

Aleksey Midenkov added a comment - 2023-01-10 20:09 - edited

This is where the problem happens.

#0  row_ins_duplicate_error_in_clust (flags=0, cursor=0x7fa445fad9c0, entry=0x7fa430059ae8, thr=0x7fa430975d20) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:2415

#1  0x000055f36cf99606 in row_ins_clust_index_entry_low (flags=0, mode=BTR_MODIFY_LEAF, index=0x7fa430058198, n_uniq=2, entry=0x7fa430059ae8, n_ext=0, thr=0x7fa430975d20) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:2808

#2  0x000055f36cf9dd97 in row_ins_clust_index_entry (index=0x7fa430058198, entry=0x7fa430059ae8, thr=0x7fa430975d20, n_ext=0) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:3236

#3  0x000055f36cfa63de in row_ins_index_entry (index=0x7fa430058198, entry=0x7fa430059ae8, thr=0x7fa430975d20) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:3373

#4  0x000055f36cfa5540 in row_ins_index_entry_step (node=0x7fa430975ac8, thr=0x7fa430975d20) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:3541

#5  0x000055f36cf9f008 in row_ins (node=0x7fa430975ac8, thr=0x7fa430975d20) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:3666

#6  0x000055f36cf9ebbd in row_ins_step (thr=0x7fa430975d20) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:3795

#7  0x000055f36cfcbcf3 in row_insert_for_mysql (mysql_rec=0x7fa430973448 "\377\001", prebuilt=0x7fa430975578, ins_mode=ROW_INS_NORMAL) at ../src/storage/innobase/row/row0mysql.cc:1308

#8  0x000055f36cc19633 in ha_innobase::write_row (this=0x7fa430974900, record=0x7fa430973448 "\377\001") at ../src/storage/innobase/handler/ha_innodb.cc:7833

#9  0x000055f36c34208c in handler::ha_write_row (this=0x7fa430974900, buf=0x7fa430973448 "\377\001") at ../src/sql/handler.cc:7584

#10 0x000055f36c6828ca in write_record (thd=0x7fa4300020f8, table=0x7fa430062368, info=0x7fa445faedc0, sink=0x0) at ../src/sql/sql_insert.cc:2203

#11 0x000055f36c6c5c0d in read_sep_field (thd=0x7fa4300020f8, info=..., table_list=0x7fa43002df10, fields_vars=..., set_fields=..., set_values=..., read_info=..., enclosed=..., skip_lines=0, ignore_check_option_errors=true) at ../src/sql/sql_load.cc:1183

#12 0x000055f36c6c344e in mysql_load (thd=0x7fa4300020f8, ex=0x7fa43002de88, table_list=0x7fa43002df10, fields_vars=..., set_fields=..., set_values=..., handle_duplicates=DUP_ERROR, ignore=true, read_file_from_client=false) at ../src/sql/sql_load.cc:688

#13 0x000055f36c6dc418 in mysql_execute_command (thd=0x7fa4300020f8, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:5025

#14 0x000055f36c6d0c1f in mysql_parse (thd=0x7fa4300020f8, rawbuf=0x7fa43002dd70 "load data infile 'mdev29813.txt' ignore into table t1 (a,row_start,row_end)", length=75, parser_state=0x7fa445fb1288) at ../src/sql/sql_parse.cc:8000

This is what current fix breaks (versioning.foreign):

#0  row_ins_duplicate_error_in_clust (flags=0, cursor=0x7f894c0555d0, entry=0x7f8928af5738, thr=0x7f8928aff3b8) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:2414

#1  0x0000561db0452606 in row_ins_clust_index_entry_low (flags=0, mode=BTR_MODIFY_LEAF, index=0x7f8928aa9a88, n_uniq=2, entry=0x7f8928af5738, n_ext=1, thr=0x7f8928aff3b8) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:2808

#2  0x0000561db0456d97 in row_ins_clust_index_entry (index=0x7f8928aa9a88, entry=0x7f8928af5738, thr=0x7f8928aff3b8, n_ext=1) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:3236

#3  0x0000561db04f1901 in row_upd_clust_rec_by_insert (node=0x7f8928aea410, index=0x7f8928aa9a88, thr=0x7f8928aff3b8, referenced=false, mtr=0x7f894c055e98) at ../src/storage/innobase/row/row0upd.cc:2319

#4  0x0000561db04efa06 in row_upd_clust_step (node=0x7f8928aea410, thr=0x7f8928aff3b8) at ../src/storage/innobase/row/row0upd.cc:2679

#5  0x0000561db04edb19 in row_upd (node=0x7f8928aea410, thr=0x7f8928aff3b8) at ../src/storage/innobase/row/row0upd.cc:2755

#6  0x0000561db04ed664 in row_upd_step (thr=0x7f8928aff3b8) at ../src/storage/innobase/row/row0upd.cc:2897

#7  0x0000561db048662c in row_update_for_mysql (prebuilt=0x7f8928ae9848) at ../src/storage/innobase/row/row0mysql.cc:1679

#8  0x0000561db00d36c4 in ha_innobase::update_row (this=0x7f8928ae8ff0, old_row=0x7f8928abcdb0 "\372\002", new_row=0x7f8928abcd88 "\372\002") at ../src/storage/innobase/handler/ha_innodb.cc:8577

#9  0x0000561daf7fb82f in handler::ha_update_row (this=0x7f8928ae8ff0, old_data=0x7f8928abcdb0 "\372\002", new_data=0x7f8928abcd88 "\372\002") at ../src/sql/handler.cc:7644

#10 0x0000561dafb259f7 in TABLE::delete_row (this=0x7f8928996c28) at /home/midenok/src/mariadb/10.6/src/sql/sql_delete.cc:285

#11 0x0000561dafb2130f in mysql_delete (thd=0x7f89280020f8, table_list=0x7f892802de30, conds=0x7f892802f420, order_list=0x7f8928007028, limit=18446744073709551614, options=0, result=0x0) at /home/midenok/src/mariadb/10.6/src/sql/sql_delete.cc:846

#12 0x0000561dafb9444b in mysql_execute_command (thd=0x7f89280020f8, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:4809

#13 0x0000561dafb89c1f in mysql_parse (thd=0x7f89280020f8, rawbuf=0x7f892802dd70 "delete from t1", length=14, parser_state=0x7f894c059288) at ../src/sql/sql_parse.cc:8000

#14 0x0000561dafb86f0f in dispatch_command (command=COM_QUERY, thd=0x7f89280020f8, packet=0x7f8928025cf9 "delete from t1", packet_length=14, blocking=true) at ../src/sql/sql_parse.cc:1894

The right solution is to restrict DB_FOREIGN_DUPLICATE_KEY to the proper conditions.

Aleksey Midenkov added a comment - 2023-01-10 20:09 - edited This is where the problem happens. #0 row_ins_duplicate_error_in_clust (flags=0, cursor=0x7fa445fad9c0, entry=0x7fa430059ae8, thr=0x7fa430975d20) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:2415 #1 0x000055f36cf99606 in row_ins_clust_index_entry_low (flags=0, mode=BTR_MODIFY_LEAF, index=0x7fa430058198, n_uniq=2, entry=0x7fa430059ae8, n_ext=0, thr=0x7fa430975d20) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:2808 #2 0x000055f36cf9dd97 in row_ins_clust_index_entry (index=0x7fa430058198, entry=0x7fa430059ae8, thr=0x7fa430975d20, n_ext=0) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:3236 #3 0x000055f36cfa63de in row_ins_index_entry (index=0x7fa430058198, entry=0x7fa430059ae8, thr=0x7fa430975d20) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:3373 #4 0x000055f36cfa5540 in row_ins_index_entry_step (node=0x7fa430975ac8, thr=0x7fa430975d20) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:3541 #5 0x000055f36cf9f008 in row_ins (node=0x7fa430975ac8, thr=0x7fa430975d20) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:3666 #6 0x000055f36cf9ebbd in row_ins_step (thr=0x7fa430975d20) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:3795 #7 0x000055f36cfcbcf3 in row_insert_for_mysql (mysql_rec=0x7fa430973448 "\377\001", prebuilt=0x7fa430975578, ins_mode=ROW_INS_NORMAL) at ../src/storage/innobase/row/row0mysql.cc:1308 #8 0x000055f36cc19633 in ha_innobase::write_row (this=0x7fa430974900, record=0x7fa430973448 "\377\001") at ../src/storage/innobase/handler/ha_innodb.cc:7833 #9 0x000055f36c34208c in handler::ha_write_row (this=0x7fa430974900, buf=0x7fa430973448 "\377\001") at ../src/sql/handler.cc:7584 #10 0x000055f36c6828ca in write_record (thd=0x7fa4300020f8, table=0x7fa430062368, info=0x7fa445faedc0, sink=0x0) at ../src/sql/sql_insert.cc:2203 #11 0x000055f36c6c5c0d in read_sep_field (thd=0x7fa4300020f8, info=..., table_list=0x7fa43002df10, fields_vars=..., set_fields=..., set_values=..., read_info=..., enclosed=..., skip_lines=0, ignore_check_option_errors=true) at ../src/sql/sql_load.cc:1183 #12 0x000055f36c6c344e in mysql_load (thd=0x7fa4300020f8, ex=0x7fa43002de88, table_list=0x7fa43002df10, fields_vars=..., set_fields=..., set_values=..., handle_duplicates=DUP_ERROR, ignore=true, read_file_from_client=false) at ../src/sql/sql_load.cc:688 #13 0x000055f36c6dc418 in mysql_execute_command (thd=0x7fa4300020f8, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:5025 #14 0x000055f36c6d0c1f in mysql_parse (thd=0x7fa4300020f8, rawbuf=0x7fa43002dd70 "load data infile 'mdev29813.txt' ignore into table t1 (a,row_start,row_end)", length=75, parser_state=0x7fa445fb1288) at ../src/sql/sql_parse.cc:8000 This is what current fix breaks (versioning.foreign): #0 row_ins_duplicate_error_in_clust (flags=0, cursor=0x7f894c0555d0, entry=0x7f8928af5738, thr=0x7f8928aff3b8) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:2414 #1 0x0000561db0452606 in row_ins_clust_index_entry_low (flags=0, mode=BTR_MODIFY_LEAF, index=0x7f8928aa9a88, n_uniq=2, entry=0x7f8928af5738, n_ext=1, thr=0x7f8928aff3b8) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:2808 #2 0x0000561db0456d97 in row_ins_clust_index_entry (index=0x7f8928aa9a88, entry=0x7f8928af5738, thr=0x7f8928aff3b8, n_ext=1) at /home/midenok/src/mariadb/10.6/src/storage/innobase/row/row0ins.cc:3236 #3 0x0000561db04f1901 in row_upd_clust_rec_by_insert (node=0x7f8928aea410, index=0x7f8928aa9a88, thr=0x7f8928aff3b8, referenced=false, mtr=0x7f894c055e98) at ../src/storage/innobase/row/row0upd.cc:2319 #4 0x0000561db04efa06 in row_upd_clust_step (node=0x7f8928aea410, thr=0x7f8928aff3b8) at ../src/storage/innobase/row/row0upd.cc:2679 #5 0x0000561db04edb19 in row_upd (node=0x7f8928aea410, thr=0x7f8928aff3b8) at ../src/storage/innobase/row/row0upd.cc:2755 #6 0x0000561db04ed664 in row_upd_step (thr=0x7f8928aff3b8) at ../src/storage/innobase/row/row0upd.cc:2897 #7 0x0000561db048662c in row_update_for_mysql (prebuilt=0x7f8928ae9848) at ../src/storage/innobase/row/row0mysql.cc:1679 #8 0x0000561db00d36c4 in ha_innobase::update_row (this=0x7f8928ae8ff0, old_row=0x7f8928abcdb0 "\372\002", new_row=0x7f8928abcd88 "\372\002") at ../src/storage/innobase/handler/ha_innodb.cc:8577 #9 0x0000561daf7fb82f in handler::ha_update_row (this=0x7f8928ae8ff0, old_data=0x7f8928abcdb0 "\372\002", new_data=0x7f8928abcd88 "\372\002") at ../src/sql/handler.cc:7644 #10 0x0000561dafb259f7 in TABLE::delete_row (this=0x7f8928996c28) at /home/midenok/src/mariadb/10.6/src/sql/sql_delete.cc:285 #11 0x0000561dafb2130f in mysql_delete (thd=0x7f89280020f8, table_list=0x7f892802de30, conds=0x7f892802f420, order_list=0x7f8928007028, limit=18446744073709551614, options=0, result=0x0) at /home/midenok/src/mariadb/10.6/src/sql/sql_delete.cc:846 #12 0x0000561dafb9444b in mysql_execute_command (thd=0x7f89280020f8, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:4809 #13 0x0000561dafb89c1f in mysql_parse (thd=0x7f89280020f8, rawbuf=0x7f892802dd70 "delete from t1", length=14, parser_state=0x7f894c059288) at ../src/sql/sql_parse.cc:8000 #14 0x0000561dafb86f0f in dispatch_command (command=COM_QUERY, thd=0x7f89280020f8, packet=0x7f8928025cf9 "delete from t1", packet_length=14, blocking=true) at ../src/sql/sql_parse.cc:1894 The right solution is to restrict DB_FOREIGN_DUPLICATE_KEY to the proper conditions.

MariaDB Server

REPLACE/IGNORE does not work with historical records in InnoDB

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration