Query regarding vertica setup

Kanishka

Hi There,
We recently got the setup for vertica(9.2.1) setup and running.
We are doing manual data copy from kafka for piece loading data into vertica.
Version:
1. Kafka: 2.11-0.10
2. Vertica: 9.2.1 (EON Mode)
3. Java code doing the manual copy from kafka to vertica: 1.8
It worked perfectly fine in our local mac environment with data getting copied from kafka to vertica but when deployed to the GAMMA environment there are issue encountered
Stack Trace:
2019-09-04 16:14:48.800 [pool-2-thread-1] [pool-2-thread-1] ERROR in.vymo.verticaloader.vertica.VerticaDataSource - SQLException exception in jobState: STARTED
java.sql.SQLSyntaxErrorException: [Vertica]VJDBC ERROR: Function KafkaSource() does not exist, or permission is denied for KafkaSource()
at com.vertica.util.ServerErrorData.buildException(Unknown Source) ~[vertica-jdbc-9.2.0-0.jar!/:?]
at com.vertica.dataengine.VQueryExecutor.executeSimpleProtocol(Unknown Source) ~[vertica-jdbc-9.2.0-0.jar!/:?]
at com.vertica.dataengine.VQueryExecutor.execute(Unknown Source) ~[vertica-jdbc-9.2.0-0.jar!/:?]
at com.vertica.jdbc.common.SStatement.executeNoParams(Unknown Source) ~[vertica-jdbc-9.2.0-0.jar!/:?]
at com.vertica.jdbc.common.SStatement.execute(Unknown Source) ~[vertica-jdbc-9.2.0-0.jar!/:?]
at sun.reflect.GeneratedMethodAccessor30.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_111]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_111]
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc-8.5.15.jar!/:?]
at com.sun.proxy.$Proxy80.execute(Unknown Source) ~[?:?]
at in.vymo.verticaloader.loader.impl.AbstractLoader.copyFromKafkaToFlex(AbstractLoader.java:36) ~[classes!/:?]
at in.vymo.verticaloader.executor.LoaderJob.executeJobInState(LoaderJob.java:75) ~[classes!/:?]
at in.vymo.verticaloader.executor.LoaderJob.run(LoaderJob.java:52) [classes!/:?]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_111]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_111]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) [?:1.8.0_111]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [?:1.8.0_111]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_111]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_111]
at java.lang.Thread.run(Thread.java:745) [?:1.8.0_111]
Caused by: com.vertica.support.exceptions.SyntaxErrorException: [Vertica]VJDBC ERROR: Function KafkaSource() does not exist, or permission is denied for KafkaSource()
... 20 more
Remedy Tried:
myuser=> GRANT ALL ON SOURCE KafkaSource() TO myuser; ROLLBACK 2059: Source with specified name and parameters does not exist: KafkaSource
Request to help with respect to same, also in case if you need anything additional from our end for better insight.

LenoyJ

See this link for some ideas: https://forum.vertica.com/discussion/216053/flextable-udfs-broken-failure-in-udx-rpc-call-invokesetexeccontext-failed-to-map-segment-from-s
Most likely due to the user you're using has insufficient privileges to your catalog directory, or your catalog directory is on a disk with noexec enabled.

LenoyJ

You seem to be using myuser to grant privileges to myuser when myuser itself does not have privilages. Try logging to the db as dbadmin (or a superuser) and;

GRANT USAGE ON library kafkalib to myuser;
GRANT EXECUTE ON source KafkaSource() to myuser;

Grant execute to any other kafka functions/parsers as needed.

LenoyJ

On the other hand, the rollback error for the grant seem to indicate that KafkaSource() itself does not exist. Could it be that the kafka library didn't get installed when the db was created? You may need to check your logs to see if anything happened.
 
Then, log in as dbadmin, and reinstall the kafka library like so:
admintools -t install_package -d dbname -p password -P kafka
 
And then perform the grants in my post above.

Kanishka

"myuser" here is the superuser itself.

@LenoyJ said:
You seem to be using myuser to grant privileges to myuser when myuser itself does not have privilages. Try logging to the db as dbadmin (or a superuser) and;

