Diesel: Crash with special timestamp value in mysql (`0000-00-00 00:00:00`) through chrono

Created on 25 Aug 2017 · 15Comments · Source: diesel-rs/diesel

Setup

Versions

Rust: rustc 1.21.0-nightly (8c303ed87 2017-08-20)
Diesel: 0.15.2
Database: mysql 5.7.1
Operating System: Ubuntu

Feature Flags

diesel: ["mysql", "chrono", "large-tables"]
diesel_codegen: ["mysql"]

Problem Description

When trying to load a value such as 0000-00-00 00:00:00 into a NaiveDateTime, there is panic inside chrono, which is called by diesel.

Steps to reproduce

Create a table in mysql with a timestamp field and add an entry with 0000-00-00 00:00:00
Try to load that row into a struct with a NaiveDateTime field.
The process will panic: thread '<unnamed>' panicked at 'invalid or out-of-range date', /checkout/src/libcore/option.rs:819:4

Full backtrace

This crash happened trying to read a table with multiple columns, therefore the bigger generic. I'm still sure that the problem is the 0000-00-00 00:00:00 as the crash doesn't occur after removing them.

thread '<unnamed>' panicked at 'invalid or out-of-range date', /checkout/src/libcore/option.rs:819:4
stack backtrace:
   0:     0x561ed0753873 - std::sys::imp::backtrace::tracing::imp::unwind_backtrace::h80d78ba3b40687b5
                               at /checkout/src/libstd/sys/unix/backtrace/tracing/gcc_s.rs:49
   1:     0x561ed074fa14 - std::sys_common::backtrace::_print::h47b9b32fe06dd6eb
                               at /checkout/src/libstd/sys_common/backtrace.rs:71
   2:     0x561ed0756263 - std::panicking::default_hook::{{closure}}::h006dcf643a2d1ee4
                               at /checkout/src/libstd/sys_common/backtrace.rs:60
                               at /checkout/src/libstd/panicking.rs:381
   3:     0x561ed0755fc2 - std::panicking::default_hook::h1e56c296d63316e2
                               at /checkout/src/libstd/panicking.rs:397
   4:     0x561ed0756767 - std::panicking::rust_panic_with_hook::h218401524ff20a29
                               at /checkout/src/libstd/panicking.rs:611
   5:     0x561ed07565c4 - std::panicking::begin_panic::h1668556d5aa9a913
                               at /checkout/src/libstd/panicking.rs:572
   6:     0x561ed0756539 - std::panicking::begin_panic_fmt::h1ac0ef5f67ba5408
                               at /checkout/src/libstd/panicking.rs:522
   7:     0x561ed07564ca - rust_begin_unwind
                               at /checkout/src/libstd/panicking.rs:498
   8:     0x561ed078f690 - core::panicking::panic_fmt::h121b79d1b9922ab6
                               at /checkout/src/libcore/panicking.rs:71
   9:     0x561ed078f6fd - core::option::expect_failed::h297561050155cf3c
                               at /checkout/src/libcore/option.rs:819
  10:     0x561ed0698469 - <core::option::Option<T>>::expect::hdbbee987ca7eef93
                               at /checkout/src/libcore/option.rs:302
  11:     0x561ed0699413 - chrono::naive::date::NaiveDate::from_ymd::h1d300ec380bb8f85
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/chrono-0.4.0/src/naive/date.rs:162
  12:     0x561ed0696ad8 - diesel::mysql::types::date_and_time::<impl diesel::types::FromSql<diesel::types::Timestamp, diesel::mysql::backend::Mysql> for chrono::naive::datetime::NaiveDateTime>::from_sql::h2cd8656b153b5a5e
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-0.15.2/src/mysql/types/date_and_time.rs:67
  13:     0x561ed02f25d9 - diesel::types::impls::option::<impl diesel::types::FromSql<diesel::types::Nullable<ST>, DB> for core::option::Option<T>>::from_sql::h103452ff4257f426
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-0.15.2/src/types/impls/option.rs:40
  14:     0x561ed02f255b - diesel::types::impls::date_and_time::chrono::<impl diesel::types::FromSqlRow<diesel::types::Nullable<diesel::types::Timestamp>, DB> for core::option::Option<chrono::naive::datetime::NaiveDateTime>>::build_from_row::hb283b609ed8bf430
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-0.15.2/src/types/impls/mod.rs:80
  15:     0x561ed02f90b1 - diesel::types::impls::tuples::<impl diesel::types::FromSqlRow<(SA, SB, SC, SD, SE, SF, SG, SH, SI, SJ, SK, SL, SM, SN, SO, SP, SQ, SR, SS, ST, SU, SV), DB> for (A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V)>::build_from_row::h1c1a76ea7195c494
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-0.15.2/src/types/impls/tuples.rs:44
  16:     0x561ed031b60c - <diesel::mysql::connection::MysqlConnection as diesel::connection::Connection>::query_by_index::{{closure}}::h285a5d3f187b6877
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-0.15.2/src/mysql/connection/mod.rs:76
  17:     0x561ed02f153a - diesel::mysql::connection::stmt::iterator::StatementIterator::map::hca914eae90b9f9d4
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-0.15.2/src/mysql/connection/stmt/iterator.rs:32
  18:     0x561ed031a37a - <diesel::mysql::connection::MysqlConnection as diesel::connection::Connection>::query_by_index::h243772f5897a7a3e
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-0.15.2/src/mysql/connection/mod.rs:75
  19:     0x561ed03103fe - <T as diesel::query_dsl::load_dsl::LoadQuery<Conn, U>>::internal_load::hc0dd74066aff7f0f
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-0.15.2/src/query_dsl/load_dsl.rs:22
  20:     0x561ed03049ae - diesel::query_dsl::load_dsl::LoadDsl::load::hc5d00ac392b993bf
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-0.15.2/src/query_dsl/load_dsl.rs:33
  21:     0x561ed032af65 - rustparl::paper_from_id::ha0948d4dfa92ae76
                               at src/main.rs:58
  22:     0x561ed032a9e9 - rustparl::rocket_route_fn_paper_from_id::hb37ad0a6f2a06613
                               at src/main.rs:42
  23:     0x561ed0547813 - rocket::rocket::Rocket::route::h7dceb302abf37427
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/rocket-0.3.2/src/rocket.rs:287
  24:     0x561ed0545bbf - rocket::rocket::Rocket::dispatch::h320edf44505c98be
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/rocket-0.3.2/src/rocket.rs:223
  25:     0x561ed05423ff - <rocket::rocket::Rocket as hyper::server::Handler>::handle::h552e9111ee31058e
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/rocket-0.3.2/src/rocket.rs:75
  26:     0x561ed044730b - <hyper::server::Worker<H>>::keep_alive_loop::hba6889363156fefc
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/hyper-0.10.12/src/server/mod.rs:337
  27:     0x561ed0448195 - <hyper::server::Worker<H>>::handle_connection::h3e27405dedee55a3
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/hyper-0.10.12/src/server/mod.rs:283
  28:     0x561ed04cd747 - hyper::server::handle::{{closure}}::h8a5cb571581578d8
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/hyper-0.10.12/src/server/mod.rs:242
  29:     0x561ed04cde7a - hyper::server::listener::spawn_with::{{closure}}::hd71bcd07197100bd
                               at /home/konsti/.cargo/registry/src/github.com-1ecc6299db9ec823/hyper-0.10.12/src/server/listener.rs:50
  30:     0x561ed0449417 - std::sys_common::backtrace::__rust_begin_short_backtrace::hab1fb1c24a4569cd
                               at /checkout/src/libstd/sys_common/backtrace.rs:136
  31:     0x561ed045abed - std::thread::Builder::spawn::{{closure}}::{{closure}}::h6cfb8020d4cbe3cf
                               at /checkout/src/libstd/thread/mod.rs:394
  32:     0x561ed0414117 - <std::panic::AssertUnwindSafe<F> as core::ops::function::FnOnce<()>>::call_once::hd7f09f7017ca2311
                               at /checkout/src/libstd/panic.rs:296
  33:     0x561ed045b1cf - std::panicking::try::do_call::hc7df21488714d3f6
                               at /checkout/src/libstd/panicking.rs:480
  34:     0x561ed075d6fc - __rust_maybe_catch_panic
                               at /checkout/src/libpanic_unwind/lib.rs:98
  35:     0x561ed045b09c - std::panicking::try::hb012c214aca5b861
                               at /checkout/src/libstd/panicking.rs:459
  36:     0x561ed04591d2 - std::panic::catch_unwind::h9532f6b5946a25e8
                               at /checkout/src/libstd/panic.rs:361
  37:     0x561ed045a6c0 - std::thread::Builder::spawn::{{closure}}::h84655cdd1ab51ba4
                               at /checkout/src/libstd/thread/mod.rs:393
  38:     0x561ed04a70e8 - <F as alloc::boxed::FnBox<A>>::call_box::h95b9ceca574e53bd
                               at /checkout/src/liballoc/boxed.rs:682
  39:     0x561ed075569b - std::sys::imp::thread::Thread::new::thread_start::h505201887c39140f
                               at /checkout/src/liballoc/boxed.rs:692
                               at /checkout/src/libstd/sys_common/thread.rs:21
                               at /checkout/src/libstd/sys/unix/thread.rs:84
  40:     0x7feb7d6196d9 - start_thread
  41:     0x7feb7d13cd7e - __clone
  42:                0x0 - <unknown>

