[SOLVED] fatal MySQL error was encountered after submitting a story

You shouldn't have a validated field in the _authors table now.  It should have been moved to the_authorprefs table. You probably somehow messed up step 16 in the update.

In  fangfanfiction_authorprefs table I have a validated field and in the authors table.  Should I therefore delete the one in the authors table?  Or do I need to reinstall something?

I have the following (if this helps)

_author fields
Field  Type  Attributes  Null  Default  Extra  Action
field_id int(11) No auto_increment
field_type tinyint(4) No 0
field_name varchar(30) No
field_title varchar(255) No
field_options text Yes NULL
field_code_in text Yes NULL
field_code_out text Yes NULL
field_on

_author info
Primary
UID
PRIMARY and INDEX keys should not both be set for column `uid`

_authorprefs
uid  int(11)    No  0    Browse distinct values    Change    Drop    Primary    Unique    Index  Fulltext
newreviews tinyint(1) No 0
newrespond tinyint(1) No 0
ageconsent tinyint(1) No 0
alertson tinyint(1) No 0
tinyMCE tinyint(1) No 0
sortby tinyint(1) No 0
storyindex tinyint(1) No 0
validated tinyint(1) No 0
userskin varchar(60) No default
level tinyint(1) No 0 Br
categories varchar(200) No 0
contact

_authors
  uid  int(11)    No  auto_increment   
penname varchar(200) No
realname varchar(200) No
email varchar(200) No
website varchar(200) No
bio text Yes NULL
image varchar(200) No
date datetime No 0000-00-00 00:00:00
newreviews char(1) No 0
admincreated char(1) No 0
password varchar(40) No 0
validated char(1) No 0
userskin varchar(60) No
level char(1) No 0
contact char(1) No 0
categories varchar(200) No 0
AOL varchar(40) No 0
Yahoo varchar(40) No 0
ICQ varchar(40) No 0
MSN varchar(40) No 0
betareader char(1) No 0
ageconsent char(1) No 0
alertson

and I have no clue what any of that means but I am trying to figiure it out I promise

it looks like your authors table wasn't modified correctly during the upgrade...I believe there's some information in there that should have been moved to the author_fields table as well.  You'll prolly have to run the SQL statements that do that manually to clean it up.

I had to skip #16 as well as #4 to get the upgrade to take.  I assumed (probably wrongly) since my database had been modified (I had successfully gone to step 19 before) that since step 4 had previously been taken care of successfully that step 16 would be the same.  (I received the same error message for it)

If that step ran these scripts, where do I look for the SQL statements to do this? Do you know? I'm sorry to keep hounding you about this, but I'm REALLY in over my head here, and truly appreciate all this help you're giving me. I think my husband (who is in Turkey right now) can talk me through how to run an SQL script over the phone, but he told me I'd have to know exactly what to input first.

Ok, let's see if you have all the new tables [you prolly do, but it can never hurt to make doubly sure].  This would take care of step 4 in knowing that it was definately successful.

The sql files that you would use to run the statement 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 fangfanfiction_stories
Copy/paste the first statement into that box, click go.

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  😉

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

 http:://www.yourArchiveURL/install/upgrade20.php?step=16

If you can't, I don't know how to modify the statements in step 16 to get all the modifications to work manually for you as it's much easier for the upgrade script to do the work.  At this point, myself..I'd start again fresh by deleting the DB and restoring the 2.0 DB from your backup and the 2.0 files. 

We are working on this now - we ran the MySQL statement  and we got this

#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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=9' at line 11

right now we are trying to find out what this means - i will post as soon as i found it so if anyone happens to know... I have no clue!

we copied it straight from the documentation

CREATE TABLE `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

okay we have successful run through step 4 and everything works (because of my version of mysql we had to delete this line.  each one has now successful been uploaded.  I will let you know what happens next.  thanks!

How we fixed this:

1 - go through the SQL statements as Chelle instructed to fix from step 4
2 - Ran step 16 again as per instructed and realized that I had duplicate validate and alertson
3 - manually went to my _authors table and deleted validated and alertson

now it works!!!

Glad that was helpful.  Sorry I didn't respond earlier..first time I've been on in a few days.  😀