CODEWITHSUNDEEP
mysqlenums
Apparently Im user2212428
Apparently Im user2212428

Reputation: 99

MySQL Error 1265 Data Truncated for Enum

I have a stored procedure in MySQL that has an enum called log_level with a few values.

..

DECLARE log_level ENUM('none','some','errors','debug') DEFAULT 1;

SET log_level = 0; 

..

Gives the error:

enter image description here

If I change this to:

..

DECLARE log_level ENUM('none','some','errors','debug') DEFAULT log_level=1;

SET log_level = 0; 

..

It gives the error:

enter image description here

How can I fix this issue?

Upvotes: 2

Views: 6648

Answers (2)

JvO
JvO

Reputation: 3106

MySQL enums work differently than the C/C++ equivalent. log_level is declared as an enum of strings so it really expects a string as value. A default of '1' doesn't make much sense, either.

The correct syntax is:

DECLARE log_level ENUM('none','some','errors','debug') DEFAULT 'some';

SET log_level = 'none';

The same error 1265 shows up if you try to assign a non-existing value to an enum (e.g. an empty string in stead of a real NULL value).

Sidenote: internally the database uses integer values but those details are completely hidden by the SQL language.

Upvotes: 1

M Siddique
M Siddique

Reputation: 1019

Same kind issue was occurring with me when i was doing `

ALTERorINSERT

` the information.

I got fixed by

UPDATE TABLE t SET t.fieldName = NULL

and this fixed my issue.

Upvotes: 1

Related Questions