CodeIgniter PDO database driver not working

Question

I'm trying to use the PDO MySQL driver in my CodeIgniter application. This is my database config:

$active_group = 'default';
$active_record = TRUE;

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'root';
$db['default']['password'] = '';
$db['default']['database'] = 'testdatabase';
$db['default']['dbdriver'] = 'pdo';
$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;

However, I'm getting this error when I load a controller:

Fatal error: Uncaught exception 'PDOException' with message 'invalid data source name' in C:\xampp\htdocs\testsite\system\database\drivers\pdo\pdo_driver.php:114

I've checked the data source using die($this->hostname); in pdo_driver.php and it's coming out as:

localhost;dbname=testdatabase

so it is getting the correct database name. The database exists and I do have MySQL running.

What could be going wrong here? Thank you.

Sidenote: I recently read the changelog for the new version (2.1.1) and there are some bugfixes regarding the PDO driver. You might want to check it — Damien Pirsy, Jun 15 '12 at 17:29
Martin, I am getting a similar error: `Fatal error: Uncaught exception 'PDOException' with message 'could not find driver' in...((several database and core dir/ files listed))... syetem/database/drivers/pdo/pdo_driver.php on line 114` I'm new enough that I can't even tell if your hack will help me. Where and what **exactly** did you edit in `.../system/database/drivers/pdo/pdo_driver.php`? I'm pretty newbie on config topics. It seems odd to me that a hack to a system file is needed to make PDO work in CI v.2.1.1, doesn't it? Why are (assumedly most) others not needing the hack you mention? — govinda, Jun 17 '12 at 01:42
@govinda, I did mine on line 81, right after the `else { ... }` in the class constructor. It does seem weird that you need to hack it at all but I've found nothing wrong with it since my fix. — James Dawson, Jun 18 '12 at 20:28
Martin, could you spell it out as a complete thought for me? I want to be sure if I am hacking a core system file. Do you mean to say you changed the out-of-the-box line #81 of `.../system/database/drivers/pdo/pdo_driver.php` from this: `empty($this->database) OR $this->hostname .= ';dbname='.$this->database;`, to this: `empty($this->database) OR $this->hostname = 'mysql:dbname='.$this->database.';host='.$this->hostname;`? Or else could you say it like that? - what line changed from what to what...? — govinda, Jun 18 '12 at 23:38
I put it up on pastebin for you: http://pastebin.com/SzBcdHGr just download that file and replace `/system/database/drivers/pdo/pdo_driver.php` with the one from Pastebin and it should work. Only one line is changed so it should work out of the box. — James Dawson, Jun 19 '12 at 01:19
thanks for doing that! When I try that copy, I get this error: `A PHP Error was encountered Severity: Warning Message: PDO::__construct() [pdo.--construct]: [2002] Can't assign requested address (trying to connect via tcp://localhost:/tmp/mysql.sock:3306) Filename: pdo/pdo_driver.php Line Number: 114 Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [2002] Can't assign requested address' in ../system/database/drivers/pdo/pdo_driver.php:114 Stack trace: #0 ` ...plus: — govinda, Jun 19 '12 at 04:15
`../system/database/drivers/pdo/pdo_driver.php(114): PDO->__construct('mysql:dbname=GM...', 'root', '', Array) #1 ../system/database/DB_driver.php(115): CI_DB_pdo_driver->db_pconnect() #2 ../system/database/DB.php(148): CI_DB_driver->initialize() #3 ../system/core/Loader.php(346): DB('', NULL) #4 ../system/core/Loader.php(1171): CI_Loader->database() #5 ../system/core/Loader.php(152): CI_Loader->_ci_autoloader() #6 /Library/WebServer/Documents/GMDO_phase3_l in ../system/database/drivers/pdo/pdo_driver.php on line 114` — govinda, Jun 19 '12 at 04:15
...which is similar to what i get when I try various (unsuccessful) guesses at what should be the value of `$db['local_dev']['hostname']` in my database config. If you have any more ideas, I would love to hear them. BTW, there were a few differences between our copies of `/system/database/drivers/pdo/pdo_driver.php`, here is mine: http://pastebin.com/hV9Ezaqp The diff on line # 192 you might find esp. interesting. I get over my head quick, so you better have a look yourself. Let me know what you think about that. — govinda, Jun 19 '12 at 04:24

score 29 · Answer 1 · answered Nov 27 '12 at 15:57

29

On file /application/config/database.php where is

$db['default']['hostname'] = 'localhost';

must be

$db['default']['hostname'] = 'mysql:host=localhost';

localhost or your database host.

answered Nov 27 '12 at 15:57

MrGusMuller

299
1
3
4

1

Doh. Of course it should. I just presumed CI would handle it off the bat. – PaulSkinner Sep 25 '13 at 08:32
Strictly speaking, that's not a hostname. The proper approach is to provide a value for dsn in the db config as described in the answer below. – S. Imp Sep 07 '18 at 22:47

score 11 · Accepted Answer · answered Jun 15 '12 at 16:23

11

This should not be the case.

localhost;dbname=testdatabase

should be

mysql:dbname=testdatabase;host=localhost;

answered Jun 15 '12 at 16:23

Mike Mackintosh

13,917
6
60
87

1

Thanks, I modified the CodeIgniter PDO driver to this: `$this->hostname = 'mysql:dbname='.$this->database.';host='.$this->hostname;` and it seems to work now. :) – James Dawson Jun 15 '12 at 16:34
3

Modifying the core driver is unnecessary, and will break the next time you update CI unless you remember to make the change to the core file again. Take the approach below, editing your database settings file instead. – PaulSkinner Sep 25 '13 at 08:34

score 7 · Answer 3 · edited Dec 14 '15 at 11:32

PDO driver require a full DSN string to be provided. The string like this

'dsn' = ‘mysql:host=localhost;dbname=databasename’;

when you use this string you should remove host and databasename value from array. I think following example gives you an idea.

$db['default'] = array(
'dsn'   => 'mysql:host=localhost;dbname=codeigniter3',
'hostname' => '',
'username' => 'root',
'password' => '',
'database' => '',
'dbdriver' => 'pdo',
'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
);

Thanks

score 0 · Answer 4 · edited Dec 14 '15 at 11:48

According to CodeIgniter's Database Configuration page,

For the PDO driver, you should change the 'hostname => 'localhost'' to "'hostname' => mysql:host=localhost" like below:

$db['default'] = array(
    'dsn'   => '',
    'hostname' => 'mysql:host=localhost',
    'username' => 'root',
    'password' => '',
    'database' => 'database_name',
    'dbdriver' => 'pdo',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
     .....
     .....
);

CodeIgniter PDO database driver not working

4 Answers4

Linked