search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
6.03k stars 981 forks source link

SQL_CALC_FOUND_ROWS wrong results with prepared statements #2101

Open nickloose opened 5 years ago

nickloose commented 5 years ago

I have a Primary/Replica Setup and get the wrong results from FOUND_ROWS() in >50% of all queries when using prepared statements. Most of the time the result count is 1 but sometimes it's also 0. It only happens if the query to get the data is a prepared statement. It looks like multiplexing is not disabled when using prepared statements like the wiki says.

So I tested it with a new Database and a basic php script using MySQLi and got the results below. I checked with PDO and got the same results.

Sadly this makes proxysql for us unusable because we have many prepared statements using SQL_CALC_FOUND_ROWS.

Test Results

SELECT Data SELECT Found Rows Wrong Result %
Query Prepare 0%
Query Query 0%
Prepare Prepare 53,93%
Prepare  Query 62,57%

Test Setup

Test Table:

CREATE TABLE `FOO` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `BAR` varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
)

Test SELECT:

SELECT SQL_CALC_FOUND_ROWS id, BAR FROM FOO LIMIT 0, 10;
SELECT FOUND_ROWS() CNT;

Test PHP

$mysql = new MySQLI($host, $username, $pw, $db);
$stmt = $mysql->prepare($sql);
$res = $stmt->execute();
$stmt->bind_result($c1, $c2);
while ($stmt->fetch()) {}

$result = $mysql->query($sqlTotal);
$cnt = $result->fetch_assoc();
$total = $cnt["CNT"];
echo "MySQLI: Prepare/Query: $total\n";
$mysql = new MySQLI($host, $username, $pw, $db);
$stmt = $mysql->prepare($sql);
$res = $stmt->execute();
$stmt->bind_result($c1, $c2);
while ($stmt->fetch()) {}

$stmt = $mysql->prepare($sqlTotal);
$res = $stmt->execute();
$stmt->bind_result($col1);
$stmt->fetch();
$total = $col1;
echo "MySQLI: Prepare/Prepare: $total\n";

Setup Versions

ProxySQL Config:

mysql_servers

+--------------+------------+------+--------+--------+---------------------+
| hostgroup_id | hostname   | port | status | weight | max_replication_lag |
+--------------+------------+------+--------+--------+---------------------+
| 10           | 172.16.1.5 | 3306 | ONLINE | 1      | 0                   |
| 20           | 172.16.1.6 | 3306 | ONLINE | 1      | 10                  |
| 20           | 172.16.1.5 | 3306 | ONLINE | 1      | 0                   |
+--------------+------------+------+--------+--------+---------------------+

mysql_replication_hostgroups

+------------------+------------------+------------+-------------------+
| writer_hostgroup | reader_hostgroup | check_type | comment           |
+------------------+------------------+------------+-------------------+
| 10               | 20               | read_only  | Primary / Replica |
+------------------+------------------+------------+-------------------+

mysql_query_rules

+---------+--------+----------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest        | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 1       | 1      | username | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT.*FOR UPDATE | NULL          | 0                    | CASELESS     | NULL    | NULL            | 10                    | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 2       | 1      | username | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT             | NULL          | 0                    | CASELESS     | NULL    | NULL            | 20                    | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+

global_variables

