Examples with DBConnectionWrapper com.yahoo.dba.perf.myperf.db.DBConnectionWrapper used on opensource projects

Example 1 with DBConnectionWrapper

use of com.yahoo.dba.perf.myperf.db.DBConnectionWrapper in project mysql_perf_analyzer by yahoo.

the class VardiffController method handleRequestImpl.

@Override
protected ModelAndView handleRequestImpl(HttpServletRequest req, HttpServletResponse resp) throws Exception {
    int status = Constants.STATUS_OK;
    String message = "OK";
    logger.info("receive url " + req.getQueryString());
    QueryParameters qps = null;
    DBInstanceInfo dbinfo = null;
    DBInstanceInfo dbinfo2 = null;
    DBConnectionWrapper connWrapper = null;
    DBConnectionWrapper connWrapper2 = null;
    qps = WebAppUtil.parseRequestParameter(req);
    qps.setSql("mysql_global_variables");
    qps.getSqlParams().put("p_1", "");
    String group2 = req.getParameter("p_1");
    String host2 = req.getParameter("p_2");
    //validation input
    String validation = qps.validate();
    if (validation == null || validation.isEmpty()) {
        //do we have such query?
        try {
            QueryInputValidator.validateSql(this.frameworkContext.getSqlManager(), qps);
        } catch (Exception ex) {
            validation = ex.getMessage();
        }
    }
    if (validation != null && !validation.isEmpty())
        return this.respondFailure(validation, req);
    dbinfo = this.frameworkContext.getDbInfoManager().findDB(qps.getGroup(), qps.getHost());
    if (dbinfo == null)
        return this.respondFailure("Cannot find record for DB (" + qps.getGroup() + ", " + qps.getHost() + ")", req);
    dbinfo2 = this.frameworkContext.getDbInfoManager().findDB(group2, host2);
    if (dbinfo2 == null)
        return this.respondFailure("Cannot find record for DB (" + group2 + ", " + host2 + ")", req);
    try {
        connWrapper = WebAppUtil.getDBConnection(req, this.frameworkContext, dbinfo);
        if (connWrapper == null) {
            status = Constants.STATUS_BAD;
            message = "failed to connect to target db (" + dbinfo + ")";
        } else {
            connWrapper2 = WebAppUtil.getDBConnection(req, this.frameworkContext, dbinfo2);
            if (connWrapper2 == null) {
                status = Constants.STATUS_BAD;
                message = "failed to connect to target db (" + dbinfo2 + ")";
            }
        }
    } catch (Throwable th) {
        logger.log(Level.SEVERE, "Exception", th);
        status = Constants.STATUS_BAD;
        message = "Failed to get connection to target db (" + dbinfo + "): " + th.getMessage();
    }
    if (status == -1)
        return this.respondFailure(message, req);
    //when we reach here, at least we have valid query and can connect to db	
    WebAppUtil.storeLastDbInfoRequest(qps.getGroup(), qps.getHost(), req);
    ModelAndView mv = null;
    ResultList rList = null;
    ResultList rList2 = null;
    try {
        rList = this.frameworkContext.getQueryEngine().executeQueryGeneric(qps, connWrapper, qps.getMaxRows());
        rList2 = this.frameworkContext.getQueryEngine().executeQueryGeneric(qps, connWrapper2, qps.getMaxRows());
        logger.info("Done query " + qps.getSql() + " with " + (rList != null ? rList.getRows().size() : 0) + " records, " + (rList2 != null ? rList2.getRows().size() : 0) + " records");
        WebAppUtil.closeDBConnection(req, connWrapper, false, this.getFrameworkContext().getMyperfConfig().isReuseMonUserConnction());
        WebAppUtil.closeDBConnection(req, connWrapper2, false, this.getFrameworkContext().getMyperfConfig().isReuseMonUserConnction());
    } catch (Throwable ex) {
        logger.log(Level.SEVERE, "Exception", ex);
        if (ex instanceof SQLException) {
            SQLException sqlEx = SQLException.class.cast(ex);
            String msg = ex.getMessage();
            logger.info(sqlEx.getSQLState() + ", " + sqlEx.getErrorCode() + ", " + msg);
            //check if the connection is still good
            if (!DBUtils.checkConnection(connWrapper.getConnection())) {
                WebAppUtil.closeDBConnection(req, connWrapper, true, false);
            } else
                WebAppUtil.closeDBConnection(req, connWrapper, true, false);
            if (!DBUtils.checkConnection(connWrapper2.getConnection())) {
                WebAppUtil.closeDBConnection(req, connWrapper2, true, false);
            } else
                WebAppUtil.closeDBConnection(req, connWrapper2, true, false);
        } else {
            WebAppUtil.closeDBConnection(req, connWrapper, false, this.getFrameworkContext().getMyperfConfig().isReuseMonUserConnction());
            WebAppUtil.closeDBConnection(req, connWrapper2, false, this.getFrameworkContext().getMyperfConfig().isReuseMonUserConnction());
        }
        status = Constants.STATUS_BAD;
        message = "Exception: " + ex.getMessage();
    }
    if (status == Constants.STATUS_BAD)
        return this.respondFailure(message, req);
    HashMap<String, String> param1 = new HashMap<String, String>(rList.getRows().size());
    HashMap<String, String> param2 = new HashMap<String, String>(rList2.getRows().size());
    for (ResultRow r : rList.getRows()) {
        param1.put(r.getColumns().get(0).toUpperCase(), r.getColumns().get(1));
    }
    for (ResultRow r : rList2.getRows()) {
        param2.put(r.getColumns().get(0).toUpperCase(), r.getColumns().get(1));
    }
    ColumnDescriptor desc = new ColumnDescriptor();
    desc.addColumn("VARIABLE_NAME", false, 1);
    desc.addColumn("DB1", false, 2);
    desc.addColumn("DB2", false, 3);
    ResultList fList = new ResultList();
    fList.setColumnDescriptor(desc);
    HashSet<String> diffSet = new HashSet<String>();
    for (Map.Entry<String, String> e : param1.entrySet()) {
        String k = e.getKey();
        String v = e.getValue();
        if (v != null)
            v = v.trim();
        else
            v = "";
        String v2 = null;
        if (param2.containsKey(k))
            v2 = param2.get(k);
        if (v2 != null)
            v2 = v2.trim();
        else
            v2 = "";
        if (!v.equals(v2)) {
            ResultRow row = new ResultRow();
            List<String> cols = new ArrayList<String>();
            cols.add(k);
            cols.add(v);
            cols.add(v2);
            row.setColumns(cols);
            row.setColumnDescriptor(desc);
            fList.addRow(row);
            diffSet.add(k);
        }
    }
    for (Map.Entry<String, String> e : param2.entrySet()) {
        String k = e.getKey();
        String v = e.getValue();
        if (v == null || v.isEmpty())
            continue;
        if (diffSet.contains(k) || param1.containsKey(k))
            continue;
        ResultRow row = new ResultRow();
        List<String> cols = new ArrayList<String>();
        cols.add(k);
        cols.add("");
        cols.add(v);
        row.setColumns(cols);
        row.setColumnDescriptor(desc);
        fList.addRow(row);
    }
    mv = new ModelAndView(this.jsonView);
    if (req.getParameter("callback") != null && req.getParameter("callback").trim().length() > 0)
        //YUI datasource binding
        mv.addObject("callback", req.getParameter("callback"));
    mv.addObject("json_result", ResultListUtil.toJSONString(fList, qps, status, message));
    return mv;
}

Example 2 with DBConnectionWrapper

use of com.yahoo.dba.perf.myperf.db.DBConnectionWrapper in project mysql_perf_analyzer by yahoo.

the class UdmController method testAndValidateAlert.

private void testAndValidateAlert(HttpServletRequest req, AlertDefinition def, String db) throws Exception {
    if (def == null)
        throw new Exception("please provide valid alert definition for test");
    if (AlertDefinition.SOURCE_SQL.equals(def.getSource())) {
        if (def.getSqlText() == null || def.getSqlText().isEmpty())
            throw new Exception("please provide valid SQL text for test");
        if (db == null || db.isEmpty())
            throw new Exception("please provide valid database for test");
        String[] dbs = db.split("\\|");
        if (dbs == null || dbs.length < 2)
            throw new Exception("please provide valid database for test");
        DBInstanceInfo dbinfo = this.frameworkContext.getDbInfoManager().findDB(dbs[0], dbs[1]);
        if (dbinfo == null)
            throw new Exception("please provide valid database for test");
        DBConnectionWrapper connWrapper = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            connWrapper = WebAppUtil.getDBConnection(req, this.frameworkContext, dbinfo);
            if (connWrapper == null) {
                throw new Exception("failed to connect to target db (" + dbinfo + ")");
            }
            String sql = def.getSqlText();
            if (def.getParams() != null && def.getParams().size() > 0) {
                for (Map.Entry<String, String> e : def.getParams().entrySet()) {
                    sql = sql.replace("&" + e.getKey(), e.getValue());
                }
            }
            stmt = connWrapper.getConnection().createStatement();
            rs = stmt.executeQuery(sql);
        } finally {
            DBUtils.close(rs);
            DBUtils.close(stmt);
            //close it anyway
            WebAppUtil.closeDBConnection(req, connWrapper, true, false);
        }
    } else if (AlertDefinition.SOURCE_METRICS.equals(def.getSource())) {
        String metricsName = def.getMetricName();
        if (metricsName == null || metricsName.isEmpty())
            throw new Exception("please provide valid metrics name in the format of group[.subgroup].metric_name");
        String[] ms = metricsName.split("\\.");
        if (ms.length < 2)
            throw new Exception("please provide valid metrics name in the format of group[.subgroup].metric_name. " + metricsName + " is not valid.");
        MetricsGroup mg = this.frameworkContext.getMetricsDef().getGroupByName(ms[0]);
        if (mg == null) {
            //try UDM
            UserDefinedMetrics udm = this.frameworkContext.getMetricsDef().getUdmManager().getUDMByName(ms[0]);
            if (udm == null)
                throw new Exception("please provide valid metrics name in the format of group[.subgroup].metric_name. " + ms[0] + " is not valid metrics group name.");
            else
                mg = udm.getMetricsGroup();
        }
        if (mg == null)
            throw new Exception("please provide valid metrics name in the format of group[.subgroup].metric_name. " + ms[0] + " is not valid metrics group name.");
        if (mg.getSubGroups().size() > 0) {
            if (ms.length < 3)
                throw new Exception("please provide valid metrics name in the format of group[.subgroup].metric_name. " + ms[0] + " has sub groups.");
            else
                mg = mg.getSubGroupByName(ms[1]);
            if (mg == null)
                throw new Exception("please provide valid metrics name in the format of group[.subgroup].metric_name. " + ms[1] + " is not a valid sub group in group " + ms[0]);
        }
        boolean findOne = false;
        for (Metric m : mg.getMetrics()) {
            if (m.getName().equals(ms[ms.length - 1])) {
                findOne = true;
                break;
            }
        }
        if (!findOne)
            throw new Exception("please provide valid metrics name in the format of group[.subgroup].metric_name. " + metricsName + " is not valid.");
    }
}

Example 3 with DBConnectionWrapper

use of com.yahoo.dba.perf.myperf.db.DBConnectionWrapper in project mysql_perf_analyzer by yahoo.

the class UdmController method testUDM.

/**
	 * 
	 * Test UDM, use exception to send out message
	 * @param req
	 * @param udm
	 * @param db
	 * @throws SQLException
	 */
private void testUDM(HttpServletRequest req, UserDefinedMetrics udm, String db) throws Exception {
    if (db == null || db.isEmpty())
        throw new Exception("please provide valid database for test");
    String[] dbs = db.split("\\|");
    if (dbs == null || dbs.length < 2)
        throw new Exception("please provide valid database for test");
    DBInstanceInfo dbinfo = this.frameworkContext.getDbInfoManager().findDB(dbs[0], dbs[1]);
    if (dbinfo == null)
        throw new Exception("please provide valid database for test");
    HashSet<String> metricsNameSet = new HashSet<String>();
    for (Metric m : udm.getMetrics()) {
        metricsNameSet.add(m.getSourceName());
    }
    DBConnectionWrapper connWrapper = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
        String sql = udm.getSql();
        MetricsGroup mg = udm.getMetricsGroup();
        String udmType = udm.getUdmType();
        String nameCol = udm.getNameCol();
        String valCol = udm.getValueCol();
        String keyCol = udm.getKeyCol();
        boolean isBuiltin = false;
        if (!"SQL".equals(udm.getSource())) {
            sql = this.frameworkContext.getSqlTextForMetricsGroup(udm.getSource());
            mg = this.frameworkContext.getMetricsDef().getGroupByName(udm.getSource());
            if (mg != null) {
                if (mg.getKeyColumn() != null)
                    udmType = "key";
                else if (mg.isMultipleMetricsPerRow())
                    udmType = "column";
                else
                    udmType = "row";
                nameCol = mg.getMetricNameColumn();
                valCol = mg.getMetricValueColumn();
                keyCol = mg.getKeyColumn();
            }
            isBuiltin = true;
        }
        if (sql == null || sql.isEmpty()) {
            throw new Exception("please provide valid SQL");
        }
        connWrapper = WebAppUtil.getDBConnection(req, this.frameworkContext, dbinfo);
        if (connWrapper == null) {
            throw new Exception("failed to connect to target db (" + dbinfo + ")");
        }
        stmt = connWrapper.getConnection().createStatement();
        rs = stmt.executeQuery(sql);
        if (rs != null) {
            ResultSetMetaData meta = rs.getMetaData();
            //verify columns
            int cols = meta.getColumnCount();
            Map<String, Integer> colMap = new HashMap<String, Integer>(cols);
            for (int i = 1; i <= cols; i++) colMap.put(meta.getColumnName(i).toUpperCase(), meta.getColumnType(i));
            if ("row".equals(udmType)) {
                if (!colMap.containsKey(udm.getNameCol().toUpperCase()))
                    throw new Exception("Failed to find name column from SQL result: " + udm.getNameCol() + ", returned: " + colMap);
                if (!colMap.containsKey(udm.getValueCol().toUpperCase()))
                    throw new Exception("Failed to find value column from SQL result: " + udm.getValueCol() + ", returned: " + colMap);
            } else //check metrics column 
            {
                if ("key".equals(udmType)) {
                    if (!colMap.containsKey(keyCol.toUpperCase()))
                        throw new Exception("Failed to find key column from SQL result: " + udm.getKeyCol());
                }
                for (Metric m : udm.getMetrics()) {
                    if (!colMap.containsKey(m.getSourceName().toUpperCase()))
                        throw new Exception("Failed to find metric column from SQL result: " + m.getSourceName());
                }
            }
        } else {
            throw new Exception("Failed to test SQL.");
        }
        while (rs != null && rs.next()) {
            if ("row".equals(udmType)) {
                String name = rs.getString(nameCol);
                if (!metricsNameSet.contains(name))
                    continue;
                String val = rs.getString(valCol);
                try {
                    BigDecimal d = new BigDecimal(val == null ? "0" : val);
                } catch (Exception ex) {
                    throw new Exception("Expect numeric value for metric from SQL result, got " + val);
                }
            } else {
                for (Metric m : udm.getMetrics()) {
                    String val = rs.getString(m.getSourceName());
                    try {
                        BigDecimal d = new BigDecimal(val == null ? "0" : val);
                    } catch (Exception ex) {
                        throw new Exception("Expect numeric value metric value from SQL result for column " + m.getShortName() + ", got " + val);
                    }
                }
            }
        }
    } finally {
        DBUtils.close(rs);
        DBUtils.close(stmt);
        //close it anyway
        WebAppUtil.closeDBConnection(req, connWrapper, true, false);
    }
}

