[Django] #29451: Add compatibility with MySQL 8

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------
Reporter: Tim | Owner: nobody
Graham |
Type: | Status: new
Cleanup/optimization |
Component: Database | Version: master
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: Accepted | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
See attached log for some test failures I experienced with MySQL 8.0.11.

It seems there's at least some changes in how regular expression matching
works.

--
Ticket URL: <https://code.djangoproject.com/ticket/29451>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* Attachment "mysql-failures.log" added.

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Adam (Chainz) Johnson):

* cc: Adam (Chainz) Johnson (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:1>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tom Forbes):

Got a small patch to fix the syntax errors:
https://github.com/django/django/pull/10021

Seems you need to quote the column names in mysql 8

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:2>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Carlton Gibson <carlton.gibson@…>):

In [changeset:"a253a580e6f20fb7087490225538422eb6cab0bb" a253a580]:
{{{
#!CommitTicketReference repository=""
revision="a253a580e6f20fb7087490225538422eb6cab0bb"
Refs #29451 -- Quoted MySQL column names in tests.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:3>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by GitHub <noreply@…>):

In [changeset:"c4f099de1d0d82c5e1f88768300896eae69bddbd" c4f099d]:
{{{
#!CommitTicketReference repository=""
revision="c4f099de1d0d82c5e1f88768300896eae69bddbd"
Refs #29451 -- Used quote_name for column names in tests.

Regression in a253a580e6f20fb7087490225538422eb6cab0bb
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:4>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tom Forbes):

I was investigating this and was surprised to see that `REGEXP BINARY
'.*'` matched nothing when using MySQL 8. I thought there might be a bug
with MySQL but could not find much online other than this cryptic commit
entitled `Work around REGEXP BINARY not working correctly on MySQL 8 by
using REGEXP_LIKE`:

https://github.com/jeremyevans/sequel/commit/ba3ea1c218d10bbab3f2b48039faedd4859865f8

After copying the Oracle `regex_lookup` method which does exactly this all
tests pass on 8, however the REGEXP_LIKE function does not exist in MySQL
5.6.

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:5>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

My brief investigation led to a similar uncertain conclusion. Making the
method's operation conditional on the MySQL version is okay with me.

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:6>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tom Forbes):

PR: https://github.com/django/django/pull/10065

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:7>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

Looks good. I see one remaining GIS failure:
{{{
======================================================================
FAIL: test_isvalid_lookup (gis_tests.geoapp.tests.GeoLookupTest)
----------------------------------------------------------------------
Traceback (most recent call last):
File "/media/sf_django/django/test/testcases.py", line 1096, in
skip_wrapper
return test_func(*args, **kwargs)
File "/media/sf_django/tests/gis_tests/geoapp/tests.py", line 308, in
test_isvalid_lookup
self.assertEqual(State.objects.filter(name='Kansas',
poly__isvalid=False).count(), 1)
AssertionError: 0 != 1
}}}
If you try to run the GIS tests use:
{{{
'OPTIONS': {
'init_command': 'SET default_storage_engine=MyISAM',
},
}}}
in your settings and use `runtests.py gis_tests`, otherwise the test suite
takes quite a long time.

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:8>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"dae75481fabbe775160893a2e7cb2b2d73705a8f" dae75481]:
{{{
#!CommitTicketReference repository=""
revision="dae75481fabbe775160893a2e7cb2b2d73705a8f"
[2.1.x] Refs #29451 -- Fixed invalid SQL on MySQL in queries tests.

Backport of c4f099de1d0d82c5e1f88768300896eae69bddbd from master
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:9>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"17017ba12a183d491c983ca1ff9c3889619676fe" 17017ba1]:
{{{
#!CommitTicketReference repository=""
revision="17017ba12a183d491c983ca1ff9c3889619676fe"
[2.1.x] Refs #29451 -- Fixed regex/iregex lookups on MySQL 8.

Backport of 42490768441701bc02255b22df8e6894cbe487c7 from master
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:10>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"42490768441701bc02255b22df8e6894cbe487c7" 4249076]:
{{{
#!CommitTicketReference repository=""
revision="42490768441701bc02255b22df8e6894cbe487c7"

Refs #29451 -- Fixed regex/iregex lookups on MySQL 8.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:11>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"f8a6488839ac3666412bd3b5573cf7eae78fbcca" f8a64888]:
{{{
#!CommitTicketReference repository=""
revision="f8a6488839ac3666412bd3b5573cf7eae78fbcca"
[2.0.x] Refs #29451 -- Fixed regex/iregex lookups on MySQL 8.

Backport of 42490768441701bc02255b22df8e6894cbe487c7 from master
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:13>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"243f07bbb3e65c44500451a78251d062301fe25d" 243f07b]:
{{{
#!CommitTicketReference repository=""
revision="243f07bbb3e65c44500451a78251d062301fe25d"
[2.0.x] Refs #29451 -- Fixed invalid SQL on MySQL in queries tests.

Backport of c4f099de1d0d82c5e1f88768300896eae69bddbd from master
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:12>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tom Forbes):

Regarding the `test_isvalid_lookup` failure, thankfully it seems that it's
not really a failure. A comment in the test says:

{{{
# Kansas has adjacent vertices with distance 6.99244813842e-12
# which is smaller than the default Oracle tolerance.
# It's invalid on MySQL too.
}}}

This doesn't seem to be the case anymore. Skipping that branch, where the
failure originates, makes the test pass just fine. I'll prepare a PR now.

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:14>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tom Forbes):

https://github.com/django/django/pull/10134

I get two other failures locally, but I assume they are to do with my
environment:

{{{
======================================================================
FAIL: test_raster_transform
(gis_tests.gdal_tests.test_raster.GDALRasterTests)

----------------------------------------------------------------------
Traceback (most recent call last):
File

"/Users/tom/PycharmProjects/django/tests/gis_tests/gdal_tests/test_raster.py",
line 517, in test_raster_transform
self.assertAlmostEqual(target.origin[0], 9124842.791079799)
AssertionError: 9124842.791069634 != 9124842.791079799 within 7 places

----------------------------------------------------------------------

======================================================================
FAIL: test_transform_3d (gis_tests.geos_tests.test_geos.GEOSTest)

----------------------------------------------------------------------
Traceback (most recent call last):
File

"/Users/tom/PycharmProjects/django/tests/gis_tests/geos_tests/test_geos.py",
line 1121, in test_transform_3d
self.assertEqual(p3d.z, 100)
AssertionError: 100.00001595821232 != 100

----------------------------------------------------------------------
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:15>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham):

Yes, those failures look unrelated to the database engine. Probably
related to your GDAL and GEOS versions.

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:16>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"f98e1c01eafa724cb87772ae03b3fd1158e9fd50" f98e1c01]:
{{{
#!CommitTicketReference repository=""
revision="f98e1c01eafa724cb87772ae03b3fd1158e9fd50"
Refs #29451 -- Fixed test_isvalid_lookup on MySQL 8+.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:17>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"c7ac2ee77309d2fa5a08960a81d65fde1a06834c" c7ac2ee7]:
{{{
#!CommitTicketReference repository=""
revision="c7ac2ee77309d2fa5a08960a81d65fde1a06834c"
[2.1.x] Refs #29451 -- Fixed test_isvalid_lookup on MySQL 8+.

Backport of f98e1c01eafa724cb87772ae03b3fd1158e9fd50 from master
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:18>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody
Type: | Status: new
Cleanup/optimization |

Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Tim Graham <timograham@…>):

In [changeset:"50e4f9adac2eb14e87570fa8c326fac5d4e0dad5" 50e4f9ad]:
{{{
#!CommitTicketReference repository=""
revision="50e4f9adac2eb14e87570fa8c326fac5d4e0dad5"
[2.0.x] Refs #29451 -- Fixed test_isvalid_lookup on MySQL 8+.

Backport of f98e1c01eafa724cb87772ae03b3fd1158e9fd50 from master
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:19>

[Django]

#29451: Add compatibility with MySQL 8
-------------------------------------+-------------------------------------

Reporter: Tim Graham | Owner: nobody

Type: | Status: closed

Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Tim Graham):

* status: new => closed
* resolution: => fixed

--
Ticket URL: <https://code.djangoproject.com/ticket/29451#comment:20>