This topic describes how to upgrade MemSQL to SingleStore DB 7.1. Please read the following information thoroughly before upgrading to SingleStore DB 7.1.

If you want to upgrade to 7.1 from 6.0 or earlier, you must first upgrade to MemSQL 6.8 before upgrading to SingleStore DB 7.1.

After you have finished upgrading, please see the Post-Upgrade Considerations section for additional information on behavioral changes that you should be aware of.

Important notes about upgrading

If upgrading from a version of MemSQL earlier than 7.0, please note the following.

Upgrade duration and behavior

Anticipate a longer upgrade time for each node. At the end of the upgrade process, anticipate a multi-minute delay while final checks are run that ensure the upgrade completed successfully and the cluster is ready for operation. If you connect to the cluster while these post-upgrade checks are running, you will receive an error that the upgrade is still in progress.

Compound keys

In compound keys with the UNIQUE constraint, if the type of the first column of the key is specified as NOT NULL, that resulted in non-standard UNIQUE constraint behavior for any NULL-able columns in that same compound key. This behavior has been changed as of MemSQL 7.0 to treat all columns the same, independent of position in the key, conforming to the SQL standard.

Delete performance

MemSQL 7.0 introduced row-level locking for columnstore update/delete so it can handle more concurrent queries, but for certain scenario it may cause performance degradation (delete on wide table with json column). Set COLUMNSTORE_TABLE_LOCK_THRESHOLD to bypass row-level locking and obtain the previous behavior.

Leaf nodes

In MemSQL 6.x, a leaf node can restart, come back online, and its databases can be queried when the Master Aggregator is offline. As of MemSQL 7.0, a leaf node can restart and recover its databases, but it won’t bring them online unless the Master Aggregator is online. In all versions of SingleStore DB, leaf nodes can safely be restarted when the Master Aggregator is online.

Plancache

Plans in the plancache are dependent upon the specific MemSQL/SingleStore DB patch version, so when you upgrade to a new MemSQL/SingleStore DB version, all previously compiled plans will be invalidated. This means that any queries run against the upgraded cluster will force a one-time plan compilation, which results in slower query times the first time those queries are run. After the plans have been recompiled, they will be stored again in the plancache and query latency will return to nominal values.

Replication

Replication is now synchronous as of MemSQL 7.0 by default. Fail-overs in SingleStore DB 7.1 will not occur unless a replica is in sync with its master. While you will never lose data on fail-overs, you could lose availability if your replicas are out of sync and a node goes down. Refer to Replication and Durability and Replay Configuration and Error Handling for more information.

SCHEMA_BINDING for Views

As of MemSQL 7.0, SCHEMA_BINDING for views is off by default. Prior to MemSQL 7.0, SCHEMA_BINDING for views was on by default. As a result, an error will no longer be issued when deleting a table under a view.

Note that this behavior only applies to views that are created after upgrading, and does not apply to views that were created prior to upgrading.

Snapshots

As of MemSQL 7.0, the default value of snapshot_trigger_size has been increased from 256MB to 2GB. This will not be updated automatically on upgrade, and it is recommended that customers make this change post-upgrade to reduce snapshot disk I/O.

Sync variables

As of MemSQL 7.0, a large percentage of system variables have been changed to “sync” variables. This allows variables to be set on the Master Aggregator, which will then persist and be replicated to the other nodes in the cluster. This obviates the need to manually set these variables in a .cnf file. Use SHOW _SYNC VARIABLES to see the current set of sync variables.

As a consequence of this, when a variable has been changed to a sync variable and has different values on two nodes (for example, a sync variable on Node X has been set to value A, and this sync variable on Node Y has been set to value B), the upgrade will issue a warning, and these variables must be reconciled before they can become sync variables.

To do so, manually update the .cnf file and, using the above example, set the value of the sync variable to either A or B on both nodes. Next, run UPGRADE VARIABLES on the Master Aggregator to enable all the new sync variables. Refer to Engine Variables and update-config for more information.

Verify your cluster is ready for upgrade

Prior to upgrading your cluster, it is recommended that you take a backup as a standard precautionary measure. See Backing Up and Restoring Data.

In addition, from the master aggregator, run the following commands:

SHOW LEAVES;
SHOW AGGREGATORS;
SHOW CLUSTER STATUS;
EXPLAIN RESTORE REDUNDANCY;
EXPLAIN REBALANCE PARTITIONS;

With the output of these commands, confirm that the following are true:

• All leaves are online

• All aggregators are online

• There are no partitions with ‘Orphan’ role

• No Rebalance or restore redundancy is necessary

After you have backed up your data and verified your cluster is ready, you are ready to upgrade your cluster to the latest version of SingleStore DB using either the SingleStore management tools or MemSQL Ops.

Upgrade versions and methods

The tables below depict which versions of MemSQL can be upgraded to SingleStore DB 7.1 and the method by which the cluster can be upgraded.

• Offline upgrade: Your cluster will be shut down and restarted over the course of the upgrade

• Online upgrade: Your cluster will not be shut down over the course of the upgrade

Upgrade via SingleStore DB Toolbox

Upgrade via MemSQL Ops

Upgrade your cluster

Select an option below to upgrade your cluster.

sudo yum install singlestoredb-toolbox -y

sudo apt install singlestoredb-toolbox -y

sudo yum install /path/to/singlestoredb-toolbox.rpm -y

sudo apt install /path/to/singlestoredb-toolbox.deb -y

sdb-deploy upgrade --file-path /path/to/memsql-server.rpm

sdb-deploy upgrade --file-path /path/to/memsql-server.deb

