hat irgendjemand erfahrung mit großen mySQL tabellen [>> 1.000.000 datensätze*] (und 'threaded' mySQL Verbindungen)?
* Es können schnell über 100 millionen Datensätze werden!
Bei kleineren Datenbanken läuft alles gut aber sobald die tabelle in Richtung 1 million Datensätze wächst kommt plötzlich "Invalid memory access" beim Aufruf von mysql_real_query.
Ich habe einen 'CrashTest' geschrieben, der >1.000.000 datensätze erzeugt und damit arbeitet.
Die Erzeugung der Datenbank und der Tabelle ist kein Problem.
Das hinzufügen der Datensätze gelingt auch problemlos.
Aber bei weiteren Befehlen wie CREATE TEMP TABLE oder ALTER TABLE oder UPDATE TABLE kommt manchmal dieser 'Invalid memory access' Fehler.
Eine weitere seltsame Sache ist die Speicherverwaltung:
Der Speicherverbrauch wächst stetig bis > 600.000K und fällt plötzlich* auf >40.000K zurück. *Ungefähr nachdem rund 900.000 Datensätze erzeugt wurden.
Liegt das an meiner 'mangelhaften' programmierung, an der PureBasic oder der mySQL (libmySQL.dll) Speicherverwaltung?
Kann sich bitte irgendjemand das 'CrashTest' Progrämmchen ansehen:
Alle Quellen, die Fehlerprotokolle, dll u.s.w. können hier heruntergeladen werden: CrashTest04.7z oder CrashTest04.zip
Code: Alles auswählen
; mySQLCrashTest.pb -> creates a test table within test database, adds 1.121.280 records and runs some queries on it (including temp tables).
; all servers passed as ProgramParameters will be connected either threaded or one after the other.
;
; 2010-03-09 sverson
; CALL mySQLCrashTest [THREADED] "server|user|password||3306|NULL|0|" "server1|user1|password1||3307|NULL|0|" "..."
; Exemple:
; mySQLCrashTest "localhost|root|||3306|NULL|0|"
; will call a single no threaded mySQL task
;
; mySQLCrashTest THREADED "www.mysqlserver1.com|testuser|testpw||3307|NULL|0|" "www.mysqlserver2.com|testuser|testpw||3308|NULL|0|"
; will call two tasks and rum it threaded
EnableExplicit
#TempTable$ = " TEMPORARY "
;{- ErrorHandler START
Procedure ErrorHandler()
Protected ErrorMessage$
ErrorMessage$ = "A program error was detected:" + Chr(13)
ErrorMessage$ + Chr(13)
ErrorMessage$ + "Error Message: " + ErrorMessage() + Chr(13)
ErrorMessage$ + "Error Code: " + Str(ErrorCode()) + Chr(13)
ErrorMessage$ + "Code Address: " + Str(ErrorAddress()) + Chr(13)
If ErrorCode() = #PB_OnError_InvalidMemory
ErrorMessage$ + "Target Address: " + Str(ErrorTargetAddress()) + Chr(13)
EndIf
If ErrorLine() = -1
ErrorMessage$ + "Sourcecode line: Enable OnError lines support to get code line information." + Chr(13)
Else
ErrorMessage$ + "Sourcecode line: " + Str(ErrorLine()) + Chr(13)
ErrorMessage$ + "Sourcecode file: " + ErrorFile() + Chr(13)
EndIf
ErrorMessage$ + Chr(13)
ErrorMessage$ + "Register content:" + Chr(13)
CompilerSelect #PB_Compiler_Processor
CompilerCase #PB_Processor_x86
ErrorMessage$ + "EAX = " + Str(ErrorRegister(#PB_OnError_EAX)) + Chr(13)
ErrorMessage$ + "EBX = " + Str(ErrorRegister(#PB_OnError_EBX)) + Chr(13)
ErrorMessage$ + "ECX = " + Str(ErrorRegister(#PB_OnError_ECX)) + Chr(13)
ErrorMessage$ + "EDX = " + Str(ErrorRegister(#PB_OnError_EDX)) + Chr(13)
ErrorMessage$ + "EBP = " + Str(ErrorRegister(#PB_OnError_EBP)) + Chr(13)
ErrorMessage$ + "ESI = " + Str(ErrorRegister(#PB_OnError_ESI)) + Chr(13)
ErrorMessage$ + "EDI = " + Str(ErrorRegister(#PB_OnError_EDI)) + Chr(13)
ErrorMessage$ + "ESP = " + Str(ErrorRegister(#PB_OnError_ESP)) + Chr(13)
CompilerCase #PB_Processor_x64
ErrorMessage$ + "RAX = " + Str(ErrorRegister(#PB_OnError_RAX)) + Chr(13)
ErrorMessage$ + "RBX = " + Str(ErrorRegister(#PB_OnError_RBX)) + Chr(13)
ErrorMessage$ + "RCX = " + Str(ErrorRegister(#PB_OnError_RCX)) + Chr(13)
ErrorMessage$ + "RDX = " + Str(ErrorRegister(#PB_OnError_RDX)) + Chr(13)
ErrorMessage$ + "RBP = " + Str(ErrorRegister(#PB_OnError_RBP)) + Chr(13)
ErrorMessage$ + "RSI = " + Str(ErrorRegister(#PB_OnError_RSI)) + Chr(13)
ErrorMessage$ + "RDI = " + Str(ErrorRegister(#PB_OnError_RDI)) + Chr(13)
ErrorMessage$ + "RSP = " + Str(ErrorRegister(#PB_OnError_RSP)) + Chr(13)
ErrorMessage$ + "Display of registers R8-R15 skipped." + Chr(13)
CompilerEndSelect
MessageRequester("mySQL CrashTest - ERROR", ErrorMessage$)
End
EndProcedure
OnErrorCall(@ErrorHandler())
;}- ErrorHandler END
;{- mySQL START
Structure MYSQL
EndStructure
Structure MYSQL_RES
EndStructure
Structure MYSQL_BIND
EndStructure
Structure MYSQL_STMT
EndStructure
Structure MYSQL_ROW
*field[255]
EndStructure
Structure MYSQL_FIELD
Name.s
org_name.s
table.s
org_table.s
DB.s
catalog.s
def.s
Length.l
max_length.l
name_length.l
org_name_length.l
table_length.l
org_table_length.l
db_length.l
catalog_length.l
def_length.l
Flags.l
decimals.l
charset_nr.l
type.l
EndStructure
Structure MY_CHARSET_INFO
Number.l
State.l
csname.s
Name.s
Comment.s
dir.s
mbminlen.l
mbmaxlen.l
EndStructure
#REFRESH_GRANT = 1 ; Refresh grant tables
#REFRESH_LOG = 2 ; Start on new log file
#REFRESH_TABLES = 4 ; close all tables
#REFRESH_HOSTS = 8 ; Flush host cache
#REFRESH_STATUS = 16 ; Flush status variables
#REFRESH_THREADS = 32 ; Flush Thread cache
#REFRESH_SLAVE = 64 ; Reset master info And restart slave Thread
#REFRESH_MASTER = 128 ; Remove all bin logs in the index And truncate the index
Import "libmysql.lib"
mysql_get_client_info()
mysql_get_client_version()
mysql_errno(*mysql.MYSQL)
mysql_init(*mysql.MYSQL)
mysql_real_connect(*mysql.MYSQL,host.p-utf8,user.p-utf8,passwd.p-utf8,DB.p-utf8,port.l,unix_socket.p-utf8,client_flag.l)
mysql_get_parameters()
mysql_sqlstate(*mysql.MYSQL)
mysql_error(*mysql.MYSQL)
mysql_get_server_version(*mysql.MYSQL)
mysql_get_server_info(*mysql.MYSQL)
mysql_get_host_info(*mysql.MYSQL)
mysql_select_db(*mysql.MYSQL, Databasename.p-utf8)
mysql_get_proto_info(*mysql.MYSQL)
mysql_info(*mysql.MYSQL)
mysql_insert_id(*mysql.MYSQL)
mysql_affected_rows(*mysql.MYSQL)
mysql_autocommit(*mysql.MYSQL,*mode.Byte)
mysql_character_set_name(*mysql.MYSQL)
mysql_close(*mysql.MYSQL)
mysql_commit(*mysql.MYSQL)
mysql_debug(DebugString.p-utf8)
mysql_disable_reads_from_master(*mysql.MYSQL)
mysql_disable_rpl_parse(*mysql.MYSQL)
mysql_dumPB_debug_info(*mysql.MYSQL)
mysql_embedded()
mysql_enable_reads_from_master(*mysql.MYSQL)
mysql_enable_rpl_parse(*mysql.MYSQL)
mysql_field_count(*mysql.MYSQL)
mysql_library_end(*mysql.MYSQL)
mysql_list_processes(*mysql.MYSQL)
mysql_more_results(*mysql.MYSQL)
mysql_next_result(*mysql.MYSQL)
mysql_ping(*mysql.MYSQL)
mysql_read_query_result(*mysql.MYSQL)
mysql_reload(*mysql.MYSQL)
mysql_rollback(*mysql.MYSQL)
mysql_rpl_parse_enabled(*mysql.MYSQL)
mysql_rpl_probe(*mysql.MYSQL)
mysql_server_end()
mysql_thread_end()
mysql_thread_init()
mysql_thread_safe()
mysql_stat(*mysql.MYSQL)
mysql_store_result(*mysql.MYSQL)
mysql_thread_id(*mysql.MYSQL)
mysql_use_result(*mysql.MYSQL)
mysql_warning_count(*mysql.MYSQL)
mysql_change_user(*mysql.MYSQL,user.p-utf8,passwd.p-utf8,DB.p-utf8)
mysql_data_seek(*result.MYSQL_RES,offset.d)
mysql_eof(*result.MYSQL_RES)
mysql_escape_string(strTo.p-utf8,strFrom.p-utf8,Length.l)
mysql_fetch_field(*result.MYSQL_RES)
mysql_fetch_field_direct(*result.MYSQL_RES,fieldnr.l)
mysql_fetch_fields(*result.MYSQL_RES)
mysql_fetch_lengths(*result.MYSQL_RES)
mysql_fetch_row(*result.MYSQL_RES)
mysql_field_seek(*result.MYSQL_RES,offset.l)
mysql_field_tell(*result.MYSQL_RES)
mysql_free_result(*result.MYSQL_RES)
mysql_get_character_set_info(*mysql.MYSQL,*cs.MY_CHARSET_INFO)
mysql_hex_string(strTo.p-utf8,strFrom.p-utf8,Length.l)
mysql_kill(*mysql.MYSQL,pid.l)
mysql_library_init(argc.l,argv.l,Groups.l)
mysql_list_dbs(*mysql.MYSQL,wild.p-utf8)
mysql_list_fields(*mysql.MYSQL,table.p-utf8,wild.p-utf8)
mysql_list_tables(*mysql.MYSQL,wild.p-utf8)
mysql_master_query(*mysql.MYSQL,query.p-utf8,Length.l)
mysql_num_fields(*result.MYSQL_RES)
mysql_num_rows(*result.MYSQL_RES)
mysql_options(*mysql.MYSQL,option.l,arg.p-utf8)
mysql_query(*mysql.MYSQL,query.p-utf8)
mysql_real_escape_string(*mysql.MYSQL,strTo.p-utf8,strFrom.p-utf8,Length.l)
mysql_real_query(*mysql.MYSQL,query.p-utf8,Length.l)
mysql_refresh(*mysql.MYSQL,Options.l)
mysql_row_seek(*mysql.MYSQL,offset.l)
mysql_row_tell(*result.MYSQL_RES)
mysql_rpl_query_type(*mysql.MYSQL,type.l)
mysql_send_query(*mysql.MYSQL,query.p-utf8,Length.l)
mysql_server_init(argc.l,argv.l,Groups.l)
mysql_set_character_set(*mysql.MYSQL,csname.p-utf8)
mysql_set_server_option(*mysql.MYSQL,option.l)
mysql_shutdown(*mysql.MYSQL,shutdown_level.l)
mysql_slave_query(*mysql.MYSQL,query.p-utf8,Length.l)
mysql_ssl_set(*mysql.MYSQL,key.p-utf8,cert.p-utf8,ca.p-utf8,capath.p-utf8,cipher.p-utf8)
EndImport
;}- mySQL END
Structure mySQL_Login
mySQL_Host$
mySQL_User$
mySQL_Password$
mySQL_DB$
mySQL_Port.l
mySQL_UnixSocket$
mySQL_ClientFlag.l
*mySQL_Handle
FileNo.l
EndStructure
Threaded mySQL_Mutex.l
Global ThreadNo.l, IsThreaded.b = #False
Procedure.s RandomString(Length.l=5) ;- Return a random string
Protected Pos.l, String$=""
If Length
For Pos = 1 To Length
String$ + Chr(Random(25)+65)
Next
EndIf
ProcedureReturn String$
EndProcedure
Procedure.i mySQL_TryToConnect(*mySQL_Login.mySQL_Login, OpenFile.b=#False) ;- Return the *mySQL_Handle on success or 0
Protected *mySQL_Handle
LockMutex(mySQL_Mutex)
With *mySQL_Login
*mySQL_Handle = mysql_init(#Null)
If *mySQL_Handle
If Not mysql_real_connect(*mySQL_Handle, \mySQL_Host$, \mySQL_User$, \mySQL_Password$, \mySQL_DB$, \mySQL_Port, \mySQL_UnixSocket$, \mySQL_ClientFlag)
mysql_close(*mySQL_Handle)
*mySQL_Handle = 0
EndIf
EndIf
\mySQL_Handle = *mySQL_Handle
If OpenFile
\FileNo = CreateFile(#PB_Any, "mySQL_CrashTest_"+Str(mySQL_Mutex)+Str(*mySQL_Handle)+".txt")
Else
\FileNo = 0
EndIf
EndWith
UnlockMutex(mySQL_Mutex)
ProcedureReturn *mySQL_Handle
EndProcedure
Procedure.l mySQL_RealQuery(*mySQL_Handle, SQL$, FileNo=0, Delay.b=#False) ;- Return 0 on success otherwise the ErrorNo
Protected *mySQL_Result
If Delay : Delay(1) : EndIf
LockMutex(mySQL_Mutex)
If FileNo ; if FileNo is set
WriteStringN(FileNo, SQL$) ; store the command to the (debug) file
FlushFileBuffers(FileNo)
EndIf
If mysql_real_query(*mySQL_Handle, SQL$, Len(SQL$)) = 0
*mySQL_Result = mysql_use_result(*mySQL_Handle)
If *mySQL_Result
mysql_free_result(*mySQL_Result)
EndIf
EndIf
If mysql_errno(*mySQL_Handle)
WriteStringN(FileNo, "ERROR! "+ Str(mysql_errno(*mySQL_Handle))+" "+PeekS(mysql_error(*mySQL_Handle)))
FlushFileBuffers(FileNo)
EndIf
UnlockMutex(mySQL_Mutex)
ProcedureReturn mysql_errno(*mySQL_Handle)
EndProcedure
Procedure.l mySQL_RealQueryOut(*mySQL_Handle.l, SQL$, FileNo.l=0) ;- Retuen 0 on success and store the SQL result to FileNo otherwise return the ErrorNo
Protected Result.l = 0, *mySQL_Result, mySQL_NumFields.l, *mySQL_Fields.l, mySQL_CurrentField.l
Protected *mySQL_Field.MYSQL_FIELD, mySQL_FieldArraySize.l = SizeOf(MYSQL_FIELD)+SizeOf(Long)
Protected mySQL_CurrentRow.l, *mySQL_CurrentRow, *mySQL_Lengths, mySQL_FieldLength.l, *mySQL_FieldPtr
Protected DisplayString$, QueryResult.l, ErrorNo.l
LockMutex(mySQL_Mutex)
If FileNo
WriteStringN(FileNo, SQL$)
FlushFileBuffers(FileNo)
EndIf
Result = mysql_errno(*mySQL_Handle)
If Result
LockMutex(mySQL_Mutex)
WriteStringN(FileNo, "ERROR! "+ Str(Result)+" "+PeekS(mysql_error(*mySQL_Handle)))
FlushFileBuffers(FileNo)
UnlockMutex(mySQL_Mutex)
MessageRequester("ERROR!", Str(Result)+" "+PeekS(mysql_error(*mySQL_Handle)))
ProcedureReturn Result
EndIf
QueryResult = mysql_real_query(*mySQL_Handle, SQL$, Len(SQL$))
UnlockMutex(mySQL_Mutex)
If QueryResult
Result = mysql_errno(*mySQL_Handle)
If Result
LockMutex(mySQL_Mutex)
WriteStringN(FileNo, "ERROR! "+ Str(Result)+" "+PeekS(mysql_error(*mySQL_Handle)))
FlushFileBuffers(FileNo)
UnlockMutex(mySQL_Mutex)
MessageRequester("ERROR!", Str(Result)+" "+PeekS(mysql_error(*mySQL_Handle)))
ProcedureReturn Result
EndIf
Else
LockMutex(mySQL_Mutex)
*mySQL_Result = mysql_use_result(*mySQL_Handle)
UnlockMutex(mySQL_Mutex)
If *mySQL_Result
LockMutex(mySQL_Mutex)
mySQL_NumFields = mysql_num_fields(*mySQL_Result)
*mySQL_Fields = mysql_fetch_fields(*mySQL_Result)
UnlockMutex(mySQL_Mutex)
DisplayString$ = ""
For mySQL_CurrentField = 0 To mySQL_NumFields-1
*mySQL_Field = *mySQL_Fields+mySQL_FieldArraySize*mySQL_CurrentField
DisplayString$ + *mySQL_Field\Name + "; "
Next
If FileNo
LockMutex(mySQL_Mutex)
WriteStringN(FileNo, DisplayString$)
FlushFileBuffers(FileNo)
UnlockMutex(mySQL_Mutex)
EndIf
mySQL_CurrentRow = 0
Repeat
mySQL_CurrentRow+1
LockMutex(mySQL_Mutex)
*mySQL_CurrentRow = mysql_fetch_row(*mySQL_Result)
UnlockMutex(mySQL_Mutex)
DisplayString$ = ""
If *mySQL_CurrentRow
LockMutex(mySQL_Mutex)
*mySQL_Lengths = mysql_fetch_lengths(*mySQL_Result)
UnlockMutex(mySQL_Mutex)
For mySQL_CurrentField=0 To mySQL_NumFields-1
mySQL_FieldLength = PeekL(*mySQL_Lengths+4*mySQL_CurrentField)
*mySQL_FieldPtr = PeekL(*mySQL_CurrentRow+4*mySQL_CurrentField)
If *mySQL_FieldPtr
DisplayString$ + PeekS(*mySQL_FieldPtr, mySQL_FieldLength) + "; "
EndIf
Next
EndIf
If FileNo
LockMutex(mySQL_Mutex)
WriteStringN(FileNo, DisplayString$)
FlushFileBuffers(FileNo)
UnlockMutex(mySQL_Mutex)
EndIf
Until *mySQL_CurrentRow = 0
EndIf
LockMutex(mySQL_Mutex)
mysql_free_result(*mySQL_Result)
UnlockMutex(mySQL_Mutex)
EndIf
Result = mysql_errno(*mySQL_Handle)
If Result
LockMutex(mySQL_Mutex)
WriteStringN(FileNo, "ERROR! "+ Str(Result)+" "+PeekS(mysql_error(*mySQL_Handle)))
FlushFileBuffers(FileNo)
UnlockMutex(mySQL_Mutex)
MessageRequester("ERROR!", Str(Result)+" "+PeekS(mysql_error(*mySQL_Handle)))
EndIf
ProcedureReturn Result
EndProcedure
Procedure.s Init() ;- Load the threaded switch and the login data from the ProgramParameter list
Protected ParameterCount.l = 0, ParameterNo.l, Parameter$, ServerNo.l = 0
Protected Result$ = ""
While ProgramParameter(ParameterCount)
If UCase(ProgramParameter(ParameterCount)) = "THREADED"
IsThreaded = #True
EndIf
ParameterCount+1
Wend
If ParameterCount
ParameterCount-1
If IsThreaded
Global Dim Servers.mySQL_Login(ParameterCount-1)
Else
Global Dim Servers.mySQL_Login(ParameterCount)
EndIf
For ParameterNo = 0 To ParameterCount
Parameter$ = ProgramParameter(ParameterNo)
If UCase(Parameter$) = "THREADED"
Continue
EndIf
With Servers(ServerNo)
\mySQL_Host$ = StringField(Parameter$, 1, "|")
\mySQL_User$ = StringField(Parameter$, 2, "|")
\mySQL_Password$ = StringField(Parameter$, 3, "|")
\mySQL_DB$ = StringField(Parameter$, 4, "|")
\mySQL_Port = Val(StringField(Parameter$, 5, "|"))
\mySQL_UnixSocket$ = StringField(Parameter$, 6, "|")
\mySQL_ClientFlag = Val(StringField(Parameter$, 7, "|"))
\mySQL_Handle = mySQL_TryToConnect(Servers(ServerNo))
If \mySQL_Handle
Result$ + Chr(10)+\mySQL_User$+"@"+\mySQL_Host$
mysql_close(\mySQL_Handle)
Else
MessageRequester("ERROR!", "Can't connect to" + Chr(10) + \mySQL_User$+"@"+\mySQL_Host$, #MB_OK|#MB_ICONERROR)
End
EndIf
EndWith
ServerNo + 1
Next
EndIf
ProcedureReturn Result$
EndProcedure
Procedure RunQueries(*mySQL_Handle.l, Table$, FileNo.l) ;- Run queries on the table
If mySQL_RealQuery(*mySQL_Handle, "SELECT * FROM " + Table$ + "_temp_Date LIMIT 1", FileNo, #True) = 1146
If mySQL_RealQuery(*mySQL_Handle, "SELECT * FROM " + Table$ + "_temp_Date_x LIMIT 1", FileNo, #True) = 1146
mySQL_RealQuery(*mySQL_Handle, "CREATE" + #TempTable$ + "TABLE " + Table$ + "_temp_Date_x (NewDate date, date char(10), VCount Int(11), SELECTED tinyint(1) UNSIGNED Default 0, INDEX(NewDate)) As Select Date(CONCAT(Right(date,4),'-',Mid(date,4,2),'-',Left(date,2))) As NewDate, date, COUNT(*) As VCount FROM " + Table$ + " GROUP BY date ORDER BY NewDate", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "ALTER TABLE " + Table$ + "_temp_Date_x RENAME " + Table$ + "_temp_Date", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "UPDATE " + Table$ + "_temp_Date SET SELECTED=0", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "UPDATE " + Table$ + "_temp_Date SET SELECTED=1 WHERE NewDate>='2000-03-04' And NewDate<='2000-07-09'", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "DROP" + #TempTable$ + "TABLE If EXISTS " + Table$ + "_temp_Date_run", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "CREATE" + #TempTable$ + "TABLE " + Table$ + "_temp_Date_run SELECT * FROM " + Table$ + "_temp_Date WHERE SELECTED=1 ORDER BY VCount DESC", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "SELECT SUM(VCount) As VCSum FROM " + Table$ + "_temp_Date_run", FileNo, #True)
EndIf
EndIf
If mySQL_RealQuery(*mySQL_Handle, "SELECT * FROM " + Table$ + "_temp_Object_id LIMIT 1", FileNo, #True) = 1146
If mySQL_RealQuery(*mySQL_Handle, "SELECT * FROM " + Table$ + "_temp_Object_id_x LIMIT 1", FileNo, #True) = 1146
mySQL_RealQuery(*mySQL_Handle, "CREATE" + #TempTable$ + "TABLE " + Table$ + "_temp_Object_id_x (Object_id varchar(200), VCount Int(11), SELECTED tinyint(1) UNSIGNED Default 0) As SELECT Object_id, COUNT(*) As VCount FROM " + Table$ + " GROUP BY Object_id ORDER BY Object_id", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "ALTER TABLE " + Table$ + "_temp_Object_id_x RENAME " + Table$ + "_temp_Object_id", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "UPDATE " + Table$ + "_temp_Object_id SET SELECTED=0", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "UPDATE " + Table$ + "_temp_Object_id SET SELECTED=1 WHERE Object_id='"+RandomString()+"' Or Object_id='"+RandomString()+"' Or Object_id='"+RandomString()+"' Or Object_id='"+RandomString()+"' Or Object_id='"+RandomString()+"' Or Object_id='"+RandomString()+"' Or Object_id='"+RandomString()+"' Or Object_id='"+RandomString()+"' Or Object_id='"+RandomString()+"'", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "DROP" + #TempTable$ + "TABLE If EXISTS " + Table$ + "_temp_Object_id_run", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "CREATE" + #TempTable$ + "TABLE " + Table$ + "_temp_Object_id_run SELECT * FROM " + Table$ + "_temp_Object_id WHERE SELECTED=1 ORDER BY VCount DESC", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "SELECT SUM(VCount) As VCSum FROM " + Table$ + "_temp_Object_id_run", FileNo, #True)
EndIf
EndIf
If mySQL_RealQuery(*mySQL_Handle, "SELECT * FROM " + Table$ + "_temp_Start_Time LIMIT 1", FileNo, #True) = 1146
If mySQL_RealQuery(*mySQL_Handle, "SELECT * FROM " + Table$ + "_temp_Start_Time_x LIMIT 1", FileNo, #True) = 1146
mySQL_RealQuery(*mySQL_Handle, "CREATE" + #TempTable$ + "TABLE " + Table$ + "_temp_Start_Time_x (Start_Time time, VCount Int(11), SELECTED tinyint(1) UNSIGNED Default 0) As SELECT Start_Time, COUNT(*) As VCount FROM " + Table$ + " GROUP BY Start_Time ORDER BY Start_Time", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "ALTER TABLE " + Table$ + "_temp_Start_Time_x RENAME " + Table$ + "_temp_Start_Time", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "UPDATE " + Table$ + "_temp_Start_Time SET SELECTED=0", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "UPDATE " + Table$ + "_temp_Start_Time SET SELECTED=1 WHERE Start_Time='00:00:00' Or Start_Time='00:15:00' Or Start_Time='00:30:00' Or Start_Time='00:45:00' Or Start_Time='01:15:00' Or Start_Time='01:45:00' Or Start_Time='02:15:00' Or Start_Time='02:45:00' Or Start_Time='03:00:00' Or Start_Time='03:15:00'", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "UPDATE " + Table$ + "_temp_Start_Time SET SELECTED=1 WHERE Start_Time='03:30:00' Or Start_Time='03:45:00' Or Start_Time='04:00:00' Or Start_Time='04:15:00' Or Start_Time='04:30:00' Or Start_Time='04:45:00' Or Start_Time='05:00:00' Or Start_Time='05:15:00' Or Start_Time='05:30:00' Or Start_Time='05:45:00'", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "UPDATE " + Table$ + "_temp_Start_Time SET SELECTED=1 WHERE Start_Time='08:30:00' Or Start_Time='08:45:00' Or Start_Time='09:00:00' Or Start_Time='09:15:00' Or Start_Time='09:30:00' Or Start_Time='09:45:00' Or Start_Time='10:00:00' Or Start_Time='10:15:00' Or Start_Time='10:30:00' Or Start_Time='10:45:00'", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "UPDATE " + Table$ + "_temp_Start_Time SET SELECTED=1 WHERE Start_Time='11:00:00' Or Start_Time='11:15:00' Or Start_Time='11:30:00' Or Start_Time='11:45:00' Or Start_Time='12:00:00' Or Start_Time='12:15:00' Or Start_Time='12:30:00' Or Start_Time='12:45:00' Or Start_Time='13:00:00' Or Start_Time='13:15:00'", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "UPDATE " + Table$ + "_temp_Start_Time SET SELECTED=1 WHERE Start_Time='16:00:00' Or Start_Time='16:15:00' Or Start_Time='16:30:00' Or Start_Time='16:45:00' Or Start_Time='17:00:00' Or Start_Time='17:15:00' Or Start_Time='17:30:00' Or Start_Time='17:45:00' Or Start_Time='18:00:00' Or Start_Time='18:15:00'", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "UPDATE " + Table$ + "_temp_Start_Time SET SELECTED=1 WHERE Start_Time='21:00:00' Or Start_Time='21:15:00' Or Start_Time='21:30:00' Or Start_Time='21:45:00' Or Start_Time='22:00:00' Or Start_Time='22:15:00' Or Start_Time='22:30:00' Or Start_Time='23:00:00' Or Start_Time='23:30:00' Or Start_Time='23:45:00'", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "DROP" + #TempTable$ + "TABLE If EXISTS " + Table$ + "_temp_Start_Time_run", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "CREATE" + #TempTable$ + "TABLE " + Table$ + "_temp_Start_Time_run SELECT * FROM " + Table$ + "_temp_Start_Time WHERE SELECTED=1 ORDER BY VCount DESC", FileNo, #True)
mySQL_RealQuery(*mySQL_Handle, "SELECT SUM(VCount) As VCSum FROM " + Table$ + "_temp_Start_Time_run", FileNo, #True)
EndIf
EndIf
If mySQL_RealQueryOut(*mySQL_Handle, "SELECT * FROM " + Table$ + " INNER JOIN " + Table$ + "_temp_Start_Time_run ON " + Table$ + "_temp_Start_Time_run.Start_Time=" + Table$ + ".Start_Time INNER JOIN " + Table$ + "_temp_Object_id_run ON " + Table$ + "_temp_Object_id_run.Object_id=" + Table$ + ".Object_id INNER JOIN " + Table$ + "_temp_Date_run ON " + Table$ + "_temp_Date_run.Date=" + Table$ + ".Date", FileNo)
LockMutex(mySQL_Mutex)
WriteStringN(FileNo, "ERROR! "+ Str(mysql_errno(*mySQL_Handle))+" "+PeekS(mysql_error(*mySQL_Handle)))
FlushFileBuffers(FileNo)
End
EndIf
EndProcedure
Procedure RunTest(ServerNo.l) ;- Create db_myCrashTest_xxx.tbl_myCrashTest_xxx, add 2*4*24*265*16 records and run some queries on the table.
Protected *mySQL_Handle, SQL$
Protected mySQL_Database$, mySQL_Table$
Protected MyDate.l, yCounter.l, dCounter.l, hCounter.l
Protected FileNo.l, TestNo.q
TestNo = ElapsedMilliseconds(): Delay(2)
If mySQL_Mutex=0 : mySQL_Mutex = CreateMutex() : EndIf
*mySQL_Handle = mySQL_TryToConnect(Servers(ServerNo), #True)
FileNo = Servers(ServerNo)\FileNo
If *mySQL_Handle
mySQL_Database$ = "db_myCrashTest_"+Str(TestNo)+Str(*mySQL_Handle)
SQL$ = "CREATE DATABASE IF NOT EXISTS " + mySQL_Database$
If mySQL_RealQuery(*mySQL_Handle, SQL$, FileNo)
LockMutex(mySQL_Mutex)
WriteStringN(FileNo, "ERROR! "+ Str(mysql_errno(*mySQL_Handle))+" "+PeekS(mysql_error(*mySQL_Handle)))
FlushFileBuffers(FileNo)
UnlockMutex(mySQL_Mutex)
MessageRequester("ERROR!", Str(mysql_errno(*mySQL_Handle))+" "+PeekS(mysql_error(*mySQL_Handle)))
End
Else
mySQL_Table$ = "tbl_myCrashTest_"+Str(TestNo)+Str(*mySQL_Handle)
SQL$ = "CREATE TABLE IF NOT EXISTS " + mySQL_Database$ + "." + mySQL_Table$ + " (`Object_id` varchar(200) DEFAULT NULL, `Date` varchar(50) DEFAULT NULL, `Start_Time` time DEFAULT NULL, `Exchange_Id` varchar(200) DEFAULT NULL, `TEST1` int(11) DEFAULT NULL, `TEST2` int(11) DEFAULT NULL, `TEST3` int(11) DEFAULT NULL, `TEST4` int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1"
If mySQL_RealQuery(*mySQL_Handle, SQL$, FileNo)
LockMutex(mySQL_Mutex)
WriteStringN(FileNo, "ERROR! "+ Str(mysql_errno(*mySQL_Handle))+" "+PeekS(mysql_error(*mySQL_Handle)))
FlushFileBuffers(FileNo)
UnlockMutex(mySQL_Mutex)
MessageRequester("ERROR!", Str(mysql_errno(*mySQL_Handle))+" "+PeekS(mysql_error(*mySQL_Handle)))
Else
For yCounter = 0 To 15
MyDate.l = Date(2000+yCounter,1,1,0,0,0)
For dCounter = 1 To 365
For hCounter = 1 To 96
SQL$ = "INSERT INTO " + mySQL_Database$ + "." + mySQL_Table$ + " (`Object_id`, `Date`, `Start_Time`, `Exchange_Id`, `TEST1`, `TEST2`, `TEST3`, `TEST4`) VALUES "
SQL$ + "('" + RandomString() + "', '" + FormatDate("%dd/%mm/%yyyy", MyDate) + "', '" + FormatDate("%hh:%ii", MyDate) + "', '" + RandomString() + "', '" + Str(Random($7FFFFFFF)) + "', '" + Str(Random($7FFFFFFF)) + "', '" + Str(Random($7FFFFFFF)) + "', '" + Str(Random($7FFFFFFF)) + "'), "
SQL$ + "('" + RandomString() + "', '" + FormatDate("%dd/%mm/%yyyy", MyDate) + "', '" + FormatDate("%hh:%ii", MyDate) + "', '" + RandomString() + "', '" + Str(Random($7FFFFFFF)) + "', '" + Str(Random($7FFFFFFF)) + "', '" + Str(Random($7FFFFFFF)) + "', '" + Str(Random($7FFFFFFF)) + "')"
If mySQL_RealQuery(*mySQL_Handle, SQL$, FileNo)
LockMutex(mySQL_Mutex)
WriteStringN(FileNo, "ERROR! "+ Str(mysql_errno(*mySQL_Handle))+" "+PeekS(mysql_error(*mySQL_Handle)))
FlushFileBuffers(FileNo)
UnlockMutex(mySQL_Mutex)
MessageRequester("ERROR!", Str(mysql_errno(*mySQL_Handle))+" "+PeekS(mysql_error(*mySQL_Handle)))
End
EndIf
MyDate = AddDate(MyDate, #PB_Date_Minute, 15)
Next
Delay(1)
Next
Delay(1)
Next
; now we should have 1.121.280 records to work with
RunQueries(*mySQL_Handle, mySQL_Database$ + "." + mySQL_Table$, FileNo) ; run some queries
SQL$ = "DROP TABLE " + mySQL_Database$ + "." + mySQL_Table$
If mySQL_RealQuery(*mySQL_Handle, SQL$, FileNo)
LockMutex(mySQL_Mutex)
WriteStringN(FileNo, "ERROR! "+ Str(mysql_errno(*mySQL_Handle))+" "+PeekS(mysql_error(*mySQL_Handle)))
FlushFileBuffers(FileNo)
UnlockMutex(mySQL_Mutex)
MessageRequester("ERROR!", Str(mysql_errno(*mySQL_Handle))+" "+PeekS(mysql_error(*mySQL_Handle)))
End
EndIf
EndIf
SQL$ = "DROP DATABASE " + mySQL_Database$
If mySQL_RealQuery(*mySQL_Handle, SQL$, FileNo)
LockMutex(mySQL_Mutex)
WriteStringN(FileNo, "ERROR! "+ Str(mysql_errno(*mySQL_Handle))+" "+PeekS(mysql_error(*mySQL_Handle)))
FlushFileBuffers(FileNo)
UnlockMutex(mySQL_Mutex)
MessageRequester("ERROR!", Str(mysql_errno(*mySQL_Handle))+" "+PeekS(mysql_error(*mySQL_Handle)))
End
EndIf
EndIf
mysql_close(*mySQL_Handle)
Servers(ServerNo)\mySQL_Handle = 0
EndIf
If mySQL_Mutex : FreeMutex(mySQL_Mutex) : mySQL_Mutex = 0 :EndIf
If FileNo
CloseFile(FileNo)
EndIf
EndProcedure
Procedure CrashTest()
Protected WindowEvent.l, Running.q, ThreadCount.l, Servers$
mySQL_Mutex = CreateMutex()
Servers$ = Init()
ThreadCount = ArraySize(Servers())
MessageRequester("INFO", "CrashTest start."+Chr(10)+"Please wait for the 'finished' window."+Chr(10)+Chr(10)+"threaded = "+Str(IsThreaded)+Servers$, #MB_OK|#MB_ICONINFORMATION)
For ThreadNo = 0 To ThreadCount
If IsThreaded
CreateThread(@RunTest(), ThreadNo)
Else
RunTest(ThreadNo)
EndIf
Next
Repeat
Running = 0
For ThreadNo = 0 To ThreadCount
Running + Servers(ThreadNo)\mySQL_Handle
Next
Until Running = 0
MessageRequester("INFO", "CrashTest finished."+Chr(10)+Chr(10)+"If you get this message, there was no crash.", #MB_OK|#MB_ICONINFORMATION)
If mySQL_Mutex : FreeMutex(mySQL_Mutex) : mySQL_Mutex = 0 :EndIf
EndProcedure
CrashTest()
End
DisableExplicit'FlushFileBuffers(FileNo) ' fügte ich so oft hinzu, um stets alle Zeilen zu sehen während der Prozess läuft.
Übrigens: Der Fehler kommt auf allen Systemen: XAMPP, WAMP und von Hand installiert, lokal und übers Netzwerk (anderer Rechner) und auf 32bit und 64bit mySQL Servern.
Vielen Dank im Voraus für Eure Hilfe.
Sven