How to get inserted row id in forge sql with AUTO_RANDOM?

Forge
, ,
1

I have created table

CREATE TABLE IF NOT EXISTS table1 
( id BIGINT AUTO_RANDOM PRIMARY KEY, name VARCHAR(256) , status VARCHAR(20) )

I am inserting rows

const data = await sql.
prepare(insert into table1(name, status) values(?,?))
.bindParams('aa','bb').execute(); 

console.log(data.rows);

It gives

{ fieldCount: 0, affectedRows: 1, 
insertId: '6052837899185946626', 
info: '', serverStatus: 2, warningStatus: 0, changedRows: 0 }

however when i fetched id from table it gives 6052837899185947000.

it shows insertId: ‘6052837899185946626’ but in table it is 6052837899185947000? see last 5 digits are different or rounded.

Seems like the returned insertId worked with only AUTO_INCREMENT and not with AUTO_RANDOM

on page https://developer.atlassian.com/platform/forge/storage-reference/sql-api-schema/ at the bottom it is mentioned

  • Avoid using AUTO_INCREMENT fields in your tables, as this could cause hotspot issues when used on databases with very large datasets. We recommend either of the following strategies instead:

It is encouraged to use AUTO_RANDOM, but there is no way i can get the inserted ID. Storing UUID in binary format has it’s own complications when you want to retrieve it in binary format and again store it in different table column as Foriegn key. i have issue open How to bind binary column in bulk insert in forge SQL? .

I wanted to avoid storing uuid in plain text format because of performance and storage issue.

What is the way to get insert id for AUTO_RANDOM?

or i don’t have option and have to use AUTO_INCREMENT here?

Thanks

Shiv

2

Hey @shiv

Thanks for raising this. This seems to be a bug in our underlying driver and we are working on a fix. We’ll update you once the fix is tested and deployed

+ @AshleyBartlett

Thanks,

Varun

2 Likes
3

Thanks for reporting this!

I’ve found that we are losing precision on BIGINT types when they are converted to the JSON response format. The returned insertID is correct ID, and as you identified the numebers in SELECT statements are losing precision to the 1000’s.

To fix this we will need to consider how we return big int numbers, which will need to be specifically handled using a BigInt, as Javascript Number type doesn’t support these numbers.

4

In light of Sudden Change in Forge SQL Returning Strings Instead of Numbers - #13 by varun, we’ve had to pause our rollout that fixes this issue.

As a workaround, as defined here, https://docs.pingcap.com/tidb/stable/auto-random/, you could consider using AUTO_RANDOM(5, 54)instead of AUTO_RANDOM for unsigned columns.

1 Like
5

Hello,
we are currently using forge sql to create some tables,
we noticed that BigInt with auto_random has issues, so we are planning to change it to int with AUTO_INCREMENT,
we used these links

is this good solution,
if it is, can you please guide us how to update the tables, we tried alter table query but it didn’t correct,
kind regards,

6

HI @AliSalem1

Short answer: switching to INT AUTO_INCREMENT is not recommended in Forge SQL / TiDB, and what you’re seeing with ALTER TABLE is expected behavior and explicitly mentioned in the documentation .

A couple of important points:

  • TiDB (which Forge SQL is built on) explicitly warns that AUTO_INCREMENT can create hotspots in production and recommends using alternatives.

  • AUTO_INCREMENT has strict limitations in TiDB:

    • it can only be defined at CREATE TABLE time
    • it cannot be added later via ALTER TABLE ADD / MODIFY / CHANGE COLUMN
    • ALTER TABLE can only be used to remove AUTO_INCREMENT, not add it

So your migration attempts didn’t fail because of Forge - this is a TiDB limitation.

Since ALTER TABLE is not supported for adding auto-increment strategies, the only way to achieve this change is to create a new table with the desired schema and migrate your data.

Recommended alternatives:

example for SEQUENCE:

CREATE SEQUENCE IF NOT EXISTS user_id_seq

CREATE TABLE  IF NOT EXISTS `user` ( 
`id` int NOT NULL DEFAULT (NEXTVAL(`user_id_seq`)), 
`name` varchar(255) NOT NULL,
 CONSTRAINT `user_id` PRIMARY KEY(`id`) )

Important note about SEQUENCE:
A sequence guarantees monotonic ordering , but not continuity .
Gaps are expected so IDs may look like:

1, 2, 5, 9, 15, 16

For your case you can take a look UUID stored as BINARY(16) or SEQUENCE