MySQL: "DateTime value not stored as DateTime, Int or string"

[divyenduz]

It is not possible to write to a field with type Date or Time in MySQL using Prisma client.

Both these cases are exhibited by this table in mapos database.

CREATE TABLE IF NOT EXISTS `logs` (
  `idLogs` INT NOT NULL AUTO_INCREMENT,
  `usuario` VARCHAR(80) NULL,
  `tarefa` VARCHAR(100) NULL,
  `data` DATE NULL,
  `hora` TIME NULL,
  `ip` VARCHAR(45) NULL,
  PRIMARY KEY (`idLogs`))
ENGINE = InnoDB;

To reproduce, import this table's SQL locally into a mysql database and introspect it (prisma2 introspect --url <db-connection-string>). Then run the following client query.

const data = await prisma.logs.create({
  data: {
    data: '2102-11-17T23:33:05.876Z',
    hora: '2060-10-19T03:28:01.420Z',
    ip: 'torgudbuj',
    tarefa: 'temdu',
    usuario: 'riawjo',
  },
})

This yields the error:

2020-03-09T10:50:51.706Z prisma-client [
  'mutation {\n' +
    '  createOnelogs(data: {\n' +
    '    data: "2102-11-17T23:33:05.876Z"\n' +
    '    hora: "2060-10-19T03:28:01.420Z"\n' +
    '    ip: "torgudbuj"\n' +
    '    tarefa: "temdu"\n' +
    '    usuario: "riawjo"\n' +
    '  }) {\n' +
    '    data\n' +
    '    hora\n' +
    '    idLogs\n' +
    '    ip\n' +
    '    tarefa\n' +
    '    usuario\n' +
    '  }\n' +
    '}'
]
2020-03-09T10:50:52.457Z prisma-client Results:
2020-03-09T10:50:52.458Z prisma-client [
  PrismaClientUnknownRequestError: Error occurred during query execution:
  ConnectorError(ConnectorError { user_facing_error: None, kind: ConversionError(Custom { kind: InvalidData, error: "DateTime value not stored as datetime, int or text" }
  
     0: failure::backtrace::internal::InternalBacktrace::new
     1: failure::backtrace::Backtrace::new
     2: sql_query_connector::row::row_value_to_prisma_value
     3: <quaint::connector::result_set::result_row::ResultRow as sql_query_connector::row::ToSqlRow>::to_sql_row
     4: <std::future::GenFuture<T> as core::future::future::Future>::poll
     5: <std::future::GenFuture<T> as core::future::future::Future>::poll
     6: std::future::poll_with_tls_context
     7: <std::future::GenFuture<T> as core::future::future::Future>::poll
     8: <std::future::GenFuture<T> as core::future::future::Future>::poll
     9: <std::future::GenFuture<T> as core::future::future::Future>::poll
    10: <std::future::GenFuture<T> as core::future::future::Future>::poll
    11: <std::future::GenFuture<T> as core::future::future::Future>::poll
    12: <std::future::GenFuture<T> as core::future::future::Future>::poll
    13: <std::future::GenFuture<T> as core::future::future::Future>::poll
    14: <std::future::GenFuture<T> as core::future::future::Future>::poll
    15: std::future::poll_with_tls_context
    16: <std::future::GenFuture<T> as core::future::future::Future>::poll
    17: <std::future::GenFuture<T> as core::future::future::Future>::poll
    18: std::panicking::try::do_call
    19: __rust_maybe_catch_panic
               at src/libpanic_unwind/lib.rs:78
    20: <std::future::GenFuture<T> as core::future::future::Future>::poll
    21: tokio::task::core::Core<T>::poll
    22: std::panicking::try::do_call
    23: __rust_maybe_catch_panic
               at src/libpanic_unwind/lib.rs:78
    24: tokio::task::harness::Harness<T,S>::poll
    25: tokio::runtime::thread_pool::worker::GenerationGuard::run_task
    26: tokio::runtime::thread_pool::worker::GenerationGuard::run
    27: std::thread::local::LocalKey<T>::with
    28: tokio::runtime::thread_pool::worker::Worker::run
    29: tokio::task::core::Core<T>::poll
    30: std::panicking::try::do_call
    31: __rust_maybe_catch_panic
               at src/libpanic_unwind/lib.rs:78
    32: tokio::task::harness::Harness<T,S>::poll
    33: tokio::runtime::context::enter
    34: std::sys_common::backtrace::__rust_begin_short_backtrace
    35: std::panicking::try::do_call
    36: __rust_maybe_catch_panic
               at src/libpanic_unwind/lib.rs:78
    37: core::ops::function::FnOnce::call_once{{vtable.shim}}
    38: <alloc::boxed::Box<F> as core::ops::function::FnOnce<A>>::call_once
               at /rustc/f3e1a954d2ead4e2fc197c7da7d71e6c61bad196/src/liballoc/boxed.rs:1022
    39: <alloc::boxed::Box<F> as core::ops::function::FnOnce<A>>::call_once
               at /rustc/f3e1a954d2ead4e2fc197c7da7d71e6c61bad196/src/liballoc/boxed.rs:1022
        std::sys_common::thread::start_thread
               at src/libstd/sys_common/thread.rs:13
        std::sys::unix::thread::Thread::new::thread_start
               at src/libstd/sys/unix/thread.rs:80
    40: start_thread
    41: __clone
  ) })
      at NodeEngine.graphQLToJSError (/home/runner/work/introspection-engine-output/introspection-engine-output/clients/mysql_public_test_utils/mapos/runtime/index.js:1:17768)
      at /home/runner/work/introspection-engine-output/introspection-engine-output/clients/mysql_public_test_utils/mapos/runtime/index.js:1:16702
      at Array.map (<anonymous>)
      at /home/runner/work/introspection-engine-output/introspection-engine-output/clients/mysql_public_test_utils/mapos/runtime/index.js:1:16669
      at processTicksAndRejections (internal/process/task_queues.js:97:5)
      at Dataloader.loader (/home/runner/work/introspection-engine-output/introspection-engine-output/clients/mysql_public_test_utils/mapos/index.js:56:23)
]

