WordPress.org

Make WordPress Core

Opened 17 months ago

Closed 17 months ago

Last modified 14 months ago

#36948 closed enhancement (fixed)

dbDelta doesn't support SPATIAL KEY syntax

Reported by: stuporglue Owned by: pento
Milestone: 4.6 Priority: normal
Severity: normal Version:
Component: Database Keywords:
Focuses: Cc:

Description

dbDelta can create spatial indexes on the initial CREATE TABLE call, but subsequent calls to dbDelta don't recognize the existing spatial index, resulting in a MySQL error indicating that it's trying add a new column.

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SPATIAL KEY (meta_value)' at line 1
ALTER TABLE wp_index_test ADD COLUMN SPATIAL KEY (meta_value)

MySQL has supported spatial columns and indexes since version 5.4. The syntax has remained the same since 5.4.

Adding spatial index support to dbDelta would allow developers to create and use tables with spatial columns while continuing to use dbDelta.

SPATIAL index syntax is the same as FULLTEXT support which dbDelta already supports. As such, adding support is a simple one line change.

Note: dbDelta works correctly for creating spatial columns. It's just indexes that it chokes on.

Attachments (3)

dbdelta_spatial_index_test.php (520 bytes) - added by stuporglue 17 months ago.
Test file
dbdelta_spatial_index_test_corrected.php (531 bytes) - added by stuporglue 17 months ago.
The previous test file did not give the spatial index a name which caused it to get re-added each time dbDelta ran.
spatial_index_patch.patch (721 bytes) - added by stuporglue 17 months ago.
This patch allows spatial indexes to be added with dbDelta

Download all attachments as: .zip

Change History (10)

#1 @stuporglue
17 months ago

My comment about it being a one line fix was wrong. Adding the one line gets rid of the error, but it adds an additional index every time dbDelta is run.

#2 @pento
17 months ago

  • Component changed from Upgrade/Install to Database
  • Milestone changed from Awaiting Review to 4.6
  • Owner set to pento
  • Status changed from new to assigned
  • Version trunk deleted

Thanks for the feature request, @stuporglue!

It's cool to see that you're writing stuff for WordPress involving spatial data, that's always been something I've enjoyed. :-)

This is a very small change to dbDelta(), so I think we can go ahead with it.

#3 @pento
17 months ago

  • Resolution set to fixed
  • Status changed from assigned to closed

In 37574:

Database: Add support for SPATIAL keys to dbDelta().

dbDelta() already supported spatial fields (by virtue of not checking field types), so it's nice to round that out with spatial key support, too.

Fixes #36948.

@stuporglue
17 months ago

The previous test file did not give the spatial index a name which caused it to get re-added each time dbDelta ran.

@stuporglue
17 months ago

This patch allows spatial indexes to be added with dbDelta

#4 @stuporglue
17 months ago

Wow that was fast @pento!

Thanks!

#5 @pento
17 months ago

No problem!

It's also worth noting, in your test script, dbDelta() expects column types to be lowercase, so GEOMETRYCOLLECTION should be geometrycollection, just in case you run into that problem with your code. :-)

#6 @nickciske
14 months ago

So this made it into 4.6, right? I didn't see stuporglue in the props list...? Which is a bummer as its his first ticket/contribution...

#7 @pento
14 months ago

Thanks for noting this, @nickciske!

@stuporglue wasn't automatically added to the props list, as I managed to fix this ticket before he uploaded the patch. You're right that he should've been though, I've updated the props list manually in [meta3826]. (This API is pretty heavily cached, so it may take up to 12 hours before you see @stuporglue appearing in the Credits list.)

Note: See TracTickets for help on using tickets.