On 08/03/2015 07:24 PM, sqlite-mail wrote: > > Hello Dan ! > > I downloaded again > http://www.sqlite.org/2015/sqlite-amalgamation-3081101.zip just and > recompiled it and running I still get the same 1000 lines of the > trigger call. > > My environment is ubuntu 14.04 and I'm using gcc 4.9.2 and now I also > compiled with clang 3.6 and I've got the same 1000 lines of trigger call. > > As you can see from the test program there is not much else than > sqlite3 and the database. > > I also tested before on OS X with the same result, but I can not test > it again now there, later on I'll do it. > > What environment are you testing ? I mean os, compiler, ... >
64-bit Linux with gcc 4.7.1. Are we using the same database? Before running the test program, I get: $ sha1sum ./odoo.db 0f5ac7aa291a9c149cf36bbc6ac4c73a90774c97 ./odoo.db and $ sqlite3 ./odoo.db "SELECT count(*) FROM res_users WHERE id = 7;" 0 Is this as expected? Dan. > Cheers ! > > Mon Aug 03 2015 13:08:08 CEST from "Dan Kennedy" > <danielk1977 at gmail.com> Subject: Re: [sqlite] Bug in > sqlite3_trace/trigger/delete > 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 > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
