php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #24522 MSSQL: "Changed database context to" error when running query
Submitted: 2003-07-07 12:01 UTC Modified: 2003-07-13 10:48 UTC
From: cdcr440 at hotmail dot com Assigned:
Status: Not a bug Package: MSSQL related
PHP Version: 4.3.1 OS: WinNT
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: cdcr440 at hotmail dot com
New email:
PHP Version: OS:

 

 [2003-07-07 12:01 UTC] cdcr440 at hotmail dot com
Description:
------------
When selecting a database with mssql_select_db(), the error "Changed database context to 'XXX'". DB server is MSSQL 2000.

The problem seems to be the result of running a query and the next time the script tries to select the database, it fails with the above message, which I wouldn't expect from mssql_query(). The failing query will first run for a long time and fail with the above message. All subsequent attempts to select the database will fail with the same message. But running other queries in other scripts will be successful.

The behaviour of the bug is hard to predict and I haven't been able to design a query that will surely generate the problem. Also the queries that fail with PHP work fine in SQL Analyzer/Enterprise manager (connected as the same user), so it's not a query/SQL problem.

The user I use to connect to the database has the target database as default database therefore I wouldn't expect a change of context to be a problem. Also the user can run exactly the same query with a very slightly different value in one of the parameters and succeed(e.g. "hup" in the query for VARCHAR will fail but "hu" for the same column will succeed), so it's not an access right problem. However, the workaround I've found is to use the sa user to connect to the server, which would tend to show that it's a user problem.

I thought for a while that it was a problem with number of open connections to the database, but I reduced them and the behaviour of the bug isn't consistent with that as it can occur when very few concurrent connections are open.

The bug also has been present in the last few versions of PHP, not only in 4.3.1.

I whish I could provide more useful information like a core dump, but the failing code is very simple, I haven't figured out what the problem is, and PHP doesn't crash.

Disclaimer: I searched for this bug in the bugs database, and found a similar Sybase bug with the reporter didn't provide feedback. I didn't find anything else. My sincere apologies if it has been reported before and I missed it.



Reproduce code:
---------------
$this->dbID = MSSQL_CONNECT($this->theServer, $this->theUser, $this->thePassword); // theServer etc are object variables
mssql_select_db($this->theDB, $this->dbID);

Running the query afterward is simply:
$this->theResults = mssql_query($this->theQuery, $this->dbID);  // where $this->theQuery is the query string


The PHP log will simply contain:
[07-Jul-2003 17:40:43] PHP Warning:  mssql_select_db() [<a href='http://www.php.net/function.mssql-select-db'>function.mssql-select-db</a>]: Unable to select database:  XXX in myDB.class on line 57
[07-Jul-2003 17:40:43] PHP Warning:  mssql_query() [<a href='http://www.php.net/function.mssql-query'>function.mssql-query</a>]: Query failed in myDB.class on line 261


My php.ini options for MSSQL are as follows:
[MSSQL]
; Allow or prevent persistent links.
mssql.allow_persistent = On

; Maximum number of persistent links.  -1 means no limit.
mssql.max_persistent = -1

; Maximum number of links (persistent+non persistent).  -1 means no limit.
mssql.max_links = -1

; Minimum error severity to display.
mssql.min_error_severity = 10

; Minimum message severity to display.
mssql.min_message_severity = 10

; Compatability mode with old versions of PHP 3.0.
mssql.compatability_mode = Off

; Valid range 0 - 2147483647.  Default = 4096.
mssql.textlimit = 10000000

; Valid range 0 - 2147483647.  Default = 4096.
mssql.textsize = 10000000

; Limits the number of records in each batch.  0 = all records in one batch.
;mssql.batchsize = 15



Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2003-07-07 16:14 UTC] fmk@php.net
Please provide a full script that reproduces the problem. The log file indicates that the mssql_select_db() failed. This could be caused by insufficient access or a bad database name. If the database name includes white space it should be included in [].

"Changed database context to 'XXX'". is not an error but a message. You should only see this message if mssql.min_message_severity = 0.

mssql.min_message_severity can be specified in php.ini or at runtime with the ini_set() command.
 [2003-07-09 07:52 UTC] cdcr440 at hotmail dot com
Thanks for looking into this.

The access rights should be sufficient because I tried several users including the sa user and the problem occured for all.

