Adding new rows screws up Autonumber and Row ID#

[FrancoisS]

@olgatrykush is there any chance that this gets fixed in the near future? These non-consecutive lines are really threatening the operability of our database in the medium term, so if there is no fix I will need to move to another provider…

Combined with other issues such as the lines changing order after some copy-paste, and some lines refusing to be re-ordered except by sorting their column, the non-consecutive lines create a huge risk that our samples ID will change at some point, which would be catastrophic.

Would there be at least a way to add a large number of empty entries and maybe also ensure that these will be consecutive?

[davide]

Hello @FrancoisS,

unfortunately we’re not able to replicate the issue and we don’t have any other report of similar issues.

Given that we’re using a sequence in the database, the most likely explanation is that the transaction fails after the sequence values have been generated but before they are committed. Since this is happening within the ORM, only a few scenarios could cause this, such as validation errors, explicit transaction rollbacks, or database constraints being violated. These events might lead to gaps in the sequence because the sequence increments are not rolled back even if the transaction fails.

I’ll try to investigate this further, but if we’re not able to replicate the issue, we won’t be able to fix it.
Out of curiosity, are you self-hosting Baserow, or is it on our SaaS? If you are self-hosting, there’s a chance it might be related to the connection pooler.

As a workaround, would the option to renumerate the field be helpful here? We don’t have a proper button yet, but for now you can drop and recreate the field with the same name to renumber all the rows correctly.

[FrancoisS]

Hi @davide,

We are not self-hosting, but we are using Eduroam to connect to the internet. Do you think that might be causing issues? I know they have strange configuration parameters (e.g. I can receive but not send messages on some dating apps while on Eduroam) and the connection is not particularly stable. Could this be a factor? This was not happening with Airtable, however.

I am currently renumerating the field every time non-consecutive IDs are generated (about once every 2 weeks across all my sheets), but there is always a chance I forget to sort the column by ID# first and screw up the whole database. Unlikely, but devastating if it happens. It also demands that I be the one creating all of the entries across three sheets to spot the problem and be able to fix it before anyone has a chance to use the wong ID numbers. So yeah, it’s OK for now but it’s far too fragile and labour intensive to scale.

[FrancoisS]

I don’t know if this helps but creating an entry can take up to a few seconds, but it does create the entry in the end. So it might be failing several times in completing the entry creation, but it always works in the end. Even when it skips 30 lines, we only clicked once.

[FrancoisS]

@davide something new happened that might give you more information. In short, I changed a formula yesterday. This morning, a colleague noticed that one of the dependant columns returned a nonsensical value, and it looked like my change had been cancelled. After refreshing my page, everything went back to normal. I then tried to add another row, and it skipped rows again, once again a different number was also skipped in the autonumber. I am willing to bet that both problems are related (somehow, the database is not syncing properly and creating fantom rows at the same time as it struggles to remember yesterday’s changes)

Here is the long story:

Yesterday I made a small change to a formula (let’s call it [Formula 1]):

it was named “GG part” and consisted of

concat(field(‘5'overhang (-ATG)’),field(‘Feature’),field(‘3'overhang’))

It was renamed to “Actual part”, whose formula became

if(isblank(field(‘HiFi part’)),
concat(field(‘5'overhang (-ATG)’),field(‘Feature’),field(‘3'overhang’)),
field(‘HiFi part’)).

That field is used as input for a different formula, (let’s call it [Formula 2]) used to calculate the volume of water needed to disolve that sequence of DNA at a 50 nM:

if(isblank(field(‘Mass synthesized (ng)’)),“”,
round(
field(‘Mass synthesized (ng)’)/(length(field(‘Actual part’))*615.96+36.04)/50*1000000,
1))

This morning, a colleague reported a problem in [Formula 2]: it was giving 6 digits numbers instead of 2-3. When I looked into it, everything appeared normal to me, until I was logged out by Baserow because an expired authentification token. When i logged back in, I could see the same problem. All the other values were very similar, starting with 722 and followed by 3 digits and one decimal.

I tinkered with the math to make sure everything was still correct, but it wouldn’t let me save because “The field ‘GG part’ did not exist”. That is when I noticed that [Formula 1] was indeed still using “GG part” as a field name, and in fact the [Formula 1] column was named “GG part” again, and its formula still consistent with the “GG part” formula. It was upsetting that my changes had been cancelled, but if anything the “GG part” field still existed, so the [Formula 2] was being inconsistent with its own error message.

So I refreshed the page. The numbers were now different, but still wrong. A quick look showed that one parenthesis had been deleted in my formula, probably resulting from me refreshing the page mid-tinkering with the math. After adding the parenthesis, the numbers were normal again.

I wondered if the same synchronisation problem that caused this nonsense number until I refreshed my bowser would have also created more empty lines, so I went to create one more row, and voila! the row ID was nonconsecutive again, and the gap was different in the rowID and in the Autonumber column.

(Look at the last two lines)

Does this give you any new clue as to what could be the cause of these synch problems?

[dev-rd]

I’m also very much interested in these errors.
While the ones experienced by my team pertain mainly to filters and sorting, I was able to associate at least some of them with “synchronisation” issues, similar to the ones reported in this thread.
This is an example of the error co-occurring with the filter/sort issues. (refresheing the page would usually solve the issue)

Since some of my team-members report filter/sort issues without seeing the “connection to the server has failed” error, I would be interested to know if such a state can occur without the error message popping up (we are on the self-hosted version, though).

[FrancoisS]

None of the problems I reported in this thread happened in the presence of the “Connection to the server has failed” error message. But maybe these problems happen when the connection is lost, and only become apparent after connection is re-established?

[dev-rd]

One of the issues we encountered was that the table contents (rows) seems to refresh upon scrolling, even with the message present, while the rest of the app (filters, sorting, etc) seems to stay “out of sync” (so it works with the data before the refresh) - and that seems to create discrepancies for filters/sorting in our case.

I suspect this may happen without the error message, it’s just that I was able to reproduce some of the errors with the message present.
These might turn out to be separate issues in the end, but I guess we’d have to wait for someone from the Baserow dev team to take a closer look at this.

[davide]

@FrancoisS

We are not self-hosting, but we are using Eduroam to connect to the internet. Do you think that might be causing issues?

Eduroam might cause problems with websockets and how the notifications are show, but it cannot corrupt data or formulas in our database or make a database sequence to skip numbers without reason. To confirm: are these errors happening on baserow.io? If so, would you mind sharing on which workspace/database and from which account? With your permission, I’d like to look at our audit logs to understand if anything strange can explain the described behavior.

[FrancoisS]

Yes it is happening on baserow.io. I have shared my account and database details with you in a private message.

Thanks a lot for looking into this!

[FrancoisS]

I have stopped updating this thread to avoid spamming but the issue keeps happening. Just now I added a row and it was nonconsecutive. It seems the longer I go without adding a row, the most likely the next row I add will be nonconsecutive. I cannot remember the last time I added a row that was consecutive with one added on a different day.

[kev_z]

Hello everyone, I have encountered a similar situation. I have finally found a formula to set up my row ID but every time when I delete a row, it screws up my auto number also… like that row number is permanently deleted and it sets up a new number which I don’t want. I want all numbers from auto number field to match the row number. So how can I go about fixing this?

[olgatrykush]

@kev_z You’ll need to restart the sequence. Currently, to get the numbers in sequential order, you need to delete and recreate the field.