Re: [sqlite] SQLite Database Crash

[Brice André]

Dear Patel,

When does your java program crashes ? At start-up when you open the
database ? When you perform DB queries ?

Which Java library do you use to interface sqlite ? How do you use it ?

Do you perform queries that retrieve big quantity of data ?

SQLite can be tuned to use more or less memory. But SQLite is not the only
element that uses memory in your setup and before trying to tune this
library, you should try to get a whole picture of where the available
memory is used. The way the java library you use interfaces sqlite to your
program, and the way you use this library may also have a huge impact on
the total memory your application needs.

Regards,
Brice

2017-08-23 6:49 GMT+02:00 Vihangi Patel :

> Hello SQLite Team,
>
> I am using SQLite database in Linux with 256MB RAM. With Java program, I
> am trying to connect with database. But that java process is crashed and
> giving "out of memory" error.
>
> I tried same test with 512MB RAM , and its working properly.
>
> Can you give me some solution using which I can create and connect
> database with 256MB RAM.
>
> --
>
> Regards,
> Vihangi Patel
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Undo-Redo

[Brice André]

Dear Tom,

There is a pragma that allows you retrieving the structure of a table :
PRAGMA table_info(table_name);

From the result of the command, you can programmatically create your
triggers.

Regards,

Brice

2017-07-15 21:11 GMT+02:00 Thomas Flemming :

