Error when hitting the Blog Subscriptions Button

[Diggazz]

Database error in vBulletin 3.6.8:
Invalid SQL:
SELECT SQL_CALC_FOUND_ROWS blog_user.entries, comments, lastblog, lastblogid, lastblogtitle, blog_user.title, blog_subscribeuser.type, blog_user.ratingnum, blog_user.ratingtotal,
user.username, user.userid, options_ignore, options_buddy, options_everyone, ignored.relationid AS ignoreid, buddy.relationid AS buddyid
FROM vb3_blog_subscribeuser AS blog_subscribeuser
INNER JOIN vb3_user AS user ON (user.userid = blog_subscribeuser.bloguserid)
LEFT JOIN vb3_blog_user AS blog_user ON (blog_user.bloguserid = blog_subscribeuser.bloguserid)
LEFT JOIN vb3_userlist AS buddy ON (buddy.userid = user.userid AND buddy.relationid = xxx AND buddy.type = 'buddy')
LEFT JOIN vb3_userlist AS ignored ON (ignored.userid = user.userid AND ignored.relationid = xxx AND ignored.type = 'ignore')
WHERE blog_subscribeuser.userid = xxx
ORDER BY blog_user.lastblog DESC
LIMIT 0, 20;
MySQL Error : Column 'comments' in field list is ambiguous
Error Number : 1052
Date : Thursday, August 30th 2007 @ 01:33:48 AM
Script : http://www.xtremegamingnetwork.com.au/Forums/blog_subscription.php?do=userlist
Referrer : http://www.xtremegamingnetwork.com.au/Forums/blog_usercp.php?do=editoptions
IP Address :
Username : Diggazz
Classname : vb_database



Did I do something incorrectly ?

[Zachery]

In config.php try setting $config['Database']['force_sql_mode'] = false;
to $config['Database']['force_sql_mode'] = true;

[Freddie Bingham]

You probably have a comments field in the user table from a hack. You'll need to remove it or rename it.

[Diggazz]

Thanks

Zachery - that had no effect

Freddie - I will investigate this tomorrow as its 2.30 am here :eek:


thank you both for your prompt response

[Marty1970]

I have the same error, but I'm confused @Freddie Bingham post, file blog_subscriptions.php requests this "comments" information :

line 429

SELECT SQL_CALC_FOUND_ROWS blog_user.entries, comments, lastblog, What exactly did you mean ?

[Zachery]

Do you have another comments table/row in your database?

[Marty1970]

File product-vbblog.xml creates this "comments" row on the blog_user table.


CREATE TABLE " . TABLE_PREFIX . "blog_user (
bloguserid INT UNSIGNED NOT NULL DEFAULT '0',
title VARCHAR(255) NOT NULL DEFAULT '',
description MEDIUMTEXT NOT NULL,
allowsmilie SMALLINT UNSIGNED NOT NULL DEFAULT '1',
options INT UNSIGNED NOT NULL DEFAULT '0',
viewoption ENUM('all','only','except') NOT NULL DEFAULT 'all',
comments INT UNSIGNED NOT NULL DEFAULT '0',
lastblog INT UNSIGNED NOT NULL DEFAULT '0',
lastblogid INT UNSIGNED NOT NULL DEFAULT '0',
lastblogtitle VARCHAR(255) NOT NULL DEFAULT '',
lastcomment INT UNSIGNED NOT NULL DEFAULT '0',
lastcommenter VARCHAR(100) NOT NULL DEFAULT '',
lastblogtextid INT UNSIGNED NOT NULL DEFAULT '0',
entries INT UNSIGNED NOT NULL DEFAULT '0',
deleted INT UNSIGNED NOT NULL DEFAULT '0',
moderation INT UNSIGNED NOT NULL DEFAULT '0',
draft INT UNSIGNED NOT NULL DEFAULT '0',
pending INT UNSIGNED NOT NULL DEFAULT '0',
ratingnum INT UNSIGNED NOT NULL DEFAULT '0',
ratingtotal INT UNSIGNED NOT NULL DEFAULT '0',
rating FLOAT UNSIGNED NOT NULL DEFAULT '0',
subscribeown ENUM('none', 'usercp', 'email') NOT NULL DEFAULT 'none',
subscribeothers ENUM('none', 'usercp', 'email') NOT NULL DEFAULT 'none',
uncatentries INT UNSIGNED NOT NULL DEFAULT '0',
akismet_key VARCHAR(15) NOT NULL DEFAULT '',
options_everyone INT UNSIGNED NOT NULL DEFAULT '0',
options_buddy INT UNSIGNED NOT NULL DEFAULT '0',
options_ignore INT UNSIGNED NOT NULL DEFAULT '0',
isblogmoderator SMALLINT UNSIGNED NOT NULL DEFAULT '0',
comments_moderation INT UNSIGNED NOT NULL DEFAULT '0',
comments_deleted INT UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (bloguserid),
KEY lastblog (lastblog, entries),
KEY ratingnum (ratingnum, rating)
)
");Thus as above with this error.

