795598 - Mass change to 500 bugs resulted in "DBD::mysql::db do failed: Got a packet bigger than 'max_allowed_packet' bytes"

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Ed Morley [:emorley]]

Was just mass changing (marking WFM and adding a comment) approx 500 bugs.

Part-way through I got a partially loaded "send mails to..." page, and at the bottom:
{
DBD::mysql::db do failed: Got a packet bigger than 'max_allowed_packet' bytes [for Statement "UPDATE bugs_fulltext SET comments = ?, comments_noprivate = ? WHERE bug_id = ?"] 
}

Comment 1

[:glob ✱]

full error:

pp-app-bugs03: [Sat Sep 29 07:38:30 2012] [error] DBD::mysql::db do failed: Got a packet bigger than 'max_allowed_packet' bytes [for Statement "UPDATE bugs_fulltext SET comments = ?, comments_noprivate = ? WHERE bug_id = ?"] at .../Bugzilla/Bug.pm line 1056.


it looks like the length all the comments on a single bug, joined with "\n", exceeds max_allowed_packet.  i'm not overly surprised that an 'orange bug' cleanup would trigger this.

i suspect the fix here would be to simply increase max_allowed_packet, but i'd also like to find the problematic bug.

sheeri: are you able to tell us what max_allowed_packet is currently set to?

sheeri: can you also run the following queries -- these are the bugs ed updated on the 29th.

set session group_concat_max_len = 640000;
select * from
(
select bug_id, length(group_concat(' ', thetext)) size
from longdescs
where bug_id in (
 284594, 403199, 418300, 421065, 428712, 438871, 441133, 443825, 451090,
 455091, 469514, 470420, 471529, 472677, 474961, 481202, 481207, 483779,
 484402, 486580, 487402, 487717, 487857, 488045, 488601, 489285, 489560,
 490062, 490517, 491735, 492986, 493995, 497053, 497602, 501034, 501960,
 502646, 504626, 505572, 505650, 506187, 506521, 506589, 507864, 509410,
 509527, 510128, 511738, 515153, 515407, 518274, 518317, 518497, 518534,
 519446, 521367, 521375, 522662, 522833, 525625, 528138, 529898, 530538,
 531510, 531882, 533218, 534277, 535445, 535490, 535684, 535730, 535880,
 539002, 539334, 539364, 540806, 540980, 542143, 542148, 542580, 542949,
 543429, 544015, 544863, 547079, 548015, 548682, 549018, 549624, 550044,
 551108, 552008, 552027, 552718, 554137, 554754, 556861, 558497, 558577,
 558579, 558658, 558705, 559272, 559454, 559954, 559957, 559981, 560152,
 560420, 560763, 560779, 560900, 561028, 561303, 561350, 561381, 561454,
 561676, 561767, 562333, 563399, 563663, 563997, 564229, 564603, 564709,
 564711, 565280, 566049, 566067, 566797, 567290, 567367, 569299, 570228,
 570402, 571022, 571420, 571747, 571825, 572546, 572932, 572934, 573361,
 574326, 575578, 575914, 575948, 576206, 577409, 577411, 578114, 578134,
 578135, 579139, 579246, 579248, 579543, 579548, 579588, 580987, 581239,
 581734, 581924, 582163, 582821, 583449, 583676, 584368, 584396, 584723,
 585022, 585025, 585213, 585929, 586020, 586295, 586422, 586613, 587263,
 587603, 587699, 588176, 588743, 589578, 590658, 591898, 591933, 592402,
 592989, 593096, 593278, 594073, 594168, 595334, 595368, 595417, 595685,
 596161, 596603, 596912, 597365, 597943, 598369, 598577, 598808, 599069,
 600417, 600973, 601181, 602345, 602469, 602736, 602772, 602783, 603539,
 603842, 604356, 604526, 604755, 604868, 605082, 605316, 605377, 605392,
 605564, 605664, 605716, 605728, 605844, 605925, 605953, 606008, 606188,
 606208, 606384, 606554, 606684, 607028, 607072, 607101, 607300, 607507,
 607523, 607543, 607727, 607929, 609198, 609267, 609773, 609964, 610062,
 610176, 610181, 610225, 610619, 610786, 610993, 611131, 611229, 611528,
 611808, 611810, 611936, 612040, 612158, 612781, 612783, 613708, 613882,
 614483, 614646, 614806, 615020, 615268, 615303, 615635, 616285, 616395,
 616515, 616710, 616851, 617520, 618531, 618926, 619436, 619687, 619744,
 619952, 620642, 621002, 621008, 621197, 621949, 622267, 622310, 622628,
 623085, 623091, 623465, 623951, 624550, 624724, 624915, 624965, 625182,
 625997, 626119, 626770, 627021, 627462, 627507, 627523, 628090, 628263,
 628538, 628617, 629228, 629282, 629287, 629288, 629430, 629591, 629614,
 629643, 629685, 629854, 630098, 630469, 630566, 630728, 631179, 631799,
 632117, 632765, 633073, 633384, 633557, 633559, 633567, 633703, 633718,
 633780, 634533, 634578, 634613, 634813, 634971, 635066, 635479, 635480,
 635487, 635545, 637237, 638267, 639268, 641719, 641849, 644471, 645267,
 645955, 646862, 646905, 647091, 647133, 647642, 648487, 650691, 650697,
 650698, 650707, 651034, 651615, 652739, 653943, 654078, 654826, 655336,
 655495, 655577, 655585, 656861, 657841, 657847, 658190, 658513, 658744,
 658745, 658802, 658897, 658899, 658920, 660599, 660645, 661096, 661756,
 661996, 662800, 663481, 666471, 666683, 666710, 667433, 667434, 667436,
 667594, 667884, 668086, 668412, 668471, 669232, 669811, 670180, 670309,
 670744, 671464, 672186, 672385, 672896, 673255, 673448, 673560, 674242,
 674244, 674396, 674552, 674588, 675605, 676870, 677000, 677096, 677139,
 677390, 678531, 678596, 679609, 679993, 681032, 681035, 681038, 681076,
 681144, 681146, 681152, 681162, 681167, 681331, 681334, 681855, 682141,
 682390, 683163, 683211, 683392, 684608, 686377, 686724, 686791, 686826,
 687031, 687255, 688052, 688577, 688650, 688713, 688718, 689318, 689320,
 689430, 689960, 690475, 691165, 691459, 691620, 691659, 691670, 692717,
 692721, 693497, 693528, 693918, 694391, 695606, 695607, 695608, 695684,
 696417, 696453, 696670, 696671, 696672, 696673, 696674, 698209, 698642,
 699048, 699333, 699550, 699994, 700095, 700146, 700396, 701922, 702373,
 702863, 702872, 703201, 703742, 703805, 703826, 703840, 704367, 704748,
 705937, 705941, 706282, 706284, 708340, 708371, 708861, 708960, 710061,
 710070, 710101, 710873, 710965, 711152, 711155, 711262, 711279, 711870,
 712033, 712111, 712119, 713533, 716800, 717855, 717857, 718263, 720065,
 721033, 722222, 722617, 723786, 723789, 723790, 724166, 724173, 724193,
 726065, 726400, 727160, 729013, 730091, 730187, 730194, 731334, 731706,
 738890, 745386, 774844, 775982, 779753, 786126, 795539, 795569, 795598,
 795624
)
group by bug_id
) t
order by size desc


