Sign inTry Free

Upgrade to SingleStoreDB 7.3

On this page

Note

Please note the following before upgrading your cluster:

  • SingleStoreDB betas and release candidates cannot be upgraded unless explicitly stated in the release notes.

  • If any host in the cluster is near or at disk capacity, please increase available storage before upgrading.

Important Notes About Upgrading

This topic describes how to upgrade SingleStoreDB. Please read the following information thoroughly before upgrading.

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

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

Plancache

Plans in the plancache are dependent upon the specific SingleStoreDB patch version, so when you upgrade to a new SingleStoreDB 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.

Non-Sync Variables

By default, convert_nonunique_hash_to_skiplist is set to TRUE as of SingleStoreDB 7.3. This means that any non-unique hash index will be recovered as a skiplist index, any newly created table will also have its non-unique hash indexes created as skiplists. For more information about this engine variable see the Non-Sync Variables List.

Verify Your Cluster is Ready for Upgrade

Warning

If upgrading from MemSQL 6.0 or earlier, you must first upgrade to MemSQL 6.8 before upgrading to SingleStoreDB 7.3.

If upgrading from MemSQL version 6.8 or earlier, refer to Upgrading to MemSQL 7.0 for the increased disk space requirements for SingleStoreDB.

If upgrading from MemSQL 6.x, there must not be any disaster recovery (DR) databases present before upgrading to SingleStoreDB 7.3. If there are any DR databases in the cluster, the database must be dropped before upgrading, and re-replicated after the upgrade. If you choose to keep a DR database, you must stop replicating to it before the upgrade. Note that this DR database cannot be used for replication after the upgrade.

If upgrading from SingleStoreDB 7.x with DR clusters, SingleStore recommends that you upgrade your DR secondary cluster(s) one at a time, and then upgrade your primary cluster last so that replication will continue to work after each upgrade.

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

In addition, run the following SQL commands from the Master Aggregator to confirm that the following are true.

  • All leaf nodes ("leaves") are online.

    SHOW LEAVES;

  • All aggregators are online.

    SHOW AGGREGATORS;

  • There are no partitions with an Orphan role.

    SHOW CLUSTER STATUS;

  • No rebalance or restore redundancy is necessary.

    EXPLAIN REBALANCE PARTITIONS;
    EXPLAIN RESTORE REDUNDANCY;

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 SingleStoreDB using the SingleStoreDB management tools or MemSQL Ops.

Upgrade Versions and Methods

The tables below depicts which versions of SingleStoreDB can be upgraded to SingleStoreDB 7.3 and the method by which the cluster can be upgraded.

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

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

Upgrade via SingleStoreDB Toolbox

Upgrade from

Offline upgrade

Online upgrade

7.0

✔

From 7.0.16 and later

6.8

✔

From 6.8.19 and later

6.7

✔

From 6.7.27 and later

6.5

✘

✘

Upgrade via MemSQL Ops

Upgrade from

Offline upgrade

Online upgrade

7.0

✔

✘

6.8

✔

✘

6.7

✔

✘

6.5

✔

✘

Upgrade Your Cluster

Select an option below to upgrade your cluster.

Post-Upgrade Considerations

When upgrading to SingleStoreDB 7.3, 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 SingleStoreDB 7.3 may differ compared to a newly installed cluster on SingleStoreDB 7.3 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 SingleStoreDB 7.3 clusters but not automatically enabled on clusters upgraded from an earlier version to 7.3. In some of these cases, it is recommended to enable the new features, after appropriate testing.

Upgrades to 7.3

  • To reduce your total cost of ownership (TCO), you may be able store data in Universal Storage instead of rowstores. This is because rowstores store their data in RAM, which can be costly. Universal Storage now supports upserts, which were previously only supported in rowstores.

  • You may want to run the command REBALANCE ALL DATABASES. This command rebalances each database in the cluster, in alphabetical order of the database name. When a rebalance runs on a database d, it first considers the placement of the partitions of the other databases in the cluster before rebalancing the partitions of d.

  • You may want to set the cardinality_estimation_level engine variable to '7.3'. This setting uses sampling and histograms together (when both are available) to improve selectivity estimation. The default setting is '7.1'.

  • Changing the value of the data_conversion_compatibility_level engine variable can change the behavior of expressions in computed columns. Refer to the Data Type Conversion 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 SingleStoreDB, in clusters with high availability enabled, replication between master partitions and replica partitions happened asynchronously. Starting with 7.0, synchronous replication is enabled 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.3, 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 Set 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

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

Last modified: November 2, 2023

Was this article helpful?

On this page

Was this article helpful?