mysql backend: 'SHOW TABLE STATUS WHERE name=...' not compatible with MySQL 4

[matf@…]

django/db/backends/mysql/base.py executes "SHOW TABLE STATUS WHERE Name='INTROSPECT_TEST'" query.

"SHOW TABLE STATUS WHERE name='table_name'" syntax was introduced in mysql 5.
See link: ​http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html

Correct syntax that works with both MySQL 4 and MySQL 5 is "SHOW TABLE STATUS LIKE 'table_name'"
See link: ​http://dev.mysql.com/doc/refman/4.1/en/show-table-status.html

Since django <= 1.4 is compatible with MySQL 4 this is a bug. This functionality is used in e.g. South database migration library.

[Claude Paroz]

This was already in 1.3 (introduced in [74d485c4ec2b]). Considering the probably low number of Django sites running on MySQL 4, this is probably not a serious enough issue to be worth fixing in a stable release. Thanks for the report anyway.

[anonymous]

I don't see what's the problem, it's just 1 line of code that needs to be changed and Your documentation states that Django <= 1.4 is compatible with MySQL 4 which is right now simply not true...

PS. Not every application is installed in one central server, where 1 manager can decide to upgrade to MySQL 5. Some types of application are distributed to clients where older installations with MySQL4 exists (e.g. running another service). And frankly having them install another MySQL service because of that silly bug is just a bit too much. We have to patch, distribute patched version and re-patch in every next version.

Again, it's just 1 line of code to be in a point when You can do what Your documentation states.

[Claude Paroz]

I did get some other core developers opinion on IRC before setting this to won't fix. You can always try to get some support by writing the the django-developers mailing list. But generally, we are very conservative with the type of bugs we backport to stable releases. The fact that we didn't get any report while this issue was already in Django 1.3 doesn't plead for it.

[matf@…]

Actually I'm using Django 1.3 and I'm considering upgrading to Django 1.4 that's why I reported it to 1.4 version. The fact that this wasn't reported before is because this functionality is rarely used, but still this is a bug and ignoring it won't make it disappear.

I am a developer myself and I deal with ticket request every day, that's why I cannot understand what is the problem here ?
Fixing this bug (only 1 line of code) takes 1 min of Your time and restores full MySQL4 compatibility. Asking other core developers and responding here takes 10 times more time and still leaves Django inconsistent with it's 1.3 AND 1.4 documentation.

[Aymeric Augustin]

Verifying this change takes a bit more than 1 minute — it requires installing a MySQL 4 server, checking why the syntax was changed, whether using the old syntax has any drawbacks vs. the new one, and if that change could introduce regressions.

Besides, our ​CI infrastructure doesn't test MySQL 4 either, so we don't know to what extends Django still supports it.

I may have estimated wrongly the severity of this bug in my discussion with Claude. Does this only break tests, or does it prevent running Django altogether?

[matf@…]

I understand Your concerns, as I said I am a developer myself and I wouldn't propose a patch if I wouldn't be sure that it does not break anything. I'm sure all the MySQL5 tests will run fine because I'm using my solution right now and it works on both MySQL4 and MySQL5.

Syntax of 'SHOW TABLE STATUS ...' in MySQL 5 was enhanced to allow filtering not only by table 'name' but also by other fields. It does not break old syntax 'SHOW TABLE STATUS LIKE ..' which works both with MySQL4 and MySQL5 (see documentation links) and Django is interested in filtering by 'name' only, so there's absolutely no reason to use new enhanced syntax.

Having looked at other mysql backend files I see A LOT of effort into falling back to support MySQL4 if new features are unavailable.
For example see: introspection.py 'get_key_columns' function. It executes query for 'information_schema' and in case of an exception it parses result of 'SHOW CREATE TABLE ..'. I mean parsing text result is a huge and error prone method of querying something from database, but nevertheless it's still done there just to support MySQL4.

If one part of the code tries VERY hard to fallback gracefully I see great inconsistency if other part uses new MySQL5 syntax where old MySQL4 syntax meets all of the requirements and works just as fine.

[Claude Paroz <claude@…>]

In ec93ecdd1071e5552fa10afa1160e2081cfc1de0:

[1.4.x] Fixed #19702 -- Changed a SQL command syntax to be MySQL 4-compatible

Thanks matf at op.pl for the report.