julien-duponchelle / julien-duponchelle/python-mysql-replication

MySQL SET type value should not use python set as container

Open
#268 0 comments 1 reaction 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.