The doco for Forge SQL recommends:
Avoid using AUTO INCREMENT fields in your tables, as Forge SQL is based on a distributed SQL database and sequences are not guaranteed to be globally unique.
The statement that “sequences are not guaranteed to be globally unique” is somewhat incorrect. Auto Increment will be unique, especially if you use a unique index, they will not be consecutively numbered and may well have gaps, you may even exhaust the pool of available numbers due to cache dumps when the nodes are restarted.
Also Auto Increment values are not sequences. The Auto increment values are not globally monotonic (i.e. increase in value across all nodes)
See https://docs.pingcap.com/tidbcloud/auto-increment/#auto_increment for more info.
Question So regarding that, what is that recommendation referring to? It seems to me Auto_Increment is globally unique, can you point to where it is not?
Follow up Question: If we should not use Auto Increment, what are the recommended alternatives for say the unique ID field?
Cheers,
Peter.
3 Likes
BTW I’m guessing the suggested alternative is using a sequence.
Hi Peter, thanks for raising this up - you are absolutely right, while using AUTO_INCREMENT in Forge SQL is not recommended, the actual reason behind it is not correctly articulated and lacks alternatives, we will address it.
The actual reason to avoid AUTO_INCREMENT is that it can cause a hot spot issue in TiDB when used excessively on large datasets - Troubleshoot Hotspot Issues
Two recommended alternatives are:
1 Like
Interesting I just changed an app to use AUTO_RANDOM in place of AUTO_INCREMENT and, well, it’s generated the exact same id numbers on the primary key id field!
This only happens in forgeSQL when I run the same statements on TiDB it works as expected.
Also the migrationRunner is failing to run all the migration steps because it times out, the next time it runs the rest of the migration script.
cause: Error: Network request can not be made: Forge proxy authorization expired 1 seconds ago. It's likely caused by code from a previous function invocation still running. Please ensure that all promises in your code are properly awaited.
at get token [as token] (/tmp/forge-dist-34596-OvOGdHOCLOBA/__forge_wrapper__.cjs:2:1144351)
at r.getProxyToken (/tmp/forge-dist-34596-OvOGdHOCLOBA/__forge_wrapper__.cjs:2:892943)
at /tmp/forge-dist-34596-OvOGdHOCLOBA/__forge_wrapper__.cjs:2:1139686
at F (/tmp/forge-dist-34596-OvOGdHOCLOBA/__forge_wrapper__.cjs:2:1140368)
at N (/tmp/forge-dist-34596-OvOGdHOCLOBA/__forge_wrapper__.cjs:2:1141583)
at <anonymous> (webpack://jira-global-page-custom-ui/node_modules/node-fetch/lib/index.mjs:1472:1)
at new Promise (<anonymous>)
at fetch (webpack://jira-global-page-custom-ui/node_modules/node-fetch/lib/index.mjs:1441:1)
at <anonymous> (webpack://jira-global-page-custom-ui/node_modules/@forge/sql/node_modules/@forge/api/out/api/fetch.js:27:1)
at SqlClient.sendRequest (webpack://jira-global-page-custom-ui/node_modules/@forge/sql/out/sql.js:14:1)
}
Interestingly in TiDB they are unique, but not really that random!
The code for the table looks like this:
create table entity_type
(
id bigint primary key auto_random,
entity_name varchar(100) null,
...
and this doco says that should work 
https://docs.pingcap.com/tidbcloud/auto-random/
I might just go back to a sequence or Auto_Increment.
1 Like
