search

mumble-voip / mumble

Mumble is an open-source, low-latency, high quality voice chat software.
https://www.mumble.info
Other
6.29k stars 1.11k forks source link

Channel Listener SQL Error: a foreign key constraint fails #6283

Open Silmerias opened 9 months ago

Silmerias commented 9 months ago

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

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 commented 9 months ago

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 commented 9 months ago

Ok, I'll update if this happen again.

Silmerias commented 9 months ago

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

Steps to reproduce:

Hartmnt commented 8 months ago

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 commented 8 months ago

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 commented 8 months ago

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