Opened 5 months ago

Closed 3 months ago

#29451 closed Cleanup/optimization (fixed)

Add compatibility with MySQL 8

Reported by: Tim Graham Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Adam (Chainz) Johnson Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Attachments (1)

mysql-failures.log (9.2 KB) - added by Tim Graham 5 months ago.

Download all attachments as: .zip

Change History (21)

Changed 5 months ago by Tim Graham

Attachment: mysql-failures.log added

comment:1 Changed 5 months ago by Adam (Chainz) Johnson

Cc: Adam (Chainz) Johnson added

comment:2 Changed 5 months ago 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

comment:3 Changed 5 months ago by Carlton Gibson <carlton.gibson@…>

In a253a580:

Refs #29451 -- Quoted MySQL column names in tests.

comment:4 Changed 4 months ago by GitHub <noreply@…>

In c4f099d:

Refs #29451 -- Used quote_name for column names in tests.

Regression in a253a580e6f20fb7087490225538422eb6cab0bb

comment:5 Changed 4 months ago 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.

comment:6 Changed 4 months ago 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.

comment:8 Changed 4 months ago 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.

comment:9 Changed 4 months ago by Tim Graham <timograham@…>

In dae75481:

[2.1.x] Refs #29451 -- Fixed invalid SQL on MySQL in queries tests.

Backport of c4f099de1d0d82c5e1f88768300896eae69bddbd from master

comment:10 Changed 4 months ago by Tim Graham <timograham@…>

In 17017ba1:

[2.1.x] Refs #29451 -- Fixed regex/iregex lookups on MySQL 8.

Backport of 42490768441701bc02255b22df8e6894cbe487c7 from master

comment:11 Changed 4 months ago by Tim Graham <timograham@…>

In 4249076:

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

comment:12 Changed 4 months ago by Tim Graham <timograham@…>

In 243f07b:

[2.0.x] Refs #29451 -- Fixed invalid SQL on MySQL in queries tests.

Backport of c4f099de1d0d82c5e1f88768300896eae69bddbd from master

comment:13 Changed 4 months ago by Tim Graham <timograham@…>

In f8a64888:

[2.0.x] Refs #29451 -- Fixed regex/iregex lookups on MySQL 8.

Backport of 42490768441701bc02255b22df8e6894cbe487c7 from master

comment:14 Changed 3 months ago 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.

comment:15 Changed 3 months ago 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

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

comment:16 Changed 3 months ago by Tim Graham

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

comment:17 Changed 3 months ago by Tim Graham <timograham@…>

In f98e1c01:

Refs #29451 -- Fixed test_isvalid_lookup on MySQL 8+.

comment:18 Changed 3 months ago by Tim Graham <timograham@…>

In c7ac2ee7:

[2.1.x] Refs #29451 -- Fixed test_isvalid_lookup on MySQL 8+.

Backport of f98e1c01eafa724cb87772ae03b3fd1158e9fd50 from master

comment:19 Changed 3 months ago by Tim Graham <timograham@…>

In 50e4f9ad:

[2.0.x] Refs #29451 -- Fixed test_isvalid_lookup on MySQL 8+.

Backport of f98e1c01eafa724cb87772ae03b3fd1158e9fd50 from master

comment:20 Changed 3 months ago by Tim Graham

Resolution: fixed
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top