DEVHIDE
Login Or Sign up

Different results for the same query

50 Views Asked by At

my name is Enrico Bonacina and I have very big problem after I moved my old application to a new Ubuntu Server. The problem is that I have different results from the same query called more and more times.

The application language is Java 1.7, is a web application using GWT 2.5 and Tomcat 7.0 On the server side I use Hibernate 4.3.11 with C3p0 0.9.2.1 to dialog with Mysql 5.5.7. On the server is running Java Jdk 11.0.19

My Persistence.xml is:

#
# #==============================================================================================
# Configurazione dei database SERVER e LOCAL
# ----------------------------------------------------------------------------------------------
# Aggiornamenti:
#   05/01/2022 - E. Bonacina - prima release
#
# ...Definizioni per Hibernate/JPA
#
org.hibernate.envers.audit_table_suffix=_aud
hibernate.connection.pool_size=1
hibernate.jdbc.batch_size = 1
hibernate.default_batch_fetch_size = 1
hibernate.show_sql = false
hibernate.bytecode.use_reflection_optimizer = false
hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect
hibernate.connection.driver_class = com.mysql.jdbc.Driver
hibernate.connection.autocommit=false
connection_provider_class = org.hibernate.connection.C3P0ConnectionProvider
hibernate.c3p0.min_size = 5
hibernate.c3p0.max_size = 20
hibernate.c3p0.timeout = 300
hibernate.c3p0.max_statements = 50
hibernate.c3p0.idle_test_period = 3000
hibernate.c3p0.preferredTestQuery = SELECT 1
hibernate.c3p0.numHelperThread = 10
hibernate.c3p0.maxIdleTime = 10
hibernate.c3p0.contextClassLoaderSource = library
#
# SERVER - (Fie_new su db locale)
#
hibernate.hbm2ddl.auto = update
hibernate.connection.username = 'uername'
hibernate.connection.password = 'password'
hibernate.connection.url = jdbc:mysql://127.0.0.1:3308/'dbname

When I call more and more times the follow method:

