Appendix H. Common Database Errors
Prev |  |

## Appendix H. Common Database Errors

Following is a list of known SQL errors, and potential solutions to the problems that they represent.

Table H.1. Known Database Error Codes

Database | Error Code | SQL State | Message | Solution
--- | --- | --- | --- | ---
`DB2` | `-803` | `23505` | `SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "%s" from having duplicate rows for those columns.` | Duplicate values have been inserted into a column that has a `UNIQUE` constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate values.
`DB2` | `-511` | `42829` | `SQL0511N The FOR UPDATE clause is not allowed because the table specified by the cursor cannot be modified.` | A datastore transaction read was attempted on a table that is marked as read-only. Either read the data outside of a transaction, or use optimistic transactions.
`DB2` | `-401` | `42818` | `SQL0401N The data types of the operands for the operation ">=" are not compatible.` | A mathematical comparison query was attempted on a field whose mapping was to a non-numeric field, such as `VARCHAR`. DB2 disallows such queries.
`DB2` | `-302` | `22003` | `SQL0302N The value of a host variable in the EXECUTE or OPEN statement is too large for its corresponding use.` | Possible attempt to store a String of a length greater than is allowed by the database's column definition. If creation is done via the `schematool`, ensure that the column-length metadata field is of sufficient length.
`DB2` | `-204` | `42S02` | `SQL0204N "%s" is an undefined name.` | The database schema does not match the mapping defined in the metadata for the persistent class. See the metadata documentation.
`DB2` | `-99999` | `22003` | `CLI0111E Numeric value out of range.` | A numeric range error occured. Ensure that the capacity of the numeric column is sufficient to store the specified value the persistent object is attempting to store.
`DB2` | `-99999` | `HY003` | `CLI0122E Program type out of range.` | A numeric or String range error occured. Ensure that the capacity of the numeric or string column is sufficient to store the specified value the persistent object is attempting to store.
`HSQL` | `-8` | `23000` | `Integrity constraint violation in statement %s` | Attempted modification of a row that would cause a violation of referential integrity constraints. Note that cascading deletes can be obtained by using the dependency metadata extensions. Otherwise, it is the responsibility of the application to deal with maintenance of database-specific referential integrity constraints.
`HSQL` | `-9` | `23000` | `Violation of unique index: 23000 Violation of unique index in statement %s` | Duplicate values have been inserted into a column that has a `UNIQUE` constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate values.
`HSQL` | `-40` | `S1000` | `General error: S1000 General error java.lang.NumberFormatException: %d in statement %s` | A numeric range error occured. Ensure that the capacity of the numeric column is sufficient to store the specified value the persistent object is attempting to store. Note that some versions of HSQL have a bug that prevents `Long.MIN_VALUE` from being stored.
`MySQL` | `1062` | `S1009` | `Invalid argument value: Duplicate entry '1' for key 1` | Duplicate values have been inserted into a column that has a `UNIQUE` constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate values.
`MySQL` | `1196` | `S1000` | `General error: Warning: Some non-transactional changed tables couldn't be rolled back` | One or more tables that are being manipulated are not configured to be transactional. Tables in MySQL, by default, do not support transactions. Table type for schema creation can be configured with the `TableType` parameter of the DictionaryProperties property.
`MySQL` | `1213` | `S1000` | `General error: Deadlock found when trying to get lock; Try restarting transaction` | A deadlock occured during a datastore transaction. This can occur when transaction `TRANS1` locks table `TABLE1`, transaction `TRANS2` locks table `TABLE2`, `TRANS1` lines up to get a lock on `TABLE2`, and then `TRANS2` lines up to get a lock on `TABLE1`. Deadlock prevention is the responsibility of the application, or the application server in which it runs. For more details, see the MySQL deadlock documentation.
`MySQL` | `0` | `08S01` | `Communication link failure: java.io.IOException` | The TCP connection underlying the JDBC Connection has been closed, possibly due to a timeout. If using Kodo's default `DataSource` wrapper, connection testing can be configured via the ConnectionTestTimeout property.
`MySQL` | `1030` | `S1000` | `General error: Got error 139 from table handler` | This is a bug in MySQL server, and can occur when using tables of type InnoDB when long SQL statements are sent to the server. Upgrade to a more recent version of MySQL to resolve the problem.
`MySQL` | `1054` | `S0022` | `Column not found: Unknown column 'Infinity' in 'field list'` | MySQL disallows storage of `Double.POSITIVE_INFINITY` or `Double.NEGATIVE_INFINITY` values.
`Oracle` | `17069` | `null` | `Use explicit XA call` | Manual transaction operations were attempted on a DataSource that was configured to use an XA transaction. In order to utilize XA transactions, set the TransactionMode property to `xa`.
`Oracle` | `17433` | `null` | `invalid arguments in call` | The Oracle JDBC driver throws this exception when a null username or password were specified. A username and password was not specified in the `kodo.propertied`, nor was it specified in the database configuration mechanism, nor was it specified in the `PersistenceManager.getPersistenceManager` invocation.
`Oracle` | `904` | `42000` | `ORA-00904: invalid column name` | The database schema does not match the mapping defined in the metadata for the persistent class. See the metadata documentation.
`Oracle` | `1722` | `42000` | `ORA-01722: invalid number` | A number that Oracle cannot store has been persisted. This can happen when a String field in the persistent class is mapped to an Oracle column of type `NUMBER` and the String value is not numeric.
`Oracle` | `1000` | `72000` | `ORA-01000: maximum open cursors exceeded` | Oracle limits the number of statements that can be open at any given time, and the application has made requests that keep open more statements than Oracle can handle. This can be resolved in one of the following ways: Increase the number of cursors allowed in the database. This is typically done by increasing the `open_cursors` parameter in the `initSIDNAME.ora` file. Ensure that Kodo Query results and Extents are being closed, since open results will maintain an open ResultSet on the server side until they are garbage collected. If using Kodo's default DataSource implementation, decrease the value of the StatementCacheMaxSize parameter.
`Oracle` | `932` | `42000` | `ORA-00932: inconsistent datatypes: expected - got CLOB` | A normal String field was mapped to an Oracle `CLOB` type. Oracle requires special handling for CLOBs. Ensure that the metadata for the persistent field is specified as a `CLOB` by setting the column-length field to `-1`.
`Oracle` | `1` | `23000` | `ORA-00001: unique constraint (%s) violated` | Duplicate values have been inserted into a column that has a `UNIQUE` constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate values.
`Oracle` | `0` | `null` | `Underflow Exception` | A numeric underflow occured. Ensure that the capacity of the numeric column is sufficient to store the specified value the persistent object is attempting to store. Note that Oracle `NUMERIC` fields have a limitation of 38 digits.
`Oracle` | `0` | `null` | `Overflow Exception` | A numeric overflow occured. Ensure that the capacity of the numeric column is sufficient to store the specified value the persistent object is attempting to store. Note that Oracle `NUMERIC` fields have a limitation of 38 digits.
`Pointbase` | `78003` | `ZW003` | `The value "%s" cannot be converted to a number.` | This can happen when a String field in the persistent class is mapped to a numeric column, and the String value cannot be parsed into a number.
`Pointbase` | `25203` | `22003` | `Data exception -- numeric value out of range. %d.` | A numeric range error occured. Ensure that the capacity of the numeric column is sufficient to store the specified value the persistent object is attempting to store.
`PostgreSQL` | `0` | `null` | `ERROR: Unable to identify an operator '>=' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast` | An integer field is mapped to a decimal column type. PostgreSQL disallows performing numeric comparisons between integers and decimals.
`PostgreSQL` | `0` | `null` | `ERROR: Cannot insert a duplicate key into unique index bug488pcx_pkey` | Duplicate values have been inserted into a column that has a `UNIQUE` constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate values.
`PostgreSQL` | `0` | `null` | `ERROR: Attribute 'infinity' not found` | PostgreSQL disallows storage of `Double.POSITIVE_INFINITY` or `Double.NEGATIVE_INFINITY` values.
`PostgreSQL` | `0` | `null` | `You will have to retype this query using an explicit cast` | A String field is mapped to a numeric column type. PostgreSQL disallows performing String comparisons in queries against a numeric column.
`SQLServer` | `0` | `08007` | `Can't start a cloned connection while in manual transaction mode.` | Append ";SelectMethod=cursor" to the ConnectionURL. See the description of the problem on the Microsoft support site.
`SQLServer` | `` | `` | `sp_cursorclose: The cursor identifier value provided (abcdef0) is not valid.` | This can sometimes show up as a warning when Kodo is closing a PreparedStatement. It is due to a bug in the SQLServer driver, and can be ignored, since it should not affect anything.
`SQLServer` | `306` | `HY000` | `The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.` | A Query ordering was attempted on a field that is mapped to a `CLOB` or `BLOB`, which is disallowed by SQLServer.
`SQLServer` | `8114` | `HY000` | `Error converting data type varchar to %s.` | This can happen when a String field in the persistent class is mapped to a numeric column, and the String value cannot be parsed into a number.
`SQLServer` | `245` | `22018` | `Syntax error converting the varchar value '%s' to a column of data type int.` | This can happen when a String field in the persistent class is mapped to a numeric column, and the String value cannot be parsed into a number.
`SQLServer` | `2627` | `23000` | `Violation of PRIMARY KEY constraint 'PK__%s'. Cannot insert duplicate key in object '%s'.` | Duplicate values have been inserted into a primary key column that has a `UNIQUE` constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate primary keys when using application identity.
`SQLServer` | `169` | `HY000` | `A column has been specified more than once in the order by list. Columns in the order by list must be unique.` | Ensure that there are no duplicates in the ordering of the `Query`.
`SQLServer` | `0` | `HY000` | `Object has been closed.` | The TCP connection underlying the JDBC Connection may have been closed, possibly due to a timeout. If using Kodo's default `DataSource` wrapper, connection testing can be configured via the ConnectionTestTimeout property.
`Sybase` | `311` | `ZZZZZ` | `The optimizer could not find a unique index which it could use to scan table '%s' for cursor 'jconnect_implicit_%d'` | A pessimistic lock was attempted on a table that does not have a primary key (or other unique index). By default, the Kodo `schematool` does not create primary keys for join tables. In order to use datastore locking for relations, an `IDENTITY` column should be added to any tables that do not already have them.
`Sybase` | `2762` | `` | `The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.` | This may happen when running the schematool against a Sybase database that is not configured to allow schema-altering commands to be executed from within a transaction. This can be enabled by entering the command `sp_dboption database_name,"ddl in tran", true` from `isql`. See the Sybase documentation for Allowing data definition commands in transactions.
`Sybase` | `0` | `JZ0BE` | `JZ0BE: BatchUpdateException: Error occurred while executing batch statement: Arithmetic overflow during implicit conversion of NUMERIC value '%d' to a NUMERIC field.` | A numeric overflow occured. Ensure that the capacity of the numeric column is sufficient to store the specified value the persistent object is attempting to store.
`Sybase` | `0` | `JZ00B` | `JZ00B: Numeric overflow.` | A numeric overflow occured. Ensure that the capacity of the numeric column is sufficient to store the specified value the persistent object is attempting to store.
`Sybase` | `257` | `42000` | `Implicit conversion from datatype 'VARCHAR' to 'TINYINT' is not allowed. Use the CONVERT function to run this query.` | A String field is stored in a column of numeric type. Sybase disallows querying against these fields.
`Sybase` | `169` | `ZZZZZ` | `Expression '1' and '8' in the ORDER BY list are same. Expressions in the ORDER BY list must be unique.` | Ensure that there are no duplicates in the ordering of the `Query`.
`Sybase` | `511` | `ZZZZZ` | `Attempt to update or insert row failed because resultant row of size 2009 bytes is larger than the maximum size (1961 bytes) allowed for this table.` | Attempt to store a String of a length greater than is allowed by the database's column definition. If creation is done via the `schematool`, ensure that the column-length metadata field is of sufficient length.
`Sybase` | `2601` | `23000` | `Attempt to insert duplicate key row in object '%s' with unique index '%s'` | Duplicate values have been inserted into a column that has a `UNIQUE` constraint. It is the responsibility of the application to deal with prevention of insertion of duplicate values.

Prev | Up |
G.9. Example properties for MySQL | Home |