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