Bug #85946 MySQL 8.0.1 breaks php compatibilty
Submitted: 14 Apr 2017 8:25 Modified: 18 Apr 2017 12:36
Reporter: Oli Sennhauser Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S2 (Serious)
Version:8.0.1 OS:Linux
Assigned to: CPU Architecture:Any
Tags: mysqli, php

[14 Apr 2017 8:25] Oli Sennhauser
Description:
Running some tests of our PHP CLI application against MySQL 8.0 gives us the following error:

PHP Warning:  mysqli::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers in /home/mysql/product/fromdual_brman-rev662/lib/fromdual_bman.inc on line 541
PHP Warning:  mysqli::__construct(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers in /home/mysql/product/fromdual_brman-rev662/lib/fromdual_bman.inc on line 541

How to repeat:
$mysqli = new mysqli($aConnection['host'], $aConnection['user'], $aConnection['password'], null, $aConnection['port'], null);
if ( $mysqli->connect_error ) {
  $rc = 175;
  $msg = $mysqli->connect_error . ' (' . $mysqli->connect_errno . ')' . " (rc=$rc).";
  tee('  ERROR: ' . $msg);
  return array($rc, null);
}

ii  libapache2-mod-php                              1:7.0+35ubuntu6                            all          server-side, HTML-embedded scripting language (Apache 2 module) (default)
ii  libapache2-mod-php7.0                           7.0.15-0ubuntu0.16.04.4                    amd64        server-side, HTML-embedded scripting language (Apache 2 module)
ii  php-common                                      1:35ubuntu6                                all          Common files for PHP packages
ii  php-mysql                                       1:7.0+35ubuntu6                            all          MySQL module for PHP [default]
ii  php7.0-cli                                      7.0.15-0ubuntu0.16.04.4                    amd64        command-line interpreter for the PHP scripting language
ii  php7.0-common                                   7.0.15-0ubuntu0.16.04.4                    amd64        documentation, examples and common module for PHP
ii  php7.0-json                                     7.0.15-0ubuntu0.16.04.4                    amd64        JSON module for PHP
ii  php7.0-mysql                                    7.0.15-0ubuntu0.16.04.4                    amd64        MySQL module for PHP
ii  php7.0-opcache                                  7.0.15-0ubuntu0.16.04.4                    amd64        Zend OpCache module for PHP
ii  php7.0-readline                                 7.0.15-0ubuntu0.16.04.4                    amd64        readline module for PHP

NAME="Ubuntu"
VERSION="16.04.2 LTS (Xenial Xerus)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 16.04.2 LTS"
VERSION_ID="16.04"
HOME_URL="http://www.ubuntu.com/"
SUPPORT_URL="http://help.ubuntu.com/"
BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"
VERSION_CODENAME=xenial
UBUNTU_CODENAME=xenial

Suggested fix:
Do not break the protocols.

Works perfectly against MySQL 5.7 and MariaDB 10.2
[16 Apr 2017 6:39] Shane Bester
oli, they changed the default charset now.   so you just have to put into my.cnf

[mysqld]
character-set-server=utf8
[17 Apr 2017 9:43] Umesh Shastry
Hello Oli,

Thank you for the report.
Could you please confirm if you are still seeing the issue even after following Shane's suggestion?

Thanks,
Umesh
[17 Apr 2017 16:24] Bill Karwin
It seems like many clients will need to update to deal with this.

I submitted a PHP bug report:

https://bugs.php.net/bug.php?id=74461
[18 Apr 2017 6:29] Oli Sennhauser
Hello Umesh

Same issue:

oli@chef:/tmp [mysql-8.0.1, 3326]> ./insert_test.php 
PHP Warning:  mysqli::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers in /tmp/insert_test.php on line 13
PHP Warning:  mysqli::__construct(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers in /tmp/insert_test.php on line 13
ERROR: Connect failed: (2054) Server sent charset unknown to the client. Please, report to the developers

root@mysql-8.0.1 [(none)] SQL> set global character_set_server='utf8';
Query OK, 0 rows affected (0.00 sec)

root@mysql-8.0.1 [(none)] SQL> show global variables like 'character_set_server';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_server | utf8  |
+----------------------+-------+
1 row in set (0.00 sec)

oli@chef:/tmp [mysql-8.0.1, 3326]> ./insert_test.php 
PHP Warning:  mysqli::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers in /tmp/insert_test.php on line 13
PHP Warning:  mysqli::__construct(): (HY000/2054): Server sent charset unknown to the client. Please, report to the developers in /tmp/insert_test.php on line 13
ERROR: Connect failed: (2054) Server sent charset unknown to the client. Please, report to the developers

By the way, this still worked in 8.0.0 and was broken in 8.0.1.

Regards,
Oli
[18 Apr 2017 7:40] Shane Bester
Oli, you really have to start the mysqld with the setting in my.cnf

[mysqld]
character-set-server=utf8

I am not sure why, to be honest.
[18 Apr 2017 7:48] Oli Sennhauser
Hello Shane

OK. Then it works...

root@localhost [(none)]> status;
--------------
mysql  Ver 8.0.1-dmr for linux-glibc2.12 on x86_64 (MySQL Community Server (GPL))

Connection id:          5
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.1-dmr-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/run/mysqld/mysql-3326.sock
Uptime:                 10 sec

root@localhost [(none)]> show global variables like 'character_set_server';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_server | utf8  |
+----------------------+-------+
1 row in set (0.00 sec)

root@localhost [(none)]> show global status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 56    |
+---------------+-------+
1 row in set (0.01 sec)

mysql@chef:~/data/mysql-8.0 [mysql-8.0, 3326]> /tmp/insert_test.php 
connected
object(mysqli)#1 (19) {
  ["affected_rows"]=>
  int(0)
  ["client_info"]=>
  string(79) "mysqlnd 5.0.12-dev - 20150407 - $Id: b5c5906d452ec590732a93b051f3827e02749b83 $"
  ["client_version"]=>
  int(50012)
  ["connect_errno"]=>
  int(0)
  ["connect_error"]=>
  NULL
  ["errno"]=>
  int(0)
  ["error"]=>
  string(0) ""
  ["error_list"]=>
  array(0) {
  }
  ["field_count"]=>
  int(0)
  ["host_info"]=>
  string(20) "127.0.0.1 via TCP/IP"
  ["info"]=>
  NULL
  ["insert_id"]=>
  int(0)
  ["server_info"]=>
  string(13) "8.0.1-dmr-log"
  ["server_version"]=>
  int(80001)
  ["stat"]=>
  string(131) "Uptime: 90  Threads: 1  Questions: 13  Slow queries: 0  Opens: 176  Flush tables: 2  Open tables: 50  Queries per second avg: 0.144"
  ["sqlstate"]=>
  string(5) "00000"
  ["protocol_version"]=>
  int(10)
  ["thread_id"]=>
  int(6)
  ["warning_count"]=>
  int(0)
}

But this would lead to an other bug report?
SET GLOBAL character_set_server = 'utf8'; does not behave as it should???

Regards,
Oli
[18 Apr 2017 12:36] Umesh Shastry
Thank you Oli for confirming that now it works per Shane's suggestions. 
For now I would mark this report as !bg as I see Bill K has opened bug @php which seems to be fixed as per updates in https://bugs.php.net/bug.php?id=74461

regards,
Umesh
[11 Oct 2018 8:43] R Snijders
Hello,
I have this problem with Matomo-platform.
So what is the practical/concrete solution?
Where should I change what: in Matomo? in MySQL? in PHP?
Friendly regards,
Rolf
[17 Jan 12:49] Umesh Shastry
Bug #93958 marked as duplicate of this one