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.

iBoughtWhat?

I am now one step further down the path of becoming a MacWeenie. I stopped by the Apple store and picked up a MacBook Pro. My goal is to make this my primary computing machine. That means I will try some really fun but wrong things like installing Windows, Visual Studio and SQL server all in a virtual machine session. The sheer wrongness of that makes me giddy. I shouldn't have bought this tonight, I want to stay up all night just twiddling with it to understand it better. Instead, the massive headache I've had for a few hours and I are going to bed.

SQL Server and datatype conversions

Transact SQL (TSQL) Pop Quiz

DECLARE
    @sufficientStorage decimal(18, 2)
,   @insufficient decimal(18, 0)

SELECT
    @sufficientStorage = 45.77

SELECT
    @insufficient = @sufficientStorage

SELECT
    @sufficientStorage AS sufficientStorage
,   @insufficient AS insufficient

Does SQL Server say

A) Cannot explicitly convert from decimal(18, 2) to decimal(18, 0) due to a loss in precision

B) Implicitly round the value for you

C) Implicitly truncate the value for you

D) I have no idea what you're talking about nor do I care

E) Arithmetic overflow error converting numeric to data type numeric

Much to my surprise the answer ended up being B. I haven't checked BOL (books online) yet but this seemed odd. Most languages that I've been associated with will happily upsize values for you but you have to explicitly tell them "trust me, I know what I'm doing when I say I can squeeze this watermelon into a mason jar." Oh well, back to deciphering the code but that one was unexpected. I'll have to check BOL now because now I want to know what rounding method they use (yes, there is more than one way to round numbers, there is even an rounding standard to be used with any data going to the IRS if I recall correctly)

And yes, BOL has it all

"When SET NUMERIC_ROUNDABORT is ON, an error is generated after a loss of precision occurs in an expression. When OFF, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result."

Copy & Paste inheritance

Apparently I've never heard the phrase copy and paste inheritance but I love it. Much love to James for using that in our conversation today. It's wonderful and an appropriate way to describe how the SQL here was implemented.