Re: [sqlite] 3.17.0 (bug?): ".mode" command ignoring ".separator" when -init used

On Tue, 14 Feb 2017 02:16:50 -0200 Dâniel Fraga wrote: > The solution was to provide the ".separator ," again after > ".mode line", but it seems redundant, since I already had specified it > in the "-init" file. Sorry, I mean ".mode list", although it should

[sqlite] 3.17.0 (bug?): ".mode" command ignoring ".separator" when -init used

3.17.0 release brings the following change: In the command-line shell, enhance the ".mode" command so that it restores the default column and row separators for modes "line", "list", "column", and "tcl". *** I use the sqlite command-line shell with the option -init

Re: [sqlite] Partially "hidden" row, very bizarre state

Being a database newb I'll defer to you guys, and plan to remove the unique keyword, but if the definition were "bad" shouldn't it fail to create the table? Not sure how it could be argued that accepting a definition which allegedly leads to a problem < 0.01% of the time is an acceptable

Re: [sqlite] Faster check: > or !=

On Mon, 13 Feb 2017 15:32:00 + jose isaias cabrera wrote: > I have a bunch of records to check, and I am wondering which is a > faster check. I am attaching a network DB as client, > > ATTACH 'h:\bkup\test.db' AS client; If you're concerned about speed or

Re: [sqlite] BUG: UPDATE with correlated sub-query

On Tue, 14 Feb 2017 00:49:29 +0700 Dan Kennedy wrote: > SQLite updates the first row (with a=1) and sets column "b" to 2. > But then, when it goes to update the next row, it runs the correlated > query a second time. And this time it returns 3. So you end up > setting "b"

Re: [sqlite] Could missing indexes ids cause slow queries?

On Mon, 13 Feb 2017 22:22:24 + jose isaias cabrera wrote: > sqlite> select id from LSOpenProjects where id > 13460 and id < > sqlite> 115520; > 13461 > 13462 > 13463 > 13464 > 13758 > 115516 > 115517 > 115518 > 115519 > sqlite> > > As you can see, the id indexing is

Re: [sqlite] Partially "hidden" row, very bizarre state

To further Keiths comment, I've never seen a database NOT treat a primary key as not unique. Primary Key automatically makes the field have a unique constraint. On Mon, Feb 13, 2017 at 8:14 PM, Keith Medcalf wrote: > > This is probably due to your bad table definition. > >

Re: [sqlite] Partially "hidden" row, very bizarre state

This is probably due to your bad table definition. CREATE TABLE messages_priority ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, type TEXT, json TEXT, options TEXT,

[sqlite] Problem compiling 3.17.0 in MSVS 12

Downloaded the latest 3.17.0 (sqlite-autoconf-317.tar.gz ) and replaced my old 3.16.2 sqlite3.c file in the VC. Now for some reason I get lots of errors when building a Windows dll. First one is at line 16116 in this code block:

Re: [sqlite] Intercepting execution of sqlite3 command to see final query

