Delimited identifiers issue

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Delimited identifiers issue

Xavier Baqués
Hi,

I am using OpenJPA with Delimited identifiers enabled through the orm.xml
file, I am able to create the database schema and I can see debugging that
table columns are correctly delimited with double quotes in Table object,
however, when I ask EntityManager to create a query it loads the table
metadata into the Table object without columns being delimited, only table
name is delimited. As the columns in table metadata are not delimited, when
the JPQL is parsed an exception is thrown because it cannot find delimited
column names (they are correctly delimited from the corresponding Entity
object) in table metadata (in this case the Table object that gets created
from the table metadata does not have the column names delimited).

Is there any way to fix that by changing the configuration and make
Delimited identifiers work correctly?

System information:

OpenJPA version: 2.4.3
MySQL version: 5.7.27

Stack trace of the error:

<openjpa-2.4.3-r422266:1833086 fatal user error>
org.apache.openjpa.persistence.ArgumentException:
"com.streamsets.apps.pipelinestore.backend.bean.TestInfo.description"
declares column ""DESCRIPTION"", but this column does not exist in table
""TEST_INFO"".
at
org.apache.openjpa.jdbc.meta.MappingInfo.mergeColumn(MappingInfo.java:692)
at
org.apache.openjpa.jdbc.meta.MappingInfo.createColumns(MappingInfo.java:593)
at
org.apache.openjpa.jdbc.meta.ValueMappingInfo.getColumns(ValueMappingInfo.java:178)
at
org.apache.openjpa.jdbc.meta.strats.StringFieldStrategy.map(StringFieldStrategy.java:84)
at
org.apache.openjpa.jdbc.meta.FieldMapping.setStrategy(FieldMapping.java:148)
at
org.apache.openjpa.jdbc.meta.RuntimeStrategyInstaller.installStrategy(RuntimeStrategyInstaller.java:81)
at
org.apache.openjpa.jdbc.meta.FieldMapping.resolveMapping(FieldMapping.java:498)
at org.apache.openjpa.jdbc.meta.FieldMapping.resolve(FieldMapping.java:463)
at
org.apache.openjpa.jdbc.meta.ClassMapping.resolveNonRelationMappings(ClassMapping.java:921)
at
org.apache.openjpa.jdbc.meta.ClassMapping.resolveMapping(ClassMapping.java:858)
at org.apache.openjpa.meta.ClassMetaData.resolve(ClassMetaData.java:1813)
at
org.apache.openjpa.meta.MetaDataRepository.processBuffer(MetaDataRepository.java:829)
at
org.apache.openjpa.meta.MetaDataRepository.resolveMapping(MetaDataRepository.java:784)
at
org.apache.openjpa.meta.MetaDataRepository.resolve(MetaDataRepository.java:664)
at
org.apache.openjpa.meta.MetaDataRepository.getMetaDataInternal(MetaDataRepository.java:418)
at
org.apache.openjpa.meta.MetaDataRepository.getMetaData(MetaDataRepository.java:389)
at
org.apache.openjpa.meta.MetaDataRepository.getMetaData(MetaDataRepository.java:472)
at
org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getClassMetaData(JPQLExpressionBuilder.java:177)
at
org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.resolveClassMetaData(JPQLExpressionBuilder.java:153)
at
org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateMetaData(JPQLExpressionBuilder.java:243)
at
org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateMetaData(JPQLExpressionBuilder.java:213)
at
org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateType(JPQLExpressionBuilder.java:206)
at
org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.access$200(JPQLExpressionBuilder.java:81)
at
org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.populate(JPQLExpressionBuilder.java:2445)
at org.apache.openjpa.kernel.jpql.JPQLParser.populate(JPQLParser.java:60)
at
org.apache.openjpa.kernel.ExpressionStoreQuery.populateFromCompilation(ExpressionStoreQuery.java:162)
at org.apache.openjpa.kernel.QueryImpl.newCompilation(QueryImpl.java:679)
at
org.apache.openjpa.kernel.QueryImpl.compilationFromCache(QueryImpl.java:652)
at
org.apache.openjpa.kernel.QueryImpl.compileForCompilation(QueryImpl.java:619)
at
org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:688)
at org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:588)
at
org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:1005)
at
org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:987)
at
org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:977)


Thanks,

Xavier Baques

Software Engineer

------------------------------

<https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
<https://www.linkedin.com/company/streamsets/>

EMAIL: [hidden email]

<https://streamsets.com>

<https://streamsets.com/products/transformer/>

<https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
Reply | Threaded
Open this post in threaded view
|

Re: Delimited identifiers issue

Xavier Baqués
Hi,

Sorry to bother you again but I've been trying to find a solution for above
issue but still couldn't find a way to make it work.

Any suggestions on what I could do to make it work?


Thanks,

Xavier Baques

Software Engineer

------------------------------

<https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
<https://www.linkedin.com/company/streamsets/>

EMAIL: [hidden email]

<https://streamsets.com>

<https://streamsets.com/products/transformer/>

<https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>



On Wed, May 13, 2020 at 7:08 PM Xavier Baqués <[hidden email]> wrote:

> Hi,
>
> I am using OpenJPA with Delimited identifiers enabled through the orm.xml
> file, I am able to create the database schema and I can see debugging that
> table columns are correctly delimited with double quotes in Table object,
> however, when I ask EntityManager to create a query it loads the table
> metadata into the Table object without columns being delimited, only table
> name is delimited. As the columns in table metadata are not delimited, when
> the JPQL is parsed an exception is thrown because it cannot find delimited
> column names (they are correctly delimited from the corresponding Entity
> object) in table metadata (in this case the Table object that gets created
> from the table metadata does not have the column names delimited).
>
> Is there any way to fix that by changing the configuration and make
> Delimited identifiers work correctly?
>
> System information:
>
> OpenJPA version: 2.4.3
> MySQL version: 5.7.27
>
> Stack trace of the error:
>
> <openjpa-2.4.3-r422266:1833086 fatal user error>
> org.apache.openjpa.persistence.ArgumentException:
> "com.streamsets.apps.pipelinestore.backend.bean.TestInfo.description"
> declares column ""DESCRIPTION"", but this column does not exist in table
> ""TEST_INFO"".
> at
> org.apache.openjpa.jdbc.meta.MappingInfo.mergeColumn(MappingInfo.java:692)
> at
> org.apache.openjpa.jdbc.meta.MappingInfo.createColumns(MappingInfo.java:593)
> at
> org.apache.openjpa.jdbc.meta.ValueMappingInfo.getColumns(ValueMappingInfo.java:178)
> at
> org.apache.openjpa.jdbc.meta.strats.StringFieldStrategy.map(StringFieldStrategy.java:84)
> at
> org.apache.openjpa.jdbc.meta.FieldMapping.setStrategy(FieldMapping.java:148)
> at
> org.apache.openjpa.jdbc.meta.RuntimeStrategyInstaller.installStrategy(RuntimeStrategyInstaller.java:81)
> at
> org.apache.openjpa.jdbc.meta.FieldMapping.resolveMapping(FieldMapping.java:498)
> at org.apache.openjpa.jdbc.meta.FieldMapping.resolve(FieldMapping.java:463)
> at
> org.apache.openjpa.jdbc.meta.ClassMapping.resolveNonRelationMappings(ClassMapping.java:921)
> at
> org.apache.openjpa.jdbc.meta.ClassMapping.resolveMapping(ClassMapping.java:858)
> at org.apache.openjpa.meta.ClassMetaData.resolve(ClassMetaData.java:1813)
> at
> org.apache.openjpa.meta.MetaDataRepository.processBuffer(MetaDataRepository.java:829)
> at
> org.apache.openjpa.meta.MetaDataRepository.resolveMapping(MetaDataRepository.java:784)
> at
> org.apache.openjpa.meta.MetaDataRepository.resolve(MetaDataRepository.java:664)
> at
> org.apache.openjpa.meta.MetaDataRepository.getMetaDataInternal(MetaDataRepository.java:418)
> at
> org.apache.openjpa.meta.MetaDataRepository.getMetaData(MetaDataRepository.java:389)
> at
> org.apache.openjpa.meta.MetaDataRepository.getMetaData(MetaDataRepository.java:472)
> at
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getClassMetaData(JPQLExpressionBuilder.java:177)
> at
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.resolveClassMetaData(JPQLExpressionBuilder.java:153)
> at
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateMetaData(JPQLExpressionBuilder.java:243)
> at
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateMetaData(JPQLExpressionBuilder.java:213)
> at
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateType(JPQLExpressionBuilder.java:206)
> at
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.access$200(JPQLExpressionBuilder.java:81)
> at
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.populate(JPQLExpressionBuilder.java:2445)
> at org.apache.openjpa.kernel.jpql.JPQLParser.populate(JPQLParser.java:60)
> at
> org.apache.openjpa.kernel.ExpressionStoreQuery.populateFromCompilation(ExpressionStoreQuery.java:162)
> at org.apache.openjpa.kernel.QueryImpl.newCompilation(QueryImpl.java:679)
> at
> org.apache.openjpa.kernel.QueryImpl.compilationFromCache(QueryImpl.java:652)
> at
> org.apache.openjpa.kernel.QueryImpl.compileForCompilation(QueryImpl.java:619)
> at
> org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:688)
> at org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:588)
> at
> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:1005)
> at
> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:987)
> at
> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:977)
>
>
> Thanks,
>
> Xavier Baques
>
> Software Engineer
>
> ------------------------------
>
> <https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
> <https://www.linkedin.com/company/streamsets/>
>
> EMAIL: [hidden email]
>
> <https://streamsets.com>
>
> <https://streamsets.com/products/transformer/>
>
> <https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Delimited identifiers issue

Xavier Baqués
Hi,

Adding more context information, my use case is that I am trying to upgrade
from MySQL 5.7.27 to MySQL 8 but in MySQL 8 there are new reserved words
which collide with column names I am using on my tables. That is why I need
to be able to delimit column names.

I tried to delimit column names in both MySQL 5 and MySQL 8 and got the
same error message described in my previous email.



Thanks,

Xavier Baques

Software Engineer

------------------------------

<https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
<https://www.linkedin.com/company/streamsets/>

EMAIL: [hidden email]

<https://streamsets.com>

<https://streamsets.com/products/transformer/>

<https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>



On Tue, May 19, 2020 at 11:40 AM Xavier Baqués <[hidden email]> wrote:

> Hi,
>
> Sorry to bother you again but I've been trying to find a solution for
> above issue but still couldn't find a way to make it work.
>
> Any suggestions on what I could do to make it work?
>
>
> Thanks,
>
> Xavier Baques
>
> Software Engineer
>
> ------------------------------
>
> <https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
> <https://www.linkedin.com/company/streamsets/>
>
> EMAIL: [hidden email]
>
> <https://streamsets.com>
>
> <https://streamsets.com/products/transformer/>
>
> <https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
>
>
>
> On Wed, May 13, 2020 at 7:08 PM Xavier Baqués <[hidden email]> wrote:
>
>> Hi,
>>
>> I am using OpenJPA with Delimited identifiers enabled through the orm.xml
>> file, I am able to create the database schema and I can see debugging that
>> table columns are correctly delimited with double quotes in Table object,
>> however, when I ask EntityManager to create a query it loads the table
>> metadata into the Table object without columns being delimited, only table
>> name is delimited. As the columns in table metadata are not delimited, when
>> the JPQL is parsed an exception is thrown because it cannot find delimited
>> column names (they are correctly delimited from the corresponding Entity
>> object) in table metadata (in this case the Table object that gets created
>> from the table metadata does not have the column names delimited).
>>
>> Is there any way to fix that by changing the configuration and make
>> Delimited identifiers work correctly?
>>
>> System information:
>>
>> OpenJPA version: 2.4.3
>> MySQL version: 5.7.27
>>
>> Stack trace of the error:
>>
>> <openjpa-2.4.3-r422266:1833086 fatal user error>
>> org.apache.openjpa.persistence.ArgumentException:
>> "com.streamsets.apps.pipelinestore.backend.bean.TestInfo.description"
>> declares column ""DESCRIPTION"", but this column does not exist in table
>> ""TEST_INFO"".
>> at
>> org.apache.openjpa.jdbc.meta.MappingInfo.mergeColumn(MappingInfo.java:692)
>> at
>> org.apache.openjpa.jdbc.meta.MappingInfo.createColumns(MappingInfo.java:593)
>> at
>> org.apache.openjpa.jdbc.meta.ValueMappingInfo.getColumns(ValueMappingInfo.java:178)
>> at
>> org.apache.openjpa.jdbc.meta.strats.StringFieldStrategy.map(StringFieldStrategy.java:84)
>> at
>> org.apache.openjpa.jdbc.meta.FieldMapping.setStrategy(FieldMapping.java:148)
>> at
>> org.apache.openjpa.jdbc.meta.RuntimeStrategyInstaller.installStrategy(RuntimeStrategyInstaller.java:81)
>> at
>> org.apache.openjpa.jdbc.meta.FieldMapping.resolveMapping(FieldMapping.java:498)
>> at
>> org.apache.openjpa.jdbc.meta.FieldMapping.resolve(FieldMapping.java:463)
>> at
>> org.apache.openjpa.jdbc.meta.ClassMapping.resolveNonRelationMappings(ClassMapping.java:921)
>> at
>> org.apache.openjpa.jdbc.meta.ClassMapping.resolveMapping(ClassMapping.java:858)
>> at org.apache.openjpa.meta.ClassMetaData.resolve(ClassMetaData.java:1813)
>> at
>> org.apache.openjpa.meta.MetaDataRepository.processBuffer(MetaDataRepository.java:829)
>> at
>> org.apache.openjpa.meta.MetaDataRepository.resolveMapping(MetaDataRepository.java:784)
>> at
>> org.apache.openjpa.meta.MetaDataRepository.resolve(MetaDataRepository.java:664)
>> at
>> org.apache.openjpa.meta.MetaDataRepository.getMetaDataInternal(MetaDataRepository.java:418)
>> at
>> org.apache.openjpa.meta.MetaDataRepository.getMetaData(MetaDataRepository.java:389)
>> at
>> org.apache.openjpa.meta.MetaDataRepository.getMetaData(MetaDataRepository.java:472)
>> at
>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getClassMetaData(JPQLExpressionBuilder.java:177)
>> at
>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.resolveClassMetaData(JPQLExpressionBuilder.java:153)
>> at
>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateMetaData(JPQLExpressionBuilder.java:243)
>> at
>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateMetaData(JPQLExpressionBuilder.java:213)
>> at
>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateType(JPQLExpressionBuilder.java:206)
>> at
>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.access$200(JPQLExpressionBuilder.java:81)
>> at
>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.populate(JPQLExpressionBuilder.java:2445)
>> at org.apache.openjpa.kernel.jpql.JPQLParser.populate(JPQLParser.java:60)
>> at
>> org.apache.openjpa.kernel.ExpressionStoreQuery.populateFromCompilation(ExpressionStoreQuery.java:162)
>> at org.apache.openjpa.kernel.QueryImpl.newCompilation(QueryImpl.java:679)
>> at
>> org.apache.openjpa.kernel.QueryImpl.compilationFromCache(QueryImpl.java:652)
>> at
>> org.apache.openjpa.kernel.QueryImpl.compileForCompilation(QueryImpl.java:619)
>> at
>> org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:688)
>> at org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:588)
>> at
>> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:1005)
>> at
>> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:987)
>> at
>> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:977)
>>
>>
>> Thanks,
>>
>> Xavier Baques
>>
>> Software Engineer
>>
>> ------------------------------
>>
>> <https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
>> <https://www.linkedin.com/company/streamsets/>
>>
>> EMAIL: [hidden email]
>>
>> <https://streamsets.com>
>>
>> <https://streamsets.com/products/transformer/>
>>
>> <https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
>>
>>
Reply | Threaded
Open this post in threaded view
|

