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

juwo
Member
Registered: 2014-09-05
Posts: 21

Strange index sqlite database corruption

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;

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Strange index sqlite database corruption

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

Offline

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

juwo
Member
Registered: 2014-09-05
Posts: 21

Re: Strange index sqlite database corruption

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Strange index sqlite database corruption

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.

Offline

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

juwo
Member
Registered: 2014-09-05
Posts: 21

Re: Strange index sqlite database corruption

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 ''

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Strange index sqlite database corruption

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.

Offline

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

juwo
Member
Registered: 2014-09-05
Posts: 21

Re: Strange index sqlite database corruption

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)

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Strange index sqlite database corruption

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

Offline

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

juwo
Member
Registered: 2014-09-05
Posts: 21

Re: Strange index sqlite database corruption

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Strange index sqlite database corruption

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.

Offline

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

juwo
Member
Registered: 2014-09-05
Posts: 21

Re: Strange index sqlite database corruption

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)

Offline

Board footer

Powered by FluxBB