[sqlite] OT: Compiling under Windows
Hello, this question is slightly OT here but. I want to compile SQLite as a library (not as a DLL) and add this library to my DLL-Project. Why? Because the C-Compiler from MS produces so many warnings and I want to compile all my own projects without even a single warning So I wonder what is correct: To compile/link the resulting sqlite.lib as a "Multithreaded" project or as a "Multithreaded-DLL" project? Remember, I want to add this sqlite.lib to my DLL Bye & Thanks, Michael
RE: [sqlite] multiple thread concurrency problem with exclusive transaction locks
Correction: I misread the code in os_unix.c. There is NO simplistic mutex mechanism, just the pthread_mutex_lock call. So, the only way for your code to fail because of this would be to 1. compile without THREADSAFE And 2. disable asserts. I don't know how this is done in C, but I assume that in a release build, asserts are off. Also, it would, of course, fail, if the pthread_mutex_lock call does not work properly..
RE: [sqlite] multiple thread concurrency problem with exclusive transaction locks
I am not running on *ix, so I cannot test.however, your mentioning multi-cpu machines, brings something to mind. In windows, most synchronization functions rely on some form of INTERLOCKED operation. Now, these operations are implemented differently on multi-cpu (and hyperthreading counts as multi-cpu in this context), than on single-cpu machines. Namely, in the multi-cpu kernel, each interlocked operation is prefixed by a bus LOCK instruction, whereas in the single-cpu kernel, it is prefixed by a NOP. As a result, if multi-threaded code runs on a machine that has a single-cpu kernel, but multiple-cpus, all hell breaks loose (it can happen, due to a bad installation). Your problem is suspiciously similar. here are some things to check for: 1. is your *ix kernel compiled for multi-cpu ? I think that linux requires a special build of the kernel for multi-cpu machines. 2. is your sqlite code compiled with SQLITE_UNIX_THREADS enabled (and THREADSAFE enabled) ? If not, the default mechanism used for mutexes in os_unix.c will certainly FAIL on multi-cpu machines, as it does not have the atomic LOCK prefixes. If yes, then the functionality of the mutexes (sqlite3OsEnterMutex) depends on how well the POSIX (or whatever) lock mechanism works. From the comments on the beginning of the file, I suspect that locking in Linux is horribly unstable... So, I would first check that the mutex mechanism works as expected in your environment before looking for a bug in the sqlite code (unless the default, simplistic mutex mechanism in sqlite3OsEnterMutex counts as a bug) -Original Message- From: Eli Burke [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 12, 2005 6:34 PM To: sqlite-users@sqlite.org Subject: [sqlite] multiple thread concurrency problem with exclusive transaction locks I hate to beat on a tired horse (threads and db locking issues), but I am running into what I believe is a bug as we scale up the number of threads in our application. A little background: there is a main scheduler thread that does most of the processing, and client threads to handle remote connections. These clients don't touch the database very often, but on occasion (in particular when they exit) they request an exclusive lock to remove themselves from the database. The problem that I see is that with multiple threads all attempting to "BEGIN EXCLUSIVE", they will occasionally *all* fail, calling the busy handler repeatedly until it finally returns SQL_BUSY. Let me re-state for clarity's sake: 10 threads all try "BEGIN EXCLUSIVE" at the same time. One succeeds, processes, and COMMITs. The other 9 will sometimes repeatedly call the busy handler over and over until they fail with SQLITE_BUSY, even though the database *should be* available to start a new exclusive transaction.
Re: [sqlite] SQLite openning a database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Christian Smith wrote: > On Tue, 11 Jan 2005, John Richard Moser wrote: > > >>-BEGIN PGP SIGNED MESSAGE- >>Hash: SHA1 >> >>http://rafb.net/paste/results/jRFmZD25.html >> >>This is code from a package manager I'm writing based on SQLite. These >>two functions open a database, creating it if it doesn't exist. >> >>While most of this isn't of general interest, I'd like to point out that >>I create the database by opening it with open() using O_EXCL and >>O_CREAT, and then close the file. After that I call SQLite's >>sqlite3_open() to open the database. >> >>The reason I do this is so that the database can't be raced to create >>arbitrary writes. For example, I don't want to call sqlite3_open(), >>have it find the database doesn't exist, then have a normal user insert >>a symlink to /etc/fstab, and have sqlite3 "create" the database. > > > > But /etc/fstab is protected from modification by the operating system > security system. Under your scheme, someone can simply rm the file after > your open but before the sqlite3_open, and you'll still have the same > none problem. /tmp/ is rwx by anyone root stats /tmp/tmpdb.root.1072 (pid==1072) Nonexistant User ln -s /etc/fstab /tmp/tmpdb.root.1072 root creates /tmp/tmpdb.root.1072 fstab erased. > > > >>It should be reviewed whether this is a general concern (I haven't >>audited SQLite, so I don't know), and if so, whether a function such as >>sqlite3_sopen() to "open or securely create" a database should be made. >>~ This may just be my paranoia. > > > > No, this is of no concern. You're protected against malicious use by the > operating system. The only person who can open, say, /etc/fstab is root. > If the user is root, they can do whatever damage they want already, they > don't need SQLite to do it for them. > Do you know what a temp file race condition is? it's roughly 20% of securuty vulnerabilites based on Ubuntu Linux Security Notices. https://www.ubuntulinux.org/wiki/USNAnalysis The type of logic I'm thinking of is the type indicated in USN 3-1, 5-1, 6-1, 4-1, 13-1, 15-1, 16-1, 24-1, 43-1, 49-1, 51-1 http://www.ubuntulinux.org/support/documentation/usn/ Sometimes SQLite may be used by a root owned process. > > Christian > - -- All content of all messages exchanged herein are left in the Public Domain, unless otherwise explicitly stated. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.0 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFB5ZWzhDd4aOud5P8RAuHOAJ90Nq0zsBwUwSIBC++3bhclM162/wCfafJS r6m/yy3VkP0DY8eCCF473wE= =P+Wx -END PGP SIGNATURE-
Re: [sqlite] add new column to table
Hi Dennis, I tried something similar to your recommendation, but there was a problem with the following INSERT INTO call_data SELECT old_call_data.*, firstname || ' ' || surname FROM old_call_data JOIN users USING extn_no; there is a syntax error near extn_no. Lloyd - Original Message - From: "Dennis Cote" <[EMAIL PROTECTED]> To: Sent: Wednesday, January 12, 2005 5:21 PM Subject: Re: [sqlite] add new column to table Lloyd Thomas wrote: Thanks Dennis. As long as I know where I stand. I can probably use PHP or Delphi to update each row manually. Lloyd - Original Message - From: "Dennis Cote" <[EMAIL PROTECTED]> To: "sqlite-users" Sent: Wednesday, January 12, 2005 4:21 PM Subject: Fw: [sqlite] add new column to table Dennis Cote wrote: Lloyd Thomas wrote: Thanks. That is going OK but I am having a problem with updating the new column with the info I need. It seems to update with the same entry from my users table to all rows. UPDATE call_data SET caller_name = (SELECT firstname || surname AS 'caller_name' FROM users WHERE extn_no = (SELECT extn_no FROM call_data)); I have missed something? Lloyd, You shouldn't need to use PHP or Delphi. You need to create a tempoarary copy of your existing table, then delete and recreate your table with the new column added. Then use the insert command to copy the old data nad the new data (from your users table) back into the new table. Finally you can delete the old copy. You need to do something like the following: CREATE TABLE old_call_data AS SELECT * FROM call_data; DROP TABLE call_data; CREATE TABLE call_data ( ., caller_nameVARCHAR(100) ); INSERT INTO call_data SELECT old_call_data.*, firstname || ' ' || surname FROM old_call_data JOIN users USING extn_no; DROP TABLE old_call_data; I have assumed that you will add the new column at the end of the existing column list. If not your select statement simply gets a little more compilcated. HTH Dennis Cote
Re: [sqlite] mailing list problems?
I also; I sent in a couple of messages on Monday and I haven't seen them post. --Keith On Wed, 12 Jan 2005 09:20:13 -0700, Dennis Cote <[EMAIL PROTECTED]> wrote: > Hi, > > Yesterday I posted three messages to the mailing list. Today when I look at > the list only the last of those three messages has been distributed back to > my email account. > > Has anyone else noticed the mailing list failing to distribute messages? > > Dennis Cote > -- ** - Ever notice how 'big' isn't, compared to 'small'? - Sounds like a Wookie; acts like mad cow. - I'm not a professional; I just get paid to do this. - Rules for programming: 1. Get it working, right? 2. Get it working right. - Things I've learned about multithreaded programming: 123... PPArrvooottieedcc ttm ueelvvteeirrtyyhtt rhheiianndgge dwi hnpi rctohhg eri aslm omscitanalgt iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb .ee^Nr waicscee snsoetd 'aotb jtehcet -slaomcea lt'il m^Ne from two or more threads **
RE: [sqlite] mailing list problems?
Your point being? ;-) -Original Message- From: Jay [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 12, 2005 10:58 AM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: Re: [sqlite] mailing list problems? Everything I try to post is marked as spam and rejected. ...
Re: [sqlite] add new column to table
Lloyd Thomas wrote: Thanks Dennis. As long as I know where I stand. I can probably use PHP or Delphi to update each row manually. Lloyd - Original Message - From: "Dennis Cote" <[EMAIL PROTECTED]> To: "sqlite-users" Sent: Wednesday, January 12, 2005 4:21 PM Subject: Fw: [sqlite] add new column to table Dennis Cote wrote: Lloyd Thomas wrote: Thanks. That is going OK but I am having a problem with updating the new column with the info I need. It seems to update with the same entry from my users table to all rows. UPDATE call_data SET caller_name = (SELECT firstname || surname AS 'caller_name' FROM users WHERE extn_no = (SELECT extn_no FROM call_data)); I have missed something? Lloyd, You shouldn't need to use PHP or Delphi. You need to create a tempoarary copy of your existing table, then delete and recreate your table with the new column added. Then use the insert command to copy the old data nad the new data (from your users table) back into the new table. Finally you can delete the old copy. You need to do something like the following: CREATE TABLE old_call_data AS SELECT * FROM call_data; DROP TABLE call_data; CREATE TABLE call_data ( ., caller_nameVARCHAR(100) ); INSERT INTO call_data SELECT old_call_data.*, firstname || ' ' || surname FROM old_call_data JOIN users USING extn_no; DROP TABLE old_call_data; I have assumed that you will add the new column at the end of the existing column list. If not your select statement simply gets a little more compilcated. HTH Dennis Cote
Re: [sqlite] $ in table and column names
Dennis Cote wrote: For what it' worth now, here is the first message I posted yesterday. It might to make my last post make a little more sense. Hanging head in shame... That didn't clarify anything did it. It should have said: "For what it's worth now, here is the first message I posted yesterday. It might help to make my last post make a little more sense." BTW. All my posts this morning are coming through right away.
Re: [sqlite] mailing list problems?
Everything I try to post is marked as spam and rejected. --- Dennis Cote <[EMAIL PROTECTED]> wrote: > Hi, > > Yesterday I posted three messages to the mailing list. Today when I > look at > the list only the last of those three messages has been distributed > back to > my email account. > > Has anyone else noticed the mailing list failing to distribute > messages? > > Dennis Cote > = - "Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of the ancient tomb of the petrified pharaoh, he vowed there would be no curse on him like on that other Lord, unless you count his marriage to Lady Tarlington who, when the lost treasure was found, will be dumped faster than that basket in the bulrushes." Melissa Rhodes - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do you Yahoo!? Yahoo! Mail - Find what you need with new enhanced search. http://info.mail.yahoo.com/mail_250
Re: [sqlite] add new column to table
Thanks Dennis. As long as I know where I stand. I can probably use PHP or Delphi to update each row manually. Lloyd - Original Message - From: "Dennis Cote" <[EMAIL PROTECTED]> To: "sqlite-users" Sent: Wednesday, January 12, 2005 4:21 PM Subject: Fw: [sqlite] add new column to table Dennis Cote wrote: Lloyd Thomas wrote: Thanks. That is going OK but I am having a problem with updating the new column with the info I need. It seems to update with the same entry from my users table to all rows. UPDATE call_data SET caller_name = (SELECT firstname || surname AS 'caller_name' FROM users WHERE extn_no = (SELECT extn_no FROM call_data)); I have missed something? Lloyd, You can't use an UPDATE statement to fill in your new column unless you want all rows to have the same value. You need to populate your new table using the INSERT ... SELECT ... statement. You use the select statement to combine the data from your old table and your new values using joins to pull values from other tables or calculating the new values based on existing columns. I hope this helps. Dennis Cote
[sqlite] multiple thread concurrency problem with exclusive transaction locks
I hate to beat on a tired horse (threads and db locking issues), but I
am running into what I believe is a bug as we scale up the number of
threads in our application. A little background: there is a main
scheduler thread that does most of the processing, and client threads to
handle remote connections. These clients don't touch the database very
often, but on occasion (in particular when they exit) they request an
exclusive lock to remove themselves from the database.
The problem that I see is that with multiple threads all attempting to
"BEGIN EXCLUSIVE", they will occasionally *all* fail, calling the busy
handler repeatedly until it finally returns SQL_BUSY. Let me re-state
for clarity's sake: 10 threads all try "BEGIN EXCLUSIVE" at the same
time. One succeeds, processes, and COMMITs. The other 9 will sometimes
repeatedly call the busy handler over and over until they fail with
SQLITE_BUSY, even though the database *should be* available to start a
new exclusive transaction.
I have tried to eliminate as many variables as I could in my testing and
in the sample program:
* No SQL queries other than BEGIN and COMMIT are being used on an empty
database.
* No threads are accessing the database in any place other than the
pthread_cleanup handler.
* I have tested on Fedora Core 1,2,3, SUSE and Gentoo, and with 32 and
64 bit systems.
The remaining variables I am working with are system load and CPU count:
* On a hyperthreaded (2 virtual CPUs) computer it fails at least 50% of
the time when unloaded and < 5% with the load around 1, and then climbs
up again as the load increases.
* On a multi-CPU computer it fails about 10% of the time when unloaded
and < 1% when both CPUs are otherwise occupied.
* I have occasional access to a single-CPU Pentium 3 where I have not
been able to duplicate the problem. This pc is running FC2 just like our
chief offender the hyperthreaded PC who fails 50% of the time when unloaded.
In order to demonstrate this problem I have attached a program called
sqlthreads.c. It builds under Linux with:
gcc -Wall -g sqlthreads.c -o sqlthreads -lpthread -lsqlite3.
sqlthreads takes two arguments: the number of threads to spawn, and the
type of busy handler to use. 0 is the standard "sqlite3_busy_timeout"
handler with a 20 second timeout. 1 is a custom sqlite3_busy_handler
with a fixed sleep time. -1 is a similar sqlite3_busy_handler with a
random sleep time. When run, the test launches N threads, waits a
second, then cancels all of them. At this point, each thread will
sqlite3_exec a BEGIN EXCLUSIVE. The one that succeeds will sleep for 1/2
second and then call COMMIT, at which time another thread *should* be
free to begin. If at the end you see "It appears as though at least x
threads failed to obtain an exclusive lock on the database!" then you
have experienced what I consider to be the problem.
If I could get any independant corroboration of this problem, that would
be fantastic. If you skimmed through my long email, it appears most
likely to occur on a hyperthreaded PC, aka Pentium 4 2.8 ghz or higher.
-Eli
/*
* sqlthreads.c -- sqlite3 locking / thread concurrency tester
*/
#include
#include
#include
#include
#include
#include
// global variables
int g_failed = 0;
int g_threadCnt = 0;
pthread_t g_pool[256] = {0};
pthread_key_t g_pkeyDb = 0;
pthread_mutex_t g_logMutex = PTHREAD_MUTEX_INITIALIZER;
//
// log output wrapper, adds timestamp and thread id
//
void sqlog(char * pattern, ...)
{
va_list varArgs;
char timeBuf[16];
time_t t = time(0);
struct tm tmStruct;
// determine the current time
localtime_r(&t, &tmStruct);
strftime(timeBuf, sizeof(timeBuf), "%H:%M:%S", &tmStruct);
// init the variable argument processor
va_start(varArgs, pattern);
// obtain a mutex lock and write out the log entry
pthread_mutex_lock(&g_logMutex);
fprintf(stderr, "%s [%u] ", timeBuf, (unsigned int)pthread_self());
vfprintf(stderr, pattern, varArgs);
pthread_mutex_unlock(&g_logMutex);
// clean up and exit
va_end(varArgs);
}
//
// sqlite3 alternative busy handler
//
int busyHandler(void *pArg1, int iPriorCalls)
{
sqlog("!! dbBusyHandler %d\n", iPriorCalls);
// sleep if handler has been called less than threshold value
if (iPriorCalls < 20)
{
// adding a random value here greatly reduces locking
if (pArg1 < 0)
usleep((rand() % 50) + 40);
else usleep(50);
return 1;
}
// have sqlite3_exec immediately return SQLITE_BUSY
return 0;
}
//
// each thread calls this when it is canceled
//
void destroySingleThread(void *ptr)
{
int rc;
sqlite3 * pDb;
// get our db handle from thread local storage
Fw: [sqlite] $ in table and column names
For what it' worth now, here is the first message I posted yesterday. It might to make my last post make a little more sense. Dennis Cote wrote: Ulrik Petersen wrote: I guess the next question is: Can someone verify that this is part of the SQL standard? Yes, it looks like this is part of the SQL standards, at least for SQL:1999 and SQL:2003. They both define a column name or table name as an identifier (directly or indirectly). An identifier is a basically an initial alphabetic or ideographic character followed by one or more characters from a larger set of characters. This larger set of characters contains alphabetic, ideographic, decimal digit, identifier combining, underscore, alternate underscore, extender, identifier ignorable, and connector characters. The details of these character classes are given in the specs. I believe that the '$' character is an ideographic character. I'm not certain since the rules are all based on unicode character properties. This seems to agree with what others have reported using other database systems. It looks like SQLite would not be able to support this and continue to use its TCL type parameter names since the latter uses the '$' character as the initial character. If the '$' character is not an ideographic character (i.e. it falls into one of the other classes like identifer combining characters), then SQLite could continue to use the initial '$' character for its TCL parameter names. I will see if I can find a list of these unicode character properties that will clarify this. Dennis Cote
Fw: [sqlite] add new column to table
Dennis Cote wrote: Lloyd Thomas wrote: Thanks. That is going OK but I am having a problem with updating the new column with the info I need. It seems to update with the same entry from my users table to all rows. UPDATE call_data SET caller_name = (SELECT firstname || surname AS 'caller_name' FROM users WHERE extn_no = (SELECT extn_no FROM call_data)); I have missed something? Lloyd, You can't use an UPDATE statement to fill in your new column unless you want all rows to have the same value. You need to populate your new table using the INSERT ... SELECT ... statement. You use the select statement to combine the data from your old table and your new values using joins to pull values from other tables or calculating the new values based on existing columns. I hope this helps. Dennis Cote
[sqlite] mailing list problems?
Hi, Yesterday I posted three messages to the mailing list. Today when I look at the list only the last of those three messages has been distributed back to my email account. Has anyone else noticed the mailing list failing to distribute messages? Dennis Cote
Re: [sqlite] SQLite openning a database
On Tue, 11 Jan 2005, John Richard Moser wrote: >-BEGIN PGP SIGNED MESSAGE- >Hash: SHA1 > >http://rafb.net/paste/results/jRFmZD25.html > >This is code from a package manager I'm writing based on SQLite. These >two functions open a database, creating it if it doesn't exist. > >While most of this isn't of general interest, I'd like to point out that >I create the database by opening it with open() using O_EXCL and >O_CREAT, and then close the file. After that I call SQLite's >sqlite3_open() to open the database. > >The reason I do this is so that the database can't be raced to create >arbitrary writes. For example, I don't want to call sqlite3_open(), >have it find the database doesn't exist, then have a normal user insert >a symlink to /etc/fstab, and have sqlite3 "create" the database. But /etc/fstab is protected from modification by the operating system security system. Under your scheme, someone can simply rm the file after your open but before the sqlite3_open, and you'll still have the same none problem. > >It should be reviewed whether this is a general concern (I haven't >audited SQLite, so I don't know), and if so, whether a function such as >sqlite3_sopen() to "open or securely create" a database should be made. >~ This may just be my paranoia. No, this is of no concern. You're protected against malicious use by the operating system. The only person who can open, say, /etc/fstab is root. If the user is root, they can do whatever damage they want already, they don't need SQLite to do it for them. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] regd. sqlite 3
Hi Neera, neera sharma wrote: thankx Ulrix for replying. I am using sqlite_exec with call back option. Neera. From http://sqlite.org/capi3ref.html#sqlite3_step " SQLITE_MISUSE means that the this routine was called inappropriately. Perhaps it was called on a virtual machine that had already been finalized or on one that had previously returned SQLITE_ERROR or SQLITE_DONE. Or it could be the case the the same database connection is being used simultaneously by two or more threads." Is any of this true? HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
Re: [sqlite] regd. sqlite 3
thankx Ulrix for replying. I am using sqlite_exec with call back option. Neera. --- Ulrik Petersen <[EMAIL PROTECTED]> wrote: > neera sharma wrote: > > >I am using sqlite3. when i pass an sqlite command > >string, following error is reported - > > > >"SQL error: library routine called out of sequence" > > > >Could anybody suggest the correct way of doing it. > > > > > Do you want to use the "sqlite3_exec with callback > function" API? Or > the "sqlite3_prepare/sqlite3_step/sqlite3_finalize" > API? > > The API is described here: > > http://www.sqlite.org/capi3.html > > HTH > > Ulrik P. > > -- > Ulrik Petersen, MA, B.Sc. > University of Aalborg, Denmark > > > > __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo
Re: [sqlite] regd. sqlite 3
neera sharma wrote: I am using sqlite3. when i pass an sqlite command string, following error is reported - "SQL error: library routine called out of sequence" Could anybody suggest the correct way of doing it. Do you want to use the "sqlite3_exec with callback function" API? Or the "sqlite3_prepare/sqlite3_step/sqlite3_finalize" API? The API is described here: http://www.sqlite.org/capi3.html HTH Ulrik P. -- Ulrik Petersen, MA, B.Sc. University of Aalborg, Denmark
[sqlite] regd. sqlite 3
I am using sqlite3. when i pass an sqlite command string, following error is reported - "SQL error: library routine called out of sequence" Could anybody suggest the correct way of doing it. __ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail
