Page MenuHomePhabricator

duplicate key problems
Closed, ResolvedPublic

Description

1root@neodymium:~$ while read host port; do echo $host $port; while read db; do yes | ./software/dbtools/osc_host.sh --host=$host --port=$port --db=$d
2b --table=page --method=ddl --no-replicate "ADD COLUMN page_lang varbinary(35) DEFAULT NULL"; done < s4.dblist; done < s4.hosts
3dbstore2002.codfw.wmnet 3306
4--------------
5mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
6
7Connection id: 1150905
8Current database:
9Current user: root@10.64.32.20
10SSL: Not in use
11Current pager: stdout
12Using outfile: ''
13Using delimiter: ;
14Server version: 5.5.5-10.0.28-MariaDB MariaDB Server
15Protocol version: 10
16Connection: dbstore2002.codfw.wmnet via TCP/IP
17Server characterset: binary
18Db characterset: binary
19Client characterset: utf8
20Conn. characterset: utf8
21TCP port: 3306
22Uptime: 9 days 4 hours 14 min 13 sec
23
24Threads: 6 Questions: 424338181 Slow queries: 1 Opens: 1269445 Flush tables: 1 Open tables: 50000 Queries per second avg: 535.204
25--------------
26
27Host : dbstore2002.codfw.wmnet
28Port : 3306
29Databases : commonswiki
30Table : page
31Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
32method : ddl
33pt dry args : --recurse=0 --set-vars=sql_log_bin=off
34pt args : --recurse=0 --set-vars=sql_log_bin=off
35ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
36analyze : 0
37
38host: dbstore2002.codfw.wmnet, database: commonswiki
39SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
40dbstore2001.codfw.wmnet 3306
41--------------
42mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
43
44Connection id: 393399
45Current database:
46Current user: root@10.64.32.20
47SSL: Not in use
48Current pager: stdout
49Using outfile: ''
50Using delimiter: ;
51Server version: 5.5.5-10.0.28-MariaDB MariaDB Server
52Protocol version: 10
53Connection: dbstore2001.codfw.wmnet via TCP/IP
54Server characterset: binary
55Db characterset: binary
56Client characterset: utf8
57Conn. characterset: utf8
58TCP port: 3306
59Uptime: 3 days 5 hours 52 min 57 sec
60
61Threads: 4 Questions: 209544408 Slow queries: 2 Opens: 21037 Flush tables: 1 Open tables: 20812 Queries per second avg: 747.366
62--------------
63
64Host : dbstore2001.codfw.wmnet
65Port : 3306
66Databases : commonswiki
67Table : page
68Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
69method : ddl
70pt dry args : --recurse=0 --set-vars=sql_log_bin=off
71pt args : --recurse=0 --set-vars=sql_log_bin=off
72ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
73analyze : 0
74
75host: dbstore2002.codfw.wmnet, database: commonswiki
76SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary
77(35) DEFAULT NULL
78dbstore2001.codfw.wmnet 3306
79--------------
80mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
81
82Connection id: 393399
83Current database:
84Current user: root@10.64.32.20
85SSL: Not in use
86Current pager: stdout
87Using outfile: ''
88Using delimiter: ;
89Server version: 5.5.5-10.0.28-MariaDB MariaDB Server
90Protocol version: 10
91Connection: dbstore2001.codfw.wmnet via TCP/IP
92Server characterset: binary
93Db characterset: binary
94Client characterset: utf8
95Conn. characterset: utf8
96TCP port: 3306
97Uptime: 3 days 5 hours 52 min 57 sec
98
99Threads: 4 Questions: 209544408 Slow queries: 2 Opens: 21037 Flush tables: 1 Open tables: 20812 Queries per second avg: 747.366
100--------------
101
102Host : dbstore2001.codfw.wmnet
103Port : 3306
104Databases : commonswiki
105Table : page
106Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
107method : ddl
108pt dry args : --recurse=0 --set-vars=sql_log_bin=off
109pt args : --recurse=0 --set-vars=sql_log_bin=off
110ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
111analyze : 0
112
113host: dbstore2002.codfw.wmnet, database: commonswiki
114SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary
115(35) DEFAULT NULL
116
117host: dbstore2002.codfw.wmnet, database: commonswiki
118SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary
119(35) DEFAULT NULL
120dbstore2001.codfw.wmnet 3306
121--------------
122mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
123
124Connection id: 393399
125Current database:
126Current user: root@10.64.32.20
127SSL: Not in use
128Current pager: stdout
129Using outfile: ''
130Using delimiter: ;
131Server version: 5.5.5-10.0.28-MariaDB MariaDB Server
132Protocol version: 10
133Connection: dbstore2001.codfw.wmnet via TCP/IP
134Server characterset: binary
135Db characterset: binary
136Client characterset: utf8
137Conn. characterset: utf8
138TCP port: 3306
139Uptime: 3 days 5 hours 52 min 57 sec
140
141Threads: 4 Questions: 209544408 Slow queries: 2 Opens: 21037 Flush tables: 1 Open tables: 20812 Queries per second avg: 747.366
142--------------
143
144Host : dbstore2001.codfw.wmnet
145Port : 3306
146Databases : commonswiki
147Table : page
148Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
149method : ddl
150pt dry args : --recurse=0 --set-vars=sql_log_bin=off
151pt args : --recurse=0 --set-vars=sql_log_bin=off
152ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
153analyze : 0
154
155host: dbstore2001.codfw.wmnet, database: commonswiki
156SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
157ERROR 1062 (23000) at line 1: Duplicate entry '14-TEP70_locomotives_of_Ukraine' for key 'name_title'
158WARNING commonswiki : page encountered problems
159db2065.codfw.wmnet 3306
160--------------
161mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
162
163Connection id: 139751369
164Current database:
165Current user: root@10.64.32.20
166SSL: Not in use
167Current pager: stdout
168Using outfile: ''
169Using delimiter: ;
170Server version: 5.5.5-10.0.22-MariaDB-log MariaDB Server
171Protocol version: 10
172Connection: db2065.codfw.wmnet via TCP/IP
173Server characterset: binary
174Db characterset: binary
175Client characterset: utf8
176Conn. characterset: utf8
177TCP port: 3306
178Uptime: 286 days 2 hours 43 min 58 sec
179
180Threads: 4 Questions: 6301743325 Slow queries: 89 Opens: 740 Flush tables: 1 Open tables: 702 Queries per second avg: 254.922
181--------------
182
183Host : db2065.codfw.wmnet
184Port : 3306
185Databases : commonswiki
186Table : page
187Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
188method : ddl
189pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db2065.codfw.wmnet
190pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db2065.codfw.wmnet
191ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
192analyze : 0
193
194host: db2065.codfw.wmnet, database: commonswiki
195SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
196ERROR 1062 (23000) at line 1: Duplicate entry '53249753' for key 'PRIMARY'
197WARNING commonswiki : page encountered problems
198db2058.codfw.wmnet 3306
199--------------
200mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
201
202Connection id: 52286488
203Current database:
204Current user: root@10.64.32.20
205SSL: Not in use
206Current pager: stdout
207Using outfile: ''
208Using delimiter: ;
209Server version: 5.5.5-10.0.22-MariaDB-log MariaDB Server
210Protocol version: 10
211Connection: db2058.codfw.wmnet via TCP/IP
212Server characterset: binary
213Db characterset: binary
214Client characterset: utf8
215Conn. characterset: utf8
216TCP port: 3306
217Uptime: 286 days 3 hours 43 min 14 sec
218
219Threads: 6 Questions: 4533408382 Slow queries: 37 Opens: 533 Flush tables: 1 Open tables: 528 Queries per second avg: 183.362
220--------------
221
222Host : db2058.codfw.wmnet
223Port : 3306
224Databases : commonswiki
225Table : page
226Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
227method : ddl
228pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db2058.codfw.wmnet
229pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db2058.codfw.wmnet
230ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
231analyze : 0
232
233host: db2058.codfw.wmnet, database: commonswiki
234SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
235ERROR 1062 (23000) at line 1: Duplicate entry '53250362' for key 'PRIMARY'
236WARNING commonswiki : page encountered problems
237db2051.codfw.wmnet 3306
238--------------
239mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
240
241Connection id: 89066123
242Current database:
243Current user: root@10.64.32.20
244SSL: Not in use
245Current pager: stdout
246Using outfile: ''
247Using delimiter: ;
248Server version: 5.5.5-10.0.22-MariaDB-log MariaDB Server
249Protocol version: 10
250Connection: db2051.codfw.wmnet via TCP/IP
251Server characterset: binary
252Db characterset: binary
253Client characterset: utf8
254Conn. characterset: utf8
255TCP port: 3306
256Uptime: 286 days 5 hours 22 min 18 sec
257
258Threads: 4 Questions: 4870864218 Slow queries: 283 Opens: 538 Flush tables: 1 Open tables: 510 Queries per second avg: 196.963
259--------------
260
261Host : db2051.codfw.wmnet
262Port : 3306
263Databases : commonswiki
264Table : page
265Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
266method : ddl
267pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db2051.codfw.wmnet
268pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db2051.codfw.wmnet
269ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
270analyze : 0
271
272host: db2051.codfw.wmnet, database: commonswiki
273SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
274db2044.codfw.wmnet 3306
275--------------
276mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
277
278Connection id: 29352279
279Current database:
280Current user: root@10.64.32.20
281SSL: Not in use
282Current pager: stdout
283Using outfile: ''
284Using delimiter: ;
285Server version: 5.5.5-10.0.22-MariaDB-log MariaDB Server
286Protocol version: 10
287Connection: db2044.codfw.wmnet via TCP/IP
288Server characterset: binary
289Db characterset: binary
290Client characterset: utf8
291Conn. characterset: utf8
292TCP port: 3306
293Uptime: 286 days 6 hours 22 min 39 sec
294
295Threads: 5 Questions: 3982386105 Slow queries: 293 Opens: 531 Flush tables: 1 Open tables: 554 Queries per second avg: 161.012
296--------------
297
298Host : db2044.codfw.wmnet
299Port : 3306
300Databases : commonswiki
301Table : page
302Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
303method : ddl
304pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db2044.codfw.wmnet
305pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db2044.codfw.wmnet
306ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
307analyze : 0
308
309host: db2044.codfw.wmnet, database: commonswiki
310SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
311db2037.codfw.wmnet 3306
312--------------
313mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
314
315Connection id: 29656210
316Current database:
317Current user: root@10.64.32.20
318SSL: Not in use
319Current pager: stdout
320Using outfile: ''
321Using delimiter: ;
322Server version: 5.5.5-10.0.22-MariaDB-log MariaDB Server
323Protocol version: 10
324Connection: db2037.codfw.wmnet via TCP/IP
325Server characterset: binary
326Db characterset: binary
327Client characterset: utf8
328Conn. characterset: utf8
329TCP port: 3306
330Uptime: 287 days 2 hours 47 min 54 sec
331
332Threads: 5 Questions: 3998279045 Slow queries: 304 Opens: 452 Flush tables: 1 Open tables: 475 Queries per second avg: 161.176
333--------------
334
335Host : db2037.codfw.wmnet
336Port : 3306
337Databases : commonswiki
338Table : page
339Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
340method : ddl
341pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db2037.codfw.wmnet
342pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db2037.codfw.wmnet
343ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
344analyze : 0
345
346host: db2037.codfw.wmnet, database: commonswiki
347SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
348db2019.codfw.wmnet 3306
349--------------
350mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
351
352Connection id: 11098170
353Current database:
354Current user: root@10.64.32.20
355SSL: Not in use
356Current pager: stdout
357Using outfile: ''
358Using delimiter: ;
359Server version: 5.5.5-10.0.22-MariaDB-log MariaDB Server
360Protocol version: 10
361Connection: db2019.codfw.wmnet via TCP/IP
362Server characterset: binary
363Db characterset: binary
364Client characterset: utf8
365Conn. characterset: utf8
366TCP port: 3306
367Uptime: 216 days 11 hours 10 min 58 sec
368
369Threads: 14 Questions: 2613961132 Slow queries: 28 Opens: 484 Flush tables: 1 Open tables: 485 Queries per second avg: 139.764
370--------------
371
372Host : db2019.codfw.wmnet
373Port : 3306
374Databases : commonswiki
375Table : page
376Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
377method : ddl
378pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db2019.codfw.wmnet
379pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db2019.codfw.wmnet
380ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
381analyze : 0
382
383host: db2019.codfw.wmnet, database: commonswiki
384SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
385dbstore1001.eqiad.wmnet 3306
386--------------
387mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
388
389Connection id: 13224146
390Current database:
391Current user: root@10.64.32.20
392SSL: Not in use
393Current pager: stdout
394Using outfile: ''
395Using delimiter: ;
396Server version: 5.5.5-10.0.22-MariaDB MariaDB Server
397Protocol version: 10
398Connection: dbstore1001.eqiad.wmnet via TCP/IP
399Server characterset: binary
400Db characterset: binary
401Client characterset: utf8
402Conn. characterset: utf8
403TCP port: 3306
404Uptime: 148 days 4 hours 43 min 44 sec
405
406Threads: 5 Questions: 14733630677 Slow queries: 25083 Opens: 1535777 Flush tables: 1 Open tables: 50000 Queries per second avg: 1150.685
407--------------
408
409Host : dbstore1001.eqiad.wmnet
410Port : 3306
411Databases : commonswiki
412Table : page
413Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
414method : ddl
415pt dry args : --recurse=0 --set-vars=sql_log_bin=off
416pt args : --recurse=0 --set-vars=sql_log_bin=off
417ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
418analyze : 0
419
420host: dbstore1001.eqiad.wmnet, database: commonswiki
421SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
422dbstore1002.eqiad.wmnet 3306
423--------------
424mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
425
426Connection id: 656141934
427Current database:
428Current user: root@10.64.32.20
429SSL: Not in use
430Current pager: stdout
431Using outfile: ''
432Using delimiter: ;
433Server version: 5.5.5-10.0.22-MariaDB MariaDB Server
434Protocol version: 10
435Connection: dbstore1002.eqiad.wmnet via TCP/IP
436Server characterset: binary
437Db characterset: binary
438Client characterset: utf8
439Conn. characterset: utf8
440TCP port: 3306
441Uptime: 119 days 5 hours 14 min 37 sec
442
443Threads: 7 Questions: 14017495864 Slow queries: 210515 Opens: 256227 Flush tables: 1 Open tables: 49999 Queries per second avg: 1360.858
444--------------
445
446Host : dbstore1002.eqiad.wmnet
447Port : 3306
448Databases : commonswiki
449Table : page
450Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
451method : ddl
452pt dry args : --recurse=0 --set-vars=sql_log_bin=off
453pt args : --recurse=0 --set-vars=sql_log_bin=off
454ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
455analyze : 0
456
457host: dbstore1002.eqiad.wmnet, database: commonswiki
458SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
459labsdb1001.eqiad.wmnet 3306
460--------------
461mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
462
463Connection id: 1820599
464Current database:
465Current user: root@10.64.32.20
466SSL: Not in use
467Current pager: stdout
468Using outfile: ''
469Using delimiter: ;
470Server version: 5.5.5-10.0.15-MariaDB Source distribution
471Protocol version: 10
472Connection: labsdb1001.eqiad.wmnet via TCP/IP
473Server characterset: binary
474Db characterset: binary
475Client characterset: utf8
476Conn. characterset: utf8
477TCP port: 3306
478Uptime: 1 day 5 hours 26 min 59 sec
479
480Threads: 48 Questions: 170486009 Slow queries: 11360 Opens: 249801 Flush tables: 1 Open tables: 9985 Queries per second avg: 1608.070
481--------------
482
483Host : labsdb1001.eqiad.wmnet
484Port : 3306
485Databases : commonswiki
486Table : page
487Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
488method : ddl
489pt dry args : --recurse=0 --set-vars=sql_log_bin=off
490pt args : --recurse=0 --set-vars=sql_log_bin=off
491ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
492analyze : 0
493
494host: labsdb1001.eqiad.wmnet, database: commonswiki
495SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
496ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
497WARNING commonswiki : page encountered problems
498labsdb1003.eqiad.wmnet 3306
499--------------
500mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
501
502Connection id: 222986
503Current database:
504Current user: root@10.64.32.20
505SSL: Not in use
506Current pager: stdout
507Using outfile: ''
508Using delimiter: ;
509Server version: 5.5.5-10.0.22-MariaDB MariaDB Server
510Protocol version: 10
511Connection: labsdb1003.eqiad.wmnet via TCP/IP
512Server characterset: binary
513Db characterset: binary
514Client characterset: utf8
515Conn. characterset: utf8
516TCP port: 3306
517Uptime: 5 hours 26 min 44 sec
518
519Threads: 46 Questions: 24732411 Slow queries: 1932 Opens: 120467 Flush tables: 1 Open tables: 10000 Queries per second avg: 1261.600
520--------------
521
522Host : labsdb1003.eqiad.wmnet
523Port : 3306
524Databases : commonswiki
525Table : page
526Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
527method : ddl
528pt dry args : --recurse=0 --set-vars=sql_log_bin=off
529pt args : --recurse=0 --set-vars=sql_log_bin=off
530ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
531analyze : 0
532
533host: labsdb1003.eqiad.wmnet, database: commonswiki
534SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
535db1069.eqiad.wmnet 3314
536--------------
537mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
538
539Connection id: 264072
540Current database:
541Current user: root@10.64.32.20
542SSL: Not in use
543Current pager: stdout
544Using outfile: ''
545Using delimiter: ;
546Server version: 5.5.5-10.0.22-MariaDB-log MariaDB Server
547Protocol version: 10
548Connection: db1069.eqiad.wmnet via TCP/IP
549Server characterset: binary
550Db characterset: binary
551Client characterset: utf8
552Conn. characterset: utf8
553TCP port: 3314
554Uptime: 15 days 3 hours 46 min 31 sec
555
556Threads: 6 Questions: 304288514 Slow queries: 0 Opens: 181 Flush tables: 1 Open tables: 168 Queries per second avg: 232.353
557--------------
558
559Host : db1069.eqiad.wmnet
560Port : 3314
561Databases : commonswiki
562Table : page
563Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
564method : ddl
565pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1069.eqiad.wmnet
566pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1069.eqiad.wmnet
567ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
568analyze : 0
569
570host: db1069.eqiad.wmnet, database: commonswiki
571SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
572db1053.eqiad.wmnet 3306
573--------------
574mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
575
576Connection id: 293088424
577Current database:
578Current user: root@10.64.32.20
579SSL: Not in use
580Current pager: stdout
581Using outfile: ''
582Using delimiter: ;
583Server version: 5.5.5-10.0.27-MariaDB MariaDB Server
584Protocol version: 10
585Connection: db1053.eqiad.wmnet via TCP/IP
586Server characterset: binary
587Db characterset: binary
588Client characterset: utf8
589Conn. characterset: utf8
590TCP port: 3306
591Uptime: 30 days 50 min 22 sec
592
593Threads: 10 Questions: 1991321200 Slow queries: 7623 Opens: 949 Flush tables: 1 Open tables: 807 Queries per second avg: 767.361
594--------------
595
596Host : db1053.eqiad.wmnet
597Port : 3306
598Databases : commonswiki
599Table : page
600Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
601method : ddl
602pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1053.eqiad.wmnet
603pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1053.eqiad.wmnet
604ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
605analyze : 0
606
607host: db1053.eqiad.wmnet, database: commonswiki
608SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
609db1056.eqiad.wmnet 3306
610--------------
611mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
612
613Connection id: 1499077500
614Current database:
615Current user: root@10.64.32.20
616SSL: Not in use
617Current pager: stdout
618Using outfile: ''
619Using delimiter: ;
620Server version: 5.5.5-10.0.22-MariaDB-log MariaDB Server
621Protocol version: 10
622Connection: db1056.eqiad.wmnet via TCP/IP
623Server characterset: binary
624Db characterset: binary
625Client characterset: utf8
626Conn. characterset: utf8
627TCP port: 3306
628Uptime: 176 days 6 hours 1 min 24 sec
629
630Threads: 13 Questions: 14833826040 Slow queries: 52638 Opens: 2750 Flush tables: 1 Open tables: 2508 Queries per second avg: 974.109
631--------------
632
633Host : db1056.eqiad.wmnet
634Port : 3306
635Databases : commonswiki
636Table : page
637Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
638method : ddl
639pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1056.eqiad.wmnet
640pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1056.eqiad.wmnet
641ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
642analyze : 0
643
644host: db1056.eqiad.wmnet, database: commonswiki
645SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
646ERROR 1062 (23000) at line 1: Duplicate entry '53255065' for key 'PRIMARY'
647WARNING commonswiki : page encountered problems
648db1059.eqiad.wmnet 3306
649--------------
650mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
651
652Connection id: 1322233603
653Current database:
654Current user: root@10.64.32.20
655SSL: Not in use
656Current pager: stdout
657Using outfile: ''
658Using delimiter: ;
659Server version: 5.5.5-10.0.23-MariaDB-log MariaDB Server
660Protocol version: 10
661Connection: db1059.eqiad.wmnet via TCP/IP
662Server characterset: binary
663Db characterset: binary
664Client characterset: utf8
665Conn. characterset: utf8
666TCP port: 3306
667Uptime: 146 days 22 hours 28 min 31 sec
668
669Threads: 6 Questions: 9903020734 Slow queries: 13670 Opens: 1124 Flush tables: 1 Open tables: 914 Queries per second avg: 780.053
670--------------
671
672Host : db1059.eqiad.wmnet
673Port : 3306
674Databases : commonswiki
675Table : page
676Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
677method : ddl
678pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1059.eqiad.wmnet
679pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1059.eqiad.wmnet
680ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
681analyze : 0
682
683host: db1059.eqiad.wmnet, database: commonswiki
684SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
685ERROR 1062 (23000) at line 1: Duplicate entry '53257028' for key 'PRIMARY'
686WARNING commonswiki : page encountered problems
687db1064.eqiad.wmnet 3306
688--------------
689mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
690
691Connection id: 99606920
692Current database:
693Current user: root@10.64.32.20
694SSL: Not in use
695Current pager: stdout
696Using outfile: ''
697Using delimiter: ;
698Server version: 5.5.5-10.0.23-MariaDB-log MariaDB Server
699Protocol version: 10
700Connection: db1064.eqiad.wmnet via TCP/IP
701Server characterset: binary
702Db characterset: binary
703Client characterset: utf8
704Conn. characterset: utf8
705TCP port: 3306
706Uptime: 30 days 2 hours 40 min 36 sec
707
708Threads: 4 Questions: 1688701001 Slow queries: 6536 Opens: 223 Flush tables: 1 Open tables: 275 Queries per second avg: 649.091
709--------------
710
711Host : db1064.eqiad.wmnet
712Port : 3306
713Databases : commonswiki
714Table : page
715Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
716method : ddl
717pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1064.eqiad.wmnet
718pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1064.eqiad.wmnet
719ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
720analyze : 0
721
722host: db1064.eqiad.wmnet, database: commonswiki
723SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
724db1068.eqiad.wmnet 3306
725--------------
726mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
727
728Connection id: 2981825804
729Current database:
730Current user: root@10.64.32.20
731SSL: Not in use
732Current pager: stdout
733Using outfile: ''
734Using delimiter: ;
735Server version: 5.5.5-10.0.23-MariaDB-log MariaDB Server
736Protocol version: 10
737Connection: db1068.eqiad.wmnet via TCP/IP
738Server characterset: binary
739Db characterset: binary
740Client characterset: utf8
741Conn. characterset: utf8
742TCP port: 3306
743Uptime: 149 days 10 hours 4 min 30 sec
744
745Threads: 11 Questions: 24045903485 Slow queries: 26177 Opens: 1928 Flush tables: 1 Open tables: 1710 Queries per second avg: 1862.598
746--------------
747
748Host : db1068.eqiad.wmnet
749Port : 3306
750Databases : commonswiki
751Table : page
752Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
753method : ddl
754pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1068.eqiad.wmnet
755pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1068.eqiad.wmnet
756ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
757analyze : 0
758
759host: db1068.eqiad.wmnet, database: commonswiki
760SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
761db1081.eqiad.wmnet 3306
762--------------
763mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
764
765Connection id: 2875502331
766Current database:
767Current user: root@10.64.32.20
768SSL: Not in use
769Current pager: stdout
770Using outfile: ''
771Using delimiter: ;
772Server version: 5.5.5-10.0.23-MariaDB-log MariaDB Server
773Protocol version: 10
774Connection: db1081.eqiad.wmnet via TCP/IP
775Server characterset: binary
776Db characterset: binary
777Client characterset: utf8
778Conn. characterset: utf8
779TCP port: 3306
780Uptime: 156 days 8 hours 28 min 10 sec
781
782Threads: 128 Questions: 119898499993 Slow queries: 71080 Opens: 1831 Flush tables: 1 Open tables: 1801 Queries per second avg: 8875.525
783--------------
784
785Host : db1081.eqiad.wmnet
786Port : 3306
787Databases : commonswiki
788Table : page
789Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
790method : ddl
791pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1081.eqiad.wmnet
792pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1081.eqiad.wmnet
793ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
794analyze : 0
795
796host: db1081.eqiad.wmnet, database: commonswiki
797SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
798db1084.eqiad.wmnet 3306
799--------------
800mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
801
802Connection id: 2911870556
803Current database:
804Current user: root@10.64.32.20
805SSL: Not in use
806Current pager: stdout
807Using outfile: ''
808Using delimiter: ;
809Server version: 5.5.5-10.0.23-MariaDB-log MariaDB Server
810Protocol version: 10
811Connection: db1084.eqiad.wmnet via TCP/IP
812Server characterset: binary
813Db characterset: binary
814Client characterset: utf8
815Conn. characterset: utf8
816TCP port: 3306
817Uptime: 156 days 11 hours 30 min 39 sec
818
819Threads: 158 Questions: 120894748466 Slow queries: 89538 Opens: 1784 Flush tables: 1 Open tables: 1748 Queries per second avg: 8942.025
820--------------
821
822Host : db1084.eqiad.wmnet
823Port : 3306
824Databases : commonswiki
825Table : page
826Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
827method : ddl
828pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1084.eqiad.wmnet
829pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1084.eqiad.wmnet
830ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
831analyze : 0
832
833host: db1084.eqiad.wmnet, database: commonswiki
834SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
835ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
836WARNING commonswiki : page encountered problems
837db1091.eqiad.wmnet 3306
838--------------
839mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
840
841Connection id: 2915490854
842Current database:
843Current user: root@10.64.32.20
844SSL: Not in use
845Current pager: stdout
846Using outfile: ''
847Using delimiter: ;
848Server version: 5.5.5-10.0.23-MariaDB-log MariaDB Server
849Protocol version: 10
850Connection: db1091.eqiad.wmnet via TCP/IP
851Server characterset: binary
852Db characterset: binary
853Client characterset: utf8
854Conn. characterset: utf8
855TCP port: 3306
856Uptime: 156 days 10 hours 31 min 30 sec
857
858Threads: 211 Questions: 120768912520 Slow queries: 60822 Opens: 1677 Flush tables: 1 Open tables: 1655 Queries per second avg: 8935.063
859--------------
860
861Host : db1091.eqiad.wmnet
862Port : 3306
863Databases : commonswiki
864Table : page
865Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
866method : ddl
867pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1091.eqiad.wmnet
868pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1091.eqiad.wmnet
869ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
870analyze : 0
871
872host: db1091.eqiad.wmnet, database: commonswiki
873SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
874ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
875WARNING commonswiki : page encountered problems
876db1040.eqiad.wmnet 3306
877--------------
878mysql Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (x86_64) using readline 6.3
879
880Connection id: 313256157
881Current database:
882Current user: root@10.64.32.20
883SSL: Not in use
884Current pager: stdout
885Using outfile: ''
886Using delimiter: ;
887Server version: 5.5.5-10.0.23-MariaDB-log MariaDB Server
888Protocol version: 10
889Connection: db1040.eqiad.wmnet via TCP/IP
890Server characterset: binary
891Db characterset: binary
892Client characterset: utf8
893Conn. characterset: utf8
894TCP port: 3306
895Uptime: 156 days 12 hours 33 min 56 sec
896
897Threads: 217 Questions: 15605950078 Slow queries: 44338 Opens: 1428 Flush tables: 1 Open tables: 1331 Queries per second avg: 1153.975
898--------------
899
900Host : db1040.eqiad.wmnet
901Port : 3306
902Databases : commonswiki
903Table : page
904Alter : ADD COLUMN page_lang varbinary(35) DEFAULT NULL
905method : ddl
906pt dry args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1040.eqiad.wmnet
907pt args : --recurse=0 --set-vars=sql_log_bin=off --check-slave-lag=db1040.eqiad.wmnet
908ddl args : SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0;
909analyze : 0
910
911host: db1040.eqiad.wmnet, database: commonswiki
912SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60; set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL
913ERROR 1062 (23000) at line 1: Duplicate entry '53260621' for key 'PRIMARY'
914WARNING commonswiki : page encountered problems
915

