MySQL Bugs: #118791: mysqldump --order-by-primary is sorting by all table indices instead of just the primary key

Bug #118791	mysqldump --order-by-primary is sorting by all table indices instead of just the primary key
Submitted:	6 Aug 2025 13:17	Modified:	13 Jan 22:09
Reporter:	Larry Philps	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	8.4.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
When using the --order-by-primary flag of mysqldump in version 8.4, it is issuing a sql command that requests that the data be ordered by "every" index on the table instead of just the primary key.

In addition to being inefficient and slow, since the table is unlikely to have such a compound index on it, if the table contains rows with data that exceeds the sort buffer size, mysqldump will crash.

How to repeat:
CREATE DATABASE db1;

CREATE TABLE `db1`.`foo` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `col1` INT UNSIGNED NOT NULL,
  `col2` INT UNSIGNED NOT NULL,
  `data` JSON DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_col1` (`col1`),
  KEY `idx_col2` (`col2`)
);

INSERT INTO `db1`.`foo` (`col1`, `col2`, `data`) VALUES (111, 222, JSON_OBJECT('data', REPEAT('LotsOfData', 250000)));

Then run:
mysqldump --order-by-primary db1
and the output will end with:

--
-- Dumping data for table `foo`
--
-- ORDER BY:  `id`,`col1`,`col2`

LOCK TABLES `foo` WRITE;
/*!40000 ALTER TABLE `foo` DISABLE KEYS */;
mysqldump: Error 1038: Out of sort memory, consider increasing server sort buffer size when dumping table `foo` at row: 0

Note the "ORDER BY:" line in the output above listing all indices on the table

In addition, if the general log is enabled when the mysqldump command is run, the log contains:

2025-08-06T12:52:49.023697Z        10 Query     SELECT /*!40001 SQL_NO_CACHE */ * FROM `foo` ORDER BY `id`,`col1`,`col2`

again showing the incorrect ORDER BY clause.

Suggested fix:
The only workaround for the moment is to not use the --order-by-primary option at all.

Hello Larry Philps,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

Posted by developer:
 
This is a regression from Bug#35205310.

Posted by developer:
 
Added the following note to the MySQL Server 8.4.8 and 9.6.0 release notes:
	
mysqldump's --order-by-primary option sorted data by every index on the table, 
instead of just sorting by the primary key.