Unit test fails on Rocky 8

[mattias-p]

I ran the unit tests on Rocky 8 and test01.t blew up when I tried to install the dist tarball using sudo and cpanm.

make[1]: Leaving directory '/root/.cpanm/work/1719318302.1693/Zonemaster-Backend-11.1.1/share'
PERL_DL_NONLAZY=1 "/usr/bin/perl" "-MExtUtils::Command::MM" "-MTest::Harness" "-e" "undef *Test::Harness::Switches; test_harness(0, 'inc', 'blib/lib', 'blib/arch')" t/*.t
t/00-load.t ................ ok
t/batches.t ................ ok
t/config.t ................. ok
t/db.t ..................... ok
t/db_ddl.t ................. ok
# not recording
t/idn.t .................... ok
t/lifecycle.t .............. ok
t/parameters_validation.t .. ok
t/queue.t .................. ok
t/rpc_validation.t ......... ok
# not recording
    # running the agent on test 6d02b1c7e0415d2e
# running the agent on test 1e74e2f11a5cf02e
DBD::SQLite::db prepare failed: too many SQL variables at /root/.cpanm/work/1719318302.1693/Zonemaster-Backend-11.1.1/blib/lib/Zonemaster/Backend/DB.pm line 1029.
DBD::SQLite::db prepare failed: too many SQL variables at /root/.cpanm/work/1719318302.1693/Zonemaster-Backend-11.1.1/blib/lib/Zonemaster/Backend/DB.pm line 1029.
# Tests were run but no plan was declared and done_testing() was not seen.
# Looks like your test exited with 2 just after 3.
t/test01.t ................. 
Dubious, test returned 2 (wstat 512, 0x200)
All 3 subtests passed 
t/test_validate_syntax.t ... ok
t/translator.t ............. ok
t/validator.t .............. ok

[mattias-p]

This happens when Zonemaster::Backend::DB::add_result_entries() is called with more than 143 or more entries. This function prepares a statement with seven variables per entry, and when the number of entries exceed 142, the number of variables exceed 1000.

[mattias-p]

This error occurs when value of the constant SQLITE_LIMIT_VARIABLE_NUMBER is too low.

On Rocky 8 the system gives us DBD::SQLite 1.58 which doesn't even have an API to read the value of the constant, but experiments have show it to be around 1000, as indicated in my previous comment.

In DBD::SQLite 1.62 the constant can be read (it is 999) but it cannot be increased.

In DBD::SQLite 1.66 it is 32766, which is enough for 4680 entries. This leaves us some margin above the largest test results we've observed (which was around 2000 entries IIRC).

[matsduf]

Is it possible to install newer DBD::SQLite?

[mattias-p]

That's what I'm working on.

[marc-vanderwal]

That reminds me of a dirty hack that was done in Backend’s migration script for 11.1.0 (https://github.com/zonemaster/zonemaster-backend/blob/master/share/patch/patch_db_zonemaster_backend_ver_11.1.0.pl#L40) to accommodate SQLite. It seems that we are faced with the same problem here.

[mattias-p]

It seems that we are faced with the same problem here.

Yeah, it's the exact same problem. I guess people don't use SQLite much, since no external users have reported the 142 entry limitation.

[mattias-p]

Fixed by #1174