Welcome Guest, Not a member yet? Register   Sign In
CI 3 DB LOCKING ISSUE ON SET SESSION sql_mode = REPLACE(REPLACE(REPLACE(REPLACE
#1
Photo 

hello guys, it's been a rough week since suddenly this error appear causing a havoc on my production web, this query ( that obviously wasn't created by me ) seems to locking my enitire DB causing delay to all the process that running on my system

Quote:SET SESSION sql_mode = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( @@sql_mode, "STRICT_ALL_TABLES,", ""), ",STRICT_ALL_TABLES", ""), "STRICT_ALL_TABLES", ""), "STRICT_TRANS_TABLES,", ""), ",STRICT_TRANS_TABLES", ""), "STRICT_TRANS_TABLES", "")



[Image: f94ydDQ.png]

i read many posts on the internet that related to his issue but still can't find a clue to solve this while my clients keep ranting day and night

i found one particular solution that suggest me to change my database striction setting  from FALSE to TRUE to this 


[Image: 09be19ea6077988b8170ce56080322dfa1e8eb22.png]

reference :

https://stackoverflow.com/questions/5749...st-website
https://www.000webhost.com/forum/t/datab...1/173568/8

to confirm that solution i digged deep into ci 3 core function and i found this 

[Image: 9cqb8G1.png]

but when i set that to TRUE some of my site function become unable to run 

so do you guys have any solutuion to tacke this issue ? and can you guys feed me with some information on why even this error appear in the first place ? cause this issue just appear recently after 2 years straight running smoothly ( without this kind of error )

thank you
Reply
#2

If you own your server yourself, just set the same settings in your my.ini file, that way you don't need CodeIgniter to set it for you.
As to why it suddenly hangs... have you updated the server version? If not you really should, not to fix this, but for security.
Reply
#3

(09-20-2020, 10:01 PM)jreklund Wrote: If you own your server yourself, just set the same settings in your my.ini file, that way you don't need CodeIgniter to set it for you.
As to why it suddenly hangs... have you updated the server version? If not you really should, not to fix this, but for security.

thanks for your reply 

for that my.ini you mean mysql config right ?

im using AWS so yes i will check on it later

and may i know what this query actually trying to achieve ? 

Quote:SET SESSION sql_mode = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( @@sql_mode, "STRICT_ALL_TABLES,", ""), ",STRICT_ALL_TABLES", ""), "STRICT_ALL_TABLES", ""), "STRICT_TRANS_TABLES,", ""), ",STRICT_TRANS_TABLES", ""), "STRICT_TRANS_TABLES", "")

and why CI core suddenly executing that query ? 

i didn't even made any change on my script before this but that query suddenly appear on my mysql monitor and make my application become very slow
Reply
#4

(This post was last modified: 09-21-2020, 08:32 AM by jreklund.)

Yes, I mean your mysql configuration. But that are a global setting and will affect all your applications.

stricton | TRUE/FALSE (boolean) - Whether to force "Strict Mode" connections, good for ensuring strict SQL while developing an application.

So it's disabling MySQL Strict in case it's on (for it's own connection). It's built that way so it can remove STRICT_ALL_TABLES and STRICT_TRANS_TABLES in case it's in the beginning, middle or end of the string.

https://dev.mysql.com/doc/refman/5.7/en/...ode-strict

It have always been executing it, but now it hangs for some reason.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB