| Bug #107996 | Contribution by Tencent: wrong default value of geometry column | ||
|---|---|---|---|
| Submitted: | 27 Jul 2022 6:38 | Modified: | 22 Feb 2023 18:29 |
| Reporter: | Yin Peng (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 8.0.29 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[27 Jul 2022 6:40]
Yin Peng
set NO_DEFAULT_VALUE_FLAG for this scenario (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: patch.txt (text/plain), 642 bytes.
[27 Jul 2022 7:44]
MySQL Verification Team
Hello yin peng, Thank you for the report and contribution. regards, Umesh
[22 Feb 2023 18:29]
Christine Cole
Posted by developer: Fixed as of the upcoming MySQL 8.0.34 release, and here's the proposed changelog entry from the documentation team: When using SELECT to create a table and the statement has an expression of GEOMETRY type, MySQL could generate an empty string as the column value by default. To resolve this issue, MySQL no longer generates default values for columns of type GEOMETRY under these circumstances. Our thanks to Tencent for the contribution. Thank you for the bug report.
Description: The following statement generates wrong default value for geometry column when binlog_format=row: CREATE TABLE t1 AS SELECT ST_POINTFROMGEOHASH("0123", 4326), which may cause replication error. How to repeat: source include/have_binlog_format_row.inc; source include/master-slave.inc; connection master; CREATE TABLE t1 AS SELECT ST_POINTFROMGEOHASH("0123", 4326); show create table t1; show binlog events; source include/sync_slave_sql_with_master.inc; connection slave; show create table t1; connection master; drop table t1; source include/sync_slave_sql_with_master.inc; source include/rpl_end.inc;