EllisLab/CodeIgniter — Issue #1068: MySQL multiple database, persistent connection
Author: mspace
Created: 2012-02-22 10:39:03+00:00
State: open

MySQL database test1:

CREATE DATABASE test1 CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS `table1` ( `name` varchar(64) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `table1` (`name`) VALUES ('Mike'),('Jennifer');

MySQL database test2:

CREATE DATABASE test2 CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS `table2` (`id` int(11) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `table2` (`id`) VALUES (1), (2), (3), (4);

pconnect.php

output of pconnect.php

resource(5) of type (mysql link)
resource(5) of type (mysql link)
Exactly the same.
Mike
Jennifer
1
2
3
4

This means that you can have persistent connection for multiple databases. You just have to use mysql_select_db.
In codeigniter if I use this:

database.php

// test1
//
$db['test1']['hostname'] = 'localhost';
$db['test1']['username'] = 'xxx';
$db['test1']['password'] = 'xxx';
$db['test1']['database'] = 'test1';
$db['test1']['dbdriver'] = 'mysql';
$db['test1']['dbprefix'] = '';
$db['test1']['pconnect'] = TRUE;
$db['test1']['db_debug'] = TRUE;
$db['test1']['cache_on'] = FALSE;
$db['test1']['cachedir'] = '';
$db['test1']['char_set'] = 'utf8';
$db['test1']['dbcollat'] = 'utf8_general_ci';
$db['test1']['swap_pre'] = '';
$db['test1']['autoinit'] = TRUE;
$db['test1']['stricton'] = FALSE;

// test2
//
$db['test2']['hostname'] = 'localhost';
$db['test2']['username'] = 'xxx';
$db['test2']['password'] = 'xxx';
$db['test2']['database'] = 'test2';
$db['test2']['dbdriver'] = 'mysql';
$db['test2']['dbprefix'] = '';
$db['test2']['pconnect'] = TRUE;
$db['test2']['db_debug'] = TRUE;
$db['test2']['cache_on'] = FALSE;
$db['test2']['cachedir'] = '';
$db['test2']['char_set'] = 'utf8';
$db['test2']['dbcollat'] = 'utf8_general_ci';
$db['test2']['swap_pre'] = '';
$db['test2']['autoinit'] = TRUE;
$db['test2']['stricton'] = FALSE;

controllers/front.php

public function test()
{
$db1 = $this->load->database('test1', true);
$db2 = $this->load->database('test2', true);

$db1->select('name')->from('table1');
foreach($db1->get()->result() as $row)
echo $row->name . PHP_EOL;
}

The output of function test() is:

A Database Error Occurred

Error Number: 1146

Table 'test2.table1' doesn't exist

SELECT `name` FROM (`table1`)

Filename: /srv/web/test/controllers/front.php

Line Number: 47

This must be a bug within codeigniter, because the first code shows that you can one persistent connection and use different database. Found that this already is a bug:

http://codeigniter.com/forums/viewthread/157298/#843990

Here is the bugfix:
http://koorb.wordpress.com/2007/11/16/codeigniter-connect-to-multiple-databases/#comment-40
all of the database calls go to the same database (last one initialized)

To fix the probleme change the simple_query function in DB_driver.php:

function simple_query($sql)
{
if ( ! $this->conn_id)
$this->initialize();
$this->db_select(); //Added this line
return $this->_execute($sql);
}