MySQL Error 1064 Solution
This article is more than 2 years old
Errors occur because you have missed out on something while writing the code. MySQL Error 1064 is one of the most common errors. In order to solve the error, you have to first figure out why you are getting the error in the first place. The message along with the error is an indication of the steps you can take you to fix the problem. Some of the MYSQL Error 1064 are solvable but others can take some time up to fix the problem
What Does MySQL 1064 Mean
MySQL error codes have values from 1000 – 2051. You can view the complete list of MySQL error codes through this link. 1064 is associated with the error being syntax error. These are usually the simplest error to solve because they can be either a simple typo, overridden command, obsolete command, special words, database transfer error or they can also indicate incomplete data.
If you read the complete error code then you can figure out which of the above category the error actually belongs to. Careful interpretation can help correct the mistake. If you have already identified the type of error you have done then simply skip to that section.
Here are some of the reasons why MySQL Error 1064 happens:
Command Typo
There are loads of commands in the MySQL. With the help of modern code editors, you will be told by the tool itself to correct the spellings or make it according to the syntax. If however, for some reason you are using an older code editor then you will not have this advantage.
For example, you may have been writing this command: drop db [database name];
It may seem logical when you are typing in quickly but actually db is not identified as a database by the MySQL. You need to write the full command as it is specified by the MySQL command directory. The right command would have been drop database[database name];
This was a quick error to fix and the above command directory will help you fix some of the common typing mistakes beginner coders do.
Commands which do not exist
MySQL has seen a series of updates over the past years. There are certain commands which have become outdated. You might be running a more latest version of the MySQL and it is not able to identify the command. The inability of identification is merely because there is no such command stored in MySQL.
In this case, you need to find the new command defined in my MySQL command directory. You might be doing this mistake because you are a veteran coder or in a bad case following a YouTube MySQL tutorial which is outdated.
In any of the cases, you need to learn the more up to date command structure of MySQL. For example command like INNODB_SYS_TABLESTATS is replaced with INNODB_SYS_TABLESTATS, INNODB_SYS_TABLESPACES is changed to INNODB_TABLESPACES. Here are some more in this table.
It is actually worth spending time in knowing what has changed in the newer version of the MySQL version. It can take some time but you can surely avoid most of the MySQL errors in the future.
Database Migration
One thing to consider carefully, if you have a website or working for a client who had a website hosted on some cheap unknown shared hosting then your work may have increased by quite some folds already. The databases might be teeming with old obsolete commands.
If you are migrating to good hosting companies such as SiteGround who have the latest MySQL version installed then you will have to fix a lot of the old commands. The good part is that now you are aware of this error type and can fix the obsolete 1064 mistakes.
It might be a lot of work to fix this mistake. A good rule of thumb is to always check what version of MySQL is installed on the previous and in the new server. It can save you time and allow you to justify your increase quote. Most of the clients, who are non-technical mainly because you did not do the homework correctly initially.
Overridden Commands
It is very much possible that you may be working with a client who just gave you to fix what the older developer had built. Depending on many circumstances, the previous developers might have overridden the commands with his own made ones. In such a case, your rightly written commands may not get identified the SQL.
The solution to such a problem would be to either get in contact with the developer or if you can manage then get in deep to the definitions of the commands and see if there is any new command defined by the developer. Although, it is a very rare probability but this problem can actually happen.
If you are annoyed even when you have corrected the typing mistake but still you get the same error then there is a big possibility that you are stuck in the overridden command problem.
MySQL Version Reserve Words
There are quite a lot of MySQL versions floating around. Each version has it own set of reserve words defined to perform a specific task. MySQL 1064 error can also happen because you are using a word in the code that simply does not belong to the version of MySQL you have installed.
It can be an annoying problem to solve. You would have to get the version of the MySQL version installed. You can get the version installed by the execution of this command.
mysqld –version
The output will get you the version of the MySQL version installed and also the operating system over it.
After getting the version you are writing code in then you should search if the command where you have the erorr is compatible with the version of the MySQL you have installed. You can read more about the reserved words here.
Missed Data
Sometime you will have set the query where it takes data from a particular table and inserts it into another database or into a different column in the same database. After reading the command the MySQL engine locate the row and column entry where it has to pick up the value. That location might have no value at all there. You can correct the mistake my manually going to that table in the database in PHPMyAdmin and write the data there. Alternatively, the application dashboard if rightly coded can also help you put in the data through the application interface.
Indirect Indication of Underlying Wrong Coding
Often on the eCommerce websites some items may have been sold out but its value may not be updated timely. Shoppers on the live session may be trying to add multiple such items into the cart. Upon checking out the whole basket can break because the item was initially allowed to be placed but in reality it did not exist in the actual inventory.
MySQL 1064, in this case, will require you to actually do the proper coding of the e-commerce website. This is one of the examples, where 1064 can point to an underlying problem.
Conclusion
MySQL 1064 is not a difficult error to solve for all cases except for the part where it may identify underlying coding issue. Apart from that rest of the error types are pretty clear. With the above list you can identify the class of error you are encountering. A few more google searches after identification of the category of 1064 error you can fix the error quickly.
