data error after move to new host server

[gregoryaide@ear]

We just moved the site to the a new host. Most things seem to be working, but am having a problem with the UserCP link.

This is the error message I get when I try to access the UserCP link

Database error in vBulletin 3.0.7:

Invalid SQL:
SELECT thread.threadid, thread.forumid
FROM thread AS thread, subscribethread AS subscribethread
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = thread.threadid AND type = 'thread')
WHERE subscribethread.threadid = thread.threadid
AND subscribethread.userid = 1
AND thread.visible = 1
AND lastpost > 1160424761
AND deletionlog.primaryid IS NULL

mysql error: Unknown column 'thread.threadid' in 'on clause'

mysql error number: 1054

Date: Tuesday 10th of October 2006 01:10:14 PM
Script: http://ourppa.com/forums/usercp.php?
Referer: http://ourppa.com/forums/index.php?
Username: Gregory Aide
IP Address: 70.151.188.30

[Floris]

Could you explain to me how you have moved your data from host A to B ?

This might explain why you receive errors.

[gregoryaide@ear]

My hosting company moved the data for me. I think they just copied the tables from the old server onto theirs.

This is what they sent me regarding the error.
The error is being caused by the query on line 269 of usercp.php. You are running vb version 3.0.7. We are running MySQL version 5.0.24a.

I will ask Samuel to clarify the move process.

[gregoryaide@ear]

how we moved the files

We couldn't get a full dump of the files after two attempts using phpmyadmin, so we dumped each table individually with phpMyAdmin. Then ftp'd the files over. Finally, we ran 'mysql mydb < table1.sql' for each table.

[gregoryaide@ear]

more info from my host.

The problem isn't the process by which the files were moved but
instead a query that in older versions of MySQL would always
return an empty set but in newer versions spews an error because
of the left outer join on a primary key for a table where that
key is NULL which is impossible because it is autoincremented on
every insert by MySQL.


mysql> select count(*) from deletionlog where primaryid IS NULL;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)


mysql> select count(*) from thread where threadid IS NULL;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)


This holds true on the new host as well as the old host.

[Steve Machol]

Please post the structure of your thread table so we can compare it to the default 3.0.7 table.

[gregoryaide@ear]

Here's the info vb requested.

mysql> describe thread;
+--------------+----------------------+------+-----+---------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+--------------+----------------------+------+-----+---------
+----------------+
| threadid | int(10) unsigned | NO | PRI | NULL |
auto_increment |
| title | varchar(250) | NO | |
| |
| firstpostid | int(10) unsigned | NO | | 0
| |
| lastpost | int(10) unsigned | NO | | 0
| |
| forumid | smallint(5) unsigned | NO | MUL | 0
| |
| pollid | int(10) unsigned | NO | MUL | 0
| |
| open | smallint(6) | NO | | 0
| |
| replycount | int(10) unsigned | NO | | 0
| |
| postusername | varchar(50) | NO | |
| |
| postuserid | int(10) unsigned | NO | MUL | 0
| |
| lastposter | varchar(50) | NO | |
| |
| dateline | int(10) unsigned | NO | | 0
| |
| views | int(10) unsigned | NO | | 0
| |
| iconid | smallint(5) unsigned | NO | MUL | 0
| |
| notes | varchar(250) | NO | |
| |
| visible | smallint(6) | NO | | 0
| |
| sticky | smallint(6) | NO | | 0
| |
| votenum | smallint(5) unsigned | NO | | 0
| |
| votetotal | smallint(5) unsigned | NO | | 0
| |
| attach | smallint(5) unsigned | NO | | 0
| |
| similar | varchar(55) | NO | |
| |
+--------------+----------------------+------+-----+---------
+----------------+
21 rows in set (0.00 sec)

[Steve Machol]

Here is the 3.0.7 thread table structure and query:

CREATE TABLE thread (
threadid INT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(250) NOT NULL DEFAULT '',
firstpostid INT UNSIGNED NOT NULL DEFAULT '0',
lastpost INT UNSIGNED NOT NULL DEFAULT '0',
forumid SMALLINT UNSIGNED NOT NULL DEFAULT '0',
pollid INT UNSIGNED NOT NULL DEFAULT '0',
open SMALLINT NOT NULL DEFAULT '0',
replycount INT UNSIGNED NOT NULL DEFAULT '0',
postusername CHAR(50) NOT NULL DEFAULT '',
postuserid INT UNSIGNED NOT NULL DEFAULT '0',
lastposter CHAR(50) NOT NULL DEFAULT '',
dateline INT UNSIGNED NOT NULL DEFAULT '0',
views INT UNSIGNED NOT NULL DEFAULT '0',
iconid SMALLINT UNSIGNED NOT NULL DEFAULT '0',
notes VARCHAR(250) NOT NULL DEFAULT '',
visible SMALLINT NOT NULL DEFAULT '0',
sticky SMALLINT NOT NULL DEFAULT '0',
votenum SMALLINT UNSIGNED NOT NULL DEFAULT '0',
votetotal SMALLINT UNSIGNED NOT NULL DEFAULT '0',
attach SMALLINT UNSIGNED NOT NULL DEFAULT '0',
similar VARCHAR(55) NOT NULL DEFAULT '',
PRIMARY KEY (threadid),
KEY iconid (iconid),
KEY postuserid (postuserid),
KEY pollid (pollid),
KEY forumid (forumid, visible, sticky, lastpost)
);

Other than the INTs instead of the INT(10) you have I can't find any other differences.

My best guess is that the 3.0.7 database backup is simply not compatible with MySQL 5. Unfortuantely the only way I know to fix this is to downgrade MySQL to the same version that the backup was created on and try to restore again.

The other option would be to try and upgrade to at least 3.5.5.

[Steve Machol]

Ah, here's something I just read that might help:

http://www.vbulletin.com/forum/showthread.php?p=1240049#post1240049

Here's the bug:

http://www.vbulletin.com/forum/bugs.php?do=view&bugid=4207