SQL user import crashing - potential memory leak

[Darren KIng]

Hi all,

We're having a problem using the SQL import software to import user to site mappings.  Small tests seem to work but bigger files (the main file we have is around 12,000 records) crash with a memory error.  Even splitting the files down so we only process a hundred records at a time can often give us the same issue.

We used the same process (just using different SQL and config files) to successfully import all 12,000 users and link them to their primary site.

Here's the error we are getting....

runtime stack:

runtime.throw(0x877140, 0xd)

        C:/SPF/Go/src/runtime/panic.go:547 +0x7f

runtime.(*mcache).refill(0xf10000, 0x5, 0x61c7bbd0)

        C:/SPF/Go/src/runtime/mcache.go:121 +0xce

runtime.mallocgc.func2()

        C:/SPF/Go/src/runtime/malloc.go:642 +0x2b

runtime.systemstack(0x11232000)

        C:/SPF/Go/src/runtime/asm_386.s:313 +0x5e

runtime.mstart()

        C:/SPF/Go/src/runtime/proc.go:1051

We then get many thousands of lines of debug information thrown out (essentially reporting every remaining line failing)

I've attached the config file we are using.

Looking at the memory usage of the application it constantly grows and crashes when it hits around 1.4Gb.  The PC it's running on still had 8Gb of RAM available and we get the same issue if we try it on another computer.

Looking at the log file there is nothing of any use and the routine stops at the update customer line each time, here's the last snippet in case it helps...  (customer specific info removed)

2017/08/17 16:21:26 [DEBUG] User ID: #100
2017/08/17 16:21:26 [DEBUG] Update Customer: #1
2017/08/17 16:21:26 [DEBUG] 2017/08/17 15:21:26 [MESSAGE] SQL Attribute for Org Lookup: ############
2017/08/17 15:21:26 [DEBUG] Found Org in Cache #############
2017/08/17 15:21:26 [DEBUG] User: #1 Already Added to Organisation: ##############
2017/08/17 15:21:26 [DEBUG] Processing User Profile Data #1
2017/08/17 15:21:26 [DEBUG] User Profile Update Success

 

2017/08/17 16:21:26 [DEBUG] User ID: #101

 

 

Fingers crossed someone can help!.

 

Many thanks,

Darren

config 2 - user import to link to org structure - Copy.json

[Hornbill Staff DR]

Hi Darren,

 thanks for your post.

I can see some discussions taking place on our workspaces and it appears a possible cause has been identified. I have asked development to post an update as soon as they know more.

Dan

[SamS]

Hi @Darren KIng,

Though admittedly, I haven't tested the script with more than 10.000 entries, I would first be wondering what is returned with your query results.

In your example, you are running SELECT * on a view, this could potentially bring in more results that actually mapped by yourself. Some of those extra fields might be longvarchar and/or blob fields which would use a lot of RAM to store. IF they are not being mapped, then I would ensure those are not included in the results.

Could you please run SELECT for each field you are actually using - or confirm to me here that the result does not contian many more (longvarchar/blob) fields ?

eg:

SELECT objectID, siteType, ... FROM view_#####

 

[SamS]

Hi @Darren KIng,

We tested the original executable with 20.000+ entries and it got well beyond the 100th user your log file suggests it ended on.

That being said, we did find a place to optimize it and have released it (as version 1.2.2) on github [ https://github.com/hornbill/goDb2HUserImport/releases ].

I have also updated the wiki instructions to emphasize the SQL best practice to specify fields in the SQL SELECT statement

[Darren KIng]

Thank you both,

New version of the code seems to be running much better and has fully imported the files.

One thing I am now noticing though is that the program doesn't exit on completion.

I get the usual progress bar and then Processing Complete! displayed and then the import software appears to pause.

I can exit the software manually but this may cause some problems when run as a scheduled task.

Please can this be investigated?

Thanks,

Darren

 

[Hornbill Staff DR]

Hi Darren,

 thanks for confirming the changes have been successful in addressing the memory leak.
I have followed up with development in relation to your most recent comment regarding the application not existing upon completion and I understand that they are currently looking into this.

Dan

[SamS]

Hi @Darren KIng,

A new release incorporates the fix for this: https://github.com/hornbill/goDb2HUserImport/releases/tag/1.2.3

[Darren KIng]

Thank you @sams.

That has fixed the problem and all is working well

Darren