The mentioning of MariaDB in the documentation

[michal-josef-spacek]

Hi, I would like to discuss information about MariaDB in DBD::mysql documentation.

My intention with this is to understand what we support and what is buggy or missing.

There are some facts:

• There is no information about MariaDB in DBD::mysql documentation
• Tests in CI are running against MySQL only
• Tests are written against MySQL only (instead of 57trackgtid.t. which has $dbh->{mysql_serverversion} > 100000 condition, which is a bit tricky (The version of MySQL could be greater than 100000 in the future).
• I have a situation where I have DBD::mysql compiled with libmysqlclient.so.21 on Fedora 41, connected to MariaDB, and result of the $dbh->get_info($GetInfoType{SQL_DBMS_NAME}) is MySQL with version $dbh->{mysql_serverversion} is 50505 (With DBD::MariaDB and same libmysqlclient.so.21 library I have "MariaDB" and "101111").
• In the real world in Red Hat RHEL: In RHEL8 we have a perl-DBD-mysql only and MySQL and MariaDB databases. So there were only one option to connect to MariaDB and this was DBD::mysql. From RHEL9 we have perl-DBD-MariaDB, so situation is "better".

My questions:

• Do we support working with MariaDB database?
• Or specify the situation. e.g. We support work with MariaDB to/from version etc.

My proposals:

• Add some information about MariaDB to the DBD::mysql documentation. It depends on what we write there, according to the answers above.

[dveeden]

When compiling DBD::mysql it requires MySQL v8.0 or newer (except for v4.x versions of DBD::mysql). Building against MariaDB isn't supported and doesn't work as the C API of MySQL and MariaDB have diverted.

But once DBD::mysql is build it should be able to connect to MariaDB and function. The same is true for connecting with DBD::mysql to TiDB or anything else that supports the MySQL protocol and syntax.

Limitations here are that very old versions of MySQL (and MariaDB) might not work. (e.g. MySQL 3.23.x, 4.0, etc). I would expect the v10 protocol and CLIENT_PROTOCOL_41.

[michal-josef-spacek]

@dveeden Ok, thank you for the answer. I understand it. I am proposing to add this information to the documentation because this is not clean. Is this ok for you? I would prepare doc change.

What about the $dbh->get_info($GetInfoType{SQL_DBMS_NAME}) and $dbh->{mysql_serverversion}?
I have Fedora 41 on system with mysql-devel package version 8.0.40, and the return values are not in a style that I could detect MySQL or MariaDB.

On my system, connecting to MariaDB.
DBD::mysql:

mysql_clientinfo: 8.0.40
mysql_clientversion: 80040
mysql_serverversion: 50505
mysql_protoinfo: 10
SQL_DBMS_NAME: MySQL
SQL_DBMS_VER: 5.5.5-10.11.11-MariaDB
SQL_DBMS_VERSION: 5.5.5-10.11.11-MariaDB
SQL_DRIVER_NAME: libmyodbc3.so
SQL_DRIVER_ODBC_VER: 03.51
SQL_DRIVER_VER: 05.11.0000

DBD::MariaDB:

mariadb_clientinfo: 8.0.40
mariadb_clientversion: 80040
mariadb_serverversion: 101111
mariadb_protoinfo: 10
SQL_DBMS_NAME: MariaDB
SQL_DBMS_VER: 10.11.1100
SQL_DBMS_VERSION: 10.11.1100
SQL_DRIVER_NAME: DBD/MariaDB.pm
SQL_DRIVER_ODBC_VER: 03.51
SQL_DRIVER_VER: 01.23.0000

There are three possibilities:

• It's a bug. Then I am proposing to fix it. I believe that's a bug, but I don't fully understand the consequences.
• It's a fact. Then I am proposing to add documentation about the incompatibility between MySQL and MariaDB databases.
• There is a mistake on my side, but I checked it, and I don't see the issue on my side.

What about it?

[dveeden]

I think there is another possibility: It's a mix.

The SQL_DRIVER_NAME seems wrong to me. This is a bug.

DBD-mysql/lib/DBD/mysql/GetInfo.pm

Line 159 in 045fd5f

6 => 'libmyodbc3.so', # SQL_DRIVER_NAME

For SQL_DBMS_NAME and SQL_DBMS_VERSION the situation is less clear.

Take these version strings:

• 9.2.0
• 8.0.11-TiDB-v9.0.0-alpha-508-g61c09e601e
• 8.0.36-u5-cloud
• 5.5.5-10.11.11-MariaDB

Two of them are clear MySQL versions (9.2.0 and 8.0.36). The second one is a development build of TiDB v9.0.0 that announces itself as MySQL 8.0.11. The last one is MariaDB 10.11.11 announcing itself as MySQL 5.5.5. Here both TiDB and MariaDB have their own version but also have a MySQL version.

Then there is mysql_clientversion, which is the client library that is in use. This seems fine.

And then mysql_serverversion is basically the MySQL version that the server identifies itself.

I would say that to find out if one is using MariaDB, TiDB or any other server one needs to check the SQL_DBMS_VERSION.

We could add something like mysql_server_implementation and check/extract the implementation name (e.g. MariaDB) and get the version. But there are many implementations, so this might be hard to get right.

And then there is the case that one uses a proxy that may or may not return the expected version string in the handshake. This would result in different versions in the server greeting packet and in the output of SELECT VERSION().

For example ProxySQL (Available in Fedora 42, probably also Fedora 41) identifies itself as 5.5.30, but have MariaDB, MySQL, etc as backend servers.

I don't think DBD::mysql tests show SQL_DRIVER_NAME. Is the output you shared from a test script?

[michal-josef-spacek]

I don't think DBD::mysql tests show SQL_DRIVER_NAME. Is the output you shared from a test script?

There is no mention of SQL_DRIVER_NAME in tests of DBD::mysql. That's true.
I see differences between MySQL and MariaDB e.g. in the case of JSON data type. And without this information. I cannot test this properly.

Thank you.

[dveeden]

@michal-josef-spacek Could you check #470 and #469 ?

[dveeden]

@michal-josef-spacek I just merged #471 to add CI testing with MariaDB.

It would be good if we could spend some time on t/51bind_type_guessing.t to make it work on MariaDB.

[michal-josef-spacek]

@dveeden Great, thank you for the commits and release.

I need some time to check it.

[michal-josef-spacek]

@dveeden About SQL_DBMS_NAME:
Actual situation is hardcoded string "MySQL".
My original proposal was to improve it for e.g. usage in tests to detect MariaDB vs MySQL.

1. There is a better way to detect the original database (MySQL, MariaDB, MySQL over ProxySQL, MariaDB over ProxySQL, ProxySQL). Combination of statements like SHOW VARIABLES LIKE '%version_comment%' and SELECT @@version_comment LIMIT 1; (this one is hardcoded in ProxySQL, so for info about database behind you need to use previous statement). → We could create helper to detect database with usage of these statements.
2. If we improve SQL_DBMS_NAME, this is only benefit. We could bring DBD::MariaDB version of code, when MariaDB string on the end means MariaDB. (See https://github.com/perl5-dbi/DBD-MariaDB/blob/master/lib/DBD/MariaDB.pm#L909C141-L909C154)

What do you think?

[michal-josef-spacek]

Examples:
mysql command (from MariaDB) to mysql database:

MySQL [(none)]> select @@version_comment limit 1;
+------------------------------+
| @@version_comment            |
+------------------------------+
| MySQL Community Server - GPL |
+------------------------------+
1 row in set (0,001 sec)

MySQL [(none)]> show variables like '%version_comment%';
+-----------------+------------------------------+
| Variable_name   | Value                        |
+-----------------+------------------------------+
| version_comment | MySQL Community Server - GPL |
+-----------------+------------------------------+
1 row in set (0,010 sec)

mysql command (from MariaDB) to proxysql with mysql as a backend:

MySQL [(none)]> select @@version_comment limit 1
+-------------------+
| @@version_comment |
+-------------------+
| (ProxySQL)        |
+-------------------+
1 row in set (0,000 sec)

MySQL [(none)]> show variables like '%version_comment%';
+-----------------+------------------------------+
| Variable_name   | Value                        |
+-----------------+------------------------------+
| version_comment | MySQL Community Server - GPL |
+-----------------+------------------------------+
1 row in set (0,006 sec)

mysql command (from MariaDB) to MariaDB database:

+-------------------+
| @@version_comment |
+-------------------+
| MariaDB Server    |
+-------------------+
1 row in set (0,000 sec)

MariaDB [(none)]> show variables like '%version_comment%';
+-----------------+----------------+
| Variable_name   | Value          |
+-----------------+----------------+
| version_comment | MariaDB Server |
+-----------------+----------------+
1 row in set (0,003 sec)

[dveeden]

I think it would be good to change SQL_DBMS_NAME as you said to return MariaDB for MariaDB, MySQL for MySQL, etc.

I think it would make sense to also consider SQL_DBMS_VER as well.

I guess that for Percona Server, we should just return MySQL?

Version String	SQL_DBMS_NAME	SQL_DBMS_VER
9.2.0	MySQL	9.2.0
8.0.11-TiDB-v9.0.0-alpha-508-g61c09e601e	TiDB	v9.0.0-alpha-508-g61c09e601e
8.0.36-u5-cloud	MySQL	8.0.36-u5-cloud

| 5.5.5-10.11.11-MariaDB | MariaDB | 10.11.11 |

Maybe we should truncate everything after -, so 8.0.36-u5-cloud returns 8.0.36?

[michal-josef-spacek]

Yes, I like it.

[michal-josef-spacek]

I guess that for Percona Server, we should just return MySQL?

I don't know.