Database error in vBulletin 3.6.8:

Invalid SQL:

SELECT SQL_CALC_FOUND_ROWS blog_user.entries, comments, lastblog, lastblogid, lastblogtitle, blog_user.title, blog_subscribeuser.type, blog_user.ratingnum, blog_user.ratingtotal,
user.username, user.userid, options_ignore, options_buddy, options_everyone, ignored.relationid AS ignoreid, buddy.relationid AS buddyid
FROM blog_subscribeuser AS blog_subscribeuser
INNER JOIN user AS user ON (user.userid = blog_subscribeuser.bloguserid)
LEFT JOIN blog_user AS blog_user ON (blog_user.bloguserid = blog_subscribeuser.bloguserid)
LEFT JOIN userlist AS buddy ON (buddy.userid = user.userid AND buddy.relationid = 1 AND buddy.type = 'buddy')
LEFT JOIN userlist AS ignored ON (ignored.userid = user.userid AND ignored.relationid = 1 AND ignored.type = 'ignore')
WHERE blog_subscribeuser.userid = 1
ORDER BY blog_user.lastblog DESC
LIMIT 0, 20;

MySQL Error : Column 'comments' in field list is ambiguous
Error Number : 1052
Date : Thursday, August 30th 2007 @ 02:36:10 AM
Script : http://gamers-crib.com/blog_subscription.php?do=userlist
Referrer : http://gamers-crib.com/blog_usercp.php
IP Address : 87.232.32.46
Username : Marty
Classname : vB_DatabaseWhich is called from file blog_subscription.php line 429


SELECT SQL_CALC_FOUND_ROWS blog_user.entries, comments, lastblog, lastblogid, lastblogtitle, blog_user.title, blog_subscribeuser.type, blog_user.ratingnum, blog_user.ratingtotal,So with that in mind are we talking about the same database table.

This is a brand new installation from release vBulletin Blog 1.0.0

[Diggazz]

You probably have a comments field in the user table from a hack. You'll need to remove it or rename it.

No this is not the case

No hacks installed that have a comments field in this new table

This is my first Blog software installation and no other hacks use table prefix vb3_blog_

[FBJunkie]

I am getting this error when clicking on the Blog Subscriptions button. Is there a fix to this?




Database error in vBulletin 3.6.8:
Invalid SQL:
SELECT SQL_CALC_FOUND_ROWS blog_user.entries, comments, lastblog, lastblogid, lastblogtitle, blog_user.title, blog_subscribeuser.type, blog_user.ratingnum, blog_user.ratingtotal,
user.username, user.userid, options_ignore, options_buddy, options_everyone, ignored.relationid AS ignoreid, buddy.relationid AS buddyid
FROM vb_blog_subscribeuser AS blog_subscribeuser
INNER JOIN vb_user AS user ON (user.userid = blog_subscribeuser.bloguserid)
LEFT JOIN vb_blog_user AS blog_user ON (blog_user.bloguserid = blog_subscribeuser.bloguserid)
LEFT JOIN vb_userlist AS buddy ON (buddy.userid = user.userid AND buddy.relationid = 1 AND buddy.type = 'buddy')
LEFT JOIN vb_userlist AS ignored ON (ignored.userid = user.userid AND ignored.relationid = 1 AND ignored.type = 'ignore')
WHERE blog_subscribeuser.userid = 1
ORDER BY blog_user.lastblog DESC
LIMIT 0, 30;
MySQL Error : Column 'comments' in field list is ambiguous
Error Number : 1052
Date : Thursday, August 30th 2007 @ 10:08:04 AM
Script : http://www.mywebsite.com/forums/blog_subscription.php?do=userlist
Referrer : http://www.mywebsite.com/forums/blog_subscription.php?do=entrylist
IP Address : **********
Username : myusername
Classname : vB_Database

[Marty1970]

I am getting this error when clicking on the Blog Subscriptions button. Is there a fix to this?




Database error in vBulletin 3.6.8:
Invalid SQL:
SELECT SQL_CALC_FOUND_ROWS blog_user.entries, comments, lastblog, lastblogid, lastblogtitle, blog_user.title, blog_subscribeuser.type, blog_user.ratingnum, blog_user.ratingtotal,
user.username, user.userid, options_ignore, options_buddy, options_everyone, ignored.relationid AS ignoreid, buddy.relationid AS buddyid
FROM vb_blog_subscribeuser AS blog_subscribeuser
INNER JOIN vb_user AS user ON (user.userid = blog_subscribeuser.bloguserid)
LEFT JOIN vb_blog_user AS blog_user ON (blog_user.bloguserid = blog_subscribeuser.bloguserid)
LEFT JOIN vb_userlist AS buddy ON (buddy.userid = user.userid AND buddy.relationid = 1 AND buddy.type = 'buddy')
LEFT JOIN vb_userlist AS ignored ON (ignored.userid = user.userid AND ignored.relationid = 1 AND ignored.type = 'ignore')
WHERE blog_subscribeuser.userid = 1
ORDER BY blog_user.lastblog DESC
LIMIT 0, 30;
MySQL Error : Column 'comments' in field list is ambiguous
Error Number : 1052
Date : Thursday, August 30th 2007 @ 10:08:04 AM
Script : http://www.mywebsite.com/forums/blog_subscription.php?do=userlist
Referrer : http://www.mywebsite.com/forums/blog_subscription.php?do=entrylist
IP Address : **********
Username : myusername
Classname : vB_Database


doesn't seem to be, yet... you could of course delete the 'comments' row from your blog_user table, but thats not the answer, I'm sure the 'comments' row is there for a reason.

[Freddie Bingham]

One of the following tables has a comments field that is not part of vBulletin.

blog_subscribeuser
user
userlist

You can either find and remove/rename the field or you can edit the source code of blog_subscription.php and change comments to blog_user.comments

[FBJunkie]

If removing/renaming the fields or editing the source code in the file, will this affect comments functions in other areas other than the blog?

[Marty1970]

One of the following tables has a comments field that is not part of vBulletin.

blog_subscribeuser
user
userlist

You can either find and remove/rename the field or you can edit the source code of blog_subscription.php and change comments to blog_user.comments

Thank you for the explanation, I had assumed the source code was only looking at blog_user table row comments, but now it makes sense.

So a quick fix for anyone else: open blog_subscriptions.php around line 429 find :
SELECT SQL_CALC_FOUND_ROWS blog_user.entries, comments, lastblog, lastblogid, lastblogtitle, blog_user.title, blog_subscribeuser.type, blog_user.ratingnum, blog_user.ratingtotal,and replace with :
SELECT SQL_CALC_FOUND_ROWS blog_user.entries, blog_user.comments, lastblog, lastblogid, lastblogtitle, blog_user.title, blog_subscribeuser.type, blog_user.ratingnum, blog_user.ratingtotal,Hope this helps...and remember this is something you guys and girls need to remember should there be an upgrade of this file.

[Freddie Bingham]

What you need to remember, is that you have a field, installed by something other than vBulletin or the blog that conflicts with the blog. One of you send me a phpMyAdmin login and I'll tell you exactly what it is.

[Marty1970]

maybe this new blog addon, needs to be more ''''commentary'''#,

[Kihon Kata]

This is what I did, it seemed to fix my DB error.

But can I ask, is this the only thing we need to do?



Thank you for the explanation, I had assumed the source code was only looking at blog_user table row comments, but now it makes sense.

So a quick fix for anyone else: open blog_subscriptions.php around line 429 find :
SELECT SQL_CALC_FOUND_ROWS blog_user.entries, comments, lastblog, lastblogid, lastblogtitle, blog_user.title, blog_subscribeuser.type, blog_user.ratingnum, blog_user.ratingtotal,and replace with :
SELECT SQL_CALC_FOUND_ROWS blog_user.entries, blog_user.comments, lastblog, lastblogid, lastblogtitle, blog_user.title, blog_subscribeuser.type, blog_user.ratingnum, blog_user.ratingtotal,Hope this helps...and remember this is something you guys and girls need to remember should there be an upgrade of this file.

[FBJunkie]

I applied the file edit posted by Marty and it fixed my problem. Hopefully, this file edit doesn't interfere with anything else.

[Freddie Bingham]

I looked at FBJunkie's database and, yes, he has a comments field in the user table. This was added by a hack/plugin. There is no bug here guys. I've added blog_user. to the call we make in the query though.

[FBJunkie]

So do I need to keep the file edit that's in post #16 of this thread, which fixed the problem for me? Or change it back since 'blog_user' was added to the query?

[Kihon Kata]

So do I need to keep the file edit that's in post #16 of this thread, which fixed the problem for me? Or change it back since 'blog_user' was added to the query?
I have the same question

[FBJunkie]

I changed my file back and get the error again. So I guess the above file edit is needed to keep the error from happening?

[Kihon Kata]

I changed my file back and get the error again. So I guess the above file edit is needed to keep the error from happening?
Same thing for me. Can someone tell us pls?

[Freddie Bingham]

I made the change to the next release, until then you'll need to either remove the hack that is causing the problem OR rename the field in your database and modify your hack to deal with it OR alter blog_subscription.php.

[PitchouneN64ngc]

Freddie, do you know which hack do that?

[Kihon Kata]

I made the change to the next release, until then you'll need to either remove the hack that is causing the problem OR rename the field in your database and modify your hack to deal with it OR alter blog_subscription.php.
Thanks Freddie, for now, I'll just leave my blog_subscription.php modded. Once the new release is out, I'll update it.

Thanks again

[Diggazz]

Thankyou everyone for assisting this fix

I like Marty was concentrating in the new blog tables

Good work Marty for posting the line

Thanks Freddie for addressing this in the next update.

I am curious as to the offending hack if anyone works it out though.

Keep up the good work VB team

[Freddie Bingham]

Freddie, do you know which hack do that?
I have no idea, I don't keep up on plugins/hacks.