From cc3ac6bd77fc50ffdba67b7f4fecf4b682b4c5be Mon Sep 17 00:00:00 2001 From: Max Date: Mon, 21 May 2018 03:47:47 +0200 Subject: Add support for NO_BACKSLASH_ESCAPES sql_mode during SQL import (#3005) --- .../SPMySQLConnection Categories/Server Info.h | 2 + Source/SPDataImport.m | 73 ++++++++++++++-------- 2 files changed, 49 insertions(+), 26 deletions(-) diff --git a/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Server Info.h b/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Server Info.h index 2e4ac7ad..8e4b1121 100644 --- a/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Server Info.h +++ b/Frameworks/SPMySQLFramework/Source/SPMySQLConnection Categories/Server Info.h @@ -60,6 +60,8 @@ * the server_status flags that were received most recently (i.e. usually * in return to the last executed query). * + * Calling this method will never affect the mysql error state. + * * THIS METHOD IS NOT THREAD-SAFE! * * @return YES, unless the MySQL connection is invalid (in which case the passed-in struct remains unchanged) diff --git a/Source/SPDataImport.m b/Source/SPDataImport.m index f6b865e7..a76ad341 100644 --- a/Source/SPDataImport.m +++ b/Source/SPDataImport.m @@ -431,9 +431,25 @@ sqlEncoding = [importEncodingPopup selectedTag]; } + //store the sqlMode to restore, if the import changes it + NSString *sqlModeToRestore = nil; + { + // this query should work in ≥ 4.1.0 (which is also the first version that allows setting sql_mode at runtime) + SPMySQLResult *res = [mySQLConnection queryString:@"SELECT @@sql_mode"]; + [res setReturnDataAsStrings:YES]; //TODO #2700: The framework misinterprets binary collation as binary data, so in order to be safe force it to use strings + + sqlModeToRestore = [[res getRowAsArray] objectAtIndex:0]; + } + + SPMySQLServerStatusBits serverStatus; + // initialize + serverStatus.noBackslashEscapes = 0; // for the moment we only care about that flag + // Read in the file in a loop sqlParser = [[SPSQLParser alloc] init]; [sqlParser setDelimiterSupport:YES]; + [mySQLConnection updateServerStatusBits:&serverStatus]; + [sqlParser setNoBackslashEscapes:serverStatus.noBackslashEscapes]; sqlDataBuffer = [[NSMutableData alloc] init]; importPool = [[NSAutoreleasePool alloc] init]; while (1) { @@ -442,12 +458,14 @@ @try { fileChunk = [sqlFileHandle readDataOfLength:fileChunkMaxLength]; } - // Report file read errors, and bail @catch (NSException *exception) { if (connectionEncodingToRestore) { [mySQLConnection queryString:[NSString stringWithFormat:@"SET NAMES '%@'", connectionEncodingToRestore]]; } + if (sqlModeToRestore) { + [mySQLConnection queryString:[NSString stringWithFormat:@"SET SQL_MODE=%@", [sqlModeToRestore tickQuotedString]]]; + } [self closeAndStopProgressSheet]; SPOnewayAlertSheet( SP_FILE_READ_ERROR_STRING, @@ -458,8 +476,7 @@ [sqlDataBuffer release]; [importPool drain]; [tableDocumentInstance setQueryMode:SPInterfaceQueryMode]; - if([filename hasPrefix:SPImportClipboardTempFileNamePrefix]) - [[NSFileManager defaultManager] removeItemAtPath:filename error:nil]; + if([filename hasPrefix:SPImportClipboardTempFileNamePrefix]) [[NSFileManager defaultManager] removeItemAtPath:filename error:nil]; return; } @@ -488,11 +505,14 @@ // Try to generate a NSString with the resulting data sqlString = [[NSString alloc] initWithData:[sqlDataBuffer subdataWithRange:NSMakeRange(dataBufferLastQueryEndPosition, dataBufferPosition - dataBufferLastQueryEndPosition)] - encoding:sqlEncoding]; + encoding:sqlEncoding]; if (!sqlString) { if (connectionEncodingToRestore) { [mySQLConnection queryString:[NSString stringWithFormat:@"SET NAMES '%@'", connectionEncodingToRestore]]; } + if (sqlModeToRestore) { + [mySQLConnection queryString:[NSString stringWithFormat:@"SET SQL_MODE=%@", [sqlModeToRestore tickQuotedString]]]; + } [self closeAndStopProgressSheet]; NSString *displayEncoding; if (![importEncodingPopup indexOfSelectedItem]) { @@ -509,8 +529,7 @@ [sqlDataBuffer release]; [importPool drain]; [tableDocumentInstance setQueryMode:SPInterfaceQueryMode]; - if([filename hasPrefix:SPImportClipboardTempFileNamePrefix]) - [[NSFileManager defaultManager] removeItemAtPath:filename error:nil]; + if([filename hasPrefix:SPImportClipboardTempFileNamePrefix]) [[NSFileManager defaultManager] removeItemAtPath:filename error:nil]; return; } @@ -531,12 +550,11 @@ dataBufferPosition -= dataBufferLastQueryEndPosition; dataBufferLastQueryEndPosition = 0; } - + // Before entering the following loop, check that we actually have a connection. // If not, check the connection if appropriate and then clean up and exit if appropriate. if (![mySQLConnection isConnected] && ([mySQLConnection userTriggeredDisconnect] || ![mySQLConnection checkConnection])) { - if ([filename hasPrefix:SPImportClipboardTempFileNamePrefix]) - [[NSFileManager defaultManager] removeItemAtPath:filename error:nil]; + if ([filename hasPrefix:SPImportClipboardTempFileNamePrefix]) [[NSFileManager defaultManager] removeItemAtPath:filename error:nil]; [self closeAndStopProgressSheet]; [errors appendString:NSLocalizedString(@"The connection to the server was lost during the import. The import is only partially complete.", @"Connection lost during import error message")]; [self showErrorSheetWithMessage:errors]; @@ -560,30 +578,33 @@ // Skip blank or whitespace-only queries to avoid errors if (![query length]) continue; - + // Run the query [mySQLConnection queryString:query usingEncoding:sqlEncoding withResultType:SPMySQLResultAsResult]; + // in case the query was a "SET @@sql_mode = ...", the server_status may have changed + if([mySQLConnection updateServerStatusBits:&serverStatus]) [sqlParser setNoBackslashEscapes:serverStatus.noBackslashEscapes]; + // Check for any errors if ([mySQLConnection queryErrored] && ![[mySQLConnection lastErrorMessage] isEqualToString:@"Query was empty"]) { [errors appendFormat:NSLocalizedString(@"[ERROR in query %ld] %@\n", @"error text when multiple custom query failed"), (long)(queriesPerformed+1), [mySQLConnection lastErrorMessage]]; // if the error is about utf8mb4 not being supported by the server display a more helpful message. // Note: the same error will occur when doing CREATE TABLE... with utf8mb4. - if([mySQLConnection lastErrorID] == 1115 && [[mySQLConnection lastErrorMessage] rangeOfString:@"utf8mb4" options:NSCaseInsensitiveSearch].location != NSNotFound && [query rangeOfString:@"SET NAMES" options:NSCaseInsensitiveSearch].location != NSNotFound) { + if([mySQLConnection lastErrorID] == 1115 /* ER_UNKNOWN_CHARACTER_SET */ && [[mySQLConnection lastErrorMessage] rangeOfString:@"utf8mb4" options:NSCaseInsensitiveSearch].location != NSNotFound && [query rangeOfString:@"SET NAMES" options:NSCaseInsensitiveSearch].location != NSNotFound) { if(!ignoreCharsetError) { __block NSInteger charsetErrorSheetReturnCode; - + SPMainQSync(^{ NSAlert *charsetErrorAlert = [NSAlert alertWithMessageText:NSLocalizedString(@"Incompatible encoding in SQL file", @"sql import error message") defaultButton:NSLocalizedString(@"Import Anyway", @"sql import : charset error alert : continue button") alternateButton:NSLocalizedString(@"Cancel Import", @"sql import : charset error alert : cancel button") - otherButton:nil + otherButton:nil informativeTextWithFormat:NSLocalizedString(@"The SQL file uses utf8mb4 encoding, but your MySQL version only supports the limited utf8 subset.\n\nYou can continue the import, but any non-BMP characters in the SQL file (eg. some typographic and scientific special characters, archaic CJK logograms, emojis) will be unrecoverably lost!", @"sql import : charset error alert : detail message")]; [charsetErrorAlert setAlertStyle:NSWarningAlertStyle]; charsetErrorSheetReturnCode = [charsetErrorAlert runModal]; }); - + switch (charsetErrorSheetReturnCode) { // don't display the message a second time case NSAlertDefaultReturn: @@ -603,25 +624,22 @@ SPMainQSync(^{ NSAlert *sqlErrorAlert = [NSAlert alertWithMessageText:NSLocalizedString(@"An error occurred while importing SQL", @"sql import error message") - defaultButton:NSLocalizedString(@"Continue", @"continue button") - alternateButton:NSLocalizedString(@"Ignore All Errors", @"ignore errors button") - otherButton:NSLocalizedString(@"Stop", @"stop button") - informativeTextWithFormat:NSLocalizedString(@"[ERROR in query %ld] %@\n", @"error text when multiple custom query failed"), (long)(queriesPerformed+1), [mySQLConnection lastErrorMessage]]; + defaultButton:NSLocalizedString(@"Continue", @"continue button") + alternateButton:NSLocalizedString(@"Ignore All Errors", @"ignore errors button") + otherButton:NSLocalizedString(@"Stop", @"stop button") + informativeTextWithFormat:NSLocalizedString(@"[ERROR in query %ld] %@\n", @"error text when multiple custom query failed"), (long)(queriesPerformed+1), [mySQLConnection lastErrorMessage]]; [sqlErrorAlert setAlertStyle:NSWarningAlertStyle]; sqlImportErrorSheetReturnCode = [sqlErrorAlert runModal]; }); - + switch (sqlImportErrorSheetReturnCode) { - // On "continue", no additional action is required case NSAlertDefaultReturn: break; - // Ignore all future errors if asked to case NSAlertAlternateReturn: ignoreSQLErrors = YES; break; - // Otherwise, stop default: [errors appendString:NSLocalizedString(@"Import cancelled!\n", @"import cancelled message")]; @@ -637,14 +655,14 @@ if (fileIsCompressed) { [singleProgressBar setDoubleValue:[sqlFileHandle realDataReadLength]]; [singleProgressText setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Imported %@ of SQL", @"SQL import progress text where total size is unknown"), - [NSString stringForByteSize:fileProcessedLength]]]; + [NSString stringForByteSize:fileProcessedLength]]]; } else { [singleProgressBar setDoubleValue:fileProcessedLength]; [singleProgressText setStringValue:[NSString stringWithFormat:NSLocalizedString(@"Imported %@ of %@", @"SQL import progress text"), [NSString stringForByteSize:fileProcessedLength], [NSString stringForByteSize:fileTotalLength]]]; } } - + // If all the data has been read, break out of the processing loop if (allDataRead) break; @@ -659,6 +677,7 @@ // Run the query [mySQLConnection queryString:query usingEncoding:sqlEncoding withResultType:SPMySQLResultAsResult]; + // we don't care for the server_status that is set AFTER the last query has been executed // Check for any errors if ([mySQLConnection queryErrored] && ![[mySQLConnection lastErrorMessage] isEqualToString:@"Query was empty"]) { @@ -673,12 +692,14 @@ if (connectionEncodingToRestore) { [mySQLConnection queryString:[NSString stringWithFormat:@"SET NAMES '%@'", connectionEncodingToRestore]]; } + if (sqlModeToRestore) { + [mySQLConnection queryString:[NSString stringWithFormat:@"SET SQL_MODE=%@", [sqlModeToRestore tickQuotedString]]]; + } [sqlParser release]; [sqlDataBuffer release]; [importPool drain]; [tableDocumentInstance setQueryMode:SPInterfaceQueryMode]; - if([filename hasPrefix:SPImportClipboardTempFileNamePrefix]) - [[NSFileManager defaultManager] removeItemAtPath:filename error:nil]; + if([filename hasPrefix:SPImportClipboardTempFileNamePrefix]) [[NSFileManager defaultManager] removeItemAtPath:filename error:nil]; // Close progress sheet [self closeAndStopProgressSheet]; -- cgit v1.2.3