Ошибка 1064 phpmyadmin как исправить

Have you ever encountered an SQL error 1064 while trying to execute a query? It can be frustrating, but don’t worry – you’re not alone. This error message typically marks a syntax error in your MySQL query.

Fortunately, there are only a handful of problems triggering that error. You can efficiently resolve them with a bit of persistence and proper guidance. In this article, we’ll explain what SQL Error 1064 is, what causes it, and how to resolve it. Plus, you’ll learn three tips to avoid it in the future.

You will likely see the SQL error code 1064 in a database administration tool like phpMyAdmin or MySQL Workbench. SiteGround users have phpMyAdmin at their disposal in Site Tools > Site > MySQL > PHPMYADMIN > ACCESS PHPMYADMIN.

Below, you can see a screenshot of phpMyAdmin producing the error.

There are several variants of the error message:

• error 1064 (42000) – you have an error in your SQL syntax
• #1064 – you have an error in your SQL syntax

This error message looks daunting, especially if you’re new to SQL. However, it’s essential to remember that syntax errors are common, and with some practice, you can learn to avoid them.

Understanding SQL Syntax

SQL syntax is a crucial aspect of database management. You can create, modify, and retrieve data from databases using correctly structured SQL commands.

SQL syntax is a set of rules that determine the structure of commands used to interact with a database. SQL commands include the following elements:

• operators,
• clauses,
• expressions,
• keywords.

The SQL syntax is similar to many other programming languages. While SQL keywords are not case-sensitive, writing them in uppercase is standard practice. SQL statements are typically separated by semicolons.

Familiarizing yourself with the MySQL Reference Manual is essential for working with a MySQL database. You will get a better understanding of the syntax, which in turn will help you avoid syntax errors in SQL queries.

What Causes SQL Error 1064?

As the description suggests, the main reason for the SQL error 1064 is a syntax error in your command. However, other problems can trigger the error, as well. Below, you’ll find a list of the most common ones.

• Syntax errors – The most common cause of SQL Error 1064. Syntax errors occur when the SQL statement is miswritten or violates the established syntax rules.
• Obsolete commands – Using outdated and deprecated commands or commands no longer supported in the current version of SQL.
• Using reserved words out of context – Reserved words are words already used by the SQL language for specific purposes. Examples are SELECT, INSERT, UPDATE, ALTER, DELETE, etc. If you use reserved words for a purpose outside of their designated use in your SQL code, you will encounter SQL Error 1064.
• Incorrect data types – Using incorrect data types in the SQL statement. Inserting text into a numeric field is a typical example.
• Missing data – If you try to insert or update data in a database missing a required field, the result could be an SQL error 1064.

How to Fix SQL Error 1064

The SQL syntax error can originate from various issues. Here is an order you can follow to find the source of the problem and solve it.

• Step 1.Read the Error Message Carefully

  When encountering an SQL Error 1064, read the error message carefully. It will indicate which line or section of the SQL statement contains a mistake.

  Below is an example of phpMyAdmin producing the error and pointing out which part is problematic.

  

• Step 2.Check Your MySQL Query for Syntax Errors

  Once you’ve identified the part of the SQL statement causing the error, check the SQL code for syntax errors. Look for missing brackets, commas, or misspelled operators, clauses, expressions, or keywords.

  The example from the previous step shows that the problematic part of the SQL command is:

  ''WERE `fpj_options`.`option_id` = 1' at line 1

  Looking closely at the line, it becomes clear that the issue comes from the misspelled “WERE” clause. Its proper form is “WHERE” so the missing “H” triggered the error. Correcting the clause results in a successful execution of the SQL command.

  

