[sqlite] No such column error

2020-03-23 Thread Aydin Ozgur Yagmur 
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

2019-12-24 Thread Aydin Ozgur Yagmur 
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

2019-12-23 Thread Aydin Ozgur Yagmur 
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

2019-12-22 Thread Aydin Ozgur Yagmur 
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

2019-10-31 Thread Aydin Ozgur Yagmur 
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

2019-10-31 Thread Aydin Ozgur Yagmur 
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

2019-10-30 Thread Aydin Ozgur Yagmur 
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

2019-02-15 Thread Aydin Ozgur Yagmur 
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

2018-12-11 Thread Aydin Ozgur Yagmur 
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

2018-12-11 Thread Aydin Ozgur Yagmur 
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

2018-12-11 Thread Aydin Ozgur Yagmur 
*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

2018-12-11 Thread Aydin Ozgur Yagmur 
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

2018-12-11 Thread Aydin Ozgur Yagmur 
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

2018-12-11 Thread Aydin Ozgur Yagmur 
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

2018-06-06 Thread Aydin Ozgur Yagmur 
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

2018-06-06 Thread Aydin Ozgur Yagmur 
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

2018-06-06 Thread Aydin Ozgur Yagmur 
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