[MySQL] conditionele insert op regelmatig interval

[sloth]

Wat achtergrondinformatie:

Een database waarin per username bijgehouden wordt hoeveel bytes er verzonden en ontvangen worden.

Wat ik wil bereiken:

Een regelmatige INSERT query uitvoeren (ik dacht hiervoor cron te gebruiken) met daarin 2 records met username als variabele ALS een gebruiker meer dan een bepaalde waarde aan bytes verzonden en ontvangen heeft.

Wat ik zelf al geprobeerd heb:

-Een query die aangeeft welke usernames meer dan 10GB (upload en download als 1 getal )verbruikt hebben:

SQL:

SELECT username from radacct having SUM(AcctOutputOctets+AcctInputOctets) > '10737418240';

Resultaat:

code:

+----------+
| username |
+----------+
| test1    |
+----------+
1 row in set (0.00 sec)

-Een insert query met daarin 2 records, vooralsnog door de username handmatig in te voeren op basis van de query hierboven

SQL:

insert into radcheck VALUES("",'test1','Auth-Type',':=','Reject'),("",'test1','My-Reply',':=','Je hebt meer dan 10GB verbruikt');

Resultaat:

code:

Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Waar loop ik tegen vast

Hoe je beide queries combineert, en eerst de username van een gebruiker op basis van de SELECT SUM(AcctOutputOctets+AcctInputOctets) (sub)query een insert doet, met als waarde de username van de eerste query.

Het gaat niet meteen om de deeltjes om deze query op te bouwen, maar wel hoe je het e.e.a intelligent combineert om een zo efficiënt mogelijke oplossing te vinden.

Wellicht goed om te vermelden: dit is geen zoveelste "helpdesk" vraag; ik heb zelf de nodige tijd en moeite gedaan om dit zelf proberen op te lossen.

Kan iemand me in de juiste richting helpen?

Alvast bedankt!

[GlowMouse]

http://dev.mysql.com/doc/refman/5.5/en/insert-select.html

Maar dan krijg je geen twee records. Je zou daarvoor deze query 2x moeten draaien.

[ Voor 43% gewijzigd door GlowMouse op 19-12-2011 20:58 ]

[sloth]

Ik begrijp niet meteen hoe je dit kan gebruiken in bovenstaande situatie. Als ik het voorbeeld uit de documentatie dat je aanhaalt neem:

SQL:

INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

Stel dat ik dan wat als

SQL:

INSERT INTO radcheck (id, username, attribute, op, value) 
SELECT "", 'test3', 'Auth-Type',':=','Reject' 
FROM radcheck;

doe, dan kom ik nog niet echt veel verder:

De WHERE slaat terug op elke user die meer dan 10GB aan verkeer heeft, dus SUM(AcctOutputOctets+AcctInputOctets) > '10737418240';
MySQL kan blijkbaar de SUM functie niet combineren met de WHERE statement, waardoor het me niet duidelijk is hoe je dit kan verwerken in bovenstaande query.

SQL:

SELECT username 
FROM radacct 
HAVING SUM(AcctOutputOctets+AcctInputOctets) > 10737418240;

geeft keurig de username die ik wil inserten bij:

SQL:

SELECT "", '$USERNAME', 'Auth-Type',':=','Reject'

Tweede probleem waar ik tegen aan loop is hoe je in het voorbeeld

SQL:

WHERE tbl_temp1.fld_order_id > 100;

toepast enkel voor de users waarbij

SQL:

SUM(AcctOutputOctets+AcctInputOctets) > '10737418240';

net omdat dit gegevens zijn uit een andere tabel die gelinkt moet worden aan de username van radcheck

[pedorus]

Kwestie van knippen en plakken. Je hebt een query nodig die de tabel genereert die je wilt inserten.

SQL:

SELECT "",username, 'Auth-Type',':=','Reject' 
from radacct having SUM(AcctOutputOctets+AcctInputOctets) > '10737418240';

(Hier lijkt trouwens een group by te ontbreken.)
En je had al wat ervoor moet (werkt dat eigenlijk met keywords als value ongeescaped?)

SQL:

