Error
Error Code:
1221
MariaDB Error 1221: Incorrect SQL Syntax Usage
Description
MariaDB Error 1221, 'Incorrect usage of %s and %s', indicates that your SQL query attempts to combine two specific SQL elements (functions, clauses, or keywords) in a way that is syntactically or semantically invalid according to MariaDB's rules. This error typically occurs during query execution when the SQL parser detects an incompatible pairing of constructs.
Error Message
Incorrect usage of %s and %s
Known Causes
3 known causesIncompatible SQL Constructs
Attempting to use two or more SQL functions, clauses, or keywords together that are not syntactically or semantically compatible in MariaDB (e.g., specific combinations of aggregate functions with other clauses).
Incorrect Syntax or Order
Misplacing arguments, clauses, or keywords within a complex SQL statement, violating MariaDB's specific parsing rules for the order of operations.
Feature/Version Mismatch
Using SQL syntax or features that are either not supported in your specific MariaDB version or are incompatible with its SQL dialect.
Solutions
3 solutions available1. Identify and Correct Keyword Misuse easy
Review your SQL query for instances where reserved keywords are used incorrectly or as identifiers.
1
Examine the SQL query that triggered the error. Look for any keywords that might be used in a way that conflicts with their standard SQL/MariaDB function.
2
Common culprits include using keywords like `SELECT`, `FROM`, `WHERE`, `GROUP`, `ORDER`, `BY`, `LIMIT`, `TABLE`, `DATABASE` as column names or table names without proper quoting.
3
If a keyword is being used as an identifier, enclose it in backticks (`).
SELECT `order` FROM `table` WHERE `group` = 'some_value';4
Alternatively, consider renaming your columns or tables to avoid using reserved keywords altogether. This is generally a more robust long-term solution.
2. Verify Function and Operator Usage medium
Ensure functions and operators are used with the correct number and type of arguments.
1
The error message 'Incorrect usage of %s and %s' often indicates that a function or operator is being applied with the wrong number of arguments or with incompatible data types.
2
Carefully review the functions and operators used in your query. For example, `CONCAT()` expects at least two arguments, `SUBSTRING()` expects a starting position and length.
3
Check the MariaDB documentation for the specific function or operator you are using to confirm its syntax and argument requirements.
SELECT CONCAT(column1) FROM my_table; -- Incorrect, CONCAT needs at least two arguments
SELECT CONCAT(column1, column2) FROM my_table; -- Correct4
Ensure that the data types of the values being passed to functions or operators are compatible. Implicit type casting can sometimes lead to unexpected errors.
3. Review Subquery Syntax and Placement medium
Validate that subqueries are correctly formed and placed within the main query.
1
Subqueries can sometimes lead to syntax errors if not constructed properly. The error might arise from an improperly closed subquery or an incorrect placement of a subquery.
2
Ensure that all subqueries are enclosed in parentheses `()`.
SELECT column1 FROM my_table WHERE column2 IN (SELECT column3 FROM another_table); -- Correct
SELECT column1 FROM my_table WHERE column2 IN SELECT column3 FROM another_table; -- Incorrect3
Verify that the subquery returns a single column when used with operators like `=`, `IN`, or when compared with a single value. If it's meant to return multiple columns, ensure the operator is appropriate (e.g., `EXISTS` or a join).
4
Check for common mistakes like missing `FROM` clauses or incorrect `WHERE` clauses within the subquery itself.