Method for determining database version fails if database is supplied through ProxySQL

[MarkRS-UK]

Steps to reproduce the issue

Try to install/update a J! site where the database is provided via ProxySQL and supplies some version(s) below the required limit. In the case of MySQL this is 5.5.30.

Expected result

The installation/update is effected.

Actual result

J! will report that it cannot make the required installation/update because the database version is too low. It will report the database version as the lowest of the versions available via ProxySQL.

System information (as much as possible)

Additional comments

This is discussed in some detail at https://joomlacommunity.cloud.mattermost.com/main/pl/hhouru9kopyx5f4amenpe3ksrr which also refers to the same issue discussed, and resolved, in the Drupal world at https://www.drupal.org/project/drupal/issues/3156078

[richard67]

Am working on a fix.

[richard67]

@MarkRS-UK Does the problem only happen when using “MySQL (PDO)” in The database settings of the Joomla installation? Or does it also happen when using “MySQLi”?

[richard67]

The Drupal issue linked in the description of this issue here discussed possible solutions for getting the right version, but these were not implemented. The issue was closed with reference to its comment number 18, where another issue was linked which solves the issue by removing the code which had problems with the low version. This is something we can’t do.

[alikon]

for my understaning this is a configuration issue and should be solved elsewhere

[richard67]

for my understaning this is a configuration issue and should be solved elsewhere

@alikon That's what was said first in the linked Drupal issue. But then it had turned out that the ProxySQL can support different versions and reports back the oldest one, and to fix that by configuration would mean that the ProxySQL would not allow to connect to these old versions anymore. Drupal fixed it by changing their code so it doesn't require a particular MySQL version anymore, which is something we cannot do. We could fix it by using e.g. SELECT version(); like it was proposed in the Drupal issue at the beginning. The discussion in the Drupal issue doesn't really show me why that was rejected. I've tested that here and will provide a PR as soon as I know if it needs the fix only for MySQL (PDO) or also for MySQLi.

[alikon]

the Joomla Technical Requirements are debatable to be honest, at least on db side, we require mysql 8 for J5 without any benefit, so my question is: how much ProxySQL is adopteed in the wild ? more than mysql 5.7 despite eol ?

[richard67]

the Joomla Technical Requirements are debatable to be honest, at least on db side, we require mysql 8 for J5 without any benefit, so my question is: how much ProxySQL is adopteed in the wild ? more than mysql 5.7 despite eol ?

@alikon It's right that we currently do not use features which really require MySQL 8. But we use json methods in our update SQL in J5 which require at least 5.7. Lowering the requirement to 5.7 might help people with updating to 5. But it would not solve the issue reported here, as the ProxySQL here reports a version 5.5.30, and that is even below the requirement for Joomla 4, which is 5.6.

[MarkRS-UK]

I came across it because my hosting company is trying to offer selectable MySQL versions. There are other people in the thread on J! Community having the same problem.
It seems to me that in this situation (host offering multiple versions) ProxySQL can't provide the correct version so I, too, came up with a "SELECT @@Version" solution.
J! 4.4.8 also now requires greater than 5.5.30, and also now can't be installed :(
I agree this should be solved elsewhere, but it doesn't look like it will be.

[richard67]

@MarkRS-UK What about my question above: Does the problem only happen when using “MySQL (PDO)” in the database settings of the Joomla installation? Or does it also happen when using “MySQLi”?

[MarkRS-UK]

@richard67 It happens with MySQLi too

[richard67]

@MarkRS-UK Do you have some knowledge on ProxySQL? Or do you know someone who has? If so, maybe you can help with a few questions.

1. Can that proxy be used only with MySQL databases, or can it also be used with MariaDB databases?
2. If 1. only for MySQL: Is there something similar for MariaDB, too?
3. And for PostgreSQL?
4. Does that Proxy just pass SQL statements to the real database as they are, and only limitations of the real database apply? Or can that proxy itself have some additional limitations? E.g. can it be that the database supports a certain feature but the proxy doesn't? E.g. character sets (utf8mb3 or utf8mb4), prepared statements, ...

Depending on the answers to these questions I might have to modify my current work.

If it turns out that we should do something for all supported kinds of databases, it could make sense to add a new method getServerVersion or more precise getDbServerVersion (as the proxy is also a kind of server) instead of modifying the getVersion method, and make the CMS use the new method.

And maybe even add a new method getConnectorVersion which is equal to the existing getVersion, make the getVersion call that new method and deprecate the getVersion method so it would be removed in a future major version of the framework. Then it would be more clear what each method does.

[MarkRS-UK]

@richard67 The only knowledge I have of ProxySQL is because of this issue.
It's an open source project, the repo is on github.
It's specifically for MySQL and derived databases, so includes MariaDb and others. I think that answers 1-3.

As for 4, I don't know in any detail. Looking at the issues list it seems to have some issues of its own, so it doesn't just pass statements on. I have tested the "SELECT @@Version" idea and it produces the correct MySQL version (my hosting company uses MySQL :-| ). I just ran it in a template for a view in my site.

[richard67]

I have tested the "SELECT @@Version" idea and it produces the correct MySQL version (my hosting company uses MySQL :-| ). I just ran it in a template for a view in my site.

@MarkRS-UK Does "SELECT version()" also work for you?

[MarkRS-UK]

@richard67 Yes, it gives the correct version, same as @@Version

[MarkRS-UK]

I have had conversation with my hosting company about this. They report that ProxySQL is not able to report the version of the database it is supplying. Whilst it may be their error, I would hope not, and in fact my reading of the response to my issue opened with ProxySQL agrees with their conclusion.
So this change is going to be required in general I think :(