INFORMATION_SCHEMA tables are not ignored in TiDB

[shunki-fujita]

Issue Summary

When connecting Redash to TiDB, information_schema is displayed as shown below.
#5754

TiDB support was added in #5477.

In TiDB, information_schema and performance_schema schema are registered in uppercase.
Additionally, information_schema.columns is case-sensitive, so the following WHERE clause cannot filter uppercase letters.

redash/redash/query_runner/mysql.py

Lines 149 to 156 in 4357ea5

	def _get_tables(self, schema):
	query = """
	SELECT col.table_schema as table_schema,
	col.table_name as table_name,
	col.column_name as column_name
	FROM `information_schema`.`columns` col
	WHERE col.table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');
	"""

One possible solution is to use the LOWER() function to compare col.table_schema in lowercase.
This change works without any issues in MySQL as well.

...
WHERE LOWER(col.table_schema) NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys'); 

・SELECT results in TiDB

mysql> SELECT DISTINCT col.table_schema as table_schema
    ->     FROM `information_schema`.`columns` col
    ->     WHERE col.table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');
+---------------------+
| table_schema        |
+---------------------+
| test                |
| INFORMATION_SCHEMA  |
| PERFORMANCE_SCHEMA  |
| METRICS_SCHEMA      |
+---------------------+
4 rows in set (0.02 sec)

mysql>


mysql> SELECT DISTINCT col.table_schema as table_schema
    ->     FROM `information_schema`.`columns` col
    ->     WHERE LOWER(col.table_schema) NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');
+---------------------+
| table_schema        |
+---------------------+
| test                |
| METRICS_SCHEMA      |
+---------------------+
2 rows in set (0.01 sec)

mysql>

To be more specific, TiDB also has something called METRICS_SCHEMA.
Technically, this should be excluded as well, but since it's not a reserved keyword in MySQL, excluding it would break compatibility.
However, it’s worth noting that METRICS_SCHEMA is only visible to users who are explicitly granted permissions to access the database, so it may not be a major concern.

Steps to Reproduce

1. This is the first step
2. This is the second step, etc.

Any other info e.g. Why do you consider this to be a bug? What did you expect to happen instead?

Technical details:

• Redash Version: v25.1.0
• Browser/OS: chrome
• How did you install Redash: docker-compose

[snickerjp]

@shunki-fujita

I believe that the _get_tables function does not have a significant impact on this issue.
In TiDB, schemas such as information_schema and performance_schema are registered in uppercase, so the existing WHERE clause cannot filter them properly. By using LOWER(col.table_schema) NOT IN (...), it should work correctly in both MySQL and TiDB.

I think this fix is a simple and effective way to address the issue.

If you could submit a pull request, I would be happy to review it in more detail.

Thank you for the great suggestion! 🎉😆

[shunki-fujita]

@snickerjp
Of course!
Here is the PR I created.
#7352

[snickerjp]

@shunki-fujita

Thank you for your contribution!
Now that the changes have been merged, I will close the issue.

以下は日本語です。

マージをしたので、クローズします。
また、何かあればご連絡ください。
issueの起票から、プルリクエストの作成までありがとうございました。