[Murmur]Upgrading from 1.3.0 to 1.3.1 produces error on database query.

[Glowsome]

Describe the bug When upgrading from v 1.3.0 murmur to v1.3.1 an sql-error is seen. When still attempting a restart (after kill) all registrations from users were killed/non-existant.

<W>2020-07-13 01:09:22.919 SSL: OpenSSL version is 'OpenSSL 1.0.2k  26 Jan 2017'
<W>2020-07-13 01:09:22.920 Initializing settings from /opt/murmur/murmur.ini (basepath /opt/murmur)
<C>2020-07-13 01:09:22.930 MetaParams: Adding 2 intermediate certificates from certificate file.
<W>2020-07-13 01:09:23.032 MetaParams: TLS cipher preference is "ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA:AES256-SHA:AES128-SHA"
<W>2020-07-13 01:09:23.145 Changing database encoding to utf8mb4...
<W>2020-07-13 01:09:23.156 Renaming old tables...
<W>2020-07-13 01:09:23.671 Generating new tables...
<W>2020-07-13 01:09:28.899 Importing old data...
<F>2020-07-13 01:09:29.215 SQL Error [INSERT INTO `users` (`server_id`, `user_id`, `name`, `pw`, `lastchannel`, `texture`, `last_active`) SELECT `server_id`, `user_id`, `name`, `pw`, `lastchannel`, `texture`, `last_active` FROM `users_old_1594595363`]: Duplicate entry '4-Sên' for key 'users_name' QMYSQL3: Unable to execute statement
<W>2020-07-13 01:09:44.201 SSL: OpenSSL version is 'OpenSSL 1.0.2k  26 Jan 2017'
<W>2020-07-13 01:09:44.201 Initializing settings from /opt/murmur/murmur.ini (basepath /opt/murmur)
<C>2020-07-13 01:09:44.203 MetaParams: Adding 2 intermediate certificates from certificate file.
<W>2020-07-13 01:09:44.305 MetaParams: TLS cipher preference is "ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA:AES256-SHA:AES128-SHA"
<W>2020-07-13 01:09:44.393 Renaming old tables...
<W>2020-07-13 01:09:44.860 Generating new tables...
<W>2020-07-13 01:09:49.234 Importing old data...
<W>2020-07-13 01:09:49.517 Removing old tables...
<W>2020-07-13 01:09:49.810 MurmurIce: Endpoint "tcp -h 127.0.0.1 -p 6502" running
<W>2020-07-13 01:09:49.824 Murmur 1.3.1 (1.3.1) running on X11: SUSE Linux Enterprise Server 12 SP5: Booting servers

Steps to Reproduce Steps to reproduce the behavior: => i use a symlink to point to the correct version for systemctl control.

1. extract murmur package 1.3.1 to /opt/murmur-1.3.1
2. copy relevant files from previous version ( 1.3.0 )
3. flip symlink to point to new version.
4. restart murmur, error as stated above is seen on logfile.
5. restart after failed above will successfully start server(s), but registrations are gone/non-existant. => channel definitions are still intact.

Expected behavior If an error is encountered in the upgrade process -> REVERT , NOTIFY and kill the process forcefully.. do not continue blindly as if it was successfull. As this was also an issue in my previous upgrade this has to be sorted as a (imho) P1 ... as continuing will lead to alot of head-ache. => in the end i would expect a flawless upgrade when trasitioning from the previous versionb to the current.

Screenshots none - the log gives more detail about the server-behaviour.

Server (please complete the following information):

• OS: => see log paste above - SLES 12 SP5
• Version: murmur 1.3.1

Additional context idk, you tell me :) - logs can be provided in full detail. for now i have performed a rollback on both database and server-version .. which has put me back into a running situation ( on 1.3.0 version)

As i had issues with incomplete upgrade to previous 1.3.0 version where i had to manually interveine in the DB to make it work i am again faced with issues upgrading to the current version.

REQ : would it be possible to introduce something like a 'preflight' (in my case DB-)check tool that would report any problems prior to actually attempting an upgrade ?

[Krzmbrzl]

