Data Truncated for Column at Row 1 on CHAR(1): Cause and Fix

You add an active CHAR(1) column that's supposed to hold Y or N, run your import, and MySQL replies with Warning 1265. Or worse, it says nothing, and a week later you find out half your rows say Y when they should say N.

If you're asking why do I get a 'Data truncated for column at row 1' warning when setting CHAR(1) for my column?, the short version is this: MySQL got a value longer than one character. Below I'll show you how to find that value, fix it, and set MySQL up so it refuses bad data instead of quietly chopping it.

Short answer: CHAR(1) holds exactly one character. When you insert something longer, like 'Yes', 'true', or 'Y' with a hidden \r from a Windows CSV, MySQL in non-strict mode keeps the first character and raises warning 1265. In strict mode you get an error instead. Fix the incoming value or widen the column.

Why Do I Get a 'Data Truncated for Column at Row 1' Warning When Setting CHAR(1) for My Column?

"Row 1" doesn't mean the first row of your table. It means the first row of that statement. For a single INSERT, it's always row 1. In a bulk INSERT or LOAD DATA, the number tells you which input line broke.

Here are the usual ways a value ends up too long:

• The app sends a word ('Yes', 'active', 'true') where you expected a letter
• A CSV made on Windows ends each line with \r\n, so the last field arrives as Y\r
• You ran ALTER TABLE to shrink a column to CHAR(1) while it still held longer values
• The column is actually an ENUM, and the value isn't in its list (MySQL reports this as "data truncated" too)

The steps below use one example table, a users table with an active flag.

Step 1: Reproduce the CHAR(1) Truncation Warning

Run this on a scratch database. The SET SESSION line turns off strict mode for this connection only, so you'll see the warning instead of an error:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  active CHAR(1) NOT NULL DEFAULT 'N'
);

SET SESSION sql_mode = '';

INSERT INTO users (name, active) VALUES ('Sara', 'Yes');
SHOW WARNINGS;

You'll see:

Warning | 1265 | Data truncated for column 'active' at row 1

Now look at what was actually saved:

SELECT name, active FROM users;

It says Y. In this case it's correct by luck. Send 'No' and you get N, also by luck. But send 'false' and you get f, and nothing tells your code that went wrong. That's why I treat this warning as a bug rather than noise.

Step 2: Find the Values That Don't Fit

When the data comes from somewhere else (a CSV, an API, another table), check what you're really sending. If you're copying from a staging table, measure it first:

SELECT id, active, CHAR_LENGTH(active) AS len, HEX(active) AS hex
FROM users_import
WHERE CHAR_LENGTH(active) > 1;

HEX() is the one that catches hidden characters. A clean Y shows 59. If you see 590D, that's Y plus a carriage return, and it means your file has Windows line endings.

If the values come from PHP, dump them before the query runs:

var_dump($request->input('active'));
// string(3) "Yes"  -> too long
// string(2) "Y\r"  -> hidden carriage return

Step 3: Fix the Incoming Value (Usually the Right Fix)

Most of the time the column is fine and the input is wrong. You want one letter, so convert the input to one letter before it reaches the database.

In plain PHP

$raw = strtolower(trim((string) ($_POST['active'] ?? '')));
$active = in_array($raw, ['y', 'yes', '1', 'on', 'true'], true) ? 'Y' : 'N';

$stmt = $pdo->prepare('INSERT INTO users (name, active) VALUES (?, ?)');
$stmt->execute([$name, $active]);

trim() also removes \r and \n, which covers the hidden-character case.

In Laravel

Validate the field so bad values never get past the request:

use Illuminate\Validation\Rule;

$validated = $request->validate([
    'name'   => ['required', 'string', 'max:100'],
    'active' => ['required', Rule::in(['Y', 'N'])],
]);

User::create($validated);

If your form sends a checkbox instead, convert it explicitly: 'active' => $request->boolean('active') ? 'Y' : 'N'.

In a CSV import with LOAD DATA

Set the line terminator to match the file:

LOAD DATA LOCAL INFILE '/path/to/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(name, active);

If you're not sure which line endings the file uses, try '\r\n' and run the HEX() check from Step 2 on a few rows. If the values still end in 0D, something else in the file is wrong.

Step 4: Clean Existing Rows Before an ALTER TABLE

This is the variation that catches a lot of people. You run something like:

ALTER TABLE users MODIFY active CHAR(1) NOT NULL DEFAULT 'N';

and MySQL stops with ERROR 1265 (01000): Data truncated for column 'active' at row 1. The column used to be VARCHAR(10) and still holds values like 'yes'. MySQL refuses to cut them down when the ALTER runs.

Convert the data first, then shrink the column:

UPDATE users
SET active = CASE
    WHEN UPPER(TRIM(REPLACE(active, '\r', ''))) IN ('Y', 'YES', '1', 'TRUE') THEN 'Y'
    ELSE 'N'
END;

ALTER TABLE users MODIFY active CHAR(1) NOT NULL DEFAULT 'N';

Back up the table before running the UPDATE. That ELSE 'N' will turn any value you didn't plan for into N.

The Laravel migration version

Schema::table('users', function (Blueprint $table) {
    $table->char('active', 1)->default('N')->change();
});

The same rule applies here: clean the data in an earlier migration, or in the same up() before the schema change. Older Laravel versions also need the doctrine/dbal package installed before change() works. If the migration later breaks your seeders, the migrate:fresh --seed troubleshooting guide covers that.

Step 5: Use ENUM or a Wider Column When One Letter Isn't Enough

Sometimes the real problem is that CHAR(1) was the wrong type to begin with. If the column needs to hold pending, active, and banned, don't squeeze those into codes like P/A/B unless you really want to. Widen the column or use an ENUM:

ALTER TABLE users MODIFY status ENUM('pending', 'active', 'banned') NOT NULL DEFAULT 'pending';

Be aware that inserting a value that isn't in the ENUM list gives you the same 1265 "Data truncated" message, even though nothing was cut. In non-strict mode MySQL stores an empty string. Personally, I'd use VARCHAR plus validation in the app over ENUM, because adding a new status to an ENUM means running another ALTER TABLE. Plenty of teams like ENUM for the built-in safety, though. Both choices are reasonable.

Step 6: Turn On Strict Mode So Bad Data Fails Loudly

A warning that nobody reads is how bad data piles up. Check what your server is running:

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

If STRICT_TRANS_TABLES isn't in there, set it for your session to test:

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

To make it permanent, add it under [mysqld] in my.cnf (or my.ini on XAMPP), then restart MySQL:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

In Laravel, open config/database.php and check that the mysql connection has 'strict' => true. Laravel then sets strict mode on every connection it opens, no matter what the server's default is.

With strict mode on, the Step 1 insert stops being a warning:

ERROR 1406 (22001): Data too long for column 'active' at row 1

That's what you want. The query fails, your app throws an exception, and you find out right away.

Verify the Fix

Run the original insert again, this time with clean input, and check that there are no warnings:

INSERT INTO users (name, active) VALUES ('Omar', 'N');
SHOW WARNINGS;

SELECT active, HEX(active) FROM users WHERE name = 'Omar';

SHOW WARNINGS should return an empty set, and HEX should show 4E with nothing after it. Then try a bad value on purpose ('No'). With strict mode on, you should get error 1406.

Next, look at your other short columns: CHAR(2) country codes, TINYINT flags filled from strings, VARCHAR(20) phone numbers. The same issue can quietly corrupt any of them. Running a quick CHAR_LENGTH check against the column limit on your import tables usually finds them.

FAQ

What's the difference between "Data truncated" and "Data too long for column"?

They come from the same problem. Non-strict mode gives warning 1265 and stores a shortened value. Strict mode gives error 1406 for an oversized string and rejects the row. An invalid ENUM value or a bad ALTER TABLE conversion can show up as 1265 even in strict mode.

Does CHAR(1) count bytes or characters?

Characters. A CHAR(1) column in utf8mb4 can hold one Arabic letter or one emoji, even though those take several bytes. So multibyte text by itself won't cause this warning. A string with two or more characters will.

Should I just turn off strict mode to make the warning go away?

I wouldn't. Turning off strict mode doesn't fix the data. It only stops MySQL from telling you that it changed your data. Some old applications depend on non-strict mode, but in new code the better fix is cleaning the input.

Why does it say row 1 when my table has thousands of rows?

The row number counts rows within the statement that failed, not rows in the table. A single-row INSERT or an ALTER TABLE that fails on the first bad row will usually say row 1. In LOAD DATA, the number points to the input line.

What is the quick answer to "why do I get a 'Data truncated for column at row 1' warning when setting char(1) for my column?"

Something longer than one character reached the column. It might be a whole word, a stray \r from a Windows file, or old data during an ALTER TABLE. Use HEX() to find it, convert the value to one letter before inserting, and turn on strict mode so the next one fails loudly instead of being cut.