[MDEV-6757] autocommit does not work in MySQLdb Python connector with MariaDB Created: 2014-09-18  Updated: 2014-09-27  Resolved: 2014-09-26

Status: Closed
Project: MariaDB Server
Component/s: Platform Power
Affects Version/s: 5.5.37
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Alan Evangelista Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Hardware architecture: x86
Operating system: Red Hat 7.0 GA
MySQL Python connector version: 1.2.3


 Description   

If i run the following command in Python command line:

import MySQLdb
 
connArgs={"host": <server_url>,
 
                   "user": <user>,
 
                   "passwd": <password>,
 
                   "db": <database_name>}
 
conn = MySQLdb.connect(**connArgs)
 
conn.autocommit = True
 
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
 
query = <insert_sql_query>
 
cursor.execute(query)

and then check the MariaDB database, the inserted tuple does not show up in a SELECT query output. If I commit as shown below, the new tuple shows up in SELECT query output.

conn.commit()

This works correctly in MySQL server 5.1.66, same MySQLdb module version.

 Comments   
Comment by Elena Stepanova [ 2014-09-26 ]

It works identically with MySQL 5.1.66 and MariaDB (or MySQL 5.5), see http://mysql-python.sourceforge.net/FAQ.html:

Starting with 1.2.0, MySQLdb disables autocommit by default, as required by the DB-API standard (PEP-249). If you are using InnoDB tables or some other type of transactional table type, you'll need to do connection.commit() before closing the connection, or else none of your changes will be written to the database.

For your conn.autocommit = True call, I'm not quite sure what it is supposed to do, MySQLdb does not seem to support autocommit attribute for connections: http://mysql-python.sourceforge.net/MySQLdb.html#mysqldb

The most likely reason why you see the difference is that you use a default storage engine, which is MyISAM in 5.1 and InnoDB in 5.5. So, in 5.1 the presence or absence of autocommit won't make any difference, while in 5.5 it will.

To double-check, create the table with an explicit engine on both servers and run the test.

Comment by Alan Evangelista [ 2014-09-27 ]

You are right about default storage engine, the involved table is using MyISAM in MySQL server and it is using InnoDB in MariaDB server. Therefore, this autocommit statement I am using never worked.

I decided to investigate this further. MySQLdb connection class inherits from _mysql.connection class, which is provided by _mysql.so. In the MySQLdb documentation you mentioned, I read "There are many more methods defined on the connection object which are MySQL-specific. For more information on them, consult the internal documentation using pydoc.". Running pydoc _mysql, I see:

(...)
CLASSES
(...)
class connection(_builtin_.object)
(...)

Methods defined here:
autocommit(...)
Set the autocommit mode. True values enable; False value disable.

I changed my code from

conn.autocommit = True

to 

conn.autocommit(True)

and now autocommit works in MariaDB. I got confused by some incorrect information posted in Internet.

Thanks for the help!

Generated at Thu Feb 08 07:14:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.