Ошибка 1064 sql как исправить - Исправление ошибок и поиск оптимальных решений проблем

When issuing a command to MySQL, I’m getting error #1064 «syntax error».

What does it mean?
How can I fix it?

asked May 7, 2014 at 10:32

TL;DR

Error #1064 means that MySQL can’t understand your command. To fix it:

Read the error message. It tells you exactly where in your command MySQL got confused.

Examine your command. If you use a programming language to create your command, use echo, console.log(), or its equivalent to show the entire command so you can see it.

Check the manual. By comparing against what MySQL expected at that point, the problem is often obvious.

Check for reserved words. If the error occurred on an object identifier, check that it isn’t a reserved word (and, if it is, ensure that it’s properly quoted).

Aaaagh!! What does #1064 mean?

Error messages may look like gobbledygook, but they’re (often) incredibly informative and provide sufficient detail to pinpoint what went wrong. By understanding exactly what MySQL is telling you, you can arm yourself to fix any problem of this sort in the future.

As in many programs, MySQL errors are coded according to the type of problem that occurred. Error #1064 is a syntax error.
- What is this «syntax» of which you speak? Is it witchcraft?
  
  Whilst «syntax» is a word that many programmers only encounter in the context of computers, it is in fact borrowed from wider linguistics. It refers to sentence structure: i.e. the rules of grammar; or, in other words, the rules that define what constitutes a valid sentence within the language.
  
  For example, the following English sentence contains a syntax error (because the indefinite article «a» must always precede a noun):
  
  This sentence contains syntax error a.
- What does that have to do with MySQL?
  
  Whenever one issues a command to a computer, one of the very first things that it must do is «parse» that command in order to make sense of it. A «syntax error» means that the parser is unable to understand what is being asked because it does not constitute a valid command within the language: in other words, the command violates the grammar of the programming language.
  
  It’s important to note that the computer must understand the command before it can do anything with it. Because there is a syntax error, MySQL has no idea what one is after and therefore gives up before it even looks at the database and therefore the schema or table contents are not relevant.
How do I fix it?

Obviously, one needs to determine how it is that the command violates MySQL’s grammar. This may sound pretty impenetrable, but MySQL is trying really hard to help us here. All we need to do is…
- Read the message!
  
  MySQL not only tells us exactly where the parser encountered the syntax error, but also makes a suggestion for fixing it. For example, consider the following SQL command:
```
UPDATE my_table WHERE id=101 SET name='foo'
```
  That command yields the following error message:
  
  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 'WHERE id=101 SET name='foo'' at line 1
  
  MySQL is telling us that everything seemed fine up to the word WHERE, but then a problem was encountered. In other words, it wasn’t expecting to encounter WHERE at that point.
  
  Messages that say ...near '' at line... simply mean that the end of command was encountered unexpectedly: that is, something else should appear before the command ends.
- Examine the actual text of your command!
  
  Programmers often create SQL commands using a programming language. For example a php program might have a (wrong) line like this:
```
$result = $mysqli->query("UPDATE " . $tablename ."SET name='foo' WHERE id=101");
```
  If you write this this in two lines
```
$query = "UPDATE " . $tablename ."SET name='foo' WHERE id=101"
$result = $mysqli->query($query);
```
  then you can add echo $query; or var_dump($query) to see that the query actually says
```
UPDATE userSET name='foo' WHERE id=101
```
  Often you’ll see your error immediately and be able to fix it.
- Obey orders!
  
  MySQL is also recommending that we «check the manual that corresponds to our MySQL version for the right syntax to use«. Let’s do that.
  
  I’m using MySQL v5.6, so I’ll turn to that version’s manual entry for an UPDATE command. The very first thing on the page is the command’s grammar (this is true for every command):
```
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
```
  The manual explains how to interpret this syntax under Typographical and Syntax Conventions, but for our purposes it’s enough to recognise that: clauses contained within square brackets [ and ] are optional; vertical bars | indicate alternatives; and ellipses ... denote either an omission for brevity, or that the preceding clause may be repeated.
  
  We already know that the parser believed everything in our command was okay prior to the WHERE keyword, or in other words up to and including the table reference. Looking at the grammar, we see that table_reference must be followed by the SET keyword: whereas in our command it was actually followed by the WHERE keyword. This explains why the parser reports that a problem was encountered at that point.
A note of reservation

Of course, this was a simple example. However, by following the two steps outlined above (i.e. observing exactly where in the command the parser found the grammar to be violated and comparing against the manual’s description of what was expected at that point), virtually every syntax error can be readily identified.

