WordPress.org

Make WordPress Core

Opened 10 months ago

Last modified 8 months ago

#44384 new defect (bug)

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

Reported by: ottok Owned by:
Milestone: Future Release Priority: normal
Severity: normal Version: 5.1
Component: Database Keywords: needs-patch
Focuses: Cc:

Description

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 https://core.trac.wordpress.org/ticket/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.

Change History (3)

#1 @jeremyfelt
9 months ago

  • Keywords needs-patch added
  • Milestone changed from Awaiting Review to Future Release

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.

Last edited 9 months ago by jeremyfelt (previous) (diff)

#2 @ottok
9 months ago

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.

#3 @ottok
8 months ago

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

Note: See TracTickets for help on using tickets.