github.com/square/finch@v0.0.0-20240412205204-6530c03e2b96/docs/content/operate/mysql.md (about)

     1  ---
     2  weight: 3
     3  title: "MySQL"
     4  ---
     5  
     6  {{< hint type=warning >}}
     7  **Do not use Finch in production**. It's a development tool intended for use only in insecure development environments.
     8  {{< /hint >}}
     9  
    10  ## User
    11  
    12  Finch defaults to username `finch`, password `amazing` , host `127.0.0.1` (via TCP).
    13  
    14  Finch is a development tool intended for use only in insecure development environments, but a safer MySQL user is suggested:
    15  
    16  ```sql
    17  CREATE USER finch@'%' IDENTIFIED BY 'amazing';
    18  
    19  GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,  REFERENCES, INDEX, ALTER,  CREATE TEMPORARY TABLES, LOCK TABLES
    20  ON finch.* TO finch@'%';
    21  ```
    22  
    23  If you want to use your local root MySQL user, setting environment variable [`FINCH_DSN`]({{< relref "operate/command-line#--dsn" >}}) is the easiest way to override the defaults.
    24  
    25  ## Connection
    26  
    27  Finch checks [`stage.mysql.dsn`]({{< relref "syntax/stage-file#mysql" >}}) first: if set, it's used and processing stops.
    28  [`--dsn`]({{< relref "operate/command-line#--dsn" >}}) sets and overwrites `stage.mysql.dsn` from the command line: `stage.mysql.dsn = --dsn`.
    29  If `stage.mysql.dsn` is not set, it inherits \_all.yaml [`mysql.dsn`]({{< relref "syntax/all-file#dsn" >}}).
    30  
    31  If no DSN is specified, Finch loads the other [`stage.mysql`]({{< relref "syntax/stage-file#mysql" >}}) settings, which inherit values from \_all.yaml [`mysql`]({{< relref "syntax/all-file#mysql" >}}).
    32  
    33  ## Default Database
    34  
    35  There are three ways to set the default database:
    36  
    37  |Connection|Client Group|Trx|
    38  |----------|------------|--------------|
    39  |[Connection](#connection)|[`stage.workload[].db`]({{< relref "syntax/stage-file#db" >}})|`USE db` in a trx|
    40  
    41  The connection default database is standard: the database that the client _requires_ when connecting to MySQL.
    42  It's specified by [`--dsn`]({{< relref "operate/command-line#--dsn" >}}), or by [`--database`]({{< relref "operate/command-line#--database" >}}), or by [`mysql.db`]({{< relref "syntax/all-file#db" >}}).
    43  If the database doesn't exist, the connection fails with an error:
    44  
    45  ```
    46  % mysql -D does_NOT_exist
    47  ERROR 1049 (42000): Unknown database 'does_not_exist'
    48  ```
    49  
    50  The connection database is useful to ensure that all clients use it, but there's a problem: it doesn't work if the stage is going to create the database.
    51  
    52  {{< hint type=warning >}}
    53  It is **not recommended** to have a stage drop schemas or tables in a trx file.
    54  A human should do this manually to ensure only the correct schemas or tables are dropped.
    55  {{< /hint >}}
    56  
    57  Although _not recommended_, a stage can create its own schema in a trx file like:
    58  
    59  ```SQL
    60  DROP DATABASE IF EXISTS finch;
    61  
    62  CREATE DATABASE finch;
    63  
    64  USE finch;
    65  
    66  CREATE TABLE ...
    67  ```
    68  
    69  That's an example of a trx default database: using an explicit `USE db` statement, which Finch allows.
    70  
    71  The client group default database is a special case: it make Finch execute `USE db` once for each client in the group during stage preparation (before the stage runs), which avoids having to use a trx default database.
    72  This is done after connecting, so the database doesn't need to exist to connect, but it needs to exist when the client group is prepared (else [`--test`]({{< relref "operate/command-line#--test" >}}) will fail).