Problems with Preemptive Threads and massive database insert (new attempt with MBS Plugin)

[jury_buono1 (jury buono)]

Hi everyone, I’m (re)trying to use Preemptive Threads (for the second time) for bulk data insertion into a MySQL database. I’d already tried using them in the past with xojo 2025 and the Xojo plugin for MySQL, but the problem seemed to be the plugin itself, which wasn’t compatible with Preemptive Threads. I’d continued to use Cooperative Threads, which didn’t seem to have any problems but did crash the GUI.
Now I’m trying to use the MBS plugin, but I’m still having problems.
The application terminates on its own without reporting errors, even in debug. Or rather, the threads run for a while (about 2000 inserts), and then debugging closes the application without even reporting errors.

I created a thread that, in his run code:

• Opens the connection to the database then inserts one record

I instantiate four threads of this type (my PC has 8 cores), then I activate a timer that cycles through the instantiated threads every second and launches the threads that are not running.

this is the run code of thread :

Dim dbTH As New SQLDatabaseMBS
Dim sq As String 
dim i as Integer

dbTH.DatabaseName="mysql:127.0.0.1,3306@attivita"
dbTH.UserName="usertest"
dbTH.Password="pswtest"
Dim f As  FolderItem= New FolderItem("C:\Program Files\MySQL\MySQL Server 5.7\lib\libmysql.dll")
Call SQLGlobalsMBS.SetDllDirectory("C:\Program Files\MySQL\MySQL Server 5.7\lib")

dbTH.Option("CLIENT_MULTI_STATEMENTS") = "true"
dbTH.Option("CLIENT_MULTI_RESULTS") = "true"
dbTH.Option("AutoCache") = "true"

dbTH.SetFileOption SQLConnectionMBS.kOptionLibraryMySQL, f
Try
  dbTH.Connect
Catch err As RuntimeException
  Exit Sub
End Try


sq="insert into allegati (idAttivitaSecondaria,  datainserimento)  values( " +Str(Me.ThreadID+i)+",  now()  ) "
Try
  dbTH.ExecuteSQL(sq)
Catch err As RuntimeException 
  Exit Sub
End Try

this is the code inside the timer : (thar run each second )

Dim i As Integer

For i=0 To arrayTHREAD.Count-1 '..in this array there are the 4 instanced thread 
  If  arrayTHREAD(i).ThreadState=Thread.ThreadStates.NotRunning  Then
    Try
      arrayTHREAD(i).Start
    Catch err As RuntimeException

    End Try
  End If
Next i

after about 2000 inserts, the IDE/debug closed the application without generating any errors, but if i check the windows log events i found these kind of errors

Does anyone have any code examples showing how to use preemptive threads to continuously insert data into a database, or an explanation for these types of errors?

[Eric_Williams]

I would suggest you refactor your approach to use a few longer-lived threads that all pull data from a single source – say, an array of items – and then loops through them to insert them into the database. Your current approach uses the database connection once and discards it; you’ll pick up a massive amount of performance by reusing the connection.

This will also reduce or eliminate the need to poll the threads to see if they have ended. In fact, you really shouldn’t be polling them at all. When the threads end (i.e., when there is no more work in the shared array to process) they can send a message to the window or whatever that they have ended by calling a method. This method can remove any lingering references to the thread so it is properly destructed.

[jury_buono1 (jury buono)]

Thank you Eric for yours suggest, but i think that the problem Is use db insidie thread and only in preemptive thread beacuse in cooperative thread It works fine

The code thst i have posted Is an example is not real but Is to est to check if now (in the lateste versione of xojo and using other db plugin) works fine but It not work and creates a non interceptable error.

[jury_buono1 (jury buono)]

i have created issue
https://tracker.xojo.com/xojoinc/xojo/-/issues/81390

[Christian_Schmitz]

You could add some logging to know maybe within which method it crashes.

If you can make a small sample project to reproduce it, we may look into it.

[jury_buono1 (jury buono)]

The methods Ares only 2
One Is the timer execution
The second Is the run of thread
The code Is very simple ( db is mysql)

You can find the example code here

https://tracker.xojo.com/xojoinc/xojo/-/issues/81390

[Rick_Araujo]

@Eric_Williams

I would suggest you refactor…

I also believe that his problem is just about something related to design. I’m not sure why he even use timers here for example.

Jury, explain in simple English terms what you wish to achieve? Like:

I wish a method where I pass some data, and it send such data very fast to a background queue to be inserted in the database and the program continues without interruption.

For such case, for example, your design is not a good one.

[jury_buono1 (jury buono)]

OK, I’ll try: my
need is to receive a lot of data from many devices every second and, once received, decode it, process it, and insert it into a main database (MySQL in this case) as quickly as possible in real time.
There are just under 6,000 devices, and they transmit a large amount of information every second. To receive this data, I created about 40 listeners. To simplify things, the listeners receive the data, save it in a local database, and then the threads (cooperative, in actual configuration) process the data queue, sending it to MySQL.

This operation works if I use cooperative threads, but obviously the app GUI slows down significantly, and the operating system GUI also slows down significantly. I wanted to switch to preemptive thread management precisely for better performance and responsiveness, but using databases in preemptive threads causes the application and even the debugger to crash without reporting errors.

I was hoping that with xojo 2026 and MBS as the data source, I would have solved this problem, but that’s not the case.

