Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
The mysql.column_stats table stores engine-independent column statistics, such as histograms, used by the optimizer to improve query execution plans.
The mysql.column_stats table is one of three tables storing data used for Engine-independent table statistics. The others are mysql.table_stats and mysql.index_stats.
It is populated when the ANALYZE TABLE statement is run, although not by default. See Collecting Statistics with the ANALYZE TABLE Statement for details.
Note that statistics for blob and text columns are not collected. If explicitly specified, a warning is returned.
It is possible to manually update the table and, unlike most system tables, there are some scenarios where this could be useful. See Manual updates to statistics tables for details.
This table uses the Aria storage engine.
The mysql.column_stats table contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
The mysql.func table registers user-defined functions (UDFs), storing their names and the shared library files containing their code.
The mysql.func table stores information about user-defined functions (UDFs) created with the CREATE FUNCTION UDF statement.
This table uses the Aria storage engine.
The mysql.func table contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
The mysql.plugin table records information about installed server plugins, ensuring they are reloaded automatically upon server startup.
The mysql.plugin table can be queried to get information about installed plugins.
This table only contains information about plugins that have been installed via the following methods:
The INSTALL SONAME statement.
The INSTALL PLUGIN statement.
The utility.
This table does not contain information about:
Built-in plugins.
Plugins loaded with the option.
Plugins loaded with the option.
This table only contains enough information to reload the plugin when the server is restarted, which means it only contains the plugin name and the plugin library.
This table uses the storage engine.
The mysql.plugin table contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
This table stores a history of password hashes to enforce security policies regarding password reuse when the relevant plugin is enabled.
The mysql.password_reuse_check_history Table is installed as part of the password_reuse_check plugin.
The mysql.password_reuse_check_history table stores old passwords, so that when a user sets a new password, it can be checked for purposes of preventing password reuse.
It contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
The mysql.help_relation table links help keywords to help topics, defining the structure of the server-side help system.
mysql.help_relation is one of the four tables used by the HELP command. It is populated when the server is installed by the fill_help_tables.sql script. The other help tables are help_topic, help_category and help_keyword.
This table uses the Aria storage engine.
The mysql.help_relation table contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
The mysql.help_keyword table maps keywords to help topics, facilitating keyword-based searches within the MariaDB help system.
mysql.help_keyword is one of the four tables used by the . It is populated when the server is installed by the fill_help_tables.sql script. The other help tables are , and .
This table uses the storage engine.
The mysql.help_keyword table contains the following fields:
The mysql.index_stats table stores engine-independent index statistics, such as cardinality, used by the optimizer to plan query execution.
The mysql.index_stats table is one of three tables storing data used for . The others are and .
It is populated when the statement is run, although not by default. See for details.
It is possible to manually update the table and, unlike most system tables, there are some scenarios where this could be useful. See for details.
This table uses the storage engine.
The mysql.index_stats table contains the following fields:
min_value
varbinary(255)
YES
NULL
max_value
varbinary(255)
YES
NULL
nulls_ratio
decimal(12,4)
YES
NULL
Fraction of NULL values (0- no NULLs, 0.5 - half values are NULLs, 1 - all values are NULLs).
avg_length
decimal(12,4)
YES
NULL
Average length of column value, in bytes. Counted as if one ran SELECT AVG(LENGTH(col)). This doesn't count NULL bytes, assumes endspace removal for CHAR(n), etc.
avg_frequency
decimal(12,4)
YES
NULL
Average number of records with the same value
hist_size
tinyint(3) unsigned
YES
NULL
Histogram size in bytes, from 0-255, or, from , number of buckets if the histogram type is JSON_HB.
hist_type
enum('SINGLE_PREC_HB', 'DOUBLE_PREC_HB') (from MariaDB 10.8) enum('SINGLE_PREC_HB', 'DOUBLE_PREC_HB','JSON_HB') (until MariaDB 10.7)
YES
NULL
Histogram type. See the system variable.
histogram
blob (from MariaDB 10.7)
varbinary(255) (until MariaDB 10.7)
YES
NULL
db_name
varchar(64)
NO
PRI
NULL
Database the table is in.
table_name
varchar(64)
NO
PRI
NULL
Table name.
column_name
varchar(64)
NO
PRI
NULL
Name of the column.
Name of the plugin library.
name
varchar(64)
NO
PRI
Plugin name.
dl
varchar(128)
NO
help_keyword_id
int(10) unsigned
NO
PRI
NULL
name
char(64)
NO
UNI
NULL
This page is licensed: CC BY-SA / Gnu FDL
db_name
varchar(64)
NO
PRI
NULL
Database the table is in.
table_name
varchar(64)
NO
PRI
NULL
Table name
index_name
varchar(64)
NO
PRI
NULL
It is possible to manually update the table. See Manual updates to statistics tables for details.
This page is licensed: CC BY-SA / Gnu FDL
name
char(64)
NO
PRI
UDF name
ret
tinyint(1)
NO
0
dl
char(128)
NO
Shared library name
type
enum('function','aggregate')
NO
NULL
hash
binary(64)
NO
PRI
NULL
time
timestamp
NO
MUL
current_timestamp()
help_topic_id
int(10) unsigned
NO
PRI
NULL
help_keyword_id
int(10) unsigned
NO
PRI
NULL
The mysql.general_log table captures a record of all SQL statements received by the server when general query logging is enabled and written to tables.
The mysql.general_log table stores the contents of the General Query Log if general logging is active and the output is being written to table (see Writing logs into tables).
It contains the following fields:
event_time
This page is licensed: CC BY-SA / Gnu FDL
The mysql.innodb_table_stats table holds persistent table-level statistics for InnoDB, such as row counts, used for query optimization.
The mysql.innodb_table_stats table stores data related to InnoDB Persistent Statistics, and contains one row per table.
This table, along with the related mysql.innodb_index_stats table, can be manually updated in order to force or test differing query optimization plans. After updating, FLUSH TABLE innodb_table_stats is required to load the changes.
mysql.innodb_table_stats is not replicated, although any ANALYZE TABLE statements on the table will be by default.
It contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
The mysql.help_category table stores category information for the server-side help system, organizing help topics into a hierarchy.
mysql.help_category is one of the four tables used by the HELP command. It is populated when the server is installed by the fill_help_tables.sql script. The other help tables are help_relation, help_topic and help_keyword.
This table uses the Aria storage engine.
The mysql.help_category table contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
The mysql.columns_priv table records column-level privileges granted to users, detailing specific access rights for individual columns.
The mysql.columns_priv table contains information about column-level privileges. The table can be queried and although it is possible to directly update it, it is best to use for setting privileges.
Note that the MariaDB privileges occur at many levels. A user may be granted a privilege at the column level, but may still not have permission on a table level, for example. See for a more complete view of the MariaDB privilege system.
The table derives its contents from mysql.columns_priv.
This table uses the storage engine.
The mysql.columns_priv table contains the following fields:
The mysql.gtid_slave_pos table tracks the Global Transaction ID (GTID) of the last applied transaction on a replica to ensure replication consistency.
The mysql.gtid_slave_pos table is used in by replica servers to keep track of their current position (the of the last transaction applied). Using the table allows the replica to maintain a consistent value for the system variable across server restarts. See .
You should never attempt to modify the table directly. If you do need to change the global gtid_slave_pos value, use SET GLOBAL gtid_slave_pos = ... instead.
The table is updated with the new position as part of each transaction committed during replication. This makes it preferable that the table is using the same storage engine as the tables otherwise being modified in the transaction, since otherwise a multi-engine transaction is needed that can reduce performance.
Multiple versions of this table are supported, each using a different storage engine. This is selected with the , by giving a comma-separated list of engine names. The server will then on-demand create an extra version of the table using the appropriate storage engine, and select the table version using the same engine as the rest of the transaction, avoiding multi-engine transactions.
The mysql.table_stats table stores engine-independent statistics about tables, such as row counts, to assist the optimizer.
The mysql.table_stats table is one of three tables storing data used for . The others are and .
It is populated when the statement is run, although not by default. See for details.
It is possible to manually update the table and, unlike most system tables, there are some scenarios where this could be useful. See for details.
This table uses the storage engine.
The mysql.table_stats table contains the following fields:
SELECT * FROM mysql.plugin;
+---------------------------+------------------------+
| name | dl |
+---------------------------+------------------------+
| spider | ha_spider.so |
| spider_alloc_mem | ha_spider.so |
| METADATA_LOCK_INFO | metadata_lock_info.so |
| OQGRAPH | ha_oqgraph.so |
| cassandra | ha_cassandra.so |
| QUERY_RESPONSE_TIME | query_response_time.so |
| QUERY_RESPONSE_TIME_AUDIT | query_response_time.so |
| LOCALES | locales.so |
| sequence | ha_sequence.so |
+---------------------------+------------------------+SELECT * FROM help_keyword;
+-----------------+-------------------------------+
| help_keyword_id | name |
+-----------------+-------------------------------+
| 0 | JOIN |
| 1 | HOST |
| 2 | REPEAT |
| 3 | SERIALIZABLE |
| 4 | REPLACE |
| 5 | AT |
| 6 | SCHEDULE |
| 7 | RETURNS |
| 8 | STARTS |
| 9 | MASTER_SSL_CA |
| 10 | NCHAR |
| 11 | COLUMNS |
| 12 | COMPLETION |
...SELECT * FROM mysql.func;
+------------------------------+-----+--------------+-----------+
| name | ret | dl | type |
+------------------------------+-----+--------------+-----------+
| spider_direct_sql | 2 | ha_spider.so | function |
| spider_bg_direct_sql | 2 | ha_spider.so | aggregate |
| spider_ping_table | 2 | ha_spider.so | function |
| spider_copy_tables | 2 | ha_spider.so | function |
| spider_flush_table_mon_cache | 2 | ha_spider.so | function |
+------------------------------+-----+--------------+-----------+...
| 106 | 456 |
| 463 | 456 |
| 468 | 456 |
| 463 | 457 |
| 194 | 458 |
| 478 | 458 |
| 374 | 459 |
| 459 | 459 |
| 39 | 460 |
| 58 | 460 |
| 185 | 460 |
| 264 | 460 |
| 269 | 460 |
| 209 | 461 |
| 468 | 461 |
| 201 | 462 |
| 468 | 463 |
+---------------+-----------------+Explore obsolete tables in the mysql database for MariaDB Server. This section provides information on deprecated system tables, useful for understanding historical contexts or migration planning.
Explore Spider-related tables within the mysql database. These system tables store crucial configuration and metadata for the Spider storage engine, essential for distributed deployments.
Explore tables in the mysql database in MariaDB Server. These system tables store essential information for server operation, including user privileges, security settings, and global configuration.
n_rows
bigint(20) unsigned
NO
NULL
Number of rows in the table.
clustered_index_size
bigint(20) unsigned
NO
NULL
Size, in pages, of the primary index.
sum_of_other_index_sizes
bigint(20) unsigned
NO
NULL
Size, in pages, of non-primary indexes.
database_name
varchar(64)
NO
PRI
NULL
Database name.
table_name
varchar(64)
NO
PRI
NULL
Table, partition or subpartition name.
last_update
timestamp
NO
current_timestamp()
Time that this row was last updated.
url
char(128)
NO
NULL
help_category_id
smallint(5) unsigned
NO
PRI
NULL
name
char(64)
NO
UNI
NULL
parent_category_id
smallint(5) unsigned
YES
NULL
Host
char(60)
NO
PRI
Host (together with User, Db , Table_name andColumn_name makes up the unique identifier for this record.
Db
char(64)
NO
PRI
Database name (together with User, Host , Table_name andColumn_name makes up the unique identifier for this record.
User
char(80)
NO
PRI
The Acl_column_grants status variable indicates how many rows the mysql.columns_priv table contains.
This page is licensed: CC BY-SA / Gnu FDL
When gtid_pos_auto_engines=innodb,rocksdb is set, the tables mysql.gtid_slave_pos_InnoDB and mysql.gtid_slave_pos_RocksDB are created and used, if needed. If there is no match to the storage engine, the default mysql.gtid_slave_pos table is used; this also happens if non-transactional updates (like MyISAM) are replicated, since there is then no active transaction at the time of the mysql.gtid_slave_pos table update.
The default mysql.gtid_slave_pos table is initially created using the default storage engine set for the server (which itself defaults to InnoDB). If the application load is primarily non-transactional MyISAM or Aria tables, it can be beneficial to change the storage engine to avoid including
an InnoDB update with every operation:
The mysql.gtid_slave_pos table should not be changed manually in any other way. It is preferable to use the gtid_pos_auto_engines server variable to get the GTID position updates to use the TokuDB or
RocksDB storage engine.
Note that, for scalability reasons, the automatic creation of a newmysql.gtid_slave_posXXX table happens asynchronously when the first transaction with the new storage engine is committed. So the very first few transactions will update the old version of the table, until the new version is created and available.
The table mysql.gtid_slave_pos contains the following fields:
domain_id
int(10) unsigned
NO
PRI
NULL
Domain id (see .
sub_id
bigint(20) unsigned
NO
Some status variables are available to monitor the use of the different gtid_slave_pos table versions:
Number of replicated transactions where the update of the gtid_slave_pos table had to choose a storage engine that did not otherwise participate in the transaction. This can indicate that setting gtid_pos_auto_engines might be useful.
Number of replicated transactions that involved changes in multiple (transactional) storage engines, before considering the update ofgtid_slave_pos. These are transactions that were already cross-engine, independent of the GTID position update introduced by replication
Number of transactions that changed data in multiple (transactional) storage engines. If this is significantly larger than Rpl_transactions_multi_engine, it indicates that setting gtid_pos_auto_engines could reduce the need for cross-engine transactions.
This page is licensed: CC BY-SA / Gnu FDL
db_name
varchar(64)
NO
PRI
NULL
Database the table is in .
table_name
varchar(64)
NO
PRI
NULL
Table name.
cardinality
bigint(21) unsigned
YES
NULL
This page is licensed: CC BY-SA / Gnu FDL
Name of the index
prefix_arity
int(11) unsigned
NO
PRI
NULL
Index prefix length. 1 for the first keypart, 2 for the first two, and so on. InnoDB's extended keys are supported.
avg_frequency
decimal(12,4)
YES
NULL
Average number of records one will find for given values of (keypart1, keypart2, ..), provided the values will be found in the table.
timestamp(6)
NO
CURRENT_TIMESTAMP(6)
Time the query was executed.
user_host
mediumtext
NO
NULL
User and host combination.
thread_id
int(11)
NO
NULL
Thread id.
server_id
int(10) unsigned
NO
NULL
Server id.
command_type
varchar(64)
NO
NULL
Type of command.
argument
mediumtext
NO
NULL
Full query.
The mysql.procs_priv table records privileges granted to users specifically for executing or altering stored procedures and functions.
System tables should not normally be edited directly. Use the related SQL statements instead.
The mysql.procs_priv table contains information about stored procedure and stored function privileges. See CREATE PROCEDURE and CREATE FUNCTION on creating these.
The INFORMATION_SCHEMA.ROUTINES table derives its contents from mysql.procs_priv.
This table uses the Aria storage engine.
The mysql.procs_priv table contains the following fields:
The status variable indicates how many rows the mysql.columns_priv table contains with the FUNCTION routine type.
The status variable indicates how many rows the mysql.columns_priv table contains with the PROCEDURE routine type.
This page is licensed: CC BY-SA / Gnu FDL
The mysql.time_zone table assigns a unique ID to each time zone supported by the server, linking to other time zone system tables.
The mysql.time_zone table is one of the mysql system tables that can contain time zone information. It is usually preferable for the system to handle the time zone, in which case the table will be empty (the default), but you can populate the mysql time zone tables using the mariadb-tzinfo-to-sql utility. See Time Zones for details.
This table uses the Aria storage engine.
The mysql.time_zone table contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
The mysql.roles_mapping table manages role assignments, linking user accounts to the roles they have been granted.
System tables should not normally be edited directly. Use the related SQL statements instead.
The mysql.roles_mapping table contains information about mariaDB roles.
This table uses the Aria storage engine.
The mysql.roles_mapping table contains the following fields:
The status variable indicates how many rows the mysql.roles_mapping table contains.
This page is licensed: CC BY-SA / Gnu FDL
The mysql.help_topic table stores the detailed content of help topics, including descriptions and examples displayed by the HELP command.
mysql.help_topic is one of the four tables used by the . It is populated when the server is installed by the fill_help_tables.sql script. The other help tables are , and .
This table uses the storage engine.
The mysql.help_topic table contains the following fields:
The mysql.innodb_index_stats table stores persistent index statistics for InnoDB, allowing optimizer plans to remain stable across restarts.
The mysql.innodb_index_stats table stores data related to particular , and contains multiple rows for each index.
This table, along with the related table, can be manually updated in order to force or test differing query optimization plans. After updating, is required to load the changes.
mysql.innodb_index_stats is not replicated, although any statements on the table will be by default..
It contains the following fields:
The mysql.transaction_registry table is used by system-versioned tables to track transaction IDs and their commit timestamps.
The mysql.transaction_registry table is part of . It is used for , and contains the following fields:
The mysql.time_zone_transition table defines the exact times at which daylight saving time or other time zone transitions occur.
The mysql.time_zone_transition table is one of the mysql system tables that can contain information. It is usually preferable for the system to handle the time zone, in which case the table will be empty (the default), but you can populate the mysql time zone tables using the utility. See for details.
This table uses the storage engine.
The mysql.time_zone_transition table contains the following fields:
The mysql.time_zone_leap_second table lists leap second corrections to be applied to specific time zones.
The mysql.time_zone_leap_second table is one of the mysql system tables that can contain information. It is usually preferable for the system to handle the time zone, in which case the table will be empty (the default), but you can populate the mysql time zone tables using the utility. See for details.
This table uses the storage engine.
The mysql.time_zone_leap_second table contains the following fields:
This table logs failures when attempting to establish connections to remote Spider links, helping to diagnose network or configuration issues.
The mysql.spider_link_failed_log table is installed by the .
This table uses the storage engine.
It contains the following fields:
The mysql.time_zone_name table maps human-readable time zone names (e.g., "Europe/Berlin") to their internal time zone IDs.
The mysql.time_zone_name table is one of the mysql system tables that can contain information. It is usually preferable for the system to handle the time zone, in which case the table will be empty (the default), but you can populate the mysql time zone tables using the utility. See for details.
This table uses the storage engine.
The mysql.time_zone_name table contains the following fields:
The mysql.ndb_binlog_index table is not used by MariaDB. It was kept for MySQL compatibility reasons, and is used there for MySQL Cluster.
For MariaDB clustering, see .
The table contains the following fields:
System tables should not normally be edited directly. Use the related SQL statements instead.
SELECT * FROM mysql.innodb_table_stats\G
*************************** 1. row ***************************
database_name: mysql
table_name: gtid_slave_pos
last_update: 2017-08-19 20:38:34
n_rows: 0
clustered_index_size: 1
sum_of_other_index_sizes: 0
*************************** 2. row ***************************
database_name: test
table_name: ft
last_update: 2017-09-15 12:58:39
n_rows: 0
clustered_index_size: 1
sum_of_other_index_sizes: 2
...SELECT * FROM help_category;
+------------------+-----------------------------------------------+--------------------+-----+
| help_category_id | name | parent_category_id | url |
+------------------+-----------------------------------------------+--------------------+-----+
| 1 | Geographic | 0 | |
| 2 | Polygon properties | 34 | |
| 3 | WKT | 34 | |
| 4 | Numeric Functions | 38 | |
| 5 | Plugins | 35 | |
| 6 | MBR | 34 | |
| 7 | Control flow functions | 38 | |
| 8 | Transactions | 35 | |
| 9 | Help Metadata | 35 | |
| 10 | Account Management | 35 | |
| 11 | Point properties | 34 | |
| 12 | Encryption Functions | 38 | |
| 13 | LineString properties | 34 | |
| 14 | Miscellaneous Functions | 38 | |
| 15 | Logical operators | 38 | |
| 16 | Functions and Modifiers for Use with GROUP BY | 35 | |
| 17 | Information Functions | 38 | |
| 18 | Comparison operators | 38 | |
| 19 | Bit Functions | 38 | |
| 20 | Table Maintenance | 35 | |
| 21 | User-Defined Functions | 35 | |
| 22 | Data Types | 35 | |
| 23 | Compound Statements | 35 | |
| 24 | Geometry constructors | 34 | |
| 25 | GeometryCollection properties | 1 | |
| 26 | Administration | 35 | |
| 27 | Data Manipulation | 35 | |
| 28 | Utility | 35 | |
| 29 | Language Structure | 35 | |
| 30 | Geometry relations | 34 | |
| 31 | Date and Time Functions | 38 | |
| 32 | WKB | 34 | |
| 33 | Procedures | 35 | |
| 34 | Geographic Features | 35 | |
| 35 | Contents | 0 | |
| 36 | Geometry properties | 34 | |
| 37 | String Functions | 38 | |
| 38 | Functions | 35 | |
| 39 | Data Definition | 35 | |
+------------------+-----------------------------------------------+--------------------+-----+ALTER TABLE mysql.gtid_slave_pos ENGINE=MyISAM;SELECT * FROM mysql.general_log\G
*************************** 1. row ***************************
event_time: 2014-11-11 08:40:04.117177
user_host: root[root] @ localhost []
thread_id: 74
server_id: 1
command_type: Query
argument: SELECT * FROM test.s
*************************** 2. row ***************************
event_time: 2014-11-11 08:40:10.501131
user_host: root[root] @ localhost []
thread_id: 74
server_id: 1
command_type: Query
argument: SELECT * FROM mysql.general_log
...User (together with Host, Db , Table_name andColumn_name makes up the unique identifier for this record.
Table_name
char(64)
NO
PRI
Table name (together with User, Db , Host andColumn_name makes up the unique identifier for this record.
Column_name
char(64)
NO
PRI
Column name (together with User, Db , Table_name andHost makes up the unique identifier for this record.
Timestamp
timestamp
NO
CURRENT_TIMESTAMP
Column_priv
set('Select', 'Insert', 'Update', 'References')
NO
The privilege type. See Column Privileges for details.
PRI
NULL
This field enables multiple parallel transactions within same domain_id to update this table without contention. At any instant, the replication state corresponds to records with largest sub_id for each domain_id.
server_id
int(10) unsigned
NO
NULL
seq_no
bigint(20) unsigned
NO
NULL
Sequence number, an integer that is monotonically increasing for each new event group logged into the binlog.
Number of records in the table.
Host
char(60)
NO
PRI
Host (together with User and Role makes up the unique identifier for this record.
User
char(80)
NO
PRI
User (together with Host and Role makes up the unique identifier for this record.
Role
char(80)
NO
PRI
Role (together with Host and User makes up the unique identifier for this record.
Admin_option
enum('N','Y')
NO
N
Whether the role can be granted (see the CREATE ROLE WITH ADMIN clause).
Transition_time
bigint(20)
NO
PRI
NULL
Correction
int(11)
NO
NULL
This page is licensed: CC BY-SA / Gnu FDL
System tables should not normally be edited directly. Use the related SQL statements instead.
char(64)
NO
table_name
char(199)
NO
link_id
char(64)
NO
failed_time
timestamp
NO
current_timestamp()
This page is licensed: CC BY-SA / Gnu FDL
db_name
NULL
File
varchar(255)
NO
NULL
epoch
bigint(20) unsigned
NO
PRI
NULL
inserts
bigint(20) unsigned
NO
NULL
updates
bigint(20) unsigned
NO
NULL
deletes
bigint(20) unsigned
NO
NULL
schemaops
bigint(20) unsigned
NO
NULL
This page is licensed: CC BY-SA / Gnu FDL
Position
bigint(20) unsigned
NO
Time_zone_id
int(10) unsigned
NO
PRI
NULL
ID field, auto_increments.
Use_leap_seconds
enum('Y','N')
NO
N
Whether or not leap seconds are used.
Name
char(64)
NO
PRI
NULL
Name of the time zone.
Time_zone_id
int(10) unsigned
NO
PRI
NULL
ID field, auto_increments.
This page is licensed: CC BY-SA / Gnu FDL
System tables should not normally be edited directly. Use the related SQL statements instead.
SELECT * FROM mysql.time_zone;
+--------------+------------------+
| Time_zone_id | Use_leap_seconds |
+--------------+------------------+
| 1 | N |
| 2 | N |
| 3 | N |
| 4 | N |
| 5 | N |
| 6 | N |
| 7 | N |
| 8 | N |
| 9 | N |
| 10 | N |
...
+--------------+------------------+SELECT * FROM mysql.time_zone_name;
+--------------------+--------------+
| Name | Time_zone_id |
+--------------------+--------------+
| Africa/Abidjan | 1 |
| Africa/Accra | 2 |
| Africa/Addis_Ababa | 3 |
| Africa/Algiers | 4 |
| Africa/Asmara | 5 |
| Africa/Asmera | 6 |
| Africa/Bamako | 7 |
| Africa/Bangui | 8 |
| Africa/Banjul | 9 |
| Africa/Bissau | 10 |
...
+--------------------+--------------+Routine_name
char(64)
NO
PRI
Routine_name (together with Host, Db User and Routine_type makes up the unique identifier for this record).
Routine_type
enum('FUNCTION','PROCEDURE', 'PACKAGE', 'PACKAGE BODY')
NO
PRI
NULL
Whether the routine is a , , or .
Grantor
char(141)
NO
MUL
Proc_priv
set('Execute','Alter Routine','Grant')
NO
The routine privilege. See and for details.
Timestamp
timestamp
NO
CURRENT_TIMESTAMP
Host
char(60)
NO
PRI
Host (together with Db, User, Routine_name and Routine_type makes up the unique identifier for this record).
Db
char(64)
NO
PRI
Database (together with Host, User, Routine_name and Routine_type makes up the unique identifier for this record).
User
char(80)
NO
PRI
User (together with Host, Db, Routine_name and Routine_type makes up the unique identifier for this record).
help_topic_id
int(10) unsigned
NO
PRI
NULL
name
char(64)
NO
UNI
NULL
help_category_id
smallint(5) unsigned
NO
NULL
This page is licensed: CC BY-SA / Gnu FDL
database_name
varchar(64)
NO
PRI
NULL
Database name.
table_name
varchar(64)
NO
PRI
NULL
Table, partition or subpartition name.
index_name
varchar(64)
NO
PRI
NULL
This page is licensed: CC BY-SA / Gnu FDL
NULL
commit_id
bigint(20) unsigned
NO
Unique
NULL
begin_timestamp
timestamp(6)
NO
Multiple
0000-00-00 00:00:00.000000
Timestamp when the transaction began (BEGIN statement), however see .
commit
timestamp(6)
NO
Multiple
0000-00-00 00:00:00.000000
Timestamp when the transaction was committed.
isolation_level
enum('READ-UNCOMMITTED','READ-COMMITTED','REPEATABLE-READ','SERIALIZABLE')
NO
NULL
Transaction .
This page is licensed: CC BY-SA / Gnu FDL
transaction_id
bigint(20) unsigned
NO
System tables should not normally be edited directly. Use the related SQL statements instead.
Primary
Time_zone_id
int(10) unsigned
NO
PRI
NULL
Transition_time
bigint(20)
NO
PRI
NULL
Transition_type_id
int(10) unsigned
NO
NULL
This page is licensed: CC BY-SA / Gnu FDL
System tables should not normally be edited directly. Use the related SQL statements instead.
The mysql.event table contains the definitions and scheduling information for events created with the CREATE EVENT statement.
The mysql.event table contains information about MariaDB events. Similar information can be obtained by viewing the INFORMATION_SCHEMA.EVENTS table, or with the SHOW EVENTS and SHOW CREATE EVENT statements.
The table is upgraded live, and there is no need to restart the server if the table has changed.
This table uses the Aria storage engine.
The mysql.event table contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
The mysql.tables_priv table records table-level privileges granted to users, specifying which actions they can perform on specific tables.
System tables should not normally be edited directly. Use the related SQL statements instead.
The mysql.tables_priv table contains information about table-level privileges. The table can be queried and although it is possible to directly update it, it is best to use GRANT for setting privileges.
Note that the MariaDB privileges occur at many levels. A user may be granted a privilege at the table level, but may still not have permission on a database level, for example. See privileges for a more complete view of the MariaDB privilege system.
The INFORMATION_SCHEMA.TABLE_PRIVILEGES table derives its contents from mysql.tables_priv.
This table uses the Aria storage engine.
The mysql.tables_priv table contains the following fields:
The status variable indicates how many rows the mysql.tables_priv table contains.
This page is licensed: CC BY-SA / Gnu FDL
The mysql.spider_xa table tracks the status of XA transactions involving Spider tables, ensuring atomicity across distributed nodes.
The mysql.spider_xa table is installed by the Spider storage engine.
This table uses the Aria storage engine.
It contains the following fields:
format_id
This page is licensed: CC BY-SA / Gnu FDL
The mysql.spider_table_crd table stores cardinality statistics for Spider tables, which the optimizer uses to create efficient query plans.
The mysql.spider_table_crd table is installed by the Spider storage engine.
This table uses the Aria storage engine.
It contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
This table records XA transaction positions for Spider tables, essential for recovering distributed transactions after a crash.
The mysql.spider_table_position_for_recovery table is installed by the Spider storage engine.
This table uses the Aria storage engine.
It contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
SELECT * FROM help_topic\G;
...
*************************** 704. row ***************************
help_topic_id: 692
name: JSON_DEPTH
help_category_id: 41
description: JSON functions were added in MariaDB 10.2.3.
Syntax
------
JSON_DEPTH(json_doc)
Description
-----------
Returns the maximum depth of the given JSON document, or
NULL if the argument is null. An error will occur if the
argument is an invalid JSON document.
Scalar values or empty arrays or objects have a depth of 1.
Arrays or objects that are not empty but contain only
elements or member values of depth 1 will have a depth of 2.
In other cases, the depth will be greater than 2.
Examples
--------
SELECT JSON_DEPTH('[]'), JSON_DEPTH('true'),
JSON_DEPTH('{}');
+------------------+--------------------+------------------+
| JSON_DEPTH('[]') | JSON_DEPTH('true') |
JSON_DEPTH('{}') |
+------------------+--------------------+------------------+
| 1 | 1 | 1 |
+------------------+--------------------+------------------+
SELECT JSON_DEPTH('[1, 2, 3]'), JSON_DEPTH('[[], {},
[]]');
+-------------------------+----------------------------+
| JSON_DEPTH('[1, 2, 3]') | JSON_DEPTH('[[], {}, []]') |
+-------------------------+----------------------------+
| 2 | 2 |
+-------------------------+----------------------------+
SELECT JSON_DEPTH('[1, 2, [3, 4, 5, 6], 7]');
+---------------------------------------+
| JSON_DEPTH('[1, 2, [3, 4, 5, 6], 7]') |
+---------------------------------------+
| 3 |
+---------------------------------------+
URL: https://mariadb.com/kb/en/json_depth/
example:
url: https://mariadb.com/kb/en/json_depth/SELECT * FROM mysql.innodb_index_stats\G
*************************** 1. row ***************************
database_name: mysql
table_name: gtid_slave_pos
index_name: PRIMARY
last_update: 2017-08-19 20:38:34
stat_name: n_diff_pfx01
stat_value: 0
sample_size: 1
stat_description: domain_id
*************************** 2. row ***************************
database_name: mysql
table_name: gtid_slave_pos
index_name: PRIMARY
last_update: 2017-08-19 20:38:34
stat_name: n_diff_pfx02
stat_value: 0
sample_size: 1
stat_description: domain_id,sub_id
*************************** 3. row ***************************
database_name: mysql
table_name: gtid_slave_pos
index_name: PRIMARY
last_update: 2017-08-19 20:38:34
stat_name: n_leaf_pages
stat_value: 1
sample_size: NULL
stat_description: Number of leaf pages in the index
*************************** 4. row ***************************
database_name: mysql
table_name: gtid_slave_pos
index_name: PRIMARY
last_update: 2017-08-19 20:38:34
stat_name: size
stat_value: 1
sample_size: NULL
stat_description: Number of pages in the index
*************************** 5. row ***************************
database_name: test
table_name: ft
index_name: FTS_DOC_ID_INDEX
last_update: 2017-09-15 12:58:39
stat_name: n_diff_pfx01
stat_value: 0
sample_size: 1
stat_description: FTS_DOC_ID
*************************** 6. row ***************************
database_name: test
table_name: ft
index_name: FTS_DOC_ID_INDEX
last_update: 2017-09-15 12:58:39
stat_name: n_leaf_pages
stat_value: 1
sample_size: NULL
stat_description: Number of leaf pages in the index
...SELECT * FROM mysql.time_zone_transition;
+--------------+-----------------+--------------------+
| Time_zone_id | Transition_time | Transition_type_id |
+--------------+-----------------+--------------------+
| 1 | -1830383032 | 1 |
| 2 | -1640995148 | 2 |
| 2 | -1556841600 | 1 |
| 2 | -1546388400 | 2 |
| 2 | -1525305600 | 1 |
| 2 | -1514852400 | 2 |
| 2 | -1493769600 | 1 |
| 2 | -1483316400 | 2 |
| 2 | -1462233600 | 1 |
| 2 | -1451780400 | 2 |
...
+--------------+-----------------+--------------------+description
text
NO
NULL
example
text
NO
NULL
url
char(128)
NO
NULL
Index name.
last_update
timestamp
NO
current_timestamp()
Time that this row was last updated.
stat_name
varchar(64)
NO
PRI
NULL
Statistic name.
stat_value
bigint(20) unsigned
NO
NULL
Estimated statistic value.
sample_size
bigint(20) unsigned
YES
NULL
Number of pages sampled for the estimated statistic value.
stat_description
varchar(1024)
NO
NULL
Statistic description.
int(11)
NO
PRI
0
gtrid_length
int(11)
NO
PRI
0
bqual_length
int(11)
NO
0
data
binary(128)
NO
PRI
status
char(8)
NO
MUL
db_name
char(64)
NO
PRI
table_name
char(199)
NO
PRI
key_seq
int(10) unsigned
NO
PRI
0
cardinality
bigint(20)
NO
0
db_name
char(64)
NO
PRI
table_name
char(199)
NO
PRI
failed_link_id
int(11)
NO
PRI
0
source_link_id
int(11)
NO
PRI
0
file
text
YES
NULL
position
text
YES
NULL
gtid
text
YES
NULL
definer
char(141)
NO
execute_at
datetime
YES
NULL
interval_value
int(11)
YES
NULL
interval_field
enum('YEAR', 'QUARTER', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'WEEK', 'SECOND', 'MICROSECOND', 'YEAR_MONTH', 'DAY_HOUR', 'DAY_MINUTE', 'DAY_SECOND', 'HOUR_MINUTE', 'HOUR_SECOND', 'MINUTE_SECOND', 'DAY_MICROSECOND', 'HOUR_MICROSECOND', 'MINUTE_MICROSECOND', 'SECOND_MICROSECOND')
YES
NULL
created
timestamp
NO
CURRENT_TIMESTAMP
modified
timestamp
NO
0000-00-00 00:00:00
last_executed
datetime
YES
NULL
starts
datetime
YES
NULL
ends
datetime
YES
NULL
status
enum('ENABLED', 'DISABLED', 'SLAVESIDE_DISABLED')
NO
ENABLED
Current status of the event, one of enabled, disabled, or disabled on the slaveside.
on_completion
enum('DROP','PRESERVE')
NO
DROP
sql_mode
set('REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH')
NO
The at the time the event was created.
comment
char(64)
NO
originator
int(10) unsigned
NO
NULL
time_zone
char(64)
NO
SYSTEM
character_set_client
char(32)
YES
NULL
collation_connection
char(32)
YES
NULL
db_collation
char(32)
YES
NULL
body_utf8
longblob
YES
NULL
db
char(64)
NO
PRI
name
char(64)
NO
PRI
body
longblob
NO
NULL
Table_name
char(64)
NO
PRI
Table name (together with User, Db and Tablemakes up the unique identifier for this record.
Grantor
char(141)
NO
MUL
Timestamp
timestamp
NO
CURRENT_TIMESTAMP
Table_priv
set('Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter', 'Create View', 'Show view', 'Trigger', 'Delete versioning rows')
NO
The table privilege type. See for details.
Column_priv
set('Select', 'Insert', 'Update', 'References')
NO
The column privilege type. See for details.
Host
char(60)
NO
PRI
Host (together with User, Db and Table_namemakes up the unique identifier for this record.
Db
char(64)
NO
PRI
Database (together with User, Host and Table_namemakes up the unique identifier for this record.
User
char(80)
NO
PRI
User (together with Host, Db and Table_namemakes up the unique identifier for this record.
The mysql.slow_log table records details of queries that exceed the long_query_time threshold when slow query logging to tables is enabled.
System tables should not normally be edited directly. Use the related SQL statements instead.
The mysql.slow_log table stores the contents of the Slow Query Log if slow logging is active and the output is being written to table (see Writing logs into tables).
It contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
The mysql.global_priv table stores global privileges and account properties for all users, replacing the older mysql.user table structure.
The mysql.global_priv table contains information about users that have permission to access the MariaDB server, and their global privileges.
Note that the MariaDB privileges occur at many levels. A user may not be granted CREATE privilege at the user level, but may still have CREATE permission on certain tables or databases, for example. See for a more complete view of the MariaDB privilege system.
The mysql.global_priv table contains the following fields:
The mysql.proc table stores the definitions, body, and metadata for stored procedures and functions created on the server.
The mysql.proc table contains information about and . It contains similar information to that stored in the table.
This table uses the storage engine.
The mysql.proc table contains the following fields:
The mysql.servers table stores connection information for remote servers, used by the FEDERATED and Spider storage engines.
The mysql.servers table contains information about servers as used by the , or , storage engines.
The contents are modified by the , and statements.
This table uses the storage engine.
The mysql.servers table contains the following fields:
Host
char(60)
NO
PRI
Host (together with User makes up the unique identifier for this account).
User
char(80)
NO
PRI
User (together with Host makes up the unique identifier for this account).
Priv
longtext
NO
From , in order to help the server understand which version a privilege record was written by, the priv field contains a new JSON field, version_id (MDEV-21704).
Readable format:
A particular user:
From :
The access field contains the grants of the user which can be mapped to individual grants with the following table. The most up-to-date information can be found in the sql/privilege.h file in the source code.
SELECT
(1UL << 0)
INSERT
(1UL << 1)
UPDATE
(1UL << 2)
DELETE
(1UL << 3)
CREATE
(1UL << 4)
DROP
(1UL << 5)
This page is licensed: CC BY-SA / Gnu FDL
db
char(64)
NO
PRI
Database name.
name
char(64)
NO
PRI
Routine name.
type
enum('FUNCTION','PROCEDURE','PACKAGE', 'PACKAGE BODY')
NO
PRI
NULL
This page is licensed: CC BY-SA / Gnu FDL
Server_name
char(64)
NO
PRI
Host
char(64)
NO
Db
char(64)
NO
This page is licensed: CC BY-SA / Gnu FDL
System tables should not normally be edited directly. Use the related SQL statements instead.
start_time
timestamp(6)
NO
CURRENT_TIMESTAMP(6)
Time the query began.
user_host
mediumtext
NO
NULL
User and host combination.
query_time
time(6)
NO
NULL
Total time the query took to execute.
lock_time
time(6)
NO
NULL
Total time the query was locked.
rows_sent
int(11)
NO
NULL
Number of rows sent.
rows_examined
int(11)
NO
NULL
Number of rows examined.
db
varchar(512)
NO
NULL
Default database.
last_insert_id
int(11)
NO
NULL
insert_id
int(11)
NO
NULL
Insert id.
server_id
int(10) unsigned
NO
NULL
The server's id.
sql_text
mediumtext
NO
NULL
Full query.
thread_id
bigint(21) unsigned
NO
NULL
Thread id.
rows_affected
int(11)
NO
NULL
The mysql.db table stores database-level privileges, determining which users have access to specific databases and what actions they can perform.
The mysql.db table contains information about database-level privileges. The table can be queried and although it is possible to directly update it, it is best to use GRANT for setting privileges.
Note that the MariaDB privileges occur at many levels. A user may not be granted a privilege at the database level, but may still have permission on a table level, for example. See privileges for a more complete view of the MariaDB privilege system.
This table uses the Aria storage engine.
The mysql.db table contains the following fields:
The status variable indicates how many rows the mysql.db table contains.
This page is licensed: CC BY-SA / Gnu FDL
The mysql.spider_xa_member table lists the member nodes participating in a distributed XA transaction managed by the Spider engine.
The mysql.spider_xa_member table is installed by the Spider storage engine.
This table uses the Aria storage engine.
It contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
The mysql.proxies_priv table manages proxy user privileges, defining which accounts are authorized to proxy as other users.
System tables should not normally be edited directly. Use the related SQL statements instead.
The mysql.proxies_priv table contains information about proxy privileges. The table can be queried and although it is possible to directly update it, it is best to use GRANT for setting privileges.
This table uses the Aria storage engine.
The mysql.proxies_priv table contains the following fields:
The status variable indicates how many rows the mysql.proxies_priv table contains.
This page is licensed: CC BY-SA / Gnu FDL
This table logs details of failed XA transactions in the Spider storage engine, providing a record for troubleshooting distributed transaction errors.
The mysql.spider_xa_failed_log table is installed by the .
This table uses the storage engine.
It contains the following fields:
SELECT * FROM mysql.global_priv;
+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| Host | User | Priv |
+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------+
| localhost | root | {"access": 18446744073709551615,"plugin":"mysql_native_password","authentication_string":"*6C387FC3893DBA1E3BA155E74754DA6682D04747"} |
| 127.% | msandbox | {"access":1073740799,"plugin":"mysql_native_password","authentication_string":"*6C387FC3893DBA1E3BA155E74754DA6682D04747"} |
| localhost | msandbox | {"access":1073740799,"plugin":"mysql_native_password","authentication_string":"*6C387FC3893DBA1E3BA155E74754DA6682D04747"} |
| localhost | msandbox_rw | {"access":487487,"plugin":"mysql_native_password","authentication_string":"*6C387FC3893DBA1E3BA155E74754DA6682D04747"} |
| 127.% | msandbox_rw | {"access":487487,"plugin":"mysql_native_password","authentication_string":"*6C387FC3893DBA1E3BA155E74754DA6682D04747"} |
| 127.% | msandbox_ro | {"access":262145,"plugin":"mysql_native_password","authentication_string":"*6C387FC3893DBA1E3BA155E74754DA6682D04747"} |
| localhost | msandbox_ro | {"access":262145,"plugin":"mysql_native_password","authentication_string":"*6C387FC3893DBA1E3BA155E74754DA6682D04747"} |
| 127.% | rsandbox | {"access":524288,"plugin":"mysql_native_password","authentication_string":"*B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD"} |
+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------+SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv;
+--------------------------------------------------------------------------------------+
| CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) |
+--------------------------------------------------------------------------------------+
| root@localhost => {
"access": 18446744073709551615,
"plugin": "mysql_native_password",
"authentication_string": "*6C387FC3893DBA1E3BA155E74754DA6682D04747"
} |
| msandbox@127.% => {
"access": 1073740799,
"plugin": "mysql_native_password",
"authentication_string": "*6C387FC3893DBA1E3BA155E74754DA6682D04747"
} |
+--------------------------------------------------------------------------------------+SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv
WHERE user='marijn';
+--------------------------------------------------------------------------------------+
| CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) |
+--------------------------------------------------------------------------------------+
| marijn@localhost => {
"access": 0,
"plugin": "mysql_native_password",
"authentication_string": "",
"account_locked": true,
"password_last_changed": 1558017158
} |
+--------------------------------------------------------------------------------------+GRANT FILE ON *.* TO user1@localhost;
SELECT Host, User, JSON_DETAILED(Priv) FROM mysql.global_priv WHERE user='user1'\G
*************************** 1. row ***************************
Host: localhost
User: user1
JSON_DETAILED(Priv): {
"access": 512,
"plugin": "mysql_native_password",
"authentication_string": "",
"password_last_changed": 1581070979,
"version_id": 100502
}SELECT * FROM mysql.servers\G
*************************** 1. row ***************************
Server_name: s
Host: 192.168.1.106
Db: test
Username: Remote
Password:
Port: 0
Socket:
Wrapper: mariadb
Owner:SELECT * FROM mysql.slow_log\G
...
*************************** 2. row ***************************
start_time: 2014-11-11 07:56:28.721519
user_host: root[root] @ localhost []
query_time: 00:00:12.000215
lock_time: 00:00:00.000000
rows_sent: 1
rows_examined: 0
db: test
last_insert_id: 0
insert_id: 0
server_id: 1
sql_text: SELECT SLEEP(12)
thread_id: 74
...Username
char(80)
NO
Password
char(64)
NO
Port
int(4)
NO
0
Socket
char(64)
NO
Wrapper
char(64)
NO
mysql or mariadb
Owner
char(64)
NO
format_id
int(11)
NO
0
gtrid_length
int(11)
NO
0
bqual_length
int(11)
NO
0
data
binary(128)
NO
MUL
scheme
char(64)
NO
host
char(64)
NO
port
char(5)
NO
socket
text
NO
NULL
username
char(64)
NO
password
char(64)
NO
ssl_ca
text
YES
NULL
ssl_capath
text
YES
NULL
ssl_cert
text
YES
NULL
ssl_cipher
char(64)
YES
NULL
ssl_key
text
YES
NULL
ssl_verify_server_cert
tinyint(4)
NO
0
default_file
text
YES
NULL
default_group
char(64)
YES
NULL
dsn
char(64)
YES
NULL
filedsn
text
YES
NULL
driver
char(64)
YES
NULL
Proxied_user
char(80)
NO
PRI
With_grant
tinyint(1)
NO
0
Grantor
char(141)
NO
MUL
Timestamp
timestamp
NO
CURRENT_TIMESTAMP
Host
char(60)
NO
PRI
User
char(80)
NO
PRI
Proxied_host
char(60)
NO
PRI
int(11)
NO
0
gtrid_length
int(11)
NO
0
bqual_length
int(11)
NO
0
data
binary(128)
NO
MUL
scheme
char(64)
NO
host
char(64)
NO
port
char(5)
NO
socket
text
NO
NULL
username
char(64)
NO
password
char(64)
NO
ssl_ca
text
YES
NULL
ssl_capath
text
YES
NULL
ssl_cert
text
YES
NULL
ssl_cipher
char(64)
YES
NULL
ssl_key
text
YES
NULL
ssl_verify_server_cert
tinyint(4)
NO
0
default_file
text
YES
NULL
default_group
char(64)
YES
NULL
dsn
char(64)
YES
NULL
filedsn
text
YES
NULL
driver
char(64)
YES
NULL
thread_id
int(11)
YES
NULL
status
char(8)
NO
failed_time
timestamp
NO
current_timestamp()
This page is licensed: CC BY-SA / Gnu FDL
format_id
Select_priv
enum('N','Y')
NO
N
Can perform statements.
Insert_priv
enum('N','Y')
NO
N
Can perform statements.
Update_priv
enum('N','Y')
NO
N
Can perform statements.
Delete_priv
enum('N','Y')
NO
N
Can perform statements.
Create_priv
enum('N','Y')
NO
N
Can .
Drop_priv
enum('N','Y')
NO
N
Can or .
Grant_priv
enum('N','Y')
NO
N
User can privileges they possess.
References_priv
enum('N','Y')
NO
N
Unused
Index_priv
enum('N','Y')
NO
N
Can create an index on a table using the statement. Without the INDEX privilege, user can still create indexes when creating a table using the statement if the user has have the CREATE privilege, and user can create indexes using the statement if they have the ALTER privilege.
Alter_priv
enum('N','Y')
NO
N
Can perform statements.
Create_tmp_table_priv
enum('N','Y')
NO
N
Can create temporary tables with the statement.
Lock_tables_priv
enum('N','Y')
NO
N
Acquire explicit locks using the statement; user also needs to have the SELECT privilege on a table in order to lock it.
Create_view_priv
enum('N','Y')
NO
N
Can create a view using the statement.
Show_view_priv
enum('N','Y')
NO
N
Can show the statement to create a view using the statement.
Create_routine_priv
enum('N','Y')
NO
N
Can create stored programs using the and statements.
Alter_routine_priv
enum('N','Y')
NO
N
Can change the characteristics of a stored function using the statement.
Execute_priv
enum('N','Y')
NO
N
Can execute or functions.
Event_priv
enum('N','Y')
NO
N
Create, drop and alter .
Trigger_priv
enum('N','Y')
NO
N
Can execute associated with tables the user updates, execute the and statements.
Delete_history_priv
enum('N','Y')
NO
N
Can delete rows created through .
Host
char(60)
NO
PRI
Host (together with User and Db makes up the unique identifier for this record. Until MariaDB 5.5, if the host field was blank, the corresponding record in the mysql.host table would be examined. From MariaDB 10.0, a blank host field is the same as the % wildcard.
Db
char(64)
NO
PRI
Database (together with User and Host makes up the unique identifier for this record.
User
char(80)
NO
PRI
User (together with Host and Db makes up the unique identifier for this record.
Global privileges, granted to the account and other account properties
RELOAD
(1UL << 6)
SHUTDOWN
(1UL << 7)
PROCESS
(1UL << 8)
FILE
(1UL << 9)
GRANT
(1UL << 10)
REFERENCES
(1UL << 11)
INDEX
(1UL << 12)
ALTER
(1UL << 13)
SHOW_DB
(1UL << 14)
SUPER
(1UL << 15)
CREATE_TMP
(1UL << 16)
LOCK_TABLES
(1UL << 17)
EXECUTE
(1UL << 18)
REPL_SLAVE
(1UL << 19)
BINLOG_MONITOR
(1UL << 20)
CREATE_VIEW
(1UL << 21)
SHOW_VIEW
(1UL << 22)
CREATE_PROC
(1UL << 23)
ALTER_PROC
(1UL << 24)
CREATE_USER
(1UL << 25)
EVENT
(1UL << 26)
TRIGGER
(1UL << 27)
CREATE_TABLESPACE
(1UL << 28)
DELETE_HISTORY
(1UL << 29)
SET_USER
(1UL << 30)
FEDERATED_ADMIN
(1UL << 31)
CONNECTION_ADMIN
(1ULL << 32)
READ_ONLY_ADMIN
(1ULL << 33)
REPL_SLAVE_ADMIN
(1ULL << 34)
REPL_MASTER_ADMIN
(1ULL << 35)
BINLOG_ADMIN
(1ULL << 36)
BINLOG_REPLAY
(1ULL << 37)
SLAVE_MONITOR
(1ULL << 38)
SHOW_CREATE_ROUTINE
(1ULL << 39)
Whether stored procedure, stored function or a package or package body.
specific_name
char(64)
NO
language
enum('SQL')
NO
SQL
Always SQL.
sql_data_access
enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA')
NO
CONTAINS_SQL
is_deterministic
enum('YES','NO')
NO
NO
Whether the routine is deterministic (can produce only one result for a given list of parameters) or not.
security_type
enum('INVOKER','DEFINER')
NO
DEFINER
INVOKER or DEFINER. Indicates which user's privileges apply to this routine.
param_list
blob
NO
NULL
List of parameters.
returns
longblob
NO
NULL
What the routine returns.
body
longblob
NO
NULL
Definition of the routine.
definer
char(141)
NO
If the security_type is DEFINER, this value indicates which user defined this routine.
created
timestamp
NO
CURRENT_TIMESTAMP
Date and time the routine was created.
modified
timestamp
NO
0000-00-00 00:00:00
Date and time the routine was modified.
sql_mode
set('REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL', 'SIMULTANEOUS_ASSIGNMENT')
NO
The SQL_MODE at the time the routine was created.
comment
text
NO
NULL
Comment associated with the routine.
character_set_client
char(32)
YES
NULL
The character set used by the client that created the routine.
collation_connection
char(32)
YES
NULL
The collation (and character set) used by the connection that created the routine.
db_collation
char(32)
YES
NULL
The default collation (and character set) for the database, at the time the routine was created.
body_utf8
longblob
YES
NULL
Definition of the routine in utf8.
aggregate
enum('NONE', 'GROUP')
NO
NONE
Field
Type
Null
Key
Default
Description
This table defines the remote servers used for link monitoring in the Spider storage engine, ensuring high availability and failover handling.
The mysql.spider_link_mon_servers table is installed by the Spider storage engine.
This table uses the Aria storage engine.
It contains the following fields:
This page is licensed: CC BY-SA / Gnu FDL
This table stores specific parameters and metadata for Spider tables, defining how they map to remote backend tables.
The mysql.spider_tables table is installed by the .
This table uses the storage engine.
It contains the following fields:
The mysql.time_zone_transition_type table describes the properties of time zone transitions, such as the offset and abbreviation.
The mysql.time_zone_transition_type table is one of the mysql system tables that can contain information. It is usually preferable for the system to handle the time zone, in which case the table will be empty (the default), but you can populate the mysql time zone tables using the utility. See for details.
This table uses the storage engine.
The mysql.time_zone_transition_type table contains the following fields:
The mysql.spider_table_sts table holds statistics such as row counts and data length for Spider tables, supporting the optimizer.
The mysql.spider_table_sts table is installed by the .
This table uses the storage engine.
It contains the following fields:
db_name
char(64)
NO
PRI
table_name
char(199)
NO
PRI
link_id
char(64)
NO
PRI
sid
int(10) unsigned
NO
PRI
0
server
char(64)
YES
NULL
scheme
char(64)
YES
NULL
host
char(64)
YES
NULL
port
char(5)
YES
NULL
socket
text
YES
NULL
username
char(64)
YES
NULL
password
char(64)
YES
NULL
ssl_ca
text
YES
NULL
ssl_capath
text
YES
NULL
ssl_cert
text
YES
NULL
ssl_cipher
char(64)
YES
NULL
ssl_key
text
YES
NULL
ssl_verify_server_cert
tinyint(4)
NO
0
default_file
text
YES
NULL
default_group
char(64)
YES
NULL
dsn
char(64)
YES
NULL
filedsn
text
YES
NULL
driver
char(64)
YES
NULL
NO
PRI
table_name
char(199)
NO
PRI
link_id
int(11)
NO
PRI
0
priority
bigint(20)
NO
MUL
0
server
char(64)
YES
NULL
scheme
char(64)
YES
NULL
host
char(64)
YES
NULL
port
char(5)
YES
NULL
socket
text
YES
NULL
username
char(64)
YES
NULL
password
char(64)
YES
NULL
ssl_ca
text
YES
NULL
ssl_capath
text
YES
NULL
ssl_cert
text
YES
NULL
ssl_cipher
char(64)
YES
NULL
ssl_key
text
YES
NULL
ssl_verify_server_cert
tinyint(4)
NO
0
monitoring_binlog_pos_at_failing
tinyint(4)
NO
0
default_file
text
YES
NULL
default_group
char(64)
YES
NULL
dsn
char(64)
YES
NULL
filedsn
text
YES
NULL
driver
char(64)
YES
NULL
tgt_db_name
char(64)
YES
NULL
tgt_table_name
char(64)
YES
NULL
link_status
tinyint(4)
NO
1
block_status
tinyint(4)
NO
0
static_link_id
char(64)
YES
NULL
This page is licensed: CC BY-SA / Gnu FDL
db_name
char(64)
char(64)
NO
PRI
table_name
char(199)
NO
PRI
data_file_length
bigint(20) unsigned
NO
0
max_data_file_length
bigint(20) unsigned
NO
0
index_file_length
bigint(20) unsigned
NO
0
records
bigint(20) unsigned
NO
0
mean_rec_length
bigint(20) unsigned
NO
0
check_time
datetime
NO
0000-00-00 00:00:00
create_time
datetime
NO
0000-00-00 00:00:00
update_time
datetime
NO
0000-00-00 00:00:00
checksum
bigint(20) unsigned
YES
NULL
This page is licensed: CC BY-SA / Gnu FDL
db_name
Time_zone_id
int(10) unsigned
NO
PRI
NULL
Transition_type_id
int(10) unsigned
NO
PRI
NULL
Offset
int(11)
NO
0
This page is licensed: CC BY-SA / Gnu FDL
System tables should not normally be edited directly. Use the related SQL statements instead.
The mysql.host table contained information about hosts and their related privileges. When determining permissions, if a matching record in the mysql.db table had a blank host value, the mysql.host table would be examined.
This table is not affected by any GRANT statements, and had to be updated manually.
This table is no longer used.
See privileges for a more complete view of the MariaDB privilege system.
This table is no longer created. However if the table is created it will be used.
The mysql.host table contains the following fields:
If you need the functionality to only allow access to your database from a given set of hosts, you can create the host table with the following command:
This page is licensed: CC BY-SA / Gnu FDL
SELECT * FROM mysql.time_zone_transition_type;
+--------------+--------------------+--------+--------+--------------+
| Time_zone_id | Transition_type_id | Offset | Is_DST | Abbreviation |
+--------------+--------------------+--------+--------+--------------+
| 1 | 0 | -968 | 0 | LMT |
| 1 | 1 | 0 | 0 | GMT |
| 2 | 0 | -52 | 0 | LMT |
| 2 | 1 | 1200 | 1 | GHST |
| 2 | 2 | 0 | 0 | GMT |
| 3 | 0 | 8836 | 0 | LMT |
| 3 | 1 | 10800 | 0 | EAT |
| 3 | 2 | 9000 | 0 | BEAT |
| 3 | 3 | 9900 | 0 | BEAUT |
| 3 | 4 | 10800 | 0 | EAT |
...
+--------------+--------------------+--------+--------+--------------+Is_DST
tinyint(3) unsigned
NO
0
Abbreviation
char(8)
NO
Database (together with Host makes up the unique identifier for this record.
Select_priv
enum('N','Y')
NO
N
Can perform statements.
Insert_priv
enum('N','Y')
NO
N
Can perform statements.
Update_priv
enum('N','Y')
NO
N
Can perform statements.
Delete_priv
enum('N','Y')
NO
N
Can perform statements.
Create_priv
enum('N','Y')
NO
N
Can .
Drop_priv
enum('N','Y')
NO
N
Can or .
Grant_priv
enum('N','Y')
NO
N
User can privileges they possess.
References_priv
enum('N','Y')
NO
N
Unused
Index_priv
enum('N','Y')
NO
N
Can create an index on a table using the statement. Without the INDEX privilege, user can still create indexes when creating a table using the statement if the user has have the CREATE privilege, and user can create indexes using the statement if they have the ALTER privilege.
Alter_priv
enum('N','Y')
NO
N
Can perform statements.
Create_tmp_table_priv
enum('N','Y')
NO
N
Can create temporary tables with the statement.
Lock_tables_priv
enum('N','Y')
NO
N
Acquire explicit locks using the statement; user also needs to have the SELECT privilege on a table in order to lock it.
Create_view_priv
enum('N','Y')
NO
N
Can create a view using the statement.
Show_view_priv
enum('N','Y')
NO
N
Can show the statement to create a view using the statement.
Create_routine_priv
enum('N','Y')
NO
N
Can create stored programs using the and statements.
Alter_routine_priv
enum('N','Y')
NO
N
Can change the characteristics of a stored function using the statement.
Execute_priv
enum('N','Y')
NO
N
Can execute or functions.
Trigger_priv
enum('N','Y')
NO
N
Can execute associated with tables the user updates, execute the and statements.
Host
char(60)
NO
PRI
Host (together with Db makes up the unique identifier for this record.
Db
char(64)
NO
PRI
CREATE TABLE IF NOT EXISTS mysql.host (HOST CHAR(60) BINARY DEFAULT '' NOT NULL,
Db CHAR(64) BINARY DEFAULT '' NOT NULL,
Select_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Insert_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Update_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Delete_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Create_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Drop_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Grant_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
References_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Index_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Alter_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Create_tmp_table_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Lock_tables_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Create_view_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Show_view_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Create_routine_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Alter_routine_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Execute_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Trigger_priv ENUM('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
PRIMARY KEY /*Host*/ (HOST,Db) )
ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin
COMMENT='Host privileges; Merged with database privileges';The mysql.user table stores user account information, global privileges, authentication details, and resource limits.
System tables should not normally be edited directly. Use the related SQL statements instead.
The mysql.global_priv table has replaced the mysql.user table, and mysql.user should be considered obsolete. It is now a view into mysql.global_priv created for compatibility with older applications and monitoring scripts. New tools are supposed to use INFORMATION_SCHEMA tables. The dedicated mariadb.sys user is created as the definer of the view. Previously, root was the definer, which resulted in privilege problems when this username was changed (MDEV-19650).
The mysql.user table contains information about users that have permission to access the MariaDB server, and their global privileges. The table can be queried and although it is possible to directly update it, it is best to use GRANT and CREATE USER for adding users and privileges.
Note that the MariaDB privileges occur at many levels. A user may not be granted create privilege at the user level, but may still have create permission on certain tables or databases, for example. See for a more complete view of the MariaDB privilege system.
The mysql.user table contains the following fields:
The status variable indicates how many rows the mysql.user table contains where is_role='Y'.
The status variable, indicates how many rows the mysql.user table contains where is_role='N'.
When the plugin column is empty, MariaDB defaults to authenticating accounts with either the or the plugins. It decides which based on the hash used in the value for the Password column. When there's no password set or when the 4.1 password hash is used, (which is 41 characters long), MariaDB uses the plugin. The plugin is used with pre-4.1 password hashes, (which are 16 characters long).
MariaDB also supports the use of alternative . When the plugin column is not empty for the given account, MariaDB uses it to authenticate connection attempts. The specific plugin then uses the value of either the Password column or the authentication_string column to authenticate the user.
A specific authentication plugin can be used for an account by providing the IDENTIFIED VIA authentication_plugin clause with the , , or statements.
For example, the following statement would create an account that authenticates with the :
If the specific authentication plugin uses the authentication_string column, then this value for the account can be specified after a USING or AS keyword. For example, the accepts a that would go into the authentication_string column for the account:
This page is licensed: CC BY-SA / Gnu FDL
User (together with Host makes up the unique identifier for this account.
Password
longtext
NO
Hashed password, generated by the function.
Select_priv
enum('N','Y')
NO
N
Can perform statements.
Insert_priv
enum('N','Y')
NO
N
Can perform statements.
Update_priv
enum('N','Y')
NO
N
Can perform statements.
Delete_priv
enum('N','Y')
NO
N
Can perform statements.
Create_priv
enum('N','Y')
NO
N
Can or .
Drop_priv
enum('N','Y')
NO
N
Can or .
Reload_priv
enum('N','Y')
NO
N
Can execute statements or equivalent commands.
Shutdown_priv
enum('N','Y')
NO
N
Can shut down the server with or .
Process_priv
enum('N','Y')
NO
N
Can show information about active processes, via or .
File_priv
enum('N','Y')
NO
N
Read and write files on the server, using statements like or functions like . Also needed to create outward tables. MariaDB server must have permission to access those files.
Grant_priv
enum('N','Y')
NO
N
User can privileges they possess.
References_priv
enum('N','Y')
NO
N
Unused
Index_priv
enum('N','Y')
NO
N
Can create an index on a table using the statement. Without the INDEX privilege, user can still create indexes when creating a table using the statement if the user has have the CREATE privilege, and user can create indexes using the statement if they have the ALTER privilege.
Alter_priv
enum('N','Y')
NO
N
Can perform statements.
Show_db_priv
enum('N','Y')
NO
N
Can list all databases using the statement. Without the SHOW DATABASES privilege, user can still issue the SHOW DATABASES statement, but it will only list databases containing tables on which they have privileges.
Super_priv
enum('N','Y')
NO
N
Can execute superuser statements: , (users who do not have this privilege can only KILL their own threads), , , or the command. Also, this permission allows the user to write data even if the startup option is set, enable or disable logging, enable or disable replication on slaves, specify a DEFINER for statements that support that clause, connect once after reaching the MAX_CONNECTIONS. If a statement has been specified for the mysqld option, that command will not be executed when a user with SUPER privileges connects to the server.
Create_tmp_table_priv
enum('N','Y')
NO
N
Can create temporary tables with the statement.
Lock_tables_priv
enum('N','Y')
NO
N
Acquire explicit locks using the statement; user also needs to have the SELECT privilege on a table in order to lock it.
Execute_priv
enum('N','Y')
NO
N
Can execute or functions.
Repl_slave_priv
enum('N','Y')
NO
N
Accounts used by slave servers on the master need this privilege. This is needed to get the updates made on the master.
Repl_client_priv
enum('N','Y')
NO
N
Can execute and statements.
Create_view_priv
enum('N','Y')
NO
N
Can create a view using the statement.
Show_view_priv
enum('N','Y')
NO
N
Can show the statement to create a view using the statement.
Create_routine_priv
enum('N','Y')
NO
N
Can create stored programs using the and statements.
Alter_routine_priv
enum('N','Y')
NO
N
Can change the characteristics of a stored function using the statement.
Create_user_priv
enum('N','Y')
NO
N
Can create a user using the statement, or implicitly create a user with the statement.
Event_priv
enum('N','Y')
NO
N
Create, drop and alter .
Trigger_priv
enum('N','Y')
NO
N
Can execute associated with tables the user updates, execute the and statements.
Create_tablespace_priv
enum('N','Y')
NO
N
Delete_history_priv
enum('N','Y')
NO
N
Can delete rows created through .
ssl_type
enum('', 'ANY', 'X509', 'SPECIFIED')
NO
TLS type - see .
ssl_cipher
blob
NO
NULL
TLS cipher - see .
x509_issuer
blob
NO
NULL
X509 cipher - see .
x509_subject
blob
NO
NULL
SSL subject - see .
max_questions
int(11) unsigned
NO
0
Number of queries the user can perform per hour. Zero is unlimited. See .
max_updates
int(11) unsigned
NO
0
Number of updates the user can perform per hour. Zero is unlimited. See .
max_connections
int(11) unsigned
NO
0
Number of connections the account can start per hour. Zero is unlimited. See .
max_user_connections
int(11)
NO
0
Number of simultaneous connections the account can have. Zero is unlimited. See .
plugin
char(64)
NO
Authentication plugin used on connection. If empty, uses the .
authentication_string
text
NO
NULL
Authentication string for the authentication plugin.
password_expired
enum('N','Y')
NO
N
MySQL-compatibility option, not implemented in MariaDB.
is_role
enum('N','Y')
NO
N
Whether the user is a .
default_role
char(80)
NO
N
Role which will be enabled on user login automatically.
max_statement_time
decimal(12,6)
NO
0.000000
If non-zero, how long queries can run before being killed automatically.
Field
Type
Null
Key
Default
Description
Host
char(60)
NO
PRI
Host (together with User makes up the unique identifier for this account.
User
char(80)
NO
PRI
CREATE USER foo2@test IDENTIFIED VIA pam;CREATE USER foo2@test IDENTIFIED VIA pam USING 'mariadb';