Privilege required for executing AGGREGATOR SYNC AUTO_INCREMENT command

{question}

What is the privilege required for executing the AGGREGATOR SYNC AUTO_INCREMENT command?

{question}

{answer}

In this article, we will discuss the privileges required for executing the AGGREGATOR SYNC AUTO_INCREMENT command. This command is used to fix one or more auto_increment counters lower than the currently inserted values in a table. Run this command directly on the aggregator that has a broken counter. It updates the auto_increment counter by querying the leaves for the next appropriate value. This value is used if it does not decrease the counter. Specifying ALL will run the sync on all aggregators instead of just the current aggregator (ALL options available from SingleStore version 7.3 and newer).

Until 7.5.8, Database users require CLUSTER permissions to execute AGGREGATOR SYNC AUTO_INCREMENT. Users without CLUSTER permissions will receive the below errors when they try to run the command:

ERROR 1227 (42000): Access denied; you need (at least one of) the CLUSTER privilege(s) for this operation

From 7.5.9 of SingleStore 7.5. The AGGREGATOR SYNC AUTO_INCREMENT command now can be performed by anyone with the ability to update the target database(s) or table in any way. Click here to check the 7.5.9 release notes. Click here to learn about upgrading to the latest patch version of SingleStore 7.5. For SingleStore CloudDB Cluster upgrade, Reach out to SingleStore Support.

Example:

Created a user just with table access:

mysql> select @@memsql_version;
+------------------+
| @@memsql_version |
+------------------+
| 7.5.9 |
+------------------+
1 row in set (0.01 sec)

mysql> GRANT INSERT ON testing.test TO 'memsql_testing'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye

Connecting as thememsql_testinguser:

master-agg-ip-1-0-27 /home/admin $ mysql -h ec2.compute-1.amazonaws.com -u memsql_testing -P 3306 -ppassword

mysql> use testing;
Database changed

mysql> AGGREGATOR SYNC AUTO_INCREMENT ON testing.test;
Query OK, 1 row affected (0.00 sec)

Click here to learn more about the AGGREGATOR SYNC AUTO_INCREMENT command.

{answer}