[#MDEV-15320] Allow per statement settting of variables

Comment by Elena Stepanova [ 2018-02-15 ]

We have SET STATEMENT since 10.1:

MariaDB [test]> CREATE TABLE t1 (a INT UNSIGNED);

Query OK, 0 rows affected (0.25 sec)

MariaDB [test]> SELECT @@sql_mode;

+--------------------------------------------+

| @@sql_mode                                 |

+--------------------------------------------+

| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+--------------------------------------------+

1 row in set (0.00 sec)

MariaDB [test]> INSERT INTO t1 VALUES (-1);

Query OK, 1 row affected, 1 warning (0.05 sec)

MariaDB [test]> SHOW WARNINGS;

+---------+------+--------------------------------------------+

| Level   | Code | Message                                    |

+---------+------+--------------------------------------------+

| Warning | 1264 | Out of range value for column 'a' at row 1 |

+---------+------+--------------------------------------------+

1 row in set (0.01 sec)

MariaDB [test]> SET STATEMENT sql_mode='STRICT_ALL_TABLES' FOR INSERT INTO t1 VALUES (-1);

ERROR 1264 (22003): Out of range value for column 'a' at row 1

MariaDB [test]> SELECT @@sql_mode;

+--------------------------------------------+

| @@sql_mode                                 |

+--------------------------------------------+

| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+--------------------------------------------+

1 row in set (0.00 sec)

Most variables can be temporarily set this way.
Is it not enough?

Comment by Hartmut Holzgraefe [ 2018-02-15 ]

Now I remember that I read about it at some point in the past.

That's still a separate statement though, so the changed setting would not be visible in SHOW PROCESSLIST and general query and audit logs, so having a specific in-query option for this, eg. in form of a /!SET ../ magic comment, might still make sense.

Downgrading priority to "Trivial" (feature request) as this is mostly just a cosmetic/convenience request now ...

Comment by Elena Stepanova [ 2018-02-15 ]

It's not a separate statement.

# Connection A

MariaDB [test]> set statement sql_mode='STRICT_ALL_TABLES' FOR SELECT SLEEP(10);

# Connection B

MariaDB [test]> SHOW PROCESSLIST;

+----+------+-----------------+------+---------+------+------------+-----------------------------------------------------------------+----------+

| Id | User | Host            | db   | Command | Time | State      | Info                                                            | Progress |

+----+------+-----------------+------+---------+------+------------+-----------------------------------------------------------------+----------+

|  3 | root | localhost:51197 | test | Query   |    0 | init       | SHOW PROCESSLIST                                                |    0.000 |

|  4 | root | localhost:51198 | test | Query   |    3 | User sleep | set statement sql_mode='STRICT_ALL_TABLES' FOR SELECT SLEEP(10) |    0.000 |

+----+------+-----------------+------+---------+------+------------+-----------------------------------------------------------------+----------+

2 rows in set (0.01 sec)

general log
180215 14:54:49 4 Query set statement sql_mode='STRICT_ALL_TABLES' FOR SELECT SLEEP(10)

server audit log
20180215 14:54:59,ws,root,localhost,4,2,QUERY,test,'set statement sql_mode=\'STRICT_ALL_TABLES\' FOR SELECT SLEEP(10)',0

Comment by Hartmut Holzgraefe [ 2018-02-15 ]

Looks as if I officially need more coffee .... :o

[MDEV-15320] Allow per statement settting of variables Created: 2018-02-15 Updated: 2020-08-25 Resolved: 2018-02-15
Status:	Closed
Project:	MariaDB Server
Component/s:	Parser
Affects Version/s:	None
Fix Version/s:	N/A

[MDEV-15320] Allow per statement settting of variables Created: 2018-02-15 Updated: 2020-08-25 Resolved: 2018-02-15