Query using up all temp disc space - but only on one server

Question

I've run into a strange situation where a query uses up all available disc space on one server (and then crashes, when there is no space left), but not on the other, and I'm not sure how to correct this situation.

On the OLD server, running MySQL 5.5.55, the query uses up about 8 GB of disc space. It returns results after about 10 minutes.

On the NEW server, running MySQL 5.7.25, the query used 15 GB and then ran out of disc space and terminated. A co-worker said that when there was more disc space available a little while ago, they observed the query using up closer to 30 GB before running out of space.

The data is the same on both servers.

The query is:

SELECT distinct subq1.duplicate_count, subq1.identifier, subq1.ReferenceEntity_DB_IDs, subq1._class AS object_type, subq1._displayName as display_name, subq1.combined_identifier, ReferenceDatabase_2_name.name as ref_db_name, subq1.species_db_id, subq1.species_name  
from ( 
    select count(ReferenceEntity.DB_ID) as duplicate_count, ReferenceEntity.identifier, group_concat(ReferenceEntity.db_id) as ReferenceEntity_DB_IDs, ReferenceEntity.referenceDatabase, DatabaseObject._class, DatabaseObject._displayName, subq.combined_identifier, subq.species_db_id, subq.species_name 
    from ReferenceEntity  
    inner join DatabaseObject on DatabaseObject.db_id = ReferenceEntity.db_id 
    inner join (select ReferenceEntity.*, concat(coalesce(ReferenceEntity.identifier,'NULL'),';', coalesce(DatabaseObject._displayName,'NULL')) as combined_identifier, species_subq.db_id AS species_db_id, species_subq.name AS species_name 
                from ReferenceEntity 
                inner join DatabaseObject on ReferenceEntity.db_id = DatabaseObject.db_id 
               LEFT OUTER JOIN ReferenceSequence ON ReferenceEntity.DB_ID = ReferenceSequence.DB_ID 
               LEFT OUTER JOIN (SELECT Species.DB_ID, Taxon_2_name.name 
                                   FROM Species  
                                   INNER JOIN Taxon_2_name ON (Taxon_2_name.DB_ID = Species.DB_ID AND Taxon_2_name.name_rank = 0)) AS species_subq 
               ON ReferenceSequence.species = species_subq.DB_ID 
                where ReferenceEntity.identifier is not null) as subq on subq.db_id = ReferenceEntity.db_id 
    group by subq.combined_identifier, ReferenceEntity.identifier, referenceDatabase, _class, _displayName, subq.species_db_id, subq.species_name 
    having count(ReferenceEntity.db_id) > 1) as subq1 
inner join ReferenceDatabase_2_name on ReferenceDatabase_2_name.DB_ID = subq1.referenceDatabase 
where ReferenceDatabase_2_name.name_rank = 0 
order by duplicate_count, ReferenceDatabase_2_name.name, identifier;

The query plan from the NEW server (where the query fails) looks like this:

