org.hibernate.exception.GenericJDBCException: could not extract ResultSet

Question

Hi Can you help me what's going on with this issue. I have this application connected in MySQL DB, every time am running this job to fetch data from the DB i always get this exception see full stack trace added java class, DAO and method and apache DBCP2 config.

POJO class

@Entity
public class ILCShiftEntries implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    @Column
    private int month;

    @Column
    private int year;

    @Column
    private int date;

    @Column
    private String groupId;

    @Column
    private String countryCode;


    @Column
    private String employeeSerialNumber;

    @Column
    private String employeeLastName;

    @Column
    private String employeeDeptNumber;

    @Column
    private String accountId;

    @Column
    private String workItemId;

    @Column
    private String workItemDesc;

    @Column
    private String activityCode;

    @Column
    private String activityDesc;

    @Column 
    private String billingCode;

    @Column
    private String weekEndingDate;

    @Column
    private String overtimeInd;

    @Column
    private String satHoursExpended;

    @Column
    private String sunHoursExpended;

    @Column
    private String monHoursExpended;

    @Column
    private String tuesdayHoursExpended;

    @Column
    private String wedHoursExpended;

    @Column
    private String thuHoursExpended;

    @Column
    private String friHoursExpended;


    @Column
    private String totalHoursExpended;

    @Column
    private String createdTimeStamp;

    @Column
    private String sumiterSerialNumber;

    @Column
    private String lbrRptInd;

    @Column
    private String employeeEffectiveDate;

    @Column
    private String labStatus;

    @Transient
    private String serialNumber;

    @Transient
    private String workDayManager;

    @Transient
    private String workDayManagerSerialNumber;

    @Column
    private String sourceApplicationId;

    @Column
    private String siteLocation;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getMonth() {
        return month;
    }

    public void setMonth(int month) {
        this.month = month;
    }

    public int getYear() {
        return year;
    }

    public void setYear(int year) {
        this.year = year;
    }

    public int getDate() {
        return date;
    }

    public void setDate(int date) {
        this.date = date;
    }

    public String getGroupId() {
        return groupId;
    }

    public void setGroupId(String groupId) {
        this.groupId = groupId;
    }

    public String getCountryCode() {
        return countryCode;
    }

    public void setCountryCode(String countryCode) {
        this.countryCode = countryCode;
    }

    public String getEmployeeSerialNumber() {
        return employeeSerialNumber;
    }

    public void setEmployeeSerialNumber(String employeeSerialNumber) {
        this.employeeSerialNumber = employeeSerialNumber;
    }

    public String getEmployeeLastName() {
        return employeeLastName;
    }

    public void setEmployeeLastName(String employeeLastName) {
        this.employeeLastName = employeeLastName;
    }

    public String getEmployeeDeptNumber() {
        return employeeDeptNumber;
    }

    public void setEmployeeDeptNumber(String employeeDeptNumber) {
        this.employeeDeptNumber = employeeDeptNumber;
    }

    public String getAccountId() {
        return accountId;
    }

    public void setAccountId(String accountId) {
        this.accountId = accountId;
    }

    public String getWorkItemId() {
        return workItemId;
    }

    public void setWorkItemId(String workItemId) {
        this.workItemId = workItemId;
    }

    public String getWorkItemDesc() {
        return workItemDesc;
    }

    public void setWorkItemDesc(String workItemDesc) {
        this.workItemDesc = workItemDesc;
    }

    public String getActivityCode() {
        return activityCode;
    }

    public void setActivityCode(String activityCode) {
        this.activityCode = activityCode;
    }

    public String getActivityDesc() {
        return activityDesc;
    }

    public void setActivityDesc(String activityDesc) {
        this.activityDesc = activityDesc;
    }

    public String getBillingCode() {
        return billingCode;
    }

    public void setBillingCode(String billingCode) {
        this.billingCode = billingCode;
    }

    public String getWeekEndingDate() {
        return weekEndingDate;
    }

    public void setWeekEndingDate(String weekEndingDate) {
        this.weekEndingDate = weekEndingDate;
    }

    public String getOvertimeInd() {
        return overtimeInd;
    }

    public void setOvertimeInd(String overtimeInd) {
        this.overtimeInd = overtimeInd;
    }

    public String getSatHoursExpended() {
        if( NumberUtil.toDouble( satHoursExpended ) > 0 ){
            return satHoursExpended;
        }
        return "0";
    }

    public void setSatHoursExpended(String satHoursExpended) {
        this.satHoursExpended = satHoursExpended;
    }

    public String getSunHoursExpended() {
        if( NumberUtil.toDouble( sunHoursExpended ) > 0 ){
            return sunHoursExpended;
        }
        return "0";
    }

    public void setSunHoursExpended(String sunHoursExpended) {
        this.sunHoursExpended = sunHoursExpended;
    }

    public String getMonHoursExpended() {
        if( NumberUtil.toDouble( monHoursExpended ) > 0 ){
            return monHoursExpended;
        }
        return "0";
    }

    public void setMonHoursExpended(String monHoursExpended) {
        this.monHoursExpended = monHoursExpended;
    }

    public String getTuesdayHoursExpended() {
        if( NumberUtil.toDouble( tuesdayHoursExpended ) > 0 ){
            return tuesdayHoursExpended;
        }
        return "0";
    }

    public void setTuesdayHoursExpended(String tuesdayHoursExpended) {
        this.tuesdayHoursExpended = tuesdayHoursExpended;
    }

    public String getWedHoursExpended() {
        if( NumberUtil.toDouble( wedHoursExpended ) > 0 ){
            return wedHoursExpended;
        }
        return "0";
    }

    public void setWedHoursExpended(String wedHoursExpended) {
        this.wedHoursExpended = wedHoursExpended;
    }

    public String getThuHoursExpended() {
        if( NumberUtil.toDouble( thuHoursExpended ) > 0 ){
            return thuHoursExpended;
        }
        return "0";
    }

    public void setThuHoursExpended(String thuHoursExpended) {
        this.thuHoursExpended = thuHoursExpended;
    }

    public String getFriHoursExpended() {
        if( NumberUtil.toDouble( friHoursExpended ) > 0 ){
            return friHoursExpended;
        }
        return "0";
    }

    public void setFriHoursExpended(String friHoursExpended) {
        this.friHoursExpended = friHoursExpended;
    }

    public String getTotalHoursExpended() {
        return totalHoursExpended;
    }

    public void setTotalHoursExpended(String totalHoursExpended) {
        this.totalHoursExpended = totalHoursExpended;
    }

    public String getCreatedTimeStamp() {
        return createdTimeStamp;
    }

    public void setCreatedTimeStamp(String createdTimeStamp) {
        this.createdTimeStamp = createdTimeStamp;
    }

    public String getSumiterSerialNumber() {
        return sumiterSerialNumber;
    }

    public void setSumiterSerialNumber(String sumiterSerialNumber) {
        this.sumiterSerialNumber = sumiterSerialNumber;
    }

    public String getLbrRptInd() {
        return lbrRptInd;
    }

    public void setLbrRptInd(String lbrRptInd) {
        this.lbrRptInd = lbrRptInd;
    }

    public String getEmployeeEffectiveDate() {
        return employeeEffectiveDate;
    }

    public void setEmployeeEffectiveDate(String employeeEffectiveDate) {
        this.employeeEffectiveDate = employeeEffectiveDate;
    }

    public String getLabStatus() {
        return labStatus;
    }

    public void setLabStatus(String labStatus) {
        this.labStatus = labStatus;
    }

    public String getSerialNumber() {
        return serialNumber;
    }

    public void setSerialNumber(String serialNumber) {
        this.serialNumber = serialNumber;
    }

    public String getWorkDayManager() {
        return workDayManager;
    }

    public void setWorkDayManager(String workDayManager) {
        this.workDayManager = workDayManager;
    }

    public String getWorkDayManagerSerialNumber() {
        return workDayManagerSerialNumber;
    }

    public void setWorkDayManagerSerialNumber(String workDayManagerSerialNumber) {
        this.workDayManagerSerialNumber = workDayManagerSerialNumber;
    }

    public String getSourceApplicationId() {
        return sourceApplicationId;
    }

    public void setSourceApplicationId(String sourceApplicationId) {
        this.sourceApplicationId = sourceApplicationId;
    }

    public String getSiteLocation() {
        return siteLocation;
    }

    public void setSiteLocation(String siteLocation) {
        this.siteLocation = siteLocation;
    }
}