This is weird... All other DB errors I have seen so far caused the server to crash immediately :thinking:

Is the username 4-Sên indeed correct this way? To me this seems as if this name has been broken :eyes:

would it be possible to introduce something like a 'preflight' (in my case DB-)check tool that would report any problems prior to actually attempting an upgrade ?

I think doing something like this would be very difficult and probably a lot of work...

[Glowsome]

i have looked in the database, (with phpmyadmin) and i think this is an interpreter issue as i have both a 'Sen' and a 'Sên' where the query trips over the 2nd entry. They both have a different user_id

[Krzmbrzl]

What are the encodings/collations of your database tables?

[Glowsome]

The users table uses utf8_bin :

1. server_idIndex int(11) No None
2. user_idIndex int(11) No None
3. nameIndex varchar(255) utf8_bin Yes NULL
4. pw varchar(128) utf8_bin Yes NULL
5. salt varchar(128) utf8_bin Yes NULL
6. kdfiterations int(11) Yes NULL
7. lastchannelIndex int(11) Yes NULL
8. texture longblob Yes NULL
9. last_active timestamp No current_timestamp() ON UPDATE CURRENT_TIMESTAMP()`

[Krzmbrzl]

This seems to be what we'd expect... I'll have to dig into this some more once I have the time to do so...

The good news is that rewriting the database code on the server is on my ToDo-List anyways, so I hope this won't take too long to be solved :grin:

[Glowsome]

I have been reading up a bit, and the issue i am facing with the query is due to a 'double encoded UTF8'- char. Not completely sure if this is the issue , but this seems to be almost a direct match : https://stackoverflow.com/questions/11436594/how-to-fix-double-encoded-utf8-characters-in-an-utf-8-table

[Krzmbrzl]

Interesting... Given that the names are correct in the old database, I assume this is happening during processing somehow :thinking:

Character encodings == endless stream of problems :sweat_smile:

[Puma24000]

Hi @Krzmbrzl

This is the same issue i had and you have fixed : https://github.com/mumble-voip/mumble/issues/3803 Servers crashed immediatly and if you want upgrade you get this error too.

For the upgrade i had the same problem that the author, it is necessary to search the server id (4 for him) and delete the user. In fact, it must have two users with and without accent or special character.

[Krzmbrzl]

Ah very interesting, thanks for pointing this out! :+1:

@Glowsome could you check whether the described procedure fixes the issue for you?

[Glowsome]

@Krzmbrzl i will have to look into it

[Glowsome]

@Krzmbrzl

i have dropped both the user-registrations from the table, then performed the upgrade. This went flawlessly.

<W>2020-10-20 17:41:44.430 SSL: OpenSSL version is 'OpenSSL 1.0.2k  26 Jan 2017'
<W>2020-10-20 17:41:44.436 Initializing settings from /opt/murmur/murmur.ini (basepath /opt/murmur)
<C>2020-10-20 17:41:44.441 MetaParams: Adding 2 intermediate certificates from certificate file.
<W>2020-10-20 17:41:44.561 MetaParams: TLS cipher preference is "ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:EC                     DHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-SH                     A:DHE-RSA-AES128-SHA:AES256-SHA:AES128-SHA"
<W>2020-10-20 17:41:44.668 Changing database encoding to utf8mb4...
<W>2020-10-20 17:41:44.678 Renaming old tables...
<W>2020-10-20 17:41:45.436 Generating new tables...
<W>2020-10-20 17:41:50.397 Importing old data...
<W>2020-10-20 17:41:50.947 Removing old tables...
<W>2020-10-20 17:41:51.383 MurmurIce: Endpoint "tcp -h 127.0.0.1 -p 6502" running
<W>2020-10-20 17:41:51.396 Murmur 1.3.1 (1.3.1) running on X11: SUSE Linux Enterprise Server 12 SP5: Booting servers

[Krzmbrzl]

Awesome!

In that case I'll close this issue as resolved :+1:

[Glowsome]

@Krzmbrzl as a last test i pushed thru on updating server to 1.3.3 without issue after i dropped the earlier mentioned 2 user-reccords.