MySQL Bugs: #94741: SQL mode error catching the wrong updated column

Bug #94741	SQL mode error catching the wrong updated column
Submitted:	22 Mar 2019 5:26	Modified:	2 May 2019 13:21
Reporter:	Juan Carlos Polanco Aguilar	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.18	OS:	CentOS (7.4)
Assigned to:		CPU Architecture:	x86 (2vCPU x 4GB RAM)
Tags:	Sql mode strict STRICT_TRANS_TABLES

Description:
when enabling sql modes by session, we recognized the validation is on a different column than expected.

TABLE:
CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  `action` tinyint(4) NOT NULL DEFAULT '0',
  `convent` tinyint(4) NOT NULL DEFAULT '0',
  `count` int(10) unsigned NOT NULL DEFAULT '0',
  `sum1` int(10) unsigned NOT NULL DEFAULT '0',
  `sum2` int(10) unsigned NOT NULL DEFAULT '0',
  `group_id` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `date1` date DEFAULT NULL,
  `messg1` varchar(255) DEFAULT NULL,
  `messg_date` date DEFAULT NULL, 
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> set session sql_mode= 'ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION';

mysql> SELECT @@sql_mode;
----------------------------------------------------------------------------
@@sql_mode
----------------------------------------------------------------------------
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

mysql> update `test1` set 
`action` = 0, 
`convent` = 0, 
`count` = 0, 
`sum1` = 0, 
`sum2` = 0, 
`group_id` = 0, 
`status` = 0, 
`date1` = null, 
`messg1` = null, 
`messg_date` = null 
where (`easy_id` = #########);

ERROR 1048 (23000): Column 'status' cannot be null

How to repeat:
CREATE TABLE `test1` ...

set session sql_mode= 'ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION';

update `test1` set 
`action` = 0, 
`convent` = 0, 
`count` = 0, 
`sum1` = 0, 
`sum2` = 0, 
`group_id` = 0, 
`status` = 0, 
`date1` = null, 
`messg1` = null, 
`messg_date` = null 
where (`easy_id` = #########);

Suggested fix:
So far, if I execute: FLUSH TABLES, the error won't reproduce.

mysql> FLUSH TABLES;

mysql> update `test1` set 
`action` = 0, 
`convent` = 0, 
`count` = 0, 
`sum1` = 0, 
`sum2` = 0, 
`group_id` = 0, 
`status` = 0, 
`date1` = null, 
`messg1` = null, 
`messg_date` = null 
where (`easy_id` = ######);

QUERY OK

HI,

Thank you for your report.

However, I truly fail to see what is the bug here ......

Your sql_mode prohibits zero dates and zero in dates, but zeroes are not inserted in your DATE columns. NULL is.

Hence, I do not think that this is a bug !!!!

Thank you for your response Sinisa Milivojevic.

However, the error message was not for zero dates nor zero in dates. The error was for a column:
`status` tinyint(4) NOT NULL DEFAULT '0',

and the error message:
ERROR 1048 (23000): Column 'status' cannot be null

while I updated:
update `test1` set 
...
`status` = 0, 
...

but the updated data was not NULL. It was a zero.

Hope you could understand my explanation.

Thank you again for your response!

Hi,

First of all, always try using latest release for the given version, which in your case is 5.7.25.

Second, please try not to use quotes for defaults in numeric values. 

Try without quotes and 5.7.25 and let us know the result.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".