Kotlin: 1.6.20
Ktorm: 3.4.1
MySQL Connector/J: 8.0.28
C3P0 Connection Pool: 0.9.5.5
I seem to be having a problem with "datetimes" (LocalDateTime) that have zero seconds. I know this would look like an issue with the JDBC driver but I tested the driver and it does not even accept LocalDateTime (only java.util.Date). This issue does not come up if the LDT does have seconds.
- Am I doing something wrong?
- Am I missing some configuration?
- Have I got the types wrong?
- Does anyone know of a workaround?
A minimum test that I am using to isolate the issue
const val tableName = "test"
const val fieldName = "ldt"
interface TestRecord : Entity<TestRecord> {
companion object : Entity.Factory<TestRecord>()
val ldt: LocalDateTime
}
object TestTable : Table<TestRecord>(tableName) {
val ldt = datetime(fieldName).bindTo { it.ldt }
}
class BlahTest {
@Test
fun a() {
val dataSource = DbConfig.from("test.properties").provideDataSource()
dataSource.connection.use {
it.prepareStatement("DROP TABLE IF EXISTS $tableName").execute()
it.prepareStatement("CREATE TABLE IF NOT EXISTS $tableName ($fieldName TIMESTAMP)").execute()
}
val db = Database.connect(dataSource = dataSource, dialect = MySqlDialect())
val tb = TestTable
val ldt = LocalDateTime.ofEpochSecond(0, 0, ZoneOffset.UTC)
.truncatedTo(ChronoUnit.SECONDS)
//.plusSeconds(1) // with anything but 0 it will work
db.insertOrUpdate(TestTable) {
set(tb.ldt, ldt)
}
}
}
Produces an error
com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01 00:00:00' for column 'ldt' at row 1
Ktorm issue: https://github.com/kotlin-orm/ktorm/issues/391
CodePudding user response:
The exception is formatting the timestamp value 0 as '1970-01-01 00:00:00', but MySQL is not.
https://dev.mysql.com/doc/refman/8.0/en/datetime.html says:
MySQL converts
TIMESTAMP
values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such asDATETIME
.)
For example, I'm in PDT, so when I try to insert a timestamp value 0, it adjusts it by 8 hours to '1969-12-31 16:00:00', which is not a legal value in the range for the TIMESTAMP
type.
mysql> create table tablename (ldt timestamp);
mysql> insert into tablename values (from_unixtime(0));
ERROR 1292 (22007): Incorrect datetime value: '1969-12-31 16:00:00' for column 'ldt' at row 1
So you could make sure to account for timezone when you generate your "zero" value to insert into a TIMESTAMP
.
Or you could use DATETIME
.
mysql> alter table tablename modify ldt datetime;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into tablename values (from_unixtime(0));
Query OK, 1 row affected (0.00 sec)
I recommend using DATETIME
anyway, because it supports a greater range of dates than TIMESTAMP
. This will become important if you need to store dates past 2038-01-19.