7.1 Release Notes
To deploy a MemSQL 7.1 cluster, refer to the Deploy MemSQL Guide.
To upgrade a self-managed install to this release, follow this guide.
To make a backup of a database in this release or to restore a database backup to this release, follow this guide.
This release improves support for OLTP-style (highly-selective) queries on columnstores, High Availability, and Disaster Recovery.
Support for Operational Applications
As part of the SingleStore initiative, enhanced the columnstore to enable transactional workloads to be processed more efficiently. These enhancements include support for single-column unique hash keys on columnstores and fast joins on columnstores, where there is a highly selective filter on one table in the join.
INTOoption to the
SELECTcommand, which can be used in procedural SQL. SELECT INTO allows you to select the columns in a one-row resultset into a list of variables, making your code more concise.
High Availability and Disaster Recovery
During replication, if your primary cluster fails, you can reverse replication from your secondary cluster to your primary cluster to quickly failback to the primary cluster.
In High Availability mode, you can now use the load_balanced setting of the engine variable
leaf_failover_fanoutto evenly distribute a leaf L’s slave partitions across a set of leaves in the cluster. This is an alternative to the
pairedsetting, where all of leaf L’s slave partitions reside on one paired leaf. Using
load_balancedallows you, if leaf L fails, to distribute its failed-over workload to the set of leaves instead of just one, balancing the load.
Additional Features and Improvements in this Release
Partition splitting, implemented as an option to the
BACKUPcommand, allows you double the number of partitions in a database easily. You may wish to increase the number of partitions to accommodate more leaves, or to enable more parallelism.
Added support for full backups to Google Cloud Storage (GCS). Also added support for incremental backups to GCS, S3, and Azure. For more information, see the Backing Up and Restoring Data topic.
Added global temporary tables, which are similar temporary tables, except that they exist beyond the duration of a client session.
When creating a table, you can now specify that a
DATETIME(6)column should be populated with the current timestamp when inserting or updating a row in the table.
Highly selective joins on columnstores are now more efficient. A highly selective join has a very selective filter on one table which produces a few rows and these rows are joined with another table.
Enhanced cardinality estimation for joins using histograms.
Updated the behavior of the DROP ALL FROM PLANCACHE command; it now purges all query plans from memory and disk.
Added more support for queries that use scalar nested subselects.
Added the NOPARAM() function that disables the parameterization of constants when a query is compiled.
Improved optimization of hash joins involving expressions.
Usability and Programmability
You can now access record variable fields inside of a SQL statement in procedural SQL.
Added the information_schema.MV_BLOCKED_QUERIES view, which provides a summary of activities that are currently waiting.
Added the TO_NUMBER() function, which converts a
VARCHARvalue to a
Added the TABLE() function, which converts a procedural SQL array to a set of rows.
Added the TRUNC(date) and TRUNC(number) functions.
TRUNC(date)allows you to truncate a date to a specified granularity.
TRUNC(number)allows you to truncate a given number to a specified number of decimal places.
Added the INET6_ATON() function, which converts an IPv6 or IPv4 internet address from its text representation to its binary representation.
Added the INET6_NTOA() function, which converts an IPv6 or IPv4 internet address from its binary representation to its text representation.
Added the REGEXP_SUBSTR() function, which searches for a regular expression pattern and returns the matching substring.
Stored procedures now support the
- Added support for Google Cloud Storage (GCS) Pipelines.
Added configurable password complexity rules. Now you can specify criteria for passwords, including criteria such as minimum length or the number of uppercase, lowercase, numeric, and special characters.
Added a configurable account lockout feature. You can specify the number of times a user can enter an incorrect password before they are locked out of the system. When a user reaches this limit, their account is locked for the specified number of seconds. For more information on the failed login attempt lockout feature, see Securing MemSQL.
OPTIMIZE TABLEnow requires the
Fixed a race condition where plan eviction during a distributed join would cause the error “MemSQL code generation has failed: Cannot compile from background threads”.
Fixed a bug where decimals lose precision when scientific notation is parsed.
2020-07-06 Version 7.1.4
- Now, MemSQL license checks are Linux control group aware. For containerized deployments, CPU and memory limits are checked against each container.
ANALYZE TABLEnow requires the
ALTER) permissions to execute.
- Added the TO_JSON() function, which converts a table column, an entire table, a scalar value, or a single row to a JSON object.
- Fixed a syntax error that occurred when using the
LINES STARTING BYor
LINES TERMINATED BYoptions with
SELECT ... INTO S3or
SELECT ... INTO KAFKA.
- Improved the accuracy of the
PROFILEcommands that have ColumnStoreScan components.
preserve_original_colstore_jsonis set to
ON, null values and empty arrays are preserved in JSON columns in columnstores.
- SSL ciphers that use elliptic curves are now supported.
- Added an optimizer setting that allows you to adjust the cost of performing a cross join.
- Fixed an issue where user-defined tables having the same name as system tables, such as
USERS, were treated as case-sensitive when
table_name_case_sensitivitywas set to
2020-06-15 Version 7.1.3
- Correctly generate an error when running
ALTER VIEWon a schema-bound view v1, where a schema-bound view v2 refers to v1.
- Fixed an auto-attach failure in specific failure conditions where a node quickly got marked offline and then online by the aggregator, but the node itself did not restart.
- Fixed an issue where setting one of the three engine variables
collation_connectionwould not persist the value for all three variables on node restart. (Setting any of these variables will change the other two variables to the same value).
SHOW PROFILEare now allowed inside of stored procedures.
- Improved predicate transitivity rewrites to avoid adding redundant predicates. This fixes compile timeouts for some queries with large numbers of predicates.
STATEcolumn of the
information_schema.PIPELINESview now indicates if a Pipeline has failed due to insufficient disk space.
2020-05-28 Version 7.1.2
- Initial GA release of MemSQL 7.1