SQL Error Codes: An Introduction

Like it or not, if you work with SQL you will encounter SQL error codes as part of your work. The cryptic messages are frustrating because they do not fully tell you what the problem is. This problem is compounded if you do not have much experience with SQL. Popular applications are incorporating database features, and you may find yourself wanting or needing to search a database with SQL. We will look at this situation with Oracle’s MySQL. We will see typical SQL error codes you may generate and some possible solutions. You can get an introduction to SQL with this beginners guide course.

Getting Started: SQL Error Codes You May Encounter

First, we attempt to start the mysql command line tool, and we see the following error:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql –user=root mysql

ERROR 2003 (HY000): Can’t connect to MySQL server on ‘localhost’ (10061)

The message tells you that you can’t connect to the server, but it doesn’t tell you why. It is looking for the server on this computer (‘localhost’), so network connectivity won’t be a problem. If you do need to troubleshoot your network connection, this course on computer networking can help. In our case we forgot to start the server, so that will be an easy fix.

Now we try to create a database to use for examples. We see this message:

mysql> CRETE DATABASE exampledb;

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 ‘CRETE

DATABASE exampledb’ at line 1

As with any computer language, SQL expects commands to follow a certain syntax. Get that wrong, and you will be seeing an error code. We get a very long message mentioning “syntax” and a section of quoted commands where it thinks the error is located. In this case, it is correct because “CREATE” is not spelled correctly. If you see any mention of syntax, check your SQL command against a reference. Be sure that all SQL keywords are spelled correctly, and that you are supplying all of the information that is expected in the form that is expected. If you get any of that wrong, you will get a syntax error code returned. You can learn some SQL basics with SQL Queries 101. This article on the top 10 SQL queries can get you started quickly.

If you misspell the name of the database you wanted, there will be no error message. The system has no way of knowing the name you wanted. It will accept whatever you type and your database name will be whatever was typed in that position of the command.

Getting Data into the Database: More SQL Error Codes

Now that we have the database created, it must be selected for use. This has to be done at the start of each session. We get the following response:

mysql> USE exampldb;

ERROR 1049 (42000): Unknown database ‘exampldb’

The “unknown” error simply means that it can’t find what you asked for. You know that the syntax of the command is good, because you don’t see a syntax error code. It quotes the problem word, so you can see that the name of the database is not the one we created. Fix that and the command works.

mysql> USE exampledb;

Database changed

The system response is not an error code, but it might feel like one. All you did was select the database to be used, you wouldn’t think you changed anything. This is just a standard response to a successful command.

Now we create a table with some fields for our database, and we see this:

mysql> CREATE TABLE courses2(course VARCHAR(30, author VARCHAR(30), cost INT, fi

nished DATE);

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 ‘ auth

or VARCHAR(30), cost INT, finished DATE)’ at line 1

This one is a bit tricky. It is the standard syntax error code, but the quoted text looks fine. This is a classic case of interpreting error codes: look just before the place where the system thinks the error is located. In this case we see that we did not close the parentheses on the preceding field specification. The system is trying to interpret the remainder of the line as part of the VARCHAR parameter.

We finally get some data into the table and now we want to retrieve it, and we see this message:

mysql> SELECT *;

ERROR 1096 (HY000): No tables used

If you leave the table name out of a SELECT statement, you will get a syntax error, even if you only have one table in the database. You might ask, “Geez, I only have one table, why is it confused?” It would be nice for SQL to figure that out, but the more you ask a computer to make assumptions the more you will be open to errors. You are better off being as specific as possible. This leaves no doubt about the result you will get and it makes for better readability in the future. If you save the query and come back to it after a while, you will remember easily what the query does if things are written out explicitly.

Next we will add a new record to the database. We get the following error code:

mysql> INSERT INTO courses VALUES (Sample1, Author1, 29, 2014-02-22);

ERROR 1054 (42S22): Unknown column ‘Sample1’ in ‘field list’

This time the problem is not so obvious. It seems to think that this is a list of fields instead of the data. When we check, we see that the second, third, and last fields don’t have the text delimiters. These values must be contained within quotes. When we fix that, the command runs and the record is added. The lesson here is, even if the error code doesn’t say “Syntax error” specifically, check carefully for correct syntax anyway, especially items such as missing or mismatched delimiters or parentheses.

This will get you started with some common SQL error codes and how to fix the problems. Take a course on Oracle SQL to expand your skills even more.