MariaDB shows errors instead of warnings since update 10.1 to 10.5

Question

System 1 and System 2 have been identical. Since updating the System 1, MariaDB throws errors in system 1 (no default, string instead of int) which have been warnings before.

No settings were changed manually. Are there settings which potentially were changed during the update? How would I check strict typing or anything related?

System 1

Server version: 10.5.15-MariaDB-0+deb11u1 - Debian 11
Protocol version: 10
Database client version: libmysql - mysqlnd 8.0.23
SELECT @@SQL_MODE; STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

System 2

Server version: 10.1.48-MariaDB-0+deb9u2 - Debian 9.13
Protocol version: 10
Database client version: libmysql - mysqlnd 8.0.13
SELECT @@SQL_MODE; NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Below the context which creates the different outcomes on system 1 and system 2

DROP TABLE IF EXISTS `testtable`;
CREATE TABLE `testtable` (
  `id` int(11) NOT NULL,
  `testid` int(11) NOT NULL,
  `testint` int(11) NOT NULL DEFAULT 0,
  `testtext` longtext COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `testtable` (`id`, `testid`, `testint`, `testtext`) VALUES
(1, 1,  1542629089, '');

ALTER TABLE `testtable`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `testtable`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

Query 1

INSERT INTO `testtable` ( `testid`,`testint` ) VALUES ("19", "0");

Response System 1

INSERT INTO `testtable` ( `testid`,`testint` ) VALUES ("19", "0");

MySQL said: Documentation
#1364 - Field 'testtext' doesn't have a default value

Response System 2

1 row inserted.
Inserted row id: 11 (Query took 0.0001 seconds.)
INSERT INTO `testtable` ( `testid`,`testint` ) VALUES ("19", "0");
[ Edit inline ] [ Edit ] [ Create PHP code ]
Warning: #1364 Field 'testtext' doesn't have a default value

Query 2

INSERT INTO `testtable` ( `testid`,`testint`, `testtext` ) VALUES ("20", "", "");

Response System 1

INSERT INTO `testtable` ( `testid`,`testint`, `testtext` ) VALUES ("20", "", "");

MySQL said: Documentation
#1366 - Incorrect integer value: '' for column `wafl_client_r4apps`.`testtable`.`testint` at row 1

Response System 2


1 row inserted.
Inserted row id: 12 (Query took 0.0001 seconds.)
INSERT INTO `testtable` ( `testid`,`testint`, `testtext` ) VALUES ("20", "", "");

Warning: #1366 Incorrect integer value: '' for column 'testint' at row 1

10.1.48 (eoled 2 years ago). – Georg Richter Sep 20 '22 at 20:51 — Georg Richter, Sep 20 '22 at 20:51

score 1 · Accepted Answer · answered Sep 20 '22 at 13:08

As documentation states, MariaDB 10.2.4 introduced STRICT_TRANS_TABLES besides others.

STRICT_TRANS_TABLES

Strict mode.
Statements with invalid or missing data are aborted and rolled back, except that for non-transactional storage engines and statements affecting multiple rows where the invalid or missing data is not the first row, MariaDB will convert the invalid value to the closest valid value, or, if a value is missing, insert the column default value. Default since MariaDB 10.2.4.

Check your sql_mode SELECT @@SQL_MODE;

Update your sql_mode to the modes you want to keep. optionally global here

SET GLOBAL sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

see also: [Is it safe for me to turn off STRICT_TRANS_TABLES?](https://stackoverflow.com/a/54837026/724039) — Luuk, Sep 20 '22 at 13:18

MariaDB shows errors instead of warnings since update 10.1 to 10.5

1 Answers1