Unable to connect to MariaDB 11.3

Description

Docker GH service:

    services:
      mysql:
        image: mysql:8
        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=5 -e MYSQL_ROOT_PASSWORD=atk4_pass_root -e MYSQL_USER=atk4_test_user -e MYSQL_PASSWORD=atk4_pass -e MYSQL_DATABASE=atk4_test
      mariadb:
        image: mariadb:11.2.3
        options: --health-cmd="mariadb-admin ping" --health-interval=10s --health-timeout=5s --health-retries=5 -e MYSQL_ROOT_PASSWORD=atk4_pass_root -e MYSQL_USER=atk4_test_user -e MYSQL_PASSWORD=atk4_pass -e MYSQL_DATABASE=atk4_test

php -r '(new PDO("mysql:host=mysql", "root", "atk4_pass_root"))->exec("ALTER USER '"'"'atk4_test_user'"'"'@'"'"'%'"'"' WITH MAX_USER_CONNECTIONS 5");'

passes, but with MariaDB:

php -r '(new PDO("mysql:host=mariadb", "root", "atk4_pass_root"))->exec("ALTER USER '"'"'atk4_test_user'"'"'@'"'"'%'"'"' WITH MAX_USER_CONNECTIONS 5");

it fails with the following error:

PDOException: SQLSTATE[HY000] [2054] Server sent charset unknown to the client. Please, report to the developers in Command line code:1
Stack trace:
#0 Command line code(1): PDO->__construct('mysql:host=mari...', 'root', 'atk4_pass_root')
#1 {main}
  thrown in Command line code on line 1

With MariaDB 11.2, the PHP is working perfectly.

I am not sure if this is MariaDB or PHP error, but I would expect at least a better error message.

full repro: #13452 (comment)

PHP Version

any

Operating System

any

At least, it does not reproduce with the master php built on my ubuntu.
Did you use the php distributed with Alpine? Or did you build it yourself on Alpine?

Official PHP base image and mysqli and pdo_mysql installed using https://github.com/mlocati/docker-php-extension-installer.

Can you provide repro code without dependencies?
At least, I was not able to reproduce it using the code you provided.

Run into the same issue and found more context/examples here : MariaDB/mariadb-docker#560

@AirBair

@AirBair
Thank you! The URL you provided means that this problem is on the MariaDB side. (I was initially worried that mysqlnd might be showing spurious errors)

This is not a PHP issue, so I Close it. Thank you!

Here is a full repro: https://github.com/atk4/data/blob/576cc2d2df48ac88ad45c5c3b5b4f8758fab0981/.github/workflows/test-unit.yml:

name: Unit

on:
  push:

jobs:
  unit-test:
    name: Unit
    runs-on: ubuntu-latest
    container:
      image: php:8.3
    services:
      mysql:
        image: mysql:8
        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=5 -e MYSQL_ROOT_PASSWORD=atk4_pass_root -e MYSQL_USER=atk4_test_user -e MYSQL_PASSWORD=atk4_pass -e MYSQL_DATABASE=atk4_test
      mariadb:
        image: mariadb
        options: --health-cmd="mariadb-admin ping" --health-interval=10s --health-timeout=5s --health-retries=5 -e MYSQL_ROOT_PASSWORD=atk4_pass_root -e MYSQL_USER=atk4_test_user -e MYSQL_PASSWORD=atk4_pass -e MYSQL_DATABASE=atk4_test
    steps:
      - name: Show PHP version
        run: |
          php --version

      - name: Install pdo_mysql
        run: |
          curl -sSLf -o /usr/local/bin/install-php-extensions https://github.com/mlocati/docker-php-extension-installer/releases/latest/download/install-php-extensions
          chmod +x /usr/local/bin/install-php-extensions
          install-php-extensions pdo_mysql

      - name: Init MySQL
        run: |
          php -r '(new PDO("mysql:host=mysql", "root", "atk4_pass_root"))->exec("ALTER USER '"'"'atk4_test_user'"'"'@'"'"'%'"'"' WITH MAX_USER_CONNECTIONS 5");'
      - name: Init MariaDB
        run: |
          php -r '(new PDO("mysql:host=mariadb", "root", "atk4_pass_root"))->exec("ALTER USER '"'"'atk4_test_user'"'"'@'"'"'%'"'"' WITH MAX_USER_CONNECTIONS 5");'

I am not sure if the issue should be closed, as any other cli client can connect to MariaDB, so maybe the php mysqli native driver needs some tweaks even if this breakage was introduced from MariaDB server side.

Hi, I'm from the MariaDB Foundation.

