| Multiple databases, problem and solution. [message #32178] |
Wed, 25 July 2007 00:52 |
alexg3
Messages: 33
Registered: October 2006 |
Member |
|
|
Hello,
I have a project with two databases. Occasionally, I need information from both databases; therefore, two connections must be made. My problem was managing those connections, but I have solved it halfway through typing this. The problem is identified in bold, the solution is at the end.
Also note that I'm not using propel/creole or doctrine. I'm simply using sfMySQLDatabase, though this should apply to sfPDO and sfPostgre as well.
[databases.yml (simplified)
all:
database1:
class: sfMySQLDatabase
param:
database: database1
username: user
password: pass
hostspec: localhost
database2:
class: sfMySQLDatabase
param:
database: database2
username: user
password: pass
hostspec: localhost
Problem: the host/user/pass is the same for both databases. Also, the call to mysql_connect in sfMySQLDatabase does not specify the optional parameter, new_link = true. Therefore, only one Resource will be created for both databases and that will cause problems.
You may stop reading at this point or continue to see the odd behavior this causes.
I am also using the sfMySQLSessionStorage. It uses a table in database1.
factories.yml
storage:
class: sfMySQLSessionStorage
param:
database: database1
db_table: session
db_id_col: id
db_data_col: data
db_time_col: time
Test code in actions.class.php
// sfMySQLSessionStorage starts
// Resource id #60
$db_manager = $this->getContext()->getDatabaseManager();
echo $db_manager->getDatabase('database2')->getConnection();
=> Resource id #60
Database2_Table::getById(1);
=> SELECT * FROM database2_table WHERE id='1'
=> array(...) // data is ok
echo $db_manager->getDatabase('database1')->getConnection();
=> Resource id #60
Database1_Table::getById(1);
=> SELECT * FROM database1_table WHERE id='1'
=> Error:: Table 'database2.database1_table' doesn't exist (# 1146)
Since the tables are not named the same in both databases, it complains that database2.table_from_database1, does not exist.
It gets strange if I reverse the situation by retrieving data from the database1 first.
// sfMySQLSessionStorage starts
// Resource id #60
$db_manager = $this->getContext()->getDatabaseManager();
echo $db_manager->getDatabase('database1')->getConnection();
=> Resource id #60
Database1_Table::getById(1);
=> SELECT * FROM database1_table WHERE id='1'
=> array(...) // data is ok
echo $db_manager->getDatabase('database2')->getConnection();
=> Resource id #60
Database2_Table::getById(1);
=> SELECT * FROM database2_table WHERE id='1'
=> array(...) // data is ok
In other words, it works depending on the order, and not the order that I'd expect.
Solution:
- Create a different database user for each database.
- Specify true for the fourth argument of mysql_connect, insde of symfony/database/sfMySQLDatabase.
IMHO, the optional new_link argument should be TRUE by default.
cheers,
alex
[Updated on: Wed, 25 July 2007 00:52]
|
|
|
Members
Search
FAQ
Register
Login
Home