Event Timeline

jcrespo created this task.Nov 18 2016, 8:59 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 18 2016, 8:59 AM
grep ERROR alter.log 
ERROR 1062 (23000) at line 1: Duplicate entry '14-TEP70_locomotives_of_Ukraine' for key 'name_title'
ERROR 1062 (23000) at line 1: Duplicate entry '53249753' for key 'PRIMARY'
ERROR 1062 (23000) at line 1: Duplicate entry '53250362' for key 'PRIMARY'
ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
ERROR 1062 (23000) at line 1: Duplicate entry '53255065' for key 'PRIMARY'
ERROR 1062 (23000) at line 1: Duplicate entry '53257028' for key 'PRIMARY'
ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction
ERROR 1062 (23000) at line 1: Duplicate entry '53260621' for key 'PRIMARY'

The lock wait timeouts are normal, the PKs and name_title are not.

jcrespo moved this task from Triage to In progress on the DBA board.Nov 18 2016, 9:48 AM
jcrespo triaged this task as High priority.Nov 18 2016, 9:52 AM
jcrespo claimed this task.Nov 18 2016, 9:54 AM

With ALGORITH=COPY, it worked without problems:

MariaDB [commonswiki]> SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60;  set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL, ALGORITHM=COPY;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 48619288 rows affected (1 hour 14 min 9.88 sec)
Records: 48619288  Duplicates: 0  Warnings: 0

