[sqlite] Intermittent malformed database schema
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, every now and then - very hard to reproduce - I get the SQLite error malformed database schema (error code 1). This happens when running lots of schema-changing statements after another and seems to occur only on systems with a very fast hard-disk (SSD). Fortunately, the database seems to be ok, i.e. everything works fine after closing and re-opening the file and re-issuing the statement. This is with in-memory temp database, WAL and PRAGMA synchronous=NORMAL. The statements (schema migrations) are grouped into blocks where each starts with a BEGIN EXCLUSIVE TRANSACTION and ends with committing the transaction. Maybe someone else had this problem or has an idea how it may be caused? All the best Marcus -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.17 (MingW32) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQEcBAEBAgAGBQJQ9SZaAAoJELuOTdgGQnyZIh4H/RJtoJl9p1GEz3BP31qvF0eF w8jBxNoIhjAcGGA5reVF0BtLyn2mklQqCIkZJG2F5/yKTxepRo7jcsJiPi2imfw+ r+AWO9rOREhxiXq35F3FxS4kFavXX1BCzelY4WnP1knZbvZiCKSBRSqJmn4cM09W 8LLb6PY62WWyd9smTGmNtkV8JlmPHvrNkzTIs3KuICDIB/yM/epM9f1Huu/nNrhS +Go17P1oSqPaD9MlzErdvY6eOT9T8l+cRHYvOfJYhcJ71C3O6qpMsoAJVZ/eVsa0 Qwbph6z+j4Bw5GwnHYmpIsCb6T3ouYncURIjuY4WOn4hE8bjmK47ya4hr8iYO6s= =I7Bc -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Intermittent malformed database schema
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Simon, thank you for the quick reply! On Dienstag, 15. Januar 2013 12:14:20, Simon Slavin wrote: On 15 Jan 2013, at 9:50am, Marcus Ilgner mar...@doo.net wrote: every now and then - very hard to reproduce - I get the SQLite error malformed database schema (error code 1). This happens when running lots of schema-changing statements after another and seems to occur only on systems with a very fast hard-disk (SSD). Fortunately, the database seems to be ok, i.e. everything works fine after closing and re-opening the file and re-issuing the statement. This is with in-memory temp database, WAL and PRAGMA synchronous=NORMAL. The statements (schema migrations) are grouped into blocks where each starts with a BEGIN EXCLUSIVE TRANSACTION and ends with committing the transaction. Which version of SQLite are you using ? You can tell us the result of SELECT sqlite_version() if that's convenient. Sorry for that. The version string is 3.7.15.1. Are you using any PRAGMAs besides PRAGMA synchronous=NORMAL ? The complete list of PRAGMAs/setup statements: PRAGMA temp_store = 2 PRAGMA journal_mode = WAL PRAGMA synchronous = 1 PRAGMA foreign_keys = ON Is the disk database you're using on a local drive or accessed across a network ? The file resides on a local drive. Also it isn't accessed by any other processes. You say that this happens only when using an SSD drive, but you also say that this is with an in-memory temporary database. This sounds a little strange. What are you doing to tell SQLite you want an in-memory temporary database ? Oops. I meant memory temp store, not database. Can you try the same thing with just BEGIN instead of BEGIN EXCLUSIVE ? It first appeared with deferred transactions and I then changed it to exclusive mode, just to make sure that the lock was acquired as soon as possible. Best regards Marcus -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.17 (MingW32) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQEcBAEBAgAGBQJQ9WAEAAoJELuOTdgGQnyZTBgIAJ6XLIAcL9O0fVwx1P/JoOCy AzPhL/C4TKAInzFgdxTXjpghclfVKlGq1eqUYecLCOTSNElrlg7/qOEVL5T3orta 2j5EARRimtenRdPG5c3rCBzUf1jgCZ/MLNmExkcGrsJNt4L6Q4Z2MABrEQbzBHJ4 JzHbQBKnpGWRjN3plVejk0KogW+SU2Csv21fZieCz8wvMDCwA2XRlFxGu6036OZe g6fK8heEXnPnN49QqzWT2i4TKDsXFwvrWcdbVXhDhRcXFG9mipofGGGzof2ob7Lf itcXgM0c9k938AzxUaQkmlF6JgdfsS/IzOLeTLbZ4gk7xa38cH2aa+gONvnDUXg= =GKwV -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQLite-based spreadsheet?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Freitag, 7. Dezember 2012 16:38:05, David Bicking wrote: From: Gilles Ganault gilles.gana...@free.fr On Fri, 7 Dec 2012 13:37:45 +, Simon Slavin slav...@bigfraud.org wrote: Use the free SQLite shell tool downloadable from the SQLite site. Write your own interface in PHP using the sqlite3 interface which does exactly what you want. Thanks but before I build my own, I wanted to check that no one had already built a Windows application with SQLite embedded, available either as free- or share-ware. It is a bit heavy, and not really what I think you are after, but you might want to look at Kexi (http://www.kexi-project.org/) It attempts to be an open source replacement for Access. It has the simpler bits of Access, but doesn't have VBA, which is a deal breaker for me. OpenOffice / LibreOffice has a built in database tool, including a spreadsheet like interface. I believe it can be set up to use an sqlite database for its back end. Frankly I could never get it to work for me. OpenOffice/LibreOffice Base could probably do the trick. To set it up, you'll have to add the SQLite JDBC driver .jar to OO/LOs class path and then choose connect to existing JDBC database (or something along these lines) when creating the .odb file. I vaguely remember setting up such a thing a few years back for someone else and iirc it worked for the intended use back then. All the best Marcus -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.17 (MingW32) Comment: Using GnuPG with undefined - http://www.enigmail.net/ iQEcBAEBAgAGBQJQwg+qAAoJELuOTdgGQnyZbZUIAI9QspGL779FwRm29SjBtRLG wkoWnbaK+xfjRz3rntVLYTTqZvA+/5K7XVkaU85F1wOy8lYdNM+X1c+kQxgEW8Z4 lRsfT0zSDMKR74Vi3y3AfZcUf07tV8ynEEOaYAHr72am2cW+Hj0QO4RHEi7jnsIu qz+l0aMk8b7TljRMx/1ou4yWxt0UN9SvhiuFiUjTti5B+VdAnnMydlZYMDwBtdAz bQpElrpTUgLi1leuBs9i9/He8tsHhJ6LfE0tqtSFWJZLRRdDugqnBx6HmAdUNQVE OvNYoWnZxO1Ae73gWxHfEMH/kkOdsBywNsfYIfGJJlu34X0bW6pavVq6QhjeLn0= =gzHN -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select ... where problem
On Donnerstag, 20. September 2012 15:16:29, 叶艳红 wrote:
insert into history (mdwkup,macdup,mdate,mdwkmd) SELECT
(select count(*) FROM fxj where mdwkup=1),
(select CURDATE()),
(select round(avg(mdwkmd),2) from fxj) where datetime('now',
'localtime')15:00
The sql command could not run
Seems like you're missing the value for the second column to insert
(macdup)...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strategies for checking the version of a database?
On Dienstag, 18. September 2012 12:38:17, Rui Maciel wrote: Say there is an application that relies on sqlite for its database needs, and that from a version onward it starts to use a different database schema. When faced with this type of change, it is always nice to have a way to infer if the application's database corresponds to the old schema in order to migrate it to the current format. Considering this, and according to your experience, what's the best sqlite-friendly way to check for the version of a database schema? You could use the user_version pragma (don't use schema_version) to store this information. See http://www.sqlite.org/pragma.html#pragma_schema_version All the best Marcus Ilgner ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count(*) help
On So, 2012-09-16 at 17:17 +0100, John Clegg wrote: I have a table Members with 896 rows and a text field Year2012. It contains Paid 156 times, Comp 13 times and the rest are null (confirmed in sqlitebrowser as empty) Back in the olden days when this table was in Access, select count(Year2013) from Members used to return 169. In LibreOfiice with the data stored in embedded HSQL it returns 169. In LibreOffice connecting to sqlite3 it returns 896. Any ideas please? You're probably looking for something like SELECT COUNT(*) FROM Members WHERE Year2012='Paid' or maybe even SELECT Year2012, COUNT(*) FROM Members GROUP BY Year2012 All the best Marcus Ilgner ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Windows 8: debugging table is locked errors
Hello list, I'm one of the authors of the SQLite-WinRT component for Windows 8. We recently adapted it to use the official Windows 8 compatible release through NuGet. Thank you for providing a current version of SQLite through this channel! Regarding this release, can you tell me what configuration was used to compile it? I'm asking because we wondered why the binary is about double the size of the one we had previously. Also, there's a strange phenomenon occurring which is probably some kind of race condition in the way that multi-threading is done: sqlite3_step() returns a generic error code 1 (for a valid statement) but when I proceed to call sqlite3_errmsg16(), the error message for error 6 (table locked) is returned. Generally speaking, is a locked table something that should be handled or do you think there may be another problem at play here? I see that SQLite does a lot of work to support seamless multi-threading and since the one application is the only process working on the database, I suspect that the problem may lie elsewhere. All the best Marcus Ilgner -- doo Windows Team doo GmbH | Argelander Strasse 1 | D-53115 Bonn https://doo.net | @ma_il https://twitter.com/ma_il | @doohttps://twitter.com/doo| mar...@doo.net AG Bonn, HRB 18719 | GF Frank Thelen, Marc Sieberger, Alex Koch ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows 8: debugging table is locked errors
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 13.09.2012 17:50, Joe Mistachkin wrote: Marcus Ilgner wrote: I'm one of the authors of the SQLite-WinRT component for Windows 8. Cool, the one on CodePlex? Oh, sorry, I forgot to include the link [1]. The one at Codeplex is more targeted towards C# development while we developed our component to use it from the new JavaScript/HTML-based framework in Windows 8. Regarding this release, can you tell me what configuration was used to compile it? The default compilation options from the MSVC makefile were used, along with the extra options necessary for WinRT (e.g. SQLITE_OS_WINRT, WINAPI_FAMILY, etc). Ok, great to know, I'll have a look at the Makefile then. I'm asking because we wondered why the binary is about double the size of the one we had previously. From where? With what compilation options? We took the official release and included it in the component. The precompiler defines were SQLITE_OS_WINRT;SQLITE_ENABLE_UNLOCK_NOTIFY;SQLITE_TEMP_STORE=2 Also, there's a strange phenomenon occurring which is probably some kind of race condition in the way that multi-threading is done: sqlite3_step() returns a generic error code 1 (for a valid statement) but when I proceed to call sqlite3_errmsg16(), the error message for error 6 (table locked) is returned. That is not necessarily a race condition. It could be the sqlite3_step is returning SQLITE_ERROR and the last error code stored for the database could be something else, like SQLITE_LOCKED. Also, the SQLite DLLs for WinRT are compiled with SQLITE_THREADSAFE=1. Ok, I was under the impression that sqlite3_step would always return the same error code as in the db. If I understand you correctly, it could be expected behaviour that a locked table returns SQLITE_ERROR instead of SQLITE_LOCKED? Generally speaking, is a locked table something that should be handled or do you think there may be another problem at play here? I see that SQLite does a lot of work to support seamless multi-threading and since the one application is the only process working on the database, I suspect that the problem may lie elsewhere. How many processes and/or threads are attempting to access the database simultaneously? There can be only one writer at a time. Also, unless WAL is used, a writer may block a reader. I had activated WAL in this scenario. There should have only been one writer at that point, but I just had an idea how to debug this further. -- Joe Mistachkin Thank you for the quick reply, it has already been very helpful! All the best Marcus Ilgner [1] https://github.com/doo/SQLite3-WinRT -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.17 (MingW32) Comment: Using GnuPG with Mozilla - http://www.enigmail.net/ iQEcBAEBAgAGBQJQUjoHAAoJELuOTdgGQnyZVhQH/RzFd+FmDrIdk5zCtOHOHEy8 NANmh4Uf4RqfpS2GZlCI0PI7Hyn4pczaPGBGKO1o8z0GJ0PjmqXcJKuKgxzOitaN KBF+Ig8MkeecuwjIuxDLPYd6h7Urm8Srx8VzNhMPCj1e24uGOItkXQDe5MCgWUYY S3kH1zyWYNfPstHsO2qims4YCnczHs49Eq0S2aEKWwLeSS8aqnZ/eY1+s4Iem1Qn vvJT1o/rjzaIobVP1R022ChGMnLYr8rEvMb+1VKcBM/OQMo+6m+fD+DHQzSieB7w V3dUkx8hx4HJbXh3d59qdAE5NLLQLJN789q+eY33eUzWB0cRV+slLl77TK+6EqQ= =1GAj -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
