MySQL Bugs: #40255: INFORMATION_SCHEMA.WARNINGS - a CREATE/INSERT .. SELECT friendly table

Bug #40255	INFORMATION_SCHEMA.WARNINGS - a CREATE/INSERT .. SELECT friendly table
Submitted:	22 Oct 2008 15:10	Modified:	23 Oct 2008 11:29
Reporter:	Serdar S. Kacar	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	CREATE ... SELECT, cursor, INSERT ... SELECT, show warnings

Description:
Especially while executing stored procedures, we handle some conditions but the enlightening information obtainable by SHOW WARNINGS is wasted.

The problem is, SHOW WARNINGS is not INSERT ... SELECT friendly.

Hence, we can only output something like
"An sqlexception occured while working on this subset."
"An sqlexception occured while working on that subset."
"Operation partially completed."

This severely limits the information gatherable from problem reports.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.1.28-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TEMPORARY TABLE  `test`.`tmpwrngs` (
    ->   `Code` int(11) NOT NULL,
    ->   `Level` enum('Not specified','Note','Warning','Error') NOT NULL DEFAULT
 'Not specified',
    ->   `Message` varchar(255) NOT NULL DEFAULT ''
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT `test`.`tmpwrngs` SHOW WARNINGS;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'SHOW
WARNINGS' at line 1
mysql>

Suggested fix:
As like most of the SHOW statements, provide INFORMATION_SCHEMA counterpart part for SHOW WARNINGS. So we can pile them up for some statement groups.

Thank you for a reasonable feature request.

This is related to:
WL#2110 (GET DIAGNOSTICS)

Note that SHOW WARNINGS is also not CURSOR friendly.
So a workaround using CURSORs is not possible.

Many people need similar functionality and have similar concerns :
http://bugs.mysql.com/bug.php?id=11660
Alhough I support having a session SQLSTATE variables like @@SQLSTATE and @@SQLSTATE_MESSAGE, they are limited to the single row of id and message. They have their own use. It is close but a separate topic.

On the otherhand, SHOW WARNINGS is much more deep as a single statement can result in "multiple" notes/warnings/errors. So its INFORMATION_SCHEMA counterpart could be much more useful. Besides it is consistent with MySQL SHOW/INFORMATION_SCHEMA tradition and (in my opinion) cleaner/easier to implement.

This is related to
http://forge.mysql.com/worklog/task.php?id=2111
GET DIAGNOSTICS

And Bug#10797. And Bug#11660. And Bug#16371.