UBB.Developers Forums UBB.threads 7 Development How Do I? 6.4b1 Upgrade Error

I used the change file method to upgrade because I have so many hacks and I ran the altertable for 6.3 to 6.4. At this point my forums will not display, here is the error I get on the top of the main site:

I would look in mysql error logs for a detail of the problem.

I would think offhand from my experience that it is related to the user table and the addition of the birthday field. You need to use phpmyadmin and check out the table. You can open up the altertable file and may have to manually add the user table 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)

Now if the above query does not run try running

ALTER TABLE w3t_Users
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)

At least thats what I remember when I upgraded a clean a few clean installs but all that had the birthday hack.

Neither of those worked. I suspected out of all the hacks I have installed that this wouldn't be a clean upgrade lol. Any other ideas?

update, even though I got errors with the command something took because the site is displaying properly now.

Glad it worked for you.

Omegatron, I am not sure if this is causing my issue or not but when birthday's are entered they are not kept (I am also having this issue with avars, it only seems to be on my site) you can enter it fine but then when you go back it's gone.

You would have to check your tables and check and see if they were formed.

Josh posted a birthday script to modify and update peoples birthdays to the new 6.4 on here

Yes, if you were running the birthday mod - then the altertable will fail until you run my conversion script - because the U_Birthday field already exists.

Not sure which script that is, is it the bday cron script? https://www.ubbdev.com/forum/showflat...prev=#Post86334

No - did you run the Birthday mod before?

If so you need this:
https://www.ubbdev.com/forum/showflat...art/3#Post96491

Okay I ran this, also keep in mind that I did what Omegatron explained above. Nothing is working now, I can't explain all of it just jump in over there and try to look at posts or profiles. If need be I will shut down and pull out a backup, please let me know.

Here is what the error log reports. I went ahead and closed the boards until it's worked out.

Unable to do_query: SELECT t1.B_Number,t2.U_Username,t1.B_Posted,t1.B_IP,t1.B_Subject,t1.B_Body,t1.B_File,t1.B_Status,t1.B_Approved,t2.U_Picture,t1.B_Reged,t2.U_Title,t2.U_Color,t1.B_Icon,t1.B_Poll,t1.B_Parent,t2.U_Status,t2.U_Signature,t1.B_LastEdit,t1.B_LastEditBy,t2.U_Location,t2.U_TotalPosts,t2.U_Registered,t2.U_Rating,t2.U_Rates,t2.U_RealRating,t2.U_PicWidth,t2.U_PicHeight,t2.U_Number,t1.B_FileCounter,t1.B_AnonName,t1.B_ParentUser,t2.U_Birthday,t2.U_ShowBday,t1.B_AddSig,t2.U_Visible FROM w3t_Posts AS t1, w3t_Users AS t2 WHERE t1.B_Main = 16373 AND t1.B_PosterId = t2.U_Number AND B_Approved = 'yes' ORDER BY B_Number LIMIT 20 - Unknown column 't2.U_Birthday' in 'field list'

(sorry about the scroll)

I am going to pull out a backup. This disclaimer in your post Josh (Backup your database first! I developed this for my own needs so use at your own risk. ) is not kidding

I found that his script added the birthday fields for the users but then made everything out of whack as it renames U_Birthday to U_BirthdayOLD and never made a NEW U_Birthday

It has this in it, doesn't this add it?

Yeah well when I ran mine it said database only available to admins LOL I was logged in as admin

It should add it.

Were there any errors that it couldn't be run.

The problem is that we had a U_BirthDay field from the old mod - the the 6.4 altertable fails on the whole query when it tries to add a bunch of new stuff.

I can help you get it fixed - but right now I get "forbidden" errors.

If you've got PhpMyAdmin - PM me with access and I should be able to get you all fixed up.

I think that some fields didn't get properly added when you did the 6.4 upgrade.

Well just as an FYI Josh when I ran it on my test forum it converted the users and changed the field name to old but never created a new one.

I wonder if the initial 6.4 altertable query did not fail for you - then mine just redoes that query - but some of the fields already existed, so the query failed.

In other words - it puzzled me that the altertable failed, although it doesn't on all servers. My Field was U_BirthDay - the new field is U_Birthday but on a Linux server, it says the field already exists. Not sure if different setups might not have had a problem with it.

Try this one - rename it .php and run it.

I split the creation of the new fields into separate queries, so if one of the fields already exists, it won't blow the whole thing.

Thanks Josh but I went ahead and had Jeremy put last night's backup on. No problem, I was warned

Is it fixed now?

Did the fields get added for 6.4?

It just got put back up by Jeremy (last nights backup) so I haven't tried it yet, I trust you though so if you say it's safe to run now I will go ahead and do it?

Do you want me to look?

You're a client of mine so you get primo service.

Sure.. have at it

OK _ just looked.

The U_Birthday field (new one) never got created, because you have U_BirthDay from my mod.

The other 6.4 fields are there.

My new script should work for you -

As it changes the old one - then goes through one by one and adds any fields that might have been missed. It'll fail on the ones that you've already got - but otherwise should be fine, because I split the queries out to add each field separately.... like this:

// Rename the old field
$query = "
ALTER TABLE {$config['tbprefix']}Users
CHANGE U_BirthDay U_BirthDayOLD INT( 2 ) UNSIGNED DEFAULT NULL
";
$sth = $dbh -> do_query($query);

echo "We need to create the new birthday fields - in case the 6.4 altertable couldn't do this at the time. You might get an error here too, which is OK.<br /><br />";
// Add the 6.4 fields in case the query to add them failed
$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_Birthday VARCHAR(10) NOT NULL DEFAULT '0',
";
$dbh -> do_query($query);

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_ShowBday INT(1) NOT NULL DEFAULT '0',
";
$dbh -> do_query($query);

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_TimeFormat VARCHAR(6),
";
$dbh -> do_query($query);

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_Ignored TEXT,
";
$dbh -> do_query($query);

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_OC_Userid VARCHAR(64),
";
$dbh -> do_query($query);

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD INDEX birthday_ndx (U_Birthday,U_ShowBday)
";
$dbh -> do_query($query);



Give it a go and let me know.

I just ran the new script (attached for refrence) and the site crashed again, do you mind checking it out Josh? Go to any post and you will see. Thanks.

Fixed - not sure why it didn't get created for you - fixed it. And will tweak and repost this script a bit.

It did not create on my test site as well

Thanks, so you added it as well? My original issue is still the same, it will not save the birthday settings. When you go into your profile and change it, it reverts back to Jan, 1 2003.

You are still using the old changebasic.php - hasn't been updated since August.

Try uploading the one with 6.4 and that should do it.

No error I can run the queries that Josh's script and they work. Now the problem is when I run the script as a script it renames the old table but never creates the new one.

Doh!! I knew it was something I missed I was also wondering how to tell what files I missed, great way.

Josh,

This is what I found with the script. I tried twice on linux.

The script will alter the old U_BirthDay field and rename it to old, However it will not create a new one. WEIRD

Anyway I went in by hand and made the field then was able to update the users to the new format.

Try this script.

If thats the one you posted a few posts back I tried that one as well.

No that one has never been posted.... the other one had som stray commas when I split the queries apart.

This is what I was talking about

<?

require("main.inc.php");

echo "We need to rename the old field. You might get an error here, which is OK. <br /><br />";
// Rename the old field
$query = "
ALTER TABLE {$config['tbprefix']}Users
CHANGE U_BirthDay U_BirthDayOLD INT( 2 ) UNSIGNED DEFAULT NULL
";
$sth = $dbh -> do_query($query);

echo "We need to create the new birthday fields - in case the 6.4 altertable couldn't do this at the time. You might get an error here too, which is OK.<br /><br />";
// Add the 6.4 fields in case the query to add them failed
$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_Birthday VARCHAR(10) NOT NULL DEFAULT '0'
";
$dbh -> do_query($query);

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_ShowBday INT(1) NOT NULL DEFAULT '0'
";
$dbh -> do_query($query);

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_TimeFormat VARCHAR(6)
";
$dbh -> do_query($query);

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_Ignored TEXT
";
$dbh -> do_query($query);

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_OC_Userid VARCHAR(64)
";
$dbh -> do_query($query);

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD INDEX birthday_ndx (U_Birthday,U_ShowBday)
";
$dbh -> do_query($query);

That sure looks the same as post 99336 which I tried.

No worries the last part works

All I was saying is every script you posted renamed the old one but does not create the new one.

No the old one had commas after the ADD statements (as they all used to be in one big query) which would have caused errors.

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_ShowBday INT(1) NOT NULL DEFAULT '0',
";
$dbh -> do_query($query);

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_TimeFormat VARCHAR(6),
";
$dbh -> do_query($query);

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_Ignored TEXT,

See the errors at the end of the ADD lines with nothing after them? Those caused SQL errors.

So with the latest script -
When it runs this query:

$query = "
ALTER TABLE {$config['tbprefix']}Users
ADD U_Birthday VARCHAR(10) NOT NULL DEFAULT '0'
";
$dbh -> do_query($query);



What SQL error do you get - either has to create or give you an error.