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
createorreplacetable t (a int) with system versioning;
set system_versioning_insert_history= on;
insertinto 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 withdefault engine of your choice
createorreplacetable t (a intprimarykey) with system versioning;
load data infile 'f'replaceintotable t (a,row_start,row_end);
select a,row_start,row_end from t for system_time all;
createorreplacetable t (a intprimarykey) with system versioning;
load data infile 'f'ignoreintotable t (a,row_start,row_end);
select a,row_start,row_end from t for system_time all;
createorreplacetable t (a intprimarykey) with system versioning;
replaceinto 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;
createorreplacetable t (a intprimarykey) with system versioning;
insertignoreinto 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
droptable t;
With InnoDB:
bb-10.11-MDEV-16546 32090722c7
MariaDB [test]> load data infile 'f'replaceintotable t (a,row_start,row_end);
ERROR 1761 (23000): Foreignkeyconstraintfortable't', record '1' would lead to a duplicate entry intable'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'ignoreintotable t (a,row_start,row_end);
ERROR 1761 (23000): Foreignkeyconstraintfortable't', record '1' would lead to a duplicate entry intable't', key'PRIMARY'
MariaDB [test]> select a,row_start,row_end from t for system_time all;
Empty set (0.001 sec)
...
MariaDB [test]> replaceinto t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
ERROR 1761 (23000): Foreignkeyconstraintfortable't', record '1' would lead to a duplicate entry intable't', key'PRIMARY'
MariaDB [test]> select a,row_start,row_end from t for system_time all;
Empty set (0.001 sec)
...
MariaDB [test]> insertignoreinto t (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
ERROR 1761 (23000): Foreignkeyconstraintfortable't', record '1' would lead to a duplicate entry intable'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'replaceintotable 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;
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 - - 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;
}
}
#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 - - 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.
People
Sergei Golubchik
Elena Stepanova
Votes:
0Vote for this issue
Watchers:
3Start watching this issue
Dates
Created:
Updated:
Resolved:
Git Integration
Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.
MyISAM behaves correctly. InnoDB is wrong because of the following code in row0ins.cc, function row_ins_duplicate_error_in_clust():
&& 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);
err = DB_FOREIGN_DUPLICATE_KEY;
}
}