search

diesel-rs / diesel

A safe, extensible ORM and Query Builder for Rust
https://diesel.rs
Apache License 2.0
12.71k stars 1.07k forks source link

[cli] MySQL Diesel only supports tables with primary keys. #2786

Open Dragonrun1 opened 3 years ago

Dragonrun1 commented 3 years ago

Setup

set DATABASE_UR with DB server URL.

Make new migration which contents:

CREATE TABLE junk (
    id BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    something VARCHAR(20)
);

-- OR --

CREATE TABLE junk (
    id BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL,
    something VARCHAR(20),
    PRIMARY KEY (id)
);

-- OR --

Any other valid SQL syntax to make a table with a PRIMARY KEY

Versions

Problem Description

do: diesel migration run

What is the expected output?

Running migration 2021-05-18-193949_junk

What is the actual output?

Running migration 2021-05-18-193949_junk
Diesel only supports tables with primary keys. Table junk has no primary key

Checklist

weiznich commented 3 years ago

I cannot reproduce this behaviour with both migrations you've provided. Feel free to add additional information that may help reproducing this problem, like:

select column_name, table_name from key_column_usage where table_name = 'junk' and constraint_name IN ( select constraint_name from table_constraints where constraint_type = 'PRIMARY KEY' )



* Provide a complete dump of your schema after the table was created.

Closed as cannot reproduce. (We will reopen this issue if there is any way to reproduce it)
Dragonrun1 commented 3 years ago 
+-------------+------------+
| COLUMN_NAME | TABLE_NAME |
+-------------+------------+
| id          | junk       |
+-------------+------------+

I've also now try testing it with Ubuntu thru WSL2 as well as from other Linux machine on the same network and getting the same error so not sure why you can't reproduce.

Dragonrun1 commented 3 years ago

Just something else I noticed the generated schema.rs file is completely empty but the junk and __diesel_schema_migrations tables are create correctly as I would expect from past use of the cli.

weiznich commented 3 years ago

Hmm, that's strange. That result looks correct. We have tests for this as well, so it's quite surprising to hear that it does not work. It might be useful to run those tests locally to see if they are working as expected for your installation. If that's the case the error would be somehow related to your table definition, if that's not the case we would know that this is a general issue. You can run the tests by doing the following steps:

  1. Clone the diesel repo and checkout the 1.4.x branch
  2. change into the diesel_cli folder
  3. run DATABASE_URL=mysql://some/url/to/test_db cargo test --no-default-features --features "mysql"

These test cases contain several similar table constructions, so it seems unlikely that this is not catched there.

Dragonrun1 commented 3 years ago

I've also looked at and tries some migrations from an old project where I was using diesel where I know I don't have any problems and they are also showing the same error now with the new cli version so looks like it's some kind of strange regression going on.

I'll take a look at try out the tests I'm also looking at a fork I seem to have made around the same time to see if there's something I did there that fixed any issues I might have been having at the time and I just forgot to do a PR for them ;) Once I remember how to bring in the upstream changes I'll see what I find on that front as well.

Dragonrun1 commented 3 years ago

Here's a log on master branch test run:

cargo test --no-default-features --features mysql
    Updating crates.io index
   Compiling proc-macro2 v1.0.27
   Compiling unicode-xid v0.2.2
   Compiling syn v1.0.72
   Compiling winapi v0.3.9
   Compiling serde_derive v1.0.126
   Compiling autocfg v1.0.1
   Compiling serde v1.0.126
   Compiling matches v0.1.8
   Compiling tinyvec_macros v0.1.0
   Compiling pkg-config v0.3.19
   Compiling bitflags v1.2.1
   Compiling vcpkg v0.2.12
   Compiling memchr v2.4.0
   Compiling percent-encoding v2.1.0
   Compiling cfg-if v1.0.0
   Compiling libc v0.2.94
   Compiling itoa v0.4.7
   Compiling byteorder v1.4.3
   Compiling ppv-lite86 v0.2.10
   Compiling unicode-width v0.1.8
   Compiling regex-syntax v0.6.25
   Compiling unicode-segmentation v1.7.1
   Compiling strsim v0.8.0
   Compiling vec_map v0.8.2
   Compiling dotenv v0.15.0
   Compiling difference v2.0.0
   Compiling unicode-bidi v0.3.5
   Compiling tinyvec v1.2.0
   Compiling getrandom v0.2.3
   Compiling form_urlencoded v1.0.1
   Compiling num-traits v0.2.14
   Compiling num-integer v0.1.44
   Compiling textwrap v0.11.0
   Compiling heck v0.3.2
   Compiling rand_core v0.6.2
   Compiling mysqlclient-sys v0.2.4
   Compiling rand_chacha v0.3.0
   Compiling unicode-normalization v0.1.17
   Compiling quote v1.0.9
   Compiling rand v0.8.3
   Compiling aho-corasick v0.7.18
   Compiling idna v0.2.3
   Compiling regex v1.5.4
   Compiling url v2.2.2
   Compiling ansi_term v0.12.1
   Compiling atty v0.2.14
   Compiling chrono v0.4.19
   Compiling remove_dir_all v0.5.3
   Compiling clap v2.33.3
   Compiling tempfile v3.2.0
   Compiling diffy v0.2.1
   Compiling diesel_derives v2.0.0 (C:\repos\github\diesel\diesel_derives)
   Compiling diesel v2.0.0 (C:\repos\github\diesel\diesel)
   Compiling toml v0.5.8
   Compiling serde_regex v0.3.1
   Compiling migrations_internals v2.0.0 (C:\repos\github\diesel\diesel_migrations\migrations_internals)
   Compiling migrations_macros v2.0.0 (C:\repos\github\diesel\diesel_migrations\migrations_macros)
   Compiling diesel_migrations v2.0.0 (C:\repos\github\diesel\diesel_migrations)
   Compiling diesel_cli v1.4.0 (C:\repos\github\diesel\diesel_cli)
    Finished test [unoptimized + debuginfo] target(s) in 1m 46s
     Running C:\repos\github\diesel\target\debug\deps\diesel-00a5fcca004075d9.exe

running 16 tests
test infer_schema_internals::mysql::trailing_parenthesis_are_stripped ... ok
test infer_schema_internals::mysql::tinyint_is_bool_if_limit_1 ... ok
test infer_schema_internals::mysql::unsigned_types_are_supported ... ok
test infer_schema_internals::mysql::types_with_space_are_not_supported ... ok
test infer_schema_internals::mysql::int_is_treated_as_integer ... ok
test database::tests::split_pg_connection_string_handles_user_and_password ... ok
test database::tests::split_pg_connection_string_returns_postgres_url_and_database ... ok
test database::tests::split_pg_connection_string_handles_query_string ... ok
test infer_schema_internals::mysql::values_which_already_map_to_type_are_returned_unchanged ... ok
test tests::convert_absolute_path_to_relative_works ... ok
test validators::num::tests::is_positive_int_should_parse_a_positive_integer_from_input_string ... ok
test validators::num::tests::is_positive_int_should_throw_an_error_with_float ... ok
test validators::num::tests::is_positive_int_should_throw_an_error_with_negative_integer ... ok
test validators::num::tests::is_positive_int_should_throw_an_error_with_zero ... ok
test tests::cargo_toml_not_found_if_no_cargo_toml ... ok
test tests::toml_directory_find_cargo_toml ... ok

test result: ok. 16 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out

     Running C:\repos\github\diesel\target\debug\deps\tests-773122eb203df718.exe

running 104 tests
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
error: test failed, to rerun pass '--test tests'

Caused by:
  process didn't exit successfully: `C:\repos\github\diesel\target\debug\deps\tests-773122eb203df718.exe` (exit code: 0xc00
0001d, STATUS_ILLEGAL_INSTRUCTION)

