WS-DAIR test database creation (how OGSA-DAI did it)

This is using MySQL 5.0.45.

Update MySQL if necessary

When initially loading stored procedures the error I got was:

ERROR 1044 (42000): Access denied for user 'dais-wg'@'%' to database 'wsdair' 

Google suggested the need for a

$ SELECT * FROM mysql.proc;

This table should exist. If it doesn't then this command can configure it:

$ /usr/bin/mysql_upgrade -u root -p

Create database and user

$ mysql -u root -p
$ CREATE wsdair;
$ GRANT ALL ON wsdair.* TO "dais-wg"@"%" IDENTIFIED BY "mypassword";

This is needed for stored procedures.

$ GRANT ALL ON mysql.proc TO "dais-wg"@"%" IDENTIFIED BY "mypassword";
$ FLUSH PRIVILEGES;

Create littleblackbook data

Using any OGSA-DAI 3.1 binary distribution

$ source setenv.sh
$ java uk.org.ogsadai.dbcreate.CreateTestMySQLDB -host myhost -database wsdair -tablename dair_littleblackbook -username dais-wg -password mypassword

Check:

$ mysql -u dais-wg -p
$ use wsdair;
$ SELECT COUNT(*) FROM dair_littleblackbook;

Load stored procedures

These are in the attached ogsadaiStoredProcedures.zip

$ mysql -u dais-wg -p
$ use wsdair;
$ source function_in_out.txt;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
$ source proc_in_out_inout.txt;
...
$ source proc_in_out.txt;
...
$ source proc_only_in_many_results.txt;
...
$ source proc_only_in.txt;
...

Permissions and stored procedures

If when running something as follows:

String sql = "CALL proc_only_in(1);";
CallableStatement statement = (CallableStatement)connection.prepareCall(sql);
statement.execute();

You get a:

Exception in thread "main" java.lang.NullPointerException
        at com.mysql.jdbc.StringUtils.indexOfIgnoreCaseRespectQuotes(StringUtils.java:959)
        at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1296)
        at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:3670)
        at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:702)
        at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:513)
        at com.mysql.jdbc.Connection.parseCallableStatement(Connection.java:4422)
        at com.mysql.jdbc.Connection.prepareCall(Connection.java:4496)
        at com.mysql.jdbc.Connection.prepareCall(Connection.java:4470)
        at StoredProcedure.main(StoredProcedure.java:26)

Or, if when trying to use a stored procedure with OGSA-DAI WS-DAIR you get a server-side

You can see permissions in MySQL by logging in as root and doing:

$ SELECT Host, User, Db, Create_routine_priv, Execute_priv, Alter_priv FROM user;
$ SELECT Host, User, Create_routine_priv, Execute_priv, Alter_priv FROM db;

Ideally for the database-specific ones you should see Y for all the entries for your user.

This is why I listed the need to do:

$ GRANT ALL ON wsdair.* TO "dais-wg"@"%" IDENTIFIED BY "gw-siad";
$ GRANT ALL ON mysql.proc TO "dais-wg"@"%" IDENTIFIED BY "gw-siad";

at the top of the page. As it turns out mysql.proc needs to be accessible so the database can set up the callable statement.

Other useful commands

Grant permission to create, alter, run stored procedures in any database:

$ GRANT CREATE ROUTINE on *.* TO "dais-wg"@"%" IDENTIFIED BY "gw-siad";
$ GRANT ALTER ROUTINE on *.* TO "dais-wg"@"%" IDENTIFIED BY "gw-siad";
$ GRANT EXECUTE on *.* TO "dais-wg"@"%" IDENTIFIED BY "gw-siad";

Grant permission run specific stored procedure in specific database:

$ GRANT EXECUTE on mydb.myproc TO "dais-wg"@"%" IDENTIFIED BY "gw-siad";

Revoke permission:

$ REVOKE EXECUTE ON PROCEDURE wsdair.proc_only_on FROM 'dais-wg'@'coal.epcc.ed.ac.uk';
$ REVOKE EXECUTE ON *.* FROM 'dais-wg'@'coal.epcc.ed.ac.uk';

Show procedure definition.

$ SHOW CREATE PROCEDURE  wsdair.proc_only_in;

+--------------+----------+------------------+
| Procedure    | sql_mode | Create Procedure |
+--------------+----------+------------------+
| proc_only_in |          | CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_only_in`(IN param1 INT)
BEGIN
declare loc VARCHAR(30);
select name from dair_littleblackbook where id=param1 LIMIT 1 into loc;
select * from dair_littleblackbook where id=param1;
select * from dair_littleblackbook where id=param1+1;
update dair_littleblackbook set name='nothing' where name=loc;
update dair_littleblackbook set name=loc where name='nothing';
END | 

$ SHOW CREATE PROCEDURE  wsdair.proc_only_in;

+--------------+----------+------------------+
| Procedure    | sql_mode | Create Procedure |
+--------------+----------+------------------+
| proc_only_in |          | NULL             | 
+--------------+----------+------------------+
1 row in set (0.00 sec)

If logged in as the user (not root) and this happens then it's an
indicator that the user doesn't have permission to read mysql.proc.

Attachments:

ogsadaiStoredProcedures.zip [OGSADAITestDatabase/ogsadaiStoredProcedures.zip]