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.
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.
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.
MySQL error messages may seem very cryptic at first, but upon closer analysis, you’ll notice that they are informative enough in that they provide sufficient detail in terms of what the problem is. Understanding how to interpret a MySQL error code will help in fixing any future problems of this nature. MySQL error codes have a 4-digit numeric value (from 1000 to 2051) that reflect a certain type of error that occurred.
What is MySQL Error 1064?
The MySQL error 1064 is a syntax error, meaning that the MySQL is unable to understand the command you are issuing, because your command is not a valid one within the Structured Query Language or SQL. Syntax errors are just like grammar errors in linguistics, but unlike in the English language, where violations of the rules of grammar may still be able to produce an understandable sentence even though it’s not a grammatically valid one, syntax errors in programming will result in the inability of the parser to understand the command and fail to do anything with it.
Possible causes and how to resolve MySQL 1064 Errors
When a 1064 error code appears, it will usually state the problem that caused it and the solution to fix it. Carefully reading the error message will reveal how your command may have violated the syntax rules in the MySQL. 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.
MySQL errors will not only indicate where the problem might have occurred, but also what to do to fix it. For example, it may recommend that you compare the grammatical rule violation against the manual that corresponds to your MySQL version for the right syntax to use.
Here are some of the possible causes of a MySQL 1064 Error:
Mistyping of commands
A 1064 Error can occur when you misspell a command (e.g. you write UDPATE instead of UPDATE). This can occur quite frequently since typos are so easy to miss. To prevent errors because of mistyped commands, make sure that you review your command for any typos before running it. If you have trouble with using the right syntax, refer to the manual of the MySQL version you’re using to search for the specific command(s) you’re having troubles with. Clearing up the typos or correcting the syntax will make the error disappear. Nowadays, a lot of IDEs and SQL tools come with SQL syntax highlighter or even parsers, which will alert you of a syntax error even before you run your query. If your IDE does not have this feature you might look around to see if there are any plugins which would do the job. There are a lot of online syntax checkers which can help you debug your queries.
Error caused by obsolete commands
Another possible reason for the MySQL 1064 error is the use of outdated commands. A number of commands and keywords have been deprecated, meaning that they are due for removal, but they are still allowed for a period of time before they turn obsolete. One example of a deprecated command that went obsolete is the ‘TYPE’ command, which was deprecated with the debut of MySQL 4.1 and was completely removed in version 5.1. Therefore, if you use the TYPE command in version 5.1, then it will return a 1064 syntax error. The ‘TYPE’ keyword was replaced with the ‘ENGINE’ keyword as of version 5.1. If you are switching hosting companies or you just happen to have an older backup of a MySQL database that you might want to import, a quick solution is to just search and replace “TYPE=InnoDB” with “ENGINE=InnoDB”.
Missing data
When data is missing in the database and that particular data is required by a query, the 1064 error code will be returned by the MySQL engine. The dashboard interface of your application will allow you to enter the missing data, or you can go into the database usually via phpMyAdmin or MySQL Workbench and manually add the data in the right table row.
If your application runs a query at every page load, for example, and you are not following the recommendations and you’re still constructing queries basically by string concatenation, you might have the following query in PHP for example: “SELECT * FROM customers WHERE customer_id = “ . $_SESSION[‘user_id’]. This assumes that the user is logged in and the $_SESSION[‘user_id’] has a value, however, sessions can expire and in that case the query which will run would become “SELECT * FROM customers WHERE customer_id = ” and MySQL’s response to this query will be the 1064 error code. There can be quite a lot of places where people do not use default values, another example would be the sorting parameters, which are usually column names but sometimes the application might receive empty values, which in turn still receive a 1064 error code.
Reserved words
Reserved words are words that are different from MySQL version to MySQL version and serve specific purposes or are used to perform specific functions in the MySQL engine. You might receive a 1064 error when using a reserved word, either because you’re using a reserved word that is not specific for the MySQL version you have or either because you fail to meet the specific requirements to use it (e.g. using it with quotation marks or backticks). A full list of the reserved words specific for each MySQL version and their usage requirements are available at MySQL.com.
WordPress MySQL database transfer to another server
The 1064 error can also be caused by a WordPress database export to another server. Choosing the compatibility mode and changing the database version to the current version you’re using can help resolve the error. Remember to select the compatibility mode under the advanced tab when performing a backup and click the auto-detect file character set when restoring the MySQL database.
Conclusion
MySQL errors are pretty straightforward, signaling both exactly where the error was encountered by the parser and making suggestions for fixing them. Reading the error message and following the recommendation set forth in the message will resolve the error.
Last updated on: November 29, 2016