[RFC] Python 3 and MySQL

[Naoki INADA]

Hi, folks.

I believe most popular MySQL driver for Python is MySQL-python (MySQLdb).

http://py3readiness.org/ shows MySQL-python is the 4th popular package that does not support Python 3.

I've forked MySQL-python because I want to move Python 3 completely ASAP.

https://pypi.python.org/pypi/mysqlclient

https://github.com/PyMySQL/mysqlclient-python

https://docs.djangoproject.com/en/dev/ref/databases/#mysql-db-api-drivers

references MySQL-for-Python3 as Python 3 port but it doesn't support binary and

it has not maintained for a long time.

So I propose replacing MySQL-for-Python3 with my fork.

MySQL-Connector/Python 1.2.2 (GA) has been released recently (not updated PyPI, but you

can download it from http://dev.mysql.com/downloads/connector/python/ ). It also support

Python 3 and Django.

One option for moving to Python 3 is recommend MySQL Connector/Python and doesn't

support MySQL-python nor mysqlclient.

https://code.djangoproject.com/ticket/12500

https://code.djangoproject.com/ticket/22732

But parsing MySQL packets with pure CPython is slow. Quick benchmark shows

MySQL Connector/Python is 5x slower than mysqlclient.

https://gist.github.com/methane/90ec97dda7fa9c7c4ef1

[Collin Anderson]

While we're on the topic, I'd like to propose (also?) supporting the pure-python, MySQLdb-compatible pymysql, which INADA Naoki (methane) has also put a lot of work into. 

pymysql is pure-python, so it's really easy to get up-and running on Mac OS X and in other environments, because you don't need the mysql header files around. Just pip install pymysql, and you're good to go.

https://code.djangoproject.com/ticket/22391

Tim says on the ticket: "One question I have is how we are going to ensure that it doesn't break if we merge it to core. Do we need another MySQL build to the Jenkins matrix?"

[Tim Graham]

I'd like see some community consensus on the best solution for a MySQL adapter rather than than have more than one build for MySQL.

I don't know the MySQL ecosystem very well. Naoki, is there no interest from MySQLdb in your Python 3 compatibility changes? On the PyPI page it says, "Python-3.0 will be supported in a future release." but it's been like that for several years so who knows if it's actually going to happen.

I think we could at least consider a change from the (somewhat random?) Python 3 fork of MySQLdb we are using for the Python 3 bulids.

[Naoki INADA]

On Wednesday, August 13, 2014 1:41:22 AM UTC+9, Tim Graham wrote:

I'd like see some community consensus on the best solution for a MySQL adapter rather than than have more than one build for MySQL.

I don't know the MySQL ecosystem very well. Naoki, is there no interest from MySQLdb in your Python 3 compatibility changes? On the PyPI page it says, "Python-3.0 will be supported in a future release." but it's been like that for several years so who knows if it's actually going to happen.

I've sent pull request to MySQLdb that support Python 3. (https://github.com/farcepest/MySQLdb1/pull/62 )

But project owner (Andy) doesn't have time to review PRs for now.

MySQLdb is the most popular MySQL driver for long years. But it's Andy's personal project and development speed is

slowed down for these years. (https://github.com/farcepest )

I hope Andy make MySQLdb as community based project.

But I have not proposed it to him, because I'm afraid my poor English say something rude.

So I've released my fork (including windows wheel) as mysqlclient.

I'll maintain it until Andy will be back active.

PyMySQL is also popular MySQL driver because it's pure Python and works well with gevent.

PyMySQL was also inactive for few years. But it switched to new maintainer (Marcel).

https://groups.google.com/forum/#!topic/pymysql-users/hrIYyeGMQwY

After switching, I'm also active committer of PyMySQL.

We've released PyMySQL 0.6 as stable release supporting Python 3.

MySQL-Connector/Python was not so popular (as far as I know).

But 1.2.2 GA was released recently.  It looks nice to me. It may be popular if Django recommend it.

It's repository was moved from launchpad to github. https://github.com/oracle/mysql-connector-python

Although it looks nice to me, there are 2 drawbacks.

* They don't put package to PyPI. `--allow-external mysql-connector-python` option is required for `pip install`.

* Although repository was moved to Github, bug database is still bit unfriendly (http://bugs.mysql.com/ ).

I think if Oracle will be more familiar with OSS community (accepting pull requests, using Travis-CI, etc...),

It can replace PyMySQL. Because it's development is more active than us.

[Claude Paroz]

On Thursday, August 14, 2014 3:01:07 PM UTC+2, Naoki INADA wrote:

MySQL-Connector/Python was not so popular (as far as I know).

But 1.2.2 GA was released recently.  It looks nice to me. It may be popular if Django recommend it.

It's repository was moved from launchpad to github. https://github.com/oracle/mysql-connector-python

Although it looks nice to me, there are 2 drawbacks.

* They don't put package to PyPI. `--allow-external mysql-connector-python` option is required for `pip install`.

* Although repository was moved to Github, bug database is still bit unfriendly (http://bugs.mysql.com/ ).

I think if Oracle will be more familiar with OSS community (accepting pull requests, using Travis-CI, etc...),

It can replace PyMySQL. Because it's development is more active than us.

Naoki,

Are you aware of performance benchmarks comparing your MySQLdb1 fork and mysql-connector-python?

About your fork, do you plan to maintain it in the middle/long term? I saw that issues were not enabled on your Github repo. Is it on purpose? What's the plan about bug tracking of your fork?

Claude

[Corey Farwell]

Claude, it looks like Naoki opened up Issues on the repository.

Coincidentally, I just opened a Django Ticket related to this: https://code.djangoproject.com/ticket/23446 I had not seen this thread until Tim Graham pointed it out. See that ticket for my views on the matter. tldr: recommending mysqldb1 is a documentation bug as it is a broken library.

[Collin Anderson]

I'm warming up to mysqlclient. I assume it has better performance or is more correct than pymysql? Is there any hope to getting the package included in debian/ubuntu?

[INADA Naoki]

>
> Naoki,
>
> Are you aware of performance benchmarks comparing your MySQLdb1 fork and
> mysql-connector-python?

I'll show you some numbers. But I'm not have time for now.

>
> About your fork, do you plan to maintain it in the middle/long term? I saw
> that issues were not enabled on your Github repo. Is it on purpose? What's
> the plan about bug tracking of your fork?
>

Thanks for pointing it out. I've enabled github issue.

I'll maintain mysqlclient-python until MySQLdb1 development is restarted.
Since I hope MySQLdb1 and mysqlclient-python merged someday (like
setuptools and distribute were merged),
I don't want to implement new feature.
'maintain' means bugfix, support new Python, new libmysqlclient and
new OS, etc...

--
INADA Naoki <songof...@gmail.com>

[Naoki INADA]

On Monday, September 8, 2014 2:04:26 PM UTC+9, Naoki INADA wrote:

>
> Naoki,
>
> Are you aware of performance benchmarks comparing your MySQLdb1 fork and
> mysql-connector-python?

I'll show you some numbers.  But I'm not have time for now.

I've posted quick benchmark:

https://github.com/methane/mysql-driver-benchmarks

Most heavy part of MySQL Driver is parsing packet.

There are (number of columsn) descripter packet and (number of columns * number of rows) result packet in query response.

On PyPy, packet parsing is faster after JIT warmup.

MySQL-Connector/Python reads from socket packet by packed.  It makes many system call.

PyMySQL uses buffering for faster receiving.

[Claude Paroz]

Le lundi 8 septembre 2014 08:33:39 UTC+2, Naoki INADA a écrit :

On Monday, September 8, 2014 2:04:26 PM UTC+9, Naoki INADA wrote:

>
> Naoki,
>
> Are you aware of performance benchmarks comparing your MySQLdb1 fork and
> mysql-connector-python?

I've posted quick benchmark:

https://github.com/methane/mysql-driver-benchmarks

Most heavy part of MySQL Driver is parsing packet.

There are (number of columsn) descripter packet and (number of columns * number of rows) result packet in query response.

On PyPy, packet parsing is faster after JIT warmup.

MySQL-Connector/Python reads from socket packet by packed.  It makes many system call.

PyMySQL uses buffering for faster receiving.

Great, 8-9x difference in favor of the C implementation (which might be expected). That's a clear sign for me that we should continue using primarily the C version. That doesn't mean we shouldn't make efforts to support at least one Python-only driver (as asked in #12500).

> About your fork, do you plan to maintain it in the middle/long term? I saw
> that issues were not enabled on your Github repo. Is it on purpose? What's
> the plan about bug tracking of your fork?
>

Thanks for pointing it out.  I've enabled github issue.

I'll maintain mysqlclient-python until MySQLdb1 development is restarted.
Since I hope MySQLdb1 and mysqlclient-python merged someday (like
setuptools and distribute were merged),
I don't want to implement new feature.
'maintain' means bugfix, support new Python, new libmysqlclient and
new OS, etc...

That makes sense.
Tim, Florian, would it be possible to switch to that fork on the CI server (both for Python 2 and 3) for MySQL?
Then of course, if all goes well, we would need to update our documentation.

Claude

[Collin Anderson]

Ohh wow. I also just noticed CyMySQL, which is a fork of PyMySQL with optional C speedups. It seems like a good idea in theory, though it appears to have diverged from PyMySQL a bit.

[Tim Graham]

I'll test mysqlclient-python on my staging CI server today.

[Collin Anderson]

It's great to see us moving forward on this. Thanks to Naoki for all of the work on this!

[Tim Graham]

We'd need mysqlclient to support Python 3.2 (or drop official support for MySQL/Python 3.2):

django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: /var/lib/jenkins/workspace/django-selenium/database/mysql/python/python3.2/tests/.env/lib/python3.2/site-packages/_mysql.cpython-32mu.so: undefined symbol: PyUnicode_AsUTF8

Python 2.7 test failures:

custom_pk.tests.CustomPKTests.test_required_pk
fixtures.tests.FixtureLoadingTests.test_loaddata_error_message
generic_relations_regress.tests.GenericRelationTests.test_target_model_is_unsaved
get_or_create.tests.GetOrCreateTests.test_get_or_create_invalid_params
get_or_create.tests.UpdateOrCreateTests.test_integrity
model_fields.tests.BooleanFieldTests.test_null_default

Python 3.4 test failures:

backends.tests.BackendTestCase.test_cursor_execute_with_pyformat
backends.tests.BackendTestCase.test_cursor_executemany_with_pyformat
backends.tests.BackendTestCase.test_cursor_executemany_with_pyformat_iterator
custom_pk.tests.CustomPKTests.test_required_pk
fixtures.tests.FixtureLoadingTests.test_loaddata_error_message
generic_relations_regress.tests.GenericRelationTests.test_target_model_is_unsaved
get_or_create.tests.GetOrCreateTests.test_get_or_create_invalid_params
get_or_create.tests.UpdateOrCreateTests.test_integrity
model_fields.tests.BooleanFieldTests.test_null_default
raw_query.tests.RawQueryTests.test_pyformat_params

Let me know if you need tracebacks, but I assume you'll need to run the tests  yourself in our to fix the issues.

[INADA Naoki]

On Mon, Sep 8, 2014 at 11:28 PM, Tim Graham <timog...@gmail.com> wrote:
> We'd need mysqlclient to support Python 3.2 (or drop official support for
> MySQL/Python 3.2):

Python 3.3 introduces PEP 393 (Flexible String Representation) and
many Unicode API has
been changed and deprecated. It also introduce unicode literal.
Supporting Python 3.2 will make code messy.

I want to drop Python 3.2 support since I believe most Python 3 users
are aggressive enough
to go forward.

How Python 3.2 important for you?

> --
> You received this message because you are subscribed to a topic in the
> Google Groups "Django developers" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/django-developers/n-TI8mBcegE/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> django-develop...@googlegroups.com.
> To post to this group, send email to django-d...@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-developers.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-developers/fcc06c58-a366-4996-9b57-6412d93c6483%40googlegroups.com.
>
> For more options, visit https://groups.google.com/d/optout.



--
INADA Naoki <songof...@gmail.com>

[Claude Paroz]

On Monday, September 8, 2014 5:19:56 PM UTC+2, Naoki INADA wrote:

On Mon, Sep 8, 2014 at 11:28 PM, Tim Graham <timog...@gmail.com> wrote:
> We'd need mysqlclient to support Python 3.2 (or drop official support for
> MySQL/Python 3.2):

Python 3.3 introduces PEP 393 (Flexible String Representation) and
many Unicode API has
been changed and deprecated.  It also introduce unicode literal.
Supporting Python 3.2 will make code messy.

I want to drop Python 3.2 support since I believe most Python 3 users
are aggressive enough
to go forward.

How Python 3.2 important for you?

I think we could live with MySQL not supporting Python 3.2.

> Python 2.7 test failures:
>
>
> custom_pk.tests.CustomPKTests.test_required_pk
> fixtures.tests.FixtureLoadingTests.test_loaddata_error_message
> generic_relations_regress.tests.GenericRelationTests.test_target_model_is_unsaved
> get_or_create.tests.GetOrCreateTests.test_get_or_create_invalid_params
> get_or_create.tests.UpdateOrCreateTests.test_integrity
> model_fields.tests.BooleanFieldTests.test_null_default

Thanks Tim for testing. These errors seem to all have the same origin, null inserts into not-null columns generate OperationalError instead of IntegrityError.
 

> Python 3.4 test failures:
>
>
> backends.tests.BackendTestCase.test_cursor_execute_with_pyformat
> backends.tests.BackendTestCase.test_cursor_executemany_with_pyformat
> backends.tests.BackendTestCase.test_cursor_executemany_with_pyformat_iterator
> custom_pk.tests.CustomPKTests.test_required_pk
> fixtures.tests.FixtureLoadingTests.test_loaddata_error_message
> generic_relations_regress.tests.GenericRelationTests.test_target_model_is_unsaved
> get_or_create.tests.GetOrCreateTests.test_get_or_create_invalid_params
> get_or_create.tests.UpdateOrCreateTests.test_integrity
> model_fields.tests.BooleanFieldTests.test_null_default
> raw_query.tests.RawQueryTests.test_pyformat_params

In addition of the above issue, it seems that pyformat isn't supported for Python3. Something around these lines:
cursor.execute("INSERT INTO table f1, f2 values(%(val1)s, %(val2)s)", {"val1": value_1, "val2": value_2})

I've created issues on the mysqlclient bug tracker.
https://github.com/PyMySQL/mysqlclient-python/issues/3
https://github.com/PyMySQL/mysqlclient-python/issues/4

Claude

 

[Tim Graham]

I don't think it'd be a problem, but I don't use MySQL or Python 3.2 on a regular basis.

[Collin Anderson]

I also don't need python 3.2 support (or even python 3.3 support for that matter)

[Naoki INADA]

I've fixed `%(xxx)s` style formatting.

I have not changed error switch:

https://github.com/PyMySQL/mysqlclient-python/blob/master/_mysql.c#L150

https://github.com/farcepest/MySQLdb1/blob/master/_mysql.c#L180

I'll investigate the problem, but I can't promise any date for fixing it.

[Naoki INADA]

I can't run django test.

Synchronizing apps without migrations:

  Creating tables...

  Installing custom SQL...

  Installing indexes...

    Failed to install index for admin_views.PrePopulatedPostLargeSlug model: (1071, 'Specified key was too long; max key length is 767 bytes')

I use 1.7 tag on git repository.

[Florian Apolloner]

On Tuesday, September 9, 2014 9:22:10 AM UTC+2, Naoki INADA wrote:

    Failed to install index for admin_views.PrePopulatedPostLargeSlug model: (1071, 'Specified key was too long; max key length is 767 bytes')

Welcome to the wonderful world to mysql, afaik this is a warning and not an error; so your database driver might convert that incorrectly.

[INADA Naoki]

I run django tests using MySQL-python 1.2.5 (aka, MySQLdb1) and Python 2.7.8.
So it means django doesn't work with MySQL with MySQL-python.

$ python -V
Python 2.7.8
(mysql2)[inada-n@MBA:~]
$ pip list
MySQL-python (1.2.5)
pip (1.5.6)
setuptools (3.6)
wsgiref (0.1.2)

> --
> You received this message because you are subscribed to a topic in the
> Google Groups "Django developers" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/django-developers/n-TI8mBcegE/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> django-develop...@googlegroups.com.
> To post to this group, send email to django-d...@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-developers.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/django-developers/4bd04867-0f32-4db2-85b3-41671f92aecc%40googlegroups.com.

[Florian Apolloner]

http://djangoci.com/ disagrees with you :) Would have to check the specific mysqldb version.

[Florian Apolloner]

Python 2.7.3 and MySQL_python-1.2.5-cp27-none-linux_x86_64.whl

[Naoki INADA]

Maybe, this difference is come from MySQL version.

I use MySQL 5.6.20 (Homebrew).

mysql> show create table admin_views_prepopulatedpostlargeslug\G

*************************** 1. row ***************************

       Table: admin_views_prepopulatedpostlargeslug

Create Table: CREATE TABLE `admin_views_prepopulatedpostlargeslug` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `title` varchar(100) NOT NULL,

  `published` tinyint(1) NOT NULL,

  `slug` varchar(1000) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> CREATE INDEX `admin_views_prepopulatedpostlargeslug_f52cfca0` ON `admin_views_prepopulatedpostlargeslug` (`slug`);

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

[Naoki INADA]

I find what cause this problem.  I usually use strict mode.  In my my.cnf:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

After commenting out this line:

mysql> CREATE INDEX `admin_views_prepopulatedpostlargeslug_f52cfca0` ON `admin_views_prepopulatedpostlargeslug` (`slug`);

Query OK, 0 rows affected, 1 warning (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 1

[Naoki INADA]

I've fixed them and released mysqlclient 1.3.3.

https://pypi.python.org/pypi/mysqlclient

[Daniel Sears]

Aside from the technical issues, there are the licensing issues. The Django community strongly prefers BSD-style licensing and both mysqldb and MySQL Connector/Python use GPL licenses. For an e-mail thread which addresses these GPL issues, see

https://groups.google.com/forum/#!msg/django-developers/8r_RVmUe5ys/09lCwJl-L1kJ

PyMySQL does appear to have a BSD-like license.

--Dan

[Corey Farwell]

It is a valid concern. That said, I'd rather the documentation recommend a GPL, Python 3+2, working MySQL connector over a GPL, Python 2, partially broken MySQL connector.

[Tim Graham]

Test failures are fixed with mysqlclient 1.3.3. I believe we can now move the discussion of the implementation of this to the ticket: https://code.djangoproject.com/ticket/23446

There are some tickets about pymysql (below), but I have some reservations about officially supporting more than one MySQL connector since our buildbot would presumably need to test all of them.

https://code.djangoproject.com/ticket/20542

https://code.djangoproject.com/ticket/22391

[Collin Anderson]

I started the ball rolling on getting mysqlclient packaged in Debian/Ubuntu.

https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=768096