"SQLSTATE[HY000]: General error" - Multiple SQL queries in one statement

Question

I have a query in my php script that is handled by PDO. To be exact it's 2 queries in 1 statement. The query itself runs fine in SQL client (I'm using HeidiSQL). PHP however gives me exactly this error: "SQLSTATE[HY000]: General error" with nothing else. No error number or message.

Is there a way to debug the query somehow? I don't think there's an error in query itself, so I don't know what's wrong. Nothing shows in mysql error log. I've enabled mysql general log, but it only logs query itself without showing an error.

My stack: XAMPP 1.8, Apache 2.4.3, PHP 5.4.7, MySQL 5.5.27

This is my query (it's rather long):

    /*First query - generating temp table with overdue jobs*/

    CREATE TEMPORARY TABLE temp AS (
        SELECT  j.NetworkID,
            @clientID := j.ClientID,
            j.BranchID,
            j.ServiceProviderID,
            (   
                (DATEDIFF(CURDATE(), j.DateBooked)) 
                - 
                IF(
                (@unit :=   (
                    SELECT      uctype.UnitTurnaroundTime
                    FROM        job
                    LEFT JOIN   product ON job.ProductID = product.ProductID
                    LEFT JOIN   unit_type AS utype ON product.UnitTypeID = utype.UnitTypeID
                    LEFT JOIN   unit_client_type AS uctype 
                            ON utype.UnitTypeID = uctype.UnitTypeID 
                            AND uctype.ClientID = @clientID
                    WHERE       job.JobID = j.JobID
                    )
                ) IS NOT NULL,      /*statement*/
                @unit,          /*TRUE - Client Unit Type has turnaround time assigned in the db*/  
                IF(         /*FALSE - Now checking if Client Default Turnaround Time is set*/
                    (@clnt := (
                        SELECT  DefaultTurnaroundTime AS dtt
                        FROM    client
                        WHERE   client.ClientID = @clientID
                    )
                    ) IS NOT NULL,  /*statement*/
                    @clnt,      /*TRUE - Client Default Turaround time is set*/
                    (           /*FALSE - falling back to general default*/
                    SELECT  gen.Default
                    FROM    general_default AS gen
                    WHERE   gen.GeneralDefaultID = 1
                    )
                )
                )
            ) AS overdue

        FROM    job AS j

        HAVING  overdue > 0
    );


    /*Second query - filtering out overdue jobs with specific time range*/

    SELECT  COUNT(*) AS number
    FROM    temp
    WHERE   overdue >= :from AND overdue <= :to AND overdue != 0

UPDATE: The problem seems to be caused by PDO refusing to run several queries in one statement. I've inserted this before executing query:

$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

So now "General error" is gone, but I'm getting

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

just after the first query, meaning that PDO just runs one query and refuses to continue...

this may help http://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — Lawrence Barsanti, Nov 25 '12 at 14:36
http://stackoverflow.com/questions/10199571/why-sqlstatehy000-general-error did u solve it yet — Drew, Nov 25 '12 at 15:06

score 1 · Accepted Answer · answered Nov 25 '12 at 20:54

1

I found a way to run several queries in one go after all. It appears MySQLi->multi_query allows this. Deeply disappointed in PDO yet again.

answered Nov 25 '12 at 20:54

Caballero

11,546
22
103
163

This is a feature, not a bug. – Waleed Khan Nov 25 '12 at 21:00
I have no user input whatsoever in these queries, so security (SQL injection) is not an issue, thus I think PDO should have multi queries as a feature. – Caballero Nov 26 '12 at 07:06
To cater to your specific needs? – Waleed Khan Nov 26 '12 at 13:36
This is not only my specific needs, existence of MySQLi->multi_query proves it. I did extensive searching on the issue and there are a lot of people looking for this functionality. – Caballero Nov 26 '12 at 18:02

score 0 · Answer 2 · answered Nov 25 '12 at 19:44

0

There is no need to create a temporary table. Combining your two statements into one should fix the problem.

SELECT  COUNT(*) AS number
FROM    (giant select statement) temp
WHERE   overdue >= :from AND overdue <= :to AND overdue != 0

If you really want the temporary table, use two different statements. One to create the table and one to query it. An even better option would be to use a view. This way that monster SQL statement lives in the database instead of your php code.

answered Nov 25 '12 at 19:44

Lawrence Barsanti

31,929
10
46
68

Thanks, I new that it's possible to merge everything into one query in this occasion, however I need this functionality non the less, because this was just initial probing, shall we say and I have other stuff, more complex that definitely needs this functionality. – Caballero Nov 25 '12 at 20:55

"SQLSTATE[HY000]: General error" - Multiple SQL queries in one statement

2 Answers2