Thursday, December 23, 2010

MySQL blocked because of many connection errors

Suddenly Tomcat was sending back errors to the clients, restarting fixed it but only for a while, inspecting the logs the cause was apparently not enough connections available:
"Host 'myhost.com' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"

Issuing the command would be the next measure and even applying brute force a script doing this every so often. Wait a minute we know better. There must be an (intentionally or not) attack.

The cluprit

Monit was configured to monitor mysql like:
if failed host 192.168.0.161 port 3306 then restart

This is a problem as all it does is opening a socket and then dropping the connection without any SQL handshaking.

Solution

Specifying mysql protocol could solve the problem but mysql is so mature and stable that it would be enough to check just the socket file:
if failed unixsocket /var/lib/mysql/mysql.sock with timeout 5 seconds then restart

Wednesday, December 22, 2010

SFTP with OpenSSH User Setup made easy

I have explained before how to get SFTP working using OpenSSH.

Let me go an extra mile now and share a simple bash script that creates a user, assigns a password, sets a maximum number of files (and allowed size) and allows *just* SFTP access. Here is how you do so from a single command line (I tested this time in Ubuntu / Debian):
sudo  /path/to/addSftpUser.sh 'testUser' 'testPassword'


Here is the script code:
#!/bin/bash
#
# @fileName: addSftpUser.sh:
# @description: Creates an SFTP user
# @author: Nestor Urquiza
# @date: Dec 22, 2010
#

#
# Constants
#
ALLOWED_KB=100000
ALLOWED_FILES=1000

#
# Functions
#
function usage {
  echo "Usage - $0 user password"
  exit 1
}