I say «virtually all», because there’s a small class of problems that aren’t quite so easy to spot—and that is where the parser believes that the language element encountered means one thing whereas you intend it to mean another. Take the following example:
```
UPDATE my_table SET where='foo'
```
Again, the parser does not expect to encounter WHERE at this point and so will raise a similar syntax error—but you hadn’t intended for that where to be an SQL keyword: you had intended for it to identify a column for updating! However, as documented under Schema Object Names:
If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it. (Exception: A reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.) Reserved words are listed at Section 9.3, “Keywords and Reserved Words”.
```
[ deletia ]
```
The identifier quote character is the backtick (“`”):
```
mysql> SELECT * FROM `select` WHERE `select`.id > 100;
```
If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks:
```
mysql> CREATE TABLE "test" (col INT);
ERROR 1064: You have an error in your SQL syntax...
mysql> SET sql_mode='ANSI_QUOTES';
mysql> CREATE TABLE "test" (col INT);
Query OK, 0 rows affected (0.00 sec)
```

It is late but will help others and ofcourse will save time
My query was working in MySQL 5.7 in local system but on live we have version MySQL 8 and query stop working.

Query:

SELECT t.*
FROM groups t
ORDER BY t.id DESC
LIMIT 10 OFFSET 0

Output in MySQL 8:

Error in query (1064): Syntax error near ‘groups t ORDER BY t.id DESC’
at line …

I came to know groups is reserved word so I have to wrap groups with « quotes or change the table name to solve this issue.

answered Jul 18, 2021 at 13:13

Muhammad ShahzadMuhammad Shahzad

9,13021 gold badges83 silver badges130 bronze badges

For my case, I was trying to execute procedure code in MySQL, and due to some issue with server in which Server can’t figure out where to end the statement I was getting Error Code 1064. So I wrapped the procedure with custom DELIMITER and it worked fine.

For example, Before it was:

DROP PROCEDURE IF EXISTS getStats;
CREATE PROCEDURE `getStats` (param_id INT, param_offset INT, param_startDate datetime, param_endDate datetime)
BEGIN
    /*Procedure Code Here*/
END;

After putting DELIMITER it was like this:

DROP PROCEDURE IF EXISTS getStats;
DELIMITER $$
CREATE PROCEDURE `getStats` (param_id INT, param_offset INT, param_startDate datetime, param_endDate datetime)
BEGIN
    /*Procedure Code Here*/
END;
$$
DELIMITER ;

answered Apr 19, 2017 at 10:54

Umair MalhiUmair Malhi

5454 silver badges16 bronze badges

Источник

If you’ve been using WordPress for a while, you may have decided to get into more advanced database management. This often involves using the MySQL command line, which can, in turn, lead to confusing problems such as MySQL 1064 errors.

Fortunately, while resolving this error can be confusing at first due to its many potential causes, its solutions tend to be relatively simple. Once you determine the reason behind the database error you’re seeing, you should be able to fix it fairly quickly.

In this post, we’ll cover the various possible causes of the MySQL 1064 error. Then we’ll share solutions for each common situation, to help you get your database and your site back up and running.

Let’s get started!

Why the MySQL 1064 Error Occurs

The MySQL 1064 error is a syntax error. This means the reason there’s a problem is because MySQL doesn’t understand what you’re asking it to do. However, there are many different situations that can lead to this type of miscommunication between you and your database.

The simplest cause is that you’ve made a mistake while typing in a command and MySQL can’t understand your request. Alternatively, you may be attempting to use outdated or even obsolete commands that can’t be read.

In other cases, you may have attempted to include a ‘reserved word’ in one of your commands. Reserved words are terms that can only be used in specific contexts in MySQL. If you attempt to use them in other ways, you’ll be faced with an error.

It’s also possible that there is some data missing from your database. When you make a request via MySQL which references data that isn’t where it’s supposed to be, you’ll also see the 1064 error. Finally, transferring your WordPress database to another server can also lead to the same issue.

As you can see, there are many potential causes for this problem, which can make it tricky to resolve. Unless you’re in the process of moving your database or taking some other action that points to a specific cause, you’ll likely need to try a few different solutions before you land on the right one. Fortunately, none of them are too difficult to execute, as we’ll see next.

Oh no, you’re getting the MySQL 1064 Error…😭 Don’t despair! Here are 5 proven solutions to get it fixed immediately 🙏Click to Tweet

How to Fix the MySQL 1064 Error (5 Methods)

If you already have an idea of what’s causing your MySQL 1064 error, you can simply skip down to the resolution for your specific situation. However, if you’re not sure why the error has occurred, the simplest strategy is to try the easiest solution first.

In that case, we’d suggest testing out the five most likely fixes in the following order.

1. Correct Mistyped Commands

The good thing about MySQL typos is that they’re the simplest explanation for syntax issues such as the 1064 error. Unfortunately, they can also be the most tedious to correct. Generally speaking, your best option is to manually proofread your code and look for any mistakes you may have made.

We suggest using the MySQL Manual as a reference while you do so, double-checking anything you’re not sure about. As you might imagine, this can get pretty time-consuming, especially if you’ve been working in the MySQL command line for a while or if you’re new to this task.

An alternative to manually checking your work is to employ a tool such as EverSQL:

EverSQL syntax checker

With this solution, you can simply input your MySQL to check for errors automatically. However, keep in mind that these platforms aren’t always perfect and you may still want to validate the results yourself.

2. Replace Obsolete Commands

As platforms grow and change, some commands that were useful in the past are replaced by more efficient ones. MySQL is no exception. If you’re working on your database following a recent update or have referenced an outdated source during your work, it’s possible that one or more of your commands are no longer valid.

You can check to see whether this is the case using the MySQL Reference Manual. You’ll find mentions of commands that have been made obsolete by each MySQL version in the relevant sections:

Manually removing obsolete commands

Once you’ve determined which command is likely causing the problem, you can simply use the ‘find and replace’ function to remove the obsolete command and add in the new version. For example, if you were using storage_engine and find that it no longer works, you could simply replace all instances with the new default_storage_engine command.

3. Designate Reserved Words

In MySQL, using a reserved word out of context will result in a syntax error, as it will be interpreted as incorrect. However, you can still use reserved words however you please by containing them within backticks, like this: `select`

Each version of MySQL has its own reserved words, which you can read up on in the MySQL Reference Manual. A quick find and replace should enable you to resolve this issue if you think it may be causing your 1064 error.

4. Add Missing Data

If your latest MySQL query attempts to reference information in a database and can’t find it, you’re obviously going to run into problems. In the event that none of the preceding solutions resolves your MySQL 1064 error, it may be time to go looking for missing data.

Unfortunately, this is another solution that can be quite tedious and has to be done by hand. The best thing you can do in this situation is to work backward, starting with your most recent query. Check each database it references, and make sure all the correct information is present. Then move on to the next most recent query, until you come to the one that’s missing some data.

5. Use Compatibility Mode to Transfer WordPress Databases

This final 1064 error solution isn’t as straightforward as the others on our list. However, if you’re migrating your WordPress site to a new host or otherwise moving it to a different server, you’ll need to take extra steps to avoid causing problems with your database.

The simplest solution is to use a migration plugin that includes a compatibility mode, such as WP Migrate DB:

WP Migrate DB WordPress plugin

This will enable an auto-detection feature that will make sure your latest site backup and database are compatible with multiple versions of MySQL. You can access the compatibility mode setting by navigating to Tools > Migrate DB > Advanced Options:

WP Migrate DB settings

Check the box next to Compatible with older versions of MySQL before starting your site migration. This way, you should be able to avoid any issues during the process.

Summary

Database errors can throw a wrench in your plans, and may even compromise your website’s stability. Knowing how to resolve issues such as the MySQL 1064 error can help you react quickly, and minimize downtime on your site.

There are five methods you can try to fix the MySQL 1064 error when you encounter it, depending on its most likely cause:

Correct mistyped commands.
Replace obsolete commands.
Designate reserved words.
Add missing data.
Transfer WordPress databases in compatibility mode.

Get all your applications, databases and WordPress sites online and under one roof. Our feature-packed, high-performance cloud platform includes:

Easy setup and management in the MyKinsta dashboard
24/7 expert support
The best Google Cloud Platform hardware and network, powered by Kubernetes for maximum scalability
An enterprise-level Cloudflare integration for speed and security
Global audience reach with up to 35 data centers and 275 PoPs worldwide

Test it yourself with $20 off your first month of Application Hosting or Database Hosting. Explore our plans or talk to sales to find your best fit.

Источник

Let’s get started!

Why the MySQL 1064 Error Occurs

Oh no, you’re getting the MySQL 1064 Error…😭 Don’t despair! Here are 5 proven solutions to get it fixed immediately 🙏Click to Tweet

How to Fix the MySQL 1064 Error (5 Methods)

In that case, we’d suggest testing out the five most likely fixes in the following order.

1. Correct Mistyped Commands

An alternative to manually checking your work is to employ a tool such as EverSQL:

EverSQL syntax checker

2. Replace Obsolete Commands

You can check to see whether this is the case using the MySQL Reference Manual. You’ll find mentions of commands that have been made obsolete by each MySQL version in the relevant sections:

Manually removing obsolete commands

3. Designate Reserved Words

4. Add Missing Data

5. Use Compatibility Mode to Transfer WordPress Databases

The simplest solution is to use a migration plugin that includes a compatibility mode, such as WP Migrate DB:

WP Migrate DB WordPress plugin

WP Migrate DB settings

Check the box next to Compatible with older versions of MySQL before starting your site migration. This way, you should be able to avoid any issues during the process.

Summary

There are five methods you can try to fix the MySQL 1064 error when you encounter it, depending on its most likely cause:

Correct mistyped commands.
Replace obsolete commands.
Designate reserved words.
Add missing data.
Transfer WordPress databases in compatibility mode.

Get all your applications, databases and WordPress sites online and under one roof. Our feature-packed, high-performance cloud platform includes:

Easy setup and management in the MyKinsta dashboard
24/7 expert support
The best Google Cloud Platform hardware and network, powered by Kubernetes for maximum scalability
An enterprise-level Cloudflare integration for speed and security
Global audience reach with up to 35 data centers and 275 PoPs worldwide

Test it yourself with $20 off your first month of Application Hosting or Database Hosting. Explore our plans or talk to sales to find your best fit.

Источник

So, you’re creating a custom SQL query to perform a task in the database. After putting the code together and running it in PHPmyAdmin it responds with a 1064 error. It may look similar to this:

The 1064 error displays any time you have an issue with your SQL syntax, and is often due to using reserved words, missing data in the database, or mistyped/obsolete commands. So follow along and learn more about what the 1064 error is, some likely causes, and general troubleshooting steps.

Note: Since syntax errors can be hard to locate in long queries, the following online tools can often save time by checking your code and locating issues:

PiliApp MySQL Syntax Check
EverSQL SQL Query Syntax Check & Validator

Causes for the 1064 error

Reserved Words
Missing Data
Mistyped Commands
Obsolete Commands

This may seem cryptic since it is a general error pointing to a syntax issue in the SQL Query statement. Since the 1064 error can have multiple causes, we will go over the most common things that will result in this error and show you how to fix them. Follow along so you can get your SQL queries updated and running successfully.

Using Reserved Words

Every version of MySQL has its own list of reserved words. These are words that are used for specific purposes or to perform specific functions within the MySQL engine. If you attempt to use one of these reserved words, you will receive the 1064 error. For example, below is a short SQL query that uses a reserved word as a table name.

CREATE TABLE alter (first_day DATE, last_day DATE);

How to fix it:

Just because the word alter is reserved does not mean it cannot be used, it just has special requirements to use it as the MySQL engine is trying to call the functionality for the alter command. To fix the issue, you will want to surround the word with backticks, this is usually the button just to the left of the “1” button on the keyboard. The code block below shows how the code will need to look in order to run properly.

CREATE TABLE `alter` (first_day DATE, last_day DATE);

Missing Data

Sometimes data can be missing from the database. This causes issues when the data is required for a query to complete. For example, if a database is built requiring an ID number for every student, it is reasonable to assume a query will be built to pull a student record by that ID number. Such a query would look like this:

SELECT * from students WHERE studentID = $id

If the $id is never properly filled in the code, the query would look like this to the server:

SELECT * from students WHERE studentID =

Since there is nothing there, the MySQL engine gets confused and complains via a 1064 error.

How to fix it:

Hopefully, your application will have some sort of interface that will allow you to bring up the particular record and add the missing data. This is tricky because if the missing data is the unique identifier, it will likely need that information to bring it up, thus resulting in the same error. You can also go into the database (typically within phpMyAdmin) where you can select the particular row from the appropriate table and manually add the data.

Mistyping of Commands

One of the most common causes for the 1064 error is when a SQL statement uses a mistyped command. This is very easy to do and is easily missed when troubleshooting at first. Our example shows an UPDATE command that is accidentally misspelled.

UDPATE table1 SET id = 0;

How to fix it:

Be sure to check your commands prior to running them and ensure they are all spelled correctly.

Below is the syntax for the correct query statement.

UPDATE table1 SET id = 0;

Obsolete Commands

Some commands that were deprecated (slated for removal but still allowed for a period of time) eventually go obsolete. This means that the command is no longer valid in the SQL statement. One of the more common commands is the ‘TYPE‘ command. This has been deprecated since MySQL 4.1 but was finally removed as of version 5.1, where it now gives a syntax error. The ‘TYPE‘ command has been replaced with the ‘ENGINE‘ command. Below is an example of the old version:

CREATE TABLE t (i INT) TYPE = INNODB;

This should be replaced with the new command as below:

CREATE TABLE t (i INT) ENGINE = INNODB;

For developers or sysadmins experienced with the command line, get High-Availability and Root Access for your application, service, and websites with Cloud VPS Hosting.

Error 1064 Summary

As you can see there is more than one cause for the 1064 error within MySQL code. Now, you know how to correct the issues with your SQL Syntax, so your query can run successfully. This list will be updated as more specific instances are reported.

Источник

Дата: 25.11.2013

Автор: Василий Лукьянчиков , vl (at) sqlinfo (dot) ru

Статья ориентирована на новичков. В ней объясняется, что означает ошибка сервера MySQL №1064, рассматриваются типичные ситуации и причины возникновения этой ошибки, а также даются рекомендации по исправлению.

Рассмотрим простейший пример.

SELECT mid, time, title, artist, download, view_count, rating, vote_num FROM dle_mservice WHERE category = ‘1’ AND approve = ‘1’ ORDER BY time DESC LIMIT -10,10;
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 ‘-10,10’ at line 1

Сервер MySQL сообщает, что в первой строке нашего SQL запроса имеется синтаксическая ошибка, и в одинарных кавычках цитирует часть запроса с того места где начинается ошибка. Это очень полезное свойство, так как позволяет сразу определить место, которое сервер счел ошибочным. В данном случае это ‘-10,10’, ошибка возникает из-за того, что параметр LIMIT не может быть отрицательным числом.

Однако, бывает и так, что цитируемый кусок запроса не содержит синтаксической ошибки. Это означает, что данная часть запроса находится не на своем месте из-за чего весь запрос становится синтаксически неверным. Например, отсутствует разделитель между двумя запросами, пропущен кусок запроса, невидимый символ в дампе и т.д. Неудобством таких ситуаций является то, что сообщение об ошибке не содержит исходный запрос.
Действия по исправлению зависят от контекста возникновения ошибки. Таковых всего 3:

1. Запрос в редакторе.

Самый простейший случай — вы пишите свой запрос в редакторе. Если причина не опечатка, то:

Смотреть в документации синтаксис команды для вашей версии сервера MySQL.

Обратите внимание: речь идет о версии сервера MySQL, а не клиента (phpmyadmin, workbench и т.д.). Версию сервера можно узнать выполнив команду select version();
В MySQL допускается использование ключевых слов в качестве имен столбцов/таблиц, но при этом их необходимо заключать в обратные кавычки (там где буква ё на клавиатуре).
Пример:

select order from test;
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 ‘order from test’ at line 1
MariaDB [test]> select `order` from test;
+——-+
| order |
+——-+
| NULL |
+——-+
По умолчанию ; разделяет команды. Если же нужно выполнить набор из нескольких инструкций как одну команду (например, при создании процедур, фунуций, триггеров), то в зависимости от используемого клиента может потребоваться переопределить разделитель с помощью DELIMITER, иначе интерпретация команды остановится на первой ; и будет ошибка синтаксиса. Пример:
delimiter //
create procedure test()
begin
set @a=1;
select @a;
end//

Обратите внимание: DELIMITER это команда консольного клиента mysql, необходимость его использования зависит от того как вы передаете команду серверу. Например,:
- mysql_query() выполняет содержимое как одну команду, добавление delimiter приведет к error 1064 с цитатой, начинающейся со слова delimiter
- phpmyadmin удаляет слово delimiter из-за чего возникает error 1064 с цитатой, начинающейся с переопределенного разделителя
- в MysqlQueryBrowser напротив необходимо использовать delimiter.

2. Перенос базы на другой сервер.

У вас есть дамп (т.е. файл с расширением .sql) и при попытке его импортировать вы получаете ошибку 1064. Причины:

В различных версиях набор ключевых слов и синтаксис может немного отличаться. Наиболее распространенный случай: команда create table, в которой ключевое слово type было заменено на engine. Например, если вы получаете ошибку:

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 ‘TYPE=MyISAM CHARACTER SET `utf8`’ at line 29

Это означает, что вы переносите базу в пятую версию сервера MySQL, в котором ключевое слово TYPE не поддерживается и его нужно заменить на ENGINE.

Редко бываю случаи, когда перенос идет на старый (~3.23) сервер, который кодировки не поддерживает. Тогда ошибка будет иметь вид:

#1064 — You have an error in your SQL syntax near ‘DEFAULT CHARACTER SET cp1251 COLLATE cp1251_general_ci’ at line 1

Такое может произойти, если вы переносите базу с хостинга на локальный комп, где стоит древняя версия MySQL. Лучшим решением в данном случае будет не править дамп, а обновить MySQL.
Часто проблемы вызваны тем, что дамп делается неродными средствами MySQL (например, phpmyadmin) из-за чего в нем могут быть BOM-маркер, собственный синтаксис комментариев, завершения команды и т.д. Кроме того при использовании того же phpmyadmin возможна ситуация при которой из-за ограничения апача на размер передаваемого файла команда будет обрезана, что приведет к ошибке 1064.
Например, если вы получаете ошибку:

#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 ‘ï»¿
CREATE TABLE `jos_banner` (
`bid` int(11) NOT NULL auto_increment,
`ci‘ at line 1

Значит ваш дамп содержит BOM-маркер. Это три байта в начале файла, помогающие программе определить что данный файл сохранен в кодировке UTF-8. Проблема в том, что MySQL пытается интерпретировать их как команду из-за чего возникает ошибка синтаксиса. Нужно открыть дамп в текстовом редакторе (например, Notepad++) и сохранить без BOM.

Для избежания подобных проблем при создании дампа и его импорте лучше пользоваться родными средствами MySQL, см http://sqlinfo.ru/forum/viewtopic.php?id=583

3. Некорректная работа сайта.

Если во время работы сайта появляются ошибки синтаксиса, то, как правило, причина в установке вами сомнительных модулей к вашей cms. Лучшее решение — отказаться от их использования. Еще лучше предварительно проверять их работу на резервной копии.

Пример. Движок dle 7.2, поставили модуль ,вроде бы все Ок, но:

MySQL Error!
————————
The Error returned was:
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 ‘AND approve=’1‘ AND date < ‘2008-10-04 04:34:25‘ LIMIT 5’ at line 1

Error Number:
1064
SELECT id, title, date, category, alt_name, flag FROM dle_post WHERE MATCH (title, short_story, full_story, xfields, title) AGAINST (‘Приобретение и оплата скрипта ‘) AND id != AND approve=‘1’ AND date < ‘2008-10-04 04:34:25’ LIMIT 5

В данном примере мы видим, что причина ошибки в отсутствии значения после «id != «

Обратите внимание: из процитированного сервером MySQL куска запроса причина ошибки не ясна. Если ваша CMS не показывает весь запрос целиком, то нужно в скриптах найти место где выполняется данный запрос и вывести его на экран командой echo.

Кусок кода, который отвечает за данный запрос это

$db->query («SELECT id, title, date, category, alt_name, flag FROM « . PREFIX . «_post WHERE MATCH (title, short_story, full_story, xfields, title) AGAINST (‘$body’) AND id != «.$row[‘id’].» AND approve=’1′».$where_date.» LIMIT «.$config[‘related_number’]);

Далее можно искать откуда взялась переменная $row и почему в ней нет элемента ‘id’ и вносить исправления, но лучше отказаться от использования такого модуля (неизвестно сколько сюрпризов он еще принесет).

P.S. Если после прочтения статьи ваш вопрос с MySQL Error 1064 остался нерешенным, то задавайте его на форуме SQLinfo

Дата публикации: 25.11.2013

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

Источник

Read time 5 minutes

At first, the syntax errors seem very tedious and cryptic while working with SQL database programs. But, at a closer glance, one can easily understand the errors as they are descriptive enough about the problem that finding possible solutions becomes a step easier. Every error code has a unique 4 digits number that determines the type of error. One such SQL error code is Error: 1064 which occurs due to wrongly typed syntax of SQL queries. Let’s dig deep to know more about this error and how to fix it.

The error message with error code 1064 occurs due to the incorrect syntax of MySQL queries. In simple words, MySQL does not understand the commands that you have written. The commands are mistyped or misspelled within the MySQL environment which the database does not recognize. Say for example UPDATE is typed as UPADTE. Also, don’t get confused between syntax error and grammar error, because grammar rules are valid for a syntax error. The parser disagrees to understand the command and fails to perform a task.

Reasons for MySQL Syntax Error: 1064

The possible reasons due to which MySQL faces syntax error – 1064 are mentioned here:

It can occur due to mistyping the spelling of command.
The error can take place due to the use of outdated or depreciated commands.
It may happen when the specific data required by the query goes missing.
Due to wrong reserved words typed as they vary from version to version in MySQL.
This occurs due to a mistake in the spelling of the command resulting in MySQL not being able to understand it.
The error can take place due to the use of outdated or obsolete commands which are no longer in function.
It may happen when some data goes missing in the written database.
Due to wrong reserved words typed as they vary from version to version in MySQL. Reserve words are used for specific context only.

Instant Solution

Avail the fastest solution Kernel for MySQL Database recovery to fix SQL Syntax Error 1064. This software can quickly resolve problems related to MySQL Database.

How to Resolve Syntax Error: 1064?

When any MySQL error occurs, it indicates the problem along with a description and the way to fix it. Hence, for different syntax errors, it shows different fix solutions. Some of them are mentioned here, follow them according to the syntax error that is troubling you:

Fix 1: Mistyped Commands

The foremost reason due to which 1064 error occurs when you type incorrect spelling of a command or typos.

Example: UDPATE table emp set id = 0;
The UPDATE command is mistyped.

Solution to Fix

To fix the spelling errors mistyped commands and typos you must recheck before executing them. In case, you are unable to recall the correct syntax; we advise you to refer MySQL Manual and search for the syntax for the version you’re using. The error will get resolved if you replace all the typos and mistyped commands with the correct syntax.

You can also try IDEs and MySQL tools that help you with MySQL syntax errors by highlighting or pop-up alerts when you execute the query. If the IDE that you installed is lacking the feature of detecting syntax errors, look for a plugin that is designed for this purpose to debug the issue.

Fix 2: Reserved Words

Reserved words vary from one MySQL version to another as every version has its list of keywords that are reserved. The reserved words are for performing a specific task and are used for different purposes in the MySQL database engine. The error 1064 might pop up in cases when you are not using the right keyword meant for serving the specific function, or the version of MySQL is not meeting the exact requirements for using the particular keyword.

For example, Create Table alter (name, id);

Here, alter is a reserved word, but it cannot be used as it needs some special requirements. Let’s know how to use a reserved keyword in a query.

Solution to Fix

To use alter in MySQL query as you need to fulfill the unique requirements to call the functionality of the alter command, you cannot use it as mentioned above. You need to enclose the alter word with backticks (`), present on your keyboard just above the Tab button.`

For example: Create Table `alter` (name, id);

Fix 3: Missing Data

At times, the relevant data goes missing from the database which is required for the execution of a query. Hence, leading to 1064 error when the data is not found in the database.

For example: Select * from students where studentID = $id

Suppose if the $id not correctly filled, the above query for the server is like this:

Select * from students where studentID =
That is the reason the server pops up error 1064 because it gets confused.
Solution to Fix

You can enter the missing data using the dashboard interface of the application, which is usually done through phpMyAdmin or MySQL Workbench. The applications allow you to bring up the record and add the missing data manually to an appropriate row of the table.

Recommended: Automated Solution to Fix MySQL Database Errors

At times, the error 1064 becomes a bit tricky to resolve as it might occur due to the corruption of database files, i.e., MyIASM, .cnf, .ddl, .arm, etc. If that is the case, then you must use the professional automated solution to recover and restore database files of any MySQL server version. The best-recommended solution is the Kernel for MySQL Database recovery. The solution is highly efficient and works immediately to resolve problems caused by MySQL database files.

Concluding Words

The error 1064 seems simple to remove if you are aware of the exact cause behind the error. The manual solution may not work correctly if you do not use the correct steps to eliminate the error. You should use Kernel SQL Database Recovery software to handle each kind of error, whether physical or logical. The software will recover the complete databases with their tables, relationships, and dependencies.

Источник

Introduction to MySQL Error 1064

MySQL Error 1064 sometimes seems to be very difficult to understand. But, If we observe and have the knowledge about the type of errors and their causes, it will be easy to determine where the query statement is going wrong and how this error can be removed. Mysql error is represented with the help of error codes which are 4-digit numbers that range from 1000 to 2051. These error codes specify what type of error has occurred. In this article, we will learn about the most common error code that arises that is 1064, and the possible causes and solutions to it.

What is Error 1064 in MySQL?

Whenever an error with error code 1064 arises after the execution of the query statement in MySQL, the main reason is that the MySQL parser is not able to parse the query due to some fault in the syntax of the query. The syntax of the query must be according to the Structured Query Language and must be bound with the rules of Mysql. The syntax error is similar to the grammar mistakes that occur in English or any other linguistic language where the sentences must be correct according to the rules of the grammar that are defined for that language. In MySQL, syntax errors fail to parse and consequently fail in the execution of the query statement.

Causes and solution to MySQL 1064 Error

There can be many causes that might be responsible for the 1064 error to occur. We need to first read the error message that is thrown along with error code1064. This message provides useful information about where the error is occurring and why. Studying that part of the query, we can conclude where the query must have gone wrong provided if we are well aware of the SQL syntaxes.

The error message mostly uses the keywords like “at the line” or “near” to specify where exactly the MySQL is not able to parse the query. Manier times, the error message also suggests the possible solution to the error that has occurred. Like sometimes, it may say that we need to check our MySQL syntax with the manual for the version of Mysql we are using to see the correct syntax. We will discuss some of the possible causes that lead to a 1064 error in MySQL. They are as listed below

Commands may be typed incorrectly

Sometimes, the mistake may be done while typing the query like for example we may type SELECT instead of SELECT. Such types of errors arising due to typo mistakes can be prevented if we check our queries for any spelling mistakes before executing the queries. Also, there are many IDE’s and client-side tools for MySQL that provide the facility to check on the spellings and also parse the queries even before running them when we are typing the query on their editor pad. If the IDE you are using does not provide this type of checking, you can go searching with the settings or any other plugins that can be used to add this facility. Also, many online syntax checkers are available that will help to check the query syntax.

Deprecated commands may be used

It may happen sometimes that you must be using the commands that are absolute in your version or have been deprecated. Many commands are removed from the Mysql from time to time. We need to take a check on these commands. For example, the command for specifying the storage engine of the table was TYPE = before version 4.1 in MySQL. But in MySQL version 4.1 and above the TYPE command was declared as deprecated and in version5.1 it was completely removed and it was compulsory to use ENGINE = command instead of TYPE command in the Mysql versions 5.1 and higher.

Data required in the query may be missing

There can be a situation when you are adding the parameter values and certain values to the restrictions in the query during runtime and building the query statement dynamically. In such situations, it is necessary to make sure that the default value is given to the variables that are assigned in the query as sometimes these variables may not contain the expected value and remain blank which leads to incorrect construction of SQL query. For example, suppose in PHP language, you use the query like “SELECT uname, password FROM users WHERE uid=”. $SESSION[‘u_id’]. When the session variable u_id does not have any value and we have not initialized this variable to some default value then the query that will be built up, in the end, will be as follows – “SELECT uname,upassword FROM users WHERE uid=” that is completely incorrect as the data required for the query is missing. In our case, the value of uid to be mentioned in the WHERE clause for restriction is missing.

The reserved words may not be supported in MySQL version you are using

Sometimes the reserved words that we are using may be incorrect or not supported by the current version of the MYSQL you are using. Like sometimes we may forget to use the quotation mark or backticks in the keywords that are net or we may use camel case instead of keyword containing _ in it. All these mistakes in keywords spellings and deprecated or absolute keyword usage give errors with a 1064 error code.

WordPress data may be transferred in an incompatible mode

When we transfer the data and export it using WordPress database to some other server then it may lead to MYSQL error with error code 1064 if the database version and mode of compatibility that is being used are not the same as the current version of your database. In this situation, changing the database version to the current version, selecting the mode of compatibility while taking out the backup, and choosing the auto-detect file option of character set when restoring the database can help to solve this error.

Conclusion

Execution of Mysql queries might lead to errors if there is any mistake in the query due to some other cause and can be corrected by applying the appropriate solution. Mysql error code 1064 is raised when there is an error in the syntax or some functionality being used is deprecated or absolute. We can determine and correct it if we study the message carefully, use good IDE for the detection of typos and parse errors, and review the query for mistakes in the query before execution.

Why MySQL Error code 1064 SQL State 42000 occurs?

The MySQL Error code mainly occurs due to the SQL Syntax error. It happens when MySQL is unable to validate the commands.

The Syntax Error occurs due to many factors like mistyping the commands, deprecated or missing data from the database.

In some cases, the error occurs when the JDBC driver initializes the connection.

How we fix the MySQL Error code 1064?

Recently, one of our customers approached us saying that he is getting MySQL Error code 1064 SQL State 42000. On checking, we found an error in the SQL syntax.

Now, let’s see the main causes for this Error 1064 SQL State 42000 and how our Support Engineers fix them.

1. Using Reserved Words

The reserved words perform some specific functions within the MySQL engine.

Sometimes we receive the error while using the reserved words, The error occurs when the MySQL is not meeting the exact requirements for using the particular keyword.

Create Table alter (first name, last name);

The alter is a reserved word. To fix the error 1064 with the reserved word we specify the alter word within backticks.

Create Table 'alter' (first name, last name);

2. Outdated JDBC driver

When the JDBC driver initializes the connection, it sends several commands to the MySQL server. At that time we may receive the MySQL Error code SQL State 42000.

The problem is that the commands were deprecated for some time which results in the error.

We fix the error by upgrading the JDBC MySQL driver to the latest version.

3. Mistyping and Missing of Data

The 1064 error occurs when the data is not found in the database or mistyping the commands.

In case, if the data is missing from the database, we manually add the data to the database. Also, we make sure that all the commands are spelled correctly.

[Need any assistance with SQL State 42000 Error codes? – We’ll help you]

Conclusion

In short, today we discussed in detail on MySQL Error code 1064 and saw how our Support Engineers find the fix for this error.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

MySQL is an open sourced rational database system that is very useful but in many cases, it is found while working with the database programs, the user starts getting database errors and also regular PHP errors. This is really the very annoying problem but no need to panic as here in this guide we are going to the complete information about the error code 1064 MySQL, its causes, and general troubleshooting tips to fix the problem.

Are you ready! Let’s read…

The MySQL error 1064 is a syntax error, which means MySQL is unable to recognize the command issued by a user, as the command is not valid within the Structured Query Language or SQL. This error might seem like cryptic at first sight, but closely analyzing it, user notice that they are informative and provide sufficient detail about the problem. The syntax error in programming results in the incapability of the parser to understand the command and fail to do anything with it.

1064

The above shown error might occur due to several unexpected reasons. But, before knowing the solutions to solve MySQL error 1064 (42000), it’s very important to know the exact reason behind getting the error message.

In the next section of this article, you are going to learn the possible causes behind issue. So, let’s see…

What Are The Major Factors For MySQL Syntax Error 1064?

Here, I have listed the common reasons that can lead to error code 1064 MySQL:

The error may arise when you use the outdated/depreciated commands.
Because of mistyping the spelling command.
Due to the incorrect reserved words had typed as they vary from version to version in MySQL.
It might occur when a specific data needed by query goes missing.

How To Resolve MySQL Faces Syntax Error: 1064?

By using the below mentioned approaches, you will be easily able to fix MySQL error 1064 in a trouble-free way. So, once try them.

Method #1: Designate The Reserved Words
Method #2: Correct the Mistyped Commands
Method #3: Missing Data
Method #4: Use A Compatibility Mode To Transfer The WordPress Databases
Method #5: Outdated Command

Method #1: Designate The Reserved Words

Reserved words are mainly used to perform the specific task in MySQL and for different purposes. The error code 1064 can occur when you’re not using a correct keyword meant for serving a specific task. Also, due to version of MySQL isn’t meeting an exact wants for using a particular keyword.

So, to try alter in the MySQL query as you require to fulfill unique requirements to call the functionality of alter command, you can’t use it. You require to enclose the alter word with the back ticks (`), present on the keyboard just above the Tab Key. `

Example: Create Table alter (name, id);

Method #2: Correct the Mistyped Commands

The major cause behind the 1064 error code is typing the wrong spelling of the command/typos.

For Example: UDPATE the table emp set id = 0;
Here an UPDATE command is mistyped.

Therefore, you need to solve the spelling mistyped commands & typos, remember you must re-check them executing. In case, if you’re not able to recall a right syntax then I would like to suggest you to refer MySQL Manual & search for a syntax version you are using.

The issue will get solved if you change all the mistyped commands and typos with correct syntax.

Method #3: Missing Data

MySQL faces syntax error: 1064 may also take place when the needed stuff isn’t available while performing a query. Therefore, to repair it you can take use the phpMyAdmin or MySQL Workbench. Simply, you need to enter a missing data by using dashboard interface of the app. This will help you out to manually add a disappeared data.

Method #4: Use A Compatibility Mode To Transfer The WordPress Databases

In order to fix MySQL error 1064 create database, the easiest approach is to use the migration plugin which includes the compatibility mode, like WP Migrate DB.

Well, this allows an auto-detection feature that will ensure your newest site backup & database are friendly with numerous versions of the MySQL. Hence, you can access a compatibility mode setting by trying these steps:

First of all, go to the Tools and then Migrate DB
Next, you need to click on Advanced Options
Now, check a box next to the Compatible with older versions of MySQL before beginning your site migration.

After completion of the above mentioned steps, you will be able to prevent any problem throughout the process.

Method #5: Outdated Command

A few commands are totally removed in the different MySQL versions or they’re deprecated. Therefore, ensure whether you’re using the right one or not. If you use the ‘TYPE’ command in MySQL version 5.1 then you’ll get error code1064. So, to repair this error fix it, just check if you’ve the earlier backup of MySQL database or not. In case, if it’s not then you need to search & replace the “TYPE=InnoDB” with “ENGINE=InnoDB”.

Automatic Solution To Troubleshoot MySQL Error 1064

If all the above mentioned methods fail to repair MySQL error 1064 create table, then you can use of the best professional MySQL File Repair tool. This program is one of the excellent tool that is highly effective in repairing the corrupted, damaged, MySQL database and various errors in the MySQL.

It works successfully by performing the systematic repair of corrupt MySQL tables with its powerful scanning techniques. It consists of a highly interactive user interface which helps the user to fix any type of error easily.

So, you can download and install this feature-loaded tool from the below given buttons.

This file repair software doesn’t modify the original data stored in the table and in fact, does not overwrite or delete the original database It helps to recognize all problems which come with database and does not matter whatever error appears. Apart from that, it not only repair the corrupted MySQL database but also recovers the deleted MySQL files easily.

STEPS TO USE MySQL FILE REPAIR TOOL:

Step 1: Download, install and launch the MySQL Database File Repair Tool.

Step 2: Now click on ‘Select’ button & select ‘Repair corrupt database which is stored at default location of MySQL’ and ‘Manual option to select your database from default location’ option for selecting your database from the default location and then click.

Step 3: In the left pane the preview of the database and its component in a tree like structure after completing scanning process.

Step 4: After preview, you can start to repair process by clicking ‘Repair’ button in file menu on the main user interface a dialog box will appear. Click Yes.

Step 5: After successful completion of the process, a message box is displayed as ‘repair process finished successfully’. Click ‘OK’ to close the message box.

Final Words

Well, last but not the least, error code 1064 MySQL is easy to repair, especially if you’re an experienced user. However, it’s quite complex also, so here I have recommended you to go for a manual ways when you’ve perfection in performing command.

Otherwise, you can go for the automatic solution that is MySQL File Repair software. It is safe as well as proven method to fix various MySQL errors and bugs in a hassle-free manner.

Thanks for reading my article…

Jacob Martin is a technology enthusiast having experience of more than 4 years with great interest in database administration. He is expertise in related subjects like SQL database, Access, Oracle & others. Jacob has Master of Science (M.S) degree from the University of Dallas. He loves to write and provide solutions to people on database repair. Apart from this, he also loves to visit different countries in free time.

Источник

TL;DR

Aaaagh!! What does #1064 mean?

What is this «syntax» of which you speak? Is it witchcraft?

What does that have to do with MySQL?

How do I fix it?

Read the message!

Examine the actual text of your command!

Obey orders!

A note of reservation

Why the MySQL 1064 Error Occurs

How to Fix the MySQL 1064 Error (5 Methods)

1. Correct Mistyped Commands

2. Replace Obsolete Commands

3. Designate Reserved Words

4. Add Missing Data

5. Use Compatibility Mode to Transfer WordPress Databases

Summary

Why the MySQL 1064 Error Occurs

How to Fix the MySQL 1064 Error (5 Methods)

1. Correct Mistyped Commands

2. Replace Obsolete Commands

3. Designate Reserved Words

4. Add Missing Data

5. Use Compatibility Mode to Transfer WordPress Databases

Summary

Causes for the 1064 error

Using Reserved Words

Missing Data

Mistyping of Commands

Obsolete Commands

Error 1064 Summary

1. Запрос в редакторе.

2. Перенос базы на другой сервер.

3. Некорректная работа сайта.

Reasons for MySQL Syntax Error: 1064

Concluding Words

Introduction to MySQL Error 1064

What is Error 1064 in MySQL?

Causes and solution to MySQL 1064 Error

Commands may be typed incorrectly

Deprecated commands may be used

Data required in the query may be missing

The reserved words may not be supported in MySQL version you are using

WordPress data may be transferred in an incompatible mode

Conclusion

Recommended Articles

Why MySQL Error code 1064 SQL State 42000 occurs?

How we fix the MySQL Error code 1064?

1. Using Reserved Words

2. Outdated JDBC driver

3. Mistyping and Missing of Data

Conclusion

PREVENT YOUR SERVER FROM CRASHING!

What Are The Major Factors For MySQL Syntax Error 1064?

How To Resolve MySQL Faces Syntax Error: 1064?

Method #1: Designate The Reserved Words

Method #2: Correct the Mistyped Commands

Method #3: Missing Data

Method #4: Use A Compatibility Mode To Transfer The WordPress Databases

Method #5: Outdated Command

Automatic Solution To Troubleshoot MySQL Error 1064

STEPS TO USE MySQL FILE REPAIR TOOL:

Final Words