I'd suggest if possible, use MariaDB 5.5 or MySQL 5.7 and later instead. MySQL 5.6 is very bad performance, I tried to migrate our postgresql to MySQL 5.5/5.6 one month ago. Some of our sql scripts take less than 0.1s in postgresql and MariaDB, but consume more than 100s in MySQL 5.5/5.6 :).
-----邮件原件----- 发件人: Robb Wagoner [mailto:[email protected]] 发送时间: 2017年2月16日 7:09 收件人: [email protected] 主题: Re: MySQL Deadlocks in OfBiz 12.04 It is AWS Aurora variant of MySQL v5.6.10. $ mysql -hofbiz.example.com -e 'select version(), @@aurora_version' +------------+------------------+ | version() | @@aurora_version | +------------+------------------+ | 5.6.10-log | 1.10 | +------------+------------------+ On Wed, Feb 15, 2017 at 3:28 PM Jacques Le Roux < [email protected]> wrote: > Hi Robb, > > Just to be sure, the MySQL version you use >= 5.6.4 ? > > Jacques > > > Le 15/02/2017 à 20:03, Robb Wagoner a écrit : > > We are running OfBiz 12.04 and are using MySQL 5.6 as the RDBMS. We > > are experiencing database deadlocks and would like to be able to add > > MySQL-specific statements to deal with the deadlocks. For example: > `SELECT > > ... FOR UPDATE` on PRIMARY KEYs and/or FOREIGN KEYs in the transactions. > > > > Being new to OfBiz, I've been reviewing the ofbiz-user and ofbiz-dev > > archives, OfBiz Wiki, Packt's *Apache OfBiz Cookbook*, and other > resources > > to learn how we can achieve this. > > I see mentions of deadlocks in the archives and in JIRA issues ( > > https://issues.apache.org/jira/browse/OFBIZ-5065). Nothing > > conclusive or directly actionable on managing/dealing with deadlocks is > > mentioned. > > > > What is the OfBiz-way of dealing with RDBMS deadlocks? > > > > > > *Deadlocking statements:* > > UPDATE SHIPMENT > > UPDATE INVENTORY_ITEM > > UPDATE GL_ACCOUNT_ORGANIZATION > > INSERT INTO ACCTG_TRANS_ENTRY > > > > > > *entityengine.xml datasource element:* <datasource name="localmysql" > > helper-class="org.ofbiz.entity.datasource.GenericHelperDAO" > > field-type-name="mysql" > > check-on-start="true" > > add-missing-on-start="true" > > check-pks-on-start="false" > > check-indices-on-start="false" > > use-foreign-keys="true" > > join-style="ansi-no-parenthesis" > > alias-view-columns="false" > > drop-fk-use-foreign-key-keyword="true" > > table-type="InnoDB" > > character-set="utf8" > > collate="utf8_general_ci"> > > <read-data reader-name="tenant"/> > > <read-data reader-name="seed"/> > > <read-data reader-name="seed-initial"/> > > <read-data reader-name="demo"/> > > <read-data reader-name="ext"/> > > <inline-jdbc > > jdbc-driver="com.mysql.jdbc.Driver" > > jdbc-uri="jdbc:mysql:// > > ofbiz-db.example.com/ofbiz?autoReconnect=true" > > jdbc-username="ofbiz" > > jdbc-password="XXXXpasswordXXXX" > > isolation-level="ReadCommitted" > > pool-minsize="2" > > pool-maxsize="250" > > time-between-eviction-runs-millis="600000"/><!-- > > Please note that at least one person has experienced a problem with > > this value with MySQL > > and had to set it to -1 in order to avoid this issue. > > For more look at > http://markmail.org/thread/5sivpykv7xkl66px > > and http://commons.apache.org/dbcp/configuration.html--> > > <!-- <jndi-jdbc jndi-server-name="localjndi" > > jndi-name="java:/MySqlDataSource" isolation-level="Serializable"/> --> > > </datasource> > > > >
