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"/>