Hello everybody and happy Easter for those who celebrate it!
I have a vm running nearly 1.5 year now start with ubuntu 22.04 and upgrade to 24.04.2 but I have been having issues with it as you might have seen from other posts that I have done over the time I am registered here.
So I decided to make a new fresh vm and start almost from the start, well not with all my data since over the course of that time, it is a lot of entries that I don't want to do again manually.
So I made a vm with fresh ubuntu 24.04.2, updated it to latest kernel and packages, restarted to the new kernel. Took a backup of it in this vanilla condition. Then I installed itflow via the script, took another backup in this condition.
I went to my original vm, took a db backup file and uploaded it to the new vm in /root/ folder. I took also the masterkey noted. Then I switch to the new vm and I try to import the db.
Both vm are ubuntu 24.04.2 and both run the same MariaDB version for databases but I am having issues with importing the data. I am not a db admin but I have had my fair share of experience with databases and migration, even from mysql to mariadb and vice versa. When dealing with mysql and mariadb migration there are tools to help that goal, I expected from mariadb to mariadb to be a super easy process but I am always getting errors.
example:
mysql -u itflow -p itflow < /root/2025-04-17_ITFlow_backup.sql

Enter password:

--------------

CREATE TABLE `accounts` (

`account_id` int(11) NOT NULL AUTO_INCREMENT,

`account_name` varchar(200) NOT NULL,

`account_description` varchar(250) DEFAULT NULL,

`opening_balance` decimal(15,2) NOT NULL DEFAULT 0.00,

`account_currency_code` varchar(200) NOT NULL,

`account_notes` text DEFAULT NULL,

`account_type` int(6) DEFAULT NULL,

`account_created_at` datetime NOT NULL DEFAULT current_timestamp(),

`account_updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),

`account_archived_at` datetime DEFAULT NULL,

PRIMARY KEY (`account_id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

--------------

ERROR 1050 (42S01) at line 3: Table 'accounts' already exists

sure okay the tables exists, I drop them so there is clean db named itflow with no tables in it. It should work right? Wrong!
CREATE TABLE `asset_interface_links` (

`interface_link_id` int(11) NOT NULL AUTO_INCREMENT,

`interface_a_id` int(11) NOT NULL,

`interface_b_id` int(11) NOT NULL,

`interface_link_type` varchar(100) DEFAULT NULL,

`interface_link_status` varchar(50) DEFAULT NULL,

`interface_link_created_at` datetime NOT NULL DEFAULT current_timestamp(),

`interface_link_updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(),

PRIMARY KEY (`interface_link_id`),

KEY `fk_interface_a` (`interface_a_id`),

KEY `fk_interface_b` (`interface_b_id`),

CONSTRAINT `fk_interface_a` FOREIGN KEY (`interface_a_id`) REFERENCES `asset_interfaces` (`interface_id`) ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT `fk_interface_b` FOREIGN KEY (`interface_b_id`) REFERENCES `asset_interfaces` (`interface_id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

--------------

ERROR 1005 (HY000) at line 217: Can't create table `itflow`.`asset_interface_links` (errno: 150 "Foreign key constraint is incorrectly formed")

Why is it like this when importing the db? I am also getting errors when I try from myphpadmin. On clean db:
CREATE TABLE `asset_interface_links` ( `interface_link_id` int(11) NOT NULL AUTO_INCREMENT, `interface_a_id` int(11) NOT NULL, `interface_b_id` int(11) NOT NULL, `interface_link_type` varchar(100) DEFAULT NULL, `interface_link_status` varchar(50) DEFAULT NULL, `interface_link_created_at` datetime NOT NULL DEFAULT current_timestamp(), `interface_link_updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`interface_link_id`), KEY `fk_interface_a` (`interface_a_id`), KEY `fk_interface_b` (`interface_b_id`), CONSTRAINT `fk_interface_a` FOREIGN KEY (`interface_a_id`) REFERENCES `asset_interfaces` (`interface_id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_interface_b` FOREIGN KEY (`interface_b_id`) REFERENCES `asset_interfaces` (`interface_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

MySQL said:

#1005 - Can't create table `itflow`.`asset_interface_links` (errno: 150 "Foreign key constraint is incorrectly formed"
How can I solve this issue and restore the db correctly? And why is it a problem in the first place when taking backup from 2 same systems?

Thanks for any answer!