0

I'm trying to import an sql file into aws RDS database the file size is 1.5 GB i'm connecting to the rds from ec2 instance then executing this command

"source sqldata.sql"

to import it into the rds then im getting this error

mysql: Out of memory (Needed 536870945 bytes)

Do i need to increase some values if so what they are?

Example sql file

SET autocommit=0;
SET foreign_key_checks=0;

START TRANSACTION;  

INSERT IGNORE INTO `orders` (`countryCode`, `date`, `id`,`productId`) 
VALUES 
    ('ru','2019-04-09 06:59',100453324298986,32829863707) ,
    ('fr','2019-04-09 05:59',100645420835625,32829863707) ,
    ('ru','2019-04-08 12:04',704482263524094,32829863707) 
    .......etc 9000 rows here at once

COMMIT;

START TRANSACTION;  

INSERT IGNORE INTO `orders` (`countryCode`, `date`, `id`,`productId`) 
VALUES 
    ('ru','2019-04-09 06:59',100453324298986,32829863707) ,
    ('fr','2019-04-09 05:59',100645420835625,32829863707) ,
    ('ru','2019-04-08 12:04',704482263524094,32829863707) 
    .......etc 9000 rows here at once

COMMIT;

RDS is 4 GB RAM 2 CPU Cores

Mysql engine innodb, any ideas ?

  • How was this SQL file generated? This error suggests a very large single statement in the file, and that you may have already done some config tweaking that you haven't mentioned. – Michael - sqlbot Apr 14 at 15:29
  • @Michael-sqlbot i edited the question and added sample sql file – saefko Myth Apr 14 at 15:33
  • @Michael-sqlbot im still fighting with this problem and this is wasting my time ,so as you hinted if lower the insert site to lets say 1000 row per insert does this do it – saefko Myth Apr 14 at 16:36
  • How was the dump file generated? If with mysqldump, was some buffer set to 500M, thereby producing 500MB INSERTs? – Rick James Apr 23 at 22:42
0

This is same question as stackoverflow.com 55675976.

Please AVOID crossposting on multiple sites. And refer to your solution that has already documented the reason for your frustration. You can NOT put a 1.5GB data file into MAX_ALLOWED_PACKET that has 1G upper limit.

  • sir i tried 300MB file size it did not work i tried with 250 it worked so it must be something else – saefko Myth Apr 14 at 20:37
  • 2
    Sir, Please post result of SELECT @@max_allowed_packet; from your 'failing' server where you tried the 300MB file size. – Wilson Hauck Apr 14 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.