I performed many other tests. What I noticed is that the problem is when connection is started from a Drupal or a Concrete5 Website. If it is started from a Wordpress (same server), it works, and also if client is HeidiSQL.
I tried to force case insensitive in MaxScale (MariaDB is on Windows) but still nothing.
So the problem is with the database names? The schemarouter should already treat table names case-insensitively:
Schemarouter compares table and database names case-insensitively. This means that the tables test.t1 and test.T1 are assumed to refer to the same table.
I cannot find where the problem is, but sure it always reports that the database does not exist (reporting in error message, as database name, exactly the value used by client, and that it's right, and always all lowercase).
But if it works case-insensitively, that is good and cannot be a problem because my MariaDB runs on Windows and so it is case-insensitive.
A full "case insensitive" environment is what my clients are expecting, because they are used to connect directly to a MariaDB running on Win.
I moved some databases from MariaDB on Win to MariaDB on docker (debian). The problem is the same:
- if the client is wordpress, it works
- if the client is Concrete5 or Drupal, maxscale reports that database does not exist
- if the client is a custom app that is using PDO ( $conn = new PDO($col , "$db_user", "$db_psw")
, MaxScale reports also that database does not exist
user and database are defined only on one server behind schemarouter
I don't know if this can help, but if I execute
show schemas like 'www.domain.com';
connected to mariaDB, it reports only one row [that's ok]
If I run it connected to maxScale, it reports the list of every existing database in mariaDB [that's not ok and I didn't find it in MaxScale limitations list]
Also, in the first result HeidiSQL colors row as green (that means "text"), on the seconds rows are gold (that means "other" data type).
Other test using MaxScale GUI -> visualization:
- if I connect to a "server", I can see databases and perform query on tables
- if I connect with same users to "service" or "listener", I cannot see databases and performing query I get "unknown database" result
So the problem starts in service schemarouter
Also tried to create a new readconnroute + listener pointing to same server, and same database is accessible from visualization.
I think it might be a bug in how the default database case-sensitiveness is handled.
I noticed that WordPress, the only app working, starts PDO sessions with this code:
$this->mysql = new
PDO("mysql:dbname={$this->options['dbname']};host={$this->options['host']};port={$this->options['port']}",
$this->options['user'], $this->options['pass'],
array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
so it assign as init command "SET NAMES utf8".
I suspect that the problem is not case related (I have only database name in lower case), but encoding related, and WP is "fixing" it with a good coding practice.
I found that the problem is the ".", BOTH on database names and on users names.
When a database or a login contains a ".", MaxScale return always "database unknown".
Not in connection from WordPress.
"_" (I also have that in logins) seems not creating problems
Ahh, it might be the . that is indeed the problem.
I think this might already be fixed in the upcoming 23.08 release.
This was partially fixed for 23.08 but required a minor fix to work correctly.