Welcome Guest, Not a member yet? Register   Sign In
Unable to connect to database - cannot find error messages
#1

I'm trying to connect a CI4 app to a DigitalOcean managed database. I have probably configured something wrong, but I am having a really hard time finding out what because I can't find any error messages from the database server. Is there anywhere in the debugger or in the logs where I can find the response from the database server?

How do I know if CI has sent a query, if it has received a response, and what that response is?

The debugger shows this error: CodeIgniter\Database\Exceptions\DatabaseException #8. Unable to connect to the database

The writable/logs log shows this error: CRITICAL - 2020-09-06 06:01:02 --> Unable to connect to the database.

For reference, here is my code:

PHP Code:
$this->db = \Config\Database::connect();

$this->db->query('INSERT INTO users (1, "user")'); 

Here is my db configuration (with sensitive info removed):

PHP Code:
public $default = [
    
'DSN'      => '',
    
'hostname' => '[unique address].ondigitalocean.com',
    
'username' => '[username]',
    
'password' => '[password]',
    
'database' => '[database]',
    
'DBDriver' => 'MySQLi',
    
'DBPrefix' => '',
    
'pConnect' => false,
    
'DBDebug'  => (ENVIRONMENT !== 'production'),
    
'cacheOn'  => false,
    
'cacheDir' => '',
    
'charset'  => 'utf8',
    
'DBCollat' => 'utf8_general_ci',
    
'swapPre'  => '',
    
'encrypt'  => false,
    
'compress' => false,
    
'strictOn' => false,
    
'failover' => [],
    
'port'     => 25060,
]; 

I have successfully connected using this login info with MySQL Workbench.
Reply
#2

Hard to say, do you have a ".env" file at the root of your project? Its content overrides your config files.

Actually, you SHOULD use that ".env" file instead of writing directly to app/Config/Database.php, because you can change configs between your different environments (development, testing, production, …) by simply having another ".env" in each copy of the project.

If my answer is no help, try to provide more details (I know it's a lot to ask, CI4 is quite secretive about why the database doesn't cooperate).
Reply
#3

(09-06-2020, 10:04 PM)SteeveDroz Wrote: Hard to say, do you have a ".env" file at the root of your project? Its content overrides your config files.

Actually, you SHOULD use that ".env" file instead of writing directly to app/Config/Database.php, because you can change configs between your different environments (development, testing, production, …) by simply having another ".env" in each copy of the project.

If my answer is no help, try to provide more details (I know it's a lot to ask, CI4 is quite secretive about why the database doesn't cooperate).
Thanks for the response!

I am using the .env file for db variables, but I have currently commented it out and only using the app/Config/Database.php for simpler debugging.

I would love to provide more details about the issue, but I am not sure where to find more useful information for this issue. Any suggestions on what information would be helpful?
Reply
#4

PHP Code:
// For getting records hasError()
if ($query->hasError())
{
        echo 'Code: '$query->getErrorCode();
        echo 'Error: '$query->getErrorMessage();
}

// For writing records
$error $this->db->error();
echo 
$error;
exit(); 
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#5

Try setting your logs threshold to 4 to see the error thrown by the connection. Then look again at your logs.
Reply
#6

Could it be that the mysql server requires ssl? If you have shell-access to the host, try running mysql client with --skip-ssl and connect to the database.
Reply
#7

(This post was last modified: 09-08-2020, 12:07 AM by John_Betong.)

@infidev,

I have recently changed to a DigitalOcean VPS and struggling to connect umpteen databases with the correct usernames/passwords... so created a standalone DEBUG script to check connections.

I decided to modify my old "CI3/app/config/database-DEBUG.php" to make it CI4 compatible.

I saved the file into "CI4/app/Config/Database-DEBUG.php" and included the file into a view that displays without any errors by using the following include statement:
PHP Code:
<?php 
  
require APPPATH 'Config/Database-DEBUG.php'

File: CI4/app/Config/database-DEBUG.php
PHP Code:
<?php DECLARE(STRICT_TYPES=1); 
error_reporting(-1);
ini_set('display_errors''true');
ini_set('log_errors''true');

$DATABASE     'databasename';
$USERNAME     'username';
$PASSWORD     'password';


$INFO "
    https://phpdelusions.net/articles/error_reporting
    https://phpdelusions.net/pdo#dsn
"
;
 
echo 
'<h4>file: ' .__FILE__ .'</h4>';

// EXACT COPY OF Database.php prameters
$default = [
        
'DSN'      => '',
        
'hostname' => 'localhost',
        
'username' => $USERNAME,
        
'password' => $PASSWORD,
        
'database' => $DATABASE,
        
'DBDriver' => 'MySQLi'# GOOD
    #    'DBDriver' => 'SQLite3',
    #    'DBDriver' => 'Postgre',         
    #    'DBDriver' => 'PDO',         
    #    'DBDriver' => 'pgsql:host=localhost;port=5432;dbname=ci4',
        
'DBPrefix' => '',
        
'pConnect' => false,
        
'DBDebug'  => (ENVIRONMENT !== 'production'),
        
'cacheOn'  => false,
        
'cacheDir' => '',
        
'charset'  => 'utf8',
        
'DBCollat' => 'utf8_general_ci',
        
'swapPre'  => '',
        
'encrypt'  => false,
        
'compress' => false,
        
'strictOn' => false,
        
'failover' => [],
        
'port'     => 3306,
    ];
    if(
0) : echo '$defaults[] ==> ';print_r($default); endif;



//=========================================================
    
if(|| isset($MYSQLI) ) :
        
