Welcome Guest, Not a member yet? Register   Sign In
Multiple database + pconnect issues

I'm not sure I follow this, but today I had a problem with a perfectly valid query that was failing. I output the generated SQL and tested it in phpMyAdmin. It worked fine. In my CI based site, it would fail. After some time, I thought what have I changed recently? It was disabling the default persistent connections in the database config and after enabling them again, the query worked fine.

I thought with data abstraction, the same query should work whether persistent connections were enabled or not. I can understand when querying between servers, but my query was not exotic. It seemed strange a valid query would work with one and not the other.

I even commented out the code and put in a simple SELECT statement and it did the same thing. Yet, other queries and parts of the site continued to work.

Is there some explanation for this?

Your observation sounds quite strange, are you able to reproduce it?

One thing that just pops up in my mind is that if you updated your DB config, you probably restarted it to take the changed parameters into account. So it might be that there was a general problem with the DBMS which you solved by restarting it..

I have the same problem. Example in a controller:

$this->load->model('Model', '', true); // default schema

$db = $this->load->database('schema', true);
$foo = $db->query(.....);


In read() method of my model I use AR to retrieve data. According with CI docs, second database load is independent, but when model tries to get data, uses LAST loaded schema.

I solved it using $db['schema']['pconnect'] = FALSE;

This might be because both schemas are on the same database server, in which case the connection is opened only once even if you call the connect method twice. If you want to open a second independent connection to the same server, you need to force this (generally the last parameter in the connect method).

This is, however, not yet implemented in the CI database config. Once I get some time, I will provide the details for how to do this. In the meantime, you need to set the parameter directly in the connection method of the CI database driver you are using. See also this thread for details.

So are you gents suggesting that if we were to connect to multiple databases we should turn off pconnect in all db connections?

What I was saying is that if you have two schemas/databases on the same database server, and you want two connections (two distinct connection pointers), one to each of them, then you need to set a special parameter in the connect method that forces a reconnect. For otherwise, the second call to the connect method will return the same connection as the first. As far as I remember, this is independent of whether you have persistent connections turned on or not.

Well I have an app that connects to two databases in the same database server. I was experiencing problems similar to the posters above. That was when I was not being too careful and had pconnect set to TRUE on both connections. What I did was set it to FALSE on both of them. I don't have any problems so far. But my question is, is this an optimal approach?

Depends on what you want to optimise. Performance-wise it is probably not that optimal to disable persistent connections. Yet maybe it makes your code cleaner and easier to maintain, which is something I would promptly trade for performance if the website is not subject to heavy load.

[quote author="bAum" date="1217333487"]Depends on what you want to optimise. Performance-wise it is probably not that optimal to disable persistent connections. Yet maybe it makes your code cleaner and easier to maintain, which is something I would promptly trade for performance if the website is not subject to heavy load.[/quote]

Hmmm I don't know about making my code easier to maintain cuz all I did was set the database.php config file to pconnect false. Then in my controllers constructor I just assigned
a $this->load->database('foo_table',true); to a variable and away we go. Although you say that it's better performance wise to have pconnect on. Do you mean sir that in my database.php config file I need to have one database connection with pconnect on? and the other connection off?

It seems that we are talking about two different problems here, let me clarify Smile

What I meant is that having an object for each databse (being able to say $db1->query() and $db2->query()) is very convenient. It is, however, not possible with the usual connection parameters if these two databases happen to be on the same mysql/postgres/.. server.

In that case the connection function will refuse to connect to the same server twice, and so $db1 and $db2 are actually the same. This means that in your code, you explicitly need to change the active database each time you alternate between $db1 and $db2 (by issuing a USE query first). While this might be more optimal, since you have only one open connection instead of two, it will mess up your code if you alternate between $db1 and $db2 very often.

Instead, you can force a reconnect and you'll end up with two connections to the same database server but on different databases ($db1 and $db2 are different). This might be considered a waste of performance, but now your $db1 and $db2 can be used transparently, without the need of clunking your code with USE queries.

A third solution would be to rewrite the db connection driver like the guys at the start of the thread did, but well, you need to do it, you need to maintain it and so on..

As for the persistent connections you are talking about, I am not sure how that affects the "different databases on same database server" issue I just described. The mysql_pconnect method also has the force reconnect parameter, which suggests that it works the same way. However, that doesn't make sense to me, since the doc says that a persistent connection is identified by host, username and password only (not the active database).

Theme © iAndrew 2016 - Forum software by © MyBB