Главная » Ошибки » FIX sqlstate 42000 – mysql error 1064 – you have an error in your sql syntax

FIX sqlstate 42000 – mysql error 1064 – you have an error in your sql syntax

Understand and FIX MySQL error 1064 – sqlstate 42000

SQL 1064 means that MySQL can’t understand your command!

This type of error first need to be understood and after that you can fix it. The common causes of this error are:

  • Upgrading MySQL or any other database to another version
  • Using Wrong syntax that is not supported on your current version
  • Error in applying the back tick symbol or while creating a database without them can also create an error
  • Due to using reserved words
  • Particular data missing while executing a query
  • Mistyped/obsolete commands

If you see words like “near” or “at line”, you need to check for problems in those lines of the code before the command ends.

How do I Fix SQL Error Code 1064?

  1. Read the message on the error:

So in general the error tells you where the parser encountered the syntax error. MySQL also suggest how to fix it.  Check the example below …..

  1. Check the text of your command!

In some cases the PHP commands has wrong lines. Create SQL commands using programing language can be the good example of this. So you will need to check and fix those commands. Use echo, console.log(), or its equivalent to show the entire command so you can see it.

  1. Mistyping of commands

The error can occur also when you misspell a command (e.g. instead of UPDATE you write UDPATE). This can occur often since are so easy to miss. To prevent this, make sure that you review your command for any typing error before running it. There are a lot of online syntax checkers that can help to debug your queries.

  1. Check for reserved words

Reserved words are words that vary from one MySQL version to another. Every version has its list of keywords that are reserved. They are used to perform specific functions in the MySQL engine. If you read the error and identified that occurred on an object identifier, check that it isn’t a reserved word (and, if it is, be sure that it’s properly quoted). “If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.”  You can find a full list of the reserved words specific for each MySQL version and their usage requirements at MySQL.com.

  1. Obsolete commands – another reason

Another possible reason for the sqlstate 42000 MySQL error 1064 is when you use outdated commands. As Platforms grow and change, some commands that were useful in the past are replaced by more efficient ones. A number of commands and keywords have been deprecated. This mean that they are due for removal, but still allowed for a short period of time before they turn obsolete. On cases that you have an older backup of a MySQL database that you want to import, a quick solution is to just search and replace “TYPE=InnoDB” with “ENGINE=InnoDB”.

  1. Particular data is missing while executing a query

If the relevant data missing from the database which is required for the query, you’re obviously going to run into problems.  Using phpMyAdmin or MySQL Workbench you can enter the missing data. Interface of the application allow you to add the missing data manually to an appropriate row of the table.

You have an error in your sql syntax

You have an error in your sql syntax

“You have an error in your sql syntax” – Example 1

The error code generated jointly with the statement “syntax error or access violation”, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL (or any other like MariaDB) server version for the right syntax to use near” and after that the part of SQL code where the issue is. So in simple way, the error view is showing you also where is the error. For example we have the error:

READ ALSO –   Sql server move database files to another drive in fastest way – data and log“Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘from, to, name, subject, message) VALUES (‘[email protected]’, ‘[email protected],com’ at line 1”

So how to understand this?

from is a keyword in SQL. You may not use it as a column name without quoting it. In MySQL, things like column names are quoted using back ticks, i.e. `from`. Or you can just rename the column.

Another example of “You have an error in your sql syntax” sqlstate 42000 – Example 2

Error:

check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1 [ SELECT COUNT(*) as count,region, MONTHNAME(date) asmonth FROM tempur_stores.stats WHERE date > DATE_ADD(DATE(NOW()), INTERVAL -1 WEEK) AND date < DATE(NOW()) GROUP BY region, MONTH(date ]

On the query:

$stmt = DB::query(Database::SELECT, ‘SELECT COUNT(*) as `count`,`region`, MONTHNAME(`date`) as`month` FROM tempur_stores.stats WHERE `date` > DATE_ADD(DATE(NOW()), INTERVAL -1 WEEK) AND `date` < DATE(NOW()) GROUP BY `region`, MONTH(`date`');

The above query is missing a closing parenthesis in the query:

$stmt = DB::query(Database::SELECT, ‘SELECT COUNT(*) as `count`,`region`, MONTHNAME(`date`) as`month`

FROM tempur_stores.stats

WHERE `date` > DATE_ADD(DATE(NOW()), INTERVAL -1 WEEK)

AND `date` < DATE(NOW())GROUP BY `region`, MONTH(`date`');----------  ^ right there

Just put a parenthesis ) before that apostrophe and it should work.

MariaDB error 1064 – Example 3

An example with MariaDB version issue. Trying to do example of tagging and when:

$id = Questions::create([            ‘body’ => request(‘title’),            ‘skillset_id’ => request(‘skillsetId’),            ‘tags’ => [‘red’, ‘blue’]        ])->id;

Getting error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘>’$.”en”‘ = ? and `type` is null limit 1’ at line 1 (SQL: select * from `tags` where `name`->’$.”en”‘ = red and `type` is null limit 1)

Reason is that is using MariaDB and JSON columns are only supported by MySQL. Convert to MySQL to resolve the issue.

MariaDB error 1064

MariaDB error 1064

Fix error 1064 mysql 42000 while creating a database – Example 4

MySQL error 1064 can be appearing also while you are creating database using hyphen in the name like Test-Db. This can be solved by using back tick around the database name properly or remove the hyphen in the database name.

Example:

mysql> create database Test-DB;

You will get error:

ERROR 1064 (42000): 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 ‘-DB’ at line 1

Solution:

mysql> create database ` Test-DB `;

So adding back tick around the database name will solve the issue.

Transfer WordPress MySQL database to another server

Exporting WordPress database to another server can also be cause the 1064 error. Can be resolved by choosing the compatibility mode and changing the database version to the current version you’re using. Please select the compatibility mode under the advanced tab when performing a backup and after that click the auto-detect file character set when restoring the MySQL database.

Read Also –

  1. Location of SQL Server Error Log File
  2. How to fix SQL Server Error 18456
  3. How to Restore Master Database

Troubleshooting

This should generally be the first step to troubleshoot any SQL syntax error in a large query: iteratively comment out blocks of SQL to narrow down where the problem is.

TIP: To make this process easier, change the group by clause to use position references
eg: group by 1,2,3,4,5 instead of group by orders.status, orders.date, to_char(…)…
as well as separate the where and having clauses onto multiple lines.

So for example, say we have the following query:

play_arrow

WITH cte AS (
select id, status, sales_amountfrom orders
)
select status, foo.date, sum(cte.sales_amount), count(*) from cte
join foo on cte.date = foo.date
group by status, foo.date
order by 3 desc

We could start by running just the portion in the CTE:

play_arrow

— WITH cte AS (
select id, status, sales_amountfrom orders
— )
— select status, foo.date, sum(cte.sales_amount), count(*)
— from cte
— join foo on cte.date = foo.date
— group by 1, 2
— order by 3 desc

Then strip out the aggregates and portions related to them

play_arrow

WITH cte AS (
select id, status, sales_amountfrom orders
)
select status, foo.date, — sum(cte.sales_amount), count(*)
from cte
join foo on cte.date = foo.date
— group by 1, 2
— order by 3 desc

Iteratively stripping out / adding back in portions of the query until you find the minimum query to trigger the error.

  • Lookup functions and syntax If the query is small enough, or if we’ve narrowed the scope enough with 1, google all the functions used in the query and verify that they exist and are being used correctly.

  • Verify all objects exist Verify that you’ve joined all tables used in the select, where, and having clause, and that those tables exist in the db. Once we’ve narrowed things down from 1, also check that each column exists in the table specified.

This content is subject to limited support .                

  • sql
  • Like
  • Quote
  • Share

class=”seperator m-h m-b-0 seperator_topic_helpfulness”>

<хедер class="list__items--хедер qa-topic-reply-box-хедер">

Using quotation marks

Another common error that occurs when writing SQL project is to use double quotation marks instead of single ones. Single quotation marks are used to delimit strings. For example, double quotation marks are used here instead of single ones, which cause an error

Useng double quotation marks instead of single ones

Invalid column name error

Replacing quotation marks with the proper ones, resolves the error

Replacing quotation marks with the proper ones

There are situations where double quotation marks need to be used, for writing some general quotes, for example

Situation where double quotation marks need to be used

As shown in the previous example, this will cause an error. But, this doesn’t mean that double quotes can’t be used, they just have to be inside the single quotes. However, adding single quotes in this example won’t solve the problem, but it will cause another one

Adding single quotes in this example won’t solve the problem

Since there is an apostrophe inside this quote, it is mistakenly used as the end of a string. Everything beyond is considered to be an error

Two SQL syntax errors

To be able to use an apostrophe inside a string, it has to be “escaped”, so that it is not considered as a string delimiter. To “escape” an apostrophe, another apostrophe has to be used next to it, as it is shown below

Dialog showing how to “escape” an apostrophe to resolve a SQL syntax error

Arrangement of commands

The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue

If the user, for example, is trying to create a new schema into an existing database, but first wants to check if there is already a schema with the same name, he would write the following command

Command to check if there is already a schema with the same name

However, even though each command is properly written, and is able to run separately without errors, in this form it results in an error

Incorrect syntax error

As the error message states, CREATE SCHEMA command has to be the first command that is given. The correct way of running this commands together looks like this

The correct way of running CREATE SCHEMA command

Error Message

SQL ERROR: syntax error at or near

Источники

  • https://www.get-itsolutions.com/fix-sqlstate-42000-mysql-error-1064/
  • https://community.looker.com/technical-tips-tricks-1021/sql-error-syntax-error-at-or-near-27263
  • https://www.sqlshack.com/sql-syntax-errors/
[свернуть]
Решите Вашу проблему!


×
Adblock
detector