WordPress.org

Forums

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

  1. rbreuss
    Member
    Posted 2 years 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
    very awesome
    Posted 2 years 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.

Topic Closed

This topic has been closed to new replies.

About this Topic