Re: [sqlite] Not Null Constraint Issue?

[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 
To: SQLite mailing list 
Subject: Re: [sqlite] Not Null Constraint Issue?
Message-ID: <46053091-b9ab-43c4-b7f9-c89d70986...@bigfraud.org>
Content-Type: text/plain;   charset=us-ascii

On 17 Jan 2020, at 6:39pm, Justin Gielski  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Not Null Constraint Issue?

[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 <
sqlite-users-requ...@mailinglists.sqlite.org> wrote:

> Send sqlite-users mailing list submissions to
> sqlite-users@mailinglists.sqlite.org
>
> 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
> sqlite-users-requ...@mailinglists.sqlite.org
>
> You can reach the person managing the list at
> sqlite-users-ow...@mailinglists.sqlite.org
>
> 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 
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Next Release? Visual release timeline?
> Message-ID: 
> 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  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: "#22",
>  yearBackground: "#EE",
>  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 th

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users