Re: [sqlite] Multiple ATTACHments to the same DB?
On Thursday, 1 August, 2019 16:32, Randall Smith wrote: >I am developing a setup where multiple independent slave SQLite >databases are periodically synching with the same master database. >It would be convenient if I could ATTACH the slave and master DBs to >each other during the synch so I could run the synch as a single >transaction and so I can do simple and fast operations on both >databases within a single query. >My question is: Can I have 10 or whatever slaves ATTACHed to the >same master at the same the time (that is, constantly ATTACHed while >he slave is open), in the same spirit as having 10 database >connections open to the master all the time? Yes. There is a compile-time define that controls the maximum number of attached databases, and the default is 10 with an upper limit of 125. The limit can be LOWERED using the sqlite3_limit() function. See https://www.sqlite.org/limits.html#max_attached >Or do I need to set up and tear down individual ATTACHments as part >of each sync operation? >I would prefer the former, but it's not clear what the issues are. >Can an ATTACH request encounter a "locked database" error, or do >these only occur when an actual modification is attempted? How are >ATTACHments different from connections? https://www.sqlite.org/lang_attach.html You will get an error if you try to attach more databases than the limit. Otherwise, ATTACH merely attaches an additional database file to the current connection. Transactions which affect multiple attached databases are atomic across all referenced attached databases (and behave exactly as you would expect). The main database (the one you open with one of the sqlite3_open* calls) is nothing more (nor less) than an attached database with the name "main". If you have tables in multiple attached databases with the same name, then you need to qualify the table with the name of the schema (attachment name) if you care which one of the tables the query is referring to (otherwise it will use the first one found with that name). A "connection" is the thing on which you issue commands, that is connected to a database. The "main" file that you open when creating a database connection is given the schema (attachment) name "main". The temporary database (if you use one) is an attached database called "temp". You can attach as many "files" as you like up to the limit, and each will have its own schema (attachment) name. If you do not specify the schema (attachment) name to which a table belongs in an SQL statement, then whatever attached file contains the table is the one that is assumed. Triggers & Indexes may only refer to things contained within their own database file. >Thanks for any words of wisdom here. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple ATTACHments to the same DB?
On 1 Aug 2019, at 11:32pm, Randall Smith wrote: > Can an ATTACH request encounter a "locked database" error, or do these only > occur when an actual modification is attempted? How are ATTACHments > different from connections? Sorry, hit 'send' too soon. I don't think ATTACH can get a 'locked' or 'busy' error. I think you're right that those errors could happen only inside a transaction. A connection is the way SQLite keeps track of a database, the state that database is in, any transaction it is in the middle of, any statements it is in the middle of, the parts of the database which are cached, and lots of other things like that. Two different programs, or two different processes in one program, would be expected to use two different connections to talk to a database, or to two different databases. Otherwise one program or process might interfere with the other, and they would both have to have complicated logic in to figure out what to do about interference. On the other hand, the ATTACH command is a way for one connection to talk to two or more databases at once. Your request is a good example of why you might want to do this. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple ATTACHments to the same DB?
On 1 Aug 2019, at 11:32pm, Randall Smith wrote: > My question is: Can I have 10 or whatever slaves ATTACHed to the same master > at the same the time (that is, constantly ATTACHed while he slave is open), > in the same spirit as having 10 database connections open to the master all > the time? Or do I need to set up and tear down individual ATTACHments as > part of each sync operation? It should not be a problem to have 10 attached databases on the same connection. SQLite was designed to do this without problems. In case you didn't know, there's a command of the form INSERT INTO tableA SELECT * FROM tableB ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple ATTACHments to the same DB?
Hi, all. I am developing a setup where multiple independent slave SQLite databases are periodically synching with the same master database. It would be convenient if I could ATTACH the slave and master DBs to each other during the synch so I could run the synch as a single transaction and so I can do simple and fast operations on both databases within a single query. My question is: Can I have 10 or whatever slaves ATTACHed to the same master at the same the time (that is, constantly ATTACHed while he slave is open), in the same spirit as having 10 database connections open to the master all the time? Or do I need to set up and tear down individual ATTACHments as part of each sync operation? I would prefer the former, but it's not clear what the issues are. Can an ATTACH request encounter a "locked database" error, or do these only occur when an actual modification is attempted? How are ATTACHments different from connections? Thanks for any words of wisdom here. Randall. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
After I have read the documentation of sqlite3ExpirePreparedStatements, I better see how the GRDB authorizers dance is a misuse of the library. The goal of GRDB authorizers, which is only to grab information about the statements before they are executed, is at odds with the fact that SQLite connections have a single authorizer dedicated to restricting database accesses. I understand why a change of authorizer invalidates statements. Conclusion: I should use a single authorizer and never change it. This issue report remains interesting, because SQLite exhibits inconsistent behaviors depending of the invalidated statements. But the real fix for "my" issue is to refactor my use of authorizers. On Thu, Aug 1, 2019 at 10:47 PM Gwendal Roué wrote: > For the context, GRDB uses authorizers as a support for its database > observation features: > > - during the compilation of a read statements in order to know what part > of the database would be accessed by the statement. > - during the compilation of other statements in order to know what part of > the database would be modified, or which transaction/savepoint operation > would be executed, or if the database schema would change. > - during the execution of statements for the sole purpose of preventing > the truncate optimization when the library user has expressed the desire of > being notified of row deletions. > > Joined together, all those pieces of observation allow the library user to > say that it wants to track a "database region" (sets of tables, columns, > and rowids), and be notified of any transaction that has committed changes > to this region. This is insanely useful. With full support for raw SQL. > > I'm not sure this is what authorizers were designed for, but... I can't > live without them now :-) > > On Thu, Aug 1, 2019 at 10:26 PM Gwendal Roué > wrote: > >> Yes, Richard, this fixes the problem! Tested with my local copy of SQLite >> 3.28.0. >> >> On Thu, Aug 1, 2019 at 9:23 PM Richard Hipp wrote: >> >>> On 8/1/19, Gwendal Roué wrote: >>> > >>> > 1. set authorizer >>> > 2. compile statement >>> > 3. reset authorizer >>> > 4. step >>> > 5. set authorizer (and do something else) >>> > 6. step -> SQLITE_ABORT_ROLLBACK >>> >>> Please test to see if changing the "0" to a "1" on the line of code >>> shown below fixes the problem, and report back. >>> >>> https://www.sqlite.org/src/artifact/0fac710388?ln=81 >>> -- >>> D. Richard Hipp >>> d...@sqlite.org >>> ___ >>> sqlite-users mailing list >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
For the context, GRDB uses authorizers as a support for its database observation features: - during the compilation of a read statements in order to know what part of the database would be accessed by the statement. - during the compilation of other statements in order to know what part of the database would be modified, or which transaction/savepoint operation would be executed, or if the database schema would change. - during the execution of statements for the sole purpose of preventing the truncate optimization when the library user has expressed the desire of being notified of row deletions. Joined together, all those pieces of observation allow the library user to say that it wants to track a "database region" (sets of tables, columns, and rowids), and be notified of any transaction that has committed changes to this region. This is insanely useful. With full support for raw SQL. I'm not sure this is what authorizers were designed for, but... I can't live without them now :-) On Thu, Aug 1, 2019 at 10:26 PM Gwendal Roué wrote: > Yes, Richard, this fixes the problem! Tested with my local copy of SQLite > 3.28.0. > > On Thu, Aug 1, 2019 at 9:23 PM Richard Hipp wrote: > >> On 8/1/19, Gwendal Roué wrote: >> > >> > 1. set authorizer >> > 2. compile statement >> > 3. reset authorizer >> > 4. step >> > 5. set authorizer (and do something else) >> > 6. step -> SQLITE_ABORT_ROLLBACK >> >> Please test to see if changing the "0" to a "1" on the line of code >> shown below fixes the problem, and report back. >> >> https://www.sqlite.org/src/artifact/0fac710388?ln=81 >> -- >> D. Richard Hipp >> d...@sqlite.org >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
Yes, Richard, this fixes the problem! Tested with my local copy of SQLite 3.28.0. On Thu, Aug 1, 2019 at 9:23 PM Richard Hipp wrote: > On 8/1/19, Gwendal Roué wrote: > > > > 1. set authorizer > > 2. compile statement > > 3. reset authorizer > > 4. step > > 5. set authorizer (and do something else) > > 6. step -> SQLITE_ABORT_ROLLBACK > > Please test to see if changing the "0" to a "1" on the line of code > shown below fixes the problem, and report back. > > https://www.sqlite.org/src/artifact/0fac710388?ln=81 > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
On 8/1/19, Gwendal Roué wrote: > > 1. set authorizer > 2. compile statement > 3. reset authorizer > 4. step > 5. set authorizer (and do something else) > 6. step -> SQLITE_ABORT_ROLLBACK Please test to see if changing the "0" to a "1" on the line of code shown below fixes the problem, and report back. https://www.sqlite.org/src/artifact/0fac710388?ln=81 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Issue report: sqlite3_set_authorizer triggers error 4/516 (SQLITE_ABORT_ROLLBACK) during statement iteration
Hello,
This is an issue report.
SQLite from version 3.8.8 to version 3.24.0 exhibits an issue which
prevents the use of sqlite3_set_authorizer during the iteration of a
statement. The issue does not happen with all statements, but only with
some of them.
It basically goes this way:
1. set authorizer
2. compile statement
3. reset authorizer
4. step
5. set authorizer (and do something else)
6. step -> SQLITE_ABORT_ROLLBACK
Please find below a reproducible test case, reduced as much as I could. It
outputs `code = 516`, when it should not.
A piece of information that may help narrowing the trouble: I could only
trigger the error with the provided query, that involve two tables.
Finally, I post this message after investigation for an issue in the GRDB
Swift library: https://github.com/groue/GRDB.swift/issues/583
Thanks for reading,
Gwendal Roué
#include
#include
int authorize(void* a,int b,const char* c,const char* d,const char* e,const
char* f) {
return SQLITE_OK;
}
int main() {
sqlite3 *connection;
sqlite3_open_v2(":memory:", &connection, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_NOMUTEX, 0);
sqlite3_extended_result_codes(connection, 1);
sqlite3_exec(connection, "CREATE TABLE user (username TEXT NOT NULL)",
0, 0, 0);
sqlite3_exec(connection, "CREATE TABLE flagUser (username TEXT NOT
NULL)", 0, 0, 0);
sqlite3_exec(connection, "INSERT INTO flagUser (username) VALUES
('User1')", 0, 0, 0);
sqlite3_exec(connection, "INSERT INTO flagUser (username) VALUES
('User2')", 0, 0, 0);
sqlite3_stmt *statement;
sqlite3_set_authorizer(connection, authorize, 0);
sqlite3_prepare_v3(connection, "SELECT * FROM flagUser WHERE (SELECT
COUNT(*) FROM user WHERE username = flagUser.username) = 0", -1, 0,
&statement, 0);
sqlite3_set_authorizer(connection, 0, 0);
int code = sqlite3_step(statement);
printf("code = %i\n", code);
sqlite3_set_authorizer(connection, 0, 0);
code = sqlite3_step(statement);
printf("code = %i\n", code);
sqlite3_finalize(statement);
sqlite3_close_v2(connection);
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Large database backup
On 1/8/62 19:49, Tammisalo Toni wrote: Hi! I have an application which is using sqlite database in WAL mode. There is a need for periodic backups to a remote site without obstructing the normal operation. Both read and write access is required during the backup. At the moment I have system which first blocks checkpoints as otherwise backup was restarted too often. Backup is done to remote database implemented with sqlite vfs layer. What happens in my tests is that all sqlite3_backup_step()'s complete without actually writing anything to remote database. Only during last step all pages are written. This would be annoying from progress monitoring point of view. However, as database mutex is held during this time it actually blocks all access to the database for a long period of time. Changing the backup step size does not help as all this happens at last step regardless. So, is this a bug? I'm I doing something wrong? What I was hoping was that backup would not hold database mutex while it is writing to the other database or at least allow splitting the work with sqlite3_backup_step() so that at least some work could be done while backup is in progress. I actually have strong impression that this worked better with some older sqlite version. Currently using 3.27.2. Relevant part of the stack trace: #8 0x005dc870 in sqlite3OsWrite (id=0x7fc1a4120f98, pBuf=0x7fc1a47b0e88, amt=, offset=) at sqlite3.c:9 #9 pager_write_pagelist (pPager=0x7fc1a41216f8, pList=0x7fc1a47c0ec0) at sqlite3.c:54971 #10 0x005bb1a5 in sqlite3PagerCommitPhaseOne (pPager=0x7fc1a41216f8, zMaster=0x0, noSync=0) at sqlite3.c:57050 #11 0x005b968f in sqlite3_backup_step (p=0x7fc1a4056658, nPage=) at sqlite3.c:74033 Seems that all database pages are written out in pager_write_pagelist() in single loop. The backup process writes through the cache of the destination database. So data is only written to disk when either (a) the cache is full or (b) the transaction is committed by the last sqlite3_backup_step() call. If you reduce the size of the cache used by the destination db writing should begin in an earlier sqlite3_backup_step() call. Dan. Also, I'm open to other suggestions. I was contemplating to just copy the database file directly while WAL checkpoints are not done but I read some comments that suggested that it would not be a safe to do that. Any help or suggestions would be appreciated! Toni Tammisalo ttamm...@iki.fi ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] storing blobs in a separate table
On 8/1/19, David Raymond wrote: > [autovacuum] doesn't let you skip ahead in the overflow page chain. Actually it does. Sort of. Sometimes. There is an optimization that lets the btree layer "guess" the next page in an overflow chain and verify its guess using the pointer-map information. If it guesses correctly, it can avoid reading a page from disk. But it has to fallback to reading the page if it guesses incorrectly. See the 22 lines of code here: https://sqlite.org/src/artifact/58d002f71?ln=4626-4648 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] storing blobs in a separate table
https://www.sqlite.org/fileformat2.html is the page with the nitty gritty for the file format. Overflow pages are stored in a singly linked list of pages, so you have to traverse through all of the pages to get to the end. So while you may know right away that you want the 20th overflow page for example, you still have to load all 19 pages in between to find out what page that is. [DD] There's a special mode where SQLite keeps extra pages to keep track of pages, and thus can potentially avoid that "page-chain", but it's not often used I believe. I think you're referring to the Pointer Map pages which are used for incremental vacuum. If used, then for every single page in the database it stores the "parent" page number. Ie backwards up the btree or backwards in the overflow page list. It's basically there so that if you want to move the contents of page X to somewhere else in the file it gives you the page number Y which has the pointer to it that will need to be changed to the new page number. But it doesn't let you skip ahead in the overflow page chain. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] storing blobs in a separate table
On Thu, Aug 1, 2019 at 5:44 PM Jens Alfke wrote:
> > On Jul 31, 2019, at 5:02 AM, Hick Gunter wrote:
> > SQLite stores rows in a compressed format that requires decoding. To
> access the nth field, all the fields that come before it need to be decoded.
>
> My understanding is that it’s just a matter of a byte-count before each
> field. So getting to the n’th field just requires n-1 memory reads and
> pointer additions, a handful of machine instructions. If so, that won’t
> measurably affect performance.
>
[DD] I think this assumes the row fits inside a page. Which with (inline)
blobs, may not be the case at all.
[DD] And Pages form a "linked-list", so reading past the blob may mean
reading ("paging") all those blob pages to follow that chain of page-ids,
to "get-past" a large blob.
[DD] There's a special mode where SQLite keeps extra pages to keep track of
pages, and thus can potentially avoid that "page-chain", but it's not often
used I believe.
[DD] But I'm no expert here :). --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Large database backup
On Thu, Aug 1, 2019 at 5:02 PM Olivier Mascia wrote: > > Le 1 août 2019 à 14:49, Tammisalo Toni a > écrit : > > I have an application which is using sqlite database in WAL mode. There > is a need for > > periodic backups to a remote site without obstructing the normal > operation. Both read > > and write access is required during the backup. At the moment I have > system which > > first blocks checkpoints as otherwise backup was restarted too often. > Backup is done > > to remote database implemented with sqlite vfs layer. > > ... > > Also, I'm open to other suggestions. > > You are using WAL mode. > Have you tried coding your backup as a single step (passing -1 for the > number of pages to step)? > > int status = sqlite3_backup_step(bck, -1); > > Or if you really want to call sqlite3_backup_step() incrementally (X pages > at a time), then do BEGIN [DEFERRED] [TRANSACTION] first. > > Your backup copy should then be allowed to proceed from start to finish > without impacting readers and writers, nor being impacted by them. You shouldn't see any restart. Your backup will be a representation of the > database as it was when the single (or first) sqlite3_backup_step() call > started. > I've little practical experience here on this, but unless I'm mistaken, it does mean the WAL file cannot be check-pointed, while readers (including the backup) are still operating (AFAIK). Not a problem per-se, just something to be aware of. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Warning - function 'sqlite3UserAuthCheckLogin' has internal linkage but is not defined
Thanks Keith. I appended the contents of the ext/userauth/userauth.c file to the bottom of the amalgamation (as per the instructions in ext/userauth/user-auth.txt) and it works fine. From: sqlite-users on behalf of Keith Medcalf Sent: Thursday, August 1, 2019 4:11:02 PM To: SQLite mailing list Subject: Re: [sqlite] Warning - function 'sqlite3UserAuthCheckLogin' has internal linkage but is not defined This define enables calls to the userauth code. You need to include the userauth.c code into your compilation unit. See ext/userauth/user-auth.txt or https://www.sqlite.org/src/artifact/e6641021a9210364 -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of x >Sent: Thursday, 1 August, 2019 09:00 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Warning - function 'sqlite3UserAuthCheckLogin' has >internal linkage but is not defined > >I’m using the amalgamation in my Embarcadero Rad Studio 10 app (Clang >compiler). I defined SQLITE_USER_AUTHENTICATION to test something and >received the above warning along with the following two > >[bcc32c Warning] sqlite3.c(16307): function 'sqlite3UserAuthInit' has >internal linkage but is not defined > >[bcc32c Warning] sqlite3.c(16308): function 'sqlite3CryptFunc' has >internal linkage but is not defined > > >I’m clueless as to the significance but thought I better report it >just in case. >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] storing blobs in a separate table
> On Jul 31, 2019, at 5:02 AM, Hick Gunter wrote: > > SQLite stores rows in a compressed format that requires decoding. To access > the nth field, all the fields that come before it need to be decoded. My understanding is that it’s just a matter of a byte-count before each field. So getting to the n’th field just requires n-1 memory reads and pointer additions, a handful of machine instructions. If so, that won’t measurably affect performance. The increased size of the record does make a big difference, though, for the reasons discussed earlier. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Warning - function 'sqlite3UserAuthCheckLogin' has internal linkage but is not defined
This define enables calls to the userauth code. You need to include the userauth.c code into your compilation unit. See ext/userauth/user-auth.txt or https://www.sqlite.org/src/artifact/e6641021a9210364 -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of x >Sent: Thursday, 1 August, 2019 09:00 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Warning - function 'sqlite3UserAuthCheckLogin' has >internal linkage but is not defined > >I’m using the amalgamation in my Embarcadero Rad Studio 10 app (Clang >compiler). I defined SQLITE_USER_AUTHENTICATION to test something and >received the above warning along with the following two > >[bcc32c Warning] sqlite3.c(16307): function 'sqlite3UserAuthInit' has >internal linkage but is not defined > >[bcc32c Warning] sqlite3.c(16308): function 'sqlite3CryptFunc' has >internal linkage but is not defined > > >I’m clueless as to the significance but thought I better report it >just in case. >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Large database backup
> Le 1 août 2019 à 14:49, Tammisalo Toni a écrit : > > Hi! > > I have an application which is using sqlite database in WAL mode. There is a > need for > periodic backups to a remote site without obstructing the normal operation. > Both read > and write access is required during the backup. At the moment I have system > which > first blocks checkpoints as otherwise backup was restarted too often. Backup > is done > to remote database implemented with sqlite vfs layer. > ... > Also, I'm open to other suggestions. You are using WAL mode. Have you tried coding your backup as a single step (passing -1 for the number of pages to step)? int status = sqlite3_backup_step(bck, -1); Or if you really want to call sqlite3_backup_step() incrementally (X pages at a time), then do BEGIN [DEFERRED] [TRANSACTION] first. Your backup copy should then be allowed to proceed from start to finish without impacting readers and writers, nor being impacted by them. You shouldn't see any restart. Your backup will be a representation of the database as it was when the single (or first) sqlite3_backup_step() call started. Background: https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupstep "Every call to sqlite3_backup_step() obtains a shared lock on the source database that lasts for the duration of the sqlite3_backup_step() call." I can't comment on buffering issues, flushing things and so on when outputting through a custom VFS. — Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Warning - function 'sqlite3UserAuthCheckLogin' has internal linkage but is not defined
I’m using the amalgamation in my Embarcadero Rad Studio 10 app (Clang compiler). I defined SQLITE_USER_AUTHENTICATION to test something and received the above warning along with the following two [bcc32c Warning] sqlite3.c(16307): function 'sqlite3UserAuthInit' has internal linkage but is not defined [bcc32c Warning] sqlite3.c(16308): function 'sqlite3CryptFunc' has internal linkage but is not defined I’m clueless as to the significance but thought I better report it just in case. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Large database backup
Hi! I have an application which is using sqlite database in WAL mode. There is a need for periodic backups to a remote site without obstructing the normal operation. Both read and write access is required during the backup. At the moment I have system which first blocks checkpoints as otherwise backup was restarted too often. Backup is done to remote database implemented with sqlite vfs layer. What happens in my tests is that all sqlite3_backup_step()'s complete without actually writing anything to remote database. Only during last step all pages are written. This would be annoying from progress monitoring point of view. However, as database mutex is held during this time it actually blocks all access to the database for a long period of time. Changing the backup step size does not help as all this happens at last step regardless. So, is this a bug? I'm I doing something wrong? What I was hoping was that backup would not hold database mutex while it is writing to the other database or at least allow splitting the work with sqlite3_backup_step() so that at least some work could be done while backup is in progress. I actually have strong impression that this worked better with some older sqlite version. Currently using 3.27.2. Relevant part of the stack trace: #8 0x005dc870 in sqlite3OsWrite (id=0x7fc1a4120f98, pBuf=0x7fc1a47b0e88, amt=, offset=) at sqlite3.c:9 #9 pager_write_pagelist (pPager=0x7fc1a41216f8, pList=0x7fc1a47c0ec0) at sqlite3.c:54971 #10 0x005bb1a5 in sqlite3PagerCommitPhaseOne (pPager=0x7fc1a41216f8, zMaster=0x0, noSync=0) at sqlite3.c:57050 #11 0x005b968f in sqlite3_backup_step (p=0x7fc1a4056658, nPage=) at sqlite3.c:74033 Seems that all database pages are written out in pager_write_pagelist() in single loop. Also, I'm open to other suggestions. I was contemplating to just copy the database file directly while WAL checkpoints are not done but I read some comments that suggested that it would not be a safe to do that. Any help or suggestions would be appreciated! Toni Tammisalo ttamm...@iki.fi ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Error in recover sqlite3 database
The error is due to a full disk. You should not be deleting files associated with an SQLite db file. Have you tried running pragma integrity_check(); before the disk actually becomes full? Other than corruption of the file, the two candidates are internal fragmentation (doing lots of INSERT and DELETE operations), which would be remedied by running VACUUM (NOTE: may require up to double the current file size); or just the sheer volume of data (doing lots of INSERTS and never DELETEing outdated rows), which you would need to handle in your application. The fact that "backing up" (how? Export/import or the SQLite backup API?) the db file helps would indicate either fragmentation or corruption. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von bhandari_nikhil Gesendet: Donnerstag, 01. August 2019 10:48 An: sqlite-users@mailinglists.sqlite.org Betreff: Re: [sqlite] [EXTERNAL] Error in recover sqlite3 database This is my table, I just wanted to show the error that it is throwing. Is the error due to /tmp being full on my device ? Further debugging at our end showed that one way to replicate the problem is to remove the hot journal file in the middle of an insert transaction but we are not sure if that is the actual cause of the problem or not. In the application, we have a logic to check the db file size and if it grows beyond a certain threshold, we would take a backup of the db file. But once we do the delete-the journal-in-the middle-of-a-transaction operation, this size check never passes and the db size keeps on growing. Why could that be ? -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Error in recover sqlite3 database
This is my table, I just wanted to show the error that it is throwing. Is the error due to /tmp being full on my device ? Further debugging at our end showed that one way to replicate the problem is to remove the hot journal file in the middle of an insert transaction but we are not sure if that is the actual cause of the problem or not. In the application, we have a logic to check the db file size and if it grows beyond a certain threshold, we would take a backup of the db file. But once we do the delete-the journal-in-the middle-of-a-transaction operation, this size check never passes and the db size keeps on growing. Why could that be ? -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Double CTRL-C in shell get you completely out
On Wed, Jul 31, 2019 at 8:37 PM Tony Papadimitriou wrote: > Recently CTRL-C was improved to abort the query and stay in the CLI. This > is very good. > FWIW, SQliteSpy uses the escape key (ESC) to abort a run-away query (Hello incorrect recursive CTE!) I'd prefer CTRL-C to keep killing the current process, and ESC to abort queries. My $0.02... --DD PS: Avoids overloading the meaning of CTRL-C/D/Z with non-standard behavior. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
