multiple database, persistent connection

[eluser]broadband[/eluser]
I tried this code:

Code:

[b]MySQL database test1:[/b]

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');

[b]MySQL database test2:[/b]

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);

[b]pconnect.php[/b]

<?php

  $username = "xxxx";
  $password = "xxxx";
  $hostname = "localhost";

  $link  = mysql_pconnect($hostname, $username, $password);
  $link2 = mysql_pconnect($hostname, $username, $password);

  var_dump($link);
  var_dump($link2);

  if( $link === $link2 )
    echo "Exactly the same." . PHP_EOL;

  // result 1
  //
  mysql_select_db('test1', $link);

  $result = mysql_query("select name from table1");

  while( $row = mysql_fetch_assoc($result) )
    echo $row['name'] . PHP_EOL;


  // result 2
  //
  mysql_select_db('test2', $link2);

  $result = mysql_query("select id from table2");

  while( $row = mysql_fetch_assoc($result) )
    echo $row['id'] . PHP_EOL;

?>

Output is:

Code:

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:

Code:

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;



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;
}

[eluser]broadband[/eluser]
The output of function test() is:

Code:

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

[eluser]broadband[/eluser]
This must be a bug within codeigniter, because the first code shows that you can one persistent connection and use different database.

[eluser]broadband[/eluser]
Found that this already is a bug:

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

This is the best solution to the problem. There is a bug in CI. 1 line of code fixes it so you can do stuff like…

$a = $this->load->database(‘default’, TRUE);
$b = $this->load->database(‘default’, TRUE);

...without the second call to load->database overwriting the first.

Here is the bugfix:
http://koorb.wordpress.com/2007/11/16/co...comment-40

Or if you don’t want to go there…

#####start bugfix#####

Description

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);
}

#####end bugfix#####http://ellislab.com/forums/viewthread/157298/#843990

[eluser]InsiteFX[/eluser]
Report it as a BUG!

You should never edit CodeIgniter core classes!

Just think what is going to happen when you upgrade to a new version and copy the system directory over...

[eluser]d3ptzz[/eluser]
I have same problem, and i just change one line in config/database.php to fix it

change value of this:

$db['test1']['pconnect'] = TRUE;
$db['test2']['pconnect'] = TRUE;

to FALSE :
$db['test1']['pconnect'] = FALSE;
$db['test2']['pconnect'] = FALSE;

and done!