[sqlite] No such column error
I have been developing a c++ program in windows. In this program, in a loop, I open database, query and update the records then close it. I am encountering randomly with "No such column error". Query looks like that (but there are more columns than this STATUS table): "select a.BIRTH_DATE, a.BIRTH_PLACE, a.BIRTH_XXX, a.BIRTH.YYY from STATUS a;" I randomly encounter with this error while executing sqlite3_prepare_v2 method. When encountered this error, I made a second execution of sqlite3_prepare_v2 with "select * from STATUS a;" statement. Return value was SQLITE_OK. Do you have any idea what is going on? Thank you very much in advance, ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No such column
In a specific case, we have to use such a scenario. I'm aware it is contrary to traditional way, but for this specific "mounted drive" situation, is there a reasonable solution? Any way to avoid this kind of database file corruption? Thanks in advance, Best Regards, On Tue, Dec 24, 2019 at 12:45 PM Darren Duncan wrote: > On 2019-12-22 10:48 p.m., Keith Medcalf wrote: > > On Sunday, 22 December, 2019 23:20, Aydin Ozgur Yagmur < > ayagmu...@gmail.com> wrote: > >> I have experienced a weird problem. I have been using sqlite database in > >> linux by mounting. > > > > Can you give some clues what "using sqlite database in linux by > mounting" means? > > My first thought on reading that was that "mounting" meant using the > ATTACH > command, since in the analogy of the SQL environment as a filesystem, > using > ATTACH is like mounting a volume within the filesystem in order to access > the > volume's contents, which are tables. -- Darren Duncan > ___ > 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] No such column
Thank you very much Simon, it is quite explicative. On Mon, Dec 23, 2019 at 11:38 AM Simon Slavin wrote: > On 23 Dec 2019, at 6:19am, Aydin Ozgur Yagmur wrote: > > > I have been using sqlite database in linux by mounting. > > Nearly all times it works well. But when testing with customer, I > encounter "No such column" error. > > SQLite does not support accessing the database drive across a network. No > network file systems seem to implement file locking correctly, and if file > locking doesn't work correctly, SQLite cannot function correctly. You may > find errors like the one you reported, where commands fail to execute > correctly. You may also find that your database has been corrupted, but > this happens less often. > > It can be very difficult to track down the locking errors which make > SQLite fail. Sometimes a setup will work perfectly, but then occasionally > fail after a reboot. Then another reboot may appear to fix the problem. > We have not found /any/ method of mounting a drive over a network, for any > operating system, which we can recommend. > > If the database you are using has important data, I suggest you run > > PRAGMA integrity_check > > to make sure that the database is not corrupt. If you don't actually need > that database, please delete it and start again with a blank database or a > backup. > ___ > 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] No such column
Hello, I have experienced a weird problem. I have been using sqlite database in linux by mounting. Nearly all times it works well. But when testing with customer, I encounter "No such column" error. After restarting system, it works again well. I wonder why I get such an error? Could you please give me some clues? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple files for a single SQLite database
Oh, I found the answer. SQLITE_MAX_ATTACH should be changed. On Thu, Oct 31, 2019 at 2:40 PM Aydin Ozgur Yagmur wrote: > How can it be raised to 125? > I tried to change with calling "sqlite3_limit(db_, SQLITE_LIMIT_ATTACHED, > 125)" but there is no effect. > I still get "too many attached databases" exception. > > > On Wed, Oct 30, 2019 at 5:01 PM David Raymond > wrote: > >> "There's a limit, somewhere around ten, I think. Though you can increase >> it by explicitly messing with limits and compilation options." >> >> Default is 10, can be raised to 125. >> ___ >> 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] Multiple files for a single SQLite database
How can it be raised to 125? I tried to change with calling "sqlite3_limit(db_, SQLITE_LIMIT_ATTACHED, 125)" but there is no effect. I still get "too many attached databases" exception. On Wed, Oct 30, 2019 at 5:01 PM David Raymond wrote: > "There's a limit, somewhere around ten, I think. Though you can increase > it by explicitly messing with limits and compilation options." > > Default is 10, can be raised to 125. > ___ > 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] Multiple files for a single SQLite database
SQLite stores a single database in a single file. According to the specific project requirements and due to the performance concerns; When working with large databases, is it possible to explicitly tell SQLite not to store the whole DB in a single file and store different tables in different files instead? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LIKE operator and collations
Hello, I want to use custom collations for "like" and "not equals" queries. *select * from tbl_internal where col_internal like 'ç%' collate TURKISH_CI;* it is ok for "equals" operator. *select * from tbl_internal where col_internal = 'çç' collate TURKISH_CI;* but not ok, for "like" and "not equals" operators How can i make a search like these? Could you give me any clues? Thanks for advance, ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Transaction commits hangs up
I have already been upgrading sqlite version. After this If I encounter with the same problem I will inform you.. For Hick Gunter I'm sorry for the inconveinence. I have been using C++. I have been using some kind of wrapper : yes. Operating system (cat /proc/version): Linux version 3.17.0-xilinx-00016-g9d68a4a-dirty I cannot reproduce the problem as I already said I have encountered such an error for the first time. But i'm sure that the wrapper cannot cause such a problem. For Simon Slavin Ok, if i encounter with same problem, I will use command shell, enter pragma command and after that I will execute analyze command. If you have any idea which may cause to this problem please let me know. Thank you very much for your help. Best regards On Tue, Dec 11, 2018 at 4:39 PM Hick Gunter wrote: > Obviously you are using some kind of wrapper and a currently undisclosed > programming language running under some OS you have neglected to name to > call SQLite. > > Without the aid of a debugger, it will not even be possible to discern if > the problem is in SQLite itself or the wrapper code you are using. > > Can you provide a simple schema and a query that exhibits the problem? Can > you reproduce the problem by running the statements from the SQLite shell? > > In 5 posts so far, you have volunteered practically no information at all, > which severely limits the amount of help you can be provided with. > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Aydin Ozgur Yagmur > Gesendet: Dienstag, 11. Dezember 2018 14:20 > An: SQLite mailing list > Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up > > *Ist hat always the same location or is it maybe running in a loop? Is it > always the same statement that "hangs"? * > >>> > I use sqlite for more than 3 years, i encouter such an error for the first > time. That is, no. > *Can you check what the thread is doing with a debugger? * > >>> > I have been logging the situation. > log--> before commit > sqlitetx->commit(); > log--> after commit > i can see "before commit" log but cannot see "after commit" log. > > > > On Tue, Dec 11, 2018 at 3:48 PM Hick Gunter wrote: > > > That answers question 2. What about the thread? Can you check what the > > thread is doing with a debugger? Ist hat always the same location or > > is it maybe running in a loop? Is it always the same statement that > "hangs"? > > > > -Ursprüngliche Nachricht- > > Von: sqlite-users > > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > Im Auftrag von Aydin Ozgur Yagmur > > Gesendet: Dienstag, 11. Dezember 2018 13:43 > > An: SQLite mailing list > > Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up > > > > I am trying to edit the database using command shell. > > for instance i try to execute analyze commad i get "database is locked" > > message > > > > On Tue, Dec 11, 2018 at 1:36 PM Hick Gunter wrote: > > > > > If you have only one thread accessing the database, how do you > > > determine that it is hanging and that the database is locked? > > > > > > -Ursprüngliche Nachricht- > > > Von: sqlite-users > > > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > > Im Auftrag von Aydin Ozgur Yagmur > > > Gesendet: Dienstag, 11. Dezember 2018 11:31 > > > An: SQLite mailing list > > > Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up > > > > > > I set busy-timeout 1000 ms. I have been using threads but only one > > > thread can access to database. > > > I will upgrade sqlite version as soon as possible. Could this lead > > > to that problem? > > > > > > On Tue, Dec 11, 2018 at 12:11 PM Hick Gunter wrote: > > > > > > > You are leaving out a lot of necessary detail. Are you using threads? > > > > Does each thread have it's own connection or are you sharing > > > > connections? Did you set a busy timeout? > > > > > > > > BTW: Your Version of SQLiteis quite old. > > > > > > > > -Ursprüngliche Nachricht- > > > > Von: sqlite-users > > > > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > > > Im Auftrag von Aydin Ozgur Yagmur > > > > Gesendet: Dienstag, 11. Dezember 2018 09:53 > > > > An: SQLite mailing list > > > > Betreff: [EXTERNAL] [sqlite] Transaction commits hangs up >
Re: [sqlite] [EXTERNAL] Transaction commits hangs up
No, I did not use it. On Tue, Dec 11, 2018 at 3:59 PM Simon Slavin wrote: > On 11 Dec 2018, at 12:42pm, Aydin Ozgur Yagmur > wrote: > > > I am trying to edit the database using command shell. > > for instance i try to execute analyze commad i get "database is locked" > > message > > Have you used the timeout PRAGMA inside the command shell ? If not, it's > not retrying. > > Simon. > ___ > 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] Transaction commits hangs up
*Ist hat always the same location or is it maybe running in a loop? Is it always the same statement that "hangs"? * >>> I use sqlite for more than 3 years, i encouter such an error for the first time. That is, no. *Can you check what the thread is doing with a debugger? * >>> I have been logging the situation. log--> before commit sqlitetx->commit(); log--> after commit i can see "before commit" log but cannot see "after commit" log. On Tue, Dec 11, 2018 at 3:48 PM Hick Gunter wrote: > That answers question 2. What about the thread? Can you check what the > thread is doing with a debugger? Ist hat always the same location or is it > maybe running in a loop? Is it always the same statement that "hangs"? > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Aydin Ozgur Yagmur > Gesendet: Dienstag, 11. Dezember 2018 13:43 > An: SQLite mailing list > Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up > > I am trying to edit the database using command shell. > for instance i try to execute analyze commad i get "database is locked" > message > > On Tue, Dec 11, 2018 at 1:36 PM Hick Gunter wrote: > > > If you have only one thread accessing the database, how do you > > determine that it is hanging and that the database is locked? > > > > -Ursprüngliche Nachricht- > > Von: sqlite-users > > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > Im Auftrag von Aydin Ozgur Yagmur > > Gesendet: Dienstag, 11. Dezember 2018 11:31 > > An: SQLite mailing list > > Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up > > > > I set busy-timeout 1000 ms. I have been using threads but only one > > thread can access to database. > > I will upgrade sqlite version as soon as possible. Could this lead to > > that problem? > > > > On Tue, Dec 11, 2018 at 12:11 PM Hick Gunter wrote: > > > > > You are leaving out a lot of necessary detail. Are you using threads? > > > Does each thread have it's own connection or are you sharing > > > connections? Did you set a busy timeout? > > > > > > BTW: Your Version of SQLiteis quite old. > > > > > > -Ursprüngliche Nachricht- > > > Von: sqlite-users > > > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > > Im Auftrag von Aydin Ozgur Yagmur > > > Gesendet: Dienstag, 11. Dezember 2018 09:53 > > > An: SQLite mailing list > > > Betreff: [EXTERNAL] [sqlite] Transaction commits hangs up > > > > > > I have been using sqlite3.8.11 on fat32 file system. Sometimes, > > > while committing a transaction commit process hangs up and database > > > became locked. I am trying to find a cause or a solution for this > problem. > > > Does anyone encounter with similar problem before? Do you have any > > > suggestion or any idea for this problem? > > > > > > Thank you, > > > yagmur > > > ___ > > > 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 > > > > > ___ > > 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 >
Re: [sqlite] [EXTERNAL] Transaction commits hangs up
I am trying to edit the database using command shell. for instance i try to execute analyze commad i get "database is locked" message On Tue, Dec 11, 2018 at 1:36 PM Hick Gunter wrote: > If you have only one thread accessing the database, how do you determine > that it is hanging and that the database is locked? > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Aydin Ozgur Yagmur > Gesendet: Dienstag, 11. Dezember 2018 11:31 > An: SQLite mailing list > Betreff: Re: [sqlite] [EXTERNAL] Transaction commits hangs up > > I set busy-timeout 1000 ms. I have been using threads but only one thread > can access to database. > I will upgrade sqlite version as soon as possible. Could this lead to that > problem? > > On Tue, Dec 11, 2018 at 12:11 PM Hick Gunter wrote: > > > You are leaving out a lot of necessary detail. Are you using threads? > > Does each thread have it's own connection or are you sharing > > connections? Did you set a busy timeout? > > > > BTW: Your Version of SQLiteis quite old. > > > > -Ursprüngliche Nachricht----- > > Von: sqlite-users > > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > Im Auftrag von Aydin Ozgur Yagmur > > Gesendet: Dienstag, 11. Dezember 2018 09:53 > > An: SQLite mailing list > > Betreff: [EXTERNAL] [sqlite] Transaction commits hangs up > > > > I have been using sqlite3.8.11 on fat32 file system. Sometimes, while > > committing a transaction commit process hangs up and database became > > locked. I am trying to find a cause or a solution for this problem. > > Does anyone encounter with similar problem before? Do you have any > > suggestion or any idea for this problem? > > > > Thank you, > > yagmur > > ___ > > 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 > > > ___ > 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Transaction commits hangs up
I set busy-timeout 1000 ms. I have been using threads but only one thread can access to database. I will upgrade sqlite version as soon as possible. Could this lead to that problem? On Tue, Dec 11, 2018 at 12:11 PM Hick Gunter wrote: > You are leaving out a lot of necessary detail. Are you using threads? Does > each thread have it's own connection or are you sharing connections? Did > you set a busy timeout? > > BTW: Your Version of SQLiteis quite old. > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Aydin Ozgur Yagmur > Gesendet: Dienstag, 11. Dezember 2018 09:53 > An: SQLite mailing list > Betreff: [EXTERNAL] [sqlite] Transaction commits hangs up > > I have been using sqlite3.8.11 on fat32 file system. Sometimes, while > committing a transaction commit process hangs up and database became > locked. I am trying to find a cause or a solution for this problem. Does > anyone encounter with similar problem before? Do you have any suggestion or > any idea for this problem? > > Thank you, > yagmur > ___ > 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 > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Transaction commits hangs up
I have been using sqlite3.8.11 on fat32 file system. Sometimes, while committing a transaction commit process hangs up and database became locked. I am trying to find a cause or a solution for this problem. Does anyone encounter with similar problem before? Do you have any suggestion or any idea for this problem? Thank you, yagmur ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite delete slow in 4GB
I was applying the changes. After moving blob to last column suggested by Hick and eduardo, 500 MB delete took 40-45 seconds. I was already attempted to change page_size & cache size but i had not seen any significant improvement, so i didnt apply this now. could you please explain positive effects of "autovacuum=incremental" solution? I did not see too much positive comment on it. Thanks for your help, On Wed, Jun 6, 2018 at 1:10 PM, Eduardo wrote: > On Wed, 6 Jun 2018 12:06:15 +0300 > Aydin Ozgur Yagmur escribió: > > > Thank you very quick response. > > > > We have already planned to change file system. But this problem seem not > > related with the file size, because we did not hit the limit and when i > try > > to delete same data in ,for example, 2 GB-sized db, i encounter with the > > same problem. This is my table and index definitions. > > Why didn't apply the changes suggested by others on the other thread? > > > Thanks for your help. > > > > CREATE TABLE "ANHXT" ( > > "id" integer primary key autoincrement, > > "ANH_AD" text, > > "ANH_DBGMHWID" text, > > "ANH_TYPE" integer, > > "ANH_INDEXNO" int64_t) > > CREATE TABLE "PRCXT" ( > > "id" integer primary key autoincrement, > > "ANP_SEGMENTNO" integer not null, > > "ANP_VALUE" blob, > > "ANH_PRC_id" bigint, > > constraint "fk_ANHPRC_ANH_PRC" foreign key ("ANH_PRC_id") references > > "ANHXT" ("id") on update cascade on delete cascade deferrable > > initially deferred) > > CREATE UNIQUE INDEX UQC_ANH_TYPE on ANHXT( ANH_TYPE)CREATE UNIQUE > > INDEX UQC_ANP_SEGMENTNO_ANAHTARID on PRCXT( > > ANP_SEGMENTNO,ANH_PRC_id)CREATE INDEX findex on PRCXT( ANH_PRC_id) > > > Move the blob to the last column > > CREATE TABLE "PRCXT" ( >"id" integer primary key autoincrement, >"ANP_SEGMENTNO" integer not null, >"ANH_PRC_id" bigint, >constraint "fk_ANHPRC_ANH_PRC" foreign key ("ANH_PRC_id") references > "ANHXT" ("id") on update cascade on delete cascade deferrable > initially deferred), >"ANP_VALUE" blob > > > even better, use a table specific for the blob: > > CREATE TABLE the_blob ( > id integer primary key, > content blob > ) > > the best for it, use 2 databases, one for metadata (ANHXT PRCXT tables) and > other for the blobs. Open the metadata and attach the blob. > > If you use foreign key constraint to point to blob table, don't use wal > mode. > If you want/need wal mode, use an integer column to the blob id table and > manage updates and deletes yourself. > > Set these pragmas for the blob schema before creation: > > pragma the_blob.autovacuum = incremental; > pragma the_blob.page_size = 65536; > pragma the_blob.cache_size = 1024; -- must set on open/attach too. 64MB > > Depending on your insert/update/delete ratios, there are other patterns > that may fit better. > > > On Wed, Jun 6, 2018 at 11:42 AM, Karl Billeter > wrote: > > > > > On Wed, Jun 06, 2018 at 09:27:57AM +0300, Aydin Ozgur Yagmur wrote: > > > > > > > I have been using fat32 file system. I have a database which has 4 GB > > > size. > > > > > > Er... I'm surprised there aren't more problems due to 4GB being the max > > > file > > > size supported by fat32. Any chance to change it to exFAT? > > > > > Don't use FAT32. Use your native fs, ntfs, ufs2, hfs+, hammer2, ext4, > > > > K > > -- > Eduardo > ___ > 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] Sqlite delete slow in 4GB
Thank you very quick response. We have already planned to change file system. But this problem seem not related with the file size, because we did not hit the limit and when i try to delete same data in ,for example, 2 GB-sized db, i encounter with the same problem. This is my table and index definitions. Thanks for your help. CREATE TABLE "ANHXT" ( "id" integer primary key autoincrement, "ANH_AD" text, "ANH_DBGMHWID" text, "ANH_TYPE" integer, "ANH_INDEXNO" int64_t) CREATE TABLE "PRCXT" ( "id" integer primary key autoincrement, "ANP_SEGMENTNO" integer not null, "ANP_VALUE" blob, "ANH_PRC_id" bigint, constraint "fk_ANHPRC_ANH_PRC" foreign key ("ANH_PRC_id") references "ANHXT" ("id") on update cascade on delete cascade deferrable initially deferred) CREATE UNIQUE INDEX UQC_ANH_TYPE on ANHXT( ANH_TYPE)CREATE UNIQUE INDEX UQC_ANP_SEGMENTNO_ANAHTARID on PRCXT( ANP_SEGMENTNO,ANH_PRC_id)CREATE INDEX findex on PRCXT( ANH_PRC_id) On Wed, Jun 6, 2018 at 11:42 AM, Karl Billeter wrote: > On Wed, Jun 06, 2018 at 09:27:57AM +0300, Aydin Ozgur Yagmur wrote: > > > I have been using fat32 file system. I have a database which has 4 GB > size. > > Er... I'm surprised there aren't more problems due to 4GB being the max > file > size supported by fat32. Any chance to change it to exFAT? > > > K > ___ > 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] Sqlite delete slow in 4GB
I have been using fat32 file system. I have a database which has 4 GB size. Database consist of 1 parent table and 1 child table. Parent table has 10 rows and child table has 4000 rows. 1 row of child table has 1 MB size. There are unique indexes on the tables. When I delete a row in parent table, deletion cascades 1MB-sized child records. (pragma foreign_keys is on) When I try to delete 100 MB data by cascade (1 parent record - 100 child records) it takes too long time (almost 1-10 minute) to complete, and the duration increase/decrease by size of data (100 Mb: 1-10 minute, 300 MB: 3-30 minute,etc). I tried some pragma commands (synchronous, temp_store, journal_mode) suggested by others posts and i also tried to add index on foreign key, but those does not help solve my problem.(Actually, after adding index on foreign key, 1 MB data deletion became faster/st, but 100 MB data deletion duration did not change) Can you give me please any suggestion to increase deletion performance? Thanx in advance, ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users