[sqlite] .dump and transactions
Hello together, I run a small script every night via cron to backup a database using the ".dump" statement. SQlite version is 3.6.6.2 on Linux. Normally this script works fine and from time to time I get a backup file that looks like this: --- BEGIN TRANSACTION; END TRANSACTION; --- Consider you have a database with a simple table. Acccess the database using the command line "sqlite3" tool and do - BEGIN TRANSACTION; INSERT INTO simple_table (xyz) ... - Now open another, concurring instance of the sqlite3 command line tool and issue a ".dump" command. It will then produce the empty transaction output mentioned first. Shouldn't the command line tool wait for a locking timeout or atleast return BUSY when it can't dump the database? Is there a better way to backup the database? Thanks in advance, Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .dump and transactions
On Friday, 9. January 2009 10:34:32 Thomas Jarosch wrote: > I run a small script every night via cron to backup a database > using the ".dump" statement. SQlite version is 3.6.6.2 on Linux. > > Normally this script works fine and from time to time > I get a backup file that looks like this: > --- > BEGIN TRANSACTION; > END TRANSACTION; > --- Here's a short example to reproduce the problem: sqlite3 test.db create table test (name varchar(16)); begin transaction; insert into test values ('test'); Now open a second shell and do this: sqlite3 test.db .dump This will output: --- BEGIN TRANSACTION; END TRANSACTION; --- I tested sqlite 3.5.9 on my workstation, it correctly dumps the table even if a transaction is open, only 3.6.6.2 shows this behavior so far. Is this supposed to be? Cheers, Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .dump and transactions
On Monday, 12. January 2009 13:52:47 P Kishor wrote: > > Here's a short example to reproduce the problem: > > > > sqlite3 test.db > > create table test (name varchar(16)); > > begin transaction; > > insert into test values ('test'); > > did you forget to COMMIT here? Thanks for your reply. In fact it's the core of the problem: You can't dump a database if there is an open, uncommited transaction. This used to work in 3.5.9 and is needed for creating backups of databases. If an open transaction would block the dump of the database, then the second command line tool should busy wait until a timeout occurs or atleast return an error message. Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .dump and transactions
On Monday, 12. January 2009 14:20:40 Thomas Jarosch wrote: > If an open transaction would block the dump of the database, > then the second command line tool should busy wait until a timeout occurs > or atleast return an error message. I finally tracked this down by testing sqlite 3.6.10 on my workstation and the target system. The target system is Redhat 6.2 based (glibc 2.1.3) and running kernel 2.6.27.11. The glibc will be upgreaded soon. A strace run of the ".dump" command showed strange behavior: write(1, "BEGIN TRANSACTION;\n", 19BEGIN TRANSACTION; )= 19 brk(0x8055000) = 0x8055000 fcntl(3, F_SETLK, {type=F_RDLCK, whence=SEEK_SET, start=1073741824, len=0}) = -1 EAGAIN (Resource temporarily unavailable) fcntl(3, F_SETLK, {type=F_RDLCK, whence=SEEK_SET, start=1073741824, len=0}) = -1 EAGAIN (Resource temporarily unavailable) write(1, "COMMIT;\n", 8COMMIT; It turned out that the "configure" script for 3.6.10 generated by autoconf 2.59 incorrectly detected large file support on this system. config.h looked like this: #define _FILE_OFFSET_BITS 64 /* #undef _LARGE_FILES */ I've rebuilt "configure" with autoconf 2.61 and now it detects this: /* #undef _FILE_OFFSET_BITS */ /* #undef _LARGE_FILES */ I've now manually disabled large file support and everything works fine. Cheers, Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [PATCH 1/2] Fix memory corruption in test routine
Credit goes to "cppcheck". The author or authors of this code dedicate any and all copyright interest in this code to the public domain. We make this dedication for the benefit of the public at large and to the detriment of our heirs and successors. We intend this dedication to be an overt act of relinquishment in perpetuity of all present and future rights to this code under copyright law. Signed-off-by: Thomas Jarosch --- src/test1.c |3 ++- 1 files changed, 2 insertions(+), 1 deletions(-) diff --git a/src/test1.c b/src/test1.c index 7fdf54b..2045c81 100644 --- a/src/test1.c +++ b/src/test1.c @@ -4395,7 +4395,8 @@ static u8 *sqlite3_stack_baseline = 0; static void prepStack(void){ int i; u32 bigBuf[65536]; - for(i=0; ihttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [PATCH 2/2] Fix file descriptor leak on error
Credit goes to "cppcheck". The author or authors of this code dedicate any and all copyright interest in this code to the public domain. We make this dedication for the benefit of the public at large and to the detriment of our heirs and successors. We intend this dedication to be an overt act of relinquishment in perpetuity of all present and future rights to this code under copyright law. Signed-off-by: Thomas Jarosch --- tool/lemon.c |2 ++ 1 files changed, 2 insertions(+), 0 deletions(-) diff --git a/tool/lemon.c b/tool/lemon.c index bd2938b..1f8082c 100644 --- a/tool/lemon.c +++ b/tool/lemon.c @@ -2521,12 +2521,14 @@ void Parse(struct lemon *gp) if( filebuf==0 ){ ErrorMsg(ps.filename,0,"Can't allocate %d of memory to hold this file.", filesize+1); +fclose(fp); gp->errorcnt++; return; } if( fread(filebuf,1,filesize,fp)!=filesize ){ ErrorMsg(ps.filename,0,"Can't read in all %d bytes of this file.", filesize); +fclose(fp); free(filebuf); gp->errorcnt++; return; -- 1.7.4.4 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ISO-8601 date string and '>' comparison buggy
Hi, consider this stripped-down database: -- CREATE TABLE "horde_alarms" ("id" INTEGER PRIMARY KEY, "alarm_end" datetime); INSERT INTO "horde_alarms" VALUES(1, '2013-08-28 22:00:00'); -- These queries work fine: -- sqlite> SELECT * FROM horde_alarms WHERE alarm_end < '2013-08-28T23:08:48'; 1|2013-08-28 22:00:00 sqlite> SELECT * FROM horde_alarms WHERE alarm_end > '2013-08-28 18:08:48'; 1|2013-08-28 22:00:00 -- This query does not work (as used by Horde): -- SELECT * FROM horde_alarms WHERE alarm_end > '2013-08-28T18:08:48'; -- Looks like the handling of ISO-8601 date strings has a problem here. Tested on sqlite 3.7.17. Version 3.6.x is also affected. Best regards, Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ISO-8601 date string and '>' comparison buggy
Zitat von Igor Tandetnik : SQLite doesn't have "datetime" data type. All these values are plain strings, and are compared as such. It just so happens that, if you use a suitable format consistently, usual string comparisons also order dates and times correctly. So, don't mix and match the format with and without T in the middle. Choose one, and stick to it. thanks for the quick explanation. Now that's what I would call a funny issue :) I'll prepare an upstream horde fix. Best regards, Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users