Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Autoincrementing primary Key

kpwn's profile image kpwn posted 2 months ago in General Permalink

Hi, i am using the newest portable version of HeidiSQL (11.3) with a mariaDB, also relatively new (10.6). When a table holds no rows, i cannot set an Auto increment value. Everytime i save it it reverts to an empty field. Furthermore if i create rows, it lets me set and autoincrementing value, but always changes it by itself. creating a new row causes the primary key to be auto incremented by 3. Viewing the auto increment option also show the nex value instead of the amount the value should be incremented by.

ansgar's profile image ansgar posted 2 months ago Permalink

Viewing the auto increment option also show the nex value instead of the amount the value should be incremented by.

That's always the case, so very normal.

Can you post your CREATE TABLE code here?

kpwn's profile image kpwn posted 2 months ago Permalink

yes i can, when i click the little green plus this code gets executed: INSERT INTO DBNAME.ProdukteG; the table ProdukteG has default values for all fields besides the primary key which is set to auto_increment. All defalut values are working as expected.

kpwn's profile image kpwn posted 2 months ago Permalink

If it helps i can create an example table with just 2 coloums

ansgar's profile image ansgar posted 2 months ago Permalink

I meant the code from the CREATE code tab:

Description

kpwn's profile image kpwn posted 2 months ago Permalink

ok sure.

i attach 3 images, the create code for an empty table, the data i then put in with the primary key automatically generated and the create code after inserting data.

3 attachment(s):
  • create1
  • data
  • create2
ansgar's profile image ansgar posted 2 months ago Permalink

I was just trying to reproduce that, but on MariaDB 10.3 and 10.5 I had no problems with setting a primary key/auto-increment value in an empty table.

Please update to the latest build and try again. (Help > Check for updates)

kpwn's profile image kpwn posted 2 months ago Permalink

newest "official" release seems to be 11.3, which is my version. The updater offers me a nightly version as an update. Should i try that one?

i will also test if it makes a difference that the mariadb i connect to is inside a galera cluster. (3 node cluster)

kpwn's profile image kpwn posted 2 months ago Permalink

WOW i connected to my 2 node cluster and now autoincrement always increments by 2! So somehow the cluster size affects the autoincrementing value. Is that expected/by-design or unwanted? BTW if you want to reproduce that i can make you a user account on my clusters, the are globally reachable.

ansgar's profile image ansgar posted 2 months ago Permalink

The updater offers me a nightly version as an update. Should i try that one?

Yes.

kpwn's profile image kpwn posted 2 months ago Permalink

I did install the nightly version. The behaviour didnt change. The autoincrement is still beeing incremented by the number of nodes in the cluster.

ansgar's profile image ansgar posted 2 months ago Permalink

I suspect your cluster nodes have a non-1 setting for auto_increment_increment variable?

Here's an explanation why that's a good idea.

kpwn's profile image kpwn posted 2 months ago Permalink

I will ask the DBA about that, but it looks really promising

kpwn's profile image kpwn posted 2 months ago Permalink

i queried for the auto increment settings and that seems to be the case. Awesome, thanks for your help.

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.