search

sunitk / multitenancy-dynamic-tenant

Spring Boot 2 JPA Hibernate with database per tenant with dynamic configuration of tenants, secured by Spring Security
Apache License 2.0
99 stars 76 forks source link

Seeking help for schema not update #6

Closed zhtangsh closed 5 years ago

zhtangsh commented 5 years ago

Hi Sunitk,

Thank you for this greate post. I clone this project and make it worked for multi tenant case. However, it seems like Hibernate only auto update schema in the first tenant datasource and I'm not sure where I went wrong. Here is spring's log

2018-12-11 14:25:38.398  INFO 3444 --- [           main] .S.SpringDataMultiTenantMySqlApplication : Starting SpringDataMultiTenantMySqlApplication on xhdeMacBook-Air-5.local with PID 3444 (/Users/xh/dev/SpringDataMultiTenantMySQL/out/production/classes started by xh in /Users/xh/dev/SpringDataMultiTenantMySQL)
2018-12-11 14:25:38.406  INFO 3444 --- [           main] .S.SpringDataMultiTenantMySqlApplication : The following profiles are active: dev
2018-12-11 14:25:38.660  INFO 3444 --- [           main] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebApplicationContext@402bba4f: startup date [Tue Dec 11 14:25:38 CST 2018]; root of context hierarchy
2018-12-11 14:25:40.547  INFO 3444 --- [           main] f.a.AutowiredAnnotationBeanPostProcessor : JSR-330 'javax.inject.Inject' annotation found and supported for autowiring
2018-12-11 14:25:40.618  INFO 3444 --- [           main] trationDelegate$BeanPostProcessorChecker : Bean 'masterDatabaseConfig' of type [com.zhtangsh.SpringDataMultiTenantMySQL.master.config.MasterDatabaseConfig$$EnhancerBySpringCGLIB$$adef62c7] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2018-12-11 14:25:41.556  INFO 3444 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat initialized with port(s): 8800 (http)
2018-12-11 14:25:41.651  INFO 3444 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2018-12-11 14:25:41.652  INFO 3444 --- [           main] org.apache.catalina.core.StandardEngine  : Starting Servlet Engine: Apache Tomcat/8.5.35
2018-12-11 14:25:41.941  INFO 3444 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2018-12-11 14:25:41.941  INFO 3444 --- [ost-startStop-1] o.s.web.context.ContextLoader            : Root WebApplicationContext: initialization completed in 3288 ms
2018-12-11 14:25:42.223  INFO 3444 --- [ost-startStop-1] c.z.S.m.config.MasterDatabaseConfig      : Start setting up master EntityManagerFactory
2018-12-11 14:25:42.237  INFO 3444 --- [ost-startStop-1] c.z.S.m.config.MasterDatabaseConfig      : Start setting up master datasource
2018-12-11 14:25:42.251  INFO 3444 --- [ost-startStop-1] c.z.S.m.config.MasterDatabaseConfig      : Finish setting up master datasource
2018-12-11 14:25:42.381  INFO 3444 --- [ost-startStop-1] c.z.S.m.config.MasterDatabaseConfig      : Finish setting up master EntityManagerFactory
2018-12-11 14:25:42.426  INFO 3444 --- [ost-startStop-1] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'master-persistence-unit'
2018-12-11 14:25:42.464  INFO 3444 --- [ost-startStop-1] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
    name: master-persistence-unit
    ...]
2018-12-11 14:25:42.666  INFO 3444 --- [ost-startStop-1] org.hibernate.Version                    : HHH000412: Hibernate Core {5.0.12.Final}
2018-12-11 14:25:42.670  INFO 3444 --- [ost-startStop-1] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2018-12-11 14:25:42.673  INFO 3444 --- [ost-startStop-1] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
2018-12-11 14:25:42.750  INFO 3444 --- [ost-startStop-1] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
2018-12-11 14:25:42.968  WARN 3444 --- [ost-startStop-1] com.zaxxer.hikari.HikariConfig           : {} - idleTimeout has been set but has no effect because the pool is operating as a fixed size pool.
2018-12-11 14:25:42.968  INFO 3444 --- [ost-startStop-1] com.zaxxer.hikari.HikariDataSource       : master-connection-pool - Starting...
Tue Dec 11 14:25:43 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
2018-12-11 14:25:43.979  INFO 3444 --- [ost-startStop-1] com.zaxxer.hikari.HikariDataSource       : master-connection-pool - Start completed.
2018-12-11 14:25:44.000  INFO 3444 --- [ost-startStop-1] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
Tue Dec 11 14:25:44 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Dec 11 14:25:44 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Dec 11 14:25:44 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Dec 11 14:25:44 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
2018-12-11 14:25:44.891  INFO 3444 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaUpdate  : HHH000228: Running hbm2ddl schema update
2018-12-11 14:25:45.161  INFO 3444 --- [ost-startStop-1] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'master-persistence-unit'
2018-12-11 14:25:45.543  INFO 3444 --- [ost-startStop-1] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
2018-12-11 14:25:45.744  INFO 3444 --- [ost-startStop-1] c.z.S.tenant.config.TenantDtabaseConfig  : Start the configuration of tenantEntityManagerFactory
2018-12-11 14:25:45.762  INFO 3444 --- [ost-startStop-1] c.z.S.tenant.config.TenantDtabaseConfig  : End pf tenantEntityManagerFactory setup
2018-12-11 14:25:45.769  INFO 3444 --- [ost-startStop-1] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'tenantdb-persistence-unit'
2018-12-11 14:25:45.769  INFO 3444 --- [ost-startStop-1] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
    name: tenantdb-persistence-unit
    ...]
Hibernate: 
    select
        mastertena0_.id as id1_0_,
        mastertena0_.password as password2_0_,
        mastertena0_.tenant_id as tenant_i3_0_,
        mastertena0_.url as url4_0_,
        mastertena0_.username as username5_0_,
        mastertena0_.version as version6_0_ 
    from
        master_tenant mastertena0_
2018-12-11 14:25:46.037  INFO 3444 --- [ost-startStop-1] ceBasedMultiTenantConnectionProviderImpl : selectAnyDataSource() --  total tenants: 2
2018-12-11 14:25:46.040  INFO 3444 --- [ost-startStop-1] c.z.S.util.DataSourceUtil                : Configured datasource: {}
2018-12-11 14:25:46.041  INFO 3444 --- [ost-startStop-1] c.z.S.util.DataSourceUtil                : Configured datasource: {}
2018-12-11 14:25:46.041  INFO 3444 --- [ost-startStop-1] com.zaxxer.hikari.HikariDataSource       : cc-connection-pool - Starting...
Tue Dec 11 14:25:46 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
2018-12-11 14:25:46.142  INFO 3444 --- [ost-startStop-1] com.zaxxer.hikari.HikariDataSource       : cc-connection-pool - Start completed.
2018-12-11 14:25:46.142  INFO 3444 --- [ost-startStop-1] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
2018-12-11 14:25:46.218  INFO 3444 --- [ost-startStop-1] org.hibernate.tool.hbm2ddl.SchemaUpdate  : HHH000228: Running hbm2ddl schema update
Tue Dec 11 14:25:46 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Dec 11 14:25:46 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
2018-12-11 14:25:46.296  INFO 3444 --- [ost-startStop-1] rmationExtractorJdbcDatabaseMetaDataImpl : HHH000262: Table not found: Company
2018-12-11 14:25:46.306  INFO 3444 --- [ost-startStop-1] rmationExtractorJdbcDatabaseMetaDataImpl : HHH000262: Table not found: Company
Tue Dec 11 14:25:46 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Dec 11 14:25:46 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Dec 11 14:25:46 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Tue Dec 11 14:25:46 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
2018-12-11 14:25:47.501  INFO 3444 --- [ost-startStop-1] rmationExtractorJdbcDatabaseMetaDataImpl : HHH000262: Table not found: Currency
2018-12-11 14:25:47.507  INFO 3444 --- [ost-startStop-1] rmationExtractorJdbcDatabaseMetaDataImpl : HHH000262: Table not found: Currency
2018-12-11 14:25:48.367  INFO 3444 --- [ost-startStop-1] rmationExtractorJdbcDatabaseMetaDataImpl : HHH000262: Table not found: User
2018-12-11 14:25:48.373  INFO 3444 --- [ost-startStop-1] rmationExtractorJdbcDatabaseMetaDataImpl : HHH000262: Table not found: User
2018-12-11 14:25:49.213  INFO 3444 --- [ost-startStop-1] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'tenantdb-persistence-unit'
2018-12-11 14:25:49.273  INFO 3444 --- [ost-startStop-1] c.z.S.config.JerseyConfig                : Register JAX-RS component -> com.zhtangsh.SpringDataMultiTenantMySQL.SipleEndpoint
2018-12-11 14:25:49.298  INFO 3444 --- [ost-startStop-1] c.z.S.config.JerseyConfig                : Register JAX-RS component -> com.zhtangsh.SpringDataMultiTenantMySQL.config.NameFilter
2018-12-11 14:25:49.801  INFO 3444 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'characterEncodingFilter' to: [/*]
2018-12-11 14:25:49.802  INFO 3444 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean   : Mapping filter: 'requestContextFilter' to: [/*]
2018-12-11 14:25:49.803  INFO 3444 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean  : Mapping servlet: 'com.zhtangsh.SpringDataMultiTenantMySQL.config.JerseyConfig' to [/api/v1/*]
2018-12-11 14:25:50.513  INFO 3444 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2018-12-11 14:25:50.514  INFO 3444 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Bean with name 'masterDataSource' has been autodetected for JMX exposure
2018-12-11 14:25:50.521  INFO 3444 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Located MBean 'masterDataSource': registering with JMX server as MBean [com.zaxxer.hikari:name=masterDataSource,type=HikariDataSource]
2018-12-11 14:25:50.614  INFO 3444 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8800 (http)
2018-12-11 14:25:50.623  INFO 3444 --- [           main] .S.SpringDataMultiTenantMySqlApplication : Started SpringDataMultiTenantMySqlApplication in 13.463 seconds (JVM running for 14.398)
2018-12-11 14:26:54.394  INFO 3444 --- [nio-8800-exec-2] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
Hibernate: 
    select
        currency0_.id as id1_1_,
        currency0_.extras as extras2_1_,
        currency0_.name as name3_1_ 
    from
        Currency currency0_
2018-12-11 14:26:54.398  INFO 3444 --- [nio-8800-exec-2] com.zaxxer.hikari.HikariDataSource       : dd-connection-pool - Starting...
Tue Dec 11 14:26:54 CST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
2018-12-11 14:26:54.483  INFO 3444 --- [nio-8800-exec-2] com.zaxxer.hikari.HikariDataSource       : dd-connection-pool - Start completed.
2018-12-11 14:26:54.510  WARN 3444 --- [nio-8800-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1146, SQLState: 42S02
2018-12-11 14:26:54.511 ERROR 3444 --- [nio-8800-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : Table 'dd.Currency' doesn't exist
2018-12-11 14:26:54.545 ERROR 3444 --- [nio-8800-exec-2] o.a.c.c.C.[.[.[.[.z.S.c.JerseyConfig]    : Servlet.service() for servlet [com.zhtangsh.SpringDataMultiTenantMySQL.config.JerseyConfig] in context with path [] threw exception [org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'dd.Currency' doesn't exist

In master database, I setup two tenants as follows.

mysql> select * from master_tenant;
+----+----------+-----------+-------------------------------------+----------+---------+
| id | password | tenant_id | url                                 | username | version |
+----+----------+-----------+-------------------------------------+----------+---------+
|  1 | root     | cc        | jdbc:mysql://10.19.140.200:3306/cc | root     |       0 |
|  2 | root     | dd        | jdbc:mysql://10.19.140.200:3306/dd | root     |       0 |
+----+----------+-----------+-------------------------------------+----------+---------+
2 rows in set (0.00 sec)

And all entities are initialized/update successfully in tenant cc, but failed in tenant dd.

mysql> use cc;
Database changed
mysql> show tables;
+--------------+
| Tables_in_cc |
+--------------+
| Company      |
| Currency     |
| User         |
+--------------+
3 rows in set (0.00 sec)
mysql> use dd;
Database changed
mysql> show tables;
Empty set (0.00 sec)
zhtangsh commented 5 years ago

well, I saw this comment in original post, I'll close this one. Thanks.

Please create the required databases in the MySQL server you are using. This exception hints that there is no database created. You need to have databases created first.

Please read the first part of the tutorial here https://sunitkatkar.blogspot.com/2018/04/building-saas-style-multi-tenant-web.html and then read the second part here https://sunitkatkar.blogspot.com/2018/04/building-saas-style-multi-tenant-web2.html. In this second part, there is sample SQL to create required tables, etc.

These two explain the concepts and how database needs to be set up. After you have read and understood these, then read and follow https://sunitkatkar.blogspot.com/2018/05/adding-tenants-without-application.html

Also, use the code from the GitHub repo (https://github.com/sunitk/multitenancy) as it has a couple of bugs fixed and checked in.

In my project, I do not depend on Hibernate to create the schema on adding a tenant. The steps we follow is to first create the tenant schema and then make an entry in the master table. This is because the tenant database could be in a different data-center (in financial applications, some countries have regulations where database has to be physically present in the country.) 

I know that the tables can get created automatically, but I have not spent time researching the way to do it for the reasons stated above.

However, there is a JDBC URL query parameter createDatabaseIfNotExistfor MySQL which will create the database and schema. Please look at this link. I have tried it in development and it did work, but decided against using it for reasons as stated above.