5.5.2 The reset Command

reset [--sequential-restart] filter_specification_list cluster_name

filter_specification_list:
    filter_specification[,filter_specification][,...]

filter_specification:
    attribute_name[:process_specification][+process_specification]]

process_specification:
    [process_name][:process_id]

process_name:
    {ndb_mgmd|ndbd|ndbmtd|mysqld|ndbapi}

This command resets an attribute to its default value. Attributes can be set on either the process level or instance level. To reset an attribute on the process level, use a filter specification having the form attribute_name:process_name, where attribute_name is the name of the attribute to be reset, and process_name is the name of a MySQL NDB Cluster process. To reset a configuration attribute on the instance level, use a filter specification of the form attribute_name:process_name:process_id, where process_id is the process ID.

You cannot issue a reset command that resets all values for a given configuration attribute regardless of process type; each reset command must specify a process type or instance of a process. Otherwise, the command fails, as shown here:

mcm> reset DataMemory mycluster;
ERROR 3 (00MGR): Illegal syntax

You also cannot revert all configuration attributes for a given process type or instance of a process using a single filter specification; you must always include the name of the attribute to be reset. Otherwise, the reset command fails, as shown here:

mcm> reset :ndbd mycluster;
ERROR 3 (00MGR): Illegal syntax

mcm> reset :ndbd:3 mycluster;
ERROR 3 (00MGR): Illegal syntax

Suppose that the data memory for all ndbd processes in the cluster named mycluster has been set to 500 MB, as shown in the output of this get command:

mcm> get DataMemory mycluster;
+------------+-------+----------+-----+----------+-----+---------+---------+
| Name       | Value | Process1 | Id1 | Process2 | Id2 | Level   | Comment |
+------------+-------+----------+-----+----------+-----+---------+---------+
| DataMemory | 500M  | ndbd     | 2   |          |     | Process |         |
| DataMemory | 500M  | ndbd     | 3   |          |     | Process |         |
+------------+-------+----------+-----+----------+-----+---------+---------+
2 rows in set (1.91 sec)

We can see from the entries in the Level column that the DataMemory setting for both ndbd processes applies on the process level. A process-level setting cannot be reset on the instance level, as shown here:

mcm> reset DataMemory:ndbd:2 mycluster;
ERROR 6010 (00MGR): No matching user defined setting was
found for config attribute DataMemory
mcm> reset DataMemory:ndbd:3 mycluster;
ERROR 6010 (00MGR): No matching user defined setting was
found for config attribute DataMemory

The following reset command also does not work, although you might think that it would do so, since it attempts to reset the attribute's value for both ndbd processes:

mcm> reset DataMemory:ndbd:2,DataMemory:ndbd:3 mycluster;
ERROR 6010 (00MGR): No matching user defined setting was
found for config attribute DataMemory

The previous command fails because MySQL Cluster Manager regards this as an attempt to apply two instance-level configuration changes. Because the DataMemory setting is a process-level setting, you must instead reset DataMemory to its default value on the process level; you can do this by using the filter specification DataMemory:ndbd in the reset command, as shown here:

mcm> reset DataMemory:ndbd mycluster;
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (6.16 sec)

If you execute the same get command as shown previously, the result is now empty:

mcm> get DataMemory mycluster;
Empty set (0.74 sec)

This is because the get command by default does not report default values. To retrieve the DataMemory values after resetting them, you must invoke get using the --include-defaults (short form: -d) option:

mcm> get --include-defaults DataMemory mycluster;
+------------+----------+----------+-----+----------+-----+---------+---------+
| Name       | Value    | Process1 | Id1 | Process2 | Id2 | Level   | Comment |
+------------+----------+----------+-----+----------+-----+---------+---------+
| DataMemory | 83886080 | ndbd     | 2   |          |     | Default |         |
| DataMemory | 83886080 | ndbd     | 3   |          |     | Default |         |
+------------+----------+----------+-----+----------+-----+---------+---------+
2 rows in set (1.21 sec)

The DataMemory values are now included in the output, and are marked with the word Default in the Comments column.

Now suppose that the mysqld configuration attribute wait_timeout for the mysqld process having the ID 4 in the cluster named mycluster has previously been set to the value 200 as shown here, and that no other changes have been to this attribute:

mcm> set wait_timeout:mysqld:4=200 mycluster;
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (7.78 sec)

