Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Binlog reader dirty reads from slave. #13

Open
kevin0607 opened this issue Jun 23, 2020 · 2 comments
Open

Binlog reader dirty reads from slave. #13

kevin0607 opened this issue Jun 23, 2020 · 2 comments

Comments

@kevin0607
Copy link

kevin0607 commented Jun 23, 2020 •
edited
Loading

Hi @renecannao ,

I'm able to setup ubuntu18 version of binlog_reader and followed the steps from link ( https://www.percona.com/blog/2020/02/18/configuring-proxysql-binlog-reader/ ) to check operation of binlog reader .
But i can still see dirty reads while reading from slaves. Is there any more configuration i am missing here .

2020-06-23 23:27:12 i 372 is ok
2020-06-23 23:27:12 i 373 is ok
2020-06-23 23:27:12 i 374 is ok
2020-06-23 23:27:12 Dirty Read Detected on i 375 . . . After 500ms rows found 1 
2020-06-23 23:27:12 i 376 is ok
2020-06-23 23:27:12 i 504 is ok
2020-06-23 23:27:12 i 505 is ok
2020-06-23 23:27:12 i 506 is ok
2020-06-23 23:27:12 i 507 is ok
2020-06-23 23:27:12 i 508 is ok

Here is the stats :

mysql> select hostgroup, srv_host, queries, queries_gtid_sync from stats_mysql_connection_pool;
+-----------+----------------+---------+-------------------+
| hostgroup | srv_host       | Queries | Queries_GTID_sync |
+-----------+----------------+---------+-------------------+
| 20        | 192.168.17.2 | 67607   | 0                 |
| 20        | 192.168.17.4 | 23796   | 0                 |
| 20        | 192.168.17.3 | 51771   | 0                 |
| 10        | 192.168.17.1 | 129765  | 0                 |
+-----------+----------------+---------+-------------------+
4 rows in set (0.00 sec)

