Failure in move_sqlite.py

[def-]

Does this mean we lost the ranks? Edit: Seems like not, good.

8 new entries in backup database found (8 ranks, 0 teamranks, 0 saves
Moving entries from /home/teeworlds/servers/ddnet-server.sqlite to /home/teeworlds/servers/ddnet-server-2020-11-08.sqlite
You can use the following commands to import the entries to MySQL (use sed 's/record_/prefix_/' for other database prefixes):

    echo '.dump --preserve-rowids' | sqlite3 /home/teeworlds/servers/ddnet-server-2020-11-08.sqlite | grep -E '^INSERT INTO record_(race|teamrace|saves)' | sed -e 's/INSERT INTO/INSERT IGNORE INTO/' | sed -e
's/rowid,//' -e 's/VALUES([0-9]*,/VALUES(/' > ddnet-server-2020-11-08.sql
    mysql -u teeworlds -p'PW2' teeworlds < ddnet-server-2020-11-08.sql

When the ranks are transfered successfully to mysql, /home/teeworlds/servers/ddnet-server-2020-11-08.sqlite can be removed

Log of the transfer:

BEGIN TRANSACTION;
CREATE TABLE record_maps (Map VARCHAR(128) COLLATE BINARY NOT NULL, Server VARCHAR(32) COLLATE BINARY NOT NULL, Mapper VARCHAR(128) COLLATE BINARY NOT NULL, Points INT DEFAULT 0, Stars INT DEFAULT 0, Timestamp
TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (Map));
CREATE TABLE record_points (Name VARCHAR(16) COLLATE BINARY NOT NULL, Points INT DEFAULT 0, PRIMARY KEY (Name));
CREATE TABLE record_race (Map VARCHAR(128) COLLATE BINARY NOT NULL, Name VARCHAR(16) COLLATE BINARY NOT NULL, Timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, Time FLOAT DEFAULT 0, Server CHAR(4), cp1
FLOAT DEFAULT 0, cp2 FLOAT DEFAULT 0, cp3 FLOAT DEFAULT 0, cp4 FLOAT DEFAULT 0, cp5 FLOAT DEFAULT 0, cp6 FLOAT DEFAULT 0, cp7 FLOAT DEFAULT 0, cp8 FLOAT DEFAULT 0, cp9 FLOAT DEFAULT 0, cp10 FLOAT DEFAULT 0, cp11
FLOAT DEFAULT 0, cp12 FLOAT DEFAULT 0, cp13 FLOAT DEFAULT 0, cp14 FLOAT DEFAULT 0, cp15 FLOAT DEFAULT 0, cp16 FLOAT DEFAULT 0, cp17 FLOAT DEFAULT 0, cp18 FLOAT DEFAULT 0, cp19 FLOAT DEFAULT 0, cp20 FLOAT DEFAULT
0, cp21 FLOAT DEFAULT 0, cp22 FLOAT DEFAULT 0, cp23 FLOAT DEFAULT 0, cp24 FLOAT DEFAULT 0, cp25 FLOAT DEFAULT 0, GameID VARCHAR(64), DDNet7 BOOL DEFAULT FALSE, PRIMARY KEY (Map, Name, Time, Timestamp, Server));
Traceback (most recent call last):
  File "/home/teeworlds/servers/scripts/move_sqlite.py", line 104, in <module>
    main()
  File "/home/teeworlds/servers/scripts/move_sqlite.py", line 101, in main
    transfer(args.f, args.to)
  File "/home/teeworlds/servers/scripts/move_sqlite.py", line 37, in transfer
    for line in conn_from.iterdump():
  File "/usr/lib/python3.7/sqlite3/dump.py", line 55, in _iterdump
    query_res = cu.execute(q)
sqlite3.OperationalError: Could not decode to UTF-8 column ''INSERT INTO "record_race"
VALUES('||quote("Map")||','||quote("Name")||','||quote("Timestamp")||','||quote("Time")||','||quote("Server")||','||quote("cp1")||','||qu

@Zwelf can you please take a look?

[Zwelf]

It would be quite interesting to see which column contains the invalid UTF-8. Is it a player name, which didn't get properly validated before writing the rank into the database. Was there an error while writing which corrupted a field (I don't think that happens with sqlite). Does the python script interprets a column as utf8 which should be binary (I think only the teamrank-TeamID column would fit for that)
Maybe the check for utf-8 differs between python and our c++ code. But these are just a few guesses.

[def-]

sqlite> select * from record_race;
Tangerine|(1)W̶̽�|2020-10-26 15:34:57|48.52|CAN|8.12|13.24|16.38|19.8|23.32|37.82|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|c7d366b6-23e3-49b7-8f60-7c91b3a1a852|0
Tangerine|(1)W̶̽�|2020-10-26 16:22:34|59.38|CAN|0.0|11.54|24.96|28.26|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|c7d366b6-23e3-49b7-8f60-7c91b3a1a852|0
Tangerine|(1)W̶̽�|2020-10-26 16:29:56|203.36|CAN|4.88|10.72|15.34|57.36|69.32|191.98|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|c7d366b6-23e3-49b7-8f60-7c91b3a1a852|0
Tangerine|(1)W̶̽�|2020-10-26 16:36:07|52.26|CAN|7.38|12.52|23.3|26.58|29.76|43.84|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|c7d366b6-23e3-49b7-8f60-7c91b3a1a852|0
Tangerine|(1)F̶̽�|2020-10-26 22:52:08|47.4|CAN|5.32|10.76|13.82|16.96|19.44|26.3|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|8c4cf742-9dab-478f-8aea-4464bff1ff6e|0
Tangerine|(1)F̶̽�|2020-10-26 22:53:32|37.3|CAN|4.14|9.06|12.94|16.04|18.6|29.68|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|8c4cf742-9dab-478f-8aea-4464bff1ff6e|0
Tangerine|(1)F̶̽�|2020-10-26 22:54:16|35.68|CAN|7.14|12.3|15.1|18.14|21.14|27.78|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|8c4cf742-9dab-478f-8aea-4464bff1ff6e|0
Snowzz|(1)W̶̽�|2020-10-27 00:03:48|505.94|CAN|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|894683a2-15bf-4d76-a46e-d30d78a2dea0|0
sqlite> select * from record_saves;
sqlite> select * from record_teamrace;

ddnet-server.sqlite.zip

[def-]

Honestly, I don't think these players have invalid names. My guess is that the string is being overwritten somewhere in the server before getting written to sqlite. Maybe we use an old buffer that is not allocated anymore?

On other servers:

LearnToPlay|(1)БОТ ЕГ�|2020-10-23 06:26:46|1812.92|RUS|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|22d47cac-f33d-4cf5-81b0-12fe98190eff|0

Just2Easy|(1)БОТ ЕГ�|2020-10-31 08:01:18|447.2|RUS|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|2e722ba1-6e88-4fba-b444-7d1930cc7e7c|0

I guess it would help to try out if those names can be used on server.

[heinrich5991]

My guess: The server does name replacement for valid UTF-8 names by prepending (1), because another player is also on the server. This truncates the name at an invalid position, resulting in invalid UTF-8 at the end.

Solution: Use str_utf8_copy at that place.

[def-]

You win, that's probably the reason.

[def-]

I guess for the ones in here I should just cut off the invalid utf8 at the end. @Zwelf got an easy command for that by chance?

[def-]

@Zwelf

I guess for the ones in here I should just cut off the invalid utf8 at the end. @Zwelf got an easy command for that by chance?

[Zwelf]

Adding a line to ignore invalid utf-8 characters and trimming of trailing whitespace might do the job. I think this behaves like the utf8-trimming done in str_utf8_copy. And trimming the whitespace due to trailing whitspace is removed by the server as well.

diff --git a/scripts/move_sqlite.py b/scripts/move_sqlite.py
index 902192193..cda741ade 100755
--- a/scripts/move_sqlite.py
+++ b/scripts/move_sqlite.py
@@ -34,6 +34,7 @@ def transfer(file_from, file_to):
        cursor_to = conn_to.cursor()
 
        conn_from = sqlite3.connect(file_from, isolation_level='EXCLUSIVE')
+       conn_from.text_factory = lambda b: b.decode(errors = 'ignore').rstrip()
        for line in conn_from.iterdump():
                cursor_to.execute(line)
                print(line)

[def-]

Thanks, that seems to have worked. I guess we don't need to persist this workaround since the root cause is fixed.

[def-]

This issue is still occuring:

sqlite3 ddnet-server.sqlite                                                                  ~/servers@master
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> select * from record_race;
Top 3|(1)[D] 三歲�|2020-11-22 09:40:25|1723.42|KOR|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|0.0|554c9586-de10-44a3-a8ea-9849fc029e2a|0

[def-]

str_utf8_trim_right does not work for this, I'll try out what does. Hm, but we actually use str_utf8_copy and that should work. Maybe the servers were still running old version?

[heinrich5991]

It was running 1883546 (huh, pure base-10 digit commit ID).

[def-]

Too bad, then this is somehow still occuring. Any idea? The code in server.cpp looks correct to me now.

[def-]

heinrich recently fixed the utf8 trimming, so I believe this is fixed now.