$style 'width:88%; margin:0 auto; background-color: #cfc ; color: RED';
        echo 
"<pre style='$style'>";

        
mysqli_report(MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT);

        
$db = new mysqli
            
(
                
$default['hostname'], 
                
$default['username'], 
                
$default['password']
            );
        
$DB $db;
        echo 
'<h1> MYSQLI - Connected </h1>';    
        echo 
'Database: $DB ==> 'print_r($DB);
        echo 
'</pre>';
        echo 
'<hr>Line: '.__line__ .'<hr><br><br><br>';
    endif;
//=========================================================


//=========================================================
    
if(|| isset($PDO) ) :
        
$style 'width:88%; margin:0 auto; background-color: #ccf ; color: RED';
        echo 
"<pre style='$style'>";

        
$dbasename     $default['database'];
        
$host             $default['hostname']; // '127.0.0.1';
        
$user             $default['username'];
        
$pass             $default['password'];
        
$charset         'utf8mb4';

        
$dsn "mysql: 
            host=
$host
            dbname=
$dbasename
            charset=
$charset";
        
$options = [
            
PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            
PDO::ATTR_EMULATE_PREPARES   => false,
        ];
        try {
             
$pdo = new PDO($dsn$user$pass$options);
        } catch (\
PDOException $e) {
             throw new \
PDOException($e->getMessage(), (int)$e->getCode());
        }
        echo 
'<h1> PDO - Connected to database: '$DATABASE .'</h1>';    
        
$DB $pdo;
        echo 
'Database: $DB ==> 'print_r($DB);
        echo 
'</pre>';
        echo 
'<hr>Line: '.__line__ .'<hr><br><br><br>';
endif;
//=========================================================


//=========================================================
if(10 || isset($PDO) ) :
        
$style 'width:88%; margin:0 auto; background-color: #fcc ; color: RED';
        echo 
"<pre style='$style'>";

    
$INFO "
        https://phpdelusions.net/pdo#dsn
        
        Note that it's important to follow the proper format - 
        no spaces or quotes or other decorations have to be used in DSN, 
        but only parameters, values and delimiters, 
        as shown in the manual.
    "
;

    
# $db = "mysql:host=$host;dbname=$db;charset=$charset";
    
$dbname     =    $default['database'];
    
$hostname $default['hostname'];
    
$charset    'utf8mb4';
    
$dsn             "mysql:host=$hostname;dbname=$dbname;charset=$charset";
    
$options     = [
        
PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        
PDO::ATTR_EMULATE_PREPARES   => false,
    ];
    try {
         
$pdo = new PDO
         
(
             
$dsn
             
$default['username'], 
             
$default['password'], 
             
$options
         
);
    } catch (\
PDOException $e) {
        
print_r($e); die;
       throw new \
PDOException($e->getMessage(), (int)$e->getCode());
    }
    
$DB $pdo;
    echo 
'<h1> PDO - Connected to database: '$default['database'] .'</h1>';    
    echo 
'Database: $DB ==> 'print_r($DB);
    echo 
'</pre>';
    echo 
'<hr>Line: '.__line__ .'<hr><br><br><br>';
endif;
//========================================================= 


https://ci4-strict.tk/lhost/dbase-connect
Reply
#8

(This post was last modified: 09-09-2020, 01:00 AM by infidev.)

Thanks for all the great responses, everyone! I haven't been able to fix the issue, but I am now getting error messages.

The debug script from @John_Betong is a great resource that I'm keeping for future projects.

I set the log threshold to 4 and I also added the debug script. Now both the log and the error in the debugger is showing that the server is not responding. Specifically, I am getting a mysqli_sql_exception #2002 in the debugger.

Now, I suspect the issue is what @tgix suggested: the DigitalOcean database is refusing the connection because it requires ssl. But I don't think turning off ssl is the right solution; I'd much rather connect using ssl.

I can see in the DigitalOcean control panel that I can download a CA certificate for the database and I can see in the CI user guide that I can add a path to this file by setting the encrypt config value to this: "‘ssl_ca’ - Path to the certificate authority file". But I am struggling with finding the correct syntax for this.

What do you think? Am I on the right track? Also, does anyone have an example of syntax for adding ssl_ca to the encrypt config value?
Reply
#9

(09-09-2020, 12:59 AM)infidev Wrote: Thanks for all the great responses, everyone! I haven't been able to fix the issue, but I am now getting error messages.

The debug script from @John_Betong is a great resource that I'm keeping for future projects.

I set the log threshold to 4 and I also added the debug script. Now both the log and the error in the debugger is showing that the server is not responding. Specifically, I am getting a mysqli_sql_exception #2002 in the debugger.

Now, I suspect the issue is what @tgix suggested: the DigitalOcean database is refusing the connection because it requires ssl. But I don't think turning off ssl is the right solution; I'd much rather connect using ssl.

I can see in the DigitalOcean control panel that I can download a CA certificate for the database and I can see in the CI user guide that I can add a path to this file by setting the encrypt config value to this: "‘ssl_ca’ - Path to the certificate authority file". But I am struggling with finding the correct syntax for this.

What do you think? Am I on the right track? Also, does anyone have an example of syntax for adding ssl_ca to the encrypt config value?

Hi,

I'm facing the same problem as you now. May I know did you manage to solve this issue?
Reply
#10

For instance, it can be because of the wrong login username and password being used to access the admin panel. If the login credentials have changed recently, you will not be able to access your database with the old ones. So your first order of business should be to confirm that the login credentials you’re entering are accurate.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB