Mysqldump error in Aurora RDS

Question

When I execute:

```
mysqldump --ssl-verify-server-cert=false -h xxxxx-mysql-main.cluster-yyy.eu-west-2.rds.amazonaws.com -u root -p databasename > output.sql
```

I receive the next error:

```
mysqldump: Couldn't execute '/*!100100 SET @@MAX_STATEMENT_TIME=0.000000 */': You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0 SET @@MAX_STATEMENT_TIME=0.000000 */' at line 1 (1064)
```

What's the problem?

Vitor Castellani · Answer

Hello Miguel!

The error you're encountering is related to the use of `@@MAX_STATEMENT_TIME` which is not recognized or supported in your MySQL version or Aurora MySQL setup. This might be due to differences in the MySQL version compatibility or a specific feature set in Aurora MySQL.

Here's how you can resolve the issue:

1. **Update `mysqldump` Utility**:
   Ensure that you are using the latest version of the `mysqldump` utility. An outdated version might not be fully compatible with your Aurora RDS instance.

2. **Disable MAX_STATEMENT_TIME**:
   You can disable the `MAX_STATEMENT_TIME` by adding the `--set-gtid-purged=OFF` and `--skip-max-statement-time` options to your `mysqldump` command. These options will skip the problematic setting.

Here’s the updated command:
   ```bash
   mysqldump --ssl-verify-server-cert=false -h xxxxx-mysql-main.cluster-yyy.eu-west-2.rds.amazonaws.com -u root -p --set-gtid-purged=OFF --skip-max-statement-time databasename > output.sql
   ```

3. **Check MySQL Version Compatibility**:
   Verify the MySQL version of your Aurora RDS instance and ensure that it is compatible with the `mysqldump` utility you are using. You can check the version by running:
   ```sql
   SELECT VERSION();
   ```

Adjust your `mysqldump` options based on the specific version of MySQL or Aurora MySQL you are using.

4. **Additional Compatibility Options**:
   If the issue persists, you might consider using additional compatibility options provided by `mysqldump`. For instance, the `--compatible` option can be used to specify the target MySQL version or server:
   ```bash
   mysqldump --ssl-verify-server-cert=false -h xxxxx-mysql-main.cluster-yyy.eu-west-2.rds.amazonaws.com -u root -p --compatible=mysql40 databasename > output.sql
   ```

By making these adjustments, you should be able to avoid the SQL syntax error related to `@@MAX_STATEMENT_TIME` and successfully perform the `mysqldump`.

Feel free to ask if you have any further questions or need more assistance!

---

Miguel · Answer

Never managed to solve it in my machine. So I created an EC2 with Amazon Linux 2023. Then I installed the client:

```
sudo dnf install mariadb105
mariadb-dump -h xxxxx-mysql-main.cluster-yyy.eu-west-2.rds.amazonaws.com -u root -p databasename > output.sql
```

It works.

Mysqldump error in Aurora RDS

답변 추가

관련 콘텐츠