CODEWITHSUNDEEP
mysqlmariadb
Kirill Zhirnov
Kirill Zhirnov

Reputation: 375

MariaDB shows warnings instead of errors

MariaDB version: 10.6.4-MariaDB-1:10.6.4+maria~focal

SQL:

insert ignore into `step` (is_draft, created_by) values ('1', 2);

Got warnings:

MariaDB [kite]> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'course_id' doesn't have a default value
*************************** 2. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'title' doesn't have a default value
*************************** 3. row ***************************
  Level: Warning
   Code: 1452
Message: Cannot add or update a child row: a foreign key constraint fails (`kite`.`step`, CONSTRAINT `step_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE)
3 rows in set (0.000 sec)

I'm expecting to have an error if a constraint fails. In fact, it used to, but not now. How to enable errors instead of warnings?

Googled about SQL_MODE and tried different combinations, but no luck:

MariaDB [kite]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE\G
*************************** 1. row ***************************
       @@SQL_MODE: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
@@GLOBAL.SQL_MODE: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Thanks in advance!

UPDATE: Looks like the problem is in ignore keyword. If I remove - it works as expected.

Upvotes: 2

Views: 617

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562631

If you want errors instead of warnings, then don't use ignore.

https://mariadb.com/kb/en/insert-ignore/ says:

By using the IGNORE keyword all errors are converted to warnings, which will not stop inserts of additional rows.

Upvotes: 2

Related Questions