0

I am restoring several MariaDB databases into a Amazon AWS RDS MariaDB instance from an AWS EC2 machine in the same VPC / region etc.

I am using a shell script like so:

mysql -u root -h hostname.amazonaws.com -ppassword < db1.sql
mysql -u root -h hostname.amazonaws.com -ppassword < db2.sql
mysql -u root -h hostname.amazonaws.com -ppassword < db3.sql
...

The sequence is important since the later databases have VIEWS and JOINS that reference earlier databases. I have figured that part out and doing the DBs one at a time manually works as expected.

The issue is, when I chain the DBs in a single shell script, the later restore for, say DB2, complains that some table in DB1 does not exist. The exact error message is below:

ERROR 1146 (42S02) at line 482: Table 'db1.table_name' doesn't exist

My guess is that the MySQL client sends the whole SQL file to the RDS server and starts on the next SQL file, without waiting for the previous one to complete successfully.

Is there any way I can get the earlier SQL files to block till it completes successfully or fail and break subsequent SQL files from executing?

Client and server info below:

mysql client  Ver 15.1 Distrib 10.0.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Server version: 10.0.31-MariaDB MariaDB Server
  • I don't have any experience with this on AWS, but running those commands on a normal MariaDB database will block until it has completed. Are you sure there isn't anything else going on? Have you verified that the tables are created in the end? – dbdemon Apr 18 '18 at 20:30
0

My guess is that the MySQL client sends the whole SQL file to the RDS server and starts on the next SQL file, without waiting for the previous one to complete successfully.

That is not the problem.

The problem is, mysqldump only takes care of this, automatically, if you use it as intended:

mysqldump [options] --databases db1 db2 db3

Otherwise, it essentially assumes any dependencies will already have been met on the server when you restore a dump file, so it does nothing to resolve them except within the individual db being restored.

If you create a single dump file, like this, specifying all the databases you need but using the --no-data option, you'll get a small file with all your tables and views, but no data. Restore that first, and then your 3 individual files.

  • Hi Michael, thanks for your input. I guess what you are trying to say is that certain tables that are required later do not exist in the earlier DB's restore. That is NOT the case here. The tables exist if I check for them later, after the entire script has run. – im-not-you Apr 20 '18 at 8:27
0

Thanks to all who provided inputs. I have got this to work for me by adding a delay of 60 seconds after the db1 restore by using the sleep command:

mysql -u root -h hostname.amazonaws.com -ppassword < db1.sql
sleep 60
mysql -u root -h hostname.amazonaws.com -ppassword < db2.sql
mysql -u root -h hostname.amazonaws.com -ppassword < db3.sql
...

I chose 1 minute randomly, there is no real reasoning behind it.

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.