CodeIgniter - how to catch DB errors?

Question

Is there a way to make CI throw an exception when it encounters a DB error instead of displaying a message like:

A Database Error Occurred Error Number: 1054 Unknown column 'foo' in 'where clause' SELECT * FROM (FooBar) WHERE foo = '1'

NOTE: I only want this to happen in one controller. In the other controllers, I'm happy for it to display the DB error messages.

score 90 · Accepted Answer · edited Jul 10 '22 at 14:48

90

Use error() method:

$this->db->error();

For CodeIgniter 2, you can use the following functions which are now deprecated:

$this->db->_error_message(); (mysql_error equivalent)
$this->db->_error_number(); (mysql_errno equivalent)

edited Jul 10 '22 at 14:48

Dharman

30,962
25
85
135

answered Oct 21 '11 at 04:17

Oskenso Kashi

1,093
8
10

4

And when the queries are dinamically created, `$this->db->last_query()` useful too. – uzsolt Oct 21 '11 at 15:17
9

How do these prevent the messages from displaying? – StackOverflowNewbie Oct 21 '11 at 21:36
8

You must turn debug off for database in config/database.php -> $db['default']['db_debug'] = FALSE; – Decebal Dec 12 '12 at 12:47
13

Why is not possible to use Exception instead :-( ? – Thomas Decaux Mar 08 '13 at 11:39
1

I'm with Thomas, I really there was a way to get these to throw exceptions by default. – TJ L May 08 '13 at 14:34
43

These methods have been removed in CodeIgniter version 3. Use `$this->db->error()` instead. (see http://www.codeigniter.com/user_guide/database/queries.html#handling-errors) – mxgr Jul 26 '15 at 11:17
3

IMHO This does not answer the question. the `display_error` function ends with `exit(8)` therefore, you can't catch the exception. The only way is to disable db_debug as other answers told. – Nicolas Thery Sep 13 '16 at 09:52

score 44 · Answer 2 · answered Jun 18 '13 at 06:15

44

Maybe this:

$db_debug = $this->db->db_debug; //save setting

$this->db->db_debug = FALSE; //disable debugging for queries

$result = $this->db->query($sql); //run query

//check for errors, etc

$this->db->db_debug = $db_debug; //restore setting

answered Jun 18 '13 at 06:15

RayJ

734
11
12

1

@RayJ Any idea why db_debug 'production' ENVIRONMENT default value (`'db_debug' => (ENVIRONMENT !== 'production'`) is set to FALSE in fresh downloaded CI v3? I see that setting it to false will show db error more details, which is not expected in production, right? I'm confused. – Jeaf Gilbert Aug 24 '19 at 18:07
First we need to step back to the main index.php file and see how ENVIRONMENT is set and why. https://stackoverflow.com/questions/36051146/codeigniter3-why-would-serverci-env-ever-be-set-in-the-first-place As we all know, this can set that to whatever: define('ENVIRONMENT', 'development') : to show errors while developing. define('ENVIRONMENT', 'production') : when site is live. So the line (in the database.php config) says: 'db_debug' => (ENVIRONMENT !== 'production'), Or Set database debug mode to TRUE if "ENVIRONMENT" is not set to "production". – RayJ Aug 25 '19 at 22:44

score 33 · Answer 3 · edited Jun 20 '20 at 09:12

33

In Codeigniter 3.0 (CI3), all you have to do is $this->db->error()

If you need to get the last error that has occured, the error() method will return an array containing its code and message

http://www.codeigniter.com/user_guide/database/queries.html#handling-errors

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 12 '15 at 18:38

CodeGodie

12,116
6
37
66

1

Codeigniter halts code execution and outputs an error message if you have an error, so you will have no opportunity to check $this->db->error. This answer is incorrect. – S. Imp Apr 10 '18 at 21:21
@S.Imp - CodeIgniter only halts execution if config/database.php's `$db['default']['db_debug']` is true. See https://stackoverflow.com/a/21073711/25507. – Josh Kelley Jan 15 '19 at 15:23

score 16 · Answer 4 · edited Jan 12 '14 at 10:48

16

You must turn debug off for database in config/database.php ->

$db['default']['db_debug'] = FALSE;

It is better for your website security.

edited Jan 12 '14 at 10:48

kleopatra

51,061
28
99
211

answered Jan 12 '14 at 10:43

Kabir Hossain

2,865
1
28
34

score 12 · Answer 5 · answered Dec 19 '14 at 12:47

I know this thread is old, but just in case there's someone else having this issue. This is a trick I used without touching the CI db classes. Leave your debug on and in your error view file, throw an exception.

So in you db config, you have :

$db['default']['db_debug'] = true;

Then in your db error view file, mine is in application/errors/error_db.php replace all content with the following:

<?php
$message = preg_replace('/(<\/?p>)+/', ' ', $message);
throw new Exception("Database error occured with message : {$message}");

?>

Since the view file will be called, the error will always get thrown as an exception, you may later add different views for different environment.

score 12 · Answer 6 · edited Jul 10 '22 at 14:49

12

An example that worked for me:

$query = "some buggy sql statement";

$this->db->db_debug = false;

if(!@$this->db->query($query))
{
    $error = $this->db->error();
    // do something in error case
}else{
    // do something in success case
}
...

edited Jul 10 '22 at 14:49

Dharman

30,962
25
85
135

answered Feb 04 '19 at 15:40

dlg_

307
4
11

score 6 · Answer 7 · answered Dec 27 '16 at 18:23

I have created an simple library for that:

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class exceptions {

    public function checkForError() {
        get_instance()->load->database();
        $error = get_instance()->db->error();
        if ($error['code'])
            throw new MySQLException($error);
    }
}

abstract class UserException extends Exception {
    public abstract function getUserMessage();
}

class MySQLException extends UserException {
    private $errorNumber;
    private $errorMessage;

    public function __construct(array $error) {
        $this->errorNumber = "Error Code(" . $error['code'] . ")";
        $this->errorMessage = $error['message'];
    }

    public function getUserMessage() {
        return array(
            "error" => array (
                "code" => $this->errorNumber,
                "message" => $this->errorMessage
            )
        );
    }

}

The example query:

function insertId($id){
    $data = array(
        'id' => $id,
    );

    $this->db->insert('test', $data);
    $this->exceptions->checkForError();
    return $this->db->insert_id();
}

And I can catch it this way in my controller:

 try {
     $this->insertThings->insertId("1");
 } catch (UserException $error){
     //do whatever you want when there is an mysql error

 }

Adriano Gonçalves · Answer 8 · 2016-01-29T14:19:23.283

Put this code in a file called MY_Exceptions.php in application/core folder:

<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

/**
 * Class dealing with errors as exceptions
 */
class MY_Exceptions extends CI_Exceptions
{

    /**
     * Force exception throwing on erros
     */
    public function show_error($heading, $message, $template = 'error_general', $status_code = 500)
    {
        set_status_header($status_code);

        $message = implode(" / ", (!is_array($message)) ? array($message) : $message);

        throw new CiError($message);
    }

}

/**
 * Captured error from Code Igniter
 */
class CiError extends Exception
{

}

It will make all the Code Igniter errors to be treated as Exception (CiError). Then, turn all your database debug on:

$db['default']['db_debug'] = true;

score 2 · Answer 9 · answered Jan 26 '14 at 11:24

2

Use it

    $this->db->_error_message();

It is better for finding error.After completing your site. Close the error messages using it

    $db['default']['db_debug'] = FALSE;

You will change it in your config folder's database.php

answered Jan 26 '14 at 11:24

Kabir Hossain

2,865
1
28
34

score 2 · Answer 10 · answered Sep 25 '18 at 11:26

Disable debugging of errors.

    $data_user = $this->getDataUser();
    $id_user   = $this->getId_user();

    $this->db->db_debug = false;
    $this->db->where(['id' => $id_user]);
    $res = $this->db->update(self::$table, $data_user['user']);

    if(!$res)
    {
        $error = $this->db->error();
        return $error;
        //return array $error['code'] & $error['message']
    }
    else
    {
        return 1;
    }

score -1 · Answer 11 · answered Mar 25 '18 at 01:56

If one uses PDO, additional to all the answers above.

I log my errors silently as below

        $q = $this->db->conn_id->prepare($query);

        if($q instanceof PDOStatement) {
           // go on with bind values and execute

        } else {

          $dbError = $this->db->error();
          $this->Logger_model->logError('Db Error', date('Y-m-d H:i:s'), __METHOD__.' Line '.__LINE__, 'Code: '.$dbError['code'].' -  '.'Message: '.$dbError['message']);

        }

score -1 · Answer 12 · edited Nov 07 '19 at 01:10

In sybase_driver.php

/**
* Manejador de Mensajes de Error Sybase
* Autor: Isaí Moreno
* Fecha: 06/Nov/2019
*/

static  $CODE_ERROR_SYBASE;

public static function SetCodeErrorSybase($Code) {
    if ($Code != 3621) {  /*No se toma en cuenta el código de command aborted*/
        CI_DB_sybase_driver::$CODE_ERROR_SYBASE = trim(CI_DB_sybase_driver::$CODE_ERROR_SYBASE.' '.$Code);       
    }
}

public static function GetCodeErrorSybase() {               
    return CI_DB_sybase_driver::$CODE_ERROR_SYBASE;
}

public static function msg_handler($msgnumber, $severity, $state, $line, $text)
{       
    log_message('info', 'CI_DB_sybase_driver - CODE ERROR ['.$msgnumber.'] Mensaje - '.$text);
    CI_DB_sybase_driver::SetCodeErrorSybase($msgnumber);   
}

// ------------------------------------------------------------------------

Add and modify the following methods in the same sybase_driver.php file

/**
 * The error message number
 *
 * @access  private
 * @return  integer
 */
function _error_number()
{
    // Are error numbers supported?
    return CI_DB_sybase_driver::GetCodeErrorSybase();
}

function _sybase_set_message_handler()
{
    // Are error numbers supported?     
    return sybase_set_message_handler('CI_DB_sybase_driver::msg_handler');
}

Implement in the function of a controller.

public function Eliminar_DUPLA(){       
    if($this->session->userdata($this->config->item('mycfg_session_object_name'))){     
        //***/
        $Operacion_Borrado_Exitosa=false;
        $this->db->trans_begin();

        $this->db->_sybase_set_message_handler();  <<<<<------- Activar Manejador de errores de sybase
        $Dupla_Eliminada=$this->Mi_Modelo->QUERY_Eliminar_Dupla($PARAMETROS);                   

        if ($Dupla_Eliminada){
            $this->db->trans_commit();
            MostrarNotificacion("Se eliminó DUPLA exitosamente","OK",true);
            $Operacion_Borrado_Exitosa=true;
        }else{
            $Error = $this->db->_error_number();  <<<<----- Obtengo el código de error de sybase para personilzar mensaje al usuario    
            $this->db->trans_rollback();                
            MostrarNotificacion("Ocurrio un error al intentar eliminar Dupla","Error",true);
            if ($Error == 547) {
                MostrarNotificacion("<strong>Código de error :[".$Error.']. No se puede eliminar documento Padre.</strong>',"Error",true);
            }  else {                   
                MostrarNotificacion("<strong>Código de Error :[".$Error.']</strong><br>',"Error",true);                 
            }
        }

        echo "@".Obtener_Contador_Notificaciones();
        if ($Operacion_Borrado_Exitosa){
            echo "@T";
        }else{
            echo "@F";
        }
    }else{
        redirect($this->router->default_controller);
    }

}

In the log you can check the codes and messages sent by the database server.

INFO - 2019-11-06 19:26:33 -> CI_DB_sybase_driver - CODE ERROR [547] Message - Dependent foreign key constraint violation in a referential integrity constraint. dbname = 'database', table name = 'mitabla', constraint name = 'FK_SR_RELAC_REFERENCE_SR_mitabla'. INFO - 2019-11-06 19:26:33 -> CI_DB_sybase_driver - CODE ERROR [3621] Message - Command has been aborted. ERROR - 2019-11-06 19:26:33 -> Query error: - Invalid query: delete from mitabla where ID = 1019.

Please answer in English as this is an English website! – csabinho Nov 06 '19 at 22:19 — csabinho, Nov 06 '19 at 22:19

CodeIgniter - how to catch DB errors?

12 Answers12

Linked

Related