RE: Triggers For Radius DB
In the last episode (Jul 07), Marcel Grandemange said: I hope someone can assist me. I have a freeradius server running off mysql. Now I would like to use triggers to negate some of the traffic logged within it. I tried to use following as trigger.. Create Trigger ftp BEFORE UPDATE ON radacct FOR EACH ROW BEGIN UPDATE radacct SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets) WHERE NASPortId=21; UPDATE radacct SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets) WHERE NASPortId=21; END; And Many variants of that but only ends up locking the db in someway with messages such as. Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: Cannot store result Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: MySQL error 'Can't update table 'radacct' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.' You don't want to UPDATE the table, since as you have seen it will cause an error. The only row you can change in the table being modified in a trigger is the active row itself, and you must change its values by SETting NEW.fieldname: IF NEW.NASPortID = 21 THEN SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets); SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets): END IF; -- Dan Nelson dnel...@allantgroup.com Thank you so much, this worked 100%! __ Information from ESET NOD32 Antivirus, version of virus signature database 4223 (20090708) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Another Trigger Question!
Good Day once again.
Im needing assistance again with triggers!
Create Trigger hostchange
BEFORE UPDATE ON host
FOR EACH ROW
BEGIN
IF NEW.status != OLD.status THEN
use smsgw;
insert into outbox (number, insertdate, text, phone, dreport) VALUES
('0836075144', '', 'HostDown', '1', '1'));
END IF;
END;
I have my idea of a trigger above that for obvious reasons wont work ,
however describes what im trying to do best!
Im monitoring a value status and if it changes id like to insert a record
into a entirely separate table in a separate DB.
Any ideas on how syntax should be?
Also anyone know of any good ebooks that describe triggers in depth? I find
most info I find on web is simplistic at best or simply don't explain well
anough for my level!
Regards
Triggers For Radius DB
Good Day. I hope someone can assist me. I have a freeradius server running off mysql. Now I would like to use triggers to negate some of the traffic logged within it. I tried to use following as trigger.. Create Trigger ftp BEFORE UPDATE ON radacct FOR EACH ROW BEGIN UPDATE radacct SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets) WHERE NASPortId=21; UPDATE radacct SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets) WHERE NASPortId=21; END; And Many variants of that but only ends up locking the db in someway with messages such as. Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: Cannot store result Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: MySQL error 'Can't update table 'radacct' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.' From Freeradius. I have managed to get triggers working to a separate db altogether but not from the same db to the same db. What am I doing wrong? Regards. Marcel Grandemange
RE: Replication
did u check if any of the file system holding bin-logs/data files are having enough free space. If the slave runs out off disk space, then you need to rebuild the slave from scratch. Yes lots of free space, so no problem there! regards anandkl On 12/8/08, ewen fortune ewen.fort...@gmail.com wrote: Hi, On Mon, Dec 8, 2008 at 9:20 AM, Marcel Grandemange thavi...@thavinci.za.net wrote: WHat errors are you getting when you try and start the slave? That's the exact thing mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.11.252 Master_User: cjcrepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.05 Read_Master_Log_Pos: 98 Relay_Log_File: gw2-relay-bin.99 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.05 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: cjcd0,cjcd0 Here you are filtering your replication, are you happy the filter is correctly applied and that you understand this configuration option. Peter from Percona wrote a blog post about this last year. http://www.mysqlperformanceblog.com/2007/11/07/filtered-mysql-replication/ Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 98 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) According to the slave all is running and 100%, although the data is visibly outdated. And updates to tables or even new tables do not replicate since connectivity loss.. This Seconds_Behind_Master: 0 combined with Slave_IO_Running: Yes and Slave_SQL_Running: Yes indicate that the slave is both up to date and running without problems caused by connectivity issues. How are you testing the differences between tables? to really know what is different you need to perform something like a table checksum, maatkit has a tool for that. http://www.maatkit.org/doc/mk-table-checksum.html If there really are differences between the two versions of table data I would suggest you are either filtering replication incorrectly (remember what filters are for master and which are for slaves) or you are using non-deterministic functions which when executed on the slave give a different result, something like this. http://www.mysqlperformanceblog.com/2008/09/29/why-audit-logging-with-trigge rs-in-mysql-is-bad-for-replication/ Cheers, Ewen What does the error log say? Good Day. Im wondering if someone can assist me. Ive been using replication for a while now and it tends to fail very easily. One of my sites lost connectivity for a while and when it came back obviously replication broke again. How can I get it to populate all data from master again? Load data from master; is being depreciated and doesn't really work anyhow. I do not wish to create dump and do this manually every time it fails. Advise? Also is replication really this unreliable? It breaks at the slightest hiccup... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ewen.fort...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com __ NOD32 3670 (20081208) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication
WHat errors are you getting when you try and start the slave? That's the exact thing mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.11.252 Master_User: cjcrepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.05 Read_Master_Log_Pos: 98 Relay_Log_File: gw2-relay-bin.99 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.05 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: cjcd0,cjcd0 Here you are filtering your replication, are you happy the filter is correctly applied and that you understand this configuration option. Yes, I only desire that db to be replicated. Peter from Percona wrote a blog post about this last year. http://www.mysqlperformanceblog.com/2007/11/07/filtered-mysql-replication/ Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 98 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) According to the slave all is running and 100%, although the data is visibly outdated. And updates to tables or even new tables do not replicate since connectivity loss.. This Seconds_Behind_Master: 0 combined with Slave_IO_Running: Yes and Slave_SQL_Running: Yes indicate that the slave is both up to date and running without problems caused by connectivity issues. How are you testing the differences between tables? to really know what is different you need to perform something like a table checksum, maatkit has a tool for that. Visibly! The Master DB has many more tables that wheir created during connection issues than the slave does. There is a massive difference. I can provide screen shots if needed. http://www.maatkit.org/doc/mk-table-checksum.html If there really are differences between the two versions of table data I would suggest you are either filtering replication incorrectly (remember what filters are for master and which are for slaves) or you are using non-deterministic functions which when executed on the slave give a different result, something like this. http://www.mysqlperformanceblog.com/2008/09/29/why-audit-logging-with-trigg ers-in-mysql-is-bad-for-replication/ Nothing of the kind. Cheers, Ewen What does the error log say? Good Day. Im wondering if someone can assist me. Ive been using replication for a while now and it tends to fail very easily. One of my sites lost connectivity for a while and when it came back obviously replication broke again. How can I get it to populate all data from master again? Load data from master; is being depreciated and doesn't really work anyhow. I do not wish to create dump and do this manually every time it fails. Advise? Also is replication really this unreliable? It breaks at the slightest hiccup... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=john.dais...@butterflysystems.co.uk __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ewen.fort...@gmail.com __ NOD32 3670 (20081208) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication
WHat errors are you getting when you try and start the slave? That's the exact thing mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.11.252 Master_User: cjcrepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.05 Read_Master_Log_Pos: 98 Relay_Log_File: gw2-relay-bin.99 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.05 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: cjcd0,cjcd0 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 98 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) According to the slave all is running and 100%, although the data is visibly outdated. And updates to tables or even new tables do not replicate since connectivity loss.. What does the error log say? Good Day. Im wondering if someone can assist me. Ive been using replication for a while now and it tends to fail very easily. One of my sites lost connectivity for a while and when it came back obviously replication broke again. How can I get it to populate all data from master again? Load data from master; is being depreciated and doesn't really work anyhow. I do not wish to create dump and do this manually every time it fails. Advise? Also is replication really this unreliable? It breaks at the slightest hiccup... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication
Good Day. Im wondering if someone can assist me. Ive been using replication for a while now and it tends to fail very easily. One of my sites lost connectivity for a while and when it came back obviously replication broke again. How can I get it to populate all data from master again? Load data from master; is being depreciated and doesn't really work anyhow. I do not wish to create dump and do this manually every time it fails. Advise? Also is replication really this unreliable? It breaks at the slightest hiccup... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
