Upgraded to 6.8; primary key ordering has become out of order

Off-Topic
1

We upgraded to MEMSQL to 6.8 and our PrimaryKey’s - which are auto generated - have taken a large step up. System was creating in order, ie, 1,2,3 for Primary Key’s and now, system has jumped to 114841790497947649 and is creating sequentially from that point. i know we shouldn’t rely on a system generated id for our own numbering…but our old code was using that method.

Is this an anomaly/error? or is this expected?

2

Hi Mark,

This is by design. Please see

If you want small, sequential numbers, we recommend you allocate them with application logic. E.g. allocate numbers 1 at a time, or 100 at a time, in a transaction, hold them in the app, use them up, then allocate more. The counter state can be maintained in a table.

Also, be sure to use 64 bit int variables to hold auto_increment values in client application software because they won’t in general fit in 32 bit ints.

Also, if you are upgrading anyway, it’s highly recommended to move to 7.3.

3

Thanks Hanson for the response.

The auto assigned numbers is 18 characters long. most of our customers/analysts use excel for their analysis. and excel truncates numbers more than 15 characters long. so a number like 123…5890 becomes 123…5000. Anything more than 15 characters long gets converted to 000.

so this is causing problems for us. Is it possible in anyway to keep the auto assigned number shorter? we are trying to avoid making large number of api changes on our end.

noted your point on 7.3 upgrade. we’ll do that as soon as 6.* stabalizes.

thanks again for your insights.