Bug #108582 mysqldump with --compatible=ANSI displays error
Submitted: 22 Sep 2022 17:57 Modified: 23 Sep 2022 12:43
Reporter: Power Gamer Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[22 Sep 2022 17:57] Power Gamer
On any database (including an empty one) the command:
mysqldump -u USERNAMEHERE --compatible=ANSI DBNAMEHERE >result.txt
displays the following error message in console:

"mysqldump: Error: 'Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'information_schema.FILES.EXTRA' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by' when trying to dump tablespaces"

Notice that the message is not displayed without --compatible=ANSI option or when the output of mysqldump is not redirected to a file (without ">result.txt").

Tested under Windows 10.

How to repeat:
Under Windows run the following command from command prompt for any database:

mysqldump -u USERNAMEHERE --compatible=ANSI DBNAMEHERE >result.txt

Ensure the option "--compatible=ANSI" is specified and the output of the command is redirected to a file (">result.txt").

Suggested fix:
Rewrite the query mysqldump uses internally to be ANSI mode compatible.
[23 Sep 2022 11:34] MySQL Verification Team
Hi Mr. Power Gamer,

Thank you for your bug report.

However, 8.0.23 is too old release of 8.0 and we need to get reports for the latest releases, for example, 8.0.30.

If you get the same error with 8.0.30 server and mysqldump versions, please create a new bug report and include in it a dump of the table that causes the error.

[23 Sep 2022 12:22] Power Gamer
Just checked it on MySQL 8.0.30 and absolutely the same issue is still present. Also, as I mentioned in my original comment, the issue happens even on an EMPTY user database so there is no table for me to dump.

Do you still want me to copy paste everything from here into a new bug report or can you just reopen this one?
[23 Sep 2022 12:43] MySQL Verification Team
Hi Mr. Power Gamer,

It turns out that you are correct.

You can no longer use ANSI option for mysqldump, due to this bug. This is due to the reason that ANSI option enforces  ONLY_FULL_GROUP_BY.

We do not know whether that particular query will be changed in mysqldump, or that ANSI mode will be disabled. This, however, has nothing to do with the fact that you are correct regarding the ANSI option.

This report is now a verified bug.