BiTel

В ошибках accounting появляется запись

Вот еще, теперь только в access.error.log
connection 08-10/20:00:25 ERROR [sa-p-10-t-2] DefaultServerSetup - Many statements was open at connection close:
com.mysql.jdbc.JDBC4PreparedStatement@97b275b: INSERT INTO connection_log_entry_13_201208 (applicationId, deviceId, devicePort, connectionId, time, acctSessId, type, requestDataLogId, requestChunkI
d, requestPosition, responseDataLogId, responseChunkId, responsePosition) VALUES (3,2,56,5832,'2012-08-10 20:00:15','4505339-q-56',3,0,1,3078,-1,-1,-1)
com.mysql.jdbc.JDBC4PreparedStatement@132424db: INSERT INTO connection_log_entry_13_201208 (applicationId, deviceId, devicePort, connectionId, time, acctSessId, type, requestDataLogId, requestChunk
Id, requestPosition, responseDataLogId, responseChunkId, responsePosition) VALUES (3,2,148,6288,'2012-08-10 20:00:15','4585945-q-148',3,0,1,3449,-1,-1,-1)
com.mysql.jdbc.JDBC4PreparedStatement@401b80c3: INSERT INTO connection_log_entry_13_201208 (applicationId, deviceId, devicePort, connectionId, time, acctSessId, type, requestDataLogId, requestChunk
Id, requestPosition, responseDataLogId, responseChunkId, responsePosition) VALUES (3,2,130,5941,'2012-08-10 20:00:15','4505352-q-130',3,0,1,3849,-1,-1,-1)
com.mysql.jdbc.JDBC4PreparedStatement@3a7bc66e: INSERT INTO connection_log_entry_13_201208 (applicationId, deviceId, devicePort, connectionId, time, acctSessId, type, requestDataLogId, requestChunk
Id, requestPosition, responseDataLogId, responseChunkId, responsePosition) VALUES (3,2,55,6327,'2012-08-10 20:00:15','4597923-q-55',3,0,1,4249,-1,-1,-1)
com.mysql.jdbc.JDBC4PreparedStatement@7df766ae: INSERT INTO connection_log_entry_13_201208 (applicationId, deviceId, devicePort, connectionId, time, acctSessId, type, requestDataLogId, requestChunk

Выложили обновление.

Еще появились ошибки

Выложили.

Доброго дня.
Хотел бы апнуть тему.
Имею
Сервер: вер. 6.1.1177 / 19.08.2016 11:58:42
os: Linux; java: Java HotSpot(TM) 64-Bit Server VM, v.1.7.0_80

Выполняю:
<pre>
Contract contract = new Contract();
manager = "SELECT id from contract LIMIT LIMIT ?,?";
preparedStatement = this.con.prepareStatement(manager);
preparedStatement.setInt(1, offset);
preparedStatement.setInt(1, limit);
while(rs.next()) {
int contract_id = rs.getInt("id");
contract = manager1.getContractById(contract_id);
}
</pre>

Получаю ошибку в логи сервера:
---
com.mysql.jdbc.ServerPreparedStatement[1905] - SELECT * FROM contract WHERE id=73681
com.mysql.jdbc.ServerPreparedStatement[1908] - SELECT * FROM contract WHERE id=73918
com.mysql.jdbc.ServerPreparedStatement[1911] - SELECT * FROM contract WHERE id=74068
com.mysql.jdbc.ServerPreparedStatement[1914] - SELECT * FROM contract WHERE id=74293
com.mysql.jdbc.ServerPreparedStatement[1917] - SELECT * FROM contract WHERE id=74450
com.mysql.jdbc.ServerPreparedStatement[1920] - SELECT * FROM contract WHERE id=74494
com.mysql.jdbc.ServerPreparedStatement[1923] - SELECT * FROM contract WHERE id=74617
com.mysql.jdbc.ServerPreparedStatement[1926] - SELECT * FROM contract WHERE id=74658
com.mysql.jdbc.ServerPreparedStatement[1929] - SELECT * FROM contract WHERE id=74763
com.mysql.jdbc.ServerPreparedStatement[1932] - SELECT * FROM contract WHERE id=75117
com.mysql.jdbc.ServerPreparedStatement[1935] - SELECT * FROM contract WHERE id=75165

java.lang.RuntimeException
at ru.bitel.bgbilling.server.util.DefaultServerSetup$2$1.close(DefaultServerSetup.java:417)
at ru.bitel.bgbilling.server.util.PoolGuardConnectionWrapper.close(PoolGuardConnectionWrapper.java:48)
at ru.bitel.common.sql.ConnectionSet.recycle(ConnectionSet.java:272)
at ru.bitel.bgbilling.kernel.base.server.DefaultContext.destroy(DefaultContext.java:37)
at ru.bitel.bgbilling.kernel.container.managed.ServerContext.destroy(ServerContext.java:400)
at bitel.billing.server.ActionBase.freeConnection(ActionBase.java:180)
at bitel.billing.server.Executer.doModule(Unknown Source)
at bitel.billing.server.Executer$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:415)
at bitel.billing.server.Executer.doPost(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at ru.bitel.common.server.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:54)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
---
Насколько видно из обсуждения проблема может быть вот здесь:
http://joxi.ru/p27OWpat0a5Zy2
Отсутствует закрывающий метод для statement .
Возможно ли поправить для моей версии сервера данную ошибку?

Он там кэшируется. Если использовать один и тот же ContractManager в цикле - должно быть нормально.
Если нет возможности - вызывать после его использования contractManager.recycle().

Вот есть есть про это тема еще :
https://forum.bitel.ru/viewtopic.php?f=22&t=7369

Добавил manager1.recycle()
Ошибка не ушла.
---
10-05/14:52:33 ERROR [http-bio-0.0.0.0-8080-exec-5] DefaultServerSetup - Many statements was open at connection close:
com.mysql.jdbc.ServerPreparedStatement[348] - SELECT * FROM contract WHERE id=26817
com.mysql.jdbc.ServerPreparedStatement[363] - SELECT * FROM contract WHERE id=16062
com.mysql.jdbc.ServerPreparedStatement[366] - SELECT * FROM contract WHERE id=18133
---skip---
java.lang.RuntimeException
at ru.bitel.bgbilling.server.util.DefaultServerSetup$2$1.close(DefaultServerSetup.java:417)
at ru.bitel.bgbilling.server.util.PoolGuardConnectionWrapper.close(PoolGuardConnectionWrapper.java:48)
at ru.bitel.common.sql.ConnectionSet.recycle(ConnectionSet.java:272)
at ru.bitel.bgbilling.kernel.base.server.DefaultContext.destroy(DefaultContext.java:37)
at ru.bitel.bgbilling.kernel.container.managed.ServerContext.destroy(ServerContext.java:400)
at bitel.billing.server.ActionBase.freeConnection(ActionBase.java:180)
---

Приводу полный листинг action
Буду благодарен если подскажете где еще может быть ошибка.
---

import bitel.billing.server.ActionBase;
import bitel.billing.server.contract.bean.BalanceUtils;
import bitel.billing.server.contract.bean.Contract;
import bitel.billing.server.contract.bean.ContractAddressParamValue;
import bitel.billing.server.contract.bean.ContractManager;
import bitel.billing.server.contract.bean.ContractParameterManager;
import bitel.billing.server.contract.bean.ContractStatus;
import bitel.billing.server.contract.bean.ContractStatusManager;
import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.Map.Entry;
import org.w3c.dom.Element;
import ru.bitel.bgbilling.common.BGException;

public class ActionGetContracts extends ActionBase {
    public ActionGetContracts() {
    }

    private void getParameter(String value, Integer pid, Map<Integer, String> contract_params, Element xml_node) {
        Iterator i$ = contract_params.entrySet().iterator();

        while(i$.hasNext()) {
            Entry entry = (Entry)i$.next();
            if(((Integer)entry.getKey()).equals(pid)) {
                Element el_param = this.createElement(xml_node, "parameter");
                el_param.setAttribute("pid", String.valueOf(pid));
                el_param.setAttribute("title", (String)entry.getValue());
                el_param.setAttribute("value", value);
                xml_node.appendChild(el_param);
                break;
            }
        }
    }

    private void getParameters(Integer cid, Element xml_node, Map<Integer, String> contract_params) throws SQLException {
        String sql_1 = "SELECT pid, val AS value FROM contract_parameter_type_1 WHERE cid = " + String.valueOf(cid) + " UNION SELECT pid, address AS value FROM contract_parameter_type_2 " + 
" WHERE cid = " + cid + " UNION SELECT pid, email AS value FROM contract_parameter_type_3 WHERE cid = " + cid + " UNION SELECT pid, val AS value FROM contract_parameter_type_6 " +
" WHERE cid = " + cid + " UNION SELECT pid, val AS value FROM contract_parameter_type_7 WHERE cid = " + cid + " UNION SELECT pid, value AS value FROM contract_parameter_type_phone " + WHERE cid = " + cid + ";";
        PreparedStatement ps = this.con.prepareStatement(sql_1);
        ResultSet rs = ps.executeQuery();

        while(rs.next()) {
            int tip = rs.getInt("pid");
            String value = rs.getString("value");
            this.getParameter(value, Integer.valueOf(tip), contract_params, xml_node);
        }

        ps.close();
    }

    public void doAction() throws SQLException, BGException {
        Element respEl = this.createElement(this.rootNode, "response");
        int limit = this.getIntParameter("limit", 10);
        int offset = this.getIntParameter("offset", 0);
        int timestamp = this.getIntParameter("timestamp", -1);
        int cid = this.getIntParameter("contract_id", -1);
        PreparedStatement preparedStatement = null;

        try {
            Date currentDate = new Date();
            Date date_from;
            Date date_to;
            int status;
            String manager;
            if(cid > 0) {
                manager = "SELECT id from contract WHERE id = ?";
                preparedStatement = this.con.prepareStatement(manager);
                preparedStatement.setInt(1, cid);
            } else if(timestamp > 0) {
                checkContracts(this.con);
                manager = "SELECT cid AS id,closed FROM `crealink`.`crealink_update_history` where closed = 0 order by last_access_date LIMIT ?,?";
                preparedStatement = this.con.prepareStatement(manager);
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(2, limit);
            } else {
                manager = "SELECT id from contract LIMIT LIMIT ?,?";
                preparedStatement = this.con.prepareStatement(manager);
                preparedStatement.setInt(1, offset);
                preparedStatement.setInt(1, limit);
            }

            ContractManager manager1 = new ContractManager(this.con);
            Contract contract = new Contract();
            ContractParameterManager param_manager = new ContractParameterManager(this.con);
            ContractStatusManager contract_status_manager = new ContractStatusManager(this.con);
            HashSet address_pids = new HashSet();
            address_pids.add(Integer.valueOf(6));
            address_pids.add(Integer.valueOf(37));
            address_pids.add(Integer.valueOf(38));
            HashMap contract_params = new HashMap();
            String sql_contract_params = "SELECT * FROM contract_parameters_pref;";
            PreparedStatement ps_contract_params = this.con.prepareStatement(sql_contract_params);
            ResultSet rs_contract_params = ps_contract_params.executeQuery();

            while(rs_contract_params.next()) {
                contract_params.put(Integer.valueOf(rs_contract_params.getInt("id")), rs_contract_params.getString("title"));
            }

            ps_contract_params.close();
            rs_contract_params.close();
            ResultSet rs = preparedStatement.executeQuery();
            ArrayList<Integer> arContractsId = new ArrayList<Integer>();
            ArrayList<Integer> arClosedContractdId = new ArrayList<Integer>();

            while(rs.next()) {
                int contract_id = rs.getInt("id");
                contract = manager1.getContractById(contract_id);
                manager1.recycle();
                Element contractxml = this.createElement(this.rootNode, "contract");
                this.getParameters(Integer.valueOf(contract_id), contractxml, contract_params);
                contractxml.setAttribute("id", String.valueOf(contract_id));
                contractxml.setAttribute("fc", String.valueOf(contract.getFc() + 1));
                contractxml.setAttribute("title", contract.getTitle());
                contractxml.setAttribute("comment", contract.getComment());
                contractxml.setAttribute("password", contract.getPswd());
                ContractStatus contract_status = contract_status_manager.getStatus(contract_id, currentDate);
                if(contract_status != null) {
                    status = contract_status.getStatus();
                    date_from = contract_status.getDateFrom();
                    date_to = contract_status.getDateTo();
                } else {
                    status = 0;
                    date_from = contract.getDateFrom();
                    date_to = contract.getDateTo();
                }
                contractxml.setAttribute("status", String.valueOf(status));
                contractxml.setAttribute("date_status_begin", date_from.toString());
                contractxml.setAttribute("date_status_end", date_to != null ? date_to.toString() : "");
                contractxml.setAttribute("date_active_out", date_to != null ? date_to.toString() : "");
                BalanceUtils balance_utils1 = new BalanceUtils(this.con);
                BigDecimal contract_balance1 = balance_utils1.getBalance(currentDate, contract_id);
                if(status == 3 && contract_balance1.floatValue() >= -10.00 && contract_balance1.floatValue() <= 10.00)
                    arClosedContractdId.add(contract_id);
                arContractsId.add(contract_id);

                contractxml.setAttribute("balance", String.valueOf(contract_balance1));
                String sql_last_credit1 = "SELECT mm,yy FROM contract_balance WHERE (cid = ?) AND (summa1 + summa2 - summa3 - summa4 < 0) AND (summa1 >= 0) ORDER BY yy DESC, 0+mm DESC LIMIT 1;";
                PreparedStatement ps11 = this.con.prepareStatement(sql_last_credit1);
                ps11.setInt(1, contract_id);
                ResultSet rsLastCredit1 = ps11.executeQuery();
                Calendar last_contract_credit1 = null;
                while(rsLastCredit1.next()) {
                    int date_format = rsLastCredit1.getInt("yy");
                    int sql_limit = rsLastCredit1.getInt("mm");
                    last_contract_credit1 = Calendar.getInstance();
                    last_contract_credit1.set(date_format, sql_limit - 1, 1);
                }
                rsLastCredit1.close();
                ps11.close();

                SimpleDateFormat date_format1 = new SimpleDateFormat("01.MM.yyyy");
                contractxml.setAttribute("last_credit", last_contract_credit1 != null?date_format1.format(last_contract_credit1.getTime()):"");
                ps11.close();
                String sql_limit1 = "SELECT closesumma FROM contract WHERE id = ? LIMIT 1;";
                PreparedStatement ps2 = this.con.prepareStatement(sql_limit1);
                ps2.setInt(1, contract_id);
                int now_contract_limit = 0;

                for(ResultSet rsLimitSelect = ps2.executeQuery(); rsLimitSelect.next(); now_contract_limit = rsLimitSelect.getInt("closesumma")) {
                    ;
                }

                ps2.close();
                contractxml.setAttribute("limit", String.valueOf(now_contract_limit));
                String sql_last_payment = "SELECT dt, summa FROM contract_payment WHERE cid = ? ORDER BY dt DESC LIMIT 1;";
                PreparedStatement ps_last_payment = this.con.prepareStatement(sql_last_payment);
                ps_last_payment.setInt(1, contract_id);
                String now_last_payment_date = "";
                BigDecimal now_last_payment_amount = new BigDecimal(0.0D);

                for(ResultSet rsLastPayment = ps_last_payment.executeQuery(); rsLastPayment.next(); now_last_payment_amount = rsLastPayment.getBigDecimal("summa")) {
                    now_last_payment_date = rsLastPayment.getString("dt");
                }

                contractxml.setAttribute("last_payment_date", String.valueOf(now_last_payment_date));
                contractxml.setAttribute("last_payment_amount", String.valueOf(now_last_payment_amount));
                ps_last_payment.close();
                Map address_map = param_manager.getAddressParamMap(contract_id, address_pids);
                Iterator sql_tariffs = address_map.entrySet().iterator();

                while(sql_tariffs.hasNext()) {
                    Entry ps_tariffs = (Entry)sql_tariffs.next();
                    int rs_tariffs = ((Integer)ps_tariffs.getKey()).intValue();
                    ContractAddressParamValue el_tariff = (ContractAddressParamValue)ps_tariffs.getValue();
                    Element el_address = this.createElement(contractxml, "address");
                    el_address.setAttribute("pid", String.valueOf(rs_tariffs));
                    el_address.setAttribute("pod", String.valueOf(el_tariff.getPod() == -1?0:el_tariff.getPod()));
                    el_address.setAttribute("floor", String.valueOf(el_tariff.getFloor() == -1?0:el_tariff.getFloor()));
                    el_address.setAttribute("house_id", String.valueOf(el_tariff.getHouseId()));
                    el_address.setAttribute("room", el_tariff.getRoom() == "null"?"":el_tariff.getRoom());
                    el_address.setAttribute("flat", el_tariff.getFlat());
                    el_address.setAttribute("address", String.valueOf(el_tariff.getAddress()));
                    contractxml.appendChild(el_address);
                }

                String sql_tariffs1 = "SELECT id, tpid, date1, date2, comment FROM contract_tariff WHERE cid = ?;";
                PreparedStatement ps_tariffs1 = this.con.prepareStatement(sql_tariffs1);
                ps_tariffs1.setInt(1, contract_id);
                ResultSet rs_tariffs1 = ps_tariffs1.executeQuery();

                while(rs_tariffs1.next()) {
                    Element el_tariff1 = this.createElement(contractxml, "tariff");
                    el_tariff1.setAttribute("rec_id", rs_tariffs1.getString("id"));
                    el_tariff1.setAttribute("plan_id", rs_tariffs1.getString("tpid"));
                    el_tariff1.setAttribute("date_from", rs_tariffs1.getString("date1"));
                    el_tariff1.setAttribute("date_to", rs_tariffs1.getString("date2"));
                    el_tariff1.setAttribute("comment", rs_tariffs1.getString("comment"));
                    contractxml.appendChild(el_tariff1);
                }
                rs_tariffs1.close();
                ps_tariffs1.close();
                this.rootNode.appendChild(contractxml);
            }

            preparedStatement.close();
            respEl.setAttribute("success", "1");
            if(arContractsId.size() != 0 )
                updateContractsCheck(this.con, arContractsId);
            if(arClosedContractdId.size() != 0 )
                updateContractsClose(this.con, arClosedContractdId);
        } catch (Exception var53) {
            var53.printStackTrace();
            respEl.setAttribute("success", "0");
            Element el = this.createElement(this.rootNode, "error");
            el.setAttribute("message", var53.getMessage());
            this.rootNode.appendChild(el);
        } finally {
            if(preparedStatement != null)
                preparedStatement.close();
        }

        this.rootNode.appendChild(respEl);
    }

    public void updateContractsCheck(Connection con, ArrayList<Integer> arCid) throws SQLException
    {
        String list = implode(",", arCid);
        String query = "update `crealink`.`crealink_update_history` set last_access_date = now() where cid in( " + list + " );";
        Statement st = con.createStatement();
        st.execute(query);
        st.close();
    }

    public void updateContractsClose(Connection con, ArrayList<Integer> arCid) throws SQLException
    {
        String list = implode(",", arCid);
        String query = "update `crealink`.`crealink_update_history` set closed = 1 where cid in( " + list + " );";
        Statement st = con.createStatement();
        st.execute(query);
        st.close();
    }

    public void addNewContractHistory(Connection con, Integer iCid) throws SQLException
    {
        String query = "insert into `crealink`.`crealink_update_history` ( cid ) values ( " + iCid + " );";
        Statement st = con.createStatement();
        st.execute(query);
        st.close();
    }

    public void delOldContractHistory(Connection con, Integer iCid) throws SQLException
    {
        String query = "delete from `crealink`.`crealink_update_history` where cid in ( " + iCid + " );";
        Statement st = con.createStatement();
        st.execute(query);
        st.close();
    }

    public void checkContracts(Connection con) throws SQLException
    {
        String query = "select c.id,ch.cid " +
                "from `bgbilling`.`contract` c " +
                "left join `crealink`.`crealink_update_history` ch on ch.cid = c.id " +
                "having cid is null;";
        PreparedStatement psContracts = con.prepareStatement(query);
        ResultSet rsContracts = psContracts.executeQuery();
        while(rsContracts.next()) {
            addNewContractHistory(con, rsContracts.getInt("id"));
        }
        psContracts.close();
        rsContracts.close();
        query = "select c.id,ch.cid " +
                "from `crealink`.`crealink_update_history` ch " +
                "left join `bgbilling`.`contract` c on ch.cid = c.id " +
                "having id is null";

        PreparedStatement psOldContracts = con.prepareStatement(query);
        ResultSet rsOldContracts = psOldContracts.executeQuery();
        while(rsOldContracts.next()) {
            delOldContractHistory(con, rsOldContracts.getInt("cid"));
        }
        psOldContracts.close();
        rsOldContracts.close();
    }

    public static String implode(String separator, ArrayList<Integer> data)
    {
        StringBuilder sb = new StringBuilder();
        Iterator<Integer> iter = data.iterator();
        while (iter.hasNext()) {
            Integer value = iter.next();
            sb.append(value.toString());
            if(iter.hasNext())
                sb.append(separator);
        }
        return sb.toString();
    }
}

---

Дело скорее всего в BalanceUtils, который создается в цикле:

Вы правы. Проблема решена после выноса из цикла BalanceUtils. Спасибо за помощь.