CODEWITHSUNDEEP
phpmysqlcodeigniter
Shahneel Ahmed
Shahneel Ahmed

Reputation: 173

Codigniter backup library creating wrong insert query

i am creating backup using codigniter library "dbutil" and i have fields in my mysql database which having datatype bit when using below given code 

 public function run_backup()
    {
        $filename="MembersPro_database_$this->curr_date.sql";
        $filepath="application/upload/DatabaseBackup/$filename";
        $dbfilepath="MembersPro/application/upload/DatabaseBackup/$filename";

        $prefs = array(
            'ignore'        => array(),                     // List of tables to omit from the backup
            'format'        => 'sql',                       // gzip, zip, txt
            'filename'      =>$filepath,                   // File name - NEEDED ONLY WITH ZIP FILES
            'add_drop'      => TRUE,                        // Whether to add DROP TABLE statements to backup file
            'add_insert'          => TRUE,
            "foreign_key_checks"  =>FALSE
           /* 'newline'       => "\n",*/
            // Newline character used in backup file
        );
        $backup="CREATE DATABASE IF NOT EXISTS `MembersManagmentSystem`; USE `MembersManagmentSystem` ";
        $backup .= $this->dbutil->backup($prefs);
        if(!write_file($filepath, $backup))
        {
            echo "Error";die;
        }
        else
        {
            $_SESSION['sucessmsgbackup']="true";
        }
        $this->insert_into_datbase($filename,$dbfilepath);
        redirect("ViewDatabaseBackup");
    }

getting wrong output insert query 

INSERT INTO `User` (`userId`, `userRoleId`,`userActive`, `userIsDelete`) VALUES ('20000046', '20001','1', '0');

in above given query "userActive" and "userIsDelete" fields having datatype "bit" and the query create by DbUtil library is treating it as string so i am getting warning error in mysql

"out of range column value"

Upvotes: 1

Views: 189

Answers (1)

Akshay Hegde
Akshay Hegde

Reputation: 16997

Read Here Why you should not use BIT columns in MySQL

Issue reported Here in Github

Look into folder and modify core classes so that it will not escape, for example for mysqli driver

system/database/drivers/mysqli

https://github.com/bcit-ci/CodeIgniter/blob/develop/system/database/drivers/mysqli/mysqli_utility.php#L159

and find file mysqli_utility.php

locate line

$is_int[$i] = in_array(strtolower($field->type),
                            array('tinyint', 'smallint', 'mediumint', 'int', 'bigint'), //, 'timestamp'),
                            TRUE);

Type numbers

numerics
-------------
BIT: 16
TINYINT: 1
BOOL: 1
SMALLINT: 2
MEDIUMINT: 9
INTEGER: 3
BIGINT: 8
SERIAL: 8
FLOAT: 4
DOUBLE: 5
DECIMAL: 246
NUMERIC: 246
FIXED: 246

and add your datatype to above array like below

$is_int[$i] = in_array($field->type,
                            array(16, 1, 2, 9, 3, 8),
                            TRUE);

Upvotes: 1

Related Questions