Not Null Constraint Issue?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Not Null Constraint Issue?

Justin Gielski
Good Afternoon

I wanted to pass along a really strange issue we just ran into in with one
of our products. We have a simple table with an INTEGER column set with a
NOT NULL DEFAULT 0 constraint.

We have no clue how it happened, but some how a null value was successfully
inserted into this column with out the constraint triggering an error or
defaulting to 0.

The application communicating with the DB is a .NET Framework app running
System.Data.SQLite. The strange thing is that the .NET application itself
will also not allow for NULL values as it converts them to 0 before sending
back to the database. This somehow occurred with no errors occurring during
the transaction.

Once the transaction completed, then we started seeing errors in both our
application and SQLite. SQLite. SQLite just kept triggering the following
message anytime we tried to adjust the null value to fix the issue":

*"database is locked release restore point sqlite"*

My first thought was a hung journal file keeping it locked, but it wasn't
the case. An application still had hold on the database though, almost as
if it hung. We closed anything that may have had the file open. Once that
was done we were able to successfully update the column with a value and
everything continued to work.

The database locking mode is set to NORMAL but the database is always
opened exclusively. Could this been a concurrency issue in which 2
connections hit the database at the exact same time? No data loss was
reported yet.

I've been working with SQLite for a while now, and this was the first time
I've seen a constraint not catch something like this. Just wanted to pass
along as I found it odd.

Have a great day

-Justin
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Not Null Constraint Issue?

Simon Slavin-3
On 15 Jan 2020, at 9:44pm, Justin Gielski <[hidden email]> wrote:

> *"database is locked release restore point sqlite"*

If there's nothing in your code that caused that to happen, then I would suspect a transient hardware glitch.  Does your code use SAVEPOINTs ?

> The database locking mode is set to NORMAL but the database is always
> opened exclusively. Could this been a concurrency issue in which 2
> connections hit the database at the exact same time?

SQLite is not meant to allow that, with the existance of the journal/shared-memory files acting as a mutex.  If it actually did happen, and you're not violating anything in the following document, then either you found a bug in SQLite, or you had hardware problems.

>  No data loss was
> reported yet.

If you haven't already run "PRAGMA integrity_check" on a copy of the database file, do so.  And tell us whether it reports corruption.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Not Null Constraint Issue?

R Smith-2

On 2020/01/16 12:47 am, Simon Slavin wrote:
> On 15 Jan 2020, at 9:44pm, Justin Gielski <[hidden email]> wrote:
>
>> *"database is locked release restore point sqlite"*
> If there's nothing in your code that caused that to happen, then I would suspect a transient hardware glitch.  Does your code use SAVEPOINTs ?
>
>> The database locking mode is set to NORMAL but the database is always
>> opened exclusively. Could this been a concurrency issue in which 2
>> connections hit the database at the exact same time?
> SQLite is not meant to allow that, with the existance of the journal/shared-memory files acting as a mutex.  If it actually did happen, and you're not violating anything in the following document, then either you found a bug in SQLite, or you had hardware problems.

Yes, or a software glitch. Remember that the SQLite database engine is
actual software library code running alongside your own code in the same
process, which means that if your code causes a memory fault (for
whatever reason), the accompanying SQLite code could easily be on the
receiving end of the memory corruption and also no longer work as
expected. This is one of the fundamental differences of having the
Database Engine as part of your process vs. having a process elsewhere
on a server that functions independently (and can check constraints
independently).

This is not a typical thing in production though, more expected during
debugging or development, but it can happen in production, especially
having situations like the OP described with rogue database locks from
"hung" processes, etc.

@Justin: I'd start debugging by your processes/code that were hanging,
finding out why that happens, and how your code paths could end up in
such a state would probably solve a lot of problems (including the
sqlite one). That said, if the you can engineer a situation in which it
happens repeatably, that would be very interesting and the devs (and the
rest of us) would be very interested.


Cheers,
Ryan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Not Null Constraint Issue?

