Hi Louis, list,
On 08/17/2015 04:08 PM, Louis Munro wrote: > Hi Mourik, > What version of mysql are you running? We're on stock wheezy 7, using the inverse repo's to install. This means: "mysql Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.2" > > Can you return the output to this please: > > MariaDB [pf]> show variables like '%mode'; mysql> show variables like '%mode'; +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | innodb_autoinc_lock_mode | 1 | | innodb_strict_mode | OFF | | pseudo_slave_mode | OFF | | slave_exec_mode | STRICT | | sql_mode | | +--------------------------+--------+ 5 rows in set (0.00 sec) mysql> MJ > > > This seems relevant to your problem, as per the ‘error 1690’message. > https://dev.mysql.com/doc/refman/5.5/en/out-of-range-and-overflow.html > > > -- > Louis Munro > [email protected] <mailto:[email protected]> :: www.inverse.ca > <http://www.inverse.ca> > +1.514.447.4918 x125 :: +1 (866) 353-6153 x125 > Inverse inc. :: Leaders behind SOGo (www.sogo.nu <http://www.sogo.nu>) > and PacketFence (www.packetfence.org <http://www.packetfence.org>) > > On Aug 17, 2015, at 9:34 , mourik jan heupink <[email protected] > <mailto:[email protected]>> wrote: > >> Just for completeness, here are the mysql queries that are executed upon >> clicking "Top 25 Bandwith Consumers": >> >>> 150817 7:25:38 477 QuerySELECT LOWER(CONCAT( >>> SUBSTRING(radacct.callingstationid,1,2),':', >>> SUBSTRING(radacct.callingstationid,3,2),':', >>> SUBSTRING(radacct.callingstationid,5,2),':', >>> SUBSTRING(radacct.callingstationid,7,2),':', >>> SUBSTRING(radacct.callingstationid,9,2),':', >>> SUBSTRING(radacct.callingstationid,11,2) >>> )) as callingstationid, >>> SUM(radacct_log.acctinputoctets) AS acctinputoctets, >>> SUM(radacct_log.acctoutputoctets) AS acctoutputoctets, >>> SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) >>> AS accttotaloctets >>> FROM radacct_log >>> LEFT JOIN radacct ON radacct_log.acctsessionid = >>> radacct.acctsessionid >>> WHERE radacct_log.timestamp BETWEEN '2015-07-18' AND '2015-08-17' >>> GROUP BY radacct.callingstationid >>> HAVING radacct.callingstationid IS NOT NULL >>> ORDER BY accttotaloctets DESC >>> LIMIT 25 >>> 477 QuerySELECT LOWER(CONCAT( >>> SUBSTRING(radacct.callingstationid,1,2),':', >>> SUBSTRING(radacct.callingstationid,3,2),':', >>> SUBSTRING(radacct.callingstationid,5,2),':', >>> SUBSTRING(radacct.callingstationid,7,2),':', >>> SUBSTRING(radacct.callingstationid,9,2),':', >>> SUBSTRING(radacct.callingstationid,11,2) >>> )) as callingstationid, >>> SUM(radacct_log.acctinputoctets) AS acctinputoctets, >>> SUM(radacct_log.acctoutputoctets) AS acctoutputoctets, >>> SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) >>> AS accttotaloctets >>> FROM radacct_log >>> LEFT JOIN radacct ON radacct_log.acctsessionid = >>> radacct.acctsessionid >>> WHERE radacct_log.timestamp BETWEEN '2015-07-18' AND '2015-08-17' >>> GROUP BY radacct.callingstationid >>> HAVING radacct.callingstationid IS NOT NULL >>> ORDER BY accttotaloctets DESC >>> LIMIT 25 >>> 477 QuerySELECT LOWER(CONCAT( >>> SUBSTRING(radacct.callingstationid,1,2),':', >>> SUBSTRING(radacct.callingstationid,3,2),':', >>> SUBSTRING(radacct.callingstationid,5,2),':', >>> SUBSTRING(radacct.callingstationid,7,2),':', >>> SUBSTRING(radacct.callingstationid,9,2),':', >>> SUBSTRING(radacct.callingstationid,11,2) >>> )) as callingstationid, >>> SUM(radacct_log.acctinputoctets) AS acctinputoctets, >>> SUM(radacct_log.acctoutputoctets) AS acctoutputoctets, >>> SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) >>> AS accttotaloctets >>> FROM radacct_log >>> LEFT JOIN radacct ON radacct_log.acctsessionid = >>> radacct.acctsessionid >>> WHERE radacct_log.timestamp BETWEEN '2015-07-18' AND '2015-08-17' >>> GROUP BY radacct.callingstationid >>> HAVING radacct.callingstationid IS NOT NULL >>> ORDER BY accttotaloctets DESC >>> LIMIT 25 >> >> And for "Bandwith per Operating System": >> >>> 470 QuerySELECT IFNULL(device_class, 'Unknown Fingerprint') AS >>> dhcp_fingerprint, >>> SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) >>> AS accttotaloctets, >>> ROUND( >>> >>> SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets)/( >>> SELECT >>> SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) >>> FROM radacct_log RIGHT JOIN radacct ON >>> radacct_log.acctsessionid = radacct.acctsessionid >>> INNER JOIN node n ON n.mac = LOWER(CONCAT( >>> SUBSTRING(radacct.callingstationid,1,2),':', >>> SUBSTRING(radacct.callingstationid,3,2),':', >>> SUBSTRING(radacct.callingstationid,5,2),':', >>> SUBSTRING(radacct.callingstationid,7,2),':', >>> SUBSTRING(radacct.callingstationid,9,2),':', >>> SUBSTRING(radacct.callingstationid,11,2))) >>> WHERE timestamp BETWEEN '2015-07-18' AND '2015-08-17' >>> )*100,1 >>> ) AS percent >>> FROM radacct_log >>> INNER JOIN radacct ON radacct_log.acctsessionid = >>> radacct.acctsessionid >>> INNER JOIN node n ON n.mac = LOWER(CONCAT( >>> SUBSTRING(radacct.callingstationid,1,2),':', >>> SUBSTRING(radacct.callingstationid,3,2),':', >>> SUBSTRING(radacct.callingstationid,5,2),':', >>> SUBSTRING(radacct.callingstationid,7,2),':', >>> SUBSTRING(radacct.callingstationid,9,2),':', >>> SUBSTRING(radacct.callingstationid,11,2)) >>> ) >>> WHERE timestamp BETWEEN '2015-07-18' AND '2015-08-17' >>> GROUP BY device_class >>> ORDER BY percent DESC >>> 470 QuerySELECT IFNULL(device_class, 'Unknown Fingerprint') AS >>> dhcp_fingerprint, >>> SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) >>> AS accttotaloctets, >>> ROUND( >>> >>> SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets)/( >>> SELECT >>> SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) >>> FROM radacct_log RIGHT JOIN radacct ON >>> radacct_log.acctsessionid = radacct.acctsessionid >>> INNER JOIN node n ON n.mac = LOWER(CONCAT( >>> SUBSTRING(radacct.callingstationid,1,2),':', >>> SUBSTRING(radacct.callingstationid,3,2),':', >>> SUBSTRING(radacct.callingstationid,5,2),':', >>> SUBSTRING(radacct.callingstationid,7,2),':', >>> SUBSTRING(radacct.callingstationid,9,2),':', >>> SUBSTRING(radacct.callingstationid,11,2))) >>> WHERE timestamp BETWEEN '2015-07-18' AND '2015-08-17' >>> )*100,1 >>> ) AS percent >>> FROM radacct_log >>> INNER JOIN radacct ON radacct_log.acctsessionid = >>> radacct.acctsessionid >>> INNER JOIN node n ON n.mac = LOWER(CONCAT( >>> SUBSTRING(radacct.callingstationid,1,2),':', >>> SUBSTRING(radacct.callingstationid,3,2),':', >>> SUBSTRING(radacct.callingstationid,5,2),':', >>> SUBSTRING(radacct.callingstationid,7,2),':', >>> SUBSTRING(radacct.callingstationid,9,2),':', >>> SUBSTRING(radacct.callingstationid,11,2)) >>> ) >>> WHERE timestamp BETWEEN '2015-07-18' AND '2015-08-17' >>> GROUP BY device_class >>> ORDER BY percent DESC >>> 470 QuerySELECT IFNULL(device_class, 'Unknown Fingerprint') AS >>> dhcp_fingerprint, >>> SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) >>> AS accttotaloctets, >>> ROUND( >>> >>> SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets)/( >>> SELECT >>> SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) >>> FROM radacct_log RIGHT JOIN radacct ON >>> radacct_log.acctsessionid = radacct.acctsessionid >>> INNER JOIN node n ON n.mac = LOWER(CONCAT( >>> SUBSTRING(radacct.callingstationid,1,2),':', >>> SUBSTRING(radacct.callingstationid,3,2),':', >>> SUBSTRING(radacct.callingstationid,5,2),':', >>> SUBSTRING(radacct.callingstationid,7,2),':', >>> SUBSTRING(radacct.callingstationid,9,2),':', >>> SUBSTRING(radacct.callingstationid,11,2))) >>> WHERE timestamp BETWEEN '2015-07-18' AND '2015-08-17' >>> )*100,1 >>> ) AS percent >>> FROM radacct_log >>> INNER JOIN radacct ON radacct_log.acctsessionid = >>> radacct.acctsessionid >>> INNER JOIN node n ON n.mac = LOWER(CONCAT( >>> SUBSTRING(radacct.callingstationid,1,2),':', >>> SUBSTRING(radacct.callingstationid,3,2),':', >>> SUBSTRING(radacct.callingstationid,5,2),':', >>> SUBSTRING(radacct.callingstationid,7,2),':', >>> SUBSTRING(radacct.callingstationid,9,2),':', >>> SUBSTRING(radacct.callingstationid,11,2)) >>> ) >>> WHERE timestamp BETWEEN '2015-07-18' AND '2015-08-17' >>> GROUP BY device_class >>> ORDER BY percent DESC >> >> I am not sure how to isolate a single query from the query log above, >> all my attempts generate failures like: >> >> ERROR 1690 (22003): BIGINT value is out of range in >> '(`pf`.`radacct_log`.`acctinputoctets` + >> `pf`.`radacct_log`.`acctoutputoctets`)' >> >> Any advise on this matter would be highly appreciated. >> >> MJ > ------------------------------------------------------------------------------ _______________________________________________ PacketFence-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/packetfence-users
