Hello and welcome to another InMotion Hosting Video tutorial. My name is Arnel Custodio
and today we will be discussing the causes of MySQL 1064 syntax errors.
We will show you the 4 main causes of these errors, examples of the errors,
and resources that can be used to help resolve them.
The current release of MySQL is version 8.0.
If you go to the MySQL website then you will find the documentation for the current three generally available versions:
5.6, 5.7, and 8.0.
You can find documentation for the older version of MYSQL in the MySQL Documentation Archive.
You can find documentation as far back as version 3.23. The URL for the archive is shown below:
It is important to have documentation for MySQL because the commands are defined there.
The first common cause of syntax errors in MySQL code is the use of a reserved word.
A reserved word is simply a pre-defined command used by MySQL.
For example: CREATE TABLE alter (first_day DATE, last_day DATE);
Here you can see the reserved word “alter” has been used for the table name.
The easiest way to fix this syntax error is to simply replace the table name.
CREATE TABLE changes
(first_day DATE, last_day DATE);
In this case we’re simply using “changes” in place of the reserved word “alter”.
You can do a search of the MySQL command index in the documentation in order to avoid this problem.
Another common cause of the MySQL 1064 syntax error is missing data.
If you review your MySQL code and you can’t find a misspelled command, missed punctuation,
accidentally used reserved words, or obsolete commands, then the issue is most likely related to data.
If a query is properly written but the data required by the query is not in the table,
then you can get a 1064 error because it’s expecting something that isn’t there.
In other words, from the perspective of the MySQL query,
your syntax is wrong because you’re requesting data is that isn’t there.
Since this is a little difficult to explain, let’s look at a database in phpMyAdmin.
Here, there is a table called student.
If a query were written and called for data from ID, instead of IDNO (which you see here in the table),
then there will be no data passed and the data is misidentified.
This type of 1064 error will often happen when the query is asking for something and it simply spelled wrong or incorrectly identified.
The easiest way to fix this kind of issue is to make sure that the query is addressing relevant data in the database.
You can double-check this by using phpMyAdmin or other MySQL clients.
The most common cause of the 1064 error is the mistyping of the command or punctuation issues.
Basically, you simply have the command misspelled or you’re missing the correct punctuation.
For example the word “update” has a typo in the command below:
The error description will say that the error is “near” a location to help you find it.
However, in the instance of missing punctuation, you will need to understand where the proper punctuation is required.
If you can’t identify it, then it’s recommended to get another set of eyes - like from an experienced developer - to take a look.
Quite often, if you’re looking at a large mass of code, it’s best to get another person to look.
You might be surprised by how much they see that you won’t
If you can’t get help, then simply review the code in smaller pieces so that you can go through it very slowly
Step away from it if you have been looking at it for a while, then return and keep reviewing your code.
Once you have identified the issue, update the code
and re-run the SQL statement to make sure that the query doesn’t continue being a problem.
The final common cause of 1064 errors in MYSQL
is the use of OBSOLETE commands. As versions are updated,
it’s very common to see some commands retired from the reserved word list.
Commands in an older version may often be deprecated (scheduled for removal)
and eventually removed in a newer version. A good example of this is the command “TYPE” in MySQL 4.1.
The command is now obsolete and has been replaced by ENGINE
You can see that when TYPE is used in the command
it generates a 1064 error because it’s no longer used.
There are free online MYSQL syntax checking tools
that can help to identify issues with your code. Also, remember that the MySQL errors themselves
can often point out where to find the problem. You can often take an entire query
and dump it into an online checker and it will identify the problems for you.
To find an online MySQL checker, simply use your favorite search tool and search for “MySQL Syntax check”
The examples in this tutorial have shown errors using the phpMyAdmin MySQL client,
but MySQL code is often written in text editors.
If you are executing short lines of MySQL code then tools like phpMyADMIN can be very helpful.
Thanks for watching our tutorial on the common causes of MySQL 1064 syntax errors.
We have shown you some examples of the common causes and identified how they can be solved.
You have also been shown resources that can help you identify and resolve these syntax errors.
If you liked our video make sure to give us a thumbs below and subscribe to our channel! Thanks again for watching and have a great day!