WordPress.org

Make WordPress Core

Opened 5 weeks ago

Last modified 7 days 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: trunk
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 (1)

#1 @jeremyfelt
7 days 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 7 days ago by jeremyfelt (previous) (diff)
Note: See TracTickets for help on using tickets.