mysql> select * from stats_mysql_gtid_executed;
+----------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| hostname       | port | gtid_executed                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | events |
+----------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
| 192.168.17.4 | 3306 | c8122108-8a04-11ea-8241-005056997130:1-1,89ef83bc-8a0d-11ea-948b-005056997130:1-267248,c5b13f31-518a-11ea-86e7-00505699712d:107-912474,837c87d7-89ff-11ea-9d55-005056997130:1-1,cfe8616d-7f32-11ea-91b3-005056a6d0e9:1-6,629ebf7b-518a-11ea-a9f0-005056995478:3-527437,77416ad4-8e0d-11ea-8f2b-00505699712d:1-2810,d4b483e6-5189-11ea-bc9b-00505699b083:1-799000,ed1050d3-91f6-11ea-a38f-005056a651a4:1-11,23fecc5e-518b-11ea-a7ce-00505699743c:183-1492704,dcd156e0-8e10-11ea-9af4-00505699b083:1-572264,fc01fde0-7f2e-11ea-94a4-005056a651a4:1-20,5bb32995-4bf4-ee15-4e9e-5dd514609ee2:2-1681,5bb32995-4bf4-ee15-4e9e-5dd514609ee2:1683-1695,5bb32995-4bf4-ee15-4e9e-5dd514609ee2:1697-1764,ebdf8531-7f0a-11ea-b0e3-005056993f08:1-11,a40aca33-8e06-11ea-b329-00505699743c:1-864318,89b13e24-8968-11ea-80d4-005056997130:1-215925,1e73fc5f-518a-11ea-9384-005056997130:1-742796,7c776d05-8e0f-11ea-9fc1-005056995478:1-40793,9838d4eb-912d-11ea-ab24-005056999677:1-20,9838d4eb-912d-11ea-ab24-005056999677:17189-17190,9838d4eb-912d-11ea-ab24-005056999677:17197-17200,a8230702-9114-11ea-8313-005056a6d0e9:1-3,2a2562f2-7f0a-11ea-a5ca-005056999677:1-28352,68e5ad48-9101-11ea-996f-005056993f08:1-18 | 31231  |
| 192.168.17.3 | 3306 | c8122108-8a04-11ea-8241-005056997130:1-1,89ef83bc-8a0d-11ea-948b-005056997130:1-267248,c5b13f31-518a-11ea-86e7-00505699712d:107-912474,837c87d7-89ff-11ea-9d55-005056997130:1-1,cfe8616d-7f32-11ea-91b3-005056a6d0e9:1-6,629ebf7b-518a-11ea-a9f0-005056995478:3-527437,77416ad4-8e0d-11ea-8f2b-00505699712d:1-2810,d4b483e6-5189-11ea-bc9b-00505699b083:1-799000,ed1050d3-91f6-11ea-a38f-005056a651a4:1-11,23fecc5e-518b-11ea-a7ce-00505699743c:183-1492704,dcd156e0-8e10-11ea-9af4-00505699b083:1-572264,fc01fde0-7f2e-11ea-94a4-005056a651a4:1-20,5bb32995-4bf4-ee15-4e9e-5dd514609ee2:2-1681,5bb32995-4bf4-ee15-4e9e-5dd514609ee2:1683-1695,5bb32995-4bf4-ee15-4e9e-5dd514609ee2:1697-1764,ebdf8531-7f0a-11ea-b0e3-005056993f08:1-11,a40aca33-8e06-11ea-b329-00505699743c:1-864318,89b13e24-8968-11ea-80d4-005056997130:1-215925,1e73fc5f-518a-11ea-9384-005056997130:1-742796,7c776d05-8e0f-11ea-9fc1-005056995478:1-40793,9838d4eb-912d-11ea-ab24-005056999677:1-20,9838d4eb-912d-11ea-ab24-005056999677:17189-17190,9838d4eb-912d-11ea-ab24-005056999677:17197-17200,a8230702-9114-11ea-8313-005056a6d0e9:1-3,2a2562f2-7f0a-11ea-a5ca-005056999677:1-28352,68e5ad48-9101-11ea-996f-005056993f08:1-18 | 31247  |
| 192.168.17.2 | 3306 | c8122108-8a04-11ea-8241-005056997130:1-1,89ef83bc-8a0d-11ea-948b-005056997130:1-267248,c5b13f31-518a-11ea-86e7-00505699712d:107-912474,837c87d7-89ff-11ea-9d55-005056997130:1-1,cfe8616d-7f32-11ea-91b3-005056a6d0e9:1-6,629ebf7b-518a-11ea-a9f0-005056995478:3-527437,77416ad4-8e0d-11ea-8f2b-00505699712d:1-2810,d4b483e6-5189-11ea-bc9b-00505699b083:1-799000,ed1050d3-91f6-11ea-a38f-005056a651a4:1-11,23fecc5e-518b-11ea-a7ce-00505699743c:183-1492704,dcd156e0-8e10-11ea-9af4-00505699b083:1-572264,fc01fde0-7f2e-11ea-94a4-005056a651a4:1-20,5bb32995-4bf4-ee15-4e9e-5dd514609ee2:2-1681,5bb32995-4bf4-ee15-4e9e-5dd514609ee2:1683-1695,5bb32995-4bf4-ee15-4e9e-5dd514609ee2:1697-1764,ebdf8531-7f0a-11ea-b0e3-005056993f08:1-11,a40aca33-8e06-11ea-b329-00505699743c:1-864318,89b13e24-8968-11ea-80d4-005056997130:1-215925,1e73fc5f-518a-11ea-9384-005056997130:1-742796,7c776d05-8e0f-11ea-9fc1-005056995478:1-40793,9838d4eb-912d-11ea-ab24-005056999677:1-20,9838d4eb-912d-11ea-ab24-005056999677:17189-17190,9838d4eb-912d-11ea-ab24-005056999677:17197-17200,a8230702-9114-11ea-8313-005056a6d0e9:1-3,2a2562f2-7f0a-11ea-a5ca-005056999677:1-28352,68e5ad48-9101-11ea-996f-005056993f08:1-18 | 31261  |
| 192.168.17.1 | 3306 | c8122108-8a04-11ea-8241-005056997130:1-1,89ef83bc-8a0d-11ea-948b-005056997130:1-267248,c5b13f31-518a-11ea-86e7-00505699712d:107-912474,837c87d7-89ff-11ea-9d55-005056997130:1-1,cfe8616d-7f32-11ea-91b3-005056a6d0e9:1-6,629ebf7b-518a-11ea-a9f0-005056995478:3-527437,77416ad4-8e0d-11ea-8f2b-00505699712d:1-2810,d4b483e6-5189-11ea-bc9b-00505699b083:1-799000,ed1050d3-91f6-11ea-a38f-005056a651a4:1-11,23fecc5e-518b-11ea-a7ce-00505699743c:183-1492704,dcd156e0-8e10-11ea-9af4-00505699b083:1-572264,fc01fde0-7f2e-11ea-94a4-005056a651a4:1-20,5bb32995-4bf4-ee15-4e9e-5dd514609ee2:2-1681,5bb32995-4bf4-ee15-4e9e-5dd514609ee2:1683-1695,5bb32995-4bf4-ee15-4e9e-5dd514609ee2:1697-1764,ebdf8531-7f0a-11ea-b0e3-005056993f08:1-11,a40aca33-8e06-11ea-b329-00505699743c:1-864318,89b13e24-8968-11ea-80d4-005056997130:1-215925,1e73fc5f-518a-11ea-9384-005056997130:1-742796,7c776d05-8e0f-11ea-9fc1-005056995478:1-40793,9838d4eb-912d-11ea-ab24-005056999677:1-20,9838d4eb-912d-11ea-ab24-005056999677:17189-17190,9838d4eb-912d-11ea-ab24-005056999677:17197-17200,a8230702-9114-11ea-8313-005056a6d0e9:1-3,2a2562f2-7f0a-11ea-a5ca-005056999677:1-28352,68e5ad48-9101-11ea-996f-005056993f08:1-18 | 31297  |
+----------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+
4 rows in set (0.00 sec)



mysql> SELECT rule_id, match_digest, destination_hostgroup, gtid_from_hostgroup,multiplex FROM mysql_query_rules;
+---------+------------------------+-----------------------+---------------------+-----------+
| rule_id | match_digest           | destination_hostgroup | gtid_from_hostgroup | multiplex |
+---------+------------------------+-----------------------+---------------------+-----------+
| 1       | ^SELECT                | 20                    | 10                  | 1         |
| 2       | ^SELECT .* FOR UPDATE$ | 10                    | NULL                | 0         |
| 3       | ROW_COUNT()            | 10                    | NULL                | 0         |
| 4       | LAST_INSERT_ID()       | 10                    | NULL                | 0         |
+---------+------------------------+-----------------------+---------------------+-----------+
4 rows in set (0.00 sec)



mysql> select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment     |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| 20           | 192.168.17.2 | 3306 | 888       | ONLINE | 1      | 0           | 8000            | 0                   | 1       | 0              |   |
| 10           | 192.168.17.1 | 3306 | 888       | ONLINE | 1      | 0           | 8000            | 0                   | 1       | 0              | |
| 20           | 192.168.17.4 | 3306 | 888       | ONLINE | 1      | 0           | 8000            | 0                   | 1       | 0              | |
| 20           | 192.168.17.3 | 3306 | 888       | ONLINE | 1      | 0           | 8000            | 0                   | 1       | 0              |  |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+



mysql> select * from global_variables where variable_name like '%multiplex%';
+--------------------------------------+----------------------+
| variable_name                        | variable_value       |
+--------------------------------------+----------------------+
| mysql-connection_delay_multiplex_ms  | 0                    |
| mysql-multiplexing                   | true                 |
| mysql-auto_increment_delay_multiplex | 5                    |
| mysql-keep_multiplexing_variables    | tx_isolation,version |
+--------------------------------------+----------------------+
4 rows in set (0.00 sec)
@kevin0607 kevin0607 changed the title Binlog reader not routing queries . Binlog reader dirty reads from slave. Jun 23, 2020
@pondix
Copy link
Contributor

pondix commented Jun 24, 2020

hi @kevin0607

If you are referring to the Queries_GTID_sync column reporting 0 - this is due to sysown/proxysql#2615 which is addressed in ProxySQL v2.0.13 (next release).

What script are you using to test this? Please keep in mind that if a SELECT is executed before ProxySQL has received the initial OK packet it doesn't know what GTID subset is required e.g.:

< ---- Here a new connection is opened
    START TRANSACTION;
< ---- At this point the GTID information is returned in the OK packet
    SELECT x,y,z...;
< ---- This select will be routed to a host with the GTID set available

Final note - you don't need to compile this, packages are available here: https://github.com/sysown/proxysql_mysqlbinlog/releases

@kevin0607
Copy link
Author

Hey hi @pondix ,Thanks for the help

Here is the script which i'm using to test the consistency reads .which i got from --> https://www.percona.com/blog/2020/02/18/configuring-proxysql-binlog-reader/

Most of the time I'm getting dirty reads , it it because i'm maintaining only single connection through out the script.

<?php
date_default_timezone_set('UTC');
$mysqli = new mysqli('*******', '*******', '*******', 'test', 3306);

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') '
            . $mysqli->connect_error);
}

/* Setup */
echo date('Y-m-d H:i:s') . " Starting to SETUP the test\n";
$mysqli->query("DROP TABLE IF EXISTS joinit");
$mysqli->query("CREATE TABLE IF NOT EXISTS `test`.`joinit` (
  `i` bigint(11) NOT NULL AUTO_INCREMENT,
  `s` char(255) DEFAULT NULL,
  `t` datetime NOT NULL,
  `g` bigint(11) NOT NULL,
  KEY(`i`, `t`),
  PRIMARY KEY(`i`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;");
$date1=date('Y-m-d H:i:s');
$mysqli->query("INSERT INTO test.joinit VALUES (NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )));");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");
$mysqli->query("INSERT INTO test.joinit SELECT NULL, uuid(), time('$date1'),  (FLOOR( 1 + RAND( ) *60 )) FROM test.joinit;");

echo date('Y-m-d H:i:s') . " Starting to RUN the test\n";

$result = $mysqli->query("SELECT MAX(i) FROM joinit");
$row = $result->fetch_row();
sleep(2);
$date2=date('Y-m-d H:i:s');

for ($i=1; $i<$row[0]; $i++)
{
  $result = $mysqli->query("SELECT i FROM joinit WHERE i = $i");
  if($result->num_rows == 0)
    continue;
  
  $mysqli->query("UPDATE joinit SET t = '$date2' WHERE i = $i");
  
  $result = $mysqli->query("SELECT i FROM joinit WHERE t = '$date2' AND i = $i");
  if($result->num_rows == 0)
  {
      echo date('Y-m-d H:i:s') . " Dirty Read Detected on i $i . . .";
      usleep(500000);
      $result = $mysqli->query("SELECT i FROM joinit WHERE t = '$date2' AND i = $i");
      echo " After 500ms rows found $result->num_rows \n";
  } else {
    echo date('Y-m-d H:i:s') . " i $i is ok\n";
  }
}
$mysqli->close();
?>

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@pondix @kevin0607