topic Re: WFA - Custom Data Source Load Error in Active IQ Unified Manager Discussions

WFA - Custom Data Source Load Error

Gab — Fri, 16 Aug 2019 10:17:55 GMT

Hello,

I have an issue to load data into my scheme wince we upgraded in WFA v5.0 (before we were running WFA4.2).

Here is the error during acquisition :

Incorrect integer value: 'N' for column 'id' at row 1
Query is: LOAD DATA INFILE 'C:/Program Files/NetApp/WFA/jboss/standalone/tmp/wfa/dfstarget.csv' INTO TABLE wfa_staging.mytable_dfstarget

Does anyone knows what changed since WFA v5.0 ?

Or should I change my script ?

Thanks in advance for your help

Re: WFA - Custom Data Source Load Error

Yanic — Fri, 16 Aug 2019 13:12:58 GMT

Hi,

It seems that the data contained in your .csv file doesn't match the data type defined in MySQL table structure.
But without knowing what the csv file contains, nor the table structure it's difficult to be more specific.

Does your CSV files contains a header displaying the column names ?

Could you may be share the first line of your csv file and a 'describe wfa_staging.mytable_dfstarget' ?
(You can remove the sensible data if any from this csv extract, the value matter less than knowing what kind of data this field contains)

As documented in the releases notes, here are the main changes introduced by WFA 5.0 :

• Added security hardening to secure WFA systems

• New features for OnCommand Workflow Automation (WFA) 5.0

◦ HTML5-based UI for Operator Portal

◦ The wfa-server service on Linux runs as a non-root user. This is done to improve security for WFA on Linux

◦ HTTP access to the WFA Web UI and REST interface is unavailable. All WFA HTTP Web URLs redirect to HTTPs

• Upgraded software The following software versions are included:

◦ Wildfly upgrade from Wildfly 8.2 to Wildfly 12.0

◦ ActiveState ActivePerl 5.24.3 (2404) with OpenSSL 1.0.2k (Windows only)

◦ Perl components of NetApp Manageability SDK 9.5

◦ Data ONTAP PowerShell Toolkit 4.7.0.0

◦ Oracle Java SE Runtime Environment (JRE) 1.8.0_192

◦ MySQL Community Edition 5.7.24

Your script may be affected by the upgrade of either the Powershell Toolkit or the Manageability SDK.
Have a look at their respective releases notes foor further details regarding the changes introduced and if they may affect your code.

Bests Regards,

Re: WFA - Custom Data Source Load Error

Gab — Fri, 16 Aug 2019 13:53:31 GMT

Hi Yanic,

We haven't any header in the clumns.

Here is our script, you would see how we build our two CSVs files:

$fldrfile='.\dfsfolder.csv'
$targetfile='.\dfstarget.csv'
new-item -type file $fldrfile -Force
new-item -type file $targetfile -Force

$mydfsnroots=(get-dfsnroot).path
$mydfstargets=

foreach ($dfsroot in $mydfsnroots){
$mydfsnfolders=get-dfsnfolder "$dfsroot\*"
foreach ($dfsnfolder in $mydfsnfolders){
>Add-Content $fldrfile ([byte[]][Char[]] ("\N`t"+(($dfsnfolder.path+"`t"+$dfsnfolder.state+"`t"+$dfsnfolder.timetolivesec) -replace '\\','/')+"`n")) -Encoding utf8
Get-DfsnFolderTarget $dfsnfolder.path |% {Add-Content $targetfile ([byte[]][Char[]] ("\N`t"+(($_.path+"`t"+$_.targetpath+"`t"+$_.state+"`t"+$_.ReferralPriorityClass+"`t"+$_.ReferralPriorityRank) -replace '\\','/')+"`n")) -Encoding utf8}
}
}

As we don't query any information to the netapp, only our DFS, is it really impacted by Powershell Toolkit and Manageability SDK ?

Best regards

Re: WFA - Custom Data Source Load Error

Gab — Fri, 16 Aug 2019 14:07:38 GMT

Find below an example of both CSVs content.

1st CSV (dfsfolder):

\N	//domain/ou/share	Online	300

2nd CSV (dfstarget):

\N //domain/ou/share //vserver/share/folder Online sitecost-normal 0

Re: WFA - Custom Data Source Load Error

Yanic — Fri, 16 Aug 2019 14:14:56 GMT

@Gab wrote:

As we don't query any information to the netapp, only our DFS, is it really impacted by Powershell Toolkit and Manageability SDK ?

Best regards

I'm encline to reply "NO !", if you don't query a NetApp filer, it shouldn't affect you.

Re: WFA - Custom Data Source Load Error

Yanic — Fri, 16 Aug 2019 14:21:19 GMT

Hi,

I think this is where it hurts.

Your rows are composed as following :
<string> <string> <string> <integer>
Is the '\N' value expected ?

Where, based on MySQL error message, the first column of your table is a numeric id.

It then looks to me that you need to either :
1) Adapt your CSV to ensure that the first field of each row contains a numeric id, which will be used as primary key in your DB
2) Make use of "NOT NULL AUTO_INCREMENT" in your table for the column 'id'.
This way, if the id value is not specified, MySQL will pick the next available id automagically for you
Exemple : https://www.w3schools.com/sql/sql_autoincrement.asp

Hope this helps,

Bests Regards,

Re: WFA - Custom Data Source Load Error

jcbettinelli — Mon, 19 Aug 2019 13:13:02 GMT

Hi Yanic,

As my colleague is on vacation, I took over his issue.

As he mentioned, the issue started after the upgrade from 4.x to version 5.

We had followed the instructions from the video below:

https://community.netapp.com/t5/Data-Infrastructure-Management-Software-Articles-and-Resources/Video-Building-your-first-script-based-custom-datasource-using-a-file/ta-p/87679

The \N was mandatory to be inserted in the CSV file, so WFA could recognize it as a new line.

There was also a reference to it in the following thread:

https://community.netapp.com/t5/Data-Infrastructure-Management-Software-Discussions/How-to-create-an-own-data-source/td-p/78018

We haven't modified the script, which was working well until the upgrade.

So far, it seems that it doesn't recognize the " \N" as an integer, which makes sense of course.

Maybe there is a new method to populate the custom database, but I haven't found any information so far.

Re: WFA - Custom Data Source Load Error

DavidK12 — Wed, 19 Aug 2020 02:48:43 GMT

I'm experiencing the same issue when upgrading from 4.x to 5.1

our customer datasources are breaking because before leaving integer blank was recognized as null

but now WFA 5.x expects \N for all integer fields.

Is there any way to disable this behavior ?