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_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 MemSQL 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 MemSQL and want to only use features fully supported by MemSQL.

  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 Overview page.

• getProcedures() and getProcedureColumns() MySQL built-ins

• 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.

• The comparison operator LIKE does not support the ESCAPE clause. However, you can use the backslash character \ as an escape character in LIKE patterns.

Behavior Differences

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.

SELECT * FROM messages;
****
+----+-------+
| id | msg   |
+----+-------+
|  2 | ohai! |
|  3 | kthx  |
|  1 | yo    |
+----+-------+
3 rows in set (0.00 sec)

SELECT * FROM messages ORDER BY id;
****
+----+-------+
| id | msg   |
+----+-------+
|  1 | yo    |
|  2 | ohai! |
|  3 | kthx  |
+----+-------+
3 rows in set (0.00 sec)

Was this Article Helpful?

Thanks For Your Feedback!