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.

I still hate MySQL

Back in the MySQL saddle again. This time I'd like to update a table and it barks that I have an "invalid use of group function" errno 1111

    UPDATE
        HOME_WAREHOUSE HW
        INNER JOIN
            HOME_CHANGED HC
            ON HC.transaction_date = HW.transaction_date
            AND HC.home_id = HW.home_id
    SET
        HW.foo_count = count(DISTINCT HC.foo_id)
    WHERE
        HC.foo_change > 0

Sure looks fine to my TSQL eye. More work, less blog

MySQL

Good gravy! How people happily futz around with this database is beyond me. Incoherent ramblings follow.

I have a simple statement

    -- Add the new records
    INSERT INTO
        FOO.BAR
    (
        bar_id
    ,   bar_name
    )
    SELECT
        ED.bar_id
    ,   ED.bar_name
    FROM
        FOO.BAR_DAILY ED
        LEFT OUTER JOIN
            FOO.BAR E
            ON E.bar_id = ED.bar_id
    WHERE
        E.bar_id IS NULL

Does it like it? Noooo, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO"

Now, it'd be interesting except that if I run this code as is, it's fine. The second I wrap it in a stored procedure, MySQL gets its drawers in a twist. No clue yet how to resolve this puppy.

User names are case sensitive, that was a good one. I couldn't figure out why I was able to authenticate using the tool but not in my python script. It kept returning "Access denied for user" and yet when I'd log in with the tool, I had no issues. I was digging through the admin tool looking to see if perhaps we were restricting the applications that could talk to the database but no, I finally figured out my issue on that one. I guess that's just too much time in the windows world.

The data I'm working with is interesting. It's not the worst data I've ever seen but it's odd. Developed by a java guy on a windows box but we have Mac line endings. The numeric values have thousand seperators in there. How convenient for reading but storing 10,000.00 in a numeric field isn't going to fly. MySQL seems pretty touchy about dates as well. They're written out as MM/DD/YY and MySQL didn't care for that so I got to swap it out to YY/MM/DD HH:MM:SS. Not sure the time was required but I'm sick of dealing with this so rather than test and find out, I just assumed it was needed and coded it in there.

The ExecuteMany was an interesting beast. It was a prepared statement that I could pass a collection to and it'd just execute the sql for each row in the set. A poorman's bulk insert. Except for the default packet size issue. What's that you say? Get an error about "Lost Connection to MySQL server during query?" The default packet size to the server is 1Mb so if you're loading up a non-trivial amount of data in your query, executemany or regular execute, if the packet is too big you may get that error. The solution is to jack your packet size up to an amount greater than 1M and less than or equal to 1G (max). Below is the query to set packet size to the max

show variables like 'max_allowed_packet';
set global max_allowed_packet=1073741824;
set local max_allowed_packet=1073741824;
show variables like 'max_allowed_packet';

Transactions were another fun one. Perhaps MS SQL Server is the rare beast, I know it was different than Oracle but that's been years. So, transactions in Oracle as I recall were implicitly created for you and to make any actual data changes you needed to explicitly invoke commit transaction. MS world, implicit transactions are auto-committed if the statement(s) succeed. Explictly opened transactions only commit when you ask for a commit. MySQL, or at least the instance I'm working against on this freelance assignment is in the Oracle camp of doing things, at least through the MySQLdb python library. Perhaps that's something I can configure but given the extremely tight timeframe for this project, that's research time I don't have.

It should be worth it though, I'm expecting the pre-tax amount to cover the new MBP I bought. I've heard that my actual take-home after taxes will be roughly half but oh well.