MySQL Bugs: #61866: Statement.clearWarnings does not clear the warnings

Bug #61866	Statement.clearWarnings does not clear the warnings
Submitted:	14 Jul 2011 14:24	Modified:	23 Nov 2011 19:04
Reporter:	Moshe Elisha	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.17	OS:	Linux
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	reset warnings

Description:
After executing a statement that produces a warning - the warning is available using Statement.getWarnings() but it is not cleared after invoking Statement.clearWarnings().

How to repeat:
public static void main(String[] args) throws SQLException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        connection = DriverManager.getConnection(args[0], args[1], args[2]);

        preparedStatement = connection.prepareStatement("CALL WARN_PROCEDURE()");
        preparedStatement.execute();

        logWarnings("FIRST", preparedStatement.getWarnings());
        preparedStatement.clearWarnings();
        logWarnings("AFTER CLEAR", preparedStatement.getWarnings());
    } finally {
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
}

private static void logWarnings(String logPrefix, SQLWarning warnings) {
    if (warnings != null) {
        for (Throwable throwable : warnings) {
            System.out.println(logPrefix + ": SQL warning " + throwable);
        }
    }
}

Output:

FIRST: SQL warning java.sql.SQLWarning: No data - zero rows fetched, selected, or processed
AFTER CLEAR: SQL warning java.sql.SQLWarning: No data - zero rows fetched, selected, or processed

The DB procedure:

CREATE PROCEDURE WARN_PROCEDURE() 
BEGIN
	DECLARE l_done INT;
	
	SELECT 1 
	INTO l_done
	FROM DUAL
	WHERE 1=2;

END

Hey,

I got emails that the bug was updated but I can't see any updates in the bug info page.
Are the updates internal or do you need any more info?

Moshe,

We had internal discussion regarding this and posted private comments in the report, thus you do not see it. Point is, if I can verify the problem as you described it, it will be fixed as it is wrong behavior. However, the fix is not trivial due to the fact that MySQL server does not clear the warnings chain once the warning is read.

Thank you for the report.

Verified as described.

test case for the testsuite

Attachment: bug61866.java (text/plain), 985 bytes.

Fixed for 5.1.18.

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Fixed in 5.1.18.

Thanks. 

The current GA release of the Connector/J is still 5.1.17.
When will the 5.1.18 be published?

Thanks again.

Moshe, I am currently in the process of setting up new build environment so I can not really say when will the 5.1.18 release happen.

No problem. Just, if you can, please post a comment here when the 5.1.18 will become available.

Just tested on 5.1.18 and it works great. Thanks!

The fix in 5.1.18 clears the warnings from the statement but new statements are created with the previous warnings:

public void testbug61866() throws Exception {
        try {
			this.stmt.execute("drop procedure if exists WARN_PROCEDURE");
			this.stmt.execute("CREATE PROCEDURE WARN_PROCEDURE() BEGIN	DECLARE l_done INT;	SELECT 1 	INTO l_done	FROM DUAL	WHERE 1=2; END");
			this.pstmt = this.conn.prepareStatement("CALL WARN_PROCEDURE()");
			this.pstmt.execute();
            System.out.println("Warnings: " + this.pstmt.getWarnings());
			this.pstmt.clearWarnings();
            this.conn.clearWarnings(); // I don't think this should be here but I just wanted to show that even this is not helpful
            System.out.println("pstmt Warning when not expected: " + this.pstmt.getWarnings());
            System.out.println("conn Warning when not expected: " + this.conn.getWarnings());
            System.out.println("stmnt Warning when not expected: " + this.conn.createStatement().getWarnings());

		} finally {
			this.stmt.execute("drop procedure if exists WARN_PROCEDURE");
	    	//closeMemberJDBCResources();
		}
	}

OUTPUT:

Warnings: java.sql.SQLWarning: No data - zero rows fetched, selected, or processed
pstmt Warning when not expected: null
conn Warning when not expected: null
stmnt Warning when not expected: java.sql.SQLWarning: No data - zero rows fetched, selected, or processed

Moshe,

Unfortunately, MySQL server doesn't allow us to reset warnings, so the fix was to track whether clearWarnings() has been called, set a flag if so, then make "getWarnings()" that does "SHOW WARNINGS" skip if this flag is set. This flag would then be -reset- on every execution thus is valid just for statement you called it for.
In this case, we have new statement created with "skip" flag NOT set (as should be for any new statement, otherwise all new warnings would be skipped) and an empty warning chain. This makes getWarnings() code enter the code where warning is fetched again from server who does not allow us to reset warnings and so on.

If you feel strongly about this, please check if "allow reset warnings" ("clear warnings" etc) feature request is filed against server and, if not, file one.

Hey,

I have requested a "reset / clear warnings" statement - http://bugs.mysql.com/63407.

In the meanwhile, maybe clearWarnings() can clear the warnings by executing a bulletproof query like "SELECT 1 FROM INFORMATION_SCHEMA.TABLES LIMIT 1" (according to the docs in order to clear the warnings the query must use a table and generate no warnings).

Moshe, I am not comfortable with that solution nor do I think it represents consistent way of doing things.