duangangpin078794
duangangpin078794
2013-06-04 19:24

使用PHP运行在SQL Server中使用存储过程和事务的查询会导致神秘错误

已采纳

This is just the strangest thing and I'm not sure why or what is happening. Everything runs fine in SQL Server but results in errors when ran in PHP.

In PHP, I build a dynamic statement that contains one to many queries or statements. It looks like this:

begin try 

    begin transaction  

    -- statement 1
    UPDATE table_name SET status = 1 WHERE things='stuff';  

    -- dynamic: to run after inserts
    exec [dbo].[SP_TEST_2]; 
    exec [dbo].[SP_TEST_3]; 
    exec [dbo].[SP_TEST_9]; 
    exec [dbo].[SP_TEST_14];

    commit 

    select 'successful' as for_php_success_message 

end try 

begin catch  

      rollback

      select   error_number()     as for_php_error_number
              ,error_severity()   as for_php_error_severity
              ,error_state()      as for_php_error_state
              ,error_procedure()  as for_php_error_procedure
              ,error_line()       as for_php_error_line
              ,error_message()    as for_php_error_message; 
end catch

This morning, someone came to me because the front page to all of this was throwing errors at them. Warning: mssql_query() [function.mssql-query]: message: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. This code has been untouched for several months and hasn't had any problems. The code in the stored procedures probably has changed since then.

I copied the code over to SQL Server Management Studio to test and everything ran fine from a direct copy and paste. No errors, no warnings, just successful.

Next, I looked up transactions online and changed rollback to if @@trancount>0 rollback and this did fix the transaction error; however - I had a new error from PHP:

Array
(
    [for_php_error_number] => 50000
    [for_php_error_severity] => 16
    [for_php_error_state] => 1
    [for_php_error_procedure] => SP_TEST_Record
    [for_php_error_line] => 247
    [for_php_error_message] => spTEST_Record: 515: Cannot insert the value NULL into column 'TEST_DATA', table 'tempdb.dbo.#IDs________________________________________________________________________________________________________________00000001D27E'; column does not allow nulls. INS
)

(still, a reminder: no errors are returned if I run the exact code in SQL Server Mgmt Studio)

The error involves a SP that is called at the end of each of the SP_TEST_# procedures listed in the dynamic query that PHP built. I cannot copy the SP code over to here because this is work stuff and I didn't write them so I'd really prefer to not have to paraphrase them, either, because they are huge and not formatted well. I will however, show the top of SP_TEST_RECORD where the error occurs (which isn't on line 247):

ALTER PROCEDURE [dbo].[SP_TEST_Record] (
    @Test_ID real=0, @debug int=1, @create_entry int=0, @autoclose bit=0, @autodelete bit=0
)
AS
BEGIN 
    SET NOCOUNT ON;  
declare @vtest_id real; set @vtest_id=@Test_ID  
declare @vdebug int; set @vdebug=@debug     
declare @vcreate_entry bit; set @vcreate_entry=@create_entry  
declare @vautoclose bit; set @vautoclose=@autoclose 
declare @vautodelete bit; set @vautodelete=@autodelete  

declare @test_date datetime; set @test_date=getutcdate()
declare @ct int;

begin try
begin tran

if object_id('tempdb..#IDs') is not null drop table #IDs
CREATE TABLE #IDs (TYC_ID int not null, TYC_TYPE_ID int not null, TYC_ENV_ID int not null, TEST_DATA nvarchar(2000) null) ON [PRIMARY]
ALTER TABLE #IDs ADD PRIMARY KEY NONCLUSTERED (TYC_ID, TYC_TYPE_ID, TYC_ENV_ID)

insert into #IDs(TYC_ID, TYC_TYPE_ID, TYC_ENV_ID, TEST_DATA) 
select TYC_ID, TYC_TYPE_ID, TYC_ENV_ID, TEST_DATA from SR_TESTING where TEST_ID=@vTest_ID

So - one thing I know... some how, my PHP transaction was being ended by something in one of the stored procedures that was called by the dynamic statement and that is cause of the transaction issue. There are transactions inside of those stored procedures.

  • But why don't I see the null insert error in SQL Server when I run the same exact code? If there's a null insert, there's a null insert... so why would it make a difference if it was called from PHP or not?

  • And less importantly, how was my transaction being ended by one of the stored procedures in the beginning?

  • Why was thetransaction ended by the SP's when the code was ran in PHP but not when it was ran in SQL Server?

  • Is there some execution-concurrency-order of operations-transaction hierarchy-something going on?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dongqi8114 dongqi8114 8年前

    I didn't want to straight delete this question because it was, at the very least, a great learning experience for me. However, it was a very localized question. I figured out the answer a while ago so I can't remember the specifics...

    It was definitely caused because of something happening in one of the stored procedures that were being called down the line. I was very surprised to find out that the error bubbled up that way, especially with the PHP. I guess error scope kind of played a part in it... the PHP took the first error it saw but running the code in SQL server allowed it to fail more gracefully (so I didn't see the error there). Dunno. Troubleshooting... when in doubt - go deeper, lol...

    点赞 评论 复制链接分享