Disabling MySQL Strict Mode only for one query Laravel 5.6

Question

I want to disable MySQL strict mode only for one query inside a controller not for whole laravel application,

I know the risks of disabling MySQL strict mode so I don't want to disable it for my whole application from config/database.php,

I am facing problem with only one query in my whole application, so I want to disable it inside my controller before running that query only for one time!

Please help me is there any way for given situation.

it is given error in a group by query, i m using laravel eloquent, App\DefaultMilestone::where('d_dept', $user_dept)->select('d_nature')->groupby('d_nature')->get(); when i remove the select, then it will give error — Mȍhǟmmǟd Șamȋm, Jul 26 '18 at 08:41
https://stackoverflow.com/questions/40881773/how-to-turn-on-off-mysql-strict-mode-in-localhost-xampp — Parth Shah, Jul 26 '18 at 08:43
i need to select * or select id of the row at least with group by. the table already contain data and i can't change the structure of table, when i disable the strict mode from config/database it will not give any error — Mȍhǟmmǟd Șamȋm, Jul 26 '18 at 08:44

Max Gaurav · Answer 1 · 2019-03-08T11:39:42.657

17

Changing the strict mode in runtime

config()->set('database.connections.mysql.strict', false);
\DB::reconnect(); //important as the existing connection if any would be in strict mode
Model::select()->get(); // your query called in non strict mode

//now changing back the strict ON
config()->set('database.connections.mysql.strict', true);
\DB::reconnect();

edited Mar 08 '19 at 11:39

answered Nov 15 '18 at 10:16

Max Gaurav

1,835
2
13
26

3

Although it works, disconnecting will break any transaction involved. Works only if your query is not part of a transaction. USE WITH CAUTION. – John Smith Apr 17 '22 at 00:18
Excellent, save my code after 5hours find the solution. Thanks. – Sylar Oct 12 '22 at 13:18

Jonas Staudenmeir · Answer 2 · 2018-07-31T11:08:18.030

5

Use this for MySQL ≤5.7 (taken from here):

DB::statement("set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'");
// your query
DB::statement("set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'");

edited Jul 31 '18 at 11:08

answered Jul 26 '18 at 15:14

Jonas Staudenmeir

24,815
6
63
109

It works for me. What query are you executing? Why does it conflict with the strict mode? – Jonas Staudenmeir Jul 28 '18 at 12:09
App\DefaultMilestone::where('d_dept', $user_dept)->select('d_nature')->groupby('d_nature')->get(); – Mȍhǟmmǟd Șamȋm Jul 30 '18 at 05:02
Why doesn't this query work in strict mode? What's the error? – Jonas Staudenmeir Jul 30 '18 at 09:56
This is working, when i want to select * and group by b_nature then it will give error in strict mode! – Mȍhǟmmǟd Șamȋm Jul 31 '18 at 10:40
You have to disable `ONLY_FULL_GROUP_BY`, I updated my answer. – Jonas Staudenmeir Jul 31 '18 at 11:09
maybe it works for you but not working for me, it doesn't mean that your answer is wrong, thanks for help dear! – Mȍhǟmmǟd Șamȋm Aug 11 '18 at 03:47

score 3 · Answer 3 · answered Jul 26 '18 at 15:37

3

One thing tha you can do is to change this value at run time

public function doQueryWithoutStrictMode{
    config('database.connections.mysql.strict', false);
    DB::select('The query you want to make, and that should work!');

    And then, if you later want to do another query in strict mode withing the same 
    method you can enable it like this.

    config('database.connections.mysql.strict', true);

    DB::select('your other query with strict mode');

}

answered Jul 26 '18 at 15:37

jarguez01

194
1
7

This seems to work, but i have tried and it is not working, have you tried it? – Mȍhǟmmǟd Șamȋm Jul 28 '18 at 07:04
1

i have tried this too config(['database.connections.mysql.strict' => false]); – Mȍhǟmmǟd Șamȋm Jul 28 '18 at 07:18

score 2 · Answer 4 · answered Jul 26 '18 at 09:31

2

In config/database.php, do as like:

'connections' => [

    'mysql' => [

        // Behave like MySQL 5.6

        'strict' => false,

        // Behave like MySQL 5.7
        'strict' => true,
    ]

]

answered Jul 26 '18 at 09:31

Rishabh Pandey

62
3

it will change the strict mode for whole application, and it will be a risk – Mȍhǟmmǟd Șamȋm Jul 26 '18 at 09:43

Disabling MySQL Strict Mode only for one query Laravel 5.6

4 Answers4

Changing the strict mode in runtime