Cakephp 无法即时更改数据库
我试图从循环中连接多个数据库,但看到 CakePHP 无法更改 database
,只能更改其他信息(如用户/密码/主机).
app/Config/database.php
app/Controller/CronController.php
$companys = $this->Company->find('all');foreach($companys as $company) {$设置=数组('数据源' =>'数据库/Mysql','主机' =>$company['Company']['host'],'登录' =>$company['公司']['用户名'],'密码' =>$company['公司']['密码'],'数据库' =>$company['Company']['database'],);ConnectionManager::drop('client');$db = ConnectionManager::create('client', $settings);尝试 {调试($this->MyModel->find('first'));} 捕获(异常 $e){echo '';echo "异常: ", $e->getMessage(), "
";/*调试($this->MyModel->getDataSource());输出:[...][配置] =>大批([持久] =>[主持人] =>0.0.0.0//正确的主机[登录] =>root//正确登录[密码] =>pass//正确的密码[数据库] =>数据库1[端口] =>3306[数据源] =>数据库/Mysql[前缀] =>[编码] =>utf8)[...]*/}}
它返回第一个连接和所有其他连接,我无法从 MyModel 中选择任何内容,因为它是错误的.它看到来自用户/密码/主机的连接是好的,但是,数据库没有改变,所以,因为用户没有在 cdatabase 上选择的权限,我得到了错误.
数组(//第一次连接,连接正常,MyModel 什么都不返回)//第二个连接异常:SQLSTATE[42000]:语法错误或访问冲突:1142 SELECT 命令拒绝用户 'database_user_2'@'localhost' 用于表 'my_model'//第三个连接异常:SQLSTATE[42000]:语法错误或访问冲突:1142 SELECT 命令拒绝用户 'database_user_3'@'localhost' 用于表 'my_model'//第四个连接异常:SQLSTATE[42000]:语法错误或访问冲突:1142 SELECT 命令拒绝用户 'database_user_4'@'localhost' 用于表 'my_model'//第五个连接异常:SQLSTATE[42000]:语法错误或访问冲突:1142 SELECT 命令拒绝用户 'database_user_5'@'localhost' 用于表 'my_model'
谢谢!
解决方案尽量不要放弃配置,只改变你需要的东西.
对于这个任务,我成功地使用了
$dataSource = ConnectionManager::getDataSource('company_data');$dataSource->config['schema'] = 'company_'.$id;
我不知道数据库切换和 mysql 作为引擎是否合适.为此,我使用了 postgresql 模式.
I am trying to connect from multiples databases from a loop, but seens CakePHP can't change database
, only others infos (like user/pass/host).
app/Config/database.php
<?php
class DATABASE_CONFIG {
[...]
public $default = array(
[..] // Where I have the companies
);
public $client = array(
[...] // Fakke settings, because I will change it on-the-fly
);
}
app/Controller/CronController.php
$companies = $this->Company->find('all');
foreach($companies as $company) {
$settings = array(
'datasource' => 'Database/Mysql',
'host' => $company['Company']['host'],
'login' => $company['Company']['username'],
'password' => $company['Company']['password'],
'database' => $company['Company']['database'],
);
ConnectionManager::drop('client');
$db = ConnectionManager::create('client', $settings);
try {
debug($this->MyModel->find('first'));
} catch (Exception $e) {
echo '<pre>';
echo "Exception: ", $e->getMessage(), "
";
/*
debug($this->MyModel->getDataSource());
Outputs:
[...]
[config] => Array
(
[persistent] =>
[host] => 0.0.0.0 // CORRECT HOST
[login] => root // CORRECT LOGIN
[password] => pass // CORRECT PASSWORD
[database] => database1
[port] => 3306
[datasource] => Database/Mysql
[prefix] =>
[encoding] => utf8
)
[...]
*/
}
}
It return the first connection and all others I can select nothing from MyModel, because it is wrong. It seens connection from user/password/host is ok, but, database are not changed, so, because user haven't permission to select on cdatabase, I get the error.
Array
(
// First connection, connection ok, MyModel return nothing
)
// Second connection
Exception: SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'database_user_2'@'localhost' for table 'my_model'
// Third connection
Exception: SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'database_user_3'@'localhost' for table 'my_model'
// Fourth connection
Exception: SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'database_user_4'@'localhost' for table 'my_model'
// Fifth connection
Exception: SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'database_user_5'@'localhost' for table 'my_model'
Thanks!
解决方案Try not to drop config, just alter the things you need.
For this task I successfully use
$dataSource = ConnectionManager::getDataSource('company_data');
$dataSource->config['schema'] = 'company_'.$id;
I don't know if database switching and mysql as engine is good pair. I use postgresql schemas for this purpose.
相关文章