[wp-trac] [WordPress Trac] #44384: InnoDB doesn't support SPATIAL indexes for query CREATE TEMPORARY TABLE

[WordPress Trac]

#44384: InnoDB doesn't support SPATIAL indexes for query CREATE TEMPORARY TABLE
--------------------------+-----------------------------
 Reporter:  ottok         |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Future Release
Component:  Database      |     Version:  5.1
 Severity:  normal        |  Resolution:
 Keywords:  needs-patch   |     Focuses:
--------------------------+-----------------------------
Description changed by SergeyBiryukov:

Old 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'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.

New 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'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.

--

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/44384#comment:4>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform