Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

bigint truncated via PDO?

I came across a problem with storing a large integer in a BIGINT column on MySQL via PDO

If i run this test:

$number = "30123456789";
var_dump($number); //prints string(11) "30123456789"

$new_number = (int)$number;
var_dump($new_number); //prints int(30123456789) 

So far so good...

If I run this SQL directly in MySQL:

update my_table set bigint_field = 30123456789 where id_field = 1

Everything works fine...

The problem arise when I try to save that number via PDO and I reduced the problem to this line of code:

//parameterized query
//update my_table set bigint_field = :bigint_field where id_field = :id_field
$statement->bindValue(":bigint_field", $new_number, PDO::PARAM_INT);

If the optional third type parameter is absent or equals PDO::PARAM_STR then the value is saved jut fine, if not the value is truncated to 58685709. If I try to save 20288976024, the value is truncated to 0. What is happening here

I'm running PHP 5.5.33 and MySQL 5.6.25 on Debian Wheezy x64

like image 692
Matías Cánepa Avatar asked Mar 22 '16 13:03

Matías Cánepa


1 Answers

I cannot reproduce your case.

On a x86 system intval() already makes 2147483647 On a 64 bit system everything works fine.

Binding bigint values as strings may lead to wrong results, as value will be cast to a float and lose precision.

Edit: it turned out to be an old libmysql issue. Having PHP configured this way I was able to reproduce the problem:

$number     = 30123456789;
$new_number = 20288976024;
var_dump($number, $new_number);

$pdo->query("CREATE TEMPORARY TABLE bint_test(i BIGINT unsigned)");

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

$stmt = $pdo->prepare("insert into bint_test values (?)");
$stmt->bindValue(1, $number, PDO::PARAM_INT);
$stmt->execute();
$stmt->bindValue(1, $new_number, PDO::PARAM_INT);
$stmt->execute();

echo json_encode($pdo->query("SELECT * FROM bint_test")->fetchAll());

prints out

int(30123456789)
int(20288976024)
[{"i":"58685717"},{"i":"0"}

Two possible solutions:

  1. (Preferred) Install php-mysqlnd, which you have to do anyway, because mysqlnd is an new replacement for the old libmysql connector.
  2. Turn emulation mode ON - a query that is constructed by PDO is also works flawless.
like image 190
Your Common Sense Avatar answered Oct 15 '22 05:10

Your Common Sense