The database name doesn't include white spaces.

mssql.min_message_severity = 10 in my php.ini as I had attached but still see the message you say I should only see with mssql.min_message_severity = 0, maybe that's related to the problem. Could it be the result of the MSSQL module getting confused?

Even though "Changed database context to 'XXX'" isn't an error message, it appears when the query I try to run fails and the subsequent database selections fail with the same error.

Here is the shortest example that produces the error, sorry for the biggish query, this one is one that I know generates an error. It connects with the sa user that can access everything without restrictions:
<?

$config["sqlserver"]="192.168.101.3";
$config["sqluser"]="sa";
$config["sqlpassword"]="x";
$config["sqldatabase"]="XXX";

$theQuery="select top 15 ni.* 	from NewsItem as ni, NewsItemStatus as nis, NewsStatus as ns
					where nis.NewsItemID=ni.ID 
					and ns.ID=nis.NewsStatusID 
					and lower(ns.Status) = lower('live')  and (ni.ID in (select ni1.ID from NewsItem ni1 JOIN NewsItemNewsTagTable as nintt1 ON nintt1.NewsItemID=ni1.ID where  ni1.[Date] >= '09-Jun-2003' and  nintt1.[Date] >= '09-Jun-2003' and  ni1.[Date] <= '09-Jul-2003' and  nintt1.[Date] <= '09-Jul-2003' and  ( (lower(cast (Text as varchar(8000)))  like '%chuan%' )or (lower(cast (BassoeComment as varchar(8000)))  like '%chuan%' )or (lower(cast (FirstParagraph as varchar(8000)))  like '%chuan%' )or (lower(cast (Title as varchar(8000)))  like '%chuan%' )or (lower(cast (nintt1.TagsCache as varchar(8000)))  like '%chuan%' ))) and ni.ID in (select ni1.ID from NewsItem ni1 JOIN NewsItemNewsTagTable as nintt1 ON nintt1.NewsItemID=ni1.ID where  ni1.[Date] >= '09-Jun-2003' and  nintt1.[Date] >= '09-Jun-2003' and  ni1.[Date] <= '09-Jul-2003' and  nintt1.[Date] <= '09-Jul-2003' and  ( (lower(cast (Text as varchar(8000)))  like '%hup%' )or (lower(cast (BassoeComment as varchar(8000)))  like '%hup%' )or (lower(cast (FirstParagraph as varchar(8000)))  like '%hup%' )or (lower(cast (Title as varchar(8000)))  like '%hup%' )or (lower(cast (nintt1.TagsCache as varchar(8000)))  like '%hup%' ))))  and ( ni.NewsTypeID=4 )  and ni.[Date] >= '09-Jun-2003' and ni.[Date] <= '09-Jul-2003' order by ni.[Date] desc, ni.[Timestamp] desc ";

print("Connecting to DB server... ");
$dbID = MSSQL_CONNECT($config["sqlserver"], $config["sqluser"], $config["sqlpassword"]);
if (!$dbID) {
	print("Error connecting to DB server.<br>\n");
} else {
	print("done.<br>\n");
}
print("Selecting database... ");
if (!mssql_select_db($config["sqldatabase"], $dbID)) {
	print("Error selecting database.<br>\n");
} else {
	print("done.<br>\n");
}
print("Running query... ");
$theResults = mssql_query($theQuery, $dbID); 
if (!$theResults) {
	print("Unable to run query:\n");
	print(mssql_get_last_message()."<br>\n");
} else {
	print("done.<br>\n");
}

?>

The output is as follows:
Connecting to DB server... done.
Selecting database... done.
Running query... Unable to run query: Changed database context to 'XXX'.

However, the query runs fine query analyzer with the same user (sa!), although slowly, so we can rule out a purely SQL problem.

