Issue Details (XML | Word | Printable)

Key: NUCRDBMS-501
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Unassigned
Reporter: Andres Murillo
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

Use of mapped-by on element instead of field/property causes problems in schema generation of M-N

Created: 17/Feb/11 07:10 PM   Updated: 07/Mar/11 05:37 PM   Resolved: 18/Feb/11 09:59 AM
Component/s: Schema
Affects Version/s: 2.2.2, 3.0.0.m1
Fix Version/s: 3.0.0.m2

File Attachments: 1. Zip Archive TestEnv.zip (12 kB)

Environment: Windows XP, maven2, vmware, DB2 Express 9.7

Forum Thread URL: http://www.datanucleus.org/servlet/forum/viewthread_thread,6555
Datastore: IBM DB2


 Description  « Hide
When running the schematool to create the schema on DB (clean database) the following error is produced:
Error thrown executing ALTER TABLE SRCMULTI_DESTMULTISET ADD SRCMULTI_ID_OID VARCHAR(256) NOT NULL : DB2 SQL Error: SQLCODE=-193, SQLSTATE=42601, SQLERRMC=SRCMULTI_ID_OID, DRIVER=3.59.81

This is due to:
- DB2 has a restriction: SQL0193N In an ALTER TABLE statement, the column column-name has been specified as NOT NULL and either the DEFAULT clause was not specified or was specified as DEFAULT NULL.
- The schema tool is issuing redundant statement to add the column to the table (see the attached log).

The original thread as an attachment with a maven2 environment that can be used to reproduce the behavior, note that the example is running with HSSQL to show the duplicate column definition, to reproduce the full error use a DB2 database.

INFO DataNucleus.Datastore.Schema - Creating table SRCMULTI_DESTMULTISET
DEBUG DataNucleus.Datastore.Schema - CREATE TABLE SRCMULTI_DESTMULTISET(DESTMULTI_ID_OID VARCHAR(256) NOT NULL,SRCMULTI_ID_EID VARCHAR(256) NOT NULL)
DEBUG DataNucleus.Datastore.Schema - ALTER TABLE SRCMULTI_DESTMULTISET ADD CONSTRAINT SRCMULTI_DEF4BV_PK PRIMARY KEY (DESTMULTI_ID_OID,SRCMULTI_ID_EID)
DEBUG DataNucleus.Datastore.Schema - Loading column info for table(s) "SINGLECHILDOBJ, SINGLECHILDOBJNULLABLE, SRCMULTI_DESTMULTISET, SRCMULTI, DESTMULTI, DESTSINGLE, MULTIPLECHILDOBJ, SRCSINGLE, PARENTOBJ" in Catalog "", Schema ""
DEBUG DataNucleus.Datastore.Schema - Column info retrieved for table "SRCMULTI_DESTMULTISET" : 2 columns found
INFO DataNucleus.Datastore.Schema - Creating column SRCMULTI_ID_OID for table SRCMULTI_DESTMULTISET
INFO DataNucleus.Datastore.Schema - Creating column DESTMULTI_ID_EID for table SRCMULTI_DESTMULTISET
DEBUG DataNucleus.Datastore.Schema - ALTER TABLE SRCMULTI_DESTMULTISET ADD SRCMULTI_ID_OID VARCHAR(256) NOT NULL
ERROR DataNucleus.Datastore - Error thrown executing ALTER TABLE SRCMULTI_DESTMULTISET ADD SRCMULTI_ID_OID VARCHAR(256) NOT NULL : DB2 SQL Error: SQLCODE=-193, SQLSTATE=42601, SQLERRMC=SRCMULTI_ID_OID, DRIVER=3.59.81
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-193, SQLSTATE=42601, SQLERRMC=SRCMULTI_ID_OID, DRIVER=3.59.81
at com.ibm.db2.jcc.am.dd.a(dd.java:676)

Andres Murillo added a comment - 17/Feb/11 07:11 PM
Example environment to reproduce the problem.

Andres Murillo made changes - 17/Feb/11 07:11 PM
Field Original Value New Value
Attachment TestEnv.zip [ 11362 ]
Andres Murillo added a comment - 17/Feb/11 11:17 PM
Please close the issue, the problem was a faulty use of annotations (used mapped by on an @Element instead of @Persistent)

Andy Jefferson added a comment - 18/Feb/11 09:58 AM
Updated title to reflect the real issue

Andy Jefferson made changes - 18/Feb/11 09:58 AM
Summary Duplicate column definitions on schema-create cause error in DB2 Use of mapped-by on element instead of field/property causes problems in schema generation of M-N
Affects Version/s 3.0.0.m1 [ 11062 ]
Component/s Schema [ 10146 ]
Component/s SchemaTool [ 10143 ]
Andy Jefferson added a comment - 18/Feb/11 09:59 AM
SVN trunk now takes any element "mapped-by" as the mapped-by for the field (when that is not specified). It's not clear from the JDO spec why element "mapped-by" exists so DataNucleus has never made use of it. 3.0 M2 and later will make use of it when not specified on the field

Andy Jefferson made changes - 18/Feb/11 09:59 AM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 3.0.0.m2 [ 11151 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 07/Mar/11 05:37 PM
Status Resolved [ 5 ] Closed [ 6 ]