mcm> get -d wait_timeout:mysqld:4 mycluster;
+--------------+-------+----------+-----+----------+-----+-------+---------+
| Name         | Value | Process1 | Id1 | Process2 | Id2 | Level | Comment |
+--------------+-------+----------+-----+----------+-----+-------+---------+
| wait_timeout | 200   | mysqld   | 4   |          |     |       |         |
+--------------+-------+----------+-----+----------+-----+-------+---------+
1 row in set (0.98 sec)

Because the Level column is empty, we know that this setting applies on the instance level. If you try to reset it on the process level, the attempt fails, as shown here:

mcm> reset wait_timeout:mysqld mycluster2;
ERROR 6010 (00MGR): No matching user defined setting was
found for config attribute wait_timeout

If you wish to reset this attribute to its default value, you must use the reset command with the instance-level filter specification wait_timeout:mysqld:4, as shown here:

mcm> reset wait_timeout:mysqld:4 mycluster;
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (7.61 sec)

Once you have reset wait_timeout, it no longer appears in the output of the earlier get command:

mcm> get wait_timeout:mysqld mycluster;
Empty set (1.42 sec)

This is because the default behavior of the get command is to display only those values that have been set either by the MySQL Cluster Manager or by the user. Since wait_timeout has been allowed to revert to its default value, you must use the --include-defaults (short form: -d) option to retrieve it, as shown here:

mcm> get -d wait_timeout:mysqld mycluster;
+--------------+-------+----------+-----+----------+-----+---------+---------+
| Name         | Value | Process1 | Id1 | Process2 | Id2 | Level   | Comment |
+--------------+-------+----------+-----+----------+-----+---------+---------+
| wait_timeout | 28800 | mysqld   | 4   |          |     | Default |         |
+--------------+-------+----------+-----+----------+-----+---------+---------+
1 row in set (1.66 sec)

Now consider a situation in which process-level and instance-level settings have been made to a configuration attribute; in this example, we use IndexMemory. First, verify that IndexMemory is set to its default value for all data node processes (in this case, there are two of them):

mcm> get -d IndexMemory mycluster;
+-------------+----------+----------+-----+----------+-----+---------+---------+
| Name        | Value    | Process1 | Id1 | Process2 | Id2 | Level   | Comment |
+-------------+----------+----------+-----+----------+-----+---------+---------+
| IndexMemory | 18874368 | ndbd     | 2   |          |     | Default |         |
| IndexMemory | 18874368 | ndbd     | 3   |          |     | Default |         |
+-------------+----------+----------+-----+----------+-----+---------+---------+
2 rows in set (1.24 sec)

Now apply both a process-level change and an instance-level change to this attribute. You can do this with a single set command, as shown here:

mcm> set IndexMemory:ndbd=500M,IndexMemory:ndbd:3=750M mycluster;
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (7.29 sec)

Because the process-level change was specified first, it is overridden for the ndbd process by the instance-level change specified second. The output from the following get command confirms that this is the case:

mcm> get IndexMemory mycluster;
+-------------+-------+----------+-----+----------+-----+---------+---------+
| Name        | Value | Process1 | Id1 | Process2 | Id2 | Level   | Comment |
+-------------+-------+----------+-----+----------+-----+---------+---------+
| IndexMemory | 500M  | ndbd     | 2   |          |     | Process |         |
| IndexMemory | 750M  | ndbd     | 3   |          |     |         |         |
+-------------+-------+----------+-----+----------+-----+---------+---------+
2 rows in set (0.85 sec)

If the instance-level IndexMemory setting for the ndbd process with process ID 3 is reset, the process-level setting still applies, as shown here:

mcm> reset IndexMemory:ndbd:3 mycluster;
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (6.41 sec)

mcm> get IndexMemory mycluster;
+-------------+-------+----------+-----+----------+-----+---------+---------+
| Name        | Value | Process1 | Id1 | Process2 | Id2 | Level   | Comment |
+-------------+-------+----------+-----+----------+-----+---------+---------+
| IndexMemory | 500M  | ndbd     | 2   |          |     | Process |         |
| IndexMemory | 500M  | ndbd     | 3   |          |     | Process |         |
+-------------+-------+----------+-----+----------+-----+---------+---------+
2 rows in set (1.09 sec)

Now, re-apply the instance-level IndexMemory setting, and verify using get that it has taken effect:

mcm> set IndexMemory:ndbd:3=750M mycluster;
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (6.79 sec)

mcm> get IndexMemory mycluster;
+-------------+-------+----------+-----+----------+-----+---------+---------+
| Name        | Value | Process1 | Id1 | Process2 | Id2 | Level   | Comment |
+-------------+-------+----------+-----+----------+-----+---------+---------+
| IndexMemory | 500M  | ndbd     | 2   |          |     | Process |         |
| IndexMemory | 750M  | ndbd     | 3   |          |     |         |         |
+-------------+-------+----------+-----+----------+-----+---------+---------+
2 rows in set (1.76 sec)

If you reset the process-level setting, the instance-level setting remains, and only the ndbd process having process ID 2 has its IndexMemory reset to the default value; the instance-level setting remains in effect, as you can see from the following sequence of commands:

mcm> reset IndexMemory:ndbd mycluster;
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (7.36 sec)

mcm> get -d IndexMemory mycluster;
+-------------+----------+----------+-----+----------+-----+---------+---------+
| Name        | Value    | Process1 | Id1 | Process2 | Id2 | Level   | Comment |
+-------------+----------+----------+-----+----------+-----+---------+---------+
| IndexMemory | 18874368 | ndbd     | 2   |          |     | Default |         |
| IndexMemory | 750M     | ndbd     | 3   |          |     |         |         |
+-------------+----------+----------+-----+----------+-----+---------+---------+
2 rows in set (0.10 sec)
Note

If the order of the specifiers in the original command that set IndexMemory had been reversed as IndexMemory:ndbd:3=750M,IndexMemory:ndbd=500M, the instance-level change would have been overridden by the process-level change, and the resulting IndexMemory setting for both ndbd processes would be 500M. As discussed elsewhere, a process-level setting made after an instance-level setting that affects the same process completely removes the instance-level setting; the instance-level setting is not preserved, and resetting the attribute on the process level merely restores the default setting for all processes of that type. See Section 5.5, “MySQL Cluster Manager Configuration Commands”, for more information.

The get and reset commands fully support multi-entry replication attributes; for example, if the replicate_ignore_table attribute has multiple entries:

mcm> get replicate_ignore_table:mysqld mycluster;
+------------------------+--------------+----------+---------+----------+---------+---------+-------------+
| Name                   | Value        | Process1 | NodeId1 | Process2 |NodeId2  | Level   | Comment     |
+------------------------+--------------+----------+---------+----------+---------+---------+-------------+
| replicate_ignore_table | mydb.t1      | mysqld   | 50      |          |         |         | Multi-entry |
| replicate_ignore_table | mydb.t50     | mysqld   | 50      |          |         |         | Multi-entry |
| replicate_ignore_table | mydb.mytable | mysqld   | 50      |          |         | Process | Multi-entry |
| replicate_ignore_table | mydb.t51     | mysqld   | 51      |          |         |         | Multi-entry |
| replicate_ignore_table | mydb.mytable | mysqld   | 51      |          |         | Process | Multi-entry |
+------------------------+--------------+----------+---------+----------+---------+---------+-------------+
5 rows in set (0.05 sec)

Without specifying a node ID, all the attribute's entries associated with the specified process type are reset with the following command:

mcm> reset replicate_ignore_table:mysqld mycluster;  # removes all process level entries
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (0.47 sec)

mcm> get replicate_ignore_table:mysqld mycluster;
+------------------------+----------+----------+---------+----------+---------+-------+-------------+
| Name                   | Value    | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment     |
+------------------------+----------+----------+---------+----------+---------+-------+-------------+
| replicate_ignore_table | mydb.t1  | mysqld   | 50      |          |         |       | Multi-entry |
| replicate_ignore_table | mydb.t50 | mysqld   | 50      |          |         |       | Multi-entry |
| replicate_ignore_table | mydb.t51 | mysqld   | 51      |          |         |       | Multi-entry |
+------------------------+----------+----------+---------+----------+---------+-------+-------------+
3 rows in set (0.08 sec)

With a node ID specified, only the instance entries associated with the node ID are reset by the following command:

mcm> reset replicate_ignore_table:mysqld:51 mycluster;  # removes all instance level entries for nodeid 51
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (0.57 sec)

mcm> get replicate_ignore_table:mysqld mycluster;
+------------------------+----------+----------+---------+----------+---------+-------+-------------+
| Name                   | Value    | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment     |
+------------------------+----------+----------+---------+----------+---------+-------+-------------+
| replicate_ignore_table | mydb.t1  | mysqld   | 50      |          |         |       | Multi-entry |
| replicate_ignore_table | mydb.t50 | mysqld   | 50      |          |         |       | Multi-entry |
+------------------------+----------+----------+---------+----------+---------+-------+-------------+
2 rows in set (0.09 sec)

reset commands are executed whether or not the cluster has been started. In a cluster that is not running, the MySQL Cluster Manager merely updates the configuration files. However, in a running cluster, the MySQL Cluster Manager in addition automatically performs any node restarts or rolling restarts (see Performing a Rolling Restart of an NDB Cluster) that are required to cause the attribute changes to take effect (use the --sequential-restart option to make the rolling restart a sequential one). However, since restart operations—particularly rolling restarts—can take a great deal of time, it is preferable to make configuration changes before starting the cluster and putting it into use.

Resetting TCP Connection Attributes.  Certain configuration attributes, such as those relating to TCP connections, apply to connections between processes rather than to individual processes or individual process types. As shown elsewhere (see Setting Attributes for mysqld nodes), when you set such an attribute on the process level using MySQL Cluster Manager, this means that the attribute applies to all connections between the two types of processes specified when issuing the set command. It is also possible to set such an attribute on the instance level, in which case it applies only to a single connection between two process instances.

Similarly, it is possible to reset such an attribute on either the process or instance level, depending on the level or levels at which it was set. In either case, an extended form of the process specifier is required, just as it is when setting an attribute that applies to a connection between processes. Assume that the SendBufferMemory attribute has previously been set for all connections between the two ndbd processes and the two mysqld processes that are found in a MySQL NDB Cluster named mycluster2, as shown in the output of this get command:

mcm> get SendBufferMemory mycluster2;
+------------------+-------+----------+-----+----------+-----+---------+---------+
| Name             | Value | Process1 | Id1 | Process2 | Id2 | Level   | Comment |
+------------------+-------+----------+-----+----------+-----+---------+---------+
| SendBufferMemory | 4M    | ndbd     | 2   | mysqld   | 4   | Process |         |
| SendBufferMemory | 4M    | ndbd     | 2   | mysqld   | 5   | Process |         |
| SendBufferMemory | 4M    | ndbd     | 3   | mysqld   | 4   | Process |         |
| SendBufferMemory | 8M    | ndbd     | 3   | mysqld   | 5   |         |         |
+------------------+-------+----------+-----+----------+-----+---------+---------+
4 rows in set (0.59 sec)

Suppose that you wish to reset SendBufferMemory only for the connection between the ndbd process having process ID 3 and the mysqld process having process ID 5. The SendBufferMemory setting that applies to this connection is specified on the instance level, as you can see because the Level column value corresponding to this connection is empty; this means that it is possible to reset this value on the instance level. You can do this using the reset command shown here:

mcm> reset SendBufferMemory:ndbd:3+mysqld:5 mycluster2;
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (7.03 sec)

You can verify that the attribute was reset using the get command. However, as noted previously, once the instance-level setting has been removed, the process-level setting for this attribute again takes effect, so that the same setting applies to all connections between ndbd and mysqld processes, as shown here:

mcm> get SendBufferMemory mycluster2;
+------------------+-------+----------+-----+----------+-----+---------+---------+
| Name             | Value | Process1 | Id1 | Process2 | Id2 | Level   | Comment |
+------------------+-------+----------+-----+----------+-----+---------+---------+
| SendBufferMemory | 4M    | ndbd     | 2   | mysqld   | 4   | Process |         |
| SendBufferMemory | 4M    | ndbd     | 2   | mysqld   | 5   | Process |         |
| SendBufferMemory | 4M    | ndbd     | 3   | mysqld   | 4   | Process |         |
| SendBufferMemory | 4M    | ndbd     | 3   | mysqld   | 5   | Process |         |
+------------------+-------+----------+-----+----------+-----+---------+---------+
4 rows in set (0.87 sec)

To reset this attribute on the process level, you can use the following reset command:

mcm> reset SendBufferMemory:ndbd+mysqld mycluster2;
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (8.01 sec)

You can verify that the attribute has been reset for all connection between ndbd processes and mysqld processes, by using the get command, as shown here:

mcm> get -d SendBufferMemory mycluster2;
Empty set (1.39 sec)

As noted elsewhere in this manual (see Section 5.5.1, “The get Command”), the empty result set is to be expected in this case, even when get is invoked using the --include-defaults (or -d) option, because the MySQL Cluster Manager client does not display attributes that appear in the [tcp] or [shm] sections of the config.ini configuration file if they have not been explicitly set by the user.