Do you have any suggestions or code examples that use preemptive threads to bulk insert data into a database?

PS: The code I posted is very simple. Why wouldn’t it work if there are no unknown issues?

[Beatrix_Willius]

@jury_buono1

This operation works if I use cooperative threads, but obviously the app GUI slows down significantly, and the operating system GUI also slows down significantly. I wanted to switch to preemptive thread management precisely for better performance and responsiveness, but using databases in preemptive threads causes the application and even the debugger to crash without reporting errors.

If your app slows down then you need to redesign your app architecture. The preemptive threads can help somewhat but they are not a magic bullet.

[jury_buono1 (jury buono)]

Okay, thanks for the advice
but why doesn’t the simple code I posted work?
This isn’t the actual code, just some test code to try using the database within preempitive threads and does not wotk.

[Christian_Schmitz]

Well, I tried your sample on macOS and didn’t see a crash so far.

Did you put in the MBS license key?

Not that the plugin tries to show a dialog about the missing license, which can cause problems on a preemptive thread.

Also can you reproduce the crash on macOS or Linux, so we can get a crash log?

[Beatrix_Willius]

@jury_buono1

but why doesn’t the simple code I posted work?

The first thing to check is the actual crash. The bomb in Xojo is not very useful. @Christian_Schmitz recently recommended something from his plugin which I can’t find. Was that only for macOS??? But if you build the app then you should get a crash log.

[Sascha_S]

@Christian_Schmitz

Not that the plugin tries to show a dialog about the missing license, which can cause problems on a preemptive thread.

Maybe this is something you could change ?

[Beatrix_Willius]

@Beatrix_Willius

ecently recommended something from his plugin which I can’t find

Found it:

Sub Opening()
  SQLGlobalsMBS.SetLicenseCode "xxx", 12121212, 12122, 121212

  Call SignalHandlerMBS.SetPrintBacktraceAndAbortHandler(4)  // illegal instruction
  Call SignalHandlerMBS.SetPrintBacktraceAndAbortHandler(10) // bus error
  Call SignalHandlerMBS.SetPrintBacktraceAndAbortHandler(11) // access violation / segmentation fault
End Sub

This should work on Windows, too.

[Sascha_S]

How many of these inserts were submitted within a single thread of the array? Put another way, is it possible that the crash occurs the moment the timer accesses the thread array? I know that shouldn’t happen, but…

And given the volume of SQL queries, it would certainly be advisable to work in transaction batches.

I would also recommend controlling this via the AddUserInterfaceUpdate event rather than using a timer.

@jury_buono1

I’d already tried using them in the past with xojo 2025 and the Xojo plugin for MySQL, but the problem seemed to be the plugin itself, which wasn’t compatible with Preemptive Threads.

Based on my experience, I cannot confirm that. I use MySQL Community Server extensively in PE threads.

The event log message suggests a memory overflow. However, I cannot see any deep recursion or looping from your sample code. As others have already mentioned, you should reconsider your system design. Try working without timers—and perhaps even without an array of threads?

---

I could also imagine that Xojo’s PE threads might struggle with repeatedly accessing the same file simultaneously from four different threads. However, that is unlikely to be the root cause of the problem; otherwise, your code would have run stable when used with the mySQLCommunityServer Plugin.

[jury_buono1 (jury buono)]

Yes, i use my real licence but i have change to public example project .

[jury_buono1 (jury buono)]

In the example code I am not accessing any shared resources within the thread, so there should not be a concurrency issue on the same resource.

[Sascha_S]

Maybe it’s simply not a good idea to hit the SQL Server simultaneously from four PE threads?

Perhaps, without too much effort, you could write the incoming data from the devices into arrays, from which a single PE thread would then pull elements until no new ones remain? The thread could transmit a few hundred elements per transaction.

This would reduce the load on both the local machine and the server, and might still be fast enough to process the data?

[jury_buono1 (jury buono)]

Sorry, but it seems like you’re trying to circumvent a problem (if there really is a problem).

The project I shared uses four threads to write data to a database, and they aren’t concurrent because they’re called when each one has finished running.
Everything works if I use cooperative threads
if I use preemptive threads, it doesn’t work.

Now, we can discuss the architecture, the operating logic, or anything else (in the real code, additional queries and updates are also performed, which for obvious reasons can’t be done in batch mode).

The real problem I haven’t yet found an answer to is:
Can databases be used in code with four/eight/x instances of the same preemptive thread, which run more or less concurrently?

If there are limitations, it’s good to know;
if there are problems, it’s good to know.
If the code I wrote should work, I’ll wait for xojo, inc., to suggest a solution.

[Eric_Williams]

@jury_buono1

Sorry, but it seems like you’re trying to circumvent a problem (if there really is a problem).

That’s… literally the goal and point of this forum. Software has bugs - we’re all here to work around them.

@jury_buono1

Everything works if I use cooperative threads
if I use preemptive threads, it doesn’t work.

Plenty of things work in cooperative threads that don’t work the same way in preemptive threads. The fact that you have that situation right now is not necessarily indicative of a bug; it could just be that your code isn’t properly designed for preemptive threads.

@jury_buono1

If there are limitations, it’s good to know;
if there are problems, it’s good to know.
If the code I wrote should work, I’ll wait for xojo, inc., to suggest a solution.

If you want help, post your actual code/project. The questions you have posed cannot be answered without it.