mysql的engine不同，導致事物復原失敗的問題

標籤：always   bug   default   base   empty   ram   ice   stl   logs   

近期在項目上遇到遇到一個頭疼的問題，前方銷售團隊反饋了一個客戶那邊在建立使用者（save object to DB）報錯了以後，前台展示了錯誤，但是資料庫卻儲存了這條記錄。

接到這個BUG以後，第一時間查看了事物是否正確復原，排查了代碼後發現事物復原成功，並且在我的環境下復原成功，錯誤沒有複現，

這個時候就比較棘手了，客戶的事物復原失敗，但是我們的復原成功，我們的項目使用的是grails架構做的，查詢了grails聲明性事物復原的規則，我們的代碼沒有問題，那我就把問題轉移到了資料庫身上，

要了客戶的資料庫，以SQL的形式把資料庫到出來，和我本地的資料庫表進行比對，在比對的過程中發現了不同之處

  客戶的SQL（mysql）

ENGINE=MyISAM AUTO_INCREMENT=522 DEFAULT CHARSET=utf8

ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8

我們的SQL

發現問題所在，我們用的資料庫引擎是InnoDB，而客戶使用的是MYISAM，查詢這兩種引擎不同之處

https://dev.mysql.com/doc/refman/5.6/en/storage-engines.html

MySQL 5.6 Supported Storage EnginesInnoDB: The default storage engine in MySQL 5.6. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. For more information about InnoDB, see Chapter 14, The InnoDB Storage Engine.MyISAM: These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.Memory: Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.CSV: Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.Archive: These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.Blackhole: The Blackhole storage engine accepts but does not store data, similar to the Unix /dev/null device. Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to slave servers, but the master server does not keep its own copy of the data.NDB (also known as NDBCLUSTER)—This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.

InnoDB支援事物，而MyISAM不支援事物，

 

在次複現錯誤，錯誤複現出來。

 

 

 

解決方式：

修改這張表的engine

alter table user engine=InnoDB

修改後再次測試，事物復原成功。

 

bug解決。

 

為了避免再次建立表的engine是MyISAM引擎，修改mysql預設引擎，在修改之前查看資料庫支援的所有引擎

mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        || MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         || MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         || BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         || MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         || CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         || ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         || PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         || FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

存在InnoDB

在設定檔my.cnf中的 [mysqld] 下面加入default-storage-engine=INNODB 一句，儲存。

重啟mysql伺服器：mysqladmin -u root -p shutdown或者service mysqld restart 登入mysql資料庫，在mysql>提示符下搞入show engines;命令。如果出現 InnoDB |DEFAULT，則表示我們 設定InnoDB為預設引擎成功。

 

mysql的engine不同，導致事物復原失敗的問題