Mailcow-dockerized: MySQL Error, can't change administrator username

[quanbrew]

~
MySQL error: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (mailcow.da_acl, CONSTRAINT fk_domain_admin_acl FOREIGN KEY (username) REFERENCES domain_admins (username) ON DELETE CASCADE ON UPDATE NO ACTION) in /web/inc/functions.inc.php:495 Stack trace: #0 /web/inc/functions.inc.php(495): PDOStatement->execute(Array) #1 /web/json_api.php(1108): edit_admin_account(Array) #2 {main}
~

[andryyy]

Any output? =>

source mailcow.conf
docker-compose exec mysql-mailcow mysql -u${DBUSER} -p${DBPASS} ${DBNAME} -e "select username from domain_admins where username not in (select username from admin);"

[andryyy]

Please also run docker-compose exec mysql-mailcow mysql -u${DBUSER} -p${DBPASS} ${DBNAME} -e " delete from admin where username not in (select distinct username from domain_admins);"

[quanbrew]

I just ran mailcow-reset-admin.sh, then change username without error.

Any output? =>

$ docker-compose exec mysql-mailcow mysql -u${DBUSER} -p${DBPASS} ${DBNAME} -e "select username from domain_admins where username not in (select username from admin);"

| username |
+----------+
| quanbrew |
+----------+

quanbrew is still old username

run docker-compose exec mysql-mailcow mysql -u${DBUSER} -p${DBPASS} ${DBNAME} -e " delete from admin where username not in (select distinct username from domain_admins);" (no output)

then run$ docker-compose exec mysql-mailcow mysql -u${DBUSER} -p${DBPASS} ${DBNAME} -e "select username from domain_admins where username not in (select username from admin);" again , still this output

| username |
+----------+
| quanbrew |
+----------+

[andryyy]

Did this happen after resetting the admin? Do not disable foreign key checks, just remove quanbrew from the table.

[quanbrew]

@andryyy I restore from backup, found that with or without reset, it does not affect the result
I use SQL update quanbrew to new username:

UPDATE domain_admins SET username = 'NEWUSERNAME' WHERE username = 'quanbrew';

same error:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mailcow`.`da_acl`, CONSTRAINT `fk_domain_admin_acl` FOREIGN KEY (`username`) REFERENCES `domain_admins` (`username`) ON DELETE CASCADE ON UPDATE NO ACTION)

but disable foreign key check, this query succeeded.

But if I change username again, occur same error again

[andryyy]

There was probably an error in the table before we created the da_acl table. Check the da_acl, domain_admins and admins table. Do all usernames still exist? Is one missing in any table?

[quanbrew]

(restore from backup again)

before change username in web admin:

MariaDB [mailcow]> SELECT * FROM da_acl;
+----------+----------+------------+----------+----------+---------+-----------+-------------+
| username | syncjobs | quarantine | login_as | bcc_maps | filters | ratelimit | spam_policy |
+----------+----------+------------+----------+----------+---------+-----------+-------------+
| quanbrew |        1 |          1 |        1 |        1 |       1 |         1 |           1 |
+----------+----------+------------+----------+----------+---------+-----------+-------------+
1 row in set (0.00 sec)

MariaDB [mailcow]> SELECT username FROM admin;
+----------+
| username |
+----------+
| quanbrew |
+----------+
1 row in set (0.00 sec)

MariaDB [mailcow]> SELECT * FROM da_acl;
+----------+----------+------------+----------+----------+---------+-----------+-------------+
| username | syncjobs | quarantine | login_as | bcc_maps | filters | ratelimit | spam_policy |
+----------+----------+------------+----------+----------+---------+-----------+-------------+
| quanbrew |        1 |          1 |        1 |        1 |       1 |         1 |           1 |
+----------+----------+------------+----------+----------+---------+-----------+-------------+
1 row in set (0.00 sec)

after change (with error):

MariaDB [mailcow]> SELECT * FROM da_acl;
+----------+----------+------------+----------+----------+---------+-----------+-------------+
| username | syncjobs | quarantine | login_as | bcc_maps | filters | ratelimit | spam_policy |
+----------+----------+------------+----------+----------+---------+-----------+-------------+
| quanbrew |        1 |          1 |        1 |        1 |       1 |         1 |           1 |
+----------+----------+------------+----------+----------+---------+-----------+-------------+
1 row in set (0.00 sec)

MariaDB [mailcow]> SELECT username FROM admin;
+----------+
| username |
+----------+
| koppa    |
+----------+
1 row in set (0.00 sec)

MariaDB [mailcow]> SELECT * FROM domain_admins;
+----------+--------+---------------------+--------+----+
| username | domain | created             | active | id |
+----------+--------+---------------------+--------+----+
| quanbrew | ALL    | 2018-02-28 04:54:39 |      1 |  1 |
+----------+--------+---------------------+--------+----+
1 row in set (0.00 sec)

[andryyy]

Yes, I just found this error. I switched to IDs as primary keys for domain admins and did not change it here.

[andryyy]

Meh, stupid. :-( Sorry, I will push a fix soon.

[andryyy]

Should be fixed now.