Bug #76880 Connector/Python - Unexplainable commit behaviour.
Submitted: 29 Apr 2015 8:56 Modified: 10 May 2015 17:28
Reporter: Stuart Wingate Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / Python Severity:S3 (Non-critical)
Version:2.0.4 OS:Linux (Red Hat Enterprise Linux Server release 6.6)
Assigned to: CPU Architecture:Any
Tags: Connector/Python, MySql Enterprise 5.6.14-log

[29 Apr 2015 8:56] Stuart Wingate
Description:
Implemented data transfer from SQL Server to MySQL for Fujitsu Finland customer using pymssql and mysql-connector. Four tables, 300,000 rows at most.
Data is committed line line from fieldlist in batches of 10000 because of global max_allowed_packets - committing every batch and then any remainder.
Batch routine is situated in sub-module and called from main program.
The batch routine reports how many rows are in the table at end of process.
After the last table in the sequence is committed and rows returned, a count query of that last table after script completion reveals the table to contain 0 rows, although the batch routine reports the expected number of rows.
Count query of the other tables populated by the same batch routine reveals the tables with connect number of rows.
This happens irrespective of the what table is the last, always the last table will appear to be populated by the script, but prove to have no rows after script run.
If the main routine is run using just one table, that table will also contain 0 rows after script completion. The connections are closed in the main program.
The behaviour appears to be that the last table is loaded and committed, but somehow that is rolled back before end of the script.
Inspection of mysql general query log reveals no rollback commands and no errors.
The workaround was to put a final commit in the main program before connection closure, however it is not possible to explain why the same piece of code populates preceding tables and commits them, but for some reason the last table is seemingly rolled back before connection end without any indication of a rollback in the query log, nor any reason why.

   

How to repeat:
I can the run the code on my local Red Hat guest on Virtual box, same Python version (2.7.9).
[6 May 2015 11:18] Peeyush Gupta
Connector/Python follows the python DBAPI which specifies autocommit to be OFF by default. For reference https://www.python.org/dev/peps/pep-0249/#commit