Hi,
I was aware of the changes regarding TRIGGERs (new DEFINER feature) when updating our production servers to MySQL 5.0.17 today (see my blog post
http://www.futhark.ch/mysql/123.html from yesterday evening). But I still was surprised by the mayhem this update let loose (it's only an update between two consecutive production versions of the same 5.0 release after all).
On plain INSERTs and UPDATEs that fired TRIGGERs our normal users suddenly got errors 1227 (Access denied; you need the SUPER privilege for this operation) from the master server. So their write access to these tables was blocked. Additionally the slave quit replication with error 1446 (Definer is not fully qualified).
I was prepared to DROP and recreate all of the TRIGGERs as I thought I'd have to do this anyway to take advantage of the new DEFINER feature. But the bad surprise was that I couldn't DROP any TRIGGERs even as root, I always got errors 1344 (Unexpected end of file while skipping unknown parameter 'sql_modes'). I was even aware of Bug #14090 that dealt with an issue like this, but this bug is actually said to be closed in 5.0.17.
I'm sorry I didn't collect more information as I had to find a solution very quickly and just did a brute force "find /var/lib/mysql -name '*.TR?' -exec rm \{\} \;" and restarted the servers to redefine the most necessary TRIGGERs.
We never ever created a TRIGGER before 5.0.15 on this server (as we only switched to 5.0 on our production master with the first official production release). Is this the normal behaviour for an update between 5.0.16 and 5.0.17 or did something very awkward go on here?
Thanks,
Beat