Dropping MySQL support?

[m4tx]

Recently, when working on #458, I discovered that our current ORM code doesn't really work with MySQL. It turns out that our Session DB backend fails to run under MySQL because it specifies a TEXT UNIQUE column as a session key, which is unsupported on MySQL. The same exact model works just fine under PostgreSQL, SQLite, as well as a popular fork of MySQL, MariaDB.

This happened because our test framework have used MariaDB instead of MySQL. In MariaDB, there is no such limitation and users are free to define TEXT UNIQUE columns.

There are several options to solve this problem:

1. change the session item model to use LimitedString instead of String
2. change the MySQL ORM backend so that when it sees TEXT UNIQUE, it internally changes it to something like STRING(255) UNIQUE instead
3. continue using MariaDB and drop support for MySQL completely

Option 1 is backwards-incompatible (this probably doesn't hurt us much), but in my opinion, the bigger problem is that we drive the API by one of the implementations. Other ORM backends are not limited by this, so it seems pointless to enforce this on them.

Option 2 sounds like silent limitations that will only expose themselves in runtime. Users might be surprised that they are able to use long session entry keys in other database engines, whereas the same session keys will fail in MySQL. What's even worse, the same thing will happen with their own models that have a unique String field.

Option 3 sounds like something I'm personally very willing to do. Supporting MySQL has been a pain before - for instance, our bulk_insert code is essentially broken right now on MySQL, because it needs to use a separate SELECT query after INSERT since MySQL doesn't support the RETURNING clause (again, something that has been supported in MariaDB for a while):

cot/cot/src/db.rs

Lines 1666 to 1701 in ae9de54

	// MySQL: Use LAST_INSERT_ID() and fetch rows
	let result = self.execute_statement(&insert_statement).await?;
	let first_id = result.last_inserted_row_id.ok_or_else(|| {
	DatabaseError::BulkInsertReturnDataInvalid {
	expected: chunk.len(),
	actual: 0,
	}
	})?;
	// Fetch the inserted rows using a SELECT query
	// Note: This assumes IDs are consecutive, which is generally safe for
	// auto_increment but could fail with concurrent inserts
	let query = sea_query::Query::select()
	.from(T::TABLE_NAME)
	.columns(auto_col_identifiers.iter().cloned())
	.and_where(
	sea_query::Expr::col(T::PRIMARY_KEY_NAME).gte(first_id).and(
	sea_query::Expr::col(T::PRIMARY_KEY_NAME).lt(first_id
	+ <u64 as TryFrom<usize>>::try_from(chunk.len())
	.expect("chunk length fits in u64")),
	),
	)
	.order_by(T::PRIMARY_KEY_NAME, sea_query::Order::Asc)
	.to_owned();
	let rows = self.fetch_all(&query).await?;
	if rows.len() != chunk.len() {
	return Err(DatabaseError::BulkInsertReturnDataInvalid {
	expected: chunk.len(),
	actual: rows.len(),
	});
	}
	for (instance, row) in chunk.iter_mut().zip(rows) {
	instance.update_from_db(row, auto_col_ids)?;
	}

MariaDB has been around for quite a while, is a much more powerful database engine, and it seems like it's popular, too - for instance, it's used on the majority of WordPress installations.

Since our time is limited and so far nobody has even complained about broken MySQL support, I would suggest us to drop the support for it and focus on MariaDB instead.