Upgrade bug from 1.0 to 1.0.1

[Nick]

I noticed a bug in the upgrade script from 1.0 to 1.0.1, and it hasn't been fixed yet.

The problem is that when the vote_rating column in the database postvotes table was changed from varchar to smallint, it reset all the existing votes to the new default of "0". That's a big problem, especially if you imported a Pligg/SWCMS site into Hotaru and upgraded from 1.0 to 1.0.1, or 1.0 to 1.0.2. If you started with 1.0.1 and upgraded to 1.0.2, you are not affected.

Here's the offending code. The problem is clearly with the ALTER TABLE query, but I don't know how to change it so that it doesn't reset all the values to the default:


// Change "positive" to 10
$sql = "UPDATE " . TABLE_POSTVOTES . " SET vote_rating = %d WHERE vote_rating = %s";
$h->db->query($h->db->prepare($sql, 10, 'positive'));

// Change "negative" to -10
$sql = "UPDATE " . TABLE_POSTVOTES . " SET vote_rating = %d WHERE vote_rating = %s";
$h->db->query($h->db->prepare($sql, -10, 'negative'));

// Change "alert" to -999
$sql = "UPDATE " . TABLE_POSTVOTES . " SET vote_rating = %d WHERE vote_rating = %s";
$h->db->query($h->db->prepare($sql, -999, 'alert'));

// Alter the PostVotes table so the vote rating is an INT
$sql = "ALTER TABLE " . TABLE_POSTVOTES . " CHANGE vote_rating vote_rating smallint(11) NOT NULL DEFAULT %d";
$h->db->query($h->db->prepare($sql, 0));If all your votes have a rating of zero (check your postvotes table) then a Who Voted or Points plugin won't be able to check votes, and anything you voted for in the past won't be recognized. Unfortunately, it's not possible to reverse that mistake because all positive, negative and flagged posts will all have the same value and we can't tell them apart.

That's my fault, I should have noticed earlier. I'm sorry if you have to run the Pligg Importer again to fix your data. I'll be more careful in future. :(

[Nick]

I reduced the problem on the demo site by going into PhpMyAdmin, clicking SQL and running this query:


UPDATE `hotaru_postvotes` SET `vote_rating` = 10 WHERE `vote_rating` = 0

That worked because no posts had been flagged yet, and "un-voting" removes a vote rather than adding a negative vote. In other words, they were all positive so all get a rating of 10.

[Jason]

Hmm ... what I sometimes do to get around this is to make a temporary table, convert the column, and then update affected fields. That said, I've never had to do anything like this with PHP and MySQL. Would it be too much of a hassle to create a temporary table with two columns, copy the row id and vote_rating values only, alter the column, update back, and then delete the temporarily created table?

Then again ... just typing the question was a lot of work :P Let me sleep on this. I'll get back to you in the morning.

[Nick]

I doubt going from 1.0 to 1.0.1 will affect anybody now, but I do need to make an almost identical change to the commentvotes table in the next upgrade, so if making a temporary table is needed then so be it. I might get you to have a look at it first though. :)