• Step 3.Check If You Are Using Reserved Words

  If you are using MySQL and receive an error message with the code 1064, it may be because you used words that MySQL considers special. These are called “reserved words,” and examples are ALTER, TABLE, SELECT, etc.

  Reserved words have specific meanings in MySQL. You can’t use them as names for tables, columns, or other parts of a MySQL database without causing errors. Below is an example of the SQL syntax error caused by using the reserved word alter as a table name.

  

  To prevent this error, check for any reserved words used out of place in your queries. You can find a complete list of these words in the MySQL manual. If you use a reserved word, change it to something else.

  If you prefer to use the reserved word, surround it with backticks (`). These backticks will instruct MySQL to treat it as a name and not a reserved word.

  

• Step 4.Replace Obsolete Commands

  MySQL has undergone several revisions and changes over the years. Some of these changes have affected the SQL syntax rules. Many of the commands have been abandoned and replaced by new ones.

  You can check the MySQL server version you are using in phpMyAdmin. For more information, read this guide on how to check the MySQL version on the server hosting your account.

  If you’ve identified that you’re using obsolete commands or no longer supported ones, replace them with up-to-date commands.

  For editing outdated commands in files, you can save time by using find and replace tools in your File manager. They can replace all the commands causing the issue with the correct new versions in one go.

  The File Manager in Site Tools offers this functionality for SiteGround users. Open the file you wish to edit, and press the button Replace.

  

  In the Find field, type the obsolete command that needs to be updated. In Replace, write the updated command to replace the old one. The tool will rewrite all instances of the outdated command with the new one.

  IMPORTANT! The new SQL command may not work as expected. Before making changes to the file, create a website backup. It will allow you to restore your website quickly in case the changes affect it negatively.

• Step 5.Check If You Are Using the Correct Data Type

  SQL databases are designed to store various data types. Each has its own specific format and storage requirements. For example, a numeric value requires a different amount of storage space than a string value.

  If you are trying to insert data of one type into a column of another type, you will encounter SQL Error 1064. This error message is usually accompanied by “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…”.

  For instance, if you insert a text string into a column defined as a numeric value, you will get SQL Error 1064.

  

  To avoid this database error, ensure that the data types of the columns in your SQL code match the data types of the values you are inserting.

• Step 6.Add Missing Data

  Another common cause of SQL Error 1064 is missing data. For example, if you try to insert data into a table with mandatory fields but leave some fields empty, you will get an SQL Error 1064.

  To fix this error, ensure you have provided all the required data. You may have to add the missing data manually.

  Furthermore, the error may appear when importing your database from a corrupted backup file. Create a new backup of your original database and import it on the new server again.

  SiteGround users can take advantage of the tool Import Database Dump, designed for importing larger databases.

  

  If unsure about the mandatory fields, check the table definition or consult the database administrator.

• Step 7.Seek Assistance

  If you have tried all the above steps and still can’t fix SQL Error 1064, try finding help.

  You can contact the database administrator or consult an online forum or community dedicated to SQL programming.

  Many online resources are available for beginners and experienced programmers alike, and you can find answers to most SQL-related questions. You can find helpful information in the official MySQL forum, where users aid each other with more elaborate problems and share their experiences.

3 Tips to Avoid SQL Error 1064

You can prevent SQL Error 1064 by following good practices that reduce the chance of making a syntax error.

Use an SQL Validator to Detect a Syntax Error

An SQL validator is a tool that checks the syntax of your SQL code and identifies errors. Using it lets you catch and fix errors early before executing the code.

EverSQL is one of the most popular free SQL validator tools. Submit your SQL query and press the button Validate SQL Syntax. The checker will inspect the code and point out potential mistakes in the MySQL queries.

Use SQL Aliases

SQL aliases allow you to assign a temporary name to a table or column. Using aliases, you can simplify your SQL code and avoid syntax errors caused by typos or ambiguities. Note that the SQL alias remains active only for the duration of the SQL query.

The clause AS enables an alias for a table or column. To assign an alias for a column, use the following template.

SELECT column_name AS alias_name
FROM table_name;

For assigning an alias to a table, use the format below.

SELECT column_name(s)
FROM table_name AS alias_name;

Replace column_name and table_name with the existing names in your database and alias_name – with your chosen custom alias.

Break Down SQL Statements

Breaking down complex SQL statements into smaller, manageable parts can help you easily identify errors. By testing smaller parts of your SQL code, you can isolate and fix errors before they cause significant problems.

Conclusion

SQL Error 1064 is a common error encountered by SQL programmers. Syntax errors, obsolete commands, incorrect data types, missing data, or other mistakes in the SQL code usually cause this error.

By following the steps outlined in this guide and implementing the tips to prevent MySQL Error 1064, you can avoid it and ensure your SQL code runs smoothly. Remember to always check for syntax errors and seek assistance when needed.

The problem is that you’re trying to define a text field (varchar) as being unsigned, which is something that can only apply to a numerical field.

i.e.: «reminder_date varchar(8) unsigned NOT NULL default '0',» makes no sense.

However, as the field is called «reminder_date», I’m guessing you’re attempting to store a date, in which case you really want to use MySQLs DATE field type.

e.g.: «reminder_date DATE NOT NULL,«

Additionally, if you’re going to want to search on any of these fields, you should also add some indexes to speed up the searches. So, if you wanted to be able to search on the name and date, you could use:

CREATE TABLE reminder_events (
reminder_id bigint(20) unsigned NOT NULL auto_increment,
reminder_name varchar(255) NOT NULL DEFAULT '',
reminder_desc text,
reminder_date DATE NOT NULL,
PRIMARY KEY (reminder_id),
KEY reminder_id (reminder_id),
KEY reminder_name (reminder_name),
KEY reminder_date (reminder_date)
) TYPE=MyISAM;

Вы не вошли. Пожалуйста, войдите или зарегистрируйтесь.

Активные темы Темы без ответов

Сообщения 12

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.

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:

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:

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:

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:

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:

1. Correct mistyped commands.
2. Replace obsolete commands.
3. Designate reserved words.
4. Add missing data.
5. Transfer WordPress databases in compatibility mode.