UBB.threads PHP Forum Software Community Forums UBB.threads 6 Version 6 Support SQL Errors in 6.0.3 to 6.1 Upgrade

I'm trying to upgrade the data base by running the altertable-6.0-6.1.php script but when I do I get this error:

Alterting the Users table to add an index on Approved users, an admin email opt in/opt out selection, and an email format.
SQL ERROR: Unable to do_query: ALTER TABLE w3t_Users ADD U_LoginName VARCHAR(64) NOT NULL, ADD INDEX Login_ndx (U_LoginName), ADD INDEX App_ndx (U_Approved), ADD U_AdminEmails VARCHAR(3) DEFAULT 'On', ADD U_EmailFormat VARCHAR(10) DEFAULT 'plaintext'
Duplicate key name 'App_ndx'SQL ERROR: Unable to do_query: UPDATE w3t_Users SET U_LoginName = U_Username
Unknown column 'U_LoginName' in 'field list'Alterting the w3t_Last table to add an index
SQL ERROR: Unable to do_query: ALTER TABLE w3t_Last ADD INDEX userlast_ndx (L_Username)
Duplicate key name 'userlast_ndx'Alterting the w3t_Boards table to add an index
SQL ERROR: Unable to do_query: ALTER TABLE w3t_Boards ADD INDEX Cat_ndx (Bo_Cat)
Duplicate key name 'Cat_ndx'Altering the w3t_Posts table to add a field to track the number of times a download has been downloaded
SQL ERROR: Unable to do_query: ALTER TABLE w3t_Posts CHANGE B_ParentUser B_ParentUser VARCHAR(64) NOT NULL, CHANGE B_Username B_Username VARCHAR(64) NOT NULL, CHANGE B_Icon B_Icon VARCHAR(30) NOT NULL, ADD INDEX Icon_ndx(B_Icon)
Duplicate key name 'Icon_ndx'Altering every users signature, converting markup in the database to reduce calls to do_markup on display.
Done...


Can anyone help me with this?

Is this your first attempt at running the altertable script? It looks as if the indexes have been added already which is making it so the rest of the query can't be executed. The rest of the query adds the new necessary database fields.

Did you make a backup of your database before you tried running the altertable? We might be able to work around it without having to go back to the backup, but will need to know if the altertable has been run just once or several times.

-------------------
Rick Baker
UBBThreads developer

Well... where should I start with this one??? <img border="0" title="" alt="[Eek!]" src="images/icons/shocked.gif" />

I mistakenly ran the 6.1 beta updater when it first came out and totally messed up the board. I didn't have a backup at the time but I was able to get the board back up and running by doing a clean install of 6.0 and then importing all of my posts and members back in. The board is working correctly now so I thought my database was back to normal.

This time I made a backup first and tried to upgrade the backup to make sure everything was going to run smoothly. This way I could do all of my testing on the backup while not interrupting the board. My database is around 25MB so I don't want to loose all of this information and start over. I hope you'll be able to find a way to import this into the new format.

Ok, can you get into your board at all? If not then you'll need something like phpmyadmin to run a few sql queries that are below:

ALTER TABLE w3t_Users
ADD U_LoginName VARCHAR(64) NOT NULL,
ADD INDEX Login_ndx (U_LoginName),
ADD U_AdminEmails VARCHAR(3) DEFAULT 'On',
ADD U_EmailFormat VARCHAR(10) DEFAULT 'plaintext'


UPDATE w3t_Users SET U_LoginName = U_Username


ALTER TABLE w3t_Posts CHANGE B_ParentUser B_ParentUser VARCHAR(64) NOT NULL,
CHANGE B_Username B_Username VARCHAR(64) NOT NULL,
CHANGE B_Icon B_Icon VARCHAR(30) NOT NULL

-------------------
Rick Baker
UBBThreads developer

Thanks... now should I do this after I run the altertable-6.0-6.1.php script or before?

I'd try running the altertable script first. If you get the same errors then try running those sql queries.

-------------------
Rick Baker
UBBThreads developer