We've occasionally seen Django processes hanging around after web server restarts. Yesterday we attached gdb to one of these processes to try and figure out why. We saw the following stack trace:

(gdb) thread 6
[Switching to thread 6 (Thread 0x7f0772ecd700 (LWP 11709))]
#0 0x00007f078dd3dfd0 in sem_wait () from /lib/x86_64-linux-gnu/libpthread.so.0

(gdb) bt
#0 0x00007f078dd3dfd0 in sem_wait () from /lib/x86_64-linux-gnu/libpthread.so.0
#1 0x0000000000516e37 in tstate_delete_common.41819 ()
#2 0x00000000004f7ff1 in t_bootstrap.49012 ()
#3 0x00007f078dd37e9a in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
#4 0x00007f078cb113fd in clone () from /lib/x86_64-linux-gnu/libc.so.6
#5 0x0000000000000000 in ?? ()

(gdb) thread 1
[Switching to thread 1 (Thread 0x7f078e162700 (LWP 3603))]
#0 0x00007f078dd3dfd0 in sem_wait () from /lib/x86_64-linux-gnu/libpthread.so.0

(gdb) bt
#0 0x00007f078dd3dfd0 in sem_wait () from /lib/x86_64-linux-gnu/libpthread.so.0
#1 0x000000000051654f in PyEval_RestoreThread ()
#2 0x00007f07892880ac in _mysql_ConnectionObject_close (self=0x7f0774089460, args=) at _mysql.c:704
#3 0x00007f078928812a in _mysql_ConnectionObject_dealloc (self=0x7f0774089460) at _mysql.c:2022
#4 0x00000000005020b8 in subtype_dealloc.25364 ()
#5 0x000000000042618d in dict_dealloc.18153 ()
#6 0x000000000057a5b5 in PyDict_DelItem ()
#7 0x000000000057a987 in _localdummy_destroyed.49047 ()
#8 0x00000000004d91b6 in PyObject_Call ()
#9 0x00000000004d9e1b in PyObject_CallFunctionObjArgs ()
#10 0x00000000004f3057 in handle_callback ()
#11 0x00000000004f3212 in PyObject_ClearWeakRefs ()
#12 0x00000000004f3fe0 in localdummy_dealloc ()
#13 0x000000000042618d in dict_dealloc.18153 ()
#14 0x00000000005185c3 in PyThreadState_Clear ()
#15 0x0000000000518993 in PyInterpreterState_Clear ()
#16 0x00000000004f6318 in Py_Finalize ()
#17 0x00000000004c708a in Py_Main ()
#18 0x00007f078ca3e76d in __libc_start_main () from /lib/x86_64-linux-gnu/libc.so.6
#19 0x000000000041ba41 in _start ()

(gdb) quit

It looks like Thread 1 initially had acquired the GIL and ran PyInterpreterState_Clear which also acquired a lock on the head_mutex. However, the cleanup of the thread's MySQL connection objects makes a call into _mysql_ConnectionObject_close, which then releases the GIL for the mysql_close call. This allows Thread 6 to acquire the GIL and run. However as that thread is exiting it attempts to acquire the head_mutex in tstate_delete_common. This means Thread 6 now has the GIL and is attempting to acquire the head_mutex lock, whereas Thread 1 has locked head_mutex and is attempting to acquire the GIL resulting in a deadlock.

I see no documentation on python.org that says that releasing the GIL in a dealloc function is a bad plan, however it seems to have caused this issue. It should be ok to not give up the GIL in the MySQL connection close function, which would seem to prevent this from happening. Thoughts?

In converters.py, there are rules to take ListTypes and TupleTypes to MySQL literals (which are used when you pass a list as a parameter).

Unfortunately, the code that does this (escape_sequence) uses a Python tuple to produce the string for MySQL insertion, which is a bad idea when you have a single element list (producing things like ('1',) - i.e. invalid SQL). I'm worried that I'm not understanding the intent of this code properly, though, since the action for a DictType appears to be completely insane (at least in the cursor.execute context). Should I only be using 'sets' (where the appropriate join is run)?

Currently I'm working around this as follows:

def fixed_escape_sequence(o, d):
    if len(o) == 1:
        return '(%s)' % MySQLdb.converters.escape(o[0], d)
    else:
        return MySQLdb.converters.escape_sequence(o, d)
from copy import copy
conversions = copy(MySQLdb.converters.conversions)
conversions[types.ListType] = fixed_escape_sequence
conversions[types.TupleType] = fixed_escape_sequence

But it would be nicer if escape_sequence was fixed in the C code.

Hello,

I was searching some memory leaks, so I used the /tests/test_MySQLdb_capabilities.py file on this project, and set the leak_test attribute to True inside.
And every tests was leaking.

So I made some other tests, with basic code such as:

    connection = MySQLdb.connect(**connect_kwargs)
    cursor = connection.cursor()
    connection.ping()
    cursor.close()
    connection.close()

Or

    with MySQLdb.connect(**connect_kwargs) as cursor:
        cursor.connection.ping()

And everytime gc was reporting some unreachable garbage:

[<cell at 0x7f9df6ad8868: weakproxy object at 0x2709f18>, <cell at 0x7f9df6ad87c0: function object at 0x2732c08>, <cell at 0x7f9df6ad8fd8: function object at 0x2732b90>, <weakproxy at 0x2709f18 to NoneType at 0x917730>, (<cell at 0x7f9df6ad8868: weakproxy object at 0x2709f18>, <cell at 0x7f9df6ad8fd8: function object at 0x2732b90>), <function funicode_literal at 0x2732b90>, (None,), (<cell at 0x7f9df6ad87c0: function object at 0x2732c08>,), <function fstring_decoder at 0x2732c08>]

I am maybe wrong, I can send you my tests if you want, but I think it comes from https://github.com/farcepest/MySQLdb1/blob/master/MySQLdb/connections.py#L200-214 :

       (...inside connection __init__()...)
        db = proxy(self)
        def _get_string_literal():
            def string_literal(obj, dummy=None):
                return db.string_literal(obj)
            return string_literal

        def _get_unicode_literal():
            def unicode_literal(u, dummy=None):
                return db.literal(u.encode(unicode_literal.charset))
            return unicode_literal

        def _get_string_decoder():
            def string_decoder(s):
                return s.decode(string_decoder.charset)
            return string_decoder

Here you define some closure inside the init, and some closures in the closures, then you use a self proxy weak reference on the connection object. Strange code. But it seems my python 2.7 does not like this double closure using a weak reference on the object, and does not know how to remove it on cleanup.

I tried to alter it to return weak proxy reference on the functions, then it did not work anymore as theses functions were cleared right after the init. So I also added some hacked attributes on the connection object to increment the ref counter. And it worked, on connection removal theses attribute were automatically set to none, and the weak ref on theses functions prevented the unreachable garbage:

       (...inside connection __init__()...)
        db = proxy(self)
        def _get_string_literal():
            def string_literal(obj, dummy=None):
                return db.string_literal(obj)
            # adding fake reference counter on the connection object
            db.fake_refcount1 = string_literal
            # returning a weak reference on this function
            return proxy(string_literal)

        def _get_unicode_literal():
            def unicode_literal(u, dummy=None):
                return db.literal(u.encode(unicode_literal.charset))
            db.fake_refcount2 = unicode_literal
            return proxy(unicode_literal)

        def _get_string_decoder():
            def string_decoder(s):
                return s.decode(string_decoder.charset)
            db.fake_refcount3 = string_decoder
            return proxy(string_decoder)

This is an ugly patch, but at least if fixed the leak.

I noticed today that OperationalError is thrown on a bunch of MySQL errors which should not be OperationalErrors. For example Error: 1054 SQLSTATE: 42S22 (ER_BAD_FIELD_ERROR) throws an OperationalError when it should throw ProgrammingError. The issue is that OperationalError is very useful if you want to programmatically detect actual connection issues. Having it thrown on a syntax related issue makes this task much harder.

One option would be to choose a different fallback exception. OperationalError, according to the documentation, should be thrown on issues related to MySQL's operation. For example: too many connections, bad handshake, etc. It may be safer to choose the generic "DatabaseError" as the fallback since that would be more accurate for the majority of the missing exceptions.

Another option is to categorize the rest of the MySQL errors and throw the correct exception for them all. Obviously this is a lot of work, but it would benefit the community a ton. Let me know if this is the path you want to take and I would be happy to help out.

Thanks!

Latest Ubuntu x64, Python 2.7.3, pip 1.3.1:

$ sudo pip install MySQL-python==1.2.4

Downloading/unpacking MySQL-python==1.2.4
  Downloading MySQL-python-1.2.4.zip (113kB): 113kB downloaded
  Running setup.py egg_info for package MySQL-python
    Traceback (most recent call last):
      File "<string>", line 16, in <module>
      File "/tmp/pip-build-root/MySQL-python/setup.py", line 14, in <module>
        from setup_posix import get_config
      File "./setup_posix.py", line 2, in <module>
        from ConfigParser import SafeConfigParser
    ImportError: No module named 'ConfigParser'
    Complete output from command python setup.py egg_info:
    Traceback (most recent call last):

  File "<string>", line 16, in <module>

  File "/tmp/pip-build-root/MySQL-python/setup.py", line 14, in <module>

    from setup_posix import get_config

  File "./setup_posix.py", line 2, in <module>

    from ConfigParser import SafeConfigParser

ImportError: No module named 'ConfigParser'

MAC OSX 10.9.2
XCODE 5.1

The issue here is that both NULL datetimes and '0000-00-00' datetimes in the context of MySQL have the same meaning in the context of MySQLdb (both are represented as None) thus data will be lost in case of (as an example) data being copied from one data base to another.

I'm not seeing a solution here - yet -, as you can't have a Datetime object with day, month and year as 0 values.

My solution, for my specific problem is this (using MySQLdb-1.2.4, installed with pip), on times.py (line 79):

def Date_or_None(s):
    if s[0:4] == '0000': # this is my solution
        return s
    try: return date(*[ int(x) for x in s.split('-',2)])
    except: return None

So I'm returning the original Datetime representation string (lines 2 and 3 of code block were added by me).

I've noticed a change in behavior for storing datetime.datetime.max via SQLAlchemy==SQLAlchemy==0.8.1 and going from mysql-python==1.2.3 to mysql-python==1.2.4. By only changing the driver from 1.2.3 to 1.2.4 I go from being able to store to being unable to store it.

Is this expected behaviour or a bug or do I have a bad setup? I fear that a change like this will break a lot of systems out there.

This is my SQLAlchemy setup:

from sqlalchemy import create_engine, Integer, DateTime, Column
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from datetime import datetime

engine = create_engine('mysql://root@localhost/test_database', echo=True)
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    age = Column(DateTime, default=datetime.max)

Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()
u = User()
session.add(u)
session.commit()

I also have a virtualenv called test. This is what happens when I run the code above.

(test)➜  ~  pip install MySQL-python==1.2.3
(test)➜  ~  python test.py
2013-06-26 10:29:18,885 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2013-06-26 10:29:18,885 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:29:18,887 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'character_set%%'
2013-06-26 10:29:18,887 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:29:18,891 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2013-06-26 10:29:18,891 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:29:18,896 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2013-06-26 10:29:18,896 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:29:18,904 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-06-26 10:29:18,905 INFO sqlalchemy.engine.base.Engine INSERT INTO users (age) VALUES (%s)
2013-06-26 10:29:18,905 INFO sqlalchemy.engine.base.Engine (datetime.datetime(9999, 12, 31, 23, 59, 59, 999999),)
2013-06-26 10:29:18,908 INFO sqlalchemy.engine.base.Engine COMMIT

And the database (test_database) looks like this:

mysql> select * from users;
+----+---------------------+
| id | age                 |
+----+---------------------+
|  1 | 9999-12-31 23:59:59 |
+----+---------------------+
1 row in set (0.00 sec)

This is my expected result so nothing strange here.

However, by simply switching the driver to mysql-python==1.2.4 I get this result.

