| Bug #113769 | Generated column json binary format is incorrent | ||
|---|---|---|---|
| Submitted: | 26 Jan 2024 2:42 | Modified: | 29 Feb 2024 8:11 |
| Reporter: | hao wu (OCA) | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.7.44 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 Jan 2024 8:11]
MySQL Verification Team
Hello hao wu,
Thank you for the report and feedback.
Please note that per Oracle's Lifetime Support policy, as of October 25, 2023, MySQL 5.7 is covered under Oracle Sustaining Support. Thus, Users are encouraged to upgrade to MySQL 8.0. - More details at https://www.mysql.com/support/eol-notice.html
I quickly tried to reproduce the issue:
rm -rf 113783/
bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/113783 --log-error-verbosity=3
bin/mysqld_safe --no-defaults --basedir=$PWD --datadir=$PWD/113783 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/113783/log.err --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 --log-bin=binlog --server-id=1 2>&1 &
-
bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE db1;
USE db1;
CREATE TABLE `test` (
`col01` bigint NOT NULL AUTO_INCREMENT,
`col02` bigint NOT NULL,
`clo03` json DEFAULT NULL,
`clo04` json DEFAULT NULL,
`col05` bigint NOT NULL,
`clo06` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`col07` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`col08` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`col09` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`col10` bit(1) NOT NULL DEFAULT b'0',
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> USE db1;
`col11` datetime NOT NULL DEFAULT '9999-12-31 23:59:59',
`col12` int NOT NULL DEFAULT '1',
`col13` json GENERATED ALWAYS AS (json_merge_preserve(ifnull(json_extract(`clo03`,_utf8mb4'$.*'),json_array()),ifnull(json_extract(`clo04`,_utf8mb4'$.*'),json_array()))) VIRTUAL,
`col14` json GENERATED ALWAYS AS (json_extract(`col13`,_utf8mb4'$[0]')) VIRTUAL,
PRIMARY KEY (`col05`,`col01`),
UNIQUE KEY `uid_orderIdOwnerId` (`col02`,`col05`),
UNIQUE KEY `idx_id` (`col01`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11802 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `db1`.`test`(col01,col02,clo03,col05,clo06,col07,col08,col09,col10,col11,col12) VALUES (123,123456,'{"123": 10}',111,'ABC','2024-01-23 00:00:01','ABCABC','2024-01-23 00:00:00',0,'9999-12-31 23:59:59',2);
Database changed
mysql>
mysql> CREATE TABLE `test` (
-> `col01` bigint NOT NULL AUTO_INCREMENT,
-> `col02` bigint NOT NULL,
-> `clo03` json DEFAULT NULL,
-> `clo04` json DEFAULT NULL,
-> `col05` bigint NOT NULL,
-> `clo06` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
-> `col07` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> `col08` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
-> `col09` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> `col10` bit(1) NOT NULL DEFAULT b'0',
-> `col11` datetime NOT NULL DEFAULT '9999-12-31 23:59:59',
-> `col12` int NOT NULL DEFAULT '1',
-> `col13` json GENERATED ALWAYS AS (json_merge_preserve(ifnull(json_extract(`clo03`,_utf8mb4'$.*'),json_array()),ifnull(json_extract(`clo04`,_utf8mb4'$.*'),json_array()))) VIRTUAL,
-> `col14` json GENERATED ALWAYS AS (json_extract(`col13`,_utf8mb4'$[0]')) VIRTUAL,
-> PRIMARY KEY (`col05`,`col01`),
-> UNIQUE KEY `uid_orderIdOwnerId` (`col02`,`col05`),
-> UNIQUE KEY `idx_id` (`col01`) USING BTREE
-> ) ENGINE=InnoDB AUTO_INCREMENT=11802 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> INSERT INTO `db1`.`test`(col01,col02,clo03,col05,clo06,col07,col08,col09,col10,col11,col12) VALUES (123,123456,'{"123": 10}',111,'ABC','2024-01-23 00:00:01','ABCABC','2024-01-23 00:00:00',0,'9999-12-31 23:59:59',2);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> UPDATE test SET col12=1,clo04='{"456": 10}' WHERE col01 = 123;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
--
bin/mysqlbinlog --base64-output=DECODE-ROWS -vv 113783/binlog.000001
.
.
### UPDATE `db1`.`test`
### WHERE
### @1=123 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=123456 /* LONGINT meta=0 nullable=0 is_null=0 */
### @3='\x00\x01\x00\x0e\x00\x0b\x00\x03\x00\x05\x0a\x00123' /* JSON meta=4 nullable=1 is_null=0 */
### @4=NULL /* JSON meta=4 nullable=1 is_null=1 */
### @5=111 /* LONGINT meta=0 nullable=0 is_null=0 */
### @6='ABC' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
### @7='2024-01-23 00:00:01' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### @8='ABCABC' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
### @9='2024-01-23 00:00:00' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### @10=b'0' /* BIT(1) meta=1 nullable=0 is_null=0 */
### @11='9999-12-31 23:59:59' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### @12=2 /* INT meta=0 nullable=0 is_null=0 */
### @13='\x02\x01\x00\x07\x00\x05\x0a\x00' /* JSON meta=4 nullable=1 is_null=0 */
### @14='\x05\x0a\x00' /* JSON meta=4 nullable=1 is_null=0 */
### SET
### @1=123 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=123456 /* LONGINT meta=0 nullable=0 is_null=0 */
### @3='\x00\x01\x00\x0e\x00\x0b\x00\x03\x00\x05\x0a\x00123' /* JSON meta=4 nullable=1 is_null=0 */
### @4='\x00\x01\x00\x0e\x00\x0b\x00\x03\x00\x05\x0a\x00456' /* JSON meta=4 nullable=1 is_null=0 */
### @5=111 /* LONGINT meta=0 nullable=0 is_null=0 */
### @6='ABC' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
### @7='2024-01-23 00:00:01' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### @8='ABCABC' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */
### @9='2024-01-23 00:00:00' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### @10=b'0' /* BIT(1) meta=1 nullable=0 is_null=0 */
### @11='9999-12-31 23:59:59' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### @12=1 /* INT meta=0 nullable=0 is_null=0 */
### @13='\x02\x02\x00\x0a\x00\x05\x0a\x00\x05\x0a\x00' /* JSON meta=4 nullable=1 is_null=0 */
### @14='\x05\x0a\x00' /* JSON meta=4 nullable=1 is_null=0 */
# at 2341
-- 8.0.36 binlog
bin/mysqlbinlog --base64-output=DECODE-ROWS -vv ../mysql-5.7.44/113783/binlog.000001
.
.
.
.
### @12=1 /* INT meta=0 nullable=0 is_null=0 */
### @13='[10, 10]' /* JSON meta=4 nullable=1 is_null=0 */
### @14='10' /* JSON meta=4 nullable=1 is_null=0 */
# at 2341
#240129 9:06:57 server id 1 end_log_pos 2372 CRC32 0x7cb0e722 Xid = 10
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
^^ I'm not seeing any invalid message. Is there anything I'm missing here? Please let us know. Thank you
regards,
Umesh
[1 Mar 2024 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".
Description: If you do an update to a table with generated column and row-based binlog enabled, the information recorded into the binary log seems incorrect. How to repeat: CREATE DATABASE db1; USE db1; CREATE TABLE `test` ( `col01` bigint NOT NULL AUTO_INCREMENT, `col02` bigint NOT NULL, `clo03` json DEFAULT NULL, `clo04` json DEFAULT NULL, `col05` bigint NOT NULL, `clo06` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `col07` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `col08` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `col09` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `col10` bit(1) NOT NULL DEFAULT b'0', `col11` datetime NOT NULL DEFAULT '9999-12-31 23:59:59', `col12` int NOT NULL DEFAULT '1', `col13` json GENERATED ALWAYS AS (json_merge_preserve(ifnull(json_extract(`clo03`,_utf8mb4'$.*'),json_array()),ifnull(json_extract(`clo04`,_utf8mb4'$.*'),json_array()))) VIRTUAL, `col14` json GENERATED ALWAYS AS (json_extract(`col13`,_utf8mb4'$[0]')) VIRTUAL, PRIMARY KEY (`col05`,`col01`), UNIQUE KEY `uid_orderIdOwnerId` (`col02`,`col05`), UNIQUE KEY `idx_id` (`col01`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=11802 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO `db1`.`test`(col01,col02,clo03,col05,clo06,col07,col08,col09,col10,col11,col12) VALUES (123,123456,'{"123": 10}',111,'ABC','2024-01-23 00:00:01','ABCABC','2024-01-23 00:00:00',0,'9999-12-31 23:59:59',2); UPDATE test SET col12=1,clo04='{"456": 10}' WHERE col01 = 123; mysqlbinlog --base64-output=DECODE-ROWS -vv <bin_log> shows: ### UPDATE `db1`.`t_order_wms_process` ### WHERE ### @1=11604 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2=1024510502 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @3='\x00\x01\x00\x14\x00\x0b\x00\x09\x00\x05\x0a\x00116_68690' /* JSON meta=4 nullable=1 is_null=0 */ ### @4=NULL /* JSON meta=4 nullable=1 is_null=1 */ ### @5=179 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @6='system' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */ ### @7='2024-01-23 00:41:35' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### @8='wms_update' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */ ### @9='2024-01-23 00:41:35' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### @10=b'0' /* BIT(1) meta=1 nullable=0 is_null=0 */ ### @11='9999-12-31 23:59:59' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### @12=2 /* INT meta=0 nullable=0 is_null=0 */ ### @13='\x02\x02\x00\x0a\x00\x05\x0a\x00' /* JSON meta=4 nullable=1 is_null=0 */ ### @14='\x05\x0a\x00' /* JSON meta=4 nullable=1 is_null=0 */ ### SET ### @1=11604 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2=1024510502 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @3='\x00\x01\x00\x14\x00\x0b\x00\x09\x00\x05\x0a\x00116_68690' /* JSON meta=4 nullable=1 is_null=0 */ ### @4='\x00\x01\x00\x15\x00\x0b\x00\x0a\x00\x05\x0a\x00116_146693' /* JSON meta=4 nullable=1 is_null=0 */ ### @5=179 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @6='system' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */ ### @7='2024-01-23 00:41:35' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### @8='wms_update' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */ ### @9='2024-01-23 00:41:35' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### @10=b'0' /* BIT(1) meta=1 nullable=0 is_null=0 */ ### @11='9999-12-31 23:59:59' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### @12=1 /* INT meta=0 nullable=0 is_null=0 */ ### @13='\x02\x02\x00\x0a\x00\x05\x0a\x00\x05\x0a\x00' /* JSON meta=4 nullable=1 is_null=0 */ ### @14='\x05\x0a\x00' /* JSON meta=4 nullable=1 is_null=0 */ And the row is： MySQL [db1]> select * from test\G*************************** 1. row ***************************col01: 123col02: 123456clo03: {"123": 10}clo04: {"456": 10}col05: 111clo06: ABCcol07: 2024-01-23 00:00:01 col08: ABCABC col09: 2024-01-23 00:00:00 col10: col11: 9999-12-31 23:59:59 col12: 1 col13: [10, 10] col14: 10 col13 is [10, 10], and in binlog we got @13='\x02\x02\x00\x0a\x00\x05\x0a\x00' in WHERE clause, its the row's before image. As I understand, its a invalid json. the col13 is [10,10], but in the binary format I can only parse out one 10 (\x05\x0a\x00), the other 10 is missing. And if use MySQL 8.0's mysqlbinlog tool, you'll got an error 'Invalid JSON', and the parsing is stopped: ### UPDATE `db1`.`t_order_wms_process` ### WHERE ### @1=11604 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @2=1024510502 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @3='{"116_68690": 10}' /* JSON meta=4 nullable=1 is_null=0 */ ### @4=NULL /* JSON meta=4 nullable=1 is_null=1 */ ### @5=179 /* LONGINT meta=0 nullable=0 is_null=0 */ ### @6='system' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */ ### @7='2024-01-23 00:41:35' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### @8='wms_update' /* VARSTRING(128) meta=128 nullable=0 is_null=0 */ ### @9='2024-01-23 00:41:35' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### @10=b'0' /* BIT(1) meta=1 nullable=0 is_null=0 */ ### @11='9999-12-31 23:59:59' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### @12=2 /* INT meta=0 nullable=0 is_null=0 */ ### @13=Invalid JSON Let me know if I missed something :)