1

So basically, I have this current query :

UPDATE act AS a
INNER JOIN blok AS b
ON b.fav_pat = a.pat_id
SET a.blok_id = b.id

Because of the volumn of data i have, its currently timing out. is there a way around to avoid the time out without modifying db config ?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
ShaxoLudoz
  • 31
  • 3
  • blob is a reserved word and the published query will not syntax. Please publish table definitions so we can see the indexes and the output from an explain. – P.Salmon May 29 '22 at 14:33
  • 1
    What's causing it to time out? Sounds like a client issue. A query that is active and executing doesn't time out by itself. Do you mean a lock wait timeout? – Bill Karwin May 29 '22 at 17:04
  • yes its a lock wait timeout – ShaxoLudoz May 29 '22 at 20:58
  • 1
    It's possible the UPDATE is not optimized because you don't have the right indexes to support the join. P.Salmon asked for the table definitions. This means you should run `SHOW CREATE TABLE ` for each table, `act` and `blok`, and add these to your question above. This is to let us know what indexes, if any, currently exist in the table. Are you ready to provide this information? – Bill Karwin May 29 '22 at 21:03

1 Answers1

1

The package you use does its best to allow any incomplete operation to be entirely rolled back using the host RDBMS's transaction semantics. That means it is designed to do update operations like the one you showed us in an ACID-compliant single transaction.

If the tables involved are large (millions of rows or more) the transactions can be very large. They can make your MySQL server thrash, spilling transaction logs to disk or SSD. Committing those transaction logs can take a very long time. You didn't mention row counts, but if they are large is is possible that flyway is not the right tool for this job.

Your lock timeout hints that you are doing this operation on a database with other concurrent activity. You may want to do it on an otherwise quiet database for best results.

You can increase the lock wait timeout by doing this.

show variables like 'innodb_lock_wait_timeout'; -- previous vale
SET GLOBAL innodb_lock_wait_timeout = 300; -- five min

Then, perhaps, try again, just before sunrise on a holiday or another quiet time. More information here.

Consider restoring the lock timeout to its previous value when your flyway job is done.

You can also consider doing your update in batches, for example 1000 rows at a time. But flyway doesn't seem to support that. If you go that route you can ask another question.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thanks for this awesome response, i've temporarly increased the lock_wait_timeout to make sure the script goes through, and revert it to his initial state. I would have liked to avoid modifying any db config, but your response confort me on doing it this way. – ShaxoLudoz May 31 '22 at 11:19