(test)➜  ~  pip install MySQL-python==1.2.4
(test)➜  ~  python test.py
2013-06-26 10:33:39,544 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2013-06-26 10:33:39,544 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:33:39,546 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'character_set%%'
2013-06-26 10:33:39,546 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:33:39,546 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2013-06-26 10:33:39,546 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:33:39,547 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2013-06-26 10:33:39,547 INFO sqlalchemy.engine.base.Engine ()
2013-06-26 10:33:39,551 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-06-26 10:33:39,552 INFO sqlalchemy.engine.base.Engine INSERT INTO users (age) VALUES (%s)
2013-06-26 10:33:39,552 INFO sqlalchemy.engine.base.Engine (datetime.datetime(9999, 12, 31, 23, 59, 59, 999999),)
/Users/pelle/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/default.py:324: Warning: Datetime function: datetime field overflow
  cursor.execute(statement, parameters)
/Users/pelle/.virtualenvs/test/lib/python2.7/site-packages/sqlalchemy/engine/default.py:324: Warning: Out of range value for column 'age' at row 1
  cursor.execute(statement, parameters)
2013-06-26 10:33:39,553 INFO sqlalchemy.engine.base.Engine COMMIT

And the database looks like this.

mysql> select * from users;
+----+---------------------+
| id | age                 |
+----+---------------------+
|  1 | 0000-00-00 00:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

So now all of the sudden I receive a warning Warning: Datetime function: datetime field overflow and I end up with a nullable value in my database.

i use MySQLdb with DBUtils.
dbutil.py:

g_dbpool = PooledDB(creator=MySQLdb, mincached=2, maxcached=40, host=config.DBHOST, port=config.DBPORT, user=config.DBUSER, passwd=config.DBPASSWORD, db=config.DBSCHEMA)

def execute(sql):
conn = g_dbpool.connection()
cur = conn.cursor()
count = cur.execute(sql)
conn.commit()
cur.close()
conn.close()
return count

but i got below exception sometimes.

count = cur.execute(sql)
File "/usr/local/lib/python2.7/site-packages/DBUtils-1.1-py2.7.egg/DBUtils/SteadyDB.py", line 552, in tough_method
result = method(_args, *_kwargs) # try to execute
File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 176, in execute
if not self._defer_warnings: self._warning_check()
File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 85, in _warning_check
warnings = self._get_db().show_warnings()
File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 319, in show_warnings
warnings = r.fetch_row(0)
AttributeError: 'NoneType' object has no attribute 'fetch_row'

The commit ad11930 (2013-1-1) should be tagged in the repo as 1.2.4

Trying to build with ARCHFLAGS=-Wno-error=unused-command-line-argument-hard-error-in-future sudo -E python setup.py build, but I get the following error:

cc -bundle -undefined dynamic_lookup -Wl,-F. -Wno-error=unused-command-line-argument-hard-error-in-future -Wno-error=unused-command-line-argument-hard-error-in-future build/temp.macosx-10.9-intel-2.7/_mysql.o -L/usr/local/mysql/lib -lmysqlclient -lzlib -lmsvcrt -llibcmt -lwsock32 -ladvapi32 -o build/lib.macosx-10.9-intel-2.7/_mysql.so
ld: library not found for -lzlib
clang: error: linker command failed with exit code 1 (use -v to see invocation)
error: command 'cc' failed with exit status 1

I couldn't install MySQL-python module through pip on Windows 8.1 x64 because the site.cfg file points to the 32 bits version of the MySQL connector. To solve it I had to download source and modify it, removing the "(x86)" from connector variable:

connector = C:\Program Files\MySQL\MySQL Connector C 6.0.2

Could you add a check so if you are building on a Win x64 platform use the x64 version of the connector?

Also a little description of the install prerequisites on INSTALL file or on a webpage would be nice.
Prior to installation I had to download:

• Microsoft Visual C++ Compiler for Python 2.7
  http://www.microsoft.com/en-us/download/details.aspx?id=44266
• Oracle MySQL C Connector
  http://dev.mysql.com/downloads/connector/c/6.0.html

Thank you very much.

Just wondering if you could upload wheels of MySQL-python to PyPI for Windows. The installer is nice, but doesn't help when using virtual environments. Should just be as simple as setup.py bdist_wheel upload (after installing the wheel package).

As a bonus, you get a tick next to your package on http://pythonwheels.com/ :)

_mysql is one of the best parts of MySQLdb, and the latest release breaks the escape function. Up till now, when passed a list or tuple, escape would return a list or tuple with each element escaped. Now when passed a list or tuple, escape returns a string.

As much as that feature is nice, it is not backwards compatible for people who depend on escape to return a list when passed a list.

At the very least make this a major or minor version bump and add it to the HISTORY (and maybe README). Your library is depended on by a huge amount of people, and changes like this are pretty dangerous.

The best solution would be to make escape continue to return a sequence if passed a sequence, but to escape any sub-sequences inside the root sequence.

Thanks.

According to python2.6/distutils/version.py:

 63 class StrictVersion (Version):
 64 
 65     """Version numbering for anal retentives and software idealists.
 66     Implements the standard interface for version number classes as
 67     described above.  A version number consists of two or three
 68     dot-separated numeric components, with an optional "pre-release" tag
 69     on the end.  The pre-release tag consists of the letter 'a' or 'b'
 70     followed by a number.

According to this some of your earlier versions are not valid, namely:
1.2.4c1
1.2.3c1
1.2.2c1
1.2.1_p2
1.2.1c8
1.2.1c7
1.2.1c6
1.2.1c5
1.2.1c4
1.2.1c3
1.2.1c2
1.2.1c1
1.0.1c1
1.0.0c2
0.9.2c3
0.9.2c2
0.9.2c1
0.9.1c2
0.9.1c1

This has a side effect when comparing versions with the standard python utils (and possibly when trying to update older packages) that the X.Y.Zc1 is considered newer than X.Y.Z

Please have this in mind for your next releases.

This query worked in 1.2.3 but now fails.

cursor = conn.cursor()

cursor.executemany(
    'INSERT INTO foo (bar, baz) VALUES (%s, LEAST(UNIX_TIMESTAMP(), %s))',
    [('bar', 1), ('baz', 2)])

Results in:

Traceback (most recent call last):
  File "/home/$USER/mysql-syntax-error.py", line 11, in <module>
    [('bar', 1), ('baz', 2)])
  File "/...//MySQLdb/cursors.py", line 252, in executemany
    r = self._query('\n'.join([query[:p], ',\n'.join(q), query[e:]]))
  File "/...//MySQLdb/cursors.py", line 344, in _query
    rowcount = self._do_query(q)
  File "/...//MySQLdb/cursors.py", line 308, in _do_query
    db.query(q)
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4")
>>>

Hello,

Trying to test connections with utf8 used in database names or database users I saw that the _mysql.connect() C code is maybe failing to launch a mysql_option to set the connection character set.
From http://dev.mysql.com/doc/refman/5.5/en/mysql-real-connect.html :

The user and passwd parameters use whatever character set has been configured for the
MYSQL object. By default, this is latin1, but can be changed by calling
mysql_options(mysql, MYSQL_SET_CHARSET_NAME, "charset_name") prior to
connecting.

It's maybe the problem, maybe not, as I only have this problem with the user and db parameter and not for the passwd one.

So here is a test file, I create 2 databases foo1and fooé, two users foo1 and fooé, and passwords for theses users on the 2 database are always fooé.

• user foo1, db foo1, password fooé => OK
• user foo1, db fooé, password fooé => NOK
• user fooé, db foo1, password fooé => NOK
• user fooé, db fooé, password fooé => NOK

And then using utf-8 fooé -> unicode -> latin for db and user strings on the connection is fixing the problem. In parallel I make shell calls with the mysql client, this client works with utf-8 strings and fails with the latin1 strings (which is the valid behavior)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb
import warnings
import sys
import os

warnings.filterwarnings('error', category=MySQLdb.Warning)

def run_query(cur,qry):
    try:
        run = cur.execute(qry)
    except :
        e = sys.exc_info()
        err = 'MySQL Exception: {0!r}: {1!r}'.format(*e)
        print err

def init_test():
    rootuser = 'root'
    rootpwd = ''
    conn = MySQLdb.connect(
        host='localhost',
        user=rootuser,
        passwd=rootpwd,
        db='mysql',
        use_unicode=True,
        charset='utf8'
    )
    cur = conn.cursor()
    #foo1
    print "Create db foo1"
    qry='CREATE DATABASE IF NOT EXISTS `foo1` CHARACTER SET utf8 COLLATE utf8_general_ci;'
    run_query(cur,qry)
    # fooé
    print "Create db foo\xc3\xa9"
    qry='CREATE DATABASE IF NOT EXISTS `foo\xc3\xa9` CHARACTER SET utf8 COLLATE utf8_general_ci;'
    run_query(cur,qry)

    # create 2 users foo1 and fooé, both with password fooé
    print "Create user : foo1 pwd: foo\xc3\xa9"
    qry="GRANT ALL PRIVILEGES ON foo1.* TO 'foo1'@'localhost' IDENTIFIED BY 'foo\xc3\xa9';"
    run_query(cur,qry)
    qry="GRANT ALL PRIVILEGES ON `foo\xc3\xa9`.* TO 'foo1'@'localhost' IDENTIFIED BY 'foo\xc3\xa9';"
    run_query(cur,qry)
    print "Create user : foo\xc3\xa9 pwd: foo\xc3\xa9"
    qry="GRANT ALL PRIVILEGES ON foo1.* TO 'foo\xc3\xa9'@'localhost' IDENTIFIED BY 'foo\xc3\xa9';"
    run_query(cur,qry)
    qry="GRANT ALL PRIVILEGES ON `foo\xc3\xa9`.* TO 'foo\xc3\xa9'@'localhost' IDENTIFIED BY 'foo\xc3\xa9';"
    run_query(cur,qry)
    qry="FLUSH PRIVILEGES;"
    run_query(cur,qry)
    qry="show variables like 'character%'";
    shell= 'mysql -u "' + rootuser + '" --password="' + rootpwd + '" -h "localhost" -D "mysql" -N -e "' + qry + '"'
    print os.system(shell)

def clean_test():
    rootuser = 'root'
    rootpwd = ''
    conn = MySQLdb.connect(
        host='localhost',
        user=rootuser,
        passwd=rootpwd,
        db='mysql',
        use_unicode=True,
        charset='utf8'
    )
    cur = conn.cursor()
    #foo1
    print "Drop db foo1"
    qry='DROP DATABASE `foo1`;'
    run_query(cur,qry)
    # fooé
    print "Drop db foo\xc3\xa9"
    qry='DROP DATABASE `foo\xc3\xa9`;'
    run_query(cur,qry)

    # create 2 users foo1 and fooé, both with password fooé
    print "Remove user : foo1 pwd: foo\xc3\xa9"
    qry="DROP USER 'foo1'@'localhost';"
    run_query(cur,qry)
    print "Remove user : foo\xc3\xa9 pwd: foo\xc3\xa9"
    qry="DROP USER 'foo\xc3\xa9'@'localhost';"
    run_query(cur,qry)
    qry="FLUSH PRIVILEGES;"
    run_query(cur,qry)


def test_conn(db,user,pwd):
    print "\n===> [TEST] : CONNECTING %(user)s ON %(db)s WITH PASSWORD %(pwd)s" % {
        'user':user,
        'db':db,
        'pwd':pwd
    }
    print "\n-> SHELL mysql command TEST:"
    shell= 'mysql -u "' + user + '" --password="' + pwd + '" -h "localhost" -D "' + db + '" -N -e "SELECT \'OK\'"'
    print shell
    print os.system(shell)
    print "->Trying with MySQLdb:"
    try:
        conn = MySQLdb.connect(
            host='localhost',
            user=user,
            passwd=pwd,
            db=db,
            use_unicode=True,
            charset='utf8'
        )
        cur = conn.cursor()
        print "=================> [OK]\n"
    except :
        e = sys.exc_info()
        err = 'MySQL Exception: {0!r}: {1!r}'.format(*e)
        print err


def connect_test():
    test_conn(db='foo1', user='foo1', pwd='foo\xc3\xa9')
    test_conn(db='foo\xc3\xa9', user='foo1', pwd='foo\xc3\xa9')
    test_conn(db='foo1', user='foo\xc3\xa9', pwd='foo\xc3\xa9')
    test_conn(db='foo\xc3\xa9', user='foo\xc3\xa9', pwd='foo\xc3\xa9')
    # REDO the 3 last failing tests by enforcing 'latin1'
    print "\n=========REDO the 3 last failing tests by enforcing 'latin1'==="
    print "\n=========The shell test will fail but not MySQLdb===\n"
    test_conn(db='foo\xc3\xa9'.decode('utf8').encode('latin1'), user='foo1', pwd='foo\xc3\xa9')
    test_conn(db='foo1', user='foo\xc3\xa9'.decode('utf8').encode('latin1'), pwd='foo\xc3\xa9')
    test_conn(db='foo\xc3\xa9'.decode('utf8').encode('latin1'), user='foo\xc3\xa9'.decode('utf8').encode('latin1'), pwd='foo\xc3\xa9')

if __name__ == '__main__':
    print MySQLdb.__version__
    init_test()
    connect_test()
    clean_test()

Hi there,

Just ran into a small issue when trying to install mysqldb 1.2.5 from source on windows (I'm using zc.buildout for dependencies management in dev and deployment, so can't use the windows installer) and python 2.7.

The issue was related to a manifest file not generated, described in an old python bug report here http://bugs.python.org/issue4431. This discussion provides a way to solve it: I simply had to modify setup_windows.py and add extra_link_args=['/MANIFEST'] to the ext_options dictionary.

I would also like make a suggestion to add several connector paths in the site.cfg, to handle win32 platforms (with C:\Program Files instead of C:\Program Files (x86) ). So that win32 users would not have to download the archive and edit site.cfg.

I can submit a pull request for these changes, or leave you implement them.

Thanks.

Thomas

Original bug on https://sourceforge.net/p/mysql-python/bugs/325/

When SELECT datatime field which contained microseconds from DB(MariaDB 5.5 and MySQL 5.6). Result can not be converted to datetime.datetime and data in that field shows as None. I use MariaDB 5.5.27.

For example:
Make table in DB and fill its

import MySQLdb
db=MySQLdb.connect(host="localhost",user="test",passwd="test",db="test")
c=db.cursor()
c.execure("""DROP TABLE IF EXISTS `t1`""")
c.execure("""CREATE TABLE `t1` (
  `id` int(11)
  `date` datatime(6) NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8""")
c.execure("""INSERT INTO t1 VALUES(1,now(6)), VALUES(2,NOW(6)), VALUES(3,NOW(6))""")

Now we can get data.

c.execute("""SELECT * FROM t1""")
r = c.fetchall()
print r

OUT:
((1L, None), (2L, None), (3L, None))

After patch:

c.execute("""SELECT * FROM t1""")
r = c.fetchall()
print r

OUT:
((1L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)), (2L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)), (3L, datetime.datetime(2012, 10, 3, 10, 56, 47, 800462)))

Hi everybody, I have a big problem with my mac, I've done everything and nothing helps. Maybe you can help me.

Here I explain the problem: http://stackoverflow.com/questions/16566331/error-installing-mysql-python-on-mac

Thanks!

I'm having issues with Python, MySQL and creating a table with two timestamp columns.

Executing the following SQL through Python and using MySQLDB:

CREATE TABLE test_db.test_with_two_datetime_columns (
first_datetime_field TIMESTAMP(6) NOT NULL DEFAULT 0,
second_datetime_field TIMESTAMP(6) NOT NULL DEFAULT 0
) ENGINE=InnoDB
I get the error (1067, Invalid default value for 'first_datetime_field'), whereas I can easily fire of the following equal command in the MySQL CLI:

$ mysql -h localhost

CREATE TABLE test_db.test_with_two_datetime_columns (
first_datetime_field TIMESTAMP(6) NOT NULL DEFAULT 0,
second_datetime_field TIMESTAMP(6) NOT NULL DEFAULT 0
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
Similarly through Sequel Pro the above command executes fine.

So I'm guessing the problem is with MySQLDB (aka. MySQL-Python), but that seems very weird, IMHO.

Running this query instead is ok:

CREATE TABLE test_db.test_with_two_datetime_columns (
first_datetime_field TIMESTAMP(6) NULL DEFAULT NULL,
second_datetime_field TIMESTAMP(6) NULL DEFAULT NULL
) ENGINE=InnoDB""")

I'm using MySQL 5.6.17, MySQL-Python 1.2.5 and Python 2.7.

The Stackoverflow thread: http://stackoverflow.com/questions/24511573/mysql-and-python-does-not-allow-two-timestamp-columns/24512549

Hello,

I've been trying to install Python-MySQL to my linux-based server for quite a while, but I'm having considerable difficulty. When trying to "python setup.py build" OR "pip install python-mysql" the setup crashes. Here is the log:

    ~ # pip install mysql-python
    Downloading/unpacking mysql-python
    Downloading MySQL-python-1.2.4.zip (113kB): 113kB downloaded
    Running setup.py egg_info for package mysql-python
    Downloading http://pypi.python.org/packages/source/d/distribute/distribute-0.6.28.tar.gz
    Extracting in /tmp/tmpzbri8D
    Now working in /tmp/tmpzbri8D/distribute-0.6.28
    Building a Distribute egg in /tmp/pip_build_root/mysql-python
    /tmp/pip_build_root/mysql-python/distribute-0.6.28-py2.7.egg

    Installing collected packages: mysql-python
    Running setup.py install for mysql-python
    building '_mysql' extension
    /usr/local/i686-linux-gnu/bin/i686-linux-gnu-ccache-gcc -fno-strict-aliasing -I/usr/syno/include/ncurses/ -I. -I./Modules/expat/ -I/usr/local/i686-linux-gnu/include -DSYNO_X86 -O2 -I/usr/syno/include -g -DSYNO_PLATFORM=X64 -g -DSDK_VER_MIN_REQUIRED=400 -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -fPIC -Dversion_info=(1,2,4,'final',1) -Dversion=1.2.4 -I/usr/local/include -I/usr/local/include/python2.7 -c _mysql.c -o build/temp.linux-x86_64-2.7/_mysql.o -g -DUNIV_LINUX -DUNIV_LINUX
    unable to execute /usr/local/i686-linux-gnu/bin/i686-linux-gnu-ccache-gcc: No such file or directory
    error: command '/usr/local/i686-linux-gnu/bin/i686-linux-gnu-ccache-gcc' failed with exit status 1
    Complete output from command /usr/bin/python -c "import setuptools;file='/tmp/pip_build_root/mysql-python/setup.py';exec(compile(open(file).read().replace('\r\n', '\n'), file, 'exec'))" install --record /tmp/pip-kb3_p7-record/install-record.txt --single-version-externally-managed:
    running install

    running build

    running build_py

    creating build

    creating build/lib.linux-x86_64-2.7

    copying _mysql_exceptions.py -> build/lib.linux-x86_64-2.7

    creating build/lib.linux-x86_64-2.7/MySQLdb

    copying MySQLdb/init.py -> build/lib.linux-x86_64-2.7/MySQLdb

    copying MySQLdb/converters.py -> build/lib.linux-x86_64-2.7/MySQLdb

    copying MySQLdb/connections.py -> build/lib.linux-x86_64-2.7/MySQLdb

    copying MySQLdb/cursors.py -> build/lib.linux-x86_64-2.7/MySQLdb

    copying MySQLdb/release.py -> build/lib.linux-x86_64-2.7/MySQLdb

    copying MySQLdb/times.py -> build/lib.linux-x86_64-2.7/MySQLdb

    creating build/lib.linux-x86_64-2.7/MySQLdb/constants

    copying MySQLdb/constants/init.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants

    copying MySQLdb/constants/CR.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants

    copying MySQLdb/constants/FIELD_TYPE.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants

    copying MySQLdb/constants/ER.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants

    copying MySQLdb/constants/FLAG.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants

    copying MySQLdb/constants/REFRESH.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants

    copying MySQLdb/constants/CLIENT.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants

    running build_ext

    building '_mysql' extension

    creating build/temp.linux-x86_64-2.7

    /usr/local/i686-linux-gnu/bin/i686-linux-gnu-ccache-gcc -fno-strict-aliasing -I/usr/syno/include/ncurses/ -I. -I./Modules/expat/ -I/usr/local/i686-linux-gnu/include -DSYNO_X86 -O2 -I/usr/syno/include -g -DSYNO_PLATFORM=X64 -g -DSDK_VER_MIN_REQUIRED=400 -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -fPIC -Dversion_info=(1,2,4,'final',1) -Dversion=1.2.4 -I/usr/local/include -I/usr/local/include/python2.7 -c _mysql.c -o build/temp.linux-x86_64-2.7/_mysql.o -g -DUNIV_LINUX -DUNIV_LINUX

    unable to execute /usr/local/i686-linux-gnu/bin/i686-linux-gnu-ccache-gcc: No such file or directory

    error: command '/usr/local/i686-linux-gnu/bin/i686-linux-gnu-ccache-gcc' failed with exit status 1

    Cleaning up...
    Command /usr/bin/python -c "import setuptools;file='/tmp/pip_build_root/mysql-python/setup.py';exec(compile(open(file).read().replace('\r\n', '\n'), file, 'exec'))" install --record /tmp/pip-kb3_p7-record/install-record.txt --single-version-externally-managed failed with error code 1 in /tmp/pip_build_root/mysql-python
    Storing complete log in /root/.pip/pip.log

I do have GCC installed on my server so I cannot figure out why the setup is looking for a GCC related file that does not exist. Any help would be greatly appreciated.

Hi,

The newest version of mariadb-devel (mariadb-devel-5.5.40-1.el7_0.x86_64) on CentOS 7 breaks when installing MySQL-python via pip:

In file included from /usr/include/mysql/my_config.h:14:0,

             from _mysql.c:44:

/usr/include/mysql/my_config_x86_64.h:654:2: error: #error <my_config.h> MUST be included first!

#error <my_config.h> MUST be included first!

^

error: command 'gcc' failed with exit status 1

I resolved this by removing all mariadb packages via yum:

yum remove maria*

Then installing an older version (mariadb-devel-5.5.37-1.el7_0.x86_64). This seemed to fix the issue.

Hope this can help anyone else in the same situation.

Need to convert docs to Sphinx and get them hosted on readthedocs

Hey there!

Your package on PyPI has several external urls that must be scraped everytime pip/easy_install attempts to install your package. It would be great if you could use the new PyPI feature to control the urls so that scraping multiple external sites is no longer required.

As you can see from http://pypi-externals.caremad.io/MySQL-python/ None of the external scrape targets offer any installation targets so they are pure waste.

You can see how to do this at http://pypi-externals.caremad.io/help/what/

Hi,

I am new to python and MySQLdb1 but i am not new to linux or programming. My eventual goal is to use Django and MySQL for a RESTful app. I am on a "managed" RedHat CloudLinux Server release 6.5. I DO NOT have root or sudo access on this machine. Python 2.6.6 is native on the machine. I have downloaded and installed "pip" into my login account. I installed the current Django using pip as well as mysql-python.

Django seems to have installed correctly. I'm not sure about mysql-python though for two reasons: 1) when I tried the Django tutorial that would initialize the databases it fails saying that "django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named MySQLdb".

The pip install of mysql-python looked like:

$ pip install mysql-python
Downloading/unpacking mysql-python
  Downloading MySQL-python-1.2.5.zip (108kB): 108kB downloaded
  Running setup.py (path:/tmp/pip_build_MYUSER/mysql-python/setup.py) egg_info for package mysql-python
Installing collected packages: mysql-python
  Running setup.py install for mysql-python
    building '_mysql' extension
    gcc -pthread -fno-strict-aliasing -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -DNDEBUG -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_GNU_SOURCE -fPIC -fwrapv -fPIC -Dversion_info=(1,2,5,'final',1) -D__version__=1.2.5 -I/usr/include/mysql -I/usr/include/python2.6 -c _mysql.c -o build/temp.linux-x86_64-2.6/_mysql.o -g -DUNIV_LINUX -DUNIV_LINUX
    In file included from _mysql.c:44:
    /usr/include/mysql/my_config.h:1088:1: warning: "HAVE_WCSCOLL" redefined
    In file included from /usr/include/python2.6/pyconfig.h:6,
                     from /usr/include/python2.6/Python.h:8,
                     from _mysql.c:29:
    /usr/include/python2.6/pyconfig-64.h:808:1: warning: this is the location of the previous definition
    gcc -pthread -shared build/temp.linux-x86_64-2.6/_mysql.o -L/usr/lib64/mysql -L/usr/lib64 -lmysqlclient_r -lz -lpthread -lcrypt -lnsl -lm -lpthread -lmygcc -lpython2.6 -o build/lib.linux-x86_64-2.6/_mysql.so
Successfully installed mysql-python
Cleaning up...

The Django error trace is:

$ python manage.py syncdb
Traceback (most recent call last):
File "manage.py", line 10, in
execute_from_command_line(sys.argv)
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/core/management/init.py", line 399, in execute_from_command_line
utility.execute()
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/core/management/init.py", line 392, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/core/management/base.py", line 242, in run_from_argv
self.execute(_args, *_options.dict)
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/core/management/base.py", line 280, in execute
translation.activate('en-us')
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/utils/translation/init.py", line 130, in activate
return _trans.activate(language)
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/utils/translation/trans_real.py", line 188, in activate
_active.value = translation(language)
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/utils/translation/trans_real.py", line 177, in translation
default_translation = _fetch(settings.LANGUAGE_CODE)
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/utils/translation/trans_real.py", line 159, in _fetch
app = import_module(appname)
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/utils/importlib.py", line 40, in import_module
import(name)
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/contrib/admin/init.py", line 6, in
from django.contrib.admin.sites import AdminSite, site
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/contrib/admin/sites.py", line 4, in
from django.contrib.admin.forms import AdminAuthenticationForm
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/contrib/admin/forms.py", line 6, in
from django.contrib.auth.forms import AuthenticationForm
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/contrib/auth/forms.py", line 17, in
from django.contrib.auth.models import User
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/contrib/auth/models.py", line 48, in
class Permission(models.Model):
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/db/models/base.py", line 96, in new
new_class.add_to_class('_meta', Options(meta, **kwargs))
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/db/models/base.py", line 264, in add_to_class
value.contribute_to_class(cls, name)
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/db/models/options.py", line 124, in contribute_to_class
self.db_table = truncate_name(self.db_table, connection.ops.max_name_length())
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/db/init.py", line 34, in getattr
return getattr(connections[DEFAULT_DB_ALIAS], item)
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/db/utils.py", line 198, in getitem
backend = load_backend(db['ENGINE'])
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/db/utils.py", line 113, in load_backend
return import_module('%s.base' % backend_name)
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/utils/importlib.py", line 40, in import_module
import(name)
File "/MYHOMEDIR/.local/lib/python2.6/site-packages/django/db/backends/mysql/base.py", line 17, in
raise ImproperlyConfigured("Error loading MySQLdb module: %s" % e)
django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named MySQLdb

ideas?

I have a lightly forked MySQLdb1 at https://github.com/tkho/MySQLdb1/commits/libmysqldb that creates a MySQLdb_embedded package and _mysql_embedded.so—versions of MySQLdb that are statically built and linked against the embedded mysql library. These have proved to be a great way to write hermetic tests using the same database stack we run in production; we have it so that subclassing a simple EmbeddedMySQLTestCase and swapping DB URLs is all that’s needed to run against the embedded database.

Now, my question: Does anyone have suggestions on the best way to make this widely available? Ideally the end result is a pypi package that exists side by side (or in) the existing MySQLdb1. Having MySQLdb1 build both _mysql.so and _mysql_embedded.so and support both is one ideal outcome, but I’m not sure there’s a clean way to get there, and it seems like a large change for a mature project. Another alternative is to create a new (published) package as a fork/patchset on top of MySQLdb1. I’d be happy to maintain a fork/sibling package, but would prefer a solution that didn’t require that.

Ideas?

I don't use mysql-python directly but via SQLAlchemy and of course, DBAPI backend is mysql-python 1.2.5.

SQLAlchemy docs: http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.orm.query.Query.update

Returns:    the count of rows matched as returned by the database’s “row count” feature.

If I update a row like this:

rowcount = DBSession.query(Model).filter(id=id).update({'value': 'current_value'})
assert rowcount == 1

That's strange. 'rowcount' now is matched rows, not affected rows. I also read code: https://github.com/farcepest/MySQLdb1/blob/master/MySQLdb/cursors.py#L145

So mysql driver doesn't support affected rows for update statement? I don't think this is a bug if mysql driver doesn't support but the code confuses me a bit.

"Incorrect datetime value: '2015-01-25 22:14:35+00:00' for column 'startTime' at row 1"

Used to work on 1.2.3. Looks like it was introduced with the change from strftime to isoformat here.

The following script should print a negative time value (-1 day, 23:30:00) but instead prints a positive one (0:30:00)

#!/usr/bin/env python
import MySQLdb

db = MySQLdb.connect('YOUR CONNECTION INFO HERE')
query = '''SELECT TIMEDIFF('20:30:00','21:00:00')''';
cur = db.cursor()
cur.execute(query)

for row in cur.fetchall():
    print(row[0])

The issue is in times.py, in the TimeDelta_or_None function:

def TimeDelta_or_None(s):
    try:
        h, m, s = s.split(':')
        if '.' in s:
            s, ms = s.split('.')
            ms = ms.ljust(6, '0')
        else:
            ms = 0
        h, m, s, ms = int(h), int(m), int(s), int(ms)
        td = timedelta(hours=abs(h), minutes=m, seconds=s,
                       microseconds=ms)
        if h < 0:
            return -td
        else:
            return td
    except ValueError:
        # unpacking or int/float conversion failed
        return None

Normally, when a negative time is given, h will be negative, and the correct result will be returned. However, when s is something like -00:30:00, h will be set to int('-00'), which will be equal to 0. The test h < 0 will then fail, and an incorrect result will be returned.

Here's the code to illustrate the issue (python 2.7, mysql 5.5.32):

import MySQLdb

connection = MySQLdb.connect(user = 'guest', db = 'test', charset = 'utf8')
cursor     = connection.cursor()

cursor.execute(u"SELECT 'abcdё' `s`, ExtractValue('<a>abcdё</a>', '/a') `b`")

print cursor.fetchone() # (u'abcd\u0451', 'abcd\xd1\x91')
print cursor.description # (('s', 253, 6, 15, 15, 31, 0), ('b', 251, 6, 50331648, 50331648, 31, 1))
print cursor.description_flags # (1, 0)

As you can see, b column is returned as a byte string instead of unicode, regardless of the fact that FLAG.BINARY is not set. Unicode decoding works fine for FIELD_TYPE.VAR_STRING (253) and FIELD_TYPE.BLOB (252), but it doesn't for FIELD_TYPE.LONG_BLOB (251), which is returned by ExtractValue.

Here's the workaround.

import MySQLdb
import MySQLdb.converters as conv
import MySQLdb.constants as const

connection = MySQLdb.connect(user = 'guest', db = 'test', charset = 'utf8')
connection.converter[const.FIELD_TYPE.LONG_BLOB] = connection.converter[const.FIELD_TYPE.BLOB]
cursor = connection.cursor()

cursor.execute(u"SELECT 'abcdё' `s`, ExtractValue('<a>abcdё</a>', '/a') `b`")

print cursor.fetchone() # (u'abcd\u0451', u'abcd\u0451')
print cursor.description # (('s', 253, 6, 15, 15, 31, 0), ('b', 251, 6, 50331648, 50331648, 31, 1))
print cursor.description_flags # (1, 0)

The workaround also shows that current value converter design needs improvement. Unicode decoders are set in connection constructor in contrast to most that are set in MySQLdb.converters. _get_string_decoder is also defined in constructor. So it's impossible to use conv constructor argument to pass extended decoder dict, and the only way is to patch instances individually.

By default both values are set to 0 unless otherwise specified from MySQLdb.connect. This can lead to problems as applications that would otherwise broke socket connections to MySQL can hang without feedback up to tcp_retries2 timeout.

I suggest setting this to 30 and 60 respectively to match server defaults.

Since the merge of 417088a, _mysql.c does not compile if HAVE_MYSQL_OPT_READ_TIMEOUT is not defined, because we end up with a trailing comma after &local_infile, around line 597. Output of gcc -E:

if (!PyArg_ParseTupleAndKeywords(args, kwargs,



                                     "|ssssisOiiisssiOi:connect",

  kwlist,
  &host, &user, &passwd, &db,
  &port, &unix_socket, &conv,
  &connect_timeout,
  &compress, &named_pipe,
  &init_command, &read_default_file,
  &read_default_group,
  &client_flag, &ssl,
                                     &local_infile,




  ))
return -1;
#615 "_mysql.c"

Unfortunately mysql-python-1.2.4b4 was released with this bug, and pip picks it up as the latest version, so now I have to force the use of 1.2.3 in all pip manifests until this is fixed :(

Trace:
File "/home/hje/Bots/botsdev/bots/botslib.py", line 208, in changeq
cursor.execute(querystring,*args)
File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 185, in execute
for key, item in args.iteritems())
File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 185, in
for key, item in args.iteritems())
File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 278, in literal
return self.escape(o, self.encoders)
File "build/bdist.linux-x86_64/egg/MySQLdb/converters.py", line 133, in quote_tuple
return "(%s)" % (','.join(escape_sequence(t, d)))
File "build/bdist.linux-x86_64/egg/MySQLdb/converters.py", line 133, in quote_tuple
return "(%s)" % (','.join(escape_sequence(t, d)))
File "build/bdist.linux-x86_64/egg/MySQLdb/converters.py", line 133, in quote_tuple
return "(%s)" % (','.join(escape_sequence(t, d)))
File "build/bdist.linux-x86_64/egg/MySQLdb/converters.py", line 133, in quote_tuple
return "(%s)" % (','.join(escape_sequence(t, d)))
File "build/bdist.linux-x86_64/egg/MySQLdb/converters.py", line 133, in quote_tuple
return "(%s)" % (','.join(escape_sequence(t, d)))
File "build/bdist.linux-x86_64/egg/MySQLdb/converters.py", line 133, in quote_tuple
return "(%s)" % (','.join(escape_sequence(t, d)))
File "build/bdist.linux-x86_64/egg/MySQLdb/converters.py", line 133, in quote_tuple
return "(%s)" % (','.join(escape_sequence(t, d)))
File "build/bdist.linux-x86_64/egg/MySQLdb/converters.py", line 133, in quote_tuple
return "(%s)" % (','.join(escape_sequence(t, d)))
TypeError: sequence item 0: expected string, dict found

This error does not occur in earlier version (1.2.2, 1.2.4).
Statement where error occurs is something like:
'''INSERT INTO filereport (idta,statust)
VALUES (%(idta)s,%(statust)s )
''', tmp_dict
problem here is in the content of the dict (I think). The error happens for key/values of the dict that are not actually used in the query; when I remove this unused key/value from the dict the errro does not occur.

I'm not sure if this is a bug in MySQL-Python or in SQLAlchemy, so I'm reporting it in both places. I just did a pip install today for a clean build, and I got the following exception (along with the database trace) from SQLAlchemy. Downgrading to 1.2.3 makes the error go away. It seems similar to the following old thread, where MySQL-Python changed the exception format. I didn't investigate this further, but am happy to do any debugging you may need. Thanks!

New SQLAlchemy thread about this:
https://groups.google.com/d/topic/sqlalchemy/POGzAtvzD4A/discussion

Old SQLAlchemy thread that may be related:
https://groups.google.com/d/topic/sqlalchemy/3DDRA-2oWWc/discussion

Exception and SQL log:

