InnoDB doesn't support SPATIAL indexes for query CREATE TEMPORARY TABLE

[ottok]

Running the WordPress PHPUnit test suite failed in my environment with the error message:

WordPress database error The storage engine InnoDB doesn't support SPATIAL indexes for query CREATE TEMPORARY TABLE wptests_spatial_index_test (
				non_spatial bigint(20) unsigned NOT NULL,
				spatial_value geometrycollection NOT NULL,
				KEY non_spatial (non_spatial),
				SPATIAL KEY spatial_key (spatial_value)
			) ENGINE=MyISAM; made by PHPUnit_TextUI_Command::main, PHPUnit_TextUI_Command->run, PHPUnit_TextUI_TestRunner->doRun, PHPUnit_Framework_TestSuite->run, PHPUnit_Framework_TestSuite->run, PHPUnit_Framework_TestCase->run, PHPUnit_Framework_TestResult->run, PHPUnit_Framework_TestCase->runBare, PHPUnit_Framework_TestCase->runTest, ReflectionMethod->invokeArgs, Tests_dbDelta->test_spatial_indices
F<div id="error"><p class="wpdberror"><strong>WordPress database error:</strong> [The storage engine InnoDB doesn&#039;t support SPATIAL indexes]<br /><code>CREATE TEMPORARY TABLE wptests_spatial_index_test (
				non_spatial bigint(20) unsigned NOT NULL,
				spatial_value geometrycollection NOT NULL,
				KEY non_spatial (non_spatial),
				SPATIAL KEY spatial_key (spatial_value)
			) ENGINE=MyISAM;

and

1) Tests_dbDelta::test_spatial_indices
Failed asserting that an array is empty.

This might be related to the fact that I am running MariaDB and not MySQL.

$ mysql --version
mysql  Ver 15.1 Distrib 10.3.7-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

MariaDB docs however state that SPATIAL INDEX should be supported: ​https://mariadb.com/kb/en/library/spatial-index/

It seems the SPATIAL INDEXes where introduced in #36948.

We should first get to the root why this isn't working, and then either fix the code in WordPress or update the WordPress requirements recommendations and state that a particular version or MySQL/MariaDB or InnoDB or setting is required.

[jeremyfelt]

I'm seeing a similar thing in the test_spatial_indices test after installing MySQL 8.0.11 via Homebrew in OSX. When I look at that $updates array, the message returned is Changed type of wptests_spatial_index_test.spatial_value from geomcollection to geometrycollection.

The addition of the now preferred "geomcollection" name is listed as an incompatible change in the 8.0.11 release notes. ​https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html

I'm guessing we may need to do some version detection in this test.

Edit: "Same thing" is wrong on my end. :) Different, but in the same test with likely a similar resolution.

[ottok]

Looks fine here:

+--------------------+
| version()          |
+--------------------+
| 10.3.9-MariaDB-log |
+--------------------+
1 row in set (0.000 sec)

MariaDB [test]> CREATE TEMPORARY TABLE wptests_spatial_index_test (non_spatial bigint(20) unsigned NOT NULL,spatial_value geometrycollection NOT NULL,KEY non_spatial (non_spatial),SPATIAL KEY spatial_key (spatial_value)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> CREATE TEMPORARY TABLE wptests_spatial_index_test2 (non_spatial bigint(20) unsigned NOT NULL,spatial_value geometrycollection NOT NULL,KEY non_spatial (non_spatial),SPATIAL KEY spatial_key (spatial_value)) ENGINE=Innodb;
Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> set global enforce_storage_engine='InnoDB';
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> show global variables like '%engine%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine |        |
| enforce_storage_engine     | InnoDB |
| gtid_pos_auto_engines      |        |
| storage_engine             | InnoDB |
+----------------------------+--------+
5 rows in set (0.002 sec)

MariaDB [test]> CREATE TEMPORARY TABLE wptests_spatial_index_test3 (non_spatial bigint(20) unsigned NOT NULL,spatial_value geometrycollection NOT NULL,KEY non_spatial (non_spatial),SPATIAL KEY spatial_key (spatial_value)) ENGINE=MyISAM;

The only place ER_TABLE_CANT_HANDLE_SPKEYS happens in innodb seems to be in an online ALTER TABLE.

[ottok]

@jeremyfelt Any pointers on what could be done about this?

[SergeyBiryukov]

As noted in comment:1, the failure in test_spatial_indices() (also reported in #49344) is caused by changing the GeometryCollection data type name to GeomCollection in ​MySQL 8.0.11, with the latter being the preferred name.

See comment:3:ticket:49344 and the patch on #49344 for more details.

[SergeyBiryukov]

Just noting that I could not reproduce the "storage engine InnoDB doesn't support SPATIAL indexes for query" error from the ticket description with MariaDB 10.4.12.

I can, however, reproduce the issue from comment:1 with MySQL 8.0.16.

[SergeyBiryukov]

In 47184:

Tests: Allow dbDelta() tests to (mostly) run on MySQL 8.0.11+.

• MySQL 8.0.11 changed the GeometryCollection data type name to GeomCollection, with the latter being the preferred name.
• MySQL 8.0.17 removed support for the display width attribute for integer data types. Previously, default display width of 20 digits was used: BIGINT(20).

The affected tests now check the MySQL server version and use the appropriate data types.

This leaves one unresolved failure on MySQL 8.0.17+ to be addressed in the future, caused by the same BIGINT display width discrepancy coming from wp_get_db_schema().

Props kaggdesign, ottok, jeremyfelt, SergeyBiryukov.
Fixes #44384, #49344. See #49364.