Checklist

[x] I have already looked over the issue tracker for similar issues.

assigned bug good first issue

Source

konstin

👍1

All 15 comments

Very good catch!

Looks like an easy fix, luckily. From the stack trace: In this code

https://github.com/diesel-rs/diesel/blob/1f8118d2b1e24288deac6d3a1cfbc3d56a125915/diesel/src/mysql/types/date_and_time.rs#L67-L71

we are calling chrono's from_ymd but could just as well call the from_ymd_opt method just below which would not panic.

Same for and_hms_micro and probably many other chrono calls!

killercup on 25 Aug 2017

Hi there! I'd like to do this one.

alexeyzab on 31 Aug 2017

❤1

@alexeyzab great! It's yours :)

If you need any help, feel free to ask here or on https://gitter.im/diesel-rs/diesel!

killercup on 31 Aug 2017

Fixed by #1137

sgrif on 5 Sep 2017

Thanks for fix!

Is it right that the current solution still means that the query will fail by returning an Error? That would imply that it is not possible to query fields with that value, which is kind of bad in a real world scenario like the one I had where the db contains those values.

konstin on 5 Sep 2017

👍1

There's nothing else we can do here. chrono doesn't support dates with a day or month of 0. There is no non-error type we can return with chrono. You can either turn on the NO_ZERO_DATE SQL mode (in which case they will be converted to NULL, and you should ensure that the column is nullable to avoid errors), or you can load them into a type other than one from chrono which supports zero dates (the only such type I'm aware of is MYSQL_TIME

sgrif on 5 Sep 2017

👍1

Or option C: Use PG. 😉

sgrif on 5 Sep 2017

Thanks for the detailed answer, I see the point now.

Using PG isn't an option here as I use data from a different program that is tied to mysql.

konstin on 5 Sep 2017

The PG answer was a joke. :wink:

sgrif on 5 Sep 2017

I got that it was joke, but sometimes jokes become solutions (though maybe not the one you want), and I've got nothing against giving PG a shot

konstin on 5 Sep 2017

This is my take on getting this weird MySQL 0000-00-00 date handled as None on the Rust side.

I have created a custom field MysqlNaiveDate:

use chrono;
use mysqlclient_sys;

use diesel::mysql::Mysql;
use diesel::sql_types::Date;
use diesel::deserialize::{self, FromSql};

#[derive(Debug, FromSqlRow)]
pub struct MysqlNaiveDate(Option<chrono::NaiveDate>);

impl FromSql<Date, Mysql> for MysqlNaiveDate {
    fn from_sql(bytes: Option<&[u8]>) -> deserialize::Result<Self> {
        let mysql_time = <mysqlclient_sys::MYSQL_TIME as FromSql<Date, Mysql>>::from_sql(bytes)?;
        Ok(MysqlNaiveDate(
            if mysql_time.day == 0 && mysql_time.month == 0 && mysql_time.year == 0 {
                None
            } else {
                Some(
                    chrono::NaiveDate::from_ymd_opt(
                        mysql_time.year as i32,
                        mysql_time.month as u32,
                        mysql_time.day as u32,
                    ).ok_or_else(|| format!("Unable to convert {:?} to chrono", mysql_time))?
                )
            }
        ))
    }
}

and use it like this:

#[derive(Debug, Queryable)]
pub struct User {
    pub user_id: u32,
    pub email: String,
    pub birthday: MysqlNaiveDate,
}

Here is the schema.rs (generated for the existing DB, so I don't need to implement ToSql for MysqlNaiveDate):

table! {
    users (user_id) {
        user_id -> Unsigned<Integer>,
        email -> Varchar,
        birthday -> Date,
    }
}

frol on 16 Jun 2018

👍1

I still get this error when i try to load a NaiveDateTime with value 0000-00-00 00:00:00 from MySQL

thread 'tokio-runtime-worker' panicked at 'Error loading prices from database for product #1057: 
DeserializationError("Cannot parse this date: st_mysql_time { year: 0, month: 0, day: 0, hour: 0, minute: 0, second: 0, second_part: 0, neg: 0, time_type: MYSQL_TIMESTAMP_DATE }")',
 src/libcore/result.rs:1189:5
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace.