ActiveRecord connecting to database not supported with multiple client request simultaneously

[thealgorithms10]

As checked in samples from previous build up to latest (3.2.3-radium), commonly the connecting to database was been implemented during on Controller's OnCreate or OnBeforeRouting, and other before events then to be followed by OnDestroy or OnAfterRouting with an implementation of Adding Connection at FDManager via Connection Name followed by removing the connection with Connection Name - this was simillar also in Middleware.ActiveRecord.

Attached file is the Middleware.ActiveRecord's implementation:

Issue here is that, this kind of implementation can't be support simultaneous request from client due to here's the scenario:

• Client has a button that has a list of requests to send and receive (this is a sync feature let's say, that will sync bunch of different records and masterlist to server, at the same time will update the masterlist to client local data)

• Client no.1 and Client no. 2 has been firing the sync feature simultaneously

• Client no. 1 has already done executing the 1st request (already fired the OnAfterRouting let's say) while Client no. 2 still ongoing on the 1st request

• since you were implementing the RemoveConnection (other samples was based on AddDefaultConnection / RemoveDefaultConnection), all running connection during processing - or let's say will use the RemoveConnection by defining it's name, still Client no. 1 and Client no. 2 has similar connection name/not unique - that will cause to removing connection of all client's request including the requests not been done

• so the error will occur

I can't provide a sample codebase on it but that's the entire scenario. Probably i can record my client's occurrence

[danieleteti]

Connection names are unique per thread. There is no problem using the same connection names because the actual connection name is "threadid_{connectioname}". The scenario you are talking about cannot happend. If it happens then something else (bad) is happening.

[thealgorithms10]

this was already happening @danieleteti not quite sure if there are other mistakes in implementation. And this was based on the sample been given. Will attach the current video

[thealgorithms10]

https://drive.google.com/file/d/1G_dEoKAm_67oOtx_9k13PxRDke62I2iJ/view?usp=sharing

Screen record via common connection implementation (OnBeforeRouting/OnBeforeAction, OnAfterRouting/OnAfterAction)

[thealgorithms10]

https://drive.google.com/file/d/1clukmYVbTbhmTgyg5hykk2NroKx3-khj/view?usp=sharing

Screen record via Middleware.ActiveRecord implementation

[thealgorithms10]

Can this reopen?

[danieleteti]

Yes, it must be investigated. Thank you for your analysis.

[thealgorithms10]

Thanks! while still diagnose/creating alternative solution for it

[danieleteti]

I did some deep tests using the sample project "simple_api_using_mvcactiverecord" and no problems raised at all. Are you using the latest version?

[RDP1974]

Hi Daniele,

can perhaps you redo the test using my libs, to see the speed up? https://github.com/RDP1974/Delphi64RTL

let me know if you need my help thank you kind regards

Roberto Della Pasqua ○|∆ 139ᵒ Software | Developer | Architect www.dellapasqua.comhttp://www.dellapasqua.com/

Da: Daniele Teti @.> Inviato: venerdì 21 aprile 2023 15:57 A: danieleteti/delphimvcframework @.> Cc: Subscribed @.***> Oggetto: Re: [danieleteti/delphimvcframework] ActiveRecord connecting to database not supported with multiple client request simultaneously (Issue #653)

I did some deep tests and no problem raised at all. Are you using the latest version?

[image]https://user-images.githubusercontent.com/1428644/233654689-29b3348b-b8b1-4748-b898-f704e62a2d65.png

— Reply to this email directly, view it on GitHubhttps://github.com/danieleteti/delphimvcframework/issues/653#issuecomment-1517875003, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ASJYYI2VFPLQL4BSQ3YCM7LXCKG3RANCNFSM6AAAAAAXEIER4I. You are receiving this because you are subscribed to this thread.Message ID: @.**@.>>

[thealgorithms10]

Maybe @danieleteti don't use the project the sample you've been given. I don't know how to test it actually using test prototypes but the main point of the issue is this:

• You will have a 2 clients that has a button that will send tons of requests to webservice
• At 1st client, will fire the button then example, at the 4th request (that will take too long due to processing of large data), then client 2 will fire the button to send also tons of requests
• that's the time the 1st client will be interupted

btw, i was using the latest 3.2.3-radium

[danieleteti]

Can you use the latest version please?

[thealgorithms10]

yes 3.2.3-radium is that right?

[danieleteti]

No, the lastest version is 3.3.0-fluorine

[thealgorithms10]

Ah it was released 3days ago sorry for late check. However @danieleteti i don't this this is working due to there were no changes at latest 3.3.0-fluorine related on the concern posted. But I will test.

Btw, hope the scenario given for the concern has been understood, tell me if there were other clarifications. Thank you

[danieleteti]

Hi,

sorry I cannot reproduce the problem. Can you provide a self-contained project wich reproduce the issue?

[thealgorithms10]

Hmm okay will try to provide a prototype instead @danieleteti but i think this is quite difficult to apply a prototype since this was occurred with a concrete data. Will try it, i'll update tomorrow

[thealgorithms10]

Hi @danieleteti ! My apologies on the very late response.

Anyway, I already prepared the prototype for this issue. Before this, further note that the project involved was using 3.2.1-carbon. Screenrecord presented was initially apply at latest build (3.3.0-fluorine) but not been polished etc. So, much better we will conduct a prototype to test the issue.

Below are the details of the prototype's feature - not that i've been use the basics implementation: Server

• using MySQL 8.0; implemented via 64bit

• been used ActiveRecord

• only 3 controllers been created, target only the routes for fetching

• there are two projects been created (attached a groupproj), implemented via middleware and via BeforeAction/AfterAction

Client

• Two features been apply, to test connection and to download masterlist from webservice (routes for fetching)

• implemented via thread

• already prepared method for processing of download without thread

• Platform is for windows and android

Below is the prototoype project: https://drive.google.com/file/d/1J8LIEYn-75WUQeQV0G_m-pmgEJquGSnp/view?usp=sharing

Summary of the diagnose/test results:

• found out that the dmvc been used was modified (3.2.1-carbon) - as i remembered
• the current 3.2.1-carbon has bug in multiple client implementation request simultaneously when using BeforeAction/AfterAction
• been fixed at 3.3.0-fluorine
• however, there were still bug in 3.3.0-fluorine and don't know if it is major or minor
• but atleast I am already okay at the current build temporarily (since it was a different error shown, but in similar scenario.)

Details of the diagnose/test results: since my dmvc is not updated, i preserve/backup the dmvc currently used and replace to the latest build 3.3.0-fluorine

I try first to test at windows.

• I copy the exe file of client in order to test different clients simultaneously syncing/downloading/working with webservice

• i first use the middleware activerecord webservice

• client's first scenario been trying was client No. 1 was ongoing download/sync while client No. 2 was attempting to test the connection to webservice; test is passed

• next, execute the next scenario - client No. 1 was ongoing download/sync while client No. 2 will sync after 10secs (purposely to delay it); test is passed

• next, trying to add 3 clients - i duplicate the client's exe file then test it based on test procedures mention before; test is passed - however, I encounter error out of my repeatedly testing - error message is "argument out of range", and i forgot to take screenshot on it

• However, 8 or 9 out of 10 been passed as of test

• since my project was using the BeforeAction/AfterAction, next was trying to use the different webservice exe file - implemented using BeforeAction/AfterAction

• execute tests based on the test cases/procedures mentioned; and it was been passed out of my many attempts (i didn't encounter the error i mentioned earlier)

• But, i remember that it was been used the dmvc 3.3.0-fluorine, so i try to revert my dmvc currently used (3.2.1-carbon)

• Back to test the middleware, but i was shocked that on my pre-test at Postman, there were error occurred at 2nd time request - it says that "duplicate connection error..or something, i forgot it", and this was the error i remember i encountered at the video/screen record i presented in this post

• So on this result, i already suspected that yes probably there were wrong on the dmvc i've been using all this time - but i don't know what happen on my test results been presented at video/screen record. Maybe it's because of the other logics/algo been applied at the current project - which is very different when I create a prototype; focuses only on the particular goal - the multiple client requests with basics

• And iI remember that there were changes/modifications been apply at my dmvc's copy last previous months; diagnose/fixing the particular issue encountered in my research (e.g. the Word datatype remember, and others)

• So, I attempt to re-download the 3.2.1-carbon, and as expected, middleware activerecord was working - test is passed. So, atleast there were few findings in this cases

• However, i was working at BeforeAction/AfterAction implementation, so attempting to to test the differenet webservice project for BeforeAction/AfterAction

• First try, 2 clients. Note, i was using the original copy of 3.2.1-carbon (freshly downloaded from git releases), and i was shock the issue encountered - Webservice crashes when simultaneously downloading/syncing - which is the common error we've been encountered at our projec

• In this tests, I confirmed that

• 1. our dmvc copy was modified
• 2. 3.2.1-carbon has bug when using BeforeAction/AfterAction
• 3. current build 3.3.0-fluorine has been fixed this issue. Maybe there were issues encountered in a small rate

However, our project was an Android platform, so i migrated the test to android platform

• Prepared multiple android emulators - btw, i was using Bluestacks for the android emulator

• So, I stick the webservice at dmvc 3.3.0-fluorine

• I test first the middleware activerecord implementation, and as expected - it was passed; however the error i encountered at windows was still occurred on some of my test.

• passed 9 out of 10

• Then, I proceed to test the BeforeAction/AfterAction, also as expected - it was been passed; and still there were errors i encountered similar at my tests at windows at some of my test

• passed 8-9 out of 10

• With this diagnose results, atleast this was better rather than previous results I shown up to you. But maybe still needed to check the issue been encountered.

[thealgorithms10]

Below are the screenshots of the errors i encountered (this was on Android platform btw).

Webservice logs was also on the project been forwarded, i zip it. for further copy will attach here incase. Since there were logs at MiddlewareActiveRecord webservice app as said in the screenshot, maybe i will attach only the ManualActiveRecord webservice app (this was implemented via BeforeAction/AfterAction). https://drive.google.com/file/d/1wMdJS5ifGXYNBKWlxMo5PP4FNkbnYaj_/view?usp=sharing https://drive.google.com/file/d/1ZjI5qjpT7HBrL6Plv0l25ap-HKbr6Pni/view?usp=sharing

Also, i checked also the taskmanager's status while monitoring - it has a factor i think due to multiple clients was sending requests, and those errors been occurred while the computer was okay - CPU was ranged 50-60%, RAM was ranged 60-70 I guess

[thealgorithms10]

With this update, i don't know if it was still valid. But I am okay to close this thread/post. But I think still needed to check the errors currently presented. Maybe at some new post

[RDP1974]

Hi Daniele,

I have read you have to implement multiple concurrent ops over database? Can I suggest some things? Look, Firedac should use custom driver definition using POOLED directive, Then the Connection, Transaction and Query can be built at runtime when needed with high speed (without the connection time penalty). The components should use Unidirectional on and Live off options, to boost performances (avoiding useless live grid buildings and cursor bidirectional). Eventually do you have a thread for every connection? You can further optimize using the DB components wrapped in a TLS threadvar, so you don’t need to create and free each time. If you don’t have each connection in a separate thread, you can use the system.threading thread pool to dispatch dynamically a thread from a pool. Example: system thread pool -> TLS db component -> query (this avoid thread creation, db connection and components creation)

Probably you already know these settings so please sorry me if I’m over useless words. Kind regards.

Roberto Della Pasqua ○|∆ 139ᵒ Software | Developer | Architect www.dellapasqua.comhttp://www.dellapasqua.com/

Da: thealgorithms10 @.> Inviato: venerdì 5 maggio 2023 05:12 A: danieleteti/delphimvcframework @.> Cc: Roberto Della Pasqua @.>; Comment @.> Oggetto: Re: [danieleteti/delphimvcframework] ActiveRecord connecting to database not supported with multiple client request simultaneously (Issue #653)

With this update, i don't know if it was still valid. But I am okay to close this thread/post. But I think still needed to check the errors currently presented. Maybe at some new post

— Reply to this email directly, view it on GitHubhttps://github.com/danieleteti/delphimvcframework/issues/653#issuecomment-1535645393, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ASJYYI2EBVQWUKA5MWT5EFLXERVXPANCNFSM6AAAAAAXEIER4I. You are receiving this because you commented.Message ID: @.**@.>>