Usage of this could depend on the PHP mysqlnd drivers but the most important thing is that the configuration is supported by Joomla core and it is tested and supported.

Any proposal here must:

• Not depend on mysqlnd for the MySQL drivers (libmysqlclient is a supported environment but admittedly it takes a bit more work to use it)
• Must be supported for all database drivers (MySQL, PostgreSQL, SQLite, and SQL Server)

Hi Michael,

Thank you for your response.

My point on mysqlnd is speaking from the perspective of Joomla development companies, having to install an additional PHP library for this type of behaviour isn't a problem, what is more important to my business is that there is a supported way of doing this. I don't particular want mysqlnd as a solution.

I'm guessing that the point about all database drivers is a Joomla policy. From my company's perspective, the overwhelming requirement should be that Joomla can scale on modern cloud infrastructure. Wouldn't it be a shame to not support read replicas because SQLite can't do replication? Could there be a "middleware" approach which enables the use of read replicas if the database supports it?

Thanks for your time,
-David.

Any feature that gets added to the Joomla\Database API should be cross-platform portable (otherwise that defeats the point of having a database abstraction layer, it's why extra work went into having a parser to support named parameters for parameterized queries on MySQLi (a feature that's only available on PDO)). So yes, it should be considered a requirement if anything in the database package changes.

Realistically, this might not need changes in the database API though as long as an efficient interface for setting configuration options is available. In Joomla 4.0 (and Framework 2.0, which is the core foundation of a bulk of the Joomla API), the notion of a global singleton database driver is deprecated and it is instead suggested to use the database factory to create drivers (connections) instead. So in an application (i.e. the CMS), someone could subclass the factory and introduce a syntax to support multiple connections with differing requirements (read-only, read/write, etc.). That part's actually relatively easy on its own, but in something like the CMS exposing a UI to configure that is a bit more complex as well as storing the parameters for those connections somewhere.

The not-so-easy part of that is in changing the application itself. Nothing in the existing API supports a notion of multiple connections and working out support for that would be rather complicated (everything right now basically relies on either the global singleton connection or supports connection of a single driver instance and for the bulk of the API that would have to be a read/write capable driver, or the driver internally has to work out whether it should use a read-only, read/write, or other connection type, and that is the bit that would have to be cross-platform portable in some form; which means even if SQLite doesn't support the feature at all internally the driver would still need to do something meaningful, be it raising an Exception because the feature's unsupported and telling someone to fix their configuration or internally mapping everything to a single connection since it doesn't support replication).

Hi Michael,

How about doing it at the database driver level? So, you have MySQL, PostgreSQL, SQLite, SQL Server and (for argument's sake) MySQLLB. So, MySQLLB would be a new load-balanced version of the MySQL driver which accepts additional configuration parameters for the data read replica.

Then you can keep your singletons, database factory and API without change. No changes to the application needed.

The MySQLLB driver would direct queries to the relevant server based on it's own internal logic. For example, it could direct all calls to the read/write master server UNLESS it was certain it was a read-only query (maybe only SELECT calls without subqueries).

The same approach can then be carried across to create a PostgreSQLLB driver, re-using some of the logic to calculate if a query is definitely a read.

Would that get around some of the problems of doing it at a higher layer?

Thanks,
-David.

Personally I'm not the biggest fan of having unique drivers for load balanced connections, that is a feature that should just be baked right into the base drivers with no questions asked. In the case of MySQL that would mean two new drivers as there would be one for both PDO and MySQLi, and had we not dropped native ext/pgsql support in favor of PDO only it'd be two drivers for that platform as well (and again, same driver count argument if we did PDO SQL Server support; remember even though the CMS application is only supporting MySQL and PostgreSQL as of 4.0 the SQLite and SQL Server drivers are still part of our package).

It might fix the problem for the CMS application with the least amount of effort, I just personally feel like that's not the right way to approach it though from an API design perspective.

I agree that this approach would multiply the number of drivers but some sort of support at the driver level seems the best way of doing it, as the application shouldn't (I think) care about splitting reads from writes, because that's a level of complexity that will only cause problems for developers. If select drivers (MySQL, PostgreSQL, SQL server) were updated to support load balancers, I can't see any reason why there should be a modification to the APIs for the CMS application (aside from configuration)?

Thanks for your time,
-David.

Thank you for raising this issue.

Joomla 3 is now in security only mode with no further bug fixes or new features.

As this issue doesn't relate to Joomla 4 it will now been closed.

If we are mistaken and this does apply to Joomla 4 please open a new issue (and reference this one if you wish) with updated details for testing in Joomla 4.
cc @zero-24