Add compatibility with MySQL 8

[Tim Graham]

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.

[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

[Carlton Gibson <carlton.gibson@…>]

In a253a580:

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

[GitHub <noreply@…>]

In c4f099d:

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

Regression in a253a580e6f20fb7087490225538422eb6cab0bb

[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.

[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.

[Tom Forbes]

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

[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.

[Tim Graham <timograham@…>]

In dae75481:

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

Backport of c4f099de1d0d82c5e1f88768300896eae69bddbd from master

[Tim Graham <timograham@…>]

In 17017ba1:

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

Backport of 42490768441701bc02255b22df8e6894cbe487c7 from master

[Tim Graham <timograham@…>]

In 4249076:

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

[Tim Graham <timograham@…>]

In 243f07b:

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

Backport of c4f099de1d0d82c5e1f88768300896eae69bddbd from master

[Tim Graham <timograham@…>]

In f8a64888:

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

Backport of 42490768441701bc02255b22df8e6894cbe487c7 from master

[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.

[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

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

[Tim Graham]

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

[Tim Graham <timograham@…>]

In f98e1c01:

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

[Tim Graham <timograham@…>]

In c7ac2ee7:

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

Backport of f98e1c01eafa724cb87772ae03b3fd1158e9fd50 from master

[Tim Graham <timograham@…>]

In 50e4f9ad:

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

Backport of f98e1c01eafa724cb87772ae03b3fd1158e9fd50 from master