Deleting all rows of all tables of a list of MySQL databases

Like most Java developers, I use the database technology with just enough knowledge of SQL, JPA/JPQL. In one of our application, we have a bunch of mysql databases, and recently, I had a need to clean up the contents of all the databases, all the tables, without deleting the structure. In other words, there was no choice of dropping and recreating databases. Also there was no choice of any UI tools, or not even a choice of writing something from my favorite Java. The only option was shell-script with a headless unix system.

Therefore, I wrote this simple utility in shell to clean-up a list of databases. It may be handy. Enjoy!

#!/bin/sh cleanup_mysql(){         read -s -p "Enter MySQL root Password: " mysqlpassword         db_list="Database1   Database2   Database3   Database4"         dml="SET foreign_key_checks = 0;"         for db in $db_list         do                 echo "Cleaning up database: $db"                 TABLES=`mysql -u root -p$mysqlpassword $db -e 'show full tables where Table_Type = "BASE TABLE"'|awk '{ print $1}'|grep -v '^Tables'`                 for table in $TABLES                 do                         dml="$dml DELETE FROM $db.$table;"                 done         done         dml="$dml SET foreign_key_checks = 1;"         mysql -u root -p$mysqlpassword --e="$dml" } cleanup_mysql

GlassFish3: Using encrypted password for JDBC connections

If you had configured a jdbc-connection-pool, or jms-connection on GlassFish3, you may have noticed that the passwords are stored in plain-text in config/domain.xml. That is unfortunate, and a security-guy in your organization may give you a hard-time for it.

Hopefully, a future release of GlassFish may choose encrypted-password-storage as the DEFAULT, but till then, creating password-alias could be a satisfactory alternative. There is enough documentation on the create-password-alias command, but I thought of creating a really simple example in this context.

Let's create a jdbc-connection-pool called MyConnectionPool from cmd-line. It could be done with a single command or easily through admin-console as well.

asadmin> create-jdbc-connection-pool --datasourceclassname oracle.jdbc.pool.OracleDataSource --restype javax.sql.DataSource MyConnectionPool

asadmin> set resources.jdbc-connection-pool.MyConnectionPool.property.user=dbuser
asadmin> set resources.jdbc-connection-pool.MyConnectionPool.property.password=${ALIAS=db_password_alias}

asadmin> set resources.jdbc-connection-pool.MyConnectionPool.property.url="jdbc:oracle:thin:@host:port:service" 

And if you look at the domain.xml , you would see something like this:

      <property name="password" value="${ALIAS=db_password_alias}"></property>

Now let's provide actual-password, the one we want to store in an encrypted manner:

asadmin> create-password-alias
Enter the value for the aliasname operand> db_password_alias
Enter the alias password> *******
Enter the alias password again> ******
Command create-password-alias executed successfully.

You can take a peek in the config/domain-passwords file, which maps aliases to passwords, and confirm that your password above can not be deciphered. That's it, but don't forget to verify that a connection could be retrieved using the encrypted password.

asadmin> ping-connection-pool MyConnectionPool

Command ping-connection-pool executed successfully.

If you fat-fingered, or if you would like to change the password, you can use update-password-alias command. Refer to glassfish commands documentation for more information, and that above command works in a non-interactive mode as well, if you prefer to script it.

Good-luck getting that security-guy off your back!

Weblogic12c: SQLException Invalid column index

Intermittently occurring problems are always hard to fix. I encountered one today in a webapp running on Weblogic12c connecting to Oracle database. There were random incidences of following exception in using a PreparedStatement:

  java.sql.SQLException: Invalid column index

    at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5330)

    at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:5318)

    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:282)

    at weblogic.jdbc.wrapper.PreparedStatement.setString(PreparedStatement.java:903)

The same code would work just fine in normal operations and had no clue as to why above would occur every now and then, even with same set of data. I have overly simplified it but the code line from which this sporadic exceptions was thrown seemed something like this:

   pstmt.setString(1,somevalue);

Typically, one may think of binding("?") mismatch in SQL and setting them from java-code, but that could be ruled out as the app has been working just fine, barring rare occassions of above exception. Moreover, the SQL used to construct the PreparedStatement is defined in a static-final-variable, and no question of changing it dynamically. So what could cause this SQL Exception for some http-requests and not for the other?

In doing some extensive load-testing, we observed an increased frequency of above exceptions. And then in looking for patterns, it turned out if two shortly-spaced http-requests exercised the same PreparedStatement, above exception may occur. Try increasing the delay between those two requests and there won't be a problem. Or do something else in-between to exercise other code, other PreparedStatements/Statements etc, and you won't reproduce it.

With that in mind, I created a simple HtmlUnit based traversing of the app-pages to quickly exercise the same PreparedStatement, and managed to reproduce the intermittent problem more frequently. I deliberately chose to use the same set of data for requests and snooped the http-traffic. The requests appeared the same on the wire, and with same set of key-values, the first request used to work and the subsequent one in shorter time-span would fail.

The SQL wasn't changing, the data wasn't changing, therefore, I ruled out code-related errors and turned my attention to Weblogic DataSource configuration. After fiddling with some related Connection-Pool parameters, I stepped on Statement-Cache-Sizing parameter, and here is the description from Weblogic12c Admin Console:

Statement Cache Sizing:

The number of prepared and callable statements stored in the cache. (This may increase server performance.) WebLogic Server can reuse statements in the cache without reloading the statements, which can increase server performance. Each connection in the connection pool has its own cache of statements. Setting the size of the statement cache to 0 turns off statement caching.

In optimizing things, I guess Weblogic12c may have a bug with Statement-Cache-Sizing that surfaces in certain situations, and disabling this caching took care of the "SQLException: Invalid column index" problem for me. It is not ideal, but nevertheless a workaround.

Note the performance implications, before using it in production-environment.

How to disable Statement-Cache-Sizing?

Admin-Console-->Services--->Data Sources-->(select-your-DS)-->Connection Pool

and set Statement-Cache-Size = 0

Post alternatives if you come across any, otherwise, happy-surfing!

Seam 3 on Weblogic 12c

The JSR299/CDI(context-dependency-injection) is now part of any JavaEE6 application server, and I have had been exploring it on one such app-server Weblogic 12c, which bundles Weld 1.1.3.SP1 as the CDI container.

In JSF world, they say, it is a good practice to use appropriate scope on JSF managed-beans to make efficient use of the server-memory, and that the same applies to CDI managed-beans. For CDI beans, the supported scopes are @RequestScoped, @SessionScoped, @ApplicationScoped, and @ConversationScoped. However, there is no support in CDI for a JSF equivalent @ViewScoped bean, which happens to be the best choice in certain use cases.

So what do we do if we want @ViewScoped added to CDI managed-beans? The answer in my search so far is: cdi-extensions. The seam-faces module from JBoss is one such CDI extension that adds the @ViewScoped support. One simply needs to add the seam-faces module jars to the web or enterprise archive and the @ViewScoped is added automagically.

The auto magic part is that the seam3 modules are BDAs(bean-deployment-archives), jars that contain META-INF/beans.xml file, and therefore they are scanned for beans in the CDI bootstrap. The seam-faces module has dependencies on other seam-modules such as seam-international and those jars are also needed. A recommendation if you are using maven is to add a dependency management to seam-BOM(bill-of-material) and you would get all the needed dependencies, tested version jars etc. That's it per the JBoss documentation on getting the seam-faces working.

--snip--
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <seam.version>3.0.0.Final</seam.version>
    </properties>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.jboss.seam</groupId>
                <artifactId>seam-bom</artifactId>
                <version>${seam.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <dependencies> 
        <dependency>
            <groupId>org.jboss.seam.faces</groupId>
            <artifactId>seam-faces</artifactId>
        </dependency>
       ........
    </dependencies>

--snip--

In reality however, getting seam-faces to work on Weblogic 12c wasn't an easy experience. It failed with a number of ClassNotFound Exceptions despite using the BOM dependency. It seemed to presume the app-server to contain some seam-classes. That wasn't the case for weblogic, and adding explicit dependencies to the pom helped get past this issue.

Next it failed due to unsatisfied CDI dependencies with exception as follows:

--snip--

Caused By: org.jboss.weld.exceptions.DeploymentException: WELD-001408 Unsatisfied dependencies for type [Messages] with qualifiers [@Default] at injection point [[parameter 2] of [method] org.jboss.seam.faces.status.MessagesAdapter.convert(PhaseEvent, Messages)]

at org.jboss.weld.bootstrap.Validator.validateInjectionPoint(Validator.java:258)

at org.jboss.weld.bootstrap.Validator.validateObserverMethods(Validator.java:484)

at org.jboss.weld.bootstrap.Validator.validateDeployment(Validator.java:314)

at org.jboss.weld.bootstrap.WeldBootstrap.validateBeans(WeldBootstrap.java:361)

at com.oracle.injection.provider.weld.WeldInjectionContainer.start(WeldInjectionContainer.java:105)

--snip--

Above exception indicates a problem satisfying an injection-point inside MessagesAdapter bean inside seam-faces. It seems to inject "Messages" bean into MessagesAdapter and fails to find the "Messages" bean. Interestingly, the "Messages" bean is defined inside seam-international module and that was part of the deployment-archive, but still it didn't find the bean. So after some deliberation and a separate test, I concluded that there is a problem/bug in Weblogic 12c that cyclic dependencies across BDAs are not satisfied. 

In other words, if you have two BDAs as part of a  test.war: a.jar and b.jar, added in WEB-INF/lib, and then trying to inject a bean from a.jar into a class in b.jar doesn't work. This is needed per the JSR 299 specification. However, there is no problem in resolving beans inside the same BDA. So what's a workaround? Merge the two jars: a.jar and b.jar into a single jar and add that one to test.war.

Here is a way to create an uber jar of seam-faces and seam-international using maven:

--snip--

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

        xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

        <modelVersion>4.0.0</modelVersion>

        <groupId>com.suhas.commonjar</groupId>

        <artifactId>seamfaces-n-seami18n</artifactId>

        <packaging>jar</packaging>

        <version>1.0-SNAPSHOT</version>

        <name>seamfaces-n-seami18n</name>

        <url>http://maven.apache.org</url>

        <dependencies>

            <dependency>

                <groupId>org.jboss.seam.faces</groupId>

                <artifactId>seam-faces</artifactId>

                <version>3.0.0.Final</version>

            </dependency>

            <dependency>

                <groupId>org.jboss.seam.international</groupId>

                <artifactId>seam-international</artifactId>

                <version>3.0.0.Final</version>

            </dependency>

            <dependency>

                <groupId>org.jboss.seam.solder</groupId>

                <artifactId>seam-solder</artifactId>

                <version>3.0.0.Final</version>

                <scope>provided</scope>

            </dependency>

        </dependencies>

        <build>

                <plugins>

                        <plugin>

                                <groupId>org.apache.maven.plugins</groupId>

                                <artifactId>maven-shade-plugin</artifactId>

                                <executions>

                                        <execution>

                                                <phase>package</phase>

                                                <goals><goal>shade</goal></goals>

                                        </execution>

                                </executions>

                                <configuration>

                                        <finalName>uber-${artifactId}-${version}</finalName>

                                </configuration>

                        </plugin>

                </plugins>

        </build>

</project>

--snip--

With above uber jar in the deployment-archive instead of seam-faces and seam-international helped resolve the CDI unsatisfied-dependencies problem, and now we have seam-faces working on 12c.

In summary, if you run into unsatisfied-dependencies problem on Weblogic 12c across two library BDAs, then see if creating an uber jar workaround may be be acceptable.