MySQL Erreur 1205

Bonjour à tous,

je post aujourd'hui pour demander un petit coup de main sur un problème MySQL. Malgré mes recherches sur le net je n'arrive pas a solutionner le soucis.

Le contexte: nous avons un base de donnée MySQL avec des millions d'enregistrement. Je fais le ménage tous les 2 ans en exportant la base et en l'important dans une base d'archive (sur un autre serveur).
Une fois l'archive effectuée, je fais du DELETE de masse (requête qui dure 9/10 heures). Il y a eu un problème d'espace sur le serveur et MySQL attendait que de l'espace se libère pour continuer sa requête.
Bref, d’après mes calculs nous devions être a 75% de DELETE effectués lorsque que le problème est apparu. Malheureusement pour moi, l'admin du serveur a annulée la requête, redémarré le serveur et nettoyé les fichiers logs (nom_serveur-bin.00XX).

Problèmes: tous les enregistrements sont encore présent dans la table. Lorsque j'essaie de faire un DELETE d'un de ces enregistrements, j'obtiens l'erreur 1205 (Lock wait timeout exceeded).
J'ai regardé avec un "show processlist" si il y avait un thread bloquant, ce n'est pas le cas et je ne trouve pas d'autre solution. Vous trouverez ci-dessous le résultat du "show engine innodb status" :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255

===================================== 2020-01-24 11:09:21 0xbb8 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 14 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 6416 srv_active, 0 srv_shutdown, 0 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 3534091 --Thread 5112 has waited at btr0sea.cc line 1197 for 0 seconds the semaphore: X-lock on RW-latch at 0000015CDCABB028 created in file btr0sea.cc line 202 a writer (thread id 6872) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.cc line 1101 Last time write locked in file G:\ade\build\sb_0-37170812-1575922901.99\mysql-8.0.19\storage\innobase\btr\btr0sea.cc line 1197 OS WAIT ARRAY INFO: signal count 1909366 RW-shared spins 468780, rounds 1126789, OS waits 656976 RW-excl spins 2022124, rounds 85805081, OS waits 2849993 RW-sx spins 594, rounds 17510, OS waits 560 Spin rounds per wait: 2.40 RW-shared, 42.43 RW-excl, 29.48 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 170025 Purge done for trx's n:o < 169996 undo n:o < 0 state: running but idle History list length 4 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 282972207017376, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 282972207018224, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 282972207022464, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 282972207021616, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 282972207016528, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 168250, ACTIVE 6642 sec recovered trx ROLLING BACK 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 47628997 -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (write thread) I/O thread 7 state: wait Windows aio (write thread) I/O thread 8 state: wait Windows aio (write thread) I/O thread 9 state: complete io for buf page (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 1; buffer pool: 1 3743843 OS file reads, 10373001 OS file writes, 942222 OS fsyncs 434.47 reads/s, 16384 avg bytes/read, 1469.61 writes/s, 143.92 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 3, free list len 14054, seg size 14058, 3 merges merged operations: insert 45, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276707, node heap has 3603 buffer(s) Hash table size 276707, node heap has 3770 buffer(s) Hash table size 276707, node heap has 4735 buffer(s) Hash table size 276707, node heap has 0 buffer(s) Hash table size 276707, node heap has 0 buffer(s) Hash table size 276707, node heap has 0 buffer(s) Hash table size 276707, node heap has 1 buffer(s) Hash table size 276707, node heap has 3864 buffer(s) 102964.50 hash searches/s, 23766.52 non-hash searches/s --- LOG --- Log sequence number 526483417383 Log buffer assigned up to 526483417383 Log buffer completed up to 526483416870 Log written up to 526483416870 Log flushed up to 526483416870 Added dirty pages up to 526483416870 Pages flushed up to 526456627340 Last checkpoint at 526456627340 7289208 log i/o's done, 1033.79 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 1098907648 Dictionary memory allocated 485518 Buffer pool size 65536 Free buffers 8103 Database pages 41456 Old database pages 15175 Modified db pages 2590 Pending reads 0 Pending writes: LRU 3, flush list 73, single page 0 Pages made young 8274, not young 1924731429 0.00 youngs/s, 0.00 non-youngs/s Pages read 3743623, created 159, written 3005314 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 795 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 41456, unzip_LRU len: 0 I/O sum[255376]:cur[2664], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 8192 Free buffers 1020 Database pages 5187 Old database pages 1895 Modified db pages 317 Pending reads 0 Pending writes: LRU 1, flush list 0, single page 0 Pages made young 1024, not young 239877624 0.00 youngs/s, 0.00 non-youngs/s Pages read 469083, created 8, written 378472 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 793 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5187, unzip_LRU len: 0 I/O sum[31922]:cur[333], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 8192 Free buffers 969 Database pages 5237 Old database pages 1914 Modified db pages 313 Pending reads 0 Pending writes: LRU 1, flush list 0, single page 0 Pages made young 900, not young 241556179 0.00 youngs/s, 0.00 non-youngs/s Pages read 469342, created 2, written 377870 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 740 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5237, unzip_LRU len: 0 I/O sum[31922]:cur[333], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 8192 Free buffers 1030 Database pages 5153 Old database pages 1883 Modified db pages 319 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 914, not young 240114468 0.00 youngs/s, 0.00 non-youngs/s Pages read 467524, created 13, written 375942 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 795 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5153, unzip_LRU len: 0 I/O sum[31922]:cur[333], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 8192 Free buffers 1025 Database pages 5146 Old database pages 1879 Modified db pages 431 Pending reads 0 Pending writes: LRU 0, flush list 73, single page 0 Pages made young 859, not young 236110382 0.00 youngs/s, 0.00 non-youngs/s Pages read 465656, created 1, written 372851 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 891 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5146, unzip_LRU len: 0 I/O sum[31922]:cur[333], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 8192 Free buffers 1032 Database pages 5175 Old database pages 1892 Modified db pages 245 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1101, not young 239780438 0.00 youngs/s, 0.00 non-youngs/s Pages read 469782, created 1, written 376850 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 957 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5175, unzip_LRU len: 0 I/O sum[31922]:cur[333], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 8192 Free buffers 1024 Database pages 5175 Old database pages 1892 Modified db pages 340 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1004, not young 244612477 0.00 youngs/s, 0.00 non-youngs/s Pages read 467839, created 64, written 375166 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 957 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5175, unzip_LRU len: 0 I/O sum[31922]:cur[333], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 8192 Free buffers 983 Database pages 5202 Old database pages 1928 Modified db pages 332 Pending reads 0 Pending writes: LRU 1, flush list 0, single page 0 Pages made young 1157, not young 242716480 0.00 youngs/s, 0.00 non-youngs/s Pages read 467934, created 65, written 374855 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 898 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5202, unzip_LRU len: 0 I/O sum[31922]:cur[333], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 8192 Free buffers 1020 Database pages 5181 Old database pages 1892 Modified db pages 293 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1315, not young 239963381 0.00 youngs/s, 0.00 non-youngs/s Pages read 466463, created 5, written 373308 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 385 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5181, unzip_LRU len: 0 I/O sum[31922]:cur[333], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=1412, Main thread ID=0000000000000334 , state=sleeping Number of rows inserted 16, updated 0, deleted 10, read 1344843 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 1, updated 315, deleted 0, read 6554 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================

