Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2007
    Posts
    77

    Unanswered: Limit of IDENTITY columns

    Hi to all,

    Most identity columns I have seen are specified as LONG data types but I want to ask if what happens if it reaches its limit. I mean, what if it has used up all its values to use? Although they generate over 2 billion values ( the max value of a LONG data type), it is quite possible that they can be used up? don't you think?

    Thanks and god bless.
    Programming is fun!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What RDBMS are you talking about? Of course it is possible - you should have some idea before you start whether or not it is feasible that the number of records will eventually exceed n.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2007
    Posts
    77
    I am using MySQL.

    So if you are using a transactional table that handles new inserts of records and then it happens to reach its limit, then it will be deemed as no longer usable?

    What are way to prevent it? Do we need to conduct maintenance on the tables and delete unused records?
    Programming is fun!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in mysql, when the auto_increment reaches its limit, you can no longer insert any more rows

    you can easily test this out for yourself by declaring a TINYINT auto_increment and inserting 128 rows

    way to prevent it? declare a bigger auto_increment column

    so if 2 billion is too small, use BIGINT which takes you to 9 quintillion

    i guarantee that you will never reach 9 quintillion rows

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Posts
    77
    I agree, I would never reach the 9 quintillion rows, but what will happen if I am using a front-end application created in VB6?

    When trying to hold the auto_increment field, do you think I should stray away from the Long data type? The long data type is about 2 billion ( I think, correct me if I am wrong). If ever the value in the auto_increment fields exceeds on the max of the Long data type, it just makes sense to change to another data type right> Currency? Double ? Maybe String would do.....
    Programming is fun!

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is no LONG datatype in mysql

    ...Currency? Double ? Maybe String

    say, what database are you using?

    that sounds a lot like microsoft access
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think the confusin is using VB to connect to MySQL... long is a valid type in DB

    perhaps this will help the OP
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2007
    Posts
    77
    Sorry for the mistake.

    There is no LONG data type in MySQL, but I was just referring to VB6. I normally use the LONG data type in VB6 to hold the INT data type of MySQL.

    If there is a case that the INT column in my MySQL table exceeds its limit, (over 2 billion for INT columns), so the LONG data type in VB6 could not hold it anymore.

    Which of the three is much a better choice: Currency? Double ? Maybe String

    thanks
    Programming is fun!

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as the reference suggests there is no way to use unsigned integers in VB, you'll have to coerce them to be double but integer values, otherwise you may have rounding issues.

    but being realistic how likely is it that you will exceed the range of VB's LONG datatype, which if my memory serves me is 2^32
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Panoy
    Which of the three is much a better choice: Currency? Double ? Maybe String
    my guess is: none of these

    if mysql's INTEGER is too small, use mysql's BIGINT

    how you deal with that in VB isn't really a mysql question, is it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2007
    Posts
    77
    I think you have it the other way around.

    What I mean is that the BIGINT, is way to big for the Long datatype in VB6. I think that the String data type is good enough to hold it. or not

    thanks and god bless
    Programming is fun!

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, moving thread to VB forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    did you read the reference?
    it looks as if the reecomendation form MySQL is NOT to use BIGINT, unless you know the values are below the Integer threshold.. in which case surely you'd use the integer datatype to start with. As I read that reference it suggests if you really need a number of that size use MySQL double which
    like MySQL Bigint is a 64 bit IEEE value. but you will have to do some work handling rounding errors inherent in VB

    it expressly states
    1) There is no such thing as a 32 bit unsigned integer in VB, so data must be stored in a double, which is actually a floating-point datatype, therefore, be careful to make sure that any value that you will be passing to MySQL is rounded off before you post it. The use of an UNSIGNED INT with VB6 is not reccomended, but you may have some luck with the larger integers of VB.NET.

    2) BIGINT cannot be used with ADO, as it will cause errors, you can use the MyODBC option of 16384 (convert BIGINT to INT) to make them funnel down, but from what I understand your BIGINT is then limited to the size of an INT anyway. If a BIGINT is brought into a long, you will be ok up to the limit if the long datatype, after which your data will be corrupted.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    whole number rounding issues shouldn't be an issue with VB6's doubles until the count gets greater than 15 significant digits. That's a thousand trillion records. Or, a million billion records. Either way you look at it, that a bunch of 'em.

    If you were to use currency, you could stretch that out to 28 significant digits. Wich is ten trillion times larger than a whole number double.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  15. #15
    Join Date
    Mar 2007
    Posts
    77
    Quote Originally Posted by healdem
    did you read the reference?
    it looks as if the reecomendation form MySQL is NOT to use BIGINT, unless you know the values are below the Integer threshold.. in which case surely you'd use the integer datatype to start with. As I read that reference it suggests if you really need a number of that size use MySQL double which
    like MySQL Bigint is a 64 bit IEEE value. but you will have to do some work handling rounding errors inherent in VB

    it expressly states
    1) There is no such thing as a 32 bit unsigned integer in VB, so data must be stored in a double, which is actually a floating-point datatype, therefore, be careful to make sure that any value that you will be passing to MySQL is rounded off before you post it. The use of an UNSIGNED INT with VB6 is not reccomended, but you may have some luck with the larger integers of VB.NET.

    2) BIGINT cannot be used with ADO, as it will cause errors, you can use the MyODBC option of 16384 (convert BIGINT to INT) to make them funnel down, but from what I understand your BIGINT is then limited to the size of an INT anyway. If a BIGINT is brought into a long, you will be ok up to the limit if the long datatype, after which your data will be corrupted.

    you're right healdem, my bad.
    Programming is fun!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •