Database errors

[hornstar6969]

After upgrading this is the error im getting. I get a couple each hour almost.

Database error in vBulletin 3.6.0:

Invalid SQL:

SELECT post.*
FROM vb3_post AS post
LEFT JOIN vb3_deletionlog AS deletionlog ON(deletionlog.primaryid = post.postid AND type = 'post')

WHERE threadid = 17463
AND dateline > 1154612215

AND visible = 1 AND deletionlog.primaryid IS NULL

AND postid <> 160173

ORDER BY dateline DESC
LIMIT 1;

MySQL Error : Column 'dateline' in where clause is ambiguous
Error Number : 1052

Date : Saturday, August 5th 2006 @ 08:36:55 AM
Script : http://www.gamerzneeds.net/forums/ne...treply&t=17463

Classname : vb_database

Any ideas on how to fix this database error?

[Steve Machol]

This error is from a hack or plugin you installed that created a second 'dateline' field. You need to check your add-ons to find out which one did this and remove the changes it made to the database.

[Paul M]

That's not quite true.

The problem is that 3.6.0 has added a dateline column to the deletionlog table, so the WHERE clause no longer knows which one to use - post.dateline or deletionlog.dateline.

Your right about it being a mod though - it's the prevent double posting mod, the fix is here ;

http://www.vbulletin.org/forum/showpost.php?p=1019223

[hornstar6969]

Thanks guys, I was updating all the mods i installed, and i found that fix yesterday, but that was the problem, thanks.

However I'm having a worse issue now, in the admincp, when i go to change someones username (and probably other things as well) I get this database error.

Database error in vBulletin 3.6.0:

Invalid SQL:
select w.*,u.username as wusername,u.userid as wuserid,wt.* from
vb3_warnings w
left join vb3_user u on(u.userid=w.warned_by)
left join vb3_warning_types wt on(wt.tid=w.warned_warning_id)
where w.warned_user='14353' order by w.warned_time DESC;

MySQL Error : Table 'abc_def.ab1_warnings' doesn't exist
Error Number : 1146

I used to have advanced warning system installed, but uninstalled that.

Has it deleted that table that infractions uses? How do I fix this problem?

[Steve Machol]

Since there is no warnings table in vB, this query is from a hack or plugin you've installed. To fix it, disable your plugins and reyupload the original vB files.

[hornstar6969]

I'll try that in a second, but uploading the files again, how is that going to fix the missing table in the database?

[Paul M]

Uploading the files is unlikely to make any difference unless you have edited them since you uploaded them before, or you didn't uppload them all last time. Disabling your plugins might cure it. The problem is that you have some code left over from the warnings hack that is still running, and hence trying to read the now deleted table.

When you uninstalled it, did you delete any files it put in the includes/xml folder ?

[hornstar6969]

Uploading the files is unlikely to make any difference unless you have edited them since you uploaded them before, or you didn't uppload them all last time. Disabling your plugins might cure it. The problem is that you have some code left over from the warnings hack that is still running, and hence trying to read the now deleted table.

When you uninstalled it, did you delete any files it put in the includes/xml folder ?

When I uninstalled the aws hack, i just clicked uninstall from the plugin menu. As I had just upgraded, im fairly sure all the templates would have been reverted.

Does infractions read from that missing table "vb3_warnings"?


This is the actual database error I get from the browser

Database error in vBulletin 3.6.0:

