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]
combining rows
Hello list, I've got a query problem with MySQL 4._0_. A specific SELECT will give me a result set with two columns, an ID and an information. Now I want to group all results by their ID. Speak: In the result there should be only one row per ID but the informations from the different rows shall be summarized within a single field. I hope that was understandable enough... Searching the manual I found GROUP_CONCAT which seems to do what I want. But this is only available for MySQL 4._1_. And for now I can't update. Is there any other possibility to get that to work? I want to keep all the logic within the SQL statement since it is just one within a huge selection of queries. A performance penalty shouldn't be a problem, since this whole query will only be called once a day at 3 or 4 AM to generate a file. Thank you Marcel PS: a simple example I will try to show it with a university database example. I have a table with all lectures, a table with all students and a table which creates a relation hearing between the two. I need a table where I have exactly one row for each lecture and all students within a column. SELECT hearing.lecture_id, students.name FROM hearing LEFT JOIN students ON hearing.student_id = students.id; That gives me the needed lecture/student combination (ok, I even need the lecture name instead of the id, but that's just a JOIN more). Hoehere Mathematik| Guenter Beckstein Hoehere Mathematik| Edmund Stoiber Hoehere Mathematik| Gerhard Schroeder Diskrete Strukturen | Britney Spears Diskrete Strukturen | Anastasia Diskrete Strukturen | Backstreet Boys But I still have a row for each combination. When I change the query to include a GROUP BY like this SELECT hearing.lecture_id, students.name FROM hearing LEFT JOIN students ON hearing.student_id = students.id GROUP BY hearing.lecture_id; I will get only one row per lecture (good) but also only with a single student in the name-field - the first one (bad). Hoehere Mathematik| Guenter Beckstein Diskrete Strukturen | Britney Spears instead of Hoehere Mathematik| Guenter Beckstein, Edmund Stoiber, Gerhard Schroeder Diskrete Strukturen | Britney Spears, Anastasia, Backstreet Boys which is what I need -- Marcel Meyer | Netzwerk- und Rechnerorganisation | Fachschaft Mathematik/Physik/Informatik | Technische Universität München -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT from multiple tables...
Hi, It would work something like this: SELECT * FROM products p, uses u, prod_uses pu WHERE p.pid=pu.pid AND u.uid=pu.uid AND productname='product 1'; Where products pid int(11) PRI (NULL) auto_increment productname varchar(25) YES (NULL) uses uid int(11) PRI (NULL) auto_increment uses varchar(25) YES (NULL) prod_uses pid int(11) 0 uid int(11) 0 I hope this works out for you. Marcel - Original Message - From: Mikey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 29, 2003 12:54 PM Subject: SELECT from multiple tables... Hi NG, this is my first visit here, so please be gentle!!! I have a table of products (`prods`), and a separate table of product uses (`uses`) and the products are linked to their uses by a list (`prod_uses`). What I need to be able to do is allow a search of products by their use and I am getting a bit stuck on writing the where clause. I know that for a simple join I can use: SELECT * FROM table1, table2, WHERE table1.field=table2.field2 AND table2.field2='value' - but how do I make a join that will get back all products with a specified application? Many thanks for your help and time... regards, Mikey - In theory, theory and practice should be the same, in practice it isn't. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT from multiple tables...
Sorry, I missed the last part of your email. Try: SELECT * FROM products p, uses u, prod_uses pu WHERE p.pid=pu.pid AND u.uid=pu.uid AND uses='use 1'; :-) - Original Message - From: Marcel Forget [EMAIL PROTECTED] To: Mikey [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 29, 2003 8:17 PM Subject: Re: SELECT from multiple tables... Hi, It would work something like this: SELECT * FROM products p, uses u, prod_uses pu WHERE p.pid=pu.pid AND u.uid=pu.uid AND productname='product 1'; Where products pid int(11) PRI (NULL) auto_increment productname varchar(25) YES (NULL) uses uid int(11) PRI (NULL) auto_increment uses varchar(25) YES (NULL) prod_uses pid int(11) 0 uid int(11) 0 I hope this works out for you. Marcel - Original Message - From: Mikey [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 29, 2003 12:54 PM Subject: SELECT from multiple tables... Hi NG, this is my first visit here, so please be gentle!!! I have a table of products (`prods`), and a separate table of product uses (`uses`) and the products are linked to their uses by a list (`prod_uses`). What I need to be able to do is allow a search of products by their use and I am getting a bit stuck on writing the where clause. I know that for a simple join I can use: SELECT * FROM table1, table2, WHERE table1.field=table2.field2 AND table2.field2='value' - but how do I make a join that will get back all products with a specified application? Many thanks for your help and time... regards, Mikey - In theory, theory and practice should be the same, in practice it isn't. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select name, email where birthday = ??
Try: select name, birthday from atable where month(birthday)=month(now()) and dayofmonth(birthday)=dayofmonth(now()); Marcel - Original Message - From: Scott Haneda [EMAIL PROTECTED] To: MySql [EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 10:20 PM Subject: Select name, email where birthday = ?? Hello, I have a date field in my database Format is -MM-DD Once a day at 12:01AM a script is going to run that will Select name, email from users where birthday = ?? And the ?? Is where I am stuck, I don¹t care about the year at all, I just need to match on the month and day, how would I do this? - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SSL
Hi all, How is the best tool for acess Mysql By SSL protocol ? --- Marcel P. Tardelli DBA - DBLive Corp. [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Tables and Indexes
In Mysql I can have tables in one HD and Index and other Like Oracle ?? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
STRING TO NUMBER
Hi, In Oracle i use TO_NUMBER to convert a String to a Number what Function Do i have to use in Mysql ?? thank´s Marcel. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
big-tables option = Your help
Hi everybody, your help please I meet the well-known problem with 3.22 mysql on a 6.1 redhat linux platform. Database error: Invalid SQL: select ..blabla MySQL Error: 1114 (The table 'SQL54_0' is full) Session halted. I put the option big-tables in all the following groups in the .my.cnf file: [mysql] [mysqld] [mysql.server] This file is read correctly, with the print default I can see, the different groups have the --big-files option to start First I start with [mysql] the following and finally all... But I've always the table full default, no progress... Your help please ? ### Marcel Bariou = [EMAIL PROTECTED] Thank for your interest -- Merci pour votre attention ###
