sql_mode设置报错及modified_at字段无默认值问题求助

Hey there, let’s work through these two errors you’re facing and get everything running smoothly:

1. Resolving the Error Number: 1364 Field 'modified_at' doesn't have a default value #

This error pops up when stricton is set to TRUE because MySQL’s strict mode enforces that non-nullable fields either have a default value or are explicitly assigned a value during inserts/updates. Here are two solid fixes:

• Set a default value for the modified_at field
  Run this SQL query (replace your_table_name with the actual table containing the modified_at column):

  ALTER TABLE your_table_name MODIFY COLUMN modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
  

  This sets the field to automatically update to the current time whenever the row is modified, and uses the current timestamp as the default if no value is provided.

• Configure your ORM to auto-populate modified_at
  Since your error mentions ci_session_lock, it looks like you’re using CodeIgniter. In your model, enable auto-timestamps to handle this automatically:

  protected $useTimestamps = true;
  protected $updatedField = 'modified_at';
  

  This will make CodeIgniter automatically set the modified_at value whenever you save or update a record.

2. Fixing the Variable 'sql_mode' can't be set... Error #

When stricton is FALSE, it seems your framework (CodeIgniter) is trying to dynamically modify the sql_mode but has a syntax error in the query. Here’s how to fix this:

• Set sql_mode directly in MySQL's config file
  Instead of letting the framework handle it, define a valid sql_mode in your MySQL configuration (usually my.cnf on Linux or my.ini on Windows):

  sql_mode = "NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"
  

  Save the file and restart your MySQL service. This ensures a consistent, valid sql_mode without relying on framework-generated queries.

• Update CodeIgniter (if using an older version)
  This specific sql_mode syntax error is a known bug in some older CodeIgniter versions. Upgrading to the latest stable release of the framework should resolve the faulty query generation.

Why the Error Lingers Until Refresh (When stricton=TRUE) #

The error message sticking around until you refresh is likely due to cached error pages or leftover logs. To clear this up:

• Delete all files in CodeIgniter’s application/cache directory
• Check your MySQL error log to confirm no new errors are being generated (the initial error was from a prior query, and the refresh just loads a clean page)

Once you implement the fixes for the modified_at field, you can keep stricton=TRUE (which is recommended for data integrity) without any persistent errors.

内容的提问来源于stack exchange，提问作者Suraj Kumar