| Bug #101877 | inserting skip check with default value '0000-00-00 00:00:00' in strict sql_mode | ||
|---|---|---|---|
| Submitted: | 5 Dec 2020 9:37 | Modified: | 5 Dec 2020 10:54 |
| Reporter: | yuxiang jiang (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S4 (Feature request) |
| Version: | 5.7.30 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution, sql_mode default value relication | ||
[5 Dec 2020 9:41]
yuxiang jiang
Tencent(Cloud-Native Database Team, CDB&CynosDB) -MySQL (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-bugfix-inserting-skip-check-with-default-value-0000-.patch (application/octet-stream, text), 4.46 KiB.
[5 Dec 2020 10:54]
MySQL Verification Team
Hello yuxiang jiang, Thank you for the report and contribution. regards, Umesh
Description: When sql_mode is strict， binlog format is row and row image is full， insertion will skip check default validation of unset column. So default values like '0000-00-00 00:00:00' will be passed to storage engine and writen to binlog with strict mode in event. Slave recieves the row insert event and reports error during execution the event. How to repeat: set sql_mode=0; CREATE TABLE `tsm2` ( `f1` int(1) , `f2` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; set sql_mode=1436549120; insert into tsm2 (f1) value (1); #should report error, but not insert into tsm2 (f1,f2) value (1, '0000-00-00 00:00:00'); #report error Suggested fix: Store write_set in TABLE object before function TABLE::mark_columns_per_binlog_row_image set all fields bit. And using the stored bitmap for validate_default_values_of_unset_fields.