You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by guillaume <gu...@pymma-software.com> on 2018/03/01 09:41:52 UTC
Apache ignit : Syntax error in SQL statement create table
I am looking to index my data apache ignite to then use spring data ignite.
But I have a problem when launching my project; ignite can not create the
sql table.
I thought it could be a mistake in the property names but I varied the
indexed properties and I always got the same error.
I use a database mongoDb, is that compatible with this feature?
org.apache.ignite.IgniteCheckedException: Failed to register query type:
QueryTypeDescriptorImpl [cacheName=CacheVehiculeImmat, name=VehiculeImmat,
schemaName=CacheVehiculeImmat, tblName=VEHICULEIMMAT, fields={ID=class
java.lang.String, OBJECTID=class java.lang.String},
idxs={VEHICULEIMMAT_OBJECTID_IDX=QueryIndexDescriptorImpl
[name=VEHICULEIMMAT_OBJECTID_IDX, type=SORTED, inlineSize=-1],
VEHICULEIMMAT_ID_IDX=QueryIndexDescriptorImpl [name=VEHICULEIMMAT_ID_IDX,
type=SORTED, inlineSize=-1]}, fullTextIdx=null, keyCls=class
java.lang.String, valCls=class java.lang.Object,
keyTypeName=java.lang.String,
valTypeName=com.persist.petiteflotte.model.VehiculeImmat, valTextIdx=false,
typeId=51183916, affKey=null, keyFieldName=null, valFieldName=null,
obsolete=false]
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.registerType(IgniteH2Indexing.java:1709)
~[ignite-indexing-2.3.0.jar:2.3.0]
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.registerCache0(GridQueryProcessor.java:1512)
~[ignite-core-2.3.0.jar:2.3.0]
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.onCacheStart0(GridQueryProcessor.java:779)
~[ignite-core-2.3.0.jar:2.3.0]
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.onCacheStart(GridQueryProcessor.java:840)
~[ignite-core-2.3.0.jar:2.3.0]
at
org.apache.ignite.internal.processors.cache.GridCacheProcessor.startCache(GridCacheProcessor.java:1113)
~[ignite-core-2.3.0.jar:2.3.0]
at
org.apache.ignite.internal.processors.cache.GridCacheProcessor.prepareCacheStart(GridCacheProcessor.java:1816)
~[ignite-core-2.3.0.jar:2.3.0]
at
org.apache.ignite.internal.processors.cache.GridCacheProcessor.startCachesOnLocalJoin(GridCacheProcessor.java:1681)
~[ignite-core-2.3.0.jar:2.3.0]
at
org.apache.ignite.internal.processors.cache.distributed.dht.preloader.GridDhtPartitionsExchangeFuture.initCachesOnLocalJoin(GridDhtPartitionsExchangeFuture.java:725)
~[ignite-core-2.3.0.jar:2.3.0]
at
org.apache.ignite.internal.processors.cache.distributed.dht.preloader.GridDhtPartitionsExchangeFuture.init(GridDhtPartitionsExchangeFuture.java:613)
~[ignite-core-2.3.0.jar:2.3.0]
at
org.apache.ignite.internal.processors.cache.GridCachePartitionExchangeManager$ExchangeWorker.body(GridCachePartitionExchangeManager.java:2279)
~[ignite-core-2.3.0.jar:2.3.0]
at
org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
[ignite-core-2.3.0.jar:2.3.0]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]
Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement
"CREATE TABLE ""CacheVehiculeImmat"".""VEHICULEIMMAT"" (_KEY VARCHAR
INVISIBLE[*] NOT NULL,_VAL OTHER INVISIBLE,_VER OTHER INVISIBLE,""ID""
VARCHAR,""OBJECTID"" VARCHAR) ENGINE
""org.apache.ignite.internal.processors.query.h2.H2TableEngine"" "; expected
"(, FOR, UNSIGNED, NOT, NULL, AS, DEFAULT, GENERATED, NOT, NULL,
AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE,
SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK,
REFERENCES, ,, )"; SQL statement:
CREATE TABLE "CacheVehiculeImmat"."VEHICULEIMMAT" (_KEY VARCHAR INVISIBLE
NOT NULL,_VAL OTHER INVISIBLE,_VER OTHER INVISIBLE,"ID" VARCHAR,"OBJECTID"
VARCHAR) engine
"org.apache.ignite.internal.processors.query.h2.H2TableEngine" [42001-193]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
~[h2-1.4.193.jar:1.4.193]
at org.h2.message.DbException.getSyntaxError(DbException.java:205)
~[h2-1.4.193.jar:1.4.193]
at org.h2.command.Parser.getSyntaxError(Parser.java:537)
~[h2-1.4.193.jar:1.4.193]
at org.h2.command.Parser.read(Parser.java:3186)
~[h2-1.4.193.jar:1.4.193]
at org.h2.command.Parser.readIfMore(Parser.java:885)
~[h2-1.4.193.jar:1.4.193]
at org.h2.command.Parser.parseCreateTable(Parser.java:6043)
~[h2-1.4.193.jar:1.4.193]
at org.h2.command.Parser.parseCreate(Parser.java:4238)
~[h2-1.4.193.jar:1.4.193]
at org.h2.command.Parser.parsePrepared(Parser.java:362)
~[h2-1.4.193.jar:1.4.193]
at org.h2.command.Parser.parse(Parser.java:317)
~[h2-1.4.193.jar:1.4.193]
at org.h2.command.Parser.parse(Parser.java:293)
~[h2-1.4.193.jar:1.4.193]
at org.h2.command.Parser.prepareCommand(Parser.java:254)
~[h2-1.4.193.jar:1.4.193]
at org.h2.engine.Session.prepareLocal(Session.java:561)
~[h2-1.4.193.jar:1.4.193]
at org.h2.engine.Session.prepareCommand(Session.java:502)
~[h2-1.4.193.jar:1.4.193]
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1203)
~[h2-1.4.193.jar:1.4.193]
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:170)
~[h2-1.4.193.jar:1.4.193]
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)
~[h2-1.4.193.jar:1.4.193]
at
org.apache.ignite.internal.processors.query.h2.H2TableEngine.createTable(H2TableEngine.java:65)
~[ignite-indexing-2.3.0.jar:2.3.0]
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.createTable(IgniteH2Indexing.java:1784)
~[ignite-indexing-2.3.0.jar:2.3.0]
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.registerType(IgniteH2Indexing.java:1702)
~[ignite-indexing-2.3.0.jar:2.3.0]
... 11 common frames omitted
Here my code
CacheConfiguration cacheVehiculeImmat = new CacheConfiguration();
cacheVehiculeImmat.setName(CacheHash.CACHEVEHICULE);
cacheVehiculeImmat.setAtomicityMode(CacheAtomicityMode.TRANSACTIONAL);
cacheVehiculeImmat.setReadThrough(true);
cacheVehiculeImmat.setIndexedTypes(String.class, VehiculeImmat.class);
cacheVehiculeImmat.setWriteThrough(true);
cacheVehiculeImmat.setStoreKeepBinary(false);
cacheVehiculeImmat.setWriteBehindEnabled(true);
cacheVehiculeImmat.setCacheMode(CacheMode.REPLICATED);
Factory<SpringDataVehiculeDataStore> storeDataVehicule =
FactoryBuilder.factoryOf(SpringDataVehiculeDataStore.class);
cacheVehiculeImmat.setCacheStoreFactory(storeDataVehicule);
my model
@Document(collection = "vehicule")
public class VehiculeImmat implements Serializable{
@Id
private String id=new ObjectId().toString();
@QuerySqlField(index = true)
private String objectId = UUID.randomUUID().toString();
}
and my datastore
public class SpringDataVehiculeDataStore extends CacheStoreAdapter<String,
VehiculeImmat> implements LifecycleAware {
private VehiculeRepository vehiculeRepository = null;
@Override
public VehiculeImmat load(String immat) throws CacheLoaderException {
VehiculeImmat courtierEnterprise =
vehiculeRepository.findByImmat(immat);
return courtierEnterprise;
}
@Override
public void write(Cache.Entry<? extends String, ? extends VehiculeImmat>
entry) throws CacheWriterException {
VehiculeImmat vehiculeImmat = entry.getValue();
vehiculeRepository.save(vehiculeImmat);
}
@Override
public void delete(Object o) throws CacheWriterException {
VehiculeImmat vehiculeImmat =
vehiculeRepository.findByImmat(String.valueOf(o));
vehiculeRepository.delete(vehiculeImmat);
}
@Override
public void start() throws IgniteException {
vehiculeRepository =
AppContext.getApplicationContext().getBean(VehiculeRepository.class);
}
public void loadCache(IgniteBiInClosure<String,VehiculeImmat> clo,
Object... args){
for(VehiculeImmat vehiculeImmat:vehiculeRepository.findAll()) {
clo.apply(vehiculeImmat.getImmat(),vehiculeImmat);
}
}
@Override
public void stop() throws IgniteException {
}
}
Do you have an idea of the problem?
UPDATE : on Stackoverflow
https://stackoverflow.com/questions/49034103/apache-ignit-syntax-error-in-sql-statement-create-table?noredirect=1#comment85073296_49034103
It's probably complaining about the [*] following INVISIBLE, because that is
not valid according to the H2 syntax
> But how to remove that character, I never add it myself?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Apache ignit : Syntax error in SQL statement create table
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello Guillaume!
Please share your pom.xml (or other source of dependencies) since something
is amiss here. In my projects h2 dependency is of proper version 1.4.195.
Thanks,
--
Ilya Kasnacheev
2018-03-05 16:51 GMT+03:00 guillaume <gu...@pymma-software.com>:
> Hello,
>
> thx you for your response.
>
> I checked my dependency tree. What I do not understand is why apache-ignite
> indexing 2.3.0 contains h2 1.4.193 if they are incompatible. I share a
> screen with you to illustrate my point.
> I use maven.
>
> <http://apache-ignite-users.70518.x6.nabble.com/file/
> t1656/Capture_du_2018-03-05_14-45-24.png>
>
> I also tested to exclude h2 1.4.193 from apache-ignite-indexing and to add
> 1.4.195 to my project and I get the following error:
>
> java.lang.ClassNotFoundException: org.h2.result.RowFactory
>
> Regards,
>
> Hochart Guillaume
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>
Re: Apache ignit : Syntax error in SQL statement create table
Posted by guillaume <gu...@pymma-software.com>.
Hello,
thx you for your response.
I checked my dependency tree. What I do not understand is why apache-ignite
indexing 2.3.0 contains h2 1.4.193 if they are incompatible. I share a
screen with you to illustrate my point.
I use maven.
<http://apache-ignite-users.70518.x6.nabble.com/file/t1656/Capture_du_2018-03-05_14-45-24.png>
I also tested to exclude h2 1.4.193 from apache-ignite-indexing and to add
1.4.195 to my project and I get the following error:
java.lang.ClassNotFoundException: org.h2.result.RowFactory
Regards,
Hochart Guillaume
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Apache ignit : Syntax error in SQL statement create table
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
As far as I can see, Apache Ignite 2.3 expects H2 of version 1.4.195. In
your case, I can see that H2 jar is of version 1.4.193. This may cause
mismatch between Apache Ignite and H2.
I recommend looking at your dependency graph (or jar pile), making sure
that only the correct H2 version is present.
Regards,
--
Ilya Kasnacheev
2018-03-01 12:41 GMT+03:00 guillaume <gu...@pymma-software.com>:
> I am looking to index my data apache ignite to then use spring data ignite.
> But I have a problem when launching my project; ignite can not create the
> sql table.
>
> I thought it could be a mistake in the property names but I varied the
> indexed properties and I always got the same error.
>
> I use a database mongoDb, is that compatible with this feature?
>
> org.apache.ignite.IgniteCheckedException: Failed to register query type:
> QueryTypeDescriptorImpl [cacheName=CacheVehiculeImmat, name=VehiculeImmat,
> schemaName=CacheVehiculeImmat, tblName=VEHICULEIMMAT, fields={ID=class
> java.lang.String, OBJECTID=class java.lang.String},
> idxs={VEHICULEIMMAT_OBJECTID_IDX=QueryIndexDescriptorImpl
> [name=VEHICULEIMMAT_OBJECTID_IDX, type=SORTED, inlineSize=-1],
> VEHICULEIMMAT_ID_IDX=QueryIndexDescriptorImpl [name=VEHICULEIMMAT_ID_IDX,
> type=SORTED, inlineSize=-1]}, fullTextIdx=null, keyCls=class
> java.lang.String, valCls=class java.lang.Object,
> keyTypeName=java.lang.String,
> valTypeName=com.persist.petiteflotte.model.VehiculeImmat,
> valTextIdx=false,
> typeId=51183916, affKey=null, keyFieldName=null, valFieldName=null,
> obsolete=false]
> at
> org.apache.ignite.internal.processors.query.h2.
> IgniteH2Indexing.registerType(IgniteH2Indexing.java:1709)
> ~[ignite-indexing-2.3.0.jar:2.3.0]
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor.
> registerCache0(GridQueryProcessor.java:1512)
> ~[ignite-core-2.3.0.jar:2.3.0]
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor.
> onCacheStart0(GridQueryProcessor.java:779)
> ~[ignite-core-2.3.0.jar:2.3.0]
> at
> org.apache.ignite.internal.processors.query.GridQueryProcessor.
> onCacheStart(GridQueryProcessor.java:840)
> ~[ignite-core-2.3.0.jar:2.3.0]
> at
> org.apache.ignite.internal.processors.cache.GridCacheProcessor.startCache(
> GridCacheProcessor.java:1113)
> ~[ignite-core-2.3.0.jar:2.3.0]
> at
> org.apache.ignite.internal.processors.cache.GridCacheProcessor.
> prepareCacheStart(GridCacheProcessor.java:1816)
> ~[ignite-core-2.3.0.jar:2.3.0]
> at
> org.apache.ignite.internal.processors.cache.GridCacheProcessor.
> startCachesOnLocalJoin(GridCacheProcessor.java:1681)
> ~[ignite-core-2.3.0.jar:2.3.0]
> at
> org.apache.ignite.internal.processors.cache.distributed.dht.preloader.
> GridDhtPartitionsExchangeFuture.initCachesOnLocalJoin(
> GridDhtPartitionsExchangeFuture.java:725)
> ~[ignite-core-2.3.0.jar:2.3.0]
> at
> org.apache.ignite.internal.processors.cache.distributed.dht.preloader.
> GridDhtPartitionsExchangeFuture.init(GridDhtPartitionsExchangeFutur
> e.java:613)
> ~[ignite-core-2.3.0.jar:2.3.0]
> at
> org.apache.ignite.internal.processors.cache.GridCachePartitionExchangeMana
> ger$ExchangeWorker.body(GridCachePartitionExchangeManager.java:2279)
> ~[ignite-core-2.3.0.jar:2.3.0]
> at
> org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
> [ignite-core-2.3.0.jar:2.3.0]
> at java.lang.Thread.run(Thread.java:748) [na:1.8.0_151]
> Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement
> "CREATE TABLE ""CacheVehiculeImmat"".""VEHICULEIMMAT"" (_KEY VARCHAR
> INVISIBLE[*] NOT NULL,_VAL OTHER INVISIBLE,_VER OTHER INVISIBLE,""ID""
> VARCHAR,""OBJECTID"" VARCHAR) ENGINE
> ""org.apache.ignite.internal.processors.query.h2.H2TableEngine"" ";
> expected
> "(, FOR, UNSIGNED, NOT, NULL, AS, DEFAULT, GENERATED, NOT, NULL,
> AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE,
> SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK,
> REFERENCES, ,, )"; SQL statement:
> CREATE TABLE "CacheVehiculeImmat"."VEHICULEIMMAT" (_KEY VARCHAR INVISIBLE
> NOT NULL,_VAL OTHER INVISIBLE,_VER OTHER INVISIBLE,"ID" VARCHAR,"OBJECTID"
> VARCHAR) engine
> "org.apache.ignite.internal.processors.query.h2.H2TableEngine" [42001-193]
> at org.h2.message.DbException.getJdbcSQLException(
> DbException.java:345)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.message.DbException.getSyntaxError(DbException.java:205)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.command.Parser.getSyntaxError(Parser.java:537)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.command.Parser.read(Parser.java:3186)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.command.Parser.readIfMore(Parser.java:885)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.command.Parser.parseCreateTable(Parser.java:6043)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.command.Parser.parseCreate(Parser.java:4238)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.command.Parser.parsePrepared(Parser.java:362)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.command.Parser.parse(Parser.java:317)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.command.Parser.parse(Parser.java:293)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.command.Parser.prepareCommand(Parser.java:254)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.engine.Session.prepareLocal(Session.java:561)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.engine.Session.prepareCommand(Session.java:502)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1203)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:170)
> ~[h2-1.4.193.jar:1.4.193]
> at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)
> ~[h2-1.4.193.jar:1.4.193]
> at
> org.apache.ignite.internal.processors.query.h2.H2TableEngine.createTable(
> H2TableEngine.java:65)
> ~[ignite-indexing-2.3.0.jar:2.3.0]
> at
> org.apache.ignite.internal.processors.query.h2.
> IgniteH2Indexing.createTable(IgniteH2Indexing.java:1784)
> ~[ignite-indexing-2.3.0.jar:2.3.0]
> at
> org.apache.ignite.internal.processors.query.h2.
> IgniteH2Indexing.registerType(IgniteH2Indexing.java:1702)
> ~[ignite-indexing-2.3.0.jar:2.3.0]
> ... 11 common frames omitted
>
> Here my code
>
> CacheConfiguration cacheVehiculeImmat = new CacheConfiguration();
> cacheVehiculeImmat.setName(CacheHash.CACHEVEHICULE);
> cacheVehiculeImmat.setAtomicityMode(CacheAtomicityMode.TRANSACTIONAL);
> cacheVehiculeImmat.setReadThrough(true);
> cacheVehiculeImmat.setIndexedTypes(String.class, VehiculeImmat.class);
> cacheVehiculeImmat.setWriteThrough(true);
> cacheVehiculeImmat.setStoreKeepBinary(false);
> cacheVehiculeImmat.setWriteBehindEnabled(true);
> cacheVehiculeImmat.setCacheMode(CacheMode.REPLICATED);
> Factory<SpringDataVehiculeDataStore> storeDataVehicule =
> FactoryBuilder.factoryOf(SpringDataVehiculeDataStore.class);
> cacheVehiculeImmat.setCacheStoreFactory(storeDataVehicule);
>
> my model
>
> @Document(collection = "vehicule")
> public class VehiculeImmat implements Serializable{
>
>
> @Id
> private String id=new ObjectId().toString();
>
> @QuerySqlField(index = true)
> private String objectId = UUID.randomUUID().toString();
> }
>
> and my datastore
>
> public class SpringDataVehiculeDataStore extends CacheStoreAdapter<String,
> VehiculeImmat> implements LifecycleAware {
>
> private VehiculeRepository vehiculeRepository = null;
>
> @Override
> public VehiculeImmat load(String immat) throws CacheLoaderException {
> VehiculeImmat courtierEnterprise =
> vehiculeRepository.findByImmat(immat);
> return courtierEnterprise;
> }
>
> @Override
> public void write(Cache.Entry<? extends String, ? extends
> VehiculeImmat>
> entry) throws CacheWriterException {
> VehiculeImmat vehiculeImmat = entry.getValue();
> vehiculeRepository.save(vehiculeImmat);
> }
>
>
> @Override
> public void delete(Object o) throws CacheWriterException {
> VehiculeImmat vehiculeImmat =
> vehiculeRepository.findByImmat(String.valueOf(o));
> vehiculeRepository.delete(vehiculeImmat);
> }
>
> @Override
> public void start() throws IgniteException {
> vehiculeRepository =
> AppContext.getApplicationContext().getBean(VehiculeRepository.class);
> }
> public void loadCache(IgniteBiInClosure<String,VehiculeImmat> clo,
> Object... args){
> for(VehiculeImmat vehiculeImmat:vehiculeRepository.findAll()) {
> clo.apply(vehiculeImmat.getImmat(),vehiculeImmat);
> }
> }
>
> @Override
> public void stop() throws IgniteException {
>
> }
> }
>
> Do you have an idea of the problem?
>
> UPDATE : on Stackoverflow
> https://stackoverflow.com/questions/49034103/apache-
> ignit-syntax-error-in-sql-statement-create-table?
> noredirect=1#comment85073296_49034103
>
> It's probably complaining about the [*] following INVISIBLE, because that
> is
> not valid according to the H2 syntax
> > But how to remove that character, I never add it myself?
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>