mySQL - Invalid memory access
Posted: Wed Mar 10, 2010 4:13 pm
Hi,
Does someone have experience on working with big mySQL tables [>>1.000.000 records*] (and threaded mySQL access)?
* can even be much more than 100 million records!
Working on small databases I have no error but as soon as the table grows up to 1 million records I suddenly get a "Invalid memory access" on calling mysql_real_query.
I coded a CrashTest to create >1.000.000 records and work on it.
Creating the db and the table is no problem.
Adding the records is no problem.
But working on the table (CREATE TEMP TABLE or ALTER TABLE or UPDATE TABLE) sometimes causes this 'Invalid memory access' error.
Another strange thing is the memory usage:
It's constantly growing up to > 600.000K to be suddenly at >40.000K which happened after adding about 900.000 records.
Is this my weak coding, PureBasic or mySQL (libmySQL.dll) memory management?
Could someone please have a look at the code:
You can find all source, error log, dll, etc. here: CrashTest04.7z or CrashTest04.zip
What we do not need to discuss is the strange date format (dd/mm/yyyy). I can't change this.
And 'FlushFileBuffers(FileNo) '. I added this line to be up to date looking at the log file while the process is running.
By the way, I get the error on XAMPP, WAMP and hand made installations, on local and network access and on 32bit and 64bit mySQL servers.
Thanks in advance for your help.
Does someone have experience on working with big mySQL tables [>>1.000.000 records*] (and threaded mySQL access)?
* can even be much more than 100 million records!
Working on small databases I have no error but as soon as the table grows up to 1 million records I suddenly get a "Invalid memory access" on calling mysql_real_query.
I coded a CrashTest to create >1.000.000 records and work on it.
Creating the db and the table is no problem.
Adding the records is no problem.
But working on the table (CREATE TEMP TABLE or ALTER TABLE or UPDATE TABLE) sometimes causes this 'Invalid memory access' error.
Another strange thing is the memory usage:
It's constantly growing up to > 600.000K to be suddenly at >40.000K which happened after adding about 900.000 records.
Is this my weak coding, PureBasic or mySQL (libmySQL.dll) memory management?
Could someone please have a look at the code:
You can find all source, error log, dll, etc. here: CrashTest04.7z or CrashTest04.zip
Code: Select all
; 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
DisableExplicitAnd 'FlushFileBuffers(FileNo) '. I added this line to be up to date looking at the log file while the process is running.
By the way, I get the error on XAMPP, WAMP and hand made installations, on local and network access and on 32bit and 64bit mySQL servers.
Thanks in advance for your help.