MySQL Bugs: #79790: mysql-connector-python + mysql 5.7.10 = lost connection during query

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:	None
Category:	Connector / Python	Severity:	S1 (Critical)
Version:	2.1.3	OS:	CentOS (7.2)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Hello

Python scripts that use mysql-connector-python (_mysql_connector C Extension Module) and mysql 5.7.10 
sometimes get exception: "Lost connection to MySQL server during query"
during big transactions with millions of selects/inserts/deletes 
if mysql-connector-python has been built with mysql 5.7.10.

If mysql-connector-python has been built with mysql-connector-c (6.1.6) bug is not reproducible.

- centos 7.2 x86_64
- mysql 5.7.10
- python3 3.5.1
- mysql-connector-python 2.1.3

------------------------------------------------------------------------------------------
/etc/my.cnf
------------------------------------------------------------------------------------------
[mysqld]

datadir                             = /var/lib/mysql
socket                              = /var/lib/mysql/mysql.sock
log-error                           = /var/log/mysqld.log
pid-file                            = /var/run/mysqld/mysqld.pid

max_allowed_packet                  = 64M
max_connections                     = 64
performance_schema                  = OFF
skip_name_resolve                   = ON

tmp_table_size                      = 128M

innodb_buffer_pool_size             = 8192M
innodb_flush_log_at_trx_commit      = 0
innodb_log_file_size                = 128M
innodb_flush_neighbors              = 1
innodb_doublewrite                  = OFF
innodb_flush_method                 = O_DIRECT    

innodb_undo_tablespaces             = 2
innodb_undo_logs                    = 35
innodb_undo_log_truncate            = ON
innodb_max_undo_log_size            = 10M

------------------------------------------------------------------------------------------
/var/log/mysqld.log:
...
[Note] InnoDB: page_cleaner: 1000ms intended loop took 8815ms. The settings might not be optimal. (flushed=341 and evicted=0, during the time.)

[Note] Aborted connection 1763 to db: 'test' user: 'test' host: 'xxx.xxx.xxx.xxx' (Got an error reading communication packets)

Please fix this error.

Thank you

--
With best regards,
Mykola

How to repeat:
xxx

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

4. sql queries are small and contain only latin1 characters

5. script is single-threaded, doesn't fork any child processes

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.

SHOW ENGINE INNODB STATUS

Attachment: innodb_status.txt (text/plain), 10.75 KiB.

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.

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.

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"

Hello Mykola Ulianytskyi,

Thank you for the bug report.
This is most likely duplicate of Bug #68711, Please see Andrii comments.

Thanks,
Chiranjeevi.

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

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.

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".