hypnotoad + Mojo::SQLite (in WAL mode) does not work as expected

[tim-2]

Hi!

I have a simple app which reads from a sqlite database and an app command which simply inserts a row. The inserted row is not shown when I start the app with hypnotoad and open the index route.

With morbo, daemon and prefork it works as expected. When hypnotoad is run in foreground mode (-f) it also works.

Here is a minimal example:

package Mojolicious::Command::sql_insert;
use Mojo::Base 'Mojolicious::Command', -signatures;

sub run($self, @args) {
    my $db = $self->app->sqlite->db;
    $db->insert('test', {name => 'foo' . rand});
}

1;

package main;
use Mojolicious::Lite -signatures;
use Mojo::SQLite;

app->plugin(Config => {
    default => {
        hypnotoad => {
            listen  => [qw(http://*:3000)],
            workers => 1,
        },
    },
});

app->helper(sqlite => sub { state $sqlite = Mojo::SQLite->new('test.sqlite') });

app->sqlite->migrations->from_data->migrate;

get '/' => sub($c) {
    my $db = $c->sqlite->db;
    my $rows = $db->select('test', '*', {})->hashes;
    $c->render(json => $rows);
} => 'index';

app->start;

__DATA__
@@ migrations
-- 1 up
create table test(
    id   integer primary key,
    name text not null
);

Steps to reproduce:
Start hypnotoad:
hypnotoad app.pl

Run the insert command:
perl app.pl sql-insert -m production

Open http://127.0.0.1:3000/

---

No rows are shown. If you do the same with hypnotoad -f app.pl or perl app.pl daemon -l http://*:3000 it works as expected.
Without WAL mode Mojo::SQLite->new('test.sqlite?no_wal=1') it also works as expected.

[tim-2]

I just noticed that when I set app->sqlite->max_connections(0);, it works. But that feels wrong. I am very confused.

Also if I start hypnotoad again

hypnotoad app.pl

Starting hot deployment for Hypnotoad server 23725

then it works. For me it looks like a forking issue or similar.

[Grinnz]

Does it help if you use a file-scoped "my $sqlite;" instead of "state $sqlite"?

[tim-2]

No, it doesn't make any difference. Same behavior. I think its some forking issue. In very rare cases I also get a "malformed database" error, but only with hypnotoad. So maybe this is relevant.

[Grinnz]

Also, always use an absolute path to the database file (with from_filename to make it most simple), use curfile from Mojo::File to make it relative to the script

[tim-2]

In my real application I use from_filename. For the example it makes no difference.

[kraih]

Unrelated to the problem, but i only use Mojo::SQLite with no_wal=1. Because i've had way too many database corruption issues in production.

[tim-2]

Yes, I also had some "database disk image is malformed" errors with WAL mode. I think this has somehow something to do with this issue.