WordPress.org

Ready to get started?Download WordPress

Forums

$wpdb->update has 32 bit limit ? (2 posts)

  1. rbreuss
    Member
    Posted 11 months ago #

    HI,
    I had trouble with my wp-database when updating values using
    $wpdb->update. It seems that there is a limit for 32 bit values somewhere, since it works without problems when I use $wp->insert.

    So I wrote a small test:
    Create a table, insert a big value, retrieve it again (this works), update the value and retrieve it again (result is always -1 for positive values, or -2147483648 for negative values).

    <?php
    global $wpdb;
    $table_name = $wpdb->prefix . "_test_db";
    
    $sql = "CREATE TABLE " . $table_name . " (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    value bigint(20) NOT NULL,
    PRIMARY KEY id (id)
    ) CHARSET utf8 ENGINE=INNODB;";
    
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);
    
    $value = 5000000000;
    
    $n_rows = $wpdb->insert( $table_name, array('value' => $value));
    $id = $wpdb->insert_id;
    error_log($n_rows . " row inserted, id=[" . $id . "] value=[" . $value . "]");
    // 1 row inserted, id=[1] value=[5000000000]
    
    $row = $wpdb->get_row( "SELECT * FROM " . $table_name, ARRAY_A);
    error_log("read from DB: id=[" . $row["id"] . "] value=[" . $row["value"] . "]");
    // read from DB: id=[1] value=[5000000000]
    
    $n_rows = $wpdb->update( $table_name,
    		array('value' => $value),
    		array('id' => $id),
    		array('%d'),
    		array( '%d' )
    );
    error_log($n_rows . " row updated, id=[" . $id . "] value=[" . $value . "]");
    // 1 row updated, id=[1] value=[5000000000]
    
    $row = $wpdb->get_row( "SELECT * FROM " . $table_name, ARRAY_A);
    error_log("read from DB: id=[" . $row["id"] . "] value=[" . $row["value"] . "]");
    // read from DB: id=[1] value=[-1]
    
    $wpdb->query("DROP TABLE " . $table_name);
    
    ?>
  2. catacaustic
    Member
    Posted 11 months ago #

    You've got a small problem with your logic for this, and that's what's causing the issue, not the value itself.

    When you insert, the $wpdb->insert() function reads the numeric value as a string and inserts it as a string value, not an integer value. That's why you can retrieve the correct value first off - but it's a string value not an integer value.

    When you update using this:

    $n_rows = $wpdb->update( $table_name,
    		array('value' => $value),
    		array('id' => $id),
    		array('%d'),
    		array( '%d' )
    );

    You are giving that an integer value, and the maximum integer value on a 32-bit system is less then your value, so the system forces the value to be it's max.

    If you change the %d to %s for the value it will save it as a string the way that it does in the initial insert and you'll be able to return the correct value.

Reply

You must log in to post.

About this Topic