[Solved] Upgrading from 2.0.7 to 3.2.1 Error

Your first error is because the table the upgrade script is trying to create already exists.  You're probably going to have to install the tables from step 4 manually so that you can skip the statements that are trying to create tables that already exist and not hang up the install.  You're getting the second error because when you started fresh, and skipped the table prefixes, the table that threw the error wasn't created, so step 8 can't find it to insert data into it.  Make sense?  From your post I suspect you may have read this thread in your search for a solution:

https://efiction.org/forums/index.php?topic=4983.msg27035#msg27035

If not, I'll repost where and how to do that just to make it easy:

This will take care of step 4 in knowing that it was definately successful.

The sql files that you would use to run the statements manually are located in the docs folder.  You'll see one called upgrade20_step4.sql.

Steps:

Access phpMyAdmin
There should be a tab that says SQL, click that
You'll see a box.
Open the upgrade20_step4.sql in a text editor like notepad
Add your table prefix in front of each table name.  So for fanfiction_stories,  it should be sinfulde_archive.fanfiction_stories
Copy/paste the first statement into that box, click go. Do this one at a time for each statement.

If you get an error that states the table already exists, then that means the table is already created, [each statement in that file is a CREATE statement to create the additional tables for version 3.0+].  You don't have to worry about it [hence why I say do each one one at a time].  If you get a different error, post it and we'll see if we can't get you sorted.  Shouldn't have any issue with the syntax..but you never know  😉    If you don't get any error beyond table exists, you should be golden to go to step 8

Now to see if you can perform step 8, put this in your browser :

  http://www.sinful-desire.org/archive2/install/upgrade20.php?step=8 

You should be able to continue with the install.  If you get another error, just post it and we'll see if we can't get you straightened out.  You may run into error stating duplicates if you didn't delete the test DB totally when you started fresh.  From the 1st error, I suspect that to be the case.  But it's nothing that can't be fixed.  😀

Kudos for duplicating your archive and doing a test run. 

Wow! Thanks for getting back to me with such a great, detailed response. That actually wasn't the thread I had read, so I'm glad you included it.

I'm working on part 1 of the problem and am at the box where I type everything in and add the prefixes. I've copied my database to a different name so I don't get confused from the real one, so my prefix is now sinfulde_testing.

I copy the following in:

-- --------------------------------------------------------

--
-- Table structure for table `sinfulde_testing.fanfiction_authorfields`
--

CREATE TABLE `sinfulde_testing.fanfiction_authorfields` (
  `field_id` int(11) NOT NULL auto_increment,
  `field_type` tinyint(4) NOT NULL default '0',
  `field_name` varchar(30) NOT NULL default ' ',
  `field_title` varchar(255) NOT NULL default ' ',
  `field_options` text,
  `field_code_in` text,
  `field_code_out` text,
  `field_on` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`field_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

-- --------------------------------------------------------

And then MySQL said: 

#1103 - Incorrect table name 'sinfulde_testing.fanfiction_authorfields'

I don't know what I'm doing wrong, but clearly something! The inner workings of SQL databases are very new to me!

No period.  If your prefix is 'sinfulde_testing' then the table would be 'sinfulde_testingfanfiction_authorfields'

OK, so I went through and added the tables manually and got up to Step 19, Optimize Database
This step will optimize the database through the removal of some indexes on the tables and the addition of a number of new ones. How do you want to optimize the database?

I select automatic, like I had done with everything else, and this comes up:

A fatal MySQL error was encountered.
Query: alter table fanfiction_categories drop index category;
Error: (1091) Can't DROP 'category'; check that column/key exists

And I'm apparently missing a fanfiction.stats table? I haven't seen that anywhere!

(OMG all of this makes me want to cry from frustration)

I wasn't sure if having a period in the prefix would make mySQL cranky..now I know for future reference /doh!  😆

Just breathe.  It's all a learning experience.  We all had to start somewhere mucking our way through upgrades, installs, mods, etc.  And you also are doing an upgrade on a duplicate..so there's no pressure to get this right asap as your live site is still working.  You're doing fine.

Your fanfiction_stats error:

Looks like that statement wasn't in the sql file, but the upgrade script does create it.  The solution is the same as doing step 4 manually, you just need to paste two statements into your SQL part of phpMyAdmin:

CREATE TABLE `sinfulde_testingfanfiction_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'
) TYPE=MyISAM;

