We help IT Professionals succeed at work.
Get Started

MySQL Insert Statement not working within a Trigger

shaf81
shaf81 asked
on
551 Views
Last Modified: 2012-05-12
Hello Experts!

I've run into quite a confusing problem. I have a MySQL trigger which examines changes on a table and then Inserts new data into another table.

I noticed sometimes the data does not get inserted into the Target table. I added some debug code and logged output to another debug table and found that the data being inserted to be valid and fine, however, I never see new inserts in the target table.

I also enabled the MySQL general query log to get an insight of whats going on, however, quite strangely I don't see any Insert queries run by the trigger being logged into the general query log - even if the Insert was successful! - Doesn't the general query log append all queries to the log irrespective of if it's generated by a connected client or not?

Below is a snipped of my code:

continuation.... 

IF _action > 0 THEN
	IF _action = 5 THEN
    
	INSERT INTO 
	SyncDB.Users(SPX_USER, SPX_USERPASS, SPX_OLD_USER, SPX_OLD_USERPASS, SPX_ACTION)
	VALUES(NEW.ATRALIAS, NEW.ATRPASSW, OLD.ATRALIAS, OLD.ATRPASSW, _action);

		/*Debug Logging*/
		Insert into debug(debug_output) values(CONCAT('Q5:',NEW.ATRALIAS,':', NEW.ATRPASSW,':', OLD.ATRALIAS,':', OLD.ATRPASSW,':', _action));
		/*End Debug Logging*/
		 
	ELSE
    
	INSERT INTO SyncDB.Users(SPX_USER, SPX_USERPASS, SPX_ACTION) VALUES(NEW.ATRALIAS, NEW.ATRPASSW, _action);

	/*Debug Logging*/
	insert into debug(debug_output) values(CONCAT('Qx:',NEW.ATRALIAS,':', NEW.ATRPASSW,':', _action));
	/*End Debug Logging*/
	
	END IF;  
END IF;
  
/*Debug Logging*/ 
insert into debug(debug_output) values(CONCAT('ACTION:',OLD.ATRALIAS,' - ',_action));
/*End Debug Logging*/

....more code... 

Open in new window


My debug code writes the proper data into the debug table (same database) and the data is valid. However, the actual data that needs to be inserted into SyncDB.Users is not getting inserted. This only happens in certain cases and all other times, things get executed without any problem at all. Irrespective of the case, the data format and validity is always the same and there is no reason why I see it should fail.

The load on the trigger is very low (about 1 query every few seconds) and the SyncDB.Users table has only a few rows of Data.

Any idea what could be going on here? Or at least, what better ways I can deploy to isolate the problem and fix it?

Thanks!
Shaf
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 3 Answers and 9 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE