Do you know where the (database) error is? Add it to the list!

Do you know where the (database) error is? Add it to the list!

We want to build the most extensive and comprehensive list of database error messages in existence! Our project may be ambitious but we think that we can achieve it with your help.

We're not asking for much of your time - if you come across an error while you work (or there is a sticky one that you've come across in the past) just post it in this thread.

In order to be able to classify all submissions, we need as many details as possible, with special attention to:

• Database Engine (MySQL, SQLite, etc)
• Error Code
• Error Message
• Error Description
• Error solution

If you don’t know the solution, that's cool - post the error anyway and we will do everything in our hands to find an answer. But if you do happen to know the solution, it would be great if you tell us.

An example:

"Database Engine: Oracle 11g

Error Code: SQLState : s1000[oracle][ODBC][ora] Ora 28000

Error Message: The account is locked

Error Description:

The true database error is ORA-28000. The rest of the message is given as the connection to the database was done via a ODBC driver.
This error message is typically given when a user has tried to log in too many times but did not provided the correct password. The number of tries is specified by the user’s profile parameter FAILED_LOGIN_ATTEMPTS.
It may also be the case that the Database Administrator has closed the account, probably for security reasons. As an example, when the database has been migrated and the old database is not to be used anymore.
Another possibility is that the password was reseted but an application connecting to the database was not updated with the new password and therefore it locks the account over and over.

Error Solution:
If it is a case of too many attempted logins, waiting may solve it. The variable PASSWORD_LOCK_TIME will indicate how long this waiting will take.
If the database has been migrated to a new server, make sure that your TNSNAMES.ORA does point to the correct server. If you think that you have an additional problem (such as a DSN name solving issue), try to use the server IP address instead of the name of the server.
If other applications are using the database, make sure the these applications are using the right password and can connect to the database with no issues."

May the errors be with us!

Database Engine: SQLite

Error Code: N/A

Error Message: Error: near "index": syntax error

Error Description:
The following query fails and the table is not created

Code:

---

create table members (index integer primary key, name text not null);

---

Error Solution:
Many syntax errors can be spotted by adopting a better style of writing queries. Though in this case the error is caused by the use of a "keyword" that has special meaning. IMHO it is much better to avoid using keywords as identifiers (it can make things confusing) but if it must be, enclosing them in single quotes will tell the database it's an identifier (double quotes signify string literals)
eg. The following query will create the table

Code:

---

CREATE TABLE 'members' (
  'index' INTEGER PRIMARY KEY
 , 'name' TEXT NOT NULL
  );

---

Edit:

:d'oh: I must have been thinking of something else - or more likely not thinking :blush:
See next post

Quote:

---

If you want to use a keyword as a name, you need to quote it. There are four ways of quoting keywords in SQLite:
'keyword' A keyword in single quotes is a string literal.
"keyword" A keyword in double-quotes is an identifier.
[keyword] A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
`keyword` A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.

---

Code:

---

CREATE TABLE "members" (
  "index" INTEGER PRIMARY KEY
 , "name" TEXT NOT NULL
  );

---

Quote:

---

Originally Posted by Mittineague

... but if it must be, enclosing them in single quotes will tell the database it's an identifier (double quotes signify string literals)

---

in general, this is incorrect

in fact, it's the other way round, single quotes delimit strings, and double quotes (can) delimit identifiers

however, sqlite is very forgiving...

Quote:

---

Originally Posted by da sqlite manual

For resilience when confronted with historical SQL statements, SQLite will sometimes bend the quoting rules above:

• If a keyword in single quotes (ex: 'key' or 'glob') is used in a context where an identifier is allowed but where a string literal is not allowed, then the token is understood to be an identifier instead of a string literal.
• If a keyword in double quotes (ex: "key" or "glob") is used in a context where it cannot be resolved to an identifier but where a string literal is allowed, then the token is understood to be a string literal instead of an identifier.

---

as a "best practice" strategy, always use single quotes for strings, and double quotes for identifiers

Database Engine: MySQL

Error Code: 1064

Error Message: 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 ...

Error Description:

Error Solution:
You have a poorly formatted SQL query, whose syntax is incorrect. This could be a missing parenthesis, missing comma, extra comma, extra parenthesis, extra or missing quote, etc.

To help track it down, consider reformatting your query to make mistakes more obvious.

For example, let's say you have the following query:

Code:

---

SELECT ProgramName AS `name`, SUM(CASE WHEN Billable = 'PENDING' THEN 1 ELSE 0 END) AS `pending_leads`, SUM(CASE WHEN Billable = 'PASS' THEN 1 ELSE 0 END) AS `pass_leads`, SUM(CASE WHEN Billable = 'FAIL' THEN 1 ELSE 0 END) AS `fail_leads`, FROM leads WHERE ProgramID = '1' AND `TimeStamp` >= '2013-01-01 00:00:00' AND `TimeStamp` < '2013-01-31 23:59:99' GROUP BY ProgramName

---

Consider re-formatting it like so:

Code:

---

SELECT
  ProgramName AS `name`
  , SUM(
    CASE WHEN Billable = 'PENDING' THEN 1
    ELSE 0
    END
  ) AS `pending_leads`
  , SUM(
    CASE WHEN Billable = 'PASS' THEN 1
    ELSE 0
    END
  ) AS `pass_leads`
  , SUM(
    CASE WHEN Billable = 'FAIL' THEN 1
    ELSE 0
    END
  ) AS `fail_leads`
  ,
FROM leads
WHERE
  ProgramID = '1'
  AND `TimeStamp` >= '2013-01-01 00:00:00'
  AND `TimeStamp` < '2013-01-31 23:59:99'
