Bug #79790 | mysql-connector-python + mysql 5.7.10 = lost connection during query | ||
---|---|---|---|
Submitted: | 29 Dec 2015 1:43 | Modified: | 3 Apr 2016 8:04 |
Reporter: | Mykola Ulianytskyi | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | Connector / Python | Severity: | S1 (Critical) |
Version: | 2.1.3 | OS: | CentOS (7.2) |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[29 Dec 2015 1:43]
Mykola Ulianytskyi
[2 Jan 2016 22:45]
Mykola Ulianytskyi
Addons: 1. Both cext and pure python versions of the driver are affected; 2. Bug is reproducible also on build with mysql-connector-c; 3. Bug appears more frequently after rollback of big transactions (several millions sql actions): a) run python script: BEGIN + millions of sql INSERT / UPDATE / DELETE queries + ROLLBACK b) run python script again that produces the same sql queries => catch "lost connection during query" after some amount of queries (may be between thousands and millions). If we add some delay (15 min for example) between script executions (wait for decreasing of Innodb_buffer_pool_pages_dirty) => bug frequency will be reduced. Any ideas? Thank you
[2 Jan 2016 22:58]
Mykola Ulianytskyi
4. sql queries are small and contain only latin1 characters 5. script is single-threaded, doesn't fork any child processes
[2 Jan 2016 23:20]
Mykola Ulianytskyi
I have added buffered=True and connection_timeout=300 options and mysql error changed from "lost connection during query" to "Lock wait timeout exceeded; try restarting transaction": self.cnx = _mysql_connector.MySQL(use_unicode=True, raw=False, charset_name='utf8', buffered=True, connection_timeout=300) SHOW ENGINE INNODB STATUS is attached.
[2 Jan 2016 23:20]
Mykola Ulianytskyi
SHOW ENGINE INNODB STATUS
Attachment: innodb_status.txt (text/plain), 10.75 KiB.
[3 Jan 2016 5:36]
Mykola Ulianytskyi
Final description of the bug: If connection_timeout is not specified or connection_timeout < innodb_lock_wait_timeout mysql-connector-python returns errno=2013 (Lost connection to MySQL server during query) instead of errno=1205 (Lock wait timeout exceeded; try restarting transaction) for lock wait timeout exceptions and it is difficult to diagnose cause of the issue without the correct errno.
[12 Jan 2016 14:56]
Josh Dunn
windows 7 python 3.5.1 connector 2.1.3 mysql 5.6.27 querying with 'SELECT mr.meter_id, mr.reading, mr.timestamp FROM meter_readings mr WHERE mr.timestamp BETWEEN "2015-12-15" AND "2015-12-20" AND mr.meter_id=6123' worked fine. Date change to ' "BETWEEN "2015-12-14" AND "2015-12-19" ' resulted in host time out error similar to described above. Both worked fine with query SQLyog.
[12 Jan 2016 14:58]
Josh Dunn
Python Error code was "OperationalError: 2055: Lost connection to MySQL server at '**.***.**.**:****', system error: 10054 An existing connection was forcibly closed by the remote host"
[21 Jan 2016 7:43]
Chiranjeevi Battula
Hello Mykola Ulianytskyi, Thank you for the bug report. This is most likely duplicate of Bug #68711, Please see Andrii comments. Thanks, Chiranjeevi.
[21 Jan 2016 7:53]
Mykola Ulianytskyi
Hi Andrii closed #68711 as 'Not a Bug' with description: > Hi, it doesn't look like Connector/Python problem. > Al least you must provide some proof that it is not some problem described in > http://dev.mysql.com/doc/refman/5.5/en/error-lost-connection.html But in current case #79790 it seems to be a real mysql-connector-python or mysql server bug that is not related to http://dev.mysql.com/doc/refman/5.5/en/error-lost-connection.html and I found how to reproduce it: > If connection_timeout is not specified or connection_timeout < innodb_lock_wait_timeout > mysql-connector-python returns errno=2013 (Lost connection to MySQL server during query) > instead of errno=1205 (Lock wait timeout exceeded; try restarting transaction) for lock wait timeout > exceptions and it is difficult to diagnose cause of the issue without the correct errno. -- Mykola
[3 Mar 2016 8:04]
Chiranjeevi Battula
Hello Mykola Ulianytskyi, Thank you for your feedback. I could not repeat the issue at my end using MySQL 5.7.11 and Connector / Python 2.1.3. Could you please provide repeatable test case (sample code, create table statements, sample data etc. - please make it as private if you prefer) to confirm this issue at our end? Thanks, Chiranjeevi.
[4 Apr 2016 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".