App Engine Cloud SQL project running locally on mySQL dev environment Access Denied

[JohnSuper]

The problem:

When running the sample guestbook application from the Cloud SQL and
GAE Plugin for Eclipse documentation locally from eclipse to a local
mySQL instance I get an exception:
java.security.AccessControlException: access denied
(java.lang.RuntimePermission accessClassInPackage.sun.jdbc.odbc)

I've seen this mentioned in this forum a few times, but no-one has
ever stated that they ever actually got it working.
The only response from google i've seen is "did you read the
documentation". Yes, for the past week

The Setup:

Windows 64-bit Vista
Eclipse 3.7.2
App Engine SDK 1.6.6
JDK 1.6
MySQL 5.5
mysql-connector-java-5.1.20-bin.jar


-VM arguments (generated by GAE Plugin for Eclipse from Google->App
Engine Settings menu)
:
-Drdbms.url=jdbc:mysql://localhost:3306/guestbook?user=root -
Drdbms.driver=com.mysql.jdbc.Driver -Drdbms.server=local -
Drdbms.password="" -Drdbms.user="root" -Drdbms.database="guestbook" -
Ddatastore.default_high_rep_job_policy_unapplied_job_pct=50 -Xmx512m "-
javaagent:C:\Users\john\eclipse\plugins
\com.google.appengine.eclipse.sdkbundle_1.6.6\appengine-java-
sdk-1.6.6\lib\agent\appengine-agent.jar"

Note that the documentation states to add some parameters to the -VM
argument rdbms.url, but since the GAE plugin manages this based on the
Google->App Engine Settings menu, the plug-in strips away anything you
manually add to the rdbms.url argument from the Debug As->Debug
configuration->arguments section.

I can connect to the local instance of mySQL fine when I go into Data
Source Explorer -> MySQL.DevInstance and browse the tables, so I know
mySQL and the Eclipse connection is fine.

It seems the App Engine Plugin is not handling some java VM security
policy rights for its mySQL JDBC connector.

Running the app on a local Dev Google Cloud SQL instance is fine.
When I select local MySQL instance I get the error on server:
java.security.AccessControlException: access denied
(java.lang.RuntimePermission accessClassInPackage.sun.jdbc.odbc)

I also tried a policy file grant permissions for the VM
java.lang.RuntimePermission accessClassInPackage.sun.jdbc.odbc with no
luck.

ANY help would be appreciated. I've tried everything I could find in
the docs and the net. If anyone has the GAE JDBC connector working
locally on a local mySQL instance using the GAE plugin for Eclipse in
Windows please let me know. I'd at least like to know that it is
working properly and not just theoretically possible!

thank you!

John

[Ken Ashcraft]

I'll try to find one of the GPE developers to figure out what is going wrong.  In the meantime, have you tried running outside of Eclipse?

https://developers.google.com/appengine/docs/java/cloud-sql/developers-guide#without_eclipse

Ken

[Raksit Ashok]

Even for local testing with mysql, in your java code, you should use production environment settings for jdbc driver (com.google.appengine.api.rdbms.AppEngineDriver) and url (jdbc:google:rdbms://....).

[ Also mentioned here: https://developers.google.com/appengine/docs/java/cloud-sql/developers-guide#using_the_java_development_server ]

GPE will add the right VM arguments (-Drdbms.url, -Drdbms.driver, etc), such that mysql will be used for local testing, if you have configured your App Engine settings that way.

Does your code use production env settings? If your code instead uses "com.mysql.jdbc.Driver" / "jdbc:mysql://localhost:3306/...", then you will run into exceptions similar to what you mentioned. 

hth,

raksit

[JohnSuper]

Yes that is the way I had it Raksit/Ken, and that is what the
documentation states is the way it "should work". But the problem is
that whoever wrote the documentation actually did not try it out as
written but relied on the typical IT guy catchphrase "it should work"
rather then trying it out (which I am also guilty of many times)

I did get it working however, but the documentation and instruction
posted for the guestbook example on Clould SQL docs and Eclipse Plugin
for Windows are both incorrect in some places and incomplete in
others. There is no way anyone could have it working just as posted
without making the following corrections:

1) To correct the error: java.security.AccessControlException: access
denied (java.lang.RuntimePermission accessClassInPackage.sun.jdbc.odbc
Java VM has security permission that restrict the VM from running some
things that access local resources when running in a browser. The
Java VM default is throw exception on anything sun.*
In your Java SDK directory you need to remove or modify these
permission. Modify JDK1.7.0_05\jre\lib\security\java.security file as
below:
Change (remove the sun.,):
FROM
package.access=sun.,com.sun.xml.internal.ws.,com.sun.xml.internal.bind.,com.sun.imageio.,com.sun.org.apache.xerces.internal.utils.,com.sun.org.apache.xalan.internal.utils.
TO
package.access=com.sun.xml.internal.ws.,com.sun.xml.internal.bind.,com.sun.imageio.,com.sun.org.apache.xerces.internal.utils.,com.sun.org.apache.xalan.internal.utils.

Change (not totally necessary but to avoid other problems):
FROM
package.definition=sun.,com.sun.xml.internal.ws.,com.sun.xml.internal.bind.,com.sun.imageio.,com.sun.org.apache.xerces.internal.utils.,com.sun.org.apache.xalan.internal.utils.
to
package.definition=com.sun.xml.internal.ws.,com.sun.xml.internal.bind.,com.sun.imageio.,com.sun.org.apache.xerces.internal.utils.,com.sun.org.apache.xalan.internal.utils.

This may leave open other java programs sun.* calls to local access
but for dev purposes this is the quickest solution without knowing
specifically what the AppEngine Driver is doing locally and messing
with app signing and policy files.

2) To correct the error "java.lang.ClassNotFoundException:
com.mysql.jdbc.Driver while calling that function"
The docs say that the CLASSPATH gets added automatically by the
plugin. This is not the case for the mySQL JDBC JAR driver that you
add to the Google Appengine mySQL settings.

Go to Windows -> Control Panel -> System -> Advanced Settings ->
Advanced Settings -> Environment Variables -> System Variables
Add a variable: CLASSPATH with a value C:\Java\mySQL-JDBC (or
wherever you stored your mySql JDBC jar mysql-connector-java-5.1.21-
bin.jar)

3) Error In the GuestbookServlet.java error on line with PrintWriter
out = resp.getWriter();
Need to add the "import: import java.io.PrintWriter;"

4) Error in the GuestbookServlet.java line with : public class
GuestbookServlet extends HttpServlet {
In the non-CloudSQL guestbook example it imports import
javax.servlet.http.*;
The CloudSQL docs say to change the imports to
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletRequest;

If you do that, You need to add: import
javax.servlet.http.HttpServlet;

5) In the Cloud SQL docs, Without Eclipse , dev_appserver for
Windows. Error starting it using provided instructions..
Instructions state use command to start the local dev server outside
eclipse:
appengine-java-sdk\bin\dev_appserver.cmd ^
--jvm_flag=-Drdbms.server=local ^
--jvm_flag=-Drdbms.driver=com.mysql.jdbc.Driver ^
--jvm_flag=-Drdbms.url=jdbc:mysql://localhost:3306/yourdatabase?
user=username&password=password ^
[options] war-location

In windows command prompt the "&" sign can not be used in a command as
it is a special character and the line breaks at that point in the
command, so you can't pass the password parameter this way (even with
quotes). So that command does not work a stated. No easy solution
without using a < to pipe input to the java VM. Easiest Just don't
use a mySQL password on local dev without eclipse I guess...

6) Cloud SQL docs state to add the following to the rdbms.url:
useInformationSchema=true useUnicode=true characterEncoding=UTF8
useServerPrepStmts=true
The eclipse plugin will not let you add anything manually to the
rdbms.url. When you add anything to it and save it it gets stripped
back out by the plugin (the rdbms.url is in the eclipse menu, right
click on project -> Debug As -> Debug configuration -> Arguments tab -
> VM arguments). No solution that I can find to this, but it still
runs without the commands, but the documentation as stated does not
work for this instructions.

7) Google Cloud SQL docs states: Although the above example connects
to the Google Cloud SQL instance as the root user, you can also
connect to the instance as a specific database user:
Connection c = DriverManager.getConnection("jdbc:google:rdbms://
instance_name/database", "user", "password");
This does not work. Your get an error doing it this way. The userID
and password is managed by the eclipse plug-in: Right-click on
project Google -> AppEngineSettings -> Use CloudSQL Instance (or my
SQL Instance) -> configure

8) When using Java 7 VM it does more strict verifications, you will
get an error when running the guestbook app and trying to submit an
guestbook entry.
You need to add the following switches to your VM debug arguments to
use JAVA VM 6 verifications rather then 7 when using JDK 7
right-click on project -> Debug As -> Debug Configuration -> Arguments
tab -> VM Arguments add the following to the end of the VM
arguments: -XX:-UseSplitVerifier

NOW the example guestbook app posted on the Google CloudSQL
documentation will work on a local mySQL instance thought Google
Eclipse Plugin on Windows. There is NOW WAY it could have possibly
worked for anyone as posted for Eclipse with GPE under Windows,
although everyone who has posted an issue about this was simply
redirected to re-read the documentation. I assume they just got
frustrated and must have gave up on it as no-one ever posted any
"thanks, it works now"

Please forward these corrections to your (cough.. cough..) QA
department to re-test and update the cloud SQL documentation (and
maybe post another sample would be nice too!)

John

On Jul 5, 7:52 am, Raksit Ashok <rak...@google.com> wrote:
> Even for local testing with mysql, in your java code, you should use
> production environment settings for jdbc driver
> (com.google.appengine.api.rdbms.AppEngineDriver) and url
> (jdbc:google:rdbms://....).

> [ Also mentioned here:https://developers.google.com/appengine/docs/java/cloud-sql/developer...
>  ]

[Saverio Campisi]

NOW the example guestbook app posted on the Google CloudSQL

documentation will work on a local mySQL instance thought Google
Eclipse Plugin on Windows.  There is NOW WAY it could have possibly
worked for anyone as posted for Eclipse with GPE under Windows,
although everyone who has posted an issue about this was simply
redirected to re-read the documentation.  I assume they just got
frustrated and must have gave up on it as no-one ever posted any
"thanks, it works now"

Thanks a lot John! I had troubles with the two exceptions mentioned above, but I solved thanks to your suggestions.

Best regards,

Saverio.