Delete data from large sized database using Laravel and mysql

Question

In my project, a table has around 13 million rows and approximately 43 Gb of size. I tried to delete the first 1000 records using laravel delete method.But the execution takes more time and trow exception for me. The exception is

Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction (SQL: delete from `demo_table` where `datetime` <= 2020-06-31 00:00:00 order by `id` asc limit 1000)

 at r/laravel/framework/src/Illuminate/Database/Connection.php:664
   660|         // If an exception occurs when attempting to run a query, we'll format the error
   661|         // message to include the bindings with SQL, which will make this exception a
   662|         // lot more helpful to the developer instead of just the database's errors.
   663|         catch (Exception $e) {
 > 664|             throw new QueryException(
   665|                 $query, $this->prepareBindings($bindings), $e
   666|             );
   667|         }
   668|

have you checked https://stackoverflow.com/questions/5836623/getting-lock-wait-timeout-exceeded-try-restarting-transaction-even-though-im.? — Kamlesh Paul, Sep 23 '20 at 10:04
`mysql> set innodb_lock_wait_timeout=100` like this you can increase time and `mysql> show variables like 'innodb_lock_wait_timeout';` — Kamlesh Paul, Sep 23 '20 at 10:04

score 0 · Answer 1 · answered Sep 23 '20 at 10:08

you need to try add additional or change mysql configuration my.ini in Windows or my.cnf in Linux or Mac bellow [mysqld]

[mysqld]
wait_timeout=NumberValue
interactive_timeout = NumberValue

the value in seconds. after you change it, you need to restart mysql services

score 0 · Answer 2 · answered Sep 23 '20 at 10:15

0

You can handle it with nextToken or you can Use Queue for running thread in background

answered Sep 23 '20 at 10:15

Haseeb Javed

44
4

Delete data from large sized database using Laravel and mysql

2 Answers2