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