nonvalid MySQL result resource

From your other message I think you also had a problem with your messages table prefix.  The error is showing a "bah1" prefix there too. 

Table 'bahprodu_bahfanfic.bah1fanfiction_messages' doesn't exist

Ok...my other  problem is fixed. I recreated the tables, got the site up and running again, deleted primary from admin/update.php...now get this

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

what am I doing wrong?

Did you somehow overwrite your tableprefix?  Same thing here.  It's showing a "bah1" prefix.

I had to go and manually rename them all with the prefix. This got the site running but still getting this error message when I go to upgrade from 3.0.2 to 3.1 like I did before I messed up the prefix.  😕

Exactly what error are you getting when you try to update?

I get this

A fatal MySQL error was encountered.
Query: alter table bah1fanfiction_authorinfo add primary key(uid,field);
Error: (1068) Multiple primary key defined

But after I delete primary from admin/update.php, it goes away and I get the one listed above

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

The site is working and is using the bah1fanfiction tables... 😐 not sure what else to try. Is it possible I missed a file in replacing them?

Comment out line 34 and see if it'll let you continue.

I get this now

A fatal MySQL error was encountered.
Query: alter table bah1fanfiction_blocks add unique index block_name (block_name);
Error: (1062) Duplicate entry 'categories' for key 2

I have a feeling it is going to do this on all of them...Here are a list of my tables. The bah1 are the ones I had to make to get the site back up.

bah1fanfiction_authorfields
bah1fanfiction_authorinfo
bah1fanfiction_authorprefs
bah1fanfiction_authors
bah1fanfiction_blocks
bah1fanfiction_categories
bah1fanfiction_challenges
bah1fanfiction_chapters
bah1fanfiction_characters
bah1fanfiction_classes
bah1fanfiction_classtypes
bah1fanfiction_codeblocks
bah1fanfiction_comments
bah1fanfiction_favorites
bah1fanfiction_inseries
bah1fanfiction_log
bah1fanfiction_messages
bah1fanfiction_news
bah1fanfiction_online
bah1fanfiction_pagelinks
bah1fanfiction_panels
bah1fanfiction_poll
bah1fanfiction_poll_votes
bah1fanfiction_ratings
bah1fanfiction_reviews
bah1fanfiction_series
bah1fanfiction_settings
bah1fanfiction_shoutbox
bah1fanfiction_stories
bahfanfiction_settings
fanfiction_authorfields
fanfiction_authorinfo
fanfiction_authorprefs
fanfiction_authors
fanfiction_blocks
fanfiction_categories
fanfiction_challenges
fanfiction_chapters
fanfiction_characters
fanfiction_classes
fanfiction_classtypes
fanfiction_codeblocks
fanfiction_comments
fanfiction_favorites
fanfiction_inseries
fanfiction_log
fanfiction_messages
fanfiction_news
fanfiction_online
fanfiction_pagelinks
fanfiction_panels
fanfiction_poll
fanfiction_poll_votes
fanfiction_ratings
fanfiction_reviews
fanfiction_series
fanfiction_settings
fanfiction_shoutbox
fanfiction_stories

You can't get this:

Quote
A fatal MySQL error was encountered.
Query: alter table bah1fanfiction_blocks add unique index block_name (block_name);
Error: (1062) Duplicate entry 'categories' for key 2

The part in red is the line I told you to  comment out.  The part in purple is an error for the next line in the script. 

How did you create these tables?  I am totally confused as to why you've got 2 sets of tables.  Is there data in both?  Which one is actually running your archive?  These bah1 tables seem to be optimized already since you're getting errors on every line.

This is the line I deleted

dbquery("alter table ".$tableprefix."fanfiction_blocks drop index block_name;");

I created the tables by exporting the file and using the SQL command. When I originally updated I had to without a prefix then I added one (bah1) when I went to upgrade to 3.1. That was when the site went out and after I created the bah1 tables it works...so, long story short, bah1 runs the site. I am so sorry to be so much trouble  😳

I just deleted all this 

dbquery("alter table ".$tableprefix."fanfiction_blocks drop index block_name;");
dbquery("alter table ".$tableprefix."fanfiction_blocks add unique index block_name (block_name);");
dbquery("alter table ".$tableprefix."fanfiction_categories drop index category;");
dbquery("alter table ".$tableprefix."fanfiction_categories drop index parentcatid;");

and the update works.  😳 again I am so sorry for being a pest. Any idea exactly what happened besides the table prefixes? should I have changed fanfiction to bah1fanfiction

If I understand what happened and what you did, I don't think it matters.  You copied fanfiction_ tables and made them bah1fanfiction_ so they should both have identical information up to the point where you switched them.

The easier and better solution for anyone who runs into a similar problem later would have been to edit the tableprefix in the settings table.  That way you only have to make one change.

Bump.  Did you get this fixed?  If there's no response in a week, this thread will be marked closed.