Open
Description
Hello,
the following code, even where there're no query rules, causes really weird behaviour of ProxySQL:
<?php
try {
$dbh = new PDO('mysql:host=127.0.0.1;dbname=test02;port=6033', "root", "a");
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
print "Before call" . PHP_EOL;
$stmt = $dbh->prepare("CALL test(?)");
$stmt->bindParam(1, $id);
$stmt->execute();
print "Before fetch" . PHP_EOL;
$stmt->fetchAll();
print "I'm here and actually i have no idea why i'm not finishing".PHP_EOL;
?>
$ php Work/weird/test.php
Before call
Before fetch
I'm here and actually i have no idea why i'm not finishing
^C
$ php Work/weird/test.php
Before call
PHP Warning: PDOStatement::execute(): MySQL server has gone away in /Work/weird/test.php on line 15
PHP Warning: PDOStatement::execute(): Error reading result set's header in /Work/weird/test.php on line 15
PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in /Work/weird/test.php:15
Stack trace:
#0 /Work/weird/test.php(15): PDOStatement->execute()
#1 {main}
thrown in /Work/weird/test.php on line 15
$
Note, that PHP hangs after executing the last (print) frame, however, even after re-run funny things are happening. While I understand PHP bug is a completely different thing (I will report it to PHP devs), any other connection using the above backend connection will fail with commands out of sync
error. Also, the issue is not reproducible when PDO is configured with
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
ProxySQL in the same time return the following in the error log:
MySQL_Session.cpp:2837:handler(): [ERROR] Detected a broken connection during query on (1,127.0.0.1,3306) , FD (Conn:28 , MyDS:28) : 2014, Commands out of sync; you can't run this command now
I was able to reproduce similar (not exactly the same) issue with the following Python code:
#!/usr/bin/python
# -*- coding: utf-8 -*-
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
try:
connection = mysql.connector.connect(host='127.0.0.1',
database='test02',
user='root',
password='a', port=6033)
cursor = connection.cursor(prepared=True)
#Update single record now
sql_parameterized_query = """call test( %s ) """
print sql_parameterized_query
cursor.execute(sql_parameterized_query , (1, ))
connection.commit()
print("Record Updated successfully using prepared stament")
except mysql.connector.Error as error :
print("Failed to update record to database: {}".format(error))
connection.rollback()
finally:
#closing database connection.
if(connection.is_connected()):
connection.close()
print("MySQL connection is closed")
$ python test.py
call test( %s )
Failed to update record to database: Unread result found
MySQL connection is closed
$ python test.py
call test( %s )
Failed to update record to database: Unread result found
Traceback (most recent call last):
File "test1.py", line 21, in <module>
connection.rollback()
File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 863, in rollback
self._execute_query("ROLLBACK")
File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 875, in _execute_query
self.cmd_query(query)
File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 494, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 268, in _send_cmd
return self._socket.recv()
File "/usr/lib/python2.7/dist-packages/mysql/connector/network.py", line 243, in recv_plain
raise errors.InterfaceError(errno=2013)
mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query
$
note, that it happens after 2-3 runs, which means that between choosing backends connections ProxySQL has issues with resetting backend connections.
Tested against ProxySQL version 1.4.11-percona-1.1, codename Truls.