Editing Troubleshooting of MySQL errors (section)

== SQL and Query Errors ==

=== Caught exception: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined ===
Solutions
* Number of question marks is not matched with the number of query values <ref>[https://stackoverflow.com/questions/10966251/sqlstatehy093-invalid-parameter-number-parameter-was-not-defined php Invalid parameter number: parameter was not defined - Stack Overflow]</ref>
* The array of query values should not be the associative array. Use sequential array!

=== ERROR 1054 - Unknown column in 'where clause' ===
Message: [Err] 1054 - Unknown column 'xxx' in 'where clause'

Solution:
# check the column name 'xxx' if exists
# if the column name 'xxx' was computed by the [http://dev.mysql.com/doc/refman/5.7/en/example-user-variables.html User-Defined Variables]. Enclosed the whole query into another parent derived query.
<pre>
-- The query which met the error message: [Err] 1054 - Unknown column 'rank' in 'where clause'
SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank`   
FROM
(
        ...
) semi
WHERE semi.rank <= 10
</pre>

Enclosed the whole query into another parent derived query.
<pre>
SELECT final.*
FROM
(
        SELECT semi.*, IF(semi.id = semi.prev, @rank := @rank +1, @rank := 1 ) AS `rank`   
        FROM
        (
                ...
        ) semi
) final
WHERE final.rank <= 10
</pre>


=== ERROR 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ===
Message
<pre>
Query Error (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.posts.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
</pre>

Query with Error
<pre>
-- Create sample table 
CREATE TABLE posts (
   id INT,
   post_id VARCHAR(10), 
   author VARCHAR(50),
   content TEXT,
   likes INT,
   post_time DATETIME
);

-- Insert sample data
INSERT INTO posts VALUES
(1, 'A123', 'Alice', 'First post', 10, '2024-01-01'),
(2, 'A123', 'Bob', 'Great!', 5, '2024-01-02'), 
(3, 'A123', 'Bob', 'Nice!', 3, '2024-01-03');

-- This will cause an error
SELECT 
   id,          -- Problem: Not in GROUP BY
   post_id,
   author,
   GROUP_CONCAT(content SEPARATOR "\n") AS content,     -- This is OK as it uses aggregate function
   likes        -- Problem: Not in GROUP BY
FROM posts
GROUP BY post_id, author;
</pre>

Solution: Fix the query by either (1) including all columns in GROUP BY or (2) using aggregate functions (like MAX, MIN, SUM, COUNT, GROUP_CONCAT) 
<pre>
-- Method 1: disable `sql_mode=only_full_group_by`
```
There are two ways to disable `sql_mode=only_full_group_by`:

1. Temporary Change (Only affects current session)
```sql
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
```

2. Permanent Change (Modify MySQL configuration file)
- Locate MySQL configuration file (usually `my.cnf` or `my.ini`) 
- Add or modify in the `[mysqld]` section:
```ini
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
```

Important Notes:
1. After modifying the configuration file, MySQL service needs to be restarted to take effect
2. Disabling this setting is not recommended because:
   - May lead to unpredictable query results
   - Violates SQL standards
   - May cause compatibility issues in future versions

The recommended approach is to fix the SQL query rather than changing the SQL mode.
```

-- Method 2: Add all columns to GROUP BY
SELECT 
   id,
   post_id,
   author,
   content,
   likes
FROM posts
GROUP BY post_id, author, id, content, likes;

-- Method 3: Use aggregate functions for non-GROUP BY columns  
SELECT 
   MAX(id) as id,
   post_id,
   author, 
   GROUP_CONCAT(content) as contents,
   SUM(likes) as total_likes
FROM posts
GROUP BY post_id, author;
</pre>

=== ERROR 1052 - Column 'column_name' in field list is ambiguous ===
Message: Error Code: 1052. Column 'column_name' in field list is ambiguous

Cause: Since 'column_name' is present in 2 or more tables ...<ref>[https://stackoverflow.com/questions/19351633/error-code-1052-column-admin-id-in-field-list-is-ambiguous mysql - Error Code: 1052 Column 'admin_id' in field list is ambiguous - Stack Overflow]</ref>

Solution: Remain only one table name 'column_name' OR adding the table name alias.

=== Error!: SQLSTATE[42000]: Syntax error or access violation ===
Message: {{kbd | key=<nowiki>Error!: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ...</nowiki>}}

Solution: 
* [[SQL syntax debug]]
* [https://www.eversql.com/sql-syntax-check-validator/ SQL Syntax Check Online, SQL Validator, Instant SQL Compiler Online – EverSQL] {{exclaim}} Not support the [https://www.php.net/manual/en/pdo.prepare.php PHP: PDO::prepare] which the query syntax contains question marks.

=== ERROR 1366: Incorrect string value ===
Message: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9F\x87\xAF\xF0\x9F...' for column 'XXX'

Solution:
* Check the charset of PHP PDO. AND execute {{kbd | key=set names utf8mb4}}<ref>[https://stackoverflow.com/questions/54947392/incorrect-string-value-when-trying-to-pass-emoji-to-the-db-encoded-with-utf8mb4 php - Incorrect string value when trying to pass emoji to the db encoded with utf8mb4 - Stack Overflow]</ref>
* Check the configuration of table
** CHARACTER SETS (aka 字元集、字符集): {{kbd | key=utf8mb4}}
** COLLATION (aka 定序、字元序): {{kbd | key=utf8mb4_unicode_ci}}

=== ERROR 1070 (42000): Specified key was too long; max key length is 767 bytes ===
'''Envoronment''': MySQL 5.6

'''Root cause''': "By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Statement”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use the DYNAMIC or COMPRESSED row format." <ref>[https://dev.mysql.com/doc/refman/5.6/en/innodb-limits.html MySQL :: MySQL 5.6 Reference Manual :: 14.22 InnoDB Limits]</ref>

'''Solution'''<ref>[https://www.opencli.com/mysql/mysql-%E5%95%8F%E9%A1%8C-1071-42000-specified-key-was-too-long MySQL 問題: 1071 (42000): Specified key was too long]</ref><ref>[https://terrytongcc.com/%E5%A6%82%E4%BD%95%E8%A7%A3%E6%B1%BAmysql%E5%87%BA%E7%8F%BEerror-1118-row-size-too-large-8126%E5%95%8F%E9%A1%8C/ 如何解決MySQL出現Error 1118: row size too large (> 8126)問題 | Terry Tong – Full Stack Web Developer]</ref>:
* execute SQL query as the following:

<pre>
SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_format_max = Barracuda;
</pre>

* modify the MySQL configuration file
<pre>
innodb_file_per_table = 1
innodb-file-format = BARRACUDA
innodb-large-prefix = ON
innodb_file_format_max = BARRACUDA
</pre>

* restart the MySQL server

* execute SQL query as the following:
<pre>
ALTER TABLE `table_name` ROW_FORMAT=COMPRESSED;

</pre>

=== ERROR 1267: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'concat' ===

Error condition: Tried to concat different type of data e.g. CONCAT(string, int)

Solution: {{kbd | key =<nowiki>SELECT CONCAT(`string_column`, CONVERT(`int_column`, CHAR))</nowiki>}} or {{kbd | key =<nowiki>SELECT CONCAT(`string_column`, CAST(`int_column` AS CHAR))</nowiki>}}<ref>[https://stackoverflow.com/questions/15368753/cast-int-to-varchar mysql - Cast int to varchar - Stack Overflow]</ref><ref>[http://blog.51cto.com/bian5399/1092772 mysql字符集问题：Illegal mix of collations-每天让自己进步一点-51CTO博客]</ref>

=== ERROR 1235: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' ===
Message: ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'. 

Solution: Change the syntax of subquery to {{kbd | key =column_name BETWEEN start_number to end_number}}

Further reading:
* [https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.6/en/subquery-restrictions.html MySQL :: MySQL Restrictions and Limitations :: 4 Restrictions on Subqueries]

=== Error: MySQL Incorrect datetime value: '0000-00-00 00:00:00' ===

Workaround solution<ref>[https://stackoverflow.com/questions/35565128/mysql-incorrect-datetime-value-0000-00-00-000000 MySQL Incorrect datetime value: '0000-00-00 00:00:00' - Stack Overflow]</ref>:
<pre>
SET SQL_MODE='ALLOW_INVALID_DATES';
</pre>

=== Optimization Solutions for Query Performance ===

Originally, product_id (VARCHAR, 200) was used as the primary key. When the data volume reached approximately 600,000, data processing became very slow. Adding a field id (int) as the primary key can solve the problem of slow query speed.