【Solve】MySqlException (0x80004005): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED

MySql.Data.MySqlClient.MySqlException (0x80004005): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

看起來是權限的問題導致資料寫不進資料庫
查了一下覺得應該是這個問題 support.oracle

剛好拋出 exception 的地方有用到 Isolation Level，感覺應該就是這個問題

var trxOption = new TransactionOptions();
trxOption.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
using (var trxScope = new TransactionScope(TransactionScopeOption.Required, trxOption))
{
    trxScope.Complete();
}

同樣一段程式在 production 是正常的，測試環境才有 exception
嘗試將 Transaction 的 code mark 起來，發現不會拋 exception 了
定案!!!就是使用 Transaction MySQL 權限設定問題導致 exception

MySQL 8+ 查一下設定值

SELECT @@GLOBAL.transaction_isolation;
SELECT @@SESSION.transaction_isolation;

結果 production 跟測試環境設定一樣阿!!! 完全無法理解!!
再查了一下發現是MySQL版本不一樣
測試環境 => 5.5.62-0ubuntu0.14.04.1
production => 8.0.28-0ubuntu0.20.04.3
決定先升級，結果升級成8問題就解決囉~

---

MySQL升级路徑：

MySQL 5.5 => MySQL 5.7 => MySQL 8.0.x
5.7版本貌似多了很多功能，如 Json、Ssl…等等

如果升級後遇到此exception

MySql.Data.MySqlClient.MySqlException (0x80004005): The host 127.0.0.1 does not support SSL connections.

需多加SslMode=none;如下

new MySqlConnection("Database=XXXX;Data Source=127.0.0.1;User Id=root;Password=1234;SslMode=none;")