Category Archives: MySQL

How do i connect to development/local MySQL in Google App Engine (GAE) application?

Place mysql-connector jdbc jar here:

<googleAppEngineSDKBundle>\<appEngineSDK>\lib\impl\

You can find location of your appEngineSDKBundle by: Project>Properties>Google>SDK>Configure

Configure Cloud/Local MySQL

Project>Properties> Google>  App Engine>

Enable (checkbox yes) Google Cloud SQL

App Engine SQL Instance – put any junk values here even if u don’t have Cloud SQL yet.

Select Radio button Use MySQL Instance, and hit configure link to configure.

Click OK and close

The Code

import com.google.cloud.sql.jdbc.Connection; //This is your Connection Class.

Class.forName(“com.mysql.jdbc.Driver”);

conn = (Connection) DriverManager.getConnection(“jdbc:google:rdbms://localhost:3306/root”);

Even though your code seems to point to Cloud, while running locally based on all the above configuration, it will actually point to your local MySQL instance.

See here: http://stackoverflow.com/questions/8952696/java-google-app-engine-and-google-cloud-sql-running-on-local-dev-server (Mark Scheel’s explanation)

Advertisements

Error Message: No suitable driver found for jdbc:mysql:.. in a maven jpa/hibernate/mysql project

Problem Statement

In your maven project, with appropriate dependencies for Hibernate/MySQL, you get the following error – when jpa-hibernate code is invoked:

WARN : org.hibernate.connection.DriverManagerConnectionProvider – no JDBC Driver class was specified by property hibernate.connection.driver_class
WARN : org.hibernate.cfg.SettingsFactory – Could not obtain connection to query metadata
java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/somedb

Solution

The solution is in the warning itself. Note the line that says:  no JDBC Driver class was specified by property hibernate.connection.driver_class

In your persistence.xml file add the following line:
<property name=”hibernate.connection.driver_class” value=”com.mysql.jdbc.Driver” />

That’s It!

MySQL reinstall on Windows fails at ‘Start Service’. Why?

Problem Statement:
I just tried to reinstall MySQL 5.5x on Windows 7. But it fails at the point where it tries to start MySQL Service i.e ‘start service’ step.

Solution:
1. Uninstall MySQL. (Control Panel > Add Remove Programs)
2. Remove any MySQL Service if existing (: sc delete MySQL).
3. This is the most important step(who would have imagined!) – and the one that causes reinstall failure:
On Windows7 delete this hidden dir: c:\programdata\MySQL
This dir contains some log files etc – which causes the reinstall to fail. On Windows Vista/XP etc.. there would be a equivalent dir too (perhaps in another location).

On Windows XP, you should delete this directory:
C:\Documents and Settings\All Users\Application Data\MySQL.

How do i allow/prevent remote connection to MySQL?

If you are not able to connect to MySQL(typically the error message will be ‘Access Denied’) from a remote host, you need to look into these:

Have you granted permission to connect from remote host ?

mysql> grant all on *.* to ‘<mysqluser>’@'<remoteHostName>’;

The above grants <mysqluser> to connect from <remoteHostName> to any database(*.*);

What does the above grant SQL actually do ?

It adds/updates the ‘user’ table in default mysql database (although you always see 0 rows affected upon running the grant sql!).

You can see this here (login as root):

mysql> use mysql;

mysql> select host, user, password from user;

In case this still does not work for you, the problem is most likely that you do not have the correct ‘host’, ‘user’ values sitting in that ‘user’ table.

try giving <remoteIPAddress> in place of <remoteHostName> while running the grant SQL.

Other things to look at:

See the following files:

/etc/hosts, /etc/hosts.allow, /etc/hosts.deny – check if the remote IP/Host has been explicitly denied access. 

Check if IPTables has a rule to block remote host:

# iptables -L

How can i monitor and improve performance of my Apache/Tomcat/MySQL application?

Monitor Apache

You can view status of your Apache Web Server by going to a URL of the kind:

This allows us to view status of Apache by going to a URL like:
If the above URL does not work, your apache is probably not configured to return status. For information on how to set it up for this, see Apache Mod Status.

To view the JK Status, go to a URL of the kind:

http://host/jk-status

Monitor-Tomcat

You can view a lot of useful statistics of Tomcat by looking at its Admin Console:

http://<IP:port>/ > click on status link.

Things to look for here are under the approriate headings JVM, jk-8009 (assuming apache is configured to connect via jk on port 8009), http-8080.

JVM

Free Memory – Not sure how this works.

Total Memory – Not sure how this works.

Maximum Memory – this is the maximum amount of memory available to tomcat server, it is the value of the -mx setting in your tomcat startup java command (in catalina.sh). If no mx setting is done, a default value is assumed (which is different for windows and linux)

 

jk-8009 or http-8080

Here the important things to look at are:

Max threads: 200 Current thread count: 28 Current thread busy: 8
Request count: 19 Error count: 0

Max threads – this is maximum number of threads that tomcat can have to process incoming requests (and its internal functions).

Current thread count – this is total number of threads in tomcat’s threadpool at present. Threads will get garbage collected from this pool. And the pool size can grow to ‘Max threads’ size when necessary.

Currrent thread busy – this is total number of threads that are processing variour requests at present.

Request count – total number of http requests received by tomcat since the time it was started.

Improve -Tomcat

You have a performance problem in tomcat when:

Current Thread Count is near about Max Threads. This is a clear indicator that tomcat is receiving more requests faster than it can handle. The default Max Thread setting is 200 which is good enough for most applications, but you can change it here in <TOMCAT_HOME>/conf/server.xml – look for your connector element, here you can change (or add if not present) maxThreads attribute. See http://tomcat.apache.org/tomcat-5.5-doc/config/http.html . But beware, you cannot increase this value indiscrimnately(will affect memory and performance both), the optimum value is based on your hardware, and primarily the number of CPUs you have (more the better for increasing maxThreads). Thumb Rule: Do not change defaults, unless you really have to.

You see Out of Memory Exceptions in your catalina log. See the <TOMCAT_HOME>\logs\catalina.out log file. This is a clear indicator that your application needs more memory than what is available. The min,max memory available can be changed here: <TOMCAT_HOME>\bin\catalina.sh or .bat.  You can add a entry like this somewhere  (A good place would be immediately before this line: # Set juli LogManager if it is present)

(on linux): JAVA_OPTS=”$JAVA_OPTS -ms256M -mx512M”

(on windows): set JAVA_OPTS=-ms256M -mx512M

Monitor MySQL

You can check lots of important mysql params by going to mysql prompt and typing as follows:

mysql> SHOW VARIABLES;

One of things of interest in the response would be: max_connections. This will tell you how many max concurrent connections are allowed.

You can also find out how many concurrent MySQL connections exist at any point in time by using following command:

mysql> show processlist;
The number of rows in the response is equal to number of connections to MySQL at that time.

Improve MySQL

The most important thing you need to find out is if you are running out of db connections. This you can find out by using the above mentioned commands i.e show processlist shows equal number of connections as max_connections. If this happens you need to increase the max_connections.. HOW?

Ofcourse check the usual things in your db connection pool, you pool size cannot be greater than max_connection value ofcourse.

How to see Log of DB Queries being executed?

How to find how much time is being spent in execution of each query?

How to find the amount of data(bytes) being returned by each query?