MySql bit columns returns weird large number in PHP 7.1 (not in previous versions)

Question

I am testing my PHP site developed with PHP 5.5 to see if it is compatible with PHP 7.1 and I found a very strange problem.

The problem is that doing a simple select on a table with a BIT(1) column returns an 18 digit number instead of a 0 / 1 that return previous PHP versions.

The problematic version is PHP 7.1.9, the other versions I tried that work OK are 5.5.12 and 7.0.23. All of the test were conducted on WAMP 2.5 with Apache 2.4.9 and MySQL 5.6.17.

Here is a minimun set of code to replicate it.

Create table and initial data:

DROP TABLE IF EXISTS `test_bit`;
CREATE TABLE IF NOT EXISTS `test_bit` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `bit_col` BIT(1) NOT NULL DEFAULT FALSE,
  `varchar_col` VARCHAR(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci;

INSERT INTO test_bit (bit_col, varchar_col)
VALUES (1, 'hello'), (0, 'world'), (TRUE, 'how'), (FALSE, 'are'), (NULL, 'you?');

Here is the PHP code:

<?php
$db = mysqli_connect("127.0.0.1", "root", "", "test_db");
$result = mysqli_query($db, "SELECT * FROM test_bit");
$rs = array();
while ($row = mysqli_fetch_assoc($result)) {
    $rs[] = $row;
}
var_dump($rs);
mysqli_free_result($result);

Here are the results in PHP 5.5 and 7.0

array (size=5)
  0 => 
    array (size=3)
      'id' => string '1' (length=1)
      'bit_col' => string '1' (length=1)
      'varchar_col' => string 'hello' (length=5)
  1 => 
    array (size=3)
      'id' => string '2' (length=1)
      'bit_col' => string '0' (length=1)
      'varchar_col' => string 'world' (length=5)
  2 => 
    array (size=3)
      'id' => string '3' (length=1)
      'bit_col' => string '1' (length=1)
      'varchar_col' => string 'how' (length=3)
  3 => 
    array (size=3)
      'id' => string '4' (length=1)
      'bit_col' => string '0' (length=1)
      'varchar_col' => string 'are' (length=3)
  4 => 
    array (size=3)
      'id' => string '5' (length=1)
      'bit_col' => string '0' (length=1)
      'varchar_col' => string 'you?' (length=4)

And here is the result in PHP 7.1

array (size=5)
  0 => 
    array (size=3)
      'id' => string '1' (length=1)
      'bit_col' => string '326352660489830401' (length=18)
      'varchar_col' => string 'hello' (length=5)
  1 => 
    array (size=3)
      'id' => string '2' (length=1)
      'bit_col' => string '326352866648260608' (length=18)
      'varchar_col' => string 'world' (length=5)
  2 => 
    array (size=3)
      'id' => string '3' (length=1)
      'bit_col' => string '326353072806690817' (length=18)
      'varchar_col' => string 'how' (length=3)
  3 => 
    array (size=3)
      'id' => string '4' (length=1)
      'bit_col' => string '326353278965121024' (length=18)
      'varchar_col' => string 'are' (length=3)
  4 => 
    array (size=3)
      'id' => string '5' (length=1)
      'bit_col' => string '326353485123551232' (length=18)
      'varchar_col' => string 'you?' (length=4)

The numbers in the 'bit_col' in PHP 7.1 change the first one or two times I set PHP 7.1 as my server version and then stay the same (until I change to 7.0 or 5.5 and then come back to 7.1 again). They seem like a timestamp or something of the sort.

Any help would be very appreciated.

Answer 1

It is in fact an already reported issue in PHP since August the 1st.

PHP Bug #75018

It seems to be only for the Windows Platform as some comments have suggested.

Answer 2

Today I faced with the same issue on armv7.

According to bug #75018 and related commit I've fixed mysqlnd_wireprotocol.c, recompiled PHP and now it works as expected.

So solution is:

• wait for new PHP version (with fix)
• compile latest PHP from sources applying required fix