Examples with QueryParameters com.yahoo.dba.perf.myperf.common.QueryParameters used on opensource projects

Example 1 with QueryParameters

use of com.yahoo.dba.perf.myperf.common.QueryParameters in project mysql_perf_analyzer by yahoo.

the class MySQLStatusQueryProcessor method querySingle.

@Override
public ResultList querySingle(MyPerfContext context, DBInstanceInfo dbinfo, String appUser, DBConnectionWrapper connWrapper, QueryParameters qps) throws SQLException {
    QueryParameters qps2 = new QueryParameters();
    qps2.setSql("mysql_show_global_status");
    ResultList rList = null;
    rList = context.getQueryEngine().executeQueryGeneric(qps2, connWrapper, qps.getMaxRows());
    if (rList != null) {
        ResultList newList = new ResultList();
        ColumnDescriptor desc = rList.getColumnDescriptor();
        ColumnDescriptor newDesc = new ColumnDescriptor();
        int idx = 0;
        int nameIdx = 0;
        for (ColumnInfo c : desc.getColumns()) {
            if ("VARIABLE_NAME".equalsIgnoreCase(c.getName()))
                nameIdx = idx;
            if ("VALUE".equalsIgnoreCase(c.getName()))
                newDesc.addColumn("VARIABLE_VALUE", c.isNumberType(), idx++);
            else
                newDesc.addColumn(c.getName().toUpperCase(), c.isNumberType(), idx++);
        }
        newList.setColumnDescriptor(newDesc);
        for (ResultRow row : rList.getRows()) {
            ResultRow newRow = new ResultRow();
            newRow.setColumnDescriptor(newDesc);
            int cols = row.getColumns().size();
            for (int i = 0; i < cols; i++) {
                String v = row.getColumns().get(i);
                if (i == nameIdx) {
                    newRow.addColumn(v == null ? null : v.toUpperCase());
                } else
                    newRow.addColumn(v);
            }
            newList.addRow(newRow);
        }
        return newList;
    }
    return null;
}

Example 2 with QueryParameters

use of com.yahoo.dba.perf.myperf.common.QueryParameters in project mysql_perf_analyzer by yahoo.

the class ReplLagQueryProcessor method querySingle.

@Override
public ResultList querySingle(MyPerfContext context, DBInstanceInfo dbinfo, String appUser, DBConnectionWrapper connWrapper, QueryParameters qps) throws SQLException {
    QueryParameters qps2 = new QueryParameters();
    qps2.setSql("mysql_repl_slave");
    ResultList rList = null;
    rList = context.getQueryEngine().executeQueryGeneric(qps2, connWrapper, qps.getMaxRows());
    String master = null;
    String port = null;
    if (rList != null) {
        java.util.LinkedHashMap<String, String> kvPairs = new java.util.LinkedHashMap<String, String>(rList.getRows().size());
        for (ResultRow row : rList.getRows()) {
            if (row.getColumns() == null || row.getColumns().size() < 2)
                continue;
            kvPairs.put(row.getColumns().get(0), row.getColumns().get(1));
        }
        master = kvPairs.get("Master_Host");
        port = kvPairs.get("Master_Port");
        if (master != null && !master.isEmpty() && port != null && !port.isEmpty()) {
            logger.info("Query master status from (" + master + ":" + port + ")");
            DBInstanceInfo dbinfo2 = new DBInstanceInfo();
            dbinfo2.setHostName(master);
            dbinfo2.setPort(port);
            dbinfo2.setDatabaseName("information_schema");
            dbinfo2.setDbType("mysql");
            String url = dbinfo2.getConnectionString();
            DBCredential cred = null;
            if (appUser != null) {
                try {
                    //first, check if the user has his own credential
                    cred = context.getMetaDb().retrieveDBCredential(appUser, dbinfo.getDbGroupName());
                    if (cred != null) {
                        Connection conn = null;
                        Statement stmt = null;
                        ResultSet rs = null;
                        try {
                            DriverManager.setLoginTimeout(60);
                            conn = DriverManager.getConnection(url, cred.getUsername(), cred.getPassword());
                            if (conn != null) {
                                stmt = conn.createStatement();
                                rs = stmt.executeQuery("show master status");
                                //TODO rearrange order
                                ResultList rList2 = new ResultList();
                                rList2.setColumnDescriptor(rList.getColumnDescriptor());
                                for (String e : SLAVE_LAG_ENTRIES) {
                                    ResultRow row = new ResultRow();
                                    row.setColumnDescriptor(rList.getColumnDescriptor());
                                    row.addColumn(e);
                                    row.addColumn(kvPairs.get(e));
                                    rList2.addRow(row);
                                    kvPairs.remove(e);
                                }
                                while (rs != null && rs.next()) {
                                    ResultRow row = new ResultRow();
                                    row.setColumnDescriptor(rList.getColumnDescriptor());
                                    row.addColumn("Master: File");
                                    row.addColumn(rs.getString("File"));
                                    rList2.addRow(row);
                                    row = new ResultRow();
                                    row.setColumnDescriptor(rList.getColumnDescriptor());
                                    row.addColumn("Master: Position");
                                    row.addColumn(rs.getString("Position"));
                                    rList2.addRow(row);
                                //TODO gtid set
                                }
                                //now add slave position info
                                for (String e : SLAVE_POS_ENTRIES) {
                                    ResultRow row = new ResultRow();
                                    row.setColumnDescriptor(rList.getColumnDescriptor());
                                    row.addColumn(e);
                                    row.addColumn(kvPairs.get(e));
                                    rList2.addRow(row);
                                    kvPairs.remove(e);
                                }
                                //now add remaining entries
                                for (Map.Entry<String, String> e : kvPairs.entrySet()) {
                                    ResultRow row = new ResultRow();
                                    row.setColumnDescriptor(rList.getColumnDescriptor());
                                    row.addColumn(e.getKey());
                                    row.addColumn(e.getValue());
                                    rList2.addRow(row);
                                }
                                return rList2;
                            }
                        } catch (Exception ex) {
                        } finally {
                            DBUtils.close(rs);
                            DBUtils.close(stmt);
                            DBUtils.close(conn);
                        }
                    }
                } catch (Exception ex) {
                }
            }
        }
    }
    return rList;
}

Example 3 with QueryParameters

use of com.yahoo.dba.perf.myperf.common.QueryParameters 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 4 with QueryParameters

use of com.yahoo.dba.perf.myperf.common.QueryParameters in project mysql_perf_analyzer by yahoo.

the class VarhistoryController 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;
    //first session check
    boolean isSessionValid = WebAppUtil.hasValidSession(req);
    if (!isSessionValid)
        return this.respondFailure("session timeout. Please logout and re-login.", req);
    qps = WebAppUtil.parseRequestParameter(req);
    qps.setSql("mysql_global_variables");
    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);
    //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 = new ResultList();
    ColumnDescriptor desc = new ColumnDescriptor();
    desc.addColumn("VARIABLE_NAME", false, 1);
    desc.addColumn("VARIABLE_VALUE", false, 2);
    desc.addColumn("COMMENTS", false, 3);
    rList.setColumnDescriptor(desc);
    try {
        ConfigHistory ch = ConfigHistory.load(new File(new File(this.frameworkContext.getFileReposirtoryPath()), "autoscan"), dbinfo);
        if (ch != null && ch.getChanges().size() > 0) {
            {
                ResultRow row = new ResultRow();
                List<String> cols = new ArrayList<String>();
                cols.add("CHANGES");
                cols.add("");
                cols.add(ch.getStartingConfig().getTime() + " - " + ch.getLastCheckedConfig().getTime());
                row.setColumns(cols);
                row.setColumnDescriptor(desc);
                rList.addRow(row);
            }
            //list changed in reverse order
            for (int i = ch.getChanges().size() - 1; i >= 0; i--) {
                ConfigBlock cb = ch.getChanges().get(i);
                ResultRow row = new ResultRow();
                List<String> cols = new ArrayList<String>();
                cols.add("CHANGE TIME");
                cols.add(cb.getTime());
                cols.add("Timestamp (UTC) when checked");
                row.setColumns(cols);
                row.setColumnDescriptor(desc);
                rList.addRow(row);
                HashMap<String, String> changes = new HashMap<String, String>();
                //scan changes with old value
                for (Map.Entry<String, String> e : cb.getVariables().entrySet()) {
                    String key = e.getKey();
                    String val = e.getValue();
                    if (key.startsWith("+-")) {
                        changes.put(key.substring(2), val);
                    }
                }
                for (Map.Entry<String, String> e : cb.getVariables().entrySet()) {
                    String key = e.getKey();
                    String v = e.getValue();
                    row = new ResultRow();
                    cols = new ArrayList<String>();
                    if (key.startsWith("+-"))
                        //skip
                        continue;
                    else if (key.startsWith("+-"))
                        //removed
                        cols.add(key.substring(1));
                    else
                        cols.add(key);
                    cols.add(v);
                    if (changes.containsKey(key))
                        cols.add("Prev Value: " + changes.get(key));
                    else if (key.startsWith("-"))
                        cols.add("Removed");
                    else
                        cols.add("");
                    row.setColumns(cols);
                    row.setColumnDescriptor(desc);
                    rList.addRow(row);
                }
                //add an empty line
                row = new ResultRow();
                cols = new ArrayList<String>();
                cols.add("");
                cols.add("");
                cols.add("");
                row.setColumns(cols);
                row.setColumnDescriptor(desc);
                rList.addRow(row);
            }
        }
        if (ch != null) {
            ConfigBlock cb = ch.getStartingConfig();
            ResultRow row = new ResultRow();
            List<String> cols = new ArrayList<String>();
            cols.add("FIRST RECORD TIME");
            cols.add(cb.getTime());
            if (ch != null && ch.getChanges().size() > 0)
                cols.add("First Recorded Timestamp (UTC)");
            else
                cols.add("No Changes Since First Check (Timestamp UTC)");
            row.setColumns(cols);
            row.setColumnDescriptor(desc);
            rList.addRow(row);
            for (Map.Entry<String, String> e : cb.getVariables().entrySet()) {
                String key = e.getKey();
                String v = e.getValue();
                row = new ResultRow();
                cols = new ArrayList<String>();
                cols.add(key);
                cols.add(v);
                cols.add("");
                row.setColumns(cols);
                row.setColumnDescriptor(desc);
                rList.addRow(row);
            }
        } else {
            status = Constants.STATUS_BAD;
            message = "No variable configuration history has been recorded yet.";
        }
    } catch (Throwable ex) {
        logger.log(Level.SEVERE, "Exception", ex);
        status = Constants.STATUS_BAD;
        message = "Exception: " + ex.getMessage();
    }
    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(rList, qps, status, message));
    return mv;
}

Example 5 with QueryParameters

use of com.yahoo.dba.perf.myperf.common.QueryParameters in project mysql_perf_analyzer by yahoo.

the class WebAppUtil method parseRequestParameter.

public static QueryParameters parseRequestParameter(HttpServletRequest req) {
    QueryParameters qps = new QueryParameters();
    //identify target db
    qps.setGroup(req.getParameter(DBGROUP));
    qps.setHost(req.getParameter(DBHOST));
    //sql handler  
    qps.setSql(req.getParameter("sql"));
    //qps.setRespFormat(req.getParameter("respFormat"));//don't care now
    try {
        //records to show
        qps.setMaxRows(Integer.parseInt(req.getParameter("rows")));
    } catch (Exception ex) {
    }
    Enumeration<String> enu = req.getParameterNames();
    while (enu.hasMoreElements()) {
        String pname = enu.nextElement().toString();
        if (pname.startsWith("p_")) {
            qps.getSqlParams().put(pname, req.getParameter(pname));
        }
    }
    return qps;
}