| Bug #99662 | Inconsistency when joining Perf Schema schema tables when PK is larger than 48 c | ||
|---|---|---|---|
| Submitted: | 21 May 2020 22:29 | Modified: | 5 Jul 2020 11:20 |
| Reporter: | Carlos Tutte | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S3 (Non-critical) |
| Version: | 8.0.20 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[25 May 2020 12:35]
MySQL Verification Team
HI Mr. Tutte, Thank you for your bug report. However, we do have some questions. Can you discern any differences between the tables in your database versus the tables in P_S ??? Especially, are the collations 100 % the same ???? Also, we need a fully repeatable test case. Hence, we need the data to fill up those two P_S tables in the way that you have done it.
[26 Jun 2020 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[26 Jun 2020 1:03]
Trey Raymond
users have no control over schema, or charset and collation, in performance_schema tables, so there's no way it could be different. these are defined by the server.
[26 Jun 2020 1:06]
Trey Raymond
I can't reopen this, so filed https://bugs.mysql.com/bug.php?id=99989&thanks=4
[26 Jun 2020 12:03]
MySQL Verification Team
Hi, Please read once again carefully all of my questions. Answer them all, please .....
[29 Jun 2020 15:12]
Carlos Tutte
> However, we do have some questions. > Also, we need a fully repeatable test case. Hence, we need the data to fill up those two P_S tables in the way that you have done it. Test case was already provided. ```Execute the following (does not need to be a valid host, just for populating the perf schma tables): change master to master_host='test' for channel '12345678901234567890123456789012345678901234567890'; change master to master_host='asdf' for channel '12345678901234567890'; ``` Also create table for custom table was provided (you can compare table structure by executing "SHOW CREATE TABLE" with same tables with same name on PS schma. > Can you discern any differences between the tables in your database versus the tables in P_S ??? No differences. The problem seems to be that the join truncates part of the column when joining > Especially, are the collations 100 % the same ???? Seems so. The problem is on PS tables, not on custom tables
[30 Jun 2020 12:21]
MySQL Verification Team
Hi Mr. Tutte, You do not have control of the P_S tables, but you can see their structure and replicate it in user database to the last detail. We can not attempt to replicate and analyse this report, unless the tables have 100 % the same structure and definitions. Next, can you replicate this scenario on the single machine, or only with slave. First variant would be more acceptable to us. Waiting on your feedback.
[30 Jun 2020 17:43]
Carlos Tutte
Hi again,
> You do not have control of the P_S tables, but you can see their structure and replicate it in user database to the last detail. We can not attempt to replicate and analyse this report, unless the tables have 100 % the same structure and definitions.
I know I do not have control over PS tables, so my test case is using default tables.
I used "CHANGE MASTER TO" statements to populate the PS tables. You do not need to set up replication, you only need to execute "CHANGE MASTER TO" to populate the tables with whatever string you like (larger than 48 chars which is when the bug occurs).
I re did the experiment with non-default tables for comparison only.
The problem happens when trying to execute some queries which show inconsistent results. For example, If I need to check replication status or generate some information using custom queries against these PS tables, result is inconsistent, i.e:
mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name
-> from performance_schema.replication_connection_configuration rcc
-> left join performance_schema.replication_connection_status rcs on rcc.channel_name=rcs.channel_name ;
+----------------------------------------------------+----------------------+-----------------------------------+
| channel_name | channel_name | rcc.channel_name=rcs.channel_name |
+----------------------------------------------------+----------------------+-----------------------------------+
| 12345678901234567890123456789012345678901234567890 | NULL | NULL |
| 12345678901234567890 | 12345678901234567890 | 1 |
+----------------------------------------------------+----------------------+-----------------------------------+
2 rows in set (0.00 sec)
Why does above query show "NULL" for first row, but "1" for second? Expected result should be "1" for both rows.
> Next, can you replicate this scenario on the single machine, or only with slave. First variant would be more acceptable to us.
To reproduce, you can do on a single machine, standalone and "CHANGE MASTER TO" is only used to populate tables, as you do not need replication to work. You only execute statements to just have the tables populated.
Complete test case:
--------------------------------------------------------
mysql> change master to master_host='test' for channel '12345678901234567890123456789012345678901234567890';
Query OK, 0 rows affected (0.09 sec)
mysql> change master to master_host='asdf' for channel '12345678901234567890';
Query OK, 0 rows affected (0.09 sec)
mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name
-> from performance_schema.replication_connection_configuration rcc
-> left join performance_schema.replication_connection_status rcs on rcc.channel_name=rcs.channel_name ;
+----------------------------------------------------+----------------------+-----------------------------------+
| channel_name | channel_name | rcc.channel_name=rcs.channel_name |
+----------------------------------------------------+----------------------+-----------------------------------+
| 12345678901234567890123456789012345678901234567890 | NULL | NULL |
| 12345678901234567890 | 12345678901234567890 | 1 |
+----------------------------------------------------+----------------------+-----------------------------------+
2 rows in set (0.01 sec)
--------------------------------------------------------------
Regards
[1 Jul 2020 12:12]
MySQL Verification Team
Hi Mr. Tutte, I have repeated the behaviour. The behaviour is correct. You get NULL , because that is how any outer join works. Not a bug.
[1 Jul 2020 15:27]
Carlos Tutte
Hi again,
> The behaviour is correct. You get NULL , because that is how any outer join works.
But I have provided many examples with same data 1) (custom tables, not PS tables) 2) PS tables with shorter PK; and result is NOT NULL.
I.e:
---------------------------------------------------------------
mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name
-> from performance_schema.replication_connection_configuration rcc
-> left join performance_schema.replication_connection_status rcs on rcc.channel_name=rcs.channel_name ;
+----------------------------------------------------+----------------------+-----------------------------------+
| channel_name | channel_name | rcc.channel_name=rcs.channel_name |
+----------------------------------------------------+----------------------+-----------------------------------+
| 12345678901234567890123456789012345678901234567890 | NULL | NULL |
| 12345678901234567890 | 12345678901234567890 | 1 |
+----------------------------------------------------+----------------------+-----------------------------------+
2 rows in set (0.00 sec)
Why is 2nd row not NULL here if that is expcted?
Same here:
mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name
-> from performance_schema.replication_connection_configuration rcc
-> left join performance_schema.replication_connection_status rcs on rcs.channel_name<=>rcc.channel_name;
+----------------------------------------------------+----------------------+-----------------------------------+
| channel_name | channel_name | rcc.channel_name=rcs.channel_name |
+----------------------------------------------------+----------------------+-----------------------------------+
| 12345678901234567890123456789012345678901234567890 | NULL | NULL |
| 12345678901234567890 | 12345678901234567890 | 1 |
+----------------------------------------------------+----------------------+-----------------------------------+
2 rows in set (0.00 sec)
------------------------------------------------------------
And why result is NOT NULL in the following exact query executed against a table created with same structure as PS table, populated with exactly same data:
mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name
-> from performance_schema.replication_connection_configuration rcc
-> left join performance_schema.replication_connection_status rcs on rcc.channel_name like rcs.channel_name;
+----------------------------------------------------+----------------------------------------------------+-----------------------------------+
| channel_name | channel_name | rcc.channel_name=rcs.channel_name |
+----------------------------------------------------+----------------------------------------------------+-----------------------------------+
| 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 |
| 12345678901234567890 | 12345678901234567890 | 1 |
+----------------------------------------------------+----------------------------------------------------+-----------------------------------+
2 rows in set (0.00 sec)
I see an inconsistency in the results.
Thanks in advance
[2 Jul 2020 12:09]
MySQL Verification Team
Hi, Because, evidently, contents are not the same ...... not structure ......
[2 Jul 2020 15:50]
Carlos Tutte
Last question,
after executing the following commands to populate the table with 2 rows:
change master to master_host='test' for channel '12345678901234567890123456789012345678901234567890';
change master to master_host='asdf' for channel '12345678901234567890';
Why does the following query show "NULL" for 1 row and "1" for the other" Shouldn't it be both NULL or both 1 ?
mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name
-> from performance_schema.replication_connection_configuration rcc
-> left join performance_schema.replication_connection_status rcs on rcc.channel_name=rcs.channel_name ;
+----------------------------------------------------+----------------------+-----------------------------------+
| channel_name | channel_name | rcc.channel_name=rcs.channel_name |
+----------------------------------------------------+----------------------+-----------------------------------+
| 12345678901234567890123456789012345678901234567890 | NULL | NULL |
| 12345678901234567890 | 12345678901234567890 | 1 |
+----------------------------------------------------+----------------------+-----------------------------------+
2 rows in set (0.00 sec)
If I keep adding rows (with change master statement), result is always "1" except for the row with PK larger than 48 chars:
mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name from performance_schema.replication_connection_configuration rcc left join performance_schema.replication_connection_status rcs on rcc.channel_name=rcs.channel_name;
+----------------------------------------------------+-----------------------------+-----------------------------------+
| channel_name | channel_name | rcc.channel_name=rcs.channel_name |
+----------------------------------------------------+-----------------------------+-----------------------------------+
| 12345678901234567890123456789012345678901234567890 | NULL | NULL |
| 12345678901234567890 | 12345678901234567890 | 1 |
| 123456789012345678905 | 123456789012345678905 | 1 |
| 123456789012345678905555555 | 123456789012345678905555555 | 1 |
+----------------------------------------------------+-----------------------------+-----------------------------------+
4 rows in set (0.00 sec)
[3 Jul 2020 12:14]
MySQL Verification Team
Hi, Try to list contents of both tables immediately you run that outer join and then draw conclusions yourself.
[3 Jul 2020 13:38]
MySQL Verification Team
Hi, Only one more detail. Please, make sure that you report bugs on MySQL binaries, not binaries from any other provider.
[5 Jul 2020 8:53]
Sveta Smirnova
Here is test case for MySQL binaries:
1. Start it using MTR:
./mtr --start innodb &
2. Now connect to it and run queries:
mysql> change master to master_host='test' for channel '12345678901234567890123456789012345678901234567890';
Query OK, 0 rows affected (0,05 sec)
mysql> change master to master_host='asdf' for channel '12345678901234567890';
Query OK, 0 rows affected (0,04 sec)
mysql> select channel_name from performance_schema.replication_connection_configuration;
+----------------------------------------------------+
| channel_name |
+----------------------------------------------------+
| 12345678901234567890123456789012345678901234567890 |
| 12345678901234567890 |
+----------------------------------------------------+
2 rows in set (0,00 sec)
mysql> select channel_name from performance_schema.replication_connection_status;
+----------------------------------------------------+
| channel_name |
+----------------------------------------------------+
| 12345678901234567890123456789012345678901234567890 |
| 12345678901234567890 |
+----------------------------------------------------+
2 rows in set (0,00 sec)
You see that both queries have exactly same rows.
2. Now run JOIN:
mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name from performance_schema.replication_connection_configuration rcc join performance_schema.replication_connection_status rcs on rcc.channel_name=rcs.channel_name ;
+----------------------+----------------------+-----------------------------------+
| channel_name | channel_name | rcc.channel_name=rcs.channel_name |
+----------------------+----------------------+-----------------------------------+
| 12345678901234567890 | 12345678901234567890 | 1 |
+----------------------+----------------------+-----------------------------------+
1 row in set (0,00 sec)
mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name from performance_schema.replication_connection_configuration rcc left join performance_schema.replication_connection_status rcs on rcc.channel_name=rcs.channel_name ;
+----------------------------------------------------+----------------------+-----------------------------------+
| channel_name | channel_name | rcc.channel_name=rcs.channel_name |
+----------------------------------------------------+----------------------+-----------------------------------+
| 12345678901234567890123456789012345678901234567890 | NULL | NULL |
| 12345678901234567890 | 12345678901234567890 | 1 |
+----------------------------------------------------+----------------------+-----------------------------------+
2 rows in set (0,00 sec)
Ups! The longer value is not considered when using JOIN condition.
4. Let's check table definitions and create similar tables in the test database:
mysql> show create table performance_schema.replication_connection_configuration\G
*************************** 1. row ***************************
Table: replication_connection_configuration
Create Table: CREATE TABLE `replication_connection_configuration` (
`CHANNEL_NAME` char(64) NOT NULL,
...
PRIMARY KEY (`CHANNEL_NAME`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0,00 sec)
mysql> show create table performance_schema.replication_connection_status\G
*************************** 1. row ***************************
Table: replication_connection_status
Create Table: CREATE TABLE `replication_connection_status` (
`CHANNEL_NAME` char(64) NOT NULL,
...
PRIMARY KEY (`CHANNEL_NAME`),
KEY `THREAD_ID` (`THREAD_ID`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0,01 sec)
mysql> create table t1(`CHANNEL_NAME` char(64) NOT NULL) engine=innodb DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0,04 sec)
mysql> create table t2(`CHANNEL_NAME` char(64) NOT NULL) engine=innodb DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0,03 sec)
mysql> insert into t1 select channel_name from performance_schema.replication_connection_configuration; Query OK, 2 rows affected (0,01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t2 select channel_name from performance_schema.replication_connection_status;
Query OK, 2 rows affected (0,01 sec)
Records: 2 Duplicates: 0 Warnings: 0
5. JOIN on tables in the test schema works as expected:
mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name from test.t1 rcc join test.t2 rcs on rcc.channel_name=rcs.channel_name ;
+----------------------------------------------------+----------------------------------------------------+-----------------------------------+
| channel_name | channel_name | rcc.channel_name=rcs.channel_name |
+----------------------------------------------------+----------------------------------------------------+-----------------------------------+
| 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 |
| 12345678901234567890 | 12345678901234567890 | 1 |
+----------------------------------------------------+----------------------------------------------------+-----------------------------------+
2 rows in set (0,00 sec)
mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name from test.t1 rcc left join test.t2 rcs on rcc.channel_name=rcs.channel_name ;
+----------------------------------------------------+----------------------------------------------------+-----------------------------------+
| channel_name | channel_name | rcc.channel_name=rcs.channel_name |
+----------------------------------------------------+----------------------------------------------------+-----------------------------------+
| 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 |
| 12345678901234567890 | 12345678901234567890 | 1 |
+----------------------------------------------------+----------------------------------------------------+-----------------------------------+
2 rows in set (0,00 sec)
6. Version of the server:
mysql> select @@version;
+--------------+
| @@version |
+--------------+
| 8.0.19-debug |
+--------------+
1 row in set (0,01 sec)
mysql> \s
--------------
../bin/mysql Ver 8.0.19 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
Connection id: 8
Current database: test
Current user: root@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.19-debug MySQL Community Server - GPL - Debug
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 13000
Binary data as: Hexadecimal
Uptime: 2 min 53 sec
Threads: 2 Questions: 24 Slow queries: 0 Opens: 149 Flush tables: 3 Open tables: 66 Queries per second avg: 0.138
[5 Jul 2020 8:54]
Sveta Smirnova
However, this was already verified as bug #99989
[5 Jul 2020 11:19]
MySQL Verification Team
Thank you for the feedback. Duplicate of https://bugs.mysql.com/bug.php?id=99989.
Description: When executing some join using perf schema tables, the result is not same to same query over custom tables (with same structure and PKs) when PK is larger than 48 char. Querying against PS schema tables: ---------------------------------------------------------------------------------------------------------------------------- mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from performance_schema.replication_connection_configuration rcc -> left join performance_schema.replication_connection_status rcs on rcc.channel_name=rcs.channel_name ; +----------------------------------------------------+----------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------+-----------------------------------+ | 12345678901234567890123456789012345678901234567890 | NULL | NULL | | 12345678901234567890 | 12345678901234567890 | 1 | +----------------------------------------------------+----------------------+-----------------------------------+ 2 rows in set (0.00 sec) mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from performance_schema.replication_connection_configuration rcc -> left join performance_schema.replication_connection_status rcs on rcs.channel_name<=>rcc.channel_name; +----------------------------------------------------+----------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------+-----------------------------------+ | 12345678901234567890123456789012345678901234567890 | NULL | NULL | | 12345678901234567890 | 12345678901234567890 | 1 | +----------------------------------------------------+----------------------+-----------------------------------+ 2 rows in set (0.00 sec) ---------------------------------------------------------------------------------------------------------------------------- Same queries against tables with same structure but on another database: mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from replication_connection_configuration rcc -> left join replication_connection_status rcs on rcc.channel_name=rcs.channel_name ; +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | 12345678901234567890 | 12345678901234567890 | 1 | | 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from replication_connection_configuration rcc -> left join replication_connection_status rcs on rcs.channel_name<=>rcc.channel_name; +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | 12345678901234567890 | 12345678901234567890 | 1 | | 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ 2 rows in set (0.00 sec) How to repeat: Execute the following (does not need to be a valid host, just for populating the perf schma tables): change master to master_host='test' for channel '12345678901234567890123456789012345678901234567890'; change master to master_host='asdf' for channel '12345678901234567890'; --------------------------------------------------------------------------------------------------------- Create tables using same structure as perf schema (cannot use "create table like due to engine=perf schema): create database test; use test; CREATE TABLE `replication_connection_configuration` ( `CHANNEL_NAME` char(64) NOT NULL, `HOST` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL, `PORT` int NOT NULL, `USER` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `NETWORK_INTERFACE` char(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `AUTO_POSITION` enum('1','0') NOT NULL, `SSL_ALLOWED` enum('YES','NO','IGNORED') NOT NULL, `SSL_CA_FILE` varchar(512) NOT NULL, `SSL_CA_PATH` varchar(512) NOT NULL, `SSL_CERTIFICATE` varchar(512) NOT NULL, `SSL_CIPHER` varchar(512) NOT NULL, `SSL_KEY` varchar(512) NOT NULL, `SSL_VERIFY_SERVER_CERTIFICATE` enum('YES','NO') NOT NULL, `SSL_CRL_FILE` varchar(255) NOT NULL, `SSL_CRL_PATH` varchar(255) NOT NULL, `CONNECTION_RETRY_INTERVAL` int NOT NULL, `CONNECTION_RETRY_COUNT` bigint unsigned NOT NULL, `HEARTBEAT_INTERVAL` double(10,3) unsigned NOT NULL COMMENT 'Number of seconds after which a heartbeat will be sent .', `TLS_VERSION` varchar(255) NOT NULL, `PUBLIC_KEY_PATH` varchar(512) NOT NULL, `GET_PUBLIC_KEY` enum('YES','NO') NOT NULL, `NETWORK_NAMESPACE` varchar(64) NOT NULL, `COMPRESSION_ALGORITHM` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Compression algorithm used for data transfer between master and slave.', `ZSTD_COMPRESSION_LEVEL` int NOT NULL COMMENT 'Compression level associated with zstd compression algorithm.', `TLS_CIPHERSUITES` text CHARACTER SET utf8 COLLATE utf8_bin, PRIMARY KEY (`CHANNEL_NAME`) ) ENGINE=innodb DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; CREATE TABLE `replication_connection_status` ( `CHANNEL_NAME` char(64) NOT NULL, `GROUP_NAME` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `SOURCE_UUID` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `THREAD_ID` bigint unsigned DEFAULT NULL, `SERVICE_STATE` enum('ON','OFF','CONNECTING') NOT NULL, `COUNT_RECEIVED_HEARTBEATS` bigint unsigned NOT NULL DEFAULT '0', `LAST_HEARTBEAT_TIMESTAMP` timestamp(6) NOT NULL COMMENT 'Shows when the most recent heartbeat signal was received.', `RECEIVED_TRANSACTION_SET` longtext NOT NULL, `LAST_ERROR_NUMBER` int NOT NULL, `LAST_ERROR_MESSAGE` varchar(1024) NOT NULL, `LAST_ERROR_TIMESTAMP` timestamp(6) NOT NULL, `LAST_QUEUED_TRANSACTION` char(57) DEFAULT NULL, `LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP` timestamp(6) NOT NULL, `LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP` timestamp(6) NOT NULL, `LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP` timestamp(6) NOT NULL, `LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP` timestamp(6) NOT NULL, `QUEUEING_TRANSACTION` char(57) DEFAULT NULL, `QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP` timestamp(6) NOT NULL, `QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP` timestamp(6) NOT NULL, `QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP` timestamp(6) NOT NULL, PRIMARY KEY (`CHANNEL_NAME`), KEY `THREAD_ID` (`THREAD_ID`) ) ENGINE=innodb DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; --------------------------------------------------------------------------------------------------------- Copy rows: mysql> insert into replication_connection_configuration select * from performance_schema.replication_connection_configuration; mysql> insert into replication_connection_status select * from performance_schema.replication_connection_status; --------------------------------------------------------------------------------------------------------- Execute rows in both dbs: Perf schema: mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from performance_schema.replication_connection_configuration rcc -> left join performance_schema.replication_connection_status rcs on rcc.channel_name=rcs.channel_name ; +----------------------------------------------------+----------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------+-----------------------------------+ | 12345678901234567890123456789012345678901234567890 | NULL | NULL | | 12345678901234567890 | 12345678901234567890 | 1 | +----------------------------------------------------+----------------------+-----------------------------------+ 2 rows in set (0.00 sec) mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from performance_schema.replication_connection_configuration rcc -> left join performance_schema.replication_connection_status rcs on rcs.channel_name<=>rcc.channel_name; +----------------------------------------------------+----------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------+-----------------------------------+ | 12345678901234567890123456789012345678901234567890 | NULL | NULL | | 12345678901234567890 | 12345678901234567890 | 1 | +----------------------------------------------------+----------------------+-----------------------------------+ 2 rows in set (0.00 sec) mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from performance_schema.replication_connection_configuration rcc -> left join performance_schema.replication_connection_status rcs on binary rcc.channel_name=rcs.channel_name; +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 | | 12345678901234567890 | 12345678901234567890 | 1 | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ 2 rows in set (0.00 sec) mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from performance_schema.replication_connection_configuration rcc -> left join performance_schema.replication_connection_status rcs on rcc.channel_name like rcs.channel_name; +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 | | 12345678901234567890 | 12345678901234567890 | 1 | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ 2 rows in set (0.00 sec) mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from performance_schema.replication_connection_configuration rcc -> left join performance_schema.replication_connection_status rcs on rcs.channel_name<=rcc.channel_name; +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 | | 12345678901234567890123456789012345678901234567890 | 12345678901234567890 | 0 | | 12345678901234567890 | 12345678901234567890 | 1 | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ 3 rows in set (0.00 sec) mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from performance_schema.replication_connection_configuration rcc -> left join performance_schema.replication_connection_status rcs on rcs.channel_name>=rcc.channel_name; +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 | | 12345678901234567890 | 12345678901234567890123456789012345678901234567890 | 0 | | 12345678901234567890 | 12345678901234567890 | 1 | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ 3 rows in set (0.00 sec) Test schema: mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from replication_connection_configuration rcc -> left join replication_connection_status rcs on rcc.channel_name=rcs.channel_name ; +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | 12345678901234567890 | 12345678901234567890 | 1 | | 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ 2 rows in set (0.00 sec) mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from replication_connection_configuration rcc -> left join replication_connection_status rcs on rcs.channel_name<=>rcc.channel_name; +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | 12345678901234567890 | 12345678901234567890 | 1 | | 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ 2 rows in set (0.01 sec) mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from replication_connection_configuration rcc -> left join replication_connection_status rcs on binary rcc.channel_name=rcs.channel_name; +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | 12345678901234567890 | 12345678901234567890 | 1 | | 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ 2 rows in set (0.00 sec) mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from replication_connection_configuration rcc -> left join replication_connection_status rcs on rcc.channel_name like rcs.channel_name; +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | 12345678901234567890 | 12345678901234567890 | 1 | | 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ 2 rows in set (0.00 sec) mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from replication_connection_configuration rcc -> left join replication_connection_status rcs on rcs.channel_name<=rcc.channel_name; +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | 12345678901234567890 | 12345678901234567890 | 1 | | 12345678901234567890123456789012345678901234567890 | 12345678901234567890 | 0 | | 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ 3 rows in set (0.00 sec) mysql> select rcc.channel_name,rcs.channel_name,rcc.channel_name=rcs.channel_name -> from replication_connection_configuration rcc -> left join replication_connection_status rcs on rcs.channel_name>=rcc.channel_name; +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | channel_name | channel_name | rcc.channel_name=rcs.channel_name | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ | 12345678901234567890 | 12345678901234567890 | 1 | | 12345678901234567890 | 12345678901234567890123456789012345678901234567890 | 0 | | 12345678901234567890123456789012345678901234567890 | 12345678901234567890123456789012345678901234567890 | 1 | +----------------------------------------------------+----------------------------------------------------+-----------------------------------+ 3 rows in set (0.00 sec) Suggested fix: For joins with a PK larger than 48 characters, it seems that it trims the join condition while joining for perf schema tables. Suggested fix, query results should be consistent whether query is executed against perf schema and non perf schema tables