> Hi Sqlite-users,
>
> I'm busy doing a undo-redo with triggers as described here:
> https://sqlite.org/undoredo.html
>
> and wondering, if there is a function to build the string for the
> insert-command automatically with all fields from that table.
>
> In the sample its only 3 fields, but in real life, this is much more so
> this could safe a lot of typing.
>
> CREATE TEMP TRIGGER ex1_dt BEFORE DELETE ON ex1 BEGIN
>   INSERT INTO undolog VALUES(NULL,'INSERT INTO ex1(rowid,a,b,c)
> VALUES('||old.rowid||','||quote(old.a)||','||quote(old.b)||
>','||quote(old.c)||')');
>
> Thanks,
> Tom
>
>
>
> --
> /
> **   Flemming Software Development CC
> **   Thomas Flemming
> **   PO Box 81244
> **   Windhoek, Namibia
> **   http://www.quovadis-gps.com
> **   mail  t...@qvgps.com
> **   +264 (0)81 3329923
> **   +49 (0)6182 8492599
> ***/
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REQUETES

[Brice André]

Hello,

Maybe replace "ZONAGES_SHP_TAB2016".'identifiant' by
"ZONAGES_SHP_TAB2016"."identifiant"
or by
"ZONAGES_SHP_TAB2016".identifiant ?

Regards,
Brice

2016-08-31 15:21 GMT+02:00 Igor Korot :

> Hi,
>
> On Wed, Aug 31, 2016 at 9:14 AM, REGIANY Lucie 
> wrote:
> >
> > HELLO
> > I WOULD DO SELECT FROM MY TABLE
> > select *
> > FROM "_L93_ZONAGES_exportdirectory_list"
> > WHERE "_L93_ZONAGES_exportdirectory_list".'TYPE' LIKE 'TAB%'
> > OR
> > "_L93_ZONAGES_exportdirectory_list".'TYPE' LIKE 'SHP%'
> >
> > AND AND SAVE THE RESULT IN A NEW TABLE ZONAGES__SHP_ TAB2016
> >
> >
> > I WOULD DO SELECT FROM MY TABLE
> >
> > select *
> > FROM "ZONAGES_SHP_TAB2016"
> > WHERE "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%gare%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%transport%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%CFP%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%tgv%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%intermobilite%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%velo%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%ferre%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%noeud%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%aeroport%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%velo%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%flux%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%deplacement%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%route%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%eurocopter%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%bus%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%Viarhonna%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '% aérien%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%trajet%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%ligne%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%chemin%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%voyageur%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%mobilite%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%interurbain%'
> > OR  "ZONAGES_SHP_TAB2016".'identifiant' LIKE '%navette%'
> > ;
> >
> > I HAVE A ERROR
> > why please? Help me it's possible
>
> What kind of error do you have? Can you post an error message?
> Is first query returned any results?
>
> BTW, I would create a view based on the first select and then use
> that view in the second query. Will simplify things a bit.
>
> Thank you.
>
> >
> > AND
> >
> > AFTER THE SELECT
> > I WOULD COUNT ON THE FILE FIELD DUPLICATES UP ALSO THE FIELD ID AND DATE
> OF CHANGE
> >
> > TANK YOU
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database Corrupt While Disk Full

[Brice André]

Dear Simon,

I am a little worried with your last post because, I must admint that I do
not take special measures in my application when such errors occur to
guarantee that no more DB access are performed.

I have thus the following questions :

   1. What would be the proper reaction to SQLITE_FULL, SQLITE_IOERR,
   SQLITE_CANTOPEN ? Perform a sqlite3_close ? Just exit the program as is
   without touching the database file descriptor ? And once the program has
   left, can we perform a sqlite3_open ?
   2. What is the exhaustive list of error codes that shall be considered
   as fatal ?
   3. Is it documented somewhere ?

Thanks in advance,

Brice

2016-01-14 11:41 GMT+01:00 Simon Slavin :

>
> On 14 Jan 2016, at 9:58am, sanhua.zh  wrote:
>
> > Through the error code timeline, it shows that much of SQLITE_FULL,
> SQLITE_IOERR, SQLITE_CANTOPEN happened before SQLITE_CORRUPT.
>
> You should never get to this situation.  The three result codes
> SQLITE_FULL, SQLITE_IOERR, SQLITE_CANTOPEN are fatal errors.  Your program
> should crash there, it must not try to continue using the database.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Random performance issues with SQLite

[Brice André]

Dear all,

Just to give some feedback on this problem : I changed the HDD of my
client, and configured his anti-virus to exclude the database directory.

I don't know which one of those actions solved the problem, but it now
seems to work like a charm. I will still wait a few days/weeks to be sure
the problem does not come back, as it was quite random, but I am relatively
confident.

Thanks to all for your advices.

Regards,

Brice



2015-11-10 17:44 GMT+01:00 Scott Hess :

> Also look for failures at the bus level.  I have had cases where some
> component was mucking up the bus, and got long pauses because the OS kept
> resetting the bus.  These caused huge pauses, like 30 seconds or more, and
> due to how various OS components mostly do synchronous disk access, that
> kind of thing can have pretty wide-ranging effects.
>
> [For Linux, usually the errors will show up in the dmesg output.  No idea
> where to look for other operating systems.]
>
> -scott
>
>
> On Tue, Nov 10, 2015 at 1:52 AM, Simon Slavin 
> wrote:
>
> >
> > On 10 Nov 2015, at 8:18am, OBones  wrote:
> >
> > > However, using tools to read the SMART values for this harddrive, I
> > noticed that it had been eating up spare sectors for quite a while.
> >
> > That's actually a good suggestion for you: look at the SMART values, run
> > any hard disk diagnostics you have, etc..  Once you've backed-up, of
> course.
> >
> > > So to answer, yes, it is definitely possible that the hardware is
> > crashing and windows does not notice it.
> >
> > You will see errors that components report to Windows.  But a dodgy
> sector
> > on a drive may just cause the driver to retry reading repeatedly (mystery
> > delays) at the driver level, and be reported as an error only if reading
> > fails completely.
> >
> > Simon.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Random performance issues with SQLite

[Brice André]

Dear all,

Thank you very much for those advices. I will try them and get back if it
does not solve the issue.

Regarding the advice of Simon on HDD starting to fail, I have one more
question. In fact, I suspected this, as my client has other problems with
his computer. But, in such case, I expected to get logs in Windows Events
Journal. I scanned those logs and did not see any logs regarding Hdd
problems or related stuff.

So, my question is the following : does someone has already experienced
performance issues on database with an HDD device starting to fail (without
any corruption problem) ? And if yes, is it possible that those performance
issues come with no notification in the windows log journal ?

Thanks in advance,

Brice

2015-11-09 17:39 GMT+01:00 Simon Slavin :

>
> On 9 Nov 2015, at 3:21pm, Brice Andr? 
> wrote:
>
> > So, does someone has an idea on what can cause such performance issues on
> > some sqlite DB requests ?
>
> Since you
>
> 1) are getting delays and not errors, and
> 2) you are using very common OS and hardware, and
> 3) you are accessing a database from an internal disk,
>
> I strongly suspect that any fault is not in SQLite.  From experience this
> sort of problem is caused by either
>
> A) an anti-virus package or
> B) a hard disk which is just starting to fail and will get worst until it
> fails completely
>
> Diagnosis and fixes should relate to trying a different disk or
> temporarily disabling the anti-virus software, not to anything inside
> SQLite.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Random performance issues with SQLite

[Brice André]

OK, but if this scenario was at the origin of my problem, I guess I would
have such a log each time a db query freezes ?

If yes, then there is probably another problem, as I am sure I got such
freezes with no such logs from sqlite.

By the way, if the antivirus is at the origin of the problem, which
practical solution may I find to the problem ? I cannot request my client
to deactivate his antivirus. And he is using a very common one (norton).
So, I would probably not be the only one to have this problem ?

Is there a way to request SQlite to don't release control of the file
between two operations ? As I only open one descriptor, this could maybe
help if it was possible ? Maybe I could ask sqlite to take control of the
file at the beginning of my program, and request it to release control at
the end ?

Thanks for your help,

Regards,

Brice

2015-11-09 17:26 GMT+01:00 Richard Hipp :