On 02/13/2017 04:14 PM, David Raymond wrote: In the page for expressions (http://www.sqlite.org/lang_expr.html) check out the Parameters section and the REGEXP operator section: "The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by

Re: [sqlite] sqlite3_blob_bytes64() ?

> Le 13 févr. 2017 à 23:23, Olivier Mascia a écrit : > > What is the purpose of sqlite3_bind_blob64() and sqlite3_bind_zeroblob64()? > > The documentation says: "The current implementation will only support a > string or BLOB length up to (2^31)-1 or 2147483647". > The

[sqlite] sqlite3_blob_bytes64() ?

Dear, What is the purpose of sqlite3_bind_blob64() and sqlite3_bind_zeroblob64()? The documentation says: "The current implementation will only support a string or BLOB length up to (2^31)-1 or 2147483647". The default upper limit is even set lower (for good reasons) at 1 thousand millions.

[sqlite] Could missing indexes ids cause slow queries?

Greetings! I have an old db with lots of data and the one of the main tables has a messed up index. For example, sqlite> select id from LSOpenProjects where id > 13460 and id < 115520; 13461 13462 13463 13464 13758 115516 115517 115518 115519 sqlite> As you can see, the id indexing is

Re: [sqlite] Faster check: > or !=

On 13 Feb 2017, at 9:03pm, jose isaias cabrera wrote: >> Can you set an index on id, projId, and Xtrab ? > Already have one for each. > > CREATE INDEX OpPid ON LSOpenProjects (ProjID); > CREATE INDEX XtraBLSOpenProjects ON LSOpenProjects (XtraB); No. This does not do

Re: [sqlite] Partially "hidden" row, very bizarre state

Thanks again to everyone for the guidance. I read through that list of corruption candidates and most don't seem to apply. Only one app accesses the db file, it's on a local NTFS partition, there were no power failures or hard reboots, no pragmas or non-default access options were used. So

Re: [sqlite] Patch for consideration: auto_vacuum slack.

On Mon, Feb 13, 2017 at 12:13 PM, Richard Hipp wrote: > > Scott: The motivation for your patch seem to be to get auto-vacuum to > run a little faster. But if performance is your goal, why not just > turn auto-vacuum off? Or, failing that, set it to INCREMENTAL and > then run

Re: [sqlite] Patch for consideration: auto_vacuum slack.

I use it, not due to drive space, but because I sometimes check my DB into a source code repo. On Mon, Feb 13, 2017 at 3:13 PM, Richard Hipp wrote: > On 2/13/17, Scott Hess wrote: > > > > Below is a patch which implements [PRAGMA auto_vacuum_slack_pages =

Re: [sqlite] Intercepting execution of sqlite3 command to see final query

In the page for expressions (http://www.sqlite.org/lang_expr.html) check out the Parameters section and the REGEXP operator section: "The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will

Re: [sqlite] Faster check: > or !=

On 2017-02-13 18:07, Simon Slavin wrote: On 13 Feb 2017, at 3:32pm, jose isaias cabrera wrote: Any input would be greatly appreciated. I doubt there's much difference, though the one with the fixed timestamp string may be a touch faster. But the real speedup

Re: [sqlite] Faster check: > or !=

On 2017-02-13 17:20, Adam DeVita wrote: How much control do you have? All of it. Can you put out your date code into an integer field? Hmmm... I could. I have to do some programming to replace the normal date with the integer date. Can you set an index on id, projId, and Xtrab ?

Re: [sqlite] Patch for consideration: auto_vacuum slack.

I use auto-vacuum in my application storage for work. This was introduced in about 2013. The motivation was more political than anything though, as convincing some as to the introduction requiring jumping through some pretty arbitrary hoops. Enabling auto-vacuum was one of those, to mitigate

[sqlite] Intercepting execution of sqlite3 command to see final query

Hello, The premise of this is the following: I'm using sqlite3 in a django application. I'm having it log the SQL that it issues (once you get passed its magical ORM setup), but that command does not work. I.e. I have something like the following (the python is not important, but does set

Re: [sqlite] Partially "hidden" row, very bizarre state

First off, thanks for the help! Here is the output: pragma integrity_check; **Command returned -1** select '<'||id||'>',created from messages_priority order by id limit 5; created <424>2/12/2017 8:07:10 PM <423>2/12/2017 8:07:09 PM <425>2/12/2017 8:07:11 PM <426>2/12/2017

Re: [sqlite] Faster check: > or !=

How much control do you have? Can you put out your date code into an integer field? Can you set an index on id, projId, and Xtrab ? regards, Adam From: sqlite-users on behalf of jose isaias cabrera

Re: [sqlite] Patch for consideration: auto_vacuum slack.

On 2/13/17, Scott Hess wrote: > > Below is a patch which implements [PRAGMA auto_vacuum_slack_pages = N]. > This setting allows client code to signal that auto_vacuum can leave pages > on the freelist until releasing them would allow a db size change. This makes me want to ask:

Re: [sqlite] BUG: UPDATE with correlated sub-query

Thanks Dan I couldn't find a reference to this behaviour in the documentation. Do you think it would be possible to add a few words? On Mon, Feb 13, 2017 at 7:49 PM Dan Kennedy wrote: > On 02/13/2017 01:44 PM, Dudu Markovitz wrote: > > Good morning > > > > While

Re: [sqlite] Partially "hidden" row, very bizarre state

On 13 Feb 2017, at 7:12pm, Jeff B. wrote: > The vacuum followed by a reindex seems to have fixed the db (see below). But > are there any other theories as to how we got to this point? Could I have > possibly caused this or do I just need to build in code to detect problems >

Re: [sqlite] Partially "hidden" row, very bizarre state

On 2/13/17, Jeff B. wrote: > But are there any other theories as to how we got to this > point? Please see https://www.sqlite.org/howtocorrupt.html -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list

[sqlite] Patch for consideration: auto_vacuum slack.

A developer was asking me questions about auto_vacuum I/O characteristics, because they were worried about "churn", where a page is moved to fill a freelist gap, then soon enough a new page is allocated anyhow, so the move wasn't really necessary. This made me wonder if auto_vacuum recognized

Re: [sqlite] Partially "hidden" row, very bizarre state

The vacuum followed by a reindex seems to have fixed the db (see below). But are there any other theories as to how we got to this point? Could I have possibly caused this or do I just need to build in code to detect problems like this and rebuild on the fly? I'm new to SQLite so I don't

Re: [sqlite] Partially "hidden" row, very bizarre state

On 2017/02/13 8:55 PM, Jeff B. wrote: Should have read up on integrity_check before posting the results... I re-worked my tool and here is the output: pragma integrity_check; *** in database main *** On tree page 11 cell 0: Rowid 424 out of order wrong # of entries in index

Re: [sqlite] Partially "hidden" row, very bizarre state

Thank you as well for offering some tips. I have run the integrity_check and the reindex. The reindex got rid of one of the 2 errors, but the overall problem still persists: pragma integrity_check; *** in database main *** On tree page 11 cell 0: Rowid 424 out of order wrong # of entries in

Re: [sqlite] Partially "hidden" row, very bizarre state

On 13 Feb 2017, at 6:55pm, Jeff B. wrote: > Should have read up on integrity_check before posting the results... I > re-worked my tool and here is the output: > > > pragma integrity_check; > > *** in database main *** > On tree page 11 cell 0: Rowid 424 out of order > wrong #

Re: [sqlite] Partially "hidden" row, very bizarre state

Should have read up on integrity_check before posting the results... I re-worked my tool and here is the output: pragma integrity_check; *** in database main *** On tree page 11 cell 0: Rowid 424 out of order wrong # of entries in index sqlite_autoindex_messages_priority_1 **2 rows returned**

Re: [sqlite] Partially "hidden" row, very bizarre state

First off, thanks for the help! Here is the output: pragma integrity_check; **Command returned -1** select '<'||id||'>',created from messages_priority order by id limit 5; created <424>2/12/2017 8:07:10 PM <423>2/12/2017 8:07:09 PM <425>2/12/2017 8:07:11 PM <426>2/12/2017

Re: [sqlite] Faster check: > or !=

On 13 Feb 2017, at 3:32pm, jose isaias cabrera wrote: > Any input would be greatly appreciated. I doubt there’s much difference, though the one with the fixed timestamp string may be a touch faster. But the real speedup would come from an index like this: CREATE INDEX

Re: [sqlite] Partially "hidden" row, very bizarre state

Correction: That's "limit 5;" of course on the second query... On 2017/02/13 7:58 PM, R Smith wrote: Could you run and post the output of your tool to the following two queries please: pragma integrity_check; select '<'||id||'>',created from messages_priority order by id limit ; Thanks,

Re: [sqlite] Partially "hidden" row, very bizarre state

Could you run and post the output of your tool to the following two queries please: pragma integrity_check; select '<'||id||'>',created from messages_priority order by id limit ; Thanks, Ryan On 2017/02/13 7:32 PM, Jeff B. wrote: Hi Everyone, Sorry for the strange subject, but the

Re: [sqlite] Partially "hidden" row, very bizarre state

I vaguely recall a while ago someone finding a small bug when "primary key unique" was used. Since those are redundant you should get rid of the "unique" in the id field creation as "primary key" takes care of that. (It "shouldn't" make a difference, but don't tempt fate) Other things: run a

Re: [sqlite] BUG: UPDATE with correlated sub-query

On 02/13/2017 01:44 PM, Dudu Markovitz wrote: Good morning While answering question on stackoverflow I have noticed a bug related to UPDATE using correlated sub-query. The demonstration code can be found in the attached file bug_report.sql and the

[sqlite] Partially "hidden" row, very bizarre state

Hi Everyone, Sorry for the strange subject, but the situation I'm facing is so odd that I wasn't even sure how to summarize it. First, a little context: I'm using v1.0.104 of System.Data.SQLite.Core in my C#/.NET 4.5 project. I have a table that is created as follows:

[sqlite] BUG: UPDATE with correlated sub-query

Good morning While answering question on stackoverflow I have noticed a bug related to UPDATE using correlated sub-query. The demonstration code can be found in the attached file bug_report.sql and the results of the demonstration with some comments

[sqlite] Faster check: > or !=

Greetings! I have a bunch of records to check, and I am wondering which is a faster check. I am attaching a network DB as client, ATTACH 'h:\bkup\test.db' AS client; and then do an INSERT based on some logic, and one of those login is checking against a variable or an actual DB value. Here

[sqlite] Inconsistent check for symbol SQLITE_DEBUG in amalgamation version 3.16.2

In the documentation for the compile time options for debugging http://sqlite.org/compile.html#debug the symbol SQLITE_DEBUG is shown without a value. And in most places in the SQLite source code only the definition status is checked using #ifdef SQLITE_DEBUG or #if defined(SQLITE_DEBUG)

Re: [sqlite] Typos in macro usages for MSVC intrinsics

Good eye, Bert. Thanks. On 2/13/17, rhuij...@apache.org wrote: > Hi, > > When looking at recent commit > https://www.sqlite.org/src/info/798fb9d70d2e5f95 I spotted a few cases where > 'MSCV_VERSION' is checked instead of 'MSVC_VERSION' (The V and C swapped), > you

[sqlite] Typos in macro usages for MSVC intrinsics

Hi, When looking at recent commit https://www.sqlite.org/src/info/798fb9d70d2e5f95 I spotted a few cases where 'MSCV_VERSION' is checked instead of 'MSVC_VERSION' (The V and C swapped), you might want to fix these before release. Thanks, Bert

Re: [sqlite] Bug: CREATE TABLE AS with GROUP BY preserves backticks in generated column name

Hello, On 2017-02-10 20:34, Alek Storm wrote: CREATE TABLE t1 (col1 INT); CREATE TABLE t2 AS SELECT `col1` FROM t1 GROUP BY col1; .schema t2 CREATE TABLE t2("`col1`" INT); I expected: CREATE TABLE t2(col1 INT); This comes from the fact that all TK_COLUMN expressions in SELECT ... GROUP BY

Re: [sqlite] Extension Loading

>You could create a user-defined function to call LoadExtension(), but >why do you need to do this from SQL? A good question, but I am attempting to use a different .Net wrapper for this project which has not imported sqlite3_enable_load_extension(). The simplest thing would be to import the