Invalid SQL:
UPDATE vb3_user SET
### Bitfield: vb3_user.options ###
options = IF(options & 8, options - 8, options),
options = IF(options & 1024, options, options + 1024),
options = IF(options & 16, options - 16, options),
options = IF(options & 256, options - 256, options),
options = IF(options & 512, options - 512, options),
options = IF(options & 32, options, options + 32),
options = IF(options & 2048, options, options + 2048),
options = IF(options & 4096, options - 4096, options),
options = IF(options & 1, options, options + 1),
options = IF(options & 2, options, options + 2),
options = IF(options & 4, options, options + 4),
options = IF(options & 64, options, options + 64),
options = IF(options & 128, options - 128, options),
options = IF(options & 32768, options - 32768, options),
### Bitfield: vb3_user.adminoptions ###
adminoptions = IF(adminoptions & 1, adminoptions - 1, adminoptions),
adminoptions = IF(adminoptions & 2, adminoptions - 2, adminoptions),
usertitle = 'Chick',
customtitle = 0,
username = 'testesteste1',
email = '[email protected]',
languageid = 1,
homepage = '',
birthday_search = '1990-08-01',
birthday = '08-01-1990',
showbirthday = 0,
icq = '',
aim = '',
yahoo = '',
msn = '',
skype = '',
parentemail = '',
referrerid = 0,
ipaddress = '165.21.154.117',
posts = 0,
usergroupid = 3,
reputationlevelid = 5,
reputation = 10,
warnings = 0,
infractions = 0,
ipoints = 0,
pmpopup = 1,
autosubscribe = 1,
threadedmode = 0,
showvbcode = 1,
styleid = 0,
timezoneoffset = '8',
daysprune = 0,
joindate = 1134185460,
lastactivity = 1134185460,
lastpost = -18001,
membergroupids = '',
vbp_receivespoints = 1,
vbp_storeaccess = 1,
vbbux = 500,
vbbank = 500,
vbp_nextbankinterest = 0,
vbp_genericperm = 0,
vbp_modperm = 0,
vbp_adminperm = 0,
vbp_nametextcolor = '',
vbp_nameglowcolor = '',
vbp_nameshadowcolor = '',
vbp_namefonttype = '',
vbp_titletextcolor = '',
vbp_titleglowcolor = '',
vbp_titleshadowcolor = '',
vbp_titlefonttype = '',
vbp_changealwaysitems = '',
### Bitfield: vb3_user.vbp_displayflags ###
vbp_displayflags = IF(vbp_displayflags & 1, vbp_displayflags - 1, vbp_displayflags),
vbp_displayflags = IF(vbp_displayflags & 2, vbp_displayflags - 2, vbp_displayflags),
vbp_displayflags = IF(vbp_displayflags & 4, vbp_displayflags - 4, vbp_displayflags),
vbp_displayflags = IF(vbp_displayflags & 8, vbp_displayflags - 8, vbp_displayflags),
vbp_displayflags = IF(vbp_displayflags & 16, vbp_displayflags - 16, vbp_displayflags),
vbp_displayflags = IF(vbp_displayflags & 32, vbp_displayflags - 32, vbp_displayflags),
vbp_displayflags = IF(vbp_displayflags & 64, vbp_displayflags - 64, vbp_displayflags),
vbp_displayflags = IF(vbp_displayflags & 128, vbp_displayflags - 128, vbp_displayflags),
### Bitfield: vb3_user.vbp_otherflags ###
vbp_otherflags = IF(vbp_otherflags & 1, vbp_otherflags - 1, vbp_otherflags),
vbp_otherflags = IF(vbp_otherflags & 2, vbp_otherflags - 2, vbp_otherflags),
vbp_otherflags = IF(vbp_otherflags & 4, vbp_otherflags - 4, vbp_otherflags),
vbp_otherflags = IF(vbp_otherflags & 8, vbp_otherflags - 8, vbp_otherflags),
vbp_otherflags = IF(vbp_otherflags & 16, vbp_otherflags - 16, vbp_otherflags),
vbp_otherflags = IF(vbp_otherflags & 32, vbp_otherflags - 32, vbp_otherflags),
vbp_otherflags = IF(vbp_otherflags & 64, vbp_otherflags - 64, vbp_otherflags),
vbp_otherflags = IF(vbp_otherflags & 256, vbp_otherflags - 256, vbp_otherflags),
### Bitfield: vb3_user.vbp_immunity ###
vbp_immunity = IF(vbp_immunity & 1, vbp_immunity - 1, vbp_immunity),
vbp_immunity = IF(vbp_immunity & 2, vbp_immunity - 2, vbp_immunity),
vbp_immunity = IF(vbp_immunity & 4, vbp_immunity - 4, vbp_immunity),
vbp_immunity = IF(vbp_immunity & 8, vbp_immunity - 8, vbp_immunity),
vbp_immunity = IF(vbp_immunity & 16, vbp_immunity - 16, vbp_immunity),
vbp_immunity = IF(vbp_immunity & 32, vbp_immunity - 32, vbp_immunity),
vbp_immunity = IF(vbp_immunity & 64, vbp_immunity - 64, vbp_immunity)
WHERE userid = 2888;

MySQL Error : Unknown column 'warnings' in 'field list'
Error Number : 1054
Date : Monday, August 7th 2006 @ 06:47:35 AM
Script : http://www.gamerzneeds.net/forums/ad....php?do=update
Referrer : http://www.gamerzneeds.net/forums/ad...do=edit&u=2888
IP Address :
Username :
Classname : vb_database

[Paul M]

As I had just upgraded, im fairly sure all the templates would have been reverted.

It has nothing to do with templates, it's trying to read a column called 'warnings' in your user table - that column (field) no longer exists.

[hornstar6969]

Okay I have looked back at some old hacks ive installed in the past, and have cleaned up files that ive installed. (made my admincp look tidier too)

However I found that the problem is this from vb.org just now.

that's normal since both systems use a database field called "warnings" and when you uninstall aws that field will be deleted ...... run

     ALTER TABLE user ADD warnings INT UNSIGNED NOT NULL DEFAULT '0';

keep TABLE_PREFIX in mind.....

I tried

     ALTER TABLE user ADD vb3_warnings INT UNSIGNED NOT NULL DEFAULT '0';

But got this error

     An error occurred while attempting to execute your query. The following information was returned.
error number: 1146
error desc: Table 'gamerzne_backup.user' doesn't exist

What should I try now?

[Paul M]

That's rather unfortunate of AWS then.

Your query fails because you have the prefix in the wrong place, it should be ;

ALTER TABLE vb3_user ADD warnings INT UNSIGNED NOT NULL DEFAULT '0';

[hornstar6969]

Thanks, that fixed that and the database errors. (I heard that aws maybe getting made for 3.6 and it will have all new database names as infractions used some that were used by aws, I'm going to be sticking with infractions for now, but hopefully they will take onboard my request that I made the other day.)

Thanks