| 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: | |
| 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
[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.