Justin Gielski
In reply to this post by Justin Gielski
After looking into the issue a bit more, it appears the INTEGER value was
actually saved into the DB as an EMPTY not a NULL, which is currently
allowed by the database constraints. This makes me feel a bit better, as
now we have a reason for the constraint not triggering.

INTEGER objects within the .NET Framework do not allow for empty values
which is why we didn't notice that it was saved this way in the DB. It
appears the issue was application side as your suggestions suspected.

We're looking into why, but your responses helped us to realize this so
thank you.

-Justin



On Thu, Jan 16, 2020 at 4:00 AM <
[hidden email]> wrote:

> Send sqlite-users mailing list submissions to
>         [hidden email]
>
> To subscribe or unsubscribe via the World Wide Web, visit
>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> or, via email, send a message with subject or body 'help' to
>         [hidden email]
>
> You can reach the person managing the list at
>         [hidden email]
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of sqlite-users digest..."
>
>
> Today's Topics:
>
>    1. Re: Next Release? Visual release timeline? (R Smith)
>    2. Re: Query Planner GROUP BY and HAVING clauses optimization        ?
>       (Keith Medcalf)
>    3. Re: Query Planner GROUP BY and HAVING clauses optimization        ?
>       (Jean-Baptiste Gardette)
>    4. Re: sqlite3_limit equivalent in System.Data.SQLite.dll
>       (Keith Bertram)
>    5. Re: sqlite3_limit equivalent in System.Data.SQLite.dll
>       (Keith Medcalf)
>    6. Not Null Constraint Issue? (Justin Gielski)
>    7. Re: Not Null Constraint Issue? (Simon Slavin)
>    8. Re: Not Null Constraint Issue? (R Smith)
>    9. Test failures on GPFS (T J)
>   10. Re: Next Release? Visual release timeline? (Dominique Devienne)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Wed, 15 Jan 2020 17:54:24 +0200
> From: R Smith <[hidden email]>
> To: [hidden email]
> Subject: Re: [sqlite] Next Release? Visual release timeline?
> Message-ID: <[hidden email]>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> On 2020/01/15 1:24 PM, Richard Hipp wrote:
> > On 1/15/20, Dominique Devienne <[hidden email]> wrote:
> >> I like Lua's way to graphically visualize releases at
> >> https://www.lua.org/versions.html
> >>
> >>
> >> Please send javascript that will generate such a graph, either as SVG
> >> or as an HTML Canvas.
> >>
> >> (1) For improved display on mobile, consider making the graph vertical
> >> instead of horizontal.
> >>
> >> (2) Assume the data is a JSON array of pairs.  The first element of
> >> each pair is the release name (ex: "3.30.0") and the second element is
> >> the time as a fractional year (ex: "2019.7775").
>
> We'd like to submit this layout as an option:
> https://sqlitespeed.com/sqlite_releases.html
>
> Shown alongside the current list in simple form. Tried a few layouts,
> not all work as well (SQLite releases are much more dense than Lua),
> finally settled on the above, but left some options open.
>
> It comes with some config structure in this form:
>
>        var options = {
>          parentId: 'versionHistoryGraph',
>          width: 200,
>          yearWidth: 100,
>          heightPerYear: 300,
>          heightPerVersion: 14, // needs to match the style for .version
>          lineColor: "#222222",
>          yearBackground: "#EEEEEE",
>          data:
>
> [["1.0",2000.6298197581566],["1.0.1",2000.6325576089437],["1.0.3",2000.6435090120922],["1.0.4",2000.659936116815],["1.0.5",2000.7064795801962],["1.0.8",2000.7502851927902],["1.0.9",2000.7749258498745],["1.0.10",2000.7804015514487],["1.0.12",2000.7968286561716],["1.0.14",2000.802304357746],["1.0.13",2000.802304357746],["1.0.15",2000.8132557608944],
>
> ...
>
> ["3.30.0",2019.7584987451517],["3.30.1",2019.7749258498745]]
>        };
>
> Note: The Release-Date array must be given Ascending, else an additional
> sort step in Java is needed, but I think SQLite is better at that.
>
>
> I will send the full script directly via e-mail (not sure if the forum
> will allow the size), but it can of course also be directly copied from
> the above html.
>
> Official statement:
> We have solely created the content of that html page and this e-mail,
> for the purpose of using it in the sqlite public pages, or as they see
> fit, but it is free to all, and herewith donated to the public domain.
>
>
> Cheers,
> Davey Van Nes, Ryan Smith
>
>
>
>
>
>
>
> ------------------------------
>
> Message: 2
> Date: Wed, 15 Jan 2020 09:05:01 -0700
> From: "Keith Medcalf" <[hidden email]>
> To: "SQLite mailing list" <[hidden email]>
> Subject: Re: [sqlite] Query Planner GROUP BY and HAVING clauses
>         optimization    ?
> Message-ID: <[hidden email]>
> Content-Type: text/plain; charset="utf-8"
>
>
> On Wednesday, 15 January, 2020 02:06, Jean-Baptiste Gardette <
> [hidden email]> wrote:
>
> > Just to be sure, is it unsafe to write a non agregate SELECT with GROUP
> > BY and HAVING clauses (without sub-SELECT) for the sole prupose
> > explained before (even if the approache is discutable) ?
>
> Presently, yes it is.
>
> >I understand 2 different answers here :
>
> >- "No, this kind of query can't be rewritten by the optimizer for the
> >technical reasons (VDBE, index etc)"
>
> This is presently the case.  Current versions of the query planner will
> not optimize these queries but will instead execute them as written.  That
> is statements of the form "select ... from ... where ... group by ...
> having ... order by ... limit ... offset ..." will not convert the "group
> by" into an "order by" or the "having" into "where".  The provers required
> to allow this sort of transformation do not exist at present.
>
> >- "Yes it is unsafe, a future version of SQLite may optimize differently
> >this kind of query"
>
> Yes.  A future version of SQLite may indeed process the query by turning
> the "group by" into an "order by" (if there isn't one, or just ignoring it
> if there is an order by, or perhaps merging them) and moving the "having"
> to a "where" condition.  Doing this would require that the optimizer
> recognize that the group by expression can only result in single row groups
> and that neither the select list nor the having expression contain
> aggregate functions.  There is almost nothing to be gained from this
> optimization, however, so it is highly unlikely that such provers would be
> written in order to implement this particular optimization.
>
> Contrast this with recent optimizations that have been added, for example,
> the LEFT JOIN optimization which downgrades an outer join into an inner
> join if it can be proved that the overall result will be the same (the
> extra candidates generated by the outer join will be removed from the
> result set by a where clause, so going to all the bother of adding and
> processing them in the first place serves no purpose), or that leaf tables
> which are not referenced in the select list are removed from the query
> since generating those results merely incurs a cost to no effect.  These
> optimizations can have a significant impact on performance.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
>
> ------------------------------
>
> Message: 3
> Date: Wed, 15 Jan 2020 17:33:02 +0100
> From: Jean-Baptiste Gardette <[hidden email]>
> To: [hidden email], [hidden email]
> Subject: Re: [sqlite] Query Planner GROUP BY and HAVING clauses
>         optimization    ?
> Message-ID: <[hidden email]>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
> Thank you Keith for the detail explanation.
> I misunderstood the 2 replies were opposite but this is not the case.
>
> Thank you again
> Jean-bapstiste
>
>
> ------------------------------
>
> Message: 4
> Date: Wed, 15 Jan 2020 19:49:40 +0000
> From: Keith Bertram <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] sqlite3_limit equivalent in
>         System.Data.SQLite.dll
> Message-ID:
>         <
> [hidden email]
> >
>
> Content-Type: text/plain; charset="utf-8"
>
> Hello,
>
> I'm attempting to use the new SetLimitOption added to the 1.0.112 version
> of System.Data.SQLite.dll. Either I'm not using this function correctly or
> there is a problem within the dll. Any help you can provide is greatly
> appreciated.
>
>         private void Button_Click (object sender, RoutedEventArgs e)
>             {
>             int iRequestValue = 20;
>             String fName = @"d:\vancouver\OpenCommsCPV.db";
>             String connStr = String.Format("Data Source={0}, Version=3",
> fName);
>             SQLiteConnection conn = new SQLiteConnection(connStr);
>             conn.Open();
>
>             int iOldValue =
> conn.SetLimitOption(SQLiteLimitOpsEnum.SQLITE_LIMIT_ATTACHED,
> iRequestValue);
>             int iNewValue =
> conn.SetLimitOption(SQLiteLimitOpsEnum.SQLITE_LIMIT_ATTACHED, -1);  // Pass
> -1 to get the current value.
>             if ( iNewValue != iRequestValue )
>                 {
>                 MessageBox.Show("Setting Num Attachments limit failed.");
>                 }
>             else
>                 {
>                 MessageBox.Show("Setting Num Attachments limit
> succeeded.");
>                 }
>             }
>
> Keith
>
> -----Original Message-----
> From: sqlite-users <[hidden email]> On
> Behalf Of Joe Mistachkin
> Sent: Thursday, October 24, 2019 8:36 AM
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll
>
>
> Yes, the NuGet packages are included in the release.
>
> Sent from my iPhone
>
> > On Oct 24, 2019, at 9:13 AM, Keith Bertram <[hidden email]>
> wrote:
> >
> > Ok. Thanks. Does it get immediately placed under nuget?
> >
> > Keith
> >
> > -----Original Message-----
> > From: sqlite-users <[hidden email]> On
> > Behalf Of Joe Mistachkin
> > Sent: Thursday, October 24, 2019 8:12 AM
> > To: SQLite mailing list <[hidden email]>
> > Subject: Re: [sqlite] sqlite3_limit equivalent in
> > System.Data.SQLite.dll
> >
> >
> > Within the next week or so.
> >
> > Sent from my iPhone
> >
> >> On Oct 24, 2019, at 8:37 AM, Keith Bertram <[hidden email]>
> wrote:
> >>
> >> Ok. Thanks. Do you have an estimated release date?
> >>
> >> Keith
> >>
> >> -----Original Message-----
> >> From: sqlite-users <[hidden email]> On
> >> Behalf Of Joe Mistachkin
> >> Sent: Wednesday, October 23, 2019 9:08 PM
> >> To: 'SQLite mailing list' <[hidden email]>
> >> Subject: Re: [sqlite] sqlite3_limit equivalent in
> >> System.Data.SQLite.dll
> >>
> >>
> >> Keith Bertram wrote:
> >>>
> >>> The default limit for the number of attached databases appears to be
> >>> 10
> >> with the
> >>> 1.0.106 version of System.Data.SQLite.dll and I would like to
> >>> increase
> >> this value
> >>> to 25. I've used the sqlite3_limit function in the past to increase
> >>> this
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
>
> https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=hmGTLOph1qd_VnCqj81HzEWkDaxmYdIWRBdoFggzhj8&r=l90GvhOo_5uKc2d8JIuoVtb0fH4diec3z4TsA-hoNTc&m=e4b8CwDAzK1-TKWOZZJxjXuHFSnDxxfXP6h5QVKBpaI&s=dOVM934ewvnS0gL9iui_PzN0keYUOjW48CiQW0Xea6A&e=
>
> ------------------------------
>
> Message: 5
> Date: Wed, 15 Jan 2020 13:54:27 -0700
> From: "Keith Medcalf" <[hidden email]>
> To: "SQLite mailing list" <[hidden email]>
> Subject: Re: [sqlite] sqlite3_limit equivalent in
>         System.Data.SQLite.dll
> Message-ID: <[hidden email]>
> Content-Type: text/plain; charset="utf-8"
>
>
> What did you define SQLITE_MAX_ATTACHED as when you compiled the DLL?  The
> default limit is 10.  You can dynamically decrease the limit to be less
> than the compile time limit, but you cannot increase it beyond the maximum
> set when you compiled the library.
>
> https://sqlite.org/limits.html
>
> 11. Maximum Number Of Attached Databases
>
> The ATTACH statement is an SQLite extension that allows two or more
> databases to be associated to the same database connection and to operate
> as if they were a single database. The number of simultaneously attached
> databases is limited to SQLITE_MAX_ATTACHED which is set to 10 by default.
> The maximum number of attached databases cannot be increased above 125.
>
> The maximum number of attached databases can be lowered at run-time using
> the sqlite3_limit(db,SQLITE_LIMIT_ATTACHED,size) interface.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-----Original Message-----
> >From: sqlite-users <[hidden email]> On
> >Behalf Of Keith Bertram
> >Sent: Wednesday, 15 January, 2020 12:50
> >To: SQLite mailing list <[hidden email]>
> >Subject: Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll
> >
> >Hello,
> >
> >I'm attempting to use the new SetLimitOption added to the 1.0.112 version
> >of System.Data.SQLite.dll. Either I'm not using this function correctly
> >or there is a problem within the dll. Any help you can provide is greatly
> >appreciated.
> >
> >        private void Button_Click (object sender, RoutedEventArgs e)
> >            {
> >            int iRequestValue = 20;
> >            String fName = @"d:\vancouver\OpenCommsCPV.db";
> >            String connStr = String.Format("Data Source={0}, Version=3",
> >fName);
> >            SQLiteConnection conn = new SQLiteConnection(connStr);
> >            conn.Open();
> >
> >            int iOldValue =
> >conn.SetLimitOption(SQLiteLimitOpsEnum.SQLITE_LIMIT_ATTACHED,
> >iRequestValue);
> >            int iNewValue =
> >conn.SetLimitOption(SQLiteLimitOpsEnum.SQLITE_LIMIT_ATTACHED, -1);  //
> >Pass -1 to get the current value.
> >            if ( iNewValue != iRequestValue )
> >                {
> >                MessageBox.Show("Setting Num Attachments limit failed.");
> >                }
> >            else
> >                {
> >                MessageBox.Show("Setting Num Attachments limit
> >succeeded.");
> >                }
> >            }
> >
> >Keith
> >
> >-----Original Message-----
> >From: sqlite-users <[hidden email]> On
> >Behalf Of Joe Mistachkin
> >Sent: Thursday, October 24, 2019 8:36 AM
> >To: SQLite mailing list <[hidden email]>
> >Subject: Re: [sqlite] sqlite3_limit equivalent in System.Data.SQLite.dll
> >
> >
> >Yes, the NuGet packages are included in the release.
> >
> >Sent from my iPhone
> >
> >> On Oct 24, 2019, at 9:13 AM, Keith Bertram <[hidden email]>
> >wrote:
> >>
> >> Ok. Thanks. Does it get immediately placed under nuget?
> >>
> >> Keith
> >>
> >> -----Original Message-----
> >> From: sqlite-users <[hidden email]> On
> >> Behalf Of Joe Mistachkin
> >> Sent: Thursday, October 24, 2019 8:12 AM
> >> To: SQLite mailing list <[hidden email]>
> >> Subject: Re: [sqlite] sqlite3_limit equivalent in
> >> System.Data.SQLite.dll
> >>
> >>
> >> Within the next week or so.
> >>
> >> Sent from my iPhone
> >>
> >>> On Oct 24, 2019, at 8:37 AM, Keith Bertram <[hidden email]>
> >wrote:
> >>>
> >>> Ok. Thanks. Do you have an estimated release date?
> >>>
> >>> Keith
> >>>
> >>> -----Original Message-----
> >>> From: sqlite-users <[hidden email]> On
> >>> Behalf Of Joe Mistachkin
> >>> Sent: Wednesday, October 23, 2019 9:08 PM
> >>> To: 'SQLite mailing list' <[hidden email]>
> >>> Subject: Re: [sqlite] sqlite3_limit equivalent in
> >>> System.Data.SQLite.dll
> >>>
> >>>
> >>> Keith Bertram wrote:
> >>>>
> >>>> The default limit for the number of attached databases appears to be
> >>>> 10
> >>> with the
> >>>> 1.0.106 version of System.Data.SQLite.dll and I would like to
> >>>> increase
> >>> this value
> >>>> to 25. I've used the sqlite3_limit function in the past to increase
> >>>> this
> >
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >https://urldefense.proofpoint.com/v2/url?u=http-
> >3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-
> >2Dusers&d=DwIGaQ&c=hmGTLOph1qd_VnCqj81HzEWkDaxmYdIWRBdoFggzhj8&r=l90GvhOo
> >_5uKc2d8JIuoVtb0fH4diec3z4TsA-hoNTc&m=e4b8CwDAzK1-
> >TKWOZZJxjXuHFSnDxxfXP6h5QVKBpaI&s=dOVM934ewvnS0gL9iui_PzN0keYUOjW48CiQW0X
> >ea6A&e=
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
>
> ------------------------------
>
> Message: 6
> Date: Wed, 15 Jan 2020 13:44:44 -0800
> From: Justin Gielski <[hidden email]>
> To: [hidden email]
> Subject: [sqlite] Not Null Constraint Issue?
> Message-ID:
>         <CAFc-Pf_3Fk_XNnOip=zKy7CiS3E+0+VVP=JOMwa-=
> [hidden email]>
> Content-Type: text/plain; charset="UTF-8"
>
> Good Afternoon
>
> I wanted to pass along a really strange issue we just ran into in with one
> of our products. We have a simple table with an INTEGER column set with a
> NOT NULL DEFAULT 0 constraint.
>
> We have no clue how it happened, but some how a null value was successfully
> inserted into this column with out the constraint triggering an error or
> defaulting to 0.
>
> The application communicating with the DB is a .NET Framework app running
> System.Data.SQLite. The strange thing is that the .NET application itself
> will also not allow for NULL values as it converts them to 0 before sending
> back to the database. This somehow occurred with no errors occurring during
> the transaction.
>
> Once the transaction completed, then we started seeing errors in both our
> application and SQLite. SQLite. SQLite just kept triggering the following
> message anytime we tried to adjust the null value to fix the issue":
>
> *"database is locked release restore point sqlite"*
>
> My first thought was a hung journal file keeping it locked, but it wasn't
> the case. An application still had hold on the database though, almost as
> if it hung. We closed anything that may have had the file open. Once that
> was done we were able to successfully update the column with a value and
> everything continued to work.
>
> The database locking mode is set to NORMAL but the database is always
> opened exclusively. Could this been a concurrency issue in which 2
> connections hit the database at the exact same time? No data loss was
> reported yet.
>
> I've been working with SQLite for a while now, and this was the first time
> I've seen a constraint not catch something like this. Just wanted to pass
> along as I found it odd.
>
> Have a great day
>
> -Justin
>
>
> ------------------------------
>
> Message: 7
> Date: Wed, 15 Jan 2020 22:47:45 +0000
> From: Simon Slavin <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] Not Null Constraint Issue?
> Message-ID: <[hidden email]>
> Content-Type: text/plain;       charset=us-ascii
>
> On 15 Jan 2020, at 9:44pm, Justin Gielski <[hidden email]>
> wrote:
>
> > *"database is locked release restore point sqlite"*
>
> If there's nothing in your code that caused that to happen, then I would
> suspect a transient hardware glitch.  Does your code use SAVEPOINTs ?
>
> > The database locking mode is set to NORMAL but the database is always
> > opened exclusively. Could this been a concurrency issue in which 2
> > connections hit the database at the exact same time?
>
> SQLite is not meant to allow that, with the existance of the
> journal/shared-memory files acting as a mutex.  If it actually did happen,
> and you're not violating anything in the following document, then either
> you found a bug in SQLite, or you had hardware problems.
>
> >  No data loss was
> > reported yet.
>
> If you haven't already run "PRAGMA integrity_check" on a copy of the
> database file, do so.  And tell us whether it reports corruption.
>
> ------------------------------
>
> Message: 8
> Date: Thu, 16 Jan 2020 01:34:47 +0200
> From: R Smith <[hidden email]>
> To: [hidden email]
> Subject: Re: [sqlite] Not Null Constraint Issue?
> Message-ID: <[hidden email]>
> Content-Type: text/plain; charset=utf-8; format=flowed
>
>
> On 2020/01/16 12:47 am, Simon Slavin wrote:
> > On 15 Jan 2020, at 9:44pm, Justin Gielski <[hidden email]>
> wrote:
> >
> >> *"database is locked release restore point sqlite"*
> > If there's nothing in your code that caused that to happen, then I would
> suspect a transient hardware glitch.  Does your code use SAVEPOINTs ?
> >
> >> The database locking mode is set to NORMAL but the database is always
> >> opened exclusively. Could this been a concurrency issue in which 2
> >> connections hit the database at the exact same time?
> > SQLite is not meant to allow that, with the existance of the
> journal/shared-memory files acting as a mutex.  If it actually did happen,
> and you're not violating anything in the following document, then either
> you found a bug in SQLite, or you had hardware problems.
>
> Yes, or a software glitch. Remember that the SQLite database engine is
> actual software library code running alongside your own code in the same
> process, which means that if your code causes a memory fault (for
> whatever reason), the accompanying SQLite code could easily be on the
> receiving end of the memory corruption and also no longer work as
> expected. This is one of the fundamental differences of having the
> Database Engine as part of your process vs. having a process elsewhere
> on a server that functions independently (and can check constraints
> independently).
>
> This is not a typical thing in production though, more expected during
> debugging or development, but it can happen in production, especially
> having situations like the OP described with rogue database locks from
> "hung" processes, etc.
>
> @Justin: I'd start debugging by your processes/code that were hanging,
> finding out why that happens, and how your code paths could end up in
> such a state would probably solve a lot of problems (including the
> sqlite one). That said, if the you can engineer a situation in which it
> happens repeatably, that would be very interesting and the devs (and the
> rest of us) would be very interested.
>
>
> Cheers,
> Ryan
>
>
>
>
> ------------------------------
>
> Message: 9
> Date: Wed, 15 Jan 2020 19:44:57 -0600
> From: T J <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Subject: [sqlite] Test failures on GPFS
> Message-ID:
>         <
> [hidden email]>
> Content-Type: text/plain; charset="UTF-8"
>
> On Sunday, January 12, 2020, Roman Fleysher <
> [hidden email]>
> wrote:
>
> >
> > I use SQLite over GPFS , but in DELETE (which I think is the default)
> > mode. Not WAL mode. No issues with locking, except performance when
> > accessing concurrently from multiple nodes. As others pointed out, this
> has
> > to do with the overhead due to lock requests. GPFS must coordinate with
> > many nodes. My observation is that when concurrent access is from a few
> > nodes, the performance is OK even though number of nodes is always the
> > same. Thus, GPFS coordinates in some smart way only between nodes
> actively
> > involved.
> >
> > One reason I do not use mySQL with its more efficient network access is
> > that sys admin must set it up. With SQLite, I am independent. In
> addition,
> > in my SQL there are authentication issues to be dealt with. I rely on
> GPFS
> > file access permissions (access control list, ACL) to regulate access to
> > database.
> >
> > I heard about BeadrockDB, which internally uses SQLite and provides
> > network access with replication. I have not tried it and do not know what
> > is involved.
> >
> >
> >
> MySQL and similar would indeed be nice to use, but in addition to the
> administrative cost, there are also developer costs to get things set up so
> that every developer can do work in their own db without affecting the
> production db, as well as complexity costs with getting data into those
> dbs. Contrast this with just copying the sqlite file(s) as needed (though
> integrity concerns still exist).
>
> So I'm mostly weighing options. The data is very much many-reads,
> few-writes. Also considering just using an external locking service and
> simple flat files, but this has obvious downsides of fewer (if any) data
> types, no joins, no transactions, etc.
>
> I may give this a try and see if the perf hit is tolerable.
>
>
> ------------------------------
>
> Message: 10
> Date: Thu, 16 Jan 2020 11:53:25 +0100
> From: Dominique Devienne <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Subject: Re: [sqlite] Next Release? Visual release timeline?
> Message-ID:
>         <
> [hidden email]>
> Content-Type: text/plain; charset="UTF-8"
>
> On Wed, Jan 15, 2020 at 4:54 PM R Smith <[hidden email]> wrote:
> > On 2020/01/15 1:24 PM, Richard Hipp wrote:
> > >> (2) Assume the data is a JSON array of pairs.  The first element of
> > >> each pair is the release name (ex: "3.30.0") and the second element is
> > >> the time as a fractional year (ex: "2019.7775").
>
> Note that Richard replied to me private with a JSON array of this form:
>
> chronology = [{"hash":"xxxxxxxxxx","vers":"3.31.0","date":2020.0398},
> {"hash":"18db032d05","vers":"3.30.1","date":2019.7748},
> {"hash":"c20a353364","vers":"3.30.0","date":2019.7557},
> {"hash":"fc82b73eaa","vers":"3.29.0","date":2019.5202},
> {"hash":"884b4b7e50","vers":"3.28.0","date":2019.2875},
> {"hash":"bd49a8271d","vers":"3.27.2","date":2019.1506},
> ...]
>
> So with a little gymnastic to recover the date, and given the hashes,
> all the currently "hardcoded" <td><tr> elements can also be generated
> from this JSON array.
>
> Of course, some people disable JavaScript, so "server-side" rendering
> might be preferred.
>
> > We'd like to submit this layout as an option:
> > https://sqlitespeed.com/sqlite_releases.html
> >
> > Shown alongside the current list in simple form. Tried a few layouts,
> > not all work as well (SQLite releases are much more dense than Lua),
> > finally settled on the above, but left some options open.
>
> Interesting, thanks for the submission. --DD
>
>
> ------------------------------
>
> Subject: Digest Footer
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ------------------------------
>
> End of sqlite-users Digest, Vol 145, Issue 16
> *********************************************
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Not Null Constraint Issue?

Simon Slavin-3
On 17 Jan 2020, at 6:39pm, Justin Gielski <[hidden email]> wrote:

> After looking into the issue a bit more, it appears the INTEGER value was actually saved into the DB as an EMPTY not a NULL, which is currently allowed by the database constraints.

Could you show us the constraint you feel disallows those things ?  We might be able to find a loophole in the exact way you phrased it.

Also, could someone explain to me what EMPTY means here ?  I've seen '' called "empty string" but it doesn't seem to be normal SQL language.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Not Null Constraint Issue?

Justin Gielski
In reply to this post by Justin Gielski
>Could you show us the constraint you feel disallows those things ?  We
might be able to find a loophole in the exact way you phrased it.

Also, could someone explain to me what EMPTY means here ?  I've seen ''
called "empty string" but it doesn't seem to be normal SQL language.
-----------------------------------------------------------------------------

Simon

You are correct. When I say "empty" I am meaning "empty string". My
apologies if my nomenclature is incorrect here in terms of SQL speak.

Regarding the constraint, to column is set as INTEGER NOT NULL, but because
of SQLite's dynamic typing system (something I forget about), empty strings
can be passed into INTEGER columns as they are technically not null. Now if
a non numeric text would have made it into an INTEGER field, we would have
dissected the issue a lot faster (and there is almost no way that would
have happened anyway). Because it was an empty string value that made it in
there, it appeared as a NULL to us when we tried reading the database as
our database reader expected an INTEGER. It took as a while to figure out
that the NULL value wasn't really NULL, but an empty string value sitting
in an INTEGER field.

I definitely know to look out for this now.

From: Simon Slavin <[hidden email]>
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Not Null Constraint Issue?
Message-ID: <[hidden email]>
Content-Type: text/plain;       charset=us-ascii

On 17 Jan 2020, at 6:39pm, Justin Gielski <[hidden email]> wrote:

> After looking into the issue a bit more, it appears the INTEGER value was
actually saved into the DB as an EMPTY not a NULL, which is currently
allowed by the database constraints.

Could you show us the constraint you feel disallows those things ?  We
might be able to find a loophole in the exact way you phrased it.

Also, could someone explain to me what EMPTY means here ?  I've seen ''
called "empty string" but it doesn't seem to be normal SQL language.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users