[wp-trac] [WordPress Trac] #46452: dbDelta given the same PRIMARY KEY and normal KEY produces no error, reports table created successfully, but the table is not created

[WordPress Trac]

#46452: dbDelta given the same PRIMARY KEY and normal KEY produces no error,
reports table created successfully, but the table is not created
--------------------------+-----------------------------
 Reporter:  radgh         |      Owner:  (none)
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  5.1
 Severity:  trivial       |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 I spent about 45 minutes trying to find out why one of several dbDelta
 functions was not creating a table. It wasn't giving a warning, and in
 fact was saying "Created table wp_dtl_exam_resultmeta". But the table was
 not created.

 I eventually found the problem which was that I had a key called "meta_id"
 and "answer_id" and simply had put the wrong primary key, which should
 have been meta_id. But I had:

 PRIMARY KEY (answer_id),
 KEY answer_id (answer_id)

 This gives a success message without creating a table - which doesn't seem
 right. Of course this isn't a problem with WordPress, it is a problem with
 my code. But it seems a check is missing from dbDelta, which is what I
 want to report.


 Here is the full code to reproduce the problem, just put it in a plugin or
 functions.php:

 {{{#!php
 <?php

 if ( !defined( 'ABSPATH' ) ) exit;

 /**
  * Creates/updates custom database tables for the plugin:
  *  -> wp_dtl_exam_results
  *  -> wp_dtl_exam_resultmeta
  */

 function example_database_creation_for_wordpress_trac() {
     global $wpdb;
     require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

     $charset_collate = $wpdb->get_charset_collate();

     $table_name = $wpdb->prefix . 'dtl_exam_resultmeta';

     // MY ERROR IS: PRIMARY KEY (answer_id),
     // IT SHOULD BE: PRIMARY KEY (meta_id),
     // But the dbDelta function doesn't notice that, and says it created a
 table when it didn't.

     $sql = <<<MYSQL
 CREATE TABLE $table_name (
   meta_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
   answer_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
   meta_key VARCHAR(255) NULL DEFAULT NULL,
   meta_value LONGTEXT NULL,
   PRIMARY KEY (answer_id),
   KEY answer_id (answer_id),
   KEY meta_key (meta_key)
 ) $charset_collate;
 MYSQL;

     $result = dbDelta( $sql );

     // Debug this:
     echo '<pre>';
     echo 'The table creation SQL:' . "\n\n";
     echo $sql;

     echo "\n -- \n";

     // This should give some warning or at least say table creation
 failed. It doesn't. It says the table was created.
     echo 'dbDelta results: ' . "\n\n";
     if ( $result ) foreach( $result as $m ) echo $m . "\n";
     else echo '(no results from dbDelta)';

     echo "\n -- \n";

     // This should show the table name by checking information_schema, but
 it's an empty array
     echo 'Table query from INFORMATION_SCHEMA, this should show the table
 name: ' . "\n\n";
     $table_exists = $wpdb->get_results( "SELECT TABLE_NAME FROM
 INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '". DB_NAME ."' AND
 TABLE_NAME = '". $table_name ."';" );
     var_dump( $table_exists );
     echo '</pre>';

     exit;
 }

 example_database_creation_for_wordpress_trac();
 }}}

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/46452>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform