AUTO_INCREMENT ( AGGREGATOR SYNC AUTO_INCREMENT is out of working)

Off-Topic
#1

https://docs.singlestore.com/db/v7.3/en/reference/sql-reference/data-definition-language-ddl/create-table.html#auto_increment-in-sharded-tables

I read the document above.

<CLUSTER 1>

I tested ‘auto_increment’ as below.

CREATE TABLE roll (id BIGINT PRIMARY KEY AUTO_INCREMENT);

INSERT INTO roll VALUES (5);

AGGREGATOR SYNC AUTO_INCREMENT;

INSERT INTO roll VALUES ();

parseLengthCodedNumber: JS precision range exceeded, number is >= 53 bit: “72057594037927937”

SELECT * FROM roll;

±------------------+

| id |

±------------------+

| 5 |

| 72057594037927937 |

±------------------+

<CLUSTER 2>

So, I conducted a test in another cluster.

However, there was no problem.

The difference I think is that ‘CLUSTER 1’ did a ‘IP, HOST’ change and upgrade.

Please let me know the problem with this.

#2

In general, auto_increment values are “bigint” values and you should use a 64-bit int type on the client side to hold values from an auto_increment column. The error message indicates that the ID number returned has too much precision for the variable being filled with the data.

An alternative would be to use app code to create ID numbers, rather than using auto_increment.

#3

Thanks,

But, I want to know below (auto_increment).

<cluster 1- issue>

DROP TABLE roll ;
CREATE TABLE roll (id BIGINT PRIMARY KEY AUTO_INCREMENT);
SELECT * FROM roll;
> Empty set (0.00 sec)
SHOW CREATE TABLE roll;
----------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                              
+-------+--------------------------------------------------------------------------------------------------------------------------
| roll  | CREATE TABLE `roll` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ****AUTO_INCREMENT=72057594037927937**** AUTOSTATS_CARDINALITY_MODE=PERIODIC AUTOSTATS_HISTOGRAM_MODE=CREATE SQL_MODE='STRICT_ALL_TABLES' |
+-------+--------------------------------------------------------------------------------------------------------------------------

<cluster 2>

  SHOW CREATE TABLE roll;
+-------+--------------------------------------------------------------------------------------------------------------------------
| Table | Create Table                                                                                                                                                                            
+-------+--------------------------------------------------------------------------------------------------------------------------
| roll  | CREATE TABLE `roll` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) AUTOSTATS_CARDINALITY_MODE=PERIODIC AUTOSTATS_HISTOGRAM_MODE=CREATE SQL_MODE='STRICT_ALL_TABLES' |
+-------+--------------------------------------------------------------------------------------------------------------------------

The value of ‘AUTO_INCREMENT’ is fixed when creating a table at ‘cluster 1’.
Is there a way to reset the value of ‘AUTO_INCREMENT’ like ‘cluster 2’?

#4

You can reset the value of the next auto_increment value for reference tables, but not sharded tables. This covers that:

#5

I have one more question.
Is the ‘warning of studio’ correct on below?
To get rid of the ‘warning of studio’, do I need additional work?

image
image776×338 20.9 KB

#6

That looks like a bug in Studio. I will report it.

Also, you can only set auto_increment when creating a reference table. Otherwise it is a Noop. You created a regular (distributed) table.