search

mumble-voip / mumble

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

last_disconnect cannot be null #5730

Closed jasonrepos closed 2 years ago

jasonrepos commented 2 years ago

The issue

My mumble server is running multiple virtual servers and using mysql as a backend.

When I try to start the daemon, I receive the following error in the logs and then the daemon dies. I have tried to dump the sql db to file and look through to find any instances where the "murmur_users" table contains a value which is NULL for the column last_disconnect, however I cannot seem to find any instances of this.

Any ideas how I can go about resolving this?

F>2022-06-29 15:56:46.237 SQL Error [UPDATE murmur_users SET last_disconnect = NULL WHERE server_id = ?]: Column 'last_disconnect' cannot be null QMYSQL3: Unable to execute statement

Mumble version

1.4.0

Mumble component

Server

OS

Linux

Additional information

Mumble Server running virtual servers

No LSB modules are available. Distributor ID: Ubuntu Description: Ubuntu 18.04.6 LTS Release: 18.04 Codename: bionic

jasonrepos commented 2 years ago

The way I am going to go about fixing this is as follows;

  1. Edit the SQL dump file and change From: last_disconnect timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', To: last_disconnect timestamp DEFAULT '0000-00-00 00:00:00',

  2. Import the new SQL dump

  3. Update all entires which are identified to contain NULL

select * from murmur_users where last_disconnect is NULL; update murmur_users set last_disconnect="0000-00-00 00:00:00" where last_disconnect is NULL; <-- I haven't tried this yet

  1. Update the table column for last_disconnect to be "NOT NULL"

Hartmnt commented 2 years ago

I do not understand why the last_disconnect column has the NOT NULL attribute attached in your DB, but this prompts further investigation. The mumble server (<=1.4.230) will set all last_disconnect values to NULL on start up. This means you will run into this issue every time you start it, which renders your step 3 pointless. This might be a classic case of "How did this ever work to begin with?".

The one thing I can say is that in commits after #5646 the server will never try to set last_disconnect to NULL as it improved the underlying logic. So I think your options are: Edit the column to allow NULL values, or update the mumble server by building from source that includes that linked patch.

Krzmbrzl commented 2 years ago

Okay so I did a bit of researching and I was able to track this issue down. The NULL value is inserted here: https://github.com/mumble-voip/mumble/blob/8505df958fc16a6f3ad902d84aed415742c5bb20/src/murmur/ServerDB.cpp#L2495

The problem is that apparently older versions of MySQL appear to have non-standard behavior where the TIMESTAMP data type (which is used for the last_disconnect column) includes an implicit NOT NULL constraint, rendering above SQL invalid. This could be fixed by explicitly trying to work around this MySQL peculiarity (not really worth it at this point though) or by using a version of MySQL of >= 8.02.

However as Hartmnt wrote, the issue will also be fixed by the upcoming Mumble release as the changes that he made also remove the code inserting the NULL value in the first place.

So for now your options are:

  1. Upgrade to MySQL >= 8.02
  2. Wait for the new Mumble release (no ETA yet though, sorry)
  3. Build from source using either master branch or 1.4.x branch (note: Not the v1.4.230 tag as that still contains the problematic code)

Ref.: https://stackoverflow.com/a/22870825/