CodeIgniter: Unable to connect to your database server using the provided settings Error Message

Question

I have been using CI just fine using the MySQL driver. I want to use the MySQL driver instead, but as soon as I change it (just add the ‘i’ at the end of MySQL, and added the port number) I get the following error message

A Database Error Occurred

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

Filename: core/Loader.php

Line Number: 232

my setting look like this:

$db['default']['hostname'] = $hostname;
$db['default']['username'] = $username;
$db['default']['password'] = $password;
$db['default']['database'] = $database;
$db['default']['dbdriver'] = 'mysqli';
$db['default']['port']     = "3306";  
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE; 

where
$hostname = 'localhost';
$username = 'myusernamegoeshere';
$password = 'mypasswordgoeshere';
$database = 'mydatabasenamegoeshere'; 

I'm Using:

CI 2.0.2 php 5.3.4 Apache/2.2.17 (Unix) mysql 5.5.13 mysql.default_port 3306

Am I doing anything wrong?

Thank you,

Solution 1

For me the issue was in the php.ini file. The property mysql.default_socket was pointing to file in a non-existent directory. The property was pointing to /var/mysql/mysql.sock but in OSX, the file was located in /tmp/mysql.sock.

Once I updated the entry in php.ini and restarted the webserver, the issue was resolved.

Solution 2

I think, there is something wrong with PHP configration.
First, debug your database connection using this script at the end of ./config/database.php :

...
  ...
  ...
  echo '<pre>';
  print_r($db['default']);
  echo '</pre>';

  echo 'Connecting to database: ' .$db['default']['database'];
  $dbh=mysql_connect
  (
    $db['default']['hostname'],
    $db['default']['username'],
    $db['default']['password'])
    or die('Cannot connect to the database because: ' . mysql_error());
    mysql_select_db ($db['default']['database']);

    echo '<br />   Connected OK:'  ;
    die( 'file: ' .__FILE__ . ' Line: ' .__LINE__); 

Then see what the problem is.

Solution 3

Today I fallen this kind of problem in live server and i solved the problem changing this line

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

to

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

Solution 4

I solved the problem by changing

$db['default']['pconnect'] = TRUE; TO $db['default']['pconnect'] = FALSE;

in /application/config/database.php

Solution 5

SET $db['default']['db_debug'] to FALSE instead of TRUE .

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

View more solutions

Comments

[Onema]

Than you for your answer. I believe starting with php 5.3 mysqli is enabled by default. But I did check my php info, and this is what I saw d.pr/FUZ6

[Craig A Rodway]

OK. Change the log level to 4 in config.php, and check app/logs/log-2011-08-31.php file for any obvious errors (once you've refreshed the page in your app). Look for lines with mysqli_connect() in it.

[gelviis]

By far the best answer. I got errors and then when I replaced default hostname with $db['default']['hostname'] = ':/Applications/MAMP/tmp/mysql/mysql.sock'; it worked. Thanks to this here stackoverflow.com/questions/4219970/…

[Petar Zivkovic]

This did the trick for me, except the values were reversed! My php.ini was pointing to '/tmp' but should have been '/var/mysql/mysql.sock'... I am also on OSX (for reference)

[iTurki]

This should be the accepted answer. You really saved my day. My problem was so simple that I couldn't see, a password typo! and your script helped me see it. Thanks man!

[sharif2008]

mee to :) and One UP VOTE for u .

[James Lai]

Please note that there are two settings for this: mysql.default_socket and pdo_mysql.default_socket.

[Jason H]

This was the issue for me using postgresql DB in a live environment. Thanks a ton because I had no clue what the issue is as I'm new to PHP.

[CesareoAguirre]

Yes! Thanks Ken. I see tree places, no time to check what for. pdo_mysql.default_socket=/tmp/mysql.sock mysql.default_socket =/tmp/mysql.sock mysqli.default_socket =/tmp/mysql.sock

[Manas Bajaj]

Thanks a lot! Leaving username and password blank seems to have solved my problem.

[Jez D]

Yep. This is what worked for me. pconnect - TRUE/FALSE (boolean) - Whether to use a persistent connection.

[Boyan]

Okay, can someone please explain WHY is this causing connection issues? I'm so frustrated, I lost about an hour trying to figure out how my project broke overnight and this fixed it. And I have no idea why.

[Lalo Sánchez]

In my case, both settings were blank in the php.ini file, causing it to use MySQL default value, which I guess is /var/mysql/mysql.sock since this answer solved it for me. An alternate solution is to set a symbolic link from /tmp/mysql.sock to /var/mysql/mysql.sock.

[Pramod Ravikant]

Worked! Any explanation for this?

[TaraGurung]

Worked for previous error but now throwing this error ** A PHP Error was encountered Severity: 8192 Message: mysql_escape_string(): This function is deprecated; use mysql_real_escape_string() instead. Filename: mysqli/mysqli_driver.php Line Number: 319**

[Shoppyonline]

I'm still trying to figure out what the problem is because though the page site is now back, the data from the DB is all gone. The data is there, I checked but its not showing on the site.

[Anthony]

Thanks, this solved my problem on OSX El Capitan MAMP

[MarcM]

Great answer. Adapted to mysqli and posted below. Just in case it might be useful for someone else.

[Pablo Camara]

For me this was also the solution. I changed my ENV variable "hostname" from "localhost" to '127.0.0.1' and it worked :)

[jvbs]

The same happened to me, i was using a Mysql database and now after changing for a SQL Server, this issue happened on my Local env and does not make any sense...

[Abdulaziz Hamdan]

This provided me the php error to identify where the database was trying to connect and query. with this, my problem was fixed by changing my php version from 7.0 to 7.3 in wamp... not sure why though..