JDBC/MySQL insert statement duplicating rows.

[sciwizeh]

Ok, so I'm working on a web-project, I connected a custom hanlder to the root Logger in contextInitialized() of a ServletContextListener:

@Override
public void contextInitialized(ServletContextEvent sce) {
    Logger def = Logger.getLogger("");
    Handler hand = new DatabaseLoggingHandler();
    hand.setLevel(Level.WARNING);
    def.addHandler(hand);
}

which looks like:

import java.io.PrintWriter;
import java.io.StringWriter;
import java.util.logging.LogRecord;
import java.util.logging.Logger;
import java.util.logging.Handler;
import matrixpeckham.db.DBManager;

public class DatabaseLoggingHandler extends Handler {
    private static final Logger LOG =
            Logger.getLogger(DatabaseLoggingHandler.class.getName());

    @Override
    public void publish(LogRecord record) {
        String message = record.getMessage();
        String logger = record.getLoggerName();
        Throwable thr = record.getThrown();
        String thrMess = "";
        String thrStack = "";
        if(thr!=null){
            thrMess=thr.getMessage();
            PrintWriter str = new PrintWriter(new StringWriter());
            thr.printStackTrace(str);
            thrStack=str.toString();
        }
        DBManager.get().log(message,logger,thrMess,thrStack);
    }

    @Override
    public void flush() {
    }

    @Override
    public void close() throws SecurityException {
    }

}

the method that calls is:

public void log(String message, String logger, String thrMess,
            String thrStack) {
        String sql = "insert into schema.`logs` (message, logger, thrmess, thrstack) values (?,?,?,?)";
        try{
            PreparedStatement stmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            stmt.setString(1, message);
            stmt.setString(2, logger);
            stmt.setString(3, thrMess);
            stmt.setString(4, thrStack);
            int i = stmt.executeUpdate();//from here the code was me trying to find out what was happening
            ResultSet rs = stmt.getGeneratedKeys();
            while(rs.next()){
                i--;
            }
            if(i!=0){
                throw new IllegalStateException("Result set did not generate keys properly");
            }
        } catch(SQLException ex){
            //if this happens while trying to log an error it would be bad.
            //do nothing because if we try to log it will recurse 
            //possibly indefinately
            int breakable=0;
        }
    }

the table was made with `

CREATE TABLE `logs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `whenithappened` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `message` varchar(4096) DEFAULT NULL,
  `logger` varchar(1024) DEFAULT NULL,
  `thrmess` varchar(4096) DEFAULT NULL,
  `thrstack` varchar(4096) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

and I have two issues, 1 the "initializing" stuff that gets logged by Glassfish will cause this method to be run more than once, and 2 when an error is generated (I use an intentional int i = 1/0; in a JSP called GenError.jsp) this method is called only once, but I will randomly have 2-5 identical rows (different keys) from the one call. int i = stmt.excecuteUpdate(); sets i to 1, and the ResultSet only contains one generated key, but the database itself has 2-4 new rows all with different keys.

Other insertions with the same connection do not display this issue, it seems to only be this one.

I assume the first problem is easily addressed with some setting logging stuff, the second problem is the one I really need help with. Is there something simple I'm doing wrong that I'm not noticing?

I will post more code if needed/requested to help, but this is the code I assume has the bug.