[MDEV-6757] autocommit does not work in MySQLdb Python connector with MariaDB - JIRA

Details

Type: Bug
Status: Closed
Priority: Minor
Resolution: Not a Bug
Affects Version/s: 5.5.37
Fix Version/s: N/A
Component/s: Platform Power
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.

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2014-09-26 23:46

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.

Show

Elena Stepanova added a comment - 2014-09-26 23:46 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.

Hide

Permalink

Alan Evangelista added a comment - 2014-09-27 19:45 - edited

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!

Show

Alan Evangelista added a comment - 2014-09-27 19:45 - edited 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!

autocommit does not work in MySQLdb Python connector with MariaDB

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile