iang, Member
I'm trying to add a report based on a stored procedure with a parameter. I've reduced the stored procedure to this simple testcase:
create procedure sp_DBR_SimpleTest(id int(10) unsigned)
begin
select id;
end//
When I add this to MyDBR, I see:
Report parameters
Report does not have any parameters
And when I attempt to run the report, I see:
DB error. SQL: call sp_DBR_SimpleTest();
I'm running MySQL 5.0.18-log, PHP 5.1.4 and MyDBR 1.2.
myDBR Team, Key Master
Although being perfectly ok as a MySQL variable definition, the complexity is bit more than the myDBR is prepared to handle. You should be ok with simplified version:
create procedure sp_DBR_SimpleTest( id int )
begin
select id;
end
If you really need integers so large that you need the 'unsigned' definition, let us know. Alternatively you can leave it out as well.
--
myDBR Team
iang, Member
Hi,
That sounds reasonable; however, I changed the SP but still see the same problem - no parameters appear when adding the report. I've checked the SP:
mysql> show create procedure mydbr.sp_DBR_SimpleTest;
...
CREATE PROCEDURE sp_DBR_SimpleTest( id int )
begin
select id;
end
myDBR Team, Key Master
The procedure is now ok and myDBR should be able to parse it without problems. You could check that the procedure actually works by issuing a command:
mysql> call sp_DBR_SimpleTest( 21 );
Does your setup work otherwise? You can run the pre-installed statistics reports that do have parameters.
--
myDBR Team
iang, Member
Yes, the SP itself seems to work.
mysql> call sp_DBR_SimpleTest( 21 );
+------+
| id |
+------+
| 21 |
+------+
1 row in set (0.16 sec)
The pre-defined statistics reports also work, and when edited they show the parameters correctly.
myDBR Team, Key Master
Do you use different username in myDR admin_username and when you create the reports?
myDBR 1.2 no longer requires SUPER privileges to operate. However, the only existing method in MySQL to find out procedure parameters without the SUPER privileges require that the stored procedures are created with same username as the myDBR admin. This is unfortunately an existing limitation in MySQL.
We'll add this to the documentation.
--
myDBR Team
aescripts, Member
I am having trouble getting parameters to work as well, do I need to create an admin user in myDBR that is the same as the database username?
myDBR Team, Key Master
A bit of background info on how things work in myDBR 1.2.
In order to support myDBR installation and usage without the need for SUPER-privilege (effectively meaning that myDBR had to be run under MySQL's root-account), we did changed how myDBR fetches the procedure parameters.
In myDBR < 1.2 parameters were fetched from MySQL system table (mysql.proc). Since this requires the SUPER-privilege, myDBR 1.2 uses now the "show create procedure" call internally. This works without the SUPER-privilege, but requires that the procedures are created under same account as the myDR's admin_username (or myDR's admin_username needs to be set as root). In current MySQL, even if you grant all privileges to user on the database, (s)he is not allowed to see the procedure source code created by other user.
So if you have problems getting myDBR to recognize the parameters, make sure that the stored procedure is created under same account as you gave to the myDBR's admin. Simply use same account for all myDBR related activities and you should be fine.
This affects only MySQL, in SQL Server we can use standard system tables for this.
We'll add a check and a proper notice to user in the next myDBR release in case of the missing privileges.
Hope this helps,
--
myDBR Team
iang, Member
Thanks - the issue was exactly that the stored procedures must be created with same username as the myDBR admin.
I'm very impressed with myDBR - keep up the good work! (and I know this is the wrong forum, but PDF generation for reports would be great in the next release!).
aescripts, Member
What if the myDBR installation and the mySQL database are on different machines?
myDBR Team, Key Master
What if the myDBR installation and the mySQL database are on different machines?
You can use data replication features of MySQL or take look at the Fedrated storage engine:
http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html
For easiest setup keep myDBR installation and the user database on the same server. If you want to report data from multiple databases at same time, usually the best way forward is to consider data replication.
http://dev.mysql.com/doc/refman/5.0/en/replication.html
MS SQL Server being more advanced database, have better support for accessing data from multiple servers.
Hope this helps,
--
myDBR Team
aescripts, Member
Unfortunately I am not able to run any kind of database on the same machine as myDBR. Is there no other option?
myDBR Team, Key Master
Unfortunately I am not able to run any kind of database on the same machine as myDBR. Is there no other option?
Ah,
sorry to misunderstood your question.
myDBR consist of:
myDBR application can be on any machine, just that it has connection to the database where myDBR objects reside. When myDBR is started, the database host and MySQL/MS SQL Server login credentials are entered.
Database objects can be created into a separate reporting database (called 'mydbr' by default) or directly to the user database. When separate database is created, the myDBR given database login credentials need to have select access to the database in order to read the data.
See the updated picture from documentation:
Hope this helps,
--
myDBR Team
aescripts, Member
So if you have problems getting myDBR to recognize the parameters, make sure that the stored procedure is created under same account as you gave to the myDBR's admin. Simply use same account for all myDBR related activities and you should be fine.
If the myMSQL database and the myDBR application are on different machines then how can I do what you instructed above. That is my question.
Thanks
Lloyd
myDBR Team, Key Master
If the myMSQL database and the myDBR application are on different machines then how can I do what you instructed above. That is my question.
myDBR application is a PHP application that is run in the server running the web server. It connects to database server using a MySQL user account specified in myDBR's setup screen (admin_username and admin_password). Web server and the database server can be run on same or on separate servers.
The database server contains the myDBR report database (mydbr) and the user database. In order to take use of myDBR 1.2's ability to run with user account not needing SUPER privileges, just make sure that the username you are entering into myDBR preferences (admin_username) is the same that is used when the report stored procedures are used.
Hope this clarifies the setup.
--
myDBR Team
aescripts, Member
The database server contains the myDBR report database (mydbr) and the user database. In order to take use of myDBR 1.2's ability to run with user account not needing SUPER privileges, just make sure that the username you are entering into myDBR preferences (admin_username) is the same that is used when the report stored procedures are used.
Yes, I think that is pretty clear and I am definitely doing this, yet I still can't get the parameters to be recognized. I am using Navicat for mySQL to create the procedures, would that cause an issue?
aescripts, Member
Yep, it was Navicat. When I was created the stored procedure from the command line it worked. Any recommendations on a GUI editor?
MySQL offers GUI tools MySQL Administrator / MySQL Query Browser as a free download. MySQL Query Browser has some limitations (ability to display multiple result sets from proceudres), but for editing it works quite nicely.
--
myDBR Team
myDBR Team, Key Master
Yes, I think that is pretty clear and I am definitely doing this, yet I still can't get the parameters to be recognized. I am using Navicat for mySQL to create the procedures, would that cause an issue?
MySQL offers GUI tools MySQL Administrator / MySQL Query Browser as a free download. MySQL Query Browser has some limitations (ability to display multiple result sets from proceudres), but for editing it works quite nicely.
--
myDBR Team
aescripts, Member
Thanks, but MySQL Query Browser doesn't seem to work either. The only method that seems to work so far is by doing it directly from the command line. Good news is that it is working now! Which is great news because I love what you guys are doing and am very happy to be able to start using it. Thanks again for all the support.
You must log in to post.