+-----------------------------------------------------+--------------------------------+
| variable_name                                       | variable_value                 |
+-----------------------------------------------------+--------------------------------+
| admin-stats_credentials                             | stats:stats                    |
| admin-stats_mysql_connections                       | 60                             |
| admin-stats_mysql_connection_pool                   | 60                             |
| admin-stats_mysql_query_cache                       | 60                             |
| admin-stats_system_cpu                              | 60                             |
| admin-stats_system_memory                           | 60                             |
| admin-telnet_admin_ifaces                           | (null)                         |
| admin-telnet_stats_ifaces                           | (null)                         |
| admin-refresh_interval                              | 2000                           |
| admin-read_only                                     | false                          |
| admin-hash_passwords                                | true                           |
| admin-vacuum_stats                                  | true                           |
| admin-version                                       | 2.0.4-116-g7d371cf             |
| admin-cluster_username                              |                                |
| admin-cluster_password                              |                                |
| admin-cluster_check_interval_ms                     | 1000                           |
| admin-cluster_check_status_frequency                | 10                             |
| admin-cluster_mysql_query_rules_diffs_before_sync   | 3                              |
| admin-cluster_mysql_servers_diffs_before_sync       | 3                              |
| admin-cluster_mysql_users_diffs_before_sync         | 3                              |
| admin-cluster_proxysql_servers_diffs_before_sync    | 3                              |
| admin-cluster_mysql_query_rules_save_to_disk        | true                           |
| admin-cluster_mysql_servers_save_to_disk            | true                           |
| admin-cluster_mysql_users_save_to_disk              | true                           |
| admin-cluster_proxysql_servers_save_to_disk         | true                           |
| admin-checksum_mysql_query_rules                    | true                           |
| admin-checksum_mysql_servers                        | true                           |
| admin-checksum_mysql_users                          | true                           |
| admin-web_enabled                                   | false                          |
| admin-web_port                                      | 6080                           |
| admin-admin_credentials                             | admin:admin                    |
| admin-mysql_ifaces                                  | 127.0.0.1:6032                 |
| mysql-shun_on_failures                              | 5                              |
| mysql-shun_recovery_time_sec                        | 10                             |
| mysql-query_retries_on_failure                      | 1                              |
| mysql-client_multi_statements                       | true                           |
| mysql-connect_retries_delay                         | 1                              |
| mysql-connection_delay_multiplex_ms                 | 0                              |
| mysql-connection_max_age_ms                         | 0                              |
| mysql-connect_timeout_server_max                    | 10000                          |
| mysql-eventslog_filename                            |                                |
| mysql-eventslog_filesize                            | 104857600                      |
| mysql-default_charset                               | utf8                           |
| mysql-free_connections_pct                          | 10                             |
| mysql-session_idle_ms                               | 1000                           |
| mysql-have_ssl                                      | false                          |
| mysql-client_found_rows                             | true                           |
| mysql-monitor_enabled                               | true                           |
| mysql-monitor_connect_timeout                       | 600                            |
| mysql-monitor_ping_max_failures                     | 3                              |
| mysql-monitor_ping_timeout                          | 1000                           |
| mysql-monitor_read_only_max_timeout_count           | 3                              |
| mysql-monitor_replication_lag_interval              | 10000                          |
| mysql-monitor_replication_lag_timeout               | 1000                           |
| mysql-monitor_groupreplication_healthcheck_interval | 5000                           |
| mysql-monitor_groupreplication_healthcheck_timeout  | 800                            |
| mysql-monitor_galera_healthcheck_interval           | 5000                           |
| mysql-monitor_galera_healthcheck_timeout            | 800                            |
| mysql-monitor_galera_healthcheck_max_timeout_count  | 3                              |
| mysql-monitor_replication_lag_use_percona_heartbeat |                                |
| mysql-monitor_query_interval                        | 60000                          |
| mysql-monitor_query_timeout                         | 100                            |
| mysql-monitor_slave_lag_when_null                   | 60                             |
| mysql-monitor_threads_min                           | 8                              |
| mysql-monitor_threads_max                           | 128                            |
| mysql-monitor_threads_queue_maxsize                 | 128                            |
| mysql-monitor_wait_timeout                          | true                           |
| mysql-monitor_writer_is_also_reader                 | true                           |
| mysql-max_allowed_packet                            | 4194304                        |
| mysql-throttle_connections_per_sec_to_hostgroup     | 1000000                        |
| mysql-max_transaction_time                          | 14400000                       |
| mysql-multiplexing                                  | true                           |
| mysql-forward_autocommit                            | false                          |
| mysql-enforce_autocommit_on_reads                   | false                          |
| mysql-autocommit_false_not_reusable                 | false                          |
| mysql-autocommit_false_is_transaction               | false                          |
| mysql-verbose_query_error                           | false                          |
| mysql-hostgroup_manager_verbose                     | 1                              |
| mysql-binlog_reader_connect_retry_msec              | 3000                           |
| mysql-threshold_query_length                        | 524288                         |
| mysql-threshold_resultset_size                      | 4194304                        |
| mysql-query_digests_max_digest_length               | 2048                           |
| mysql-query_digests_max_query_length                | 65000                          |
| mysql-wait_timeout                                  | 28800000                       |
| mysql-throttle_max_bytes_per_second_to_client       | 0                              |
| mysql-throttle_ratio_server_to_client               | 0                              |
| mysql-max_stmts_per_connection                      | 20                             |
| mysql-max_stmts_cache                               | 10000                          |
| mysql-mirror_max_concurrency                        | 16                             |
| mysql-mirror_max_queue_length                       | 32000                          |
| mysql-default_max_latency_ms                        | 1000                           |
| mysql-query_processor_iterations                    | 0                              |
| mysql-query_processor_regex                         | 1                              |
| mysql-reset_connection_algorithm                    | 2                              |
| mysql-auto_increment_delay_multiplex                | 5                              |
| mysql-long_query_time                               | 1000                           |
| mysql-query_cache_size_MB                           | 256                            |
| mysql-poll_timeout_on_failure                       | 100                            |
| mysql-server_capabilities                           | 569867                         |
| mysql-keep_multiplexing_variables                   | tx_isolation,version           |
| mysql-kill_backend_connection_when_disconnect       | true                           |
| mysql-session_idle_show_processlist                 | true                           |
| mysql-show_processlist_extended                     | 0                              |
| mysql-query_digests                                 | true                           |
| mysql-query_digests_lowercase                       | false                          |
| mysql-query_digests_normalize_digest_text           | false                          |
| mysql-query_digests_track_hostname                  | false                          |
| mysql-servers_stats                                 | true                           |
| mysql-default_reconnect                             | true                           |
| mysql-ssl_p2s_ca                                    |                                |
| mysql-ssl_p2s_cert                                  |                                |
| mysql-ssl_p2s_key                                   |                                |
| mysql-ssl_p2s_cipher                                |                                |
| mysql-init_connect                                  |                                |
| mysql-ldap_user_variable                            |                                |
| mysql-add_ldap_user_comment                         |                                |
| mysql-default_sql_mode                              |                                |
| mysql-default_time_zone                             | SYSTEM                         |
| mysql-connpoll_reset_queue_length                   | 50                             |
| mysql-min_num_servers_lantency_awareness            | 1000                           |
| mysql-stats_time_backend_query                      | false                          |
| mysql-stats_time_query_processor                    | false                          |
| mysql-query_cache_stores_empty_result               | true                           |
| mysql-threads                                       | 4                              |
| mysql-max_connections                               | 2048                           |
| mysql-default_query_delay                           | 0                              |
| mysql-default_query_timeout                         | 36000000                       |
| mysql-have_compress                                 | true                           |
| mysql-poll_timeout                                  | 2000                           |
| mysql-interfaces                                    | 0.0.0.0:3306                   |
| mysql-default_schema                                | information_schema             |
| mysql-stacksize                                     | 1048576                        |
| mysql-server_version                                | 5.5.30                         |
| mysql-connect_timeout_server                        | 3000                           |
| mysql-monitor_username                              | username                       |
| mysql-monitor_password                              | password                       |
| mysql-monitor_history                               | 600000                         |
| mysql-monitor_connect_interval                      | 60000                          |
| mysql-monitor_ping_interval                         | 10000                          |
| mysql-monitor_read_only_interval                    | 1500                           |
| mysql-monitor_read_only_timeout                     | 500                            |
| mysql-ping_interval_server_msec                     | 120000                         |
| mysql-ping_timeout_server                           | 500                            |
| mysql-commands_stats                                | true                           |
| mysql-sessions_sort                                 | true                           |
| mysql-connect_retries_on_failure                    | 10                             |
+-----------------------------------------------------+--------------------------------+

Error Log

Nothing is written to the error log

jett-dave commented 5 years ago

I believe I have run into this same issue - I have a Wordpress site that was giving incorrect results with SELECT FOUND_ROWS() even though I have multiplexing completely disabled. I ended up having to write a specific query rule to direct the queries at the writer hostgroup as a temporary solution.

Edit: Forgot to mention, I'm using ProxySQL version 2.0.6-73-gc746bf7, codename Truls