| Bug #109217 | The datas of master and standby are inconsistent for Spatial Data Formats | ||
|---|---|---|---|
| Submitted: | 28 Nov 2022 9:51 | Modified: | 12 Dec 2022 3:19 |
| Reporter: | Huaxiong Song (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.7.40 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 Nov 2022 1:18]
MySQL Verification Team
Hi,
If you look at the warnings
> INSERT INTO `geom` (v) VALUES (geomfromtext('POLYGON((121.474243 31.234504, 121.471775 31.233348, 121.470724 31.23155, 121.471603 31.230229, 121.472655 31.230357, 121.475777 31.232045, 121.474243 31.234504))'));
Query OK, 1 row affected, 1 warning (0.03 sec)
master [localhost:20640] {msandbox} (test) > show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'GEOMFROMTEXT' is deprecated and will be removed in a future release. Please use ST_GEOMFROMTEXT instead |
You should really not be using GEOMFROMTEXT(), there is a reason it is deprecated
[29 Nov 2022 1:22]
MySQL Verification Team
> 7. INSERT INTO geom_2 SELECT * FROM geom; #it will be successful unexpectedly. This does not fail as your target (geom_2) has the same number of fields as your source (* from geom) and if conversion cannot be done result will be null and your target (geom_2.v) accepts null values
[29 Nov 2022 1:27]
MySQL Verification Team
problem here is that result differs on master and on slave, on master geom_2.v has the value while on slave geom_2.v does not hence I'm verifying the bug. Thank you for the report
[12 Dec 2022 3:17]
Huaxiong Song
Make insert...select...between different geometry types fail (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug#109217.diff (application/octet-stream, text), 19.54 KiB.
[12 Dec 2022 3:19]
Huaxiong Song
Hi, I made a bugfix, details can be seen in the above [Contributions] file. Solution ======== Add logic to judge type "MYSQL_TYPE_GEOMETRY", so that "field_conv()" will call "Field_geom::store" eventually instead of returning [TYPE_OK] directly. Because the query can not be executed in master, data in slave will be consistent naturally with master one.
Description: When insert spatial datas into column whose type is spatial data and different with insert one, error will happen, just like: ```sql MySQL [test]> create table geom_2(v point); Query OK, 0 rows affected (0.00 sec) MySQL [test]> INSERT INTO `geom_2` (v) VALUES (geomfromtext('POLYGON((121.474243 31.234504, 121.471775 31.233348, 121.470724 31.23155, 121.471603 31.230229, 121.472655 31.230357, 121.475777 31.232045, 121.474243 31.234504))')); ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field ``` However, "INSERT INTO ... SELECT" is successful unexpectedly! At the same time, as long as there is an master-standby relationship, the data in the standby database will be inconsistent with the main database.Just like: ```sql MySQL [test]> create table geom(v POLYGON); Query OK, 0 rows affected (0.01 sec) MySQL [test]> INSERT INTO `geom` (v) VALUES (geomfromtext('POLYGON((121.474243 31.234504, 121.471775 31.233348, 121.470724 31.23155, 121.471603 31.230229, 121.472655 31.230357, 121.475777 31.232045, 121.474243 31.234504))')); Query OK, 1 row affected, 1 warning (0.01 sec) MySQL [test]> create table geom_2(v point); Query OK, 0 rows affected (0.00 sec) MySQL [test]> insert into geom_2 select * from geom; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 ``` master: MySQL [test]> select * from geom_2; +-----------------------------------------------------------------------------------------------------------------------------------+ | v | +-----------------------------------------------------------------------------------------------------------------------------------+ | ��O�Y^^@��B<?@�ŏ1^^@F�̱�;?@O\�W ^^@�c]�F;?@�lY�.^^@>��I�:?@����?^^@�g%��:?@�_!s^^@H3Mg;?@��O�Y^^@��<?@ | +-----------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) standby: MySQL [test]> select * from geom_2; +------+ | v | +------+ | | +------+ By the way, POINT to POLYGON/POLYGON to POINT, problem occurs. I am not sure if other spatial types also have this situation. How to repeat: 1. build master-standby mysql-server ==master== 2. CREATE DATABASE test; 3. USE test; 4. CREATE TABLE geom(v POLYGON); 5. INSERT INTO `geom` (v) VALUES (geomfromtext('POLYGON((121.474243 31.234504, 121.471775 31.233348, 121.470724 31.23155, 121.471603 31.230229, 121.472655 31.230357, 121.475777 31.232045, 121.474243 31.234504))')); 6. CREATE TABLE geom_2(v point); 7. INSERT INTO geom_2 SELECT * FROM geom; #it will be successful unexpectedly. 8. INSERT INTO `geom_2` (v) VALUES (geomfromtext('POLYGON((121.474243 31.234504, 121.471775 31.233348, 121.470724 31.23155, 121.471603 31.230229, 121.472655 31.230357, 121.475777 31.232045, 121.474243 31.234504))')); #it will be failed. 9. select * from geom_2; ==standby== 10. select * from geom_2; # It is different with master one. Suggested fix: I think "INSERT INTO ... SELECT" should be failed also. The way to avoid problem is to not use this syntax, but I don't think it's a good and "once and for all" way.