ADF, DB2 and unexpected token query error

In yet another exciting episode of head to desk, I have a beautiful DB2 query. It runs just fine in a tool like DBeaver.

SELECT 
    STR.CPY_CD
,   STR.STR_CD
,   STR.DFLT_FAC_NBR -- I think this is division number
,   current date || 'T' || VARCHAR_FORMAT(CURRENT TIMESTAMP, 'HH24:MI:SS')  AS CurrentDateTime
FROM 
    FOO.CUSTMASTER AS STR
FOR FETCH ONLY WITH UR

Punch it into ADF and it fails, repeatedly with this ever-so-helpful error message. What is going on here?

Operation on target Copy StoreDivision to ADLS failed: Failure happened on 'Source' side. ErrorCode=DB2DriverRunFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Error thrown from driver. Sql code: '-104',Source=Microsoft.Connectors.Db2,''Type=Microsoft.HostIntegration.DrdaClient.DrdaException,Message=An unexpected token "END-OF-STATEMENT , STR.DFLT_FAC_NBR " was found following "". Expected tokens may include: "". SQLSTATE=42601 SQLCODE=-104,Source=Microsoft.HostIntegration.Drda.Requester,'

After pasting the query into a text editor and examining each character in hex mode, no, no there are no "weird" unprintable characters hiding in my query. I mean, the only thing weird about the fourth line is that I have a comment there. But that syntax is totally valid for DB2...

Oh gentle reader, I'm sure there's a page somewhere in documentation that indicates carriage returns/line feeds are stripped from queries when subsmitted at least for Db2 connectors but I'd really like to stop running into surprises with Azure Data Factory. At least, that's my best guess as to what is happeneing because BEHOLD, this query works

SELECT 
    STR.CPY_CD
,   STR.STR_CD
,   STR.DFLT_FAC_NBR /* I think this is division number */
,   current date || 'T' || VARCHAR_FORMAT(CURRENT TIMESTAMP, 'HH24:MI:SS')  AS CurrentDateTime
FROM 
    FOO.CUSTMASTER AS STR
FOR FETCH ONLY WITH UR

Why do I think the CR/LFs are stripped? Because if I run this query in a tool like DBeaver, it fails and you won't believe what the error message is

SELECT     STR.CPY_CD,   STR.STR_CD,   STR.DFLT_FAC_NBR -- I think this is division number 

SQL Error [42601]: An unexpected token "END-OF-STATEMENT" was found following ", STR.DFLT_FAC_NBR". Expected tokens may include: "".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.31.10

So, the moral of the story? Always use the /**/ comment syntax in your queries. Not just in ADF but everywhere because you never know when formatting is going to get eaten.

ADF ForEach Activity

This occasional series of posts I'm calling Notes from the field is primarily for my benefit as I don't work with Azure Data Factory with any regularity and have no muscle memory in it. And the amount of sharp edges and non-intuitive baked in the product is quite frustrating for me. If you derive value from it, all the better.

The Azure Data Factory ForEach Activity shreds some enumerable thing but here I want to reference a Lookup to ForEach.

When I click on the ForEach Activity, in the "Items" section. I click "Add dynamic content" and am presented with these wonderful choices (given that my canvas consists of a Lookup Activity named "LKP_ReferenceTables" and the ForEach wired as a successor:

My options are

LKP_ReferenceTables

LKP_ReferenceTables activity output

LKP_ReferenceTables

LKP_ReferenceTables pipeline return value

LKP_ReferenceTables count

Count of the rows

LKP_ReferenceTables value array

Array of row data

and if I pick "activity output" the dyanmic expression is @activity('LKP_ReferenceTables').output WILLIAM! DO NOT CLICK OK, yet. If you accept the default code, you're going to get a lovely error in the form of Operation on target FE_ReferenceTables failed: The function 'length' expects its parameter to be an array or a string. The provided value is of type 'Object' You need to add the ".value" property to the expression to get the actual stuff you want @activity('LKP_ReferenceTables').output.value

Orrrr, I pick Array of row data and that generates the expected @activity('LKP_ReferenceTables').output.value Le sigh

ADF and MySql.Data.MySqlClient.MySqlException,Message=Got a packet bigger than 'max_allowed_packet'

Azure Data Factory, ADF, and exception MySql.Data.MySqlClient.MySqlException,Message=Got a packet bigger than 'max_allowed_packet'

My StackOverflow developer profile specifies "I'd prefer to not work with" and honestly, the only thing I don't want to deal with is MySQL. I don't like Visual Basic or Access or plenty of other things but good grief, I find working MySQL to be an absolute cesspit after every other RDBMS I've worked with. Which brings me to an overdue client project, consolidating various MySQL instances to a single reporting server. They have a standard schema on all the boxes (no really, that was my biggest fear but they're good at ensuring the nearly 200 sites have the exact same point release of code) and I needed to bring it to a single server so it can be fed into reports.

It seemed like a great fit for Azure Data Factory but I kept getting an error dealing with some packet size issue. What do I know about packet sizes? Error details Error code 2200 Failure type User configuration issue Details 'Type=MySql.Data.MySqlClient.MySqlException,Message=Got a packet bigger than 'max_allowed_packet' bytes,Source=MySqlConnector,''Type=MySql.Data.MySqlClient.MySqlException,Message=Got a packet bigger than 'max_allowed_packet' bytes,Source=MySqlConnector,'

What's the internet got to say about all this? I checked the setting on a server that worked and one that didn't SHOW VARIABLES LIKE 'max_allowed_packet'; but they both listed 4194304 (bytes).

Beyond changing configuration settings, and no guarantee that solves the issue, the idea of inconsistent table definition sounded promising. But no dice. I tried making all the fields nullable, but to no avail. I ran the mysqldump utility from the commandline to see if I could reproduce the packet issue. Nothing.

After a lot of frustration, I looked hard at the custom integration logs. Before I move data, I copy over the source information_schema.tables for the database and store the TABLE_ROWS for each table. That number is a approximately the number of rows in the table. In the copy activity itself, I log the actual rows transferred and that's when I noticed something. The largest set of data from a single source was 6k rows. ALL OF THE HOSTS THAT GENERATED THE MAX PACKET EXCEPTION HAD MORE ROWS THAN 6K.

Well, what if the issue is one of volume? That's easy enough to test. I put a LIMIT 1000; on the query and pointed ADF at the server that never transferred data for that table. It worked. Sonofa. Ok, LIMIT 5000; Worked. Removed the limit - Failed, got a packet bigger than 'max_allowed_packet'

The error is not being generated from the source as I assumed. Normally, ADF says whether it's the source or the sink that caused the error. The exception makes sense if the error is on the sink. "You're sending too much data in one shot" would be a more useful error.

How do we fix it

The default Write Batch Size for a Copy Activity is 10,000. I dropped the size to 5000 and ran through all the troublesome hosts. Of the 51 hosts that would never transfer the suspect table, every.single.one.worked.