Strange index sqlite database corruption

juwo · 2022-12-07 09:59:43

Hi,
I have users table with emails as a logonname. I found very strange thing. If i add 2 users with logonname like below then i have:
row 1 missing from index IndexAuthUserLogonName
row 1 missing from index sqlite_autoindex_AuthUser_1
non-unique entry in index sqlite_autoindex_AuthUser_1
in my database. But if i remove "_" and add this 2 users all is fine. If i change 'aa_g@ot.eu' to 'a_ag@ot.eu' also work ok.
Index problems have impact to users login. Some cannot login to database. I spent 3 days trying to isolate problem and at this moment don't have idea what to do next. I will be gratefull for any idea.
(tested on last mormot 1)

   Model_srv := CreateModel_srv('');
   Db_srv2 := TSQLRestClientDB.Create(Model_srv,nil, ed_wynik.Text, TSQLRestServerDB, false, '');
   a := TAuthUser.Create;
   a.LogonName := 'a_d@ap.com';
   Db_srv2.Add(a,True);
   a.Free;
   a := TAuthUser.Create;
   a.LogonName := 'aa_g@ot.eu';
   Db_srv2.Add(a,True);
   a.Free;
   db_srv2.Free;
   Model_srv.Free;

mpv · 2022-12-07 11:33:16

Do you mean data exists in SQLite table but you can't select it using SQL like `select * from user_table where logonName = 'a_d@ap.com'` ?
Are you sure equal (=) condition is used while selecting? Because for `like` condition underscore `_` wildcard matches any single character

juwo · 2022-12-07 12:22:33

Thanks for respond. This is empty database. I only add two records and this causes (from integrity check):

row 1 missing from index IndexAuthUserLogonName
row 1 missing from index sqlite_autoindex_AuthUser_1
non-unique entry in index sqlite_autoindex_AuthUser_1

On production database with this index problem user cannot login (random user). After vacuum indexes are ok and user can login. But after few houres problem return.

ab · 2022-12-07 13:38:41

There is a missing

Db_srv2.Server.CreateMissingTables;

in your code anyway.

First ensure you delete the DB file before your test in the code.
If you can run your test code without the CreateMissingTables, it is very likely that you are working on an existing DB.

I am not able to reproduce your issue, using TAuthUser as defined in mORMOt 2.

How is TAuthUser defined?

Please enable the logs and look at the SQL requests generated, and the eventual error.
You can post the log somewhere on a gist for us to investigate.

juwo · 2022-12-07 14:56:04

Thanks ab,
Here is a gist:
https://gist.github.com/juwo123/f48e275 … 1341c1a5bb
Here is a simple project: https://www.dropbox.com/s/j6c0kwhe1ar1p … m.zip?dl=0 - only 2 standard TSQLAuthGroup, TsqlAuthUser
I run with CreateMissingTables.
I noticed one more thing - index problem appears only when root in TSQLModel.Create([TSQLAuthGroup, TsqlAuthUser],'sss') is different than ''

ab · 2022-12-07 15:19:42

The root should never be '' anyway.

But I don't see any error in the log file of the gist.
And I have no problem running your dropbox project.

Ensure you downloaded the latest static files for mORMot 1 - current version of SQLite3 is 3.40.0, and from the logs, you have an older revision.
And you are using 1.18.6348 which is also old - more than one year old.
I suspect there is something wrong with your local mORMot setup.

juwo · 2022-12-07 15:26:02

ab wrote:

The root should never be '' anyway.

Sorry for that - of course - i'am trying many ways and made mistake.

But I don't see any error in the log file of the gist.
And I have no problem running your dropbox project.

If you check database file data.db with integrity check there is no problems?

Ensure you downloaded the latest static files for mORMot 1 - current version of SQLite3 is 3.40.0, and from the logs, you have an older revision.
I doubt it is the root cause, but I suspect there is something wrong with your local mORMot setup.

I tested last mormot version with same results - but try it again.

Last edited by juwo (2022-12-07 15:26:51)

ab · 2022-12-07 15:38:23

What do you call "integrity check"?
With an external tool?

If I run

pragma integrity_check(authuser)

with SynDBExplorer, I don't have any problem reported.

If you use the default ORM collation, my guess is that an external tool is not able to use it, because it does not know our SYSTEMNOCASE collation.
If you want to work with external tools, you need to use the standard NOCASE collation, i.e. inherit from TSQLRecordCaseSensitive, or call

TSQLModel.SetCustomCollationForAll(sftUTF8Text,'NOCASE');

This is clearly documented as such
https://synopse.info/files/html/Synopse … #TITLE_155

juwo · 2022-12-07 15:55:42

You are right, i use third party tool to check integrity.
So i tought i found something wrong, but that was simply my mistake, and my problems comes from other side. Sorry for bothered you, and other members.
I return to investigation.

Best Regards

ab · 2022-12-07 16:23:08

I have updated the documentation to make this even more explicit.
https://synopse.info/files/html/Synopse … l#TITL_205

Especially, now the FAQ speaks about such collation issues.

juwo · 2022-12-07 17:24:11

Thank you very much,

May i ask yet what is the simplest way to copy all database to new one. With the preserve of IDs? I found one update query with error in my db: (ErrorCode":11,"SQLite3ErrorCode":"secCORRUPT","Message":"Error SQLITE_CORRUPT (11) [Step] using 3.40.0 - database disk image is malformed, extended_errcode=779)
And now i thinking that copy all data to new database will be the best option.

SOLVED: forceID is your friend.

Last edited by juwo (2022-12-07 18:16:30)

mORMot Open Source

#1 2022-12-07 09:59:43

Strange index sqlite database corruption

#2 2022-12-07 11:33:16

Re: Strange index sqlite database corruption

#3 2022-12-07 12:22:33

Re: Strange index sqlite database corruption

#4 2022-12-07 13:38:41

Re: Strange index sqlite database corruption

#5 2022-12-07 14:56:04

Re: Strange index sqlite database corruption

#6 2022-12-07 15:19:42

Re: Strange index sqlite database corruption

#7 2022-12-07 15:26:02

Re: Strange index sqlite database corruption

#8 2022-12-07 15:38:23

Re: Strange index sqlite database corruption

#9 2022-12-07 15:55:42

Re: Strange index sqlite database corruption

#10 2022-12-07 16:23:08

Re: Strange index sqlite database corruption

#11 2022-12-07 17:24:11

Re: Strange index sqlite database corruption

Board footer