SHOW WARNINGS following CREATE TABLE

tonymarston's profile image tonymarston posted 4 years ago in Running SQL scripts Permalink

I am using version 11.0.0.6046 with MySQL 8.0.20.

When I run a CREATE table script which produces warnings I see that the log window includes the statement "SHOW WARNINGS LIMIT 5", but the warnings to not appear anywhere. In order to see them I have to run this statement myself in the query window. These should really appear automatically in the "results" pane.

immobilia's profile image immobilia posted 4 years ago Permalink

Hy, I'm in same case with HeidiSQL 11.0 (11.0.0.5919 and 11.0.0.6103) and MySQL 5.6.19

Two use case similar, with differents result !

On statements : DROP TABLE, DROP TABLE, CREATE TABLE

Use case N°1 : not display warnings

With command line :

mysql> DROP TABLE IF EXISTS zzz_my_table;
Query OK, 0 rows affected (0.05 sec)

mysql> DROP TABLE IF EXISTS zzz_my_table;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> SHOW WARNINGS;
+-------+------+--------------------------------------+
| Level | Code | Message                              |
+-------+------+--------------------------------------+
| Note  | 1051 | Unknown table 'y_my_db.zzz_my_table' |
+-------+------+--------------------------------------+
1 row in set (0.04 sec)

mysql> CREATE TABLE zzz_my_table
    -> SELECT 'not_showing_warnings' AS virtual_colomn
    -> ;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

With HeidiSQL :

DROP TABLE IF EXISTS zzz_my_table;
DROP TABLE IF EXISTS zzz_my_table;
CREATE TABLE zzz_my_table
SELECT 'not_showing_warnings' AS virtual_colomn
;
/* Affected rows: 1  Found rows: 0  Warnings: 1  Duration for 3 queries: 0,141 sec. */
SHOW WARNINGS LIMIT 5;

The popup - with empty message about (last) "SHOW WARNING" :

[Window Title]
DEV - MySQL 5: Warning

[Main Instruction]
Your query produced 1 warnings.

[Content]
Warnings from last query:

[X] Keep showing this dialog.  [OK]

Use case N°2 : display last warning

With command line :

mysql> DROP TABLE IF EXISTS zzz_my_table1;
Query OK, 0 rows affected (0.05 sec)

mysql> DROP TABLE IF EXISTS zzz_my_table2;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Note  | 1051 | Unknown table 'y_my_db.zzz_my_table2' |
+-------+------+---------------------------------------+
1 row in set (0.04 sec)

mysql> DROP TABLE IF EXISTS zzz_my_table1;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Note  | 1051 | Unknown table 'y_my_db.zzz_my_table1' |
+-------+------+---------------------------------------+
1 row in set (0.04 sec)

mysql> DROP TABLE IF EXISTS zzz_my_table2;
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> SHOW WARNINGS;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Note  | 1051 | Unknown table 'y_my_db.zzz_my_table2' |
+-------+------+---------------------------------------+
1 row in set (0.04 sec)

mysql> CREATE TABLE zzz_my_table1
    -> SELECT USER();
Query OK, 1 row affected, 1 warning (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                  |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

The popup - with last message about (last) "SHOW WARNING" :

[Window Title]
DEV - MySQL 5: Warning

[Main Instruction]
Your query produced 4 warnings.![image description](null)

[Content]
Warnings from last query:
Note: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.

[X] Keep showing this dialog.  [OK]

HeidiSQL issue : open each warnings as new result tab

Instead display only (and something missing) last "SHOW WARNINGS" message,

can't HeidiSQL open automatically a new result tab "SHOW WARNINGS" for each query resulting warning(s) ?

2 attachment(s):
  • 2020-10-14-23_37_50-DEV-MySQL-5-HeidiSQL-11.0.0.6103
  • 2020-10-14-23_43_15-DEV-MySQL-5-HeidiSQL-11.0.0.6103

Please login to leave a reply, or register at first.