See comments inline below... However also note that it now turns out after more testing that the new user is having the same issue "some of the time". Sometimes the replace does not cascade other times it does. Every single time in the binary log it properly lists the SET FOREIGN_KEY_CONSTRAINTS as succeeding right before the replace though.
John A. McCaskey -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, July 11, 2005 11:42 AM To: John McCaskey Cc: mysql@lists.mysql.com Subject: Re: SET FOREIGN_KEY_CHECKS=0 being ignored Some additional info would probably be helpful: What version of mysql are you running? 4.0.18 On what platform? Debian Linux (2.4 kernel) For the sake of comparison, what does SHOW GRANTS say for your "brand new user"? +----------------------------------------------------------------------- -------------+ | Grants for [EMAIL PROTECTED] | +----------------------------------------------------------------------- -------------+ | GRANT USAGE ON *.* TO 'proxyuser'@'10.%' IDENTIFIED BY PASSWORD '12345273123400f' | | GRANT ALL PRIVILEGES ON `IPS_config`.* TO 'proxyuser'@'10.%' | | GRANT ALL PRIVILEGES ON `IPS_data`.* TO 'proxyuser'@'10.%' | +----------------------------------------------------------------------- -------------+ 3 rows in set (0.00 sec) Finally, did you 'SELECT CURRENT_USER()' to verify that you were logged in as 'scopeuser'@'10.254.%' when it didn't work? These are being called from a c program using the c_api, so no. However I do use show processlist to see the connections and can verify there that they are connected as the correct user. Michael John McCaskey wrote: > Ok, So I recreated a brand new user in our production server and it now > works correctly. However, I still have no idea why the old user did not > work. Here is the permission info for the old user: > > mysql> show grants for 'scopeuser'@'10.254.%'; > +----------------------------------------------------------------------- > --------------------------+ > | Grants for [EMAIL PROTECTED] > | > +----------------------------------------------------------------------- > --------------------------+ > | GRANT ALL PRIVILEGES ON *.* TO 'scopeuser'@'10.254.%' IDENTIFIED BY > PASSWORD '2326f23b5ff9232' | > +----------------------------------------------------------------------- > --------------------------+ > 1 row in set (0.00 sec) > > mysql> > > So it looks like this is some sort of bug with the user being corrupted > somehow and permissions checks causing the SET FOREIGN_KEY_CHECKS=0 to > not work but still return success? Seems like a mysql bug then right? > > John A. McCaskey > > -----Original Message----- > From: John McCaskey > Sent: Monday, July 11, 2005 9:51 AM > To: John McCaskey; mysql@lists.mysql.com > Subject: RE: SET FOREIGN_KEY_CHECKS=0 being ignored > > Oh, I should also mention we have binary logging on and I verified by > looking at the binary log that the commands are being excuted and logged > with an error code of 0. So the obvious thought of the codes broken and > not running them is unfortunately not the problem. It seems to be some > sort of legitimate mysql setup error on our part or a bug in mysql. > > John A. McCaskey > > -----Original Message----- > From: John McCaskey [mailto:[EMAIL PROTECTED] > Sent: Monday, July 11, 2005 9:46 AM > To: mysql@lists.mysql.com > Subject: SET FOREIGN_KEY_CHECKS=0 being ignored > > Hey, > > I have an application using the C API that is doing a REPLACE command > into an innodb table that has other tables with cascading deletes > relying on it's entries. Rather than use an UPDATE/Check > affected/Insert/Check success/repeat method we have wrapped the REPLACE > query in a SET FOREIGN_KEY_CHECKS=0; then after SET > FOREIGN_KEY_CHECKS=1; pair of commands. This is working great in our > dev and test environments but its been discovered that on our production > servers it is apparently having no effect and the cascading deletes are > occurring anyway. > > So, the first thing I thought was 'something must be wrong with the > permissions' but I've been unable to find any discrepancies and the > manual doesn't seem to indicate you even need any special permissions to > execute the set command. Has anyone else experienced anything similar? > Does anyone have any ideas what environmental differences could cause > the SET FOREIGN_KEY_CHECKS command to be ignored? I'm at my wits end > here... any suggestions appreciated. > > John A. McCaskey > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
