Outdated Version
You are viewing an older version of this section. View current production version.
SHOW DATABASES
Shows the list of databases that exist on this SingleStore DB instance.
Syntax
SHOW DATABASES [EXTENDED]
[LIKE 'pattern']
Remarks
- The
EXTENDEDsuffix is a SingleStore DB-only extension that displays extra information about each database. - This command must be run on the master aggregator or a child aggregator node (see Node Requirements for SingleStore DB Commands).
Output (EXTENDED option)
| Column | Description |
|---|---|
Database |
Name of a database in the SingleStore DB instance |
Commits |
Number of committed transactions |
Role |
Database replication role |
State |
Current state (Database States) |
Position |
Current position in the transaction log in memory, formatted as <LogFileId>:<ByteOffsetInLogFile> |
Details |
Optional message about the current state |
AsyncSlaves |
Number of currently attached asynchronous replicas |
SyncSlaves |
Number of currently attached synchronous replicas |
ConsensusSlaves |
Number of currently attached replicas running in consensus mode |
CommittedPosition |
Position up to which each transaction has been committed |
HardenedPosition |
Current position in the transaction log on disk |
ReplayPosition |
Position to which changes have been applied to replica |
Term |
A unique identifier of the current master instance of the database |
LastPageTerm |
Term of the master that wrote the contents of the most recent page in the log that the secondary instance is aware of the database |
Memory (MBs) |
Memory used by the database |
Pending IOs |
Number of transaction log pages (of size 4KB each) that are waiting to be persisted to disk |
Database States
Within a SingleStore DB instance, users can create one or more databases. These databases all reside in the cluster, with data sharded across nodes.
The SHOW DATABASES command with the EXTENDED option adds extra information per-database on top of the standard SHOW DATABASES output. The state column conveys the state of each database with respect to replication and recovery. A SingleStore DB database is in one of the following eight states:
| State | Description |
|---|---|
online |
The database is available for read and write queries. This is the default state after creating a new database. This is the only state in which a database can be a replication master. A database cannot be a replica in this state. |
provisioning |
Not available for read or write queries. This is the first state after running REPLICATE DATABASE. During this state, the database is a replica and is currently downloading and replaying a snapshot file from the replication master. Once the snapshot has been successfully downloaded and replayed, the database will transition to the replicating state. |
replicating |
Available for read but not write queries. The database is a replica and is continually downloading and replaying committed transactions from the replication master. To find the lag between primary and secondary you can use SHOW REPLICATION STATUS. The exception is child aggregators, where databases show up as replicating but are in fact available for write queries. |
pending |
Not available for read or write queries. The database has finished recovering and is waiting for the master aggregator to attach it back into the cluster as a partition database or reference database. The database state will move to replicating or online once the master aggregator has finished attaching it. |
recovering |
Not available for read or write queries. This is the immediate state after starting SingleStore DB. The database is currently recovering from snapshot and log files on disk. Once all data is recovered, the database will transition to replicating (if it’s a replica) or online (if it’s not a replica). If the end of the transaction log is corrupted (e.g. due to power loss) and the database is not a replica, then the database will transition to offline. |
offline |
Not available for read or write queries. You can use the REPAIR DATABASE command to repair the end of the transaction log and database will transition to online. As of MemSQL 4.0, the REPAIR DATABASE command will be executed automatically if an offline database is found during recovery to bring the database online. |
unrecoverable |
Not available for read or write queries. The database enters this state if recovery of the snapshot fails. Such a failure occurs if SingleStore DB runs out of memory while loading the snapshot or if the snapshot file is damaged. The REPAIR DATABASE dbName command cannot be used in this state. If the database is a secondary database, you can force re-provisioning by running DROP DATABASE and then restarting replication with REPLICATE DATABASE dbName FROM ... |
transition |
Databases and partitions are not available for write queries, and are in the process of transitioning to their metadata state. This state can show up temporarily during online or offline failovers. If a partition is not mapped in metadata, then it might also be in the transition state. |
Examples
SHOW DATABASES;
****
+--------------------+
| Database |
+--------------------+
| cluster |
| memsql_demo |
| information_schema |
| memsql |
+--------------------+
SHOW DATABASES EXTENDED;
****
+--------------------+---------+--------+--------+----------+---------+-------------+------------+-----------------+-------------------+------------------+----------------+------+--------------+--------------+-------------+
| Database | Commits | Role | State | Position | Details | AsyncSlaves | SyncSlaves | ConsensusSlaves | CommittedPosition | HardenedPosition | ReplayPosition | Term | LastPageTerm | Memory (MBs) | Pending IOs |
+--------------------+---------+--------+--------+----------+---------+-------------+------------+-----------------+-------------------+------------------+----------------+------+--------------+--------------+-------------+
| cluster | 0 | master | online | 0:130 | | 1 | 0 | 0 | 0:130 | 0:130 | NULL | 24 | 0 | 0.00 | 0 |
| memsql_demo | 0 | master | online | 0:269 | | 1 | 0 | 0 | 0:269 | 0:269 | NULL | 21 | 0 | 0.00 | 0 |
| information_schema | 0 | master | online | 0:1 | | 0 | 0 | 0 | 0:1 | 0:1 | NULL | 2 | 0 | 0.00 | 0 |
| memsql | 0 | master | online | 0:408 | | 0 | 0 | 0 | 0:408 | 0:408 | NULL | 24 | 0 | 0.00 | 0 |
+--------------------+---------+--------+--------+----------+---------+-------------+------------+-----------------+-------------------+------------------+----------------+------+--------------+--------------+-------------+
