1743309 - (SQLite3.37.0) Upgrade to SQLite 3.37.0

Status: RESOLVED FIXED

(Toolkit :: Storage, task, P3)

Description

[Chris Peterson [:cpeterson]]

@ RyanVM: heads up: there is a new SQLite minor release. :)

SQLite Release 3.37.0 On 2021-11-27

• STRICT tables provide a prescriptive style of data type management, for developers who prefer that kind of thing.
• When adding columns that contain a CHECK constraint or a generated column containing a NOT NULL constraint, the ALTER TABLE ADD COLUMN now checks new constraints against preexisting rows in the database and will only proceed if no constraints are violated.
• Added the PRAGMA table_list statement.
• CLI enhancements:
  • Add the .connection command, allowing the CLI to keep multiple database connections open at the same time.
  • Add the --safe command-line option that disables dot-commands and SQL statements that might cause side-effects that extend beyond the single database file named on the command-line.
  • Performance improvements when reading SQL statements that span many lines.
• Added the sqlite3_autovacuum_pages() interface.
• The sqlite3_deserialize() does not and has never worked for the TEMP database. That limitation is now noted in the documentation.
• The query planner now omits ORDER BY clauses on subqueries and views if removing those clauses does not change the semantics of the query.
• The generate_series table-valued function extension is modified so that the first parameter ("START") is now required. This is done as a way to demonstrate how to write table-valued functions with required parameters. The legacy behavior is available using the -DZERO_ARGUMENT_GENERATE_SERIES compile-time option.
• Added new sqlite3_changes64() and sqlite3_total_changes64() interfaces.
• Added the SQLITE_OPEN_EXRESCODE flag option to sqlite3_open_v2().
• Use less memory to hold the database schema.

Comment 1

[Ryan VanderMeulen [:RyanVM]]

Let's wait until the version bump next week before trying to take this.

Comment 2

[Arthur K. (he/him)]

(In reply to Ryan VanderMeulen [:RyanVM] from comment #1)

Let's wait until the version bump next week before trying to take this.

I'd surmise that besides what's in the relnotes that the 5+ months since 3.36.0 probably brought along with it a lot of other benefits and fixes as well.

Comment 3

[Marco Bonardo [:mak]]

SQLite keeps improving and will continue to. That by itself is not a good reason for us to rush updating it. We have often seen dot versions being released to address regressions, shortly after a release. If the version doesn't provide critical fixes, or things that we specifically need, it's ok to wait a cycle.

Comment 4

[Ryan VanderMeulen [:RyanVM]]

Attachment: Bug 1743309 - Upgrade to SQLite 3.37.0. r=mak

Comment 5

[Ryan VanderMeulen [:RyanVM]]

There's been a 3.37 branch created upstream for a potential future dot release, but nothing obviously on the horizon. The 3.37.0 release is green on Try, so let's go ahead and move forward with getting this landed for now.
https://treeherder.mozilla.org/jobs?repo=try&revision=27fba5fc180a7dae06377f447c109b54be91ccdd

Comment 6

[Marco Bonardo [:mak]]

yes, the only interesting fix in dot release, if I read the timeline correctly, is an OOB in FTS5, that we don't use yet. We can pick it once available, but nothing blocking us for now.

Comment 7

[Pulsebot]

Pushed by [e-mail]:
https://hg.mozilla.org/integration/autoland/rev/a65f38f0f9ed
Upgrade to SQLite 3.37.0. r=mak

Comment 8

[Iulian Moraru]

https://hg.mozilla.org/mozilla-central/rev/a65f38f0f9ed

Comment 9

[Alexandru Ionescu (needinfo me) [:alexandrui]]

== Change summary for alert #32753 (as of Thu, 16 Dec 2021 10:02:22 GMT) ==

Improvements:

Ratio	Test	Platform	Options	Absolute values (old vs new)
7%	amazon PerceptualSpeedIndex	macosx1014-64-shippable-qr	cold fission webrender	893.46 -> 835.08

For up to date results, see: https://treeherder.mozilla.org/perfherder/alerts?id=32753

Comment 10

[Arthur K. (he/him)]

Sqlite 3.37.1 got released a couple days ago: https://www.sqlite.org/releaselog/3_37_1.html

Comment 11

[Ryan VanderMeulen [:RyanVM]]

Yeah, there's been a lot of activity on the 3.37 branch since then too. I'm wondering if they've got a .2 release in the works.

Comment 12

[Arthur K. (he/him)]

(In reply to Ryan VanderMeulen [:RyanVM] from comment #11)

Yeah, there's been a lot of activity on the 3.37 branch since then too. I'm wondering if they've got a .2 release in the works.

Might be good to ping Richard Hipp to get a heads up on any .2 release in the pipe.

Comment 13

[Arthur K. (he/him)]

I'll send him an email real quick.

Comment 14

[Andrew Sutherland [:asuth] (he/him)]

(In reply to Arthur K. [He/Him] from comment #13)

I'll send him an email real quick.

We have a forum for all communication with the SQLite team through MoCo's support contract so that we don't need to email :drh individually. Information on that is at https://mana.mozilla.org/wiki/pages/viewpage.action?spaceKey=FIREFOX&title=SQLite+Technical+Support

Comment 15

[Arthur K. (he/him)]

(In reply to Andrew Sutherland [:asuth] (he/him) from comment #14)

(In reply to Arthur K. [He/Him] from comment #13)

I'll send him an email real quick.

We have a forum for all communication with the SQLite team through MoCo's support contract so that we don't need to email :drh individually. Information on that is at https://mana.mozilla.org/wiki/pages/viewpage.action?spaceKey=FIREFOX&title=SQLite+Technical+Support

Understood. I don't have an account there but now that I know the proper channel, I'll let someone who does take the reigns.

Comment 16

[Ryan VanderMeulen [:RyanVM]]

I don't think this is worth bugging drh over. If they do, they do. Otherwise, I don't think there's much in the 3.37.1 release to fret about.

Comment 17

[Arthur K. (he/him)]

(In reply to Ryan VanderMeulen [:RyanVM] from comment #16)

I don't think this is worth bugging drh over. If they do, they do. Otherwise, I don't think there's much in the 3.37.1 release to fret about.

Ok. In any case, Richard did reply to me and said to look for a new dot release in the next 48 hours.

Specifically because of: "https://www.sqlite.org/src/info/73c2b50211d3ae26 is a database-corruption bug introduced in 3.35.0 and discovered just the other day by a researcher running a fuzzer. It is not a security risk, but it does need a patch."

Comment 18

[D. Richard Hipp]

SQLite version 3.37.2 has been tagged. We are still working on the precompiled binaries for Windows, but y'all probably don't care about that.

Version 3.37.2 fixes a potential database corruption bug that was introduced in SQLite version 3.35.0 (2021-03-12). The bug only comes up when using SAVEPOINT, and then only rarely. I don't know if FF uses SAVEPOINT or not, but I think you should probably update just to be safe.

The bug arose due to a patch to address a concern about excess memory usage with SAVEPOINT. (See https://sqlite.org/forum/forumpost/e78ffd751185a67e for the discussion thread). The patch seemed to work, and wasn't given anybody any problems. The bug was discovered by a security researcher at Wingtecher Lab of Tsinghua University. Jingzhou Fu is using AFL++ to fuzz various database engines, including SQLite. He found a case that caused an assertion fault(). The assert() that failed indicates an internal inconsistency in the sub-journals used to rollback SAVEPOINTs. As I write this, we still don't have a test case that will actually cause database corruption. Database corruption remains just a theoretical possibility. But upgrading still seems prudent.

Sorry for the bug....

Comment 19

[Ryan VanderMeulen [:RyanVM]]

Thanks for the info, drh. I'll get a bug filed for picking up the 3.37.2 update then.

Comment 20

[Andrew Sutherland [:asuth] (he/him)]

(In reply to D. Richard Hipp from comment #18)

Version 3.37.2 fixes a potential database corruption bug that was introduced in SQLite version 3.35.0 (2021-03-12). The bug only comes up when using SAVEPOINT, and then only rarely. I don't know if FF uses SAVEPOINT or not, but I think you should probably update just to be safe.

IndexedDB does use savepoints quite a lot, but the SQL is quite simple except for the (simple) triggers. So, we probably do indeed want the update, but it seems unlikely we'd be in any interesting edge case territory.