MySQL INSERT fails with ERROR 1064, 1265, 1292 (invisible character in numeric ID, ENUM mismatch, invalid DATETIME)

Question

MySQL INSERT fails with ERROR 1064, 1265, 1292 (invisible character in numeric ID, ENUM mismatch, invalid DATETIME)

Arthur Tong 0

Hi,

I’m importing seed data into a MySQL table and my bulk INSERT fails with multiple errors depending on the row. I suspect some data-cleaning/format issues during copy/paste or CSV → SQL conversion.

Table definition

CREATE TABLE lead_broker_status (

lead_id int(11) NOT NULL,

broker_id int(11) NOT NULL,

status_id int(11) DEFAULT NULL,

followup_datetime datetime DEFAULT NULL,

warmth enum('Hot','Warm','Cold') DEFAULT NULL,

updated_at datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),

deleted tinyint(1) DEFAULT 0

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

Insert statement (sanitized sample)

INSERT INTO lead_broker_status

(lead_id, broker_id, status_id, followup_datetime, warmth, updated_at, deleted) VALUES

-- Row 1: MySQL throws ERROR 1064 near part of the numeric id

(101, 4, 1, NULL, NULL, '2025-09-27 03:34:18', 0),

-- Row 2: MySQL throws ERROR 1265 data truncated for enum column

(102, 5, 1, NULL, 'Hot ', '2025-09-27 03:34:25', 0),

-- Row 3: MySQL throws ERROR 1292 incorrect datetime value

(103, 4, 1, NULL, NULL, '2025-09-27 13:05:61', 0);

Errors

ERROR 1064 (18882749655) – syntax error near part of the numeric value

ERROR 1265 (01000) – data truncated for column warmth

ERROR 1292 (22007) – incorrect datetime value for updated_at

What I suspect

For ERROR 1064: one of the numeric IDs in my real dataset may contain a non-printable character (for example a zero-width space U+200B) from copy/paste, which makes the number invalid even though it looks normal.

For ERROR 1265: the value 'Hot ' has a trailing space, so it doesn’t match the ENUM definition exactly.

For ERROR 1292: the timestamp has invalid seconds (61), since seconds must be 00–59.

What I’m asking

Best way to detect and remove non-printable characters in SQL import data (or how to identify them in MySQL/CLI tools).

Best practices for handling ENUM input safely (trim/validate before insert vs changing schema).

Recommended approach to validate/correct datetime values during bulk inserts.

Any MySQL settings (e.g., sql_mode) that can help catch these issues early with clearer feedback.

Environment:

MySQL version: (add your version)

OS: Windows

Tool: (MySQL CLI / phpMyAdmin / Workbench)

Thanks in advance for any guidance on cleaning the data and preventing these errors in future imports.

0 comments

Answer recommended by moderator

1 additional answer

Your answer

Answer 1

Hi @Arthur Tong ,

The issue isn’t the table definition or MySQL itself it’s unclean import data. The reliable fix is to stage and validate before insert.

Follow bellow steps which works,

Load the data into a staging table with all columns as VARCHAR. This avoids hidden characters breaking numeric and datetime parsing.
Clean the data explicitly:
- Strip non-numeric characters from IDs (fixes ERROR 1064)
  - TRIM() ENUM values before insert (fixes ERROR 1265)
    - Reject invalid timestamps (seconds must be 00–59) before insert (fixes ERROR 1292)
    1. Insert only validated rows into the final table using CAST().
    2. Keep strict SQL mode enabled so bad rows fail early instead of corrupting data.

This staging-and-validation step is the missing piece. It reliably eliminates invisible characters, ENUM mismatches, and invalid DATETIME values during bulk imports.

Thanks,
Akhil.

Answer 2

Yitzhak Khabinsky 27,106

Hi @Arthur Tong,

This is MS SQL Server forum. Though it seems that you are using MySQL...

In any case, *.csv/txt flat files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data. Or data elements are too long or missing completely, i.e. column positions will vary. The most reliable format for data feeds is XML enforced by an XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee proper data format/shape, data types, cardinality, encoding, and enforce data quality.

Here is how to bulk load an XML file into a MySQL table.

<rows>
  <row>
    <id>1</id>
    <name>Alice</name>
    <email>alice@example.com</email>
  </row>
  <row>
    <id>2</id>
    <name>Bob</name>
    <email>bob@example.com</email>
  </row>
</rows>

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255)
);

Bulk load command

-- Server-side (requires secure_file_priv setup and file readable by server)
LOAD XML INFILE '/path/to/users.xml'
INTO TABLE users
ROWS IDENTIFIED BY '<row>';

-- Client-side (uploads from client to server; safer when you can't access server files)
LOAD XML LOCAL INFILE '/local/path/to/users.xml'
INTO TABLE users
ROWS IDENTIFIED BY '<row>';

Akhil Gajavelly 1,820 Reputation points Microsoft External Staff Moderator

2026-01-20T11:03:00.5766667+00:00
Hi @Arthur Tong ,

Just to check back the errors you listed usually come from unclean import data, not the table itself. A reliable way to move forward is to,

load the data into a staging table (all columns as VARCHAR),

trim ENUM values (e.g. 'Hot ' → 'Hot'),

strip non-numeric characters from IDs,

and validate DATETIME values before inserting into the final table.

This approach avoids hidden characters and invalid timestamps during bulk inserts. If you’re still stuck, let us know how you’re loading the data (CSV, copy-paste, Workbench, etc.) and your MySQL version.

Thanks,
Akhil.
Akhil Gajavelly 1,820 Reputation points Microsoft External Staff Moderator

2026-02-02T05:34:10.5833333+00:00

Hi @Arthur Tong ,

Just checking in to see if the staging-table approach helped resolve the import errors.

If this worked for you, please consider marking the answer as accepted so others can benefit. If not, let us know how you’re loading the data and the MySQL version, and we can dig further.

Thanksm
Akhil.

Share via

MySQL INSERT fails with ERROR 1064, 1265, 1292 (invisible character in numeric ID, ENUM mismatch, invalid DATETIME)

1 additional answer

Your answer