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

[kmaxim]

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

[kmaxim]

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

[pento]

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.

[SergeyBiryukov]

#49202 was marked as a duplicate.

[krut1]

@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

[SergeyBiryukov]

#56877 was marked as a duplicate.