Channel Listener SQL Error: a foreign key constraint fails

[Silmerias]

Description

Murmur crashed with an SQL error after a while. It is related to channel listener see log.

QMYSQL3: Unable to execute statement
Cannot add or update a child row: a foreign key constraint fails

Steps to reproduce

• Create a temporary channel
• Create a listener

Mumble version

1.5.517

Mumble component

Server

OS

Linux

Reproducible?

Yes

Additional information

This is from a database that is 10 years old.
Murmur did the SQL update at first startup.

Relevant log output

<F>2023-12-15 22:12:55.025 SQL Error [INSERT INTO `channel_listeners` (`server_id`, `user_id`, `channel_id`) VALUES (?, ?, ?)]: Cannot add or update a child row: a foreign key constraint fails (`mydatabase`.`channel_listeners`, CONSTRAINT `channel_listeners_del_channel` FOREIGN KEY (`server_id`, `channel_id`) REFERENCES `channels` (`server_id`, `channel_id`) ON DELETE CASCADE) QMYSQL3: Unable to execute statement
!! 12/15/23 22:12:55.026 error: communicator not destroyed during global destruction.

Screenshots

No response

[Krzmbrzl]

It seems like a non-existing channel was referenced. However, without a repro this is not really actionable for us and since the DB Backend has been entirely rewritten in #6263 I'll have to assume that the underlying issue has been fixed by that.

[Silmerias]

Ok, I'll update if this happen again.

[Silmerias]

@Krzmbrzl ok we found the issue, 100% reproductible.

Steps to reproduce:

• Create a temporary channel
• Create a listener

[Hartmnt]

I can not reproduce this with a 1.5.517 server/client and a Sqlite database.

So this issue either only occurs with the current mysql driver, or something else has to be true to trigger this bug.

[Krzmbrzl]

I can not reproduce this with a 1.5.517 server/client and a Sqlite database.

SQLite doesn't enfore foreign key constraints by default due to backwards compatibility issues (iirc their first implementation had a bug that prevented checking and then they decided to keep it that way to not break existing tables). Thus, you'll never see a foreign key constraint error with Mumble's current DB implementation.

However, with the new implementation foreign key checks for SQLite are enabled as well.

@Silmerias thank you very much for the repro. I'll ensure that the issue will by fixed in the new DB impl!

[Silmerias]

I sould have mentioned this is happening with the MySQL driver.