<@U2VJ1T0E5> <@U071QGDLH> I THINK I have found the...
# docker-commandbox
g
@mborn @jclausen I THINK I have found the issue I am having with using the Ortus Hibernate extension / Hibernate 5.x.... An exception is thrown where ever I use a transaction / transactionCommit(). In my Application.cfc I have the following ORM settings; (which with 3.5.x means I need to use transactions... as I don't have
flushAtRequestEnd
at play...)
Copy code
this.ormSettings = {
		datasource = "GSN",
		dialect = "MySQL",
		dbCreate = "none",
		cfcLocation = "/gsncfc/db",
		automanageSession = false,
		flushAtRequestEnd = false,
		useDBForMapping = false,
		eventHandling = true,
		eventHandler = "gsncfc.db.GlobalEventHandler",
		logSQL = false
	};
m
An exception is thrown where ever I use a transaction / transactionCommit().
What exception would that be? aka, the full stacktrace?
Since
flushAtRequestEnd=false
, the only way to commit changes is to close a transaction block or run
ormFlush()
. Hence, I am curious - do you also get this error if you comment out the
transaction{
block and run
ormFlush()
?
g
I have started using v6.3.1 of the extension. So using a transaction block AND calling transactionCommit() - fails. Commenting out the transaction "container" and using ormFlush() insead - also fails. Here is my function
Copy code
public void function endAllSessions(required numeric userId) output="false" {

		//transaction action="begin" {

			try {

				// Retreive any sessions for the user that are current
				var currentSessions = ORMExecuteQuery("
											SELECT s
											FROM session s
											INNER JOIN s.profile p
											WHERE p.user.id = :uid
											AND s.sessionEnd >= :datetimenow
											AND s.impersonatingProfile IS NULL",
											{
												uid=arguments.userId,
												datetimenow=createODBCDatetime(now())
											}
				);

				for(var j=1; j LTE arrayLen(currentSessions); j++) {

					// End session now
					currentSessions[j].setSessionEnd( now() );
					entitySave(currentSessions[j]);
				};

				//transactionCommit();
				ormFLush();
			} catch(any e) {

				// TODO : LOG
				// transactionRollback();
				rethrow;
			}
		//}
	}
g
I can't paste it in here, too long apparently.
j
You can click the “+” and select “create snippet”. That will allow you to paste longer text
👍🏼 1
g
Untitled.java
j
“Could not read entity state from ResultSet” is the significant block. Which database server are you using ( brand and version ) and which ORM dialect?
g
we use MariaDB, with the Lucee MySQL extension. And dialect is "MySQL"
m
I've got a feeling "session" is a reserved word, what if you escape that table name?
g
I have altered the SQL, surrounding the tablename with backticks. Same error / same line / etc. (Complete restart of the container and app, too)
j
Try changing the ORM dialect to “org.hibernate.dialect.MySQL5InnoDBDialect”. I think the “MySQL” dialect in Hibernate 5 maps to the MySQL 8 dialect. Basically, the error you are receiving means that the the update statement isn’t receiving the expected response from the statement, and Hibernate can’t mark the entity as “non-dirty” in the session.
g
I have made the change you suggested and everything remains unchanged.
j
😞
I also see this in the stack trace: “Caused by: java.sql.SQLException: Operation not allowed after ResultSet closed”. Do you have any events that happen on the entity, either postSave, postUpdate, or postLoad?
g
No, none. This function isn't being called from within a transaction block or anything like that either.
j
Are there any circular dependencies between users, where Hibernate might be seeing another user entity as dirty and trying to update that one?
g
No Jon, pretty boring here. no funcy I am my own parent or anything like that either, sorry. Going back one question... We do have a globalEvent handler defined.. And we do have some pre/post events defined - but they are just doing logging. only.
j
Try commenting out those logging actions, to eliminate them as the culprits. Hibernate errors within events have a way of getting swallowed and are hard to track down from the stack trace
g
Thanks Jon, I do REALLY appreciate the help you giving me - but is after 0130 here- so I need to run away and get some ZZZs, sorry. I will remove the logging AND also go through a couple of other CFCsm that we have Eg. "_mappedSuperClass"... And ensure that we're not "nesting" transactions / orm tasks. And report back in the thread.
OK I have checked the CFCs again - and for the login process as a hold we are not extending any CFCs - so it is just the GlobalEventHandler in play. And I can confirm - that commenting out all the logging does not help us.
m
You don't get this error on the Hibernate 3.5 Lucee extension, correct?
👍🏼 1
What about on the Hibernate 5.4 Lucee extension?
I suspect the table identifier is the issue here. You may need some funky Hibernate configuration to properly escape that
session
reserved word.
The Hibernate extension is smart enough to detect that and escape it when generating DDL, but it doesn't currently affect how or if Hibernate escapes identifiers at query time. See https://vladmihalcea.com/escape-sql-reserved-keywords-jpa-hibernate/
g
Just installing / testing Lucee 5.x extension
Same error with the Lucee 5.x extension.
👍 1
m
Got it, so that's good to know. (Obviously doesn't help you, though.)
👍🏼 1
I strongly suspect Hibernate's identifier parsing / result building changed in v4 or v5, and they're more strict about table names.
Do you have the ability to set a different table name in this app?
If not, I may be able to send you some configuration that will auto-escape all identifiers at the Hibernate level.
g
Just trying with specifiying the table in the CFC
Copy code
table="`session`"
👍 1
Ultimate We can change the table name, just not any time soon, easily. The app is a real mismatch of orm / plain SQL / with a framework / not using the framework... Using a Super Class (with interceptors) / not using And while this is all shit... the biggest issue is we have no unit tests to speak of... So it will be a nightmare to ""promise" we haven't broken something.
👍 1
Ok changing the session CFC - and adding in the table attribute - did not change the error at all. I'll try again with
table="\"session"\"
m
Yeah, I get it, you're between a rock and a hard place right now.
It's not easy being there. 😕
I'm not sure what I can do on my end other than offer more suggestions.
👍🏼 1
There are a number of levels / methods for escaping reserved words, and most of them aren't available to us in CFML-land.
There's a Hibernate setting which should make this work globally:
Copy code
hibernate.globally_quoted_identifiers=true
but I don't know if we have the ability to set ad-hoc Hibernate config properties in CFML.
g
The app is good from a purpose perspective.... It has been run very much in "start-up" mode for a really long time. Get it going ASAP - move onto the next "thing". No overarching standards and no time to refactor out the "sins" you committed in getting working the first time around.
👍 1
FIXED.
1
table="\"session"\"
was the answer!
m
Whoa, nice!!
g
backicks - no worky.
m
That is great to hear - I'll be adding that to the docs.
Glad to hear you got it working!
👍🏼 1
g
Thanks again for all your help! please pass on my gratitude to Jon, too.
👍 1
I'll change back to using the Ortus extension - and retest.
m
Welcome!
"Don't forget to like and subscribe" 😆 🙂
g
And leave a nice google review.
☝️ 1
Sorry had a typo in the lucee_extensions... Just restarting the new container.
Not working - with Ortus extension. Same error. Just doing an ormReload()...
👎🏼 1
1
I'll revert back to the Lucee 5.x and see if that is still working.
There is something happening in the weird shit-o-meter. Not working for either now.
m
Wow, that is sad news.
g
Just doing another ormReload() and applicationStop() after the change back to Lucee Ext.
Nup - not working. And so I have absolutely no idea - as to how it did work before. All I did was change the table attribute.
not working with the Lucee extension or the Ortus one 😞 Still some investigating to do - I feel.
Me again .... Is there a method I can call that will give me the status of open ORM transaction? Despite fighting this of r along time - I end up getting to the next-step, by commenting out a transaction block. So if I could add a "isOrmTransactionOpen()" - check, I can litter my code with some logging / output - to look for an overlap of open transactions.
m
Good question.
None I can think of right now.
Technically, at the Hibernate level, a transaction is always open. So it's really the CFML / Lucee-level transactions that you're wanting to trace.
Here's a beta build that turns on Hibernate's DEBUG logging. Why don't you give this a try, @gavinbaumanis? Install it in the Lucee server admin, and use
box server log --follow
to see what's going on when you run that page/query.
👍🏼 1