How do I resolve errors when I load data between Amazon Aurora and Amazon S3?

Short description

To load data as a text file between Amazon Aurora and Amazon S3, use the SELECT INTO OUTFILE S3 command or the LOAD DATA FROM S3 command. When you run either command, then you might receive errors such as the following:

1. Error Code: 1871. Missing Credentials
2. Incorrect Command: missing file/prefix/manifest keyword
3. Error code: 1045. Access denied error
4. Error code: 1815. Internal error: Unable to initialize
5. Error Code: 1871. S3 API returned error: Access Denied

Resolution

Run the SELECT INTO OUTFILE S3 or LOAD DATA FROM S3 command

Complete the following steps:

1. Create an S3 bucket and copy the ARN.

2. Create an AWS Identity and Access Management (IAM) policy with permissions for the S3 bucket. Specify the bucket ARN, and then grant permissions to the objects within the bucket ARN. If you aren't using a FullS3Access policy, then run a custom policy similar to the following:

   {  "Version": "2012-10-17",
     "Statement": [
       {
         "Sid": "VisualEditor0",
         "Effect": "Allow",
         "Action": "s3:*",
         "Resource": [
           "arn:aws:s3:::<your_bucket_name>",
           "arn:aws:s3:::<your_bucket_name>/*"
         ]
       }
     ]
   }

3. Create an IAM role for the Amazon Relational Database Service (Amazon RDS) service. Then, attach the IAM policy that you created. The trust relationship looks similar to the following:

   {  "Version": "2012-10-17",
     "Statement": [
       {
         "Sid": "",
         "Effect": "Allow",
         "Principal": {
           "Service": "rds.amazonaws.com"
         },
         "Action": "sts:AssumeRole"
       }
     ]
   }

4. Create a custom DB cluster parameter group for Amazon Aurora. For Amazon Aurora MySQL-Compatible Edition version 1 or 2, edit aurora_select_into_s3_role or aws_default_s3_role to the value of the IAM Role ARN. For Aurora MySQL-Compatible version 3, edit the aws_default_s3_role to the value of the IAM Role ARN.

5. Attach the IAM role to the DB cluster that you created. For more information, see Associating an IAM role with an Amazon Aurora MySQL DB cluster.

6. Log in to your Amazon Aurora DB cluster.

7. To create a new user, run the following command:

   CREATE USER 'testsaveload'@'%' IDENTIFIED BY 'somepassword';show grants for 'testsaveload'@'%';

8. To grant permissions to the user, run the following command:

   GRANT SELECT, CREATE, INSERT, DROP ON *.* TO 'testsaveload'@'%';

9. For Aurora MySQL version 1 or 2, use the following statement:

   GRANT SELECT INTO S3 ON *.* TO 'testsaveload'@'%';GRANT LOAD FROM S3 ON *.* TO 'testsaveload'@'%';

10. For Aurora MySQL version 3, use the following statement:

    GRANT AWS_SELECT_S3_ACCESS TO 'testsaveload'@'%'GRANT AWS_LOAD_S3_ACCESS TO 'testsaveload'@'%';

    Note: In Aurora MySQL version 3, use the DB cluster parameter to turn on activate_all_roles_on_login. When a user connects to a DB instance, then all roles are automatically activated. For more information, see Granting privileges to load data in Amazon Aurora MySQL.

11. Run the SELECT INTO OUTFILE S3 or the LOAD DATA INTO S3 command:

    SELECT * from test1.test2034 INTO OUTFILE S3 's3://tests3saveloadaurora/testfile1prefix.part_00000';LOAD DATA FROM S3 PREFIX 's3-us-east-1://tests3saveloadaurora/testfile1prefix.part_00000' INTO TABLE test3001;
    LOAD DATA FROM S3 's3-us-east-1://tests3saveloadaurora/prefix3.part_00000' INTO TABLE test3000;

Error Code: 1871. Missing Credentials

If the IAM role attachment to the DB cluster is missing, then see step 5 in the "Run the SELECT INTO OUTFILE S3 or LOAD DATA FROM S3 command" section. If the role ARN is not specified and only the role name is listed in the parameter group, then see step 2.

Incorrect Command: missing file/prefix/manifest keyword

If you receive this error, then make sure that the command keyword is correct.

Error code: 1045. Access denied error

If you enter an incorrect command, then you might receive an error such as "1045: Access denied for user ''testsaveload'@'%';". For example, the following command is incorrect:

SELECT * from test1.test2034 INTO OUTFILE 's3://tests3saveloadaurora/testfile1prefix.part_00000';

Confirm that the keyword S3 has been entered in the query such as the following command:

 SELECT * from test1.test2034 INTO OUTFILE S3 's3://tests3saveloadaurora/testfile1prefix.part_00000';

Error code: 1815. Internal error: Unable to initialize

If you receive this error, then complete the following steps:

1. Confirm that the file exists in your S3 bucket, and then confirm that the name you specified in your script matches the file name. Confirm that the S3 bucket, folder, and object names match the load command.
2. Review step 2 in the "Run the SELECT INTO OUTFILE S3 or LOAD DATA FROM S3 command" section to make sure that you have set the correct permissions.
3. If there is a syntax error in the LOAD script, then review your LOAD script, and then rerun the command.
4. If the S3 bucket and DB cluster are in different Regions, and the path to the S3 bucket is missing the Region value, then see Amazon Simple Storage Service endpoints and quotas for more information.
5. If the network configuration for the reader and writer instances are different, then see Amazon Aurora endpoint connections for more information.

Error Code: 1871. S3 API returned error: Access Denied

You might receive this error if you have encryption on your S3 bucket, or if there is an encrypted file inside your S3 bucket. If the ServerSideEncryptionConfigurationExists isn't false, then add kms:* to the policy that you attached to the IAM role used to perform the LOAD operation.

For example, if you aren't using a FULLS3Access policy, then use the following example custom policy:

{  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "VisualEditor0",
      "Effect": "Allow",
      "Action": [
        "s3:*",
        "kms:*"
      ],
      "Resource": [
        "arn:aws:s3:::<your_bucket_name>",
        "arn:aws:s3:::<your_bucket_name>/*"
      ]
    }
  ]
}

Error: S3 API returned error: Resource Not Found: No response body.

To resolve this error, then make sure that the ARN and bucket name are correct.

Related information

Saving data from an Amazon Aurora MySQL DB cluster into text files in an Amazon S3 bucket

Loading data into an Amazon Aurora MySQL DB cluster from text files in an Amazon S3 bucket