pingcap / pingcap/tidb

information_schema.schemata isn't compatible with MySQL

Open
#46,318 0 comments 0 reactions 0 assignees View on GitHub
compatibility-mysql80 severity/moderate sig/sql-infra type/bug type/compatibility
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

On Linux at least the schema names for system schemas in the `information_schema.schemata` table are upper case in TiDB while they are in lowercase in MySQL. This causes queries from tools that were made for MySQL to return unexpected output on TiDB making migration harder than needed.

### 1. Minimal reproduce step (Required)

```
SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN('mysql','information_schema','performance_schema','sys')
```

### 2. What did you expect to see? (Required)

Only non-system schemas
MySQL 8.1.0 gives this:
```
sql> SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN('mysql','information_schema','performance_schema','sys');
+-------------+
| SCHEMA_NAME |
+-------------+
| test |
+-------------+
1 row in set (0.0013 sec)
```

### 3. What did you see instead (Required)

```
sql> SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN('mysql','information_schema','performance_schema','sys');
+--------------------+
| schema_name |
+--------------------+
| blog |
| INFORMATION_SCHEMA |
| METRICS_SCHEMA |
| PERFORMANCE_SCHEMA |
| test |
+--------------------+
5 rows in set (0.0026 sec)
```

### 4. What is your TiDB version? (Required)

```
sql> SELECT TIDB_VERSION()\G
*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v7.3.0
Edition: Community
Git Commit Hash: 40b72e7a9a4fc9670d4c5d974dd503a3c6097471
Git Branch: heads/refs/tags/v7.3.0
UTC Build Time: 2023-08-08 10:08:14
GoVersion: go1.20.7
Race Enabled: false
Check Table Before Drop: false
Store: tikv
1 row in set (0.0011 sec)
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.