Hello!
I use CMDBuild v2.1.4 and I stuck with the error, which is repeating each time I start a new CMDBuild user session.
First of all, the error is generated ONLY when I log in with user rights. If I log in to the CMDBuild with admin rights, the error vanishes.
I've thought that the problem is linked with user rights, but I got no luck after changing all access rights to write for particular group.
To reproduce the error I need to open for editing an existing card or create a new card in some classes, which have relations to other classes.
Here is a part of my class tree:
Class
|
|-> Directory (superclass)
| |
| |-> User
| |-> OU
| |-> HardwareClass
| |-> SoftwareType
| .
| .
| .
|
|-> Hardware (superclass)
| |
| |-> System (superclass)
| | |
| | |-> ServerSystem (superclass)
| | | |
| | | |-> Server
| . . .
| . . .
| . . .
|
|-> Location (superclass)
| |
| |-> BaseStation
| |-> Office
| |-> Room
. .
. .
. .
The superclass "System" has a domain link with class "Users" and superclass "Location"
When I open for edit a card from class "Server" I get two similar errors:
Error: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar
followed by huge SQL query and:
in first case a note, that table "BaseStation" not exists in FROM clause,
in second case a note, that table "SoftwareType" not exists in FROM clause.
I tried to disable the attribute "Location" in the "Server" class, and found that corresponding error obviously disappears. Also I tried to remove CQL filter from that attribute, but no luck, error remains.
I tried to analyze the SQL query from error message and found that it really contains some classes which are not in the FROM clause:
SELECT *,(SELECT count(*) FROM (
SELECT "LookUp#Location#OfficeType"."Description" AS "LookUp#Location#OfficeType#Description",
..... many, many query fields .....
FROM "Location" AS "Location"
LEFT JOIN "LookUp" AS "LookUp#Location#OfficeType" ON "LookUp#Location#OfficeType"."Id" = "Location"."OfficeType"
LEFT JOIN "Location" AS "Location#Location#Location" ON "Location#Location#Location"."Id" = "Location"."Location"
LEFT JOIN "System" AS "System#Location#System" ON "System#Location#System"."Id" = "Location"."System"
LEFT JOIN "OU" AS "OU#Location#OU" ON "OU#Location#OU"."Id" = "Location"."OU"
WHERE ( TRUE AND TRUE AND ( "Location"."IdClass" = ? OR
"BaseStation"."IdClass" = ? OR
"BladeRack"."IdClass" = ? OR
"Rack"."IdClass" = ? OR
"MetaLocation"."IdClass" = ? OR
"Office"."IdClass" = ? OR
"Room"."IdClass" = ? OR
"VMPlatform"."IdClass" = ? ) )
AND "Location"."Status" = ?) AS main) AS _Location_RowsCount,row_number()
OVER (ORDER BY "Location#Description" ASC) AS _Location_RowNumber FROM (
SELECT "LookUp#Location#OfficeType"."Description" AS "LookUp#Location#OfficeType#Description",
..... many, many query fields .....
LEFT JOIN "LookUp" AS "LookUp#Location#OfficeType" ON "LookUp#Location#OfficeType"."Id" = "Location"."OfficeType"
LEFT JOIN "Location" AS "Location#Location#Location" ON "Location#Location#Location"."Id" = "Location"."Location"
LEFT JOIN "System" AS "System#Location#System" ON "System#Location#System"."Id" = "Location"."System"
LEFT JOIN "OU" AS "OU#Location#OU" ON "OU#Location#OU"."Id" = "Location"."OU"
WHERE ( TRUE AND TRUE AND ( "Location"."IdClass" = ? OR
"BaseStation"."IdClass" = ? OR
"BladeRack"."IdClass" = ? OR
"Rack"."IdClass" = ? OR
"MetaLocation"."IdClass" = ? OR
"Office"."IdClass" = ? OR
"Room"."IdClass" = ? OR
"VMPlatform"."IdClass" = ? ) )
AND "Location"."Status" = ?) AS main LIMIT 5000 OFFSET 0
As you can see, two WHERE clauses contain references to some tables. The "Location" is a superclass, and all other classes ("BaseStation", "BladeRack", etc.) are underlying classes of superclass "Location".
Can you please check this issue and tell me if there is a workaround or preferably a solution of this problem, except giving admin rights to users (as I said previously, the error vanishes when I give admin rights to the test group of users).
Thank you!
Previously Andrey wrote:
Hello!
I use CMDBuild v2.1.4 and I stuck with the error, which is repeating each time I start a new CMDBuild user session.
First of all, the error is generated ONLY when I log in with user rights. If I log in to the CMDBuild with admin rights, the error vanishes.
I've thought that the problem is linked with user rights, but I got no luck after changing all access rights to write for particular group.
To reproduce the error I need to open for editing an existing card or create a new card in some classes, which have relations to other classes.
Here is a part of my class tree:
Class
|
|-> Directory (superclass)
| |
| |-> User
| |-> OU
| |-> HardwareClass
| |-> SoftwareType
| .
| .
| .
|
|-> Hardware (superclass)
| |
| |-> System (superclass)
| | |
| | |-> ServerSystem (superclass)
| | | |
| | | |-> Server
| . . .
| . . .
| . . .
|
|-> Location (superclass)
| |
| |-> BaseStation
| |-> Office
| |-> Room
. .
. .
. .
The superclass "System" has a domain link with class "Users" and superclass "Location"
When I open for edit a card from class "Server" I get two similar errors:
Error: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar
followed by huge SQL query and:
in first case a note, that table "BaseStation" not exists in FROM clause,
in second case a note, that table "SoftwareType" not exists in FROM clause.
I tried to disable the attribute "Location" in the "Server" class, and found that corresponding error obviously disappears. Also I tried to remove CQL filter from that attribute, but no luck, error remains.
I tried to analyze the SQL query from error message and found that it really contains some classes which are not in the FROM clause:
SELECT *,(SELECT count(*) FROM (
SELECT "LookUp#Location#OfficeType"."Description" AS "LookUp#Location#OfficeType#Description",
..... many, many query fields .....
FROM "Location" AS "Location"
LEFT JOIN "LookUp" AS "LookUp#Location#OfficeType" ON "LookUp#Location#OfficeType"."Id" = "Location"."OfficeType"
LEFT JOIN "Location" AS "Location#Location#Location" ON "Location#Location#Location"."Id" = "Location"."Location"
LEFT JOIN "System" AS "System#Location#System" ON "System#Location#System"."Id" = "Location"."System"
LEFT JOIN "OU" AS "OU#Location#OU" ON "OU#Location#OU"."Id" = "Location"."OU"
WHERE ( TRUE AND TRUE AND ( "Location"."IdClass" = ? OR
"BaseStation"."IdClass" = ? OR
"BladeRack"."IdClass" = ? OR
"Rack"."IdClass" = ? OR
"MetaLocation"."IdClass" = ? OR
"Office"."IdClass" = ? OR
"Room"."IdClass" = ? OR
"VMPlatform"."IdClass" = ? ) )
AND "Location"."Status" = ?) AS main) AS _Location_RowsCount,row_number()
OVER (ORDER BY "Location#Description" ASC) AS _Location_RowNumber FROM (
SELECT "LookUp#Location#OfficeType"."Description" AS "LookUp#Location#OfficeType#Description",
..... many, many query fields .....
LEFT JOIN "LookUp" AS "LookUp#Location#OfficeType" ON "LookUp#Location#OfficeType"."Id" = "Location"."OfficeType"
LEFT JOIN "Location" AS "Location#Location#Location" ON "Location#Location#Location"."Id" = "Location"."Location"
LEFT JOIN "System" AS "System#Location#System" ON "System#Location#System"."Id" = "Location"."System"
LEFT JOIN "OU" AS "OU#Location#OU" ON "OU#Location#OU"."Id" = "Location"."OU"
WHERE ( TRUE AND TRUE AND ( "Location"."IdClass" = ? OR
"BaseStation"."IdClass" = ? OR
"BladeRack"."IdClass" = ? OR
"Rack"."IdClass" = ? OR
"MetaLocation"."IdClass" = ? OR
"Office"."IdClass" = ? OR
"Room"."IdClass" = ? OR
"VMPlatform"."IdClass" = ? ) )
AND "Location"."Status" = ?) AS main LIMIT 5000 OFFSET 0
As you can see, two WHERE clauses contain references to some tables. The "Location" is a superclass, and all other classes ("BaseStation", "BladeRack", etc.) are underlying classes of superclass "Location".
Can you please check this issue and tell me if there is a workaround or preferably a solution of this problem, except giving admin rights to users (as I said previously, the error vanishes when I give admin rights to the test group of users).
Thank you!
Hello Davide,
Thank you for your answer!
Sure, by default all classes for my test group set to read access except some of them which are set to write. There are no classes with no access for this group. So, this is not the case
.
Previously Tecnoteca wrote:
Dear Andrey,just a question about user rights: given your model, did you set "read" access for those classes that are the destination of a reference fields?I mean: given two classes A and B, and A has a reference to B, B must have at least "read" access.I hope this helps and to solve your problem, let us know if you still have it.Best regards.-- Davide Pavan
Hello Davide,
Thank you for your answer!
Sure, by default all classes for my test group set to read access except some of them which are set to write. There are no classes with no access for this group. So, this is not the case
.
Previously Tecnoteca wrote:
Dear Andrey,just a question about user rights: given your model, did you set "read" access for those classes that are the destination of a reference fields?I mean: given two classes A and B, and A has a reference to B, B must have at least "read" access.I hope this helps and to solve your problem, let us know if you still have it.Best regards.-- Davide Pavan
Hi Davide,
I've just completed data truncation from clone of our production DB and now I'm ready to send it to you for analysis. Which format do you prefer, e.g.:
1) VMware ESXi 5 virtual machine (full installation with debian 7, postgres and tomcat)
2) Pg server dump made with pg_dumpall
3) DB dump made with pg_dump
4) something another?
I especially checked the existense of query error on this truncated dataset - it still exist.
Previously Tecnoteca wrote:
Hi Andrey,could you provide us the database that you are using? We could use it for identify your issue.Best regards.-- Davide PavanPreviously Andrey wrote:Hello Davide,
Thank you for your answer!
Sure, by default all classes for my test group set to read access except some of them which are set to write. There are no classes with no access for this group. So, this is not the case
.
Previously Tecnoteca wrote:
Dear Andrey,just a question about user rights: given your model, did you set "read" access for those classes that are the destination of a reference fields?I mean: given two classes A and B, and A has a reference to B, B must have at least "read" access.I hope this helps and to solve your problem, let us know if you still have it.Best regards.-- Davide Pavan
Hi Davide,
I've just completed data truncation from clone of our production DB and now I'm ready to send it to you for analysis. Which format do you prefer, e.g.:
1) VMware ESXi 5 virtual machine (full installation with debian 7, postgres and tomcat)
2) Pg server dump made with pg_dumpall
3) DB dump made with pg_dump
4) something another?
I especially checked the existense of query error on this truncated dataset - it still exist.
Previously Tecnoteca wrote:
Hi Andrey,could you provide us the database that you are using? We could use it for identify your issue.Best regards.-- Davide PavanPreviously Andrey wrote:Hello Davide,
Thank you for your answer!
Sure, by default all classes for my test group set to read access except some of them which are set to write. There are no classes with no access for this group. So, this is not the case
.
Previously Tecnoteca wrote:
Dear Andrey,just a question about user rights: given your model, did you set "read" access for those classes that are the destination of a reference fields?I mean: given two classes A and B, and A has a reference to B, B must have at least "read" access.I hope this helps and to solve your problem, let us know if you still have it.Best regards.-- Davide Pavan
Hi Davide,
yesterday I sent to you via email the backup file with our truncated DB. Please, tell if you have received it.
Thank you!
Previously Tecnoteca wrote:
Dear Andrey,option 3 would be perfect. I recommend you to use compressed format.Best regards.--Davide PavanPreviously Andrey wrote:Hi Davide,
I've just completed data truncation from clone of our production DB and now I'm ready to send it to you for analysis. Which format do you prefer, e.g.:
1) VMware ESXi 5 virtual machine (full installation with debian 7, postgres and tomcat)
2) Pg server dump made with pg_dumpall
3) DB dump made with pg_dump
4) something another?
I especially checked the existense of query error on this truncated dataset - it still exist.
Previously Tecnoteca wrote:
Hi Andrey,could you provide us the database that you are using? We could use it for identify your issue.Best regards.-- Davide PavanPreviously Andrey wrote:Hello Davide,
Thank you for your answer!
Sure, by default all classes for my test group set to read access except some of them which are set to write. There are no classes with no access for this group. So, this is not the case
.
Previously Tecnoteca wrote:
Dear Andrey,just a question about user rights: given your model, did you set "read" access for those classes that are the destination of a reference fields?I mean: given two classes A and B, and A has a reference to B, B must have at least "read" access.I hope this helps and to solve your problem, let us know if you still have it.Best regards.-- Davide Pavan