2012-09-26 13:41:48,217 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
[I 120926 13:41:48 log:107] SELECT DATABASE()
2012-09-26 13:41:48,218 INFO sqlalchemy.engine.base.Engine ()
I 120926 13:41:48 log:107
2012-09-26 13:41:48,219 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'character_set%%'
[I 120926 13:41:48 log:107] SHOW VARIABLES LIKE 'character_set%%'
2012-09-26 13:41:48,219 INFO sqlalchemy.engine.base.Engine ()
I 120926 13:41:48 log:107
2012-09-26 13:41:48,220 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
[I 120926 13:41:48 log:107] SHOW VARIABLES LIKE 'lower_case_table_names'
2012-09-26 13:41:48,220 INFO sqlalchemy.engine.base.Engine ()
I 120926 13:41:48 log:107
2012-09-26 13:41:48,221 INFO sqlalchemy.engine.base.Engine SHOW COLLATION
[I 120926 13:41:48 log:107] SHOW COLLATION
2012-09-26 13:41:48,221 INFO sqlalchemy.engine.base.Engine ()
I 120926 13:41:48 log:107
2012-09-26 13:41:48,224 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
[I 120926 13:41:48 log:107] SHOW VARIABLES LIKE 'sql_mode'
2012-09-26 13:41:48,224 INFO sqlalchemy.engine.base.Engine ()
I 120926 13:41:48 log:107
2012-09-26 13:41:48,226 INFO sqlalchemy.engine.base.Engine DESCRIBE "newaccounttoken"
[I 120926 13:41:48 log:107] DESCRIBE "newaccounttoken"
2012-09-26 13:41:48,226 INFO sqlalchemy.engine.base.Engine ()
I 120926 13:41:48 log:107
2012-09-26 13:41:48,229 INFO sqlalchemy.engine.base.Engine ROLLBACK
[I 120926 13:41:48 log:107] ROLLBACK
Traceback (most recent call last):
File "configuration/mysql/makedb.py", line 93, in
main()
File "configuration/mysql/makedb.py", line 82, in main
server.models.createDB()
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/server/models.py", line 534, in createDB
Base.metadata.create_all(server.db.get_engine())
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/sqlalchemy/schema.py", line 2564, in create_all
tables=tables)
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2303, in _run_visitor
conn._run_visitor(visitorcallable, element, *_kwargs)
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1973, in _run_visitor
*_kwargs).traverse_single(element)
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 106, in traverse_single
return meth(obj, **kw)
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/sqlalchemy/engine/ddl.py", line 54, in visit_metadata
if self._can_create_table(t)]
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/sqlalchemy/engine/ddl.py", line 32, in _can_create_table
table.name, schema=table.schema)
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 1986, in has_table
rs = connection.execute(st)
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1449, in execute
params)
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1628, in _execute_text
statement, parameters
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1698, in _execute_context
context)
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1691, in _execute_context
context)
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 331, in do_execute
cursor.execute(statement, parameters)
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/MySQLdb/cursors.py", line 181, in execute
self.errorhandler(self, exc, value)
File "/Users/ej/unnamed/build/venv/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass(errorvalue)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) (1146, "Table 'lectorius.newaccounttoken' doesn't exist") 'DESCRIBE "newaccounttoken"' ()

pip install mysql-python

Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-aOV0uI/mysql-python

Issue when compiling MySQLdb extension

System configuration
Ubuntu: 12.04 precise
Python: 2.7
VirtualEnvironment
libmariadbclient-dev_5.5.40+maria-1~wheezy_amd64.deb

Package groups installed via ansible
- libpq-dev
- libmariadbclient18
- libmariadbclient-dev
- libxml2-dev
- libxslt1-dev
- libjpeg62
- libjpeg62-dev
- libfreetype6
- libfreetype6-dev
- libcurl4-gnutls-dev
- librtmp-dev
- zlib1g-dev
- mariadb-common
- mysql-common
- mariadb-client
- python-dev
- python-setuptools
- python-imaging
- python-mysqldb
- python-psycopg2
- subversion
- git-core

I am getting this error

(venv)root@Ubuntu-test01:/var/projects/www/mysql_source# python setup.py install
running install
running bdist_egg
running egg_info
creating MySQL_python.egg-info
writing MySQL_python.egg-info/PKG-INFO
writing top-level names to MySQL_python.egg-info/top_level.txt
writing dependency_links to MySQL_python.egg-info/dependency_links.txt
writing manifest file 'MySQL_python.egg-info/SOURCES.txt'
reading manifest file 'MySQL_python.egg-info/SOURCES.txt'
reading manifest template 'MANIFEST.in'
writing manifest file 'MySQL_python.egg-info/SOURCES.txt'
installing library code to build/bdist.linux-x86_64/egg
running install_lib
running build_py
creating build
creating build/lib.linux-x86_64-2.7
copying _mysql_exceptions.py -> build/lib.linux-x86_64-2.7
creating build/lib.linux-x86_64-2.7/MySQLdb
copying MySQLdb/__init__.py -> build/lib.linux-x86_64-2.7/MySQLdb
copying MySQLdb/converters.py -> build/lib.linux-x86_64-2.7/MySQLdb
copying MySQLdb/connections.py -> build/lib.linux-x86_64-2.7/MySQLdb
copying MySQLdb/cursors.py -> build/lib.linux-x86_64-2.7/MySQLdb
copying MySQLdb/release.py -> build/lib.linux-x86_64-2.7/MySQLdb
copying MySQLdb/times.py -> build/lib.linux-x86_64-2.7/MySQLdb
creating build/lib.linux-x86_64-2.7/MySQLdb/constants
copying MySQLdb/constants/__init__.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants
copying MySQLdb/constants/CR.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants
copying MySQLdb/constants/FIELD_TYPE.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants
copying MySQLdb/constants/ER.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants
copying MySQLdb/constants/FLAG.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants
copying MySQLdb/constants/REFRESH.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants
copying MySQLdb/constants/CLIENT.py -> build/lib.linux-x86_64-2.7/MySQLdb/constants
running build_ext
building '_mysql' extension
creating build/temp.linux-x86_64-2.7
gcc -pthread -fno-strict-aliasing -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -fPIC -Dversion_info=(1,2,4,'final',1) -D__version__=1.2.4 -I/usr/include/mysql -I/usr/include/python2.7 -c _mysql.c -o build/temp.linux-x86_64-2.7/_mysql.o -fno-omit-frame-pointer -g -pipe -Wno-uninitialized -g -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing
In file included from _mysql.c:44:0:
/usr/include/mysql/my_config.h:439:0: warning: "HAVE_WCSCOLL" redefined [enabled by default]
/usr/include/python2.7/pyconfig.h:890:0: note: this is the location of the previous definition
/usr/include/mysql/my_config.h:654:2: error: #error <my_config.h> MUST be included first!
error: command 'gcc' failed with exit status 1

Perhaps the readme on GitHub and on PyPI should point Python 3 users to a working alternative?

https://pypi.python.org/pypi/mysql-connector-python

Use the django, crash when syncdb

System: CentOS 5.10

python: Version 3.3.3

django: Version 1.6.1

mysql: Version 5.0.95

