MySQL BIGINT inconsistent for inserts?

Question

On ubuntu.. running MySQL v 5.6. created a python program that performs all my operations.

my app creates tables dynamically. there are many. a few are very similar.. for example, here are two:

create table tst.intgn_party_test_load (
  party_id bigint unsigned NOT NULL,
  party_supertype varchar(15) NOT NULL,
  carrier_party_id bigint unsigned NOT NULL,
  full_name varchar(500),
  lda_actv_ind integer,
  lda_file_id integer,
  lda_created_by varchar(100),
  lda_created_on datetime,
  lda_updated_by varchar(100),
  lda_updated_on datetime, 
  PRIMARY KEY(party_id,party_supertype,carrier_party_id)
)

and

create table tst.intgn_party_relationship (
  parent_party_id bigint unsigned NOT NULL,
  child_party_id bigint unsigned NOT NULL,
  relationship_type varchar(10),
  lda_actv_ind integer,
  lda_file_id integer,
  lda_created_by varchar(100),
  lda_created_on datetime,
  lda_updated_by varchar(100),
  lda_updated_on datetime, 
  PRIMARY KEY(parent_party_id,child_party_id,relationship_type)
)

My program also dynamically populates the tables. I construct the party id fields using source data converted to an BIGINT. For example, the insert it constructs for the first table is:

INSERT INTO intgn_party_test_load (
  party_supertype, 
  carrier_party_id, 
  party_id, 
  full_name, 
  lda_actv_ind, 
  lda_file_id) 
SELECT  
  'Agency' as s0,
  0 as s1,
  CONV(SUBSTRING(CAST(SHA(CONCAT(full_name,ga)) AS CHAR), 1, 16), 16, 10) as s2,
  CONCAT(full_name,'-',ga) as s3, 
  lda_actv_ind, 
  lda_file_id 
FROM tst.raw_listing_20210118175114 
ON DUPLICATE KEY 
UPDATE  
  full_name = VALUES(full_name), 
  lda_actv_ind = VALUES(lda_actv_ind), 
  lda_file_id = VALUES(lda_file_id) ;

and for the second table the insert constructed looks very similar, and is based on the exact same source data:

INSERT INTO tst.intgn_party_relationship (
  parent_party_id,
  relationship_type,
  child_party_id, 
  lda_actv_ind, 
  lda_file_id) 
SELECT (Select party_id 
        from intgn_party 
        where full_name = 'xxx') as s0,
       'Location' as s1,
       CONV(SUBSTRING(CAST(SHA(CONCAT(full_name,ga)) AS CHAR), 1, 16), 16, 10) as s2, 
       lda_actv_ind, 
       lda_file_id 
FROM tst.raw_listing_20210118175114 
ON DUPLICATE KEY 
UPDATE  
  lda_actv_ind = VALUES(lda_actv_ind), 
  lda_file_id = VALUES(lda_file_id)

Now... the first table (intgn_party_test_load) is the issue. I can drop it, recreate it manually even.. no matter what i do, the data inserted into it via python has the BIGINT party_id truncated to just 16 digits. EVERY OTHER TABLE that uses the exact same formula to populate the party_id, creates BIGINT numbers that are between 18 and 20 digits long. I can see all the same source records loaded in the tables, and i see the truncated values in the first table (intgn_party_test_load). for example, the first table has a record with party id = 7129232523783260. the second table (and many others) has the same record loaded with [child]party id = 7129232523783260081.

The exact same formula, executed the exact same way from python.. but this table gets shorter BIGINTs.

Interestingly, I tried manually running the insert into this table (not using the python program), and it inserts the full BIGINT values. So I'm confused why the python program has 'chosen' this table to not work correctly, while it works fine on all other tables.

Is there some strange scenario where values get truncated? BTW, my python program utilizes sqlalchemy to run the creations/inserts. Since it works manually, I have to assume its related to sqlalchemy.. but no idea why it works on all but this table..

[edit] to add, the sql commands through sqlalchemy are executed using db_connection.execute(sql)

[edit - adding more code detail]

