MySQL foreign key constraints

[fbarmentlo]

Question: Which MySQL versions are compatible for use as back-end? I experience problems with MariaDB 10.3 and 10.7:

Actual Result / Problem

Long story short: rebuilding my kubernetes cluster due to changes in underlying host. Got all pods running but import of backup fails with a MySQL error: error: Error 1451: Cannot delete or update a parent row: a foreign key constraint fails (sshportal,host_group_acls, CONSTRAINT fk_host_group_acls_acl FOREIGN KEY (acl_id) REFERENCES acls (id))

I get similar errors when adding hosts or assigining hosts to groups via sshportal's admin commands. already tried dumping the DB, modifying the dump and then importing it.. but i get similar errors.

Another one: config> host create --name --group 2 ssh://@ error: Error 1452: Cannot add or update a child row: a foreign key constraint fails (sshportal.hosts, CONSTRAINT fk_hosts_hop FOREIGN KEY (hop_id) REFERENCES hosts (id))

Expected Result / Suggestion

It has worked before, but I accidentally used the "latest"-tag and my old environment is unavailable to check what version I was running. I of course do not expected to see SQL-errors when adding hosts.

Some context

Using image docker.io/moul/sshportal:latest, but also tried versions 1.19.3, 1.19.0.

version: n/a debug mode (server): false Hostname: sshportal-7cf9d56bd4-dq7j5 (Kubernetes Pod) CPUs: 4 Demo mode: false DB Driver: mysql DB Conn: root:@tcp(sshsql:3306)/sshportal?charset=utf8&parseTime=true&loc=Local Bind Address: :2222 System Time: 2022-04-06T09:06:26.302297884Z OS Type: linux OS Architecture: amd64 Go routines: 10 Go version (build): go1.18 Uptime: 15h32m50.066400161s User ID: 1 User email: admin@localhost Version: n/a GIT SHA: n/a GIT Tag: n/a

[firecyberice]

I have the same problem. from the logs:

error: Error 1451: Cannot delete or update a parent row: a foreign key constraint fails (`sshportal`.`host_group_acls`, 
CONSTRAINT `fk_host_group_acls_acl` FOREIGN KEY (`acl_id`) REFERENCES `acls` (`id`))

And while importing a backup I got this:

Error 1452: Cannot add or update a child row: a foreign key constraint fails (`sshportal`.`events`, 
CONSTRAINT `fk_events_author` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`))

I have used docker.io/moul/sshportal:1.19.3 and mariadb:latest It seems like there is no problem with the docker.io/moul/sshportal:1.18.5 image.

[fdabe]

Error 1452: Cannot add or update a child row: a foreign key constraint fails (`sshportal`.`events`, 
CONSTRAINT `fk_events_author` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`))

it arrive because user system does not exist in table users with id "0". So when you start server it test to update user 0 as author_id but user id not present.

I create user with id 0 and no worries after. Hope is the good solution and help :)

[yumitsu]

Same problem with latest github version when using MySQL. When trying to add new host:

Error 1452: Cannot add or update a child row: a foreign key constraint fails (`db`.`hosts`, CONSTRAINT `fk_hosts_hop` FOREIGN KEY (`hop_id`) REFERENCES `hosts` (`id`))

After inserting a new row with ID = 0 new hosts can be added, but when trying to delete one:

Error 1451: Cannot delete or update a parent row: a foreign key constraint fails (`db`.`host_host_groups`, CONSTRAINT `fk_host_host_groups_host` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`id`))