• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
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
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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.