Example 4 with DBConnectionWrapper

use of com.yahoo.dba.perf.myperf.db.DBConnectionWrapper in project mysql_perf_analyzer by yahoo.

the class GlobalVariableChangeScanTask method scanHost.

private ConfigBlock scanHost(DBInstanceInfo dbinfo) {
    boolean status = false;
    DBCredential cred = DBUtils.findDBCredential(context, dbinfo.getDbGroupName(), appUser);
    if (cred == null) {
        logger.info("No credential for cluster " + dbinfo.getDbGroupName() + ", skip it");
        //log the error
        return null;
    }
    logger.info("Scan for host (" + dbinfo + ") as user " + cred.getUsername());
    //find one connection is enough
    DBConnectionWrapper conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    ConfigBlock cb = null;
    try {
        conn = conns.checkoutConnection(dbinfo, cred);
        if (conn == null) {
            logger.info("Failed to access " + dbinfo + ", skip it");
            return null;
        }
        String sqlText = "select variable_name, variable_value from information_schema.global_variables";
        stmt = conn.getConnection().createStatement();
        rs = stmt.executeQuery(sqlText);
        cb = new ConfigBlock();
        while (rs != null && rs.next()) {
            String key = rs.getString("variable_name");
            //exclude a timestamp variable
            if ("TIMESTAMP".equalsIgnoreCase(key))
                continue;
            cb.addVariable(key, rs.getString("variable_value"));
        }
        Calendar c = Calendar.getInstance();
        Date dt = c.getTime();
        cb.setTime(sdf.format(dt));
    } catch (Exception ex) {
        logger.log(Level.WARNING, "exception", ex);
    } finally {
        DBUtils.close(rs);
        DBUtils.close(stmt);
        if (conn != null)
            conns.checkinConnectionAndClose(conn);
    }
    logger.info("Done configuration scan for host (" + dbinfo + ") as user " + cred.getUsername());
    return cb;
}

Example 5 with DBConnectionWrapper

use of com.yahoo.dba.perf.myperf.db.DBConnectionWrapper in project mysql_perf_analyzer by yahoo.

the class InnoController method handleRequestImpl.

@Override
protected ModelAndView handleRequestImpl(HttpServletRequest req, HttpServletResponse resp) throws Exception {
    int status = Constants.STATUS_OK;
    String message = "OK";
    String group = req.getParameter("group");
    String host = req.getParameter("host");
    QueryParameters qps = new QueryParameters();
    qps.setGroup(group);
    qps.setHost(host);
    qps.setSql("mysql_innodb_engine_status");
    ResultList rList = null;
    LinkedHashMap<String, ResultList> listMap = new LinkedHashMap<String, ResultList>();
    DBInstanceInfo dbinfo = null;
    DBConnectionWrapper connWrapper = null;
    try {
        dbinfo = this.frameworkContext.getDbInfoManager().findDB(group, host).copy();
        connWrapper = WebAppUtil.getDBConnection(req, this.frameworkContext, dbinfo);
        if (connWrapper == null) {
            status = Constants.STATUS_BAD;
            message = "failed to connect to target db (" + dbinfo + ")";
        } else {
            rList = this.frameworkContext.getQueryEngine().executeQueryGeneric(qps, connWrapper, qps.getMaxRows());
            logger.info("Done query " + qps.getSql() + " with " + (rList != null ? rList.getRows().size() : 0) + " records.");
            if (rList != null && rList.getRows().size() > 0) {
                logger.info(rList.getRows().get(0).getColumns().get(rList.getRows().get(0).getColumns().size() - 1));
                listMap = parse(rList.getRows().get(0).getColumns().get(rList.getRows().get(0).getColumns().size() - 1));
            }
            WebAppUtil.closeDBConnection(req, connWrapper, false, this.getFrameworkContext().getMyperfConfig().isReuseMonUserConnction());
        }
    } catch (Throwable th) {
        logger.log(Level.SEVERE, "Exception", th);
        if (th instanceof SQLException) {
            SQLException sqlEx = SQLException.class.cast(th);
            String msg = th.getMessage();
            logger.info(sqlEx.getSQLState() + ", " + sqlEx.getErrorCode() + ", " + msg);
            //check if the connection is still good
            if (!DBUtils.checkConnection(connWrapper.getConnection())) {
                WebAppUtil.closeDBConnection(req, connWrapper, true, false);
            } else
                WebAppUtil.closeDBConnection(req, connWrapper, true, false);
        } else {
            if (connWrapper != null)
                WebAppUtil.closeDBConnection(req, connWrapper, false, this.getFrameworkContext().getMyperfConfig().isReuseMonUserConnction());
        }
        status = Constants.STATUS_BAD;
        message = "Exception: " + th.getMessage();
    } finally {
    }
    ModelAndView mv = new ModelAndView(this.jsonView);
    if (req.getParameter("callback") != null && req.getParameter("callback").trim().length() > 0)
        //YUI datasource binding
        mv.addObject("callback", req.getParameter("callback"));
    mv = new ModelAndView(this.jsonView);
    mv.addObject("json_result", ResultListUtil.toMultiListJSONStringUpper(listMap, qps, status, message));
    return mv;
}