Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <blockquote> <p>Unable to call a stored procedure in MySQL</p> </blockquote> <p>You've created a procedure in MySQL 5. You've granted EXECUTE privileges to your application's user account. But when your application prepares a statement to call that procedure, an exception is thrown. Perhaps it's a NullPointerException deep inside of ConnectorJ. Perhaps the exception claims that the procedure does not exist. Or perhaps you've gotten lucky enough to get the exception that leads you down the right path(Driver requires declaration of procedure to either contain a ''\nbegin'' or ''\n'' to follow argument declaration, or SELECT privilege on mysql.proc to parse column types). Here are the troubleshooting steps that I've gleaned from hours of searching the MySQL forums.</p> <p>First, open two console windows. In one, log into MySQL as the application user. In the other, log in as root. In your app window, list the databases.</p> <pre><code>SHOW DATABASES; </code></pre> <p>If your application's database does not appear, then you should go to your root window and grant database privileges.</p> <pre><code>USE mydatabase; GRANT ALL ON mydatabase TO appuser; </code></pre> <p>Now go back to the app window. List the databases again to ensure that it appears. Now that it does, go into it.</p> <pre><code>USE mydatabase; </code></pre> <p>Once you're in, try to view the procedure.</p> <pre><code>SHOW CREATE PROCEDURE myproc /G </code></pre> <p>One of three things will happen. If you get a message saying "PROCEDURE myproc does not exist" then the app user does not have privileges for it. Go back to the root window and grant them.</p> <pre><code>GRANT EXECUTE ON PROCEDURE myproc TO appuser; </code></pre> <p>The second thing that might happen is that the procedure will be found, but the body will not be listed. Instead, it will say "Create Procedure: NULL". If so, go back to the root window and take care of that.</p> <pre><code>GRANT SELECT ON mysql.proc TO appuser; </code></pre> <p>The third thing that could happen is what you want to happen; the body of the procedure will be listed. Now you should be able to call the procedure from your application.</p> <blockquote> <p>Warnings</p> </blockquote> <p>You may be tempted to take a couple of short-cuts to resolve problems like these. Please don't. Shortcuts may cause bigger problems down the road.</p> <p>The first thing you might try is to use the root account from within your application. This is extremely dangerous. Security comes in layers. While you should use stored procedures and prepared statements to avoid SQL injection attacks, you should also apply the principle of least privilege just in case they occur anyway. Allowing your application to connect as root gives an attacker access to your most valuable resource: your data. But giving each part of the application a separate account with only the privilege that it requires quarantines the bad guy.</p> <p>The second thing that you might try after hours of research is to set the "noAccessToProcedureBodies" flag in the ConnectorJ connection string. Please avoid this flag, as it circumvents the parameter type checking that the JDBC driver provides for you. This flag causes ConnectorJ to convert all of the parameters to strings, which MySQL will then convert back to the required type.</p> <p>But by walking through the problem step-by-step, these short-cuts should not be necessary.</p> <blockquote> <p>Reference</p> </blockquote> <p><a href="http://adventuresinsoftware.com/blog/?p=74" rel="nofollow">http://adventuresinsoftware.com/blog/?p=74</a> </p>
    singulars
    1. This table or related slice is empty.
    1. POCreating stored procedure again in MySQL DB
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USQuery Master
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload