UBB.Developers Forums UBB.threads 7 Development How Do I? sql errors while running altertable-6.3-6.4.php

Hello everybody.

I've searched forum for simillar problem, but found nothing.

I have UBBThreads 6.3 running and i want upgrade it to version 6.4.1 Here's what I do:

Buckup first
Then I copy and overwrite all files and catalogues except filters/ images/ includes/ stylesheets/ and three main config files.
Then I run altertable-6.3-6.4.php (this takes a while, my database weights about 200MB) and I get this output:

Adding a new table to save database queries that are used frequently from the admin section
SQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: CREATE TABLE w3t_Queries ( Q_Entry INT(9) NOT NULL AUTO_INCREMENT PRIMARY KEY, Q_Query TEXT )
Table 'w3t_Queries' already existsAdding a new field to the Groups table to allow for disabling of groups.
SQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: ALTER TABLE w3t_Groups ADD G_Disabled INT(1) UNSIGNED DEFAULT '0'
Duplicate column name 'G_Disabled'Grabbing all old poll data for conversion.
SQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: SELECT * FROM w3t_Polls ORDER BY P_Name,P_Number
Table 'forum.w3t_Polls' doesn't existSQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: SELECT * FROM w3t_PollData ORDER BY P_Name
Table 'forum.w3t_PollData' doesn't existDropping old poll tables and creating new ones for the updated poll system.
SQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: DROP TABLE w3t_Polls
Unknown table 'w3t_Polls'SQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: DROP TABLE w3t_PollData
Unknown table 'w3t_PollData'SQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: CREATE TABLE w3t_PollMain ( P_Id INT(11) UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT PRIMARY KEY, P_Start INT(11) UNSIGNED, P_Stop INT(11) UNSIGNED, P_MustVote INT(1) UNSIGNED, P_NoResults INT(1) UNSIGNED )
Table 'w3t_PollMain' already existsSQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: CREATE TABLE w3t_PollQuestions ( P_QuestionNum INT(11) UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT PRIMARY KEY, P_PollId INT(11), P_Question TEXT, P_ChoiceType VARCHAR(4) )
Table 'w3t_PollQuestions' already existsSQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: CREATE TABLE w3t_PollOptions ( P_OptionNum INT(11) UNSIGNED DEFAULT '0' NOT NULL AUTO_INCREMENT PRIMARY KEY, P_QuestionNum INT(11) UNSIGNED NOT NULL, P_Option VARCHAR(255), P_PollId INT(11) UNSIGNED, INDEX Question_ndx(P_QuestionNum) )
Table 'w3t_PollOptions' already existsSQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: CREATE TABLE w3t_PollVotes ( P_PollId INT(11) UNSIGNED, P_QuestionNum INT(11) UNSIGNED NOT NULL, P_OptionNum INT(11) UNSIGNED NOT NULL, P_Voter VARCHAR(15), INDEX Option_ndx(P_QuestionNum,P_OptionNum), INDEX Voted_ndx(P_PollId,P_Voter) )
Table 'w3t_PollVotes' already existsimporting old poll data...
Adding a table for calendar events...
SQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: CREATE TABLE w3t_Calendar ( C_Entry INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, C_Owner INT(9) UNSIGNED, C_Date INT(4) UNSIGNED, C_Month INT(4) UNSIGNED, C_Year INT(4) UNSIGNED, C_Recurring ENUM ('monthly','yearly','never') DEFAULT 'never', C_Brief VARCHAR(255), C_Notes TEXT, C_Type ENUM ('private','public'), INDEX owner_ndx (C_Owner), INDEX date_ndx (C_Date,C_Month,C_Year), INDEX recur_ndx (C_Recurring) )
Table 'w3t_Calendar' already existsAltering the user's table to add a few new fields/preferences...
SQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: ALTER TABLE w3t_Users ADD U_Birthday VARCHAR(10) NOT NULL DEFAULT '0', ADD U_ShowBday INT(1) NOT NULL DEFAULT '0', ADD U_TimeFormat VARCHAR(6), ADD U_Ignored TEXT, ADD U_OC_Userid VARCHAR(64), ADD INDEX birthday_ndx (U_Birthday,U_ShowBday)
Duplicate column name 'U_Birthday'Altering the online table to store info a bit differently...
SQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: ALTER TABLE w3t_Online ADD O_Board VARCHAR(100)
Duplicate column name 'O_Board'Altering the board's table to allow admin's to associate an image with a forum...
SQL ERROR: Thu, Feb 05 2004 12:19:29 +0100 Unable to do_query: ALTER TABLE w3t_Boards ADD Bo_Image VARCHAR(255)
Duplicate column name 'Bo_Image'Altering the posts table so posts can be linked to a calendar event...
SQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Posts ADD B_CalDay INT(2) NOT NULL DEFAULT '0', ADD B_CalMonth INT(2) NOT NULL DEFAULT '0', ADD B_CalYear INT(2) NOT NULL DEFAULT '0', ADD B_AddSig INT(1) NOT NULL DEFAULT '1', ADD INDEX cal_ndx (B_CalYear,B_CalMonth,B_CalDay)
Duplicate column name 'B_CalDay'Reworking some indexes for speed, this may take awhile. You will get a few errors about indexes not being able to be dropped. This is ok, index names might be a bit different depending on when you installed but they will be dropped properly...
SQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Last DROP INDEX Last_indx
Can't DROP 'Last_indx'. Check that column/key existsSQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Last DROP INDEX userlast_ndx
Can't DROP 'userlast_ndx'. Check that column/key existsSQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Last DROP INDEX Luid_ndx
Can't DROP 'Luid_ndx'. Check that column/key existsSQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Last ADD PRIMARY KEY Last_index(L_Uid,L_Board)
Multiple primary key definedSQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX w3t_Postsindex10
Can't DROP 'w3t_Postsindex10'. Check that column/key existsSQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX w3t_Postsindex10
Can't DROP 'w3t_Postsindex10'. Check that column/key existsSQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX topic_index
Can't DROP 'topic_index'. Check that column/key existsSQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX topic_ndx
Can't DROP 'topic_ndx'. Check that column/key existsSQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX w3t_Postsindex9
Can't DROP 'w3t_Postsindex9'. Check that column/key existsSQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX w3t_Postsindex9
Can't DROP 'w3t_Postsindex9'. Check that column/key existsSQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX w3t_Postsindex6
Can't DROP 'w3t_Postsindex6'. Check that column/key existsSQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Posts DROP INDEX w3t_Postsindex6
Can't DROP 'w3t_Postsindex6'. Check that column/key existsSQL ERROR: Thu, Feb 05 2004 12:20:57 +0100 Unable to do_query: ALTER TABLE w3t_Posts ADD INDEX board_topic_ndx (B_Board,B_Topic)
Duplicate key name 'board_topic_ndx'Creating a cache table for some things that don't change to often...
Creating a table to store version info to make future upgrades easier...
Done



