opened
konstin
username_0
## 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>
```


<!--
Please include as much of your codebase as needed to reproduce the error.  If the relevant files are large, please consider linking to a public repository or a [Gist](https://gist.github.com/).

Please post as much of your database schema as necessary. If you are using `infer_schema!`, you can use `diesel print-schema` and post the relevant parts from that.
-->

## Checklist

- [x] I have already looked over the [issue tracker](https://github.com/diesel-rs/diesel/issues) for similar issues.
Crash with special timestamp value in mysql (`0000-00-00 00:00:00`) through chrono
issue
created
killercup
username_1
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](
https://github.com/chronotope/chrono/blob/fe529c801609ea1063d901a17adba229682405ab/src/naive/date.rs#L161-L163) 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!
comment
created
alexeyzab
username_2
Hi there! I'd like to do this one.
comment
created
killercup
username_1
@username_2 great! It's yours :)

If you need any help, feel free to ask here or on <https://gitter.im/diesel-rs/diesel>!
comment
created
sgrif
username_3
Fixed by #1137
comment
closed
sgrif
username_3
issue
created
konstin
username_0
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.
comment
created
sgrif
username_3
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`](http://docs.diesel.rs/mysqlclient_sys/type.MYSQL_TIME.html)
comment
created
sgrif
username_3
Or option C: Use PG. 😉
comment
created
konstin
username_0
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.
comment
created
sgrif
username_3
The PG answer was a joke. :wink:
comment
created
konstin
username_0
I got that it was joke, but sometimes jokes become solutions ([though maybe not the one you want](http://www.montulli.org/theoriginofthe%3Cblink%3Etag)), and I've got nothing against giving PG a shot
comment
created
frol
username_4
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`:

```rust
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:

```rust
#[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`):

```rust
table! {
    users (user_id) {
        user_id -> Unsigned<Integer>,
        email -> Varchar,
        birthday -> Date,
    }
}
```
comment
created
mro95
username_5
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.
```
comment
created
weiznich
username_6
@username_5 That's expected behaviour.
comment
created
mro95
username_5
It is? In MySQL it is valid to store.
comment
created
weiznich
username_6
@username_5 Yes
comment