+----+-------------+--------------------------+------------+--------+--------------------+-------------+---------+--------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table                    | partitions | type   | possible_keys      | key         | key_len | ref                            | rows   | filtered | Extra                                        |
+----+-------------+--------------------------+------------+--------+--------------------+-------------+---------+--------------------------------+--------+----------+----------------------------------------------+
|  1 | PRIMARY     | ReferenceDatabase_2_name | NULL       | ALL    | DB_ID              | NULL        | NULL    | NULL                           |    292 |    10.00 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | <derived2>               | NULL       | ref    | <auto_key0>        | <auto_key0> | 5       | ReferenceDatabase_2_name.DB_ID |   2526 |   100.00 | NULL                                         |
|  2 | DERIVED     | ReferenceEntity          | NULL       | ALL    | PRIMARY            | NULL        | NULL    | NULL                           | 737849 |   100.00 | Using temporary; Using filesort              |
|  2 | DERIVED     | DatabaseObject           | NULL       | eq_ref | PRIMARY            | PRIMARY     | 4       | ReferenceEntity.DB_ID          |      1 |   100.00 | Using index condition                        |
|  2 | DERIVED     | ReferenceEntity          | NULL       | eq_ref | PRIMARY,identifier | PRIMARY     | 4       | ReferenceEntity.DB_ID          |      1 |   100.00 | Using where                                  |
|  2 | DERIVED     | ReferenceSequence        | NULL       | eq_ref | PRIMARY            | PRIMARY     | 4       | ReferenceEntity.DB_ID          |      1 |   100.00 | NULL                                         |
|  2 | DERIVED     | Species                  | NULL       | eq_ref | PRIMARY            | PRIMARY     | 4       | ReferenceSequence.species      |      1 |   100.00 | Using index                                  |
|  2 | DERIVED     | Taxon_2_name             | NULL       | ref    | DB_ID              | DB_ID       | 5       | ReferenceSequence.species      |      1 |   100.00 | Using where                                  |
|  2 | DERIVED     | DatabaseObject           | NULL       | eq_ref | PRIMARY            | PRIMARY     | 4       | ReferenceEntity.DB_ID          |      1 |   100.00 | Using index condition                        |
+----+-------------+--------------------------+------------+--------+--------------------+-------------+---------+--------------------------------+--------+----------+----------------------------------------------+

The query plan from the OLD server (where the query succeeds) is different, and it looks like this:

+----+-------------+--------------------------+--------+--------------------+---------+---------+----------------------------------------+--------+---------------------------------+
| id | select_type | table                    | type   | possible_keys      | key     | key_len | ref                                    | rows   | Extra                           |
+----+-------------+--------------------------+--------+--------------------+---------+---------+----------------------------------------+--------+---------------------------------+
|  1 | PRIMARY     | <derived2>               | ALL    | NULL               | NULL    | NULL    | NULL                                   |   4169 | Using temporary; Using filesort |
|  1 | PRIMARY     | ReferenceDatabase_2_name | ref    | DB_ID              | DB_ID   | 5       | subq1.referenceDatabase                |      2 | Using where                     |
|  2 | DERIVED     | <derived3>               | ALL    | NULL               | NULL    | NULL    | NULL                                   | 737849 | Using temporary; Using filesort |
|  2 | DERIVED     | ReferenceEntity          | eq_ref | PRIMARY            | PRIMARY | 4       | subq.DB_ID                             |      1 |                                 |
|  2 | DERIVED     | DatabaseObject           | eq_ref | PRIMARY            | PRIMARY | 4       | test_database_XX.ReferenceEntity.DB_ID |      1 | Using where                     |
|  3 | DERIVED     | ReferenceEntity          | ALL    | PRIMARY,identifier | NULL    | NULL    | NULL                                   | 737849 | Using where                     |
|  3 | DERIVED     | ReferenceSequence        | eq_ref | PRIMARY            | PRIMARY | 4       | test_database_XX.ReferenceEntity.DB_ID |      1 |                                 |
|  3 | DERIVED     | <derived4>               | ALL    | NULL               | NULL    | NULL    | NULL                                   |     79 |                                 |
|  3 | DERIVED     | DatabaseObject           | eq_ref | PRIMARY            | PRIMARY | 4       | test_database_XX.ReferenceEntity.DB_ID |      1 | Using where                     |
|  4 | DERIVED     | Species                  | index  | PRIMARY            | PRIMARY | 4       | NULL                                   |     79 | Using index                     |
|  4 | DERIVED     | Taxon_2_name             | ref    | DB_ID              | DB_ID   | 5       | test_database_XX.Species.DB_ID         |      1 | Using where                     |
+----+-------------+--------------------------+--------+--------------------+---------+---------+----------------------------------------+--------+---------------------------------+

My guess is that there is some significant difference in how these two instances of MySQL are configured, but I'm not sure. the MySQL 5.7.25 server was set up with a config split into several files, and since it's not the same version, I'm not sure how to properly compare.

Does anyone have any suggestions for debugging this issue? Maybe in terms of which variables to compare first?

Edit:

