Getting db column size using JDBC

Looking at ResultSetMetaData api below was my first approach to the problem.

    private static String getColDbType(Connection conn, String table, String col) throws Exception {
        String query = " select * from " + table + " where 2='9999'";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        ResultSetMetaData rsmd = rs.getMetaData();
        try {
            for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
                String columnName = rsmd.getColumnName(i);
                if (columnName.equals(col)) {
                    String s = rsmd.getColumnTypeName(i);
                    if (s.equalsIgnoreCase("varchar")) {
                        s += "(" + rsmd.getPrecision(i) + ")";
                    }
                    return s;
                }
            }
            return null;
        } finally {
            rs.close();
            stmt.close();
        }
    }

However the column sizes were not accurate; googling helped me understand that ResultSetMetaData is in the context of the result set returned by the query and it need not reflect the actual table details. So below is the 2nd approach that finally worked. Tried this with MySQL 5.x db instance

    private static String getColDbType(Connection connection, String table, String col) throws Exception {
        DatabaseMetaData metadata = connection.getMetaData();
        int i = url.lastIndexOf("/") + 1;
        String schema = url.substring(i);
        ResultSet resultSet = metadata.getColumns(connection.getCatalog(), schema.trim(), table, col);
        while (resultSet.next()) {
            String name = resultSet.getString("COLUMN_NAME");
            String type = resultSet.getString("TYPE_NAME");
            int size = resultSet.getInt("COLUMN_SIZE");
            if (type.equalsIgnoreCase("varchar")) {
                return type + "(" + size + ")";
            }
            return type;
        }
        return null;
    }

Logging queries in mysql

At times when debugging ORMs lke hibernate we may need to know what queries are actually hitting the db. MySql gives an easy way to do this. Add the below in the my.cnf file

1. log=/var/log/mysql/general.log
2. place the above in the section [mysqld] and
3. restart the mysql: /etc/init.d/mysqld restart;

MYSQL: logging with out userid and pwd

at times when u import the db it may erase ur userid and pwd. When the java tries to connect to the db it throws exception that this ip is not allowed to connect to the db; in such a case use the below fix. it removes the necessity to have userid and pwd to loginto the mysql

1. skip-grant-tables under the section [mysqld]
2. restart the mysql: /etc/init.d/mysqld restart

be careful that u dont do this on production dbs

MySQL:Got a packet bigger than 'max_allowed_packet' bytes

This is generally the case when u try to import a database from a dump. The fix is to increase the max_allowed_packet size follow the below steps

1. vi /etc/my.cnf
2. add: max_allowed_packet=1G under the section [mysqld] and save the file
3. restart the mysql: /etc/init.d/mysqld restart;
4. it should be smooth, if at all u get a warning telling it will ignore the cnf file, then try
1. chmod 655 /etc/my.cnf and again try to restart.

This should do.

MySQL ERROR 1005 (HY000): Can't create table

Below is the table def that caused the problem:

CREATE  TABLE IF NOT EXISTS `T_ABC` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `source_col` int NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_src_col` (`source_col` ASC) ,
  CONSTRAINT `fk_src_col`
    FOREIGN KEY (`source_col` )
    REFERENCES `parent_table` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

and on execution it was giving the error ERROR 1005 (HY000): Can't create table schema.T_ABC.frm

The fix to issue is: that parent_table.id is of bigint type, while this table foreign key is only int; when i changed the source_col from int to big_int things went well.

hope this helps somebody.