(django-vpn)[liuxf@vpn vpnlog.git]$ python manage.py syncdb
Traceback (most recent call last):
File "manage.py", line 10, in
execute_from_command_line(sys.argv)
File "/home/liuxf/django-vpn/lib/python3.3/site-packages/django/core/management/init.py", line 399, in execute_from_command_line
utility.execute()
File "/home/liuxf/django-vpn/lib/python3.3/site-packages/django/core/management/init.py", line 392, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "/home/liuxf/django-vpn/lib/python3.3/site-packages/django/core/management/base.py", line 242, in run_from_argv
self.execute(_args, *_options.dict)
File "/home/liuxf/django-vpn/lib/python3.3/site-packages/django/core/management/base.py", line 285, in execute
output = self.handle(_args, *_options)
File "/home/liuxf/django-vpn/lib/python3.3/site-packages/django/core/management/base.py", line 415, in handle
return self.handle_noargs(**options)
File "/home/liuxf/django-vpn/lib/python3.3/site-packages/django/core/management/commands/syncdb.py", line 57, in handle_noargs
cursor = connection.cursor()
File "/home/liuxf/django-vpn/lib/python3.3/site-packages/django/db/backends/init.py", line 157, in cursor
cursor = self.make_debug_cursor(self._cursor())
File "/home/liuxf/django-vpn/lib/python3.3/site-packages/django/db/backends/init.py", line 129, in _cursor
self.ensure_connection()
File "/home/liuxf/django-vpn/lib/python3.3/site-packages/django/db/backends/init.py", line 124, in ensure_connection
self.connect()
File "/home/liuxf/django-vpn/lib/python3.3/site-packages/django/db/backends/init.py", line 112, in connect
self.connection = self.get_new_connection(conn_params)
File "/home/liuxf/django-vpn/lib/python3.3/site-packages/django/db/backends/mysql/base.py", line 435, in get_new_connection
conn = Database.connect(**conn_params)
File "/home/liuxf/django-vpn/lib/python3.3/site-packages/MySQL_python-1.3.0a1-py3.3-linux-x86_64.egg/MySQLdb/init.py", line 77, in Connect
return Connection(_args, *_kwargs)
File "/home/liuxf/django-vpn/lib/python3.3/site-packages/MySQL_python-1.3.0a1-py3.3-linux-x86_64.egg/MySQLdb/connections.py", line 235, in init
self.encoders[types.StringType] = string_literal
AttributeError: 'module' object has no attribute 'StringType'
*** glibc detected *** python: double free or corruption (out): 0x0000000010b735c0 ***
======= Backtrace: =========
/lib64/libc.so.6[0x37c2e7174f]
/lib64/libc.so.6(cfree+0x4b)[0x37c2e75a4b]
python[0x4289a8]
python[0x529ba9]
python[0x4a7bee]
python[0x4a7bfb]
python[0x4a7bfb]
python[0x4a7bfb]
python[0x4a7bfb]
python[0x4a7bfb]
python[0x4a7bfb]
python[0x4a7bfb]
python[0x4a7bfb]
python[0x4a7bfb]
python[0x4a7bfb]
python[0x4a7bfb]
python[0x4a7bfb]
python[0x4a7bfb]
python[0x541217]
python(PyDict_SetItemString+0x7d)[0x54219d]
python(PyImport_Cleanup+0xcc)[0x49022c]
python(Py_Finalize+0xc5)[0x49d545]
python(Py_Main+0x455)[0x4b4985]
python(main+0x14c)[0x4188ec]
/lib64/libc.so.6(__libc_start_main+0xf4)[0x37c2e1d9c4]
python[0x4186e9]
======= Memory map: ========
00400000-005c3000 r-xp 00000000 fd:00 983103 /home/liuxf/django-vpn/bin/python
007c2000-0082a000 rw-p 001c2000 fd:00 983103 /home/liuxf/django-vpn/bin/python
0082a000-00845000 rw-p 0082a000 00:00 0
104dd000-10b97000 rw-p 104dd000 00:00 0 [heap]
37c2a00000-37c2a1c000 r-xp 00000000 fd:00 65540 /lib64/ld-2.5.so
37c2c1c000-37c2c1d000 r--p 0001c000 fd:00 65540 /lib64/ld-2.5.so
37c2c1d000-37c2c1e000 rw-p 0001d000 fd:00 65540 /lib64/ld-2.5.so
37c2e00000-37c2f4f000 r-xp 00000000 fd:00 65547 /lib64/libc-2.5.so
37c2f4f000-37c314f000 ---p 0014f000 fd:00 65547 /lib64/libc-2.5.so
37c314f000-37c3153000 r--p 0014f000 fd:00 65547 /lib64/libc-2.5.so
37c3153000-37c3154000 rw-p 00153000 fd:00 65547 /lib64/libc-2.5.so
37c3154000-37c3159000 rw-p 37c3154000 00:00 0
37c3200000-37c3202000 r-xp 00000000 fd:00 65557 /lib64/libdl-2.5.so
37c3202000-37c3402000 ---p 00002000 fd:00 65557 /lib64/libdl-2.5.so
37c3402000-37c3403000 r--p 00002000 fd:00 65557 /lib64/libdl-2.5.so
37c3403000-37c3404000 rw-p 00003000 fd:00 65557 /lib64/libdl-2.5.so
37c3600000-37c3616000 r-xp 00000000 fd:00 65561 /lib64/libpthread-2.5.so
37c3616000-37c3816000 ---p 00016000 fd:00 65561 /lib64/libpthread-2.5.so
37c3816000-37c3817000 r--p 00016000 fd:00 65561 /lib64/libpthread-2.5.so
37c3817000-37c3818000 rw-p 00017000 fd:00 65561 /lib64/libpthread-2.5.so
37c3818000-37c381c000 rw-p 37c3818000 00:00 0
37c3a00000-37c3a82000 r-xp 00000000 fd:00 65551 /lib64/libm-2.5.so
37c3a82000-37c3c81000 ---p 00082000 fd:00 65551 /lib64/libm-2.5.so
37c3c81000-37c3c82000 r--p 00081000 fd:00 65551 /lib64/libm-2.5.so
37c3c82000-37c3c83000 rw-p 00082000 fd:00 65551 /lib64/libm-2.5.so
37c3e00000-37c3e14000 r-xp 00000000 fd:00 65738 /lib64/libz.so.1.2.3
37c3e14000-37c4013000 ---p 00014000 fd:00 65738 /lib64/libz.so.1.2.3
37c4013000-37c4014000 rw-p 00013000 fd:00 65738 /lib64/libz.so.1.2.3
37c4200000-37c4207000 r-xp 00000000 fd:00 65563 /lib64/librt-2.5.so
37c4207000-37c4407000 ---p 00007000 fd:00 65563 /lib64/librt-2.5.so
37c4407000-37c4408000 r--p 00007000 fd:00 65563 /lib64/librt-2.5.so
37c4408000-37c4409000 rw-p 00008000 fd:00 65563 /lib64/librt-2.5.so
37c4600000-37c463b000 r-xp 00000000 fd:00 65585 /lib64/libsepol.so.1
37c463b000-37c483b000 ---p 0003b000 fd:00 65585 /lib64/libsepol.so.1
37c483b000-37c483c000 rw-p 0003b000 fd:00 65585 /lib64/libsepol.so.1
37c483c000-37c4846000 rw-p 37c483c000 00:00 0
37c4a00000-37c4a15000 r-xp 00000000 fd:00 65587 /lib64/libselinux.so.1
37c4a15000-37c4c15000 ---p 00015000 fd:00 65587 /lib64/libselinux.so.1
37c4c15000-37c4c17000 rw-p 00015000 fd:00 65587 /lib64/libselinux.so.1
37c4c17000-37c4c18000 rw-p 37c4c17000 00:00 0
37c4e00000-37c4e15000 r-xp 00000000 fd:00 65555 /lib64/libnsl-2.5.so
37c4e15000-37c5014000 ---p 00015000 fd:00 65555 /lib64/libnsl-2.5.so
37c5014000-37c5015000 r--p 00014000 fd:00 65555 /lib64/libnsl-2.5.so
37c5015000-37c5016000 rw-p 00015000 fd:00 65555 /lib64/libnsl-2.5.so
37c5016000-37c5018000 rw-p 37c5016000 00:00 0
37c5200000-37c5209000 r-xp 00000000 fd:00 65599 /lib64/libcrypt-2.5.so
37c5209000-37c5408000 ---p 00009000 fd:00 65599 /lib64/libcrypt-2.5.so
37c5408000-37c5409000 r--p 00008000 fd:00 65599 /lib64/libcrypt-2.5.so
37c5409000-37c540a000 rw-p 00009000 fd:00 65599 /lib64/libcrypt-2.5.so
37c540a000-37c5438000 rw-p 37c540a000 00:00 0
37c5600000-37c560d000 r-xp 00000000 fd:00 65553 /lib64/libgcc_s-4.1.2-20080825.so.1
37c560d000-37c580d000 ---p 0000d000 fd:00 65553 /lib64/libgcc_s-4.1.2-20080825.so.1
37c580d000-37c580e000 rw-p 0000d000 fd:00 65553 /lib64/libgcc_s-4.1.2-20080825.so.1
37c5a00000-37c5a11000 r-xp 00000000 fd:00 65727 /lib64/libresolv-2.5.so
37c5a11000-37c5c11000 ---p 00011000 fd:00 65727 /lib64/libresolv-2.5.so
37c5c11000-37c5c12000 r--p 00011000 fd:00 65727 /lib64/libresolv-2.5.so
37c5c12000-37c5c13000 rw-p 00012000 fd:00 65727 /lib64/libresolv-2.5.so
37c5c13000-37c5c15000 rw-p 37c5c13000 00:00 0
37c5e00000-37c5e02000 r-xp 00000000 fd:00 65733 /lib64/libcom_err.so.2.1
37c5e02000-37c6001000 ---p 00002000 fd:00 65733 /lib64/libcom_err.so.2.1
37c6001000-37c6002000 rw-p 00001000 fd:00 65733 /lib64/libcom_err.so.2.1
37c6600000-37c672d000 r-xp 00000000 fd:00 65740 /lib64/libcrypto.so.0.9.8e
37c672d000-37c692c000 ---p 0012d000 fd:00 65740 /lib64/libcrypto.so.0.9.8e
37c692c000-37c694d000 rw-p 0012c000 fd:00 65740 /lib64/libcrypto.so.0.9.8e
37c694d000-37c6951000 rw-p 37c694d000 00:00 0
37c6a00000-37c6a0f000 r-xp 00000000 fd:00 5749789 /usr/lib64/libbz2.so.1.0.3
37c6a0f000-37c6c0e000 ---p 0000f000 fd:00 5749789 /usr/lib64/libbz2.so.1.0.3
37c6c0e000-37c6c10000 rw-p 0000e000 fd:00 5749789 /usr/lib64/libbz2.so.1.0.3
37c6e00000-37c6e91000 r-xp 00000000 fd:00 5752299 /usr/lib64/libkrb5.so.3.3
37c6e91000-37c7091000 ---p 00091000 fd:00 5752299 /usr/lib64/libkrb5.so.3.3
37c7091000-37c7095000 rw-p 00091000 fd:00 5752299 /usr/lib64/libkrb5.so.3.3
37c7200000-37c7202000 r-xp 00000000 fd:00 65725 /lib64/libkeyutils-1.2.so
37c7202000-37c7401000 ---p 00002000 fd:00 65725 /lib64/libkeyutils-1.2.so
37c7401000-37c7402000 rw-p 00001000 fd:00 65725 /lib64/libkeyutils-1.2.so
37c7600000-37c762c000 r-xp 00000000 fd:00 5752300 /usr/lib64/libgssapi_krb5.so.2.2
37c762c000-37c782c000 ---p 0002c000 fd:00 5752300 /usr/lib64/libgssapi_krb5.so.2.2
37c782c000-37c782e000 rw-p 0002c000 fd:00 5752300 /usr/lib64/libgssapi_krb5.so.2.2
37c7a00000-37c7a08000 r-xp 00000000 fd:00 5744775 /usr/lib64/libkrb5support.so.0.1
37c7a08000-37c7c07000 ---p 00008000 fd:00 5744775 /usr/lib64/libkrb5support.so.0.1
37c7c07000-37c7c08000 rw-p 00007000 fd:00 5744775 /usr/lib64/libkrb5support.so.0.1
37c7e00000-37c7e24000 r-xp 00000000 fd:00 5752298 /usr/lib64/libk5crypto.so.3.1
37c7e24000-37c8023000 ---p 00024000 fd:00 5752298 /usr/lib64/libk5crypto.so.3.1
37c8023000-37c8025000 rw-p 00023000 fd:00 5752298 /usr/lib64/libk5crypto.so.3.1
37c8200000-37c8248000 r-xp 00000000 fd:00 65757 /lib64/libssl.so.0.9.8e
37c8248000-37c8448000 ---p 00048000 fd:00 65757 /lib64/libssl.so.0.9.8e
37c8448000-37c844e000 rw-p 00048000 fd:00 65757 /lib64/libssl.so.0.9.8e
37cb200000-37cb202000 r-xp 00000000 fd:00 65602 /lib64/libutil-2.5.so
37cb202000-37cb401000 ---p 00002000 fd:00 65602 /lib64/libutil-2.5.so
37cb401000-37cb402000 r--p 00001000 fd:00 65602 /lib64/libutil-2.5.so
37cb402000-37cb403000 rw-p 00002000 fd:00 65602 /lib64/libutil-2.5.so
2b1a43938000-2b1a4393a000 rw-p 2b1a43938000 00:00 0
2b1a43945000-2b1a43948000 rw-p 2b1a43945000 00:00 0
2b1a43948000-2b1a46f22000 r--p 00000000 fd:00 5749831 /usr/lib/locale/locale-archive
2b1a46f22000-2b1a46f29000 r--s 00000000 fd:00 5800796 /usr/lib64/gconv/gconv-modules.cache
2b1a46f29000-2b1a47029000 rw-p 2b1a46f29000 00:00 0
2b1a4705a000-2b1a470da000 rw-p 2b1a4705a000 00:00 0
2b1a470da000-2b1a470dd000 r-xp 00000000 fd:00 6390101 /usr/local/lib/python3.3/lib-dynload/_heapq.cpython-33m.so
2b1a470dd000-2b1a472dc000 ---p 00003000 fd:00 6390101 /usr/local/lib/python3.3/lib-dynload/_heapq.cpython-33m.so
2b1a472dc000-2b1a472df000 rw-p 00002000 fd:00 6390101 /usr/local/lib/python3.3/lib-dynload/_heapq.cpython-33m.so
2b1a472df000-2b1a4739f000 rw-p 2b1a472df000 00:00 0
2b1a4739f000-2b1a473a6000 r-xp 00000000 fd:00 6390116 /usr/local/lib/python3.3/lib-dynload/_struct.cpython-33m.so
2b1a473a6000-2b1a475a6000 ---p 00007000 fd:00 6390116 /usr/local/lib/python3.3/lib-dynload/_struct.cpython-33m.so
2b1a475a6000-2b1a475a8000 rw-p 00007000 fd:00 6390116 /usr/local/lib/python3.3/lib-dynload/_struct.cpython-33m.so
2b1a475a8000-2b1a475e8000 rw-p 2b1a475a8000 00:00 0
2b1a475e8000-2b1a475ed000 r-xp 00000000 fd:00 6390094 /usr/local/lib/python3.3/lib-dynload/time.cpython-33m.so
2b1a475ed000-2b1a477ed000 ---p 00005000 fd:00 6390094 /usr/local/lib/python3.3/lib-dynload/time.cpython-33m.so
2b1a477ed000-2b1a477ef000 rw-p 00005000 fd:00 6390094 /usr/local/lib/python3.3/lib-dynload/time.cpython-33m.so
2b1a477ef000-2b1a477f7000 r-xp 00000000 fd:00 6390091 /usr/local/lib/python3.3/lib-dynload/math.cpython-33m.so
2b1a477f7000-2b1a479f7000 ---p 00008000 fd:00 6390091 /usr/local/lib/python3.3/lib-dynload/math.cpython-33m.so
2b1a479f7000-2b1a479f9000 rw-p 00008000 fd:00 6390091 /usr/local/lib/python3.3/lib-dynload/math.cpython-33m.so
2b1a479f9000-2b1a47a0a000 r-xp 00000000 fd:00 6390087 /usr/local/lib/python3.3/lib-dynload/_datetime.cpython-33m.so
2b1a47a0a000-2b1a47c09000 ---p 00011000 fd:00 6390087 /usr/local/lib/python3.3/lib-dynload/_datetime.cpython-33m.so
2b1a47c09000-2b1a47c0c000 rw-p 00010000 fd:00 6390087 /usr/local/lib/python3.3/lib-dynload/_datetime.cpython-33m.so
2b1a47c0c000-2b1a47c4c000 rw-p 2b1a47c0c000 00:00 0
2b1a47c74000-2b1a47cf4000 rw-p 2b1a47c74000 00:00 0
2b1a47d15000-2b1a47d95000 rw-p 2b1a47d15000 00:00 0
2b1a47d95000-2b1a47d99000 r-xp 00000000 fd:00 6390089 /usr/local/lib/python3.3/lib-dynload/select.cpython-33m.so
2b1a47d99000-2b1a47f99000 ---p 00004000 fd:00 6390089 /usr/local/lib/python3.3/lib-dynload/select.cpython-33m.so
2b1a47f99000-2b1a47f9b000 rw-p 00004000 fd:00 6390089 /usr/local/lib/python3.3/lib-dynload/select.cpython-33m.so
2b1a47f9b000-2b1a47f9e000 r-xp 00000000 fd:00 6390100 /usr/local/lib/python3.3/lib-dynload/_posixsubprocess.cpython-33m.so
2b1a47f9e000-2b1a4819d000 ---p 00003000 fd:00 6390100 /usr/local/lib/python3.3/lib-dynload/_posixsubprocess.cpython-33m.so
2b1a4819d000-2b1a4819e000 rw-p 00002000 fd:00 6390100 /usr/local/lib/python3.3/lib-dynload/_posixsubprocess.cpython-33m.so
2b1a4819e000-2b1a481de000 rw-p 2b1a4819e000 00:00 0
2b1a481de000-2b1a481e0000 r-xp 00000000 fd:00 6390084 /usr/local/lib/python3.3/lib-dynload/atexit.cpython-33m.so
2b1a481e0000-2b1a483df000 ---p 00002000 fd:00 6390084 /usr/local/lib/python3.3/lib-dynload/atexit.cpython-33m.so
2b1a483df000-2b1a483e0000 rw-p 00001000 fd:00 6390084 /usr/local/lib/python3.3/lib-dynload/atexit.cpython-33m.so
2b1a483e0000-2b1a483ee000 r-xp 00000000 fd:00 6390128 /usr/local/lib/python3.3/lib-dynload/_socket.cpython-33m.so
2b1a483ee000-2b1a485ee000 ---p 0000e000 fd:00 6390128 /usr/local/lib/python3.3/lib-dynload/_socket.cpython-33m.so
2b1a485ee000-2b1a485f3000 rw-p 0000e000 fd:00 6390128 /usr/local/lib/python3.3/lib-dynload/_socket.cpython-33m.so
2b1a485f3000-2b1a485f7000 r-xp 00000000 fd:00 6390136 /usr/local/lib/python3.3/lib-dynload/_hashlib.cpython-33m.so
2b1a485f7000-2b1a487f6000 ---p 00004000 fd:00 6390136 /usr/local/lib/python3.3/lib-dynload/_hashlib.cpython-33m.so
2b1a487f6000-2b1a487f7000 rw-p 00003000 fd:00 6390136 /usr/local/lib/python3.3/lib-dynload/_hashlib.cpython-33m.so
2b1a487f7000-2b1a487fa000 r-xp 00000000 fd:00 6390102 /usr/local/lib/python3.3/lib-dynload/_random.cpython-33m.so
2b1a487fa000-2b1a489f9000 ---p 00003000 fd:00 6390102 /usr/local/lib/python3.3/lib-dynload/_random.cpython-33m.so
2b1a489f9000-2b1a489fa000 rw-p 00002000 fd:00 6390102 /usr/local/lib/python3.3/lib-dynload/_random.cpython-33m.so
2b1a489fa000-2b1a48a3a000 rw-p 2b1a489fa000 00:00 0
2b1a48a3a000-2b1a48a3e000 r-xp 00000000 fd:00 6390110 /usr/local/lib/python3.3/lib-dynload/binascii.cpython-33m.so
2b1a48a3e000-2b1a48c3e000 ---p 00004000 fd:00 6390110 /usr/local/lib/python3.3/lib-dynload/binascii.cpython-33m.so
2b1a48c3e000-2b1a48c3f000 rw-p 00004000 fd:00 6390110 /usr/local/lib/python3.3/lib-dynload/binascii.cpython-33m.so
2b1a48c3f000-2b1a48c7f000 rw-p 2b1a48c3f000 00:00 0
2b1a48c7f000-2b1a48c88000 r-xp 00000000 fd:00 6390123 /usr/local/lib/python3.3/lib-dynload/array.cpython-33m.so
2b1a48c88000-2b1a48e88000 ---p 00009000 fd:00 6390123 /usr/local/lib/python3.3/lib-dynload/array.cpython-33m.so
2b1a48e88000-2b1a48e8b000 rw-p 00009000 fd:00 6390123 /usr/local/lib/python3.3/lib-dynload/array.cpython-33m.so
2b1a48e8b000-2b1a48ecb000 rw-p 2b1a48e8b000 00:00 0
2b1a48ecb000-2b1a48ed3000 r-xp 00000000 fd:00 6390082 /usr/local/lib/python3.3/lib-dynload/_json.cpython-33m.so
2b1a48ed3000-2b1a490d3000 ---p 00008000 fd:00 6390082 /usr/local/lib/python3.3/lib-dynload/_json.cpython-33m.so
2b1a490d3000-2b1a490d4000 rw-p 00008000 fd:00 6390082 /usr/local/lib/python3.3/lib-dynload/_json.cpython-33m.so
2b1a490d4000-2b1a490d9000 r-xp 00000000 fd:00 6390132 /usr/local/lib/python3.3/lib-dynload/zlib.cpython-33m.so
2b1a490d9000-2b1a492d9000 ---p 00005000 fd:00 6390132 /usr/local/lib/python3.3/lib-dynload/zlib.cpython-33m.so
2b1a492d9000-2b1a492db000 rw-p 00005000 fd:00 6390132 /usr/local/lib/python3.3/lib-dynload/zlib.cpython-33m.so
2b1a492db000-2b1a4931b000 rw-p 2b1a492db000 00:00 0
2b1a4931b000-2b1a4931e000 r-xp 00000000 fd:00 6390109 /usr/local/lib/python3.3/lib-dynload/fcntl.cpython-33m.so
2b1a4931e000-2b1a4951d000 ---p 00003000 fd:00 6390109 /usr/local/lib/python3.3/lib-dynload/fcntl.cpython-33m.so
2b1a4951d000-2b1a4951f000 rw-p 00002000 fd:00 6390109 /usr/local/lib/python3.3/lib-dynload/fcntl.cpython-33m.so
2b1a4951f000-2b1a4955f000 rw-p 2b1a4951f000 00:00 0
2b1a4955f000-2b1a49604000 r-xp 00000000 fd:00 6390129 /usr/local/lib/python3.3/lib-dynload/unicodedata.cpython-33m.so
2b1a49604000-2b1a49804000 ---p 000a5000 fd:00 6390129 /usr/local/lib/python3.3/lib-dynload/unicodedata.cpython-33m.so
2b1a49804000-2b1a4981a000 rw-p 000a5000 fd:00 6390129 /usr/local/lib/python3.3/lib-dynload/unicodedata.cpython-33m.so
2b1a4981a000-2b1a4995a000 rw-p 2b1a4981a000 00:00 0
2b1a4995a000-2b1a4995c000 r-xp 00000000 fd:00 6390114 /usr/local/lib/python3.3/lib-dynload/grp.cpython-33m.so
2b1a4995c000-2b1a49b5b000 ---p 00002000 fd:00 6390114 /usr/local/lib/python3.3/lib-dynload/grp.cpython-33m.so
2b1a49b5b000-2b1a49b5c000 rw-p 00001000 fd:00 6390114 /usr/local/lib/python3.3/lib-dynload/grp.cpython-33m.so
2b1a49b5c000-2b1a49b5e000 r-xp 00000000 fd:00 6390135 /usr/local/lib/python3.3/lib-dynload/_bz2.cpython-33m.so
2b1a49b5e000-2b1a49d5e000 ---p 00002000 fd:00 6390135 /usr/local/lib/python3.3/lib-dynload/_bz2.cpython-33m.so
2b1a49d5e000-2b1a49d5f000 rw-p 00002000 fd:00 6390135 /usr/local/lib/python3.3/lib-dynload/_bz2.cpython-33m.so
2b1a49d5f000-2b1a49d64000 r-xp 00000000 fd:00 6390141 /usr/local/lib/python3.3/lib-dynload/_lzma.cpython-33m.so
2b1a49d64000-2b1a49f64000 ---p 00005000 fd:00 6390141 /usr/local/lib/python3.3/lib-dynload/_lzma.cpython-33m.so
2b1a49f64000-2b1a49f66000 rw-p 00005000 fd:00 6390141 /usr/local/lib/python3.3/lib-dynload/_lzma.cpython-33m.so
2b1a49f71000-2b1a49f90000 r-xp 00000000 fd:00 5739052 /usrAborted

