paid subscriptions ACP error

[greigeh]

When i press 'view users' from a Paid Subscription in the ACP i get the following database error

Database error in vBulletin 3.8.8. Patch Level 1:

Invalid SQL:

SELECT COUNT(*) AS users
FROM live_subscriptionlog AS subscriptionlog
LEFT JOIN live_user AS user USING (userid)
WHERE 1=1 AND subscriptionid=85 AND status = 1
AND user.userid = subscriptionlog.userid;

MySQL Error : Column 'status' in where clause is ambiguous
Error Number : 1052
Request Date : Saturday, January 24th 2015 @ 12:08:15 AM
Error Date : Saturday, January 24th 2015 @ 12:08:15 AM
Script : http://----.com/acp/subscriptions.php?do=find&status=1&subscriptionid=85&s=
Referrer : http://-------.com/acp/subscriptions.php?do=modify
IP Address : -----
Username : Greig
Classname : vB_Database
MySQL Version : 5.5.40-cll

Any ideas on what is wrong? (removed some information as private)

Any help appreciated.

[kh99]

It looks to me like a mod has added a column to the user table called 'status', and that broke a query in subscriptions.php. Assuming that you don't want to remove that mod or that column from the user table, you could either figure out how to rename it, or else change acp/subscriptions.php to add the table name to that field so that it isn't ambiguous.

[greigeh]

It looks to me like a mod has added a column to the user table called 'status', and that broke a query in subscriptions.php. Assuming that you don't want to remove that mod or that column from the user table, you could either figure out how to rename it, or else change acp/subscriptions.php to add the table name to that field so that it isn't ambiguous.

Hey, I renamed the column 'status' and it broke the Subscription Manager all together. I've tried disabling all mods etc but it doesn't seem to resolve anything :(

[Zachery]

Why did you rename a column? Rename it back.

[greigeh]

Why did you rename a column? Rename it back.

I have changed it back to 'status', I genuinely have no idea how to fix this ha but hey i'm trying.

[Lynne]

You renamed the column "status" in the user table, not the subscriptionlog table, right? Try disabling your modifications and then View Users and see if you still get the same database error. There is definitely not supposed to be a user.status field, so something added it and it is what is causing the problem.

[greigeh]

You renamed the column "status" in the user table, not the subscriptionlog table, right? Try disabling your modifications and then View Users and see if you still get the same database error. There is definitely not supposed to be a user.status field, so something added it and it is what is causing the problem.

How would I go about this first part? I've tried disabling everything and that seems to have failed so i'm a tiny bit baffled...

[ozzy47]

Ok lets do this, disable plugins via the includes/config.php file.

To do that open your includes/config.php file and below <?php add the following.

define('DISABLE_HOOKS', true);So it looks like this:
<?php
define('DISABLE_HOOKS', true);
/*================================================= =====================*\
|| ################################################## ################## ||
|| # vBulletin 3.8.x Use a editor like notepad++ (http://notepad-plus-plus.org/download/v6.6.8.html) to edit any files, don't use Notepad or Wordpad.

If that fixes the issue enable plugins again by adding // before the line you added, then navigate to ACP --> Plugins & Products --> Manage Products and disable one mod at a time until you find the one causing the issue.

Once you find the culprit, post in that mods thread what the issue is, and maybe someone in that thread can help. :)

[greigeh]

Ok lets do this, disable plugins via the includes/config.php file.

To do that open your includes/config.php file and below <?php add the following.

define('DISABLE_HOOKS', true);So it looks like this:
<?php
define('DISABLE_HOOKS', true);
/*================================================= =====================*\
|| ################################################## ################## ||
|| # vBulletin 3.8.x Use a editor like notepad++ (http://notepad-plus-plus.org/download/v6.6.8.html) to edit any files, don't use Notepad or Wordpad.

If that fixes the issue enable plugins again by adding // before the line you added, then navigate to ACP --> Plugins & Products --> Manage Products and disable one mod at a time until you find the one causing the issue.

Once you find the culprit, post in that mods thread what the issue is, and maybe someone in that thread can help. :)

I done the whole 'define('DISABLE_HOOKS', true); ' but even disabling all mods/scripts/whatever seem to not cure the problem. :down:

[kh99]

I have changed it back to 'status', I genuinely have no idea how to fix this ha but hey i'm trying.

Yeah, sorry I wasn't clear enough. While one option for fixing the issue would be to figure out how to rename the added column so that it doesn't conflict, that would involve understanding why it was added and making corresponding changes to the code that uses it. I probably shouldn't have mentioned it, but instead just told you to figure out which mod it is and either disable it or ask the developer for help.

[ozzy47]

Here is a old thread on vb.com, http://www.vbulletin.com/forum/forum/vbulletin-legacy-versions-products/legacy-vbulletin-versions/vbulletin-3-0-how-do-i-and-troubleshooting-forum/106443-vb3-0-1-sql-errors-clause-is-ambiguous

OP thinks it was from a old mod, that did not remove the table when the mod was deleted.

[kh99]

I done the whole 'define('DISABLE_HOOKS', true); ' but even disabling all mods/scripts/whatever seem to not cure the problem. :down:

OK, then the extra column is probably not being used. What you could try, since you know how to rename table columns, is to go to the *user* table and rename that column. Then if it turns out that something is using it, you'll still have the data. But if nothing else breaks, you can eventually delete that column from the user table, if you want.

Be extremely careful when making database changes. You should back up your database before doing it.

[greigeh]

I have since fixed the problem, thanks a lot for your help!

[ozzy47]

What did you do to fix it incase someone else runs across the same issue?

[Dave]

My guess is that he renamed the status column in the users table.

In case people are unsure what the error means:
It indicates that both tables have a column called status but the WHERE statement doesn't specify which table to use for the status column.

[Lynne]

And for future reference in case someone else stumbled upon this thread, how did you fix it?

[greigeh]

Fixed the error by renaming 'status' from the user column as advised, the error seemed to go away entirely. Old script must have added it and not removed it upon un-install making the database confused at the two 'status' rows or whatever

thanks for your help!