Join the MemSQL Community Today
Get expert advice, develop skills, and connect with others.

7.1 Release Notes

Info

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.

Release Highlights

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.

  • Added the INTO option to the SELECT command, 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_fanout to evenly distribute a leaf L’s replica partitions across a set of leaves in the cluster. This is an alternative to the paired setting, where all of leaf L’s replica partitions reside on one paired leaf. Using load_balanced allows 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

Storage

  • Partition splitting, implemented as an option to the BACKUP command, 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 or 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.

Query Optimization

  • 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 CHAR or a VARCHAR value to a DECIMAL value.

  • 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 CREATE VIEW and DROP VIEW commands.

Data Ingest

Security

  • 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 TABLE now requires the INSERT permission.

Other

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

Maintenance Release Changelog

2020-08-31 Version 7.1.8

  • Fixed an issue that occurred when using server side prepared statements via the newer versions of the MariaDB client. The MySQL client is unaffected.
  • Fixed an issue cleaning up global temp tables on leaves during rebalancing operations.
  • Fixed an issue where two window functions with identical ORDER BY and PARTITION BY clauses, but differing ROWS BETWEEN clauses, incorrectly output the same values.
  • Fixed a performance issue with JSON_TO_ARRAY().
  • Fixed an issue where incremental autostats may enter an invalid state and be unable to recover automatically due to out of memory on a leaf node.
  • Improved error message in cases where MemSQL has issues accessing the memsql.cnf file.
  • Fixed an issue where REPLICATE DATABASE would fail in cases where no replicated database existed yet and the node containing the master aggregator was previously replaced with a new node.
  • Added a multipart_chunk_size_mb setting to SELECT .. INTO S3 that controls the size of the file uploaded to S3 to support uploading larger result sets to S3.
  • Fixed an issue where auto-attach would fail to attach a partition but succeed in bringing the leaf online, resulting in offline partitions.
  • Ensured that any re-used existing replica partition is still forced in sync as part of the copy operation run as a step of a REBALANCE operation. Previously, this was a cause of failure in cases where REBALANCE failed once prior.
  • Fixed a failure when running a ROLLUP query over a constant expression.
  • Audit logging now supports auditing logins and login attempts.
  • Audit logging file headers now include information about what level the file is logging at as well as if the level was changed.
  • Added a tracelog (memsql.log) message to indicate if audit logging has been disabled from previously having been enabled.
  • Added support for a SELECT containing a semicolon in the CREATE TABLE ... AS SELECT statement.
  • Modified the infrastructure of the native monitoring solution by removing the Kafka and memsql-pusher components and automating the monitoring setup with MemSQL Toolbox commands.

2020-08-10 Version 7.1.7

  • Fixed a compatibility issue that occurred when running newer versions of SAP Business Object Data Services (BODS) against MemSQL. The issue was that the command SET sql_mode = <expression> was not supported by MemSQL.
  • Fixed an issue that could occur when using columnstore unique keys in an update query with a join.
  • Avoid taking unneeded locks for queries writing into columnstore tables with unique key columns. Taking these locks could cause transactions containing multiple statements to deadlock.
  • Improved the error message returned when REMOVE AGGREGATOR is run on the master aggregator.
  • The behavior of the max_compilation_time_s engine variable no longer applies to DDL queries. Eliminating compilation timeouts for these queries, especially for ALTER TABLE, will prevent the queries from potentially running forever.
  • Reduced the CPU usage required for many threads to allocate memory for variable length strings.
  • Added a new engine variable convert_nonunique_hash_to_skiplist which, when set to ON, creates a skiplist index in place of any non-unique hash index. By default, the variable is set to OFF.
  • Pipelines no longer stop executing if free disk space drops lower than 5 * <the value of the minimal_disk_space engine variable>. They now stop executing if free disk space drops lower than 400 mb + <the value of the minimal_disk_space engine variable>.

2020-07-27 Version 7.1.5

  • Now, SHOW PROCESSLIST returns the IP address of the client (in the Host column) if the host name cannot be resolved.
  • Fixed an issue that occurred when the engine variable interpreter_mode was set to interpret_first, where the first run of the query remained in interpreted mode even after compilation had finished.
  • Added support for ALTER PIPELINE ... SET RESOURCE POOL.
  • Improved the performance of rebalance partition operations that copy partitions with many small columnstore BLOB files.
  • Improved predicate transitivity rewrites to avoid adding redundant predicates. This fixes compile timeouts for some queries with large numbers of predicates.
  • Fixed an issue where the engine variable explicit_defaults_for_timestamp could be set to a different value on new nodes added to the cluster, as compared to the existing value already set on existing nodes. This would cause the error “partition’s table metadata are out of sync” when queries were run.
  • Improved heartbeat failure detection when leaves were unresponsive to queries.
  • Fixed an issue where setting the engine variable sql_mode to ONLY_FULL_GROUP_BY would , for queries using INTERSECT and EXCEPT, incorrectly generate an error indicating a field is unaggregated and not in the GROUP BY clause.
  • Fixed an issue where setting the engine variable materialized_ctes to ALL would prevent certain queries using INTERSECT and EXCEPT from running.
  • Re-enabled support for certain nested subselects that had been added previously and then removed erroneously.
  • Improved the performance of batch deletes on columnstores. The increase in performance depends on the encoding of the columns.

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 TABLE now requires the SELECT and (INSERT or 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 BY or LINES TERMINATED BY options with SELECT ... INTO S3 or SELECT ... INTO KAFKA.
  • Improved the accuracy of the PROFILE commands that have ColumnStoreScan components.
  • When preserve_original_colstore_json is 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_sensitivity was set to OFF.

2020-06-15 Version 7.1.3

  • Correctly generate an error when running ALTER VIEW on 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_server, collation_database, or collation_connection would 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).
  • EXPLAIN, PROFILE, and SHOW PROFILE are 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.
  • The STATE column of the information_schema.PIPELINES view 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