Weird cfdump issue - CF2018 - I dump a db table containing a datetime field and I get - a string. Same code, same dump, same data - in CF2021 I get an object? CF2018 is vanilla install, CF2021 is CommandBox ?????

I thought Adobe did some work on preserving types that might be responsible? And perhaps also a different version of the database driver that might change the types coming back from the DB?

@Jim Priest: Can you Please check if this is the same issue as https://tracker.adobe.com/#/view/CF-4211276

That looks like it - let me test and check which MySQL driver we're using - thanks!

In order for this to not happen you need to the 8.0.22 version of the driver, any higher gives this issue

It's awful that MySQL introduced BREAKING changes in a PATCH release: 8.0.23 😞

It is @seancorfield, but I believe it has been long enough that a fix could have been put in place on the CF side.

You may want to try MariaDB. Its opensource MySQL by the developers who actually created MySQL. Has some features MySQL is seriously lacking.

@Evil Ware the issue is with the JDBC driver.

I live in a world where I cannot change the driver unless its ordained by Adobe, blessed by tech-priests and given the rubber stamp of love by security. I can however change the database.

Would you not use the same driver to connect? It is still seen as mySQL

Different databases, operate slightly differently, even using the same driver.

So you have tested this with the latest driver? What version are you using?

No, and ACF10 - 23, depends on the the use. Mariadb 10.3.x corrected a few problems we had with mysql 5.X, same driver and acf version.

Do you know the exact driver version?

@jc FWIW, we're stuck on (MySQL driver) 8.0.22 because of the breaking changes around date/time handling in 8.0.23 -- the connection string flag they suggest in https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-23.html#connector-j-8-0-23-feature does not address all the issues -- and we haven't yet allocated the time it will take to change our code to work around this (the changes only break one of our apps and we've already sunk a week into investigation without getting anywhere). We're using the Percona 5.7 variant of MySQL (again, because migration to later versions breaks things, much as we'd like to upgrade the DB!). So, bottom line, I don't know how much Adobe can actually do about this on their end since the changes are somewhat app- / domain-specific 🤷🏻‍♂️

@seancorfield the notes on that is way above my knowledge but I would assume that this would give some insight to what Adobe could do, but I could definitely be wrong. I also have a project on Percona 5.7 and will be upgrading soon and forcing myself to use the 8.0.22 driver as I do with my other MySQL 8.x apps I have.

Also, with the implementation of the new mechanism, a

getObject(columnIndex)

call on a

DATETIME

column returns a

LocalDateTime

object now instead of a

String

. To receive a

String

like before, use

getObject(columnIndex, String.class)

instead.

We pretty much never use

DATETIME

-- we either use

DATE

or

TIMESTAMP

(partly because those map directly to

java.sql.Date

and

java.sql.Timestamp

-- there is no direct equivalent in JDBC for

DATETIME

). But, yeah, changing the fundamental type of a returned column like that is a terrible thing to do -- let alone in a PATCH release. I'm really not sure what Adobe could or should do about this. ACF is Java code and it relies on JDBC driver behavior just like any other JVM application. And CFML is a JVM language so it too, ultimately, depends on JDBC behavior. Calling

.getObject(columnIndex)

is pretty fundamental in JDBC-based apps and you get back whatever Java type the driver decides. You could query to get the schema details and introspect the metadata to identify that column 4 should be mapped to Java type String and call

.getObject()

with two arguments -- but that is not portable across databases or even across drivers and versions, so there really is an inherent limit in JDBC code in terms of what you can do at a generic level. This sort of decision requires domain knowledge and that really has to come from the application, not the "middleware". I've maintained Clojure's two main JDBC wrappers for a decade and they have to be generic since they need to work across dozens of databases and many dozens of JDBC drivers/versions. JDBC is a nightmare for this: it's underspecified and a lot of useful behavior is not portable -- and some databases and/or drivers just don't support certain operations (throwing an exception) or return

null

(even when the JDBC "spec" says you can't get

null

!).

Thanks for the explanation @seancorfield

Exhibit A: https://github.com/seancorfield/next-jdbc/blob/develop/doc/tips-and-tricks.md 🙂

My last 2 cents, if for some reason anyone wanted to continue to use the new drivers, the hack to make it work ( and not recommended ) is to call

.toString()

on the value. So for instance in @Jim Priest dataset, he would basically do the following ( again not recommended )

password_dt.toString()