Handling ENUM – Backup using mysqldump and navicat – MySQL

During the backup of MySQL database using mysqldump or during the data transfer using navicat, we do get the following error message if any of the table has a column defined with ENUM data type. If you ignore or skip this error message, you will end up with the truncated data or the column value changed to multiple question marks.

If any table has a column created with an ENUM data type, we receive the following error message when backing up a MySQL database using mysqldump or when transferring data using navicat.

[ERR] 78> 1265 – Data truncated for column ‘groupType’ at row 3
[DTF] 79> Student_Data: Getting table structure
[DTF] Process terminated

You will end up with truncated data or a column value that is several question marks if you ignore or skip over this error warning.

It is because an ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time and the elements listed in the column specification are assigned index numbers, beginning with 1.

You must include the –hex-blob option in your mysqldump command since ENUM flags are typically defined in hexadecimal or blob format. Tick the “Use hexadecimal format for BLOB” checkbox if you’re using Navicat, as seen below.


Hope you find this article helpful.

Please subscribe for more interesting updates.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s