WordPress.org

Make WordPress Core

Opened 3 years ago

Last modified 13 days ago

#41335 new enhancement

dbDelta() does not recognize MySQL's IF NOT EXISTS statement

Reported by: kmaxim Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version:
Component: Upgrade/Install Keywords: has-patch reporter-feedback
Focuses: Cc:
PR Number:

Description

When a new table is creating with IF NOT EXISTS MySQL statement in query then array $cqueries(creational queries) will have invalid table names as keys.

The keys of the array use below in the function's code for fetch the table column structure in DESCRIBE statement.

Example query: CREATE TABLE wp_delta_table...
The extracted table name: wp_delta_table

Example query: CREATE TABLE IF NOT EXISTS wp_delta_table...
The extracted table name: IF

Attachments (2)

update.php (25.3 KB) - added by kmaxim 3 years ago.
Added IF NOT EXISTS statement when tablename index is created from a query
upgrade.diff (1.2 KB) - added by kmaxim 3 years ago.

Download all attachments as: .zip

Change History (6)

@kmaxim
3 years ago

Added IF NOT EXISTS statement when tablename index is created from a query

#1 @kmaxim
3 years ago

  • Keywords has-patch added

@kmaxim
3 years ago

#2 @pento
3 years ago

  • Keywords reporter-feedback added
  • Type changed from defect (bug) to enhancement
  • Version 4.8 deleted

Thank you for the feature request, @kmaxim!

Do you have a particular use case for this? dbDelta() generally doesn't need to support IF NOT EXISTS, because the function checks to see if the table exists, then decides whether it needs to create the table, or modify it to match the CREATE TABLE statement passed to it.

If you pass dbDelta() the same CREATE TABLE statement twice, it will do nothing the second time, as the table exists, and there are no changes to be made.

#3 @SergeyBiryukov
2 weeks ago

#49202 was marked as a duplicate.

#4 @krut1
13 days ago

@pento But the logik is broken. You try to get the name of the table here:

<?php
// Create a tablename index for an array ($cqueries) of queries
foreach ( $queries as $qry ) {
        if ( preg_match( '|CREATE TABLE ([^ ]*)|', $qry, $matches ) ) {
                $cqueries[ trim( $matches[1], '`' ) ] = $qry;
                $for_update[ $matches[1] ]            = 'Created table ' . $matches[1];
        } elseif ( preg_match( '|CREATE DATABASE ([^ ]*)|', $qry, $matches ) ) {
                array_unshift( $cqueries, $qry );
        } elseif ( preg_match( '|INSERT INTO ([^ ]*)|', $qry, $matches ) ) {
                $iqueries[] = $qry;
        } elseif ( preg_match( '|UPDATE ([^ ]*)|', $qry, $matches ) ) {
                $iqueries[] = $qry;
        } else {
                // Unrecognized query type
        }
}

But regex pattern schould be:

<?php
if ( preg_match( '|CREATE TABLE( IF NOT EXISTS)? ([^ ]*)|', $qry, $matches ) ) {
        $cqueries[ trim( $matches[2], '`' ) ] = $qry;
        $for_update[ $matches[2] ]            = 'Created table ' . $matches[2];
}

In this case you will have the table's name. In current version of code you have IF instead of table's name.

I wrote about it already here: #49202

Last edited 13 days ago by SergeyBiryukov (previous) (diff)
Note: See TracTickets for help on using tickets.