From
mysqldump option name ‘–compatible’ as documented in mysql developer site
We have –
- –compatible
Produce output that is more compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options. To use several values, separate them by commas. These values have the same meaning as the corresponding options for setting the server SQL mode. See Section 5.1.10, “Server SQL Modes”.
This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. For example, –compatible=oracle does not map data types to Oracle types or use Oracle comment syntax.
Here, I have a query –
mysqldump -u admin -p database1 --compatible=oracle > /home/payel/Documents/Unimportant/MySQLBackUp/test.sql
Invalid mode to --compatible: oracle
Why am I getting this error?
Update:
Only ansi mode is not giving invalid mode error, but it is giving a separate error –
mysqldump -u admin -p database3 --compatible=ansi > /home/payel/Documents/Unimportant/MySQLBackUp/test.sql
Enter password:
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
Even after turning off only_full_group_by the error persists –
SET sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
The rest all the options gives invalid mode error
>Solution :
You are showing a link to the documentation for the very old MySQL 5.6. The documentation for the current MySQL 8 says the only allowed value now is ansi. If ansi format is not good enough for you, you could install the mariadb client tools (which I believe can connect to a MySQL 8 server) and use mariadb-dump which still has the oracle option (but note that it does not actually produce oracle data types or comment syntax).