JPA-hib not saving to database, but reading db (mysql, Hikari, tomcat 9, spring boot 2.1.8)

Question

I have the latest versions of mysql, Hikari, spring boot 2 in a multi tenant app, using datasource set programmatically.

I am able to read table entries in the (mysql) databases, but not able to update or insert or modify.

Basically the following, 'believe is leading to queuing the save requests

===========

j.i.AbstractLogicalConnectionImplementor : Preparing to begin transaction via JDBC Connection.setAutoCommit(false)
2019-09-25 10:05:20.758 TRACE 2643 --- [nio-8080-exec-1] j.i.AbstractLogicalConnectionImplementor : Transaction begun via JDBC Connection.setAutoCommit(false)

=========

resulting in queueing the insert

======== EntityIdentityInsertAction for [com.xx.xx.xx.user] object

2019-09-25 10:05:20.761 TRACE 2643 --- [nio-8080-exec-1] org.hibernate.engine.spi.ActionQueue     : Adding insert with no non-nullable, transient entities: [EntityIdentityInsertAction[com.xx.xx.user#<delayed:2>]]

========

How to set the underlying JDBC connection to auto commit?

What am I missing? Thanks in advance

Have tried:

1. repo.saveAndFlush() and repo.flush() -- both give me exception that there is no transaction in progress

2. have serialized the object/table-entity

3. have set ds.setAutoCommit(true); ds.setReadOnly(false); // ds is the datasource

4. In application.properties have set spring.datasource.hikari.auto-commit =true

=====Hikari pool config dump shows auto commit is true ===

2019-09-25 10:05:20.795 DEBUG 2643 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariConfig           : allowPoolSuspension.............false
2019-09-25 10:05:20.795 DEBUG 2643 --- [nio-8080-exec-1] com.zaxxer.hikari.HikariConfig           : autoCommit......................true

========end of Hikari config dump ====

But, the JDBC trace shows auotcommit to false while calling repo.save(user)

019-09-25 10:05:20.758 DEBUG 2643 --- [nio-8080-exec-1] o.h.e.t.internal.TransactionImpl         : begin
2019-09-25 10:05:20.758 TRACE 2643 --- [nio-8080-exec-1] j.i.AbstractLogicalConnectionImplementor : Preparing to begin transaction via JDBC Connection.setAutoCommit(false)
2019-09-25 10:05:20.758 TRACE 2643 --- [nio-8080-exec-1] j.i.AbstractLogicalConnectionImplementor : Transaction begun via JDBC Connection.setAutoCommit(false)
2019-09-25 10:05:20.759 TRACE 2643 --- [nio-8080-exec-1] cResourceLocalTransactionCoordinatorImpl : ResourceLocalTransactionCoordinatorImpl#afterBeginCallback

2019-09-25 10:05:20.759 TRACE 2643 --- [nio-8080-exec-1] .i.SessionFactoryImpl$SessionBuilderImpl : Opening Hibernate Session.  tenant=1, owner=null
2019-09-25 10:05:20.759 TRACE 2643 --- [nio-8080-exec-1] org.hibernate.internal.SessionImpl       : Opened Session [c0c25dff-2c0a-4996-b352-1ac4d49e48e8] at timestamp: 1569423920759
2019-09-25 10:05:20.760 TRACE 2643 --- [nio-8080-exec-1] o.hibernate.engine.spi.IdentifierValue   : ID unsaved-value: 0
2019-09-25 10:05:20.760 TRACE 2643 --- [nio-8080-exec-1] o.h.e.i.AbstractSaveEventListener        : Transient instance of: com.xx.xx.xx.user
2019-09-25 10:05:20.760 TRACE 2643 --- [nio-8080-exec-1] o.h.e.i.DefaultPersistEventListener      : Saving transient instance
2019-09-25 10:05:20.760 TRACE 2643 --- [nio-8080-exec-1] o.h.e.i.AbstractSaveEventListener        : Saving [com.xx.xx.xx.user#<null>]
2019-09-25 10:05:20.760 TRACE 2643 --- [nio-8080-exec-1] org.hibernate.engine.spi.ActionQueue     : Adding an EntityIdentityInsertAction for [com.xx.xx.xx.user] object
2019-09-25 10:05:20.761 TRACE 2643 --- [nio-8080-exec-1] org.hibernate.engine.spi.ActionQueue     : Adding insert with no non-nullable, transient entities: [EntityIdentityInsertAction[com.xx.xx.user#<delayed:2>]]
2019-09-25 10:05:20.761 TRACE 2643 --- [nio-8080-exec-1] org.hibernate.engine.spi.ActionQueue     : Adding resolved non-early insert action
2019-09-25 10:05:20.761 TRACE 2643 --- [nio-8080-exec-1] org.hibernate.internal.SessionImpl       : Closing session [c0c25dff-2c0a-4996-b352-1ac4d49e48e8]
2019-09-25 10:05:20.761 TRACE 2643 --- [nio-8080-exec-1] o.h.e.jdbc.internal.JdbcCoordinatorImpl  : Closing JDBC container [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl@3cba167b]
2019-09-25 10:05:20.761 TRACE 2643 --- [nio-8080-exec-1] o.h.r.j.i.ResourceRegistryStandardImpl   : Releasing JDBC resources
2019-09-25 10:05:20.762 TRACE 2643 --- [nio-8080-exec-1] o.h.r.j.i.LogicalConnectionManagedImpl   : Closing logical connection
2019-09-25 10:05:20.762 TRACE 2643 --- [nio-8080-exec-1] o.h.r.j.i.LogicalConnectionManagedImpl   : Logical connection closed
2019-09-25 10:05:20.762 DEBUG 2643 --- [nio-8080-exec-1] o.h.e.t.internal.TransactionImpl         : committing
2019-09-25 10:05:20.762 TRACE 2643 --- [nio-8080-exec-1] cResourceLocalTransactionCoordinatorImpl : ResourceLocalTransactionCoordinatorImpl#beforeCompletionCallback
2019-09-25 10:05:20.762 TRACE 2643 --- [nio-8080-exec-1] org.hibernate.internal.SessionImpl       : SessionImpl#beforeTransactionCompletion()
2019-09-25 10:05:20.762 TRACE 2643 --- [nio-8080-exec-1] org.hibernate.internal.SessionImpl       : Automatically flushing session
2019-09-25 10:05:20.762 TRACE 2643 --- [nio-8080-exec-1] .t.i.SynchronizationRegistryStandardImpl : SynchronizationRegistryStandardImpl.notifySynchronizationsBeforeTransactionCompletion
2019-09-25 10:05:20.762 TRACE 2643 --- [nio-8080-exec-1] j.i.AbstractLogicalConnectionImplementor : Preparing to commit transaction via JDBC Connection.commit()
2019-09-25 10:05:20.762 TRACE 2643 --- [nio-8080-exec-1] j.i.AbstractLogicalConnectionImplementor : Transaction committed via JDBC Connection.commit()
2019-09-25 10:05:20.763 TRACE 2643 --- [nio-8080-exec-1] j.i.AbstractLogicalConnectionImplementor : re-enabling auto-commit on JDBC Connection after completion of JDBC-based transaction
2019-09-25 10:05:20.763 TRACE 2643 --- [nio-8080-exec-1] j.i.AbstractLogicalConnectionImplementor : LogicalConnection#afterTransaction
2019-09-25 10:05:20.763 TRACE 2643 --- [nio-8080-exec-1] o.h.r.j.i.ResourceRegistryStandardImpl   : Releasing JDBC resources

========================= Repo is

@Repository public interface UserRepository extends JpaRepository {}

======== Entity is

@Persistent
@Entity
@Table(name = "user")
public class User implements Serializable {

   // private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private long id;    
    @Column(name = "username")
     private String username;

   ===Rest of fields with setters and getters==


@Override
public String toString() {
    return "User [id=" + this.id + ", username=" + this.username +", email="+ this.email+ " 
            + "]";
}

}

service class is

@Service
@Persistent
@Transactional(transactionManager="tenantTransactionManager")     
public class UserService {

    @Autowired
    private UserRepository repo;

    public List<User> listAll() {

    return repo.findAll();
    }




public void save(User user) { 

                try {

                    repo.save(user);

            }catch(Exception e) {
                e.printStackTrace();
            }

        }

        public User get(long id) { return repo.findById(id).get();}



public void delete(long id) { repo.deleteById(id); } 

    }

=======tenantManager===

@Bean(name = "tenantEntityManagerFactory") @ConditionalOnBean(name = "datasourceBasedMultitenantConnectionProvider")

public LocalContainerEntityManagerFactoryBean entityManagerFactory(
       ) {
    //cleaned up on code not directly related

    LocalContainerEntityManagerFactoryBean emfBean = new LocalContainerEntityManagerFactoryBean(); 


    emfBean.setPackagesToScan(
            new String[] { User.class.getPackage().getName(),
                    UserRepository.class.getPackage().getName(),
                    UserService.class.getPackage().getName() });
    emfBean.setJpaVendorAdapter(jpaVendorAdapter());
    Map<String, Object> properties = new HashMap<>();

    properties.put(org.hibernate.cfg.Environment.MULTI_TENANT,
            MultiTenancyStrategy.SCHEMA);

    properties.put(
            org.hibernate.cfg.Environment.MULTI_TENANT_CONNECTION_PROVIDER,  connectionProvider);

    properties.put(
            org.hibernate.cfg.Environment.MULTI_TENANT_IDENTIFIER_RESOLVER,
            tenantResolver);



    properties.put(org.hibernate.cfg.Environment.DIALECT,
            "org.hibernate.dialect.MySQL5Dialect");      

    properties.put(org.hibernate.cfg.Environment.SHOW_SQL, true);
    properties.put(org.hibernate.cfg.Environment.FORMAT_SQL, true);

    properties.put("hibernate.id.new_generator_mappings", "false");
    properties.put("spring.jpa.hibernate.ddl-auto", "none");
    properties.put("spring.jpa.hibernate.autocommit", "true");
    properties.put("spring.datasource.hikari.auto-commit", "true");
    properties.put("hibernate.connection.autocommit", "true"); 

    emfBean.setJpaPropertyMap(properties);   

    return emfBean;
}

} =====and datasource provider===

public static DataSource createAndConfigureDataSource( {

    //=======================
    HikariDataSource ds = new HikariDataSource();
    ds.setUsername(xxx);
    ds.setPassword(xxx);
    ds.setJdbcUrl(xxxx);
    ds.setDriverClassName("com.mysql.cj.jdbc.Driver");   

    ds.setConnectionTimeout(20000);

    // Minimum number of idle connections in the pool
    ds.setMinimumIdle(10);
    ds.setMaximumPoolSize(20);
    ds.setIdleTimeout(300000);
    ds.setConnectionTimeout(20000);
    ds.setAutoCommit(true);
    ds.setReadOnly(false);

    return ds;

} }

application.properties ====

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
logging.level.org.springframework=WARN
logging.level.com=TRACE
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.type=TRACE
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=TRACE
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
logging.level.org.springframework.jdbc.core.JdbcTemplate=TRACE
    logging.level.org.springframework.jdbc.core.StatementCreatorUtils=TRACE

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
logging.level.org.hibernate=TRACE
logging.level.org.hibernate.type=TRACE
spring.jpa.properties.hibernate.id.new_generator_mappings=false
spring.jpa.hibernate.ddl-auto=none
spring.datasource.hikari.auto-commit =true
spring.jpa.hibernate.connection.autoCommit=true

=====

@Bean(name = "tenantTransactionManager")
    public JpaTransactionManager transactionManager(
            EntityManagerFactory tenantEntityManager) {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(tenantEntityManager);
        return transactionManager;
    }

Answer 1

Finally, thanks to all. Struggled for a week on this. Minor Qualifier on the @Bean(name = "tenantTransactionManager") with @Qualifier("tenantEntityManagerFactory") on emf did the trick.

Yes, stopped fiddling with autoCommit and removed all additional transactional as suggested. Much appreciated for your pointers. Thank you.