INSERT INTO radcheck (id, username, attribute, op, value)  

Andere vraag is natuurlijk waarom je twee rijen wil hebben voor hetzelfde event. Dit zou je met een UNION ALL in een query kunnen proppen, maar het blijft wat raar.

[ Voor 4% gewijzigd door pedorus op 19-12-2011 22:56 ]

[Rotterdammertje]

Waarom vergelijk je de uitkomst van de SUM (een getal) met een tekststring? Ik verwacht dat het in dit geval wel goed gaat, en dat mySql de string zal omzetten naar een getal, maar het zou net zo goed kunnen dat mySql besluit het precies andersom te doen -- dus de uitkomst van de SUM om te zetten naar een string. En dan is '9' ineens groter dan '10737418240'...

[pedorus]

mysql> select '10'>'9',10>'9';
+----------+--------+
| '10'>'9' | 10>'9' |
+----------+--------+
|        0 |      1 |
+----------+--------+
1 row in set (0.00 sec)

Hoewel het natuurlijk bad practice blijft.

Het issue zonder group by is trouwens een stuk groter. Er staat nu iets als retourneer een onbepaalde rij als de totale som over de hele tabel groter is dan x.

[sloth]

Bedankt voor jullie reacties, ik heb (bijna) wat ik hebben wil!

De query:

SQL:

INSERT INTO radcheck (id, username, attribute, op, value)
SELECT "",username,'My-Reply',':=','Je hebt meer dan 500MB verbruikt'
FROM radacct
GROUP BY username
HAVING SUM(AcctOutputOctets+AcctInputOctets) > '524288000'
UNION ALL
SELECT "",username, 'Auth-Type',':=','Reject' 
FROM radacct
GROUP BY username
HAVING SUM(AcctOutputOctets+AcctInputOctets) > '524288000';

Het nadeel is dat indien je deze query vaak gaat uitvoeren, er erg veel duplicates in de tabel terechtkomen.
Om dit tegen te gaan heb ik naar INSERT ... ON DUPLICATE KEY UPDATE en INSERT IGNORE gekeken, maar in beide gevallen blijven er duplicates bijkomen.

Voor alle duidelijkheid de opbouw van radcheck:

code:

mysql> describe radcheck;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| username  | varchar(64)      | NO   | MUL |         |                |
| attribute | varchar(64)      | NO   |     |         |                |
| op        | char(2)          | NO   |     | ==      |                |
| value     | varchar(253)     | NO   |     |         |                |
+-----------+------------------+------+-----+---------+----------------+

code:

mysql> show create table radcheck;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------          ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                          |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------          ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| radcheck | CREATE TABLE `radcheck` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL DEFAULT '',
  `attribute` varchar(64) NOT NULL DEFAULT '',
  `op` char(2) NOT NULL DEFAULT '==',
  `value` varchar(253) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `username` (`username`(32))
) ENGINE=MyISAM AUTO_INCREMENT=205 DEFAULT CHARSET=latin1 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------          ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Het is de combinatie van username, attribute, op en value die bepaalt of een record "uniek" is. Er komen namelijk meerdere records voor met dezelfde username en op, maar met een andere attribute.

Kan iemand me nogmaals een schopje in de juiste richting geven?

[ Voor 38% gewijzigd door sloth op 20-12-2011 19:34 . Reden: SHOW CREATE TABLE aanpassing op aanraden van GlowMouse ]

[GlowMouse]

sloth schreef op dinsdag 20 december 2011 @ 19:30:
Voor alle duidelijkheid de opbouw van radcheck:

Dat is niet duidelijk. Gebruik SHOW CREATE TABLE.

Ik mis de UNIQUE constraint in je tabel. Zonder zo'n constraint doet INSERT IGNORE of ON DUPLICATE KEY niks.

[ Voor 21% gewijzigd door GlowMouse op 20-12-2011 19:35 ]

[sloth]

Dat was inderdaad de oplossing, bedankt voor je reactie!

SQL:

alter table radcheck add unique index(username, attribute, op, value);

* sloth gaat nu uitzoeken hoe je het makkelijkst deze query op een regelmatig interval kan uitvoeren