7

So I have a query that, on my production installation requires quotations around "id" in the 'where' method, otherwise returns no results. On my local installation, the exact opposite is happening. If I'm using quotations, it's not functioning.

Production

'invoices' => Auth::user()->invoices->where('paid', "0")

Localhost

'invoices' => Auth::user()->invoices->where('paid', 0)

It's really strange, and I'd really like to not have go in and change this whenever I deploy from github. Is this a common issue? I can't seem to find anything about it.

I'll list some Laravel query logs:

Local install with quotations around the int (not working)

Array ( 
    [0] => Array ( 
        [query] => select * from `users` where `users`.`id` = ? limit 1 
        [bindings] => Array ( 
            [0] => 1 
        ) 
        [time] => 10.49 
    )
    [1] => Array ( 
        [query] => select * from `invoices` where `invoices`.`user_id` = ? and `invoices`.`user_id` is not null 
        [bindings] => Array ( 
            [0] => 1 
        ) 
        [time] => 1.39 
    ) 
)

Local install without quotations around the int (working)

Array (     
    [0] => Array ( 
        [query] => select * from `users` where `users`.`id` = ? limit 1 
        [bindings] => Array ( 
            [0] => 1 
        ) 
        [time] => 0.84 
    ) 
    [1] => Array ( 
        [query] => select * from `invoices` where `invoices`.`user_id` = ? and `invoices`.`user_id` is not null 
        [bindings] => Array ( 
            [0] => 1 
        ) 
        [time] => 1.15 
    ) 
)

Production installation without quotations around int (not working)

Array ( 
    [0] => Array ( 
        [query] => select * from `users` where `users`.`id` = ? limit 1
        [bindings] => Array ( 
            [0] => 1 
        ) 
        [time] => 2.3 
    ) 
    [1] => Array ( 
        [query] => select * from `invoices` where `invoices`.`user_id` = ? and `invoices`.`user_id` is not null 
        [bindings] => Array ( 
            [0] => 1 
        ) 
        [time] => 0.67 
    ) 
)

Production installation with quotations around int (working)

Array ( 
    [0] => Array ( 
        [query] => select * from `users` where `users`.`id` = ? limit 1 
        [bindings] => Array ( 
            [0] => 1 
        ) 
        [time] => 0.88 
    ) 
    [1] => Array ( 
        [query] => select * from `invoices` where `invoices`.`user_id` = ? and `invoices`.`user_id` is not null 
        [bindings] => Array ( 
            [0] => 1 
        ) 
        [time] => 0.81 
    ) 
)

Solution:

It turns out I didn't have mysqlnd as the active driver on my server. Activating it solved the issue.

Shane Lessard
  • 655
  • 1
  • 8
  • 18
  • This is probably a bug. With that said, are you perhaps using PDO emulated prepares on one but not the other? – Andrea Dec 12 '15 at 02:21
  • Naw, it's literally the exact same code base on both. – Shane Lessard Dec 12 '15 at 02:22
  • Is the column `paid` a *boolean* or an *int* in the database? Do both environments use the same mysql driver? Are the environments (server/php/mysql/apache) different in anyway? – Jeemusu Dec 12 '15 at 03:32
  • Also is it the same exact database version on both sides. – Doon Dec 12 '15 at 03:33
  • paid is an int column, but will only be 0 or 1 so boolean would work just as well. And yes, it's the same database version on both sides. Same migrations, same version of mysql. – Shane Lessard Dec 12 '15 at 03:35
  • Using a boolean for the column may be a way to avoid the issue. Check for true or false instead. `->where('paid', '=', true)` – Jeemusu Dec 12 '15 at 03:42
  • Jeemusu, that worked 100%. And although it solved my problem, I'm dying to find out what the cause was. – Shane Lessard Dec 12 '15 at 03:44
  • Yeah, hopefully someone will come up with an explanation. Have you tried logging the queries being made on both local and production and comparing them? Should point to whats happening with the where clause, which could help identify the issue. – Jeemusu Dec 12 '15 at 03:45
  • Strangely the querylog is an empty array when the query isn't functioning on either environment. But when I have them both working (quotes for production, no quotes for local) I'm getting the exact same output. – Shane Lessard Dec 12 '15 at 03:49
  • Is that the mysql query log? Try logging the queries via Laravel, that should show us what eloquents final output is. http://stackoverflow.com/questions/19131731/laravel-4-logging-sql-queries/19132111#19132111 – Jeemusu Dec 12 '15 at 03:53
  • @Jeemusu Ok I've added some query logs from laravel ```id = 0``` with and without quotations on both live and local. Strangely using booleans only worked locally, not in production for some reason. So this is very much still an issue for me. – Shane Lessard Dec 13 '15 at 03:06
  • MySQL ? You can try using the mysqlnd php driver (native driver) and see if it helps. – lagbox Dec 13 '15 at 07:20
  • That might just do the trick, because it looks like it's enabled on my local install and not on my server. – Shane Lessard Dec 13 '15 at 13:56
  • Thats most likely the issue, which is why I asked you if your environments use the same mysql driver :P – Jeemusu Dec 14 '15 at 03:09
  • I was sure that they did, until I actually tested. I'm not sure when or how my server stopped using mysqlnd. – Shane Lessard Dec 14 '15 at 20:51
  • Instead of editing the question to include an answer, write up an answer with a bit more detail and then mark it accepted. You can answer your own question, and this will prevent the question from showing up as unanswered. – miken32 Dec 16 '15 at 17:46

1 Answers1

0

It turns out I didn't have mysqlnd as the active driver on my server. Activating it solved the issue. I did so with the following commands:

yum remove php-mysql

yum install php-mysqlnd

php -m | grep mysqlnd

Shane Lessard
  • 655
  • 1
  • 8
  • 18