Global variables from NEW server: https://pastebin.com/EacpyyAb

Global variables from OLD server: https://pastebin.com/uSj7bEbt

Global status from NEW server: https://pastebin.com/CD0g3qSb

Global status from OLD server: https://pastebin.com/ETnTbKA1

Indicies, OLD server:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
ReferenceDatabase_2_name    1   DB_ID   1   DB_ID   A   146 NULL    NULL    YES BTREE       
ReferenceDatabase_2_name    1   name    1   name    A   97  10  NULL    YES BTREE       

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
ReferenceEntity 0   PRIMARY 1   DB_ID   A   737849  NULL    NULL        BTREE       
ReferenceEntity 1   referenceDatabase   1   referenceDatabase   A   90  NULL    NULL    YES BTREE       
ReferenceEntity 1   identifier  1   identifier  A   245949  10  NULL    YES BTREE       

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
DatabaseObject  0   PRIMARY 1   DB_ID   A   2439461 NULL    NULL        BTREE       
DatabaseObject  1   _class  1   _class  A   65  NULL    NULL    YES BTREE       
DatabaseObject  1   _timestamp  1   _timestamp  A   45175   NULL    NULL        BTREE       
DatabaseObject  1   created 1   created A   187650  NULL    NULL    YES BTREE       
DatabaseObject  1   _displayName    1   _displayName    A   304932  10  NULL    YES BTREE       
DatabaseObject  1   stableIdentifier    1   stableIdentifier    A   2439461 NULL    NULL    YES BTREE       

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
ReferenceSequence   0   PRIMARY 1   DB_ID   A   735705  NULL    NULL        BTREE       
ReferenceSequence   1   species 1   species A   52  NULL    NULL    YES BTREE       
ReferenceSequence   1   sequenceLength  1   sequenceLength  A   735705  NULL    NULL    YES BTREE       
ReferenceSequence   1   isSequenceChanged   1   isSequenceChanged   A   735705  10  NULL    YES BTREE       
ReferenceSequence   1   checksum    1   checksum    A   735705  10  NULL    YES BTREE       

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
Species 0   PRIMARY 1   DB_ID   A   79  NULL    NULL        BTREE       
Species 1   abbreviation    1   abbreviation    A   79  10  NULL    YES BTREE       

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
Taxon_2_name    1   DB_ID   1   DB_ID   A   551 NULL    NULL    YES BTREE       
Taxon_2_name    1   name    1   name    A   551 NULL    NULL    YES BTREE

NEW Server:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
ReferenceDatabase_2_name    1   DB_ID   1   DB_ID   A   146 NULL    NULL    YES BTREE       
ReferenceDatabase_2_name    1   name    1   name    A   97  10  NULL    YES BTREE       

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
ReferenceEntity 0   PRIMARY 1   DB_ID   A   737849  NULL    NULL        BTREE       
ReferenceEntity 1   referenceDatabase   1   referenceDatabase   A   90  NULL    NULL    YES BTREE       
ReferenceEntity 1   identifier  1   identifier  A   245950  10  NULL    YES BTREE       

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
DatabaseObject  0   PRIMARY 1   DB_ID   A   2439461 NULL    NULL        BTREE       
DatabaseObject  1   _class  1   _class  A   65  NULL    NULL    YES BTREE       
DatabaseObject  1   _timestamp  1   _timestamp  A   45175   NULL    NULL        BTREE       
DatabaseObject  1   created 1   created A   187651  NULL    NULL    YES BTREE       
DatabaseObject  1   _displayName    1   _displayName    A   304933  10  NULL    YES BTREE       
DatabaseObject  1   stableIdentifier    1   stableIdentifier    A   2439461 NULL    NULL    YES BTREE       

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
ReferenceSequence   0   PRIMARY 1   DB_ID   A   735705  NULL    NULL        BTREE       
ReferenceSequence   1   species 1   species A   52  NULL    NULL    YES BTREE       
ReferenceSequence   1   sequenceLength  1   sequenceLength  A   735705  NULL    NULL    YES BTREE       
ReferenceSequence   1   isSequenceChanged   1   isSequenceChanged   A   735705  10  NULL    YES BTREE       
ReferenceSequence   1   checksum    1   checksum    A   735705  10  NULL    YES BTREE       

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
Species 0   PRIMARY 1   DB_ID   A   79  NULL    NULL        BTREE       
Species 1   abbreviation    1   abbreviation    A   79  10  NULL    YES BTREE       

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
Taxon_2_name    1   DB_ID   1   DB_ID   A   551 NULL    NULL    YES BTREE       
Taxon_2_name    1   name    1   name    A   551 NULL    NULL    YES BTREE

