diesel_cli print-schema: Failed to execute a database query: Error deserializing field 'character_maximum_length': could not convert slice to array

[devindependent]

Setup

Table:

CREATE TABLE transaction (
id mediumint(9) NOT NULL AUTO_INCREMENT,
date date NOT NULL,
account char(32) NOT NULL,
symbol char(32) NOT NULL,
security char(128) NOT NULL,
action char(32) NOT NULL,
memo char(128) DEFAULT NULL,
price decimal(14,6) DEFAULT NULL,
adj_price decimal(14,6) DEFAULT NULL,
shares decimal(14,6) DEFAULT NULL,
adj_shares decimal(14,6) DEFAULT NULL,
amount decimal(10,2) NOT NULL,
cost_basis decimal(10,2) DEFAULT NULL,
type char(16) NOT NULL,
dt datetime NOT NULL,
PRIMARY KEY (id),
KEY fk_action_type (action,type),
CONSTRAINT fk_action_type FOREIGN KEY (action, type) REFERENCES action_types (action, type)
)

Versions

• Rust: 1.87.0
• Diesel: 2.2.10
• Database: MariaDB: 10.6.22
• Operating System Linux Mint 21.3

Feature Flags

• diesel: mysql

Problem Description

Running: diesel print-schema

gives the following error with the above table:

"Failed to execute a database query: Error deserializing field 'character_maximum_length': could not convert slice to array"

What are you trying to accomplish?

Generate a schema file from a single table in an existing MariaDB database. Note that this worked on earlier iterations of the table structure and versions of all software involved.

What is the expected output?

No errors and a 'schema.rs' file with the schema for the 'transaction' table.

What is the actual output?

"Failed to execute a database query: Error deserializing field 'character_maximum_length': could not convert slice to array"

Are you seeing any additional errors?

I was able to instrument the source with some print! statements and get the more specific error of:

QueryError(DeserializationError(DeserializeFieldError { field_name: Some("character_maximum_length"), error: TryFromSliceError(()) }))

Additionally from print! debugging, the query in question appears to be:

SELECT information_schema.columns.column_name, information_schema.columns.column_type, ?, information_schema.columns.is_nullable, information_schema.columns.character_maximum_length, NULLIF(information_schema.columns.column_comment, ?) FROM information_schema.columns WHERE ((information_schema.columns.table_name = ?) AND (information_schema.columns.table_schema = ?)) ORDER BY information_schema.columns.ordinal_position -- binds: [None, "", "transaction", "investments"]

Steps to reproduce

Run: diesel print-schema against the 'transaction' table above. Note that this shouldn't require any data in the transaction table itself as the failing query goes against an information schema table.

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

• This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)
• This issue can be reproduced without requiring a third party crate

[weiznich]

Thanks for opening this report. I've tried to reproduce this locally with the same mariadb version installed and with the provided table definition. I cannot trigger the described error, therefore that might be a system specific issue. So from a formal point of view this seems to work as expected, therefore I converted this to a support thread in the discussion forum.

As for fixing the actual issue: It's hard to help you there without having access to the exact state your system is in. You can try to execute the query printed by the debug logging and provide the result. Maybe there is something useful in there, maybe not.

[devindependent]

Thanks for the reply.

I included the output of the query at the end of this post. Last night I discovered this is not only a problem when running diesel_cli, but it is also breaking an existing unit test I have for a Tauri app, and the app itself when I perform certain queries. The unit tests query the 'transaction' table itself, not the information schema. Strange that the other four tests that hit the exact same table with similar parameters work just fine. I'll try to see what is "special" about the failing unit test and associated query results.

Failing that, I'll rebuild my DB and wipe out my cargo packages and try again.

In the meantime, here's the stack trace from the failing unit test:

running 5 tests
test tests::test_transactions_bad_date_order ... ok

thread 'tests::test_transactions_summaries_all_dates' panicked at src/dao.rs:92:10:
Internal Database Error loading transactions: DeserializationError(DeserializeFieldError { field_name: Some("cost_basis"), error: TryFromSliceError(()) })
stack backtrace:
test tests::test_transactions_le_end_date ... ok
test tests::test_transactions_between_dates ... ok
test tests::test_transactions_ge_start_date ... ok
0: __rustc::rust_begin_unwind
at /rustc/17067e9ac6d7ecb70e50f92c1944e545188d2359/library/std/src/panicking.rs:697:5
1: core::panicking::panic_fmt
at /rustc/17067e9ac6d7ecb70e50f92c1944e545188d2359/library/core/src/panicking.rs:75:14
2: core::result::unwrap_failed
at /rustc/17067e9ac6d7ecb70e50f92c1944e545188d2359/library/core/src/result.rs:1704:5
3: core::result::Result<T,E>::expect
at /rustc/17067e9ac6d7ecb70e50f92c1944e545188d2359/library/core/src/result.rs:1061:23
4: investmentsui_lib::dao::get_transactions
at ./src/dao.rs:85:19
5: investmentsui_lib::get_transactions_summaries
at ./src/lib.rs:42:15
6: investmentsui_lib::tests::test_transactions_summaries_all_dates
at ./src/lib.rs:125:28
7: investmentsui_lib::tests::test_transactions_summaries_all_dates::{{closure}}
at ./src/lib.rs:119:47
8: core::ops::function::FnOnce::call_once
at /rustc/17067e9ac6d7ecb70e50f92c1944e545188d2359/library/core/src/ops/function.rs:250:5
9: core::ops::function::FnOnce::call_once
at /rustc/17067e9ac6d7ecb70e50f92c1944e545188d2359/library/core/src/ops/function.rs:250:5
note: Some details are omitted, run with RUST_BACKTRACE=full for a verbose backtrace.
test tests::test_transactions_summaries_all_dates ... FAILED

Query output:

MariaDB [investments]> SELECT information_schema.columns.column_name, information_schema.columns.column_type, NULL, information_schema.columns.is_nullable, information_schema.columns.character_maximum_length, NULLIF(information_schema.columns.column_comment, "") FROM information_schema.columns WHERE ((information_schema.columns.table_name = "transaction") AND (information_schema.columns.table_schema = "investments")) ORDER BY information_schema.columns.ordinal_position;
+-------------+---------------+------+-------------+--------------------------+-------------------------------------------------------------+
| column_name | column_type | NULL | is_nullable | character_maximum_length | NULLIF(information_schema.columns.column_comment, "") |
+-------------+---------------+------+-------------+--------------------------+-------------------------------------------------------------+
| id | mediumint(9) | NULL | NO | NULL | NULL |
| date | date | NULL | NO | NULL | NULL |
| account | char(32) | NULL | NO | 32 | NULL |
| symbol | char(32) | NULL | NO | 32 | NULL |
| security | char(128) | NULL | NO | 128 | NULL |
| action | char(32) | NULL | NO | 32 | NULL |
| memo | char(128) | NULL | YES | 128 | NULL |
| price | decimal(14,6) | NULL | YES | NULL | NULL |
| adj_price | decimal(14,6) | NULL | YES | NULL | NULL |
| shares | decimal(14,6) | NULL | YES | NULL | NULL |
| adj_shares | decimal(14,6) | NULL | YES | NULL | NULL |
| amount | decimal(10,2) | NULL | NO | NULL | NULL |
| cost_basis | decimal(10,2) | NULL | YES | NULL | NULL |
| type | char(16) | NULL | NO | 16 | NULL |
| dt | datetime | NULL | NO | NULL | NULL |
+-------------+---------------+------+-------------+--------------------------+-------------------------------------------------------------+

[devindependent]

For anyone who stumbles upon this I was able to resolve the unit test and Tauri app issues by reinstalling all the relevant software, and completely rebuilding my Maria DB instance and restoring from a backup script. See below for an overview of the steps:

Strangely after all that I still had the same problem with diesel print-schema using a fresh install of diesel_cli installed using the script method.

This suggests the root issue is/was not related to the database (software or data) at all, but something with my copy of diesel and/or its mysql integration.

I finally resolved the diesel print-schemaissue by deleting the script-installed copy of diesel from ~/.cargo/bin and installing via the cargo install diesel_cli --no-default-features --features mysql method.

Re-installation / Rebuild Overview:

• Cleared all relevant project cargo caches with cargo clear
• Uninstalled diesel CLI with cargo uninstall diesel_cli
• Completely purged the entire MariaDB database, MariaDB packages, mysqlclient etc. packages with apt purge
• Reboot
• Reinstalled MariaDB, mysqlclient, etc. packages with apt install
• Setup MariaDB and created a new 'investments' schema
• Rebuilt/repopulated tables and data from a backup
• Reinstalled diesel using cargo install diesel_cli --no-default-features --features mysql` (the copy installed via script method was still broken per the above)

[nmlt]

I also ran into this issue (print-schema not working with that exact error message) after changing my migration script. I renamed a table and its primary key, which is also a foreign key in another table. I also use MariaDB but on MacOS 15.5 (aarch64). I also updated MariaDB recently to version 11.8.2 (from 11.7.2), so maybe I only now run into this issue.

• Reinstalling diesel_cli (not via the script) and cleaning the cargo project did not help so far.
• Reinstalling mariadb (and deleting some leftover files in /opt/homebrew's subdirectories) and also the above did not help.
• I instead installed mysql (9.3), now it works.

I wish I could use Postgres.

[weiznich]

It would be very helpful if you could help pin down this to a specific combination of mariadb server versions + mysqlclient/mariadbclient (linked by diesel) versions. This could help me reproducing the issue.

[xanonid]

I could reproduce the issue on a similar system as the original reporter. There seems to be an issue with NULL value detection in the diesel code if the MariaDB client library and/or MariaDB is used.

In the particular case, character_maximum_length can result in MysqlValue { raw: [], tpe: UnsignedLongLong } which seems to be an illegal value and accordingly fails to be de-serialized. It probably should have been detected previously as NULL value and handled differently from the beginning.

If corresponding code is changed to

#[cfg(feature = "mysql_backend")]
impl FromSql<Unsigned<BigInt>, Mysql> for u64 {
    #[allow(
        clippy::cast_possible_wrap,
        clippy::cast_sign_loss,
        clippy::cast_possible_truncation
    )]
    fn from_sql(bytes: MysqlValue<'_>) -> deserialize::Result<Self> {
        // should never happen, but seems to be the case with mariadb
        if bytes.as_bytes().is_empty() {
            return Ok(0); // wrong as it maps NULL to 0, but fixes print-schema
        }
        let signed: i64 = FromSql::<BigInt, Mysql>::from_sql(bytes)?;
        Ok(signed as u64)
    }
}

diesel print-schema works again. Of course, the underlying issue needs to be found and fixed instead of this (for other purposes wrong) workaround.

[weiznich]

This still raises the question why this is not marked as null in the first place. The relevant underlying diesel code to construct the MysqlValue wrapper is here:

diesel/diesel/src/mysql/connection/bind.rs

Lines 407 to 428 in 7629365

	pub(super) fn value(&'_ self) -> Option<MysqlValue<'_>> {
	if self.is_null() {
	None
	} else {
	let data = self.bytes?;
	let tpe = (self.tpe, self.flags).into();
	let slice = unsafe {
	// We know that this points to a slice and the pointer is not null at this
	// location
	// The length pointer is valid as long as none missuses `bind_for_truncated_data`
	// as this is the only location that updates the length field before the corresponding data are
	// written. At the time of writing this comment, the `BindData::bind_for_truncated_data`
	// function is only called by `Binds::populate_dynamic_buffers` which ensures the corresponding
	// invariant.
	std::slice::from_raw_parts(
	data.as_ptr(),
	self.length.try_into().expect("Usize is at least 32 bit"),
	)
	};
	Some(MysqlValue::new_internal(slice, tpe))
	}
	}

The is_null() function essentially only checks a flag set by libmysqlclient, so there is not much in diesel that can go wrong there. Given that I cannot reproduce this issue at all I would either need a description to a complete reproducible environment (e.g. a docker image or similar) or someone else needs to debug this.

Now there are several possibilities:

• For some reason the server stopped writing this field, which would be a bug and breaking change in mariadb and should be reported there
• The linked client library change, e.g by using a different layout for the return type, etc. Given that I have no information at all which mysqlclient versions are actually used on your system it's hard to tell if that's the case or not

Finally given the amount of issue we recently have with such kind of repeatedly breaking changes in mysql/mariadb + I personally do not even use this part of diesel I consider deprecating the the mysql backend if noone that actually uses that backend steps up helping maintaining it.

[xanonid]

The issue could be reproduced by using a Ubuntu Noble or Ubuntu Jammy Docker file:

docker run -it --rm ubuntu:noble
# inside the container
apt-get update
apt-get install -y mariadb-server libmariadb-dev-compat curl build-essential libicu-dev libpq-dev libsqlite3-dev
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh -s -- --profile minimal -y
source "$HOME/.cargo/env"
cargo install diesel_cli
mariadbd-safe &
sleep 2 # wait until mariadb is ready
diesel print-schema --database-url=mysql://localhost/sys

Interestingly, on Debian it seems to run fine.

v2.2.11 is affected, but if I try diesel from current master, it works fine. According to my tries with bisecting, 77ca1a3 might have fixed it (using cargo build --features mysql --no-default-features as build command).

The bug does not seem to be in diesel code, but in the mysql/mariadb driver or in the bindings to it. When the bug occurs for the NULL value, self.is_null() is false, whereas self.length is zero which causes then the trouble. A workaround could be to map number fields with zero length to NULL.

[weiznich]

Thanks for tracking this down ❤️ . That's really helpful.

According to my tries with bisecting, 77ca1a3 might have fixed it (using cargo build --features mysql --no-default-features as build command).

That's unfortunately not really a fix per se. What that commit does is enabling the bundled feature of the mysqlclient-sys crate which results in not using the libmsqlclient version provided by the operating system. That fixes the issues given that this is a problem in exactly that library, but it's more a workaround than a proper fix given that there are situations where you want to use the library provided by the operating system. The good news is that you can have this fix today by just building diesel-cli via cargo install diesel-cli --features "mysql-bundled"

When the bug occurs for the NULL value, self.is_null() is false, whereas self.length is zero which causes then the trouble. A workaround could be to map number fields with zero length to NULL.

That sounds reasonable, mind submitting a PR for that?