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`))