MySQL Forums :: InnoDB :: "create table as select ..." failed with unknown reason


Advanced Search

Re: "create table as select ..." failed with unknown reason
Posted by: harry sheng ()
Date: December 20, 2016 09:47AM

OK, the cause is the IF() function in STMT_2.

I have IFNULL(t.queue, '') and IFNULL(t.port, ''); and IF(t.queue, t.queue, '') and IF(t.port, t.port, '') in the 1st and 2nd lines of STMT_2.

I changed the IF() in the 2nd line to IFNULL(), all works fine.

Here is the full text of STMT_4 which creates and populates the temporary table as expected:

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'), IFNULL(t.queue, ''), '@', IFNULL(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, null 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-08-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

Options: ReplyQuote


Subject Views Written By Posted
"create table as select ..." failed with unknown reason 295 harry sheng 12/19/2016 07:17PM
Re: "create table as select ..." failed with unknown reason 164 Peter Brawley 12/19/2016 10:21PM
Re: "create table as select ..." failed with unknown reason 172 harry sheng 12/20/2016 07:15AM
Re: "create table as select ..." failed with unknown reason 141 Peter Brawley 12/20/2016 12:58PM
Re: "create table as select ..." failed with unknown reason 187 harry sheng 12/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.