0
Update t_ro2EX 
    inner join t_lX3pw on t_lX3pw.id = t_ro2EX.id
    set t_ro2EX.column_bcde = concat_ws('----', t_lX3pw.column_5, t_lX3pw.column_7) 
where t_lX3pw.column_20 > 0;

I wrote this query and it does not seem to finish.

The schema:

Both t_lX3pw and t_ro2EX have about 80,000 rows. The ids are the same, it is just csv data split in multiple tables. I am trying to concat a couple of columns and put them into a single column delimted by '----'

I get the following error:

Lock wait timeout exceeded; try restarting transaction

What is the problem with the query?

Ranjith Ramachandra
  • 10,399
  • 14
  • 59
  • 96
  • 1
    Suggestions to look at processes running: http://stackoverflow.com/questions/26342855/update-query-lock-wait-timeout-exceeded Suggestions to get off of MySQL and move to a proper database like Postgres http://stackoverflow.com/questions/19458960/mysql-error-code-1205-lock-wait-timeout-during-update-with-inner-join... well there is a suggestion to increase the timeout in the answers there too. – JNevill May 11 '15 at 13:40
  • haha. It was much easier to do this with PostreSQL as it took no time. I was trying to benchmark MySQL against PostgreSQL for the same query as we are choosing database right now. Suggestion is welcome :D – Ranjith Ramachandra May 11 '15 at 13:42

2 Answers2

0

Do you use innodb?

Linux: In mysql configuration (/etc/my.cnf or /etc/mysql/my.cnf), insert / edit this line

innodb_lock_wait_timeout = 50

peterpeterson
  • 1,315
  • 2
  • 14
  • 38
0

Try this way:

UPDATE t_ro2EX 
    INNER JOIN t_lX3pw 
    ON t_lX3pw.id = t_ro2EX.id
      AND t_lX3pw.column_20 > 0
    SET t_ro2EX.column_bcde = concat_ws('----', t_lX3pw.column_5, t_lX3pw.column_7);

Could be faster and more efficient.

Alex
  • 16,739
  • 1
  • 28
  • 51