October 13th, 2009

Previous Entry Next Entry
01:53 pm - How does this even happen?
$customer has some of those god awful "control panel" servers... not Plesk, but the same concept. You get the idea. $vendor is forcing him to do a migration, because they no longer support FreeBSD, so they want him to move to CentOS. $customer contracts with $vendor to maintain these things... sorta, mostly, except he's still responsible for maintaining the actual servers. Confused yet? Me too.

But really that's not the confusing part, and the only reason I mention it is because SOMEHOW, I am pretty sure $vendor was responsible for this monstrosity:

mysql> SELECT Host,Db,User,Table_name,Column_name,Timestamp,Column_priv FROM columns_priv ORDER BY Host,Table_name LIMIT 8;
| Host       | Db     | User     | Table_name     | Column_name  | Timestamp           | Column_priv |
|  | mysql  | phpuser  | tables_priv    | Host         | 2008-01-11 19:39:35 | Select      |
|  | mysql  | phpuser  | tables_priv    | Host         | 2008-06-20 20:03:49 | Select      |
|  | mysql  | phpuser  | user           | Host         | 2008-01-11 19:39:35 | Select      |
|  | mysql  | phpuser  | user           | Host         | 2008-06-20 20:03:49 | Select      |
| localhost  | mysql  | phpuser  | tables_priv    | Host         | 2008-01-11 19:39:35 | Select      |
| localhost  | mysql  | phpuser  | tables_priv    | Host         | 2008-06-20 20:03:49 | Select      |
| localhost  | mysql  | phpuser  | user           | Host         | 2008-01-11 19:39:35 | Select      |
| localhost  | mysql  | phpuser  | user           | Host         | 2008-06-20 20:03:49 | Select      |
8 rows in set (0.00 sec)

That's the columns_priv table in the mysql database. If you didn't already know this, the PRIMARY KEY of the column_priv table is a complex key consisting of Host,Db,User,Table_name,Column_name.

See the problem?

If you think that "localhost" has an extra space on it for half of those or something like that... you're fucking wrong. Those are, I shit you not, DUPLICATE primary keys. For every. Fucking. Row. In. The. Table. The same problem was in the db table.

So, for the two tables that control user permissions for every database on the server... there are primary key duplication violations littered throughout the whole fucking thing. Upshot being, whenever the server needs information about user rights, who knows what the fuck it'll get - it might get one record; it might get another record; it might get no record at all, or the whole goddamn server might catch on fire and melt into slag. Duplicated primary keys are something a SQL server checks for on insertion or update of a record... not on read of a record. In fact, before fixing this, I played around with it a little and discovered all sorts of ways to get wildly different results out of querying these tables because of this. In fact, the only way to fix it was to dump the tables completely and recreate them from scratch, because just trying to delete a row would instantly crash the server.

Anyway, it's all fixed now. But what I keep trying to figure out - and failing - is how the fuck did $vendor get those rows in the database to begin with?!
Current Mood: boggled

(3 comments | Leave a comment)


[User Picture] From: clme
Date: October 15th, 2009 - 06:13 am
Is there a way to import a backup file rather than restoring it?
Can you edit it from another server?
Was it karma?
Was there some sort of black magic in the air on June 20th, 2008 at 8:03pm?
Do they know any Microsoft reps that may have been trying to get their business?

[User Picture] From: jimbojones
Date: October 15th, 2009 - 12:17 pm
SQL backups aren't like file backups; a SQL backup is the set of SQL commands required to create the tables backed up and populate them with the data in them. Which is actually how I discovered the problem to begin with - because running the SQL commands necessary to get the data in there would produce errors.

You can't edit SQL data directly, either, from that server or any other. You process it with, again, SQL commands that would error out if you attempted to violate the key constraints.

Always suspect the Microsoft reps... :shifty:

[User Picture] From: clme
Date: October 16th, 2009 - 08:50 am
Its just... well.. how the hell did it... I mean...

Hell, you have to try to mess up tables like that. I need to take lessons from whoever pulled that off because I am obviously not as good as I thought at breaking shit. :-)

> Go to Top