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.
CREATE TEMPORARY TABLE AS SELECT ...
--> only used once per query.WITH .. AS SELECT ...
--> multiple times in that one SELECT.