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.