GROUP BY ProgramName

---

Now it is easy to see the extra comma immediately before "FROM leads"

This is most common issue we face in oracle DB

Database Engine: Oracle

Error Code: ORA-00907

Error Message: missing right parenthesis

Error Description :

Let’s see this SQL statement below

Quote:

---

SELECT *
FROM Table_Name
WHERE Name_Value IN (' GENERAL'S OFFICE')

---

If we run a statement by passing a similar value in WHERE statement.
Where the parameter passed to IN statement with single quotes within the value,
This error prompts deciding the value ends where the first single quotes is hit ' GENERAL'S OFFICE' i.e.
‘GENERAL’

Error solution
The solution is pretty simple just replace the single quotes within the text value similar to one below

Quote:

---

SELECT *
FROM Table_Name
WHERE Name_Value IN (' GENERAL''S OFFICE')

---

Here it may look like double quotes in text but it is replaced with two single quotes.
That’s the exact solution.

Database Engine: Oracle

Error Code: ORA-01439

Error Message: column to be modified must be empty to change datatype

Error Description :
When we try to change a column data type which already has values stored in it

Quote:

---

ALTER TABLE table_name MODIFY column_name column_type;

---

Error solution
You can only modify a column data type if has no or null values

Quote:

---

UPDATE table_name SET column_name = NULL;

---

Then try to alter the table column datatype

Database Engine: MySQL

Error Code: none

Error Message: "Query End" in processlist - multiple entries. Possibly "too many connections"

Error Description:
When you check the process list in MySQL, you may see lots of processes in "Query End" state. If you kill them they remain as "Killed" but don't go away. You may also have problems stopping and starting the MySQLd process. Another symptom is that you may run out of connections, with a "too many connections" error. MySQL has a max_connections variable that actually means "maximum standard users, plus one extra super user", so if your site is connecting via a super-user then you won't be able to log in to perform any maintenance either.

Error Solution:
There are many reasons for this, but the most common one that I've come across is that the drive where your MySQL data is stored is out of space. This is especially common if you're logging (ie using replication) or maybe you've just recently added a LOT of data to your database. On a *nix based system, you can try a command like this to see if any of your drives are low on space:

Code:

---

df

---

Look for the Use% column. If you see anything close to 100% then you may have just found your problem.

If you can log in to MySQL you can purge logs (assuming that is where the space has gone) with a query something like this:

Code MySQL:

---

PURGE LOGS BEFORE '2013-01-01 00:00:00'

---

Alternatively, you may be able to get rid of a few large files on that drive and can then restart MySQL. You will need to either add more space, or in the case where you're logging too much data you'll need to implement a better maintenance schedule to keep the size of these down

Database Engine: MySQL

Error Code: 2003 / 10061

Error Message: Can't connect to MySQL server on 'hostname' (10061)

Error Description:
When you try to connect to a MySQL database using a site (ie WordPress, PHPMyAdmin etc) or a desktop GUI (ie HeidiSQL, MySQL Workbench) you may receive an error stating that you cannot connect to MySQL server on 'hostname'

Error Solution:
There are many causes for this, but there is one fundamental reason: Your attempt to connect has been blocked somewhere between the client and the server. You will not get this error if the MySQL client has been able to connect to the MySQL server but could then not authenticate.

Steps to take to attempt to resolve:

1. Check that MySQL is actually running on the server. If you're running it on Linux and you have access to the command line (usually via SSH) then there are a number of ways to check this. On a Red Hat based version (CentOS, Amazon Linux, Fedora etc) you can simply type "service mysqld status" which should hopefully return something along the lines of "mysqld (pid 1234) is running...". If not then you need to start the service. Again, on RH based OS's you can simply type "service mysqld start". Ensure that it does actually start. For other OS's you may need to check how to start/stop the MySQL daemon.
2. Check that your connection data is correct. A common mistake is to set the database host to "localhost" or "127.0.0.1" when you're connecting to a MySQL server that is not actually on the same machine that you're connecting from. Also bear in mind that if you need to connect across the internet then you will need to ensure that the hostname or IP address is public. IE, you can't use an internal hostname or IP address like 192.168.0.3. All publicly visible servers will have at least two IP addresses associated with them: One will be an internal private IP address (take a look at this Wikipedia article for a list of private IP ranges) and another will be a public IP address (basically one that doesn't fit into the private ranges). If your web and database server are in the same subnet you will use the private IP to connect. If they're not, you will use the public IP. Don't forget that just because you can connect to the database server from your website using a particular IP or hostname, it doesn't mean that you can use the same to connect from your workstation. If you have a hostname, rather than an IP address, try to ping it from the machine that you're trying to connect from. Open a command line and simply type "ping enter_hostname_here". For example, "ping google.com". If that hostname is resolved to an IP address then you can use the hostname from where you are. If it doesn't, then it's most likely a private hostname and you need to find public connection information. One last clause on this point is that in some cases a public IP/hostname will NOT be available. We have this ourselves where some database servers are only accessible from within our private network, so I cannot connect from home without first SSH'ing in to the network. That's beyond the scope of this advice however.
3. Check firewall rules. MySQL uses port 3306 by default, and if you don't provide a port number when you connect then most clients will assume you meant to use 3306. You must ensure that TCP port 3306 will accept requests from your client (whether it be your webserver or your workstation), or if the default port has been changed (this is often recommended for security to hide that you have MySQL running on your server) then you will need to specify the port when connecting

There are other reasons, but I've tried to cover the three most likely ones and the first ports of call