useful mysql queries snippets

– remove duplicate records:
CREATE TEMPORARY TABLE temp_table (column1 VARCHAR(256),column2 VARCHAR(256));
INSERT INTO temp_table (`column1`,`column2`) SELECT DISTINCT `column1`,`column2` FROM table1;
DELETE from table1;
INSERT INTO table1 (`column1`,`column2`) SELECT * FROM temp_table;

– show duplicate records:
SELECT column1, count(*) FROM table1 GROUP BY id HAVING count(*) > 1;
select * from table1 i where in
(SELECT id FROM table1 GROUP BY id HAVING count(*) > 1);

– set foreign key:
update table1 a, table2 b set where y.column1=i.column1;

– remove column:
alter table table1 drop column1;

– compare two columns to find unmatched records:
select column2 from table1
where column2 not in (select column1 from table1);


java mysql utf8 character problem

1. For Database:

Open Glassfish Admin Console, navigate “JDBC Connection Pools > pool_name > Additional Properties”
Update URL parameter’s value as:


(Add "?useUnicode=true&characterEncoding=UTF-8" part)

2. For Server:

Open Glassfish Admin Console, navigate “Configurations > server-config > JVM Settings > JVM Options”
Add option "-Dfile.encoding=UTF8"

3. Add the following filter to web.xml:

	<filter-name>Set Character Encoding</filter-name>
	<filter-name>Set Character Encoding</filter-name>

filter.SetCharacterEncodingFilter class:

package filters;


import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

 * Example filter that sets the character encoding to be used in parsing the
 * incoming request, either unconditionally or only if the client did not
 * specify a character encoding.
public class SetCharacterEncodingFilter implements Filter {

    protected String encoding = null;
    protected FilterConfig filterConfig = null;
    protected boolean ignore = true;

     * Take this filter out of service.
    public void destroy() {
        this.encoding = null;
        this.filterConfig = null;

     * Select and set (if specified) the character encoding to be used to
     * interpret request parameters for this request.
    public void doFilter(ServletRequest request, ServletResponse response,
            FilterChain chain)
            throws IOException, ServletException {
// Conditionally select and set the character encoding to be used
        if (ignore || (request.getCharacterEncoding() == null)) {
            String encoding = selectEncoding(request);
            if (encoding != null) {
// Pass control on to the next filter
        chain.doFilter(request, response);

     * Place this filter into service.
    public void init(FilterConfig filterConfig) throws ServletException {
        this.filterConfig = filterConfig;
        this.encoding = filterConfig.getInitParameter("encoding");
        String value = filterConfig.getInitParameter("ignore");
        if (value == null) {
            this.ignore = true;
        } else if (value.equalsIgnoreCase("true")) {
            this.ignore = true;
        } else if (value.equalsIgnoreCase("yes")) {
            this.ignore = true;
        } else {
            this.ignore = false;

     * Select an appropriate character encoding to be used, based on the
     * characteristics of the current request and/or filter initialization
     * parameters. If no character encoding should be set, return null.
    protected String selectEncoding(ServletRequest request) {
        return (this.encoding);

mysql “a windows service with the name mysql already exists” and “[SC] OpenService FAILED 5: Access is Denied” problems

when re-installing mysql after uninstalling it, if there is a problem like:
“a windows service with the name mysql already exists”,

run “sc delete mysql” command from cmd.

If it says “[SC] OpenService FAILED 5: Access is Denied“, open cmd as Administrator and run again the same command.