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.
?
-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 !
