MySQL column size error when inserting hash values

Other Questions MySQL
1

I’m experiencing an issue with inserting a SHA1 hash into my MySQL database. The hash is 40 characters long, yet I’m getting an error stating that the data exceeds the column limit.

Here’s what I’ve done so far:

  • Started with varchar(20) which was definitely too small.
  • Updated it to varchar(40) that should accommodate the SHA1 hash size.
  • Attempted changing to TEXT and LONGTEXT types, but the error persists.

My stored procedure command is:

CALL CreateAccount('user123', 'A94F2B8E7D1C9F3E5B6A8D2C4E7F9B1A3C5D8E0F', 'user123', 'user123', @result);

The procedure works fine with shorter strings. I’ve heard that switching MySQL to non-strict mode might resolve this, but I’d rather find a proper solution. What could be behind this problem?

2

Had this exact problem last year - it’s a character set issue. Your varchar(40) column might be using utf8mb4, which means MySQL calculates space differently. Run SHOW CREATE TABLE your_table_name to check. With utf8mb4, each character can eat up 4 bytes, so your 40-character field actually needs 160 bytes of storage. Since SHA1 hashes are just hex characters, either switch the column to latin1 or bump up your varchar size to handle the multi-byte overhead. Fixed my hash insertion issues without touching strict mode.

3

sounds like there’s a hidden char or encoding issue. check if ur hash string has trailing spaces or weird chars. also run DESCRIBE your_table_name to confirm the col updated to varchar(40).