Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.0.23
-
None
-
Raspbian 4.1.17-v7+ #838 SMP Tue Feb 9 13:15:09 GMT 2016 armv7l GNU/Linux
Description
The problem is with variables in SQL. Sometimes they are properly calculated, sometimes they are NULL.
How to reproduce:
1) login into MariaDB
mysql -u user -p password network
|
2) run query
call x(); select bytes_received, bytes_sent from timeserie; |
3) output:
+----------------+------------+
|
| bytes_received | bytes_sent |
|
+----------------+------------+
|
| NULL | NULL |
|
| NULL | NULL |
|
| NULL | NULL |
|
| NULL | NULL |
|
| NULL | NULL |
|
| NULL | NULL |
|
| NULL | NULL |
|
| NULL | NULL |
|
| NULL | NULL |
|
| NULL | NULL |
|
+----------------+------------+
|
4) run query again
+----------------+------------+
|
| bytes_received | bytes_sent |
|
+----------------+------------+
|
| -332305605 | 954862349 |
|
| 367026 | 12973563 |
|
| 421202 | 12788025 |
|
| 262783 | 6121133 |
|
| 306716 | 8915553 |
|
| 396136 | 13731970 |
|
| 373469 | 12127536 |
|
| 317901 | 10592084 |
|
| 380241 | 12344703 |
|
| 331722 | 11424220 |
|
+----------------+------------+
|
DDL:
delimiter $$
|
CREATE TABLE `traffic` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`date` datetime NOT NULL, |
`bytes_received` bigint(20) DEFAULT NULL, |
`bytes_sent` bigint(20) DEFAULT NULL, |
`hostname` varchar(45) NOT NULL, |
PRIMARY KEY (`id`), |
UNIQUE KEY `duplicate_entry` (`hostname`,`date`) |
) ENGINE=Aria AUTO_INCREMENT=54744 DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1$$ |
|
|
|
|
CREATE PROCEDURE `x`() |
BEGIN
|
DROP TEMPORARY TABLE IF EXISTS timeserie; |
CREATE TEMPORARY TABLE timeserie ENGINE = Aria AS |
SELECT |
`c`.`date` AS `date`, |
@prev_id AS `previous_id`, |
`c`.`id` AS `current_id`, |
@prev_received AS `bytes_received_cumulative_n`, |
`c`.`bytes_received` AS `bytes_received_cumulative_c`, |
@prev_sent AS `bytes_sent_cumulative_n`, |
`c`.`bytes_sent` AS `bytes_sent_cumulative_c`, |
c.bytes_received - @prev_received AS `bytes_received`, |
( `c`.`bytes_sent` - @prev_sent) AS `bytes_sent`, |
@prev_received := c.bytes_received,
|
@prev_sent := c.bytes_sent,
|
@prev_id := c.id
|
FROM `network`.`traffic` `c` |
WHERE `c`.`hostname` = 'hostname' |
ORDER BY `c`.`id`; |
END |