"create table as select ..." failed with unknown reason
Posted by: harry sheng
Date: December 19, 2016 07:17PM
Date: December 19, 2016 07:17PM
Environment: MySQL 5.7.16 on RHL6
I have the following SQL statements listed below.
If I execute STMT_1, STMT_2 or STMT_3, I get the expected result without problem.
Execution of STMT_4 fails with error message:
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '1/11/1'
*** STMT_1 ***:
SELECT ((cast(c.time_recorded as date) + interval hour(c.time_recorded) hour) + interval (floor(((minute(c.time_recorded) + 7) / 15)) * 15) minute) AS base_ts,
r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, null AS queue, AVG(c.periodic_time) AS periodic_time,
SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) AS octets_forwarded,
SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) / AVG(c.periodic_time) AS rate,
SUM(COALESCE(c.in_profile_octets_dropped + c.out_of_profile_octets_dropped, c.octets_dropped)) AS octets_dropped
FROM (service_combined_network_egress_octets r JOIN service_combined_network_egress_octets_periodic c ON ((r.id = c.id)))
WHERE r.time_recorded >= STR_TO_DATE('2016-10-01', '%Y-%m-%d')
AND DAYOFWEEK(r.time_recorded) IN (2,3,4,5,6)
AND DATE_FORMAT(r.time_recorded, '%H:%i') BETWEEN '08:00' AND '20:00'
AND (ISNULL(r.suspect) OR (r.suspect = FALSE))
GROUP BY base_ts, site, lag, port, queue
ORDER BY site, lag, port, queue, DATE_FORMAT(base_ts, '%Y-%m-%d'), rate desc
*** STMT_2 ***:
SELECT @ROWNUM := CASE WHEN @ROWKEY = CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IFNULL(t.queue, ''), '@', IFNULL(t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) THEN @ROWNUM + 1 ELSE 1 END AS rownum,
@ROWKEY := CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IF(t.queue, t.queue, ''), '@', IF(t.port, t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) as rowkey,
t.base_ts, t.site, t.lag, t.port, t.queue, t.periodic_time, t.octets_forwarded, t.rate, t.octets_dropped
FROM ( STMT_1) AS t JOIN (SELECT @ROWNUM:=0, @ROWKEY:='') AS p
*** STMT_3 ***:
SELECT * FROM ( STMT_2 ) AS i WHERE rownum <= 10
*** STMT_4 ***:
create temporary table trunktrending as STMT_3
- or -
create table trunktrending as STMT_3
This is the full text of STMT_4:
create temporary table trunktrending as SELECT * FROM ( SELECT @ROWNUM := CASE WHEN @ROWKEY = CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IFNULL(t.queue, ''), '@', IFNULL(t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) THEN @ROWNUM + 1 ELSE 1 END AS rownum, @ROWKEY := CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IF(t.queue, t.queue, ''), '@', IF(t.port, t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) as rowkey, t.base_ts, t.site, t.lag, t.port, t.queue, t.periodic_time, t.octets_forwarded, t.rate, t.octets_dropped FROM ( SELECT ((cast(c.time_recorded as date) + interval hour(c.time_recorded) hour) + interval (floor(((minute(c.time_recorded) + 7) / 15)) * 15) minute) AS base_ts, r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, null AS queue, AVG(c.periodic_time) AS periodic_time, SUM(c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded) AS octets_forwarded, SUM(c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded) / AVG(c.periodic_time) AS rate, SUM(c.in_profile_octets_dropped + c.out_of_profile_octets_dropped) AS octets_dropped FROM (service_combined_network_ingress_octets r JOIN service_combined_network_ingress_octets_periodic c ON ((r.id = c.id))) WHERE r.time_recorded >= STR_TO_DATE('2016-10-01', '%Y-%m-%d') AND DAYOFWEEK(r.time_recorded) IN (2,3,4,5,6) AND DATE_FORMAT(r.time_recorded, '%H:%i') BETWEEN '08:00' AND '20:00' AND 1=1 AND (ISNULL(r.suspect) OR (r.suspect = FALSE)) GROUP BY base_ts, site, lag, port, queue ORDER BY site, lag, port, queue, DATE_FORMAT(base_ts, '%Y-%m-%d'), rate desc) AS t JOIN (SELECT @ROWNUM:=0, @ROWKEY:='') AS p) AS i WHERE rownum <= 10
These are samples of SELECT statement and CREATE TABLE ... AS SELECT statement, the difference of the two CREATE TABLE ... AS SELECT statements is, one SELECT statement has the LIMIT 10 clause, the other does not,
mysql> SELECT * FROM ( SELECT @ROWNUM := CASE WHEN @ROWKEY = CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IFNULL(t.queue, ''), '@', IFNULL(t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) THEN @ROWNUM + 1 ELSE 1 END AS rownum, @ROWKEY := CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IF(t.queue, t.queue, ''), '@', IF(t.port, t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) as rowkey, t.base_ts, t.site, t.lag, t.port, t.queue, t.periodic_time, t.octets_forwarded, t.rate, t.octets_dropped FROM ( SELECT ((cast(c.time_recorded as date) + interval hour(c.time_recorded) hour) + interval (floor(((minute(c.time_recorded) + 7) / 15)) * 15) minute) AS base_ts, r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, null AS queue, AVG(c.periodic_time) AS periodic_time, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) AS octets_forwarded, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) / AVG(c.periodic_time) AS rate, SUM(COALESCE(c.in_profile_octets_dropped + c.out_of_profile_octets_dropped, c.octets_dropped)) AS octets_dropped FROM (service_combined_network_egress_octets r JOIN service_combined_network_egress_octets_periodic c ON ((r.id = c.id))) WHERE r.time_recorded >= STR_TO_DATE('2016-10-01', '%Y-%m-%d') AND DAYOFWEEK(r.time_recorded) IN (2,3,4,5,6) AND DATE_FORMAT(r.time_recorded, '%H:%i') BETWEEN '08:00' AND '20:00' AND (ISNULL(r.suspect) OR (r.suspect = FALSE)) GROUP BY base_ts, site, lag, port, queue ORDER BY site, lag, port, queue, DATE_FORMAT(base_ts, '%Y-%m-%d'), rate desc) AS t JOIN (SELECT @ROWNUM:=0, @ROWKEY:='') AS p) AS i WHERE rownum <= 10 limit 10;
+--------+---------------------------------+---------------------+---------------+------+-------+-------+---------------+------------------+----------------+----------------+
| rownum | rowkey | base_ts | site | lag | port | queue | periodic_time | octets_forwarded | rate | octets_dropped |
+--------+---------------------------------+---------------------+---------------+------+-------+-------+---------------+------------------+----------------+----------------+
| 1 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 19:45:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 105102491154 | 116780545.7267 | 0 |
| 2 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 19:30:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 98761467968 | 109734964.4089 | 0 |
| 3 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 10:45:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 96715541909 | 107461713.2322 | 0 |
| 4 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 10:30:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 96278107024 | 106975674.4711 | 0 |
| 5 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 11:00:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 95300297379 | 105889219.3100 | 0 |
| 6 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 09:15:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 92374906753 | 102638785.2811 | 0 |
| 7 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 10:15:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 90550554994 | 100611727.7711 | 0 |
| 8 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 10:00:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 90204160249 | 100226844.7211 | 0 |
| 9 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 20:00:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 88249418862 | 98054909.8467 | 0 |
| 10 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 09:30:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 87781313684 | 97534792.9822 | 0 |
+--------+---------------------------------+---------------------+---------------+------+-------+-------+---------------+------------------+----------------+----------------+
10 rows in set, 6483 warnings (17.19 sec)
mysql>
mysql> create temporary table trunktrending as SELECT * FROM ( SELECT @ROWNUM := CASE WHEN @ROWKEY = CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IFNULL(t.queue, ''), '@', IFNULL(t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) THEN @ROWNUM + 1 ELSE 1 END AS rownum, @ROWKEY := CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IF(t.queue, t.queue, ''), '@', IF(t.port, t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) as rowkey, t.base_ts, t.site, t.lag, t.port, t.queue, t.periodic_time, t.octets_forwarded, t.rate, t.octets_dropped FROM ( SELECT ((cast(c.time_recorded as date) + interval hour(c.time_recorded) hour) + interval (floor(((minute(c.time_recorded) + 7) / 15)) * 15) minute) AS base_ts, r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, null AS queue, AVG(c.periodic_time) AS periodic_time, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) AS octets_forwarded, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) / AVG(c.periodic_time) AS rate, SUM(COALESCE(c.in_profile_octets_dropped + c.out_of_profile_octets_dropped, c.octets_dropped)) AS octets_dropped FROM (service_combined_network_egress_octets r JOIN service_combined_network_egress_octets_periodic c ON ((r.id = c.id))) WHERE r.time_recorded >= STR_TO_DATE('2016-10-01', '%Y-%m-%d') AND DAYOFWEEK(r.time_recorded) IN (2,3,4,5,6) AND DATE_FORMAT(r.time_recorded, '%H:%i') BETWEEN '08:00' AND '20:00' AND (ISNULL(r.suspect) OR (r.suspect = FALSE)) GROUP BY base_ts, site, lag, port, queue ORDER BY site, lag, port, queue, DATE_FORMAT(base_ts, '%Y-%m-%d'), rate desc) AS t JOIN (SELECT @ROWNUM:=0, @ROWKEY:='') AS p) AS i WHERE rownum <= 10;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '1/11/1'
mysql>
mysql> create temporary table trunktrending as (SELECT * FROM ( SELECT @ROWNUM := CASE WHEN @ROWKEY = CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IFNULL(t.queue, ''), '@', IFNULL(t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) THEN @ROWNUM + 1 ELSE 1 END AS rownum, @ROWKEY := CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IF(t.queue, t.queue, ''), '@', IF(t.port, t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) as rowkey, t.base_ts, t.site, t.lag, t.port, t.queue, t.periodic_time, t.octets_forwarded, t.rate, t.octets_dropped FROM ( SELECT ((cast(c.time_recorded as date) + interval hour(c.time_recorded) hour) + interval (floor(((minute(c.time_recorded) + 7) / 15)) * 15) minute) AS base_ts, r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, null AS queue, AVG(c.periodic_time) AS periodic_time, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) AS octets_forwarded, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) / AVG(c.periodic_time) AS rate, SUM(COALESCE(c.in_profile_octets_dropped + c.out_of_profile_octets_dropped, c.octets_dropped)) AS octets_dropped FROM (service_combined_network_egress_octets r JOIN service_combined_network_egress_octets_periodic c ON ((r.id = c.id))) WHERE r.time_recorded >= STR_TO_DATE('2016-10-01', '%Y-%m-%d') AND DAYOFWEEK(r.time_recorded) IN (2,3,4,5,6) AND DATE_FORMAT(r.time_recorded, '%H:%i') BETWEEN '08:00' AND '20:00' AND (ISNULL(r.suspect) OR (r.suspect = FALSE)) GROUP BY base_ts, site, lag, port, queue ORDER BY site, lag, port, queue, DATE_FORMAT(base_ts, '%Y-%m-%d'), rate desc) AS t JOIN (SELECT @ROWNUM:=0, @ROWKEY:='') AS p) AS i WHERE rownum <= 10 limit 10);
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '1/11/1'
mysql>
I have the following SQL statements listed below.
If I execute STMT_1, STMT_2 or STMT_3, I get the expected result without problem.
Execution of STMT_4 fails with error message:
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '1/11/1'
*** STMT_1 ***:
SELECT ((cast(c.time_recorded as date) + interval hour(c.time_recorded) hour) + interval (floor(((minute(c.time_recorded) + 7) / 15)) * 15) minute) AS base_ts,
r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, null AS queue, AVG(c.periodic_time) AS periodic_time,
SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) AS octets_forwarded,
SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) / AVG(c.periodic_time) AS rate,
SUM(COALESCE(c.in_profile_octets_dropped + c.out_of_profile_octets_dropped, c.octets_dropped)) AS octets_dropped
FROM (service_combined_network_egress_octets r JOIN service_combined_network_egress_octets_periodic c ON ((r.id = c.id)))
WHERE r.time_recorded >= STR_TO_DATE('2016-10-01', '%Y-%m-%d')
AND DAYOFWEEK(r.time_recorded) IN (2,3,4,5,6)
AND DATE_FORMAT(r.time_recorded, '%H:%i') BETWEEN '08:00' AND '20:00'
AND (ISNULL(r.suspect) OR (r.suspect = FALSE))
GROUP BY base_ts, site, lag, port, queue
ORDER BY site, lag, port, queue, DATE_FORMAT(base_ts, '%Y-%m-%d'), rate desc
*** STMT_2 ***:
SELECT @ROWNUM := CASE WHEN @ROWKEY = CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IFNULL(t.queue, ''), '@', IFNULL(t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) THEN @ROWNUM + 1 ELSE 1 END AS rownum,
@ROWKEY := CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IF(t.queue, t.queue, ''), '@', IF(t.port, t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) as rowkey,
t.base_ts, t.site, t.lag, t.port, t.queue, t.periodic_time, t.octets_forwarded, t.rate, t.octets_dropped
FROM ( STMT_1) AS t JOIN (SELECT @ROWNUM:=0, @ROWKEY:='') AS p
*** STMT_3 ***:
SELECT * FROM ( STMT_2 ) AS i WHERE rownum <= 10
*** STMT_4 ***:
create temporary table trunktrending as STMT_3
- or -
create table trunktrending as STMT_3
This is the full text of STMT_4:
create temporary table trunktrending as SELECT * FROM ( SELECT @ROWNUM := CASE WHEN @ROWKEY = CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IFNULL(t.queue, ''), '@', IFNULL(t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) THEN @ROWNUM + 1 ELSE 1 END AS rownum, @ROWKEY := CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IF(t.queue, t.queue, ''), '@', IF(t.port, t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) as rowkey, t.base_ts, t.site, t.lag, t.port, t.queue, t.periodic_time, t.octets_forwarded, t.rate, t.octets_dropped FROM ( SELECT ((cast(c.time_recorded as date) + interval hour(c.time_recorded) hour) + interval (floor(((minute(c.time_recorded) + 7) / 15)) * 15) minute) AS base_ts, r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, null AS queue, AVG(c.periodic_time) AS periodic_time, SUM(c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded) AS octets_forwarded, SUM(c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded) / AVG(c.periodic_time) AS rate, SUM(c.in_profile_octets_dropped + c.out_of_profile_octets_dropped) AS octets_dropped FROM (service_combined_network_ingress_octets r JOIN service_combined_network_ingress_octets_periodic c ON ((r.id = c.id))) WHERE r.time_recorded >= STR_TO_DATE('2016-10-01', '%Y-%m-%d') AND DAYOFWEEK(r.time_recorded) IN (2,3,4,5,6) AND DATE_FORMAT(r.time_recorded, '%H:%i') BETWEEN '08:00' AND '20:00' AND 1=1 AND (ISNULL(r.suspect) OR (r.suspect = FALSE)) GROUP BY base_ts, site, lag, port, queue ORDER BY site, lag, port, queue, DATE_FORMAT(base_ts, '%Y-%m-%d'), rate desc) AS t JOIN (SELECT @ROWNUM:=0, @ROWKEY:='') AS p) AS i WHERE rownum <= 10
These are samples of SELECT statement and CREATE TABLE ... AS SELECT statement, the difference of the two CREATE TABLE ... AS SELECT statements is, one SELECT statement has the LIMIT 10 clause, the other does not,
mysql> SELECT * FROM ( SELECT @ROWNUM := CASE WHEN @ROWKEY = CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IFNULL(t.queue, ''), '@', IFNULL(t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) THEN @ROWNUM + 1 ELSE 1 END AS rownum, @ROWKEY := CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IF(t.queue, t.queue, ''), '@', IF(t.port, t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) as rowkey, t.base_ts, t.site, t.lag, t.port, t.queue, t.periodic_time, t.octets_forwarded, t.rate, t.octets_dropped FROM ( SELECT ((cast(c.time_recorded as date) + interval hour(c.time_recorded) hour) + interval (floor(((minute(c.time_recorded) + 7) / 15)) * 15) minute) AS base_ts, r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, null AS queue, AVG(c.periodic_time) AS periodic_time, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) AS octets_forwarded, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) / AVG(c.periodic_time) AS rate, SUM(COALESCE(c.in_profile_octets_dropped + c.out_of_profile_octets_dropped, c.octets_dropped)) AS octets_dropped FROM (service_combined_network_egress_octets r JOIN service_combined_network_egress_octets_periodic c ON ((r.id = c.id))) WHERE r.time_recorded >= STR_TO_DATE('2016-10-01', '%Y-%m-%d') AND DAYOFWEEK(r.time_recorded) IN (2,3,4,5,6) AND DATE_FORMAT(r.time_recorded, '%H:%i') BETWEEN '08:00' AND '20:00' AND (ISNULL(r.suspect) OR (r.suspect = FALSE)) GROUP BY base_ts, site, lag, port, queue ORDER BY site, lag, port, queue, DATE_FORMAT(base_ts, '%Y-%m-%d'), rate desc) AS t JOIN (SELECT @ROWNUM:=0, @ROWKEY:='') AS p) AS i WHERE rownum <= 10 limit 10;
+--------+---------------------------------+---------------------+---------------+------+-------+-------+---------------+------------------+----------------+----------------+
| rownum | rowkey | base_ts | site | lag | port | queue | periodic_time | octets_forwarded | rate | octets_dropped |
+--------+---------------------------------+---------------------+---------------+------+-------+-------+---------------+------------------+----------------+----------------+
| 1 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 19:45:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 105102491154 | 116780545.7267 | 0 |
| 2 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 19:30:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 98761467968 | 109734964.4089 | 0 |
| 3 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 10:45:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 96715541909 | 107461713.2322 | 0 |
| 4 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 10:30:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 96278107024 | 106975674.4711 | 0 |
| 5 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 11:00:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 95300297379 | 105889219.3100 | 0 |
| 6 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 09:15:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 92374906753 | 102638785.2811 | 0 |
| 7 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 10:15:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 90550554994 | 100611727.7711 | 0 |
| 8 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 10:00:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 90204160249 | 100226844.7211 | 0 |
| 9 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 20:00:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 88249418862 | 98054909.8467 | 0 |
| 10 | 2016-12-14@1/1/1@@209.148.255.1 | 2016-12-14 09:30:00 | 209.148.255.1 | | 1/1/1 | NULL | 900.0000 | 87781313684 | 97534792.9822 | 0 |
+--------+---------------------------------+---------------------+---------------+------+-------+-------+---------------+------------------+----------------+----------------+
10 rows in set, 6483 warnings (17.19 sec)
mysql>
mysql> create temporary table trunktrending as SELECT * FROM ( SELECT @ROWNUM := CASE WHEN @ROWKEY = CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IFNULL(t.queue, ''), '@', IFNULL(t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) THEN @ROWNUM + 1 ELSE 1 END AS rownum, @ROWKEY := CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IF(t.queue, t.queue, ''), '@', IF(t.port, t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) as rowkey, t.base_ts, t.site, t.lag, t.port, t.queue, t.periodic_time, t.octets_forwarded, t.rate, t.octets_dropped FROM ( SELECT ((cast(c.time_recorded as date) + interval hour(c.time_recorded) hour) + interval (floor(((minute(c.time_recorded) + 7) / 15)) * 15) minute) AS base_ts, r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, null AS queue, AVG(c.periodic_time) AS periodic_time, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) AS octets_forwarded, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) / AVG(c.periodic_time) AS rate, SUM(COALESCE(c.in_profile_octets_dropped + c.out_of_profile_octets_dropped, c.octets_dropped)) AS octets_dropped FROM (service_combined_network_egress_octets r JOIN service_combined_network_egress_octets_periodic c ON ((r.id = c.id))) WHERE r.time_recorded >= STR_TO_DATE('2016-10-01', '%Y-%m-%d') AND DAYOFWEEK(r.time_recorded) IN (2,3,4,5,6) AND DATE_FORMAT(r.time_recorded, '%H:%i') BETWEEN '08:00' AND '20:00' AND (ISNULL(r.suspect) OR (r.suspect = FALSE)) GROUP BY base_ts, site, lag, port, queue ORDER BY site, lag, port, queue, DATE_FORMAT(base_ts, '%Y-%m-%d'), rate desc) AS t JOIN (SELECT @ROWNUM:=0, @ROWKEY:='') AS p) AS i WHERE rownum <= 10;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '1/11/1'
mysql>
mysql> create temporary table trunktrending as (SELECT * FROM ( SELECT @ROWNUM := CASE WHEN @ROWKEY = CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IFNULL(t.queue, ''), '@', IFNULL(t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) THEN @ROWNUM + 1 ELSE 1 END AS rownum, @ROWKEY := CONCAT(DATE_FORMAT(t.base_ts, '%Y-%m-%d'), IF(t.queue, t.queue, ''), '@', IF(t.port, t.port, ''), '@', IFNULL(t.lag, ''), '@', t.site) as rowkey, t.base_ts, t.site, t.lag, t.port, t.queue, t.periodic_time, t.octets_forwarded, t.rate, t.octets_dropped FROM ( SELECT ((cast(c.time_recorded as date) + interval hour(c.time_recorded) hour) + interval (floor(((minute(c.time_recorded) + 7) / 15)) * 15) minute) AS base_ts, r.monitored_object_site_id AS site, r.lag_port AS lag, r.port_id AS port, null AS queue, AVG(c.periodic_time) AS periodic_time, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) AS octets_forwarded, SUM(COALESCE((c.in_profile_octets_forwarded + c.out_of_profile_octets_forwarded), c.octets_forwarded)) / AVG(c.periodic_time) AS rate, SUM(COALESCE(c.in_profile_octets_dropped + c.out_of_profile_octets_dropped, c.octets_dropped)) AS octets_dropped FROM (service_combined_network_egress_octets r JOIN service_combined_network_egress_octets_periodic c ON ((r.id = c.id))) WHERE r.time_recorded >= STR_TO_DATE('2016-10-01', '%Y-%m-%d') AND DAYOFWEEK(r.time_recorded) IN (2,3,4,5,6) AND DATE_FORMAT(r.time_recorded, '%H:%i') BETWEEN '08:00' AND '20:00' AND (ISNULL(r.suspect) OR (r.suspect = FALSE)) GROUP BY base_ts, site, lag, port, queue ORDER BY site, lag, port, queue, DATE_FORMAT(base_ts, '%Y-%m-%d'), rate desc) AS t JOIN (SELECT @ROWNUM:=0, @ROWKEY:='') AS p) AS i WHERE rownum <= 10 limit 10);
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '1/11/1'
mysql>
Subject
Views
Written By
Posted
672
December 19, 2016 10:21PM
752
December 20, 2016 07:15AM
573
December 20, 2016 12:58PM
752
December 20, 2016 09:47AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.