MySQL engine is different, causing things to roll back the problem of failure

Recently encountered a headache in the project, the front sales team feedback a customer over the creation of the user (save object to DB) error, the front desk showed errors, but the database saved this record.

After receiving this bug, the first time to see if things are correctly rolled back, after troubleshooting the code to find that things rolled back successfully, and in my environment the next roll success, error is not reproduced,

This time is more tricky, the customer's things rollback failure, but our rollback success, our project uses the Grails framework to do, query the grails declaration of sex Things rollback rules, our code is not a problem, then I transferred the problem to the database,

To the customer's database, in the form of SQL to the database out, and my local database table to compare, in the process of comparison found the difference

Client's SQL (MySQL)

ENGINE=MyISAM auto_increment=522DEFAULT CHARSET=UTF8

ENGINE=InnoDB auto_increment=DEFAULT CHARSET=UTF8

Our SQL

Find the problem, we use the database engine is InnoDB, and the customer is using MyISAM, query the two engine differences

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

Mysql5.6supported Storage enginesinnodb:thedefaultStorage EngineinchMysql5.6. InnoDB isATransaction-Safe (ACID compliant) storage engine forMySQL that haveCommit,rollback, andCrash-Recovery capabilities toProtectUserData. InnoDB row- LevelLocking (without escalation toCoarser granularity Locks) andOracle-Style consistent nonlocking reads increase multi-UserConcurrency andPerformance. InnoDB StoresUserDatainch ClusteredIndexes toReduce I/O forCommon queries based on PrimaryKeys. toMaintain data integrity, InnoDB also supportsFOREIGN KEYReferential-Integrity constraints. forMore information on InnoDB, see Chapter -, the InnoDB Storage Engine.MyISAM:These tables has a small footprint.Table- LevelLocking limits the performanceinch Read/Write workloads, so it isOften usedinch Read- only or Read-Mostly workloadsinchWeb andData warehousing configurations. Memory:stores AllDatainchRam forFast AccessinchEnvironments that require quick lookups ofNon-Critical data. This engine was formerly known asThe HEAP engine. Its UseCases is decreasing; InnoDB withIts buffer pool memory area provides a general-Purpose andDurable toKeep mostor  AllDatainchMemory andNdbcluster provides fastKey-Value lookups forHugeDistributeddata sets. Csv:its tables is reallytextFiles withComma-SeparatedValues. CSV Tables let you importor DumpDatainchCSV format, toExchange data withScripts andApplications thatRead  andwrite that same format. Because CSV Tables is notIndexed, you typically keep the datainchInnoDB tables during normal operation, and  only  UseCSV tables during the importorexport stage. Archive:these Compact, unindexed tables is intended forStoring andRetrieving large amounts ofSeldom-Referenced historical, archived,orSecurity Audit information. Blackhole:the blackhole Storage Engine accepts but does notStore data, similar toThe Unix/Dev/NULLDevice. Queries alwaysreturnAn emptySet. These tables can be usedinch ReplicationConfigurationswhereDML statements is sent toSlave servers, but the master server does notKeep its own copy ofThe data. NDB (also known asNdbcluster)-thisClustered DatabaseEngine isparticularly suited forApplications that require the highest possible degree ofUptime andAvailability.

InnoDB supports things, and MyISAM does not support things,

Errors are reproduced in the second repetition.

How to resolve:

Modify the engine of this watch

Alter Table user engine=InnoDB

After you modify the test again, the thing rolls back successfully.

Bug resolved.

To avoid creating a table again, the engine is MyISAM, modifying the MySQL default engine to see all the engines supported by the database before modifying it

Mysql>show engines;+--------------------+---------+----------------------------------------------------------------+------------- -+------+------------+|Engine|Support|Comment|Transactions|Xa|Savepoints|+--------------------+---------+----------------------------------------------------------------+------------- -+------+------------+|InnoDB| DEFAULT |Supports transactions, row- LevelLocking and ForeignKeys|YES|YES|YES||Mrg_myisam|YES|Collection ofIdentical MyISAM tables|NO|NO|NO||MEMORY|YES|Hash based, storedinchMemory, useful for TemporaryTables|NO|NO|NO||Blackhole|YES| /Dev/NULLStorage engine (anything you write toIt 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|PerformanceSchema                                             |NO|NO|NO||Federated|NO|Federated MySQL Storage Engine| NULL         | NULL | NULL       |+--------------------+---------+----------------------------------------------------------------+------------- -+------+------------+

Presence InnoDB

Under [mysqld] in configuration file my.cnf, add Default-storage-engine=innodb, save.

Restart MySQL server: mysqladmin-u root-p shutdown or service mysqld restart log in to the MySQL database, enter show mysql> at the engines prompt; If InnoDB appears | Default indicates that we set InnoDB to be successful for the defaults engine.

MySQL engine is different, causing things to roll back failed problems