dbDelta() fails to create new tables

Patch to fix this issue available at
https://github.com/markmont/wp-db-driver/commit/2ffcb1e7a18763dc51735e98da8cbfb05182f667

wb-db-driver plugin git revision 9febab3815 (head as of 2013-08-03), WordPress 3.6, PHP version 5.5.1, PDO driver, MariaDB version 5.5.31.

When a plugin calls dbDelta() to create a new database table (example code at the end of this post), the following errors get logged to PHP’s error_log, and the database table does not get created:

[03-Aug-2013 13:55:54 UTC] Error executing query: 42S02 - SQLSTATE[42S02]: Base table or view not found: 1146 Table 'wp1.w19a_dbtest_table1' doesn't exist in query DESCRIBE w19a_dbtest_table1;
[03-Aug-2013 13:55:54 UTC] PHP Notice:  Undefined property: stdClass::$Field in /www/w19a.catseye.org/wordpress/wp-admin/includes/upgrade.php on line 1561
[03-Aug-2013 13:55:54 UTC] Error executing query: 42S02 - SQLSTATE[42S02]: Base table or view not found: 1146 Table 'wp1.w19a_dbtest_table1' doesn't exist in query SHOW INDEX FROM w19a_dbtest_table1;
[03-Aug-2013 13:55:54 UTC] WordPress database error Table 'wp1.w19a_dbtest_table1' doesn't exist for query SHOW INDEX FROM w19a_dbtest_table1; made by activate_plugin, do_action('activate_dbtest.php'), call_user_func_array, dbtest_activate, dbDelta, wpdb_drivers->query, wpdb_drivers->print_error
[03-Aug-2013 13:55:54 UTC] PHP Notice:  Undefined property: stdClass::$Key_name in /www/w19a.catseye.org/wordpress/wp-admin/includes/upgrade.php on line 1609
[03-Aug-2013 13:55:54 UTC] PHP Notice:  Undefined property: stdClass::$Column_name in /www/w19a.catseye.org/wordpress/wp-admin/includes/upgrade.php on line 1610
[03-Aug-2013 13:55:54 UTC] PHP Notice:  Undefined property: stdClass::$Sub_part in /www/w19a.catseye.org/wordpress/wp-admin/includes/upgrade.php on line 1610
[03-Aug-2013 13:55:54 UTC] PHP Notice:  Undefined property: stdClass::$Non_unique in /www/w19a.catseye.org/wordpress/wp-admin/includes/upgrade.php on line 1611
[03-Aug-2013 13:55:54 UTC] Error executing query: 42S02 - SQLSTATE[42S02]: Base table or view not found: 1146 Table 'wp1.w19a_dbtest_table1' doesn't exist in query ALTER TABLE w19a_dbtest_table1 ADD COLUMN dbtest_key varchar(100) NOT NULL
[03-Aug-2013 13:55:54 UTC] WordPress database error Table 'wp1.w19a_dbtest_table1' doesn't exist for query ALTER TABLE w19a_dbtest_table1 ADD COLUMN dbtest_key varchar(100) NOT NULL made by activate_plugin, do_action('activate_dbtest.php'), call_user_func_array, dbtest_activate, dbDelta, wpdb_drivers->query, wpdb_drivers->print_error
[03-Aug-2013 13:55:54 UTC] Error executing query: 42S02 - SQLSTATE[42S02]: Base table or view not found: 1146 Table 'wp1.w19a_dbtest_table1' doesn't exist in query ALTER TABLE w19a_dbtest_table1 ADD COLUMN dbtest_value varchar(100) NOT NULL
[03-Aug-2013 13:55:54 UTC] WordPress database error Table 'wp1.w19a_dbtest_table1' doesn't exist for query ALTER TABLE w19a_dbtest_table1 ADD COLUMN dbtest_value varchar(100) NOT NULL made by activate_plugin, do_action('activate_dbtest.php'), call_user_func_array, dbtest_activate, dbDelta, wpdb_drivers->query, wpdb_drivers->print_error
[03-Aug-2013 13:55:54 UTC] Error executing query: 42S02 - SQLSTATE[42S02]: Base table or view not found: 1146 Table 'wp1.w19a_dbtest_table1' doesn't exist in query ALTER TABLE w19a_dbtest_table1 ADD PRIMARY KEY  (dbtest_key)
[03-Aug-2013 13:55:54 UTC] WordPress database error Table 'wp1.w19a_dbtest_table1' doesn't exist for query ALTER TABLE w19a_dbtest_table1 ADD PRIMARY KEY  (dbtest_key) made by activate_plugin, do_action('activate_dbtest.php'), call_user_func_array, dbtest_activate, dbDelta, wpdb_drivers->query, wpdb_drivers->print_error

The first error above is expected and correct; all of the other errors should not happen.

The problem occurs on line 1514 of wp-admin/includes/upgrade.php because the database driver should return false when the table does not exist, but instead returns the last result object. This, in turn, is because line 1531 in wp-includes/wp-db.php (the get_results() method) always returns the instance’s last_result object, regardless of whether the query succeeded or failed. Finally, line 276 of wp-content/plugins/wp-db-driver/db.php (the query() method) returns on error without clearing the instance’s last_result object.

The db driver query() method always invokes the flush() method before executing the query. To fix the problem, I re-added the lines to flush() to clear last_result and other instance variables. This code was originaly in the flush() method, but this code was removed in the patch for Trac ticket #21663 for some reason.

Here is the example code for a plugin reproducing the problem (save this code as wp-content/plugins/dbtest.php). The problem occurs when the plugin is activated.

<?php
/*
Plugin Name: DB Test
Description: Demonstrates PDO DB driver problem
Version: 1.0
Author: Mark Montague
Author URI: http://mark.catseye.org/
License: GPL3
*/

function dbtest_activate() {

    global $wpdb;

    $table_name = $wpdb->prefix . "dbtest_table1";
    $sql = "CREATE TABLE $table_name (
        dbtest_key varchar(100) NOT NULL,
        dbtest_value varchar(100) NOT NULL,
        PRIMARY KEY  (dbtest_key)
        );";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );

http://wordpress.org/plugins/wp-db-driver/