2

I have an SQL SELECT statement of the form:

SELECT a.date, a.count FROM (
  SELECT d.date, x.count + y.count - z.count AS count
  FROM date_table d
  LEFT JOIN (
    SELECT date, count FROM caluculated_table_x
  ) x ON d.date = x.date 
  LEFT JOIN (
    SELECT date, count caluculated_table_y
  ) y ON d.date = y.date 
  LEFT JOIN (
    SELECT date, count caluculated_table_z
  ) z ON d.date = z.date
) a ON a.date = d.date;

The SELECT works just fine, taking roughly 10s to complete. However, when I try to use it in a CREATE TABLE/UPDATE/INSERT INTO statement, e.g.:

CREATE TABLE count_summary
SELECT...;

I get "Error Code: 2013. Lost connection to MySQL server during query" after 600s. Any idea what might be causing this issue or how I might diagnose the problem?

James Clubbe
  • 21
  • 1
  • Is this the actual query you use or you simplified it to post here? How many rows are returned? – ypercubeᵀᴹ Feb 16 '18 at 22:12
  • Simplified. The calculated tables join two or three tables a piece to produce counts of specific actions on a given day, joining to a specified list of dates from date_table. The query produces 1164 rows. I have tried update/inserting with other more basic tables, with similar data, and that worked just fine. – James Clubbe Feb 16 '18 at 22:30
  • Is the same behaviour observed if you run it in 2 statements? CREATE TABLE t (<columns>); INSERT INTO t SELECT ...; instead of one? – ypercubeᵀᴹ Feb 16 '18 at 22:33
  • Correct. It runs all command up until the CREATE statement, and then stalls exactly as the CREATE TABLE ... SELECT ... ; – James Clubbe Feb 16 '18 at 22:36
  • I asked wha happens if you run two statements. One CREATE and one INSERT. – ypercubeᵀᴹ Feb 16 '18 at 22:42
  • Yes, the same behavior occurs. I meant to say it performs the CREATE, then stalls at the INSERT. – James Clubbe Feb 16 '18 at 22:49
  • The query is flawed -- a.date = d.date cannot work because d.date is not visible outside there. – Rick James May 19 '18 at 01:11

0 Answers0