Re: Delimited identifiers issue

Mark Struberg-3
Hi Xavier!

I'd need to debug through this!

Can you probably share how you created the Entities and the database?
Do you create the DB manually or via OpenJPA?
Is there an explicit name in the @Column annotation or the orm?

In other words: for reproducing the problem we need to know pretty much exactly how your situation looks like.

Txs and LieGrue,
strub


> Am 19.05.2020 um 11:55 schrieb Xavier Baqués <[hidden email]>:
>
> Hi,
>
> Adding more context information, my use case is that I am trying to upgrade
> from MySQL 5.7.27 to MySQL 8 but in MySQL 8 there are new reserved words
> which collide with column names I am using on my tables. That is why I need
> to be able to delimit column names.
>
> I tried to delimit column names in both MySQL 5 and MySQL 8 and got the
> same error message described in my previous email.
>
>
>
> Thanks,
>
> Xavier Baques
>
> Software Engineer
>
> ------------------------------
>
> <https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
> <https://www.linkedin.com/company/streamsets/>
>
> EMAIL: [hidden email]
>
> <https://streamsets.com>
>
> <https://streamsets.com/products/transformer/>
>
> <https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
>
>
>
> On Tue, May 19, 2020 at 11:40 AM Xavier Baqués <[hidden email]> wrote:
>
>> Hi,
>>
>> Sorry to bother you again but I've been trying to find a solution for
>> above issue but still couldn't find a way to make it work.
>>
>> Any suggestions on what I could do to make it work?
>>
>>
>> Thanks,
>>
>> Xavier Baques
>>
>> Software Engineer
>>
>> ------------------------------
>>
>> <https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
>> <https://www.linkedin.com/company/streamsets/>
>>
>> EMAIL: [hidden email]
>>
>> <https://streamsets.com>
>>
>> <https://streamsets.com/products/transformer/>
>>
>> <https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
>>
>>
>>
>> On Wed, May 13, 2020 at 7:08 PM Xavier Baqués <[hidden email]> wrote:
>>
>>> Hi,
>>>
>>> I am using OpenJPA with Delimited identifiers enabled through the orm.xml
>>> file, I am able to create the database schema and I can see debugging that
>>> table columns are correctly delimited with double quotes in Table object,
>>> however, when I ask EntityManager to create a query it loads the table
>>> metadata into the Table object without columns being delimited, only table
>>> name is delimited. As the columns in table metadata are not delimited, when
>>> the JPQL is parsed an exception is thrown because it cannot find delimited
>>> column names (they are correctly delimited from the corresponding Entity
>>> object) in table metadata (in this case the Table object that gets created
>>> from the table metadata does not have the column names delimited).
>>>
>>> Is there any way to fix that by changing the configuration and make
>>> Delimited identifiers work correctly?
>>>
>>> System information:
>>>
>>> OpenJPA version: 2.4.3
>>> MySQL version: 5.7.27
>>>
>>> Stack trace of the error:
>>>
>>> <openjpa-2.4.3-r422266:1833086 fatal user error>
>>> org.apache.openjpa.persistence.ArgumentException:
>>> "com.streamsets.apps.pipelinestore.backend.bean.TestInfo.description"
>>> declares column ""DESCRIPTION"", but this column does not exist in table
>>> ""TEST_INFO"".
>>> at
>>> org.apache.openjpa.jdbc.meta.MappingInfo.mergeColumn(MappingInfo.java:692)
>>> at
>>> org.apache.openjpa.jdbc.meta.MappingInfo.createColumns(MappingInfo.java:593)
>>> at
>>> org.apache.openjpa.jdbc.meta.ValueMappingInfo.getColumns(ValueMappingInfo.java:178)
>>> at
>>> org.apache.openjpa.jdbc.meta.strats.StringFieldStrategy.map(StringFieldStrategy.java:84)
>>> at
>>> org.apache.openjpa.jdbc.meta.FieldMapping.setStrategy(FieldMapping.java:148)
>>> at
>>> org.apache.openjpa.jdbc.meta.RuntimeStrategyInstaller.installStrategy(RuntimeStrategyInstaller.java:81)
>>> at
>>> org.apache.openjpa.jdbc.meta.FieldMapping.resolveMapping(FieldMapping.java:498)
>>> at
>>> org.apache.openjpa.jdbc.meta.FieldMapping.resolve(FieldMapping.java:463)
>>> at
>>> org.apache.openjpa.jdbc.meta.ClassMapping.resolveNonRelationMappings(ClassMapping.java:921)
>>> at
>>> org.apache.openjpa.jdbc.meta.ClassMapping.resolveMapping(ClassMapping.java:858)
>>> at org.apache.openjpa.meta.ClassMetaData.resolve(ClassMetaData.java:1813)
>>> at
>>> org.apache.openjpa.meta.MetaDataRepository.processBuffer(MetaDataRepository.java:829)
>>> at
>>> org.apache.openjpa.meta.MetaDataRepository.resolveMapping(MetaDataRepository.java:784)
>>> at
>>> org.apache.openjpa.meta.MetaDataRepository.resolve(MetaDataRepository.java:664)
>>> at
>>> org.apache.openjpa.meta.MetaDataRepository.getMetaDataInternal(MetaDataRepository.java:418)
>>> at
>>> org.apache.openjpa.meta.MetaDataRepository.getMetaData(MetaDataRepository.java:389)
>>> at
>>> org.apache.openjpa.meta.MetaDataRepository.getMetaData(MetaDataRepository.java:472)
>>> at
>>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getClassMetaData(JPQLExpressionBuilder.java:177)
>>> at
>>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.resolveClassMetaData(JPQLExpressionBuilder.java:153)
>>> at
>>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateMetaData(JPQLExpressionBuilder.java:243)
>>> at
>>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateMetaData(JPQLExpressionBuilder.java:213)
>>> at
>>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateType(JPQLExpressionBuilder.java:206)
>>> at
>>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.access$200(JPQLExpressionBuilder.java:81)
>>> at
>>> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.populate(JPQLExpressionBuilder.java:2445)
>>> at org.apache.openjpa.kernel.jpql.JPQLParser.populate(JPQLParser.java:60)
>>> at
>>> org.apache.openjpa.kernel.ExpressionStoreQuery.populateFromCompilation(ExpressionStoreQuery.java:162)
>>> at org.apache.openjpa.kernel.QueryImpl.newCompilation(QueryImpl.java:679)
>>> at
>>> org.apache.openjpa.kernel.QueryImpl.compilationFromCache(QueryImpl.java:652)
>>> at
>>> org.apache.openjpa.kernel.QueryImpl.compileForCompilation(QueryImpl.java:619)
>>> at
>>> org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:688)
>>> at org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:588)
>>> at
>>> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:1005)
>>> at
>>> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:987)
>>> at
>>> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:977)
>>>
>>>
>>> Thanks,
>>>
>>> Xavier Baques
>>>
>>> Software Engineer
>>>
>>> ------------------------------
>>>
>>> <https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
>>> <https://www.linkedin.com/company/streamsets/>
>>>
>>> EMAIL: [hidden email]
>>>
>>> <https://streamsets.com>
>>>
>>> <https://streamsets.com/products/transformer/>
>>>
>>> <https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
>>>
>>>

Reply | Threaded
Open this post in threaded view
|

Re: Delimited identifiers issue

Xavier Baqués
Hi Mark,

We create DB tables through OpenJPA. I have been working on an example to
share with you in order to help you debug this issue. After some testing I
have noticed that identifiers are not correctly delimited when
setting <property name="openjpa.jdbc.SchemaFactory"
value="native(ForeignKeys=true)"/> in the persistence.xml. See below the
code to reproduce the issue:

Test class:

package com.test;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Id;
import javax.persistence.Persistence;
import javax.persistence.Table;
import javax.persistence.TypedQuery;
import java.util.Properties;

public class TestX {

  private static final boolean CREATE_TABLE = true;
  private static final boolean INSERT_DATA = true;

  @Entity(name = "EBean")
  @Table(name = "EBEAN")
  public static class EBean {
    @Id
    @Column(name = "SYSTEM")
    private boolean system;

    public Boolean getSystem() {
      return system;
    }

    public EBean setSystem(Boolean system) {
      this.system = system;
      return this;
    }
  }

  private static Properties getConfig() {
    Properties config = new Properties();
    config.setProperty("openjpa.ConnectionDriverName", "com.mysql.jdbc.Driver");
    config.setProperty("openjpa.ConnectionURL",
"jdbc:mysql://localhost:3306/test?createDatabaseIfNotExist=true");
    config.setProperty("openjpa.ConnectionUserName", "root");
    config.setProperty("openjpa.ConnectionPassword", "root");
    config.setProperty("openjpa.RuntimeUnenhancedClasses", "supported");
    return config;
  }

  private static EntityManagerFactory
createEntityManagerFactory(Properties config) {
    return Persistence.createEntityManagerFactory("test", config);
  }

  public static void main(String[] args) {
    EntityManagerFactory emf;
    if (CREATE_TABLE) {
      // create db schema
      Properties createSchemaConfig = getConfig();
      createSchemaConfig.setProperty("openjpa.jdbc.SynchronizeMappings",
"buildSchema");
      emf = createEntityManagerFactory(createSchemaConfig);
      try {
        EntityManager em = emf.createEntityManager();
        em.close();
      } finally {
        emf.close();
      }
    }
    // access db
    emf = createEntityManagerFactory(getConfig());
    try {
      EntityManager em = emf.createEntityManager();
      try {
        if (INSERT_DATA) {
          // start transaction
          em.getTransaction().begin();
          // insert EBean 1
          EBean eBean1 = new EBean();
          eBean1.setSystem(true);
          em.persist(eBean1);
          // insert EBean 2
          EBean eBean2 = new EBean();
          eBean2.setSystem(false);
          em.persist(eBean2);
          // end transaction
          em.getTransaction().commit();
        }
        // perform select query
        TypedQuery<EBean> query = em.createQuery("SELECT b from EBean
b where b.system = :s", EBean.class);
        query.setParameter("s", true);
        int counter = 0;
        for (EBean eBeanResult : query.getResultList()) {
          System.out.println("EBEAN [" + counter + "]: SYSTEM = " +
eBeanResult.getSystem());
          ++counter;
        }

      } finally {
        em.close();
      }
    } finally {
      emf.close();
    }
  }
}

persistence.xml:

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_2_1.xsd"
             version="1.0">
  <persistence-unit name="test" transaction-type="RESOURCE_LOCAL">
    <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
    <class>com.test.TestX$EBean</class>
    <exclude-unlisted-classes>true</exclude-unlisted-classes>

    <properties>
      <!-- Below config is the one making delimited identifiers NOT
work!! If below config is removed, delimited identifiers works fine-->
      <property name="openjpa.jdbc.SchemaFactory"
value="native(ForeignKeys=true)"/>

    </properties>
  </persistence-unit>
</persistence>

orm.xml:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm
http://java.sun.com/xml/ns/persistence/orm_2_0.xsd" version="2.0">
  <persistence-unit-metadata>
    <persistence-unit-defaults>
      <delimited-identifiers/>
    </persistence-unit-defaults>
  </persistence-unit-metadata>
</entity-mappings>


Is there a way to make delimited identifiers work when config <property
name="openjpa.jdbc.SchemaFactory" value="native(ForeignKeys=true)"/> is
used?


Thanks,

Xavier Baques

Software Engineer

------------------------------

<https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
<https://www.linkedin.com/company/streamsets/>

EMAIL: [hidden email]

<https://streamsets.com>

<https://streamsets.com/products/transformer/>

<https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>



On Tue, May 19, 2020 at 8:22 PM Mark Struberg <[hidden email]>
wrote:

> Hi Xavier!
>
> I'd need to debug through this!
>
> Can you probably share how you created the Entities and the database?
> Do you create the DB manually or via OpenJPA?
> Is there an explicit name in the @Column annotation or the orm?
>
> In other words: for reproducing the problem we need to know pretty much
> exactly how your situation looks like.
>
> Txs and LieGrue,
> strub
>
>
> > Am 19.05.2020 um 11:55 schrieb Xavier Baqués <[hidden email]
> >:
> >
> > Hi,
> >
> > Adding more context information, my use case is that I am trying to
> upgrade
> > from MySQL 5.7.27 to MySQL 8 but in MySQL 8 there are new reserved words
> > which collide with column names I am using on my tables. That is why I
> need
> > to be able to delimit column names.
> >
> > I tried to delimit column names in both MySQL 5 and MySQL 8 and got the
> > same error message described in my previous email.
> >
> >
> >
> > Thanks,
> >
> > Xavier Baques
> >
> > Software Engineer
> >
> > ------------------------------
> >
> > <https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
> > <https://www.linkedin.com/company/streamsets/>
> >
> > EMAIL: [hidden email]
> >
> > <https://streamsets.com>
> >
> > <https://streamsets.com/products/transformer/>
> >
> > <https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
> >
> >
> >
> > On Tue, May 19, 2020 at 11:40 AM Xavier Baqués <[hidden email]>
> wrote:
> >
> >> Hi,
> >>
> >> Sorry to bother you again but I've been trying to find a solution for
> >> above issue but still couldn't find a way to make it work.
> >>
> >> Any suggestions on what I could do to make it work?
> >>
> >>
> >> Thanks,
> >>
> >> Xavier Baques
> >>
> >> Software Engineer
> >>
> >> ------------------------------
> >>
> >> <https://www.facebook.com/streamsets/> <https://twitter.com/streamsets>
> >> <https://www.linkedin.com/company/streamsets/>
> >>
> >> EMAIL: [hidden email]
> >>
> >> <https://streamsets.com>
> >>
> >> <https://streamsets.com/products/transformer/>
> >>
> >> <https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
> >>
> >>
> >>
> >> On Wed, May 13, 2020 at 7:08 PM Xavier Baqués <[hidden email]>
> wrote:
> >>
> >>> Hi,
> >>>
> >>> I am using OpenJPA with Delimited identifiers enabled through the
> orm.xml
> >>> file, I am able to create the database schema and I can see debugging
> that
> >>> table columns are correctly delimited with double quotes in Table
> object,
> >>> however, when I ask EntityManager to create a query it loads the table
> >>> metadata into the Table object without columns being delimited, only
> table
> >>> name is delimited. As the columns in table metadata are not delimited,
> when
> >>> the JPQL is parsed an exception is thrown because it cannot find
> delimited
> >>> column names (they are correctly delimited from the corresponding
> Entity
> >>> object) in table metadata (in this case the Table object that gets
> created
> >>> from the table metadata does not have the column names delimited).
> >>>
> >>> Is there any way to fix that by changing the configuration and make
> >>> Delimited identifiers work correctly?
> >>>
> >>> System information:
> >>>
> >>> OpenJPA version: 2.4.3
> >>> MySQL version: 5.7.27
> >>>
> >>> Stack trace of the error:
> >>>
> >>> <openjpa-2.4.3-r422266:1833086 fatal user error>
> >>> org.apache.openjpa.persistence.ArgumentException:
> >>> "com.streamsets.apps.pipelinestore.backend.bean.TestInfo.description"
> >>> declares column ""DESCRIPTION"", but this column does not exist in
> table
> >>> ""TEST_INFO"".
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.MappingInfo.mergeColumn(MappingInfo.java:692)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.MappingInfo.createColumns(MappingInfo.java:593)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.ValueMappingInfo.getColumns(ValueMappingInfo.java:178)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.strats.StringFieldStrategy.map(StringFieldStrategy.java:84)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.FieldMapping.setStrategy(FieldMapping.java:148)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.RuntimeStrategyInstaller.installStrategy(RuntimeStrategyInstaller.java:81)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.FieldMapping.resolveMapping(FieldMapping.java:498)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.FieldMapping.resolve(FieldMapping.java:463)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.ClassMapping.resolveNonRelationMappings(ClassMapping.java:921)
> >>> at
> >>>
> org.apache.openjpa.jdbc.meta.ClassMapping.resolveMapping(ClassMapping.java:858)
> >>> at
> org.apache.openjpa.meta.ClassMetaData.resolve(ClassMetaData.java:1813)
> >>> at
> >>>
> org.apache.openjpa.meta.MetaDataRepository.processBuffer(MetaDataRepository.java:829)
> >>> at
> >>>
> org.apache.openjpa.meta.MetaDataRepository.resolveMapping(MetaDataRepository.java:784)
> >>> at
> >>>
> org.apache.openjpa.meta.MetaDataRepository.resolve(MetaDataRepository.java:664)
> >>> at
> >>>
> org.apache.openjpa.meta.MetaDataRepository.getMetaDataInternal(MetaDataRepository.java:418)
> >>> at
> >>>
> org.apache.openjpa.meta.MetaDataRepository.getMetaData(MetaDataRepository.java:389)
> >>> at
> >>>
> org.apache.openjpa.meta.MetaDataRepository.getMetaData(MetaDataRepository.java:472)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getClassMetaData(JPQLExpressionBuilder.java:177)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.resolveClassMetaData(JPQLExpressionBuilder.java:153)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateMetaData(JPQLExpressionBuilder.java:243)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateMetaData(JPQLExpressionBuilder.java:213)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.getCandidateType(JPQLExpressionBuilder.java:206)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder.access$200(JPQLExpressionBuilder.java:81)
> >>> at
> >>>
> org.apache.openjpa.kernel.jpql.JPQLExpressionBuilder$ParsedJPQL.populate(JPQLExpressionBuilder.java:2445)
> >>> at
> org.apache.openjpa.kernel.jpql.JPQLParser.populate(JPQLParser.java:60)
> >>> at
> >>>
> org.apache.openjpa.kernel.ExpressionStoreQuery.populateFromCompilation(ExpressionStoreQuery.java:162)
> >>> at
> org.apache.openjpa.kernel.QueryImpl.newCompilation(QueryImpl.java:679)
> >>> at
> >>>
> org.apache.openjpa.kernel.QueryImpl.compilationFromCache(QueryImpl.java:652)
> >>> at
> >>>
> org.apache.openjpa.kernel.QueryImpl.compileForCompilation(QueryImpl.java:619)
> >>> at
> >>>
> org.apache.openjpa.kernel.QueryImpl.compileForExecutor(QueryImpl.java:688)
> >>> at org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:588)
> >>> at
> >>>
> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:1005)
> >>> at
> >>>
> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:987)
> >>> at
> >>>
> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerImpl.java:977)
> >>>
> >>>
> >>> Thanks,
> >>>
> >>> Xavier Baques
> >>>
> >>> Software Engineer
> >>>
> >>> ------------------------------
> >>>
> >>> <https://www.facebook.com/streamsets/> <https://twitter.com/streamsets
> >
> >>> <https://www.linkedin.com/company/streamsets/>
> >>>
> >>> EMAIL: [hidden email]
> >>>
> >>> <https://streamsets.com>
> >>>
> >>> <https://streamsets.com/products/transformer/>
> >>>
> >>> <https://go.streamsets.com/WC-2019-Book-DataOps-Full-Book_LP.html>
> >>>
> >>>
>
>