Restore detailed error messages for FK errors in MySQL 5.5+

3 files changed, 54 insertions, 10 deletions

diff --git a/Source/SPServerSupport.h b/Source/SPServerSupport.h
index 8052ed90..e4551643 100644
--- a/Source/SPServerSupport.h
+++ b/Source/SPServerSupport.h
@@ -28,6 +28,11 @@
 //
 //  More info at <https://github.com/sequelpro/sequelpro>
 
+typedef struct {
+	NSString *queryString;
+	NSUInteger columnIndex;
+} SPInnoDBStatusQueryFormat;
+
 /**
  * @class SPServerSupport SPServerSupport.h
  *
@@ -79,6 +84,7 @@
 	BOOL supportsArchiveStorageEngine;
 	BOOL supportsCSVStorageEngine;
 	BOOL supportsQuotingEngineTypeInCreateSyntax;
+	BOOL supportsShowEngine;
 	
 	// Triggers
 	BOOL supportsTriggers;
@@ -259,9 +265,19 @@
  */
 @property (readonly) BOOL supportsFulltextOnInnoDB;
 
+/**
+ * @property supportsShowEngine Indicates whether the server supports the "SHOW ENGINE x {LOGS|STATUS}" query.
+ */
+@property (readonly) BOOL supportsShowEngine;
+
 - (id)initWithMajorVersion:(NSInteger)majorVersion minor:(NSInteger)minorVersion release:(NSInteger)releaseVersion;
 
 - (void)evaluate;
 - (BOOL)isEqualToOrGreaterThanMajorVersion:(NSInteger)majorVersion minor:(NSInteger)minorVersion release:(NSInteger)releaseVersion;
 
+/**
+ * @return The correct query to get the InnoDB engine status. queryString is nil for unsupported versions.
+ *         The columnIndex tells the index of the column (starting with 0) in which the status text is returned.
+ */
+- (SPInnoDBStatusQueryFormat)innoDBStatusQuery;
 @end
diff --git a/Source/SPServerSupport.m b/Source/SPServerSupport.m
index 5537e476..489acc04 100644
--- a/Source/SPServerSupport.m
+++ b/Source/SPServerSupport.m
@@ -77,6 +77,7 @@
 @synthesize serverMinorVersion;
 @synthesize serverReleaseVersion;
 @synthesize supportsFulltextOnInnoDB;
+@synthesize supportsShowEngine;
 
 #pragma mark -
 #pragma mark Initialisation
@@ -200,6 +201,28 @@
 	// Fractional second support wasn't added until MySQL 5.6.4
 	supportsFractionalSeconds = [self isEqualToOrGreaterThanMajorVersion:5 minor:6 release:4];
 	supportsFulltextOnInnoDB  = supportsFractionalSeconds; //introduced in 5.6.4 too
+	
+	// The SHOW ENGINE query wasn't added until MySQL 4.1.2
+	supportsShowEngine = [self isEqualToOrGreaterThanMajorVersion:4 minor:1 release:2];
+}
+
+- (SPInnoDBStatusQueryFormat)innoDBStatusQuery
+{
+	SPInnoDBStatusQueryFormat tuple = {nil,0};
+	
+	//if we have SHOW ENGINE go with that
+	if(supportsShowEngine) {
+		tuple.queryString = @"SHOW ENGINE INNODB STATUS";
+		tuple.columnIndex = 2;
+	}
+	//up to mysql 5.5 we could also use the old SHOW INNODB STATUS
+	if([self isEqualToOrGreaterThanMajorVersion:3 minor:23 release:52] &&
+	   ![self isEqualToOrGreaterThanMajorVersion:5 minor:5 release:0]) {
+		tuple.queryString = @"SHOW INNODB STATUS";
+		tuple.columnIndex = 0;
+	}
+	
+	return tuple;
 }
 
 /**
@@ -291,6 +314,7 @@
 	supportsQuotingEngineTypeInCreateSyntax = NO;
 	supportsFractionalSeconds               = NO;
 	supportsFulltextOnInnoDB                = NO;
+	supportsShowEngine                      = NO;
 }
 
 /**
diff --git a/Source/SPTableRelations.m b/Source/SPTableRelations.m
index 5734a96d..43156eae 100644
--- a/Source/SPTableRelations.m
+++ b/Source/SPTableRelations.m
@@ -35,6 +35,7 @@
 #import "SPTableView.h"
 #import "SPAlertSheets.h"
 #import "RegexKitLite.h"
+#import "SPServerSupport.h"
 
 #import <SPMySQL/SPMySQL.h>
 
@@ -192,16 +193,19 @@ static NSString *SPRelationOnDeleteKey   = @"on_delete";
 		// most common are 121 (name probably in use) and 150 (types don't exactly match).
 		// Retrieve the InnoDB status and extract the most recent error for more helpful text.
 		if ([connection lastErrorID] == 1005) {
-			NSString *statusText = [connection getFirstFieldFromQuery:@"SHOW INNODB STATUS"];
-			NSString *detailErrorString = [statusText stringByMatching:@"latest foreign key error\\s+-----*\\s+[0-9: ]*(.*?)\\s+-----" options:(RKLCaseless | RKLDotAll) inRange:NSMakeRange(0, [statusText length]) capture:1L error:NULL];
-			if (detailErrorString) {
-				errorText = [NSString stringWithFormat:NSLocalizedString(@"%@\n\nDetail: %@", @"Add relation error detail intro"), errorText, [detailErrorString stringByReplacingOccurrencesOfString:@"\n" withString:@" "]];
-			}
-
-			// Detect name duplication if appropriate
-			if ([errorText isMatchedByRegex:@"errno: 121"] && [errorText isMatchedByRegex:@"already exists"]) {
-				[takenConstraintNames addObject:[[constraintName stringValue] lowercaseString]];
-				[self controlTextDidChange:[NSNotification notificationWithName:@"dummy" object:constraintName]];
+			SPInnoDBStatusQueryFormat status = [[tableDocumentInstance serverSupport] innoDBStatusQuery];
+			if(status.queryString) {
+				NSString *statusText = [[[connection queryString:status.queryString] getRowAsArray] objectAtIndex:status.columnIndex];
+				NSString *detailErrorString = [statusText stringByMatching:@"latest foreign key error\\s+-----*\\s+[0-9: ]*(.*?)\\s+-----" options:(RKLCaseless | RKLDotAll) inRange:NSMakeRange(0, [statusText length]) capture:1L error:NULL];
+				if (detailErrorString) {
+					errorText = [NSString stringWithFormat:NSLocalizedString(@"%@\n\nDetail: %@", @"Add relation error detail intro"), errorText, [detailErrorString stringByReplacingOccurrencesOfString:@"\n" withString:@" "]];
+				}
+				
+				// Detect name duplication if appropriate
+				if ([errorText isMatchedByRegex:@"errno: 121"] && [errorText isMatchedByRegex:@"already exists"]) {
+					[takenConstraintNames addObject:[[constraintName stringValue] lowercaseString]];
+					[self controlTextDidChange:[NSNotification notificationWithName:@"dummy" object:constraintName]];
+				}
 			}
 		}