Note that master still says its building v1.4.0 here.

git checkout -b rocket_test v1.4.6
Switched to a new branch 'rocket_test'
cargo test --no-default-features --features mysql
   Compiling tinyvec_macros v0.1.0
   Compiling syn v1.0.72
   Compiling cc v1.0.67
   Compiling matches v0.1.8
   Compiling vcpkg v0.2.12
   Compiling gimli v0.24.0
   Compiling unicode-xid v0.0.4
   Compiling serde_derive v1.0.126
error[E0658]: using the `?` macro Kleene operator for "at most one" repetition is unstable (see issue #48075)
 --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\tinyvec_macros-0.1.0\src\lib.rs:9:106
  |
9 |         $v:vis fn $fname:ident ($seif:ident : $seifty:ty $(,$argname:ident : $argtype:ty)*) $(-> $ret:ty)? ;
  |                                                                                                          ^

error: expected `*` or `+`
 --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\tinyvec_macros-0.1.0\src\lib.rs:9:106
  |
9 |         $v:vis fn $fname:ident ($seif:ident : $seifty:ty $(,$argname:ident : $argtype:ty)*) $(-> $ret:ty)? ;
  |                                                                                                          ^

error[E0658]: using the `?` macro Kleene operator for "at most one" repetition is unstable (see issue #48075)
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\tinyvec_macros-0.1.0\src\lib.rs:15:74
   |
15 |         $v fn $fname($seif : $seifty, $($argname: $argtype),*) $(-> $ret)? {
   |                                                                          ^

error: expected `*` or `+`
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\tinyvec_macros-0.1.0\src\lib.rs:15:74
   |
15 |         $v fn $fname($seif : $seifty, $($argname: $argtype),*) $(-> $ret)? {
   |                                                                          ^

error: aborting due to 4 previous errors

For more information about this error, try `rustc --explain E0658`.
error: Could not compile `tinyvec_macros`.
warning: build failed, waiting for other jobs to finish...
error[E0658]: using the `?` macro Kleene operator for "at most one" repetition is unstable (see issue #48075)
 --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\arch.rs:4:75
  |
4 |     ($struct_name:ident, { $($name:ident = ($val:expr, $disp:expr)),+ $(,)? }) => {
  |                                                                           ^

error: expected `*` or `+`
 --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\arch.rs:4:75
  |
4 |     ($struct_name:ident, { $($name:ident = ($val:expr, $disp:expr)),+ $(,)? }) => {
  |                                                                           ^

error[E0658]: using the `?` macro Kleene operator for "at most one" repetition is unstable (see issue #48075)
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\constants.rs:59:95
   |
59 |     ($(#[$meta:meta])* $struct_name:ident($struct_type:ty) { $($name:ident = $val:expr),+ $(,)? }) => {
   |                                                                                               ^

error: expected `*` or `+`
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\constants.rs:59:95
   |
59 |     ($(#[$meta:meta])* $struct_name:ident($struct_type:ty) { $($name:ident = $val:expr),+ $(,)? }) => {
   |                                                                                               ^

error[E0658]: use of unstable library feature 'split_ascii_whitespace' (see issue #48656)
    --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\cc-1.0.67\src\lib.rs:2197:14
     |
2197 |             .split_ascii_whitespace()
     |              ^^^^^^^^^^^^^^^^^^^^^^

error: aborting due to previous error

For more information about this error, try `rustc --explain E0658`.
error: Could not compile `cc`.
warning: build failed, waiting for other jobs to finish...
error[E0658]: use of unstable library feature 'alloc': this library is unlikely to be stabilized in its current form or nam
e (see issue #27783)
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\lib.rs:41:1
   |
41 | extern crate alloc;
   | ^^^^^^^^^^^^^^^^^^^

error[E0658]: use of unstable library feature 'try_from' (see issue #33417)
 --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:3:5
  |
3 | use core::convert::TryInto;
  |     ^^^^^^^^^^^^^^^^^^^^^^

error[E0658]: use of unstable library feature 'maybe_uninit' (see issue #53491)
 --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\read\cfi.rs:5:23
  |
5 | use core::mem::{self, MaybeUninit};
  |                       ^^^^^^^^^^^

error[E0658]: use of unstable library feature 'maybe_uninit' (see issue #53491)
    --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\read\cfi.rs:2313:20
     |
2313 |     rules_storage: MaybeUninit<[(Register, RegisterRule<R>); MAX_RULES]>,
     |                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

error[E0658]: use of unstable library feature 'try_from' (see issue #33417)
 --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\read\reader.rs:2:5
  |
2 | use core::convert::TryInto;
  |     ^^^^^^^^^^^^^^^^^^^^^^

error[E0658]: use of unstable library feature 'try_from' (see issue #33417)
 --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\read\abbrev.rs:5:5
  |
5 | use core::convert::TryFrom;
  |     ^^^^^^^^^^^^^^^^^^^^^^

error[E0658]: use of unstable library feature 'maybe_uninit' (see issue #53491)
    --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\read\cfi.rs:2330:28
     |
2330 |             rules_storage: MaybeUninit::uninit(),
     |                            ^^^^^^^^^^^^^^^^^^^

error[E0658]: use of unstable library feature 'try_from' (see issue #33417)
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:24:40
   |
24 |         let bytes: &[u8; 2] = buf[..2].try_into().unwrap();
   |                                        ^^^^^^^^

error[E0658]: use of unstable library feature 'int_to_from_bytes' (see issue #52963)
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:26:13
   |
26 |             u16::from_be_bytes(*bytes)
   |             ^^^^^^^^^^^^^^^^^^

error[E0658]: use of unstable library feature 'int_to_from_bytes' (see issue #52963)
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:28:13
   |
28 |             u16::from_le_bytes(*bytes)
   |             ^^^^^^^^^^^^^^^^^^

error[E0658]: use of unstable library feature 'try_from' (see issue #33417)
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:39:40
   |
39 |         let bytes: &[u8; 4] = buf[..4].try_into().unwrap();
   |                                        ^^^^^^^^

error[E0658]: use of unstable library feature 'int_to_from_bytes' (see issue #52963)
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:41:13
   |
41 |             u32::from_be_bytes(*bytes)
   |             ^^^^^^^^^^^^^^^^^^

error[E0658]: use of unstable library feature 'int_to_from_bytes' (see issue #52963)
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:43:13
   |
43 |             u32::from_le_bytes(*bytes)
   |             ^^^^^^^^^^^^^^^^^^

error[E0658]: use of unstable library feature 'try_from' (see issue #33417)
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:54:40
   |
54 |         let bytes: &[u8; 8] = buf[..8].try_into().unwrap();
   |                                        ^^^^^^^^

error[E0658]: use of unstable library feature 'int_to_from_bytes' (see issue #52963)
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:56:13
   |
56 |             u64::from_be_bytes(*bytes)
   |             ^^^^^^^^^^^^^^^^^^

error[E0658]: use of unstable library feature 'int_to_from_bytes' (see issue #52963)
  --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:58:13
   |
58 |             u64::from_le_bytes(*bytes)
   |             ^^^^^^^^^^^^^^^^^^

error[E0658]: use of unstable library feature 'int_to_from_bytes' (see issue #52963)
   --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:136:15
    |
136 |             n.to_be_bytes()
    |               ^^^^^^^^^^^

error[E0658]: use of unstable library feature 'int_to_from_bytes' (see issue #52963)
   --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:138:15
    |
138 |             n.to_le_bytes()
    |               ^^^^^^^^^^^

error[E0658]: use of unstable library feature 'int_to_from_bytes' (see issue #52963)
   --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:151:15
    |
151 |             n.to_be_bytes()
    |               ^^^^^^^^^^^

error[E0658]: use of unstable library feature 'int_to_from_bytes' (see issue #52963)
   --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:153:15
    |
153 |             n.to_le_bytes()
    |               ^^^^^^^^^^^

error[E0658]: use of unstable library feature 'int_to_from_bytes' (see issue #52963)
   --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:166:15
    |
166 |             n.to_be_bytes()
    |               ^^^^^^^^^^^

error[E0658]: use of unstable library feature 'int_to_from_bytes' (see issue #52963)
   --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\endianity.rs:168:15
    |
168 |             n.to_le_bytes()
    |               ^^^^^^^^^^^

error[E0599]: no function or associated item named `uninit` found for type `core::mem::MaybeUninit<_>` in the current scope
    --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\read\cfi.rs:2330:28
     |
2330 |             rules_storage: MaybeUninit::uninit(),
     |                            ^^^^^^^^^^^^^^^^^^^ function or associated item not found in `core::mem::MaybeUninit<_>`

error[E0658]: use of unstable library feature 'maybe_uninit' (see issue #53491)
    --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\read\cfi.rs:2367:60
     |
2367 |             core::slice::from_raw_parts(self.rules_storage.as_ptr() as *const _, self.rules_len)
     |                                                            ^^^^^^

error[E0658]: use of unstable library feature 'maybe_uninit' (see issue #53491)
    --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\read\cfi.rs:2375:36
     |
2375 |                 self.rules_storage.as_mut_ptr() as *mut _,
     |                                    ^^^^^^^^^^

error[E0658]: use of unstable library feature 'maybe_uninit' (see issue #53491)
    --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\read\cfi.rs:2406:50
     |
2406 |                     let ptr = self.rules_storage.as_mut_ptr() as *mut (Register, RegisterRule<R>);
     |                                                  ^^^^^^^^^^

error[E0658]: use of unstable library feature 'maybe_uninit' (see issue #53491)
    --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\read\cfi.rs:2447:42
     |
2447 |             let ptr = self.rules_storage.as_mut_ptr() as *mut (Register, RegisterRule<R>);
     |                                          ^^^^^^^^^^

error[E0658]: use of unstable library feature 'try_from' (see issue #33417)
   --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\read\reader.rs:398:14
    |
398 |             .try_into()
    |              ^^^^^^^^

error[E0658]: use of unstable library feature 'try_from' (see issue #33417)
   --> C:\Users\mgcum\.cargo\registry\src\github.com-1ecc6299db9ec823\gimli-0.24.0\src\read\abbrev.rs:158:27
    |
158 |         if let Ok(code) = usize::try_from(code) {
    |                           ^^^^^^^^^^^^^^^

error: aborting due to 33 previous errors

Some errors occurred: E0599, E0658.
For more information about an error, try `rustc --explain E0599`.
error: Could not compile `gimli`.

To learn more, run the command again with --verbose.

Note because of outdated rust-toolchain file everything is still trying to be build using rustc 1.31.0 instead of more current versions.

I'll try quick fix by just changing the rust-toolchain file so my default rustc 1.52.1 is used instead.

cargo test --no-default-features --features mysql
   Compiling autocfg v1.0.1
   Compiling proc-macro2 v1.0.27
   Compiling winapi v0.3.9
   Compiling unicode-xid v0.2.2
   Compiling cfg-if v1.0.0
   Compiling syn v1.0.72
   Compiling libc v0.2.94
   Compiling tinyvec_macros v0.1.0
   Compiling memchr v2.4.0
   Compiling vcpkg v0.2.12
   Compiling matches v0.1.8
   Compiling pkg-config v0.3.19
   Compiling cc v1.0.67
   Compiling adler v1.0.2
   Compiling gimli v0.24.0
   Compiling unicode-xid v0.0.4
   Compiling serde_derive v1.0.126
   Compiling lazy_static v1.4.0
   Compiling ppv-lite86 v0.2.10
   Compiling serde v1.0.126
   Compiling regex v0.2.11
   Compiling rustc-demangle v0.1.19
   Compiling percent-encoding v1.0.1
   Compiling quote v0.3.15
   Compiling bitflags v1.2.1
   Compiling object v0.24.0
   Compiling ucd-util v0.1.8
   Compiling unicode-width v0.1.8
   Compiling byteorder v1.4.3
   Compiling utf8-ranges v1.0.4
   Compiling vec_map v0.8.2
   Compiling strsim v0.8.0
   Compiling difference v1.0.0
   Compiling getrandom v0.2.3
   Compiling tinyvec v1.2.0
   Compiling unicode-bidi v0.3.5
   Compiling miniz_oxide v0.4.4
   Compiling num-traits v0.2.14
   Compiling num-integer v0.1.44
   Compiling synom v0.11.3
   Compiling thread_local v0.3.6
   Compiling regex-syntax v0.5.6
   Compiling textwrap v0.11.0
   Compiling mysqlclient-sys v0.2.4
   Compiling rand_core v0.6.2
   Compiling syn v0.11.11
   Compiling unicode-normalization v0.1.17
   Compiling addr2line v0.15.1
   Compiling backtrace v0.3.59
   Compiling rand_chacha v0.3.0
   Compiling quote v1.0.9
   Compiling aho-corasick v0.6.10
   Compiling rand v0.8.3
   Compiling idna v0.1.5
   Compiling url v1.7.2
   Compiling error-chain v0.10.0
   Compiling remove_dir_all v0.5.3
   Compiling time v0.1.43
   Compiling atty v0.2.14
   Compiling rand v0.4.6
   Compiling tempfile v3.2.0
   Compiling clap v2.33.3
   Compiling chrono v0.4.19
   Compiling tempdir v0.3.7
   Compiling derive-error-chain v0.10.1
   Compiling dotenv v0.10.1
   Compiling diesel_derives v1.4.1 (C:\repos\github\diesel\diesel_derives)
   Compiling diesel v1.4.6 (C:\repos\github\diesel\diesel)
warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
   --> diesel\src\result.rs:269:64
    |
269 |             Error::RollbackTransaction => write!(f, "{}", self.description()),
    |                                                                ^^^^^^^^^^^
    |
    = note: `#[warn(deprecated)]` on by default

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
   --> diesel\src\result.rs:270:65
    |
270 |             Error::AlreadyInTransaction => write!(f, "{}", self.description()),
    |                                                                 ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
   --> diesel\src\result.rs:279:59
    |
279 |             Error::InvalidCString(ref nul_err) => nul_err.description(),
    |                                                           ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
   --> diesel\src\result.rs:282:50
    |
282 |             Error::QueryBuilderError(ref e) => e.description(),
    |                                                  ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
   --> diesel\src\result.rs:283:53
    |
283 |             Error::DeserializationError(ref e) => e.description(),
    |                                                     ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
   --> diesel\src\result.rs:284:51
    |
284 |             Error::SerializationError(ref e) => e.description(),
    |                                                   ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
   --> diesel\src\result.rs:319:69
    |
319 |             ConnectionError::InvalidCString(ref nul_err) => nul_err.description(),
    |                                                                     ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
   --> diesel\src\result.rs:322:68
    |
322 |             ConnectionError::CouldntSetupConfiguration(ref e) => e.description(),
    |                                                                    ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
   --> diesel\src\result.rs:368:30
    |
368 |         write!(f, "{}", self.description())
    |                              ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel\src\migration\errors.rs:40:57
   |
40 |             MigrationError::IoError(ref error) => error.description(),
   |                                                         ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel\src\migration\errors.rs:54:14
   |
54 |         self.description().fmt(f)
   |              ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel\src\migration\errors.rs:98:68
   |
98 |             RunMigrationsError::MigrationError(ref error) => error.description(),
   |                                                                    ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel\src\migration\errors.rs:99:64
   |
99 |             RunMigrationsError::QueryError(ref error) => error.description(),
   |                                                                ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
   --> diesel\src\migration\errors.rs:108:43
    |
108 |         write!(f, "Failed with: {}", self.description())
    |                                           ^^^^^^^^^^^

   Compiling migrations_internals v1.4.1 (C:\repos\github\diesel\diesel_migrations\migrations_internals)
warning: 14 warnings emitted

   Compiling toml v0.4.10
   Compiling diesel_cli v1.4.1 (C:\repos\github\diesel\diesel_cli)
warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:49:28
   |
49 |                 .map(|e| e.description())
   |                            ^^^^^^^^^^^
   |
   = note: `#[warn(deprecated)]` on by default

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:50:42
   |
50 |                 .unwrap_or_else(|| error.description()),
   |                                          ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:53:28
   |
53 |                 .map(|e| e.description())
   |                            ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:54:42
   |
54 |                 .unwrap_or_else(|| error.description()),
   |                                          ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:57:28
   |
57 |                 .map(|e| e.description())
   |                            ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:58:42
   |
58 |                 .unwrap_or_else(|| error.description()),
   |                                          ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:65:14
   |
65 |         self.description().fmt(f)
   |              ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:49:28
   |
49 |                 .map(|e| e.description())
   |                            ^^^^^^^^^^^
   |
   = note: `#[warn(deprecated)]` on by default

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:50:42
   |
50 |                 .unwrap_or_else(|| error.description()),
   |                                          ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:53:28
   |
53 |                 .map(|e| e.description())
   |                            ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:54:42
   |
54 |                 .unwrap_or_else(|| error.description()),
   |                                          ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:57:28
   |
57 |                 .map(|e| e.description())
   |                            ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:58:42
   |
58 |                 .unwrap_or_else(|| error.description()),
   |                                          ^^^^^^^^^^^

warning: use of deprecated associated function `std::error::Error::description`: use the Display impl or to_string()
  --> diesel_cli\src\database_error.rs:65:14
   |
65 |         self.description().fmt(f)
   |              ^^^^^^^^^^^

warning: 7 warnings emitted

warning: 7 warnings emitted

    Finished test [unoptimized + debuginfo] target(s) in 1m 35s
     Running unittests (C:\repos\github\diesel\target\debug\deps\diesel-94b15a69502361aa.exe)

running 12 tests
test infer_schema_internals::mysql::int_is_treated_as_integer ... ok
test infer_schema_internals::mysql::tinyint_is_bool_if_limit_1 ... ok
test infer_schema_internals::mysql::trailing_parenthesis_are_stripped ... ok
test database::tests::split_pg_connection_string_returns_postgres_url_and_database ... ok
test database::tests::split_pg_connection_string_handles_user_and_password ... ok
test database::tests::split_pg_connection_string_handles_query_string ... ok
test infer_schema_internals::mysql::unsigned_types_are_supported ... ok
test infer_schema_internals::mysql::types_with_space_are_not_supported ... ok
test infer_schema_internals::mysql::values_which_already_map_to_type_are_returned_unchanged ... ok
test tests::convert_absolute_path_to_relative_works ... ok
test tests::cargo_toml_not_found_if_no_cargo_toml ... ok
test tests::toml_directory_find_cargo_toml ... ok

test result: ok. 12 passed; 0 failed; 0 ignored; 0 measured; 0 filtered out; finished in 0.02s

     Running tests\tests.rs (C:\repos\github\diesel\target\debug\deps\tests-f5b0704d61aedc3d.exe)

running 77 tests
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
error: test failed, to rerun pass '--test tests'

Caused by:
  process didn't exit successfully: `C:\repos\github\diesel\target\debug\deps\tests-f5b0704d61aedc3d.exe` (exit code: 0xc00
0001d, STATUS_ILLEGAL_INSTRUCTION)

Looks a lot like the master branch now just with a lot more warnings ;)

Maybe I'd doing something wrong but I don't really think so since some of the tests seem to run but others don't. CI workflows are great but they don't always show things that can crop up when running stuff directly on a local machine without being very careful to make sure they use the same environment etc. Docket images can lead to the same kind of issues as well plus their own special flakiness.

Let me know if you want me to try something else and also if you want me to try opening some issues for things like the bad rust-toolchain file etc.

Edit: Fixed missing code block start on second run.

weiznich commented 3 years ago

The tests panic because you've likely used a connection with insufficient permissions. Those tests need to be able to create and drop databases.

CI workflows are great but they don't always show things that can crop up when running stuff directly on a local machine without being very careful to make sure they use the same environment etc. Docket images can lead to the same kind of issues as well plus their own special flakiness.

I'm happy to get suggestions on how to improve our testing story, but I feel there is really not much we can do to ever test all potential environments.

Dragonrun1 commented 3 years ago 
SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE = '''rocket_test''@''localhost''';
+---------------------------+---------------+----------------------------+--------------+
| GRANTEE                   | TABLE_CATALOG | PRIVILEGE_TYPE             | IS_GRANTABLE |
+---------------------------+---------------+----------------------------+--------------+
| 'rocket_test'@'localhost' | def           | SELECT                     | NO           |
| 'rocket_test'@'localhost' | def           | INSERT                     | NO           |
| 'rocket_test'@'localhost' | def           | UPDATE                     | NO           |
| 'rocket_test'@'localhost' | def           | DELETE                     | NO           |
| 'rocket_test'@'localhost' | def           | CREATE                     | NO           |
| 'rocket_test'@'localhost' | def           | DROP                       | NO           |
| 'rocket_test'@'localhost' | def           | RELOAD                     | NO           |
| 'rocket_test'@'localhost' | def           | SHUTDOWN                   | NO           |
| 'rocket_test'@'localhost' | def           | PROCESS                    | NO           |
| 'rocket_test'@'localhost' | def           | FILE                       | NO           |
| 'rocket_test'@'localhost' | def           | REFERENCES                 | NO           |
| 'rocket_test'@'localhost' | def           | INDEX                      | NO           |
| 'rocket_test'@'localhost' | def           | ALTER                      | NO           |
| 'rocket_test'@'localhost' | def           | SHOW DATABASES             | NO           |
| 'rocket_test'@'localhost' | def           | SUPER                      | NO           |
| 'rocket_test'@'localhost' | def           | CREATE TEMPORARY TABLES    | NO           |
| 'rocket_test'@'localhost' | def           | LOCK TABLES                | NO           |
| 'rocket_test'@'localhost' | def           | EXECUTE                    | NO           |
| 'rocket_test'@'localhost' | def           | REPLICATION SLAVE          | NO           |
| 'rocket_test'@'localhost' | def           | REPLICATION CLIENT         | NO           |
| 'rocket_test'@'localhost' | def           | CREATE VIEW                | NO           |
| 'rocket_test'@'localhost' | def           | SHOW VIEW                  | NO           |
| 'rocket_test'@'localhost' | def           | CREATE ROUTINE             | NO           |
| 'rocket_test'@'localhost' | def           | ALTER ROUTINE              | NO           |
| 'rocket_test'@'localhost' | def           | CREATE USER                | NO           |
| 'rocket_test'@'localhost' | def           | EVENT                      | NO           |
| 'rocket_test'@'localhost' | def           | TRIGGER                    | NO           |
| 'rocket_test'@'localhost' | def           | CREATE TABLESPACE          | NO           |
| 'rocket_test'@'localhost' | def           | CREATE ROLE                | NO           |
| 'rocket_test'@'localhost' | def           | DROP ROLE                  | NO           |
| 'rocket_test'@'localhost' | def           | APPLICATION_PASSWORD_ADMIN | NO           |
| 'rocket_test'@'localhost' | def           | AUDIT_ADMIN                | NO           |
| 'rocket_test'@'localhost' | def           | BACKUP_ADMIN               | NO           |
| 'rocket_test'@'localhost' | def           | BINLOG_ADMIN               | NO           |
| 'rocket_test'@'localhost' | def           | BINLOG_ENCRYPTION_ADMIN    | NO           |
| 'rocket_test'@'localhost' | def           | CLONE_ADMIN                | NO           |
| 'rocket_test'@'localhost' | def           | CONNECTION_ADMIN           | NO           |
| 'rocket_test'@'localhost' | def           | ENCRYPTION_KEY_ADMIN       | NO           |
| 'rocket_test'@'localhost' | def           | FLUSH_OPTIMIZER_COSTS      | NO           |
| 'rocket_test'@'localhost' | def           | FLUSH_STATUS               | NO           |
| 'rocket_test'@'localhost' | def           | FLUSH_TABLES               | NO           |
| 'rocket_test'@'localhost' | def           | FLUSH_USER_RESOURCES       | NO           |
| 'rocket_test'@'localhost' | def           | GROUP_REPLICATION_ADMIN    | NO           |
| 'rocket_test'@'localhost' | def           | INNODB_REDO_LOG_ARCHIVE    | NO           |
| 'rocket_test'@'localhost' | def           | INNODB_REDO_LOG_ENABLE     | NO           |
| 'rocket_test'@'localhost' | def           | PERSIST_RO_VARIABLES_ADMIN | NO           |
| 'rocket_test'@'localhost' | def           | REPLICATION_APPLIER        | NO           |
| 'rocket_test'@'localhost' | def           | REPLICATION_SLAVE_ADMIN    | NO           |
| 'rocket_test'@'localhost' | def           | RESOURCE_GROUP_ADMIN       | NO           |
| 'rocket_test'@'localhost' | def           | RESOURCE_GROUP_USER        | NO           |
| 'rocket_test'@'localhost' | def           | ROLE_ADMIN                 | NO           |
| 'rocket_test'@'localhost' | def           | SERVICE_CONNECTION_ADMIN   | NO           |
| 'rocket_test'@'localhost' | def           | SESSION_VARIABLES_ADMIN    | NO           |
| 'rocket_test'@'localhost' | def           | SET_USER_ID                | NO           |
| 'rocket_test'@'localhost' | def           | SHOW_ROUTINE               | NO           |
| 'rocket_test'@'localhost' | def           | SYSTEM_USER                | NO           |
| 'rocket_test'@'localhost' | def           | SYSTEM_VARIABLES_ADMIN     | NO           |
| 'rocket_test'@'localhost' | def           | TABLE_ENCRYPTION_ADMIN     | NO           |
| 'rocket_test'@'localhost' | def           | XA_RECOVER_ADMIN           | NO           |
+---------------------------+---------------+----------------------------+--------------+
59 rows in set (0.00 sec)

So now the user is effectively root and tests still fail as before with panicked while panicking. Other ideas?

Edit:

DROP USER 'rocket_test'@'localhost';
CREATE USER 'rocket_test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xxx';
GRANT ALL PRIVILEGES ON *.* TO 'rocket_test'@'localhost';
FLUSH PRIVILEGES;
weiznich commented 3 years ago

The panic while panicing is very likely coming from this Drop impl, which means either you cannot connect to the database using the provided url, or the database cannot be droped. You can verify both cases by commenting out the corresponding code.

Dragonrun1 commented 3 years ago

I know for regular use DATABASE_URL includes a actual DB part but on the tests should it have one or does it matter?

mysql://rocket_test:xxx@localhost/rocket_test OR mysql://rocket_test:xxx@localhost/?

Dragonrun1 commented 3 years ago

Did cleanup of some stuff I was trying and switched back to master and I'm getting some other interesting errors now.

error: unused import: `bigdecimal::FromPrimitive`
   --> diesel\src\mysql\connection\bind.rs:602:9
    |
602 |     use bigdecimal::FromPrimitive;
    |         ^^^^^^^^^^^^^^^^^^^^^^^^^
    |
note: the lint level is defined here
   --> diesel\src\lib.rs:98:9
    |
98  | #![deny(warnings)]
    |         ^^^^^^^^
    = note: `#[deny(unused_imports)]` implied by `#[deny(warnings)]`

error[E0308]: mismatched types
    --> diesel\src\mysql\connection\bind.rs:1152:37
     |
1152 |         let conn: MysqlConnection = crate::test_helpers::connection();
     |                   ---------------   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected struct `mysql::connection::MysqlConnection`, found
struct `sqlite::connection::SqliteConnection`
     |                   |
     |                   expected due to this

error[E0308]: mismatched types
    --> diesel\src\mysql\connection\bind.rs:1299:37
     |
1299 |         let conn: MysqlConnection = crate::test_helpers::connection();
     |                   ---------------   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected struct `mysql::connection::MysqlConnection`, found
struct `sqlite::connection::SqliteConnection`
     |                   |
     |                   expected due to this

error[E0308]: mismatched types
    --> diesel\src\mysql\connection\bind.rs:1468:37
     |
1468 |         let conn: MysqlConnection = crate::test_helpers::connection();
     |                   ---------------   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected struct `mysql::connection::MysqlConnection`, found
struct `sqlite::connection::SqliteConnection`
     |                   |
     |                   expected due to this

error: aborting due to 4 previous errors

For more information about this error, try `rustc --explain E0308`.
error: could not compile `diesel`

To learn more, run the command again with --verbose.
warning: build failed, waiting for other jobs to finish...
error: build failed

running cargo test --no-default-features --features mysql

I seem to be finding all kinds of interesting stuff which is really not helping track down the root cause here for us of why my setup breaks vs CI etc.

Edit: Never mind just needed to do update from Github and back to the panicking error during tests :)

Dragonrun1 commented 3 years ago

Tried commenting out the drop in diesel_cli/tests/support/mysql_database.rs and I'm still getting:

running 104 tests
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
thread panicked while panicking. aborting.
error: test failed, to rerun pass '--test tests'

Caused by:
  process didn't exit successfully: `C:\repos\github\diesel\target\debug\deps\tests-022e4ebaed8c79ea.exe` (exit code: 0x
c000001d, STATUS_ILLEGAL_INSTRUCTION)

So that doesn't seem to help. Other ideas I can try?

Edit: BTW tried switching the URL to root user and still same issue so don't think it's a permission issue.

weiznich commented 3 years ago

Could you grab a stack trace via gdb and provide exact steps how to trigger this? Otherwise its not possible to say much about this error. Just to be sure: You followed the general setup described in the contributing documentation? Otherwise the CI configuration may also contain information about how to get things to work, as this is the way tests work.

SpBills commented 3 years ago

This was the original problem that I was trying to describe in my issue (then I got very busy for a week so I wasn't able to follow up on it). I am also having this issue.

weiznich commented 3 years ago

@SpBills Just commenting that you have the same issue will not magically fix it. Please provide any details that can help us reproducing the problem. The best approach for this is probably bundling together your migration with some docker container where you can reproduce the issue.

SpBills commented 3 years ago

@weiznich I have now tested it with a MariaDB instance in a docker container. It works correctly. Below are some details.

Docker version: 20.10.6, build 370c289 diesel version: 1.4.6 Ubuntu version: 20.04 mariadb image version: mariadb:10.2.38-bionic

bigtree9307 commented 3 years ago

@weiznich I have met the same problem when I used mysql:8.0 docker image，but it works correctly when I used mysql:5.7 docker image。

weiznich commented 3 years ago

@bigtree9307 Please provide a exact version. 8.0 is quite a large version range.

bigtree9307 commented 3 years ago

@weiznich I think it is 8.0.26. The docker image is here https://hub.docker.com/_/mysql?tab=description&page=1&ordering=last_updated

weiznich commented 3 years ago

@bigtree9307 Can you additionally provide the following information:

bigtree9307 commented 3 years ago

@weiznich Rust：1.54.0 diesel：1.4.4 diesel-cli：1.4.1 You can just follow the document with mysql 5.7 and 8.0: http://diesel.rs/guides/getting-started

jmqd commented 2 years ago

I recently ran into this using Mysql 8.0.26. The curious thing is that the exact same migration sql, and exact same diesel_cli was previously working correctly with Mysql 5.7. I noticed this particular error only after upgrading my database in place to 8.0.26.

Some info first:

MySQL [information_schema]> select VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.26    |
+-----------+
[ec2-user@ip-REDACTED ~]$ diesel --version
diesel 1.4.1

Tracing backwards from the error location, I see that it uses this to determine the primary keys for each table. After running diesel migration run, and seeing Table goals has no primary key, I decided to open up mysql and re-trace the queries to see what might be different:

Corresponding to lines 149-151, we query from table_constraints looking for constraint_type of PRIMARY KEY.

MySQL [information_schema]>
select * from table_constraints
where
  table_name = 'goals'
  and constraint_type = 'PRIMARY KEY';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | iremono           | PRIMARY         | iremono      | goals      | PRIMARY KEY     | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
1 row in set (0.00 sec)

So far, so good. Note that the rust code is only selecting constraint_name, so we get the value PRIMARY out of that row.

Next, lines 153-156 are fairly straightforward, it's just copying the schema name.

Finally, tracing the final query of key_column_usage:

MySQL [information_schema]>
select column_name
from key_column_usage
where
  constraint_name in (select constraint_name from table_constraints where table_name = 'goals')
  and table_name = 'goals'
  and table_schema = 'iremono'
order by ordinal_position;
+-------------+
| COLUMN_NAME |
+-------------+
| id          |
+-------------+

So, as far as I can tell, all of the data is there! (Even after the CLI printed that error, FWIW.)

At this point, my top candidate place to verify is to check that the match table.schema expression is working as intended, so I will go ahead and triple check that. (Also might end up enabling query history logs and dumping what exact queries diesel was running.)

jmqd commented 2 years ago

I now believe this is possibly a race condition of some sort, potentially with the information_schema being populated?

See here how it is not deterministic:

[ec2-user@ip-REDACTED]$ diesel migration redo
Rolling back migration 2021-11-28-110249_initial
Running migration 2021-11-28-110249_initial
Diesel only supports tables with primary keys. Table goals has no primary key

[ec2-user@ip-REDACTED]$ diesel migration redo
Rolling back migration 2021-11-28-110249_initial
Running migration 2021-11-28-110249_initial

One thing I should note: I'm on ARM.

weiznich commented 2 years ago

@jmqd Thanks for your debugging work :tada:

I think an easy easy way to verify that it is some kind of race condition is to just try using diesel print-schema after diesel emitted the error about the missing primary key. If that turns to be the issue we should raise a bug at the upstream issue tracker. We could try to workaround this issue by inserting a small sleep period so that mysql could update the information_schema behind the scene.

Edit: Reopened this issue, as this seems to be actionable now.

zdannar commented 2 years ago

I seem to be running into this issue. print-schema blows up with primary key issue. I did not have any issues on my dev and staging environment. I created my production database this morning which came up as 8.0.26. (It appears that previous migrations on daily and staging were done on an earlier version. They are now also at 8.0.26, but they do not the issue.). Is there any information I could provide that may help with this issue?

jmqd commented 2 years ago

I seem to be running into this issue. print-schema blows up with primary key issue. I did not have any issues on my dev and staging environment. I created my production database this morning which came up as 8.0.26. (It appears that previous migrations on daily and staging were done on an earlier version. They are now also at 8.0.26, but they do not the issue.). Is there any information I could provide that may help with this issue?

I didn't dig any deeper into this issue, as it was not deterministic, and I was able to unblock myself by blowing up the migration and retrying until it went through.

I believe there are three main possibilities, but I have no leads on which is correct:

  1. There's a bug in MySQL 8.0.26, and probably something to do with how information_schema is populated.
  2. There was a change in MySQL 8.0.26 that altered the contract of how diesel uses MySQL during migrations.
  3. There was a performance or other change in 8.0.26 that surfaced a latent bug in how diesel does MySQL migrations (e.g. exposing a race condition or sloppy parallelism).
jmqd commented 2 years ago

I seem to be running into this issue. print-schema blows up with primary key issue. I did not have any issues on my dev and staging environment. I created my production database this morning which came up as 8.0.26. (It appears that previous migrations on daily and staging were done on an earlier version. They are now also at 8.0.26, but they do not the issue.). Is there any information I could provide that may help with this issue?

@zdannar Are you also on an ARM machine?

zdannar commented 2 years ago

I am not on an ARM machine (Mac Intel and reproducible with linux containers)

zdannar commented 2 years ago

My backtrace is produced while it is checking foreign keys.

weiznich commented 2 years ago

@zdannar If you have a reproducible container that would be really helpful.

Otherwise, I assume that this is caused by a changed way how mysql updates the information_schema. If it's a purely time based thing, that might be fixable by just adding a small amount of "sleep time" in the right place. Otherwise it could require reopening the connection at all. Testing that would require code modifications and trying out a few variants. If someone is interested in that, this would be a good entry point: https://github.com/diesel-rs/diesel/blob/4fe987676e7c465f2fdc116fdf11ba4378e64f9a/diesel_cli/src/main.rs#L69-L73 I would probably just start with adding a std::thread::sleep_ms(1000) there between line 72 and 73.

Other than that I can say that variant 3 from @jmqd's list is quite likely not the cause, as there is just no parallelism there. We execute the migrations one by one in order and afterwards the schema file is regenerated.

zdannar commented 2 years ago

@weiznich The weird part is that the whole schema is there and looks fine. Tables, primary keys, foreign keys and such appear to be completely in order.

weiznich commented 2 years ago

Just to be sure: Your error happens with diesel migration run, right? Not with diesel print-schema itself.

zdannar commented 2 years ago

@weiznich Both.

weiznich commented 2 years ago

Can you provide a backtrace for that? This seems like a different kind of issue.

zdannar commented 2 years ago

RUST_BACKTRACE=full diesel print-schema  101 ✘  15:05:12 thread 'main' panicked at 'Error loading primary keys for order_request: "Diesel only supports tables with primary keys. Table order_request has no primary key"', /Users/zack.dannar/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel_cli-1.4.1/src/infer_schema_internals/foreign_keys.rs:34:72 stack backtrace: 0: 0x10f9c0652 - std::backtrace_rs::backtrace::libunwind::trace::h95efb3c30cc8d3eb at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/../../backtrace/src/backtrace/libunwind.rs:93:5 1: 0x10f9c0652 - std::backtrace_rs::backtrace::trace_unsynchronized::hf55232b46c2b2a5c at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/../../backtrace/src/backtrace/mod.rs:66:5 2: 0x10f9c0652 - std::sys_common::backtrace::_print_fmt::hd000e024c6701252 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/sys_common/backtrace.rs:66:5 3: 0x10f9c0652 - ::fmt::h4cae82d438451481 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/sys_common/backtrace.rs:45:22 4: 0x10f9e274b - core::fmt::write::hb68c3045179d0cad at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/core/src/fmt/mod.rs:1190:17 5: 0x10f9bb24e - std::io::Write::write_fmt::haf84c797e63d79f0 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/io/mod.rs:1657:15 6: 0x10f9c28b0 - std::sys_common::backtrace::_print::h4051b3c764203017 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/sys_common/backtrace.rs:48:5 7: 0x10f9c28b0 - std::sys_common::backtrace::print::h946847bf78a6f565 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/sys_common/backtrace.rs:35:9 8: 0x10f9c28b0 - std::panicking::default_hook::{{closure}}::he18137441e51da1f at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/panicking.rs:295:22 9: 0x10f9c2596 - std::panicking::default_hook::ha3efe84526f027fa at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/panicking.rs:314:9 10: 0x10f9c300d - std::panicking::rust_panic_with_hook::h429a7ddefa5f0258 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/panicking.rs:698:17 11: 0x10f9c2d33 - std::panicking::begin_panic_handler::{{closure}}::h9b033a6b15b84a74 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/panicking.rs:588:13 12: 0x10f9c0ae7 - std::sys_common::backtrace::__rust_end_short_backtrace::hcdd3bec8e0e38aa6 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/sys_common/backtrace.rs:138:18 13: 0x10f9c29fa - rust_begin_unwind at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/panicking.rs:584:5 14: 0x10f9f3db3 - core::panicking::panic_fmt::hf7d6e5207e013f69 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/core/src/panicking.rs:143:14 15: 0x10f9f3ea5 - core::result::unwrap_failed::h95d9e30ede493473 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/core/src/result.rs:1749:5 16: 0x10f850961 - <core::iter::adapters::cloned::Cloned as core::iter::traits::iterator::Iterator>::next::h8696d36ae0f6163c 17: 0x10f84ca56 - <alloc::vec::Vec as alloc::vec::spec_from_iter::SpecFromIter<T,I>>::from_iter::hbb606fc06aa20745 18: 0x10f83cdd6 - diesel::infer_schema_internals::foreign_keys::remove_unsafe_foreign_keys_for_codegen::hbf67757597e97664 19: 0x10f868873 - diesel::print_schema::output_schema::hd4df424745b0c31a 20: 0x10f868478 - diesel::print_schema::run_print_schema::h5b74817cbdefc7ae 21: 0x10f8206bc - diesel::main::h43868340e74887e8 22: 0x10f887616 - std::sys_common::backtrace::__rust_begin_short_backtrace::h11faa1cef74cda35 23: 0x10f842e7c - std::rt::lang_start::{{closure}}::h432435ba236e3e87 24: 0x10f9bfd85 - core::ops::function::impls::<impl core::ops::function::FnOnce for &F>::call_once::h0b34a00ab870ad57 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/core/src/ops/function.rs:259:13 25: 0x10f9bfd85 - std::panicking::try::do_call::hf96b04a014b00830 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/panicking.rs:492:40 26: 0x10f9bfd85 - std::panicking::try::h52320e36f38e53ab at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/panicking.rs:456:19 27: 0x10f9bfd85 - std::panic::catch_unwind::hfb74ccd231e67a94 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/panic.rs:137:14 28: 0x10f9bfd85 - std::rt::lang_start_internal::{{closure}}::hac12182f9ae31dd1 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/rt.rs:128:48 29: 0x10f9bfd85 - std::panicking::try::do_call::h7607cb0e1d9db8ca at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/panicking.rs:492:40 30: 0x10f9bfd85 - std::panicking::try::h5bcafddd15b2a478 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/panicking.rs:456:19 31: 0x10f9bfd85 - std::panic::catch_unwind::h74f42b63c70aad90 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/panic.rs:137:14 32: 0x10f9bfd85 - std::rt::lang_start_internal::hef78d5782ed29805 at /rustc/7737e0b5c4103216d6fd8cf941b7ab9bdbaace7c/library/std/src/rt.rs:128:20 33: 0x10f8254d9 - _main

weiznich commented 2 years ago

So essentially this query is the problem. Can you try to execute the underlying query manually and see what it returns?

zdannar commented 2 years ago

I haven't pulled it all apart yet... If you are just looking at the table in question. ``` select * from table_constraints where table_name = 'order_request' and constraint_type = 'PRIMARY KEY'; +--------------------+-------------------+-----------------+--------------+---------------+-----------------+----------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED | +--------------------+-------------------+-----------------+--------------+---------------+-----------------+----------+ | def | some-api | PRIMARY | some-api | order_request | PRIMARY KEY | YES | +--------------------+-------------------+-----------------+--------------+---------------+-----------------+----------+ 1 row in set (0.07 sec)

mysql> select version(); +---------------+ | version() | +---------------+ | 8.0.26-google | +---------------+ 1 row in set (0.08 sec)```

zdannar commented 2 years ago

A broader look. 


    -> FROM information_schema.table_constraints t
    -> JOIN information_schema.key_column_usage k
    -> USING(constraint_name,table_schema,table_name)
    -> WHERE t.constraint_type in ('FOREIGN KEY', 'PRIMARY KEY', 'UNIQUE')
    ->   AND t.table_schema='SOMEDB';
+----------------------------+-----------------+-----------------+
| TABLE_NAME                 | COLUMN_NAME     | CONSTRAINT_TYPE |
+----------------------------+-----------------+-----------------+
| __diesel_schema_migrations | version         | PRIMARY KEY     |
| credit                     | id              | PRIMARY KEY     |
| order_data                 | id              | PRIMARY KEY     |
| order_data                 | id              | FOREIGN KEY     |
| order_message              | id              | PRIMARY KEY     |
| order_message              | order_status_id | FOREIGN KEY     |
| order_metadata             | id              | PRIMARY KEY     |
| order_metadata             | id              | FOREIGN KEY     |
| order_request              | id              | PRIMARY KEY     |
| order_status               | id              | PRIMARY KEY     |
| order_status               | id              | FOREIGN KEY     |
| vendor                     | id              | PRIMARY KEY     |
| vendor_product             | id              | PRIMARY KEY     |
| vendor_product             | vendor_id       | FOREIGN KEY     |
+----------------------------+-----------------+-----------------+```
zdannar commented 2 years ago

I started trying to compile a local cli and add some statements, but haven't gotten very far on the compilation.

weiznich commented 2 years ago

Can you also provide some content of key_column_usage?

zdannar commented 2 years ago 
*************************** 1. row ***************************
                   TABLE_NAME: __diesel_schema_migrations
                  COLUMN_NAME: version
              CONSTRAINT_TYPE: PRIMARY KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: __diesel_schema_migrations
                  COLUMN_NAME: version
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
*************************** 2. row ***************************
                   TABLE_NAME: credit
                  COLUMN_NAME: id
              CONSTRAINT_TYPE: PRIMARY KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: credit
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
*************************** 3. row ***************************
                   TABLE_NAME: order_data
                  COLUMN_NAME: id
              CONSTRAINT_TYPE: PRIMARY KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: order_data
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
*************************** 4. row ***************************
                   TABLE_NAME: order_data
                  COLUMN_NAME: id
              CONSTRAINT_TYPE: FOREIGN KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: order_data_ibfk_1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: order_data
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: somedb
        REFERENCED_TABLE_NAME: order_request
       REFERENCED_COLUMN_NAME: id
*************************** 5. row ***************************
                   TABLE_NAME: order_message
                  COLUMN_NAME: id
              CONSTRAINT_TYPE: PRIMARY KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: order_message
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
*************************** 6. row ***************************
                   TABLE_NAME: order_message
                  COLUMN_NAME: order_status_id
              CONSTRAINT_TYPE: FOREIGN KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: order_message_ibfk_1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: order_message
                  COLUMN_NAME: order_status_id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: somedb
        REFERENCED_TABLE_NAME: order_status
       REFERENCED_COLUMN_NAME: id
*************************** 7. row ***************************
                   TABLE_NAME: order_metadata
                  COLUMN_NAME: id
              CONSTRAINT_TYPE: PRIMARY KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: order_metadata
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
*************************** 8. row ***************************
                   TABLE_NAME: order_metadata
                  COLUMN_NAME: id
              CONSTRAINT_TYPE: FOREIGN KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: order_metadata_ibfk_1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: order_metadata
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: somedb
        REFERENCED_TABLE_NAME: order_request
       REFERENCED_COLUMN_NAME: id
*************************** 9. row ***************************
                   TABLE_NAME: order_request
                  COLUMN_NAME: id
              CONSTRAINT_TYPE: PRIMARY KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: order_request
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
*************************** 10. row ***************************
                   TABLE_NAME: order_status
                  COLUMN_NAME: id
              CONSTRAINT_TYPE: PRIMARY KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: order_status
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
*************************** 11. row ***************************
                   TABLE_NAME: order_status
                  COLUMN_NAME: id
              CONSTRAINT_TYPE: FOREIGN KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: order_status_ibfk_1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: order_status
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: somedb
        REFERENCED_TABLE_NAME: order_request
       REFERENCED_COLUMN_NAME: id
*************************** 12. row ***************************
                   TABLE_NAME: vendor
                  COLUMN_NAME: id
              CONSTRAINT_TYPE: PRIMARY KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: vendor
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
*************************** 13. row ***************************
                   TABLE_NAME: vendor_product
                  COLUMN_NAME: id
              CONSTRAINT_TYPE: PRIMARY KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: vendor_product
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL
*************************** 14. row ***************************
                   TABLE_NAME: vendor_product
                  COLUMN_NAME: vendor_id
              CONSTRAINT_TYPE: FOREIGN KEY
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: somedb
              CONSTRAINT_NAME: vendor_product_ibfk_1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: somedb
                   TABLE_NAME: vendor_product
                  COLUMN_NAME: vendor_id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: somedb
        REFERENCED_TABLE_NAME: vendor
       REFERENCED_COLUMN_NAME: id
14 rows in set (0.09 sec)
zdannar commented 2 years ago

Sorry, I should have included the select statement for reference.

SELECT t.table_name, k.column_name, t.constraint_type, k.* FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING(constraint_name,table_schema,table_name) WHERE t.constraint_type in ('FOREIGN KEY', 'PRIMARY KEY', 'UNIQUE') AND t.table_schema='somedb'\G

zdannar commented 2 years ago

I checked this output against the same schema in my dev/staging databases.. no difference. I have no idea what is going on.

zdannar commented 2 years ago

I turned on the general_log and got exactly what was being sent. It appears that this is a mysql IN clause issue. (I could have made a mistake though). 

SELECT
    `information_schema`.`key_column_usage`.`column_name`
FROM
    `information_schema`.`key_column_usage`
WHERE
    `information_schema`.`key_column_usage`.`constraint_name` IN (
        SELECT
            `information_schema`.`table_constraints`.`constraint_name`
        FROM
            `information_schema`.`table_constraints`
        WHERE
            `information_schema`.`table_constraints`.`constraint_type` = 'PRIMARY KEY'
    )

    AND `information_schema`.`key_column_usage`.`table_name` = 'order_request'
    AND `information_schema`.`key_column_usage`.`table_schema` = 'iod-api'
    ORDER BY `information_schema`.`key_column_usage`.`ordinal_position`;

mysql> SELECT
    ->     `information_schema`.`key_column_usage`.`column_name`
    -> FROM
    ->     `information_schema`.`key_column_usage`
    -> WHERE
    ->     `information_schema`.`key_column_usage`.`constraint_name` IN (
    ->         SELECT
    ->             `information_schema`.`table_constraints`.`constraint_name`
    ->         FROM
    ->             `information_schema`.`table_constraints`
    ->         WHERE
    ->             `information_schema`.`table_constraints`.`constraint_type` = 'PRIMARY KEY'
    ->     )
    ->
    ->     AND `information_schema`.`key_column_usage`.`table_name` = 'order_request'
    ->     AND `information_schema`.`key_column_usage`.`table_schema` = 'iod-api'
    ->     ORDER BY `information_schema`.`key_column_usage`.`ordinal_position`;
Empty set (0.07 sec)

# Decomposing the subselect
mysql> SELECT
    ->             `information_schema`.`table_constraints`.`constraint_name`
    ->         FROM
    ->             `information_schema`.`table_constraints`
    ->         WHERE
    ->             `information_schema`.`table_constraints`.`constraint_type` = 'PRIMARY KEY';
+-----------------+
| CONSTRAINT_NAME |
+-----------------+
| PRIMARY         |
| PRIMARY         |
| PRIMARY         |
| PRIMARY         |
| .......         | // 111 rows of 'PRIMARY'
| PRIMARY         |
+-----------------+
111 rows in set (0.07 sec)

# Ripping out the subselect and doing a direct replacement.

SELECT
    `information_schema`.`key_column_usage`.`column_name`
FROM
    `information_schema`.`key_column_usage`
WHERE
    `information_schema`.`key_column_usage`.`constraint_name` = "PRIMARY"
    AND `information_schema`.`key_column_usage`.`table_name` = 'order_request'
    AND `information_schema`.`key_column_usage`.`table_schema` = 'iod-api'
    ORDER BY `information_schema`.`key_column_usage`.`ordinal_position`;

mysql> SELECT
    ->     `information_schema`.`key_column_usage`.`column_name`
    -> FROM
    ->     `information_schema`.`key_column_usage`
    -> WHERE
    ->     `information_schema`.`key_column_usage`.`constraint_name` = "PRIMARY"
    ->     AND `information_schema`.`key_column_usage`.`table_name` = 'order_request'
    ->     AND `information_schema`.`key_column_usage`.`table_schema` = 'iod-api'
    ->     ORDER BY `information_schema`.`key_column_usage`.`ordinal_position`;
+-------------+
| COLUMN_NAME |
+-------------+
| id          |
+-------------+
zdannar commented 2 years ago

I actually run into this issue a couple of years ago. Trying to find reference to the issue.

zdannar commented 2 years ago

I am not sure how to change information_schema.rs.::get_primary_keys to execute the query like this. 

SELECT
    `information_schema`.`key_column_usage`.`column_name`
FROM
    `information_schema`.`key_column_usage`
    JOIN `information_schema`.`table_constraints`
    ON `information_schema`.`table_constraints`.`table_name` = `information_schema`.`key_column_usage`.`table_name`
WHERE
    `information_schema`.`key_column_usage`.`table_name` = 'order_request'
    AND `information_schema`.`key_column_usage`.`table_schema` = 'iod-api'
    AND `information_schema`.`table_constraints`.`constraint_type` = 'PRIMARY KEY'
    ORDER BY `information_schema`.`key_column_usage`.`ordinal_position`;
zdannar commented 2 years ago

Regardless, I am going to try rebuilding my database and see if the information schema is somehow corrupted.

weiznich commented 2 years ago

I am not sure how to change information_schema.rs.::get_primary_keys to execute the query like this.

This would require to change this section: https://github.com/diesel-rs/diesel/blob/4fe987676e7c465f2fdc116fdf11ba4378e64f9a/diesel_cli/src/infer_schema_internals/information_schema.rs#L262-L277

to something like this:

key_column_usage::table.inner_join(table_constraints::table.on(table_constraints::constraint_name.eq(key_column_usage::constraint_name)))
    .filter(key_column_usage::table_name.eq(&table.sql_name))
    .filter(key_column_usage::table_schema.eq(schema_name))
    .filter(table_constraints::constraint_type.eq("PRIMARY KEY"))
    .select(key_column_usage::column_name)

(Additionally it is required to adjust the where clause above to make this compile.)

zdannar commented 2 years ago

I rebuilt the database completely and still have the issue. I am having to convert all of my code over to postgres but want to see if I can fix this later tonight.

tyrypyrking commented 1 year ago

I was able to recreate this with existing pg db. You just need to login as a user who lacks privileges. User: reader Role: pg_read_all_data (Note: the role is default and empty) Fixed this by giving him superuser, but will see what minimal requirements can be made.