MySQL ID exhaustion

[Rytiss]

My result.id field has reached maxint and no new results can be inserted. We likely need to use bigint for result.id, but that may require multiple changes to the code, anywhere where result IDs are written.

@davidpanderson I believe SETI may have reached the result limit; what did you do?

As a workaround I am going to update result set id=id-1000000000, but I will hit the limit some time again.

[AG5BPilot]

What about changing result.id from a signed int to an unsigned int? It should require less change to the C code, and is less likely to break project specific DB schemas and code. It also has the advantage of not increasing the size of the result table. The disadvantage is it only doubles the number of results and you'll eventually run out again.

[SETIguy]

I think most of the large projects have been using bigint. I don't know why that hasn't been put into the default database configuration. MariaDB > describe result; *************************** 1. row *************************** Field: id Type: bigint(20) Null: NO Key: PRI Default: NULL Extra: auto_increment

…

On Sat, Mar 20, 2021 at 5:41 PM AG5BPilot ***@***.***> wrote: What about changing result.id from a signed int to an unsigned int? It should require less change to the C code, and is less likely to break project specific DB schemas and code. It also has the advantage of not increasing the size of the result table. The disadvantage is it only doubles the number of results and you'll eventually run out again. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#4276 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACS5ZMUUNPVRYJ5ESYAGDLTTEU6DNANCNFSM4ZQ4GXQQ> .

-- Eric Korpela ***@***.*** AST:7731^29u18e3

[Rytiss]

Does it require code changes? I don't think normal ints would work anymore, and result IDs are used in various places.

[RichardHaselgrove]

SETI reached this limit in July 2015, and changes were put in place to accommodate 64-bit IDs for both results and workunits.

Most changes were made in 'server software: handle 64-bit database IDs' 8cd8c8e (55 changed files, but not including the database)

The remaining changes were made in 'server: more 64-bit ID fixes' a87d039 (12 changed files)

I hope that helps you to assess the scale of the problem, and work out how many changes have been applied already through routine updates.

[davidpanderson]

We changed it to bigint(20). The BOINC code is 64-bit everywhere, so this works.

[Rytiss]

Assignment feature is not working after migration to bigint(20).

boinc_db.insert_id() is using atoi, which has a limit of 2147483647 and thus does not work.

[Rytiss]

Added a pull request; this does fix the assigned work feature, but I wonder if there are more places in the code that breaks.