Support database transactions and replication restrictions

[KT Walrus]

I noticed today that you are working on IPS 4.2. One of the new features I'd like to see is to require the use of INNODB engine and group requests within SQL transactions. So, if a user post updates more than one table, all the updates for saving the post to the database are done in one transaction. If some update fails in the transaction, the entire transaction should rolled back. This means that the transaction only changes the underlying database tables if the transaction is COMMIT'ed at the end of the transaction.

Along the same lines, many MySQL replication tools require all rows updated to have a primary key (so replication can do certification checks before committing a transaction to a clustered database). It would be nice if IPS 4.2 made sure that all tables have primary key (last I checked, there were a few that didn't have a primary key defined for the table).

Also, IPS 4.2 should handle gracefully COMMIT returning an error. Using MySQL 5.7.17 Group Replication (just went GA) or Galera Cluster, COMMITs may fail due to a commit conflict. Sometimes, the proper thing for the PHP to do is to retry the transaction (maybe waiting a few seconds between retries) as the failure may be transient. If the transaction fails to commit after several retries, the user should be informed that there was a problem adding the post that may be intermittent and the user should try again later. Hopefully, the changes made for the post will still be stored/restored when the user later returns to the form and submits it again.

Basically, I'm suggesting that you spend a little development effort to make IPS 4.2 transaction safe and friendly to the end user is using some HA MySQL setup (like Percona/Galera Cluster w/ ProxySQL or the new Group Replication plugin with a proxy in front of the Group).