Skip to main content

diesel_async/mysql_like/
mod.rs

1use crate::AsyncMultiConnectionHelper;
2use crate::stmt_cache::{CallbackHelper, QueryFragmentHelper, StatementHelper};
3use crate::{AnsiTransactionManager, AsyncConnection, AsyncConnectionCore, SimpleAsyncConnection};
4use core::marker::PhantomData;
5use diesel::QueryResult;
6use diesel::connection::StrQueryHelper;
7use diesel::connection::statement_cache::{
8    MaybeCached, PreparedStatement, QueryFragmentForCachedStatement, StatementCache,
9};
10use diesel::connection::{CacheSize, Instrumentation};
11use diesel::connection::{DynInstrumentation, InstrumentationEvent};
12use diesel::mysql_like::{MysqlLikeBackend, MysqlType};
13use diesel::query_builder::QueryBuilder;
14use diesel::query_builder::{QueryFragment, QueryId, bind_collector::RawBytesBindCollector};
15use diesel::result::{ConnectionError, ConnectionResult};
16use diesel::sql_types::TypeMetadata;
17use futures_core::Stream;
18use futures_core::future::BoxFuture;
19use futures_core::stream::BoxStream;
20use futures_util::{FutureExt, StreamExt, TryStreamExt};
21use mysql_async::prelude::Queryable;
22use mysql_async::{Opts, OptsBuilder, Statement, UrlError};
23use std::borrow::Cow;
24use std::future::Future;
25
26mod cancel_token;
27mod error_helper;
28mod row;
29mod serialize;
30
31pub use self::cancel_token::MysqlLikeCancelToken;
32use self::error_helper::ErrorHelper;
33use self::row::MysqlLikeRow;
34use self::serialize::ToSqlHelper;
35
36/// A connection to a MySQL or Mariadb database. Connection URLs should be in the form
37/// `mysql://[user[:password]@]host/database_name` or `mariadb://[user[:password]@]host/database_name`
38pub struct AsyncMysqlLikeConnection<DB: MysqlLikeBackend> {
39    conn: mysql_async::Conn,
40    stmt_cache: StatementCache<DB, StatementHelper<Statement>>,
41    transaction_manager: AnsiTransactionManager,
42    instrumentation: DynInstrumentation,
43    stmt_to_free: Vec<mysql_async::Statement>,
44}
45
46/// A trait for backends which implement the MySQL wire protocol. This is implemented for both MySQL and MariaDB,
47/// and can be used when writing async code that is compatible with both backends.
48#[expect(private_bounds)]
49pub trait MysqlLikeAsyncBackend: MysqlLikeBackend
50where
51    Self: Send + Sync + Unpin,
52    Self: UrlHelper,
53{
54}
55
56impl<DB: MysqlLikeBackend> MysqlLikeAsyncBackend for DB
57where
58    DB: Send + Sync + Unpin,
59    DB: UrlHelper,
60{
61}
62
63impl<DB: MysqlLikeAsyncBackend> SimpleAsyncConnection for AsyncMysqlLikeConnection<DB>
64where
65    QueryFragmentHelper: QueryFragmentForCachedStatement<DB>,
66{
67    async fn batch_execute(&mut self, query: &str) -> diesel::QueryResult<()> {
68        self.instrumentation()
69            .on_connection_event(InstrumentationEvent::start_query(&StrQueryHelper::new(
70                query,
71            )));
72        let result = self
73            .conn
74            .query_drop(query)
75            .await
76            .map_err(ErrorHelper::<DB>::new)
77            .map_err(Into::into);
78        self.instrumentation()
79            .on_connection_event(InstrumentationEvent::finish_query(
80                &StrQueryHelper::new(query),
81                result.as_ref().err(),
82            ));
83        result
84    }
85}
86
87const CONNECTION_SETUP_QUERIES: &[&str] = &[
88    "SET time_zone = '+00:00';",
89    "SET character_set_client = 'utf8mb4'",
90    "SET character_set_connection = 'utf8mb4'",
91    "SET character_set_results = 'utf8mb4'",
92];
93
94impl<DB: MysqlLikeAsyncBackend> AsyncConnectionCore for AsyncMysqlLikeConnection<DB>
95where
96    QueryFragmentHelper: QueryFragmentForCachedStatement<DB>,
97{
98    type ExecuteFuture<'conn, 'query> = BoxFuture<'conn, QueryResult<usize>>;
99    type LoadFuture<'conn, 'query> = BoxFuture<'conn, QueryResult<Self::Stream<'conn, 'query>>>;
100    type Stream<'conn, 'query> = BoxStream<'conn, QueryResult<Self::Row<'conn, 'query>>>;
101    type Row<'conn, 'query> = MysqlLikeRow<DB>;
102    type Backend = DB;
103
104    fn load<'conn, 'query, T>(&'conn mut self, source: T) -> Self::LoadFuture<'conn, 'query>
105    where
106        T: diesel::query_builder::AsQuery,
107        T::Query: diesel::query_builder::QueryFragment<Self::Backend>
108            + diesel::query_builder::QueryId
109            + 'query,
110    {
111        self.with_prepared_statement(source.as_query(), |conn, stmt, binds| async move {
112            Ok(Self::poll_result_stream(conn, stmt, binds).await?.boxed())
113        })
114        .boxed()
115    }
116
117    fn execute_returning_count<'conn, 'query, T>(
118        &'conn mut self,
119        source: T,
120    ) -> Self::ExecuteFuture<'conn, 'query>
121    where
122        T: diesel::query_builder::QueryFragment<Self::Backend>
123            + diesel::query_builder::QueryId
124            + 'query,
125    {
126        self.with_prepared_statement(source, |conn, stmt, binds| async move {
127            let params = mysql_async::Params::try_from(binds)?;
128            conn.exec_drop(&stmt.0, params)
129                .await
130                .map_err(ErrorHelper::<DB>::new)?;
131            conn.affected_rows()
132                .try_into()
133                .map_err(|e| diesel::result::Error::DeserializationError(Box::new(e)))
134        })
135    }
136}
137
138impl<DB: MysqlLikeAsyncBackend> AsyncConnection for AsyncMysqlLikeConnection<DB>
139where
140    QueryFragmentHelper: QueryFragmentForCachedStatement<DB>,
141{
142    type TransactionManager = AnsiTransactionManager;
143
144    async fn establish(database_url: &str) -> diesel::ConnectionResult<Self> {
145        let mut instrumentation = DynInstrumentation::default_instrumentation();
146        instrumentation.on_connection_event(InstrumentationEvent::start_establish_connection(
147            database_url,
148        ));
149        let r = Self::establish_connection_inner(database_url).await;
150        instrumentation.on_connection_event(InstrumentationEvent::finish_establish_connection(
151            database_url,
152            r.as_ref().err(),
153        ));
154        let mut conn = r?;
155        conn.instrumentation = instrumentation;
156        Ok(conn)
157    }
158
159    fn transaction_state(&mut self) -> &mut AnsiTransactionManager {
160        &mut self.transaction_manager
161    }
162
163    fn instrumentation(&mut self) -> &mut dyn Instrumentation {
164        &mut *self.instrumentation
165    }
166
167    fn set_instrumentation(&mut self, instrumentation: impl Instrumentation) {
168        self.instrumentation = instrumentation.into();
169    }
170
171    fn set_prepared_statement_cache_size(&mut self, size: CacheSize) {
172        self.stmt_cache.set_cache_size(size);
173    }
174}
175
176#[inline(always)]
177fn update_transaction_manager_status<T>(
178    query_result: QueryResult<T>,
179    transaction_manager: &mut AnsiTransactionManager,
180) -> QueryResult<T> {
181    if let Err(diesel::result::Error::DatabaseError(
182        diesel::result::DatabaseErrorKind::SerializationFailure,
183        _,
184    )) = query_result
185    {
186        transaction_manager
187            .status
188            .set_requires_rollback_maybe_up_to_top_level(true)
189    }
190    query_result
191}
192
193fn prepare_statement_helper<'a, DB: MysqlLikeAsyncBackend>(
194    conn: &'a mut mysql_async::Conn,
195    sql: &str,
196    _is_for_cache: diesel::connection::statement_cache::PrepareForCache,
197    _metadata: &[MysqlType],
198) -> CallbackHelper<
199    impl Future<Output = QueryResult<(StatementHelper<Statement>, &'a mut mysql_async::Conn)>>
200    + Send
201    + use<'a, DB>,
202> {
203    // ideally we wouldn't clone the SQL string here
204    // but as we usually cache statements anyway
205    // this is a fixed one time const
206    //
207    // The problem with not cloning it is that we then cannot express
208    // the right result lifetime anymore (at least not easily)
209    let sql = sql.to_owned();
210    CallbackHelper(async move {
211        let s = conn.prep(sql).await.map_err(ErrorHelper::<DB>::new)?;
212        Ok((StatementHelper(s), conn))
213    })
214}
215
216impl PreparedStatement for StatementHelper<Statement> {
217    fn parameter_count(&self) -> u64 {
218        u64::from(self.0.num_params())
219    }
220}
221
222#[expect(private_bounds)]
223impl<DB: MysqlLikeAsyncBackend> AsyncMysqlLikeConnection<DB>
224where
225    QueryFragmentHelper: QueryFragmentForCachedStatement<DB>,
226{
227    /// Wrap an existing [`mysql_async::Conn`] into a async diesel mysql connection
228    ///
229    /// This function constructs a new `AsyncMysqlConnection<DB>` based on an existing
230    /// [`mysql_async::Conn]`.
231    pub async fn try_from(conn: mysql_async::Conn) -> ConnectionResult<Self> {
232        use crate::run_query_dsl::RunQueryDsl;
233        let mut conn = AsyncMysqlLikeConnection {
234            conn,
235            stmt_cache: StatementCache::new(),
236            transaction_manager: AnsiTransactionManager::default(),
237            instrumentation: DynInstrumentation::default_instrumentation(),
238            stmt_to_free: Vec::new(),
239        };
240
241        for stmt in CONNECTION_SETUP_QUERIES {
242            diesel::sql_query(*stmt)
243                .execute(&mut conn)
244                .await
245                .map_err(ConnectionError::CouldntSetupConfiguration)?;
246        }
247
248        Ok(conn)
249    }
250
251    /// Constructs a cancellation token that can later be used to request cancellation of a query running on the connection associated with this client.
252    pub fn cancel_token(&self) -> MysqlLikeCancelToken<DB> {
253        let kill_id = self.conn.id();
254        let opts = self.conn.opts().clone();
255
256        MysqlLikeCancelToken {
257            kill_id,
258            opts,
259            phantom: PhantomData,
260        }
261    }
262
263    fn with_prepared_statement<'conn, T, F, R>(
264        &'conn mut self,
265        query: T,
266        callback: impl (FnOnce(
267            &'conn mut mysql_async::Conn,
268            MaybeCached<'conn, StatementHelper<Statement>>,
269            ToSqlHelper<DB>,
270        ) -> F)
271        + Send
272        + 'conn,
273    ) -> BoxFuture<'conn, QueryResult<R>>
274    where
275        R: Send + 'conn,
276        T: QueryFragment<DB> + QueryId,
277        F: Future<Output = QueryResult<R>> + Send,
278    {
279        let backend = &DB::default();
280        self.instrumentation()
281            .on_connection_event(InstrumentationEvent::start_query(&diesel::debug_query(
282                &query,
283            )));
284        let mut bind_collector = RawBytesBindCollector::<DB>::new();
285        let bind_collector = query
286            .collect_binds(&mut bind_collector, &mut (), backend)
287            .map(|()| bind_collector);
288
289        let AsyncMysqlLikeConnection {
290            conn,
291            stmt_cache,
292            transaction_manager,
293            instrumentation,
294            stmt_to_free,
295            ..
296        } = self;
297
298        let is_safe_to_cache_prepared = query.is_safe_to_cache_prepared(backend);
299        let mut qb = DB::QueryBuilder::new();
300        let sql = query.to_sql(&mut qb, backend).map(|()| qb.finish());
301        let query_id = T::query_id();
302
303        async move {
304            // We need to close any non-cached statement explicitly here as otherwise
305            // we might error out on too many open statements. See https://github.com/weiznich/diesel_async/issues/26
306            // and https://github.com/weiznich/diesel_async/issues/269 for details
307            //
308            // We remember these statements from the last run as there is currently no reliable way to
309            // run this as destruction step after the execution finished. Users might abort polling the future, etc
310            //
311            // The overhead for this is keeping one additional statement open until the connection is used
312            // next, so you would need to have `max_prepared_stmt_count - 1` other statements open for this to cause issues.
313            // This is hopefully not a problem in practice
314            for stmt in std::mem::take(stmt_to_free) {
315                conn.close(stmt).await.map_err(ErrorHelper::<DB>::new)?;
316            }
317            let RawBytesBindCollector {
318                metadata, binds, ..
319            } = bind_collector?;
320            let is_safe_to_cache_prepared = is_safe_to_cache_prepared?;
321            let sql = sql?;
322            let helper = QueryFragmentHelper {
323                sql,
324                safe_to_cache: is_safe_to_cache_prepared,
325                bind_count: binds.len(),
326            };
327            let inner = async {
328                let (stmt, conn) = stmt_cache
329                    .cached_statement_non_generic(
330                        query_id,
331                        &helper,
332                        &DB::default(),
333                        &metadata,
334                        conn,
335                        prepare_statement_helper::<DB>,
336                        &mut **instrumentation,
337                    )
338                    .await?;
339                // for any not cached statement we need to remember to close them on the next connection usage
340                if let MaybeCached::CannotCache(stmt) = &stmt {
341                    stmt_to_free.push(stmt.0.clone());
342                }
343                callback(
344                    conn,
345                    stmt,
346                    ToSqlHelper::<DB> {
347                        metadata,
348                        binds,
349                        _phantom: PhantomData,
350                    },
351                )
352                .await
353            };
354            let r = update_transaction_manager_status(inner.await, transaction_manager);
355            instrumentation.on_connection_event(InstrumentationEvent::finish_query(
356                &StrQueryHelper::new(&helper.sql),
357                r.as_ref().err(),
358            ));
359            r
360        }
361        .boxed()
362    }
363
364    async fn poll_result_stream<'conn>(
365        conn: &'conn mut mysql_async::Conn,
366        stmt: MaybeCached<'_, StatementHelper<Statement>>,
367        binds: ToSqlHelper<DB>,
368    ) -> QueryResult<impl Stream<Item = QueryResult<MysqlLikeRow<DB>>> + Send + use<'conn, DB>>
369    {
370        let params = mysql_async::Params::try_from(binds)?;
371        let stmt_for_exec = match stmt {
372            MaybeCached::Cached(ref s) => s.0.clone(),
373            MaybeCached::CannotCache(ref s) => s.0.clone(),
374            _ => unreachable!(
375                "Diesel has only two variants here at the time of writing.\n\
376                     If you ever see this error message please open in issue in the diesel-async issue tracker"
377            ),
378        };
379
380        let res = conn
381            .exec_iter(stmt_for_exec, params)
382            .await
383            .map_err(ErrorHelper::<DB>::new)?;
384
385        let stream = res
386            .stream_and_drop::<MysqlLikeRow<DB>>()
387            .await
388            .map_err(ErrorHelper::<DB>::new)?
389            .ok_or_else(|| {
390                diesel::result::Error::DeserializationError(Box::new(
391                    diesel::result::UnexpectedEndOfRow,
392                ))
393            })?
394            .map_err(|e| diesel::result::Error::from(ErrorHelper::<DB>::new(e)));
395
396        Ok(stream)
397    }
398
399    async fn establish_connection_inner(
400        database_url: &str,
401    ) -> Result<AsyncMysqlLikeConnection<DB>, ConnectionError> {
402        let url = DB::check_and_replace_schema(database_url)
403            .map_err(|e| diesel::result::ConnectionError::InvalidConnectionUrl(e.to_string()))?;
404        let opts = Opts::from_url(&url)
405            .map_err(|e| diesel::result::ConnectionError::InvalidConnectionUrl(e.to_string()))?;
406        let builder = OptsBuilder::from_opts(opts)
407            .init(CONNECTION_SETUP_QUERIES.to_vec())
408            .stmt_cache_size(0) // We have our own cache
409            .client_found_rows(true); // This allows a consistent behavior between MariaDB/MySQL and PostgreSQL (and is already set in `diesel`)
410
411        let conn = mysql_async::Conn::new(builder)
412            .await
413            .map_err(ErrorHelper::<DB>::new)?;
414
415        Ok(AsyncMysqlLikeConnection {
416            conn,
417            stmt_cache: StatementCache::new(),
418            transaction_manager: AnsiTransactionManager::default(),
419            instrumentation: DynInstrumentation::none(),
420            stmt_to_free: Vec::new(),
421        })
422    }
423}
424
425pub(crate) trait UrlHelper: MysqlLikeBackend {
426    fn check_and_replace_schema<'a>(url: &'a str) -> Result<Cow<'a, str>, UrlError>;
427}
428
429impl<DB: MysqlLikeAsyncBackend> AsyncMultiConnectionHelper for AsyncMysqlLikeConnection<DB>
430where
431    QueryFragmentHelper: QueryFragmentForCachedStatement<DB>,
432{
433    fn to_any<'a>(
434        lookup: &mut <Self::Backend as TypeMetadata>::MetadataLookup,
435    ) -> &mut (dyn std::any::Any + 'a) {
436        lookup
437    }
438
439    fn from_any(
440        lookup: &mut dyn std::any::Any,
441    ) -> Option<&mut <Self::Backend as diesel::sql_types::TypeMetadata>::MetadataLookup> {
442        lookup.downcast_mut()
443    }
444}
445
446#[cfg(any(
447    feature = "deadpool",
448    feature = "bb8",
449    feature = "mobc",
450    feature = "r2d2"
451))]
452impl<DB: MysqlLikeAsyncBackend> crate::pooled_connection::PoolableConnection
453    for AsyncMysqlLikeConnection<DB>
454where
455    QueryFragmentHelper: QueryFragmentForCachedStatement<DB>,
456{
457    fn is_broken(&mut self) -> bool {
458        use crate::TransactionManager;
459
460        // mysql_async marks a connection as disconnected while a command is in
461        // flight and clears the flag once the command completes. A command
462        // dropped midway (for example by a timeout) leaves it set, and its
463        // reply may still arrive on the connection, so it must not be reused.
464        Self::TransactionManager::is_broken_transaction_manager(self) || self.conn.is_disconnected()
465    }
466}
467
468#[cfg(test)]
469mod tests {
470    use crate::RunQueryDsl;
471    mod diesel_async {
472        pub use crate::*;
473    }
474    include!("../doctest_setup.rs");
475
476    const STMT_COUNT: usize = 16382 + 1000;
477
478    #[derive(Queryable)]
479    #[expect(dead_code, reason = "used for the test as loading target")]
480    struct User {
481        id: i32,
482        name: String,
483    }
484
485    #[tokio::test]
486    async fn check_cached_statements_are_dropped() {
487        use self::schema::users;
488
489        let mut conn = establish_connection().await;
490
491        for _i in 0..STMT_COUNT {
492            users::table
493                .select(users::id)
494                .load::<i32>(&mut conn)
495                .await
496                .unwrap();
497        }
498    }
499
500    #[tokio::test]
501    async fn check_uncached_statements_are_dropped() {
502        use self::schema::users;
503
504        let mut conn = establish_connection().await;
505
506        for _i in 0..STMT_COUNT {
507            users::table
508                .filter(users::dsl::id.eq_any(&[1, 2]))
509                .load::<User>(&mut conn)
510                .await
511                .unwrap();
512        }
513    }
514
515    #[tokio::test]
516    async fn check_cached_statements_are_dropped_get_result() {
517        use self::schema::users;
518        use diesel::OptionalExtension;
519
520        let mut conn = establish_connection().await;
521
522        for _i in 0..STMT_COUNT {
523            users::table
524                .select(users::id)
525                .get_result::<i32>(&mut conn)
526                .await
527                .optional()
528                .unwrap();
529        }
530    }
531
532    #[tokio::test]
533    async fn check_uncached_statements_are_dropped_get_result() {
534        use self::schema::users;
535        use diesel::OptionalExtension;
536
537        let mut conn = establish_connection().await;
538
539        for _i in 0..STMT_COUNT {
540            users::table
541                .filter(users::dsl::id.eq_any(&[1, 2]))
542                .get_result::<User>(&mut conn)
543                .await
544                .optional()
545                .unwrap();
546        }
547    }
548}