# Unsupported MySQL Features

SingleStore intentionally does not support all of the features of MySQL. In some cases, SingleStore can safely ignore unsupported features in a SQL statement and continue executing only the supported features.

## Unsupported Feature List

* **warn\_level Engine Variable** - how SingleStore behaves when it encounters unsupported functionality is controlled via the `warn_level` engine variable. The variable has two settings:

  * Warnings (default) - Permits SQL statements that aren’t supported, but whose unsupported features can safely be ignored. Warnings will be issued when such queries are used. Use this mode when porting an existing application to SingleStore and to avoid having to change the application to not use unsupported features.
  * Errors - SQL statements with unsupported features will be rejected as errors. This is the most strict warn level. Use this level when you are developing a new application on SingleStore and want to only use features fully supported by SingleStore.

  The `warn_level` variable is set just like other global engine variables: `SET GLOBAL warn_level= "errors";`. 

  For more information on setting engine variables, see the [Engine Variables](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables.md) overview.
* `getProcedures()` and `getProcedureColumns()` MySQL built-ins
* **User-Defined Variables**. SingleStore does not fully support user-defined variables. See [User-Defined Variables](https://docs.singlestore.com/db/v9.1/reference/sql-reference/user-defined-variables.md).
* **Triggers (other than the TIMESTAMP type)**
* **Foreign keys and referential integrity**
* **Prepared statements** - SingleStore does not fully support server-side prepared statements. See [Using Prepared Statements](https://docs.singlestore.com/db/v9.1/developer-resources/connect-with-application-development-tools/using-prepared-statements.md).
* SingleStore geospatial is incompatible with MySQL geospatial.
* The comparison operator `LIKE` does not support the `ESCAPE` clause. However, you can use the backslash character `\` as an escape character in `LIKE` patterns.
* `get_lock()` and related functions (`release_lock()`, etc.).
* Certain SingleStore engine variables exist for backwards compatibility with MySQL and are non-operational in SingleStore. These engine variables are:

  * `character_set_client`
  * `character_set_connection`
  * `character_set_database`
  * `character_set_filesystem`
  * `character_set_results`
  * `character_set_system`
  * `character_sets_dir`

  Refer to [Reserved Engine Variables](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/reserved-engine-variables.md) for the complete list.

## Behavior Differences

SingleStore differs from MySQL query behavior in a few ways, mostly in cases where MySQL behavior is officially undefined, and the observed behavior depends on the details of implementation.

* **No implicit ordering of results by primary key:** MySQL (and other single-machine databases) may return data in order of the primary key of the table, even when the query does not specify an ordering. In SingleStore, a `SELECT *` query without an order clause may return data in any order, and the ordering may even be different between runs of the same query. This is because partitions stream their results to the aggregator in parallel, and the results are forwarded to the client as they arrive. To retrieve rows in order, you must specify an explicit order clause.
  ```sql
  SELECT * FROM messages;

  ```
  ```output

  +----+-------+
  | id | msg   |
  +----+-------+
  |  2 | ohai! |
  |  3 | kthx  |
  |  1 | yo    |
  +----+-------+
  3 rows in set (0.00 sec)

  ```
  ```sql
  SELECT * FROM messages ORDER BY id;

  ```
  ```output

  +----+-------+
  | id | msg   |
  +----+-------+
  |  1 | yo    |
  |  2 | ohai! |
  |  3 | kthx  |
  +----+-------+
  3 rows in set (0.00 sec)

  ```
* **`UNION ALL` with `ORDER BY` behavior inconsistent with MySQL:** SingleStore parses UNION ALL followed by ORDER BY commands differently from MySQL. In SingleStore, the following query is valid. In MySQL, it is invalid.
  ```sql
  SELECT * FROM table1 UNION ALL SELECT * FROM table2 ORDER BY table2.col1
  ```

***

Modified at: January 20, 2023

Source: [/db/v9.1/connect-to-singlestore/connect-with-mysql/connect-with-mysql-client/unsupported-mysql-features/](https://docs.singlestore.com/db/v9.1/connect-to-singlestore/connect-with-mysql/connect-with-mysql-client/unsupported-mysql-features/)

(An index of the documentation is available at /llms.txt)