GRANT USAGE ON library kafkalib to myuser;
GRANT EXECUTE ON source KafkaSource() to myuser;

Grant execute to any other kafka functions/parsers as needed.

@LenoyJ said:
You seem to be using myuser to grant privileges to myuser when myuser itself does not have privilages. Try logging to the db as dbadmin (or a superuser) and;

GRANT USAGE ON library kafkalib to myuser;
GRANT EXECUTE ON source KafkaSource() to myuser;

Grant execute to any other kafka functions/parsers as needed.

LenoyJ

@Kanishka , Thanks for confirming. In which case see my second post above.

Kanishka

Thanks Lenoy for the response but when we run:
admintools -t install_package -d dbname -p password -P kafka
we end up into:
Failure in UDx RPC call InvokeCheckLibrary(): Error calling setupExecContext()

@LenoyJ said:
@Kanishka , Thanks for confirming. In which case see my second post above.

LenoyJ

Anything more to the error? Check out /opt/vertica/log/adminTools.log in case the error message was truncated.
 
Alternatively, admintools reinstalls the kafka package by running:
/opt/vertica/packages/kafka/ddl/install.sql
 
You may want to view/run that sql file and see what's failing exactly.

Kanishka

Have tried above also and ended up in:
vsql:/opt/vertica/packages/kafka/ddl/install.sql:2: ROLLBACK 2175: An error occurred when loading library file on node v_vymo_node0002, message: Failure in UDx RPC call InvokeCheckLibrary(): Error calling setupExecContext() in User Defined Object [] at [/data/qb_workspaces/jenkins2/ReleaseBuilds/Grader/REL-9_2_1-x_grader/build/vertica/OSS/UDxFence/vertica-udx-C++.cpp:274], error code: 0, message: Error happened in dlopen(): [/data/vymo/v_vymo_node0002_catalog/Libraries/028e9329c47a3faa0ceef8f08e6faa3000c0000000000224/KafkaLib_028e9329c47a3faa0ceef8f08e6faa3000c0000000000224.so: failed to map segment from shared object] vsql:/opt/vertica/packages/kafka/ddl/install.sql:4: ROLLBACK 3861: Library not found: public.KafkaLib vsql:/opt/vertica/packages/kafka/ddl/install.sql:5: ROLLBACK 3861: Library not found: public.KafkaLib vsql:/opt/vertica/packages/kafka/ddl/install.sql:7: ROLLBACK 3861: Library not found: public.KafkaLib vsql:/opt/vertica/packages/kafka/ddl/install.sql:8: ROLLBACK 3861: Library not found: public.KafkaLib vsql:/opt/vertica/packages/kafka/ddl/install.sql:9: ROLLBACK 3861: Library not found: public.KafkaLib vsql:/opt/vertica/packages/kafka/ddl/install.sql:11: ROLLBACK 3861: Library not found: public.KafkaLib vsql:/opt/vertica/packages/kafka/ddl/install.sql:12: ROLLBACK 3861: Library not found: public.KafkaLib vsql:/opt/vertica/packages/kafka/ddl/install.sql:14: ROLLBACK 3861: Library not found: public.KafkaLib vsql:/opt/vertica/packages/kafka/ddl/install.sql:16: ROLLBACK 3861: Library not found: public.KafkaLib vsql:/opt/vertica/packages/kafka/ddl/install.sql:17: ROLLBACK 3861: Library not found: public.KafkaLib vsql:/opt/vertica/packages/kafka/ddl/install.sql:18: ROLLBACK 3861: Library not found: public.KafkaLib vsql:/opt/vertica/packages/kafka/ddl/install.sql:19: ROLLBACK 3861: Library not found: public.KafkaLib

Kanishka

@LenoyJ said:
See this link for some ideas: https://forum.vertica.com/discussion/216053/flextable-udfs-broken-failure-in-udx-rpc-call-invokesetexeccontext-failed-to-map-segment-from-s
Most likely due to the user you're using has insufficient privileges to your catalog directory, or your catalog directory is on a disk with noexec enabled.

Thanks LenoyJ, you are right we had "catalog directory is on a disk with noexec enabled."