FVS: Failed pre-audit (Step 2) when processing BioSum output data

[Cat-Ducat-USDA]

I created a new project to use for 5.11.1 validation. When I got to the output data stage and initiated the pre-audit, it returned a failed audit message stating "FVSOut.db ERROR: 761 fvs_cutlist trees for variant PN could not be found in the FIADB tree table by matching FVS_Tree_Id (see audit table audit_fvs_tree_id). Jeremy and I have both verified that the trees are present (all with IDs, no nulls) in master.tree and FVS_Cutlist tables, but the audit_fvs_tree_id table indicates the tree IDs are not found. All 761 trees are not being found. We also ran an old project through 5.11.1 to see if we could replicate, but the audit passed for the older CWI project.

I posted the zipped project, called cwi_5.11.1validation_10.01.24, in the temp 30 days folder.

[lbross]

We might need to go back to the fiadb database to troubleshoot this one. Can you tell me which state you are using? And the file date/size so I am sure we have the same one?

[Cat-Ducat-USDA]

Hey Lesley, I am using the Washington FIADB data - I am zipping it up and am posting it in the temp 30 days folder. It says the total size of the folder is 1987 MB. It should be up in the next few mins.

[Cat-Ducat-USDA]

Sorry, 247,886 KB total size of the 7z file.

[lbross]

Thanks! In the future, I can also download it from the FIADB site, but I appreciate the assist. And can you also send me the list of plots you are loading?

[lbross]

Just found that this is a problem with the SQL statement for the audit and that the data in your test project is fine. I need to add some trim() statements in the SQL where we are joining on fvs_tree_id. We've run into this before when joining MS Access tables to SQLite tables, but I am puzzled that it worked in v5.11.0 but doesn't in v5.11.1. Maybe the data format is slightly different in the source FIADB database? Anyhow ... it's an easy fix that we should get into v5.11.1. I'll post a new .msi soon.

[Cat-Ducat-USDA]

Oh excellent! Do you still need my plot data, or is this good to go?

[lbross]

I've got all I need for now. Thank you!!

[Cat-Ducat-USDA]

Hey @lbross, so... bad news bears. I am working on the new 'starter project' using plots in CWI. When I ran the pre-append audit (FVS Output data), I got this error message again saying that cutlist trees for variant EC could not be found in the FIADB tree table by matching FVS_Tree_Id. Did we ever find a solution for this issue? The issue could be with the plot data, maybe? This is not the exact same plot data we used for the actual CWI project.

[jsfried]

I suspect that we might find a workaround by trimming extra spaces out of whichever column has them. Can look at this with you after 3 or 3:15 most likely

Get Outlook for iOShttps://aka.ms/o0ukef

---

From: catducat @.> Sent: Friday, October 11, 2024 12:37:05 PM To: USFS-PNW/Fia-Biosum-Manager @.> Cc: Subscribed @.***> Subject: Re: [USFS-PNW/Fia-Biosum-Manager] FVS: Failed pre-audit (Step 2) when processing BioSum output data (Issue #361)

Hey @lbrosshttps://github.com/lbross, so... bad news bears. I am working on the new 'starter project' using plots in CWI. When I ran the pre-append audit (FVS Output data), I got this error message again saying that cutlist trees for variant EC could not be found in the FIADB tree table by matching FVS_Tree_Id. Did we ever find a solution for this issue? The issue could be with the plot data, maybe? This is not the exact same plot data we used for the actual CWI project.

— Reply to this email directly, view it on GitHubhttps://github.com/USFS-PNW/Fia-Biosum-Manager/issues/361#issuecomment-2408018424, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AEFM5IB36WNFFLZ6S5X4G63Z3ASGDAVCNFSM6AAAAABPGRS2MCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMBYGAYTQNBSGQ. You are receiving this because you are subscribed to this thread.Message ID: @.***>

This electronic message contains information generated by the USDA solely for the intended recipients. Any unauthorized interception of this message or the use or disclosure of the information it contains may violate the law and subject the violator to civil or criminal penalties. If you believe you have received this message in error, please notify the sender and delete the email immediately.

[Cat-Ducat-USDA]

Sounds good Jeremy, thanks!

[lbross]

This is fixed with the next version that nobody has yet. 98% that it's a red herring. I would continue with the remaining steps of the project.

[Cat-Ducat-USDA]

That would make sense, the tree cut recs column still populated and there are trees in all of the relevant tables. Fabulous, thanks!

[Cat-Ducat-USDA]

Well... ended up getting this. If it's a plot data issue, Jeremy and I can look at it this afternoon, but I have never seen this before.

[Cat-Ducat-USDA]

@lbross Jeremy and I took another look through the data and nothing appears amiss. We tried to rerun and reappend but ran into the same error messages. We did note, however, that biomass information is not populating in the FVSOUT_TREE_LIST in the FVS_CutTree table, specifically... seems like there's a Java code-related issue. I zipped up the folder (titled cwi_mini_24.10.11) and put it in the temp 30 days box!

[lbross]

This is usually an issue with the java setup on the host computer. Do you still have v5.11.0 on this computer? If so, could you try an FVS append with an older project? If it's a problem with the java configuration, it should fail everywhere. You may also want to double check to see if you have a JDK >= 1.8 installed. If this is a USFS computer, it's possible that I/T pushed a software update that broke java. I believe the USFS options for this component are Oracle SDK and OpenJDK

[lbross]

Just ran into a related issue in Processor where fvs_tree_id needs to be trimmed before it can run successfully. I'll try to post a new .msi for Cat soon so that she can successfully create the reference project. This doesn't have anything to do with the Java error though, so that can be worked through if I don't get the .msi out right away. But don't try to run Processor for now.

[Cat-Ducat-USDA]

I do not still have v5.11.0, but can redownload if needed? IT definitely pushed some kind of software update... but I found OpenJDK and am downloading it presently. After that I'll download the new msi!

[Cat-Ducat-USDA]

I installed OpenJDK and it still did not fix the Java execution error. Looking like I'll need to get on with help desk. I have downloaded the new msi but need to fix this part first I suppose.

[lbross]

Hi Cat. Did you restart after installing OpenJDK? This tripped me up a couple of installs ago. If you type 'java -version' at a command or powershell prompt it should tell you which version of Java is installed. I also had to restart after installing the SQLite ODBC drivers on a new Windows 11 computer which I haven't had to do before ...

[Cat-Ducat-USDA]

@lbross Jeremy ran cwi_mini on his computer with v5.11.1 as well and ran into the same error with the java execution (he got the cutlist error messages as well).

Heads up, I downloaded the new msi, but still ran into the cutlist error messages at the pre-append audit stage.

Jeremy then tried to run cwi_mini in 5.11.0 (I don't have it anymore), but we can't even open up the project in 5.11.0 because it was made in 5.11.1. The error flags a database change in fvsmaster.db. Any thoughts on how we could modify to try and run in 5.11.0?

So, after that, Jeremy ran an old CWI project in 5.11.0 and it worked (both pre-audit and append). I then ran the same version of the old CWI project in 5.11.1, and it also successfully passed the audit and appended.

It would appear this Java execution issue is limited to cwi_mini.

[Cat-Ducat-USDA]

Hi Cat. Did you restart after installing OpenJDK? This tripped me up a couple of installs ago. If you type 'java -version' at a command or powershell prompt it should tell you which version of Java is installed. I also had to restart after installing the SQLite ODBC drivers on a new Windows 11 computer which I haven't had to do before ...

Yes, I did! Was forced to.

[lbross]

Can you tell me what it says when you run the java -version command? I'll download and take a look at cwi_mini this morning.

[Cat-Ducat-USDA]

It says openjdk version "11.0.24" 2024-07-16 (build 11.0.24+8-LTS) and OpenJDK 64-Bit Server VM Microsoft-9905872, etc.

[jsfried]

You need a space before the dash

[Cat-Ducat-USDA]

Yep, I realized that after I commented... just edited my earlier comment to reflect what it actually says.

[Cat-Ducat-USDA]

Full message: openjdk version "11.0.24" 2024-07-16 LTS OpenJDK Runtime Environment Microsoft-9905872 (build 11.0.24+8-LTS) OpenJDK 64-Bit Server VM Microsoft-9905872 (build 11.0.24+8-LTS, mixed mode, sharing)

[jsfried]

This is what mine shows:

C:\Windows\SysWOW64>java -version java version "1.8.0_421" Java(TM) SE Runtime Environment (build 1.8.0_421-b09) Java HotSpot(TM) 64-Bit Server VM (build 25.421-b09, mixed mode)

Lesley- Can you run the mini project on your computer?

[lbross]

I'm getting the error again for the audit and it's throwing a java stacktrace with it tries to run the FICS .jar. This is something we haven't seen before. I'm digging into it. No further action required from your side at this point.

[jsfried]

Ugh! this suggests there is something different in the kind of input we are throwing at FICS in cwi_mini vs other projects, right? You can't complain that BioSum lacks code diversity! My hats off to those of you capable of handling debugging in C#, R, Java, Microsoft frameworks (Db engine, DAO, ADO), SQLite, etc. with such aplomb. Good luck with it!

[lbross]

Right. The error suggests something to do with the spcd. I'm about to send it to Jason. I found some spcd = -1 in the biosum_calc table which is bizarre because there aren't any in master.tree like this. But even when I deleted them, it wouldn't work. It may be another place where fvs_tree_id isn't joining correctly. I'll keep you posted.

[jsfried]

Could our jar not be set up for 4 digit species codes?

[lbross]

I didn't see any in the biosum_calc table so I don't think that's it.

[jsfried]

I see lots of -1 values in species in the biosum_volumes_input table of FVSOUT_TREE_LIST.DB. I checked the biosumCondId of one of them and that cond does not even show up in master.cond of the project-- not sure what is going on there...

[lbross]

Yes. I recommend you work on something else for a bit. I'll be working on this today but will be out tomorrow. I'll keep you posted.

[jsfried]

So for clarity, since we've tasked Cat with getting a demo project put together as a top priority, she needs to switch to something else whilst you and Drue resolve these issues, right?

[lbross]

This one's on me currently, but yes. I think I just found some trees in master.tree that don't have parent records in master.cond. I'm not sure how that could possibly happen so I may need some help with that once I confirm.

[lbross]

@Cat-Ducat-USDA Can you send me the list of plot_cn's that you used to create this project? I have found 158 tree records with biosum_cond_id not in the cond table. I don't know how this could happen. You can run this query in master.mdb to get the list. I am open to suggestions as to how this might have happened. SELECT tree.biosum_cond_id, fvs_tree_id FROM tree WHERE NOT EXISTS (SELECT 1 FROM cond WHERE cond.biosum_cond_id = tree.biosum_cond_id);

[jsfried]

CWI_CNs_2002_2021.txt

We did that strept together and this is the list of CNs that we uploaded, using the latest EVALID (532101) to filter it to those that are in that EVALID (These CNs span from 2002 to 2021).

[jsfried]

I just ran that query on the copy of the project on my computer (before I posted it for Cat) and it returned no trees. Cat's current version returns 158 trees. I think that Cat then did the Assign FVS Variant step and I coached her through that because there was something that the BUG docs didn't make clear. She can tell you what she did with the project after that

[jsfried]

My copy of the project has 1745 conditions; hers has 1740. I do remember something about trying to remove the conditions that were reserved from the project and that being a struggle that Cat ultimately decided to resolve by exclusing them at the optimizer step. But perhaps conditions got removed and trees did not...

[lbross]

OK. Since we haven't see this before, my suggestion is to rewind and continue from Jeremy's version of the project. I think the -1's are coming from these trees that are linked to conditions missing from master.cond. I'm continuing to follow-up with Jason because even when I remove the -1's, I'm still getting the same error.

[jsfried]

Agreed. Will let you know whether new project loads FVSOut successfully when we get there. Curious how trees got preserved even with conditions deleted and that their spcd got set to -1. Cat found some other anomaly that she will share with you

[Cat-Ducat-USDA]

I created a new project that I am loading the same plot data into currently to see where things were corrupted. I appended the plot data from the correct evalid and the return message said I had 1745 conditions and only 32,920 trees... in master.mdb, it says 40,368 trees. Not sure what that discrepancy is about, but my numbers match Jeremy's at this point. Are we able to do a query to remove trees associated with reserved conditions, then remove the reserved conditions, or will we need to also do this for seedling and sitetree? Ultimately just curious if that would fix the issue... however, we are leaning toward using the new project I am making so that our demo project is 'clean.'

[jsfried]

We are going to leave these reserved areas in the project and not attempt to remove them from the master before proceeding on the logic that we want a stable demo that is not potentially corrupted by analyst overrides. We will need to remember to filter them out at the optimizer stage so that we don't accidentally present results that are based in part on logging in wilderness. The intermediate results (volvalbysppdiamclass and harvest_costs) will nonetheless be corrupted as they will contain results for such harvests that cannot actually happen. We can't simply omit plot CNs from the plot load list because reserve status is defined at the condition level not the plot level and a plot can have both reserved and unreserved conditions (even if rare). At some point, we will need to document at safe way for analysts to remove unwanted conditions and their trees (and sitetrees and seedlings to if necessary).

[lbross]

Leaving the reserved areas in the project for this demo is the best plan. I don't feel comfortable suggesting a series of queries that could remove these conditions. Maybe once we get the condition zapper rewritten for SQLite, that could be a potential solution. And I don't know that I'd say the Processor output is "corrupted" but yes, it will contain results tat shouldn't be included. The -1's are showing up in the biosum_calc table (input for FICS) because the query that populates them includes a link to the condition table which prevents those trees from being updated. The spcd in master.tree are fine.

Wanted to add that this should also fix the problem with the pre-audit as that query involves the cond table.

[Cat-Ducat-USDA]

Morning all, just wanted to update everyone that I have finally got the new version of cwi_mini (I'm calling cwi_demo) past the FVSOut stage. No cutlist error messages, no java execution issues (which confirms that removing the conditions prior to running but not the trees caused the crash), and no other issues. Successfully passed both audits! On to processor...

[lbross]

From @Cat-Ducat-USDA: Audits successfully passed with no errors (cwi_demo)