Bug #94741 SQL mode error catching the wrong updated column
Submitted: 22 Mar 5:26 Modified: 2 May 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

[22 Mar 5:26] Juan Carlos Polanco Aguilar
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
[26 Mar 15:06] Sinisa Milivojevic
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 !!!!
[27 Mar 0:47] Juan Carlos Polanco Aguilar
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!
[27 Mar 13:29] Sinisa Milivojevic
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.
[28 Apr 1:00] Bugs System
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".