I agree. Can you send me a patch about the foreign key checks for mysql? On Dec 9, 3:39 pm, Thadeus Burgess <[email protected]> wrote: > If I tell it to delete everything, and everything it references, I > expect it to do it, not complain about the fact that that could > happen. > > One way or another, all of those records are getting deleted, and I > hate it when I have to run circles around a "this is for your own > protection". > > I don't want to write 20 more lines of code to get around the fact one > function that is supposed to delete everything, won't because it > thinks I am not smart enough to realize that everything will be > deleted. > > If a table has cascading deletes, it should well... cascade. If I > don't want a .delete() to cascade, then I wouldn't declare a table as > cascading. > > I want it to do what I mean. > > -Thadeus > > On Wed, Dec 9, 2009 at 3:03 PM, mdipierro <[email protected]> wrote: > > and that is? > > > On Dec 9, 2:09 pm, Thadeus Burgess <[email protected]> wrote: > >> we're all consenting programmers here are we not? > > >> I expect it to do what I mean it to do :) > > >> -Thadeus > > >> On Wed, Dec 9, 2009 at 4:50 AM, Maciek Sykulski <[email protected]> wrote: > > >> > 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 > >> > athttp://groups.google.com/group/web2py?hl=en. > > > -- > > > 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 > > athttp://groups.google.com/group/web2py?hl=en. > >
-- 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.
