Table not updating with dbdelta

  • I have following code for creating table:

    $sql = "CREATE TABLE " . $table_name . "("
         . "id mediumint(9) NOT NULL AUTO_INCREMENT, "
         . "product_id mediumint(9) DEFAULT 0 NOT NULL,"
         . "name_type mediumint(9) NOT NULL, "
         . "name_key varchar(48) NOT NULL, "
         . "valid_until datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, "
         . "created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, "
         . "updated_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, "
         . "UNIQUE KEY id (id)"
         . ")" . $charset_collate. ";";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );

    It is working and table created.
    Now I need update table and add new column (sites_list):

    $sql = "CREATE TABLE " . $table_name . "("
         . "id mediumint(9) NOT NULL AUTO_INCREMENT, "
         . "product_id mediumint(9) DEFAULT 0 NOT NULL,"
         . "name_type mediumint(9) NOT NULL, "
         . "name_key varchar(48) NOT NULL, "
         . "sites_list longtext, "
         . "valid_until datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, "
         . "created_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, "
         . "updated_at datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, "
         . "UNIQUE KEY id (id)"
         . ")" . $charset_collate. ";";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );

    But this not work.
    And I has a error:
    WordPress database error: [Table 'table_name' already exists]

    What is incorrect?
    Thanks.

Viewing 1 replies (of 1 total)

  • Here’s what I have found works well.

    
      global $wpdb;
      // $charset_collate = $wpdb->get_charset_collate(); //This doesn't work in a lot of cases I've found
      $charset_collate = "";
      $collation = $wpdb->get_row("SHOW FULL COLUMNS FROM {$wpdb->posts} WHERE field = 'post_content'");

      if(isset($collation->Collation)) {
        $charset = explode('_', $collation->Collation);

        if(is_array($charset) && count($charset) > 1) {
          $charset = $charset[0]; //Get the charset from the collation
          $charset_collate = "DEFAULT CHARACTER SET {$charset} COLLATE {$collation->Collation}";
        }
      }

      if(empty($charset_collate)) {$charset_collate = $wpdb->get_charset_collate(); } //Okay fine, we'll try it your way

      $jobs =
        "CREATE TABLE {$this->jobs} (
          id bigint(20) NOT NULL auto_increment,
          runtime datetime NOT NULL,
          firstrun datetime NOT NULL,
          lastrun datetime DEFAULT NULL,
          priority bigint(20) DEFAULT 10,
          tries bigint(20) DEFAULT 0,
          class varchar(255) NOT NULL,
          batch varchar(255) DEFAULT NULL,
          args text DEFAULT '',
          reason text DEFAULT '',
          status varchar(255) DEFAULT 'pending',
          created_at datetime NOT NULL,
          PRIMARY KEY  (id),
          KEY job_runtime (runtime),
          KEY job_firstrun (firstrun),
          KEY job_lastrun (lastrun),
          KEY job_status (status),
          KEY job_priority (priority),
          KEY job_tries (tries),
          KEY job_class (class),
          KEY job_batch (batch),
          KEY job_created_at (created_at)
        ) {$charset_collate};";

      dbDelta($jobs);
    • This reply was modified 8 years, 3 months ago by cartpauj. Reason: Fixed broken code block
Viewing 1 replies (of 1 total)
  • The topic ‘Table not updating with dbdelta’ is closed to new replies.