Running into issue: TEIID40095 TEIID31071 Invalid table; Table SugarCRMDataSourceJNDI_public.accounts_bugs_pkey has no columns defined

[ichanjasper]

I am running to issue using teiid, TEIID40095 TEIID31071 Invalid table; Table SugarCRMDataSourceJNDI_public.accounts_bugs_pkey has no columns defined.  Is it a way to skip table with has no columns defined, instead of throwing exception?

Thanks,

Ivan

[rareddy]

There is not much information to go on. The error is saying that whatever data source you are pointing with the JNDI, the metadata creation at the deployment time is defining a table with no columns in it.

[ichanjasper]

How do I collect more information?

[ichanjasper]

Hi, I think this issue occurred after I changed from using postgresSQL driver from version 9.2-1002.jdbc4 to 9.4-1210.jdbc41.

[rareddy]

translator? resource adapter? version? how you are defining the VDB? is there any data in your source to create the models in the Teiid. What other debugging have you done to make sure sure that the error is with the Teiid?  I already mentioned what is the error that Teiid is seeing, that needs to be rectified.

[ichanjasper]

Here is the exception:

Caused by: org.teiid.deployers.VirtualDatabaseException: TEIID40095 TEIID31071 Invalid table; Table SugarCRMDataSourceJNDI_public.accounts_bugs_pkey has no columns defined

        at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:730)

        at org.teiid.runtime.EmbeddedServer.deployVDB(EmbeddedServer.java:640)

        at com.jaspersoft.jasperserver.api.engine.common.virtualdatasourcequery.impl.TeiidVirtualDataSourceQueryServiceImpl.deployVirtualDataSource(TeiidVirtualDataSourceQueryServiceImpl.java:330)

I will collect more information and get back to you shortly.

Thanks,

Ivan

[rareddy]

Turn off importing the "indexes" then it will work fine. Take look at the importer properties at JDBC Translators · Teiid Documentation

[ichanjasper]

Thank you.  Let me your suggestion.

[ichanjasper]

It still doesn't work.  I am using "org.teiid.translator.jdbc.postgresql.PostgreSQLExecutionFactory" and Teiid version 8.12.3.

[ichanjasper]

And I set the following 2 properties to False:

"importer.importIndexes"

"importer.importApproximateIndexes"

[shawkins]

You want importer.importApproximateIndexes almost always to be true - as that can be more performanc related rather than what is actually imported.

In accounts_bugs_pkey a regular table (what is it's table type)?  Or is there anything special about it in the postgresql  - is it inherited for example?

[ichanjasper]

When I ran the following SQL in PGAdmin, I got the following error:

ERROR:  "accounts_bugs_pkey" is an index

LINE 1: select * from accounts_bugs_pkey;

                      ^

********** Error **********

ERROR: "accounts_bugs_pkey" is an index

SQL state: 42809

Character: 15

It seems like accounts_bugs_pkey is an index, not a table.

[rareddy]

"importer.importIndexes" to "false" should have taken of that, can you post your VDB?

[shawkins]

Are you setting the tabletypes import property?  It looks like pg can expose indexes as a table - but then apparently may not associate any columns.

[ichanjasper]

It works ok if I set importer.tableTypes to TABLE,VIEW,SYNONYM.