Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

36 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB) >>The setting for synchronous is basically what level of safety net do you want if it dies in the middle of something. Setting it to off shouldn't

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

>>The setting for synchronous is basically what level of safety net do you want if it dies in the middle of something. Setting it to off shouldn't cause any corruption if things go well, it should only come into play if you saw errors or didn't close things down correctly etc. You're right, my

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

On Sun, 15 Oct 2017 18:36:56 -0700 (MST), Fiona wrote: > Thanks for noticing that problem! Follow your instructions, now I'm sure > it's all because my db file is corrupted. Is there anything I can do to fix > it? > > Integrity check result: >

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

On 10/16/17, David Raymond wrote: > The setting for synchronous is basically what level of safety net do you > want if it dies in the middle of something. Setting it to off shouldn't > cause any corruption if things go well, it should only come into play if you > saw

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

Of Fiona Sent: Sunday, October 15, 2017 9:15 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB) Thanks a lot! That may be the problem: my db file is corrupted. Below is the *PRAGMA integrity_check* res

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

On 16 Oct 2017, at 3:33am, Kees Nuyt wrote: > In SQLite, columns are stored in database pages in the order > they are defined. [snip] > > So, it is best practice put keys and all columns with small > contents up front, and all big TEXT and BLOB columns at the end > of the

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

On Sun, 15 Oct 2017 17:45:03 -0700 (MST), Fiona wrote: >>> Swapping the columns tile_data and tile_id may improve performance > significantly, especially if the BLOB can get bigger than a database page. > > Thanks for your advice, could you please explain more why is that?

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

On 16 Oct 2017, at 2:14am, Fiona wrote: > Thanks a lot! That may be the problem: my db file is corrupted. Below is the > *PRAGMA integrity_check* result. It didn't return OK. > > > I think it's because

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

Thanks a lot! That may be the problem: my db file is corrupted. Below is the *PRAGMA integrity_check* result. It didn't return OK. I think it's because I set PRAGMA synchronous=off in Python code to enhance insert speed. Does

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

>>Interesting, because you cannot have two rows (two tile_id) for the same combination of tile_zoom / tile_row / tile_column since the latter are a required to be unique primary keys. Thanks for noticing that problem! Follow your instructions, now I'm sure it's all because my db file is

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

>> Swapping the columns tile_data and tile_id may improve performance significantly, especially if the BLOB can get bigger than a database page. Thanks for your advice, could you please explain more why is that? The primary key is not change at all, what exectly causes the improvement? --

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

>> SELECT typeof(tile_id), tile_id FROM map WHERE zoom_level=18 AND tile_column=214233 AND tile_row=147702; tile_id is text type -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

er, 2017 20:33 >To: sqlite-users@mailinglists.sqlite.org >Subject: Re: [sqlite] Sqlite3.6 Command-line delete/update not >working with large db file(>280GB) > >>> SQLite does none of those. Have your program print out the actual >command >it’s trying to execute.

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

On 13 Oct 2017, at 3:32am, Fiona wrote: > delete/insert operations: > The two screenshots are useful. Your two commands do not have the same WHERE clause. I agree that it looks like they should have the same

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

On Thu, 12 Oct 2017 19:32:53 -0700 (MST), Fiona wrote: > schema: > Not related to your problem, just a hint: Swapping the columns tile_data and tile_id may improve performance significantly, especially if the BLOB

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

On 10/12/17, Fiona wrote: > > As you can see, insert works, and I can also delete/update this last > inserted record. It seems some pages of my db file is locked or something. Before running your query, enter ".mode quote". Then show us what the output of this query is:

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

>> SQLite does none of those. Have your program print out the actual command it’s trying to execute. Then try typing it manually. See if it works when you type it by hand. Yes, I manually type all the command, also I check all the records I wanna delete by SELECT with the same where clause.

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

Sorry about my ambiguous description. Here is what I wanna do. Normally I use Python code to insert/update data of two tables in my sqlite database file: *map and images*, table *map* stores the indexs of pics, while table *images* stores the contents of these pics. My Python code works well,

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

to:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Fiona Sent: Fri 13 October 2017 02:19 To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB) CAUTION - EXTERNAL EMAIL >> This suggests INSER

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

On 13 Oct 2017, at 2:19am, Fiona wrote: > Thanks a lot! Yes, that's the situation. I'm using WHERE clause in my > UPDATE/DELETE sentences, and I'm pretty sure the syntax and my shell are not > the problem, because there has no retrun of error, and I also test the same >

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

>> This suggests INSERT works but UPDATE and DELETE does not. Is this >> correct ? Thanks a lot! Yes, that's the situation. I'm using WHERE clause in my UPDATE/DELETE sentences, and I'm pretty sure the syntax and my shell are not the problem, because there has no retrun of error, and I also

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

users-boun...@mailinglists.sqlite.org] On Behalf Of Fiona Sent: Thursday, October 12, 2017 5:41 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB) Here is the specifics of my problem: ubuntu, sqlite 3.6.20 I have only two

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

On 12 Oct 2017, at 10:41am, Fiona wrote: > Now I have a db file of about 289GB in size, when I updata/delete with > command-line, the data is not changed/deleted at all, and no error ever > returned, while insert is still working. You say that the data is not changed,

[sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

Here is the specifics of my problem: ubuntu, sqlite 3.6.20 I have only two tables, each with primary key and index, I use python code to insert/update these two data, one table have a column with large blob data. Now I have a db file of about 289GB in size, when I updata/delete with