Connecting CI to TWO different DB on localhost

Hello I am new

I already wrote that question here but got no satisfying answer
http://stackoverflow.com/questions/26741...-instances

Let me describe the problem quicky.

I have an application somewhere on two servers that's not supposed to have http or php but have their mysql database.

I have a third server, that runs two instances of mysql. Both run on same host with seperate logfiles, config dirs and ports.
One runs on port 3306 the other uses 3307.
One uses /var/run/mysql/mysql.sock
The other /var/run/mysql/mysql2.sock

Now on this server I want to use CI framework to collect data from both servers and output them with json.

It works connecting to mysql instance but not the mysql2 instance.
Here is my database conf for the second server:

PHP Code:

//db1 array here..

$db['db2'] = array(
        'dsn'   => '',
        'hostname' => 'localhost',
        'port'     => '3307',
        'username' => '***',
        'password' => '***',
        'database' => 'db',
        'dbdriver' => 'mysqli',
        'dbprefix' => '',
        'pconnect' => FALSE,
        'db_debug' => TRUE,
        'cache_on' => FALSE,
        'cachedir' => '',
        'char_set' => 'utf8',
        'dbcollat' => 'utf8_general_ci',
        'swap_pre' => '',
        'autoinit' => TRUE,
        'encrypt' => FALSE,
        'compress' => FALSE,
        'stricton' => FALSE,
        'failover' => array(),
        'save_queries' => TRUE
); 


When I stop the first server, I get the error that it can not connect to socket '/var/run/mysqld/mysqld.sock', of course because I just stopped it. But how can I get CI to use  socket '/var/run/mysqld/mysqld2.sock' for db2 while still using mysql.sock for db1? Or do I have tell php to use TCP instead of unix socket? Because on chell I need to use --protocol=TCP to connect to the second server. How?

TIA

Just connect to both depend on your database configuration like that

PHP Code:

$db1 = $this->load->database('db1', TRUE);
$db2 = $this->load->database('db2', TRUE); 


Just connect to both depend on your database configuration like that

PHP Code:

$db1 = $this->load->database('db1', TRUE);
$db2 = $this->load->database('db2', TRUE); 


The same was told in the stackoverflow question but...
This does NOT work, because both server instances are connected to different sockets.

My question was "But how can I get CI to use socket '/var/run/mysqld/mysqld2.sock' for db2 while still using mysql.sock for db1?" Or can I connect to the servers using TCP localhost instead?

Can anyone please help me with a solution for that?

You can use a socket in your connection like this

PHP Code:

'hostname' => 'localhost:/var/run/mysqld/mysqld2.sock', 


You can use a socket in your connection like this

PHP Code:

'hostname' => 'localhost:/var/run/mysqld/mysqld2.sock', 


Wow cool when this works, it is exactly what I was looking for

thank you!

Sadly this does not work. It gives an error: Message: mysqli::real_connect(): (HY000/2005): Unknown MySQL server host 'localhost:/var/run/mysqld/mysqld.sock' (2)

Funny enough even if I uncomment the loader for db1, it still tries to access db1 and mysql.sock and not mysql2.sock... why?

here is the beginning of my model

PHP Code:

   function __construct()
    {
        // Call the Model constructor
        parent::__construct();

        $this->db1 = $this->load->database('db1', TRUE);
        $this->db2 = $this->load->database('db2', TRUE);
    } 


my full database.php

PHP Code:

$active_group = 'db1';
$query_builder = TRUE;

$db['db1'] = array(
        'dsn'   => '',
        'hostname' => 'localhost:/var/run/mysqld/mysqld.sock',
        'port'     => '3306',
        'username' => '***',
        'password' => '***',
        'database' => 'db',
        'dbdriver' => 'mysqli',
        'dbprefix' => '',
        'pconnect' => FALSE,
        'db_debug' => TRUE,
        'cache_on' => FALSE,
        'cachedir' => '',
        'char_set' => 'utf8',
        'dbcollat' => 'utf8_general_ci',
        'swap_pre' => '',
        'autoinit' => TRUE,
        'encrypt' => FALSE,
        'compress' => FALSE,
        'stricton' => FALSE,
        'failover' => array(),
        'save_queries' => TRUE
);

$db['db2'] = array(
        'dsn'   => '',
        'hostname' => 'localhost:/var/run/mysqld/mysqld2.sock',
        'port'     => '3307',
        'username' => '***',
        'password' => '***',
        'database' => 'db',
        'dbdriver' => 'mysqli',
        'dbprefix' => '',
        'pconnect' => FALSE,
        'db_debug' => TRUE,
        'cache_on' => FALSE,
        'cachedir' => '',
        'char_set' => 'utf8',
        'dbcollat' => 'utf8_general_ci',
        'swap_pre' => '',
        'autoinit' => TRUE,
        'encrypt' => FALSE,
        'compress' => FALSE,
        'stricton' => FALSE,
        'failover' => array(),
        'save_queries' => TRUE
); 


The sockets are there

PHP Code:

root@vps:~# ls -la /var/run/mysqld/mysqld*
-rw-rw---- 1 mysql mysql 6 Dec  9 18:36 /var/run/mysqld/mysqld2.pid
srwxrwxrwx 1 mysql mysql 0 Dec  9 18:36 /var/run/mysqld/mysqld2.sock
-rw-rw---- 1 mysql mysql 6 Dec  9 19:05 /var/run/mysqld/mysqld.pid
srwxrwxrwx 1 mysql mysql 0 Dec  9 19:05 /var/run/mysqld/mysqld.sock 


Is it exactly the same database?

Is it exactly the same database?

No
Its the same database layout but it are two slave-instances for two different masters filling in different data. So both dbs are copies of each other but different contents.

Ah i think the problem is, that the sockect connection i described should work with a >>mysql<< connection. mysqli needs an own parameter for the socket. I looked through the driver class and saw, that there is a null value for the socket param. I seams that we have to extend this requirement.

Can you try - just as test - to connect with the outdated mysql instead of mysqli.

Ok, this gives me a warning level

PHP Code:

Severity: Warning

Message: mysql_select_db() expects parameter 2 to be resource, boolean given

Filename: mysql/mysql_driver.php

Line Number: 180 


And then fails to select the database

A Database Error Occurred
Unable to select the specified database: db

Looking into the driver it seems that conn_id is not get set to an ID but still false, hence the connection with the socked parameter failed silently (there was no "unable to connect" error).

(When I remove the :/var/run/mysqld/mysqld.sock part from localhost, it works with mysql_driver, but then I still can not specify my sockets)