Trying to upgrade, database error 1146

[billp]

Newbie, at best. I'm preparing to move my forum to another host. I've set up the domain and DB at new host. I took all of the files from the old host and uploaded to new host. Ran install and went ok, but none of the current stuff/mods/etc were on new one.

I then uploaded my DB from the backup. I then get a message that I needed to run upgrade and maybe run bitfield repair. So I did upgrade, and then with tools.php attempted to repair bitfields, All that I see is Update bitfields cache. Which I did, nothing seems to "fix" this.

In the end I get this message:

Database error in vBulletin 3.6.8:

Invalid SQL:

SELECT COUNT(*) AS count
FROM attachment AS attachment
INNER JOIN post AS post USING (postid)
WHERE attachment.visible = 0;

MySQL Error : Table 'xxxxxx_vb.post' doesn't exist
Error Number : 1146
Date : Sunday, October 25th 2009 @ 09:29:57 PM
Script : http://mydomain/admincp/index.php?do=home
Referrer : http://mydoman/admincp/index.php
IP Address : xxxxxxxx
Username : xxxxxxxx
Classname : vB_Database

I then attempted to create the post table, using the info from the old, to the new. I get an error 1170 from that side. I did make postid the primary and the other info appears to be right.

#1170 - BLOB/TEXT column 'pagetext' used in key specification without a key length

This may be related, the userid, threadid, title, in one of the errors showthat these are a "key" does that mean that I need to click on "unique" with these?

Any help would be appreciated.

[McCarroll21]

When you moved it over, the best thing to do would have probably been import your backup to the new server, then run the upgrade script.

[billp]

When you say backup, are you referring just the files, without the database?

What puzzles me is why VB doesn't make the post table when you install it. You obviously need it. And in this case you are trying to bring a DB across with a post table in it.

Thanks

[McCarroll21]

Backup the database on the current server and import it on the new server. You'll also need to move all the files over to the new server too.

Then when everything is moved over, upload the files for the version you are trying to upgrade to and overwrite all the files from the previous version. You'll then want to run the upgrade script so that it will be using your database as the source.

Try reading through this thread for more help: http://www.vbulletin.com/forum/showt...Upgrade-method

The only difference is that you also have to take the step of moving everything to another server.

[billp]

Well, some progress, if I went in and put a value in for posttext (10000) it put the table in, but when I go to import the post table from the old database, it says that there is no post table and indeed it is gone.

Over at the new host, I re-ran install which caught that it was already there and said run upgrade, which I did and it wasn't my modified version, but at least up. When I went to look at a post, the first time it said that there were no post, after attempting to import the post table, and then trying to post it said "no post table"

Any ideas/direction? Thanks

[billp]

Well, I'm still learning. VB does make the post table. It appears that when I up load the old database, it kills the post table. Is that possible? What needs to be address to solve this, obviously, the old post need to be salvaged.

[billp]

More info. I uploaded the DB without the post table , it told me to re-run upgrade and repair the tables, which I did. Everything looked fine in Admin, except there was no post. When I tried to go to home page, just a blank page. Never got around that. Decided to see what would happen if I uploaded the post table, backed up separate. It kills the post table and gives me the above error 1174 about the key lenght.

Any idea? Thanks

[billp]

Progress I think, I messed around until, I find that if I don't include this post table, in the backup then it doesn't kill/delete the post table in the new DB.

I get this error:

CREATE TABLE `post` (
`postid` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
`threadid` int( 10 ) unsigned DEFAULT '0' NOT NULL ,
`parentid` int( 10 ) unsigned DEFAULT '0' NOT NULL ,
`username` varchar( 100 ) NOT NULL ,
`userid` int( 10 ) unsigned DEFAULT '0' NOT NULL ,
`title` varchar( 250 ) NOT NULL ,
`dateline` int( 10 ) unsigned DEFAULT '0' NOT NULL ,
`pagetext` mediumtext,
`allowsmilie` smallint( 6 ) DEFAULT '0' NOT NULL ,
`showsignature` smallint( 6 ) DEFAULT '0' NOT NULL ,
`ipaddress` char( 15 ) NOT NULL ,
`iconid` smallint( 5 ) unsigned DEFAULT '0' NOT NULL ,
`visible` smallint( 6 ) DEFAULT '0' NOT NULL ,
`attach` smallint( 5 ) unsigned DEFAULT '0' NOT NULL ,
`infraction` smallint( 5 ) unsigned DEFAULT '0' NOT NULL ,
`reportthreadid` int( 10 ) unsigned DEFAULT '0' NOT NULL ,
PRIMARY KEY ( `postid` ) ,
KEY `userid` ( `userid` ) ,
KEY `threadid` ( `threadid` , `userid` ) ,
KEY `title` ( `title` , `pagetext` )
) TYPE = MYISAM ;

#1170 - BLOB/TEXT column 'pagetext' used in key specification without a key length

Looking through my tables I find others that appear to be have the same appearance with the "pagetext mediumtext NULL = yes" field

I'm just not bright enough to figure this out or find the solution yet on the web.

Thanks for any assistance

[billp]

One last time, I hope. I think that I've solved the mystery. When I used VB to back up the DB, and brought it over, it worked right. I was using MySQL Quick Admin provided by my present host to do the back up. I think that it was corrupting that table. Thanks for taking to read this saga. I'm now ready to upgrade to 3.8.4 but its 2AM so maybe tomorrow.

Any other suggestions welcome.