SQLite limits the number of host parameters in a single SQL statement via the SQLITE_MAX_VARIABLE_NUMBER setting, which can only be set at compile time and is thus impractical for most Travis CI users to change. Its default value is 999, which is small enough to prevent common, reasonable database queries, such as restoring even a moderately sized table backup. I propose increasing the value for everyone. Of course, if there is a way for an individual user to change the setting without making their setup fragile or opting into a maintenance headache, I would very happily use it if someone could explain it or point me to instructions. Thanks!
This won’t save you. Whatever limit you set, you’ll hit it beyond a certain size.
Could you describe your use case in more detail, to understand why you cannot use the standard mechanism?
According to http://www.ibiblio.org/elemental/howto/sqlite-backup.html , SQLite databases are designed to be restored from a dump produced by the .dump command. Which creates a separate statement for each table row and as such, does not hit the limitation.
Obviously, as you indicate @native-api, my proposal is a mitigation, not a solution. My particular difficulty is that I’m using an application (Drupal) with a database abstraction layer that itself suffers from this problem. (See Fix SQLite variable limit [#2031261] .) So I don’t have the ability to just use the .dump command, for example. My failing queries are being generated by APIs two or more levels abstracted. According to the issue just referenced, most OS distributions increase SQLITE_MAX_VARIABLE_NUMBER to 250,000 or more. I cannot substantiate that claim, but I can say that it seems like a reasonable precedent and that it would be sufficient for my use case.
If indeed “most OS distributions increase SQLITE_MAX_VARIABLE_NUMBER to 250,000 or more”, that should be the same for Ubuntu.
Please link to the affected build(s), and try to find out which SQLite instance they use. You may be using a private copy statically linked with some 3rd-party library or something.
Good point, @native-api. Here’s an affected build. As you can see, the failing query includes 2,519 variables.
What do you mean by “which SQLite instance they use”? What I know off-hand is that they’re using the sqlite3 PHP extension via PDO.
As I suspected, Travis’ PHP is configured with the default configure options which include --with-pgo-sqlite that uses a bundled copy of libsqlite3 by default. I believe this is what php-build does with default settings.
This can be overridden by passing --with-pgo-sqlite=/usr to PHP’s configure (see the equivalent for php-build).
Though since this is not the default, it’s up to Travis staff how warranted this is.
Thank you for taking the trouble to investigate that, @native-api! It would appear from your findings that there isn’t anything I can do about this myself. This is my first interaction in this forum… Do I just wait now to see if this request gets the staff’s attention?
You can build PHP from source with an altered configuration and use that…
This feature request was (unintentionally) fulfilled in PHP 7.4 because this version no longer bundles a private copy of libsqlite3.