[sqlite] "SQLite Database schema has changed" error
Hi, I am using SQLite 3.6.13, After issuing a vaccuum, I am inserting into the SQLite db, that time I am getting "SQLite Database schema has changed" error. According to this: http://www.mail-archive.com/sqlite-users@sqlite.org/msg04902.html, It should never occur in SQLite 3.0, but I am not understanding why is this error is occuring now. (I understand that rerunning the prepared statement will solve the problem, but just wanted to know whether I am missing something here) Manohar -- hope is the only thing which keeps us all happy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error : sqlite3.h:1722: parse error before string constant
Hi, Renaming the parameters did the trick. however I searched in the app for such declarations, I could not find any, But there are many third party libraries are included before that. One of them might have caused the problem. Thanks for the quick replies. Manohar On Sat, Apr 18, 2009 at 12:31 AM, D. Richard Hipp <d...@hwaci.com> wrote: > > On Apr 17, 2009, at 2:53 PM, manohar s wrote: > > > Hi > > > > I am getting this error: > > > > sqlite3.h:1757: parse error before string constant > > > > Whenever I am including the static sqlite library in my c++ app. > > This is > > happening with recent release 3.6.13. Earlier I never experienced any > > problem. In that line in the header file, This function is present: > > > > void sqlite3_randomness(int N, void *P); > > > > Strangely I am able to compile successfully if I comment out this > > line! > > > > 1) Am I doing anything wrong here? > > 2) Is it safe to comment this out? > > > > No, it is not safe to comment this line out. You need to fix the > problem, not just ameliorate the symptoms. > > Perhaps you have a line such as one of these: > > #define P "something" > #define N "something" > > Somewhere in one of your header files prior to the point where you > #include "sqlite3.h"? > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- hope is the only thing which keeps us all happy http://sqlyog.wordpress.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Error : sqlite3.h:1722: parse error before string constant
Hi I am getting this error: sqlite3.h:1757: parse error before string constant Whenever I am including the static sqlite library in my c++ app. This is happening with recent release 3.6.13. Earlier I never experienced any problem. In that line in the header file, This function is present: void sqlite3_randomness(int N, void *P); Strangely I am able to compile successfully if I comment out this line! 1) Am I doing anything wrong here? 2) Is it safe to comment this out? Regards, Manohar -- hope is the only thing which keeps us all happy http://sqlyog.wordpress.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Vacuum" command is failing with "SQL Error:Database or disk is full"
Also, In linux whether these temporary files are created while vacuuming? If yes, can you tell me where will this be created? manohar On Tue, Mar 17, 2009 at 6:20 PM, manohar s <manohar...@gmail.com> wrote: > Yes, the solution you suggested is working fine. But can't we change this > through SQLite? > > Thanks and Regards, > Manohar.S > > > On Wed, Mar 11, 2009 at 12:44 PM, Mihai Limbasan <mihai...@security.ro>wrote: > >> manohar s wrote: >> > Hi, >> > I am trying to execute "PRAGMA page_size=4096; Vacuum;" on a SQLite >> DB(Size >> > 1.5 GB), On a drive which has 9 GB free space (But my C: has 150 MB free >> is >> > this an issue?). But it is failing with "SQL Error:Database or disk is >> full" >> > error everytime. SQLite version: 3.6.11. >> > >> > 1) Am I missing anything here? >> > >> > Regards, >> > manohar.S >> > >> The error refers to the file system hosting the temporary files >> location. Try setting the TEMP environment variable to a location with >> more free space, reboot your system, and it willwork. It's actually a >> really bad idea to have little space ont he file system hosting the >> temporary file location... >> >> HTH, >> Mihai >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Vacuum" command is failing with "SQL Error:Database or disk is full"
Yes, the solution you suggested is working fine. But can't we change this through SQLite? Thanks and Regards, Manohar.S On Wed, Mar 11, 2009 at 12:44 PM, Mihai Limbasan <mihai...@security.ro>wrote: > manohar s wrote: > > Hi, > > I am trying to execute "PRAGMA page_size=4096; Vacuum;" on a SQLite > DB(Size > > 1.5 GB), On a drive which has 9 GB free space (But my C: has 150 MB free > is > > this an issue?). But it is failing with "SQL Error:Database or disk is > full" > > error everytime. SQLite version: 3.6.11. > > > > 1) Am I missing anything here? > > > > Regards, > > manohar.S > > > The error refers to the file system hosting the temporary files > location. Try setting the TEMP environment variable to a location with > more free space, reboot your system, and it willwork. It's actually a > really bad idea to have little space ont he file system hosting the > temporary file location... > > HTH, > Mihai > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- hope is the only thing which keeps us all happy http://sqlyog.wordpress.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to interrupt a running query in SQLite?
sqlite3_errcode() was not used, I was just checking the return code of sqlite3_step(), I read about the problem with sqlite3_step(), I will think about using sqlite3_prepare_v2(). It is returning with SQLITE_INTERRUPT if I use sqlite3_errcode(). Thanks for replying, Manohar On Fri, Mar 13, 2009 at 4:35 PM, Dan <danielk1...@gmail.com> wrote: > > On Mar 13, 2009, at 5:41 PM, manohar s wrote: > > > Thanks for that. I Should have searched that :). > > > > Now I am Executing the Vacuum query, and sqlite3_interrupt() is > > called from > > a different thread, It is interrupted. > > But once interrupted query execution is failing with SQLITE_ERROR. > > It has to > > fail with SQLITE_INTERRUPT right? > > > > sqlite3_errmsg() is returning proper error "[DB: C:\Documents and > > Settings\All Users.WINDOWS\Application Data/data/sqlitedb.data] > > interrupted". > > > > Am I missing anything here? > > Which function returns SQLITE_ERROR? What does sqlite3_errcode() > return? > > Dan. > > > > > > > > > > I am testing with SQLite 3.6.11. > > > > Regards, > > Manohar.S > > > > On Thu, Mar 12, 2009 at 4:10 PM, Dan <danielk1...@gmail.com> wrote: > > > >> > >> On Mar 12, 2009, at 4:54 PM, manohar s wrote: > >> > >>> Hi, > >>> Is there any way to interrupt a query which is running? I am > >>> executing a > >>> VACUUM query which takes lot of time to complete. I want to stop > >>> this > >>> execution forcefully whenever I want to stop. Is this possible? > >> > >> The sqlite3_interrupt() API: > >> > >> http://www.sqlite.org/c3ref/interrupt.html > >> > >> Dan. > >> > >> > >> > >>> Regards, > >>> Manohar > >>> ___ > >>> sqlite-users mailing list > >>> sqlite-users@sqlite.org > >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- hope is the only thing which keeps us all happy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to interrupt a running query in SQLite?
No, I am using sqlite3_prepare(). Regards, Manohar.S On Fri, Mar 13, 2009 at 4:23 PM, Simon Davies < simon.james.dav...@googlemail.com> wrote: > 2009/3/13 manohar s <manohar...@gmail.com>: > > Thanks for that. I Should have searched that :). > > > > Now I am Executing the Vacuum query, and sqlite3_interrupt() is called > from > > a different thread, It is interrupted. > > But once interrupted query execution is failing with SQLITE_ERROR. It has > to > > fail with SQLITE_INTERRUPT right? > > > > sqlite3_errmsg() is returning proper error "[DB: C:\Documents and > > Settings\All Users.WINDOWS\Application Data/data/sqlitedb.data] > > interrupted". > > > > Am I missing anything here? > > > > I am testing with SQLite 3.6.11. > > > > Regards, > > Manohar.S > > Are you using the v2 version of prepare? > http://www.sqlite.org/c3ref/prepare.html > > Rgds, > Simon > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- hope is the only thing which keeps us all happy http://sqlyog.wordpress.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to interrupt a running query in SQLite?
Thanks for that. I Should have searched that :). Now I am Executing the Vacuum query, and sqlite3_interrupt() is called from a different thread, It is interrupted. But once interrupted query execution is failing with SQLITE_ERROR. It has to fail with SQLITE_INTERRUPT right? sqlite3_errmsg() is returning proper error "[DB: C:\Documents and Settings\All Users.WINDOWS\Application Data/data/sqlitedb.data] interrupted". Am I missing anything here? I am testing with SQLite 3.6.11. Regards, Manohar.S On Thu, Mar 12, 2009 at 4:10 PM, Dan <danielk1...@gmail.com> wrote: > > On Mar 12, 2009, at 4:54 PM, manohar s wrote: > > > Hi, > > Is there any way to interrupt a query which is running? I am > > executing a > > VACUUM query which takes lot of time to complete. I want to stop this > > execution forcefully whenever I want to stop. Is this possible? > > The sqlite3_interrupt() API: > > http://www.sqlite.org/c3ref/interrupt.html > > Dan. > > > > > Regards, > > Manohar > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to interrupt a running query in SQLite?
Hi, Is there any way to interrupt a query which is running? I am executing a VACUUM query which takes lot of time to complete. I want to stop this execution forcefully whenever I want to stop. Is this possible? Regards, Manohar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "Vacuum" command is failing with "SQL Error:Database or disk is full"
Hi, I am trying to execute "PRAGMA page_size=4096; Vacuum;" on a SQLite DB(Size 1.5 GB), On a drive which has 9 GB free space (But my C: has 150 MB free is this an issue?). But it is failing with "SQL Error:Database or disk is full" error everytime. SQLite version: 3.6.11. 1) Am I missing anything here? Regards, manohar.S -- hope is the only thing which keeps us all happy http://sqlyog.wordpress.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite caching
Thanks for your quick replies, although restarting my machine, disc cache is cleared, I am trying to find an utility which could do the job without requiring a restart. Regards, Manohar.S On Mon, Feb 23, 2009 at 1:47 PM, Dan <danielk1...@gmail.com> wrote: > > On Feb 23, 2009, at 2:44 PM, manohar s wrote: > > > Hi, > > I am doing some performance analysis on my SQLite queries. The > > problem is > > SQLite seems to be caching query results. > > I tried restarting my program, that is not helping. only if i don't > > access > > that database for 2 days then it is giving proper profile data. > > * > > Question* > > 1) How can I disable query result caching in SQLite? > > Assuming you are using SQLite directly, SQLite does not cache query > results. It does cache pages of the database file in memory to reduce > the amount of data loaded from the disk. This cache is just in regular > heap memory, so it is destroyed when the process using sqlite exits. > > Probably what you are seeing is that the OS caches recently accessed > file-system sectors. The first time you run the SQLite query data must > be loaded from the magnetic disk. The second and subsequent runs > retrieve data from the OS cache. Much faster. > > Dan. > > > > > > > Regards, > > Manohar.S > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- hope is the only thing which keeps us all happy http://sqlyog.wordpress.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite caching
Hi, I am doing some performance analysis on my SQLite queries. The problem is SQLite seems to be caching query results. I tried restarting my program, that is not helping. only if i don't access that database for 2 days then it is giving proper profile data. * Question* 1) How can I disable query result caching in SQLite? Regards, Manohar.S ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Very slow query execution with SQLite Database
Hi, I tried with concatenated index (by dropping an index which was already present on timestamp_id) It took 12 seconds for completion, so there was no improvement in speed. > If we might assume your table is 1.5 GB in size and you do that query you > have to do a full table scan for it. (unless SQLite can use the PK index if > it exists to speed it up). It does have a concatenated PK on timestamp_id and metric_id, so it does not have to do full table scan right? Regards, Manohar.S On Fri, Jan 23, 2009 at 10:31 PM, Michael Schlenker <m...@contact.de> wrote: > manohar s schrieb: > > I have a SQLite database which is of size 1.5 GB. The problem that it is > > taking a lot of time (12 seconds after execution of vacuum) to execute a > * > > SELECT* query. > > > > Query : > > SELECT metric_id, MAX(timestamp_id) AS timestamp_id_max FROM > > snapshot_master GROUP BY metric_id > > > > I do not understand why this query has to take 12 seconds?? I tried to > > optimize by creating more indexes but no luck. > > BTW, before execution of vacuum, time taken for Query to execute is > aprrox. > > 17 minutes(1074890649319 nano seconds) > > > > I am using sftp_profile for finding out the time. > > > > 1) What am I doing wrong here? > > 2) Is there any problem in the database design? > > If we might assume your table is 1.5 GB in size and you do that query you > have to do a full table scan for it. (unless SQLite can use the PK index if > it exists to speed it up). > > So your probably I/O bound, 1.5 GB in 12 seconds is about 130MB/s, sounds > like full read speed of a current harddisk. > > Michael > > -- > Michael Schlenker > Software Engineer > > CONTACT Software GmbH Tel.: +49 (421) 20153-80 > Wiener Straße 1-3 Fax:+49 (421) 20153-41 > 28359 Bremen > http://www.contact.de/ E-Mail: m...@contact.de > > Sitz der Gesellschaft: Bremen > Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe > Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215 > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Very slow query execution with SQLite Database
I have a SQLite database which is of size 1.5 GB. The problem that it is taking a lot of time (12 seconds after execution of vacuum) to execute a * SELECT* query. Here is the create Table statement: CREATE TABLE IF NOT EXISTS [snapshot_master] ( PRIMARY KEY (metric_id, timestamp_id), [timestamp_id] INTEGER NOT NULL, [metric_id] INTEGER NOT NULL, [metric_now] TEXT, [metric_diff] TEXT ) Index on this Table for the column: *timestamp_id* Query : SELECT metric_id, MAX(timestamp_id) AS timestamp_id_max FROM snapshot_master GROUP BY metric_id I do not understand why this query has to take 12 seconds?? I tried to optimize by creating more indexes but no luck. BTW, before execution of vacuum, time taken for Query to execute is aprrox. 17 minutes(1074890649319 nano seconds) I am using sftp_profile for finding out the time. 1) What am I doing wrong here? 2) Is there any problem in the database design? Waiting for your response, Manohar.S ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_profile() not working with SQLite 3.6.10
Hi, I am trying to profile queries on a database of size 2 GB, but the code inside callback is not executing. Here is the snippet: ret = sqlite3_open("before_vacuum.data", ); if(ret != SQLITE_OK) printf("error in opening sqlite database file!\n"); ret = sqlite3_prepare(vSqlite, query, strlen(query), ,(const char **)); if(ret != SQLITE_OK) printf("preparing SQLite query failed\n"); sqlite3_profile(vSqlite, xProfile, NULL); ret = sqlite3_step(stmt); if(ret != SQLITE_DONE && ret != SQLITE_ROW) { if(ret == SQLITE_ERROR) printf("error in executing the query!\n"); } and callback: void xProfile(void * pArg, const char * pQuery, sqlite3_uint64 pTimeTaken) { printf("%s", pQuery);// It never executes here printf("%I64d", pTimeTaken); } I downloaded the latest source code and compiled it as a static library. 1) Am I doing something wrong here? [I couldnt find even a single sample source or snippet regarding this :( ] 2) Do I need to take care of any compilation option while building SQLite? Waiting for answers, Manohar.S hope is the only thing which keeps us all happy http://sqlyog.wordpress.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] API to find number of rows returned by select query
Hi all, Is there any SQLite C API to find number of rows returned by select query? right now we have to do workaround like this, "Select count(*) as Count from (select .) ". Regards, Manohar.S -- hope is the only thing which keeps us all happy http://sqlyog.wordpress.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Convert a string to Timestamp using SQLite
Hi all, I have a string of type "080110 17:32:34" where.08 refers year in yy format and 01 refers to month(mm) jan and 10 refers date(dd). I want to insert this into SQLite as any standard timestamp format such as -mm-dd HH:MM:SS or unixepoch etc.. Is it possible to do this? Please Help Thanks in advance Manohar.s -- hope is the only thing which keeps us all happy