[patch] Updates for MySQL backend

Patch for improved MySQL support

Discuss this bug on Google Groups

A couple of immediate concerns I have here:

• the unconditional use of utf-8 to communicate with the database would seem to imply that the database must be in utf-8 collation mode. Is that wise? How about legacy databases, are they going to any worse off than they are at the moment?
• I'm not super-comfortable with requiring MySQLdb-1.2.1, since that was only released in April this year, so it's less than 6 months old and won't be widely used yet.

In general, I think the patch does some useful things. Using the ability to be somewhat more standards compliant and better unicode support in MySQL 5.0 is nice. However, we cannot ignore 4.x releases, since, as mentioned in the thread, hosting companies still supply it and I personally know of a number of corporate production environments where 4.x is still the primary system in use. Need to think about whether we can keep a "one size fits all (with version checks)" mysql backend or if we want to bite the bullet and move to a mysql4 and mysql split set of backends.

I know I'm going to regret this, but I'll keep an eye on this patch and help work towards integrating it slowly.

What would make things easier would be if the patch could be split into a few pieces along the following lines (or at least knowing which splits aren't possible).

1. Changes that work with MySQL 4.0 (I realise you don't have a test system for this. A good guess would still help, though).
2. Changes that require MySQL 4.1
3. Changes that require MySQL 5.x
4. Changes that require MySQL-1.2.1 (is this all of it, or just some features?)

I think we'll need unicode strings off for now, until more of the internals are converted. But having it available at the change of more or less a single line like you have now is great. That will certainly help in the not too distant future.

The current trunk version already sets the connection character set to UTF-8. See http://code.djangoproject.com/browser/django/trunk/django/db/backends/mysql/base.py#L93 This does *not* imply that the database itself is in UTF-8. If a different character set is in use for the database/table/column, the server transcodes from the connection character set into the correct one. The difference between the way the trunk version does it and the patched version is, the trunk version sets it with an SQL statement, and the patched version uses an API call. Additionally there are some internal things that have to happen for the correct conversion to take place, so doing it with SQL doesn't necessarily work the way you'd expect.

I'll try splitting up the patch sometime next week. My original intention was to use this as a different backend (mysql5) so that we'd have backwards-compatibility for those who need it.

Also, I've been developing an app with this patch and I haven't had to do anything special with Django yet even with all text-like columns coming back in Unicode. That said, there are no bad problems with the trunk version.

I tried your patch for django and mysql5 today. It has the same problem as it has with 'SET NAMES utf8'.

If I change two lines of your code, my problem is solved in the same way as I did it with the patch above: 'use_unicode': False, 'charset': 'latin1',

I would suggested configuring the DATABASE_ENCODING for mysql backend.

Sorry, posted to the wrong thread.

I tried commenting on the thread that was suggested, but google groups wouldn't let me. The above patch does not work with MySQL 4.1 for me. Gives an SQL error, which as far as I can tell is due to double quoting. If you need/want the actual error I can provide it. (Just email me)

I think that we need to keep support for 4.1 because many shared hosting environments still use it. See my comments on #2810

Hi Andy,

thanks for contributing this ticket, unfortunately it has been waiting a bit long in the queue. I'm now trying to triage it, but it looks a bit "aborted". Is this still current? Do you have any comments about mysql 4.1, 4.0 and 3.23, as the preceding comment reports a problem with 4.1?

Michael

Hmmm ... I guess a core dev needs to look through it if they want it or not.

I need to review it some more, too. I would like to get support in for read_default_file. I am at PyCon now and staying for Sprints and expect to be working.on this.

Updated MySQL support

What's new with this patch:

• Adds support for DATABASE_OPTIONS setting.
• Doesn't try to change the character set.
• Doesn't try to set the sql_mode.
• Doesn't try to return data as unicode

With the addition of DATABASE_OPTIONS support, you are able to set charset, sql_mode, and use_unicode in the settings file. You can also use read_default_file to set connection options.

I've tested this with MySQL-5.0 but there's nothing here that should break on earlier versions. It passes the Django unit tests, but beware: Settings in DATABASE_OPTIONS override those in DATABASE_*, so the tests will use the database specified by DATABASE_OPTIONS, and **not** the the test database it creates (django_test_db). This is probably true for any backend that uses DATABASE_OPTIONS.

If you do set charset, you may want to set use_unicode=False. use_unicode is set to True if you set charset. Currently the unit tests for models do not pass if you have use_unicode=True, but they all look like comparison tests.

I am also working on some MySQL notes for Django.

You could probably remove the test for MySQLdb-1.2.1 or newer.

Some notes on using MySQL with Django. (disregard the previous HTML file, this is the original)

Found a small bug in the last patch dealing with Django's overloading of DATABASE_HOST

Great work, sir. This last patch (and the mysql.txt file) looks like something we should use. I've read the code once and it looks right from a "fits in with Django" perspective. I'm deferring to your experience on the MySQL front. I want to test it out on an older machine that I'll have access to tomorrow just to see that we haven't hosed bakcwards compatibility. Given the features this adds and the long break that will exist between 0.96 and 1.0, if it doesn't break backwards compat in any obvious way, I think it should go in before 0.96, since it will help with a lot of peoples' issues.

In the short-term we should fix the testing framework to look inside DATABASE_OPTIONS and force it to point to django_test_db, but that's probably not a showstopper for these changes, since it's possible to work around it in any case.

Thanks a lot for persisting with this and coming up with a non-intrusive solution.

Commit pending on resolution of this thread on django-dev.

(In [4724]) Fixed #2635 -- Added improved MySQL backend support from Andy Dustman. Also added database.txt documentation; currently only describing Django-related features of MySQL versions.

As of this commit, there are four known test failures due to (a) no transaction support with MyISAM storage engine and (b) MySQLdb automatically creating decimal types for Django's "float" field.

i just upgraded to a revision > 4724 -- that broke my text encodings coming from mysql. is this a backwards incompatible change (which i failed to notice) or should everything be working as before?

in the latter case something is definately wrong :)

I guess i forgot to mention that my MySQL Version is 4.1.11 (current Debian Sarge).

Also i had a quick look at http://code.djangoproject.com/wiki/BackwardsIncompatibleChanges but i did not find a notice that would suggest that i have to change something.

So this either needs Documentation or the patch needs improvement.

It should be backwards compatible, so please provide some more information about what setup you have (database encoding, any special settings.py options, etc) and precisely what is going wrong ("it broke" could mean anything).

Please also open this issue as a new ticket: if everybody having some problem with the MySQL upgrade reopens this one, it will end up containing a multitude of different issues, rather than just the upgrade patch.

Replying to derelm:

i just upgraded to a revision > 4724 -- that broke my text encodings coming from mysql. is this a backwards incompatible change (which i failed to notice) or should everything be working as before? in the latter case something is definately wrong :)

Try putting in your settings:

DATABASE_OPTIONS = dict(charset="utf8")

and see if that helps.

that didn't help.

but i noticed that the version check doesn't work as expected (at least not with Debian Sarges MySQLdb version 1.2.1g2). see #3747

and seeing that that version isn't supported at all, my complaint about the umlauts not working is invalid.

so i will need to stick with r4723.

There shouldn't be a huge difference at the "character encoding level" between 1.2.1g2 and 1.2.1-final, so if you wanted to post some details to the django-users list, we can probably try to work out what's happening here.

Replying to Andy Dustman <farcepest@gmail.com>:

I haev the same problem

Try putting in your settings: DATABASE_OPTIONS = dict(charset="utf8") and see if that helps.

and this helps. But I am very angry, that such a change in config is needed. Also django uses UTF-8 from beginning and the UTF-8 should be default for every install.

Replying to derelm:

that didn't help. but i noticed that the version check doesn't work as expected (at least not with Debian Sarges MySQLdb version 1.2.1g2). see #3747 and seeing that that version isn't supported at all, my complaint about the umlauts not working is invalid. so i will need to stick with r4723.

Revision 4724 is giving me headaches for several hours now. My dev environment has no problem at all with rev 4724. But, in my production environment utf8 characters are not handled correctly. It leads to decode errors and utf8 character displayed as , instead of a nice character. My dev enviroment has >>> print MySQLdb.version_info = (1, 2, 1, 'final', 2) and my production enviroment has >>> print MySQLdb.version_info = (1, 2, 1, 'gamma', 3) (Debian). I will try upgrading MySQLdb in my production environment.

Please report any further observations regarding character encoding and mysql in #3754.