Dao Class


public class ILCShiftEntriesDaoImpl implements ILCShiftEntriesDao{



    @Override
    public List< ILCShiftEntries > getListOfILCShiftEntriesWithLimitAndOffSet( StatelessSession statelessSession, int limitNumber, int offsetNumber ) {
        List<ILCShiftEntries > listOfILCShiftEntries = null;
        Query query = statelessSession.createQuery("from ILCShiftEntries order by id desc");
        query.setFirstResult( offsetNumber );
        query.setMaxResults( limitNumber );
        List list = query.getResultList();
        if( Utilities.isListNotEmpty( list ) ){
             listOfILCShiftEntries = ( List<ILCShiftEntries > )list;
        }
        return listOfILCShiftEntries;
    }

}

apache DBCP2 config


    database.dbcp.maxTotal=-1
    database.dbcp.maxIdle=8
    database.dbcp.minIdle=0
    database.dbcp.initialSize=20
    database.dbcp.maxWaitMillis=23904
    database.dbcp.testWhileIdle=true
    database.dbcp.maxOpenPreparedStatements=-1
    database.dbcp.validationQuery=SELECT 1
    database.dbcp.softMinEvictableIdleTimeMillis=300000
    database.dbcp.timeBetweenEvictionRunsMillis=300000

23:35:46.799 [DefaultQuartzScheduler_Worker-1] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Can not read response from server. Expected to read 183 bytes, read 75 bytes before connection was unexpectedly lost. javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423) at org.hibernate.query.Query.getResultList(Query.java:146) at daos.ILCShiftEntriesDaoImpl.getListOfILCShiftEntriesWithLimitAndOffSet(ILCShiftEntriesDaoImpl.java:275) at jobs.VarianceReportJobs.doILCWorkdayDBSync(VarianceReportJobs.java:400) at jobs.VarianceReportJobs.execute(VarianceReportJobs.java:205) at org.quartz.core.JobRunShell.run(JobRunShell.java:202) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573) Caused by: org.hibernate.exception.GenericJDBCException: could not extract ResultSet at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69) at org.hibernate.loader.Loader.getResultSet(Loader.java:2168) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1931) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893) at org.hibernate.loader.Loader.doQuery(Loader.java:938) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341) at org.hibernate.loader.Loader.doList(Loader.java:2692) at org.hibernate.loader.Loader.doList(Loader.java:2675) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507) at org.hibernate.loader.Loader.list(Loader.java:2502) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:392) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) at org.hibernate.internal.StatelessSessionImpl.list(StatelessSessionImpl.java:464) at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1445) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414) ... 6 more Caused by: java.sql.SQLException: Can not read response from server. Expected to read 183 bytes, read 75 bytes before connection was unexpectedly lost. at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974) at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1024) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) ... 21 more javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1423) at org.hibernate.query.Query.getResultList(Query.java:146) at daos.ILCShiftEntriesDaoImpl.getListOfILCShiftEntriesWithLimitAndOffSet(ILCShiftEntriesDaoImpl.java:275) at jobs.VarianceReportJobs.doILCWorkdayDBSync(VarianceReportJobs.java:400) at jobs.VarianceReportJobs.execute(VarianceReportJobs.java:205) at org.quartz.core.JobRunShell.run(JobRunShell.java:202) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573) Caused by: org.hibernate.exception.GenericJDBCException: could not extract ResultSet at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:69) at org.hibernate.loader.Loader.getResultSet(Loader.java:2168) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1931) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893) at org.hibernate.loader.Loader.doQuery(Loader.java:938) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341) at org.hibernate.loader.Loader.doList(Loader.java:2692) at org.hibernate.loader.Loader.doList(Loader.java:2675) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507) at org.hibernate.loader.Loader.list(Loader.java:2502) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:392) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) at org.hibernate.internal.StatelessSessionImpl.list(StatelessSessionImpl.java:464) at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1445) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414) ... 6 more Caused by: java.sql.SQLException: Can not read response from server. Expected to read 183 bytes, read 75 bytes before connection was unexpectedly lost. at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974) at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1024) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) ... 21 more

You need to show us the relevant code which generated this error. Just posting a stacktrace is usually not enough information. — Tim Biegeleisen
The Exception occurs in the MySQL Driver. Try to change the driver (update?) and disable the connection pool to isolate the source of the exception — drkunibar
Hi drkunibar i already using the latest my sql connecter, after removing the pooling connection i encounter the same thing — J1743
Hi Thank you for all your help. This issue is resolve now, its my wrong doing i forgot to remove this line -Djavax.net.debug=all in production server. :) — J1743

Chameera W. Ashan Chameera W. Ashan · Accepted Answer · 2019-06-02T18:44:25

I have got the same error with java.sql.SQLException: Too many tables; MySQL can only use 61 tables in a join

This occurs because of relating too many tables If you are not use fetch type It may occur because of relations

eg- In my case I have over 150 tables Book_Selling, Refund and etc. I create one table to connect all table regarding payment in that case If I get data from payment table it connect all other tables and as well as their related table so the count of this all tables exceeded 61 tables. In mysql default you can create only join query using 61 tables at a time

Use fetch type as lazy then it will solve your problem.

org.hibernate.exception.GenericJDBCException: could not extract ResultSet

1 Answers