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

[22 Oct 2008 15:10] Serdar S. Kacar
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.
[22 Oct 2008 15:29] Valeriy Kravchuk
Thank you for a reasonable feature request.
[22 Oct 2008 20:04] Marc ALFF
This is related to:
WL#2110 (GET DIAGNOSTICS)
[23 Oct 2008 10:57] Serdar S. Kacar
Note that SHOW WARNINGS is also not CURSOR friendly.
So a workaround using CURSORs is not possible.
[23 Oct 2008 11:29] Serdar S. Kacar
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.
[29 Oct 2008 20:37] Peter Gulutzan
This is related to
http://forge.mysql.com/worklog/task.php?id=2111
GET DIAGNOSTICS

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