0

I have this query I'm in the process of porting over from SQL Server 2019 to MySQL 8.0.29. This error can't reopen table has been driving me insane. This is a very well-known issue with a ton of questions and answers even on this site (I couldn't find a solution for my specific implementation or something similar to it). However, I'd like to get help for my specific case, as I've been hitting a wall for the past 3 days trying to fix this.

The error is Error 1137: Can't reopen table: tmp. It happens after cte4 is inserted into tmp, in the next block of statements. I tried following the MySQL bug report (creating another WITH cte) but this doesn't work because the cte I created is within another CTE so I can't access it outside of the outer cte.

CREATE PROCEDURE `GetLocationAvailability`(
    IN locationID INT,
    IN startDate DATETIME,
    IN endDate DATETIME
)
BEGIN
    DROP TEMPORARY TABLE IF EXISTS tmp;
    DROP TEMPORARY TABLE IF EXISTS tmp2;
    DROP TEMPORARY TABLE IF EXISTS tmp3;
    DROP TEMPORARY TABLE IF EXISTS LocationData;
    
    -- no support for CSVs yet, only single location IDs
    CREATE TEMPORARY TABLE tmp (
        ScheduleEventID INT PRIMARY KEY,
        ScheduleID INT,
        `Name` VARCHAR(400),
        ScheduleStartDate DATETIME,
        ScheduleEndDate DATETIME,
        LeadTime INT,
        LocationID INT,
        ScheduledQuantity INT,
        ProductOrderID INT,
        ProducedQuantity INT,
        CreatedOn DATETIME,
        CreatedBy VARCHAR(100),
        LastModifiedOn DATETIME,
        LastModifiedBy VARCHAR(100),
        ScheduleEventTypeID INT,
        ParentScheduleEventID INT,
        BackgroundColor VARCHAR(510),
        Speed DECIMAL,
        Efficiency DECIMAL,
        MinutesPerShift DECIMAL,
        CustomerID INT
    );
    CREATE TEMPORARY TABLE tmp2 (
        ScheduleEventID INT PRIMARY KEY,
        ScheduleID INT,
        `Name` VARCHAR(400),
        ScheduleStartDate DATETIME,
        ScheduleEndDate DATETIME,
        LeadTime INT,
        LocationID INT,
        ScheduledQuantity INT,
        ProductOrderID INT,
        ProducedQuantity INT,
        CreatedOn DATETIME,
        CreatedBy VARCHAR(100),
        LastModifiedOn DATETIME,
        LastModifiedBy VARCHAR(100),
        ScheduleEventTypeID INT,
        ParentScheduleEventID INT,
        BackgroundColor VARCHAR(510),
        Speed DECIMAL,
        Efficiency DECIMAL,
        MinutesPerShift DECIMAL,
        CustomerID INT
    );
    CREATE TEMPORARY TABLE tmp3 (
        ScheduleEventID INT PRIMARY KEY,
        ScheduleID INT,
        `Name` VARCHAR(400),
        ScheduleStartDate DATETIME,
        ScheduleEndDate DATETIME,
        LeadTime INT,
        LocationID INT,
        ScheduledQuantity INT,
        ProductOrderID INT,
        ProducedQuantity INT,
        CreatedOn DATETIME,
        CreatedBy VARCHAR(100),
        LastModifiedOn DATETIME,
        LastModifiedBy VARCHAR(100),
        ScheduleEventTypeID INT,
        ParentScheduleEventID INT,
        BackgroundColor VARCHAR(510),
        Speed DECIMAL,
        Efficiency DECIMAL,
        MinutesPerShift DECIMAL,
        CustomerID INT
    );
    CREATE TEMPORARY TABLE IF NOT EXISTS LocationData AS (SELECT locationID AS LocationID);
    
    -- get initial dataset
    INSERT INTO tmp
    WITH cte AS (
        SELECT a.*
        FROM scheduleevent a
        WHERE a.ScheduleStartDate > startDate
            AND a.ScheduleEndDate < endDate
            AND a.LocationID = locationID
    ), cte2 AS (-- check to see if there is a time record that starts before the selection and ends in the selected range
        SELECT a.*
        FROM scheduleevent a
        WHERE a.ScheduleStartDate < startDate
            AND a.ScheduleEndDate > startDate
            AND a.LocationID = locationID
    ), cte3 AS (-- check to see if thre is a time record that includes the end date in the selected range
        SELECT a.*
        FROM scheduleevent a
        WHERE a.ScheduleStartDate < endDate
            AND a.ScheduleEndDate > endDate
            AND a.LocationID = locationID
    ), cte4 AS ( -- combine the datasets
        SELECT *
        FROM cte
        
        UNION ALL
        
        SELECT *
        FROM cte2
        
        UNION ALL 
        
        SELECT *
        FROM cte3
    )
    
    SELECT * 
    FROM cte4;
    
    -- merge blocks of time that are contiguous
    INSERT INTO tmp2
    WITH heads
    AS (
        WITH temprev AS (SELECT * FROM tmp)
        SELECT ROW_NUMBER() OVER (
            ORDER BY head.ScheduleStartDate, head.LocationID
        ) PK, head.*
        FROM temprev AS head
        WHERE NOT EXISTS (
            SELECT *
            FROM temprev AS `prev`
            WHERE prev.ScheduleStartDate < head.ScheduleStartDate
                AND head.ScheduleStartDate <= prev.ScheduleEndDate
        )
    ), heads2 AS (SELECT * FROM heads)
    SELECT ROW_NUMBER() OVER (ORDER BY h.ScheduleStartDate) AS PK,
        h.ScheduleStartDate,
        max(t.ScheduleEndDate) AS ScheduleEndDate,
        max(h.LocationID) AS LocationID
    FROM heads2 h
    LEFT JOIN heads2 nh
        ON nh.PK = h.PK + 1
    LEFT JOIN tmp AS t
        ON h.ScheduleStartDate <= t.ScheduleStartDate
        AND (
            t.ScheduleStartDate < nh.ScheduleStartDate
            OR nh.ScheduleStartDate IS NULL
        )
    WHERE h.LocationID > -1
    GROUP BY h.ScheduleStartDate;
    
    -- get the available time slots from the blocked out time
    INSERT INTO tmp3
    WITH cte AS ( -- associate that start and end selection with each location for checking the date range in each record
        SELECT a.LocationID,
            startDate AS StartDT,
            endDate AS EndDt
        FROM tmp2 a 
        GROUP BY a.LocationID
    ), cte2 AS ( -- merge the datasets for the start and end and each record found for a block of time
        SELECT a.*,
            b.ScheduleStartDate,
            b.ScheduleEndDate
        FROM cte a
        INNER JOIN tmp2 b
            ON a.LocationID = b.LocationID
    ), cte3 AS ( -- group schedule by location
        SELECT ROW_NUMBER() OVER (
                PARTITION BY LocationID ORDER BY a.ScheduleStartDate
            ) as rowID,
            a.LocationID,
            a.ScheduleStartDate,
            a.ScheduleEndDate
        FROM cte2 a
    ), cte4 AS ( -- check the start
        SELECT a.LocationID,
            startDate AS ScheduleStartDate,
            a.ScheduleStartDate AS ScheduleEndDate
        FROM cte3 a
        WHERE a.rowID = 1
            AND a.ScheduleStartDate > startDate
    ), cte32 AS (SELECT * FROM cte3), 
    cte5 AS ( -- check records between start and stop
        SELECT a.LocationID,
            a.ScheduleEndDate AS ScheduleStartDate,
            b.ScheduleStartDate AS ScheduleEndDate
        FROM cte3 a
        INNER JOIN cte32 b
            ON a.rowID = b.rowID - 1
            AND a.LocationID = b.LocationID
    ), cte6 AS ( -- check last record near stop
        SELECT a.LocationID,
            max(a.ScheduleEndDate) AS ScheduleStartDate,
            endDate AS ScheduleEndDate,
            max(a.rowID) as rowID
        FROM cte3 a
        WHERE a.ScheduleEndDate < endDate
        GROUP BY a.LocationID
    ), cte7 AS ( -- combine the datasets
        SELECT *
        FROM cte4
        
        UNION ALL 
        
        SELECT *
        FROM cte5
        
        UNION ALL
        
        SELECT a.LocationID,
            a.ScheduleStartDate,
            a.ScheduleEndDate
        FROM cte6 a
    )
    
    SELECT *
    FROM cte7
    ORDER BY LocationID,
        ScheduleStartDate;
        
    -- check for locations that did not return any scheduled slots
    WITH cte AS (
        SELECT LocationID
        FROM LocationData
        WHERE LocationID NOT IN (SELECT LocationID FROM tmp2)
        -- EXCEPT MySQL only supports 8.0.31, ^ workaround
        -- 
        -- SELECT LocationID
        -- FROM tmp2
    ), cte2 AS (
        SELECT a.LocationID,
            startDate AS ScheduleStartDate,
            endDate AS ScheduleEndDate
        FROM cte a
        
        UNION ALL
        
        SELECT *
        FROM tmp3
    )
    
    SELECT a.*
    FROM cte2 a
    ORDER BY a.LocationID,
        a.ScheduleStartDate;

END

I tried replacing

SELECT ROW_NUMBER() OVER (
        ORDER BY head.ScheduleStartDate, head.LocationID
    ) PK, head.*
    FROM tmp AS head
    WHERE NOT EXISTS (
            SELECT *
            FROM tmp AS `prev`
            WHERE prev.ScheduleStartDate < head.ScheduleStartDate
                AND head.ScheduleStartDate <= prev.ScheduleEndDate
        )

with

WITH temprev AS (SELECT * FROM tmp)
        SELECT ROW_NUMBER() OVER (
            ORDER BY head.ScheduleStartDate, head.LocationID
        ) PK, head.*
        FROM temprev AS head
        WHERE NOT EXISTS (
            SELECT *
            FROM temprev AS `prev`
            WHERE prev.ScheduleStartDate < head.ScheduleStartDate
                AND head.ScheduleStartDate <= prev.ScheduleEndDate
        )

but still no go. The MySQL documentation suggests that fix but it doesn't work in my case. I'm at my wit's end.

Based on comments and feedback

Here is my updated version - I removed the temporary tables and used CTEs. This ran once successfully but any other time I ran it after the first time, it stopped the mysql service (on a Windows server)!!

CREATE PROCEDURE `GetLocationAvailability`(
    IN locationID INT,
    IN startDate DATETIME,
    IN endDate DATETIME
)
BEGIN
    DROP TEMPORARY TABLE IF EXISTS LocationData;
    CREATE TEMPORARY TABLE IF NOT EXISTS LocationData AS (SELECT locationID AS LocationID);
    
    -- get initial dataset
    WITH cte AS (
        SELECT a.*
        FROM scheduleevent a
        WHERE a.ScheduleStartDate > startDate
            AND a.ScheduleEndDate < endDate
            AND a.LocationID = locationID
    ), cte2 AS (-- check to see if there is a time record that starts before the selection and ends in the selected range
        SELECT a.*
        FROM scheduleevent a
        WHERE a.ScheduleStartDate < startDate
            AND a.ScheduleEndDate > startDate
            AND a.LocationID = locationID
    ), cte3 AS (-- check to see if thre is a time record that includes the end date in the selected range
        SELECT a.*
        FROM scheduleevent a
        WHERE a.ScheduleStartDate < endDate
            AND a.ScheduleEndDate > endDate
            AND a.LocationID = locationID
    ), cte4 AS ( -- combine the datasets
        SELECT *
        FROM cte
        
        UNION ALL
        
        SELECT *
        FROM cte2
        
        UNION ALL 
        
        SELECT *
        FROM cte3
    ), heads AS (
        SELECT ROW_NUMBER() OVER (
            ORDER BY head.ScheduleStartDate, head.LocationID
        ) PK, head.*
       FROM cte4 AS head
       WHERE NOT EXISTS (
            SELECT *
            FROM cte4 AS `prev`
            WHERE prev.ScheduleStartDate < head.ScheduleStartDate
                AND head.ScheduleStartDate <= prev.ScheduleEndDate
        )
    ), temp2 AS (
        SELECT ROW_NUMBER() OVER (ORDER BY h.ScheduleStartDate) AS PK,
            h.ScheduleStartDate,
            max(t.ScheduleEndDate) AS ScheduleEndDate,
            max(h.LocationID) AS LocationID
        FROM heads h
        LEFT JOIN heads nh
            ON nh.PK = h.PK + 1
        LEFT JOIN cte4 AS t
            ON h.ScheduleStartDate <= t.ScheduleStartDate
            AND (
                t.ScheduleStartDate < nh.ScheduleStartDate
                OR nh.ScheduleStartDate IS NULL
            )
        WHERE h.LocationID > -1
        GROUP BY h.ScheduleStartDate
    ), cte11 AS ( -- associate that start and end selection with each location for checking the date range in each record
        SELECT a.LocationID,
            startDate AS StartDT,
            endDate AS EndDt
        FROM temp2 a 
        GROUP BY a.LocationID
    ), cte12 AS ( -- merge the datasets for the start and end and each record found for a block of time
        SELECT a.*,
            b.ScheduleStartDate,
            b.ScheduleEndDate
        FROM cte11 a
        INNER JOIN temp2 b
            ON a.LocationID = b.LocationID
    ), cte13 AS ( -- group schedule by location
        SELECT ROW_NUMBER() OVER (
                PARTITION BY LocationID ORDER BY a.ScheduleStartDate
            ) as rowID,
            a.LocationID,
            a.ScheduleStartDate,
            a.ScheduleEndDate
        FROM cte12 a
    ), cte14 AS ( -- check the start
        SELECT a.LocationID,
            startDate AS ScheduleStartDate,
            a.ScheduleStartDate AS ScheduleEndDate
        FROM cte13 a
        WHERE a.rowID = 1
            AND a.ScheduleStartDate > startDate
    ), cte15 AS ( -- check records between start and stop
        SELECT a.LocationID,
            a.ScheduleEndDate AS ScheduleStartDate,
            b.ScheduleStartDate AS ScheduleEndDate
        FROM cte13 a
        INNER JOIN cte13 b
            ON a.rowID = b.rowID - 1
            AND a.LocationID = b.LocationID
    ), cte16 AS ( -- check last record near stop
        SELECT a.LocationID,
            max(a.ScheduleEndDate) AS ScheduleStartDate,
            endDate AS ScheduleEndDate,
            max(a.rowID) as rowID
        FROM cte13 a
        WHERE a.ScheduleEndDate < endDate
        GROUP BY a.LocationID
    ), cte17 AS ( -- combine the datasets
        SELECT *
        FROM cte14
        
        UNION ALL 
        
        SELECT *
        FROM cte15
        
        UNION ALL
        
        SELECT a.LocationID,
            a.ScheduleStartDate,
            a.ScheduleEndDate
        FROM cte16 a
    ), temp3 AS (
        SELECT *
        FROM cte17
        ORDER BY LocationID,
            ScheduleStartDate
    ), cte21 AS (-- check for locations that did not return any scheduled slots
        SELECT LocationID
        FROM LocationData
        WHERE LocationID NOT IN (SELECT LocationID FROM temp2)
        -- EXCEPT MySQL only supports 8.0.31, ^ workaround
        -- 
        -- SELECT LocationID
        -- FROM tmp2
    ), cte22 AS (
        SELECT a.LocationID,
            startDate AS ScheduleStartDate,
            endDate AS ScheduleEndDate
        FROM cte21 a
        
        UNION ALL
        
        SELECT *
        FROM temp3
    )
    
    SELECT a.*
    FROM cte22 a
    ORDER BY a.LocationID,
        a.ScheduleStartDate;
END

Is there anything glaring that would cause this behavior?

EDIT 2:

I added an answer that it was caused by unknown unprintable characters at the end of a few lines of the query, but it happened again - I would run the query (from the front end of my application), and then the DB connection would drop because the service was stopped. Here is a snippet from the mysql logs (I don't know if it's just showing the start of the query or if this is the location where it's causing it to shut down):

Timestamp, Thread, Type, Details
, , , Query (18ca90e0030): WITH cte AS (
, , , SELECT a.*
, , , FROM scheduleevent a
, , , WHERE a.ScheduleStartDate > _startDate
, , , AND a.ScheduleEndDate < _endDate
, , , AND a.LocationID = _locationID
, , , ), cte2 AS (-- check to see if there is a time record that starts before the selection and ends in the selected range
, , , SELECT a.*
, , , FROM scheduleevent a
, , , WHERE a.ScheduleStartDate < _startDate
, , , AND a.ScheduleEndDate > _startDate
, , , AND a.LocationID = _locationID
, , , ), cte3 AS (-- check to see if thre is a time record that includes the end date in the selected range
, , , SELECT a.*
, , , FROM scheduleevent a
, , , WHERE a.ScheduleStartDate < _endDate
, , , AND a.ScheduleEndDate > _endDate
, , , AND a.LocationID = _locationID
, , , ), cte4 AS ( -- combine the datasets
, , , SELECT *
, , , FROM cte
, , , 
, , , UNION ALL
, , , 
, , , SELECT *
, , , FROM cte2
, , , 
, , , UNION ALL
, , , 
, , , SELECT *
, , , FROM cte3
, , , ), heads AS (
, , , SELECT ROW_NUMBER() OVER (
, , , ORDER BY head.ScheduleStartDate, head.Locatio
, , , Connection ID (thread ID): 10
, , , Status: NOT_KILLED
, , , The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
, , , information that should help you find out what is causing the crash.
Improve this question
9
  • Temporary table cannot be used more than once in a query. Create static table instead, or use intermediate CTE.
    – Akina
    Nov 10, 2022 at 4:24
  • What would using an intermediate cte look like? Does using a temporary table mean querying from it or only writing to it more than once?
    – sqlcaveman
    Nov 10, 2022 at 5:04
  • CREATE TEMPORARY TABLE AS SELECT ... --> only used once per query. WITH .. AS SELECT ... --> multiple times in that one SELECT.
    – Rick James
    Nov 10, 2022 at 5:34
  • @RickJames can you check the following post below to see if there are any obvious issues with what I'm doing that would cause the service to be shut down? I don't know mysql equivalents from sql server, so I could be way off the mark.
    – sqlcaveman
    Nov 10, 2022 at 16:10
  • "it stopped the mysql service" -- What did the logs say?
    – Rick James
    Nov 10, 2022 at 17:52

1 Answer 1

Reset to default
0

I see locationID being used as an argument, a column name, etc. May I suggest you decrease the confusion for the reader (and for the parser) by spelling them differently? Capitalization differences are not sufficient. I like to add a leading underscore to arguments and local variables.

    FROM LocationData
    WHERE LocationID NOT IN (SELECT LocationID FROM temp2)
    -- EXCEPT MySQL only supports 8.0.31, ^ workaround

--> Either use NOT EXISTS( SELECT ... ) or LEFT JOIN ... WHERE ... IS NULL.

Cut back on the number of CTEs. It looks like many don't need to be CTEs, but could be simple JOINs or maybe "derived tables":

FROM ( SELECT ... ) AS a
JOIN b ON ...

CTE support is relatively new, and might not have been tested with 22 nested CTEs.

File a bug with bugs.mysql.com

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.