8

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.

10
  • That sounds very odd. I can't think of any reason why this would happen, if you run the exact same code, just with different PHP versions. PHP doesn't really care for the type of column (VARCHAR vs BIT for example), so it doesn't know that it returns a bit per se, it just fetches the value in the column - but it shouldn't mangle the value like that. You said they change for every time you re-run this code in PHP 7.1.9? Commented Sep 1, 2017 at 22:08
  • 1
    There's no logical relation between the two numbers, so I honestly have no idea. As I said, I'm quite intrigued, I'll be following this question to see if someone has an answer ;-) I don't have any tools available on this system right now to reproduce the issue either. Commented Sep 1, 2017 at 22:22
  • 1
    I could reproduce the issue on PHP 7.1.8 + MariaDB (Windows 10 + xampp). The numbers change after restarting apache. Commented Sep 1, 2017 at 22:22
  • 1
    Same issue with PDO. Commented Sep 1, 2017 at 22:30
  • 1
    I spun up some test containers for PHP 7.1.[89] and MariaDB 10.2 and did not encounter the issue. I would wager that it's something to do with the Windows builds of one or the other. Commented Sep 2, 2017 at 0:36

2 Answers 2

5

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.

Sign up to request clarification or add additional context in comments.

1 Comment

Good spot. I am going to change my bits to tinyints, best solution I can come up with.
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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.