from sqlalchemy import create_engine, exc

engine = create_engine(
            connection_string,
            pool_size=6, max_overflow=10, encoding='latin1', isolation_level='AUTOCOMMIT'
        )
        connection = engine.connect()

sql = "INSERT INTO intgn_party_test_load (
  party_supertype, 
  carrier_party_id, 
  party_id, 
  full_name, 
  lda_actv_ind, 
  lda_file_id) 
SELECT  
  'Agency' as s0,
  0 as s1,
  CONV(SUBSTRING(CAST(SHA(CONCAT(full_name,ga)) AS CHAR), 1, 16), 16, 10) as s2,
  CONCAT(full_name,'-',ga) as s3, 
  lda_actv_ind, 
  lda_file_id 
FROM tst.raw_listing_20210118175114 
ON DUPLICATE KEY 
UPDATE  
  full_name = VALUES(full_name), 
  lda_actv_ind = VALUES(lda_actv_ind), 
  lda_file_id = VALUES(lda_file_id) ;"

        result = db_connection.execute(sql)

Thats as best i can reduce it too (the code is much more complicated as it dynamically creates the statement amoungst other things).. but from my logging, i see the exact statement it is executing (As above), and i see the result in the BIGINT columns after. all tables but this one. And only when through the app. so it doesn't happen to the other tables even through the app..

very confusing.. was hoping someone just knew a bug in mySQL 5.6 around BIGINTs as it pertains to maybe the destination table's key construct or total length of records.. or some other crazy reason. I do see that interestingly, if i do a distinct on BIGINT column that has >18 digit lengths, it comes back as 16 digits - guess the distinct function doesn't support BIGINT.. was kinda hoping this hints at an issue, but i don't get why the other tables would work fine...

[EDIT - adding some of the things i see sqlalchemy running apparently, around the actual run of my query.. just in the crazy case they impact anything - for the one table?? ]

SET AUTOCOMMIT = 0
SET AUTOCOMMIT = 1
SET NAMES utf8mb4
SHOW VARIABLES LIKE 'sql_mode'
SHOW VARIABLES LIKE 'lower_case_table_names'
SELECT VERSION()
SELECT DATABASE()
SELECT @@tx_isolation
show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
ROLLBACK
SET NAMES utf8mb4

hard to say the order or anything like that.. there are a ton that get run at the same microsecond.

and just to add.. you'll notice that i'm not dealing with the datatypes in my python code at all.. The SQL statements execute on the server, so i don't *think sqlalchemy even knows i'm dealing with BIGINT or not. But then why does the exact same statement run differently through it, then when i run direct on the db? — da Bich
Do you have any transactions that might get rolled back? Do you have the same problem in the same table using INT keys? — tadman
i can't use INT keys because they aren't large enough for my uniqueness formulas. Not sure about the roll back question.. i can drop the table and recreate it. I'm starting to consider replacing all the BIGINT columns with varchar and just convert each time (add the casting of the BIGINT to a varchar in the inserts).. Just a headache to go back and change all the tables.. i can't think of a reason why i don't just change them though... sidestep this issue.. — da Bich
note the party id formula in the above example is "CONV(SUBSTRING(CAST(SHA(CONCAT(full_name,ga)) AS CHAR), 1, 16), 16, 10) as s2" -- it takes two columns, concatenates them, and the uses SHA to get it converted to a number.. — da Bich
Since you're only having the problem when you run the code via Python, you need to post the relevant Python code. — Barmar

da Bich da Bich · Accepted Answer · 2021-02-04T06:50:03

after racking my brains for days.. coming at it from all angles, i could not figure out why 1 table out of many, had issues with truncating the SHA'd value. In the end, i have redesigned how i hold my Ids, and i no longer bother converting to BIGINT. it all works fine when i leave it as CHAR.

CAST(SHA(CONCAT(full_name,ga)) AS CHAR)

So changed all my Id columns to varchar(40) and use the above style. All good now. Joins will use varchar instead of bigint - which i'm ok with.

MySQL BIGINT inconsistent for inserts?

1 Answers