Possible issue with multiple databases and mssql

OK, relatively complex one here I suspect, thanks in advance if anyone can help.

First of all some background.

I have written what amounts to most of an ERP package for my company, based on code igniter.  I should take this opportunity to thank the developers for a great framework, which I've been working with for about 4 years now!

This app does most of the work for the company, which is an online retailer - order creation and processing, inventory and stock control, purchasing, the returns process (customer and supplier), warehouse processes, and much more.  

It is built around and coexists with other software, notably our retail websites, some older back-end software (classic ASP, eww) and others.

I'm looking to do a bit of a refactor/rewrite job, and as part of this I would like to upgrade from CI 2.X to 3.X, which is where I hit some snags, hence this post.

Basically the app is hosted on a linux VM, and communicates with our database server, which is an MS-SQL server.  So I'm using the mssql DB driver, freetds and so on.

The problem I have is with connecting to multiple databases within the application.  For historical reasons, our data is divided into a number of databases, all running on a single database server. Some models use one database, some models use another database, some models use multiple databases.  As an example, let's say customers and orders are in one database, and returns and sales statistics are in another.

So a model might load a couple of databases in the __construct() method, which then get used by various methods in the model.

Code:

       $this->foo = $this->load->database('foo', TRUE);
       $this->bar = $this->load->database('bar', TRUE)

Databases are obviously defined in config/database.php.  This all works fine (in the live 2.x based version) but I run into problems under 3.0.6.

Specifically what I'm seeing is that all queries being made, regardless of whether they use $this->foo or $this->bar, are actually being run against $this->bar (or more specifically, whatever model I have loaded last).

I did some digging and debugging, and I've found - by for example using error_log(var_export($this->bar, true)) - that the database object has the correct database name, however the underlying driver still appears to be making queries against whichever db was loaded last.

I then did some further digging, and what I found was that both databases loaded above seem to get the same connection ID, and are associated with the same specific PHP resource ID. 

I tested with persistent vs non-persistent options for the database, etc, that made no difference to the behaviour.

So, I have put in place a fix which resolves the issue, and in the process I think I have some idea where the issue is, but I'm hoping someone here with a little more knowledge of the 'guts' of the DB classes can tell me I was doing something wrong and there is a more elegant solution.

The fix was to give each database, as defined in database.php, a dummy hostname, so that each connection is against a different 'hostname' (actually all the same database server, but CI doesn't know that). So for example the database foo in database.php is defined with a hostname like dbserver-foo, and database bar is defined in dbserver-bar.  These have corresponding dummy entries in freetds.conf, and it's now all working - queries are hitting the correct databases, and I can see that they are all getting seperate connection IDs / PHP resource handles under the hood.

So it seems that something, I'm not sure if it's the MSSQL driver or the CI DB model itself, is making the assumption that there should only be one connection ID / PHP resource per "physical" server.  Possibly this works fine with MySQL?  I wouldn't know, unfortunately I have to work with our existing MS-SQL server as migrating everything to MySQL would be a big job.  But with MS-SQL it seems to have the result described above: loading multiple databases from a single DB in 3.0.6 with the mssql driver seems to result in all of the connections actually being a single connection, specifically the last connection loaded.

Thanks in advance to anyone who's taken the time to read this issue, especially if you have some insights which might lead to a less hacky fix.

And thanks again to everyone involved with developing CI, by far my favourite PHP MVC framework .

PHP does that with a lot of its database extensions, and it can be a PITA, but there's also logic behind it - if you're using the same username and password to connect to the same host, it makes little sense to create new connections again and again; just re-use the already established one.

(it's not just about the "physical" host)

If a second call is made to mssql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned.

Source: https://secure.php.net/mssql_connect

mssql_connect() has a parameter that forces a new connection to be established, but CI doesn't use it ...

Thanks for the update, that (sort of) makes sense. Wonder why I didn't run into the problem in 2.X. Must be some change in the ms-sql driver I guess. That or I hacked my way around it some other way a few years ago, and then forgot, entirely possible .