my money's on bug 542580.

once you've found the top bug, please increase group_concat_max_len until we have the real size of the concatenated comments.


thanks!

Comment 2

[Shyam Mani [:fox2mike]]

Talked to glob, this can wait till a DBA is available.

Comment 3

[Matt Pressman [:mpressman]]

max_allowed_packet is set to 32MB
group_concat_max_len is set to 1024

Comment 4

[Matt Pressman [:mpressman]]

bug 542580 exceeds 16M in the group_concat query from above

Comment 5

[Matt Pressman [:mpressman]]

Attachment: Query results

Comment 6

[:glob ✱]

thanks matt.

bugzilla doesn't use group_concat to concatenate the query (it's joined in perl), however we update both comments and comments_noprivate with the same query .. so our query ends up at least slightly more than 32932372 byes, which is pushing the max_allowed_packet limit.

what are the DBAs thoughts on increasing max_allowed_packet to 64mb?  (i don't know what negative impact, if any, this would have on mysql).

Comment 7

[Matt Pressman [:mpressman]]

I am not opposed to increasing it. It seems that this isn't an issue that comes up often, so it shouldn't have much impact. I'll increase the cluster to 64MB as it will also require setting the slaves as well. I'll update once this is complete.

Comment 8

[Matt Pressman [:mpressman]]

Ok, I updated prod and staging with a 64MB max_allowed_packet. I still need to modify puppet to permanently hold the changes, but you should be good to go now.

Comment 9

[Matt Pressman [:mpressman]]

puppet has been updated to hold the changes after a server restart

Comment 10

[:glob ✱]

thanks mpressman :)