1. 

   rpark, Member

   Hi all,
   Data in my reports and in the Data Browser are showing incorrect IDs, however, when I switch over to the database I see the correct IDs as expected. Thus, this appears to be an issue with MyDBR reporting only and not a real issue with the data (thank goodness!)...

   I am stuck using BIGINT(19) for my IDs, this is due to legacy constraints, but for all intents and purposes this was working just fine in MyDBR until today.

   Today, when I re-ran a report I noticed that IDs which are required to be unique, have been "copied" down by MyDBR and appear the same for multiple records in an arbitrary fashion (which is impossible because the column is primary key and auto-increment).

   Screenshots visual the issue well
   MyDBR Report screenshot:
   

   MyDBR Data Browser screenshot:
   

   phpMyAmdmin screenshot:
   

   Edit: to add, this issue affects most, if not all tables in my database, it's not isolated to the one above.

   I am losing hair trying to figure this out, couple things I want to mention that may help:
   1. I accidentally set the ID column to BIGINT(20) when it should actually be BIGINT(19), however, I don't think this has caused the issue as BIGINT's of 19 characters should fit just fine within a BIGINT(20) column.
   2. I ran an update yesterday, but I did not notice this issue right away, thus I'm not able to isolate the update as the cause.

   Help is greatly appreciated!
2. 

   rpark, Member

   Update 1: when I select a record in a selectable list and send it over URL to a report, I see the correct ID from the database in the URL. However, when I try to edit a record and send the ID to a popup window, the ID received is incorrect and as is per above.

   ID sent over selectable list to new report:
   3307235000002258990

   ID sent over by edit link to popup:
   3307235000000225944

   Update 2: If I cast the ID to char, such as select Cast(ID as char) ... then I see the correct IDs, this appears to conclude that MyDBR is rounding bigints instead of displaying as expected. Of course, this is not just a cosmetic issue, as linked reports with passed in parameters have rounded IDs and that breaks the linkage to the correct record of course.

   Update 3: If I Cast to char, then cast back to unsigned integer (bigint) the original issue reoccurs the and ID is incorrect. It's conclusive now, MyDBR is not handling bigints properly and is rounding or modifying the value in some similar fashion most likely due to an overflow (which is the reason I use bigint in the first place!)

   Really need help with this, it's breaking my production environment, willing to pay!

   Thank you very much
3. 

   myDBR Team, Key Master

   The problem was caused by an overflow on PHP side (number was formatted using PHP routines which converts the integer number to a floating point number which has lesser range, up to 2^53). The issue is now fixed, you can run the automatic updater.

   Are the numbers that big in your system really real life numbers or strings with numbers in it? If they are real numbers, can you tell what do they represet? If they are actually strings with numbers, you should use a varchar.

   Note 1: If you export to Excel, Excel will do the rounding as well as Excel cannot handle such large numbers.

   Note 2: There is no datatype BIGINT(19) in MySQL. There is just BIGINT. The number in parentheses is a metadata for display width (an useless extension in MySQL). The number BIGINT(1), BIGINT(10) and BIGINT(20) are all the same. The same goes with INT. You often see INT(11), which is the same as INT.

   --
   myDBR Team
4. 

   rpark, Member

   Thank you for the update and solution, much appreciated!

   Thank you for letting me know exports to Excel will break in MyDBR, please consider adding a single quote in-front of bigint figures when exporting to Excel to treat the bigint as a string. This is what all cloud providers I've used (who typically include bigint in their databases) do to prevent breaking the ID field after export.

   I appreciate your comments on the size of bigint, never had to use it before in production, thus I followed the integer() length. MYSQL is somewhat quirky here isn't it. But good to know!
5. 

   myDBR Team, Key Master

   Note that also in JavaScript those numbers are out of range. The max integer number (Number.MAX_SAFE_INTEGER) is 9007199254740991. So, calculations like 330723500000225892 - 330723500000225891 result 0. Handling such large numbers outside the database may give you trouble.

   If you run into trouble, just use cast( id as char(20) ), so they are treated as strings. Things like Excel exports will then work ok.
   --
   myDBR Team

Reply

You must log in to post.