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:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.7.40 OS:Any
Assigned to: CPU Architecture:Any

[28 Nov 2022 9:51] Huaxiong Song
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.
[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.