utf8 on connection database name or user failure

[regilero]

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()

[regilero]

script output:

1.2.3
Create db foo1
Create db fooé
Create user : foo1 pwd: fooé
Create user : fooé pwd: fooé
+--------------------------+----------------------------+
|     character_set_client |                       utf8 |
| character_set_connection |                       utf8 |
|   character_set_database |                       utf8 |
| character_set_filesystem |                     binary |
|    character_set_results |                       utf8 |
|     character_set_server |                       utf8 |
|     character_set_system |                       utf8 |
|       character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
0
===> [TEST] : CONNECTING foo1 ON foo1 WITH PASSWORD fooé
-> SHELL mysql command TEST:
mysql -u "foo1" --password="fooé" -h "localhost" -D "foo1" -N -e "SELECT 'OK'"
+----+
| OK |
+----+
0
->Trying with MySQLdb:
=================> [OK]
===> [TEST] : CONNECTING foo1 ON fooé WITH PASSWORD fooé
-> SHELL mysql command TEST:
mysql -u "foo1" --password="fooé" -h "localhost" -D "fooé" -N -e "SELECT 'OK'"
+----+
| OK |
+----+
0
->Trying with MySQLdb:
MySQL Exception: : OperationalError(1044, "Access denied for user 'foo1'@'localhost' to database 'foo\xc3\xa9'")
===> [TEST] : CONNECTING fooé ON foo1 WITH PASSWORD fooé
-> SHELL mysql command TEST:
mysql -u "fooé" --password="fooé" -h "localhost" -D "foo1" -N -e "SELECT 'OK'"
+----+
| OK |
+----+
0
->Trying with MySQLdb:
MySQL Exception: : OperationalError(1045, "Access denied for user 'foo\xc3\xa9'@'localhost' (using password: YES)")
===> [TEST] : CONNECTING fooé ON fooé WITH PASSWORD fooé
-> SHELL mysql command TEST:
mysql -u "fooé" --password="fooé" -h "localhost" -D "fooé" -N -e "SELECT 'OK'"
+----+
| OK |
+----+
0
->Trying with MySQLdb:
MySQL Exception: : OperationalError(1045, "Access denied for user 'foo\xc3\xa9'@'localhost' (using password: YES)")
=========REDO the 3 last failing tests by enforcing 'latin1'===
=========The shell test will fail but not MySQLdb===
===> [TEST] : CONNECTING foo1 ON foo� WITH PASSWORD fooé
-> SHELL mysql command TEST:
mysql -u "foo1" --password="fooé" -h "localhost" -D "foo�" -N -e "SELECT 'OK'"
ERROR 1044 (42000): Access denied for user 'foo1'@'localhost' to database 'foo'
256
->Trying with MySQLdb:
=================> [OK]
===> [TEST] : CONNECTING foo� ON foo1 WITH PASSWORD fooé
-> SHELL mysql command TEST:
mysql -u "foo�" --password="fooé" -h "localhost" -D "foo1" -N -e "SELECT 'OK'"
ERROR 1045 (28000): Access denied for user 'foo'@'localhost' (using password: YES)
256
->Trying with MySQLdb:
=================> [OK]
===> [TEST] : CONNECTING foo� ON foo� WITH PASSWORD fooé
-> SHELL mysql command TEST:
mysql -u "foo�" --password="fooé" -h "localhost" -D "foo�" -N -e "SELECT 'OK'"
ERROR 1045 (28000): Access denied for user 'foo'@'localhost' (using password: YES)
256
->Trying with MySQLdb:
=================> [OK]
Drop db foo1
Drop db fooé
Remove user : foo1 pwd: fooé
Remove user : fooé pwd: fooé