I try to change sql_mode global variable with root user using
perfomance_schema.global_variables table.
I tried by using SET GLOBAL sql_mode='???';
But it's not working.
When I show my root grants, I think I have the correct rights :
libertalia@labuse:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0,00 sec)
Next step, I tried to change the sql_mode global variable:
mysql> use performance_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> describe global_variables;
+----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| VARIABLE_NAME | varchar(64) | NO | | NULL | |
| VARIABLE_VALUE | varchar(1024) | YES | | NULL | |
+----------------+---------------+------+-----+---------+-------+
2 rows in set (0,01 sec)
mysql> SELECT * FROM global_variables WHERE VARIABLE_NAME = 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,01 sec)
mysql> UPDATE global_variables SET VARIABLE_VALUE = 'ONLY_FULL_GROUP_BY,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' WHERE VARIABLE_NAME = 'sql_mode';
ERROR 1142 (42000): UPDATE command denied to user 'root'@'localhost' for table 'global_variables'
I don't understand why my 'root' super user can't UPDATE this table? Does 'root' have the correct rights? Does MySQL protect the system tables?
To change the sql_mode variable do I need SUPER privileges but not ALL PRIVILEGES ? Is this why SET GLOBAL sql_mode isn't working either?
information_schema are generally views which are read only in nature.
INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several read-only tables. They are actually views, not base tables, so there are no files associated with them, and you cannot set triggers on them. Also, there is no database directory with that name.
Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.