mapserver crashes with minetest 5.5.0 and sqlite3

[nogajun]

When I use mapserver with minetest 5.5.0 and sqlite3, the whole minetest crashes.

• OS: Debian GNU/Linux sid amd64
• Client: Minetest 5.5.0 flatpak version

Reproduce

1. Create a world on minetest. (Example: mapserver-test).
2. Play the game in the created world and exit.
3. Create mapserver's config file in the world directory.

$ cd ~/.var/app/net.minetest.Minetest/.minetest/worlds/mapserver-test/
$ mapserver -createconfig

4. Start mapserver(at same directory).

$ mapserver

5. Play the game in the world.

After playing for a while, an error message appears and minetest and mapserver crash.

see mapserver.log

[BuckarooBanzay]

interesting, i think i reintroduced this bug with the sqlite driver change in e7528d9aceecaf6d3e865a9c09dccb5ba93be79c

[BuckarooBanzay]

I re-added the timeout param properly, can you test with the current master version? Do you need a build or can you do it yourself?

[nogajun]

Thanks for the fix. I will build and try it out.

[nogajun]

It no longer crashes. However, the logs are mixed with errors. Is this not a problem?

time="2022-03-07T12:23:59+09:00" level=error msg="Tile render job tile" LayerId=1 X=-4 Y=-2 Zoom=12 err="database is locked (5) (SQLITE_BUSY)" prefix=tilerenderjob

mapserver-20220307.log

[BuckarooBanzay]

fyi: i've encountered this error too now :/ This seems to happen since i've change to the new sqlite driver, i think it doesn't open the db in read-only mode...

Upstream-issue here: https://gitlab.com/cznic/sqlite/-/issues/97

[BuckarooBanzay]

i managed to get it up and running with sqlite again when i switched to WAL mode in the sqlite3 cli:

PRAGMA journal_mode=WAL;

@nogajun can i bother you to test that again or did you switch to postgres permanently? :smirk:

[nogajun]

Sorry for the late reply. I changed the journal_mode and tried.

$ sqlite3 ~/.var/app/net.minetest.Minetest/.minetest/worlds/mapserver-test/map.sqlite 
SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> PRAGMA journal_mode;
delete
sqlite> PRAGMA journal_mode=wal;
wal
sqlite> 

OK! No more errors. Thank you very much @BuckarooBanzay 😸

[ludo75june]

Hi sorry but i'm new at github but i don't find any forum or pm function so:

I've tried mapserver and when it crashed for me i tried mt5.0.0 but then i found out so much is outdated and incompatible, after 2 days i manage to switch to postgresql in the latest mt 5.7 (?) and that seem to be working very nice and fast, but to replicate the 48 hour steps to set it up again give me the creeps, so how does it work with sqlite3 now days?

[BuckarooBanzay]

but to replicate the 48 hour steps to set it up again give me the creeps, so how does it work with sqlite3 now days?

What took 2 days for you? Even the initial migration shouldn't take that long :/

Sqlite3 work pretty well, except for the above problem that you have to do a one-time fixup for the journaling mode with PRAGMA journal_mode=wal. Otherwise the database can't be shared across processes.

Try it out on a test-instance first if you have doubts, if you have issues, feel free to open an issue or contact me on Libera-IRC BuckarooBanzai or Discord BuckarooBanzai#6742

[ludo75june]

Oh, sorry i'm tired i guess. I tried PRAGMA journal_mode=wal but didn't get it right, how do i do that? btw postgresql is lightningfast so please keep it... :D

[BuckarooBanzay]

I tried PRAGMA journal_mode=wal but didn't get it right, how do i do that?

Uh, where did you try that? Do you have the sqlite3 packages installed? If so: you have to execute sqlite3 map.sqlite to open the database and execute the wal-statement there...

[ludo75june]

sqlite> PRAGMA journal_mode ...> delete ...> PRAGMA journal_mode=wal ...> wal ...>

return gives me alot of ...> how do i save?

that's mostly the reason i started installing postgresql...

[BuckarooBanzay]

return gives me alot of ...>

Try this:

PRAGMA journal_mode=wal;

Be sure to enter the ";" at the end, this is pretty important :smirk:

[ludo75june]

Allright 🤯Skickat från min Galaxy -------- Originalmeddelande --------Från: Buckaroo Banzai @.> Datum: 2022-08-09 06:46 (GMT+01:00) Till: minetest-mapserver/mapserver @.> Kopia: ludo75june @.>, Comment @.> Ämne: Re: [minetest-mapserver/mapserver] mapserver crashes with minetest 5.5.0 and sqlite3 (Issue #255)

return gives me alot of ...>

Try this:

PRAGMA journal_mode=wal;

Be sure to enter the ";" at the end, this is pretty important 😏

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: @.***>

[dennisjenkins75]

I just built a new LXC container with the latest minetest-5.6.1 and mapserver-4.5.0 and came across this issue. I was about to file a bug, but found this one near the top of the queue :)

root@mt-zoonami-1:/home/minetest/.minetest/worlds/world# for A in *.sqlite; do echo -ne "${A}  \t"; sqlite3 ${A} "pragma journal_mode"; done
auth.sqlite     delete
map.sqlite      delete
mapserver.sqlite        delete
mod_storage.sqlite      delete
players.sqlite          delete

root@mt-zoonami-1:/home/minetest/.minetest/worlds/world# for A in *.sqlite; do echo -ne "${A}  \t"; sqlite3 ${A} "pragma journal_mode=WAL;"; done
auth.sqlite     wal
map.sqlite      wal
mapserver.sqlite        wal
mod_storage.sqlite      wal
players.sqlite          wal

I can confirm that this fixes the issue. However, it would be nice if either minetest or mapserver would set the journal mode automagically. :)

[dennisjenkins75]

BAH...

        fname = "/etc/systemd/system/minetest.service"
        self.container.files.put(fname, data)
        self.run(["systemctl", "daemon-reload"])
        self.run(["systemctl", "enable", "minetest.service"])
        self.run(["systemctl", "start", "minetest.service"])

        # https://github.com/minetest-mapserver/mapserver/issues/255
        sleep(2)
        self.run(["sqlite3", self.world_dir + "/map.sqlite", 
                  "pragma journal_mode=WAL;"])

[BuckarooBanzay]

fixed with https://github.com/minetest-mapserver/mapserver/commit/a9aab89f29491f182da2dd8776865d5a4fc2da38