> So the problem is occurring on a call to GetFileAttributesEx().
>
> I'm guessing that this is an adverse interaction with an anti-virus
> (AV) program.
>
> The scenario might be something like this.
>
> (1) SQLite writes one small part of a complex change to the database file
> (2) The AV seizes control and scans the entire database, taking a long
> time to do so (because the file is large) and denying SQLite access
> while it does.
> (3) The AV finally releases control of the file
> (4) Goto 1.
>
>
> On 11/9/15, Brice Andr?  wrote:
> > Dear Richard,
> >
> > Thanks for your answer. The version of sqlite I am using is the 3.9.1
> > amalgamation.
> >
> > But, as stated above, I am not sure my freeze problem is linked to the
> > strange log observed at my client as this log does not systematically
> > appears on database queries that freeze. Those logs sometimes appear on
> > queries that do not freeze, and sometimes, queries that freeze do not
> have
> > those logs.
> >
> > I checked in the different logs I have collected, and it seems to be
> always
> > the same line (39428) that causes this strange log.
> >
> > Regards,
> >
> > Brice
> >
> > 2015-11-09 16:36 GMT+01:00 Richard Hipp :
> >
> >> On 11/9/15, Brice Andr?  wrote:
> >> >
> >> > 08-11-2015 (13h25): Exception: SQLiteDataBase : Error callback -> 27 :
> >> > delayed 25ms for lock/sharing conflict at line 39428
> >>
> >> The "line 39428" refers to a specific source code line number in the
> >> "sqlite3.c" file, which in turn depends on exactly which version of
> >> SQLite you are running - information which you failed to share with
> >> us.  If you will let us know which version of SQLite you are running,
> >> we might be able to figure out what is going wrong.
> >> --
> >> D. Richard Hipp
> >> drh at sqlite.org
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users at mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Random performance issues with SQLite

[Brice André]

Dear Richard,

Thanks for your answer. The version of sqlite I am using is the 3.9.1
amalgamation.

But, as stated above, I am not sure my freeze problem is linked to the
strange log observed at my client as this log does not systematically
appears on database queries that freeze. Those logs sometimes appear on
queries that do not freeze, and sometimes, queries that freeze do not have
those logs.

I checked in the different logs I have collected, and it seems to be always
the same line (39428) that causes this strange log.

Regards,

Brice

2015-11-09 16:36 GMT+01:00 Richard Hipp :

> On 11/9/15, Brice Andr?  wrote:
> >
> > 08-11-2015 (13h25): Exception: SQLiteDataBase : Error callback -> 27 :
> > delayed 25ms for lock/sharing conflict at line 39428
>
> The "line 39428" refers to a specific source code line number in the
> "sqlite3.c" file, which in turn depends on exactly which version of
> SQLite you are running - information which you failed to share with
> us.  If you will let us know which version of SQLite you are running,
> we might be able to figure out what is going wrong.
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Random performance issues with SQLite

[Brice André]

Dear all,

I experience some performance issues with SQLite.

I have an application that uses SQLite to store user data, including agenda
information. One of my client has a relatively big database (around 1.3Gb).
I have indexed the database so that the queries take a reasonable time to
execute.

In the general case, the complete update  process of the more demanding
tasks (handling of user action, search in database and screen refresh)
takes less than 1 second.

But, at very sparse moments, for reasons I cannot identify, one of my
clients experiences freezes in the application that can raise up to several
minutes ! After those freezes, the application continues to work normally

I instrumented my application to find the origin of the freeze. From this
instrumentation, I can guarantee that the time is spent in the database
request.

But what is strange is that executing exactly the same request, with
exactly the same parameters on exactly the same database content just
after, or just before the problem occurs does not cause any performance
issue.

I logged all information on what was happening on my client database (I
currently only have one client that encounters this strange behaviour), and
the only strange thing that I collected from his computer is the following
kind log from sqlite :

08-11-2015 (13h25): Exception: SQLiteDataBase : Error callback -> 27 :
delayed 25ms for lock/sharing conflict at line 39428
08-11-2015 (13h25): Exception: SQLiteDataBase : Error callback -> 27 :
delayed 75ms for lock/sharing conflict at line 39428

27 is the SQLIte error code, and the text located after is the sqlite
message.

The occurrence of such warnings do not seem to be direcly linked to my
problem, as they can appear on database access that do not suffer from
performance issues, and some database access with performance issues can
appear without those strange logs.

But, for me, those logs are strange because :
   - I never encountered them on other computers (even if it's true that I
did not pay attention to those logs before this problem was reported by my
client)
   - my application is supposed to be the only process to access the
database, and only one thread of my application is accessing the database.

So, does someone has an idea on what can cause such performance issues on
some sqlite DB requests ?

Thanks in advance,

Brice

PS: the problem is present at a client computer running a Windows 7 over a
relatively old computer (intel core 2 duo) with 4G ram. The version of
sqlite is the last one (because I upgraded hoping this would solve the
issue), but was also observerd with an older version of sqlite.