This could actually be seen as a bug in mysqlnd because it is using behaviour that went away in MySQL 4.1, 20 years ago.

For reference:

php-src/ext/mysqlnd/mysqlnd_commands.c

Line 619 in 642e111

    
           mnd_sprintf(&msg, 0, "Server sent charset (%d) unknown to the client. Please, report to the developers", greet_packet.charset_no);

The server sends 0 in the server default collation byte of the initial handshake packet for collations that are above 1..255 range.

But actually the connector is mainly interested in the character set, not the collation.

In MySQL-4.0 the client received the collation ID in the initial handshake packet from the server and set its own localization specific variables accordingly, to make things like strcasemp() work like on the server side.

Since MySQL-4.1 (which was released in ~2003) it works the other way around: the client sends its desired character set to the server. This collation ID in the handshake packet does not mean anything useful. It was preserved only for 4.0 client compatibility.

We are planning on working around this on the server side by downgrading the collation ID for legacy behaviour (https://jira.mariadb.org/browse/MDEV-32975). But the real fix should also be done in the PHP connector - it should not read this collation ID at all.

Thanks for the clarification. This would indeed need a proper fix at PHP's side then.

My gut feeling would be that to maintain you current behaviour, of you receive a zero for the charset byte, set your charset to 0x21.

I will send a message to MariaDB engineers now and will ask them to assist.

This was reported as https://jira.mariadb.org/browse/MDEV-32975 and it's marked as blocker, meaning it blocks the release, so the fix almost certainly will be in the next release of MariaDB.

As for what you can do, if you don't want to hard-code a specific charset (utf8mb3 looks rather safe), you can fail to connect, saying "buggy mariadb version please upgrade" — but please don't do that until there is a MariaDB version to upgrade to.

@LinuxJedi

@LinuxJedi

Hi. Let me ask you a question.

Regarding the charset ID specified in the handshake response, both utf8mb4_general_ci (45) and utf8mb4_unicode_ci (224) are utf8mb4, but will the behavior change?

Since character_set_client etc. are simply displayed as utf8mb4, I expect that the behavior will be the same no matter which one is specified.

@SakiTakamachi

@SakiTakamachi the character set between these two is the same, the difference is the collation, the way the characters are sorted and compared (ORDER BY and LIKE clauses for example). The _general_ci is faster, but less accurate for sorting. It doesn't support expansions, contractions and ignorable characters. _unicode_ci is more correct. For example, in _general_ci ß = s but in _unicode_ci ß = ss.

@LinuxJedi

@LinuxJedi
Thank you. That is, when it comes to character_set_client, character_set_connection and character_set_results, there is no sorting, so am I correct in understanding that these character sets will have the same behavior?

@LinuxJedi

@LinuxJedi Thank you. That is, when it comes to character_set_client, character_set_connection and character_set_results, there is no sorting, so am I correct in understanding that these character sets will have the same behavior?

Correct, and _general_ci is the oldest supported collation for that character set.

Thank you! This information is very useful in helping us come up with a default connection character set.

@SakiTakamachi

@SakiTakamachi you're right. This was discussed in https://jira.mariadb.org/browse/MDEV-32966 and the conclusion was that if a given collation is above 255, then a connector can always use a default collation instead, because default collations are below 255 and the charset is the same anyway.

And that's how https://jira.mariadb.org/browse/MDEV-32975 was fixed in MariaDB/server@1b37cb7

@vuvova

@vuvova
Thank you. The fact that we reached the same conclusion with MariaDB is very reassuring to us.

Fixed handshake response charset. (#13470)

Navigation Menu

Repository navigation

Unable to connect to MariaDB 11.3 #13452

Description

PHP Version

Operating System

21 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Unable to connect to MariaDB 11.3 #13452

Description

Description

PHP Version

Operating System

Activity

SakiTakamachi commented on Feb 21, 2024

mvorisek commented on Feb 21, 2024

SakiTakamachi commented on Feb 21, 2024

AirBair commented on Feb 21, 2024

SakiTakamachi commented on Feb 21, 2024

mvorisek commented on Feb 21, 2024

LinuxJedi commented on Feb 21, 2024

ndossche commented on Feb 21, 2024

21 remaining items

LinuxJedi commented on Feb 23, 2024

vuvova commented on Feb 23, 2024

SakiTakamachi commented on Feb 29, 2024

LinuxJedi commented on Feb 29, 2024

SakiTakamachi commented on Feb 29, 2024

LinuxJedi commented on Feb 29, 2024

SakiTakamachi commented on Feb 29, 2024

vuvova commented on Feb 29, 2024

SakiTakamachi commented on Feb 29, 2024

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions