SQLite works correctly for Subversion since 3.6.8 (which made the triggers
work the way we use them).
SQLite 3.6.8 - 3.7.8 don’t use the query plans we expect them to use for
normal performance. In simple words operations that should operate on a
single node, may touch every node at every op-depth in the working copy.
(E.g. svn changelist LIST file, would do that).
So, while Subversion works correctly with this range it will be ‘unusable
slow’ (my words) in certain scenarios. I can’t decide if we should
therefore call these SQLite versions unsupported just by myself.
Then there is a different problem that you found: We have triggers that
detect invalid database states when SVN_DEBUG is defined in wc_db_wcroot.c.
These are currently depend on parent nodes being changed before their
children, which is not guaranteed by the SQL specification.
SQLite just happens to do this correctly for us with a table scan (because
our primary key is wc_id, local_relpath, op_depth) and the optimal indexes
for the relevant queries (the primary key, or wc_id, parent_relpath).
As SQLite 3.6.8-3.7.8 decide to prefer the wc_id, moved_to index instead
for an operation over wc_id, local_relpath it takes a completely different
order.
The result after the SQLite query is identical and the SQL standard doesn’t
prescribe an order, so there is no bug there... The bug is in an unwritten
assumption in that validation trigger.
(We would really want a validation *after* the complete query is evaluated,
but that feature is not available in SQLite)
We can’t disable this trigger from wc_db_wcroot.c, as that file doesn’t
know the SQLite version we use.
I don’t see a problem with bumping the requirement to 3.7.9, but that
decision should be made here on this list. Not by me :)
Bert
*From:* Greg Stein <gst...@gmail.com>
*Sent:* vrijdag 1 juni 2012 11:28:27
*To:* Bert Huijben <b...@qqmail.nl>
*CC:* dev@subversion.apache.org
*Subject:* Re: validity check error? huh?
And why is 3.7.9 now required?
Performance? Or bug fixes?
On Fri, Jun 1, 2012 at 5:25 AM, Greg Stein <gst...@gmail.com> wrote:
> I'm unclear here.
>
> Is 3.7.9 *required* ? ... or is that just for a better query planner?
>
> If this trigger is firing for me only in the maintenance builds, then
> what is it really telling me?
>
> Come on, Bert. What is going on? Do we need to REQUIRE 3.7.9 or NOT?
>
> Don't just feed me little bits of information. What is going on?
>
> On Fri, Jun 1, 2012 at 5:10 AM, Bert Huijben <b...@qqmail.nl> wrote:
>> The required fix is in 3.7.9. (
http://www.sqlite.org/releaselog/3_7_9.html.
>> Probably the ‘Enhanced the query planner so that it can factor terms in
and
>> out of OR expressions in the WHERE clause in an effort to find better
>> indices.’ Change)
>>
>>
>>
>> These triggers are temporary triggers inserted when you open the db
using a
>> maintainer build. So 1.7 is not affected, nor are release builds.
>>
>>
>>
>> Bert
>>
>>
>>
>> From: Greg Stein [mailto:gst...@gmail.com]
>> Sent: vrijdag 1 juni 2012 10:11
>> To: Bert Huijben
>> Cc: dev@subversion.apache.org
>> Subject: RE: validity check error? huh?
>>
>>
>>
>>
>> On Jun 1, 2012 3:56 AM, "Bert Huijben" <b...@qqmail.nl> wrote:
>>>
>>>
>>>
>>> > -----Original Message-----
>>> > From: Greg Stein [mailto:gst...@gmail.com]
>>> > Sent: vrijdag 1 juni 2012 9:28
>>> > To: dev@subversion.apache.org
>>> > Subject: validity check error? huh?
>>> >
>>> > Any ideas on why I'm seeing this now?
>>> >
>>> >
>>> > $ ./basic_tests.py 1
>>> > W: subversion/svn/delete-cmd.c:92: (apr_err=200035)
>>> > W: subversion/svn/util.c:913: (apr_err=200035)
>>> > W: subversion/libsvn_client/delete.c:482: (apr_err=200035)
>>> > W: subversion/libsvn_client/delete.c:383: (apr_err=200035)
>>> > W: subversion/libsvn_wc/adm_ops.c:701: (apr_err=200035)
>>> > W: subversion/libsvn_wc/wc_db.c:6974: (apr_err=200035)
>>> > W: subversion/libsvn_wc/wc_db.c:2595: (apr_err=200035)
>>> > W: subversion/libsvn_wc/wc_db_util.c:202: (apr_err=200035)
>>> > W: subversion/libsvn_subr/sqlite.c:1060: (apr_err=200035)
>>> > W: subversion/libsvn_wc/wc_db_util.c:185: (apr_err=200035)
>>> > W: subversion/libsvn_wc/wc_db.c:6795: (apr_err=200035)
>>> > W: subversion/libsvn_wc/wc_db.c:6734: (apr_err=200035)
>>> > W: subversion/libsvn_subr/sqlite.c:251: (apr_err=200035)
>>> > W: subversion/libsvn_subr/sqlite.c:235: (apr_err=200035)
>>> > W: subversion/libsvn_subr/sqlite.c:271: (apr_err=200035)
>>> > W: svn: E200035: sqlite: constraint failed
>>> > W: subversion/libsvn_subr/sqlite.c:607: (apr_err=200035)
>>> > W: svn: E200035: sqlite: WC DB validity check 03 failed
>>> >
>>> >
>>> > That's ra_local to fsfs.
>>>
>>> One of the buildbots had this too before upgrading its Sqlite.
>>>
>>> The WC-DB validations assume that the database is consistent at all
times,
>>> while the Sqlite queries that update the database assume that the
database
>>> is consistent between queries.
>>>
>>> There are differences between those assumptions if an UPDATE or INSERT
>>> uses
>>> a different index than expected.
>>
>> So you're suggesting that I need to update sqlite? I'm currently using a
>> 3.7.5 in-tree amalgamation. (Pending a non-obvious build/link error)
>> Assuming yes on upgrade, then do you also recommend that I step-wise
upgrade
>> to determine our new minimum requirement, or do you already know it?
(and if
>> so, why is that not already encoded into our build system) ... happy to
help
>> discover; just need some direction.
>>
>> Are there any changes to triggers? Will 1.8 continue to work on a 1.7
wc.db?
>> Or do we have some kind of change in triggers that is causing a problem?
>>
>> Cheers,
>> -g
