Mantis Bugtracker
Main | My View | View Issues | Change Log | Docs
  

Viewing Issue Simple Details [ Jump to Notes ] [ View Advanced ] [ Issue History ] [ Print ]
ID Category Severity Reproducibility Date Submitted Last Update
0004612 [Quercus] text always 06-10-11 10:48 06-16-11 08:40
Reporter thr View Status public  
Assigned To
Priority normal Resolution open  
Status new   Product Version
Summary 0004612: Quercus suffers from MySql SQL_CALC_FOUND_ROWS / FOUND_ROWS() Bug
Description Running wordpress on quercus I found a problem wordpress displaying wrong navigation next/previous page.

This resulted from a Bug in querying the total number of articles with the mysql functions SQL_CALC_FOUND_ROWS / FOUND_ROWS().
The Sun's jdbc mysql driver has a known, unresolved bug using these functions (see http://bugs.mysql.com/bug.php?id=15117) [^]

As this happens only if there are other mysql_... functions called between the query SQL_CALC_FOUND_ROWS and FOUND_ROWS() I fixed this with a workaround. I implemented my own 'mysql_query' function wich does a FOUND_ROWS call every time it sees a SQL_CALC_FOUND_ROWS an stores this value. If the next call is indeed a FOUND_ROWS() call, i returns the stored count instead of issuing the query.

This is just a dirty fix ...
PS: I'm reporting this as there might be others suffering from this Bug and trying to figure out whats going on ...
Additional Information     private static HashMap<Integer, String> lastSqlStatements = new HashMap<Integer, String>(
            10);
    private static HashMap<Integer, Value> lastCalcFoundRows = new HashMap<Integer, Value>(
            10);

    public static Value fixed_mysql_query(Env env, StringValue sql,
            @Optional Mysqli conn) {

        Integer hashConn = conn.hashCode();
        if (sql.toJavaString().contains("SQL_CALC_FOUND_ROWS")) {

            Value tempRowCount;
            releaseTempResultSet(hashConn);

            MysqlModule.mysql_query(env, sql, conn);

            tempRowCount = MysqlModule.mysql_query(env,
                    (StringValue) StringValue.create("SELECT FOUND_ROWS()"),
                    conn);

            lastCalcFoundRows.put(hashConn, tempRowCount);
            
        }else if (sql.toJavaString().toUpperCase().contains("FOUND_ROWS()")) {
            String lastSqlStatement = lastSqlStatements.get(hashConn);
            if (lastSqlStatement.toUpperCase().contains("SQL_CALC_FOUND_ROWS")) {

                return lastCalcFoundRows.get(hashConn);

            }
        } else {
            releaseTempResultSet(hashConn);
        }
        lastSqlStatements.put(hashConn, sql.toJavaString());

        return MysqlModule.mysql_query(env, sql, conn);
    }
Attached Files

- Relationships

- Notes
(0005319)
thr
06-16-11 08:40

 Note: my previous 'dirty fix' had some erros, here is the final code


    private static HashMap<Integer, String> lastSqlStatements = new HashMap<Integer, String>(
            10);
    private static HashMap<Integer, Value> lastCalcFoundRows = new HashMap<Integer, Value>(
            10);

    public static Value fixed_mysql_query(Env env, StringValue sql,
            @Optional Mysqli conn) {
        Value tempRowCount;
        if (conn == null) {
            conn = (Mysqli) env.getSpecialValue("caucho.mysql");
            if(conn == null) {
                MysqlModule.mysql_query(env, (StringValue) StringValue.create("SELECT 1"), conn);
                conn = (Mysqli) env.getSpecialValue("caucho.mysql");
            }
        }
        Integer hashConn = conn.hashCode();
        if (sql.toJavaString().toUpperCase().contains("SQL_CALC_FOUND_ROWS")) {
            
            releaseTempResultSet(hashConn);

            MysqlModule.mysql_query(env, sql, conn);

            tempRowCount = MysqlModule.mysql_query(env,
                    (StringValue) StringValue.create("SELECT FOUND_ROWS()"),
                    conn);

            lastCalcFoundRows.put(hashConn, tempRowCount);
            
            
        }else if (sql.toJavaString().toUpperCase().contains("FOUND_ROWS()")) {
            String lastSqlStatement = lastSqlStatements.get(hashConn);
            if (lastSqlStatement.toUpperCase().contains("SQL_CALC_FOUND_ROWS")) {

                tempRowCount = lastCalcFoundRows.get(hashConn);
                if(tempRowCount != null) {
                    return tempRowCount;
                }
            }
        } else {
            releaseTempResultSet(hashConn);
        }
        lastSqlStatements.put(hashConn, sql.toJavaString());
 
        return MysqlModule.mysql_query(env, sql, conn);
    }

    protected static void releaseTempResultSet(Integer hashConn) {
        Value tempRowCount = lastCalcFoundRows.get(hashConn);
        if (tempRowCount != null) {
            if (tempRowCount.toJavaObject() instanceof MysqliResult) {
 
                // release old set
                MysqlModule.mysql_free_result((MysqliResult)tempRowCount.toJavaObject());
            }
            lastCalcFoundRows.put(hashConn, null);
        }
    }
 

- Issue History
Date Modified Username Field Change
06-10-11 10:48 thr New Issue
06-11-11 13:04 kdecherf Issue Monitored: kdecherf
06-16-11 08:40 thr Note Added: 0005319

Mantis 1.0.0rc3[^]
Copyright © 2000 - 2005 Mantis Group
30 total queries executed.
26 unique queries executed.
Powered by Mantis Bugtracker