{"id":7199,"date":"2019-04-02T08:25:31","date_gmt":"2019-04-02T07:25:31","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=7199"},"modified":"2019-05-16T17:05:08","modified_gmt":"2019-05-16T16:05:08","slug":"nouveautes-mysql-8-0-variables-persistes","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/","title":{"rendered":"Nouveaut\u00e9s MySQL 8.0 : Variables persist\u00e9s"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

La nouvelle version MySQL 8.0 est sortie en Septembre 2016 et la derni\u00e8re version disponible sortie en F\u00e9vrier 2019 est la 8.0.15<\/p>\n

Elle impl\u00e9mente beaucoup de nouvelles fonctionnalit\u00e9s et am\u00e9liorations comme :<\/p>\n

– Variables persist\u00e9s (set persist)
\n– Dictionnaire de donn\u00e9es transactionnel
\n– Support des r\u00f4les SQL
\n– Common Table Expression (CTE)
\n– Fonctions de fen\u00eatrage (windowing)
\n– Indexes descendants et invisibles
\n– Histogrammes – Statistiques
\n– Groupes de Ressources
\n– Nouveau character set UTF8MB4 (emojis)
\n– Configuration automatique de variables (innodb_dedicated_server)
\n…
\n…<\/p>\n

Ce premier article se fera sur les variables persist\u00e9s en MySQL 8.0.14<\/h2>\n

MySQL permet de modifier les param\u00e8tres via des fichiers de configuration comme my.cnf ou my.ini, les modifications prenant effet au prochain red\u00e9marrage du serveur MySQL.
\nCependant, cela signifie le red\u00e9marrage du serveur et cela est devenu de moins en moins facile car cela augmente le temps d’indisponibilit\u00e9 du serveur MySQL.<\/p>\n

Gr\u00e2ce \u00e0 SET PERSIST, on peut modifier de nombreuses variables avec un effet visible et cela survivra au red\u00e9marrage du serveur. De plus, elle permet de g\u00e9rer les param\u00e8tres du serveur lorsque la modification du fichier de configuration (my.ini) est compliqu\u00e9e, par exemple \u00e0 cause des droits du fichier ou quand on n’a pas acc\u00e8s au syst\u00e8me de fichiers dans le Cloud.<\/p>\n

MySQL 8.0 permet aussi avec SET PERSIST de d\u00e9finir la plupart des variables en lecture seule. Les nouvelles valeurs seront prises en compte lors du prochain d\u00e9marrage du serveur.
\nCependant, il reste certaines variables en lecture seule qui ont \u00e9t\u00e9 laiss\u00e9es intentionnellement non r\u00e9glables.<\/p>\n

Toute variable GLOBAL ou DYNAMIC peut \u00eatre modifi\u00e9e avec l\u2019instruction SET PERSIST. Les param\u00e8tres seront conserv\u00e9s dans un fichier mysqld-auto.cnf qui sera cr\u00e9\u00e9 dans le datadir lors de la premi\u00e8re ex\u00e9cution SET PERSIST.
\nCe fichier sera lu lors du d\u00e9marrage du serveur et il sera le dernier fichier \u00e0 \u00eatre appliqu\u00e9 au d\u00e9marrage. Il sera donc prioritaire au fichier my.ini et aux options en ligne de commande.<\/p>\n

Cette fonctionnalit\u00e9 permet \u00e9galement d’avoir une meilleur gestion des op\u00e9rations manuelles sur les fichiers de configuration et de d\u00e9ployer plus facilement les configurations dans des bases sur le Cloud par exemple.<\/p>\n

 <\/p>\n

Voici plusieurs exemples montrant le changement de param\u00e8tres en utilisant ou non la fonctionnalit\u00e9, suivi d’un red\u00e9marrage du serveur MySQL sur Windows 7.<\/p>\n

Avant modification :<\/strong><\/h3>\n
mysql> select @@global.version;\r\n+-----------+\r\n| @@version |\r\n+-----------+\r\n| 8.0.14    |\r\n+-----------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select @@global.max_connections;\r\n+-------------------+\r\n| @@max_connections |\r\n+-------------------+\r\n| 151 |\r\n+-------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select @@global.wait_timeout;\r\n+----------------+\r\n| @@wait_timeout |\r\n+----------------+\r\n| 28800 |\r\n+----------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select @@global.sort_buffer_size;\r\n+--------------------+\r\n| @@sort_buffer_size |\r\n+--------------------+\r\n| 262144 |\r\n+--------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select @@global.tmpdir;\r\n+-------------------------------------------------+\r\n| @@tmpdir |\r\n+-------------------------------------------------+\r\n| C:\\Windows\\SERVIC~2\\NETWOR~1\\AppData\\Local\\Temp |\r\n+-------------------------------------------------+\r\n1 row in set (0.00 sec)\r\n<\/pre>\n
 <\/p>\n
L’ajout d’un nouveau fichier de configuration pour modifier les configurations de variable avec SET PERSIST, peut nous emm\u00ealer les pinceaux quand il faut savoir si une variable a \u00e9t\u00e9 modifi\u00e9e dans le fichier de configuration my.ini ou mysqld-auto.cnf.<\/p>\n
C’est pourquoi, une nouvelle table de performance_schema appel\u00e9e variables_info est \u00e9galement impl\u00e9ment\u00e9e en MySQL 8. Celle-ci va nous permettre de voir la source et la configuration des variables qui ont \u00e9t\u00e9 mise en place.<\/p>\n
On peut y voir les diff\u00e9rentes variables avec leur source (explicit ou compiled), ainsi que le chemin de la variable si elles utilisent my.ini :<\/p>\n
mysql> select * from performance_schema.variables_info where variable_name like 'max_conn%';\r\n+--------------------+-----------------+----------------------------------------------+-----------+------------+----------+----------+----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+--------------------+-----------------+----------------------------------------------+-----------+------------+----------+----------+----------+\r\n| max_connect_errors | EXPLICIT | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini | 1 | 4294967295 | NULL | NULL | NULL |\r\n| max_connections | EXPLICIT | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini | 1 | 100000 | NULL | NULL | NULL |\r\n+--------------------+-----------------+----------------------------------------------+-----------+------------+----------+----------+----------+\r\n2 rows in set (0.01 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name like 'wait_timeout';\r\n+---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+\r\n| wait_timeout | COMPILED | | 1 | 2147483 | NULL | NULL | NULL |\r\n+---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+\r\n1 row in set (0.01 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name like 'sort_buffer_size';\r\n+------------------+-----------------+----------------------------------------------+-----------+------------+----------+----------+----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+------------------+-----------------+----------------------------------------------+-----------+------------+----------+----------+----------+\r\n| sort_buffer_size | EXPLICIT | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini | 32768 | 4294967295 | NULL | NULL | NULL |\r\n+------------------+-----------------+----------------------------------------------+-----------+------------+----------+----------+----------+\r\n1 row in set (0.01 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name like 'tmpdir';\r\n+---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+\r\n| tmpdir | COMPILED | | 0 | 0 | NULL | NULL | NULL |\r\n+---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+\r\n1 row in set (0.01 sec)\r\n\r\nmysql> SET PERSIST max_connections=50;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> SET PERSIST wait_timeout=600;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> SET PERSIST sort_buffer_size=524288;\r\n\r\nmysql> SET PERSIST tmpdir='C:\/Tmp\/tmpdir';\r\nERROR 1238 (HY000): Variable 'tmpdir' is a read only variable\r\n\r\nmysql> SET GLOBAL tmpdir='C:\/Tmp\/tmpdir';\r\nERROR 1238 (HY000): Variable 'tmpdir' is a read only variable<\/pre>\n
Comme dit pr\u00e9c\u00e9demment avant les tests, il semblerait que plusieurs variables (avec des chiffres) de configuration de type EXPLICIT peuvent \u00eatre modifi\u00e9es de mani\u00e8re dynamique mais pas toutes, comme certaines de type COMPILED pour des modifications de r\u00e9pertoires (tmpdir : ne marche pas avec SET PERSIST | wait_timeout : marche avec SET PERSIST).<\/p>\n
 <\/p>\n
Puisque la variable tmpdir ne peut pas \u00eatre modifi\u00e9 de mani\u00e8re dynamique avec SET PERSIST ou SET GLOBAL, il nous faut donc ajouter son entr\u00e9e dans le fichier de configuration C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini<\/em><\/p>\n
Modifier le fichier de configuration my.ini et ajouter une entr\u00e9e pour tmpdir :<\/p>\n
# Path to the temporary tables\r\ntmpdir=C:\/Tmp\/tmpdir<\/pre>\n
 <\/p>\n
Red\u00e9marrer le serveur MySQL ou bien le Service Windows MySQL pour la prise en compte des nouvelles valeurs pour les param\u00e8tres persist\u00e9s, ici le param\u00e8tre tmpdir dans le fichier my.ini.<\/p>\n
mysql> restart;\r\nQuery OK, 0 rows affected (0.00 sec)<\/pre>\n
 <\/p>\n
Apr\u00e8s modification :<\/strong><\/h3>\n
On voit bien la bonne prise en compte des variables modifi\u00e9es.<\/p>\n
mysql> select global.max_connections;\r\nERROR 2006 (HY000): MySQL server has gone away\r\nNo connection. Trying to reconnect...\r\nConnection id: 8\r\nCurrent database: *** NONE ***\r\nmysql> select @@global.max_connections;\r\n+-------------------+\r\n| @@max_connections |\r\n+-------------------+\r\n| 50 |\r\n+-------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select @@global.wait_timeout;\r\n+-----------------------+\r\n| @@global.wait_timeout |\r\n+-----------------------+\r\n| 600 |\r\n+-----------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select @@global.sort_buffer_size;\r\n+---------------------------+\r\n| @@global.sort_buffer_size |\r\n+---------------------------+\r\n| 524288 |\r\n+---------------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select @@global.tmpdir;\r\n+-----------------+\r\n| @@global.tmpdir |\r\n+-----------------+\r\n| C:\/Tmp\/tmpdir |\r\n+-----------------+\r\n1 row in set (0.00 sec)<\/pre>\n
 <\/p>\n
On peut voir les changements dans la source, le chemin et la date des variables modifi\u00e9es avec SET PERSIST pointant vers mysqld-auto.cnf ainsi que celle en EXPLICIT dans le fichier my.ini :<\/p>\n
mysql> select * from performance_schema.variables_info where variable_name like 'max_conn%';\r\n+--------------------+-----------------+------------------------------------------------------------+-----------+------------+----------------------------+----------+-----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+--------------------+-----------------+------------------------------------------------------------+-----------+------------+----------------------------+----------+-----------+\r\n| max_connect_errors | EXPLICIT | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini | 1 | 4294967295 | NULL | NULL | NULL |\r\n| max_connections | PERSISTED | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data\\mysqld-auto.cnf | 1 | 100000 | 2019-02-15 17:48:57.277699 | root | localhost |\r\n+--------------------+-----------------+------------------------------------------------------------+-----------+------------+----------------------------+----------+-----------+\r\n2 rows in set (0.01 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name like 'wait_timeout';\r\n+---------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+---------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+\r\n| wait_timeout | PERSISTED | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data\\mysqld-auto.cnf | 1 | 2147483 | 2019-02-15 17:49:07.287713 | root | localhost |\r\n+---------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+\r\n1 row in set (0.01 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name like 'sort_buffer_size';\r\n+------------------+-----------------+------------------------------------------------------------+-----------+------------+----------------------------+----------+-----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+------------------+-----------------+------------------------------------------------------------+-----------+------------+----------------------------+----------+-----------+\r\n| sort_buffer_size | PERSISTED | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data\\mysqld-auto.cnf | 32768 | 4294967295 | 2019-02-18 11:16:13.401398 | root | localhost |\r\n+------------------+-----------------+------------------------------------------------------------+-----------+------------+----------------------------+----------+-----------+\r\n1 row in set (0.01 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name like 'tmpdir';\r\n+---------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+---------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+\r\n| tmpdir | EXPLICIT | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini | 0 | 0 | NULL | NULL | NULL |\r\n+---------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+\r\n1 row in set (0.01 sec)<\/pre>\n
 <\/p>\n
Une variable persistante peut \u00eatre mise en place avec une certaine valeur mais pour autant, on voudrait remettre la valeur par d\u00e9faut de celle-ci mais on ne s’en souvient plus.<\/p>\n
Ce n’est pas grave car on peut r\u00e9initialiser la variable en lui attribuant la valeur DEFAULT. Cela va l’enlever de mysqld-auto.cnf et remettre la configuration pr\u00e9c\u00e9dente du fichier de configuration my.ini<\/p>\n
Voici un exemple de changement de la variable thread_cache_size avec SET PERSIST, puis d’un retour arri\u00e8re avec DEFAULT :<\/p>\n
mysql> select @@global.thread_cache_size;\r\n+----------------------------+\r\n| @@global.thread_cache_size |\r\n+----------------------------+\r\n| 10 |\r\n+----------------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> SET PERSIST thread_cache_size=30;\r\nQuery OK, 0 rows affected (0.02 sec)\r\n\r\nmysql> restart;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> select @@global.thread_cache_size;\r\nERROR 2006 (HY000): MySQL server has gone away\r\nNo connection. Trying to reconnect...\r\nConnection id: 8\r\nCurrent database: *** NONE ***\r\n\r\n+----------------------------+\r\n| @@global.thread_cache_size |\r\n+----------------------------+\r\n| 30 |\r\n+----------------------------+\r\n1 row in set (0.05 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name like 'thread_cache_size';\r\n+-------------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+-------------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+\r\n| thread_cache_size | PERSISTED | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data\\mysqld-auto.cnf | 0 | 16384 | 2019-02-18 15:36:25.584870 | root | localhost |\r\n+-------------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> SET PERSIST thread_cache_size=DEFAULT;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> restart;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> select @@global.thread_cache_size;\r\nERROR 2006 (HY000): MySQL server has gone away\r\nNo connection. Trying to reconnect...\r\nConnection id: 8\r\nCurrent database: *** NONE ***\r\n\r\n+----------------------------+\r\n| @@global.thread_cache_size |\r\n+----------------------------+\r\n| 9 |\r\n+----------------------------+\r\n1 row in set (0.05 sec)<\/pre>\n
 <\/p>\n
Pour autant, un Bug existait en 8.0 et 8.1 sur les variables persist\u00e9s concernant general_log et long_slow_query : https:\/\/bugs.mysql.com\/bug.php?id=82807<\/a><\/p>\n
Apr\u00e8s un test sur la variable general_log, il s’av\u00e8re que le bug ait \u00e9t\u00e9 corrig\u00e9 car la valeur est bien enregistr\u00e9e dans mysqld-auto.cnf apr\u00e8s un restart :<\/p>\n
mysql> select @@global.general_log;\r\n+----------------------+\r\n| @@global.general_log |\r\n+----------------------+\r\n| 0 |\r\n+----------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name= 'general_log' or variable_name='slow_query_log';\r\n+----------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+----------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+\r\n| general_log | EXPLICIT | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini | 0 | 0 | NULL | NULL | NULL |\r\n| slow_query_log | EXPLICIT | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini | 0 | 0 | NULL | NULL | NULL |\r\n+----------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+\r\n2 rows in set (0.01 sec)\r\n\r\nmysql> set persist general_log=1;\r\nQuery OK, 0 rows affected (0.02 sec)\r\n\r\nmysql> select @@global.general_log;\r\n+----------------------+\r\n| @@global.general_log |\r\n+----------------------+\r\n| 1 |\r\n+----------------------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> restart;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> select @@global.general_log;\r\nERROR 2006 (HY000): MySQL server has gone away\r\nNo connection. Trying to reconnect...\r\nConnection id: 8\r\nCurrent database: *** NONE ***\r\n\r\n+----------------------+\r\n| @@global.general_log |\r\n+----------------------+\r\n| 1 |\r\n+----------------------+\r\n1 row in set (0.05 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name= 'general_log' or variable_name='slow_query_log';\r\n+----------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+----------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+\r\n| general_log | PERSISTED | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data\\mysqld-auto.cnf | 0 | 0 | 2019-02-18 16:16:39.149481 | root | localhost |\r\n| slow_query_log | EXPLICIT | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini | 0 | 0 | NULL | NULL | NULL |\r\n+----------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+\r\n2 rows in set (0.01 sec)<\/pre>\n
 <\/p>\n
Cependant, si vous souhaitez supprimer toutes les variables persist\u00e9es de mysqld-auto.cnf, vous devez utiliser RESET PERSIST.<\/p>\n
Par contre, si vous souhaitez supprimer une variable sp\u00e9cifique de mysqld-auto.cnf, vous devez mettre le nom de la variable apr\u00e8s RESET PERSIST.<\/p>\n
Par exemple avec la variable general_log :<\/p>\n
mysql> reset persist general_log;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name= 'general_log' or variable_name='slow_query_log';\r\n+----------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+----------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+\r\n| general_log | PERSISTED | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Data\\mysqld-auto.cnf | 0 | 0 | 2019-02-18 16:16:39.149481 | root | localhost |\r\n| slow_query_log | EXPLICIT | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini | 0 | 0 | NULL | NULL | NULL |\r\n+----------------+-----------------+------------------------------------------------------------+-----------+-----------+----------------------------+----------+-----------+\r\n2 rows in set (0.01 sec)\r\n\r\nmysql> restart;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> select * from performance_schema.variables_info where variable_name= 'general_log' or variable_name='slow_query_log';\r\nERROR 2006 (HY000): MySQL server has gone away\r\nNo connection. Trying to reconnect...\r\nConnection id: 8\r\nCurrent database: *** NONE ***\r\n\r\n+----------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+\r\n| VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST |\r\n+----------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+\r\n| general_log | EXPLICIT | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini | 0 | 0 | NULL | NULL | NULL |\r\n| slow_query_log | EXPLICIT | C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini | 0 | 0 | NULL | NULL | NULL |\r\n+----------------+-----------------+----------------------------------------------+-----------+-----------+----------+----------+----------+\r\n2 rows in set (0.07 sec)\r\n\r\nmysql> select @@global.general_log;\r\n+----------------------+\r\n| @@global.general_log |\r\n+----------------------+\r\n| 0 |\r\n+----------------------+\r\n1 row in set (0.00 sec)<\/pre>\n
 <\/p>\n
Vous en savez donc plus sur cette nouveaut\u00e9 SET PERSIST de MySQL 8.0 qui fait beaucoup penser \u00e0 l’option scope=spfile lors de la modification d’une variable syst\u00e8me sur une base Oracle.<\/p>\n
 <\/p>\n
De prochains articles sur les autres nouveaut\u00e9s de MySQL 8.0 seront test\u00e9s et mise en lignes sur le blog Capdata.<\/p>\n
Restez donc inform\u00e9 des derniers articles en vous abonnant \u00e0 la Newsletter<\/a> \ud83d\ude09<\/p>\n
Erwan Ollitrault<\/p>\n\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"
La nouvelle version MySQL 8.0 est sortie en Septembre 2016 et la derni\u00e8re version disponible sortie en F\u00e9vrier 2019 est la 8.0.15 Elle impl\u00e9mente beaucoup de nouvelles fonctionnalit\u00e9s et am\u00e9liorations comme : – Variables persist\u00e9s (set persist) – Dictionnaire de… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":7351,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[310,308,309],"class_list":["post-7199","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-mysql-8-0","tag-nouveautes","tag-variables-persistes"],"yoast_head":"\nNouveaut\u00e9s MySQL 8.0 : Variables persist\u00e9s - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Nouveaut\u00e9s MySQL 8.0 : Variables persist\u00e9s - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"La nouvelle version MySQL 8.0 est sortie en Septembre 2016 et la derni\u00e8re version disponible sortie en F\u00e9vrier 2019 est la 8.0.15 Elle impl\u00e9mente beaucoup de nouvelles fonctionnalit\u00e9s et am\u00e9liorations comme : – Variables persist\u00e9s (set persist) – Dictionnaire de… Continuer la lecture →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-02T07:25:31+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-05-16T16:05:08+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/03\/logo_mysql8.png\" \/>\n\t<meta property=\"og:image:width\" content=\"700\" \/>\n\t<meta property=\"og:image:height\" content=\"394\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Capdata team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Capdata team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"13 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/\"},\"author\":{\"name\":\"Capdata team\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9\"},\"headline\":\"Nouveaut\u00e9s MySQL 8.0 : Variables persist\u00e9s\",\"datePublished\":\"2019-04-02T07:25:31+00:00\",\"dateModified\":\"2019-05-16T16:05:08+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/\"},\"wordCount\":2544,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"mysql 8.0\",\"nouveautes\",\"variables persistes\"],\"articleSection\":[\"MySQL\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/\",\"name\":\"Nouveaut\u00e9s MySQL 8.0 : Variables persist\u00e9s - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2019-04-02T07:25:31+00:00\",\"dateModified\":\"2019-05-16T16:05:08+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Nouveaut\u00e9s MySQL 8.0 : Variables persist\u00e9s\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9\",\"name\":\"Capdata team\",\"sameAs\":[\"https:\/\/www.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Nouveaut\u00e9s MySQL 8.0 : Variables persist\u00e9s - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/","og_locale":"fr_FR","og_type":"article","og_title":"Nouveaut\u00e9s MySQL 8.0 : Variables persist\u00e9s - Capdata TECH BLOG","og_description":"La nouvelle version MySQL 8.0 est sortie en Septembre 2016 et la derni\u00e8re version disponible sortie en F\u00e9vrier 2019 est la 8.0.15 Elle impl\u00e9mente beaucoup de nouvelles fonctionnalit\u00e9s et am\u00e9liorations comme : – Variables persist\u00e9s (set persist) – Dictionnaire de… Continuer la lecture →","og_url":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2019-04-02T07:25:31+00:00","article_modified_time":"2019-05-16T16:05:08+00:00","og_image":[{"width":700,"height":394,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/03\/logo_mysql8.png","type":"image\/png"}],"author":"Capdata team","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Capdata team","Dur\u00e9e de lecture estim\u00e9e":"13 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/"},"author":{"name":"Capdata team","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9"},"headline":"Nouveaut\u00e9s MySQL 8.0 : Variables persist\u00e9s","datePublished":"2019-04-02T07:25:31+00:00","dateModified":"2019-05-16T16:05:08+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/"},"wordCount":2544,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["mysql 8.0","nouveautes","variables persistes"],"articleSection":["MySQL"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/","url":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/","name":"Nouveaut\u00e9s MySQL 8.0 : Variables persist\u00e9s - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2019-04-02T07:25:31+00:00","dateModified":"2019-05-16T16:05:08+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/nouveautes-mysql-8-0-variables-persistes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"Nouveaut\u00e9s MySQL 8.0 : Variables persist\u00e9s"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9","name":"Capdata team","sameAs":["https:\/\/www.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/7199","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=7199"}],"version-history":[{"count":59,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/7199\/revisions"}],"predecessor-version":[{"id":7550,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/7199\/revisions\/7550"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/7351"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=7199"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=7199"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=7199"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}