I reproduced on 10.2.7-MariaDB-debug.

set sql_mode='';

CREATE TABLE t1 (i int not null, PRIMARY KEY (i));

INSERT into t1 values (1);

CREATE TABLE t2 (

  i int not null primary key,

  vi int not null,

  m int,

  UNIQUE KEY (vi),

  CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE);

INSERT into t2 VALUES (1, 1, 100);

INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3;

Warning (Code 1364): Field 'vi' doesn't have a default value

Error (Code 1452): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `cc` FOREIGN KEY (`vi`) REFERENCES `t1` (`i`) ON DELETE CASCADE ON UPDATE CASCADE)

INSERT into t1 values (0);

INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3;

select * from t2;

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

| i | vi | m    |

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

| 1 |  1 |    3 |

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

Query works as expected if column `vi`(foreign key) is not declared as NOT NULL or if we insert 0 in the first table, then query does not produce error. I guess, this is because after mysql to tries insert, field 'vi'=0 (https://mariadb.com/kb/en/mariadb/insert/ --If a NOT NULL column without a DEFAULT value is not referenced, an empty value will be inserted (for example, 0 for INTEGER columns and ” for CHAR columns)

no error on 10.1.25-MariaDB-debug

The difference in behavior between 10.2 and 10.1 does not come from the server, it comes from InnoDB 5.7 vs 5.6. The same can be observed with MySQL 5.7 vs MySQL 5.6.

In addition to sql_mode being irrelevant here as test cases above confirm, innodb_strict_mode does not make a difference either.

10.2 behavior could probably be explained by [NOT] NULLability and absence of default value, as Alice noted above. Some more details can be found here in Sergei's comment.

However, even in 10.2 there is inconsistency which makes me think something might not work as designed.

If we take the test case above as a base,

--source include/have_innodb.inc

set sql_mode='';

set innodb_strict_mode=0;

CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB;

INSERT into t1 values (1);

CREATE TABLE t2 (

  i int not null primary key,

  vi int not null,

  m int,

  UNIQUE KEY (vi),

  CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB;

INSERT into t2 VALUES (1, 1, 100);

INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3;

It causes the error, as described:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `cc` FOREIGN KEY (`vi`) REFERENCES `t1` (`i`) ON DELETE CASCADE ON UPDATE CASCADE)

But if we only change the vi key from UNIQUE to non-unique – no error anymore:

set sql_mode='';

set innodb_strict_mode=0;

CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB;

INSERT into t1 values (1);

CREATE TABLE t2 (

  i int not null primary key,

  vi int not null,

  m int,

  KEY (vi),

  CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB;

INSERT into t2 VALUES (1, 1, 100);

INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3;

MariaDB [test]> INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3;

Query OK, 2 rows affected, 1 warning (0.03 sec)

It shouldn't matter here whether the key is unique or not, and yet it does.

I'll assign it to marko first to check whether it's expected behavior, and if it is so, we should probably document it.

elenst, I cannot remember any change in MySQL 5.7 that would change this behaviour on purpose.
Maybe the behaviour change was a side effect of a refactoring such as the upd_cascade_t that was part of
Bug #16244691 SERVER GONE AWAY ERROR OCCURS DEPENDING ON THE NUMBER OF TABLE/KEY RELATIONS

Given that a simple test case exists, I would suggest searching for the change in MySQL 5.7 by invoking git bisect.

If my guess about upd_cascade_t is correct, then 5.7.2 should be the first affected version, and 5.7.1 should be clean. Tests with those versions could be the starting point of the "git bisect" exercise.

serg Why in 10.1 generated value is 1 but on 10.2 0?

CREATE TABLE `v` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT v values (1);

CREATE TABLE `vp` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`v_id` int(10) unsigned NOT NULL,

`p_id` int(10) unsigned NOT NULL,

`ppp` varchar(255) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `IDX_vp_uniq` (`v_id`,`p_id`),

KEY `FK_vp_v` (`v_id`),

CONSTRAINT `FK_vp_v` FOREIGN KEY (`v_id`) REFERENCES `v` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT vp VALUES (12, 1, 100, 'text12');

INSERT INTO `vp` (`id`,`ppp`) VALUES (12, 'test12-2') ON DUPLICATE KEY UPDATE `ppp` = VALUES(`ppp`);

Warnings:

Warning	1364	Field 'v_id' doesn't have a default value

Warning	1364	Field 'p_id' doesn't have a default value

SHOW WARNINGS;

Level	Code	Message

Warning	1364	Field 'v_id' doesn't have a default value

Warning	1364	Field 'p_id' doesn't have a default value

SELECT * FROM vp;

id	v_id	p_id	ppp

12	1	100	test12-2

DROP TABLE vp, v;

CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB;

INSERT into t1 values (1);

CREATE TABLE t2 (

i int not null primary key,

vi int not null,

m int,

UNIQUE KEY (vi),

CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB;

INSERT into t2 VALUES (1, 1, 100);

INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3;

Warnings:

Warning	1364	Field 'vi' doesn't have a default value

SHOW WARNINGS;

Level	Code	Message

Warning	1364	Field 'vi' doesn't have a default value

SELECT * FROM t2;

i	vi	m

1	1	3

DROP TABLE t2,t1;

CREATE TABLE t1 (i int PRIMARY KEY) ENGINE=InnoDB;

INSERT into t1 values (1);

CREATE TABLE t2 (

i int not null primary key,

vi int not null,

m int,

KEY (vi),

CONSTRAINT `cc` FOREIGN KEY (vi) REFERENCES t1 (i) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB;

INSERT into t2 VALUES (1, 1, 100);

INSERT INTO t2 (i,m) VALUES (1, 2) ON DUPLICATE KEY UPDATE m=3;

Warnings:

Warning	1364	Field 'vi' doesn't have a default value

SHOW WARNINGS;

Level	Code	Message

Warning	1364	Field 'vi' doesn't have a default value

SELECT * FROM t2;

i	vi	m

1	1	3

DROP TABLE t2, t1;

innodb.jan 'xtradb'                      [ pass ]    204

jplindst, where 10.1 generates 1, but 10.2 — 0? You have many tests and many 1's in the test above.

marko Found the offending changes on 5.7. There is actually two of them:

• commit 25781c154396dbbc21023786aa3be070057d6999

  Author: Annamalai Gurusami <annamalai.gurusami@oracle.com> Mon Feb 24 10:30:03 2014

  Committer: Annamalai Gurusami <annamalai.gurusami@oracle.com> Mon Feb 24 10:30:03 2014

  Bug #17604730 ASSERTION: *CURSOR->INDEX->NAME == TEMP_INDEX_PREFIX

  Problem:

  When INSERT ... ON DUPLICATE UPDATE or REPLACE statements are used, then

  after encountering a DB_DUPLICATE_KEY error, we continue to process all

  the unique secondary indexes to place the necessary gap locks. The

  problem is in the following scenario:

  1. The table has one primary index, one unique secondary index and

  one non-unique secondary index.

  2. The INSERT ... ON DUPLICATE UPDATE ... is executed on the table.

  3. Insert into the clustered index reported DB_DUPLICATE_KEY. This

  error information is saved. We proceed to take gap locks in all

  unique secondary indexes.

  4. Insert into the unique secondary index reported DB_LOCK_WAIT.

  5. Step 4 is repeated from a higher layer row_ins(). When this is

  done, the earlier error information saved in step 3 is lost.

  6. Next instead of taking just gap locks or skipping non-unique

  secondary indexes, because of loss of information regarding the

  error already saved, an actual insert is performed on the non-unique

  secondary index. This triggers the assert.

  Solution:

  Save the error information in a non-local location so that it is not lost.

  rb#4723 approved by Kevin.

• commit 95d29c99f01882ffcc2259f62b3163f9b0e80c75

Author:	Marko Mäkelä <marko.makela@oracle.com>  Tue Nov 27 11:12:13 2012

Committer:	Marko Mäkelä <marko.makela@oracle.com>  Tue Nov 27 11:12:13 2012

Bug#15920445 INNODB REPORTS ER_DUP_KEY BEFORE CREATE UNIQUE INDEX COMPLETED

There is a phase during online secondary index creation where the index has

been internally completed inside InnoDB, but does not 'officially' exist yet.

We used to report ER_DUP_KEY in these situations, like this:

ERROR 23000: Can't write; duplicate key in table 't1'

What we should do is to let the 'offending' operation complete, but report an

error to the

ALTER TABLE t1 ADD UNIQUE KEY (c2):

ERROR HY000: Index c2 is corrupted

(This misleading error message should be fixed separately:

Bug#15920713 CREATE UNIQUE INDEX REPORTS ER_INDEX_CORRUPT INSTEAD OF DUPLICATE)

row_ins_sec_index_entry_low(): flag the index corrupted instead of

reporting a duplicate, in case the index has not been published yet.

rb:1614 approved by Jimmy Yang

Problem is that we found the duplicate key error on primary key but then also we found DB_NO_REFERENCED_ROW as search for v_id value 0 fails from index IDX_vp_uniq. This leads wrong error code returned from row_ins().

https://github.com/MariaDB/server/commit/769166e9ddea723143630da16323a583d0f3eca4

Please address my review comment and request another review.

commit 0c4d11e8199f4c7a9e41fc3f86819219c21ddc4d
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Thu Nov 16 11:05:24 2017 +0200

MDEV-13206: INSERT ON DUPLICATE KEY UPDATE foreign key fail

This is caused by following change:

commit 95d29c99f01882ffcc2259f62b3163f9b0e80c75
Author: Marko Mäkelä <marko.makela@oracle.com>
Date: Tue Nov 27 11:12:13 2012 +0200

Bug#15920445 INNODB REPORTS ER_DUP_KEY BEFORE CREATE UNIQUE INDEX COMPLETED

There is a phase during online secondary index creation where the index has
been internally completed inside InnoDB, but does not 'officially' exist yet.
We used to report ER_DUP_KEY in these situations, like this:

ERROR 23000: Can't write; duplicate key in table 't1'

What we should do is to let the 'offending' operation complete, but report an
error to the
ALTER TABLE t1 ADD UNIQUE KEY (c2):

ERROR HY000: Index c2 is corrupted
(This misleading error message should be fixed separately:
Bug#15920713 CREATE UNIQUE INDEX REPORTS ER_INDEX_CORRUPT INSTEAD OF DUPLICATE)

row_ins_sec_index_entry_low(): flag the index corrupted instead of
reporting a duplicate, in case the index has not been published yet.

rb:1614 approved by Jimmy Yang

Problem is that after we have found duplicate key on primary key
we continue to get necessary gap locks in secondary indexes to
block concurrent transactions from inserting the searched records.
However, search from unique index used in foreign key constraint
could return DB_NO_REFERENCED_ROW if INSERT .. ON DUPLICATE KEY UPDATE
does not contain value for foreign key column. In this case
we should return the original DB_DUPLICATE_KEY error instead
of DB_NO_REFERENCED_ROW.

Consider as a example following:

create table child(a int not null primary key,
b int not null,
c int,
unique key (b),
foreign key (b) references
parent (id)) engine=innodb;

insert into child values (1,1,2);

insert into child(a) values (1) on duplicate key update c = 3;

Now primary key value 1 naturally causes duplicate key error that will be
stored on node->duplicate. If there was no duplicate key error, we should
return the actual no referenced row error. As value for column b used in
both unique key and foreign key is not provided, server uses 0 as a
search value. This is naturally, not found leading to DB_NO_REFERENCED_ROW.
But, we should update the row with primay key value 1 anyway as
requested by on duplicate key update clause.

The alleged cause of this bug (95d29c99f01882ffcc2259f62b3163f9b0e80c75) is already present in MariaDB 10.0 and 10.1. It was committed by myself in MySQL 5.6.10.

However, this error is claimed to affect 10.2 only. A much more likely cause of this bug is Annamalai Gurusami’s change (25781c154396dbbc21023786aa3be070057d6999) in MySQL 5.7.4. That was applied to MariaDB 10.2.2 along with many other changes from MySQL 5.7.9. That change was initially disabled in MDEV-17073 (10.2.19) and later removed in MDEV-17614 (10.2.27). An MDEV-17614 after-merge fix removed the problematic code that was changed in the MDEV-13206 fix.