public java.util.List<it.eb.fie.persistence.model.Anagrafica> listAll(Set<String> ruoli, String username, Long associazioneId, Integer stagione) {
    
    EntityManager em = getSession();
    em.getEntityManagerFactory().getCache().evictAll();
    if(em.isJoinedToTransaction()) {
        SystemUtils.info("AnagraficaDAO.listAll - eseguito flush");
        em.flush();
    }
    List<Anagrafica> lAnagrafica = new ArrayList<Anagrafica>();
    if (ruoli.contains("anagrafica_visualizza_associazione") || ruoli.contains("anagrafica_visualizza_tutto")) {
        //
        // ...Query 
        //
        String queryString = "from it.eb.fie.persistence.model.AnagraficaAssociazioneSport p where 1=1 and p.evento.id IS NULL";
        queryString = queryString + " AND " + "p.associazioneSport.associazione.id = :associazioneId";
        queryString = queryString + " AND " + "p.stagione = :stagione";
        queryString = queryString + " AND " + "p.dzFigure.id = 4";
        queryString = queryString + " ORDER BY p.anagrafica.cognome, p.anagrafica.nome";
        javax.persistence.Query query = em.createQuery(queryString);
        query.setParameter("stagione", stagione);
        query.setParameter("associazioneId", associazioneId);
        
        List<it.eb.fie.persistence.model.AnagraficaAssociazioneSport> lAas = query.getResultList();
        SystemUtils.info("AnagraficaDAO.listAll - trovati: " + lAas.size() + " record");
        //
        // ...Test with native query
        //
        queryString = "select SQL_NO_CACHE aas.id from anagraficaassociazionesport aas";
        queryString = queryString + " " + "inner join associazionesport asp on asp.id = aas.associazionesport_id";
        queryString = queryString + " " + "inner join associazione ass on ass.id = asp.associazione_id";
        queryString = queryString + " " + "inner join anagrafica a on a.id = aas.anagrafica_id";
        queryString = queryString + " " + "WHERE evento_id IS NULL";
        queryString = queryString + " " + "AND ass.id = :associazioneId";
        queryString = queryString + " " + "AND aas.stagione = :stagione";
        queryString = queryString + " " + "AND aas.dzFigure_id = 4";
        query = em.createNativeQuery(queryString);
        query.setParameter("stagione", stagione);
        query.setParameter("associazioneId", associazioneId);
        SystemUtils.info("ChiamataNativa  - trovati: " + query.getResultList().size() + " record");
        //
        // ...Carica le anagrafiche
        //
        if(lAas != null && lAas.size() > 0) {
            for(AnagraficaAssociazioneSport aas : lAas) {
                Anagrafica a = aas.getAnagrafica();
                a.setNumeroTessera(aas.getNumeroTessera());
                a.setDataTesseramento(aas.getDataTesseramento());
                a.setTessera(aas.getTessera());
                lAnagrafica.add(a);
            }
        }
    } else {
        String queryString = "from it.eb.fie.persistence.model.Anagrafica p where p.username = :username";
        javax.persistence.Query query = em.createQuery(queryString);
        query.setParameter("username", username);
        query.setHint("javax.persistence.cache.retriveMode", "BYPASS");
        lAnagrafica = query.getResultList();
    }
    em.close();
    return lAnagrafica

The result written on the log file is:

http-apr-8080-exec-7-05/06/23 17:27:07.398 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 56 record
http-apr-8080-exec-7-05/06/23 17:27:07.402 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 56 record
http-apr-8080-exec-7-05/06/23 17:27:07.404 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 56 record
http-apr-8080-exec-1-05/06/23 17:27:14.778 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 57 record
http-apr-8080-exec-1-05/06/23 17:27:14.782 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 57 record
http-apr-8080-exec-1-05/06/23 17:27:14.784 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 57 record
http-apr-8080-exec-2-05/06/23 17:30:53.156 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 56 record
http-apr-8080-exec-2-05/06/23 17:30:53.160 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 56 record
http-apr-8080-exec-2-05/06/23 17:30:53.161 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 56 record
http-apr-8080-exec-4-05/06/23 17:30:56.830 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 56 record
http-apr-8080-exec-4-05/06/23 17:30:56.833 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 56 record
http-apr-8080-exec-4-05/06/23 17:30:56.834 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 56 record
http-apr-8080-exec-8-05/06/23 17:31:00.727 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 57 record
http-apr-8080-exec-8-05/06/23 17:31:00.730 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 57 record
http-apr-8080-exec-8-05/06/23 17:31:00.731 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 57 record
http-apr-8080-exec-3-05/06/23 17:31:02.127 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 56 record
http-apr-8080-exec-3-05/06/23 17:31:02.131 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 56 record
http-apr-8080-exec-3-05/06/23 17:31:02.132 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 56 record
http-apr-8080-exec-9-05/06/23 17:31:03.736 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 56 record
http-apr-8080-exec-9-05/06/23 17:31:03.740 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 56 record
http-apr-8080-exec-9-05/06/23 17:31:03.741 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 56 record
http-apr-8080-exec-6-05/06/23 17:31:05.868 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 56 record
http-apr-8080-exec-6-05/06/23 17:31:05.871 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 56 record
http-apr-8080-exec-6-05/06/23 17:31:05.872 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 56 record
http-apr-8080-exec-5-05/06/23 17:31:07.338 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 56 record
http-apr-8080-exec-5-05/06/23 17:31:07.342 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 56 record
http-apr-8080-exec-5-05/06/23 17:31:07.343 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 56 record
http-apr-8080-exec-10-05/06/23 17:31:08.658 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 57 record
http-apr-8080-exec-10-05/06/23 17:31:08.661 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 57 record
http-apr-8080-exec-10-05/06/23 17:31:08.662 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 57 record
http-apr-8080-exec-7-05/06/23 17:31:10.722 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 57 record
http-apr-8080-exec-7-05/06/23 17:31:10.724 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 57 record
http-apr-8080-exec-7-05/06/23 17:31:10.725 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 57 record
http-apr-8080-exec-1-05/06/23 17:31:11.989 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 57 record
http-apr-8080-exec-1-05/06/23 17:31:11.992 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 57 record
http-apr-8080-exec-1-05/06/23 17:31:11.993 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 57 record
http-apr-8080-exec-2-05/06/23 17:31:13.229 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 57 record
http-apr-8080-exec-2-05/06/23 17:31:13.232 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 57 record
http-apr-8080-exec-2-05/06/23 17:31:13.233 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 57 record
http-apr-8080-exec-4-05/06/23 17:31:14.510 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 57 record
http-apr-8080-exec-4-05/06/23 17:31:14.513 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 57 record
http-apr-8080-exec-4-05/06/23 17:31:14.514 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 57 record
http-apr-8080-exec-8-05/06/23 17:31:16.132 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN AnagraficaDAO.listAll - trovati: 57 record
http-apr-8080-exec-8-05/06/23 17:31:16.135 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN ChiamataNativa  - trovati: 57 record
http-apr-8080-exec-8-05/06/23 17:31:16.135 INFO  SystemUtils             :100   - 2589-AD20 SCSEDRIN getListaAnagrafica - trovati 57 record

Any idea? Sorry for my bad English!

sql mysql hibernate c3p0
Original Q&A
0

There are 0 best solutions below

Related Questions in SQL

Related Questions in MYSQL

Related Questions in HIBERNATE

Related Questions in C3P0

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs

Popular Questions

.

Copyright © 2021 Jogjafile Inc.