julien-duponchelle / julien-duponchelle/python-mysql-replication
MySQL SET type value should not use python set as container
- Dominant language
- Python
- Stars
- 2.4k
- Forks
- 690
- PR merge metrics
- No merged PRs in 30d
Description
Value order does matter in MySQL SET type value comparisons, so when parsing the binlog event we should preserve the set value order very carefully.
e.g.
```
CREATE TABLE `test` (
`id` bit(8) DEFAULT NULL,
`size` enum('x-small','small','medium','large','x-large') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`color` set('a','b','c','d') COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
```
and execute
```
mysql>update test set color='c,d,a,b' where color='b,c';
OK 1 row effected.
```
the parsed values for color from the update_event are:
before_value:set([u'c', u'b'])
after value:set([u'a', u'c', u'b', u'd'])
the order is interrupted, if i use the before value 'c,b' to find rows in the table, i will get nothing, as
'select * from test where color='c,b' and ....'
will not match any row. If i use the python-mysql-replication as sync method between two database, this will cause data inconsistent.
The following doc is about the value order matters in set comparisons.
https://dev.mysql.com/doc/refman/5.7/en/set.html
PS:
As MySQL always sort the enum value in order when store a set value, maybe i can sort the parsed set value by myself, that can also bypass the problem.
Contributor guide
Research direction
Start by locating the binlog event parsing entry point that converts MySQL SET values into Python containers. Read the linked MySQL SET comparison documentation and reproduce the example with the reported before and after values. Done means parsed SET values retain the order required for comparisons and synchronization, with verification for the update case described.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- mysql, python
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100