Create temp table timeout.

[expired user #8393]'s profile image [expired user #8393] posted 8 years ago in Running SQL scripts Permalink
Hi.

I'm using HeidiSQL 9.3.0.4997 on windows 64bit against mysql 5.6.12. Also using MySQL Workbench.

Using mysql cli and MySQL Workbench, queries run fine.

Using HeidiSQL, a recent update seems to have broken query.

This is my query ...

SET @BookingRef = 'D58GWM35';
DROP TABLE IF EXISTS TempOrders;
DROP TABLE IF EXISTS TempOrdersItems;
CREATE TEMPORARY TABLE TempOrders SELECT o.orderID, o.companyID, o.deviceID, o.sellersUserID FROM orders o WHERE originalref = @BookingRef;
CREATE TEMPORARY TABLE TempOrdersItems SELECT oi.ordersItemsID, oi.orderID, oi.branchID, oi.itemID, oi.discountID FROM TempOrders o INNER JOIN orders_items oi ON o.orderID = oi.orderID;
SELECT o.* FROM TempOrders tmpo INNER JOIN orders o ON tmpo.orderID = o.orderID;
SELECT oi.* FROM TempOrdersItems tmpoi INNER JOIN orders_items oi ON tmpoi.ordersItemsID = oi.ordersItemsID;
SELECT c.*  FROM TempOrders o INNER JOIN companies c ON o.companyID = c.companyID;
SELECT od.* FROM TempOrders o INNER JOIN orders_discounts od ON o.orderID = od.orderID;
SELECT os.* FROM TempOrders o INNER JOIN orders_surcharges os ON o.orderID = os.orderID;
SELECT p.*  FROM TempOrders o INNER JOIN payments p ON o.orderID = p.orderID;
SELECT d.*  FROM TempOrders o INNER JOIN devices d ON o.deviceID = d.deviceID;
SELECT u.*  FROM TempOrders o INNER JOIN xusers u ON o.sellersUserID = u.userID;
SELECT DISTINCT b.* FROM TempOrdersItems oi INNER JOIN branches b ON oi.branchID = b.branchID;
SELECT i.*   FROM TempOrdersItems oi INNER JOIN items i ON oi.itemID = i.itemID;
SELECT t.*   FROM TempOrdersItems oi INNER JOIN tickets t ON oi.itemID = t.ticketID;
SELECT p.*   FROM TempOrdersItems oi INNER JOIN products p ON oi.itemID = p.productID;
SELECT b.*   FROM TempOrdersItems oi INNER JOIN bundles b ON oi.itemID = b.bundleID;
SELECT d.*   FROM TempOrdersItems oi INNER JOIN discounts d ON oi.discountID = d.discountID;
SELECT vie.* FROM TempOrdersItems oi INNER JOIN view_items_extended vie ON oi.itemID = vie.itemID;
SELECT bi.*  FROM TempOrdersItems oi INNER JOIN bundled_items bi ON oi.itemID = bi.bundleID;
SELECT oa.*  FROM TempOrders o INNER JOIN orders_adjustments oa ON o.orderID IN (oa.adjustsID, oa.adjustedByID);
SELECT oia.* FROM TempOrdersItems oi INNER JOIN orders_items_adjustments oia WHERE oi.ordersItemsID IN (oia.adjustsID, oia.adjustedByID);
DROP TABLE IF EXISTS TempOrders;
DROP TABLE IF EXISTS TempOrdersItems;


The query fails to complete the first temporary table creation.

Instead I get 2 popups.

First shows ...

SQL Error (1205): Lock wait timeout exceeded; try restarting transaction


Second shows ...

Your query produced 2 warnings
Warnings from last query:
Error: Lock wait timeout exceeded; try restarting transaction



Same response if I run the query as a batch or as one at a time. The query is unchanged for several months and works find from the mysql CLI and from workbench.

Happy to assist in diagnosing the issue, but I can see nothing untoward on my side.

Using workbench to monitor the interaction, it is showing the "sending data" state and there are no locks.

I've restarted mysql and all tools. No change in result.

Regards,

Richard Quadling.

Please login to leave a reply, or register at first.