[MXS-4199] SchemaRouter cannot handle schemas in upper case letters Created: 2022-07-09  Updated: 2022-07-13  Resolved: 2022-07-13

Status: Closed
Project: MariaDB MaxScale
Component/s: schemarouter
Affects Version/s: 6.3.1
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Edward Stoever Assignee: markus makela
Resolution: Cannot Reproduce Votes: 0
Labels: None


 Description   

Tested version 6.3.1

The existence of any schemas with names in UPPER CASE letters or Mixed Case letters will produce strange behaviors in maxscale schemarouter:

Strange behavior #1:
Querying directly on the table by specifying SCHEMA_NAME.table_name will produce an error:
ERROR 1142 (42000) at line 1: SELECT command denied to user 'edward'@'mx.edw.ee' for table 'test'
However, if the same user connects directly to the MariaDB server instead of through maxscale, he can easily query without error.

Strange Behavior #2:
show schemas command properly shows all schemas user has rights to. However, when the command is given: use SCHEMA_NAME; the session hangs - no response. Just hangs. CTRL+C to get out of it.

Strange Behavior #3:

MariaDB [(none)]> show schemas;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| JJ                 |
 
| dii                |
 
| information_schema |
 
| tek                |
 
+--------------------+
 
4 rows in set (0.000 sec)
 
 
 
MariaDB [(none)]> use JJ;
 
ERROR 1049 (42000): Unknown database: JJ
 
MariaDB [(none)]>

Strange Behavior #4
The existence of a schema with UPPER CASE name will cause similar problems for querying schemas with lower case names.

 Comments   
Comment by markus makela [ 2022-07-11 ]

The schemarouter is documented to compare table and database 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.

Does this happen with the latest version 6 release? If it does, can you provide an example that fully reproduces the problems?

Comment by Edward Stoever [ 2022-07-12 ]

This behavior was discovered in release 6.3.1. The problem is easy to reproduce. I could make a video to show you the behavior. Nevertheless, you can see it right there from my command line. It does not recognize that there is a schema called JJ. – Edward

Comment by Edward Stoever [ 2022-07-12 ]

Please watch this 4 minute demonstration:
https://youtu.be/LyzSuu8qVmQ
Thank you!

Comment by markus makela [ 2022-07-13 ]

For some reason it's not loading for me at this moment. Can you plese write the steps in text?

There were some fixes done to the schemarouter for the 6.4.0 release and it would be good to rule them out as the cause. If you can, please test this with the 6.4.0 release to make sure.

Comment by markus makela [ 2022-07-13 ]

A couple of configuration related things:

  1. The schemarouter service needs to have auth_all_servers=true to make sure all databases are queried for the user accounts
  2. The listener for the schemarouter should have authenticator_options=lower_case_table_names=2 to perform case-insensitive matching on database names
  3. The databases used by the schemarouter services must also have lower_case_table_names=1 configured
Comment by markus makela [ 2022-07-13 ]

Tested with 6.4.0 and lower_case_table_names on all database nodes as well as authenticator_options=lower_case_table_names=2 in the listener and auth_all_servers=true in the service and it seems to work as expected. If the database itself doesn't have lower_case_table_names enabled, the schemarouter does successfully route the query to the correct backend but the database ends up rejecting it due to case-sensitivity being on.

Comment by markus makela [ 2022-07-13 ]

I'll close this as Cannot Reproduce as it seems to be working as expected in 6.4.0: table and database names are compared case-insensitively.

If you can still reproduce this with 6.4.0, please enable log_info and attach the logs it generates. Also if you can give a reproducible test case, that would help.

Generated at Thu Feb 08 04:26:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.