On Dec 8, 10:09 pm, mdipierro <[email protected]> wrote: > You are right. I think this line was introduced to fix a problem and > created another. The "SET FOREIGN_KEY_CHECKS=0" should be executed > only when dropping a table in MySQL. Do you agree?
Hi Massimo, I'm not sure. With SET FOREIGN_KEY_CHECKS=1 MySQL won't allow DROP TABLE to be executed when there are other tables referencing it, which might be a very good thing because database remains consistent. In an ideal world user should alter referencing tables and remove all referencing columns before delete/truncate is performed. Oracle has CASCADE CONSTRAINTS which causes all relevant constraints to be dropped. MySQL drop table CASCADE is not doing anything in later versions. On the other hand, TRUNCATE TABLE might be an opposite problem - it does delete all rows in a table and also all others from other tables referencing it. This means that a whole database may be purged by db.table.truncate() not well thought through (ON DELETE CASCADE is the default setting for all foreign key references in web2py, isn't it?). The FOREIGN_KEY_CHECKS should be 1 for most of operations. When comes to delete, truncate - I'm not sure how to weight on that. It depends on general policy of web2py - how user-friendly, dummy- proof, do-what-i-want-no-matter-what it is. Maciek > On Dec 8, 2:47 pm, Maciek Sykulski <[email protected]> wrote: > > > Hi, > > > We noticed that in our MySQL database ON DELETE CASCADE is not working > > when a row is deleted by web2py controller. > > It is working ok when I run SQL delete from mysql console. > > Because of that, it is possible to get database into inconsistent > > state with web2py > > > When looking into this problem I noticed self._execute('SET > > FOREIGN_KEY_CHECKS=0;') in web2py source: > > > web2py$ grep -n -A 2 -B 10 -e "FOREIGN_KEY_CHECKS" -R * > > gluon/sql.py-870- self._pool_connection(lambda : > > MySQLdb.Connection( > > gluon/sql.py-871- db=db, > > gluon/sql.py-872- user=user, > > gluon/sql.py-873- passwd=passwd, > > gluon/sql.py-874- host=host, > > gluon/sql.py-875- port=int(port), > > gluon/sql.py-876- charset=charset, > > gluon/sql.py-877- )) > > gluon/sql.py-878- self._cursor = self._connection.cursor() > > gluon/sql.py-879- self._execute = lambda *a, **b: > > self._cursor.execute(*a, **b) > > gluon/sql.py:880: self._execute('SET > > FOREIGN_KEY_CHECKS=0;') > > gluon/sql.py-881- self._execute("SET > > sql_mode='NO_BACKSLASH_ESCAPES';") > > gluon/sql.py-882- elif not is_jdbc and self._uri[:11] == > > 'postgres://': > > > What is the rationale for self._execute('SET FOREIGN_KEY_CHECKS=0;') > > there? > > I'd like to have my database consistent and ON DELETE CASCADE working > > - > > can I/ should I change it to =1 ? > > > I've found a discussion about FOREIGN_KEY_CHECKS in web2py > > here ...but not sure if it's > > related.http://markmail.org/message/6472owgwupttlblq > > > Regards, > > Maciek -- You received this message because you are subscribed to the Google Groups "web2py-users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/web2py?hl=en.
