Webmastersite.net
Register Log In

db destoyed

Comments on db destoyed

frankbeckers
Forum Regular

Usergroup: Customer
Joined: Dec 20, 2007
Location: Germany

Total Topics: 33
Total Comments: 106
Posted Apr 25, 2010 - 7:17 AM:

Hello Paul,

the mysql db seems to be destroyed thru an suggested entry from an new member. I can not reproduce this but it happens a second time since 5.1.30 or near by this. I loaded an old db backup and all is running very well. I do the suggest for my own under an admin user and all works fine. I do the suggestion under guest and all works fine. So what happened?

thanx
Frank
Paul
developer

Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California

Total Topics: 61
Total Comments: 7868
Paul
Posted Apr 26, 2010 - 3:38 AM:

Can you please say what "destroyed" means? It could mean anything, but I assume you don't mean it actually drops all tables and removes the database. If you have a copy of the "destroyed" database please send it to me.
frankbeckers
Forum Regular

Usergroup: Customer
Joined: Dec 20, 2007
Location: Germany

Total Topics: 33
Total Comments: 106
Posted Apr 30, 2010 - 2:27 PM:

You are right. Destroyed could mean everthing. In my case it means all entries in the links table are overwritten with the same data. url, description, title etc. are all the same and pointig to an new and pending entry. This happens teo times and I recovered the hole db. Actually I have disable suggestion by renaming suggest.php to something other.
Paul
developer

Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California

Total Topics: 61
Total Comments: 7868
Paul
Posted Apr 30, 2010 - 3:18 PM:

I've heard of this from one other person recently. It seems to have something to do with aliases (secondary categories), but I need a copy of the destroyed table records to inspect in order to see what's unique about them (since all forms of aliasing I test don't cause any issues).
frankbeckers
Forum Regular

Usergroup: Customer
Joined: Dec 20, 2007
Location: Germany

Total Topics: 33
Total Comments: 106
Posted May 01, 2010 - 12:28 AM:

From one situation I have an phpMyAdmin SQL Dump from the corrupted db. Where should I sent this? It's about 5mb.
frankbeckers
Forum Regular

Usergroup: Customer
Joined: Dec 20, 2007
Location: Germany

Total Topics: 33
Total Comments: 106
Posted May 01, 2010 - 1:45 PM:

Paul wrote:
I've heard of this from one other person recently. It seems to have something to do with aliases (secondary categories), but I need a copy of the destroyed table records to inspect in order to see what's unique about them (since all forms of aliasing I test don't cause any issues).



I have been thinking about this alias issue. Per default for all guests and members all "can alias" options are disabled. Only for admins they are enabled. Only I'm an admin, so I think this couldn't be a reason.
Paul
developer

Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California

Total Topics: 61
Total Comments: 7868
Paul
Posted May 03, 2010 - 5:12 PM:

Please email it to wsn@webmastersite.net, or attach to a private message here.

You haven't done any aliases using your admin account?

Aliasing is the only operation which updates database records to make them the same as each other. That said, I've been over all the alias code pretty carefully and inserted checks at central points that would display an error and refuse to proceed if it tried to run a query that'd potentially change the whole database to be identical... so for it to happen anyway despite these checks, maybe it's related to something I'd never guess. Hopefully the database will contain a clue.
frankbeckers
Forum Regular

Usergroup: Customer
Joined: Dec 20, 2007
Location: Germany

Total Topics: 33
Total Comments: 106
Posted May 05, 2010 - 2:12 PM:

I have sent the db via email to you.

I'm now at WSN Links 5.1.33 Beta 6
Paul
developer

Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California

Total Topics: 61
Total Comments: 7868
Paul
Posted May 05, 2010 - 4:43 PM:

Looks like submission 212 is at fault somehow. Here's what I can see so far:

  • 212 is owned by user 88, all the ruined are guest-owned
  • 212 is active=1 and validated=1, all the ruined aren't
  • 212 has a submission time about 30 seconds later, all the ruined got the same older submission time
  • 212 has a lasteditvalue (as every real submission should), all the ruined have had their lastedit zeroed out somehow
  • 212 has an ip address recorded, all the ruined don't (oddly, some of the later ok submissions don't have one either)
  • 212 has a blank recipurl, all the ruined have http://
  • 212 DOESN'T have cached data for the associated feed, all the ruined DO have cached data. the feed cache time is 1271150510 whereas the submission time is 1271150507 so it's caching about 3 seconds after setting the submission time. They all have the same last feed cache update time, so maybe this has something to do with feed cache updates.
  • None of the catids were altered. That would fit the aliasing theory, but so much of the other stuff doesn't, with aliases the ip and recipurl and so forth should be identical to the original. Besides, the alias field isn't filled anywhere.


Unfortunately none of that tells me what the problem is yet.

Since only two people have noticed this, but both have seen it with some frequency, maybe it's the way a particular mysql version/configuration deals with an error situation. Like maybe some mysql configuration would make an update query with an "id=" (blank) condition update all rows while another would generate an error while not altering any rows.
Paul
developer

Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California

Total Topics: 61
Total Comments: 7868
Paul
#10 - Quote - Permalink
Posted May 05, 2010 - 6:15 PM:

Still no idea what's going on, but I can't imagine how the responsible query could be getting processed through anywhere except $db->updatelist(), so I've added checks in there to (a) stop any query with the id= mentioned above and mail me a report (b) mail me a report on any update query that alters most of the records -- I can't think of a valid case where most records get updated. B won't stop it from destroying the db but should email me useful information when it does, hopefully.

This is applied in beta 7, which will be out an hour from the time of this post.
frankbeckers
Forum Regular

Usergroup: Customer
Joined: Dec 20, 2007
Location: Germany

Total Topics: 33
Total Comments: 106
#11 - Quote - Permalink
Posted May 06, 2010 - 1:57 PM:

Today the db was overwritten again. I will send you the db via emmail.
frankbeckers
Forum Regular

Usergroup: Customer
Joined: Dec 20, 2007
Location: Germany

Total Topics: 33
Total Comments: 106
#12 - Quote - Permalink
Posted May 06, 2010 - 1:59 PM:

I have done the update to Beta 7 after recovering a db backup from yesterday.
Paul
developer

Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California

Total Topics: 61
Total Comments: 7868
Paul
#13 - Quote - Permalink
Posted May 07, 2010 - 6:45 PM:

Since the lastedit field is 0 on the bad dbs, I'm adding another check to stop and report when a query attempts to set the lastedit field to 0 or blank. I can't see any way for it to zero the field without getting caught now in 5.1.33.
Paul
developer

Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California

Total Topics: 61
Total Comments: 7868
Paul
#14 - Quote - Permalink
Posted May 08, 2010 - 4:48 PM:

Looks like the checkpoint caught it, I just got this email:

Dangerous db->updatelist call! Table blogfinden_links fields/values title = '', url = '', description = '', rating = '0', votes = '', validated = '', sumofvotes = '', email = '', time = '', hits = '1', numcomments = '', hide = '', ownerid = '', voterips = '', voterids = '', lastedit = '', type = 'regular', notify = '', suspect = '', pendingedit = '', funds = '', suspended = '', expire = '', ip = '', typeorder = '', recipurl = '', hitsin = '', recipwith = '', hitsinips = '', hitsoutips = '', lastcomment = '', related = '', viewers = '', threadviewers = '', hitsintemp = '', hitsouttemp = '1', origtype = '', importance = '6', timesdead = '', timesemailed = '', threadclosed = '', threadposters = '', lastposterid = '', lastpostername = '', ownername = '', deleted = '', deletionreason = '', deletedby = '', timevalidated = '', filefield = '', message = '', sticky = '', downloads = '', pollid = '', posticon = '', savedby = '', validatedemail = '', unpaid = '', recipverified = '', effectivetime = '', sugcatid = '', pagerank = '', wysihtml = '', movedid = '', unrevised = '', feedurl = '', feedcache = '', feedcachetime = '', tags = '', xmlsource = '', ordercomments = '', lastpadupdate = '', padfile = '', lastmonthlycheck = '', invisibleto = '', profileurl = '', lastprofileurl = '', timesrenewed = '', timedeleted = '', sponsorend = '', address = '', city = '', state = '', country = '', latitude = '', longitude = '', zip = '', phone = '', summary = '', active = '', sponsorendtime = '', price = '' where alias=0. Happened at blog-finden.de/link.php?rew...ten=1&action=detail&id=133

So it's something that happens on the listing details page (or maybe a cron) no more than once a day, and it's related to aliases. Doesn't appear to relate to new submissions. I'll review every WHERE alias= query source again.

Edit: Since it's setting hits=1, maybe it's in the hits counting. Seems like that'd happen a lot more often though.
Paul
developer

Usergroup: Administrator
Joined: Dec 20, 2001
Location: Diamond Springs, California

Total Topics: 61
Total Comments: 7868
Paul
#15 - Quote - Permalink
Posted May 11, 2010 - 5:22 AM:

Oddly, the emailed reports show the db-wiping query being run on numerous sites including my own websites -- even though my databases haven't been affected.

The issue seems to happen on attempts to process nonexistent listings, due to malformed URLs (it had & a m p ; instead of & in the ones on your site) or dead links. I've fixed this so that it just displays the "bad request" page on any attempt to update nonexistent listings, and stops all further operations.
Search thread for
Download thread as
  • 0/5
  • 1
  • 2
  • 3
  • 4
  • 5



Sorry, you don't have permission to post posts. Log in, or register if you haven't yet.