- Transactions (autocommit, uncommitted work, when a transaction was started, isolation level)
- Temporary tables
- Session variables which affect behaviour
- Other session variables
If you can't, then you will have to deal with reconnection, retry at a higher level. This is complicated and difficult to test.
I'll discuss ways of avoiding these things one at a time:
I'm not advocating shunning transactions completely, just avoiding keeping any transaction state in the connection:
- Keep autocommit on. Execute everything you want in a transaction in a single statement (in MySQL it is possible to delete from several tables in one statement, insert several rows etc).
- Transactional engines will still use transactions if autocommit is on - a single statement becomes a single transaction - you will see a consistent view within that statement - no other thread will see parts of a batch insert, update or delete.
- Non-transactional engines behave the same anyway.
- Alternative: do everything that needs to be in a transaction inside a stored procedure - if these are retried, they will be retried as a whole.
These are best avoided. They also cause problems for statement-level replication.
- Use short-lived permanent tables with unique names instead (perhaps in a separate database which is automatically cleaned up when tables get old)
- Wrap things which need temporary tables inside stored procedures
- Use derived tables instead - in some cases this is sufficient.
Sometimes you can't get away from having to set these variables. Common examples are:
- SET NAMES utf8
- SET SQL_MODE = 'TRADITIONAL'
As these are unavoidable, they always have to be set - but there is a solution - the MYSQL_INIT_COMMAND option. As these commands won't actually have any effect on their own, we can have them automatically re-done when the library reconnects to the database.
The only problem with this approach is that not all client library interfaces allow the setting of this option - but in some cases it can be set in my.cnf (for client libraries which read this)
Other session variables
Quite simply - don't use them. There is no reason I know of for having another session variable.
- Don't select @@LAST_INSERT_ID or last_insert_id() - use your API method to get it instead. An auto-reconnect happening between the insert and the select @@LAST_INSERT_ID will cause it to return zero, which is probably not what you wanted or expected.
- Don't define your own variables - keep them on the client instead (or if you absolutely must store state server-side, put it in a table!)
- It is possible to enable automatic reconnect for greater resilience at the MySQL API level
- To do so safely you must avoid keeping session state in the connection
- Variables which need to be reset on connect can be reset with mysql_init_command