I recently solved a persistent database error. As a manager of a lot of different kinds of websites, WordPress seems to be at the heart of the classic, open-source computing environment dilemma: Users have limitless flexibility to install plug-ins or build and modify code of their own. As a result, users have been conditioned to try to install plugins as a solution to every problem. This can cause software conflicts and memory errors.
Out of the box, WordPress security is responsible in their deployment of the core program. You have to intentionally do something to cause a vulnerability. Web security is one of the hats we wear, which involves a pro-active hardening of the webserver environment to protect against comment exploits. Typically we don’t rely on alot of plugins for security, but do it at the webserver and server levels, a couple step deeper than WordPress-based security allows. Plugins can be very helpful to solve or prevent alot of problems, it is true.
So, why was one of our dedicated WordPress virtual hosts going offline intermittently in the middle of the night? This particular host only runs about 10 active WordPress sites, and this error in the logs was reporting the database going offline due to a memory error. Hm…
[client 67.213.118.245:55038] WordPress database error Out of memory (Needed 57148 bytes) for query SELECT wp_comments.* FROM wp_comments WHERE comment_ID IN (38571,38572,38574,38576,38577,38578,38579,38580,38583,38588,38590,38591,38593,38597,38598,38600,38601,38602,38603,38608,38609,38610,38612,38615,38617,38618,38620,38621,38623,38625,38627,38628,38630,38631,38633,38635,38636,38639,38640,38642,38643,38644,38645,38646,38647,38649,38652,38653,38658,38660,38661,38663,38667,38668,38669,38673,38677,38681,38682,38689,38690,38691,38692,38693,38696,38697,38698,38701,38703,38704,38705,38708,38709,38711,38712,38713,38714,38716,38717,38720,38721,38722,38723,38724,38726,38728,38729,38730,38733,38734,38736,38738,38741,38742,38743,38744,38745,38747,38749,38752,38754,38756,38757,38758,38761,38762,38763,38764,38766,38767,38768,38769,38770,38771,38772,38773,38774,38775,38777,38778,38779,38780,38783,38785,38786,38787,38788,38791,38794,38796,38797,38799,38802,38803,38805,38807,38808,38810,38812,38815,38818,38824,38826,38828,38829,38831,38833,38834,38836,38838,38841,38842,38843,38844,38845,38848,38849,38851,38852,38853,38855,38856,38857,38860,38861,38864,38865,38867,38868,38869,38872,38877,38878,38879,38880,38881,38882,38883,38885,38886,38889,38890,38891,38894,38896,38898,38900,38903,38905,38910,38911,38912,38913,38914,38915,38917,38918,38921,38922,38923,38924,38925,38926,38928,38929,38930,38931,38932,38933,38934,38935,38937,38938,38941,38942,38947,38949,38950,38951,38952,38953,38955,38957,38958,38960,38962,38963,38965,38966,38968,38970,38971,38972,38973,38974,38976,38977,38978,38980,38982,38983,38984,38986,38987,38992,38993,38994,38996,38997,38998,38999,39000,39001,39002,39003,39004,39007,39009,39011,39013,39017,39018,39020,39022,39023,39024,39026,39027,39028,39030,39031,39033,39037,39039,39040,39041,39042,39043,39044,39045,39046,39049,39050,39052,39055,39057,39058,39059,39061,39062,39063,39065,39066,39067,39069,39072,39076,39077,39079,39082,39084,39086,39087,39088,39093,39096,39097,39100,39105,39106,39107,39110,39111,39113,39114,39121,39122,39123,39124,39129,39130,39133,39136,39138,39139,39140,39143,39145,39150,39151,39154,39155,39156,39162,39166,39167,39170,39172,39175,39176,39181,39184,39185,39186,39190,39193,39195,39198,39199,39202,39208,39209,39211,39213,39217,39220,39223,39226,39228,39229,39232,39234,39238,39242,39244,39250,39251,39252,39254,39256,39265,39266,39267,39268,39269,39271,39273,39274,39278,39279,39281,39283,39285,39286,39287,39288,39290,39292,39294,39296,39297,39299,39300,39301,39302,39305,39306,39307,39309,39310,39312,39314,39315,39317,39318,39320,39321,39324,39326,39327,39328,39329,39330,39331,39332,39334,39336,39337,39339,39340,39341,39342,39343,39344,39345,39347,39348,39349,39350,39351,39352,39355,39356,39359,39360,39361,39362,39363,39364,39365,39366,39368,39369,39370,39371,39372,39374,39376,39377,39378,39379,39380,39381,39382,39383,39384,39385,39388,39389,39390,39391,39392,39393,39394,39395,39396,39397,39398,39399,39400,39401,39402,39403,39405,39407,39409,39410,39411,39412,39413,39414,39415,39416,39417,39419,39420,39422,39423,39424,39425,39426,39427,39428,39429,39430,39431,39433,39434,39435,39437,39438,39439,39441,39442,39443,39444,39445,39446,39447,39448,39449,39450,39451,39452,39453,39454,39457,39458,39459,39460,39461,39462,39463,39464,39465,39466,39469,39470,39471,39473,39475,39476,39477,39478,39479,39484,39485,39487,39489,39490,39491,39492,39493,39494,39495,39497,39499,39500,39502,39503,39504,39506,39507,39508,39509,39511,39512,39515,39519,39521,39523,39524,39525,39527,39528,39529,39530,39531,39533,39534,39536,39538,39539,39540,39541,39542,39543,39545,39548,39549,39550,39551,39552,39554,39555,39560,39561,39562,39563,39566,39567,39568,39569,39571,39572,39573,39574,39576,39577,39578,39580,39582,39584,39586,39589,39590,39593,39595,39596,39601,39602,39605,39609,39612,39613,39615,39616,39619,39623,39628,39629,39632,39634,39637,39638,39641,39644,39647,39648,39651,39654,39656,39657,39658,39662,39665,39666,39667,39669,39670,39671,39673,39674,39678,39679,39680,39683,39684,39685,39686,39687,39688,39689,39692,39693,39696,39697,39698,39699,39700,39701,39702,39703,39705,39706,39707,39708,39712,39713,39714,39715,39716,39717,39718,39720,39721,39722,39723,39724,39725,39726,39727,39729,39730,39731,39733,39734,39735,39736,39738,39739,39740,39742,39744,39745,39746,39747,39748,39749,39750,39754,39755,39756,39757,39758,39759,39760,39761,39763,39764,39767,39768,39769,39771,39772,39773,39774,39775,39776,39777,39778,39780,39782,39783,39784,39785,39786,39787,39788,39789,39790,39791,39792,39795,39796,39797,39799,39801,39802,39803,39805,39808,39810,39812,39813,39817,39820,39823,39824,39826,39828,39829,39830,39831,39838,39839,39840,39841,39843,39845,39848,39849,39852,39853,39855,39856,39857,39858,39860,39862,39870,39875,39876,39881,39883,39888,39893,39894,39895,39897,39901,39902,39908,39910,39912,39913,39915,39923,39928,39931,39932,39935,39938,39944,39949,39953,39955,39959,39962,39963,39969,39974,39976,39980,39984,39985,39989,39990,39998,40000,40001,40004,40005,40006,40014,40015,40020,40027,40030,40031,40032,40033,40037,40040,40041,40043,40045,40052,40053,40054,40059,40061,40064,40066,40071,40073,40075,40077,40078,40085,40087,40091,40097,40098,40100,40101,40103,40110,40112,40114,40117,40119,40123,40125,40126,40128,40131,40133,40134,40137,40141,40142,40146,40148,40150,40151,40153,40154,40155,40157,40158,40162,40163,40166,40168,40173,40174,40175,40180,40181,40182,40183,40187,40188,40192,40194,40198,40200,40202,40205,40208,40212,40216,40217,40225,40229,40232,40236,40237,40239,40242,40245,40247,40253,40255,40258,40263,40264,40268,40270,40272,40275,40280,40282,40284,40289,40296,40297,40298,40301,40306,40311,40316,40322,40328,40333,40351,40352,40357,40358,40360,40361,40368,40371,40372,40373,40374,40376,40382,40383,40385,40386,40390,40391,40393,40395,40396,40398,40404,40405,40408,40409,40410,40419,40421,40424,40426,40427,40428,40436,40437,40442,40443,40446,40447,40448,40456,40458,40460,40462,40466,40470,40471,40477,40478,40481,40482,40484,40490,40491,40492,40494,40495,40498,40502,40505,40511,40512,40515,40520,40521,40522,40523,40525,40526,40527,40528,40531,40534,40535,40538,40540,40541,40542,40545,40546,40547,40548,40549,40551,40552,40555,40558,40559,40562,40563,40564,40568,40569,40570,40571,40572,40573,40574,40576,40577,40580,40581,40582,40583,40587,40588,40594,40597,40600,40601,40603,40609,40612,40613,40614,40617,40620,40624,40625,40626,40629,40635,40637,40639,40642,40644,40649,40650,40651,40652,40659,40662,40664,40665,40666,40670,40675,40676,40677,40680,40683,40688,40694,40695,40696,40700,40701,40708,40709,40710,40712,40713,40720,40722,40723,40726,40727,40728,40732,40734,40735,40740,40741,40742,40743,40745,40748,40749,40750,40755,40756,40757,40759,40763,40764,40766,40768,40770,40772,40774,40777,40779,40780,40784,40787,40788,40790,40791,40795,40799,40800,40801,40803,40804,40811,40812,40814,40815,40816,40820,40822,40828,40829,40830,40832,40838,40839,40840,40841,40843,40848,40853,40855,40858,40862,40864,40865,40866,40870,40872,40873,40876,40878,40881,40882,40885,40886,40893,40894,40896,40897,40899,40901,40902,40903,40909,40914,40916,40919,40920,40925,40932,40934,40935,40937,40942,40946,40948,40949,40955,40959,40961,40962,40964,40970,40971,40972,40977,40981,40982,40988,40991,40994,40997,41003,41004,41005,41011,41015,41017,41018,41022,41026,41030,41031,41035,41038,41039,41040,41042,41043,41046,41048,41049,41050,41051,41053,41054,41056,41058,41059,41060,41061,41062,41063,41064,41066,41067,41069,41070,41072,41074,41075,41076,41078,41079,41080,41082,41088,41089,41090,41091,41093,41094,41096,41099,41101,41102,41104,41105,41107,41109,41110,41112,41116,41119,41122,41123,41124,41125,41126,41132,41136,41138,41139,41140,41145,41146,41150,41151,41153,41154,41155,41159,41160,41161,41162,41163,41164,41169,41174,41175,41177,41179,41181,41182,41183,41186,41187,41194,41197,41199,41200,41202,41203,41204,41212,41213,41214,41215,41218,41221,41222,41224,41225,41228,41229,41232,41233,41236,41243,41244,41245,41246,41247,41251,41254,41256,41258,41259,41260,41261,41264,41266,41267,41269,41271,41272,41278,41282,41283,41286,41289,41
So the first question was, is this a cron job or plugin causing this problem, or a brute force attack? Or both? Given that these same debilitating errors were coming from many different ip ranges over the course of several weeks according to the logs, my guess was no. A Denial of Service attack?
First we needed to know which website the deadly query was being run against. For that I searched all the access logs for the IPs that triggered the query resulting in the database failure. To reiterate, we run many different kinds of WordPress sites, some with thousands of active customers and users. Surely the issue was from one of the most busy sites we run?
I was shocked to find the problem was coming from one of our least-busy WordPress websites — my own, somewhat inactive blog. The one you’re now reading!
Apparently I had set another one of my articles (about WordPress in fact) to allow for comments, as an experiment, and promptly forgot about it…for three years. Spammers had patiently flooded the comments for that article over the course of three years with over 111,000 comments. Eventually the size of the queries due to the number of comments overwhelmed the database, causing it to shut down with the out of memory error above, resulting in all the WordPress websites on that virtual host to be inaccessible until the database was restarted.
We have 24/7 monitoring in place to alert us whenever a system goes offline, so it was never offline for very long. It was annoying however, and preventable. Discovering the problem and the solution to these kinds of complex problems is what keeps us in business, but often the solution is quite simple. We locked down commenting on the article, deleted all the comments, upgraded the platform, plugins and removed any that weren’t in active use. Every customer on the virtual host should see an improvement in speed of their own websites as a result of the responsive work of our system administration team!