After upgrade I login as administrator and confirm changes by editing forum config. After that i run altertable-6.4-6.4.1.php and confirm changes again.

Everything works fine except polls. Some of them are converted and some are not. Those which works, are nicely displayed by doincludepoll.php, and those which doesn't work are displayed like in 6.3 (as form in post body).

If You need any more details, please tell me, I'll provide it.

Thanks in advance,
Hubert.

what I found is that Polls with text in the body of the Post before the Poll do not work after conversion but Polls with Only the Poll in the body worked without a glitch. SO I suppose you could use something like phpmyadmin and delete the text in a Poll or something like that Also I found that Polls that were not in the first Post in a thread would someties have problems too. This is probably by default because the converter probably could not "Find" the Poll in the thread with many posts or with text first (maybe it could not find the Poll Start tag or something like that.

Those errors indicate that you already ran the altertable. The altertable should only be run once.

Table 'w3t_Queries' already existsAdding a new field to the Groups table to allow for disabling of groups.

Unfortunatly running the altertable more than once can really mess stuff up. You might need to restore your database to 6.3 format from a backup and try again.

Also, In searching here - you won't find much info on upgrading or installing recent version, as official support for install/upgrade moved to www.ubbcentral.com.

[]JoshPet said:
Those errors indicate that you already ran the altertable. The altertable should only be run once.
[/]

I also see that those errors indicates that, but I'm 100% sure that I ran it only once The whole process looks like that:

I type the link to altertable file in the browser (Opera) and hit enter. Since then mysql uses 99% of cpu resources. But my browser seems to be stalled. First it looks up a hostname, then contacts host and awaits for page. It waits like this for some time and suddenly presents the output shown above.

I'm running out of ideas , but is there a way, that browser could resend request during the timeout or something. I tried this on IE and on another server with the same database and still the same problem. I even exported the database to txt files, installed fresh UBBT 6.3 and imported the database. Then tried to do the trick, and still the same.


[]JoshPet said:
Also, In searching here - you won't find much info on upgrading or installing recent version, as official support for install/upgrade moved to www.ubbcentral.com. [/]

I searched there too, but didn't find anything helpfull either.

It's a good idea to run altertables via command line if you have a large database. You don't have to worry about timeouts of your webserver.

Could You tell me how to run PHP script from command line? Only way I know, is to use the web server.

Ok, I figured out how to run it from command line. It was simple php altertable-6.3-6.4.php
This time I didn't get any timeouts and altertable completed succesfuly. My polls are ok, however I got some errors in error log:



and some errors about droping indexes, but as altertable says, I shouldn't worry about them


After upgrade everything seems to be fine, but errors indicate that something wasnt finished properly. So may question is ... should I worry about it or maybe theese errors are harmless?