trnchawla
commented
3 years ago Closed trnchawla closed 3 years ago
trnchawla
commented
3 years ago trnchawla
commented
3 years ago Am using expose version: 0.29.1 pgjdbc-ng:0.8.6
trnchawla
commented
3 years ago Am opening database connection like this:
fun getDBConn(): Database? {
if (DBConnection == null) {
synchronized(this) {
if (DBConnection == null) {
try {
DBConnection = DataBaseConnection.connectWithDb(
Config.DB_URL,
Config.DB_DRIVER,
Config.DB_USERNAME,
Config.DB_PASSWORD
)
} catch (e: Exception) {
println("error while connecting to db $e")
return null
}
}
}
}
return DBConnection
}
private fun connectWithDb(dbUrl: String, dbDriver: String, userName: String = "", password: String = ""): Database {
return Database.connect(dbUrl, dbDriver, userName, password)
}
Tapac
commented
3 years ago Can you share the more complex example as provided errors looks unrelated to a connection part but a prroblem maybe on how you use the transactions.
trnchawla
commented
3 years ago Will share in a while.
Regards, Tarun
On 14-Feb-2021, at 21:58, Andrey.Tarashevskiy notifications@github.com wrote:
Can you share the more complex example as provided errors looks unrelated to a connection part but a prroblem maybe on how you use the transactions.
— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe.
trnchawla
commented
3 years ago fun createCompany(ownerEmail: String, companyMapper: CompanyMapper): Triple<Boolean, UUID?, String?> {
var created = false
var uuid: UUID? = null
var errorRet: String? = null
try {
transaction {
if (companyMapper.address == null) {
errorRet = "Empty addressed passed while creating company"
return@transaction
}
val addressUUID = createAddress(companyMapper.address!!)
if (addressUUID == null) {
errorRet = "Not able to create address"
rollback()
return@transaction
}
val companyUUID = Company.insert {
it[name] = companyMapper.name
it[type] = companyMapper.type
it[subtype] = companyMapper.subtype
it[officeAddress] = addressUUID!!
} get Company.uuid
if (companyUUID == null) {
errorRet = "Not able to create company"
rollback()
return@transaction
}
val companyOwnerUUIDList = CompanyOwner.select {
CompanyOwner.email eq ownerEmail
}
if (companyOwnerUUIDList.count() == 0L) {
errorRet = "No owner with email id $ownerEmail"
rollback()
return@transaction
}
val companyOwnerUUID = companyOwnerUUIDList.first()[CompanyOwner.uuid]
val companyOwnerCompanyEntity = CompanyOwnerCompany.insert {
it[owner] = companyOwnerUUID
it[company] = companyUUID
}
if (companyOwnerCompanyEntity == null) {
errorRet = "Not able to relate owner with company"
rollback()
return@transaction
}
created = true
uuid = companyUUID
}
} catch (e: Exception) {
errorRet = "$e"
}
return Triple(created, uuid, errorRet)
}
fun getAllCompanies(ownerEmail: String): ArrayList<CompanyMapper>? {
val companiesArray = arrayListOf<CompanyMapper>()
val companyOwner = getCompanyOwnerFromEmail(ownerEmail)
if (companyOwner?.uuid == null) {
return null
}
try {
transaction {
val companiesOwned = CompanyOwnerCompany.select {
CompanyOwnerCompany.owner eq companyOwner.uuid
}
if (companiesOwned.count() == 0L) {
return@transaction
}
for (companyOwned in companiesOwned) {
val companyMapper = getCompanyDetails(companyOwned[CompanyOwnerCompany.company])
if (companyMapper != null) {
companiesArray.add(companyMapper)
}
}
}
} catch (e: Exception) {
println("Error: exception while getting companies $e")
return null
}
return companiesArray
}@Tapac I shared two apis. Is there any issue the way I use transaction?
Tapac
commented
3 years ago I think the problem is in return@transaction as the "commit" and "close" happens after transaction body block. I'll check it a bit later
trnchawla
commented
3 years ago @Tapac meanwhile I will try removing the return. is there any better way to complete the transaction block as I don't want to continue?
trnchawla
commented
3 years ago @Tapac also when these warnings come I don't see that return@transaction is getting hit so it could be something else most probably.
trnchawla
commented
3 years ago fun createCompanyEmployee(
ownerEmail: String,
companyEmployeeMapper: CompanyEmployeeMapper
): Triple<Boolean, UUID?, String?> {
var created = false
var uuid: UUID? = null
var errorRet: String? = null
transaction {
addLogger(StdOutSqlLogger)
if (companyEmployeeMapper.companyUUID != null) {
if (!isEmailRefersToCompanyOwner(ownerEmail, companyEmployeeMapper.companyUUID!!)) {
errorRet = "No company of this owner present"
return@transaction
}
} else {
errorRet = "No company uuid passed"
return@transaction
}
val companyEmployeeUUID = CompanyEmployee.insert {
it[name] = companyEmployeeMapper.name
it[username] = companyEmployeeMapper.username
it[email] = companyEmployeeMapper.email
it[credential] = companyEmployeeMapper.credential
it[company] = companyEmployeeMapper.companyUUID
if (companyEmployeeMapper.address != null) {
it[address] = createAddress(companyEmployeeMapper.address)
}
if (companyEmployeeMapper.dob != null) {
it[dob] = LocalDateTime.parse(companyEmployeeMapper.dob, dateFormatter)
}
if (companyEmployeeMapper.anniversaryDate != null) {
it[anniversaryDate] = LocalDateTime.parse(companyEmployeeMapper.anniversaryDate, dateFormatter)
}
} get CompanyEmployee.uuid
createCompanyEmployeePhoneNos(companyEmployeeMapper.numbers, companyEmployeeUUID)
created = true
uuid = companyEmployeeUUID
errorRet = null
}
return Triple(created, uuid, errorRet)
}
One more api for your reference.
trnchawla
commented
3 years ago @Tapac any update on this?
hfazai
commented
3 years ago Hi, I tried your code and I was unable to reproduce this issue. I ran it on an embedded postgresql database without changing any configuration. Maybe this due to some specific configuration on your instace. You can try to run the exact code with otj-pg-embedded to check if the warning still ocuring. If this is the case, we may make improvement in Exposed to deal properly with it. I hope this can help.
trnchawla
commented
3 years ago Am using one instance on elephantsql free instance. https://www.elephantsql.com/.
Tapac
commented
3 years ago Do you have an outer transaction block where you call createCompany?
trnchawla
commented
3 years ago
suspend fun registerCompany(call: ApplicationCall) {
val jwtPayload = call.principal<JWTPrincipal>()?.payload
val userEmail = jwtPayload?.getClaim("email")?.asString()
if (userEmail == null) {
call.respond(HttpStatusCode.BadRequest, "No email in jwt token! Fatal")
}
val companyMapper = call.receive<CompanyMapper>()
val (created, companyUUID, errorRet) = CompanyOwnerApis.createCompany(userEmail!!, companyMapper)
if (!created) {
call.respond(HttpStatusCode.BadRequest, "Not able to create company with error $errorRet")
}
call.respond(HttpStatusCode.Created, "Created company with UUID : $companyUUID")
}
@Tapac There is no outer transaction block for createCompany(), above is attached snippet and only place where it is getting called. But I have places where I have transaction inside transaction below is one example:
fun getAllCompanies(ownerEmail: String): ArrayList<CompanyMapper>? {
val companiesArray = arrayListOf<CompanyMapper>()
val companyOwner = getCompanyOwnerFromEmail(ownerEmail)
if (companyOwner?.uuid == null) {
return null
}
try {
transaction {
val companiesOwned = CompanyOwnerCompany.select {
CompanyOwnerCompany.owner eq companyOwner.uuid
}
if (companiesOwned.count() == 0L) {
return@transaction
}
for (companyOwned in companiesOwned) {
val companyMapper = getCompanyDetails(companyOwned[CompanyOwnerCompany.company])
if (companyMapper != null) {
companiesArray.add(companyMapper)
}
}
}
} catch (e: Exception) {
println("Error: exception while getting companies $e")
return null
}
return companiesArray
}
fun getCompanyDetails(companyUUID: UUID): CompanyMapper? {
var companyMapper: CompanyMapper? = null
transaction {
val companyList = Company.select {
Company.uuid eq companyUUID
}
if (companyList.count() <= 0L) {
return@transaction
}
val companyEntity = companyList.first()
companyMapper = CompanyMapper.createMapper(companyEntity)
val addressEntityList = Address.select {
Address.uuid eq companyEntity[Company.officeAddress]
}
if (addressEntityList.count() <= 0L) {
return@transaction
}
val addressMapper = AddressMapper.createMapper(addressEntityList.first())
companyMapper?.address = addressMapper
}
return companyMapper
}If you see above getCompanyDetails is getting called inside getAllCompanies.
trnchawla
commented
3 years ago I was missing this db.useNestedTransactions = true, do you think this will cause above issue?
Tapac
commented
3 years ago Thank you for the details, I guess I found some ResultSet leaks in count()/empty() functions on Query. Fixed in master
trnchawla
commented
3 years ago @Tapac am seeing this error again. Am on the latest version of exposed 0.31.1.
May 21, 2021 9:07:15 PM com.impossibl.postgres.jdbc.ThreadedHousekeeper$HousekeeperReference cleanup WARNING: Cleaning up leaked result-set Allocation occurred @ at org.jetbrains.exposed.sql.statements.jdbc.JdbcPreparedStatementImpl.executeQuery(JdbcPreparedStatementImpl.kt:24) at org.jetbrains.exposed.sql.Query.executeInternal(Query.kt:83) at org.jetbrains.exposed.sql.Query.executeInternal(Query.kt:13) at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:60) at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:129) at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:115) at org.jetbrains.exposed.sql.AbstractQuery.iterator(AbstractQuery.kt:65) at kotlin.collections.CollectionsKt___CollectionsKt.first(_Collections.kt:198) at tech.tarunchawla.repository.apis.CompanyUserApis.getOrderItemMapper(CompanyUserApis.kt:250) at tech.tarunchawla.repository.apis.CompanyUserApis.getOrderListMapper(CompanyUserApis.kt:232) at tech.tarunchawla.repository.apis.CompanyUserApis.access$getOrderListMapper(CompanyUserApis.kt:13) at tech.tarunchawla.repository.apis.CompanyUserApis$getOrders$1.invoke(CompanyUserApis.kt:210) at tech.tarunchawla.repository.apis.CompanyUserApis$getOrders$1.invoke(CompanyUserApis.kt:13) at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$1.invoke(ThreadLocalTransactionManager.kt:173) at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$inTopLevelTransaction$2.invoke(ThreadLocalTransactionManager.kt:194) at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:202) at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:193) at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt$transaction$1.invoke(ThreadLocalTransactionManager.kt:151) at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.keepAndRestoreTransactionRefAfterRun(ThreadLocalTransactionManager.kt:202) at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:123) at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:121) at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:120) at tech.tarunchawla.repository.apis.CompanyUserApis.getOrders(CompanyUserApis.kt:198) at tech.tarunchawla.server.Gateway.getOrders(Gateway.kt:425) at tech.tarunchawla.server.routes.CompanyUserRoutesKt$CustomerOrderRoutes$1.invokeSuspend(CompanyUserRoutes.kt:31) at tech.tarunchawla.server.routes.CompanyUserRoutesKt$CustomerOrderRoutes$1.invoke(CompanyUserRoutes.kt) at io.ktor.util.pipeline.SuspendFunctionGun.loop(PipelineContext.kt:323) at io.ktor.util.pipeline.SuspendFunctionGun.proceed(PipelineContext.kt:168) at io.ktor.util.pipeline.SuspendFunctionGun.execute(PipelineContext.kt:188) at io.ktor.util.pipeline.Pipeline.execute(Pipeline.kt:31) at io.ktor.routing.Routing.executeResult(Routing.kt:147) at io.ktor.routing.Routing.interceptor(Routing.kt:34) at io.ktor.routing.Routing$Feature$install$1.invokeSuspend(Routing.kt:99) at io.ktor.routing.Routing$Feature$install$1.invoke(Routing.kt) at io.ktor.util.pipeline.SuspendFunctionGun.loop(PipelineContext.kt:323) at io.ktor.util.pipeline.SuspendFunctionGun.proceed(PipelineContext.kt:168) at io.ktor.features.ContentNegotiation$Feature$install$1.invokeSuspend(ContentNegotiation.kt:107) at io.ktor.features.ContentNegotiation$Feature$install$1.invoke(ContentNegotiation.kt) at io.ktor.util.pipeline.SuspendFunctionGun.loop(PipelineContext.kt:323) at io.ktor.util.pipeline.SuspendFunctionGun.proceed(PipelineContext.kt:168) at io.ktor.features.StatusPages$interceptCall$2.invokeSuspend(StatusPages.kt:101) at io.ktor.features.StatusPages$interceptCall$2.invoke(StatusPages.kt) at kotlinx.coroutines.intrinsics.UndispatchedKt.startUndispatchedOrReturn(Undispatched.kt:91) at kotlinx.coroutines.CoroutineScopeKt.coroutineScope(CoroutineScope.kt:194) at io.ktor.features.StatusPages.interceptCall(StatusPages.kt:100) at io.ktor.features.StatusPages$Feature$install$2.invokeSuspend(StatusPages.kt:140) at io.ktor.features.StatusPages$Feature$install$2.invoke(StatusPages.kt) at io.ktor.util.pipeline.SuspendFunctionGun.loop(PipelineContext.kt:323) at io.ktor.util.pipeline.SuspendFunctionGun.proceed(PipelineContext.kt:168) at io.ktor.features.CallLogging$Feature$install$2.invokeSuspend(CallLogging.kt:139) at io.ktor.features.CallLogging$Feature$install$2.invoke(CallLogging.kt) at io.ktor.util.pipeline.SuspendFunctionGun.loop(PipelineContext.kt:323) at io.ktor.util.pipeline.SuspendFunctionGun.proceed(PipelineContext.kt:168) at io.ktor.util.pipeline.SuspendFunctionGun.execute(PipelineContext.kt:188) at io.ktor.util.pipeline.Pipeline.execute(Pipeline.kt:31) at io.ktor.server.engine.DefaultEnginePipelineKt$defaultEnginePipeline$2.invokeSuspend(DefaultEnginePipeline.kt:121) at io.ktor.server.engine.DefaultEnginePipelineKt$defaultEnginePipeline$2.invoke(DefaultEnginePipeline.kt) at io.ktor.util.pipeline.SuspendFunctionGun.loop(PipelineContext.kt:323) at io.ktor.util.pipeline.SuspendFunctionGun.proceed(PipelineContext.kt:168) at io.ktor.util.pipeline.SuspendFunctionGun.execute(PipelineContext.kt:188) at io.ktor.util.pipeline.Pipeline.execute(Pipeline.kt:31) at io.ktor.server.netty.NettyApplicationCallHandler$handleRequest$1.invokeSuspend(NettyApplicationCallHandler.kt:40) at io.ktor.server.netty.NettyApplicationCallHandler$handleRequest$1.invoke(NettyApplicationCallHandler.kt) at kotlinx.coroutines.intrinsics.UndispatchedKt.startCoroutineUndispatched(Undispatched.kt:55) at kotlinx.coroutines.CoroutineStart.invoke(CoroutineStart.kt:112) at kotlinx.coroutines.AbstractCoroutine.start(AbstractCoroutine.kt:158) at kotlinx.coroutines.BuildersKt__Builders_commonKt.launch(Builders.common.kt:56) at kotlinx.coroutines.BuildersKt.launch(Unknown Source) at io.ktor.server.netty.NettyApplicationCallHandler.handleRequest(NettyApplicationCallHandler.kt:30) at io.ktor.server.netty.NettyApplicationCallHandler.channelRead(NettyApplicationCallHandler.kt:24) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379) at io.netty.channel.AbstractChannelHandlerContext.access$600(AbstractChannelHandlerContext.java:61) at io.netty.channel.AbstractChannelHandlerContext$7.run(AbstractChannelHandlerContext.java:370) at io.netty.util.concurrent.AbstractEventExecutor.safeExecute(AbstractEventExecutor.java:164) at io.netty.util.concurrent.SingleThreadEventExecutor.runAllTasks(SingleThreadEventExecutor.java:472) at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:500) at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989) at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74) at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) at java.base/java.lang.Thread.run(Thread.java:832)
trnchawla
commented
3 years ago can you please point me to the problem, this could be a critical issue for my app, please help me fix this.
trnchawla
commented
3 years ago @Tapac it seems this is happening always after adding this API:
fun getOrders(customerEmail: String, companyUUID: String): Pair<ArrayList<OrderListMapper>, String?> {
var errorRet: String? = null
val orders: ArrayList<OrderListMapper> = arrayListOf()
try {
transaction {
val customerEntityList = CompanyCustomer.select { (CompanyCustomer.email eq customerEmail) and
(CompanyCustomer.company eq UUID.fromString(companyUUID))}
if (customerEntityList.count() != 1L) {
errorRet = "No customer with email $customerEmail for companyUUID $companyUUID"
rollback()
return@transaction
}
val customerEntity = customerEntityList.first()
val customerUUID = customerEntity[CompanyCustomer.uuid]
val orderList = OrderList.select { OrderList.createdBy eq customerUUID }
orderList.forEach {
getOrderListMapper(it, customerEmail)?.let { it1 -> orders.add(it1) }
}
}
} catch (e: Exception) {
println("$e")
errorRet = "$e"
}
return Pair(orders, errorRet)
} private fun getOrderListMapper(it: ResultRow, customerEmail: String): OrderListMapper? {
var orderListMapper : OrderListMapper? = null
transaction {
val orderListUUID = it[OrderList.uuid]
val orderNoResult = OrderNo.select { OrderNo.uuid eq orderListUUID }.first()
val year = orderNoResult[OrderNo.year]
val month = orderNoResult[OrderNo.month]
val orderNoInt = orderNoResult[OrderNo.orderno]
val orderNo = "$year-$month-$orderNoInt"
val storeUUID = it[OrderList.store]
//TODO add order notes
val orderItems = OrderedItem.select { OrderedItem.order eq orderListUUID }
val orderedItemsMapper = arrayListOf<OrderedItemMapper>()
orderItems.forEach { orderItem ->
getOrderItemMapper(orderItem)?.let { it1 -> orderedItemsMapper.add(it1) }
}
orderListMapper = OrderListMapper(
orderNo,
customerEmail,
storeUUID,
null,
orderedItemsMapper,
it[OrderList.uuid],
null,
it[OrderList.orderAmount],
OrderTypeEnum.ONLINE.toString()
)
}
return orderListMapper
} private fun getOrderItemMapper(orderItem: ResultRow): OrderedItemMapper? {
var orderedItemMapper : OrderedItemMapper? = null
transaction {
val itemUUID = orderItem[OrderedItem.item]
val itemEntity = Item.select { Item.uuid eq itemUUID }.first()
val orderedItemModifiers = OrderedItemModifier.join(Modifier, JoinType.INNER, additionalConstraint =
{ (OrderedItemModifier.orderedItem eq orderItem[OrderedItem.uuid]) and (Modifier.uuid eq OrderedItemModifier.appliedModifier) })
.selectAll()
val orderedItemModifiersMapper = arrayListOf<OrderedItemModifierMapper>()
orderedItemModifiers.forEach {
val orderedItemModifierMapper = OrderedItemModifierMapper(it[Modifier.uuid], null, it[Modifier.name])
val size = orderedItemModifiersMapper.size
orderedItemModifiersMapper.add(orderedItemModifierMapper)
}
val baseModifier = orderedItemModifiers.filter { it[Modifier.modCat] == itemEntity[Item.baseModCat] }.first()
orderedItemMapper = OrderedItemMapper(
itemUUID,
itemEntity[Item.name],
orderItem[OrderedItem.quantity],
orderItem[OrderedItem.amount],
null,
null,
baseModifier[Modifier.uuid],
null,
orderedItemModifiersMapper
)
}
return orderedItemMapper
}getOrders() is the main function which is calling the other two functions. Am pasting here the function after adding which am getting the issue always.