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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|

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

Fiona
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
command-line,  the data is not changed/deleted at all, and no error ever
returned, while insert is still working.

I look through the sqlite limits, it says practically there is no limit
about the size of a db file given that you have enough disk space. So please
help me, where I can look into to solve this?

Thanks a lot!



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3


On 12 Oct 2017, at 10:41am, Fiona <[hidden email]> 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, but the insert is working.  This suggests INSERT works but UPDATE and DELETE does not.  Is this correct ?

Are you sure you have the right syntax for the UPDATE and DELETE commands ?  Do they have a WHERE clause ?  Have you escaped the quote signs around your values correctly ?

Try issuing the same commands using the SQLite shell tool interactively.  If it works, the problem is with your shell.  You have be having trouble figuring out how to escape quote characters.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

David Raymond
In reply to this post by Fiona
Could you provide a little more clarification on what you're doing?

Are you trying to use the CLI <while> the Python script is doing inserts?

When you try to do an update or delete with the CLI does it hang and not complete, or does it happily continue on and let you keep going?

If you do ".changes on" before running the query, does the reported change count increase?

If there's no sensitive data in the schema could you share the schema and/or copy the screen text from the CLI with an example?


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Fiona
Sent: Thursday, October 12, 2017 5:41 AM
To: [hidden email]
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 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
command-line,  the data is not changed/deleted at all, and no error ever
returned, while insert is still working.

I look through the sqlite limits, it says practically there is no limit
about the size of a db file given that you have enough disk space. So please
help me, where I can look into to solve this?

Thanks a lot!



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Fiona
In reply to this post by Simon Slavin-3

>> 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 test the same
UPDATE/DELETE sentences with the same shell tool but in a small db file, it
works correctly.

I assume the problem is caused by some kind of *db file consistency check* I
don't know? Or sqlite is doing some *index rebuilding* for my UPDATE/DELETE
operation?  What I really can't understand is why INSERT still works in the
mean time.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
On 13 Oct 2017, at 2:19am, Fiona <[hidden email]> 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
> UPDATE/DELETE sentences with the same shell tool but in a small db file, it
> works correctly.

Here is an example of a command which returns no error but does not do what you want:

DELETE FROM MyTable WHERE myColumn = '"2"'

A DELETE or UPDATE command which affects no rows is a successful command and returns SQLITE_OK.

> I assume the problem is caused by some kind of *db file consistency check* I
> don't know? Or sqlite is doing some *index rebuilding* for my UPDATE/DELETE
> operation?  What I really can't understand is why INSERT still works in the
> mean time.

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.

Your problem is probably to do with quote characters " ' “ ' `.  The quote characters you expect to be passed to SQLite may be interpreted by your shell instead.  Or maybe the other way around.

If you can post the command you’re trying to execute we might be able to help further.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Fiona
In reply to this post by David Raymond
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, untill the db file grows too big: I find some
pics I insert into the db file is incorrect,  then I rerun my Python code to
update these pics with right ones, but the select result of table images is
still not right. So i use command-line tool to test it, and there goes my
problem, I find the update/delete operation not working or giving any error
message, while I can still insert data into the same file.

>> Are you trying to use the CLI <while> the Python script is doing inserts?
Of course I use the CLI after my Python script finishes all inserts,
otherwise the db file is locked and all my operations in CLI would retrun
error.

>> If you do ".changes on" before running the query, does the reported
>> change count increase?
I can't find this command in my CLI, and I use count_changes,  here is the
schema and my commands. I think my delete operation returns correctly, but
after delete, the select result confuses me.

Schem:
<http://sqlite.1065341.n5.nabble.com/file/t8403/schema.jpg>

delete:
<http://sqlite.1065341.n5.nabble.com/file/t8403/delete.jpg>

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.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Fiona
In reply to this post by Simon Slavin-3
>> 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. Here are the scripts.

schema:
<http://sqlite.1065341.n5.nabble.com/file/t8403/schema.jpg>

delete/insert operations:
<http://sqlite.1065341.n5.nabble.com/file/t8403/delete.jpg>



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Andy Ling-2
In reply to this post by Fiona
Try changing the "DELETE FROM table WHERE ...." to "SELECT COUNT(1) FROM table WHERE ...." and see if you get a number bigger than 0. If not, then your WHERE isn't matching the rows you think it should.

Regards

Andy Ling


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Fiona
Sent: Fri 13 October 2017 02:19
To: [hidden email]
Subject: Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

CAUTION - EXTERNAL EMAIL

>> 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 test the same
UPDATE/DELETE sentences with the same shell tool but in a small db file, it
works correctly.

I assume the problem is caused by some kind of *db file consistency check* I
don't know? Or sqlite is doing some *index rebuilding* for my UPDATE/DELETE
operation?  What I really can't understand is why INSERT still works in the
mean time.




--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
---------------------------------------------------------------------------------------
This email has been scanned for email related threats and delivered safely by Mimecast.
For more information please visit http://www.mimecast.com
---------------------------------------------------------------------------------------

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Hipp-3
In reply to this post by Fiona
On 10/12/17, Fiona <[hidden email]> 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:

SELECT typeof(tile_id), tile_id FROM map WHERE zoom_level=18 AND
tile_column=214233 AND tile_row=147702;



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Kees Nuyt
In reply to this post by Fiona
On Thu, 12 Oct 2017 19:32:53 -0700 (MST), Fiona
<[hidden email]> wrote:

> schema:
> <http://sqlite.1065341.n5.nabble.com/file/t8403/schema.jpg>

Not related to your problem, just a hint:
Swapping the columns tile_data and tile_id may improve
performance significantly, especially if the BLOB can get bigger
than a database page.

Original:
CREATE TABLE images (
        tile_data BLOB
, tile_id VARCHAR(256) NOT NULL
, PRIMARY KEY (tile_id)); -- might be slow

Improved:
CREATE TABLE images (
        tile_id VARCHAR(256) NOT NULL
, tile_data BLOB
, PRIMARY KEY (tile_id)); -- could be faster


--
Regards,
Kees Nuyt

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by Fiona


On 13 Oct 2017, at 3:32am, Fiona <[hidden email]> wrote:

> delete/insert operations:
> <http://sqlite.1065341.n5.nabble.com/file/t8403/delete.jpg>

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 result, but obviously this is not working.

Please take a backup copy of your database.  Then, using the SQLite shell tool, starting with the same PRAMGA settings, I would like to see the results from these lines

.mode quote
PRAGMA count_changes;
PRAGMA integrity_check;
SELECT COUNT(*) FROM map WHERE tile_id='a37e1dba ….';
SELECT * FROM map WHERE zoom_level=18 AND tile_column=214233 AND tile_row=147702 AND tile_id='a37e1dba <whatever it is>';
DELETE FROM map WHERE zoom_level=18 AND tile_column=214233 AND tile_row=147702 AND tile_id='a37e1dba <whatever it is>';
SELECT * FROM map WHERE zoom_level=18 AND tile_column=214233 AND tile_row=147702;
DELETE FROM map WHERE zoom_level=18 AND tile_column=214233 AND tile_row=147702;

Use the full value for tile_id where indicated.  I may also have made a mistake typing the other values above.  Please check they match yours.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Keith Medcalf
In reply to this post by Fiona

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.  That is, the map table is declared as if it were the dictionary:

map[tile_zoom, tile_row, tile_column] = tile_id

Unless your keys are not of the correct type somewhere, it is impossible to have the same dictionay selectors have two results.  You need to check the typeof() each of your keys aka:

select *, typeof(tile_zoom), typeof(tile_row), typeof(tile_column), typeof(tile_id), '"' || tile_id || '"' from map where ....;

is returning duplicate rows.  You must have one of them with an incorrect (different) type.  Also, your tile_id could have "trailing spaces" that you cannot see thus counfounding your delete request (which will be shown by the above select).

so your delete statement is effectively:

if map[tile_zoom, tile_row, tile_column] == tile_id:
        del map[tile_zoom, tile_row, tile_column]

Have you run an integrity_check on the database to make sure it is not corrupted (because it certainly appears that it is).  Either that or you have simply inserted some bad data.

---
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-
>[hidden email]] On Behalf Of Fiona
>Sent: Thursday, 12 October, 2017 20:33
>To: [hidden email]
>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.  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. Here are the scripts.
>
>schema:
><http://sqlite.1065341.n5.nabble.com/file/t8403/schema.jpg>
>
>delete/insert operations:
><http://sqlite.1065341.n5.nabble.com/file/t8403/delete.jpg>
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Fiona
In reply to this post by Richard Hipp-3
>> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Fiona
In reply to this post by Kees Nuyt
>> 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?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Fiona
In reply to this post by Simon Slavin-3
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.
<http://sqlite.1065341.n5.nabble.com/file/t8403/integrity_check.jpg>

I think it's because I set PRAGMA synchronous=off in Python code to enhance
insert speed.
Does that mean this db file can not be used anymore? Or is there any way I
can fix it?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Fiona
In reply to this post by Keith Medcalf
>>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 corrupted.  Is there anything I can do to fix
it?

Integrity check result:
<http://sqlite.1065341.n5.nabble.com/file/t8403/integrity_check.jpg>




--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by Fiona


On 16 Oct 2017, at 2:14am, Fiona <[hidden email]> 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.
> <http://sqlite.1065341.n5.nabble.com/file/t8403/integrity_check.jpg>
>
> I think it's because I set PRAGMA synchronous=off in Python code to enhance
> insert speed.
> Does that mean this db file can not be used anymore?

Yes.  Anything done with this database may give the wrong result.  Any changes you make to it may overwrite data you want preserved.  Do not use it any more.  This is our best method of rescuing as much data as possible:

1) Open the database in the SQLite shell tool.
2) Use the ".dump" command to dump the data into a file of SQL commands:

        .dump sql.txt

3) Quit the SQLite shell tool with the ".quit" command.
4) Using a text editor open the text file and make sure it looks like it has the right sort of commands in.
5) Use the SQLite shell tool to create a new database file:

        sqlite newfile.sqlite

6) Use the ".read" command to read and execute the commands from the text file:

        .read sql.txt

7) Quit the SQLite shell tool with the ".quit" command.

You now have a new database which is guaranteed uncorrupt as far as SQLite is concerned (the data in it may not be all the data you had originally, some of that may have been lost).  You can rename your old and new database files and see whether things are working correctly now.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Kees Nuyt
In reply to this post by Fiona
On Sun, 15 Oct 2017 17:45:03 -0700 (MST), Fiona
<[hidden email]> 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?
> The primary key is not change at all, what exectly causes the improvement?

In SQLite, columns are stored in database pages in the order
they are defined. The primary key of any table is usually
accessed more often than any other column, becuase it is either
used by a WHERE clause or the ON clause of a JOIN.

If a BLOB is bigger than a page, SDQLite has to read one or more
so called overflow paged to get to the primary key.

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 column list, in ascending order of expected size.

HTH

--
Regards,
Kees Nuyt
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3


On 16 Oct 2017, at 3:33am, Kees Nuyt <[hidden email]> 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 column list, in ascending order of expected size.

Kees’ clear answer missed an important point.  When retrieving data from a row, SQLite does not bother to read past the last column it needs.  So if you have

CREATE TABLE MyTable (c1 INTEGER, c2 TEXT, c3 INTEGER, c4 TEXT, c5 BLOB)

and do this

SELECT c1, c4 FROM MyTale WHERE c2 = "HELLO"

then SQLite reads from c1 to c4, because it needs c4, but it stops at c4 for every row.  It never has to read any BLOB data at all.  This can save a lot of time, especially if your BLOBs are long and may cross a page boundary.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

David Raymond
In reply to this post by Fiona
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.

The unique index you declared is redundant by the way, declaring those three fields as the primary key makes a unique index already to keep track of that. Did you intend to make that on the retry table?


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Fiona
Sent: Sunday, October 15, 2017 9:15 PM
To: [hidden email]
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* result. It didn't return OK.
<http://sqlite.1065341.n5.nabble.com/file/t8403/integrity_check.jpg>

I think it's because I set PRAGMA synchronous=off in Python code to enhance
insert speed.
Does that mean this db file can not be used anymore? Or is there any way I
can fix it?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12