#
# Main program
#
if [ $# -lt 2 ]
then
        usage
fi
USER=$1
PASSWORD=$2
useradd -d /home/$USER -s /bin/false -m $USER
usermod -g sftponly $USER
sudo usermod -p `mkpasswd $PASSWORD` $USER
chown root:root /home/$USER
chmod 755 /home/$USER
mkdir /home/$USER/$USER
chown $USER:$USER /home/$USER/$USER
chmod 755 /home/$USER/$USER
#Quotas: Feel free to remove if you do not need to limit uploads
setquota -u $USER $ALLOWED_KB $ALLOWED_KB $ALLOWED_FILES $ALLOWED_FILES -a /


You must be sure the user cannot SSH into the box:
$ ssh testUser@192.168.3.161
testUser@192.168.3.161's password: 
This service allows sftp connections only.
Connection to 192.168.0.161 closed.
$ 

You want to be sure the user can use SFTP
$ sftp testUser@192.168.3.161
Connecting to 192.168.3.161...
testUser@192.168.3.161's password: 
sftp> exit

Thursday, December 16, 2010

Rendering CSV as ERT from BHUB with Spring

As I showed for Excel we can create a custom View to render tabular data in CSV format instead.

Note that this example uses Spring and a ControllerContext class that I use to pass information about different layers (Context-Object pattern). You can of course get the fundamental idea in case you do not use a ControllerContext in your design.

The unique dependency:
<dependency>
            <groupId>net.sf.opencsv</groupId>
            <artifactId>opencsv</artifactId>
            <version>2.0</version>
</dependency>

The Controller is very similar to the one I presented for the ExcelView post. Just a little modification so actually we can render either Excel or CSV:
package com.nestorurquiza.spring.web;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.nestorurquiza.spring.web.ExcelView;
import com.nestorurquiza.spring.web.CsvView;

@Controller
public class TabularController extends RootController {

    @RequestMapping("/board")
    public ModelAndView welcomeHandler(HttpServletRequest request,
            HttpServletResponse response) {
        //Initialize the context (mandatory)
        ControllerContext ctx = new ControllerContext(request, response);
        init(ctx);
        
        DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
        Map<String, List<Map<String, Object>>> excelWorkbookViewMap = new HashMap<String, List<Map<String, Object>>>();
        List<Map<String, Object>> excelRows = new ArrayList<Map<String, Object>>();
        try {
            Map<String, Object> excelRow = new HashMap<String, Object>(); 
            excelRow.put("Name", "Gregory");
            excelRow.put("Age", 33);
            excelRow.put("Salary", 33000.55);
            excelRow.put("DOB", df.parse("2/1/1980"));
            excelRow.put("Graduated", false);
            excelRow.put("Comments", "He is our \"report designer\"");
            excelRows.add(excelRow);
            excelRow = new HashMap<String, Object>(); 
            excelRow.put("Name", "Mark");
            excelRow.put("Age", 41);
            excelRow.put("Salary", 33000.55);
            excelRow.put("DOB", df.parse("20/12/1975"));
            excelRow.put("Graduated", true);
            excelRow.put("Comments", "He is our \"web designer\"");
            excelRows.add(excelRow);
            excelWorkbookViewMap.put("First Sheet", excelRows);
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
        String ert = ctx.getParameter("ert");
        String baseFilename = "board";
        if("csv".equals(ert)) {
            Map<String, List<Map<String, Object>>> csvViewMap = new HashMap<String, List<Map<String, Object>>>();
            String fileName = baseFilename + ".csv";
            csvViewMap.put(fileName, excelRows);
            return new ModelAndView(new CsvView(ctx, fileName, ','), csvViewMap);
        } else {
            String fileName = baseFilename + ".xls";
            return new ModelAndView(new ExcelView(ctx, fileName), excelWorkbookViewMap);
        }
    }
}
The CsvView:
package com.nestorurquiza.spring.web;

import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.servlet.view.AbstractView;

import au.com.bytecode.opencsv.CSVWriter;

import com.nestorurquiza.utils.Utils;
import com.nestorurquiza.web.ControllerContext;


public class CsvView extends AbstractView {
    private static final String CONTENT_TYPE = "text/csv";

    public CsvView(ControllerContext ctx, String fileName, char fieldSeparator) {
        super();
        if(Utils.isEmpty(fileName)) {
            fileName = "fileName";
        }
        this.fileName = fileName;
        this.fieldSeparator = fieldSeparator;
        this.ctx = ctx;
        setContentType(CONTENT_TYPE);
    }
    
    private String fileName;
    private char fieldSeparator;
    private ControllerContext ctx; 

    /**
     * 
     * model: Map<String, List<Map, Object>>
     * This view returns back a CSV stream
     * The model must come with an entry for fileName key
     * Each list entry (the list map) corresponds to a row
     * The headers for each row are the list map keys
     * The comma separated values are the list map values
     * 
     * @author nestor
     *
     */
    protected void renderMergedOutputModel(Map model, HttpServletRequest request, HttpServletResponse response) throws Exception {
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
        CSVWriter writer = new CSVWriter(response.getWriter(), fieldSeparator);

        if (Utils.isEmpty(model)) {
            writer.writeNext("error.empty.model".split(""));
        } else {
            Set<Map.Entry<String, List<Map<String, Object>>>> set = model.entrySet();
            for (Map.Entry<String, List<Map<String, Object>>> entry : set) {
                String key = entry.getKey();
                if (fileName.equals(key)) {
                    List<Map<String, Object>> content = entry.getValue();
                    int rowCount = 0;
                    for (Map<String, Object> row : content) {
                        if (rowCount == 0) {
                            String[] tokens = new String[row.size()];
                            int i = 0;
                            for( String cellName : row.keySet() ) {
                                tokens[i] = cellName;
                                i++;
                            }
                            writer.writeNext(tokens);
                        }
                        String[] tokens = new String[row.size()];
                        int i = 0;
                        for( String cellName : row.keySet() ) {
                            Object cellValue = row.get(cellName);
                            tokens[i] = cellValue.toString();
                            i++;
                        }
                        writer.writeNext(tokens);
                        rowCount++;
                    }

                }
            }
        }
        writer.flush();
    }
}

Tuesday, December 14, 2010

Rendering Excel as ERT from BHUB with Spring POI

Why Excel is not suitable as expected response type (ERT) for all possible BHUB methods is obviously a consequence of the fact that websites render hierarchical data and not just tabular data.

When Excel is needed to present some tabular data like for example when too many columns are to be presented then I provide a specific controller to manage the rendering. One could argue that with so many DHTML data grids components it should not be a big deal to still use HTML instead of Excel and I agree that is the case especially when a front end developer is on board. Still even the best grid component out there will not allow for real post processing, multiple sheets, formulas: Excel sometimes is simply "the tool".

Spring has a View that wraps the POI API. The only extra dependency to include is shown below:
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.6</version>
</dependency>


Here is a Spring Controller using a custom ExcelView. Note that the custom View will acccept simple Map List to contain cells, rows and sheets:
package com.nestorurquiza.spring.web;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.nestorurquiza.spring.web.ExcelView;


@Controller
public class ExcelController extends RootController {

    @RequestMapping("/excel/sample")
    public ModelAndView welcomeHandler(HttpServletRequest request,
            HttpServletResponse response) {
        //Initialize the context (mandatory)
        ControllerContext ctx = new ControllerContext(request, response);
        init(ctx);
        
        DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
        Map<String, List<Map<String, Object>>> excelWorkbookViewMap = new HashMap<String, List<Map<String, Object>>>();
        try {
            List<Map<String, Object>> excelRows = new ArrayList<Map<String, Object>>();
            Map<String, Object> excelRow = new HashMap<String, Object>(); 
            excelRow.put("Name", "Gregory");
            excelRow.put("Age", 33);
            excelRow.put("Salary", 33000.55);
            excelRow.put("DOB", df.parse("2/1/1980"));
            excelRow.put("Graduated", false);
            excelRows.add(excelRow);
            excelRow = new HashMap<String, Object>(); 
            excelRow.put("Name", "Mark");
            excelRow.put("Age", 41);
            excelRow.put("Salary", 33000.55);
            excelRow.put("DOB", df.parse("20/12/1975"));
            excelRow.put("Graduated", true);
            excelRows.add(excelRow);
            excelWorkbookViewMap.put("First Sheet", excelRows);
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
        return new ModelAndView(new ExcelView(ctx, "sample.xls"), excelWorkbookViewMap);
    }
}

Finally the custom ExcelView:
package com.nestorurquiza.web;

import java.util.Date;
import java.util.Map;
import java.util.List;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.nestorurquiza.utils.Utils;
import com.nestorurquiza.web.ControllerContext;


public class ExcelView extends AbstractExcelView{
    public ExcelView(ControllerContext ctx, String fileName) {
        super();
        this.fileName = fileName;
        this.ctx = ctx;
    }

    private String fileName;
    private ControllerContext ctx; 
 
    /**
     * 
     * model: Map<String, List<Map, Object>>
     * This view returns back an Excel stream
     * The sheets are determined by the amount of parent model map keys
     * The content of the sheets are determined by the value of the model map key (a List of Maps)
     * Each list list entry (the list map) corresponds to a row
     * The headers for each row are the list map keys
     * The content of the cell are the list map values
     * 
     * @author nestor
     *
     */
    @Override
    protected void buildExcelDocument(Map model, HSSFWorkbook workbook,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
        if(Utils.isEmpty(model)) {
            HSSFSheet sheet = workbook.createSheet(ctx.getString("error.empty.model"));
        } else {
            Set<Map.Entry<String, List<Map<String, Object>>>> set = model.entrySet();
            for (Map.Entry<String, List<Map<String, Object>>> entry : set) {
              String sheetName = entry.getKey();
              HSSFSheet sheet = workbook.createSheet(sheetName);
              List<Map<String, Object>> sheetContent = entry.getValue();
              HSSFRow header = sheet.createRow(0);
              HSSFRow excelRow = header;
              int rowCount = 0;
              for( Map<String, Object> row : sheetContent ) {
                  
                  int i = 0;
                  if( rowCount == 0 ){
                      
                      for( String cellName : row.keySet() ) {
                          Object cellValue = row.get(cellName);
                          header.createCell(i++).setCellValue(cellName);
                      }
                      rowCount++;
                      i = 0;
                  }
                  excelRow = sheet.createRow(rowCount);
                  for( String cellName : row.keySet() ) {
                      Object cellValue = row.get(cellName);
                      HSSFCell cell = excelRow.createCell(i++);
                      
                      //CELL_TYPE_NUMERIC, CELL_TYPE_STRING, CELL_TYPE_BOOLEAN
                      if (cellValue instanceof Integer) {
                          cell.setCellValue((Integer) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                      } else if(cellValue instanceof Float) {
                          cell.setCellValue((Float) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                      } else if(cellValue instanceof Double) {
                          cell.setCellValue((Double) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                      } else if (cellValue instanceof Boolean) {
                          cell.setCellValue((Boolean) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
                      } else if (cellValue instanceof Date) {
                          cell.setCellValue((Date) cellValue);
                          cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                          HSSFCellStyle style = workbook.createCellStyle();
                          HSSFDataFormat dataFormat = workbook.createDataFormat();
                          style.setDataFormat(dataFormat.getFormat("dd/MM/yyyy"));
                          cell.setCellStyle(style);
                      } else {
                          cell.setCellValue(cellValue.toString());
                          cell.setCellType(HSSFCell.CELL_TYPE_STRING);   
                      }
                  }
                  rowCount++;
              }
            }
        }
    }
}

Monday, December 06, 2010

Liferay The requested resource is not available

For some reason Tomcat kept deploying my portlet without complaint but Liferay was showing the below content inside the portlet:
The requested resource (/myWar/myPortlet/invoke) is not available

Solution

Undeploying the whole exploded war from tomcat and then redeploying it back fixed the issue. To undeploy just delete the whole exploded directory from "webapps" folder.

Saturday, December 04, 2010

Form submit IE6 problems

There are at least three points to take into consideration when submitting forms with IE6:
  1. IE6 has a limitation on how long the query string can be so use POST instead of GET (for debugging purposes GET is good) in your production systems.
  2. IE6 has limitations in terms of rendering and processing javascript in parallel. A setTimeout() can be handy to resolve related problems.
  3. IE6 is less forgiven than other browsers (which in my opinion is not exactly wrong) so pay attention to the correctness of the document. Always use a validator for your markup.
And I have to say it again and again, if you can afford it make sure you have a dedicated developer for front end. There is simply too much to hack, fragmentation keeps on being an issue and there is no solver bullet.

I still do not understand why I need to be writing about Internet Explorer version 6 in almost year 2011. It reminds me some dictatorships in the world: everybody knows it is evil, do not work fine, it is a waste of time, it is *not* efficient, and the list goes on. Still like some dictatorship for some reason millions of people still want to live oppressed apparently (read: use a FREE mozilla browser, FREE Chrome, FREE Opera).

Life is complicated and I cannot propose to ban IE6 as that will make me a dictator.

Developers are like doctors: no matter what the person is the doctor must save that life. We as developers must fix IE6 issues to save IE6 slaves.

Hopefully this will explain why that form of yours sometimes submits and sometimes simply it does not.

Interesting enough I have seen myself IE6 not submitting when receiving the form from the server however the very same form gets submitted if tested from a saved HTML file. But seriously I have no time to deal with it ...

Thursday, December 02, 2010

VINE Remote access for Mac OSX with VNC

As I have to do this every so often I rather document it instead of copying and pasting emails (DRY).

  1. In the target MAC run Vine Server
  2. Choose a password
  3. Share password, external IP address, port (default 5900) and display (default 0) with the client
  4. Open the VNC client from a different machine (In OSX use "Chicken of the VNC". In Windows use RealVNC). Use the provided the IP. If the port is not 5900 and the display is not 0 then use "ip:port+display"
  5. When done stop and quit Vine Server

Troubleshooting

Most of the time firewall rules are responsible for problems so be sure you have port 5900 accessible in your server side. You can configure a different port from the Preferences window of Vine. Share that port with the client. If you use a different than 0 display share it as well with the client.

Followers