Re: [sqlite] Bug in sqlite? Can't read tables just after creating them
Hi Richard, The problem is actually elsewhere. I changed the filesystem and it works fine. So the problem is not with sqlite but with our special filesystem. Sorry for having raised the alarm too quickly! Best regards, Fabrice -Original Message- From: Richard Hipp d...@sqlite.org Sender: sqlite-users-bounces@sqlite.orgDate: Tue, 11 Mar 2014 07:27:37 To: General Discussion of SQLite Databasesqlite-users@sqlite.org Reply-To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Bug in sqlite? Can't read tables just after creating them On Tue, Mar 11, 2014 at 6:35 AM, ftrib...@falcon-one.com wrote: Hi, I found a problem in sqlite. In essence, here is what my code does: 1 - It opens a database file 2 - If it doesn't find certain tables, it assumes this is a new one and creates the necessary tables and add a few entries in one of them (let's call it mytable) 3 - It queries mytable by doing a 'SELECT xyz, abc FROM mytable;' When I run this code on Debian, I don't have any problem. When I run it on my evaluation kit (ARM9), step 3 fails saying 'no such table: mytable'. I found out that closing and then re-opening the database file works. So I do steps 1 and 2, and close and re-open the database file, and now step 3 works fine! For the time being, I have this workaround of closing/re-opening the database file, but that's really a kludge. Any idea about from where this could come from? No ideas. Please enable the error and warning log ( http://www.sqlite.org/errlog.html) and see if that provides any further information. Many thanks for any ideas! Fabrice ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ 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] Question about how sqlite recovers after a power loss
Hi Simon, If I run my program step-by-step, I can see that the sqlite database is not modified when I do an INSERT. It is modified only when I do a COMMIT. I read the page on the sqlite website detailing how sqlite deals with crashes and untimely power losses, and it says that the journal file is created just before modifying the database and is a copy of the areas that are to be modified. Once the journal file is committed to the disk, the database is modified. But this is not what I can see. What I can see is that the database file is not modified when I do the INSERT, it is modified only when I do the COMMIT. And even then, that would not explain why the journal file lingers after re-opening the database. Could anyone shed some light on the above? Thank you very much for any help! Best regards, Fabrice --Original Message-- From: Simon Slavin To: Fabrice Triboix To: General Discussion of SQLite Database Subject: Re: [sqlite] Question about how sqlite recovers after a power loss Sent: 18 Feb 2014 08:57 On 18 Feb 2014, at 8:13am, Fabrice Triboix ftrib...@falcon-one.com wrote: Actually, the journal file does not get deleted when I do a SELECT just after opening the database. Depending on your journal mode (I think), the journal file should be deleted, but then it will immediately be recreated. Are you sure that this is not what's happening ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about how sqlite recovers after a power loss
Hi Max, Many thanks for your input. This is probably how it works. Best regards, Fabrice --Original Message-- From: Max Vlasov To: Fabrice Triboix To: General Discussion of SQLite Database Subject: Re: [sqlite] Question about how sqlite recovers after a power loss Sent: 26 Feb 2014 10:24 On Wed, Feb 26, 2014 at 12:33 PM, Fabrice Triboix ftrib...@falcon-one.com wrote: And even then, that would not explain why the journal file lingers after re-opening the database. I remember asking a similar question. As long as I remember, the main logical implication is that journal file presence is not a mark to force database into some actions. It's rather a supplement for the error state of the base. So if the base is in error state and there's no journal file, it's bad. But reverse is ok and might appear with some scenarios. Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about how sqlite recovers after a power loss
Hi Richard, Many thanks for your input. My ideas about how sqlite works were indeed incorrect. When I say re-opening the database, I mean opening it and doing a few SELECT to it (as described in a previous email). So I do read it (especially the sqlite_master table), and still the journal file stays there. Max suggested that it might be normal behaviour... Please also note that I now do a PRGMA to force the journal mode to DELETE (although I am pretty sure this is what it was by default...) Best regards, Fabrice -Original Message- From: Richard Hipp d...@sqlite.org Sender: drhsql...@gmail.com Date: Wed, 26 Feb 2014 05:40:27 To: ftrib...@falcon-one.com; General Discussion of SQLite Databasesqlite-users@sqlite.org Subject: Re: [sqlite] Question about how sqlite recovers after a power loss On Wed, Feb 26, 2014 at 3:33 AM, Fabrice Triboix ftrib...@falcon-one.comwrote: Hi Simon, If I run my program step-by-step, I can see that the sqlite database is not modified when I do an INSERT. It is modified only when I do a COMMIT. Correct. Changes are normally kept in memory (unless the change set grows too large) until you COMMIT. This allows other concurrent processes to continue reading the unmodified database file. I read the page on the sqlite website detailing how sqlite deals with crashes and untimely power losses, and it says that the journal file is created just before modifying the database and is a copy of the areas that are to be modified. Once the journal file is committed to the disk, the database is modified. But this is not what I can see. What I can see is that the database file is not modified when I do the INSERT, it is modified only when I do the COMMIT. Perhaps your mental model of INSERT and COMMIT is wrong. The abstraction that SQL (all SQL databases, not just SQLite) tries to present is this: INSERT does not modify an SQL database; it merely queues up changes to be made atomically when you COMMIT. And even then, that would not explain why the journal file lingers after re-opening the database. Simply calling sqlite3_open() does not re-open a database. It merely saves the filename so that the database can be opened later when needed. The re-open does not occur until you first try to read from the database file. The rollback journal is replayed and deleted at that point. Could anyone shed some light on the above? Thank you very much for any help! Best regards, Fabrice --Original Message-- From: Simon Slavin To: Fabrice Triboix To: General Discussion of SQLite Database Subject: Re: [sqlite] Question about how sqlite recovers after a power loss Sent: 18 Feb 2014 08:57 On 18 Feb 2014, at 8:13am, Fabrice Triboix ftrib...@falcon-one.com wrote: Actually, the journal file does not get deleted when I do a SELECT just after opening the database. Depending on your journal mode (I think), the journal file should be deleted, but then it will immediately be recreated. Are you sure that this is not what's happening ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about how sqlite recovers after a power loss
Hi Richard, Actually, the journal file does not get deleted when I do a SELECT just after opening the database. Why is that? NB: I do not set/modify any PRAGMA at all. Many thanks for your help! Best regards, Fabrice -Original Message- From: Richard Hipp d...@sqlite.org Sender: drhsql...@gmail.com Date: Sat, 15 Feb 2014 09:57:03 To: ftrib...@falcon-one.com; General Discussion of SQLite Databasesqlite-users@sqlite.org Subject: Re: [sqlite] Question about how sqlite recovers after a power loss On Sat, Feb 15, 2014 at 9:55 AM, Fabrice Triboix ftrib...@falcon-one.comwrote: Hi Richard, All right, many thanks for that. So if I do, say, a SELECT just after opening the database, that's when the recovery will actually take place and the journal file will be replayed and deleted. Is my understanding correct? yes. SELECT count(*) FROM sqlite_master is a good query to run for this. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about how sqlite recovers after a power loss
Hi Richard, All right, many thanks for that. So if I do, say, a SELECT just after opening the database, that's when the recovery will actually take place and the journal file will be replayed and deleted. Is my understanding correct? Many thanks for your help! Best regards, Fabrice -Original Message- From: Richard Hipp d...@sqlite.org Sender: sqlite-users-bounces@sqlite.orgDate: Sat, 15 Feb 2014 08:07:57 To: General Discussion of SQLite Databasesqlite-users@sqlite.org Reply-To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] Question about how sqlite recovers after a power loss On Fri, Feb 14, 2014 at 10:13 AM, ftrib...@falcon-one.com wrote: Hello, I would like to understand better at which moment does sqlite recovers from a previous power loss. Let's take the following scenario: - sqlite3_open() - BEGIN TRANSACTION; - INSERT INTO SomeTable VALUES (blah, blah, blah); = Power loss! - sqlite3_open() What I notice, is that the 2nd sqlite3_open() succeeds, but the journal file is left intact and not deleted. I would like to know at what moment is the journal file actually deleted after a power loss. When you first access the database. sqlite3_open() does not actually open the database file. It just queues up the information. The real open is deferred until you actually try to read something out of the database. The delay of open gives the application time to run PRAGMA statements to further setup the connection. In particular, it allows the key PRAGMA to run to set the encryption key for encrypted database files, which would otherwise be unreadable. -- D. Richard Hipp d...@sqlite.org ___ 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