Trying to fix this by supplying just Date and Time for the respective fields also fails at Prisma client's validation step:

const data = await prisma.logs.create({
  data: {
    data: '2102-11-17',
    hora: '03:28:01',
    ip: 'torgudbuj',
    tarefa: 'temdu',
    usuario: 'riawjo',
  },
})

This Prisma client call yields:

divyendusingh [p2-date-and-time]$ node index.js
(node:40482) UnhandledPromiseRejectionWarning: Error: 
Invalid `prisma.logs.create()` invocation in
/Users/divyendusingh/Documents/prisma/p2-date-and-time/index.js:6:34


Invalid `prisma.logs.create()` invocation:

{
  data: {
    data: '2102-11-17',
          ~~~~~~~~~~~~
    hora: '03:28:01',
          ~~~~~~~~~~
    ip: 'torgudbuj',
    tarefa: 'temdu',
    usuario: 'riawjo'
  }
}

Argument data: Got invalid value '2102-11-17' on prisma.createOnelogs. Provided String, expected DateTime.
Argument hora: Got invalid value '03:28:01' on prisma.createOnelogs. Provided String, expected DateTime.

---

Internal notes:

Test utils database(s) affected:

• https://github.com/prisma/introspection-engine-output/blob/test_utils/test-utils/results/mysql_public_test_utils_mapos_stderr.log

[tomhoule]

I did some manual testing, and the query in the description works, as of prisma2@alpha928

[tomhoule]

Please close the issue if this is satisfactory and no further testing is needed.

[divyenduz]

Based on https://github.com/prisma/introspection-engine-output/pull/223

This is fixed