MySQL - delete from a table with a subquery on the same table

Assume you have a table called story with columns id, content and status. 

Table schema:

CREATE TABLE `story` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` longtext NOT NULL,
  `status` smallint(6) NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB ;

We want to delete all the records that have status 3 and id less than 6022.

You may do the following:

delete from story where id in (select id from story where status = 3 and id < 6022);

However, this does not work.

ERROR 1093 (HY000): You can't specify target table 'story' for update in FROM clause

Instead, you need to wrap the subquery in an abstract table with an alias (called old_featured below).

delete from story where id in (select * from (select id from story where status = 3 and id < 6022) as old_featured);

Regex prepend and append

I was working on making all the countries below as options in a select dropdown box. For the upcoming example, you can use Sublime or Notepad++.

The countries have the following form (country_value {tab_character} country_name):

Sample Input:

AD Andorra AE United Arab Emirates AF Afghanistan AG Antigua and Barbuda AI Anguilla AL Albania AM Armenia

An option html element has the following form:

country_name

There are two things we need to do:

1. trim all the whitespaces
2. Replace with the option tag

1. Trim all the whitespaces

You can trim all the whitespaces by matching the following:

Pattern: ^[ \t]+|[ \t]+$
Replace with: (nothing)

2. Replace with the option tag

Match them into two blocks, you can use \1 to reference first block (first matching parenthesis below),

Pattern: (.*)\t(.*)
Replace with: \2

Java - escaping the pipe character

Use double backslash (\\)

Ex. string.split("\\|");

Stopping Image Bandwidth Theft with .htaccess

A few days ago, my host notified me that my shared host account has been using more than usual CPU resources. I didn't see the email, and my site was banned. I emailed my host and asked them to un-suspend my account so I could investigate what was the problem.

I checked Google Analytics and the traffic was normal. I then checked the bandwidth usage and found that the bandwidth was very high.

I dived deeper into the problem and I discovered that some sites were hot linking pictures on my domain.

Here's the hot-linking site:

In case if you are wondering why the image is so familiar, I actually purchased it from ShutterStock.

So I have an idea - why not swap those pictures with an image to market my site?

I used Photoshop and created this.

In case you are wondering, the banner goes to appgags.com.

At the root of my .htaccess, I appended the following code:

RewriteCond %{HTTP_REFERER} !^http://(.+\.)?appsmylife\.com/ [NC]
RewriteCond %{HTTP_REFERER} !^$
RewriteRule .*\.(jpe?g|gif|bmp|png|jpg)$ /images/appgags_banner.jpg [L]

Here's the result:

Unfortunately, the image is stretched due to the way that website set it.

Building a JAR Executable with Maven and Spring

I have a Spring MVC web project. I want to generate some xml files based on some data stored in my database. This program will be run once or a few times everyday. As the data size grows, the program may take longer to run. The best way to do this is to write a JAR executable and feed it to Amazon to Elastic MapReduce.

We will set up a small JAR project with Maven below.

First generate a Maven project called maventest. If you do not have maven set up, read Install Maven 3 on Amazon EC2 Ubuntu.

mvn archetype:generate -DarchetypeGroupId=org.apache.maven.archetypes -DgroupId=com.mycompany.maventest -DartifactId=maventest

Resources files are located in src/main/resources/.

Spring requires a context xml file that defines how classes are instantiated. Create a file called applicationContext.xml in src/main/resources.

Paste the following into the applicationContext.xml file



Create a file called jdbc.properties in src/main/resources. We will be connecting to a MySQL database.



My whole project is called myproject. It has the Spring MVC web project has the package name   com.mycompany.package1 and artifactId mywebproject. This cron program will have the package name com.mycompany.package2 with artifactId cron.

We will use the maven-jar-plugin to define the location of our main class.

< plugin>
< groupId>org.apache.maven.plugins< /groupId>
< artifactId>maven-jar-plugin< /artifactId>
< configuration>
  < archive>
    < manifest>
      < mainClass>com.mycompany.package2.App< /mainClass>
    < /manifest>
  < /archive>
< /configuration>
< /plugin>

In order to package all the dependencies as one jar file, we will use maven-shade-plugin. Sometimes, spring.schemas and spring.handlers files can be overwritten. Examine the transformers tag below to see that the content of these files will be appended.

We will now include all the dependencies in the pom.xml file.

Package the project (It's useful to understand what's compile, package, install and deploy)

mvn clean package

If the package command fails, it may be complaining that it can't find the com.mycompany.package1.mywebproject project.

You can do a mvn:install for mywebproject. This will install mywebproject to the local maven repository. It is located under /Users/{your_name}/.m2. Remember to compile mywebproject as JAR by setting it in the pom.xml. If you ever suspect that some classes are missing or the build is not quite correct. It's highly likely that mywebproject in the local repository is not up-to-date.

The jar file is located in the target folder. Run the jar

java -jar maventest-1-0-SNAPSHOT

Unable to locate resource hector-core-1.1-3-SNAPSHOT.jar

If you ever get the following message regarding the hector client, you can install it manually by download the file from the hectorclient website.

Error message:

Unable to locate resource https://oss.sonatype.org/content/groups/public/org/hectorclient/hector-core/1.1-3-SNAPSHOT/hector-core-1.1-3-SNAPSHOT.jar

Installation command:

mvn install:install-file -DgroupId=org.hectorclient -DartifactId=hector-core -Dversion=1.1-3-SNAPSHOT -Dpackaging=jar -Dfile=~/Downloads/hector-core-1.1-3-20130112.031550-9.jar

Spring component-scan: scanning services from multiple base packages

In your applicationContext.xml, you can specify base packages for scanning by:

<context:component-scan base-package="com.mycompany.package1, com.mycompany.package2"/>

Maven - A sample pom.xml for java jar executable

The following provides a sample JAR executable.

It uses hiberate, spring, mysql.

The class that contains the Main function is specified in the "mainClass" tag.

Sample pom.xml

< ?xml version="1.0"?>

<project xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd" xmlns="http://maven.apache.org/POM/4.0.0"

    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <modelVersion>4.0.0</modelVersion>

  <groupId>com.mycompany.generator</groupId>

  <artifactId>onix-generator</artifactId>

  <version>1.0-SNAPSHOT</version>

  <name>onix-generator</name>

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

  <properties>

    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

    <spring.version>3.2.1.RELEASE</spring.version>

    <jdbc.groupId>mysql</jdbc.groupId>

        <jdbc.artifactId>mysql-connector-java</jdbc.artifactId>

        <jdbc.version>5.1.14</jdbc.version>

        <hibernate.version>3.6.10.Final</hibernate.version>

        <javamail.version>1.4.1</javamail.version>

        <log4j.version>1.2.16</log4j.version>

  </properties>

  <build>

    <plugins>

      <plugin>

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

        <artifactId>maven-jar-plugin</artifactId>

        <configuration>

          <archive>

            <manifest>

              <addClasspath>true</addClasspath>

              <mainClass>com.mycompany.generator.App</mainClass>

              <packageName>com.mycompany.generator</packageName>

            </manifest>

          </archive>

        </configuration>

      </plugin>

      <plugin>

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

    <artifactId>maven-compiler-plugin</artifactId>

    <configuration>

      <source>1.7</source>

      <target>1.7</target>

      </configuration>

      </plugin>

    </plugins>

  </build>

  <dependencies>

    <dependency>

      <groupId>junit</groupId>

      <artifactId>junit</artifactId>

      <version>3.8.1</version>

      <scope>test</scope>

    </dependency>

    <dependency>

      <groupId>${jdbc.groupId}</groupId>

      <artifactId>${jdbc.artifactId}</artifactId>

      <version>${jdbc.version}</version>

    </dependency>

    <dependency>

            <groupId>log4j</groupId>

            <artifactId>log4j</artifactId>

            <version>${log4j.version}</version>

            <exclusions>

                <exclusion>

                    <artifactId>mail</artifactId>

                    <groupId>javax.mail</groupId>

                </exclusion>

                <exclusion>

                    <artifactId>jms</artifactId>

                    <groupId>javax.jms</groupId>

                </exclusion>

                <exclusion>

                    <artifactId>jmxtools</artifactId>

                    <groupId>com.sun.jdmk</groupId>

                </exclusion>

                <exclusion>

                    <artifactId>jmxri</artifactId>

                    <groupId>com.sun.jmx</groupId>

                </exclusion>

            </exclusions>

        </dependency>

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-jdbc</artifactId>

            <version>${spring.version}</version>

        </dependency>

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-orm</artifactId>

            <version>${spring.version}</version>

        </dependency>

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-aspects</artifactId>

            <version>${spring.version}</version>

        </dependency>

        <dependency>

            <groupId>org.springframework</groupId>

            <artifactId>spring-context-support</artifactId>

            <version>${spring.version}</version>

        </dependency>

        <dependency>

        <groupId>com.mycompany.generator</groupId>

        <artifactId>epubserver</artifactId>

        <version>1.0-SNAPSHOT</version>

        <classifier>classes</classifier>

        </dependency>

  </dependencies>

</project>

You can generate an executable by running

mvn clean package

The jar file will be located in the target folder. You can run the executable by:

java -jar {name_of_jar}

Maven javac: invalid target release: 1.7

When I try to compile my maven project by running "mvn package", I get the following error

[ERROR] Failed to execute goal org.apache.maven.plugins:maven-compiler-plugin:2.3.2:compile (default-compile) on project onix-generator: Compilation failure
[ERROR] Failure executing javac, but could not parse the error:
[ERROR] javac: invalid target release: 1.7
[ERROR] Usage: javac
[ERROR] use -help for a list of possible options

1) Check your Java version:

java -version

java version "1.7.0_09"
Java(TM) SE Runtime Environment (build 1.7.0_09-b05)
Java HotSpot(TM) 64-Bit Server VM (build 23.5-b02, mixed mode) 

2) Check javac version:

javac -version

javac 1.7.0_09

3) Check $JAVA_HOME

Make sure it points to the java bin directory.

How to create a Maven project

First install Maven - Read Install Maven 3 on Amazon EC2 Ubuntu.

Create a folder anywhere you like. cd into the directory.

Create a Maven project

mvn archetype:generate -DarchetypeGroupId=org.apache.maven.archetypes -DgroupId=com.mycompany.maventest -DartifactId=maventest

The CLI will prompt for a few options:

1.) Choose a number or apply filter (format: [groupId:]artifactId, case sensitive contains): 284:

Push Enter. We will use the default 284.

remote -> org.apache.maven.archetypes:maven-archetype-quickstart (An archetype which contains a sample Maven project.)

2.) Choose org.apache.maven.archetypes:maven-archetype-quickstart version:

Choose 1.1 (Number 6).

3.) Define value for property 'version':  1.0-SNAPSHOT: :

You can leave the version "1.0-SNAPSHOT" as the default value.

4.) Confirm properties configuration:

Push Enter.


You should see a folder created.

AWS - Elastic Map Reduce Tutorial

MapReduce has become a very common technique utilizing parallel computing.

Let's say you have a database table with username and description columns in it. You want to replace html tags in the description column with empty spaces. Let's say the database has petabytes of data. It will take forever for a single machine to do this job.

MapReduce works by distributing this job among multiple machines. Each machine executes different dataset in parallel, and then the outputs will be aggregated. Therefore, a job that may take days to compute can take only mins to finish.

In this tutorial, we will experiment with Amazon's Elastic MapReduce.

Let's get started.


Create a S3 bucket

Elastic MapReduce uses S3 to store it's input and output. We will first create a bucket.

Log into your Amazon Web S3 console. Create a bucket, say my_map_reduce_data. Amazon S3 bucket names need to be unique across all Amazon S3 buckets. It's best to prefix it with your company name.


Create input data

Let's create a text file and put some random data into it. We will create a MapReduce function to count word frequencies.

Ex.
apple apple orange orange orange
pear pear pear pear pear pear pear pineapple pineapple

Label this file input.txt.

Create a folder inside my_map_reduce_data and call it input.


Implementing the mapper function

Download the following file and save it as wordSplitter.py

https://s3.amazonaws.com/elasticmapreduce/samples/wordcount/wordSplitter.py

It's a script that reads the input file line by line and prints out the number of occurrence for each distinct word in that line.

Upload wordSplitter.py to my_map_reduce_data


Launch the Elastic MapReduce Cluster

Sign in to the Elastic MapReduce Console.

Click on Create New Job Flow.

Give the Job Flow Name WordSplitter.

Choose Amazon Distribution for the Hadoop version.

Choose Streaming as the job flow type. You write the mapper and reducer scripts in any of the following languages: Ruby, Perl, Python, PHP, R, Bash, or C++.

Click Continue.


Input, output locations

Fill in the following:
Input Location: my_map_reduce_data/input
Output Location: my_map_reduce_data/output
Mapper: my_map_reduce_data/wordSplitter.py
Reducer: aggregate

Click Continue.


Configure EC2 Instances

Leave the options as it is.

Click Continue.


Advanced Options

If you want to ssh into the master node, specify the Amazon EC2 Key Pair.

For the Amazon S3 Log Path, put my_map_reduce_data/logs

Check Yes for Enable debugging. It will create an index of the log files in Amazon SimpleDB.

Leave the other boxes as NO.


BootStrap Actions

Proceed without bootstrap actions. BootStrap allows additional software to be installed in the cluster nodes before the MapReduce process any data.


Review the information and start the job. You should see the job being started.

You can monitor the status of the nodes in the MapReduce Web console.

Check the output folder in S3 after the job is completed.

Remember to delete all buckets to avoid getting charges.

Composer Update doesn't install files

If you ever update with composer.phar and not seem to get any files fetched, it may be a cache issue.

Remove the composer cache folder.

Ex. rm -rf /root/.composer

Make sure you delete the whole .composer folder. Not just the cache folder inside.

Symfony 2 FOSUserBundle - overriding the registration success event

I was trying the change the registration flow of FOSUserBundle the other day. The goal is to let new users to use the site without activating their accounts while an activation email can still be sent. Every time these users log in to the site, a flash message will show up and ask them to activate their accounts.

The first thing I did was creating a EmailConfirmationListener. You can copy this file from the FOSUserBundle in the vender's folder. All I did was 1) setting user isEnabled flag to true and 2) changing the route (if needed).

Note that the EmailConfirmationListener registers the following event:

FOSUserEvents::REGISTRATION_SUCCESS => 'onRegistrationSuccess'

When I registered a new user, I registered two activation emails were be sent.

The trace log shows that two onRegistrationSuccess events were fired. One is the one from the vendor, the other one is the one I just created.

The listener service is declared in email_confirmation.xml.

Doing a quick search on the project root folder for the term "email_confirmation.xml" reveals the following:

if ($config['confirmation']['enabled']) {
         $loader->load('email_confirmation.xml');
}

Now turn the confirmation setting to false in config.yml

fos_user:
    registration:
        confirmation:
            enabled: true

Then only one onRegistrationSuccess event would be fired.

Symfony 2 JS Error in Production Only - Assetic Compression

If your JS files work perfectly in dev but not in production, it may be a compression issue indirectly caused by the Assetic bundle.

Make sure every single javascript file ends with a semi colon. The issue is that JS allows files to not end with semi colon, but when Assetic compresses and combines all JS files, the files are appended and causing incorrect syntax error.

Symfony 2 Composer - error occurred when generating the bootstrap file

[RuntimeException]                                  
An error occurred when generating the bootstrap file.

If you see the following error during updating Symfony vendors via Composer, do the following:


php composer update --no-scripts
php vendor/sensio/distribution-bundle/Sensio/Bundle/DistributionBundle/Resources/bin/build_bootstrap.php

User Load Testing Simulation - Installing Apache JMeter on Windows 2012 Base

Apache JMeter is good for load testing web applications. Our goal of this post is to install JMeter on a EC2 instance.  The reason why we are using Windows is that JMeter has a GUI. It is less work to use it on Windows than Ubuntu.

Specs:

• Windows 2012 Base
• m1.large EC2 instance
• Java Open JDK 1.7

Make a security group called JMeter.  Open the following ports

• port 3389 for RDP for 0.0.0.0\0
• all ports for ICMP for the JMeter Group
• all ports for the JMeter Group

First spin a Windows 2012 Base instance. Use m1.large. Choose a region that your web application machines are located.  Attach to the JMeter security group. Label the instance JMeter Master.

The machine will be generating a password. You will need to wait a bit before you can RDP into the machine.

Once you RDP into the machine, lower the Internet security so you can use the browser.

Go to control panel and turn off all the firewalls. We will

Download the latest JDK. Follow the install wizard.

Download Apache JMeter, and unzip it to a folder (ex. C:\tools\apache-jmeter-2.9).

Download the JMeter Plugins.

Unzip the JMeter Plugins and put the JMeterPlugins.jar into the JMETER_INSTALL_DIRECTORY\lib\ext directory.

Change the following properties in bin\jmeter.properties.

jmeter.save.saveservice.thread_counts=true

If you want to use more than one machine to generate load testing requests, you can change the remote hosts attribute in jmeter.properties and declare the hosts in hosts files. We will set up extract two machines: slave1, slave2

Change the remote_hosts property from

remote_hosts=127.0.0.1

to

remote_hosts=slave1:1099,slave2:1099

Edit C:\Windows\System32\drivers\etc\hosts

127.0.0.1 slave1
127.0.0.1 slave2

Create a test plan.

Create a AMI in the EC2 console.

Start an instance using this AMI. Label this the JMeter Slave.

Try to ping each other and make sure the JMeter Master and Slave can connect to each other. If not, remember to open ICMP in your security group.

Log into the JMeter Slave. Start jmeter-server.bat in the bin folder.

Log into the JMeter Master.

In the hosts file, change slave 1's IP to that of the JMeter Slave.

Start JMeter. Open your test plan. Run -> Remote Start -> slave 1:1009.

You should be able to see the test results.

Java Spring Profiling with Spring Insight

We will be using Spring Insight to profile a Spring MVC application in Amazon EC2. As long as you are using Ubuntu, you should be able to follow this post.

Specs:

• Ubuntu Server 12.04.1 LTS
• m1.small instance
• a pre-existing Java Spring application deployed on tomcat7
• Java Open JDK 1.7

Being by connecting to you machine via ssh.

Download Spring Insight. Select the vfabric tc server developer edition instead of the springsource tool suite (STS). You will need to sign on an account in the VMware website. Use wget to fetch the file to your ubuntu instance.

wget {link to vfabric-tc-server-developer-2.9.2.RELEASE.zip}
cd /opt
mkdir /opt/tools
cd /opt/tools
unzip vfabric-tc-server-developer-2.9.2.RELEASE.zip
mv vfabric-tc-server-developer-2.9.2.RELEASE

Inside the vfabric-tc-server-developer-2.9.2.RELEASE folder, run

./tcruntime-instance.sh create insight -t insight

You will see

Creating instance 'insight' ...
  Using separate layout
  Creating bin/setenv.sh
  Applying template 'base'
    Copying template's contents
    Applying fragment 'context-fragment.xml' to 'conf/context.xml'
    Applying fragment 'server-fragment.xml' to 'conf/server.xml'
    Applying fragment 'web-fragment.xml' to 'conf/web.xml'
    Applying fragment 'tomcat-users-fragment.xml' to 'conf/tomcat-users.xml'
    Applying fragment 'catalina-fragment.properties' to 'conf/catalina.properties'
  Applying template 'base-tomcat-7'
    Copying template's contents
    Applying fragment 'server-fragment.xml' to 'conf/server.xml'
    Applying fragment 'web-fragment.xml' to 'conf/web.xml'
    Applying fragment 'catalina-fragment.properties' to 'conf/catalina.properties'
  Applying template 'insight'
    Copying template's contents
    Applying fragment 'server-fragment.xml' to 'conf/server.xml'
  Configuring instance 'insight' to use Tomcat version 7.0.39.B.RELEASE
  Setting permissions
Instance created
Connector summary
  Port: 8080   Type: Auto-Switching HTTP 1.1   Secure: false

A folder called insight is created.

If you change into the insight directory, you will see a folder called webapps (this is almost same as the Tomcat directories). Copy your war file into this directory.

cd insight
cp {your_java_war_file} webapps/

Start the insight instance.

./tcruntime-ctl.sh insight start

You will see

Using CATALINA_BASE:   /opt/tools/vfabric-tc-server-developer-2.9.2.RELEASE/insight
Using CATALINA_HOME:   /opt/tools/vfabric-tc-server-developer-2.9.2.RELEASE/tomcat-7.0.39.B.RELEASE
Using CATALINA_TMPDIR: /opt/tools/vfabric-tc-server-developer-2.9.2.RELEASE/insight/temp
Using JRE_HOME:        /usr/lib/jvm/java-7-openjdk-amd64/jre
Using CLASSPATH:       /opt/tools/vfabric-tc-server-developer-2.9.2.RELEASE/insight/bin/insight-bootstrap-tomcat-extlibs-1.9.2.SR1.jar:/opt/tools/vfabric-tc-server-developer-2.9.2.RELEASE/tomcat-7.0.39.B.RELEASE/bin/bootstrap.jar:/opt/tools/vfabric-tc-server-developer-2.9.2.RELEASE/tomcat-7.0.39.B.RELEASE/bin/tomcat-juli.jar
Using CATALINA_PID:    /opt/tools/vfabric-tc-server-developer-2.9.2.RELEASE/insight/logs/tcserver.pid
Status:                RUNNING as PID=503

Be sure to open port 8080 in your security group.

Now browse to the webpage.

http://{external_ec2_address}:8080/{war_name}