Debugging JDBC based applications

You are a Java developer and you use JDBC because you use a RDBMS like MySQL. Clearly you will be blind if everything you do is at JPA or ORM level. You must understand JDBC and databases if you want to find out why your application is suffering from performance issues, deadlocks and more.

The p6spy driver helps you with that. It will give you all the complete queries including parameters that are run in your system, when they are run and how long it takes to execute them. We have built a POB recipe that easily installs p6spy in tomcat. Below is a copy of it:

#!/bin/bash -e
# common/tomcat/p6spy-install.sh

TOMCAT_SETENV_PATH=/opt/tomcat/bin/setenv.sh
ARTIFACTORY_P6SPY_JAR=http://artifactory-server/ext-releases-local/p6spy/p6spy/1.1/p6spy-1.1.jar
SVN_P6SPY_PROPERTIES=http:/my.path.to.svn.spy.properties/spy.properties 

curl -o /opt/tomcat/lib/psspy.jar $ARTIFACTORY_JAR
common/tools/export-from-svn.sh  /opt/tomcat/lib/ SVN_P6SPY_PROPERTIES
sed -i 's/\(jdbc.*driverClassName\).*/\1=com.p6spy.engine.spy.P6SpyDriver/g' /opt/tomcat/lib/sample-app.properties
sed -i "/JAVA_OPTS -Dp6.home/d" $TOMCAT_SETENV_PATH
echo "JAVA_OPTS=\"\$JAVA_OPTS -Dp6.home=/opt/tomcat/lib/\"" >> $TOMCAT_SETENV_PATH

Breakpoints do not help when troubleshooting concurrency issues. Real world goes beyond the development box and so only hitting your application with a stress test (read JMeter for example) can prove that you have no issues that will later come back to your plate. At a minimum run those stress tests in Integration environment every so often.

MySQL EXPLAIN Divide and Conquer

Especially when you are using sub-selects (AKA subqueries) things gets not that clear when analyzing the results of EXPLAIN. A good technique is to run your subqueries and try to tune them separately before trying to adjust the whole query.

I found myself wearing the DBA hat during this long weekend resulting in a speed improvement of more than four thousand times.

As MySQL manual suggests make sure you run EXPLAIN and look first at those entries with the "extra" field stating "Using temporary" and/or "Using filesort".

Then identify the query bits using those conflicting tables and try to isolate them (divide)

Continue running EXPLAIN again on those bits to come up with a solution that increase performance (conquer).

Contrary to the belief it is not always a lack of indexes, in fact too many indexes will slow the query or other queries down, for sure it will slow down inserts.

Many times is about a query that should be rewritten.

Look for things like not filtering in the subquery. The WHERE clause is your friend. In my case I found the developer used a WHERE clause in the wrapper dataset but not in the sub-selects where he could have done the same for the very same key.

Look for multiple keys or indexes that are just redundant. Remember the rule: A composite index/key is read from left to right, there is no need to add other keys or indexes with portions of the composite, if your fields are organized in that order you can use one, two or more fields and that unique composite key will be enough.

Any job can be done using multiple ways, a developer can use any but the engineer should always pick the faster. Let us strive to be better Engineers when we develop code! Rewrite your query for which of course you must have documentation of what you are trying to achieve. Yes documentation is on the right side in the Agile Manifesto but that actually does not mean it is not necessary.

Learn by challenge. You will not master MySQL optimization by reading the whole manual and ten books. You will master it as you face problems and wonder why the occur. In that process you will definitely learn *just* what you need to perform your job.

In extreme cases some table structures will have to be changed. Even de-normalization will be sometimes the only option but please be sure there is nothing else to do with indexes or rewriting your query. Changing table structure might be hiding a lot more time, complexity and probably no that much efficiency improvement.

Windows account locked when working from a MAC

Your Enterprise Domain Controller is for sure expiring passwords but your MAC is not integrated with it and yet you access several network resources like Exchange and the Shared File System (AKA Common Internet File System or CIFS)

I thought I had found all the entries related to my account from the KeyChain.app and so I told the sysadmin. However today I had a second thought, what about if the KeyChain search is actually not that smart? And indeed. The search will always look at key entries but never inside them.

In my case the sysadmin was seeing the account being locked from "\\workstation" which is not a name defined in any place after all.

Running the below command I was able to find several old entries for Remote Desktop Connections, File System, Exchange, iCal, Address Book and more.

$ security dump-keychain | grep $username

Then I realized I really had too many entries so I started going through them using:

$ security dump-keychain | more

I ended up then verifying I had a lot of old entries with expired passwords (I went back to KeyChain.app and searched for the specific key named "srvr" in the CLI output). Some of those key/attribute/srvr names I found were having interesting Access Control names like "localhost/Address Book", "https/exchange", "IISupport/Mail,Mail,iSync" (note the multiple access control values).

Deleting and later recreating keys on demand sounds like a good option especially if you have migrated from a previous major version of the Operating system. We all know software is ultimately buggy so you never know how the old garbage can actually impact in your newly installed OS. In my case I upgraded from Snow Leopard to Mountain Lion. BTW to avoid confusion I am not stating though the upgrade is responsible for the locking as this was happening when I was in Snow Leopard as well.

Other causes

I have found manually created entries in /etc/fstab to a be cause for this issue as well.

Talend Eclipse default new line and Control M characters

I noticed some code I wrote in Talend Component's Designer Perspective was inserting Control M characters as new lines. Apparently the default for "New text file line delimiter" is not being picked correctly in OSX (10.8.2) using Talend Version: 4.2.3 Build id: r67267-20110905-0421

OSX brew: Error: Cannot write to /usr/local/Cellar

sudo chown -RL `logname` /usr/local/Cellar

Filter the flow in Talend Components

When building a custom Talend component you might want to ignore certain input rows. I spent a lot of time trying to figure out why my tFileInputCSVFilter component was insisting on printing empty records for the filter flow and for the reject flow, the reason? Just an attribute from /COMPONENT/HEADER node :

HAS_CONDITIONAL_OUTPUTS="true"

In Talend by default when you use a flow from a main template and you don't assign anything to the output connector the result will be an empty record unless you set HAS_CONDITIONAL_OUTPUTS="true".

VDI Path: Access Linux Desktop remotely using RDP

Update 20220123: Works well for 20.04.

Update 20190511: In 18.04 clipboard does work again

Update 20161201: In 16.04 Clipboard is not longer working.

Update 20150731: Clipboard works great in Ubuntu 14.04 LTS, however Unity is not supported.

Original post for historical reasons: RDP has been for ages a great protocol giving Windows the big advantage on the Virtual Desktop Infrastructure (VDI) competition. The FreeRDP GitHub project is changing that little by little especially thanks to XRDP. Here is a POB Recipe to install XRDP. For the latest version of the script please pull the recipe from github pob-recipes project.

#!/bin/bash -e
# common/ubuntu/xrdp-reinstall.sh

apt-get -q -y remove xrdp
apt-get -q -y update
apt-get -q -y clean
apt-get -q -y purge xrdp
apt-get -q -y install xrdp

After you have done so the Ubuntu Desktop will then be available via RDP so you can use any RDC to connect to it on default port 8309. You must login with an existing linux user so make sure the user you are trying to connect with is in /etc/passwd

I have found a very good performance after setting the desktop background to a simple color. Also restarting the desktop is recommended to make sure after a fresh reboot RDP will be available.

You can configure the port and a lot of other settings from ini files in /etc/xrdp/

RDP Sessions

XRDP does a great job and by default it does what you would expect if you are disconnected from your session: Upon reconnection you will continue working using the very same session you were when you got disconnected. Just make sure you do not change the resolution of your client before you completely logout from an existing session because that will end up creating a brand new session.