search

julien-duponchelle / python-mysql-replication

Pure Python Implementation of MySQL replication protocol build on top of PyMYSQL
2.31k stars 678 forks source link

Write events as QueryEvents #221

Open facundo-guerrero-olx opened 7 years ago

facundo-guerrero-olx commented 7 years ago

Hi,

I'm having a problem with one database that the events INSERT/DELETE/UPDATE are only working with 3 tables and the rest not.

I'm seeing this events as QueryEvents and not WriteEvents

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.16-log |
+------------+
mysql> SHOW VARIABLES where Variable_name in ('log_bin', 'binlog_format', 'binlog_row_image') ;
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_format    | ROW   |
| binlog_row_image | FULL  |
| log_bin          | ON    |
+------------------+-------+
=== QueryEvent ===
Date: 2017-08-07T09:10:34
Log position: 2422824
Event size: 52
Read bytes: 52
Schema: db1
Execution time: 0
Query: BEGIN
()
=== QueryEvent ===
Date: 2017-08-07T09:10:34
Log position: 2423150
Event size: 303
Read bytes: 303
Schema: db1
Execution time: 0
Query: UPDATE `table` SET `field1` = NULL, `field1` = '77788', `phpsessid` = 'm5b', `device_type` = 'android', `device` = 'XX-123' WHERE table.id = 123456
()
=== XidEvent ===
Date: 2017-08-07T09:10:34
Log position: 2423181
Event size: 8
Read bytes: 8
Transaction ID: 79354691
()
=== QueryEvent ===
Date: 2017-08-07T09:10:34
Log position: 2423309
Event size: 40
Read bytes: 40
Schema:
Execution time: 0
Query: BEGIN
()
observed_users
=== TableMapEvent ===
Date: 2017-08-07T09:10:34
Log position: 2423372
Event size: 40
Read bytes: 39
Table id: 130
Schema: db2
Table: table2
Columns: 5
()
observed_users
=== WriteRowsEvent ===
Date: 2017-08-07T09:10:34
Log position: 2423426
Event size: 31
Read bytes: 12
Table: db2.table2
Affected columns: 5
Changed rows: 1
Values:
--
('*', u'notification_level', ':', 251231231235)
('*', u'target_id', ':', 123123123)
('*', u'id', ':', 123123123123)
('*', u'observed_date', ':', datetime.datetime(2017, 8, 7, 9, 10, 34))
('*', u'origin_id', ':', 123123123)
()
baloo commented 7 years ago

It might depends on the actual engine of the table. Could you please provide a DESCRIBE table here?

mvidela commented 7 years ago

Hi, I'm continuing with this issue. Here is the output of 'show status

for one table where it works as expected and another were we receive a Query event instead.

``` mysql SHOW TABLE STATUS WHERE Name = 'works'; +------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+ | works | InnoDB | 10 | Dynamic | 655627 | 1600 | 1049395200 | 0 | 428589056 | 6291456 | 1050322197 | 2017-05-16 00:45:27 | 2017-09-14 09:05:07 | NULL | utf8_polish_ci | NULL | checksum=1 | | +------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+----------------+----------+----------------+---------+ 1 row in set (0.00 sec) ` `mysql> SHOW TABLE STATUS WHERE Name = 'doesnt_work'; +---------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +---------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | doesnt_work | InnoDB | 10 | Dynamic | 1132649 | 410 | 465354752 | 0 | 384991232 | 1269825536 | 3413373 | 2017-04-10 21:13:52 | 2017-09-14 09:05:36 | NULL | utf8_general_ci | NULL | | | +---------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ 1 row in set (0.00 sec) ```
baloo commented 7 years ago

(reformated your comment for readability)

facundo-guerrero-olx commented 6 years ago

@baloo Do you have a clue why this it's happening? I'm not sure if there is some config missing in the database.

baloo commented 6 years ago

not much. I would investigate with mysqlbinlog first see if it's related to python-mysql-replication or a configuration error on mysql master.

Maybe an binlog-db-ignore or something. Could you please paste the output of show master status?

facundo-guerrero-olx commented 6 years ago

If in the mysql is enabled binlog_rows_query_log_events then the library don't read the events properly.

felixls commented 6 years ago

@facundo-guerrero-olx put this in your my.cnf

binlog-format = ROW

facundo-guerrero-olx commented 6 years ago

@Felixls It's in row already.

  • © Githubissues.
  • Githubissues is a development platform for aggregating issues.