Bug #80777 order by on LAST_SEEN_TRANSACTION results in empty set
Submitted: 17 Mar 2016 11:07 Modified: 6 Jun 2018 14:50
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.10-log, 5.7.11, 5.7.20 OS:Any
Assigned to: CPU Architecture:Any
Tags: GTID, MTS, multi threaded, performance_schema, replication

[17 Mar 2016 11:07] Daniël van Eeden
Description:
I expect the same resultset only in a different order when I order by some column.

How to repeat:
root@server1 [(none)]> select * from performance_schema.replication_applier_status_by_worker order by LAST_SEEN_TRANSACTION;
Empty set (0.00 sec)

root@server1 [(none)]> select * from performance_schema.replication_applier_status_by_worker;
+--------------+-----------+-----------+---------------+--------------------------------------------+-------------------+--------------------+----------------------+
| CHANNEL_NAME | WORKER_ID | THREAD_ID | SERVICE_STATE | LAST_SEEN_TRANSACTION                      | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE | LAST_ERROR_TIMESTAMP |
+--------------+-----------+-----------+---------------+--------------------------------------------+-------------------+--------------------+----------------------+
|              |         1 |    947233 | ON            | 70d1ffb3-e5d1-11e5-b182-5820b1e4c770:18473 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         2 |    947234 | ON            | 70d1ffb3-e5d1-11e5-b182-5820b1e4c770:17212 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         3 |    947235 | ON            | 70d1ffb3-e5d1-11e5-b182-5820b1e4c770:17211 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         4 |    947236 | ON            | 70d1ffb3-e5d1-11e5-b182-5820b1e4c770:17210 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         5 |    947237 | ON            | 70d1ffb3-e5d1-11e5-b182-5820b1e4c770:17642 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         6 |    947238 | ON            | 70d1ffb3-e5d1-11e5-b182-5820b1e4c770:17648 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         7 |    947239 | ON            | 70d1ffb3-e5d1-11e5-b182-5820b1e4c770:18051 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         8 |    947240 | ON            | 70d1ffb3-e5d1-11e5-b182-5820b1e4c770:18515 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         9 |    947241 | ON            | 70d1ffb3-e5d1-11e5-b182-5820b1e4c770:18527 |                 0 |                    | 0000-00-00 00:00:00  |
|              |        10 |    947242 | ON            | 70d1ffb3-e5d1-11e5-b182-5820b1e4c770:18530 |                 0 |                    | 0000-00-00 00:00:00  |
+--------------+-----------+-----------+---------------+--------------------------------------------+-------------------+--------------------+----------------------+
10 rows in set (0.00 sec)

root@server1 [(none)]> select version();
+------------+
| version()  |
+------------+
| 5.7.10-log |
+------------+
1 row in set (0.00 sec)
[17 Mar 2016 13:50] MySQL Verification Team
Hello Daniël,

Thank you for the bug report.
Observed this with 5.7.11 build.

Thanks,
Umesh
[17 Mar 2016 13:52] MySQL Verification Team
test results

Attachment: 80777.results (application/octet-stream, text), 7.31 KiB.

[20 Oct 2016 19:50] Sven Sandberg
Posted by developer:
 
The debug assert happens only when slave_parallel_workers=0.

The empty result happens for SELECT...ORDER BY from the following tables:
performance_schema.replication_connection_status
performance_schema.replication_connection_configuration
performance_schema.replication_applier_status_by_coordinator
performance_schema.replication_applier_status_by_worker

(but performance_schema.replication_applier_configuration and performance_schema.replication_applier_status seem ok.)

I tested a couple of non-replication performance_schema tables and it seems to work, so this is still looking like a replication bug.

Test case:

#########################################################
--source include/master-slave.inc

--connection slave
--source include/stop_slave.inc
set global slave_parallel_workers= 2;
--source include/start_slave.inc

--connection master

CREATE TABLE t1 (a INT);
--sync_slave_with_master

set @var = 'blah';

--echo #
select * from performance_schema. events_stages_current ;
select * from performance_schema. events_stages_current 
order by event_name;

--echo #
select * from performance_schema. user_variables_by_thread  ;
select * from performance_schema. user_variables_by_thread 
order by thread_id;

--echo #
select * from  performance_schema.replication_connection_status;
select * from  performance_schema.replication_connection_status
order by SOURCE_UUID;

--echo #
select * from  performance_schema.replication_connection_configuration;
select * from  performance_schema.replication_connection_configuration
order by host;

--echo #
select * from  performance_schema.replication_applier_configuration;
select * from  performance_schema.replication_applier_configuration
order by DESIRED_DELAY;

--echo #
select * from
performance_schema.replication_applier_status_by_coordinator;
select * from
performance_schema.replication_applier_status_by_coordinator
order by thread_id;

--echo #
select * from
performance_schema.replication_applier_status;
select * from
performance_schema.replication_applier_status
order by service_state;

--echo #
select * from
performance_schema.replication_applier_status_by_worker;
select * from
performance_schema.replication_applier_status_by_worker
order by LAST_SEEN_TRANSACTION;
[12 Dec 2017 16:29] Daniël van Eeden
Updated version and tags
[12 Dec 2017 16:32] Simon Mudd
Still not resolved in 5.7.19 where I see:

root@myserver [(none)]> select * from performance_schema.replication_applier_status_by_worker;
+--------------+-----------+-----------+---------------+------------------------------------------------+-------------------+--------------------+----------------------+
| CHANNEL_NAME | WORKER_ID | THREAD_ID | SERVICE_STATE | LAST_SEEN_TRANSACTION                          | LAST_ERROR_NUMBER | LAST_ERROR_MESSAGE | LAST_ERROR_TIMESTAMP |
+--------------+-----------+-----------+---------------+------------------------------------------------+-------------------+--------------------+----------------------+
|              |         1 |        47 | ON            | 9f58c169-d121-11e7-835b-ac162db9c048:150298087 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         2 |        48 | ON            | 9f58c169-d121-11e7-835b-ac162db9c048:150298085 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         3 |        49 | ON            | 9f58c169-d121-11e7-835b-ac162db9c048:150298035 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         4 |        50 | ON            | 9f58c169-d121-11e7-835b-ac162db9c048:150297065 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         5 |        51 | ON            | 9f58c169-d121-11e7-835b-ac162db9c048:150297056 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         6 |        52 | ON            | 9f58c169-d121-11e7-835b-ac162db9c048:150295870 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         7 |        53 | ON            | 9f58c169-d121-11e7-835b-ac162db9c048:150292627 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         8 |        54 | ON            | 9f58c169-d121-11e7-835b-ac162db9c048:150292628 |                 0 |                    | 0000-00-00 00:00:00  |
|              |         9 |        55 | ON            | 9f58c169-d121-11e7-835b-ac162db9c048:150253298 |                 0 |                    | 0000-00-00 00:00:00  |
|              |        10 |        56 | ON            | 9f58c169-d121-11e7-835b-ac162db9c048:149111411 |                 0 |                    | 0000-00-00 00:00:00  |
+--------------+-----------+-----------+---------------+------------------------------------------------+-------------------+--------------------+----------------------+
10 rows in set (0.00 sec)

root@myserver [(none)]> select * from performance_schema.replication_applier_status_by_worker order by LAST_SEEN_TRANSACTION;
Empty set (0.00 sec)

root@myserver [(none)]> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.19-log |
+------------+
1 row in set (0.00 sec)

root@myserver [(none)]> select @@slave_parallel_workers;
+--------------------------+
| @@slave_parallel_workers |
+--------------------------+
|                       10 |
+--------------------------+
1 row in set (0.01 sec)

Not being able to use SQL ordering (and maybe aggregation) of this P_S table makes it less useful and an empty resultset seems somewhat surprising.
With this version I see no error reported or logged.
[6 Jun 2018 14:50] Margaret Fisher
Posted by developer:
 
Changelog entry added for MySQL 5.7.24 and 8.0.13:
If an ORDER BY clause was used in retrieving records from certain Performance Schema tables relating to replication, an empty set was returned. The issue has now been fixed.