[sqlite] Why does this sql error
I use the following sql
INSERT INTO malware (file, location, md5, size, sig, sig_name, cnt,
clam_result, date_found, date_removed, ref) VALUES
('Setup.exe-IRAD0n', '/Users/tshaw/malware/Setup.exe-IRAD0n',
'1186b3a97de73f924dcfb12cba0bb1bf', 15360, '', '', 1,
'/Users/tshaw/virus_archive/Setup.exe-IRAD0n: Worm.Koobface-20 FOUND
', 1243947206, 1243947206, NULL);
and get the following error
Error!: SQLSTATE[HY000]: General error: 1 near ",": syntax error code:HY000
This occur whenever I try to do a second insert with a unique
variable which is fine but why the error message above and not a
message about duplicate uniques?
TIA,
Tom
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can someone explain this error....
I periodically get the following error: Error!: SQLSTATE[HY000]: General error: 17 database schema has changed However all I am doing is selecting, inserting and updating. How can those functions change the schema? TIA Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] 17 database schema has changed
I have 2 PHP 5 scripts simulatneously that just access a sqlite 3 DB and update a entry or two or insert a new row. They do not change the schema of the DB however periodically I get the following error . Can you explain 1) how we would get this and 2) how to stop it. Error!: SQLSTATE[HY000]: General error: 17 database schema has changed Thanks for your help Tom PS I also see periodically a DB locked error which shuts down a script yet I am doing only simple times and nothing is taking a long time. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLSTATE[HY000] help
I have PHP 5 compiled for SQLite 3 and everything works great except sometimes when I am running two separate scripts updating the same table I get Error!: SQLSTATE[HY000]: General error: 1 SQL logic error or missing database yet when I run each script separately all is well. What extra locking should I do to stop this? TIA, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] cast problems sqlite3
At 1:40 PM + 3/12/07, [EMAIL PROTECTED] wrote: Tom Shaw <[EMAIL PROTECTED]> wrote: Here ya go. >Tom Shaw <[EMAIL PROTECTED]> wrote: >> UPDATE av_summary SET rank=((det*100.0)/(tot)); only sets the >> first row correctly then all the others have bogus data in rank > >That seems wrong. Can you post a sample database that demonstrates >this behavior? > I tried this on the database you sent me. The answers all look right to me. Using sqlite 3.3.5 UPDATE av_summary SET rank=((det*100.0)/(tot)); sets first row to an integer (serendipity?) and then all the other rows are real or text which caused problems since I was expecting that column was integer since that is how the table was created. I see when I export that the numbers are real in the text exported. This must have been what confused me. There is obviously something here that I don't grok. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] cast problems sqlite3
Help is appreciated. I have a table with integer columns rank, tot, det with values in tot and det and I want to put an integer percent (0-100) into rank UPDATE av_summary SET rank=(det/tot)*100; returns 0 I assume because the arithmetic is in integer UPDATE av_summary SET rank=((det*100.0)/(tot)); only sets the first row correctly then all the others have bogus data in rank UPDATE av_summary SET rank=ROUND((det*100.0)/(tot)); works over all rows Could someone explain. Also is there a "cast" operator in the SQL that SQLite executes? TIA, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] retrieval speedup help requested
In sqlite 3 I have two tables. city_loc has 156865 entries and city_block has 1874352 entries: CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT); CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER UNSIGNED NOT NULL, loc_id INTEGER NOT NULL); And my retrieval is but it is slow (6 seconds!): SELECT cc, region, city, postalCode, lat, lon, areaCode FROM city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end; I tried using: CREATE INDEX city_block_idx ON city_block (start,end); but it did not appear to speedup anything but it did use up a lot of space. Any suggestions on how to speedup retrievals? All help is appreciated. TIA Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] INTEGER PRIMARY KEY
Here's a question on INTEGER PRIMARY KEY. I would like use IP
addresses (converted to an unsigned number to man them monotonically
increasing) for INTEGER PRIMARY KEY however I can't determine from
the online docs whether if I supply an unsigned integer in PHP 5:
$uip = sprintf("%u", ip2long($ip));
to sqlite 3. Is this possible or do I have to either use text (yuk)
or split the ips (yuk)
TIA,
Tom
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] PS Re: [sqlite] two process problem
At 4:59 PM -0600 2/3/07, Jay Sprenkle wrote: On 2/3/07, Tom Shaw <[EMAIL PROTECTED]> wrote: I have 2 processes running one is updating portions of a table and one is inserting. Are you using threads? There are some issues using the same database handle with multiple threads. Each process is single threaded. Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] two process problem
At 4:59 PM -0600 2/3/07, Jay Sprenkle wrote: On 2/3/07, Tom Shaw <[EMAIL PROTECTED]> wrote: I have 2 processes running one is updating portions of a table and one is inserting. Are you using threads? There are some issues using the same database handle with multiple threads. No, two separate processes Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] two process problem
I have 2 processes running one is updating portions of a table and one is inserting. I don't accumulate updates but rather update a record at a time to keep the time of locking down. (eg begin update commit) Likewise, I only insert one at a time for the same reason. Each process works fine when running on its own yet when running them together I get errors such as: SQLSTATE[HY000]: General error: 1 SQL logic error or missing database and SQLSTATE[HY000]: General error: 8 attempt to write a readonly database I thought sqlite handled locks. What am I doing wrong? TIA, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] PHP 5 and sqlite 3
There is no entry when I execute the below, yet the update acts as if
all is OK yet nothing is updated (works same if beginTransaction and
commit is uncommented).
What is interesting is that the reverse (eg INSERT attempted first works OK.
My concern (other than I what to know why it doesn't work) is that
the "normal" case would be UPDATE and thus using the reverse sems
like it will be slower.
TIA,
Tom
try {
//$db_conn->beginTransaction();
$sql = "UPDATE av_summary SET tot = tot + 1 WHERE
name='$av_system';";
$result = $db_conn->exec($sql);
//$db_conn->commit();
} catch (PDOException $e) {
//$db_conn->beginTransaction();
$sql = "INSERT INTO av_summary (name, tot, sig,
huristic, paranoid) VALUES ('$av_system', 1, 0, 0, 0);";
$result = $db_conn->exec($sql);
//$db_conn->commit();
$sql = "CREATE TABLE $av_system (virus VARCHAR
UNIQUE, cnt INTEGER);";
$result = $db_conn->query($sql);
}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Search question
Is there an easy way to perform a search on VARCHAR/CHAR/TEXT column for those entries that has strings that are longer than x without writing my own comparitor? I am using SQLite in a PHP environment, I am looking for an intrinsic function that would run natively rather than a interpretive PHP function to preform this comparison. All help is appreciated, Tom
Re: [sqlite] What happens to unused space?
At 2:24 PM +0100 7/4/05, Tim Browse wrote: Hi, Before I go diving into the SQLite source code, can anyone tell me what happens to unused space? (i.e. from deleted data in the db) It gets reused unless you use VACUUM in which case it could end up reused in the DB or reused in the OS file system Specifically, I'm interested in whether it gets over-written by zeroes, or something. I'm storing information in a database (in a blob field), which the user can then decide to encrypt, and then the plaintext blob is set to NULL, and the ciphertext blob (i.e. a different column) is set to contain the encrypted data. So what I'm asking is, in this scenario, is it possible that sometimes I could load the sqlite db into a hex editor, and see the original unencrypted data? (i.e. the old deleted data from the plaintext blob) Yes and, in fact, you might also see it in a disk drive hex editor on some free sector or in the VM swap area. If so, a workaround is obviously to set the original blob data to contain a block of zeroes (of the same length), and *then* set it to NULL. But then it's also within the realms of possibility (not wishing to criticise anyone's code; only guessing) that SQLite might reallocate a block for the blob even if it's the same length as the old data, in which case my cunning plan wouldn't work either. Actually, as long as you don't release the space you have control of it during your rewriting. So in summary, when I delete a blob, I want it to stay deleted, even from prying eyes with a hex editor - what's the best way to do this in the SQLite model? Overwrite exactly and commit the update before you release the storage by committing NULL Depending on how paranoid you are, you will have to overwrite all disk based data with all zeros, all ones and then alternating ones and zeros or else one can reconstruct the data if one physically has possession of the media. Indeed, you will also need to zero all buffers in your normal course of business because they may be staged to your VM disk. NB. To pre-empt various invigorating arguments, I know about the various 'encrypt the whole db' solutions that are available, but I don't need them; I have my own encryption framework. I just want to know how to *really* delete data from the DB (without calling VACUUM after every update). Calling VACUUM just moves the problem to the OS' file system. Tom
Re: [sqlite] Update variable problems. Bug?
Thanks, All. I totally missed the nuance between single and double quotes. Tom -- Tom Shaw - Chief Engineer, OITC <[EMAIL PROTECTED]>, http://www.oitc.com/ US Phone Numbers: 321-984-3714, 321-729-6258(fax), 321-258-2475(cell/voice mail,pager) Text Paging: http://www.oitc.com/Pager/sendmessage.html http://www.oitc.com/Antarctica/ PGP Public Keys available at: ldap://keyserver.pgp.com/;>PGP's Key Server http://www.oitc.com/OITC/PGPKeys.html;>OITC's Public Key List 14A7 A308 266A 3646 FBA8 9A86 E139 F108 B1BE 37BD
Re: [sqlite] Newbie problem with locks and DB repair
On Tue, 3 May 2005, Tom Shaw wrote: At 6:09 PM -0400 5/3/05, D. Richard Hipp wrote: On Tue, 2005-05-03 at 17:45 -0400, Tom Shaw wrote: Hi. I was using SQLite with PHP 5 (MacOSX) and due to some issues the php page timedout before completing the DB update. Now I can't read the DB via php nor via the sqlite command line tool. I keep getting DB busy/DB locked. How do I repair the DB and remove the "lock"and either 1) try to recover the data in the DB or 2) verify the data is OK and begin to reuse. SQLite is suppose to automatically repair itself. If you are getting "locks" that stick around, it must mean that PHP is somehow holding the database open (and locked) after it times out. Try shutting down and restarting your webserver and see if that doesn't clear the problem. Richard, I restarted Apache - No luck :-) Any other ideas? The current DB is only 144K if you want to look at it. is your db nfs mounted? No but the entire directory was also being accessed via webdav. Doing a restart cleared the problem but I hope not to have to do that in the future when it happens. Tom
Re: [sqlite] Newbie problem with locks and DB repair
On Tue, 2005-05-03 at 18:55 -0400, Tom Shaw wrote: At 6:09 PM -0400 5/3/05, D. Richard Hipp wrote: >On Tue, 2005-05-03 at 17:45 -0400, Tom Shaw wrote: >> Hi. >> >> I was using SQLite with PHP 5 (MacOSX) and due to some issues the php >> page timedout before completing the DB update. Now I can't read the >> DB via php nor via the sqlite command line tool. I keep getting DB >> busy/DB locked. How do I repair the DB and remove the "lock"and >> either 1) try to recover the data in the DB or 2) verify the data is >> OK and begin to reuse. >> > >SQLite is suppose to automatically repair itself. If you >are getting "locks" that stick around, it must mean that PHP >is somehow holding the database open (and locked) after it >times out. Try shutting down and restarting your webserver >and see if that doesn't clear the problem. Richard, I restarted Apache - No luck :-) Any other ideas? Do a "ps uax" and look for processes that seem to be PHP related. Then "kill -9" them. Richard, None running. Tom -- Tom Shaw - Chief Engineer, OITC <[EMAIL PROTECTED]>, http://www.oitc.com/ US Phone Numbers: 321-984-3714, 321-729-6258(fax), 321-258-2475(cell/voice mail,pager) Text Paging: http://www.oitc.com/Pager/sendmessage.html http://www.oitc.com/Antarctica/ PGP Public Keys available at: ldap://keyserver.pgp.com/;>PGP's Key Server http://www.oitc.com/OITC/PGPKeys.html;>OITC's Public Key List 14A7 A308 266A 3646 FBA8 9A86 E139 F108 B1BE 37BD
Re: [sqlite] Newbie problem with locks and DB repair
At 6:09 PM -0400 5/3/05, D. Richard Hipp wrote: On Tue, 2005-05-03 at 17:45 -0400, Tom Shaw wrote: Hi. I was using SQLite with PHP 5 (MacOSX) and due to some issues the php page timedout before completing the DB update. Now I can't read the DB via php nor via the sqlite command line tool. I keep getting DB busy/DB locked. How do I repair the DB and remove the "lock"and either 1) try to recover the data in the DB or 2) verify the data is OK and begin to reuse. SQLite is suppose to automatically repair itself. If you are getting "locks" that stick around, it must mean that PHP is somehow holding the database open (and locked) after it times out. Try shutting down and restarting your webserver and see if that doesn't clear the problem. Richard, I restarted Apache - No luck :-) Any other ideas? The current DB is only 144K if you want to look at it. Tom -- Tom Shaw - Chief Engineer, OITC <[EMAIL PROTECTED]>, http://www.oitc.com/ US Phone Numbers: 321-984-3714, 321-729-6258(fax), 321-258-2475(cell/voice mail,pager) Text Paging: http://www.oitc.com/Pager/sendmessage.html http://www.oitc.com/Antarctica/ PGP Public Keys available at: ldap://keyserver.pgp.com/;>PGP's Key Server http://www.oitc.com/OITC/PGPKeys.html;>OITC's Public Key List 14A7 A308 266A 3646 FBA8 9A86 E139 F108 B1BE 37BD
[sqlite] Newbie problem with locks and DB repair
Hi. I was using SQLite with PHP 5 (MacOSX) and due to some issues the php page timedout before completing the DB update. Now I can't read the DB via php nor via the sqlite command line tool. I keep getting DB busy/DB locked. How do I repair the DB and remove the "lock"and either 1) try to recover the data in the DB or 2) verify the data is OK and begin to reuse. TIA, Tom
