Hi, Roman,
On Tue, May 19, 2015 at 1:23 PM, Roman Fleysher
<roman.fleysher at einstein.yu.edu> wrote:
> "Confirmation" of a bug:
>
> When I use old SQLite version 3.7.2, the offending INSERT does NOT lead to
> integrity failure. This is indicative of either bug in old integrity check or
> new insert. Please let me know what useful info/contribution I can make.
>
> Another addition. The output of PRAGMA integrity_check is 31 lines (31 rows
> are being inserted), all identical with first and last reading:
>
> row 1 missing from index sqlite_autoindex_Exam_1
> ...
> row 31 missing from index sqlite_autoindex_Exam_1
>
> In addition, I have a trigger that updates Exam table when subject table is
> updated (not on insert). I presume that trigger should not be triggered. Not
> knowing what sqlite_autoindex_Exam_1 means, and seeing "Exam" I conclude the
> relationship between my subject() and Exam() tables might be relevant for the
> problem.
Can you post a complete schema of you database? What tables are made?
What indexes? Some test data to use?
Thank you.
>
> Roman
>
>
> ________________________________________
> From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces
> at mailinglists.sqlite.org] on behalf of Roman Fleysher [roman.fleysher at
> einstein.yu.edu]
> Sent: Tuesday, May 19, 2015 12:25 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] index broken by insert
>
> Dear SQLiters,
>
> I do not really know what info to provide for sufficient information. I use
> SQLite shell only for all create/insert manipulations. This insert below
> causes PRAGMA integrity_check; to report missing index (what appears to be on
> every inserted row):
>
> SQLite version 3.8.8.3 2015-02-25 13:29:11
>
> PRAGMA foreign_keys=ON;
> ATTACH DATABASE 'demographics.sqlite' AS demo;
> INSERT OR IGNORE INTO subject(subjectID, dob, gender)
> SELECT subjectID, dob, gender FROM demo.demographics;
>
>
> The subject table is defined as:
>
> CREATE TABLE gender(
> gender TEXT PRIMARY KEY NOT NULL,
> description TEXT
> );
>
> INSERT OR IGNORE INTO gender (gender, description) VALUES ('Male', 'male
> gender');
> INSERT OR IGNORE INTO gender (gender, description) VALUES ('Female', 'female
> gender');
>
> -- force dob yyyy-mm-dd or NULL
> CREATE TABLE subject(
> subjectID INT PRIMARY KEY NOT NULL,
> dob TEXT CHECK (dob IS date(dob)),
> gender TEXT REFERENCES gender(gender) ON UPDATE CASCADE,
> race TEXT,
> handedness TEXT REFERENCES handedness(handedness) ON UPDATE CASCADE
> );
>
>
> Please let me know what other info might be useful to debug, including
> debugging on my end! Integrity check on demographics.sqlite is OK.
>
> Thank you for your help,
>
>
> Roman
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