Are you going to file a bug report to MariaDB?

No, I cannot replicate, and as long as the alter gets reverted, I am not even sure this is a bug.

Although it would be nice to correlate with version numbers.

Mentioned in SAL (#wikimedia-operations) [2016-11-21T10:29:04Z] <jynus> performing blocking schema change on db2065 T151029

Mentioned in SAL (#wikimedia-operations) [2016-11-21T13:56:55Z] <jynus> performing blocking schema change on db2058 T151029

Change 322655 had a related patch set uploaded (by Jcrespo):
Depool db1056 for schema change

https://gerrit.wikimedia.org/r/322655

Change 322655 merged by Jcrespo:
Depool db1056 for schema change

https://gerrit.wikimedia.org/r/322655

Mentioned in SAL (#wikimedia-operations) [2016-11-21T15:52:13Z] <jynus@tin> Synchronized wmf-config/db-eqiad.php: Depool db1056 T151029 (duration: 00m 53s)

Mentioned in SAL (#wikimedia-operations) [2016-11-21T16:07:39Z] <jynus> performing blocking schema change on db1056 (depooled) T151029

Change 322699 had a related patch set uploaded (by Jcrespo):
Depool db1059 to apply schema change

https://gerrit.wikimedia.org/r/322699

Mentioned in SAL (#wikimedia-operations) [2016-11-21T16:54:58Z] <jynus@tin> Synchronized wmf-config/db-eqiad.php: Repool db1056 T151029 (duration: 00m 59s)

Change 322699 merged by jenkins-bot:
Depool db1059 to apply schema change

https://gerrit.wikimedia.org/r/322699

Mentioned in SAL (#wikimedia-operations) [2016-11-22T09:19:35Z] <jynus@tin> Synchronized wmf-config/db-eqiad.php: Depool db1059 T151029 (duration: 00m 49s)

Mentioned in SAL (#wikimedia-operations) [2016-11-22T09:36:48Z] <jynus> performing blocking schema change on db1059 (depooled) T151029

Mentioned in SAL (#wikimedia-operations) [2016-11-22T09:44:08Z] <jynus> performing blocking schema change on db1084 (depooled) T151029

Mentioned in SAL (#wikimedia-operations) [2016-11-22T10:34:18Z] <jynus@tin> Synchronized wmf-config/db-eqiad.php: Repool db1059 & db1084 T151029 (duration: 00m 51s)

Change 322858 had a related patch set uploaded (by Jcrespo):
Depool db1091 to apply blocking schema change

https://gerrit.wikimedia.org/r/322858

Change 322858 merged by jenkins-bot:
Depool db1091 to apply blocking schema change

https://gerrit.wikimedia.org/r/322858

Mentioned in SAL (#wikimedia-operations) [2016-11-22T11:15:34Z] <jynus> performing blocking schema change on db1091 (depooled) T151029

Change 322868 had a related patch set uploaded (by Jcrespo):
Repool db1091

https://gerrit.wikimedia.org/r/322868

Change 322868 merged by jenkins-bot:
Repool db1091

https://gerrit.wikimedia.org/r/322868

Mentioned in SAL (#wikimedia-operations) [2016-11-22T12:05:52Z] <jynus> retrying schema change on db1040 (page) T151029

jcrespo closed this task as Resolved.Nov 22 2016, 2:02 PM

I did the rest of the changes in a blocking way, and I got no error. For the master, I could not do it blocking without a failover or read_only, but of course I tried the same non-blocking query first:

host: db1040.eqiad.wmnet, database: commonswiki
SET SESSION innodb_lock_wait_timeout=1; SET SESSION lock_wait_timeout=60;  set session sql_log_bin=0; alter table page ADD COLUMN page_lang varbinary(35) DEFAULT NULL

This time it worked. I have no idea why, although I suspect it has to do with the innodb_file_per_table=0 tables on some of those. In any case, this is resolved.

jcrespo renamed this task from duplicate key problems on s4 to duplicate key problems.Dec 14 2016, 9:42 PM
jcrespo reopened this task as Open.
jcrespo removed a project: Patch-For-Review.

This is worrying:

MariaDB MARIADB labsdb1001 enwiki > SET GLOBAL innodb_file_per_table = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB MARIADB labsdb1001 enwiki > SET GLOBAL innodb_file_format = Barracuda;
Query OK, 0 rows affected (0.00 sec)

MariaDB MARIADB labsdb1001 enwiki > CREATE TABLE page_new like page;
Query OK, 0 rows affected (0.00 sec)

MariaDB MARIADB labsdb1001 enwiki > ALTER TABLE page_new ADD COLUMN page_lang varbinary(35) DEFAULT NULL, ALGORITHM=COPY;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB MARIADB labsdb1001 enwiki > INSERT INTO page_new (page_id, page_namespace, page_title, page_restrictions, page_counter, page_is_redirect, page_is_new, page_random, page_touched, page_links_updated, page_latest, page_len, page_content_model) SELECT page_id, page_namespace, page_title, page_restrictions, page_counter, page_is_redirect, page_is_new, page_random, page_touched, page_links_updated, page_latest, page_len, page_content_model FROM page;
ERROR 1062 (23000): Duplicate entry '0-Feng_Shen_Bang_3D' for key 'name_title'

We can try to:

  1. mysqldump that table and place it somewhere else
  2. try the alter

if it works, it is clearly a bug although we thought that forcing a table rebuild (COPY) always fixed the issue, so it is very worrying if not indeed

Oh, I just read correctly that you are creating AN EMPTY TABLE!!! :o

I have been trying a few different things but I cannot really how is that duplicate key possible.

I am trying to reproduce this issue in a 10.0.28 version with the same data.

It is labsdb1001, it could be a 10.0.15-specific bug.

Mentioned in SAL (#wikimedia-operations) [2016-12-15T10:19:33Z] <jynus> stopping slave on labsdb1001 - s1 to run alter table T151029

This is definitely the table itself. I have tried importing that table (taken with a mysqldump) into a brand new 10.0.28 server and I got

"ERROR 1062 (23000) at line 4362: Duplicate entry '0-Merah_Putih' for key 'name_title'

Plan: stop replication with other server in sync; recreate the table from 0; restart replication.

jcrespo closed this task as Resolved.Jan 30 2017, 3:37 PM
jcrespo reopened this task as Open.Jul 18 2017, 1:36 PM

From db2072 (ENGINE=InnoDB row_format=COMPRESSED KEY_BLOCK_SIZE=8):

change_tag...
ERROR 1062 (23000) at line 1: Duplicate entry '963806525-mobile edit' for key 'ct_rc_id'
linter...
ERROR 1062 (23000) at line 1: Duplicate entry '2-4114522-691-1260' for key 'linter_cat_page_position'

Process has not finished yet.

more errors:

tag_summary...
ERROR 1062 (23000) at line 1: Duplicate entry '964297507' for key 'ts_rc_id'
user...
ERROR 1062 (23000) at line 1: Duplicate entry 'XXXXXXXXXXX' for key 'user_name'
watchlist...
ERROR 1062 (23000) at line 1: Duplicate entry 'XXXXXXXXXXXXXXXXXXXX' for key 'wl_user'

mysqldump && TRUNCATE TABLE $table; ALTER TABLE $table ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 && reimport seems to be doing the trick- no errors on reimport.

Mentioned in SAL (#wikimedia-operations) [2017-07-21T15:16:20Z] <jynus> restarting replication on db2072 after maintenance T151029

jcrespo closed this task as Resolved.Jul 21 2017, 3:24 PM

This might happen again on other hosts- we should report further issues here. The above procedure seems to work. This is probably related to some internal format that is incompatible with some files we have since 5.5 or lower. We may have to do logical dumps when we do complex or important work.

I have fixed duplicate entries on s4 on the following hosts by importing+exporting the tables, after that compression went thru without any issues:
db2073
db2065
For the tables:

linter
page
watchlist

I have fixed duplicate entries on db2075 for:
dewiki.watchlist
wikidatawiki.wb_items_per_site

Change 370863 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-codfw.php: Depool db2045

https://gerrit.wikimedia.org/r/370863

Mentioned in SAL (#wikimedia-operations) [2017-08-09T18:39:40Z] <marostegui> Stop replication on db2045 to fix duplicate keys - T151029

Change 370863 merged by jenkins-bot:
[operations/mediawiki-config@master] db-codfw.php: Depool db2045

https://gerrit.wikimedia.org/r/370863

Fixed duplicate entries on db2045 for:

dewiki.watchlist
wikidatawiki.wb_items_per_site

Mentioned in SAL (#wikimedia-operations) [2017-08-10T14:32:09Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2045 - T151029 (duration: 00m 51s)

Change 371050 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-codfw.php: Depool db2046

https://gerrit.wikimedia.org/r/371050

Change 371050 merged by jenkins-bot:
[operations/mediawiki-config@master] db-codfw.php: Depool db2046

https://gerrit.wikimedia.org/r/371050

Mentioned in SAL (#wikimedia-operations) [2017-08-10T15:05:21Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Depool db2046 - T151029 (duration: 00m 50s)

Mentioned in SAL (#wikimedia-operations) [2017-08-10T15:09:44Z] <marostegui> Stop replication on db2046 to fix duplicate entries - T151029

Marostegui added a comment.EditedAug 11 2017, 1:38 AM

s6 had issues with the following tables (on db2046 and db2076):

frwiki: linter, user_properties, watchlist, wbc_entity_usage (fixed already on db2046)
jawiki: user_properties (fixed already on db2046)
ruwiki: linter,user_properties, wbc_entity_usage (fixed already on db2046)

Mentioned in SAL (#wikimedia-operations) [2017-08-11T11:19:56Z] <marostegui> Stop replication on db2046 to fix duplicate entries - T151029

Mentioned in SAL (#wikimedia-operations) [2017-08-11T12:05:34Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Repool db2046 - T151029 (duration: 00m 48s)

Change 371934 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/mediawiki-config@master] db-eqiad,db-codfw.php: Add db2076

https://gerrit.wikimedia.org/r/371934

Change 371934 merged by jenkins-bot:
[operations/mediawiki-config@master] db-eqiad,db-codfw.php: Add db2076

https://gerrit.wikimedia.org/r/371934

Mentioned in SAL (#wikimedia-operations) [2017-08-14T11:00:35Z] <marostegui@tin> Synchronized wmf-config/db-codfw.php: Add db2076 - T170662 T151029 (duration: 01m 02s)

Mentioned in SAL (#wikimedia-operations) [2017-08-14T11:01:27Z] <marostegui@tin> Synchronized wmf-config/db-eqiad.php: Add db2076 - T170662 T151029 (duration: 00m 47s)

Mentioned in SAL (#wikimedia-operations) [2017-08-16T06:01:18Z] <marostegui> Stop replication on db2076 to fix duplicate entries - T151029

The following tables have been reimported in db2076 and compressed successfully:

  • frwiki: linter, user_properties, watchlist, wbc_entity_usage
  • jawiki: user_properties
  • ruwiki: linter,user_properties, wbc_entity_usage

Mentioned in SAL (#wikimedia-operations) [2017-08-17T05:55:17Z] <marostegui> Stop replication on db2077 to fix duplicate entries - T151029

Mentioned in SAL (#wikimedia-operations) [2017-08-17T06:24:43Z] <marostegui> Stop slave on db2047 to fix duplicate keys - T151029

Fixed issues on db2077 and db2047 so far (still compressing on db2047):

arwiki: linter, tag_summary, wbc_entity_usage
hewiki: change_tag
eswiki: linter

jcrespo added a comment.EditedApr 27 2019, 1:24 PM
root@db2097:/srv$ mysql -A -BN -S /run/mysqld/mysqld.s6.sock -e "select CONCAT(table_schema, '.', table_name) FROM information_schema.tables where table_Schema like '%wik%' and engine='InnoDB' and row_format != 'COMPRESSED'" | head -n 1 | while read table; do echo "$table..."; mysql -S /run/mysqld/mysqld.s6.sock -e "set session sql_log_bin=0; ALTER TABLE $table row_format=COMPRESSED"; done
frwiki.actor...
ERROR 1062 (23000) at line 1: Duplicate entry 'X.X.X.X' for key 'actor_name'

X.X.X.X was an ip. This was on 10.1.38.

:-(

Retrying now with replication stopped. It worked.