Environment:
- Grails v5.3.2
- PostgreSQL 14.0
- OpenJDK 11.0.12
I use Grails 5.3.2 to develop a multi-tenancy web application. In this web application, I use SCHEMA as my multi-tenancy mode. According to my understand, multi-tenancy with mode SCHEMA means that each assigned schema will have the same tables. But I can not make it.
Below are the key points of relevant files.
application.yml
---
grails:
profile: vue
codegen:
defaultPackage: worker.order
gorm:
failOnError: true
reactor:
events: false
multiTenancy:
mode: SCHEMA
tenantResolverClass: org.grails.datastore.mapping.multitenancy.web.SessionTenantResolver
....
....
---
hibernate:
cache:
queries: false
use_second_level_cache: false
use_query_cache: false
dataSource:
pooled: true
jmxExport: true
driverClassName: org.h2.Driver
username: sa
password: ''
environments:
development:
logging:
config: classpath:logback-dev.xml
dataSource:
dbCreate: create-drop
schemaHandler: com.workerorder.utils.MySchemaHandler
driverClassName: org.postgresql.Driver
dialect: org.hibernate.dialect.PostgreSQLDialect
url: jdbc:postgresql://localhost/workerorder
username: 'foo'
password: 'foo'
MySchemaHandler.groovy
package com.workerorder.utils
import groovy.util.logging.Slf4j
import org.grails.datastore.gorm.jdbc.schema.SchemaHandler
import javax.sql.DataSource
import java.sql.Connection
import java.sql.ResultSet
import java.sql.SQLException
import java.sql.Statement
@Slf4j
class MySchemaHandler implements SchemaHandler {
private List<String> existedSchemaList = []
private static List<String> TARGET_SCHEMA_LIST = ['cx_david', 'cx_john']
@Override
Collection<String> resolveSchemaNames(DataSource dataSource) {
log.info("resolveSchemaNames(): ENTER .....")
return TARGET_SCHEMA_LIST
}
@Override
void useSchema(Connection connection, String name) {
String lowerCaseSchemaName = name.toLowerCase()
log.info("useSchema(): name: {} ==> {}", name, lowerCaseSchemaName)
ResultSet rs = connection.createStatement().executeQuery("""
SELECT EXISTS (
SELECT 1
FROM information_schema.schemata
WHERE schema_name = '${lowerCaseSchemaName}'
) as schema_exist;
""")
rs.next()
boolean schemaExist = rs.getBoolean("schema_exist")
log.info("useSchema(): schema {} exists? {}", lowerCaseSchemaName, schemaExist)
if (!schemaExist) throw new SQLException("schema [${lowerCaseSchemaName}] not exists")
connection.createStatement().execute("set search_path = ${lowerCaseSchemaName}")
}
@Override
void useDefaultSchema(Connection connection) {
log.info("useDefaultSchema(): ENTER ...........")
connection.createStatement().execute("set search_path = public")
}
@Override
void createSchema(Connection connection, String name) {
String lowerCaseSchemaName = name.toLowerCase()
log.info("createSchema(): name: {} =>{}", name, lowerCaseSchemaName)
Statement createSchemaStmt = connection.createStatement()
String sql = "create schema ${lowerCaseSchemaName} "
boolean rc = createSchemaStmt.execute(sql)
}
}
domain: TUser
package com.workerorder.domain
class TUser implements MultiTenant<TUser> {
String userid
static mapping = {
id name:'userid', generator:'assigned'
}
static constraints = {
userid maxSize: 20
}
}
TestController
package com.workerorder.controller
import com.workerorder.domain.TUser
import grails.gorm.multitenancy.Tenants
import grails.rest.*
import grails.converters.*
import org.grails.datastore.gorm.jdbc.schema.DefaultSchemaHandler
import org.grails.datastore.gorm.jdbc.schema.SchemaHandler
import org.grails.datastore.mapping.multitenancy.AllTenantsResolver
import org.grails.datastore.mapping.multitenancy.web.SessionTenantResolver
/**
* http://localhost:8080/test
*/
class TestController {
static responseFormats = ['json', 'xml']
def index() {
saveTUser('alexji', 'cx_mary')
render "TEST DONE"
}
private void saveTUser(String userid, String schemaName) {
log.info("save user {} to schema {}", userid, schemaName)
session[SessionTenantResolver.ATTRIBUTE] = schemaName
Tenants.withId(schemaName) {
TUser tUser = new TUser(userid: userid)
TUser.withTransaction {
tUser.save()
}
}
}
}
When this web starts, the logs are below:
|Running application...
2023-05-20 07:54:22.920 INFO --- [ restartedMain] Application : The following 1 profile is active: "development"
2023-05-20 07:54:26.488 INFO --- [ restartedMain] MySchemaHandler : resolveSchemaNames(): ENTER .....
2023-05-20 07:54:26.498 INFO --- [ restartedMain] MySchemaHandler : =========================================
2023-05-20 07:54:26.503 DEBUG --- [ restartedMain] MySchemaHandler : resolveSchemaNames(): schema: information_schema
2023-05-20 07:54:26.503 DEBUG --- [ restartedMain] MySchemaHandler : resolveSchemaNames(): schema: pg_catalog
2023-05-20 07:54:26.503 DEBUG --- [ restartedMain] MySchemaHandler : resolveSchemaNames(): schema: public
2023-05-20 07:54:26.504 INFO --- [ restartedMain] MySchemaHandler : resolveSchemaNames(): existedSchemaList: []
2023-05-20 07:54:26.510 INFO --- [ restartedMain] MySchemaHandler : useSchema(): name: cx_david ==> cx_david
2023-05-20 07:54:26.515 INFO --- [ restartedMain] MySchemaHandler : useSchema(): schema cx_david exists? false
2023-05-20 07:54:26.517 INFO --- [ restartedMain] MySchemaHandler : createSchema(): name: cx_david =>cx_david
2023-05-20 07:54:26.520 INFO --- [ restartedMain] MySchemaHandler : useDefaultSchema(): ENTER ...........
2023-05-20 07:54:26.532 INFO --- [ restartedMain] MySchemaHandler : useSchema(): name: cx_david ==> cx_david
2023-05-20 07:54:26.534 INFO --- [ restartedMain] MySchemaHandler : useSchema(): schema cx_david exists? true
2023-05-20 07:54:26.541 INFO --- [ restartedMain] MySchemaHandler : useDefaultSchema(): ENTER ...........
2023-05-20 07:54:26.552 INFO --- [ restartedMain] MySchemaHandler : useSchema(): name: cx_john ==> cx_john
2023-05-20 07:54:26.554 INFO --- [ restartedMain] MySchemaHandler : useSchema(): schema cx_john exists? false
2023-05-20 07:54:26.554 INFO --- [ restartedMain] MySchemaHandler : createSchema(): name: cx_john =>cx_john
2023-05-20 07:54:26.557 INFO --- [ restartedMain] MySchemaHandler : useDefaultSchema(): ENTER ...........
2023-05-20 07:54:26.567 INFO --- [ restartedMain] MySchemaHandler : useSchema(): name: cx_john ==> cx_john
2023-05-20 07:54:26.569 INFO --- [ restartedMain] MySchemaHandler : useSchema(): schema cx_john exists? true
2023-05-20 07:54:26.573 INFO --- [ restartedMain] MySchemaHandler : useDefaultSchema(): ENTER ...........
2023-05-20 07:54:28.514 INFO --- [ restartedMain] Application : Started Application in 6.282 seconds (JVM running for 7.276)
Grails application running at http://localhost:8080 in environment: development
At this moment, in PostgreSQL, I confirm the above two schemas were just created.
workerorder=> SELECT schema_name
FROM information_schema.schemata;
schema_name
--------------------
pg_catalog
public
information_schema
cx_david
cx_john
(5 rows)
workerorder=> \dt *.tuser;
List of relations
Schema | Name | Type | Owner
----------+-------+-------+---------
cx_david | tuser | table | appuser
cx_john | tuser | table | appuser
public | tuser | table | appuser
(3 rows)
Now I use TestController to simulate dynamically adding a schema cx_mary. When I execute http://localhost:8080/test, below exception happens:
2023-05-21 19:32:17.040 INFO --- [nio-8080-exec-2] TestController : save user alexji to schema cx_mary
2023-05-21 19:32:17.058 ERROR --- [nio-8080-exec-2] StackTrace : Full Stack Trace:
java.lang.reflect.InvocationTargetException: null
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.grails.core.DefaultGrailsControllerClass$ReflectionInvoker.invoke(DefaultGrailsControllerClass.java:211)
at org.grails.core.DefaultGrailsControllerClass.invoke(DefaultGrailsControllerClass.java:188)
at org.grails.web.mapping.mvc.UrlMappingsInfoHandlerAdapter.handle(UrlMappingsInfoHandlerAdapter.groovy:90)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1071)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:964)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:670)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:779)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.grails.web.servlet.mvc.GrailsWebRequestFilter.doFilterInternal(GrailsWebRequestFilter.java:77)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.grails.web.filters.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:67)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:91)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:177)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:891)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1784)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.grails.datastore.mapping.core.exceptions.ConfigurationException: DataSource not found for name [cx_mary] in configuration. Please check your multiple data sources configuration and try again.
at org.grails.orm.hibernate.HibernateDatastore.getDatastoreForConnection(HibernateDatastore.java:359)
at org.grails.orm.hibernate.HibernateDatastore.getDatastoreForConnection(HibernateDatastore.java:85)
at org.grails.orm.hibernate.AbstractHibernateDatastore.withNewSession(AbstractHibernateDatastore.java:366)
at grails.gorm.multitenancy.Tenants$_withId_closure2.doCall(Tenants.groovy:258)
at grails.gorm.multitenancy.Tenants$_withId_closure2.call(Tenants.groovy)
at grails.gorm.multitenancy.Tenants$CurrentTenant.withTenant(Tenants.groovy:358)
at grails.gorm.multitenancy.Tenants.withId(Tenants.groovy:236)
at grails.gorm.multitenancy.Tenants.withId(Tenants.groovy:169)
at grails.gorm.multitenancy.Tenants$withId.call(Unknown Source)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:47)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:148)
at com.workerorder.controller.TestController.saveTUser(TestController.groovy:25)
at com.workerorder.controller.TestController.index(TestController.groovy:19)
... 55 common frames omitted
My questions is:
- At the runtime of Grails, how to add a new schema and how to let Grails dynamically duplicate all tables (ex:
tuserin this case) in the new-created schema ?