Bug #104955 The error_count variable doesn't work with MySQL Shell
Submitted: 16 Sep 7:02 Modified: 16 Sep 11:57
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:Shell General / Core Client Severity:S3 (Non-critical)
Version:8.0.26 OS:Any
Assigned to: CPU Architecture:Any

[16 Sep 7:02] Daniël van Eeden
Description:
Using the @@error_count session variable doesn't work with MySQL Shell unless it is used on the same line as the statement generating errors.

This looks related to Bug #104502

How to repeat:
8.0.26 127.0.0.1:18026+   SQL  \w
Show warnings disabled.
8.0.26 127.0.0.1:18026+   SQL  SELECT NOW(999);
ERROR: 1426: Too-big precision 231 specified for 'now'. Maximum is 6.
8.0.26 127.0.0.1:18026+   SQL  SELECT @@error_count;
+---------------+
| @@error_count |
+---------------+
|             0 |
+---------------+
1 row in set (0.0005 sec)
8.0.26 127.0.0.1:18026+   SQL  SELECT NOW(999); SELECT @@error_count;
ERROR: 1426: Too-big precision 231 specified for 'now'. Maximum is 6.
+---------------+
| @@error_count |
+---------------+
|             1 |
+---------------+
1 row in set (0.0003 sec)
[16 Sep 10:35] MySQL Verification Team
Hello Daniël,

Thank you for the report and feedback.
I'm not seeing the discrepancy when I quickly checked it my end: 

- 
MySQL Shell 8.0.26

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > \c root@localhost
Creating a session to 'root@localhost'
Please provide the password for 'root@localhost':
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 22 (X protocol)
Server version: 8.0.26 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  JS >
 MySQL  localhost:33060+ ssl  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  localhost:33060+ ssl  SQL >  \w
Show warnings disabled.
 MySQL  localhost:33060+ ssl  SQL > SELECT NOW(999);
ERROR: 1426: Too-big precision 231 specified for 'now'. Maximum is 6.
 MySQL  localhost:33060+ ssl  SQL > SELECT @@error_count;
+---------------+
| @@error_count |
+---------------+
|             1 |
+---------------+
1 row in set (0.0004 sec)
 MySQL  localhost:33060+ ssl  SQL >  SELECT NOW(999); SELECT @@error_count;
ERROR: 1426: Too-big precision 231 specified for 'now'. Maximum is 6.
+---------------+
| @@error_count |
+---------------+
|             1 |
+---------------+
1 row in set (0.0003 sec)
 MySQL  localhost:33060+ ssl  SQL >

Not sure it matters but just thought to mention - MySQL Server 8.0.26, MySQL Shell 8.0.26 hosted on Windows 10.

Anything I'm missing here? Pls let me know.

regards,
Umesh
[16 Sep 11:36] Daniël van Eeden
I found out what is causing this:

### With no ~/.mysqlsh/prompt.json

 MySQL  127.0.0.1:18026+ ssl  SQL > SELECT NOW(999);
ERROR: 1426: Too-big precision 231 specified for 'now'. Maximum is 6.
 MySQL  127.0.0.1:18026+ ssl  SQL > SELECT @@error_count;
+---------------+
| @@error_count |
+---------------+
|             1 |
+---------------+
1 row in set (0.0004 sec)

### With my ~/.mysqlsh/prompt.json

8.0.26 127.0.0.1:18026+   SQL  SELECT NOW(999);
ERROR: 1426: Too-big precision 231 specified for 'now'. Maximum is 6.
8.0.26 127.0.0.1:18026+   SQL  SELECT @@error_count;
+---------------+
| @@error_count |
+---------------+
|             0 |
+---------------+
1 row in set (0.0004 sec)

### Info about my prompt

$ diff -u /usr/share/mysqlsh/prompt/prompt_256pl.json  ~/.mysqlsh_bak/prompt.json 
--- /usr/share/mysqlsh/prompt/prompt_256pl.json	2021-06-11 01:10:56.000000000 +0200
+++ /home/dvaneeden/.mysqlsh_bak/prompt.json	2021-09-16 13:29:59.052702059 +0200
@@ -65,17 +65,11 @@
 	  "classes": ["disconnected%host%", "%is_production%"]
 	},
     {
-      "text": " My",
+      "text": "%sysvar:version%",
       "bg": 254,
       "fg": 23
     },
     {
-      "separator": "",
-      "text": "SQL ",
-      "bg": 254,
-      "fg": 166
-    },
-    {
       "classes": ["disconnected%host%", "%ssl%host%session%"],
       "shrink": "truncate_on_dot",
       "bg": 237,

So it looks like using a `sysvar` in a prompt is causing this.

I checked Bug #104502 as well and that is also caused by the prompt.
[16 Sep 11:39] Daniël van Eeden
See also https://github.com/mysql/mysql-shell/blob/master/samples/prompt/README.prompt
[16 Sep 11:57] MySQL Verification Team
Thank you Daniel.
Verified as described.

On my windows box, %AppData%\MySQL\mysqlsh\prompt.json was missing but after creating a prompt file based on prompt_256.json and making changes as in your environment I'm seeing it now finally:

--
MySQL Shell 8.0.26

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
SQL  JS > \sql
Switching to SQL mode... Commands end with ;
SQL  SQL >
SQL  SQL > \c root@localhost
Creating a session to 'root@localhost'
Please provide the password for 'root@localhost':
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 23 (X protocol)
Server version: 8.0.26 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
8.0.26SQL  localhost:33060+ ssl  SQL >
8.0.26SQL  localhost:33060+ ssl  SQL > SELECT NOW(999);
ERROR: 1426: Too-big precision 231 specified for 'now'. Maximum is 6.
8.0.26SQL  localhost:33060+ ssl  SQL > SELECT @@error_count;
+---------------+
| @@error_count |
+---------------+
|             0 |
+---------------+
1 row in set (0.0004 sec)
8.0.26SQL  localhost:33060+ ssl  SQL > SELECT NOW(999); SELECT @@error_count;
ERROR: 1426: Too-big precision 231 specified for 'now'. Maximum is 6.
+---------------+
| @@error_count |
+---------------+
|             1 |
+---------------+
1 row in set (0.0002 sec)
8.0.26SQL  localhost:33060+ ssl  SQL >

regards,
Umesh