Unknown column 'STRICT_ALL_TABLES,' setting up CodeIgniter DB connection

user_2843552 0
Viewed 1896

Setting up a new CodeIgniter 3 project I'm stucked with a DB access issue. When visiting my home page I get:

'Unknown column 'STRICT_ALL_TABLES,' in 'field list'

...which is fired when Codeigniter initializes the database. After mysqli/mysqli_driver.php call parent::__construct()

After some code and google resarch is not clear to me why is this happening. Any hints? Suggestions?

Some details

Using same database connection I successfully use from others PHP setups in the same dev computer. (with older codeigniter versions)

I use CodeIgniter 3.1.10, installed along with ci3-fire-starter

My DB config:

$active_group = 'default';
$query_builder = TRUE;

$db['default'] = array(
    'dsn'          => '',
    'hostname'     => '127.0.0.1',
    'username'     => 'my_username', 
    'password'     => 'my_password',
    'database'     => 'my_db',
    'dbdriver'     => 'mysqli',
    'dbprefix'     => '',
    'pconnect'     => FALSE,
    'db_debug'     => TRUE,
    'cache_on'     => FALSE,
    'cachedir'     => '',
    'char_set'     => 'utf8',
    'dbcollat'     => 'utf8_general_ci',
    'swap_pre'     => '',
    'encrypt'      => FALSE,
    'compress'     => FALSE,
    'stricton'     => FALSE, // Notice I have stricton set to FALSE!
    'failover'     => array(),
    'save_queries' => TRUE
);

And finally the full error obtained:

Severity: Warning

Message: mysqli::real_connect(): (42S22/1054): Unknown column 'STRICT_ALL_TABLES,' in 'field list'

Filename: mysqli/mysqli_driver.php

Line Number: 203

Backtrace:

File: C:\my\path\application\core\MY_Controller.php
Line: 26
Function: __construct

File: C:\my\path\application\core\Public_Controller.php
Line: 13
Function: __construct

File: C:\my\path\application\controllers\Welcome.php
Line: 10
Function: __construct

File: C:\my\path\index.php
Line: 325
Function: require_once
A Database Error Occurred

Unable to connect to your database server using the provided settings.

Filename: C:/my/path/system/database/DB_driver.php

Line Number: 436
codeigniter-3 mysqli mysql php
2 Answers

Self-answered question, just in case it can be useful to anyone:

It's a pretty weird situation related to codeigniter mysqli driver class CI_DB_mysqli_driver at system\database\drivers\mysqli\mysqli_driver.php. For some reason code under:

if (isset($this->stricton))         { /* ... */ }

...is setting some mysqli MYSQLI_INIT_COMMAND options which are causing the error.

It's easy to by-pass the error not setting 'stricton' flag in you db settings. Just comment it out: 

$db['default'] = array(
    'dsn'          => '',
    'hostname'     => '127.0.0.1',
    'username'     => 'my_username', 
    'password'     => 'my_password',
    'database'     => 'my_db',
    'dbdriver'     => 'mysqli',
    'dbprefix'     => '',
    'pconnect'     => FALSE,
    'db_debug'     => TRUE,
    'cache_on'     => FALSE,
    'cachedir'     => '',
    'char_set'     => 'utf8',
    'dbcollat'     => 'utf8_general_ci',
    'swap_pre'     => '',
    'encrypt'      => FALSE,
    'compress'     => FALSE,
    // 'stricton'     => FALSE,
    'failover'     => array(),
    'save_queries' => TRUE
);
user_28435520

It has been a while since this questions was asked, but I just ran into this issue myself trying to migrate from MySQL 5.2 to 8, so I thought I'd answer it properly for anyone else that may trip over it.

Message: mysqli::real_connect(): (42S22/1054): Unknown column 'STRICT_ALL_TABLES,' in 'field list'

The underlying issue here is the sql_mode used. There's a setting ANSI_QUOTES that will make the " an identifier character. Because of that setting, the following code in mysqli_driver.php in CodeIgniter fails:

if (isset($this->stricton))
        {
            if ($this->stricton)
            {
                $this->_mysqli->options(MYSQLI_INIT_COMMAND, 'SET SESSION sql_mode = CONCAT(@@sql_mode, ",", "STRICT_ALL_TABLES")');
            }
            else
            {
                $this->_mysqli->options(MYSQLI_INIT_COMMAND,
                    '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", "")'
                );
            }
        }

So, two possible solutions:

  1. Remove ANSI_QUOTES either at session or global level, or
  2. Rework the query by "swapping" single and double quotes:
if (isset($this->stricton)) {
            if ($this->stricton) {
                $this->_mysqli->options(MYSQLI_INIT_COMMAND, "SET SESSION sql_mode = CONCAT(@@sql_mode, ',', 'STRICT_ALL_TABLES')");
            } else {
                $this->_mysqli->options(MYSQLI_INIT_COMMAND, "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', '')"
                );
            }
        }
user_35888190
Related