Plugin breaks my forum - Column 'InsertUserID' in where clause is ambiguous

unixhero

Great plugin!
My forum breaks when I enable this plugin. That is, the plugin works as intended and it all seems fine.
Except for users who has not logged into the forum.
Versions: Vanillaforums 2.1.10 Post Scheduler 13.04.15b
On the website root when a user has not logged in, this message comes:

PDO Statement failed to prepare
Column 'InsertUserID' in where clause is ambiguous

---

The error occurred on or near: /home/theuserfromanotheruserhimom/public_html/*****.com/library/database/class.database.php

289: if (!is_null($InputParameters) && count($InputParameters) > 0) {
290: $PDOStatement = $this->Connection()->prepare($Sql);
291:
292: if (!is_object($PDOStatement)) {
293: trigger_error(ErrorMessage('PDO Statement failed to prepare', $this->ClassName, 'Query', $this->GetPDOErrorMessage($this->Connection()->errorInfo())), E_USER_ERROR);
294: } else if ($PDOStatement->execute($InputParameters) === FALSE) {
295: trigger_error(ErrorMessage($this->GetPDOErrorMessage($PDOStatement->errorInfo()), $this->ClassName, 'Query', $Sql), E_USER_ERROR);
296: }

297: } else {

---

Backtrace:
/home/theuserfromanotheruserhimom/public_html/*****.com/library/database/class.database.phpPHP::Gdn_ErrorHandler();
[/home/theuserfromanotheruserhimom/public_html/*****.com/library/database/class.database.php:293] PHP::trigger_error();
[/home/theuserfromanotheruserhimom/public_html/*****.com/library/database/class.sqldriver.php:1630] Gdn_Database->Query();
[/home/theuserfromanotheruserhimom/public_html/*****.com/library/database/class.sqldriver.php:654] Gdn_SQLDriver->Query();
[/home/theuserfromanotheruserhimom/public_html/*****.com/applications/vanilla/models/class.discussionmodel.php:348] Gdn_SQLDriver->Get();
[/home/theuserfromanotheruserhimom/public_html/*****.com/applications/vanilla/controllers/class.discussionscontroller.php:136] DiscussionModel->GetWhere();
[/home/theuserfromanotheruserhimom/public_html/*****.com/applications/vanilla/controllers/class.discussionscontroller.php:136] DiscussionsController->Index();
[/home/theuserfromanotheruserhimom/public_html/*****.com/library/core/class.dispatcher.php:350] PHP::call_user_func_array();
[/home/theuserfromanotheruserhimom/public_html/*****.com/index.php:46] Gdn_Dispatcher->Dispatch();

---

---

Variables in local scope:

[Sql] 'select d2.*
from GDN_Discussion d
join GDN_Discussion d2 on d.DiscussionID = d2.DiscussionID
where (InsertUserID = :InsertUserID
or Scheduled is null
or ScheduleTime <= :ScheduleTime)
and d.CategoryID in (null)
order by d.DateLastComment desc
limit 50'
[InputParameters] array (
':InsertUserID' => 0,
':ScheduleTime' => '2015-05-27 12:05:15',
)
[Options] array (
'ReturnType' => 'DataSet',
)
[ReturnType] 'DataSet'

[PDOStatement] false

---

Additional information for support personnel:
Application: Vanilla
Application Version: 2.1.10
PHP Version: 5.4.38
Operating System: Linux
Server Software: Apache
User Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/42.0.2311.152 Safari/537.36
Request Uri: /discussions
Controller: Gdn_Database

Method: Query

unixfox

Same error

hgtonight

I can see where the generated query is failing, but not where it is being referenced from this plugin.

What other plugins do you have enabled?

businessdad

@hgtonight you don't see the issue because there seems to be a problem with the repository. The "main" plugin page gives you the wrong plugin version. That is, this page gives you this http://prntscr.com/7b3bsv, while the correct version is this one: http://vanillaforums.org/addon/postscheduler-plugin-13.04.15b.

@unixfox There is a good chance that version 13.04.15b will also fix the issue you reported.

businessdad

In short, @hgtonight, what you see in the master branch of the Git repository is not the same you get if you download the "main" plugin (which is the wrong one).

hgtonight

@businessdad said:
In short, hgtonight, what you see in the master branch of the Git repository is not the same you get if you download the "main" plugin (which is the wrong one).

Interesting. I was searching the git repository which would be the latest version.

Whoops.

businessdad

As for the reason of the issue, in Vanilla 2.1 they introduced "self joins", for which I found no valid reason (an old thread indicates that "it forces index scan", but I still think that's the wrong way of doing it). Joining a table with itself, if anything, should decrease performance.

Therefore, while in 2.0 we had:

SELECT
  X, Y, Z
FROM
  Gdn_Discussion d
  JOIN
  Gdn_SomeOtherTable
WHERE
  InsertUserID = 123

In 2.1 we have

SELECT
  X, Y, Z
FROM
  Gdn_Discussion d
  JOIN
  Gdn_Discussion d2 ON (d2.DiscussionID = d.DiscussionID)
WHERE
  d2.InsertUserID = 123

That is, any field that appears in Gdn_Discussion has to be qualified with a table prefix, to resolve ambiguity. This was fixed in Post Scheduler 13.02.26, i.e. over two years ago.

More in-depth history of the plugin, a.k.a "why the fix was not made available earlier"
The plugin available in the repository until recently was kind of a demo. The Post Scheduler was a paid project, and I had agreed with the person who commissioned it that I would release it to the community when ready. However, when the plugin was ready, the requester told me that he was not going to pay anything for it, and just wait until it would become public. Due to that, I made the Post Scheduler a premium plugin to recoup the costs of development, but it didn't work either, so I decided that making it public would be better than abandoning it, and it would give others the opportunity to build upon it.

hgtonight

@businessdad said:
However, when the plugin was ready, the requester told me that he was not going to pay anything for it, and just wait until it would become public.

Wow. Just wow. Complete dick move.

Due to that, I made the Post Scheduler a premium plugin to recoup the costs of development, but it didn't work either...

so I decided that making it public would be better than abandoning it, and it would give others the opportunity to build upon it.

My take away from this is that @businessdad is awesome.

unixhero

That's unheard of. Well I guess in any business one has to decide if to trust the client and make it cash on delivery or to make a coding job upfront cash payment. @businessdad Well thanks a lot for publishing it anyways.

x00

@hgtonight said:
Wow. Just wow. Complete dick move.

Someone did that to me with skrill payments on market place. They didn't say they would wait for it to be released, I'm guessing they were hoping I would release it with updates. I just removed the code. I won't release it until someone pays for it.

businessdad

@unixhero said:
That's unheard of. Well I guess in any business one has to decide if to trust the client and make it cash on delivery or to make a coding job upfront cash payment.

Not to mention that some clients go to the extent of filing a fraud chargeback, after they pay with a credit card. In the last two years, it happened seven times. The process is "get the product, finish the testing, get bugs fixed, pay, then file a chargeback" (they have up to two years to file one). The result is that the money is immediately refunded to the buyer, the seller is charged an extra fee (about $25) and has to provide a ton of documentation to prove that he was commissioned some work and that he delivered it. Even after doing that, the chargeback is lost 99% of the time. In the rare case the seller wins, the buyer can request a second chargeback, and he's practically sure that he will win it.

A freelancer I know got scammed over 10 thousand dollars with this "trick".

In case anyone wonders what happens if a buyer is discovered filing fraudulent chargebacks, the answer is nothing. Banks don't lose a penny, as all charges are passed to the merchant, and they are more than happy to keep a customer who keeps using their card. Merchants may try to sue them, but it's often not worth it:

• When a buyer says that he did not authorise the seller to get the money, the burden of proof is on the seller.
• When a seller says that the chargeback is fraudulent, the burden of proof is on the seller.

x00

Fortunately that hasn't happened to me. Generally I insist on sending out the invoice, and the pay that directly. They are always initiating payment.

It doesn't prevent charge backs, but it provides a contract with detail in it.

businessdad

@x00 said:
Fortunately that hasn't happened to me. Generally I insist on sending out the invoice, and the pay that directly. They are always initiating payment.

It doesn't prevent charge backs, but it provides a contract with detail in it.

Trust me, even with contract details the chargeback is successful. Been there, done that.

x00

@businessdad said:
Trust me, even with contract details the chargeback is successful. Been there, done that.

Sure, I guess I have been lucky. I had one guy opened a dispute, after five minutes of realising something was not working, which I think was pretty unreasonable, given he didn't contact me first.

I'm a trade liberal, but unfortunately I won't deal in certain countries, simply becuase they don't have the infrastructure to fight fraud, and they have high corruption, at government level (which creates a lack of incentive). I have made exceptions, but I try to do background checks.

Some merchant system have better procedures. Paypal is particularly bad (in more ways than one). Stripe is a bit better

https://stripe.com/help/disputes
https://support.stripe.com/questions/avoiding-fraud-and-disputes

businessdad

For the record, five of the fraud chargebacks I received (and lost) were with Stripe. The other two were with PayPal. Bottom line: customer can file chargebacks as they like, there's no chance they could lose anything.

agcyphers

@businessdad, if you'll PM me your PayPal address, I'd be happy to contribute monetarily for this plugin!

businessdad

@agcyphers Thanks for your support. I will send you a PM, but I have to point out that I won't have much time to work on the plugin or to support it. I shifted my focus on a different market and, although I will still participate to this community, it will be something done occasionally.

A contribution for the work done so far will be welcome, I just don't want to create the expectation that it will allow me to get back to work on the plugin on a regular basis (unless it covers the high four-figures costs I sustained, LOL!)

agcyphers

No expectations at all. I'm also a contract developer, so I know what you're going through and will toss a little money at something I like.

unixhero

@businessdad : Why don't you be a good sport and send me your paypal details, so I can send some support too.

I missed your question here:

@businessdad said:
I can see where the generated query is failing, but not where it is being referenced from this plugin.
What other plugins do you have enabled?

When this error occured I had no idea where it came from initially. When I had disabled everything, the error still remained. I could reproduce this error by enabling the plugin when the forum was "Vanilla"

businessdad

Thanks for the feedback and the donations so far. It's nice to get some appreciation!