We help IT Professionals succeed at work.

MySQL Insert Statement not working within a Trigger

shaf81
shaf81 asked
on
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

Top Expert 2012

Commented:
From http://dev.mysql.com/doc/refman/5.1/en/query-log.html

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.

So no, it's not expected to log the statements done by the trigger.

It does look like your trigger is straightforward.  So you're saying the insert into debug that's right after the insert into SyncDB.Users is executed, but the record expected to be in SyncDB.Users is not found, right?  Maybe check on SyncDB.Users if it has its own triggers that could possibly remove the records in question.  See also if there's a pattern in the actual data in the records are inserted without problems vs those that go missing.

Author

Commented:
Hello,

Thanks for the reply. I don't see any difference in the data that's written successfully to the data that fails. SyncDB dont have any triggers or functions. Its a simple database with 2 small tables.

However, from analyzing things further, I have found 1 pattern. To explain this further, below is my full Trigger code:

/*
_action values.
1=ADD
2=DELETE
3=ENABLE
4=DISABLE
5=UPDATE
*/


USE billing;

DELIMITER |

DROP TRIGGER IF EXISTS SHAF_SPX_UPDATE_U |

CREATE TRIGGER SHAF_SPX_UPDATE_U AFTER UPDATE ON ENTVOIPACCT
FOR EACH ROW BEGIN
 
		DECLARE _action INT;
  
		SET _action = 0;
  
IF NEW.ATRACCTTYID = 1 THEN
  
		IF OLD.ATRAUTHTYID != 2 AND NEW.ATRAUTHTYID = 2 THEN 
		SET _action = 1;
		ELSEIF OLD.ATRAUTHTYID = 2 AND NEW.ATRAUTHTYID != 2 THEN
		SET _action = 2;
		END IF;
   
	insert into debug(debug_output) values(CONCAT('INIT:',OLD.ATRALIAS,' - ', NEW.ATRBALANCE,' - ', NEW.ATRCREDLIM,' - ', OLD.ATRBALANCE,' - ', OLD.ATRCREDLIM));

	IF NEW.ATRBALANCE + NEW.ATRCREDLIM >= 0.01 THEN
		IF OLD.ATRBALANCE + OLD.ATRCREDLIM < 0.01 THEN 
			SET _action = 3;
			insert into debug(debug_output) values(CONCAT('ADD:',OLD.ATRALIAS,' - ', NEW.ATRBALANCE,' - ', NEW.ATRCREDLIM,' - ', OLD.ATRBALANCE,' - ', OLD.ATRCREDLIM));
		END IF;
	ELSE
		SET _action = 4;
		insert into debug(debug_output) values(CONCAT('DELETE:',NEW.ATRALIAS,' - ', NEW.ATRBALANCE,' - ', NEW.ATRCREDLIM,' - ', OLD.ATRBALANCE,' - ', OLD.ATRCREDLIM));
	END IF;
   
	IF OLD.ATRACCTSTAT = 1 AND NEW.ATRACCTSTAT != 1 THEN
		SET _action = 4;
	ELSEIF OLD.ATRACCTSTAT != 1 AND NEW.ATRACCTSTAT = 1 THEN
		SET _action = 3;
	END IF;
 
	IF OLD.ATRALIAS != NEW.ATRALIAS THEN
		SET _action = 5;
	END IF;
   
	IF OLD.ATRPASSW != NEW.ATRPASSW THEN
		SET _action = 5;
	END IF;
  
END IF;
 
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);

		insert into debug(debug_output) values(CONCAT('Q5:',NEW.ATRALIAS,':', NEW.ATRPASSW,':', OLD.ATRALIAS,':', OLD.ATRPASSW,':', _action));
	ELSE
		INSERT INTO SyncDB.Users(SPX_USER, SPX_USERPASS, SPX_ACTION) VALUES(NEW.ATRALIAS, NEW.ATRPASSW, _action);
		
		insert into debug(debug_output) values(CONCAT('Qx:',NEW.ATRALIAS,':', NEW.ATRPASSW,':', _action));
	END IF;  
END IF;
  
insert into debug(debug_output) values(CONCAT('ACTION:',OLD.ATRALIAS,' - ',_action));
 
END;
|
DELIMITER ;

Open in new window



This script used to work perfectly fine for quite sometime. Recently some upgrades happened on the 'billing' database. I believe since then I'm having problems. I dropped and recreated the triggers again, no luck. There are no other triggers in the database that interfere with the tables used in this script.

