MySQL Features Unsupported in MemSQL
MemSQL intentionally does not support all of the features of MySQL. In some cases, MemSQL can safely ignore unsupported features in a SQL statement and continue executing only the supported features.
Unsupported Feature List
- warn_level Engine Variable - how MemSQL behaves when it encounters unsupported functionality is controlled via the
warn_levelengine variable. The variable has two settings:
- 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 MemSQL and want to only use features fully supported by MemSQL.
- 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 MemSQL and to avoid having to change the application to not use unsupported features.
warn_levelvariable is set just like other global engine variables:
set global warn_level="errors";
- User-Defined Variables
- Triggers (other than the TIMESTAMP type)
- Foreign keys and referential integrity
- Character encodings other than UTF-8. MemSQL does not accept 4-byte characters.
- Prepared statements - MemSQL does not fully support server-side prepared statements. See Prepared Statements.
- MemSQL geospatial is incompatible with MySQL geospatial.
MemSQL 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 MemSQL, 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.
memsql> SELECT * FROM messages; +----+-------+ | id | msg | +----+-------+ | 2 | ohai! | | 3 | kthx | | 1 | yo | +----+-------+ 3 rows in set (0.00 sec) memsql> SELECT * FROM messages ORDER BY id; +----+-------+ | id | msg | +----+-------+ | 1 | yo | | 2 | ohai! | | 3 | kthx | +----+-------+ 3 rows in set (0.00 sec)