CodeIgniter 1.x中SQL语法错误排查：同代码在不同MariaDB实例表现异常

Hey there, let's break down why you're hitting that 1064 syntax error with FIND_IN_SET—especially since the exact same model code works on another database. The issue is almost certainly tied to environment differences or data/parameter quirks in your current setup. Here's how to diagnose and fix it:

1. First, Verify the Generated SQL (Critical!) #

The fastest way to spot the problem is to see the actual SQL your CodeIgniter model is producing. In your model function, right before executing the query, add code to output the compiled SQL:

// For CodeIgniter 1.x, use this to get the raw SQL
$sql = $this->db->_compile_select();
echo "<pre>" . htmlspecialchars($sql) . "</pre>";
exit;

Compare this SQL to the one generated in the working database environment. Look specifically at the FIND_IN_SET clause—you’ll likely see that in the broken setup, one of the parameters is missing (e.g., FIND_IN_SET(, pr_policy.department_ids) instead of FIND_IN_SET('123', pr_policy.department_ids)).

2. Check if FIND_IN_SET Parameters Are Empty/Unset #

The 1064 error happens because MariaDB expects two arguments for FIND_IN_SET, but your query is passing fewer. Common reasons:

• The variable you’re using for the first parameter (e.g., $department_id or $designation_id) is empty or undefined in the current environment. Use var_dump($your_variable) to confirm its value before building the query.
• If you’re using user input (like $this->input->post()), make sure the input is actually being sent in the current request. For example, maybe the form field name is misspelled, or the API request isn’t including the required parameter.

3. Compare SQL Modes Between Databases #

MariaDB’s SQL mode can enforce stricter syntax rules. Your working database might have a more permissive mode that tolerates edge cases (like empty parameters in functions) that your current 10.1.25 instance rejects.

Run this query in both databases to compare:

SELECT @@sql_mode;

If your current database has modes like STRICT_TRANS_TABLES or NO_EMPTY_STRING enabled that the working one doesn’t, that could be triggering the error. You can temporarily adjust the mode for testing:

SET sql_mode = 'ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

If this fixes the issue, you can update the my.cnf/my.ini file to set the desired mode permanently.

4. Validate Table Structure Consistency #

Double-check that the tables pr_policy, pr_departments, and pr_designation have identical structures in both databases:

• Ensure columns used in FIND_IN_SET (like department_ids in pr_policy) exist and are of the correct type (e.g., VARCHAR or TEXT—not a numeric type that might cause implicit conversion issues).
• Confirm there are no typos in column names in your model code (a typo could lead to unexpected parsing of the FIND_IN_SET clause).

5. Fix the Query Logic #

Once you’ve identified the missing parameter, update your model code to handle empty values gracefully. For example:

$department_id = $this->input->post('department_id');
if (!empty($department_id)) {
    $this->db->where("FIND_IN_SET('{$department_id}', pr_policy.department_ids)");
}

This way, you only add the FIND_IN_SET clause when the parameter has a valid value, avoiding the syntax error entirely.

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