2

Mule 4 App has been doing select from a SQL Server database around 20k rows and convert result set into pipe separated csv file and push the file to s3 using s3 connector.

In the database, we have columns like aId, bId, cId etc. When we checked the data in the csv file, value for aId is actual value for bId, value for bId is the actual value for cId in the database. ( only few places)

<db:select doc:name="Select" doc:id="e8d1eda4-3ecc-48e9-90ea-a19a0a4e20f9" config-ref="MSSQL_Database_Config">
    <db:sql >#['SELECT * FROM Table']</db:sql>
</db:select>
<ee:transform doc:name="Set Payload" doc:id="9b7e5bcc-e61b-427d-8507-8655db76cb83">
    <ee:message>
        <ee:set-payload><![CDATA[%dw 2.0
    output application/csv separator='|'
    ---
    payload]]></ee:set-payload>
    </ee:message>
</ee:transform>
<s3:create-object doc:name="Create object" doc:id="beea8757-7e69-4119-8529-e0c26be08b65" config-ref="Amazon_S3_Configuration" bucketName="bucket1" key="file123"/>

We have double check the database value, it confirm somehow the mule is changing the order of the columns value ( few places)

For example, in the database, aId bId cId values are like 1, 2, 3

In the CSV file, aId|bId|cId values are 2,3,1 (in a few places)

I have not configured anything extra in db connector/aws streaming all are default. Is there anything do i need to enable to fix this issue.

Thanks in advance

Infinity
  • 484
  • 2
  • 8
  • 21
  • Do you mean `1|2|3` and `2|1|3`? – aled Sep 29 '21 at 12:27
  • What version of Mule 4 are you using, and what patches if any are installed? What version of the MS SQL Driver and DB Connector? Are the examples real data? Please share actual data to see if there are any special characters that may cause issues. – aled Sep 29 '21 at 15:10
  • Yes its mule 4 application – Infinity Sep 29 '21 at 18:04
  • please see my comments fully. It is not the same if it is Mule 4.1.1 than 4.3.0. Same with patches applied, connectors versions, etc. – aled Sep 29 '21 at 18:06
  • Thanks for helping me. sorry for not providing details. Cloud hub Mule runtime 4.3.0.(no patches applied) DB Connector : 1.8.2 S3 Connector: 5.7.1 MS SQL: 7.4.1.jre8 . Dara looks XACF123F , YARF234, REAFYED123RERWDFSDSF( no special character) . – Infinity Sep 29 '21 at 18:20
  • I do have referred this thread https://help.mulesoft.com/s/question/0D52T00004mXTBhSAO/retain-column-order-in-database-select-resultset where it kinda similar issue discussed. db connector says 500 object in memory by default wonder its does multiply with different column order – Infinity Sep 29 '21 at 18:23
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/237651/discussion-between-aled-and-infinity). – aled Sep 29 '21 at 18:27
  • You could add logging before the transform to see if the data is swapped from the query before reaching DataWeave. Also using `*` in a select is not a good practice in SQL. – aled Sep 29 '21 at 18:28
  • @aled oh yes,I am already in the chat – Infinity Sep 29 '21 at 18:50

1 Answers1

0

Changing the repeatable file store values (database connector) to 50000 from 500 helps to resolve the issue. I raised the ticket with Mulesoft Support. Update here if I get any solution.

<db:select doc:name="Select" doc:id="e8d1eda4-3ecc-48e9-90ea-a19a0a4e20f9" config-ref="MSSQL_Database_Config">
    <ee:repeatable-file-store-iterable inMemoryObjects="50000" />
    <db:sql >#['SELECT * FROM Table']</db:sql>
</db:select>
Infinity
  • 484
  • 2
  • 8
  • 21