📋

Description

This error signifies that a value provided for a column during an INSERT or UPDATE operation does not conform to the column's defined data type or constraints. It indicates a fundamental incompatibility between the data being supplied and the database schema, leading to data integrity failures.

💬

Error Message

Incorrect %s value: '%s' for column '%s' at row %ld

🔍

Known Causes

4 known causes

⚠️

Data Type Mismatch

Attempting to store a value with a different data type (e.g., a string) into a column defined as another type (e.g., an integer or date).

⚠️

Invalid Numeric Format

Providing a non-numeric string or a number outside the valid range for a numeric column (INT, DECIMAL, etc.).

⚠️

Incorrect Date/Time Format

Supplying a date or time string that does not adhere to MySQL's expected format for DATE, DATETIME, or TIMESTAMP columns.

⚠️

ENUM/SET Value Violation

Inserting a value into an ENUM or SET column that is not among its predefined, allowed options.

🛠️

Solutions

5 solutions available

1. Use Correct Character Set medium

Ensure column charset matches your data

1

Check column character set

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';

2

Convert column to utf8mb4 for full Unicode

ALTER TABLE your_table 
MODIFY COLUMN your_column VARCHAR(255) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3

Or convert entire table

ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. Set Connection Charset easy

Match client connection charset with database

1

Set charset in SQL

SET NAMES 'utf8mb4';

2

Or in connection string

// Node.js
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  database: 'db',
  charset: 'utf8mb4'
});

// PHP PDO
$dsn = 'mysql:host=localhost;dbname=db;charset=utf8mb4';

3. Clean Invalid Characters medium

Remove or replace problematic characters

1

Find and replace invalid characters

// JavaScript - remove non-printable characters
const cleanString = input.replace(/[\x00-\x08\x0B\x0C\x0E-\x1F]/g, '');

// Or convert to valid encoding
const buffer = Buffer.from(input, 'utf8');
const cleanString = buffer.toString('utf8');

2

Python encoding handling

# Remove invalid UTF-8 characters
clean_string = input_string.encode('utf-8', errors='ignore').decode('utf-8')

# Or replace with placeholder
clean_string = input_string.encode('utf-8', errors='replace').decode('utf-8')

4. Handle Emoji and Special Characters medium

Use utf8mb4 for emoji support

1

utf8 vs utf8mb4

-- utf8 (MySQL's utf8) = 3 bytes, no emoji
-- utf8mb4 = 4 bytes, full Unicode including emoji

-- Emoji like 😀 requires utf8mb4
ALTER TABLE messages MODIFY content TEXT CHARACTER SET utf8mb4;

2

Update database default

ALTER DATABASE your_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5. Fix Binary Data in Text Column medium

Use appropriate column type for binary data

1

Use BLOB for binary data

-- Wrong: storing binary in VARCHAR
-- Right: use BLOB
ALTER TABLE files MODIFY content LONGBLOB;

2

Or encode binary as base64

// JavaScript
const base64Data = Buffer.from(binaryData).toString('base64');
// Store base64 string in VARCHAR/TEXT

🔗

Related Errors

5 related errors

MySQL Error 1366: Data Type Mismatch