The NEW server doesn't log the error. The only message that it emits is ERROR 3 (HY000): Error writing file '/tmp/MYIZQlbr' (Errcode: 28 - No space left on device) I am not sure I have permission to change the error logging settings...

SHOW CREATE TABLE from OLD server:

Table   Create Table
ReferenceDatabase_2_name        CREATE TABLE `ReferenceDatabase_2_name` (
  `DB_ID` int(10) unsigned DEFAULT NULL,
  `name_rank` int(10) unsigned DEFAULT NULL,
  `name` mediumtext COLLATE utf8_unicode_ci,
  KEY `DB_ID` (`DB_ID`),
  KEY `name` (`name`(10))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Table   Create Table
ReferenceEntity CREATE TABLE `ReferenceEntity` (
  `DB_ID` int(10) unsigned NOT NULL DEFAULT '0',
  `identifier` mediumtext COLLATE utf8_unicode_ci,
  `referenceDatabase` int(10) unsigned DEFAULT NULL,
  `referenceDatabase_class` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`DB_ID`),
  KEY `referenceDatabase` (`referenceDatabase`),
  KEY `identifier` (`identifier`(10))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Table   Create Table
DatabaseObject  CREATE TABLE `DatabaseObject` (
  `DB_ID` int(10) NOT NULL AUTO_INCREMENT,
  `_class` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `_displayName` mediumtext COLLATE utf8_unicode_ci,
  `_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` int(10) unsigned DEFAULT NULL,
  `created_class` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `stableIdentifier` int(10) unsigned DEFAULT NULL,
  `stableIdentifier_class` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`DB_ID`),
  KEY `_class` (`_class`),
  KEY `_timestamp` (`_timestamp`),
  KEY `created` (`created`),
  KEY `_displayName` (`_displayName`(10)),
  KEY `stableIdentifier` (`stableIdentifier`)
) ENGINE=MyISAM AUTO_INCREMENT=11631469 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Table   Create Table
ReferenceSequence       CREATE TABLE `ReferenceSequence` (
  `DB_ID` int(10) unsigned NOT NULL DEFAULT '0',
  `species` int(10) unsigned DEFAULT NULL,
  `species_class` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sequenceLength` int(10) DEFAULT NULL,
  `isSequenceChanged` mediumtext COLLATE utf8_unicode_ci,
  `checksum` mediumtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`DB_ID`),
  KEY `species` (`species`),
  KEY `sequenceLength` (`sequenceLength`),
  KEY `isSequenceChanged` (`isSequenceChanged`(10)),
  KEY `checksum` (`checksum`(10))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Table   Create Table
Species CREATE TABLE `Species` (
  `DB_ID` int(10) unsigned NOT NULL DEFAULT '0',
  `abbreviation` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`DB_ID`),
  KEY `abbreviation` (`abbreviation`(10))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Table   Create Table
Taxon_2_name    CREATE TABLE `Taxon_2_name` (
  `DB_ID` int(10) unsigned DEFAULT NULL,
  `name_rank` int(10) unsigned DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  KEY `DB_ID` (`DB_ID`),\n  KEY `name` (`name`)\n) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

SHOW CREATE TABLE from NEW server is identical to OLD server.

Please post SHOW GLOBAL VARIABLES; from OLD and NEW server for comparison analysis of GV OLD to NEW. Also post your error log from the NEW server that includes data from the attempt to use the query described. The query plans posted are now where similar in ROWS used by each part of the plan. — Wilson Hauck, Apr 22, 2019 at 0:11
@WilsonHauck: Those are large dumps. Anything that can be safely filtered out? — FrustratedWithFormsDesigner, Apr 25, 2019 at 19:02
@WilsonHauck: Trying to paste the FULL dump of global variables from both servers is exceeding the max content size. Do you have suggestions as to what I should filter for? — FrustratedWithFormsDesigner, Apr 25, 2019 at 19:12
Please consider posting your results to pastebin.com and sharing the links and you will avoid the 'content size limit'. Thanks, — Wilson Hauck, Apr 25, 2019 at 19:44

Rick James · Accepted Answer · 2019-04-30 21:33:46Z

1

Ouch!

key_buffer_size 16777216

Set that to about 20% of available RAM.

join_buffer_size = 1M

tmp_table_size and max_heap_table_size  -- about 1% of RAM

Note: When (I'm not saying "if") you change to InnoDB, be sure to lower key_buffer_size down again, while raising innodb_buffer_pool_size to 70% of RAM.

(More after I review the VARIABLES and STATUS)

Check the engines -- none of the 5.5 is using InnoDB; some of 5.7 is. Did these tables change when moving to 5.7??

For transitioning from MyISAM to InnoDB on a 32GB MySQL-only server:

key_buffer_size = 3G
innodb_buffer_pool_size = 10G

There are some nasty queries, both in the old system and new.

long_query_time = 1

and turn on the slowlog. Then get help here: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

edited Apr 30, 2019 at 21:33

answered Apr 30, 2019 at 20:59

Rick James

73.8k4 gold badges41 silver badges102 bronze badges

1

@FrustratedWithFormsDesigner - I added to my Answer.
– Rick James
Apr 30, 2019 at 21:16
I'll have a look at the engines. I did not do the initial setup. I suspect it was just a default installation, so if some defaults were InnoDB, maybe that's the problem and I need to switch them back to MyISAM... ? It's a lead, and I'm not sure I would have seen that myself. Thanks!
– FrustratedWithFormsDesigner
Apr 30, 2019 at 21:20
1

@FrustratedWithFormsDesigner - Most important things for now is to change those two buffer sizes (and restart mysqld). It is hard to predict how much it will help, and it is hard to predict whether some of the other "problems" will go away.
– Rick James
Apr 30, 2019 at 21:36

Add a comment |

Wilson Hauck · Accepted Answer · 2019-05-03 16:04:17Z

Suggestions to consider for your my.cnf [mysqld] section

max_heap_table_size=320M  # from 16M for ~ 1% RAM
tmp_table_size=320M  # from 16M for size matched to MHTSize
thread_cache_size=64  # from 8 to reduce threads_created count
sort_buffer_size=2M  # from 32M to conserve RAM requested per connection
slow_query_log=ON  # from OFF to allow daily monitoring
query_cache_size=0  # from 16M since Query_Cache_Type is OFF
lock_wait_timeout=300  # from 1 year of seconds, when REPLICATION not used
key_buffer_size=10G  # from 16M - all ISAM data tables
key_age_threshold=7200  # from 300 seconds to reduce key_reads
key_cache_block_size=16382  # from 1024 to reduce CPU cycles used for mgmt
key_cache_division_limit=50  # from 100 for HOT/WARM split to reduce key_reads
innodb_lru_scan_depth=100  # from 1024 to conserve CPU cycles every second for function

Disclaimer: I am the content author of web site listed in my profile, Network profile with contact information.

Rick James · Accepted Answer · 2019-05-01 19:32:45Z

0

A possible way to get 5.7 to optimize like 5.5 did for this one statement...

First take a look at

SHOW VARIABLES LIKE 'optimizer_switch';

You will set a lot of settings. I think one of them controls a new feature that is not working well for your query. I do not know if it is the only relevant one. See if the EXPLAIN reverts when you do this:

SET SESSION optimizer_switch='materialization=off';
EXPLAIN SELECT ...
SET SESSION optimizer_switch='materialization=on';

If so, then add those SETs around your SELECT.

answered May 1, 2019 at 19:32

Rick James

73.8k4 gold badges41 silver badges102 bronze badges

I tried this, the execution plan remained the same.
– FrustratedWithFormsDesigner
May 17, 2019 at 14:43

Add a comment |

FrustratedWithFormsDesigner · Accepted Answer · 2019-05-17 19:25:08Z

First off, thanks to Rick James and Wilson Hauck who tried to help with this. It is greatly appreciated, and shows I still have much to learn in terms of fine-tuning MySQL databases.

I tried most of the suggestions above, alone and in combination, and so far, none of them have worked. :(

I did more research and happened upon a web page (lost the link, sorry) that described similar problems that I was having, with the group_concat function. I tried running the inner query with the group_concat as a stand-alone query, and found that when I removed the group_concat, I didn't have any problems! One suggestion on that other page was to change the ORDER BY of the query making use of group_concat. I noticed that I didn't have a sorting clause. I added one, and the query worked!! Query now looks more like this:

SELECT distinct subq1.duplicate_count, subq1.identifier, subq1.ReferenceEntity_DB_IDs, subq1._class AS object_type, subq1._displayName as display_name, subq1.combined_identifier, ReferenceDatabase_2_name.name as ref_db_name, subq1.species_db_id, subq1.species_name  
from ( 
    select count(ReferenceEntity.DB_ID) as duplicate_count, ReferenceEntity.identifier, group_concat(ReferenceEntity.db_id) as ReferenceEntity_DB_IDs, ReferenceEntity.referenceDatabase, DatabaseObject._class, DatabaseObject._displayName, subq.combined_identifier, subq.species_db_id, subq.species_name 
    from ReferenceEntity  
    inner join DatabaseObject on DatabaseObject.db_id = ReferenceEntity.db_id 
    inner join (select ReferenceEntity.*, concat(coalesce(ReferenceEntity.identifier,'NULL'),';', coalesce(DatabaseObject._displayName,'NULL')) as combined_identifier, species_subq.db_id AS species_db_id, species_subq.name AS species_name 
                from ReferenceEntity 
                inner join DatabaseObject on ReferenceEntity.db_id = DatabaseObject.db_id 
               LEFT OUTER JOIN ReferenceSequence ON ReferenceEntity.DB_ID = ReferenceSequence.DB_ID 
               LEFT OUTER JOIN (SELECT Species.DB_ID, Taxon_2_name.name 
                                   FROM Species  
                                   INNER JOIN Taxon_2_name ON (Taxon_2_name.DB_ID = Species.DB_ID AND Taxon_2_name.name_rank = 0)) AS species_subq 
               ON ReferenceSequence.species = species_subq.DB_ID 
                where ReferenceEntity.identifier is not null) as subq on subq.db_id = ReferenceEntity.db_id 
    group by subq.combined_identifier, ReferenceEntity.identifier, referenceDatabase, _class, _displayName, subq.species_db_id, subq.species_name 
    having count(ReferenceEntity.db_id) > 1
    -- The ORDER BY below is *NEW*
    order by ReferenceEntity.identifier desc ) as subq1 
inner join ReferenceDatabase_2_name on ReferenceDatabase_2_name.DB_ID = subq1.referenceDatabase 
where ReferenceDatabase_2_name.name_rank = 0 
order by duplicate_count, ReferenceDatabase_2_name.name, identifier;

So I guess for MySQL 5.7.26 (it seems someone upgraded recently), the results need to be sorted for group_concat.

What seems weird to me is that it seems to work with any sorting. I even tried ORDER BY rand() desc and the query ran just as well as when I specific an actual column name.

Now I'm wondering if there's a bug in MySQL group_concat...

Stack Exchange Network

Query using up all temp disc space - but only on one server

4 Answers 4

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
or ask your own question.

Hot Network Questions

Query using up all temp disc space - but only on one server

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysql or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
or ask your own question.