From 59f37e44da3450d37356c87129a3d8592d5c2259 Mon Sep 17 00:00:00 2001 From: rowanbeentje Date: Wed, 17 Jun 2009 00:18:10 +0000 Subject: Significant connection improvements: - Restored MySQL 3 compatibility - max_packet_size details are now cached per connection for query speedup and to fix network recovery problems - Improved disconnection recovery - Errors during results fetching are correctly detected for the first time - Error strings are now correctly set in several places - The correct number of rows affected is now returned for queries which triggered reconnects or max_packet_size increases --- Source/CMMCPConnection.h | 7 ++- Source/CMMCPConnection.m | 126 ++++++++++++++++++++++++++++++++++------------- 2 files changed, 98 insertions(+), 35 deletions(-) diff --git a/Source/CMMCPConnection.h b/Source/CMMCPConnection.h index 03a5079f..f69c3069 100644 --- a/Source/CMMCPConnection.h +++ b/Source/CMMCPConnection.h @@ -52,6 +52,8 @@ NSString *connectionHost; int connectionPort; NSString *connectionSocket; + int maxAllowedPacketSize; + unsigned long connectionThreadId; int connectionTimeout; int currentSSHTunnelState; BOOL useKeepAlive; @@ -60,6 +62,7 @@ float lastQueryExecutionTime; NSString *lastQueryErrorMessage; unsigned int lastQueryErrorId; + my_ulonglong lastQueryAffectedRows; BOOL isMaxAllowedPacketEditable; @@ -91,7 +94,7 @@ - (float) lastQueryExecutionTime; - (MCPResult *) listDBsLike:(NSString *) dbsName; - (BOOL) checkConnection; -- (void) restoreEncodingDetails; +- (void) restoreConnectionDetails; - (void) setDelegate:(id)object; - (NSTimeZone *) timeZone; - (BOOL) pingConnection; @@ -100,6 +103,8 @@ - (void) keepAlive:(NSTimer *)theTimer; - (void) threadedKeepAlive; - (const char *) cStringFromString:(NSString *) theString usingEncoding:(NSStringEncoding) encoding; +- (void) setLastErrorMessage:(NSString *)theErrorMessage; +- (BOOL) fetchMaxAllowedPacket; - (int) getMaxAllowedPacket; - (BOOL) isMaxAllowedPacketEditable; - (int) setMaxAllowedPacketTo:(int)newSize resetSize:(BOOL)reset; diff --git a/Source/CMMCPConnection.m b/Source/CMMCPConnection.m index fdb92d59..5d5cf52e 100644 --- a/Source/CMMCPConnection.m +++ b/Source/CMMCPConnection.m @@ -39,7 +39,7 @@ static void forcePingTimeout(int signalNumber); { if( mConnected ) { CMMCPResult *theResult; - theResult = [self queryString:@"SHOW VARIABLES WHERE Variable_name = 'version'"]; + theResult = [self queryString:@"SHOW VARIABLES LIKE 'version'"]; if ([theResult numOfRows]) { [theResult dataSeek:0]; serverVersionString = [[NSString stringWithString:[[theResult fetchRowAsArray] objectAtIndex:1]] retain]; @@ -129,9 +129,12 @@ static void forcePingTimeout(int signalNumber); keepAliveInterval = [[[NSUserDefaults standardUserDefaults] objectForKey:@"KeepAliveInterval"] doubleValue]; if (!keepAliveInterval) keepAliveInterval = 0; lastKeepAliveSuccess = nil; + connectionThreadId = 0; + maxAllowedPacketSize = -1; lastQueryExecutionTime = 0; lastQueryErrorId = 0; lastQueryErrorMessage = nil; + lastQueryAffectedRows = 0; if (![NSBundle loadNibNamed:@"ConnectionErrorDialog" owner:self]) { NSLog(@"Connection error dialog could not be loaded; connection failure handling will not function correctly."); } @@ -244,6 +247,7 @@ static void forcePingTimeout(int signalNumber); theRet = mysql_real_connect(mConnection, theHost, theLogin, thePass, NULL, connectionPort, theSocket, mConnectionFlags); thePass = NULL; if (theRet != mConnection) { + [self setLastErrorMessage:nil]; if (connectionTunnel) { [connectionTunnel disconnect]; [delegate setStatusIconToImageWithName:@"ssh-disconnected"]; @@ -253,9 +257,18 @@ static void forcePingTimeout(int signalNumber); mConnected = YES; mEncoding = [MCPConnection encodingForMySQLEncoding:mysql_character_set_name(mConnection)]; + connectionThreadId = mConnection->thread_id; [self timeZone]; // Getting the timezone used by the server. isMaxAllowedPacketEditable = [self isMaxAllowedPacketEditable]; + if (![self fetchMaxAllowedPacket]) { + [self setLastErrorMessage:nil]; + if (connectionTunnel) { + [connectionTunnel disconnect]; + [delegate setStatusIconToImageWithName:@"ssh-disconnected"]; + } + return mConnected = NO; + } // Register notification if a query was sent to the MySQL connection // to be able to identify the sender of that query @@ -430,6 +443,7 @@ static void forcePingTimeout(int signalNumber); } } } else if (parentWindow) { + [self setLastErrorMessage:nil]; // If the connection was not successfully established, ask how to proceed. [NSApp beginSheet:connectionErrorDialog modalForWindow:parentWindow modalDelegate:self didEndSelector:nil contextInfo:nil]; @@ -488,7 +502,7 @@ static void forcePingTimeout(int signalNumber); /* - * Ends and existing modal session + * Ends an existing modal session */ - (IBAction) closeSheet:(id)sender { @@ -640,6 +654,7 @@ static void forcePingTimeout(int signalNumber); [self startKeepAliveTimerResettingState:YES]; return YES; } + [self setLastErrorMessage:nil]; if (connectionTunnel) { [connectionTunnel disconnect]; [delegate setStatusIconToImageWithName:@"ssh-disconnected"]; @@ -689,20 +704,20 @@ static void forcePingTimeout(int signalNumber); unsigned long theCQueryLength; int queryResultCode; int queryErrorId = 0; + my_ulonglong queryAffectedRows = 0; int currentMaxAllowedPacket = -1; BOOL isQueryRetry = NO; - unsigned long threadid = mConnection->thread_id; NSString *queryErrorMessage = nil; // If no connection is present, return nil. if (!mConnected) { // Write a log entry - [delegate queryGaveError:@"No conncetion available!"]; + if ([delegate respondsToSelector:@selector(queryGaveError:)]) [delegate queryGaveError:@"No connection available!"]; // Notify that the query has been performed [[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryHasBeenPerformed" object:self]; // Show an error alert while resetting - NSBeginAlertSheet(NSLocalizedString(@"Error", @"error"), @"No conncetion available!", - nil, nil, [delegate valueForKeyPath:@"tableWindow"], self, nil, nil, nil, @"No conncetion available!"); + NSBeginAlertSheet(NSLocalizedString(@"Error", @"error"), @"No connection available!", + nil, nil, [delegate valueForKeyPath:@"tableWindow"], self, nil, nil, nil, @"No connection available!"); return nil; } @@ -718,8 +733,8 @@ static void forcePingTimeout(int signalNumber); // Set the length of the current query + 1 (\0) theCQueryLength = strlen(theCQuery)+1; - // Check query lenght against max_allowed_packet - // If max_allowed_packet is editable for the user + // Check query length against max_allowed_packet; if it is larger, the + // query would error, so if max_allowed_packet is editable for the user // increase it for the current session and reconnect. if([self getMaxAllowedPacket] < theCQueryLength) { @@ -735,8 +750,10 @@ static void forcePingTimeout(int signalNumber); @"error message if max_allowed_packet < query size"), theCQueryLength, [self getMaxAllowedPacket]]; - // Write a log entry - [delegate queryGaveError:errorMessage]; + // Write a log entry and update the connection error messages for those uses that check it + if ([delegate respondsToSelector:@selector(queryGaveError:)]) [delegate queryGaveError:errorMessage]; + [self setLastErrorMessage:errorMessage]; + // Notify that the query has been performed [[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryHasBeenPerformed" object:self]; // Show an error alert while resetting @@ -753,16 +770,11 @@ static void forcePingTimeout(int signalNumber); // If this query has failed once already, check the connection if (isQueryRetry) { if (![self checkConnection]) { + // Notify that the query has been performed [[NSNotificationCenter defaultCenter] postNotificationName:@"SMySQLQueryHasBeenPerformed" object:self]; return nil; } - - // If the connection thread is still the same as at the start, the - // connection wasn't the issue - don't retry. - if (threadid == mConnection->thread_id) break; - - threadid = mConnection->thread_id; } // Run (or re-run) the query, timing the execution time of the query - note @@ -774,11 +786,20 @@ static void forcePingTimeout(int signalNumber); // On success, capture the results if (0 == queryResultCode) { - if (mysql_field_count(mConnection) != 0) + if (mysql_field_count(mConnection) != 0) { theResult = [[CMMCPResult alloc] initWithMySQLPtr:mConnection encoding:mEncoding timeZone:mTimeZone]; + // Ensure no problem occurred during the result fetch + if (mysql_errno(mConnection) != 0) { + queryErrorMessage = [[NSString alloc] initWithString:[self stringWithCString:mysql_error(mConnection)]]; + queryErrorId = mysql_errno(mConnection); + break; + } + } + queryErrorMessage = [[NSString alloc] initWithString:@""]; queryErrorId = 0; + queryAffectedRows = mysql_affected_rows(mConnection); // On failure, set the error messages and IDs } else { @@ -798,17 +819,17 @@ static void forcePingTimeout(int signalNumber); // If the mysql thread id has changed as a result of a connection error, // ensure connection details are still correct - if (threadid != mConnection->thread_id) [self restoreEncodingDetails]; + if (connectionThreadId != mConnection->thread_id) [self restoreConnectionDetails]; // If max_allowed_packet was changed, reset it to default if(currentMaxAllowedPacket > -1) [self setMaxAllowedPacketTo:currentMaxAllowedPacket resetSize:YES]; // Update error strings and IDs - if (lastQueryErrorMessage) [lastQueryErrorMessage release], lastQueryErrorMessage = nil; lastQueryErrorId = queryErrorId; - lastQueryErrorMessage = [[NSString alloc] initWithString:queryErrorMessage?queryErrorMessage:@""]; + [self setLastErrorMessage:queryErrorMessage?queryErrorMessage:@""]; if (queryErrorMessage) [queryErrorMessage release]; + lastQueryAffectedRows = queryAffectedRows; // If an error occurred, inform the delegate if (0 != queryResultCode && delegate && [delegate respondsToSelector:@selector(queryGaveError:)]) @@ -851,15 +872,10 @@ static void forcePingTimeout(int signalNumber); */ - (BOOL)checkConnection { - unsigned long threadid; - if (!mConnected) return NO; BOOL connectionVerified = FALSE; - // Get the current thread ID for this connection - threadid = mConnection->thread_id; - // Check whether the connection is still operational via a wrapped version of MySQL ping. connectionVerified = [self pingConnection]; @@ -889,7 +905,7 @@ static void forcePingTimeout(int signalNumber); // If a connection exists, check whether the thread id differs; if so, the connection has // probably been reestablished and we need to reset the connection encoding - } else if (threadid != mConnection->thread_id) [self restoreEncodingDetails]; + } else if (connectionThreadId != mConnection->thread_id) [self restoreConnectionDetails]; return connectionVerified; } @@ -898,8 +914,10 @@ static void forcePingTimeout(int signalNumber); * Restore the connection encoding details as necessary based on the delegate-provided * details. */ -- (void) restoreEncodingDetails +- (void) restoreConnectionDetails { + connectionThreadId = mConnection->thread_id; + [self fetchMaxAllowedPacket]; if (delegate && [delegate valueForKey:@"_encoding"]) { [self queryString:[NSString stringWithFormat:@"/*!40101 SET NAMES '%@' */", [NSString stringWithString:[delegate valueForKey:@"_encoding"]]]]; if (delegate && [delegate respondsToSelector:@selector(connectionEncodingViaLatin1)]) { @@ -1125,14 +1143,31 @@ static void forcePingTimeout(int signalNumber) /* * Returns a string for the last MySQL error message on the connection. + * This is cached within the object to allow helper queries to be performed + * without affecting the state that the GUI is querying. */ - (NSString *) getLastErrorMessage { return lastQueryErrorMessage; } +/* + * Sets the string for the last MySQL error message on the connection, + * managing memory as appropriate. Supply a nil string to store the + * last error on the connection. + */ +- (void) setLastErrorMessage:(NSString *)theErrorMessage +{ + if (!theErrorMessage) theErrorMessage = [self stringWithCString:mysql_error(mConnection)]; + + if (lastQueryErrorMessage) [lastQueryErrorMessage release], lastQueryErrorMessage = nil; + lastQueryErrorMessage = [[NSString alloc] initWithString:theErrorMessage]; +} + /* * Returns the ErrorID of the last MySQL error on the connection. + * This is cached within the object to allow helper queries to be performed + * without affecting the state that the GUI is querying. */ - (unsigned int) getLastErrorID { @@ -1140,24 +1175,47 @@ static void forcePingTimeout(int signalNumber) } /* - * Retrieves max_allowed_packet size set as global variable. - * It returns -1 if it fails. + * Returns the number of affected rows by the last query. + * This is cached within the object to allow helper queries to be performed + * without affecting the state that the GUI is querying. */ -- (int) getMaxAllowedPacket +- (my_ulonglong) affectedRows { + return lastQueryAffectedRows; +} - if (0 == mysql_query(mConnection, "SELECT @@global.max_allowed_packet")) { +/* + * Retrieve the max_allowed_packet size from the server; returns + * false if the query fails. + */ +- (BOOL) fetchMaxAllowedPacket +{ + char *queryString; + + if ([self serverMajorVersion] == 3) queryString = "SHOW VARIABLES LIKE 'max_allowed_packet'"; + else queryString = "SELECT @@global.max_allowed_packet"; + if (0 == mysql_query(mConnection, queryString)) { if (mysql_field_count(mConnection) != 0) { CMMCPResult *r = [[CMMCPResult alloc] initWithMySQLPtr:mConnection encoding:mEncoding timeZone:mTimeZone]; NSArray *a = [r fetchRowAsArray]; [r autorelease]; - if([a count]) return [[a objectAtIndex:0] intValue]; + if([a count]) { + maxAllowedPacketSize = [[a objectAtIndex:([self serverMajorVersion] == 3)?1:0] intValue]; + return true; + } } } - NSRunAlertPanel(@"Error", [NSString stringWithFormat:@"An error occured while retrieving max_allowed_packet size:\n\n%@", [self getLastErrorMessage]], @"OK", nil, nil); + return false; +} - return -1; +/* + * Retrieves max_allowed_packet size set as global variable. + * It returns -1 if it fails. + */ +- (int) getMaxAllowedPacket +{ + return maxAllowedPacketSize; } /* -- cgit v1.2.3