Your Database Is Lying to You.

Seven MySQL mistakes that silently corrupt your data. No errors. No warnings. Just wrong numbers in production.

Your tests pass. Your code works. Your client is happy. Six months later, someone pulls a financial report and the numbers do not add up. You check the code. The code is correct. You check the queries. The queries are correct.

The database is lying to you. It has been lying since day one. And MySQL is very, very good at lying quietly.

I have been building Laravel applications on MySQL for over eight years. Every mistake in this article is one I have either made myself or discovered in a client’s codebase during an audit. None of them threw an error. All of them corrupted data.

1. You Are Storing Money as a Float

This is the most expensive mistake on the list. Literally.

// Migration that will cost you money
Schema::create('invoices', function (Blueprint $table) {
    $table->id();
    $table->float('total');        // DO NOT DO THIS
    $table->float('tax_amount');   // DO NOT DO THIS
    $table->float('discount');     // DO NOT DO THIS
});

Floats are IEEE 754 binary representations. They cannot represent most decimal fractions exactly. The number 0.1 does not exist in binary floating point. What gets stored is 0.100000000000000005551115... or something close to it.

For most applications, this does not matter. For financial applications, it destroys trust.

Here is what happens in production:

SELECT SUM(total) FROM invoices WHERE tenant_id = 42;
-- Expected: 157,250.00
-- Actual:   157,249.99999999997

One sen off. Nobody notices for months. Then the accountant tries to reconcile the books against the bank statement and every total is off by fractions of a sen. Some high. Some low. No pattern. No consistency.

The fix is integer cents. Store every monetary value as an integer representing the smallest currency unit.

// Migration that respects money
Schema::create('invoices', function (Blueprint $table) {
    $table->id();
    $table->unsignedBigInteger('total_cents');
    $table->unsignedBigInteger('tax_amount_cents');
    $table->unsignedBigInteger('discount_cents');
});

In your model, cast it for display:

// In your Invoice model
public function getTotalAttribute(): string
{
    return number_format($this->total_cents / 100, 2);
}

RM 1,572.50 becomes 157250 in the database. No rounding. No drift. No angry accountants.

If you absolutely must store decimals, use decimal(10,2) instead of float. But integer cents is cleaner and eliminates the problem entirely.

2. Your Charset Is Silently Eating Data

MySQL has a charset called utf8. It is not UTF-8. That is not a typo. MySQL's utf8 only supports characters up to 3 bytes. Real UTF-8 supports up to 4 bytes.

Emojis are 4 bytes. Many Chinese, Japanese and Korean characters are 4 bytes. Mathematical symbols are 4 bytes. If your column uses utf8 (also called utf8mb3) and someone inserts a 4-byte character, one of two things happens depending on your SQL mode:

In strict mode, MySQL throws an error and the insert fails. In non-strict mode, MySQL silently truncates the string at the first 4-byte character. Everything after the emoji disappears. No warning. No error.

A user writes a product review: “Great product! 😊 Would buy again.” What gets stored: “Great product! “ Everything after the emoji is gone.

Laravel has defaulted to utf8mb4 in config/database.php since Laravel 5.4. But that only applies if your database and tables were actually created with utf8mb4. If you inherited a database from an older project, or your server's MySQL defaults are set to utf8, your tables might still be on the old charset.

Check right now:

SELECT TABLE_NAME, TABLE_COLLATION 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database_name';

If you see utf8_general_ci or utf8_unicode_ci anywhere, you have a problem.

The fix:

ALTER DATABASE your_database_name 
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Then for each table:
ALTER TABLE your_table_name 
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

And verify your config/database.php:

'mysql' => [
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
],

One warning: if you have indexed VARCHAR(255) columns, switching to utf8mb4 may hit InnoDB's index key length limit (3072 bytes in modern MySQL, 767 bytes in older versions). The safe maximum for indexed columns on older MySQL is VARCHAR(191). On MySQL 8.0+, you are fine with VARCHAR(255).

3. Your WHERE Clause Is Comparing the Wrong Types

This one is invisible. It does not corrupt stored data. It corrupts your query results.

MySQL performs implicit type casting when you compare a string column to a numeric value. The result is often not what you expect.

SELECT * FROM users WHERE phone = 0;

You would expect this to return users whose phone number is literally 0. Instead, it returns every row where the phone column contains a string that does not start with a digit. Because MySQL casts the string "not-a-number" to the integer 0, which equals 0.

In Laravel, this happens more subtly:

// Somewhere in a controller
$user = User::where('ic_number', $request->input('ic_number'))->first();

If ic_number is a VARCHAR column and the request sends an integer (from a form that strips the leading zero), MySQL casts every value in the column to an integer for comparison. This bypasses any index on the column (full table scan) and returns wrong results.

Malaysian IC numbers start with digits like 010315.... If the leading zero is stripped and sent as 10315..., the query silently matches the wrong person.

The fix: always ensure your comparison types match. In Laravel, cast your input explicitly:

$user = User::where('ic_number', (string) $request->input('ic_number'))->first();

Better yet, validate the format before it hits the database:

$request->validate([
    'ic_number' => ['required', 'string', 'size:12'],
]);

4. You Have No Unique Constraint and Now You Have Duplicates

This is the one that keeps me up at night because it is so easy to prevent and so painful to fix after the fact.

// This migration is missing something critical
Schema::create('subscriptions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id');
    $table->foreignId('plan_id');
    $table->timestamp('starts_at');
    $table->timestamp('ends_at')->nullable();
    $table->timestamps();
});

Can you spot what is missing? There is no unique constraint preventing a user from having two active subscriptions to the same plan.

Your application code probably checks for duplicates before inserting:

// This looks safe but it is not
if (!Subscription::where('user_id', $user->id)->where('plan_id', $plan->id)->whereNull('ends_at')->exists()) {
    Subscription::create([...]);
}

This fails under concurrency. Two requests arrive at the same millisecond. Both check. Both find no existing subscription. Both insert. Now you have a duplicate. The user gets charged twice. Or gets double the features. Or both.

The fix is a database-level unique constraint:

Schema::create('subscriptions', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id');
    $table->foreignId('plan_id');
    $table->timestamp('starts_at');
    $table->timestamp('ends_at')->nullable();
    $table->timestamps();
    
    $table->unique(['user_id', 'plan_id', 'ends_at']);
});

Now the database itself rejects duplicates. No race condition can bypass it. Your application code should still check (for a better user experience), but the constraint is your safety net.

For existing databases with duplicates already in them, you need to clean up first. Find them:

SELECT user_id, plan_id, COUNT(*) as count
FROM subscriptions
WHERE ends_at IS NULL
GROUP BY user_id, plan_id
HAVING count > 1;

Fix them. Then add the constraint. Do not skip the cleanup step or the migration will fail.

5. Your Dates Are Lying About Time Zones

MySQL has two temporal types that look almost identical but behave completely differently.

DATETIME stores a literal date and time. No time zone information. If you store 2026-02-19 14:30:00, you get back 2026-02-19 14:30:00 regardless of the server's time zone setting.

TIMESTAMP stores the value as UTC internally and converts it to the session's time zone on retrieval.

The problem happens when you mix them. Or when your application server and database server are in different time zones. Or when daylight saving time changes.

Here is a real scenario. Your Laravel app is configured with 'timezone' => 'Asia/Kuala_Lumpur' in config/app.php. Your MySQL server is set to UTC. You store an event time using a DATETIME column.

// In your controller
Event::create([
    'name' => 'Client meeting',
    'starts_at' => '2026-03-15 09:00:00', // 9 AM MYT
]);

Laravel sends 2026-03-15 09:00:00 to MySQL. MySQL stores it as-is. No conversion. No problem... until you query:

// Later, in a scheduled command running on the server
$upcoming = Event::where('starts_at', '<=', now())->get();

now() in Laravel returns Carbon::now('Asia/Kuala_Lumpur'). But Carbon::now() in a queue worker might return UTC depending on your queue configuration. Now your comparisons are off by 8 hours. Events fire early or late. Meeting reminders go out at 1 AM.

The fix: pick a time zone convention and enforce it everywhere.

My preference: store everything as UTC in DATETIME columns. Convert to local time only for display.

In config/app.php:

'timezone' => 'UTC',

In your Blade templates or API responses:

{{ $event->starts_at->timezone('Asia/Kuala_Lumpur')->format('d M Y, h:i A') }}

If you use TIMESTAMP columns instead, MySQL handles the conversion. But TIMESTAMP has a range limit: it only supports dates from 1970-01-01 00:00:01 to 2038-01-19 03:14:07. If you are storing birthdates, historical records or dates far in the future, TIMESTAMP will silently clip or overflow.

6. Your VARCHAR Is Silently Truncating

You created a column for addresses:

$table->string('address'); // VARCHAR(255) by default

A customer enters their full address: a 280-character string with unit number, building name, street, city, postcode and state. What gets stored depends entirely on your SQL mode.

With strict mode enabled (the default in MySQL 8.0 and Laravel’s recommended configuration), the insert throws an error. Good.

Without strict mode, MySQL silently truncates the string to 255 characters. The postcode and state disappear. The address is now incomplete. Delivery fails. No one knows why because the data “looks” correct until you count the characters.

The danger is not obvious columns like address. It is columns you do not think about. A notes field. A description. A rejection_reason in an approval workflow. Any column where a user types free text.

The fix is two-fold.

First, make sure strict mode is enabled. Check:

SELECT @@sql_mode;

You should see STRICT_TRANS_TABLES in the result. If you do not, add it to your MySQL configuration or set it in Laravel's config/database.php:

'mysql' => [
    'strict' => true,
],

Second, size your columns correctly. If a field could contain more than 255 characters, use TEXT:

$table->text('address');
$table->text('notes');
$table->text('rejection_reason');

TEXT holds up to 65,535 bytes. MEDIUMTEXT holds up to 16 MB. Use the right type for the data.

7. Your Auto-Increment Will Overflow and You Will Not See It Coming

Every $table->id() in Laravel creates an UNSIGNED BIGINT auto-incrementing primary key. That gives you a maximum value of 18,446,744,073,709,551,615. You will never hit it.

But older migrations and older MySQL defaults sometimes used INT UNSIGNED instead. The maximum for INT UNSIGNED is 4,294,967,295. Four billion. That sounds like a lot until you realize it is not just user records counting against it.

Transaction logs. API request records. Queue job histories. Audit trails. Event sourcing tables. Any table that gets a new row per action, not per user, can blow through four billion faster than you think.

A SaaS application logging 100 requests per second hits 4.29 billion in about 16 months. A high-traffic e-commerce platform during a sale can push 1,000 inserts per second on event tables.

When the auto-increment overflows, MySQL does not throw an error. It tries to insert with the maximum value. The insert fails with a duplicate key error because that ID already exists. Your application crashes. Your queue workers start failing. No amount of restarting fixes it because the next ID is still the maximum value.

Check your tables right now:

SELECT 
    TABLE_NAME,
    AUTO_INCREMENT,
    COLUMN_TYPE
FROM information_schema.TABLES t
JOIN information_schema.COLUMNS c 
    ON t.TABLE_NAME = c.TABLE_NAME 
    AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
WHERE t.TABLE_SCHEMA = 'your_database_name'
    AND c.COLUMN_KEY = 'PRI'
    AND c.EXTRA LIKE '%auto_increment%'
    AND COLUMN_TYPE LIKE '%int unsigned%'
    AND COLUMN_TYPE NOT LIKE '%bigint%'
ORDER BY AUTO_INCREMENT DESC;

This shows you every table using regular INT for its primary key, ordered by how close to the limit they are.

The fix is to migrate to BIGINT:

ALTER TABLE your_table MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

Do this during a maintenance window. On large tables, this operation can take minutes to hours because MySQL rewrites every row. Plan for downtime or use pt-online-schema-change from Percona Toolkit to do it without locking.

The Common Thread

Every mistake in this list shares three properties.

First, they are silent. No exception. No error log. No failed test. The data just quietly becomes wrong.

Second, they are cumulative. One float rounding error is invisible. A million of them adds up to a financial discrepancy that takes days to trace. One truncated address is a mystery. A hundred of them is a pattern that nobody connects to the database.

Third, they are preventable. Every single one. With the right column type, the right constraint, the right SQL mode and the right migration.

The Audit You Should Run Today

If you manage a Laravel application on MySQL, run these checks right now. It takes ten minutes and might save you months of debugging.

Check 1: Strict mode. Run SELECT @@sql_mode; and confirm STRICT_TRANS_TABLES is present.

Check 2: Charset. Query information_schema.TABLES for any table still using utf8_general_ci or utf8_unicode_ci. Convert them to utf8mb4_unicode_ci.

Check 3: Float columns. Search your migrations for $table->float( and $table->double(. If any of them store monetary values, plan a migration to integer cents or decimal.

Check 4: Missing unique constraints. Look at every table that represents a relationship or a status. Ask yourself: “Can this row legitimately exist twice?” If not, add a unique constraint.

Check 5: Auto-increment types. Run the query from section 7. If any high-traffic table uses INT instead of BIGINT, schedule the migration before it becomes an emergency.

Check 6: Time zone consistency. Compare your Laravel config/app.php timezone, your MySQL @@global.time_zone and your server's system time zone. If they do not all agree and you have not explicitly handled the conversion, you have a bug waiting to happen.

Your database does not lie on purpose. It lies because you told it to accept bad data and it obeyed.

Stop trusting. Start verifying.