SQLSTATE[HY000]: General error – using php/pdo mysql stored procedures (sp)

I spent many hours last night trying to figure out why I was getting a fatal error: ‘SQLSTATE[HY000]: General error 0’ in my code and finally figured it out but first my environment. I’m using Zend Server CE running php 5.3.0, zend framework 1.9.0 and mysql 5.1.32. I should have tested this bug without the zend framework to make sure it’s not specific to zf (I don’t think it is) but I’m feeling lazy so I’ll let someone else try it out.

My code works as follows. I have a php class that calls a stored procedure which will take in an id, return a record set (if found) and will also return 2 out variables. While it was returning a record set everything was working perfectly fine. When I tried passing an invalid id, nothing was being returned and my code would keep giving me the ‘SQLSTATE[HY000]: General error 0’ (very helpful error indeed…).

The issue ended up being the way I had my stored procedure coded. I would first check to see if the id passed was valid, if so I would select the data else I would set my out vars to some value and do nothing else. For some reason, because I wasn’t returning a select, my code would blow up. In the mysql query browser, my stored procedure worked fine and my second select to get the out vars was working correctly. But php didn’t like it one bit. I tried forcing a select in my stored procedure in the invalid id section and then everything worked fine again. This sounds a bit confusing so here is the way I can replicate this.

Here is a sample stored procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test` $$
CREATE PROCEDURE `test`
(
  OUT counter INT
)
BEGIN
  SET counter = 5;
END $$

DELIMITER ;

In mysql query browser this works just fine if I call test(@out); and then select @out I get 5. But in my php code this blows up when I run:

$query = "CALL test(@out);";
$data = Zend_Registry::get('db')->fetchAll($query);

To fix this problem I just need to add a select statement to the stored procedure even if I don’t need that data. I add select ‘hi’; to the sp and then my code works:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test` $$
CREATE PROCEDURE `test`
(
  OUT counter INT
)
BEGIN
  select 'hi';
  SET counter = 5;
END $$

DELIMITER ;

The quick testing php code:

$query = "CALL test(@out);";
$data = Zend_Registry::get('db')->fetchAll($query);
$data = Zend_Registry::get('db')->fetchRow('select @out;');
print_r($data);

Instead of the general error now I get Array ( [@out] => 5 ) which is what was excepted. Hope this is helpful to you guys. Now I know I have to modify my stored procedures to always have a select even if it’s empty. What I don’t know if it’s specific to my environment or not which I may do more tests later to find out. If you try this and don’t get my same error please post so we have a better idea of what causing this issue or if you know why do share.

4 thoughts on “SQLSTATE[HY000]: General error – using php/pdo mysql stored procedures (sp)”

  1. The “SELECT hi” you ‘ve put in your procedure does not fix the problem you are talking about;

    You just need to do the following 2 queries:

    try {
    Zend_Registry::get(‘db’)->query(“CALL test(@out)”);
    $out = Zend_Registry::get(‘db’)->fetchOne(‘SELECT @out FROM dual’);
    print_r($out);
    } catch (Zend_Exception $ze) {
    print ‘Some error to your sqls: ‘ . $ze->getMessage();
    }

  2. I don’t understand the solution.
    I’ve the exact same problem : “SQLSTATE[HY000]: General error” and I’m not using Zend framework, just plain PDO calling a stored procedure.
    At the end of my procedure, I have a SELECT because I need some data back.

    Everything works well for now, but I need to use a variable like :
    IF _aaa IS NOT NULL THEN
    SET @bbb = ‘blah’;
    ELSE
    SET @bbb = ‘test’;
    END IF;
    and just adding that (not even using the variable later) will give me the “SQLSTATE[HY000]: General error” 🙁

  3. my solution to :
    ERROR: SQLSTATE[HY000]: General error: mode must be an integer

    is: not using this line:
    $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  4. As of 05/05/2016 (6 years later) this issue is not yet resolved in mariaDB 10.1.9

    I use pure PDO and I searched for 6 hours before findind this post. Error message is “SQLSTATE[HY000]: General error” (they removed the 0!).

Leave a Reply

Your email address will not be published. Required fields are marked *