Database errors, explained
What the message means, why it happens and how to fix it, for the errors people search for most. Every one was reproduced on a real server, and the output on each page is what that server printed.
PostgreSQL
- canceling statement due to lock timeoutThe statement waited longer than lock_timeout for a lock another session holds, so it gave up before doing anything. Find the blocking session (often one left idle in a transaction), then retry.
- canceling statement due to statement timeoutThe statement ran longer than statement_timeout allows, so the server cancelled it and undid its work. Either the query is slow, it was stuck waiting for a lock, or the limit is too short for this job.
- cannot drop because other objects depend on itSomething else is built on the object you’re dropping: a view on the table, a foreign key pointing at it, a column of that type. The DETAIL lines list them. Drop or change those first, or use CASCADE once you’ve read exactly what it will remove.
- column does not existPostgreSQL can’t find a column by that name in the tables your query can see. Most often a string was written in double quotes, which PostgreSQL reads as a column name; otherwise it’s a typo, a capitalised name, or an alias used where it doesn’t exist yet.
- column must appear in the GROUP BY clause or be used in an aggregate functionYour query groups rows, and one column in SELECT, HAVING or ORDER BY could have several different values within a group, so PostgreSQL won’t pick one for you. Add it to GROUP BY, wrap it in an aggregate, or group by the table’s primary key.
- column reference is ambiguousTwo tables in your query, or a table and a PL/pgSQL variable, have something with the same name, and you used it without saying which. Prefix it with the table or alias, such as c.id instead of id.
- Connection refused: Is the server running on that host and accepting TCP/IP connections?Your client reached the machine, but nothing there was listening on that port, so the operating system refused the connection. The server isn’t running, you have the wrong port, or it only listens on other addresses.
- could not serialize access due to concurrent updateYour transaction runs at REPEATABLE READ or SERIALIZABLE, and another transaction changed data in a way that breaks that promise, so PostgreSQL cancelled yours. This is expected: roll back and run the whole transaction again.
- current transaction is aborted, commands ignored until end of transaction blockAn earlier statement in this transaction failed, and PostgreSQL refuses everything else until the transaction ends. Find that first error, run ROLLBACK, and start again.
- database does not existYou signed in, but the server has no database with exactly that name. Often the client used your user name because no database was given, or you’ve reached a different server than you meant.
- deadlock detectedTwo transactions were each waiting for a lock the other held, so PostgreSQL cancelled one of them. Retry the cancelled transaction, and make your code take locks in the same order everywhere so it doesn’t happen again.
- duplicate key value violates unique constraintA row would give a primary key or unique column a value another row already has. If the clash is on an id you didn’t supply, the table’s sequence is behind the data (usually after an import) and needs moving past the highest id.
- fe_sendauth: no password suppliedThe server asked for a password and your client had none to give: nothing in the connection string, no PGPASSWORD, no matching line in ~/.pgpass, and no way to ask you. Supply the password in one of those places.
- insert or update on table violates foreign key constraintA foreign key says every value in a child column must exist in the parent table. Either you’re inserting or updating a child row whose parent isn’t there, or you’re deleting or changing a parent row that child rows still point at.
- invalid input syntax for typePostgreSQL tried to turn a piece of text into a typed value (an integer, a uuid, a boolean, JSON) and the text isn’t in a form that type accepts. The quoted value in the message is exactly what it got, which usually shows the problem: an empty string, a comma, or a word like “undefined”.
- more than one row returned by a subquery used as an expressionA subquery in brackets, used where a single value belongs, returned two or more rows. Decide which you want: test membership with IN or EXISTS, combine the rows with an aggregate, or choose one with ORDER BY … LIMIT 1.
- no pg_hba.conf entry for hostNo line in the server’s pg_hba.conf matches your connection, so the server turns it away before it asks for a password. Add a rule for the host, user, database and encryption named in the message, or connect the way an existing rule expects (often with TLS).
- null value in column violates not-null constraintA row would have NULL in a column declared NOT NULL. Either the statement left the column out and it has no default, or something sent an explicit NULL, which PostgreSQL stores as NULL even when the column has a default.
- operator does not exist: integer = textYou’re comparing (or adding, or matching) two values of different types, and PostgreSQL has no operator for that pair and won’t convert one silently. Cast one side so both are the same type, or fix the column that has the wrong type.
- password authentication failed for userThe server asked for a password and didn’t accept the one it got. Either the password is wrong, or the user doesn’t exist: PostgreSQL deliberately gives the same message for both.
- permission denied for tableYour role lacks a privilege the statement needs on the object the message names: a table, a schema, a sequence or a database. Grant that privilege (and USAGE on the schema), or run the statement as a role that has it.
- relation does not existPostgreSQL found no table, view or sequence with that exact name in the schemas it searched. Usually the name was created with capitals in double quotes, the table is in a schema that isn’t on your search_path, or you’re connected to a different database.
- role does not existThe server has no role (user) with exactly that name. Usually the client sent a name you didn’t choose, such as your Mac user name or root, or the server’s first superuser isn’t called postgres.
- server does not support SSL, but SSL was requiredYour client was told to require TLS (sslmode=require or stronger), and the server said it doesn’t do TLS, so the client stopped. Either connect without requiring it, which is fine on your own machine, or turn TLS on in the server.
- sorry, too many clients alreadyEvery connection slot on the server is in use, so it turns new ones away. Find which clients hold them, close the idle ones, and make your connection pools fit inside max_connections.
- syntax error at or nearPostgreSQL couldn’t parse the statement, and the quoted word is where it gave up. The mistake is usually just before that point: a reserved word used as a name, a missing or extra comma, MySQL-only syntax, or a quote or bracket that isn’t closed.
- value too long for type character varyingA string is longer than its column allows: varchar(n) and char(n) hold at most n characters. The message doesn’t name the column, so match the n against your table’s columns, then widen the column (cheap) or shorten the value.
MySQL and MariaDB
- ERROR 1040: Too many connectionsEvery connection slot the server allows (max_connections, 151 by default) is taken, so it turns new ones away. Usually an app holds more connections than it needs: leaked, idle or stuck behind slow queries. Find them first; raise the limit only if the load is real.
- ERROR 1045 (28000): Access denied for userThe server didn’t accept the user, password and client address you connected with. MySQL gives the same message for a wrong password, an unknown user and an account that only exists for another host.
- ERROR 1049 (42000): Unknown databaseThe server has no database with that exact name. Check the spelling and letter case against SHOW DATABASES, make sure you’re on the server you think, and create it if it was never made.
- ERROR 1054 (42S22): Unknown column in 'field list'The query names a column the server can’t find in the tables it’s reading. Often the column really is missing or misspelt; as often, it’s a string written without quotes, or a SELECT alias used in WHERE.
- ERROR 1055 (42000): Expression of SELECT list is not in GROUP BY clause (only_full_group_by)Your query groups rows but also selects a column that can have several values in a group, so MySQL can’t know which one to show. Add the column to GROUP BY, wrap it in an aggregate such as MAX(), or use ANY_VALUE() if any value will do.
- ERROR 1062 (23000): Duplicate entry for keyA row you inserted or updated has the same value as an existing row in a PRIMARY KEY or UNIQUE index, so the server refused it. The message gives the value and the index; the clash can be less obvious than it looks, because comparisons follow the column’s collation.
- ERROR 1064 (42000): You have an error in your SQL syntaxThe parser hit something it couldn’t make sense of. The text after “near” is where it gave up; the mistake is usually right before it. “near ''” means the statement ended too early.
- ERROR 1146 (42S02): Table doesn't existThe database named in the message has no table with exactly that name. Most often it’s letter case: macOS and Windows ignore it, Linux doesn’t. Otherwise you’re in the wrong database, or the table hasn’t been created.
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionYour statement waited too long for a lock another session holds, usually a transaction left open, and gave up. Find the blocking session and commit, roll back or kill it. Only the timed-out statement is undone; the rest of your transaction is still open.
- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionTwo transactions each waited for a lock the other held, so InnoDB rolled one of them back, entirely. Retry that transaction; to make deadlocks rarer, take locks in the same order, keep transactions short, and avoid check-then-insert.
- ERROR 1215 (HY000): Cannot add foreign key constraintThe server refused to create a foreign key because the two columns don’t match exactly, the referenced column has no suitable index, or a table can’t have foreign keys. MySQL 8 names the cause in a specific error; MariaDB says “errno: 150” and puts the reason in SHOW WARNINGS.
- ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F…' for columnThe text contains characters the column’s character set can’t store, most often an emoji going into a utf8 (really utf8mb3) or latin1 column, or a connection that isn’t utf8mb4. Convert the column to utf8mb4 and connect with utf8mb4.
- ERROR 1406 (22001): Data too long for columnA value is longer than its column’s declared size, so in strict mode the server rejects the row instead of cutting it short. Check the column’s size, then widen the column or shorten the value.
- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint failsOther rows still point at the row you tried to delete or whose key you tried to change. Delete or re-point those child rows first, or change the foreign key so the server does it for you with ON DELETE CASCADE or SET NULL.
- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint failsYou inserted or updated a row whose foreign key points at a parent row that doesn’t exist. Insert the parent first, use NULL when there’s no parent, or find and fix the orphaned rows.
- ERROR 2002 (HY000): Can't connect to local MySQL server through socketYour client tried to reach a server on the same machine through a Unix socket file and nothing answered there. Either the server isn’t running, it uses a different socket path, or you meant to connect over TCP.
- ERROR 2003 (HY000): Can't connect to MySQL server on 'host'Your client couldn’t open a TCP connection to the server’s host and port. The number at the end says how: (111) or (61) means refused, nothing is listening there; (110) or (60) means timed out, something in between dropped it.
SQLite
- attempt to write a readonly databaseSQLite opened the database but can’t write to it. Either the process lacks write permission on the file, its folder or its -wal and -shm files, or the database was opened read-only on purpose.
- database disk image is malformedSQLite read a page of the file that doesn’t make sense: the database is corrupt. Stop writing to it, work on a copy, run PRAGMA integrity_check to see what’s damaged, then rebuild an index or recover the data into a new file.
- database is lockedAnother connection holds a lock on the database file, and yours gave up instead of waiting. Set a busy timeout so it waits, switch to WAL so readers stop blocking the writer, and keep write transactions short.
- file is not a databaseThe file doesn’t start with an SQLite header, so SQLite won’t read it. Usually it isn’t an SQLite database at all (a CSV, a SQL dump, a compressed backup, a Git LFS pointer) or it’s encrypted.
- FOREIGN KEY constraint failedA row refers to a parent row that doesn’t exist, or you tried to delete or change a parent that other rows still refer to. SQLite checks this only when PRAGMA foreign_keys = ON, which is off on every new connection.
- near "…": syntax errorSQLite’s parser reached the quoted word and couldn’t fit it into any valid statement. The mistake is at that word or right before it: often a reserved word used as a name, a missing or extra comma, or syntax from another database.
- no such tableSQLite looked for the table in the database file you opened and didn’t find it. Most often it’s the wrong file: SQLite quietly creates an empty database when the path doesn’t exist.
- UNIQUE constraint failedThe row you inserted or updated has the same value as an existing row in a column (or set of columns) that must be unique. The message names the table and column; find the row that already has that value.
MongoDB
- Document failed validationThe collection has a validator, and the document your insert or update would produce breaks one of its rules. The error’s errInfo field says which rule and which field; fix the document, or change the rules if they’re wrong.
- E11000 duplicate key error collectionA unique index already holds the value your insert or update tried to write. The message names the collection, the index and the duplicate key; find the document that has it, then upsert, change the value or remove duplicates.
- MongoServerError: Authentication failedThe server didn’t accept the user name and password for the database the client authenticated against. Usually the password is wrong or authSource names a database where the user doesn’t exist; MongoDB gives the same message for both.
- MongoServerSelectionError: connect ECONNREFUSEDYour driver couldn’t reach a usable MongoDB server before its timeout (30 seconds by default), so the query was never sent. The text after the colon says why: nothing listening, a name that doesn’t resolve, a blocked network, or a replica set that doesn’t match.
- not authorized on db to execute commandYou signed in, but your user has no role that allows this command on this database. Check which roles the connection has, then grant the role it needs on the right database, or connect as a user that has it.
- Transaction numbers are only allowed on a replica set member or mongosYou started a transaction (or a retryable write) on a standalone mongod, and only replica sets and sharded clusters support them. Run the server as a replica set; a single-member set is enough for development.