$wpdb->update has 32 bit limit ?

  • 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);

?>
Viewing 1 replies (of 1 total)

  • 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.

Viewing 1 replies (of 1 total)
  • The topic ‘$wpdb->update has 32 bit limit ?’ is closed to new replies.

Tags