Rework connection loss handling in SPMySQL, particularly to improve background loss of connections:

 - Attempt to fix a condition causing a reconnection loop by fixing the order of connection state check and a query variable
 - If a connection is lost in the background, only attempt a single reconnect instead of requiring user intervention at once
 - Add a new connection state to handle background disconnects
 - If the connection has been lost in the background but is about to be used, reconnect it automatically (informing the user of loss if appropriate)
 - Don't attempt background reconnections if the connection has not been used for some time
(Also update localisable strings, and tweak navigator controller connection usage)

4 files changed, 30 insertions, 5 deletions

diff --git a/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Delegate & Proxy.m b/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Delegate & Proxy.m
index 59e78c35..e0b745e0 100644
--- a/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Delegate & Proxy.m
+++ b/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Delegate & Proxy.m
@@ -109,8 +109,19 @@
 		// Clear the state change selector on the proxy until a connection is re-established
 		proxyStateChangeNotificationsIgnored = YES;
 
-		// Trigger a reconnect
-		[NSThread detachNewThreadSelector:@selector(reconnect) toTarget:self withObject:nil];
+		// Trigger a reconnect depending on connection usage recently.  If the connection has
+		// actively been used in the last couple of minutes, trigger a full reconnection attempt.
+		if (_elapsedSecondsSinceAbsoluteTime(lastConnectionUsedTime) < 60 * 2) {
+			[NSThread detachNewThreadSelector:@selector(reconnect) toTarget:self withObject:nil];
+		
+		// If used within the last fifteen minutes, trigger a background/single reconnection attempt
+		} else if (_elapsedSecondsSinceAbsoluteTime(lastConnectionUsedTime) < 60 * 15) {
+			[NSThread detachNewThreadSelector:@selector(_reconnectAfterBackgroundConnectionLoss) toTarget:self withObject:nil];
+
+		// Otherwise set the state to connection lost for automatic reconnect on next use
+		} else {
+			state = SPMySQLConnectionLostInBackground;
+		}
 	}
 
 	// Update the state record
diff --git a/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Ping & KeepAlive.m b/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Ping & KeepAlive.m
index 3201b55f..9a28bf57 100644
--- a/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Ping & KeepAlive.m
+++ b/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Ping & KeepAlive.m
@@ -32,6 +32,7 @@
 
 
 #import "Ping & KeepAlive.h"
+#import "SPMySQL Private APIs.h"
 #import "Locking.h"
 #import <pthread.h>
 
@@ -81,9 +82,21 @@
 - (void)_threadedKeepAlive
 {
 
-	// If the maximum number of ping failures has been reached, trigger a reconnect
+	// If the maximum number of ping failures has been reached, determine whether to reconnect.
 	if (keepAliveLastPingBlocked || keepAlivePingFailures >= 3) {
-		[self reconnect];
+
+		// If the connection has been used within the last fifteen minutes,
+		// attempt a single reconnection in the background
+		if (_elapsedSecondsSinceAbsoluteTime(lastConnectionUsedTime) < 60 * 15) {
+			[self _reconnectAfterBackgroundConnectionLoss];
+
+		// Otherwise set the state to connection lost for automatic reconnect on
+		// next use.
+		} else {
+			state = SPMySQLConnectionLostInBackground;
+		}
+
+		// Return as no further ping action required this cycle.
 		return;
 	}
 
diff --git a/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Querying & Preparation.m b/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Querying & Preparation.m
index 46615cae..0af0684a 100644
--- a/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Querying & Preparation.m
+++ b/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Querying & Preparation.m
@@ -235,7 +235,7 @@
 	// Ensure per-thread variables are set up
 	[self _validateThreadSetup];
 
-	// Check the connection if necessary, returning nil if the query couldn't be validated
+	// Check the connection if necessary, returning nil if the state couldn't be validated
 	if (![self _checkConnectionIfNecessary]) return nil;
 
 	// Determine whether a maximum query size needs to be restored from a previous query
diff --git a/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Server Info.m b/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Server Info.m
index 1022ccd1..59a384a9 100644
--- a/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Server Info.m
+++ b/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Server Info.m
@@ -142,6 +142,7 @@
 
 	// Get the process list
 	MYSQL_RES *mysqlResult = mysql_list_processes(mySQLConnection);
+	lastConnectionUsedTime = mach_absolute_time();
 
 	// Convert to SPMySQLResult
 	SPMySQLResult *theResult = [[SPMySQLResult alloc] initWithMySQLResult:mysqlResult stringEncoding:stringEncoding];