INSERT INTO sinfulde_testingfanfiction_stats(`sitekey`, `newestmember`) VALUES('$sitekey', '1');

Hopefully the syntax is right, I copied that from the script, so I could have missed a quote.  This should create that table and insert the data

Onto your step 19 error:

Basically what's happening is the upgrade script is trying to alter the table by deleting something that it can't find.  Much like your earlier error when the script was trying to insert data into a table that didn't exist.  Fortunately, this step also can be done manually where you can run each statement one by one and you won't get hung up.  First though, take a look at your fanfiction_categories table and verify what data is and isn't there.

Steps:

Access phpMyAdmin
Click on your fanfiction_categories table
You should see an area that says Indexes in the lower left side of the screen

I'm suspecting that this table is already optimized because it can't find an index to drop [hence the error].  Just verify that that is the case and you don't see something called category in the column with the field heading.  If it's not there, it's ok, because it's supposed to go when you upgrade. 

Now, onto doing the DB optimization manually.  Much like step 4, you'll be copy/pasting statements one at a time.  The file that has the statements is called optimize.sql and is in the docs folder just like the upgrade20_step4.sql file was

Steps:

Access phpMyAdmin
Click the tab for SQL
You'll see a box.
Open the optimize.sql file in a text editor like notepad
Add your table prefix in front of each table name.  So for fanfiction_authorinfo,  it should be sinfulde_testingfanfiction_authorinfo
Copy/paste the first statement into that box, click go. Do this one at a time for each statement. 

Make a note of the errors you get, so you can look at the table in question and verify any missing data is not there for statements that are dropping [aka deleting] information.  Also make note of errors for things being altered.  Chances are the table's already altered, and since mySQL is new to you, you may not know how to double check.  So post what you have and we can help you sort through that.  [Shouldn't be an issue since you didn't get past the first query, but just in case]

Now from this, looking at the upgrade script, I'm not 100% certain so perhaps Tammy, Carrissa, Jan_AQ et al can clarify.. it looks like a part of step 19 does some updating in addition to the sql statements in the sql file.  What I would do [and I tend to do things the hard way first, so you may want to wait for another suggestion :P] is to alter the upgrade20.php to take out the optimizing queries and run step 19.  I can detail that below if you want to try it.

Steps:

Make a copy of upgrade20.php  [this will be your backup copy in case my suggestion doesn't quite work out.  Backups are good,  backups are wise  :lol:]
Open upgrade20.php in a editor like notepad
Find the code that says:

	case "19":

Then look for lines that follow after it that says

		if(isset($_GET['install'])) {
if($_GET['install'] == "automatic") {
dbquery("alter table ".$tableprefix."fanfiction_categories drop index category;");
dbquery("alter table ".$tableprefix."fanfiction_categories drop index parentcatid;");
dbquery("create index byparent on ".$tableprefix."fanfiction_categories (parentcatid,displayorder);");
dbquery("create index forstoryblock on ".$tableprefix."fanfiction_chapters (sid,validated);");
dbquery("alter table ".$tableprefix."fanfiction_comments drop index nid;");
dbquery("alter table ".$tableprefix."fanfiction_comments add index commentlist (nid,time);");
dbquery("alter table ".$tableprefix."fanfiction_inseries drop index seriesid;");
dbquery("alter table ".$tableprefix."fanfiction_inseries drop index inorder;");
dbquery("alter table ".$tableprefix."fanfiction_inseries add index (seriesid,inorder);");
dbquery("alter table ".$tableprefix."fanfiction_inseries drop index sid;");
dbquery("alter table ".$tableprefix."fanfiction_inseries add primary key (sid,seriesid);");
dbquery("create index avgrating on ".$tableprefix."fanfiction_reviews(type,item,rating);");
dbquery("alter table ".$tableprefix."fanfiction_reviews drop index sid;");
dbquery("create index bychapter on ".$tableprefix."fanfiction_reviews (chapid,rating);");
dbquery("alter table ".$tableprefix."fanfiction_reviews add index byuid (uid,item,type);");
dbquery("alter table ".$tableprefix."fanfiction_series drop index owner;");
dbquery("create index owner on ".$tableprefix."fanfiction_series (uid,title);");
dbquery("alter table ".$tableprefix."fanfiction_stories drop index validated;");
dbquery("create index validateduid on ".$tableprefix."fanfiction_stories (validated,uid);");
dbquery("create index recent on ".$tableprefix."fanfiction_stories (updated,validated);");
dbquery("ALTER TABLE `".$tableprefix."fanfiction_inseries` DROP `updated`");
dbquery("ALTER TABLE `".$tableprefix."fanfiction_news` ADD `comments` INT NOT NULL DEFAULT '0'");
dbquery("ALTER TABLE `".$tableprefix."fanfiction_series` ADD `numstories` INT NOT NULL DEFAULT '0'");
$serieslist = dbquery("SELECT seriesid FROM ".$tableprefix."fanfiction_series");

You want to take out all the lines that start with dbquery  since these are the statements you have just run manually.  When you're done that section should look like this:

		if(isset($_GET['install'])) {
if($_GET['install'] == "automatic") {

$serieslist = dbquery("SELECT seriesid FROM ".$tableprefix."fanfiction_series");

That should be all you have to change.  Save the file, upload it to your server overwriting the copy that's already there.  Then in your browser, put in this URL:

http://www.sinful-desire.org/archive2/install/upgrade20.php?step=19

and see if you can't finish the install

OK, thank you for the fanfiction_stats coding!

And before I get to step 19, I'm a step 10 that asks this: Update Stories and Series Tables. This step will create the classes column in your stories and series tables. How do you want to proceed?

If I do automatic it comes up with this:
A fatal MySQL error was encountered.
Query: ALTER TABLE fanfiction_stories ADD `classes` VARCHAR( 200 ) NULL AFTER `catid`;
Error: (1060) Duplicate column name 'classes'

I opened the upgrade20_step10.php file in /docs to try and do it manually but haha, I read the first line And I'm already confused!:
ALTER TABLE fanfiction_stories ADD `classes` VARCHAR( 200 ) NULL AFTER `catid`;

Thank you SO much for coming back to help me with this! I reall do appreciate it.

Oh! And I went ahead and looked at the fanfiction_categories table and I believe this is a category in the column with the field heading - do I need to get rid of that before I get to step 19?

Not a problem.

Question: are you trying the solutions, and then going through the install from that point onward?

I ask because you're getting that error at step 10 most likely because that step was already done [you got to step 19 before you got halted by errors].  That's why you're getting an error stating duplicates.  If you look at the table in your database, you more than likely have classes there.

For the manual sql file..it's the same as you been doing.  The ALTER statement is a command to mySQL to change a table in some way.  In this case, that particular statement is altering your stories table by adding a field to it to store information in named classes.  If you want to verify that step 10 was done correctly, you would follow the same steps as the others with opening up the sql file [as you did] , putting your prefix in front of the table name and copy/pasting each statement one by one.  If you get errors stating duplicates, this means the table's been altered already and you can verify by looking at the tables.

I'm going to assume that this is the case [cause I can't see your DB naturally ;)] since you're getting that error.  That's the first thing I'd look for to see if it already took.  Also, silly question, but it will save some trouble and frustration on your part.  The duplicate database you're working with...is the prefix of the tables of the DB sinfulde_testing or are they sinfulde_archive.  I know you said you changed the prefix to steer away from confusion.  Been awhile since I ran an upgrade, but you're asked for a table prefix for your settings table if you want it different from the prefix you use for the rest.  After that, it checks for your dbconfig file and pulls your information from there if it exists.  Did you change the prefix there when the script presented your archive settings?  I ask cause I'm kinda suspecting the script is looking for table names that aren't correct.