[FNS-18] Truncation errors while importing data

Description

Truncation errors while importing data

You may find that the default column widths configured by the Analytics for FusionReactor application (e.g. fusionreactor-fadc-1.0.5-DCAPP.zip) are not long enough for the data supplied by your server. If a value is too big to fit into the belonging target column the value will be truncated before it is inserted and an error message is written to the fusionanalytics-datacollector.log and fusionanalytics-datacollector-error.log files as shown below:

...
2011-11-03T10:59:45.135-0400 [FADC-importer-3] ERROR [com.intergral.fusionanalytics.dc.evt.FileMonitorEventHandler] myApp/myDC/request.log: 2011-11-03 10:59:45,135 [LINE] 6578 [ERROR] "myApp/myDC/request.log (ID=3c8ffa2a-413f-472b-b0cc-fab9c277854f): Truncated value of column JSESSIONID from 54 to 50 characters: Original value: d4309ef9d92a84dfeddc5e364d59535b6b26,+cftoken=13194335: Truncated value: d4309ef9d92a84dfeddc5e364d59535b6b26,+cftoken=13194335" [INPUT] "2011-11-03 10:57:01.692 1320332221692 4 1320259710907 98327 COMPLETED "" jrpp-124 10.10.37.249 GET http://10.0.0.10/index.cfm 93 15 8108992 1221932 8108992 6887059 "fuseaction=lms.enterClassSection&classSectionId=48506" 302 78 "" d4309ef9d92a84dfeddc5e364d59535b6b26,+cftoken=13194335 387467 85523722 0 0 0 0 0 0 0 0 93"
...

In this case you can change the column size as follows:

1. Stop the FusionAnalytics Service/Daemon
2. Open the DCML file of the application you want to change (e.g. C:FusionAnalyticsServerdatafadcApplicationsmyApplicationmyApplication.dcml) in an editor.
3. Search all columns definitions you want to update and replace their column size with the new value.

Example: Changing the size of the JSESSIONID column from 50 to 100

<FadcConfig version="1.0" xmlns="https://www.fusion-analytics.com/datacollector">
  <DataOutput dbType="mssql">
    ...
    <Table name="request_status" engine="InnoDB">
      ...
      <Column type="varchar(50)" required="false" name="jsessionid"/>
    </Table>
    ...
    <Table name="request" engine="InnoDB">
      <Column type="varchar(50)" required="false" name="jsessionid"/>
    </Table>
    ...
    <Table name="crashprotection" engine="InnoDB">
      <Column type="varchar(50)" required="false" name="jsessionid"/>
    </Table>
    <Table name="request_status_report" engine="InnoDB">
      <Column type="varchar(50)" required="false" name="jsessionid"/>
    </Table>
    ...

becomes

<FadcConfig version="1.0" xmlns="https://www.fusion-analytics.com/datacollector">
  <DataOutput dbType="mssql">
    ...
    <Table name="request_status" engine="InnoDB">
      ...
      <Column type="varchar(100)" required="false" name="jsessionid"/>
    </Table>
    ...
    <Table name="request" engine="InnoDB">
      <Column type="varchar(100)" required="false" name="jsessionid"/>
    </Table>
    ...
    <Table name="crashprotection" engine="InnoDB">
      <Column type="varchar(100)" required="false" name="jsessionid"/>
    </Table>
    <Table name="request_status_report" engine="InnoDB">
      <Column type="varchar(100)" required="false" name="jsessionid"/>
    </Table>
    ...

4. Save the updated DCML file
5. Update the columns in the database

Following the example above, you should now execute the following SQL statements:

-- MySQL
ALTER TABLE CRASHPROTECTION MODIFY JESSESIONID VARCHAR(100); 
ALTER TABLE REQUEST MODIFY JESSESIONID VARCHAR(100); 
ALTER TABLE REQUEST_STATUS MODIFY JESESSIONID VARCHAR(100); 
ALTER TABLE REQUEST_STATUS_REPORT MODIFY JESSESIONID VARCHAR(100); 

-- Microsoft SQL Server
ALTER TABLE CRASHPROTECTION ALTER COLUMN JESESSIONID VARCHAR(100);
ALTER TABLE REQUEST ALTER COLUMN JESESSIONID VARCHAR(100);
ALTER TABLE REQUEST_STATUS ALTER COLUMN JESESSIONID VARCHAR(100);
ALTER TABLE REQUEST_STATUS_REPORT ALTER COLUMN JESESSIONID VARCHAR(100);

6. Restart the FusionAnalytics Service/Daemon

This completed the procedure. You can now insert data with JESESSIONID values up to 100 characters long without truncation errors.

Issue Details

Type: Technote
Issue Number: FNS-18
Components: DataCollector
Environment:
Resolution: Fixed
Added: 14/11/2011 14:42:18
Affects Version: 1.0.0
Fixed Version: 1.0.0
Server:
Platform:
Related Issues: None