CREATE DATABASE

Syntax

CREATE DATABASE [IF NOT EXISTS] <database_name> [<sync_options>] [PARTITIONS n]
    [<create_specification>] 
[SUB_PARTITIONS n]
  
 [ON WORKSPACE <workspace_name>]  
 
<sync_options>: 
WITH {SYNC | ASYNC} DURABILITY 
 | WITH {SYNC | ASYNC} REPLICATION 
| WITH SYNC DURABILITY SYNC REPLICATION | WITH ASYNC DURABILITY ASYNC REPLICATION

<create_specification>:
    [DEFAULT] CHARACTER SET [=] charset_name 

 
 
 

 
 

-- "schema" is an alias for "database"
CREATE SCHEMA [IF NOT EXISTS]..

Remarks

• database_name is the name to assign to this new SingleStore database. Do not use the _XX suffix (such as test_database_01) while naming a SingleStore Helios database as it may cause a mismatch with the database partition ordinals (sharded databases on each leaf node).

• Database names are case-sensitive. Databases named DB and db are not the same objects and will be treated as such when granting permissions to users.

• CONFIG '{"verify_ssl":false|true}' allows you to connect to an unverified SSL certificate in S3 when using the option verify_ssl:false

• SYNC REPLICATION is enabled by default thereby ensuring durability against all single-node failures. SYNC DURABILITY is enabled also by default but only for reference databases.

• SingleStore recommends to leave the default settings for durability and replication when creating a database. If you specify SYNC DURABILITY with the CREATE DATABASE command you will get it on all the partitions as well. This will have a non-trivial performance impact since it has to wait for Amazon Elastic Block Store (EBS). If you specify ASYNC REPLICATION, it can result in occasional data loss.

• WITH {SYNC | ASYNC} REPLICATION specifies whether high availability, redundancy-2 replication will be done synchronously or asynchronously. Synchronous replication from the master partitions will complete on all replicas before the commit of the transaction is acknowledged to the client application. If WITH {SYNC | ASYNC} REPLICATION is not specified, synchronous replication is used.

• High availability, redundancy-2 replication will always be done synchronously. The WITH SYNC REPLICATION clause can be optionally specified, but specifying it has no effect on replication behavior.

• WITH {SYNC | ASYNC} DURABILITY specifies whether in-memory database updates you make using DDL and DML commands are also saved to the log on disk synchronously or asynchronously. Synchronous updates to the log on disk will complete before the commit of the transaction is acknowledged to the client application. If WITH {SYNC | ASYNC} DURABILITY is not specified, async durability is used.

  Note

  SYNC DURABILITY cannot be disabled once a database is created.

• This command causes implicit commits. Refer to COMMIT for more information.

• You cannot specify WITH SYNC DURABILITY ASYNC REPLICATION.

• PARTITIONS n allows you to set the total number of partitions that the data will be split into. By default this is controlled by the default_partitions_per_leaf variable. Note that n refers to the total number of partitions across all leaves. Refer to the Flexible Parallelism page for more information.

• SUB_PARTITIONS n allows you to set the number of sub-partitions for a database per physical partition at the time of database creation. Using this optional clause in the CREATE DATABASE command will override the value set by the sub_to_physical_partition_ratio variable. The value of n must be set at 0 or a power of 2 value of the physical partition count. For example, if the number of partitions is 6, the number of sub-partitions must be 6 * (a power of 2 value). Refer to the Flexible Parallelism page for more information.

• The CREATE DATABASE ... DEFAULT CHARSET= syntax is accepted by SingleStore for compatibility with MySQL, but it has no effect. . Character set and collation defaults can be set during table creation. See CREATE TABLE for more information.

• The CREATE DATABASE ... ON WORKSPACE <workspace_name>; syntax is used to create a read/write database within a workspace that has already been created. See Creating and Using Workspaces for more information.

• The ceph_mode flag has been deprecated.

• Refer to the Permission Matrix for the required permission.

Examples

CREATE DATABASE IF NOT EXISTS test;

CREATE DATABASE IF NOT EXISTS test1 ON WORKSPACE `test-wkspc-1`;

SELECT @@sub_to_physical_partition_ratio;
+-----------------------------------+
| @@sub_to_physical_partition_ratio |
+-----------------------------------+
|                                 4 |
+-----------------------------------+

CREATE DATABASE test1 PARTITIONS 6 SUB_PARTITIONS 96;

SELECT DATABASE_NAME, NUM_PARTITIONS, NUM_SUB_PARTITIONS 
    FROM information_schema.distributed_databases
    WHERE DATABASE_NAME = 'test1';

+---------------+----------------+--------------------+
| DATABASE_NAME | NUM_PARTITIONS | NUM_SUB_PARTITIONS |
+---------------+----------------+--------------------+
| test1         |              6 |                 96 |
+---------------+----------------+--------------------+

CREATE DATABASE test2 SUB_PARTITIONS 64;

SELECT DATABASE_NAME, NUM_PARTITIONS, NUM_SUB_PARTITIONS 
    FROM information_schema.distributed_databases
    WHERE DATABASE_NAME = 'test2';

+---------------+----------------+--------------------+
| DATABASE_NAME | NUM_PARTITIONS | NUM_SUB_PARTITIONS |
+---------------+----------------+--------------------+
| test2         |              8 |                 64 |
+---------------+----------------+--------------------+

CREATE DATABASE test3 PARTITIONS 6 SUB_PARTITIONS 36;
ERROR 1504 (HY000): Value of sub_partitions (36) needs to be a power-of-2 multiple of the partition count (6)

CREATE DATABASE test4 SUB_PARTITIONS 40;
ERROR 1504 (HY000): Value of sub_partitions (40) needs to be a power-of-2 multiple of the partition count (8)