dbDelta/SQL error, very unexpected

2 Views

Here’s an error that I’m having trouble with. It occurs when I use the dbDelta function in WordPress. I can’t really figure it out, perhaps someone else has experience something similar? My code: 

$sql1 = "CREATE TABLE `".$table_name1."` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT  PRIMARY KEY ,
`blogid` INT( 11 ) NOT NULL ,
`symbol` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM";

I trigger this code during the theme_switch action. And it does not seem to occur when I turn the theme on, only when I switch to something else (I know, it’s weird). I have read and I have attempted to follow the caveats mentioned at WordPress.org, for example putting two spaces between primary key and the definition. I have two other, similar, pieces of SQL code that generate the same error; all three were originally generated by phpMyAdmin so I know that they work.

Read More
Notice: Undefined offset: 1 in /Applications/MAMP/htdocs/wp-experiment/wp-admin/includes/upgrade.php on line 1463

Notice: Undefined offset: 1 in /Applications/MAMP/htdocs/wp-experiment/wp-admin/includes/upgrade.php on line 1463

Notice: Undefined offset: 1 in /Applications/MAMP/htdocs/wp-experiment/wp-admin/includes/upgrade.php on line 1463

WordPress database error: [Multiple primary key defined]
ALTER TABLE wp_stocks_0 CHANGE COLUMN id `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY

Warning: Cannot modify header information - headers already sent by (output started at /Applications/MAMP/htdocs/wp-experiment/wp-admin/includes/upgrade.php:1463) in /Applications/MAMP/htdocs/wp-experiment/wp-includes/pluggable.php on line 897

In case I have misunderstood dbDelta, I should perhaps add, that the above code is executed several times.. so the table already exists. But if I understand dbDelta, that means it simply ought to do nothing because there is nothing in the db structure that needs changing.

Post Views: 2

Related posts

Leave a Reply

3 comments

  2. My problem was that I had an extra space between ‘create table’ and the table name.

    I had:

    $sqlOptions = "CREATE TABLE  $options_table_name (

    Where I needed:

    $sqlOptions = "CREATE TABLE $options_table_name (

    Picky function

  3. Try —

    $sql1 = "CREATE TABLE `".$table_name1."` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`blogid` int(11) NOT NULL,
`symbol` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MYISAM";

    In my experience, dbDelta is even fussier than alluded to in the Codex.

    Update
    From your edit, I would suggest you first check / only create the database if it doesn’t exist yet, which you can do using —

    global $wpdb;

if ($wpdb->get_var("show tables like `" . $wpdb->prefix . $table_name1 . "`") != $wpdb->prefix . $table_name1) {
  // your dbDelta query here
}