In python I am inserting into a table using "insert on update keyword". However when a certain number of columns are used in the update statement the query goes on forever. I have to kill it manually.In my case the number of columns are 21. So in the SQL5 variable if the number of updates are reduced to 10 this code starts working. I also checked with oursql module and it works with the oursql module. Can somebody tell me if this is a bug or I am doing something wrong. Just to add I have also changed the MYSQL variable max_allowed_packet to 1024M.

To replicate please find the code along with DDL below:

CREATE TABLE `bseb_scada_stg` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL,
`time` time DEFAULT NULL,
`schedule` double DEFAULT NULL,
`total_drawal` double DEFAULT NULL,
`ui_pow` double DEFAULT NULL,
`freq` double DEFAULT NULL,
`ui_rate` double DEFAULT NULL,
`purnea_pg_purnea_bseb` double DEFAULT NULL,
`purnea_pg_kishangunj` double DEFAULT NULL,
`muzaffarpur_pg_mtps_kanti` double DEFAULT NULL,
`kafen_pg_hajipur` double DEFAULT NULL,
`purnea_pg_madhepura_bseb` double DEFAULT NULL,
`kb_gen` double DEFAULT NULL,
`begusarai_b_sharif` double DEFAULT NULL,
`b_sharif_pg_b_sharif_bseb` double DEFAULT NULL,
`pusauli_pg_dehri_bseb` double DEFAULT NULL,
`pusauli_pg_arah_pg` int(11) DEFAULT NULL,
`patna_pg_khagaul_bseb` double DEFAULT NULL,
`patna_pg_fatwa_bseb` int(11) DEFAULT NULL,
`k_gaon_ntpc_k_gaon_bseb` double DEFAULT NULL,
`k_gaon_ntpc_sabour_bseb` double DEFAULT NULL,
`lalmatia_sabour_sult` int(11) DEFAULT NULL,
`b_sharif_barhi_barhi_end` double DEFAULT NULL,
`barhi_rajgir` double DEFAULT NULL,
`b_sharif_tenughat` double DEFAULT NULL,
`sonenar_garhwa` double DEFAULT NULL,
`sultangunj_deoghar` int(11) DEFAULT NULL,
`sonenagar_rihand` int(11) DEFAULT NULL,
`karmanasa_sahupuri` int(11) DEFAULT NULL,
`gaya_pg_bodhgaya_bseb` double DEFAULT NULL,
`gaya_pg_dehri_bseb` double DEFAULT NULL,
`patna_pg_sipara_bseb` double DEFAULT NULL,
`banka_pg_banka_bseb` double DEFAULT NULL,
`lakhisarai_lakhisarai_pg` double DEFAULT NULL,
`lakhisarai_jamui` double DEFAULT NULL,
`PK` int(11) DEFAULT NULL,
`U_I_Ind` int(11) DEFAULT NULL,
`Load_Ind` int(11) DEFAULT NULL,
`Load_Date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `date_time_UNIQUE` (`date`,`time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

import MySQLdb

data = [('04/11/2014', '0:01:31', '2129.7', '2166.9', '-37.2', '50.03', '106.8', '123.2413445', '40.52749252', '339.7173157', '96.14920044', '106.1057739', '78.25858307', '120.7000008', '-272.524292', '69.84365845', '-9.233032227', '127.5198746', '0', '31.44219208', '49.53681183', '0', '0.04003052', '0.030030521', '-175.3537292', '34.77833557', '0', '0', '0', '-110.5733643', '168.9000015', '218.2078094', '24.44289398', '60.0909462', '66.49983215'), ('04/11/2014', '0:02:01', '2129.7', '2168', '-38.3', '50', '178', '123.1911049', '40.55540085', '340.3117371', '94.96035767', '106.1057739', '78.25858307', '120.7000008', '-270.7372437', '68.05661011', '-7.445983887', '127.5198746', '0', '31.44219208', '49.53681183', '0', '0.04003052', '0.030030521', '-177.5875549', '34.24224854', '0', '0', '0', '-110.5733643', '168.9000015', '218.2078094', '24.44289398', '62.95358276', '62.94747925'), ('04/11/2014', '0:02:30', '2129.7', '2176.3', '-46.6', '49.97', '240.5', '123.0962105', '40.45493317', '340.9061584', '87.23275757', '106.1057739', '78.25858307', '120.6000023', '-271.6307373', '70.14151001', '-8.637390137', '127.5198746', '0', '31.44219208', '49.53681183', '0', '0.030030521', '0.030030521', '-176.6940308', '34.77833557', '0', '0', '0', '-112.1370392', '167.0999985', '218.2078094', '24.44289398', '64.015625', '66.52424622')]
conn = MySQLdb.connect(passwd="****",db="***")

SQL5 = """insert into `bseb_scada_stg`
            (`date`,
             `time`,
             `schedule`,
             `total_drawal`,
             `ui_pow`,
             `freq`,
             `ui_rate`,
             `purnea_pg_purnea_bseb`,
             `purnea_pg_kishangunj`,
             `muzaffarpur_pg_mtps_kanti`,
             `kafen_pg_hajipur`,
             `purnea_pg_madhepura_bseb`,
             `kb_gen`,
             `begusarai_b_sharif`,
             `b_sharif_pg_b_sharif_bseb`,
             `pusauli_pg_dehri_bseb`,
             `pusauli_pg_arah_pg`,
             `patna_pg_khagaul_bseb`,
             `patna_pg_fatwa_bseb`,
             `k_gaon_ntpc_k_gaon_bseb`,
             `k_gaon_ntpc_sabour_bseb`,
             `lalmatia_sabour_sult`,
             `b_sharif_barhi_barhi_end`,
             `barhi_rajgir`,
             `b_sharif_tenughat`,
             `sonenar_garhwa`,
             `sultangunj_deoghar`,
             `sonenagar_rihand`,
             `karmanasa_sahupuri`,
             `gaya_pg_bodhgaya_bseb`,
             `gaya_pg_dehri_bseb`,
             `patna_pg_sipara_bseb`,
             `banka_pg_banka_bseb`,
             `lakhisarai_lakhisarai_pg`,
             `lakhisarai_jamui`)
             values(
                  STR_TO_DATE(%s,'%%d/%%m/%%Y'),
                  STR_TO_DATE(%s,'%%H:%%i:%%s'),
                  %s, %s, %s, %s, %s,
                  %s, %s, %s, %s, %s,
                  %s, %s, %s, %s, %s,
                  %s, %s, %s, %s, %s,
                  %s, %s, %s, %s, %s,
                  %s, %s, %s, %s, %s,
                  %s, %s, %s)
            on duplicate key
            update `schedule` = values(schedule),
                   `total_drawal` = values(total_drawal),
                   `ui_pow` = values(ui_pow),
                   `freq` = values(freq),
                   `ui_rate` = values(ui_rate),
                   `purnea_pg_purnea_bseb` = values(purnea_pg_purnea_bseb),
                   `purnea_pg_kishangunj` = values(purnea_pg_kishangunj),
                   `muzaffarpur_pg_mtps_kanti` = values(muzaffarpur_pg_mtps_kanti),
                   `kafen_pg_hajipur` = values(kafen_pg_hajipur),
                   `purnea_pg_madhepura_bseb` = values(purnea_pg_madhepura_bseb),
                   `kb_gen` = values(kb_gen),
                   `begusarai_b_sharif` = values(begusarai_b_sharif),
                   `b_sharif_pg_b_sharif_bseb` = values(b_sharif_pg_b_sharif_bseb),
                   `pusauli_pg_dehri_bseb` = values(pusauli_pg_dehri_bseb),
                   `pusauli_pg_arah_pg` = values(pusauli_pg_arah_pg),
                   `patna_pg_khagaul_bseb` = values(patna_pg_khagaul_bseb),
                   `patna_pg_fatwa_bseb` = values(patna_pg_fatwa_bseb),
                   `k_gaon_ntpc_k_gaon_bseb` = values(k_gaon_ntpc_k_gaon_bseb),
                   `k_gaon_ntpc_sabour_bseb` = values(k_gaon_ntpc_sabour_bseb),
                   `lalmatia_sabour_sult` = values(lalmatia_sabour_sult),
                   `b_sharif_barhi_barhi_end` = values(b_sharif_barhi_barhi_end),
                   `barhi_rajgir` = values(barhi_rajgir),
                   `b_sharif_tenughat` = values(b_sharif_tenughat),
                   `sonenar_garhwa` = values(sonenar_garhwa),
                   `sultangunj_deoghar` = values(sultangunj_deoghar),
                   `sonenagar_rihand` = values(sonenagar_rihand),
                   `karmanasa_sahupuri` = values(karmanasa_sahupuri),
                   `gaya_pg_bodhgaya_bseb` = values(gaya_pg_bodhgaya_bseb),
                   `gaya_pg_dehri_bseb` = values(gaya_pg_dehri_bseb),
                   `patna_pg_sipara_bseb` = values(patna_pg_sipara_bseb),
                   `banka_pg_banka_bseb` = values(banka_pg_banka_bseb),
                   `lakhisarai_lakhisarai_pg` = values(lakhisarai_lakhisarai_pg),
                   `lakhisarai_jamui` = values(lakhisarai_jamui)"""

cursor = conn.cursor()
cursor.executemany(SQL5, data)
conn.commit()
cursor.close()

A prepared statement is used to execute the same statement repeatedly with high efficiency and safety .
The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is send to the database server. The server performs a syntax check and initializes server internal resources for later use.

Is MySQLdb support Prepared Statements ?

this is the test code:

import MySQLdb

conn = MySQLdb.connect(user='xxx',passwd='xxx',db = 'xxx')

cursor = conn.cursor()

n = cursor.execute(
    'select * from xxx where `content` in %(content)s',
    { 'content': ('aa','bb','cc') }
)
print cursor._last_executed

I hope it print:

select * from xxx where `content` in ('aa', 'bb', 'cc')

but actually it print:

select * from xxx where `content` in ("'aa'", "'bb'", "'cc'")

and because of this , the query fetch nothing.

Is this a bug of MySQLdb ?

my system:

• Ubuntu 12.04.2 LTS
• Python 2.7.3
• MySQLdb 1.2.3

http://pypi.python.org/pypi/MySQL-python/1.2.2 refers to http://internap.dl.sourceforge.net/sourceforge/mysql-python/MySQL-python-1.2.2.tar.gz, which doesn't resolve.

http://download.sourceforge.net/sourceforge/mysql-python/MySQL-python-1.2.2.tar.gz would probably work.

Someone reported this to sourceforge/PyPI a year ago, but they closed the issue because they said it's up to the package maintainer to fix it:

http://sourceforge.net/tracker/?func=detail&aid=3107645&group_id=66150&atid=513503

I've found a reproducible error case where an error that should be simple SQL syntax is masked and made very hard to spot by giving a rather mysterious:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
_mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now")"

What I expected:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';)' at line 1")

Minimal case to reproduce this:
(assume you have a blank db, and have created only a connection to the db, called conn)

   >>> conn.query("Create table foo(bar int(11))")
   >>> conn.query("insert into foo values (1););")
   >>> conn.query("insert into foo values (2););")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
_mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

I realise this is bad syntax, but it was generated and from the error I couldn’t tell that that was the problem.

I am trying to make a python program that connects to a mysql server using mysqldb via SSL certificates.

I believe the MYSQL server has correctly implemented SSL and the certificates I have work, because I can connect from a different machine using mysql connectors with these certificates.

I believe mysqldb is correctly installed because I can connect to a different mysql server without ssl.

I am left to believe it is my code that is incorrect. The resulting error code is difficult to interpret and seems to cover a wide range of fault points. If anyone has a suggestion, I would appreciate it.

PYTHON CODE:
ssl = {'cert': './security/dan.crt', 'ca': './security/mysql.crt', 'key': './security/dan.key'}
mysql_danre = {'host':'host.edu', 'user':'dan', 'passwd':'password', 'db':'dan_re', 'ssl':ssl}
myDB = MySQLdb.connect(**mysql_danre)

RESULTING ERROR:
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on 'host.edu' (110)")

I just recently upgraded to 1.2.4, and during an executemany statement with a somewhat large dataset (around 40,000 lines), I consistently get this error at the same place in the code:

Traceback (most recent call last):
  File "./update_controller.py", line 277, in <module>
    pq=args.pq).weekly_update()
  File "./update_controller.py", line 148, in weekly_update
    TableUpdater(self.table)().update_pq(self.allowprdr)
  File
"/home/usapparel/projects/Logo_Company/inventory/src/utilities/updates.py",
line 300, in update_pq
    super(AmazonTableUpdater, self).update_pq(allow_pr_decr)
  File
"/home/usapparel/projects/Logo_Company/inventory/src/utilities/updates.py",
line 138, in update_pq
    sql_statement(self.conn, stmnt, prices.values())
  File
"/home/usapparel/projects/Logo_Company/inventory/src/utilities/database.py",
line 75, in sql_statement
    conn.rollback()
_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')

The code is (don't laugh :) ):

    134         stmnt = ("CREATE TEMPORARY TABLE pq_temp(id INT PRIMARY KEY, price "
    135                  "DECIMAL(10,2), quantity INT, extratime INT(2) NOT NULL)")
    136         sql_statement(self.conn, stmnt)
    137         stmnt = "INSERT INTO pq_temp VALUES(%s, %s, %s, %s)"
--> 138         sql_statement(self.conn, stmnt, prices.values())

sql_statement is an abstracted function that simply calls 'execute' or, in
this case, 'executemany'.

The data is something like this:

ipdb> prices.items()[0:2]
[((2554L,), [2554L, Decimal('13.37'), 307L, 0L]), ((40790L,), [40790L,
Decimal('22.47'), 2244L, -1L])]

Rolling back to 1.2.3 (which I've been using successfully with this same code
for a long time now) works perfectly. Mysql version is 5.5.30.
I also tried inserting the data into a non-temporary table, but got the same
result. Also got the same result on two different machines with the same mysql
and mysql-python versions.

Let me know if there's any other information I can provide.

Thanks!
Scott

I encounter some warnings since I updated to MySQLdb 1.2.4 with datetime objects.

http://sourceforge.net/p/mysql-python/svn/659/

That update affected to some types of datetime.

>>> import datetime, pytz
>>> datetime.datetime(2013, 1, 2, 23, 30, 10, 12345, tzinfo=pytz.utc).isoformat()
'2013-01-02T23:30:10.012345+00:00'

datetime.datetime.isoformat() is not compatible with strftime. It includes microseconds and utcoffset.

What should I do?

The following code reproduces the problem. I've upped the number of connections to make the problem arise quicker, but the default twisted connection pool of 5 also causes the same issue. The issue appears to arise primarily on large number of rows (though I haven't tested a large number of columns with few rows). For this example, I ran it on a table with the following schema:

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| test  | varchar(32) | YES  |     | NULL    |                |
| RID   | int(11)     | NO   | PRI | NULL    | auto_increment |
+-------+-------------+------+-----+---------+----------------+

I inserted 1000 rows, all consisting of the single character "a". I will also attach the mysql dump of the table for ease of reproduction.

from twisted.enterprise import adbapi
from twisted.internet import reactor

dbpool = adbapi.ConnectionPool(
    "MySQLdb", host='localhost',
    cp_max=10, cp_min=10, db='x', user='x', passwd='x')

def select():
    return dbpool.runQuery("SELECT * FROM test")

num = 0

def print_result(result):
    global num
    print "Done", num
    num += 1

for i in xrange(1, 1000):
    select().addCallback(print_result)

print "Done Adding"

reactor.run()

Is there any reason not to add CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS on mysql connection in mysql_real_connect.

http://dev.mysql.com/doc/refman/5.6/en/password-expiration-sandbox-mode.html