Collection not working with iRODS 4.2.2

Hello,

We have upgraded to the latest iRODS version 4.2.2 and we now have problems with collections in metalnx. The collection link loops on itself and we have these errors on irods logs:

Dec 12 10:54:28 pid:14474 NOTICE: SQL Error message: [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.56-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ON USER_GROUP_MAIN.user_id = R_USER_GROUP.group_user_id JOIN R_OBJTACCESS ON R' at line 1 Dec 12 10:54:28 pid:14474 NOTICE: chlSpecificQuery cmlGetFirstRowFromSql failure -806000 Dec 12 10:54:28 pid:14474 NOTICE: _rsSpecificQuery: specificQuery status = -806000 Dec 12 10:54:28 pid:14474 NOTICE: rsSpecificQuery: rcSpecificQuery failed, status = -806000

metalnx is assuming you are talking to a postgres iCAT. If you look here https://github.com/DICE-UNC/metalnx-web I had the same issue and did a bunch of work to extract those queries out and then use a factory to decide the type of iCAT database you were talking to.

That work is collected here: https://github.com/DICE-UNC/metalnx-web/issues/15

There is now a postgres 'plugin' that works, and an interface to provision a MySQL/MariaDB plugin, but I had stopped there.

On Tue, Dec 12, 2017 at 5:25 AM, emedernach notifications@github.com wrote:

Hello,

We have upgraded to the latest iRODS version 4.2.2 and we now have problems with collections in metalnx. The collection link loops on itself and we have these errors on irods logs:

Dec 12 10:54:28 pid:14474 NOTICE: SQL Error message: [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.56-MariaDB]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ON USER_GROUP_MAIN.user_id = R_USER_GROUP.group_user_id JOIN R_OBJTACCESS ON R' at line 1 Dec 12 10:54:28 pid:14474 NOTICE: chlSpecificQuery cmlGetFirstRowFromSql failure -806000 Dec 12 10:54:28 pid:14474 NOTICE: _rsSpecificQuery: specificQuery status = -806000 Dec 12 10:54:28 pid:14474 NOTICE: rsSpecificQuery: rcSpecificQuery failed, status = -806000

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/Metalnx/metalnx-web/issues/27, or mute the thread https://github.com/notifications/unsubscribe-auth/ABC-LY1l2uEBZRcEOKWRuLPwl86RcHu7ks5s_lSPgaJpZM4Q-vKp .

Ok, Thanks for your explanations.

Metalnx actually worked with both Postgresql and MySQL but you have to adjust the necessary parameters in the database.properties file in

/webapps/emc-metalnx-web/WEB/classes. The information on how to do that is contained in the database properties file as well as in the installation instructions. Remember, after you change the properties file you must stop and restart tomcat in order for the changes to take effect. On Tue, Dec 12, 2017 at 10:27 AM, Mike Conway wrote: > metalnx is assuming you are talking to a postgres iCAT. If you look here > https://github.com/DICE-UNC/metalnx-web I had the same issue and did a > bunch of work to extract those queries out and then use a factory to decide > the type of iCAT database you were talking to. > > That work is collected here: > https://github.com/DICE-UNC/metalnx-web/issues/15 > > There is now a postgres 'plugin' that works, and an interface to provision > a MySQL/MariaDB plugin, but I had stopped there. > > MC > > On Tue, Dec 12, 2017 at 5:25 AM, emedernach > wrote: > > > Hello, > > > > We have upgraded to the latest iRODS version 4.2.2 and we now have > > problems with collections in metalnx. The collection link loops on itself > > and we have these errors on irods logs: > > > > Dec 12 10:54:28 pid:14474 NOTICE: SQL Error message: [MySQL][ODBC 5.2(w) > > Driver][mysqld-5.5.56-MariaDB]You have an error in your SQL syntax; > check > > the manual that corresponds to your MariaDB server version for the right > > syntax to use near 'ON USER_GROUP_MAIN.user_id = > R_USER_GROUP.group_user_id > > JOIN R_OBJT_ACCESS ON R_' at line 1 > > Dec 12 10:54:28 pid:14474 NOTICE: chlSpecificQuery cmlGetFirstRowFromSql > > failure -806000 > > Dec 12 10:54:28 pid:14474 NOTICE: _rsSpecificQuery: specificQuery status > = > > -806000 > > Dec 12 10:54:28 pid:14474 NOTICE: rsSpecificQuery: rcSpecificQuery > failed, > > status = -806000 > > > > — > > You are receiving this because you are subscribed to this thread. > > Reply to this email directly, view it on GitHub > > , or mute the thread > > LY1l2uEBZRcEOKWRuLPwl86RcHu7ks5s_lSPgaJpZM4Q-vKp> > > . > > > > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > , > or mute the thread > > . >

Hey Steve I think this was for the specific queries it was issuing against the iRODS server itself versus what database it was talking to for the metalnx database schema? That's the bit I ran into...that the specific queries in Metalnx were for postgres.

On Tue, Dec 12, 2017 at 11:01 AM, Stephen Worth notifications@github.com wrote:

Metalnx actually worked with both Postgresql and MySQL but you have to adjust the necessary parameters in the database.properties file in
/webapps/emc-metalnx-web/WEB/classes. The information on how to do that is contained in the database properties file as well as in the installation instructions. Remember, after you change the properties file you must stop and restart tomcat in order for the changes to take effect. On Tue, Dec 12, 2017 at 10:27 AM, Mike Conway wrote: > metalnx is assuming you are talking to a postgres iCAT. If you look here > https://github.com/DICE-UNC/metalnx-web I had the same issue and did a > bunch of work to extract those queries out and then use a factory to decide > the type of iCAT database you were talking to. > > That work is collected here: > https://github.com/DICE-UNC/metalnx-web/issues/15 > > There is now a postgres 'plugin' that works, and an interface to provision > a MySQL/MariaDB plugin, but I had stopped there. > > MC > > On Tue, Dec 12, 2017 at 5:25 AM, emedernach > wrote: > > > Hello, > > > > We have upgraded to the latest iRODS version 4.2.2 and we now have > > problems with collections in metalnx. The collection link loops on itself > > and we have these errors on irods logs: > > > > Dec 12 10:54:28 pid:14474 NOTICE: SQL Error message: [MySQL][ODBC 5.2(w) > > Driver][mysqld-5.5.56-MariaDB]You have an error in your SQL syntax; > check > > the manual that corresponds to your MariaDB server version for the right > > syntax to use near 'ON USER_GROUP_MAIN.user_id = > R_USER_GROUP.group_user_id > > JOIN R_OBJT_ACCESS ON R_' at line 1 > > Dec 12 10:54:28 pid:14474 NOTICE: chlSpecificQuery cmlGetFirstRowFromSql > > failure -806000 > > Dec 12 10:54:28 pid:14474 NOTICE: _rsSpecificQuery: specificQuery status > = > > -806000 > > Dec 12 10:54:28 pid:14474 NOTICE: rsSpecificQuery: rcSpecificQuery > failed, > > status = -806000 > > > > — > > You are receiving this because you are subscribed to this thread. > > Reply to this email directly, view it on GitHub > > , or mute the thread > > LY1l2uEBZRcEOKWRuLPwl86RcHu7ks5s_lSPgaJpZM4Q-vKp> > > . > > > > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > , > or mute the thread > > . > — You are receiving this because you commented. Reply to this email directly, view it on GitHub , or mute the thread .

Likely so. I have not had an opportunity to look into this. Just noticed the use of MySQL in the log notes.

Sent from iPhone

On Dec 12, 2017, at 11:23 AM, Mike Conway notifications@github.com wrote:

Hey Steve I think this was for the specific queries it was issuing against the iRODS server itself versus what database it was talking to for the metalnx database schema? That's the bit I ran into...that the specific queries in Metalnx were for postgres.

On Tue, Dec 12, 2017 at 11:01 AM, Stephen Worth notifications@github.com wrote:

Metalnx actually worked with both Postgresql and MySQL but you have to adjust the necessary parameters in the database.properties file in
/webapps/emc-metalnx-web/WEB/classes. The information on how to do that is contained in the database properties file as well as in the installation instructions. Remember, after you change the properties file you must stop and restart tomcat in order for the changes to take effect. On Tue, Dec 12, 2017 at 10:27 AM, Mike Conway wrote: > metalnx is assuming you are talking to a postgres iCAT. If you look here > https://github.com/DICE-UNC/metalnx-web I had the same issue and did a > bunch of work to extract those queries out and then use a factory to decide > the type of iCAT database you were talking to. > > That work is collected here: > https://github.com/DICE-UNC/metalnx-web/issues/15 > > There is now a postgres 'plugin' that works, and an interface to provision > a MySQL/MariaDB plugin, but I had stopped there. > > MC > > On Tue, Dec 12, 2017 at 5:25 AM, emedernach > wrote: > > > Hello, > > > > We have upgraded to the latest iRODS version 4.2.2 and we now have > > problems with collections in metalnx. The collection link loops on itself > > and we have these errors on irods logs: > > > > Dec 12 10:54:28 pid:14474 NOTICE: SQL Error message: [MySQL][ODBC 5.2(w) > > Driver][mysqld-5.5.56-MariaDB]You have an error in your SQL syntax; > check > > the manual that corresponds to your MariaDB server version for the right > > syntax to use near 'ON USER_GROUP_MAIN.user_id = > R_USER_GROUP.group_user_id > > JOIN R_OBJT_ACCESS ON R_' at line 1 > > Dec 12 10:54:28 pid:14474 NOTICE: chlSpecificQuery cmlGetFirstRowFromSql > > failure -806000 > > Dec 12 10:54:28 pid:14474 NOTICE: _rsSpecificQuery: specificQuery status > = > > -806000 > > Dec 12 10:54:28 pid:14474 NOTICE: rsSpecificQuery: rcSpecificQuery > failed, > > status = -806000 > > > > — > > You are receiving this because you are subscribed to this thread. > > Reply to this email directly, view it on GitHub > > , or mute the thread > > LY1l2uEBZRcEOKWRuLPwl86RcHu7ks5s_lSPgaJpZM4Q-vKp> > > . > > > > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > , > or mute the thread > > . > — You are receiving this because you commented. Reply to this email directly, view it on GitHub , or mute the thread . — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

Yep, so the state I think is that there is now just an interface that can be implemented to return the mysql friendly specific queries to metalnx instead of the postgres ones, so that's a bit of good news!

On Tue, Dec 12, 2017 at 12:24 PM, Stephen Worth notifications@github.com wrote:

Likely so. I have not had an opportunity to look into this. Just noticed the use of MySQL in the log notes.

Sent from iPhone

On Dec 12, 2017, at 11:23 AM, Mike Conway notifications@github.com wrote:

Hey Steve I think this was for the specific queries it was issuing against the iRODS server itself versus what database it was talking to for the metalnx database schema? That's the bit I ran into...that the specific queries in Metalnx were for postgres.

On Tue, Dec 12, 2017 at 11:01 AM, Stephen Worth < notifications@github.com> wrote:

Metalnx actually worked with both Postgresql and MySQL but you have to adjust the necessary parameters in the database.properties file in
/webapps/emc-metalnx-web/WEB/classes. The information on how to do that is contained in the database properties file as well as in the installation instructions. Remember, after you change the properties file you must stop and restart tomcat in order for the changes to take effect. On Tue, Dec 12, 2017 at 10:27 AM, Mike Conway < notifications@github.com> wrote: > metalnx is assuming you are talking to a postgres iCAT. If you look here > https://github.com/DICE-UNC/metalnx-web I had the same issue and did a > bunch of work to extract those queries out and then use a factory to decide > the type of iCAT database you were talking to. > > That work is collected here: > https://github.com/DICE-UNC/metalnx-web/issues/15 > > There is now a postgres 'plugin' that works, and an interface to provision > a MySQL/MariaDB plugin, but I had stopped there. > > MC > > On Tue, Dec 12, 2017 at 5:25 AM, emedernach < notifications@github.com> > wrote: > > > Hello, > > > > We have upgraded to the latest iRODS version 4.2.2 and we now have > > problems with collections in metalnx. The collection link loops on itself > > and we have these errors on irods logs: > > > > Dec 12 10:54:28 pid:14474 NOTICE: SQL Error message: [MySQL][ODBC 5.2(w) > > Driver][mysqld-5.5.56-MariaDB]You have an error in your SQL syntax; > check > > the manual that corresponds to your MariaDB server version for the right > > syntax to use near 'ON USER_GROUP_MAIN.user_id = > R_USER_GROUP.group_user_id > > JOIN R_OBJT_ACCESS ON R_' at line 1 > > Dec 12 10:54:28 pid:14474 NOTICE: chlSpecificQuery cmlGetFirstRowFromSql > > failure -806000 > > Dec 12 10:54:28 pid:14474 NOTICE: _rsSpecificQuery: specificQuery status > = > > -806000 > > Dec 12 10:54:28 pid:14474 NOTICE: rsSpecificQuery: rcSpecificQuery > failed, > > status = -806000 > > > > — > > You are receiving this because you are subscribed to this thread. > > Reply to this email directly, view it on GitHub > > , or mute the thread > > LY1l2uEBZRcEOKWRuLPwl86RcHu7ks5s_lSPgaJpZM4Q-vKp> > > . > > > > > — > You are receiving this because you are subscribed to this thread. > Reply to this email directly, view it on GitHub > , > or mute the thread > > . > — You are receiving this because you commented. Reply to this email directly, view it on GitHub , or mute the thread . — You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/Metalnx/metalnx-web/issues/27#issuecomment-351122930, or mute the thread https://github.com/notifications/unsubscribe-auth/ABC-LdRY9fsjq2HyHzK64ftvcGg_sugLks5s_rbWgaJpZM4Q-vKp .

I checked the content of mysql.properties file and it seems correct.

Here is the SQL query resulting in an error:

SELECT R_USER_MAIN.user_name, R_USER_MAIN.user_id, R_OBJT_ACCESS.access_type_id, R_USER_MAIN.user_type_name, R_USER_MAIN.zone_name, R_COLL_MAIN.coll_name,
 USER_GROUP_MAIN.user_name, R_COLL_MAIN.coll_name
FROM R_USER_MAIN AS USER_GROUP_MAIN JOIN R_USER_GROUP
 JOIN R_USER_MAIN ON R_USER_GROUP.user_id = R_USER_MAIN.user_id
                  ON USER_GROUP_MAIN.user_id = R_USER_GROUP.group_user_id
 JOIN R_OBJT_ACCESS ON R_USER_GROUP.group_user_id = R_OBJT_ACCESS.user_id
 JOIN R_COLL_MAIN ON R_OBJT_ACCESS.object_id = R_COLL_MAIN.coll_id
WHERE R_COLL_MAIN.coll_name = ? AND R_USER_MAIN.user_name = ? 
ORDER BY R_COLL_MAIN.coll_name, R_USER_MAIN.user_name, _OBJT_ACCESS.access_type_id DESC ;

The error is at the 2nd 'ON' for the R_USER_MAIN join.

Hey @emedernach I suspect we're mixing up two different database access services. One is the metalnx database itself, which as @sgworth points out goes through hibernate, and the database properties etc can switch dialects between postres and mysql. The other database is the actual iRODS iCAT database. That's what 'specific query' does, it is a direct issuance of SQL queries against that iRODS iCAT, not through hibernate, but through the iRODS protocol.

The statement above is a select against the iRODS iCAT, not metalnx. If you are going against mysql or maria it may be a syntax error between the different database flavors. That's what I ran into and started working on a way to have a different set of specific queries for the different iCAT dbs. Those queries are built here https://github.com/Metalnx/metalnx-web/blob/master/src/emc-metalnx-services/src/main/java/com/emc/metalnx/services/irods/CollectionServiceImpl.java

So that's what I had started to do over in the DICE-UNC code fork, add a client hints capability to ask iRODS what flavor of iCAT it was using and be able to get different flavors of specific query for each target database. So perhaps that query can just be tuned in the code in that one place for maria and you'd be done, but I'm hoping longer term to just memorialze any variances in a MySQL versus Postgres specific query service and select them via a factory method based on the target iCAT.

I -think- that's the issue here. Cheers MC

Closing for now. NB the master now has a SpecificQueryProvider https://github.com/irods-contrib/metalnx-web/blob/master/src/emc-metalnx-services/src/main/java/com/emc/metalnx/services/irods/utils/SpecificQueryProvider.java that can be extended to allow pluggable specific query support for databases other than Postgres. The default postgres provider is implemented.

irods-contrib / metalnx-web

Collection not working with iRODS 4.2.2 #27