PlaceOweb Dotclear

• Server Status Variables utiliser SHOW STATUS pour voir le status des variables.
• Server System Variables utiliser SHOW VARIABLES pour lister leur valeur ou le format avec 2 @ : @@event_scheduler
• User-defined Variables avec un seul @ : @ma_variable

-- SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr]
SHOW STATUS; -- par défaut SESSION, identique à SHOW SESSION STATUS, retourne : Variable_name | Value 
SHOW SESSION STATUS;
SHOW GLOBAL STATUS;

SHOW STATUS LIKE 'Uptime'; -- L'uptime MariaDB

SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS; -- retourne : VARIABLE_NAME | VARIABLE_VALUE 
SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS;

-- SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern' | WHERE expr]
SHOW VARIABLES; -- par défaut SESSION, identique à SHOW SESSION VARIABLES, retourne : Variable_name | Value 
SHOW SESSION VARIABLES;
SHOW GLOBAL VARIABLES;

SHOW VARIABLES LIKE 'event_scheduler'; -- L'état du planificateur d'événements

SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES; -- retourne : VARIABLE_NAME | VARIABLE_VALUE 
SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES;

SELECT @@event_scheduler;
SELECT @@GLOBAL.event_scheduler;
SELECT @@SESSION.event_scheduler; -- ERROR 1238 (HY000): Variable 'event_scheduler' is a GLOBAL variable

-- Pour avoir simultanément les 2 types de variables (status et système), par d'autre choix que de passer par le dictionnaire :
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE 'Uptime'
UNION ALL
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE 'event_scheduler';

-- Les colonnes retournées :

SHOW STATUS LIKE '%time%';
/*
+-------------------------------------+----------+
| Variable_name                       | Value    |
+-------------------------------------+----------+
| Binlog_group_commit_trigger_timeout | 0        |
| Busy_time                           | 0.000000 |
| Cpu_time                            | 0.000000 |
| Feature_timezone                    | 0        |
| Innodb_row_lock_time                | 1275     |
| Innodb_row_lock_time_avg            | 0        |
| Innodb_row_lock_time_max            | 515      |
| Ssl_default_timeout                 | 0        |
| Ssl_session_cache_timeouts          | 0        |
| Uptime                              | 422769   |
| Uptime_since_flush_status           | 422769   |
+-------------------------------------+----------+
*/

SHOW VARIABLES LIKE '%time%';
/*
+-----------------------------+-------------------+
| Variable_name               | Value             |
+-----------------------------+-------------------+
| connect_timeout             | 10                |
| datetime_format             | %Y-%m-%d %H:%i:%s |
| deadlock_timeout_long       | 50000000          |
| deadlock_timeout_short      | 10000             |
| delayed_insert_timeout      | 300               |
| flush_time                  | 0                 |
| innodb_flush_log_at_timeout | 1                 |
| innodb_lock_wait_timeout    | 50                |
| innodb_old_blocks_time      | 1000              |
| innodb_rollback_on_timeout  | OFF               |
| interactive_timeout         | 28800             |
| lc_time_names               | en_US             |
| lock_wait_timeout           | 31536000          |
| long_query_time             | 2.000000          |
| net_read_timeout            | 30                |
| net_write_timeout           | 60                |
| progress_report_time        | 5                 |
| slave_net_timeout           | 3600              |
| slow_launch_time            | 2                 |
| system_time_zone            | CEST              |
| thread_pool_idle_timeout    | 60                |
| time_format                 | %H:%i:%s          |
| time_zone                   | SYSTEM            |
| timed_mutexes               | OFF               |
| timestamp                   | 1465899161.605375 |
| wait_timeout                | 28800             |
+-----------------------------+-------------------+
*/

SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME LIKE '%time%';
/*
+-----------------------------+-------------------+
| VARIABLE_NAME               | VARIABLE_VALUE    |
+-----------------------------+-------------------+
| INNODB_FLUSH_LOG_AT_TIMEOUT | 1                 |
| TIMED_MUTEXES               | OFF               |
| DELAYED_INSERT_TIMEOUT      | 300               |
| SLOW_LAUNCH_TIME            | 2                 |
| CONNECT_TIMEOUT             | 10                |
| DEADLOCK_TIMEOUT_SHORT      | 10000             |
| INNODB_ROLLBACK_ON_TIMEOUT  | OFF               |
| LOCK_WAIT_TIMEOUT           | 31536000          |
| THREAD_POOL_IDLE_TIMEOUT    | 60                |
| NET_WRITE_TIMEOUT           | 60                |
| FLUSH_TIME                  | 0                 |
| LONG_QUERY_TIME             | 2.000000          |
| TIME_FORMAT                 | %H:%i:%s          |
| SYSTEM_TIME_ZONE            | CEST              |
| SLAVE_NET_TIMEOUT           | 3600              |
| DEADLOCK_TIMEOUT_LONG       | 50000000          |
| INNODB_LOCK_WAIT_TIMEOUT    | 50                |
| INNODB_OLD_BLOCKS_TIME      | 1000              |
| NET_READ_TIMEOUT            | 30                |
| DATETIME_FORMAT             | %Y-%m-%d %H:%i:%s |
| LC_TIME_NAMES               | en_US             |
| PROGRESS_REPORT_TIME        | 5                 |
| TIME_ZONE                   | SYSTEM            |
| INTERACTIVE_TIMEOUT         | 28800             |
| WAIT_TIMEOUT                | 28800             |
+-----------------------------+-------------------+
*/