On 07/31/2015 08:34 PM, sqlite-mail wrote: > Hello ! > > I'm using sqlite for a project and with this specific database > https://dev.dadbiz.es/tmp/odoo.db.zip (12MB compressed / 38MB uncompressed) > this is happening: > > -1 Registering an sqlite3_trace function when trying to delete a record just > inserted on the table "res_users" the registered sqlite3_trace function is > called lots of times and sometimes it segfaults (I think stack overflow), I > think it enters in a unintended loop.
If I build the test program below using the command line provided with SQLite 3.8.11.1 and then run it against the oodo.db file from the link above, I get a single line of output: SQL: DELETE FROM res_users WHERE id=7 No errors under valgrind. Is this the expected result? Does it mean bug (1) has been fixed already? Dan. > > -2 If we do a dump "sqlite3 odoo.db .dump > odoo.db.sql" and try to recreate > the database with "sqlite3 new-odoo.db < odoo.db.sql" we get errors for > tables/views declarations out of order (trying to create a view > https://www.endad.eu/tmp/odoo.db.zipthat refer to other views not yet > created). > > Attached there is the simple "C" test file with a shell file to make it with > the flags I use on this project. > > This database uses a lot of foreign keys. > > The trigger on the "res_users" table is very simple: > ----- > BEFORE DELETE ON "res_users" > BEGIN > SELECT RAISE(ABORT, 'Can not remove root/admin user!') > WHERE OLD.id = 1; > END; > ----- > > I've also tested with a fresh sqlite3.c/h from > https://www.sqlite.org/snapshot/sqlite-amalgamation-201507231639.zip. > > When tested with a single table with the above trigger with a fresh database > the test program behaves as expected. > ----- > CREATE TABLE IF NOT EXISTS tbl(id INTEGER PRIMARY KEY, name varchar); > INSERT OR IGNORE INTO tbl(id, name) VALUES > (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'); > CREATE TRIGGER IF NOT EXISTS tbl_delete_trigger BEFORE DELETE ON tbl > BEGIN > SELECT RAISE(ABORT, 'Can not remove root/admin user!') WHERE OLD.id = > 1; > END; > ----- > > > I found a small test that shows how to create a database that after ".dump" > will not properly be restored. > > After writing to you about this bug with sqlite3_trace/trigger I start > thinking what I did with this database (odoo.db) that could possibly make > it's ".dump" not usable to restore and I found the reason. > > On that database I was constantly droping and recreating tables/views with > slight different fields and that seems what makes sqlite3 get lost. > > Example that creates a database not correctly restorable: > -------- > begin; > create table if not exists tbl(id integer primary key, name varchar); > insert or ignore into tbl(id, name) values (1,'a'), (2, 'b'); > create view if not exists tbl_view as select * from tbl; > create view if not exists tbl_view_view as select * from tbl_view; > drop view if exists tbl_view; > create view if not exists tbl_view as select * from tbl; > end; > -------- > > After creating a database with the above sql we get the following from > ".dump": > -------- > PRAGMA foreign_keys=OFF; > BEGIN TRANSACTION; > CREATE TABLE tbl(id integer primary key, name varchar); > INSERT INTO "tbl" VALUES(1,'a'); > INSERT INTO "tbl" VALUES(2,'b'); > CREATE VIEW tbl_view_view as select * from tbl_view; --<<<<<< here we > are trying to create a view on another view not yet created > CREATE VIEW tbl_view as select * from tbl; > COMMIT; > -------- > > On the ".dump"/".restore" problem it seems that sqlite3 shell rely on > sqlite3_master rowid order to perform the ".dump" and when we drop/recreate a > table/view that other tables/views depends sqlite3 do not detect it and > simply add a new entry at the end of sqlite3_master. > > > -------- shell script to make the bug test program > MYINC=$HOME/dev/sqlite3 > #MYINC=. > > gcc \ > -DTHREADSAFE=1 \ > -DSQLITE_DEFAULT_FILE_FORMAT=4 \ > -DSQLITE_DEFAULT_AUTOVACUUM=1 \ > -DSQLITE_DEFAULT_FOREIGN_KEYS= 1 \ > -DSQLITE_ENABLE_COLUMN_METADATA=1 \ > -DSQLITE_ENABLE_FTS4=1 \ > -DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \ > -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 \ > -DSQLITE_ENABLE_RTREE=1 \ > -DSQLITE_ENABLE_STAT4=1 \ > -DSQLITE_OMIT_TCL_VARIABLE=1 \ > -DSQLITE_USE_URI=1 \ > -DSQLITE_SOUNDEX=1\ > -o test-sqlite-bug test-sqlite-bug.c -I $MYINC $MYINC/sqlite3.c > -lpthread -lm -ldl > --------- > --------- test-sqlite-bug.c > #include <stdio.h> > #include "sqlite3.h" > > static const char test_sql[] = > "CREATE TABLE IF NOT EXISTS tbl(id INTEGER PRIMARY KEY, name > varchar);" > "INSERT OR IGNORE INTO tbl(id, name) VALUES " > "(1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');" > "CREATE TRIGGER IF NOT EXISTS tbl_delete_trigger BEFORE DELETE ON tbl > " > "BEGIN" > " SELECT RAISE(ABORT, 'Can not remove root/admin user!') WHERE > OLD.id = 1;" > "END;"; > > static void db_trace_callback(void *user, const char *sql) > { > printf("SQL: %s\n", sql ? sql : "??"); > } > > static void insertRecord(sqlite3 *db) > { > char *errmsg; > const char szSQL[] = "INSERT INTO > \"res_users\"(\"alias_id\",\"company_id\",\"create_uid\",\"login\",\"partner_i > d\") VALUES('10','3',4,'daddad','12')"; > sqlite3_trace(db, db_trace_callback, NULL); > int rc = sqlite3_exec(db, szSQL, NULL, NULL, &errmsg); > } > > int main(int argc, char *argv[]) > { > sqlite3 *db; > int rc = sqlite3_open("../odoo.db", &db); > if(rc == SQLITE_OK) > { > char *errmsg; > const char szSQL[] = "DELETE FROM res_users WHERE id=7"; > sqlite3_trace(db, db_trace_callback, NULL); > insertRecord(db); > //rc = sqlite3_exec(db, test_sql, NULL, NULL, &errmsg); > rc = sqlite3_exec(db, szSQL, NULL, NULL, &errmsg); > sqlite3_close(db); > } > return 0; > } > ------- > ------- output of test-sqlite-bug > SQL: INSERT INTO > "res_users"("alias_id","company_id","create_uid","login","partner_id") > VALUES('10','3',4,'daddad','12') > SQL: DELETE FROM res_users WHERE id=7 > SQL: -- TRIGGER res_users_admin_trigger > SQL: -- TRIGGER > .. <<<<<< the above line repeated 1000 times > ------- > > Thanks in advance for your time, attention and great work ! > Cheers ! > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
