3.1 to 3.2 upgrade problem

The problem is your database is reporting the current version as 3.0 and not 3.1 so the 3.2 updater is trying to run the 3.0 => 3.1 database changes again. There error message indicates it is trying to add a primary key to a table when that has already been done.

It is likely something went wrong with the 3.1 update and it only partially completed so it did not set the version number to 3.1 in the fanfiction_settings table. To repair it:

1. Take each SQL statement in the list starting on line 36 and ending on line 70 of admin/update.php and run it in phpmysqladmin. Remember to insert the correct tableprefix

e.g.If you prefix is 'fred':

dbquery("alter table ".$tableprefix."fanfiction_authorinfo add primary key(uid,field);");

becomes:

alter table fredfanfiction_authorinfo add primary key(uid,field);

many of these will fail with errors - as long as they aren't 'syntax' errors you can ignore them - it just means that statement worked ok during the 3.0=>3.1 update.

2. Run the query:

UPDATE fredfanfiction_settings SET version='3.1'  WHERE sitekey = 'yoursitekeygoeshere';

3. Now run the 3.2 update

When it comes to MySQL I really don't know what I'm doing. How do you run a statement in the SQL admin? How do you manually run a query? I'm sorry that I'm making things difficult. Learning SQL (and PHP) is on my list of things to do, believe me.

Log into phpMyAdmin, select your database and click on SQL in the top. There should be a box you can paste the queries into.

Another way to do it that may be easier for you is comment out line 36 of the update.php script and run it again. If it still fails comment out line 37 and repeat until it works or you get to line 70 and it is still broken in which case something else is wrong.

I commented out line 36 then ran the update. Now it's displaying the original error I received:

A fatal MySQL error was encountered.
Query: CREATE TABLE `test2fanfiction_stats` ( `sitekey` varchar(50) NOT NULL default '0', `stories` int(11) NOT NULL default '0', `chapters` int(11) NOT NULL default '0', `series` int(11) NOT NULL default '0', `reviews` int(11) NOT NULL default '0', `wordcount` int(11) NOT NULL default '0', `authors` int(11) NOT NULL default '0', `members` int(11) NOT NULL default '0', `reviewers` int(11) NOT NULL default '0', `newestmember` int(11) NOT NULL default '0' ) ENGINE=MyISAM
Error: (1064) You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM' at line 12

What version of mysql are you using - it says 12.22 at the top but that is not a valid mysql version number - it will be 3.23, 4.0.x, 4.1.x or 5.0.x. One way to tell is to run "SELECT @@version" in phpmysqladmin.

It's 4.0.16. Sorry about the incorrect number. It's listed weirdly in my vdeck.

That's old enough that it might not understand the ENGINE keyword. Try removing ENGINE=myisam from that and any other statements in the upgrade script.

Sorry it took me so long to reply. I couldn't access my site for a few days.

Anyway, I continued to get the "authorinfo" error until I realized that there was an update.php file in the parent folder so I commented out that line in it as well. I continued to get errors so I continued to comment out lines (finding the queries that didn't complete), removed ENGINE=myisam, activated my news and info blocks again, and everything finally looks okay.

Was it okay that I changed the other update.php? It didn't seem to have a negative effect on the archive.

Now I have to upgrade my live archives. I may be back, but I don't think so since the version number updated like it should have on both of them. I shouldn't have the same problem.

Thanks for all your help, jrabbit, Carissa.