1

In DBeaver, when right-clicking the 'Foreign Keys' to select 'View Foreign Keys', the following informational page is displayed:

enter image description here

I would think that the 'Auto Increment' field value would read 1 (or some other small number) to indicate the step value by which that foreign key is auto-incremented.

Instead, I see a value of MAX INT 32 bit (2147483647).

What does that value mean?

1
  • It simply means the maximum possible (see my answer). To test this, insert some record into an arbitrary table (CREATE TABLE test (test_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, my_name VARCHAR(10)); and then INSERT INTO test (my_name) VALUES ('Bob'), ('Billy'), ('Fred'); and then SELECT * FROM test; - you should see very low values for test_id!
    – Vérace
    Commented Apr 23, 2019 at 18:49

1 Answer 1

2

2 to the power of 32 is 4294967296.

If you divide that by 2, you get:

(2^32/2) = 2147483648

This second value minus 1 is 2147483647 = 2^31 - 1, i.e. your value of interest.

Mathematically, it's the 8th Mersenne prime and is also equal to the hexadecimal (base 16) value 7FFF,FFFF. It's also the largest known double Mersenne prime and (probably) the biggest of that class of number!

In databases and computing, it's the maximum size of a 32 bit SIGNED INTEGER. The signed/unsigned INTEGER type is one of the few areas in which MySQL is superior to PostgreSQL. I think that unsigned INTEGERs have many potential uses, but then again, PostgreSQL has CHECK CONSTARAINTs which mean that it's not obligatory to enforce positive integers as a type. Still (personal opinion), I feel it's a good type to have, if only for brevity and clarity!

Previously (in many 32 bit systems), it was the maximum number (i.e. 2147483647) of records allowed in a table and/or the maximum that an AUTO_INCREMENTing (i.e. SERIAL or SEQUENCE) integer field could reach - but this is not now normally the case with 64 bit machines.

When this number was reached, either for the number of records, or for a key of some sort, then the behaviour of many database systems was/is either undocumented or erratic or both. The value could start again from zero (possibly breaching integrity constraints) or start at an arbitrary value or cause the system to crash completely!

On the 19th of January 2038 (at 03:14:07 UTC), there will be more than that many (2147483647) seconds elapsed since Jan 01, 1970, i.e. the beginning of time as far as Unix is concerned. Aircraft will drop out of the sky, nuclear missiles will be launched without provocation and there will generally be a great wailing and gnashing of teeth - unless, of course, you're a progrmamer who can make a mint out of this (just like the COBOLers in 2000!).

The Year 2038 problem relates to representing time in many digital systems as the number of seconds passed since 1 January 1970 and storing it as a signed 32-bit binary integer. Such implementations cannot encode times after 03:14:07 UTC on 19 January 2038. Just like the Y2K problem, the Year 2038 problem is caused by insufficient capacity of the chosen storage unit.

Best to avoid completely and use 64 bit these days!

3
  • Actually, I think this is the next number to be placed into the referenced column... How do I reset that field?
    – datsb
    Commented Apr 24, 2019 at 12:54
  • OK - have you tested this? Are you on a 32 or a 64 bit system? What happens if you run the small test in my comment? Also, in your screenshot, the tablename appears to be strange - have you tried on a table with a name composed entirely of ASCII characters? It's been a while since I've run MySQL instances - but you could look here - obtained from here.
    – Vérace
    Commented Apr 24, 2019 at 12:57
  • That ALTER TABLE table_name AUTO_INCREMENT = 1; from viralpatel.net/blogs/reseting-mysql-autoincrement-column did the trick. Thank you.
    – datsb
    Commented Apr 28, 2019 at 5:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.