# MySQL

An open-source, reliable, and easy-to-use relational database that allows for seamless integrations with many platforms and programming languages.

## Supported Methods

Means for Lumi to connect to the client to implement the services required:

* [x] **Direct** 

  *The default / typical connection option. Interfaces directly with the target system over the Internet with no mediation. Suitable for most cloud-hosted scenarios or public-facing resources. See* [*Network Configuration*](https://docs.lumi-ai.com/using-lumi/network-configuration) *for more details.*
* [x] **Gateway**\
  \&#xNAN;*An alternative connection method leveraging a Lumi AI Data Gateway. This uses Lumi AI's purpose-built connection agent to mediate communications. Ideal for restricting access to systems within a protected network. For more information, see* [*Data Gateway*](https://docs.lumi-ai.com/product-features/data-gateway)*.* 

## Supported Limits

The following are limitations that can be set to fine tune the agents' actions: 

* [ ] **Cost Limit**\
  \&#xNAN;*Before running a query, if the system supports it, the system-specific compute cost (or surrogate) estimate will be processed and compared to an organization-level/admin-set cost limit for systems of this type (if configured/set). If exceeded, the query will not run (and either the workflow will attempt an optimization or the user will be notified).*
* [x] **Duration Limit**\
  \&#xNAN;*An alternative to cost, queries will be stopped the system supports a duration/timeout limit and one is set/configured at the organization level (across systems).*

## Available Parameters

These properties are the essential source system connection properties that all queries are directed towards.

*\* Required parameters*

{% hint style="info" %}
Note: The Gateway parameter is common to all systems (when supported) and is only available when gateway is the selected connection method.
{% endhint %}

<details>

<summary>Host*</summary>

Domain or IP of the database to allow a connection to Lumi AI.

:thumbsup: **Valid Examples**

* Domain name: *docs.lumi-ai.com*
* IPv4: *55.88.92.101*

:thumbsdown: **Invalid Examples**

* IPv4 with alphanumeric characters: *123.abc.def.456*
* Incomplete domain name resolutions: *lumi-ai.com*

</details>

<details>

<summary>Port*</summary>

Access port to allow for connections to the database.

:thumbsup: **Valid Examples**

* Default MySQL Server port: *3306*

:thumbsdown: **Invalid Examples**

* Known Port Numbers (0-1023): *80*

</details>

<details>

<summary>Username*</summary>

Username of the generated database user which will be used for interactions and transactions.

**Special Considerations**

* If back ticks are used, then the name will be case sensitive

:thumbsup: **Valid Examples**

* Uses alphanumeric characters : *lumi1*
* Utilizing underscore (\_) in place of spaces: *user\_number*

:thumbsdown: **Invalid Examples**

* Starting with a number: *1lumi*
* Utilizing spaces: *user name*
* Using reserved words not encapsulated: *select*
* Using invalid characters not encapsulated: *user#1*

</details>

<details>

<summary>Password*</summary>

Password of the generated database user which will be used for interactions and transactions.

</details>

<details>

<summary>Database*</summary>

Literal database name that will be used for transactions within the agents.

**Special Considerations**

* If case sensitivity is required, utilize double quotes for database name; ex. "New\_database"

:thumbsup: **Valid Examples**

* Uses alphanumeric characters: *lumi1*
* Utilizing underscore (\_) in place of spaces: *new\_database*

:thumbsdown: **Invalid Examples**

* Starting with a number: *1lumi*
* Utilizing spaces: *user name*
* Using reserved words not encapsulated: *select*
* Using invalid characters not encapsulated: *user#1*

</details>

## System Permissions & Configuration

Provided user must have the following:

* [x] If applicable CREATE a user
* [x] Grant the user with `db_datareader` ROLE
* [x] SELECT PRIVILEGE in the database via `GRANT SELECT ON *.* TO <USER>;` and reload privleges with `FLUSH PRIVILEGES;`
* [x] (if applicable) SELECT PRIVILEGE on table(s) for column(s)

For configurations:

* [x] Ensure the server is allowing connections from the Lumi gateway or server via a my.cnf or my.ini file and bind-address

## Special Notes

* When making the user, it is recommended to specify the remote host like `'username'@'%'` where % is the guest address
* Encasing a name with back ticks (\` \`)  allows for a usage of reserved words and special characters but is not recommended
* Depending on the file system on Linux computers, case sensitivity may be enforced while the default on Windows and most Linux systems are case-incentive &#x20;

## Common Issues

* MySql has no division by zero, just renders nulls
* Ensure the server is not in recovery mode and be on an online state
* Ensure the firewall settings allow for a connection to the database
* Ensure the username, password, and database values are explicitly provided&#x20;