Advice on moving to 2 mysql_connect instances in old code

[epinc]

We have a 10 year old system that has 100's of php files that all connect to a central database using a single shared php include file that does a mysql_connect before any other code executes

 

include_once("db_connect.php");

 

mysql_query(...);

 

We never used the returned connection id (link identifier) from the mysql_connect function call as it was always implied in every subsequent mysql_query statement etc.

 

Now we've moved some of our bigger tables to a separate server, so we have to update small areas of some files that need to connect to this other system and send the old existing query to the new server instead of to the main server and process the results

 

So, going back through hundreds of files and converting mysql_query($string) to mysql_query($string, $connection1) is not practical

 

Possible solution one: anytime I need to use the new server I do this

------------------------------------------------------------------------------

 

mysql_connect(NEW_SERVER_IP,"...",""");

mysql_query($string);

...

// get back to normal

mysql_connect(OLD_SERVER_IP,"...",""); 

 

 

Possible solution two: modify the master include file

------------------------------------------------------------------------------

 

//add a new line ABOVE the existing mysql_connect statement...

$new_server=mysql_connect(NEW_SERVER_IP,"...",""");

mysql_connect(OLD_SERVER_IP,"...","");

 

and then anytime I need to use the new database I have to go back and modify all of the mysql_query,mysql_affected_rows, etc to reference the $new_server link identifier

 

solution one seems much simpler, but all of the mysql_connect reconnections I assume will be extremely inefficient

 

solution two seems cleaner but if I miss one mysql_ statement that needs the new connection object as a reference, the code will completely break or worse yet it will produce unexpected results

 

Example: If I miss converting a mysql_errno() function that really needs to now be mysql_errno($new_server) it would cause major issues.

 

So, I'm wondering if others have faced this dilema of migrating single mysql_connect code to multi mysql_connect mode and if there is a better way of going about implementing it.

 

Thanks in advance for any help

 

Steve

[mac_gyver]

how do you know which queries are to use the second database connection and if possible can you add some unique table prefix to indicate this?

 

one way would be to write a function/class to abstract the database access, that a simple search/replace can change all the msyql_query() statements to use, that would run the query using the correct connection based on something unique in the query statement. are any of your existing mysql_query() statements inside of your own user functions/classes?

 

edit: answer you probably don't want to hear -

is a better way of going about implementing it.

yes, if you have a design that has hundreds of files, where database queries exist in a majority of those files, it might be time to rewrite the code to use one common set of code that dynamically produces the pages of the site.

[ginerjm]

You may be trying to avoid some work here in order to make this necessary change, but you eventually will have to do a whole lot of work to Stop using the deprecated MySQL_* functions and move to mysqlI or pdo.

[mikosiko]

Assuming that the tables that you moved to the other server doesn't exists anymore in the main server It may or may not be a solution for you, but you can test it ... MYSQL provide the FEDERATED  Storage Engine that could help you to solve your issue, be aware of the caveats and restrictions well explained under "Limitations".

 

 

 

http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html