However, if the query is reduced a bit, it doesn't fail:
select top 15 ni.* 	from NewsItem as ni, NewsItemStatus as nis, NewsStatus as ns
					where nis.NewsItemID=ni.ID 
					and ns.ID=nis.NewsStatusID 
					and lower(ns.Status) = lower('live')  and (ni.ID in (select ni1.ID from NewsItem ni1 JOIN NewsItemNewsTagTable as nintt1 ON nintt1.NewsItemID=ni1.ID where  ni1.[Date] >= '09-Jun-2003' and  nintt1.[Date] >= '09-Jun-2003' and  ni1.[Date] <= '09-Jul-2003' and  nintt1.[Date] <= '09-Jul-2003' and  ( (lower(cast (Text as varchar(8000)))  like '%chuan%' )or (lower(cast (BassoeComment as varchar(8000)))  like '%chuan%' )or (lower(cast (FirstParagraph as varchar(8000)))  like '%chuan%' )or (lower(cast (Title as varchar(8000)))  like '%chuan%' )or (lower(cast (nintt1.TagsCache as varchar(8000)))  like '%chuan%' )))) and ( ni.NewsTypeID=4 )  and ni.[Date] >= '09-Jun-2003' and ni.[Date] <= '09-Jul-2003' order by ni.[Date] desc, ni.[Timestamp] desc

So although it looks like a query problem, I remind you that both queries run fine through both query analyzer and the enterprise manager. Could it be a kind of time out problem since the second query (that succeeds) runs faster? I don't see timeout settings in the MSSQL configuration in php.ini. The script itself doesn't timeout and continues executing after the error.

The log I attached the other day was also showing that subsequent attempts to select the database to run another query would fail.

Does this help?
 [2003-07-09 17:19 UTC] fmk@php.net
This sounds like a timeout porblem.

You can use two php.ini settings to control the timeouts.

mssql.connect_timeout = 5
mssql.timeout = 60

These are default values in seconds. try to increase the second timeout value. (I'll make sure thes values makes it into the distributed versions of php.ini).
 [2003-07-10 04:03 UTC] cdcr440 at hotmail dot com
Thank you very much for that. Unfortunately, I can't check that this fixes the problem because it's gone today and I didn't manage to find a query that fails. As I said it's unpredictable and appears/disappears regularly. I've increased the timeout and I'll see if the problem comes back again.

Sorry to have wasted your time, I think you can temporarily close this bug report.
 [2003-07-13 10:48 UTC] sniper@php.net
Temporarily closing. :)

 [2011-07-10 18:34 UTC] cjrogala at gmail dot com
You may want to reopen this issue.  I am inserting data into MS SQL Server 2008r2 and using PHP 5.2; and the same error occurs.  The positive is that the error did not prevent the data from being inserted into the database, but it did display the same error message.  I did not attempt to change the timeout since this is a much later version.  I also noticed this issue in a few different location on the web.  I am logging into the database as the sa and like I said, the data is making into the database; but the error still appears when validating the result of the query.  Unlike the previous post, I will not provide a credentials, but I know it's not a connection error since the data is getting into the database.  Here is my code:

$tsql = "USE littleliam
    INSERT INTO [littleliam].[dbo].[tbl_blogPosts]
           ([postText]
           ,[created]
           ,[createdBy]
           ,[approved])
     VALUES ('" .$postText ."', GETDATE(), " .$author .", " .$approvalStatus .")";

//Used to validate the query by running it in SQL Server Management studios
echo $tsql;
//Prepare and execute the statement.

mssql_select_db('littleliam');

$insertReview = mssql_query($tsql, $msServerLink);

if (!$res) {
    print("SQL statement failed with error:\n");
    print("   ".mssql_get_last_message()."\n");
} else {
    print("One data row inserted.\n");
}  

mssql_close($msServerLink); 


My connection script is:

$msServerLink = mssql_connect($db_server, $db_user, $db_pass);  

The only thing I could think of is selecting the database in the connect function.  Can a database be selected in the mssql_connect function?
 [2011-07-10 18:42 UTC] cjrogala at gmail dot com
I am an idiot.  Please disregard my post.
 [2011-09-10 05:22 UTC] rayphilip3 at gmail dot com
Hi Cjrogala, 

Could you tell how you had solved this issue? My application also has this strange problem 

Thanks
Ray
 [2012-06-30 00:58 UTC] paul at lumient dot com dot au
Just had this issue too. The problem was I was not passing the connection object 
to mssql_query(...). As a result the query failed to return anything and when I 
detected this I showed an error. When I looked at the mysql_error(...) return 
value it displayed this message (even though it was just a low-level warning) 
because it was the last message that had been logged. When I passed in a valid 
connection object to mssql_query it all went away!
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Jun 25 00:01:31 2024 UTC