A Couple of MySQL Tips - Join on Three Tables and Select Results into a File

These are a couple of things that I had to do with a mysql database lately and I thought that I would pass on these tips.

We were debugging a problem where certain data elements were dimensioned across three tables.  While debugging program logic, we wanted to see the data aggregated by the common column across all three tables.  Here is the basic sql statement to do that:

SELECT * (or any number of columns separated by commas) FROM table1 join table2 ON table1.primarykey = table2.foreignkey

join table3 ON table2.primarykey = table3.foreignkey;

OK, so once you do that, you want to put it into a separate file so that you can ftp it from the server to the dev platform to analyze the data.  Here is how you select results into a file:

SELECT * (or any number of columns separated by commas) FROM table1 INTO OUTFILE '/tmp/table1.txt';

Hope this helps someone.

Read More

MySQL Can not issue data manipulation statements with executeQuery()

Had an interesting MySQL error.  It was:  Can not issue data manipulation statements with executeQuery().

This was the code snippet that caused it:

try {
stmt = conn.createStatement();
myQueryString = "delete from groupnames where id=" + gid;
stmt.executeQuery(myQueryString);

The thing to notice is the query string.  What it was doing, was deleting a record in a table.  It wasn't a query so the line in red is incorrect.

It should have read:

try {
stmt = conn.createStatement();
myQueryString = "delete from groupnames where id=" + gid;
stmt.execute(myQueryString);

Notice that instead of executeQuery, because there is no query in a delete, it should merely read execute.

Hope this helps someone.

Read More

MySQL ROW_COUNT() Bug?

Had a strange bug with a MySQL stored procedure.  Consider the following line:

START TRANSACTION;

Insert into conversations(subjectLine) values(subjectLine);

IF (ROW_COUNT() > 0) then

set @pass1 = 1;

end if;

 Look closely at the IF statement.  It works perfectly on a Windows server.  When you put the CREATE statement on a Linux machine in the mysql command line, it fails and fills with garbage.  

The fix took some head-scratching, but here it is:

START TRANSACTION;

Insert into conversations(subjectLine) values(subjectLine);

IF ((SELECT ROW_COUNT()) > 0) then

set @pass1 = 1;

end if;

I had to put (SELECT ROW_COUNT()) to make it work in the Linux command line.

Read More

Strange MySQL Error

Had a strange MySQL error:

ERROR 1033 (HY000): Incorrect information in file (table_name)

We couldn't log into our app.  It looked like the database connection wasn't happening.  The logs were useless.  All they said was that there was a null pointer exception for getting the data back from the database.

We have a Linux server, so I typed in the /etc/init.d/mysqld stop  and then issued the start command:

/etc/init.d/mysqld start .  It failed.  I did it again (stop and start) and it said OK.  I could log into the database.  I could show tables.  But when I went to select from the tables, I got the above error.

Nothing worked.  Finally I rebooted the server and it was fixed.  If anyone has an idea of what caused this, please leave a comment.

Read More

Data source rejected establishment of connection

It was frustrating. I set up a Java JDBC connection pool to MySQL, and the app ran for awhile, then it would not connect. Obviously I had leaking connections somewhere.

The message in the transcript log was:
Data source rejected establishment of connection, message from server: "Too many connections"

  I went and tried closing all of the connections, but the app is a fairly large one. What to do? I Googled around and there was no obvious way of seeing where the connection leak was, so I opted for brute force. The pseudo code for establishing a connection with at connection pool looks like this:

 DataSource ds=getDataSource();
 Connection conn=ds.getConnection();

and to close the connection, it was:

conn.close();

 So the way that I solved it and found the connection leak, was that I added a couple of lines to the above code.

In the open connection method, I added

System.out.println("Open Connection " + conn.toString();

and in the close method, before the close statement, I added:

System.out.println("Close Connection " + conn.toString();

It prints out stuff in the console like this:


Open  Connection ProxyConnection[PooledConnection[com.mysql.jdbc.JDBC4Connection@e78c1b]]
Close Connection ProxyConnection[PooledConnection[com.mysql.jdbc.JDBC4Connection@e78c1b]]

I then match up the open and closes as I do stuff, and find my unclosed connections leaks.

Hope this helps.

Read More

Setting Up JNDI JDBC MySQL Connection Pool in Tomcat

So it was time to set up a connection pool for our high concurrency application.  It seemed like an easy thing to do.  I went to avajava.com followed one of their instruction tutorials, and burned a whole afternoon debugging.  Tomcat 7 has connection pooling built in, so I figured it would be a walk in the park.  Man, I got the following list of errors:

• org.apache.tomcat.dbcp.dbcp.BasicDataSource cannot be cast to org.apache.tomcat.jdbc.pool.DataSource
• name is not bound in this context. unable to find 
• java.lang.ClassNotFoundException: org.apache.tomcat.jdbc.pool DataSourceFactor
• java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp.BasicDataSource cannot be cast to org.apache.tomcat.jdbc.pool.DataSource
• java.lang.ClassNotFoundException: org.apache.tomcat.jdbc.pool DataSourceFactory

After wasting a whole bunch of time, I finally got it to bind to the database resource, but I had an error with the login stored procedure.  It was this one:

• mysql - java.sql.SQLException: Parameter number 3 is not an OUT parameter

I knew that I was getting some sort of binding but not a good connection.  Not knowing what I didn't know, I decided to do a debug on my connection called conn:

                       System.out.println(conn.toString());

System.out.println(conn.getCatalog());

System.out.println(conn.getAutoCommit());

System.out.println(conn.getMetaData().allTablesAreSelectable());

System.out.println(conn.getMetaData().getDriverName());

System.out.println(conn.getMetaData().getMaxConnections());

System.out.println(conn.getMetaData().supportsStoredFunctionsUsingCallSyntax());

System.out.println(conn.getMetaData().supportsStoredProcedures());

System.out.println(conn.getMetaData().allProceduresAreCallable());

It was quite an interesting transcript.  It told me that getMaxConnections() was zero, that allTablesAreSelectable was false, and allProceduresAreCallable() was false.  I spent a lot of time chasing down this dead end rabbit hole.

Finally I went to the expert:

http://www.tomcatexpert.com/blog/2010/04/01/configuring-jdbc-pool-high-concurrency

I followed the instructions implicitly and voila -- every thing works:

Simple Connection Pool for MySQL

<Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
/>

The first thing we notice is the factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" attribute.
When Tomcat reads the type="javax.sql.DataSource" it will automatically configure its repackaged DBCP, unless you specify a different factory. The factory object is what creates and configures the connection pool itself.

There are two ways to configure Resource elements in Apache Tomcat.

Configure a global connection pool

File: conf/server.xml

<GlobalNamingResources>
  <Resource type="javax.sql.DataSource"
            name="jdbc/TestDB"
            factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/mysql"
            username="mysql_user"
            password="mypassword123"
/>
 </GlobalNamingResources>

You then create a ResourceLink element to make the pool available to the web applications. If you want the pool available to all applications under the same name, the easiest way is to edit the File: conf/context.xml

<Context>
  <ResourceLink type="javax.sql.DataSource"
                name="jdbc/LocalTestDB"
                global="jdbc/TestDB"
/>
 <Context>

Note, that if you don't want a global pool, move the Resource element from server.xml into your context.xml file for the web application.

And to retrieve a connection from this configuration, the simple Java code looks like

Context initContext = new
 InitialContext();
   Context envContext  = (Context)initContext.lookup("java:/comp/env");
   DataSource datasource = (DataSource)envContext.lookup("jdbc/LocalTestDB");
   Connection con = datasource.getConnection();

Mister TomcatExpert is really an expert.

Read More

MySQL Connection Pooling with Java & Tomcat Tip

It is time we got serious with a webapp of my to quit setting up and tearing down connections to the mysql database.  I decided to implement a connection pool.  There are examples all over the web on how to do this.  Luckily, I am using Apache Tomcat 7 and it has the connection pooling built in.

So I was implementing the java code:

 import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.Statement;

  import org.apache.tomcat.jdbc.pool.DataSource;
  import org.apache.tomcat.jdbc.pool.PoolProperties;

and the import was throwing the class not found error.  I thought "WTH -- I am using Apache 7".  As it turns out, I had to go to the properties, and add the Apache Tomcat Library (not the jars but the libraries) in the Project Build Path.  Problem solved.  Hope this helps someone.

Read More

A Whole Bunch of MySQL Stuff

Had a major event today on our server.  MySQL took the server down.  It was the daemon mysqld.  It started to spike the memory, which started the system to page furiously and then we had a kernel dump.  Went to /var/log/messages and grepped for “memory” and saw that UID27 reported out of memory as part of the crash output.  The message was

 [ERROR] /usr/share/mysqld: Out of memory (Needed xxx bytes)

So what to do?  Obviously we didn't have enough RAM allocation.  I use entirely an innodb engine with mysql.  So I had to find where mysql was getting its configs from.

From the server console, I typed in:

mysql --help

and among all of the stuff that was returned, it told me where to look for the config file.  It said:


Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf

So I went to /etc/my.cnf and used the vi editor.  I added

innodb_buffer_pool_size = 1000M

I added it under [mysqld].  We have 4 Gigs of RAM and the allowable value is up to 80% of the RAM.  Previous to this it was 16M.

Then I stopped the database by issuing the following command:

/etc/init.d/mysqld stop

No issues.

To start it, I issued

/etc/init.d/mysqld start

and it failed.  This is what it said:


[root@ap1 etc]# /etc/init.d/mysqld start
Timeout error occurred trying to start MySQL Daemon.
Starting MySQL:                                            [FAILED]

Well, went back to /etc/my.cnf and I noticed that I had forgotten the "i" in the innodb_buffer_pool_size.  I added that, and it started.

I gotta tell you, the application now screams!!  I should have done a RAM allocation earlier.

Read More

MySQL Cleaning Out Database Duplicates

I had a MySQL database that was automatically populated with raw data and no checking. So as a result, I had duplicate rows of data and each data row had a unique key id number (auto increment). The idea was to get rid of the duplicate rows. I was lucky that one of the fields in the row was an equipment serial number, and I used that fact to easily eliminate the duplicates.

Lets say that the table was called equipment_table. I would copy the table to make equipment_table_clean with the following:

CREATE TABLE equipment_table_clean LIKE equipment_table;

We know that the the serial number is the thing that defines duplicates so:

ALTER TABLE equipment_table_clean ADD UNIQUE (serial_number);

Then feed the data into the new table with the following command:

INSERT IGNORE INTO equipment_table_clean (serial_number, name, col1, col2) SELECT serial_number, name, col1, col2 from equipment_table;

Works like magic and saves a lot of work. Then drop the old table and rename the new table (after you have checked the data).

Read More

MySQL TODAY() Function from TIMESTAMP

MySQL is my database of choice, and its sql query language is part of the package.

In an SQL table, I have a series of transactions that are timestamped. The timestamp column is of type TIMESTAMP NOT NULL so for every transaction insert, the NOW() timestamp is inserted in the format yyyy-MM-dd HH:MM:SS. This is all fine and dandy except a timestamp is not really a date. However one can get a date with the following syntax:

SELECT DATE(timestamp_column) and one will get a date.

MySQL has a NOW() function, but I needed a TODAY() function to select all of the transactions occurring today from a timestamp column. It would work something like this:

SELECT * FROM transaction_table where transaction_timestamp = TODAY();

Of course transaction_timestamp is not a date so it would have to be re-worded to DATE(transaction_timestamp).

The way to get a TODAY() function is to use the following SQL statement:

SELECT * FROM transaction_table WHERE DATE(timestamp_column) = CURDATE();

CURDATE() is current date and it works like a charm.

Read More

Java MySQL SQL Tip Comparing Timestamp in Column to Current DateTime

There are many ways to skin a cat. I have a database table where I have to compare a column timestamp with the current date/time as well as adding hours and minutes. I realize that there is an SQL NOW function, but I also need the now time converted to calendar to be able to add and subtract calendar units, and I construct the SQL statement in Java.

Here is the syntax of the snippet in Java for comparison to the current datetime:

java.util.Date utilDate = new java.util.Date();

java.sql.Timestamp sqlDate = new java.sql.Timestamp(utilDate.getTime());

try {

stmt = conn.createStatement();

String myQueryString = "SELECT * FROM sale_table where saleStart < {ts '" + sqlDate.toString() + "' }";

Read More

JdbcOdbcDriver.finalize() line: not available

I was using Eclipse, with a Tomcat Java project, and every time I tried to start the server, I immediately jumped into debug mode, and the issue was:

JdbcOdbcDriver.finalize() line: not available

Google was no help.

I previously thought that this was the answer. I was wrong.

Finally I figured it out. I had some managed beans that were backed by a database. These managed beans were annotated @eager

When the server started the app from eclipse, they were instantiated and the tool went to the database to get the data. I had an exception thrown in the method from the database bean (a null pointer exception), and as a result, the connection to the database was being left open.

Somehow, the .metadata folder was buggered up and corrupted. I exited Eclipse. Then I went to the Eclipse workspace and copied the .metadata folder to my desktop to have a safe copy, then I deleted it in the workspace.

I re-started Eclipse. There were no projects. I imported them back in using Import > File System.

I had to fix the Build Path under the project properties and under the Window > Preferences, I had to reset the Tomcat Home. Voila, got rid of this super annoying problem.

That was the cause of the problem. Hope this helps someone.

Read More