Problem to delete all data with a LIKE

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem to delete all data with a LIKE

Ludovic Gasc Lemaire
Hi,

With this package: https://packages.ubuntu.com/cosmic/sqlite3

In the sqlite file of Asterisk (
https://wiki.asterisk.org/wiki/display/AST/Asterisk+Internal+Database ) you
have this schema:
CREATE TABLE IF NOT EXISTS `astdb` (
`key` VARCHAR ( 256 ),
`value` VARCHAR ( 256 ),
PRIMARY KEY(`key`)
);

You can have hundred lines like that:

INSERT INTO `astdb` (key,value) VALUES
('/SIP/Registry/XXXXXXXX','XXX.XXX.XX.XXX:62467:360:XXXXXXXXXXX@192.168.0.85:2051
');

When I launch this SQL query:
DELETE FROM astdb WHERE key LIKE '%SIP%';

It deletes between 3 and 40 lines, but not all lines.
And each time I launch this SQL query, it removes between 3 and 40 other
lines, but not all lines.

It's a very strange behavior, I have never see that.

It looks like a bug.

Thanks for your help.

Regards.
--
Ludovic Gasc Lemaire (GMLudo)
_______________________________________________
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: Problem to delete all data with a LIKE

Simon Slavin-3
On 7 Feb 2019, at 3:51pm, Ludovic Gasc Lemaire <[hidden email]> wrote:

> When I launch this SQL query:
> DELETE FROM astdb WHERE key LIKE '%SIP%';
>
> It deletes between 3 and 40 lines, but not all lines.
> And each time I launch this SQL query, it removes between 3 and 40 other
> lines, but not all lines.

Please run

    PRAGMA integrity_check

on the database.  If that doesn't spot anythnig wrong, can you prepare a small sample database so we can reproduce the problem ?

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: Problem to delete all data with a LIKE

Ludovic Gasc Lemaire
Hi Simon,

Thanks for your tip, this command has found problems, see below.
It should explain this strange behavior with DELETE ?

I have found this procedure on the Web to fix the file:
https://www.2doapp.com/kb/article.php?id=743&oid=38

Is it the correct way or another way exists ?

Kind regards.

*** in database main ***
On tree page 290 cell 13: Rowid 21980476 out of order
On tree page 290 cell 6: Rowid 21980466 out of order
On tree page 290 cell 5: Rowid 21980467 out of order
On tree page 290 cell 4: Rowid 21980468 out of order
On tree page 290 cell 3: Rowid 21980468 out of order
On tree page 290 cell 2: Rowid 21980468 out of order
On tree page 290 cell 1: Rowid 21980469 out of order
On tree page 290 cell 0: Rowid 21980474 out of order
On tree page 289 cell 15: Rowid 21980442 out of order
On tree page 289 cell 14: Rowid 21980445 out of order
On tree page 289 cell 13: Rowid 21980448 out of order
On tree page 289 cell 12: Rowid 21980450 out of order
On tree page 289 cell 11: Rowid 21980450 out of order
On tree page 289 cell 10: Rowid 21980450 out of order
On tree page 289 cell 7: Rowid 21980448 out of order
On tree page 289 cell 6: Rowid 21980449 out of order
On tree page 288 cell 23: Rowid 21980426 out of order
On tree page 288 cell 22: Rowid 21980426 out of order
On tree page 288 cell 21: Rowid 21980426 out of order
On tree page 288 cell 20: Rowid 21980429 out of order
On tree page 288 cell 19: Rowid 21980429 out of order
On tree page 288 cell 18: Rowid 21980429 out of order
On tree page 288 cell 17: Rowid 21980429 out of order
On tree page 288 cell 16: Rowid 21980429 out of order
On tree page 288 cell 15: Rowid 21980432 out of order
On tree page 288 cell 14: Rowid 21980434 out of order
On tree page 288 cell 7: Rowid 21980415 out of order
On tree page 288 cell 6: Rowid 21980418 out of order
On tree page 287 cell 20: Rowid 21980410 out of order
On tree page 287 cell 7: Rowid 21980401 out of order
On tree page 287 cell 6: Rowid 21980402 out of order
On tree page 287 cell 5: Rowid 21980408 out of order
On tree page 286 cell 28: Rowid 21980386 out of order
On tree page 286 cell 27: Rowid 21980386 out of order
On tree page 286 cell 26: Rowid 21980388 out of order
On tree page 286 cell 25: Rowid 21980388 out of order
On tree page 286 cell 24: Rowid 21980393 out of order
On tree page 286 cell 23: Rowid 21980394 out of order
On tree page 286 cell 22: Rowid 21980396 out of order
On tree page 286 cell 21: Rowid 21980396 out of order
On tree page 286 cell 20: Rowid 21980400 out of order
On tree page 286 cell 19: Rowid 21980410 out of order
On tree page 286 cell 16: Rowid 21980408 out of order
On tree page 286 cell 15: Rowid 21980410 out of order
On tree page 286 cell 14: Rowid 21980410 out of order
On tree page 286 cell 13: Rowid 21980410 out of order
On tree page 286 cell 12: Rowid 21980410 out of order
On tree page 286 cell 11: Rowid 21980410 out of order
On tree page 286 cell 10: Rowid 21980410 out of order
On tree page 286 cell 9: Rowid 21980410 out of order
On tree page 286 cell 8: Rowid 21980410 out of order
On tree page 286 cell 7: Rowid 21980410 out of order
On tree page 286 cell 6: Rowid 21980411 out of order
On tree page 286 cell 5: Rowid 21980411 out of order
On tree page 286 cell 4: Rowid 21980411 out of order
On tree page 286 cell 3: Rowid 21980411 out of order
On tree page 286 cell 2: Rowid 21980411 out of order
On tree page 286 cell 1: Rowid 21980411 out of order
On tree page 286 cell 0: Rowid 21980411 out of order
On tree page 2 cell 281: Rowid 21980411 out of order
On tree page 285 cell 28: Rowid 21980411 out of order
On tree page 285 cell 27: Rowid 21980411 out of order
On tree page 285 cell 26: Rowid 21980411 out of order
On tree page 285 cell 25: Rowid 21980416 out of order
On tree page 285 cell 23: Rowid 21980411 out of order
On tree page 285 cell 22: Rowid 21980411 out of order
On tree page 285 cell 21: Rowid 21980411 out of order
On tree page 285 cell 20: Rowid 21980411 out of order
On tree page 285 cell 19: Rowid 21980411 out of order
On tree page 285 cell 18: Rowid 21980411 out of order
On tree page 285 cell 17: Rowid 21980412 out of order
On tree page 285 cell 16: Rowid 21980413 out of order
On tree page 285 cell 15: Rowid 21980414 out of order
On tree page 285 cell 14: Rowid 21980415 out of order
On tree page 285 cell 13: Rowid 21980415 out of order
On tree page 285 cell 12: Rowid 21980416 out of order
On tree page 285 cell 11: Rowid 21980416 out of order
On tree page 285 cell 10: Rowid 21980416 out of order
On tree page 285 cell 9: Rowid 21980416 out of order
On tree page 285 cell 8: Rowid 21980417 out of order
On tree page 285 cell 7: Rowid 21980418 out of order
On tree page 285 cell 6: Rowid 21980425 out of order
On tree page 285 cell 4: Rowid 21980418 out of order
On tree page 285 cell 3: Rowid 21980418 out of order
On tree page 285 cell 2: Rowid 21980418 out of order
On tree page 285 cell 1: Rowid 21980418 out of order
On tree page 285 cell 0: Rowid 21980418 out of order
On tree page 2 cell 280: Rowid 21980418 out of order
On tree page 284 cell 27: Rowid 21980418 out of order
On tree page 284 cell 26: Rowid 21980419 out of order
On tree page 284 cell 25: Rowid 21980419 out of order
On tree page 284 cell 24: Rowid 21980419 out of order
On tree page 284 cell 23: Rowid 21980419 out of order
On tree page 284 cell 22: Rowid 21980419 out of order
On tree page 284 cell 21: Rowid 21980419 out of order
On tree page 284 cell 20: Rowid 21980419 out of order
On tree page 284 cell 19: Rowid 21980419 out of order
On tree page 284 cell 18: Rowid 21980419 out of order
On tree page 284 cell 17: Rowid 21980419 out of order
On tree page 284 cell 16: Rowid 21980419 out of order

--
Ludovic Gasc Lemaire (GMLudo)


Le jeu. 7 févr. 2019 à 17:42, Simon Slavin <[hidden email]> a écrit :

> On 7 Feb 2019, at 3:51pm, Ludovic Gasc Lemaire <[hidden email]> wrote:
>
> > When I launch this SQL query:
> > DELETE FROM astdb WHERE key LIKE '%SIP%';
> >
> > It deletes between 3 and 40 lines, but not all lines.
> > And each time I launch this SQL query, it removes between 3 and 40 other
> > lines, but not all lines.
>
> Please run
>
>     PRAGMA integrity_check
>
> on the database.  If that doesn't spot anythnig wrong, can you prepare a
> small sample database so we can reproduce the problem ?
>
> Simon.
> _______________________________________________
> 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: Problem to delete all data with a LIKE

Simon Slavin-3
On 8 Feb 2019, at 1:49pm, Ludovic Gasc Lemaire <[hidden email]> wrote:

> Thanks for your tip, this command has found problems, see below.
> It should explain this strange behavior with DELETE ?

Yes.  The corruption you have found in your database can explain lots of weird and non-standard behaviour.  In fact, even if you can get commands to work now, you may lose data later.  Do not proceed with this database.

You may be able to rescue some or all of the data from this database.  To do this use the SQLite CLI tool to dump the database as SQL commands, then create a new database file by executing those commands.  If you're not familiar with '.dump' and '.read', you can find more details here:

<https://sqlite.org/cli.html#converting_an_entire_database_to_an_ascii_text_file>



Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users