sdb-deploy upgrade --online --file-path /path/to/memsql-server.rpm

sdb-deploy upgrade --online --file-path /path/to/memsql-server.deb

memsql-ops agent-upgrade

sudo memsql-ops agent-upgrade --file-path /path/to/memsql-ops-XYZ.tar.gz

memsql-ops memsql-upgrade --version 7.1

sudo memsql-ops memsql-upgrade --file-path /path/to/memsqlbin_amd64.tar.gz

Post-upgrade considerations

When upgrading to SingleStore DB 7.1, you should be aware of the following changes to system behavior or default configuration settings. The behavior of a cluster upgraded from an earlier version to SingleStore DB 7.1 may differ compared to a newly installed cluster on SingleStore DB 7.1 as described below. Most of the changes fall into two categories:

• In some versions, the default value for a configuration variable was changed compared to previous versions, but clusters upgraded from earlier versions retain their previous setting, both if it was set to a specific value or if it was not explicitly set and hence using the previous default. In some of these cases, it is recommended to update your configuration to the new default if you were previously using the old default, after appropriate testing.

• Some new features are automatically enabled by default on newly installed MemSQL 7.0 clusters but not automatically enabled on clusters upgraded from an earlier version to 7.0. In some of these cases, it is recommended to enable the new features, after appropriate testing.

• Changing the value of the data_conversion_compatibility_level sync variable can change the behavior of expressions in computed columns. Refer to the data conversion compatibility level section of Data Types for more information.

• sp_query_dynamic_param should be turned off if an application breaks post-upgrade due to a change in type conversion behavior. See the Example: Changes in Type Conversion Behavior for more information.

• Upgrading the cluster, with json_extract_string_collation set to auto (default setting), changes the collation settings for JSON_EXTRACT_STRING from json to server. Refer to In-Depth Variable Definitions for information on json_extract_string_collation settings.

Upgrades from 6.8 and earlier to 7.0 and later

Synchronous replication on by default

In previous versions of SingleStore DB, in clusters with high availability enabled, replication between master partitions and replica partitions happened asynchronously. Starting with 7.0, synchronous replication is enable d by default for all new databases. And as noted in the upgrade section of this topic, you have the option to opt-out of synchronous replication for the existing databases in your cluster. For more information on synchronous replication, see Replication and Durability and CREATE DATABASE.

Security change for resource pools

Between MemSQL 6.5 to 6.8, all users were able to access all resource pools. Access had to be controlled externally from the database, such as in a client application. Starting in 7.0, users can only access resource pools that they have explicit permission to access when sync_permissions is enabled. Otherwise, no permission checking is done for any user for any resource pool. Refer to Synchronizing Permissions Across Your Cluster for more information.

To ensure current users will be able to access pools immediately after upgrading to 7.1, all existing users will be granted USAGE permissions to all existing and future resource pools if sync_permissions was enabled prior to upgrade (i.e. for each user GRANT USAGE ON RESOURCE POOL '*' TO <user>@<host> is run internally on upgrade to 7.0 or later) unless REVOKE USAGE ON RESOURCE POOL '*' FROM <user>@<host> is run. You will then have to explicitly grant USAGE permissions to specific resource pools for those users and any other new users created. Refer to Setting Resource Limits for more information.

Many existing engine variables are now sync variables

The following engine variables from 6.8 are now sync variables, which means you set the value on the master aggregator and the change is propagated across your cluster. You no longer have to set and manage these variables on each node in the cluster.

Global variables

• auditlog_disk_sync
• columnstore_disk_insert_threshold
• columnstore_flush_bytes
• columnstore_ingest_management_queue_timeout
• columnstore_segment_rows
• disk_plan_expiration_minutes
• enable_columnstore_ingest_management
• enable_disk_plan_expiration
• explain_expression_limit
• forward_aggregator_plan_hash
• geo_query_info
• geo_sphere_radius
• internal_columnstore_window_minimum_blob_size
• load_data_internal_compression
• load_data_max_buffer_size
• load_data_read_size
• load_data_write_size
• materialize_ctes
• max_connect_errors
• max_prepared_stmt_count
• multi_insert_tuple_count
• pipelines_batches_metadata_to_keep
• pipelines_extractor_debug_logging
• pipelines_kafka_version
• pipelines_max_concurrent
• pipelines_max_concurrent_batch_partitions
• pipelines_max_errors_per_partition
• pipelines_stderr_bufsize
• plan_expiration_minutes
• read_advanced_counters
• replication_timeout_ms
• snapshot_trigger_size
• sync_slave_timeout
• sync2_timeout
• synchronize_database_timeout

Session Variables

• character_set_server
• collation_connection
• collation_database
• collation_server
• enable_binary_protocol
• enable_broadcast_left_join
• enable_local_shuffle_group_by
• enable_multipartition_queries
• enable_skiplist_sampling_for_selectivity
• explain_joinplan_costs
• ignore_insert_into_computed_column
• inlist_precision_limit
• leaf_pushdown_default
• leaf_pushdown_enable_rowcount
• lock_wait_timeout
• max_broadcast_tree_rowcount
• max_subselect_aggregator_rowcount
• optimize_constants
• optimize_expressions_larger_than
• optimize_huge_expressions
• optimize_stmt_threshold
• optimizer_warnings
• report_mpl_optimizations
• reshuffle_group_by_base_cost
• sampling_estimates_for_complex_filters
• sql_select_limit
• statistics_warnings

See the List of Engine Variables for more information on these variables.

Was this Article Helpful?

Thanks For Your Feedback!