0

Hello I have trouble running mysql update cross databases. MySQL UPDATE query hangs with error - Lock wait timeout exceeded; try restarting transaction. I know similar questions have been already raised but could not find any working solution. I am using this query inside of the PHP script. BUt when I run it within mysql server it does the same thing. When I kill the process, it completes. Here is what hangs. PS: This started like 2 days ago, before that, the query ran without problem. Temp table is filled only when admin pushes there data from html form. 'Teams to esc' table grows like 20 rows per day.

UPDATE query - 

UPDATE escalations.teams_to_esc 
JOIN domguard.temp_table ON escalations.teams_to_esc.ticket_number = domguard.temp_table.task_number 
AND 
escalations.teams_to_esc.team = domguard.temp_table.team
SET 
escalations.teams_to_esc.`status` = domguard.temp_table.`status`, 
escalations.teams_to_esc.`wiw_assigned` = domguard.temp_table.`person` 
WHERE 
(escalations.teams_to_esc.team,escalations.teams_to_esc.ticket_number) IN 
(SELECT domguard.temp_table.team, domguard.temp_table.task_number 
FROM domguard.temp_table);

First table - temp_table 

CREATE TABLE `temp_table` (
    `ID` INT(32) NOT NULL AUTO_INCREMENT,
    `task_number` INT(10) NULL DEFAULT '0',
    `type` VARCHAR(50) NULL DEFAULT '0',
    `assign_date` DATE NULL DEFAULT NULL,
    `team` VARCHAR(50) NULL DEFAULT NULL,
    `person` VARCHAR(50) NULL DEFAULT NULL,
    `task` VARCHAR(50) NULL DEFAULT NULL,
    `comment` TEXT NULL,
    `short_text` VARCHAR(500) NULL DEFAULT NULL,
    `delay_comment` VARCHAR(500) NULL DEFAULT NULL,
    `color` VARCHAR(500) NULL DEFAULT NULL,
    `status` VARCHAR(500) NULL DEFAULT NULL,
    `end_time` TIME NULL DEFAULT NULL,
    `tel_it` INT(2) NULL DEFAULT '0',
    `co_allocation` VARCHAR(500) NULL DEFAULT '0',
    `co_allocation_text` VARCHAR(500) NULL DEFAULT '0',
    `delay_code_text` VARCHAR(500) NULL DEFAULT '0',
    PRIMARY KEY (`ID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2483
;

Second table

CREATE TABLE `teams_to_esc` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `esc` INT(11) NOT NULL,
    `team` VARCHAR(500) NOT NULL,
    `ticket_number` VARCHAR(500) NOT NULL,
    `closed_time` DATE NOT NULL,
    `checked` VARCHAR(500) NULL DEFAULT NULL,
    `reaction_from_tl` VARCHAR(50) NULL DEFAULT NULL,
    `status` VARCHAR(50) NULL DEFAULT NULL,
    `wiw_assigned` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`ID`),
    INDEX `esc` (`esc`),
    CONSTRAINT `teams_to_esc_ibfk_1` FOREIGN KEY (`esc`) REFERENCES `main_table` (`ID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2670
;

=== ERROR INVESTIGATING ===

Output from SHOW processlist;

17107338    root    localhost   \N  Query   1278    Sending data    UPDATE escalations.teams_to_esc JOIN domguard.temp_table ON 
escalations.teams_to_esc.ticket_number

Output from Transaction settings

SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation;
marhyno
  • 677
  • 1
  • 8
  • 20
  • How large are the two tables involved here? Note that having several join conditions can really slow down a query. – Tim Biegeleisen Oct 22 '18 at 07:38
  • Temp table is around - 2500 rows - Teams_to_esc - 2500 rows also – marhyno Oct 22 '18 at 07:39
  • "Lock wait timeout exceeded" means that *another* query is interfering/blocking your query, see e.g. [this](https://stackoverflow.com/q/6000336) to debug it. We cannot really tell you which query it is, since we only see one query. You may want to look into (and/or add to your question) whatever query you killed in *"When I kill the process, it completes."* and/or whatever the innodb status tells you. Also: isn't your join condition the same as the `where in`? And in general, adding indexes on your tables could help, although for 2500 rows, that's probably not the culprit here. – Solarflare Oct 22 '18 at 08:37

0 Answers0