---

Si quelqu'un a une idée de ce que je pourrais faire je suis preneur.

Merci d'avance pour votre aide.

Dewey.

Bonjour,

il semblerait que le serveur ai rendu la main sur les enregistrements, je peux maintenant les supprimer.
Je pense que c’était simplement une question de délai, vu le nombre d'enregistrement il a fallu attendre quelques heures.

je passe le sujet en résolu.

Salut dewey01.

Je pense que votre approche de la suppression de masse n'est pas bonne.
Si vous devez effectuer de l'archivage sur un critère, autant créer des tables de partitions.
Cela sera plus simple et plus rapide à faire puisque vous pouvez recopier ces fichiers en dehors du serveur mysql.

D'autre part, si les tables de partitions ne sont pas ce qui vous intéresse, vous devez envisager de faire des suppressions avec un point de reprises (ou de sauvegarde).
Cela consiste à créer un traitement qui va supprimer N lignes, valider la suppression et stocker un point de reprises.
Cela permet de mieux gérer vos plantages et de ne pas perdre du temps inutilement.
En cas de plantage, vous repartez sur le dernier point de reprise valide.
Il vous faut évaluer N pour que ce traitement ne vous soit pas trop contraignant.
Ainsi vous pourrez l'automatiser.

Et enfin, effectuer une optimisation de vos tables pour récupérer de la place et reconstituer vos index.

@+