Thread

Originally posted this message in a thread on the same topic, but just realized that was in the legacy support forum (surprised this issue dates back to EE 1 and hasn’t been solved).

So, here it is again:

I just experienced a problem on a site running EE 2.3.1 and MSM 2.1.1. The site has gone completely off the air, and I can’t even login or access the CP.

It happened after an aggressive session updating banned IPs, and apparently I tripped over the limit. After a lot of sleuthing, I finally figured out what must have happened — that this data is stored in exp_sites > site_system_preferences and overloading the field causes truncation and system failure. Great. :-p

Is there any way to rehabilitate the site_system_preferences data directly in the db, at least enough to get back into the CP and sort out the Banned IPs so that this field no longer truncates?

Unfortunately, I don’t have a recent backup of the DB :-(

Next step will be to go to the hosting service and see if they have a backed-up state we can retrieve. But still . . .

Is there anything that can be done with the truncated field to get back up and running? Or is the only option to find some backup and go backward?

Hey Kent,

I feel your pain.

I think your best bet is to get that backup from your hosting provider.

If that is not something that can be done, then we can go from there. I am not sure we can paste things back together, but we can try if that’s the last option.

Please let me know!

Cheers,

Shane — Thanks for your response.

Turns out our host only keeps 48 hours worth of hourly backups. And I had already spent more than two days last weekend trying to figure out this issue before finally calling in. So I was S.O.L. as far as getting a clean, recent backup of our database. :-(

So, now with nothing left to lose, I went back to the months-old backup that I had. It only had one [old state] of the two sites that we’re running on this installation. But I took the contents of the site_system_preferences field and copied it back into this field for *both* rows/sites in exp_sites of the current db.

That worked to get me back to where the sites would display and I could get into the CP.

I went through and tried to check pref settings to reset those that needed to be different. I would feel more confident if I had a reference for which specific site preference settings are stored in that particular field. (Aside from the User Banning data, of course—Why on earth is that lumped in there? . . . It would be nice if there was some warning, at least.)

Meanwhile, we’re looking into other ways to combat the onslaught of spammers.

Hey Kent,

Wow, what a pain. I am sorry for all the drama you had to go through.

Is there anything I can do? Any other questions?

Thank you,

Is there anything I can do? Any other questions?

Thanks, Shane.  What would be helpful is a list of the specific site settings that are stored in the exp_sites > site_system_preferences field in the db, so I (or anyone else who encounters this issue in the future) can know what to review and reset to make sure everything is all good again after the recovery.

Hey Kent,

That’s encoded, but you can use phpMyAdmin to take a look at the structure. Does that help?

Cheers,

Does that help?

No, not really. Thanks, though.

I knew it was encoded — phpMyAdmin is how I finally worked out a “solution”, dinking around in that field blindly.

Since the field is encoded, I couldn’t just go in and manually edit it to remove the excessive User Banning data and get back below the truncation limit. (Decoding that data is waaay beyond my skill level.) That’s why I had to just copy the contents of that field from an older backup and cross my fingers.

Like I said, however: if I knew which specific settings were encoded in that particular field, then I could go into those prefs in the CP and review them to make sure they’re what I want, and reset them if not.

I mean, I basically did that, by going through *all* the myriad Administration sections. But obviously, those prefs are not all encoded in that one MySQL field. So, if I knew which ones were, that would save the hassle of looking at everything and wondering.

I just thought maybe this might be documented somewhere you could point me to.

I don’t know, maybe it’s a security risk to publish that information?

Hey Kent,

No, nothing security related here.

I understand your frustration. I used this tool to look at my data and you are right, ip banning is tracked in that field. I had no idea that we kept that information in there.

This should not be, the fact that you have hit a limit here proves that this is buggy. We need to handle this differently. I filed this bug. I am hoping that the Devs will update that bug, so be sure to comment on it to stay up to date.

Is there anything else I can help with?

Thank you,

Shane — Nice tool! Thanks. Bookmarked that. Now I can see all the settings stored in that field. That really helps, especially for future (wish I’d known about this a couple weekends ago 😉 . Thanks.

Apparently, this bug was identified as early as 2004 and reported in EE 1.6.4:

http://ellislab.com/forums/viewthread/160328/

It would be nice if it finally gets fixed, especially given all the EE-targeting spammers now. (Don’t know why the Devs didn’t take the opportunity of the EE 2 overhaul to change this structure; might be much harder now.) Thanks for your help.

Hi Kent,

Awesome! Glad to help.

If you need anything else, please just let me know by opening a new thread.

Hopefully the devs will take a look at this asap.

Cheers,

Hey Kent,

A quick update!

The devs will likely fix that by changing the MySQL field type to MEDIUMTEXT from TEXT. So you might try that as a fix!

Cheers,