dbdelta creat table inconsistent behavior

  • Resolved sk8ermeb

    (@sk8ermeb)


    10 years, 2 months ago

    I have been getting really inconsistent behavior with the use of dbdelta recently (likely since 4.4.2 upgrade). The following (seemingly a bug) has been driving me crazy.

    $table_name = $wpdb->prefix . "rbelegvote";
      $sql = "CREATE TABLE " . $table_name . " (
      id mediumint(9) NOT NULL AUTO_INCREMENT,
      email varchar(120) NOT NULL,
      twitid varchar(120) NOT NULL,
      PRIMARY KEY  (id),
      KEY (email)
      );";
      require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
      dbDelta($sql);

    works, however putting an underscore in the table name so it becomes:

    $table_name = $wpdb->prefix . "rbe_legvote";
      $sql = "CREATE TABLE " . $table_name . " (
      id mediumint(9) NOT NULL AUTO_INCREMENT,
      email varchar(120) NOT NULL,
      twitid varchar(120) NOT NULL,
      PRIMARY KEY  (id),
      KEY (email)
      );";
      require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
      dbDelta($sql);

    does not work. I have tried this like 10 times to make sure I wasn’t doing something stupid. Can anyone out there tell my why the first one would work and the second one wouldn’t? The second one used to work and no longer does, and I think I obeyed all the strange spacing requirements for db delta. I am suspecting this has to do with the wordpress update but not sure. Thank you in advance

Viewing 5 replies - 1 through 5 (of 5 total)
  • Moderator bcworkz

    (@bcworkz)

    10 years, 2 months ago

    Why use dbDelta? Why not $wpdb->query()? No restrictions at all, any valid mySQL query accepted.

    catacaustic

    (@catacaustic)

    10 years, 2 months ago

    Why use dbDelta?

    Because dbDelta() is developed to make changes to any existing tables to match the given schema that’s passed into it. Tis is mostly important for updates/upgrades. Just using $wpdb->query() will break if the table already exists. In a lot of cases this won’t be a big deal, but when you’re developing for a plugin or theme that has any possibility of a database update at all, then dbDelta() is what should be used.

    @sk8ermeb – There’s no reason for a table name with an underscore to fail as it’s a valid table name. The way that I’d suggest testing this is to turn on error reporting (set define('WP_DEBUG', true); in your wp-config.php file) and if it was me I’d also echo out the query string so that I can be sure of what it says. That way you should get any errors that occur, and if you don’t see any you can test the SQL string directly in phpMyAdmin (or whichever database management tool that you use) to see what errors that shows.

    Thread Starter sk8ermeb

    (@sk8ermeb)

    10 years, 2 months ago

    So this was only giving this strange behavior I have on a live server and the error was from their SQL console:
    #1005 - Can't create table 'wordpress_i4bkahld3f.wp_exrz_rbe_legvote' (errno: -1)
    I coudln’t find that sql error number so I opened a support ticket and they responded saying that they were able to re-create the issue and a few hours later they closed it saying they created the table for me. I asked for an explanation for for learning sake but have not heard anything.

    If anyone knows what that sql error number refers to that would be helpful. Thank you

    catacaustic

    (@catacaustic)

    10 years, 2 months ago

    After a very qucik search I’ve found others that have the same issue:

    http://stackoverflow.com/questions/26980657/mysql-cant-create-table-errno-1

    http://stackoverflow.com/questions/15152857/mysql-error-code-1005-errno-1

    From those, it looks like it’s a problem with the InnoDB table type having an issue that’s sort of there, but really not. It’s most likely come about from repeated attempts to create and destroy that table that the InnoDB engine hasn’t quite kept up with as well as it should.

    The “solution” that’s suggested there is to backup the database, dump/delete it, and restore it from the backup. That should get rid of whatever temporary stuff is going on.

    Thread Starter sk8ermeb

    (@sk8ermeb)

    10 years, 2 months ago

    @catacaustic – Thank you very much! I can have at least a little resolution to my confusion! 🙂

Viewing 5 replies - 1 through 5 (of 5 total)

The topic ‘dbdelta creat table inconsistent behavior’ is closed to new replies.