Set sql_mode “blank” in MySQL 5.7 use only mysqli_query

Question

Relative answer search
I tried all questions & answers related this topic.

I want to update global variable in mysql sql_mode = ''.

After upgrading MySQL from 5.5 to 5.7.15, some our app face to problem and need set sql_mode to blank to solve this issue. I added sql_mode = '' but there was no effect on the mysql setting.

How do I keep the sql_mode blank ?

tired Code

$db = new DB_Sql();
$db->Host  = "172.**.**.**";
$db->Database   = "MyDatabase";
$db->User   = "root";
$db->Password   = "MyPassword";

global $ourFileName,$link;  
         
$SITE_URL="http://".$_SERVER[HTTP_HOST];
$SITE_HTTPS_URL="http://".$_SERVER[HTTP_HOST];

$ourFileName = $_SERVER['DOCUMENT_ROOT'] . "/Log/MaintenaceLogFiles.txt"; 

 $ndb=$mdb=$db; 
 
 $link=mysqli_connect($db->Host, $db->User, $db->Password,$db->Database);

    if(mysqli_connect_errno())
    {
    echo '<p>Error: Unable to connect to MySQL: '.mysqli_connect_error().'</p>';
    }
    
  mysqli_query($link,"set sql_mode = '' ") or die(mysqli_error());

Note: Using only mysqli_query not use .cnf

Your Common Sense · Accepted Answer · 2021-08-24T08:09:12.960

9

Why do you think it doesn't work?

It works for me all right

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db=mysqli_connect($db->Host, $db->User, $db->Password,$db->Database);
$db->set_charset("utf8mb4");

$row = $db->query("SHOW VARIABLES LIKE 'sql_mode'")->fetch_row();
var_dump($row[1]);

$db->query("SET SESSION sql_mode=''");

$row = $db->query("SHOW VARIABLES LIKE 'sql_mode'")->fetch_row();
var_dump($row[1]);

outputs

string(62) "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
string(0) ""

if, for some reason, you want to set it global (while you shouldn't run your queries under root)

$mysqli->query("SET GLOBAL sql_mode='', SESSION sql_mode=''");

edited Aug 24 '21 at 08:09

answered Mar 22 '17 at 11:15

Your Common Sense

156,878
40
214
345

thank for answer . i tried but update a simple variables `sql_mode` not change global variables . i update global variables – Sumit patel Mar 22 '17 at 12:16
i check my update global variable `sql_mode` . `select @@sql_mode;` not be null – Sumit patel Mar 22 '17 at 12:20
to update a global variable you should update a global variable. but you are updating a session variable in your code. just make your mind – Your Common Sense Mar 22 '17 at 12:40
i set user `customuser` in phpmyadmin not (`root`user use ) . so i can't updated global variable so please help – Sumit patel Mar 23 '17 at 10:51
then set it to session – Your Common Sense Mar 23 '17 at 11:19

Set sql_mode “blank” in MySQL 5.7 use only mysqli_query

1 Answers1