DB restore fails

Support
#1

Problem:

DB restore fails with these messages:

# foreman-rake db:import_dump file=/shared_fs/foreman-dump/foreman.sql
Your backup is going to be imported from: /shared_fs/foreman-dump/foreman.sql
You can backup the old database 'foreman' by running:
 - foreman-rake db:dump destination=/mydir/dumps/foreman.sql RAILS_ENV=production
This task will destroy your old database tables! Are you sure you want to continue? [y/N]
y
ERROR 1005 (HY000) at line 1893: Can't create table 'foreman.dynflow_actions' (errno: 150)
Completed.

Expected outcome:

No errors.

Foreman and Proxy versions:

1.14.3 (yes, I know, it is old, but still should work)

Foreman and Proxy plugin versions:

Other relevant data:

Sequence of commands:

# mysql -u root -p$DB_PASS -e "drop database foreman"
# mysql -u root -p$DB_PASS -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
# mysql -u root -p$DB_PASS -e "create database foreman"

and then import task that fails after a while…

Am I missing something in this sequence?
Quick Google search points out that this error is related to FK constrains, so how do I go about it?

Thanks!

#2

I haven’t touched MySQL for years as most of our users use PostgreSQL, but in general you want to remove all foreign keys and indicies and then import all data. PostgreSQL has some options for pg_dump/pg_restore to take care of this, I think it’s even fully transparent. Search for the same to skip them, you may need to remove from your backup manually prior restore.

Testing of PostgreSQL backup mechanisms in Foreman is better than MySQL, that’s a fact unfortunately. We are looking for more MySQL testers.

#3

Yes, I figured that was my best course of action so I started on excluding tables that I ran into issues with all together. So far I excluded these 4:

dynflow_actions
dynflow_delayed_plans
dynflow_execution_plans
dynflow_steps

They are empty anyway, so I think I’m close to resolving this.

As for PostgreSQL vs MySQL - the reason we went with MySQL is multi-master config provided by Galera. This may change in the future though.

Thanks!

#4

Yeah we are aware that MySQL is better fit for multinode setups, but PosgreSQL has been catching up recently.