As seen above, we track update changes on the billing.ENTVOIPACCT Table, and based on some field changes we decide on a action and write it to SyncDB.Users table - pretty much straightforward.

The pattern I noticed is:

1) If the trigger encounters the following condition:
....
	IF NEW.ATRBALANCE + NEW.ATRCREDLIM >= 0.01 THEN
		IF OLD.ATRBALANCE + OLD.ATRCREDLIM < 0.01 THEN 
			SET _action = 3;
			insert into debug(debug_output) values(CONCAT('ADD:',OLD.ATRALIAS,' - ', NEW.ATRBALANCE,' - ', NEW.ATRCREDLIM,' - ', OLD.ATRBALANCE,' - ', OLD.ATRCREDLIM));
		END IF;
	ELSE .....

Open in new window


_action is set to 3, debug output is inserted perfectly fine, and the data looks perfectly normal.

Also, the actual writing to SyncDB.Users:
.....
	ELSE
		INSERT INTO SyncDB.Users(SPX_USER, SPX_USERPASS, SPX_ACTION) VALUES(NEW.ATRALIAS, NEW.ATRPASSW, _action);
		
		insert into debug(debug_output) values(CONCAT('Qx:',NEW.ATRALIAS,':', NEW.ATRPASSW,':', _action));
	END IF;  
END IF;
  
insert into debug(debug_output) values(CONCAT('ACTION:',OLD.ATRALIAS,' - ',_action));
 
.....

Open in new window


In the above code, both the debug output data looks perfectly fine, however, data is not logged into the SyncDB.Users


2) For the exact same record, if the trigger encounters the following condition:
...
	ELSEIF OLD.ATRACCTSTAT != 1 AND NEW.ATRACCTSTAT = 1 THEN
		SET _action = 3;
	END IF;
...

Open in new window



Then data is written to SyncDB.Users without a problem.

In either case, the debug output looks exactly the same matching 100% (Same record), however, the only difference I see is which condition it executed in trigger - nevertheless, the written data don't change.

So I don't understand how the 1st case fails, while the 2nd case is successful?

Any thoughts?

Thanks!
Top Expert 2012
Commented:
Hmmm this will be very hard to figure out without test data.  If you can provide a dump of the tables involved maybe I can give it a try later (about 7 hours from now when I get home from work).
Yes like johanntagle said can you provide us with the dump tables and sample data that you are using
Commented:
Hello Gentlemen,

I've spent about 6 straight hours trying to debug this hell. I created about 5 different debug tables, logged a whole lot of data and had no luck with this. This has been my worse MySQL nightmare!

When run queries manually on the MySQL command line,  the trigger works fine and even the usually failing dataset executes just fine, but when the application is executed, things kept failing. As I mentioned earlier, the exact same data either passes or fails on the INSERT statement merely based on which condition is met on the trigger. It never made any sense. Utter senseless.

Anyway, finally I managed to fix the problem. The solution too was crazily unguessable doesn't make sense to me either. If the destination table (SyncDB.Users) engine is of type InnoDB, the the trigger acts weird and crazy. I changed it to MyISAM and it all worked fine. InnoDB again, things start failing... So I'm sticking to MyISAM. Did extensive testing and found no issues when using MyISAM.

So that's the solution, change table engine to MyISAM. Explanation? God alone knows. This is one of those funny and frustrating scenarios. Hell with it!

I'm going for a coffee now!

Author

Commented:
Well, awarded points to contibutors appreciating their goodwill. Solution remains unexplained, yet, it works.
Top Expert 2012

Commented:
Sounds like a MySQL bug, suggest you look it up or  report it at http://bugs.mysql.com/.  Anyway, thanks for awarding points!

Commented:
Are you tried

SHOW INNODB STATUS

after the trigger fails?

Will show what's wrong with your queries or data.

Author

Commented:
No, I did not do a SHOW INNODB STATUS.

Partly because the changeover to MyISAM was just an intuition and it worked fine. Changed back to InnoDB and it failed fine.

So being the tiring and frustrated person I was at that point of time, I was just happy to stick with what works. I could've troubleshooted and found out why InnoDB fails, but it was a live system and I had to get it working - that was the motive.

Moreover, its a very lightweight database, with not much of any overhead, so there was absolutely no need for InnoDB.

Happy that it works, nevertheless, if possible I shall try to check whats  wrong with InnoDB, and post my findings for the benefit of all readers.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.