How can I resolve errors I receive while uploading data to or downloading data from Amazon Aurora to Amazon S3?

6 minute read
0

I want to upload data to Amazon Aurora from Amazon Simple Storage Service (Amazon S3). Or I want to download data from Amazon Aurora and upload it to Amazon S3.

Short description

Load data as a text file between Amazon Aurora and Amazon S3 by using one of these commands:

  • SELECT INTO OUTFILE S3 - Load data from Amazon Aurora into Amazon S3
  • LOAD DATA FROM S3 - Load data from Amazon S3 into Amazon Aurora

Some of the common errors you can receive when running the SELECT INTO OUTFILE S3 or LOAD DATA FROM S3 commands include:

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

Resolution

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

Run the SELECT INTO OUTFILE S3 or LOAD DATA FROM S3 commands using Amazon Aurora:

1.    Create an S3 bucket and copy the ARN.

2.    Create an AWS Identity and Access Management (IAM) policy for the S3 bucket with permissions. Specify the bucket ARN, and then grant permissions to the objects within the bucket ARN.

Run a custom policy similar to the following if you aren't using a FullS3Access policy:

{
  "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 to it.

The trust relationship looks like this:

{
  "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 Aurora.

For Aurora MySQL version 1 or 2:

Edit one of these roles aurora_select_into_s3_role or aws_default_s3_role to the value of the IAM Role ARN.

For Aurora MySQL 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 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.    Create a new user by running the following command:

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

8.    Grant permissions to the user by running the following command:

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

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'@'%';

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 the activate_all_roles_on_login can be turned on by using DB cluster parameter. This automatically activate all roles when a user connects to a DB instance. For more information, see Granting privileges to load data in Amazon Aurora MySQL.

9.    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

SELECT * from test1.test2034 INTO OUTFILE S3 '"s3://tests3saveloadaurora/testfile1prefix.part_00000';
Error Code: 1871. S3 API returned error: Missing Credentials: Cannot instantiate S3 Client

You can receive this error if:

  • The IAM role attachment to the DB cluster is missing. See step 5 in the previous section to fix this issue.
  • The role ARN is not specified, and only the role name is listed in the parameter group. See step 2 in the previous section to resolve this.

Incorrect Command: missing file/prefix/manifest keyword

If you receive the error "Incorrect Command: missing file/prefix/manifest keyword" then you haven't entered the correct keyword for the command to run successfully. To fix this error, enter the keyword for the command.

Error code: 1045. Access denied error

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

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

Make sure that the keyword S3 has been entered in the query. This command is written correctly:

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

Error code: 1815. Internal error: Unable to initialize

LOAD DATA FROM S3 's3://tests3saveloadaurora/testfile1prefix.part_00000' INTO TABLE test3000;
Error Code: 1815. Internal error: Unable to initialize S3Stream

There are a number of reasons you might receive the "Error code: 1815. Internal error: Unable to initialize" error.

  • Your file doesn't exist. Confirm that the file exists in your S3 bucket, and that the name that you specified in your script matches the name of the file. Case sensitivity is important. Confirm that the S3 bucket name, the folder name, and the object name match exactly what is specified in the load command.
  • You lack the appropriate permissions on the file, folder, or bucket level. Review step 2 to make sure you have set the correct permissions.
  • You might have a syntax error in the LOAD script. Review your LOAD script, and then re-run the command.
  • The S3 bucket and DB cluster are in different Regions, and the path to the S3 bucket is missing the Region value. For more information on AWS Regions, see Amazon S3 endpoints and quotas.
  • The network configuration for the reader and writer instances are different. For more information, see Using the reader endpoint.

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

You receive the "Error Code: 1871. S3 API returned error: Access Denied" error if you have encryption on your S3 bucket. Or you can receive this error if there is an encrypted file inside your S3 bucket. If the ServerSideEncryptionConfigurationExists isn't false, add kms:* to the policy you've attached to the IAM role used to perform the LOAD operation. For example, use the following example custom policy if you aren't using a FULLS3Access 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>/*"
      ]
    }
  ]
}

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

AWS OFFICIAL
AWS OFFICIALUpdated a year ago
2 Comments

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

The bucket doesn´t exist or invalid arn. Check the right ARN for the bucket.

Samuel
replied 2 months ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

profile pictureAWS
MODERATOR
replied 2 months ago