Thursday, April 06, 2023

The Mystery of the NULL Values in a NOT NULL DATETIME MySQL Database Column

My development team at work yesterday picked up a task to investigate a error that was periodically showing up in our production logs:

NoMethodError: undefined method `>' for nil:NilClass

This is a common Ruby error that occurs when you try to call a method on a variable with a value of nil. In this case, the "method" in question was the greater-than operator, ">". 

The line of code associated with the error in question was actually a conditional evaluation that involved 3 separate ">" evaluations, which obscured the exact variable that was the source of the error a bit. 

Two of the three variables in question were attributes of an ActiveRecord model object whose associated database column was defined as NOT NULL. Those attributes were not being re-assigned after being read from the database, so we initially ruled them out as being the possible cause of the error.

However, further investigation revealed that none of the three variables on the problematic line of code could -- in theory -- possibly ever be null. Faced with this, I decided to take a closer look at the actual data in the database.

Our production environment is split into multiple MySQL databases. For purposes of conveniently being able to query customer data across databases all at once, we have an ETL process which extracts (non-sensitive) customer data from the source databases, and populates it all into a single central Snowflake SQL database. 

The database structure of the relevant table (Users) was similar to this (simplified for brevity):

  • id: INT, primary key
  • first_name: VARCHAR(255)
  • last_name: VARCHAR(255)
  • updated_at: DATETIME, NOT NULL

Temporarily putting common sense aside, I queried the snowflake database to see if any of the NOT NULL updated_at values were nevertheless null:

SELECT * FROM Users WHERE updated_at IS null

This returned 0 results, as expected. 

Acting on a hunch, I tried searching for unexpectedly old records; this database has been in service since about 2009:

SELECT * FROM Users WHERE updated_at < '2005-01-01'

This query did produce some results! Out of the tens of thousands of records in the Users table, a few hundred records were returned whose updated_at date was '1970-01-01 00:00' -- a value equal to the start of the epoch in Unix time

At this point I questioned: Does Ruby on Rails and/or ActiveRecord somehow treat start-of-epoch date values of '1970-01-01 00:00' at nil? This seemed unlikely, but I tested it anyway, setting the updated_at value for an existing record in my local machine's copy of the database to that start-of-epoch value; and then reading the record into the corresponding ActiveRecord model object. Not unexpectedly, the updated_at attribute on the model did not end up with a nil value; it had the expected value of midnight on January 1, 1970.

I still felt like I might be onto something here, though. At this point, I wanted to inspect the data in the actual MySQL customer database. I don't have access to the live database, but I was able to access a read-only replica of the database.

Having connected to that MySQL database, I verified that the updated_at column was still defined at NOT NULL; it was. I then ran the same query to look for old values:

SELECT * FROM Users WHERE updated_at < '2005-01-01'

As before, this returned a few records -- about 300. However, I noticed that the actual updated_at values were different. This time, they were returned as '0000-00-00 00:00'. 

Year "0", month "0", day "0" -- all nonexistent values. Feeling a bit of a chill, I re-ran my earlier query to look for records with null values:

SELECT * FROM Users WHERE updated_at IS null

MySQL returned the same 300 records

Cool, cool, coolcoolcool. The evidence indicated that (1) MySQL was allowing values of '0000-00-00 00:00' to be set on a DATETIME, NOT NULL column; and (2) despite the aforementioned NOT NULL restriction on the column, was evaluating such values as being null.

To close the loop, back on my local development machine's database, I set an existing record to the '0000-00-00 00:00' value -- which MySQL happily allowed -- and then, in the Rails console, populated a model object from the record. Sure enough: The updated_at attribute on the record had been assigned a value of nil.

Summary of findings

(1) In a Rails application (and possibly in other languages / frameworks as well), it may not be safe to assume that a value read from a DATETIME, NOT NULL column on a MySQL database is actually guaranteed not to be null. MySQL allows a value of '0000-00-00 00:00' to be set in such columns (despite the NOT NULL restriction); such values are treated as null both my MySQL itself, and by Rails / ActiveRecord.

(2) Although I'm not familiar with its inner workings, that the ETL process from the multiple production MySQL databases to consolidate data into a single Snowflake database couldn't 100.0% be trusted not to change data values. In this case, it silently converted MySQL DATETIME values of '0000-00-00 00:00' to values of '1970-01-01 00:00' 


Doing a bit of additional reading after the fact, I found a StackOverflow post that references a MySQL configuration mode of "NO_ZERO_DATE," which when set, prevents such "zero date values" from being set. 

According to the MySQL documentation, not setting that option may be "more convenient" and/or consume less space. Based on my team's experience here, though, I'd certainly be inclined to take advantage of that NO_ZERO_DATE setting whenever feasible!

Wednesday, December 28, 2022

The Jon Schneider Game of the Year awards: 2022

Starting with my personal Game of the Year for 2022, and continuing on with the rest of the top 10, these were my favorite games that I played for the first time this year (along with the platform(s) on which I played them)!

1. Persona 5 Royal (Switch)

Probably the most expansive single-player RPG I've ever played, I was hesitant to dive into the this game given the expected time commitment, but I'm certainly glad I did! Engaging turn-based combat system, check. Interesting story, check. Realistic and sympathetic characters, check. Getting to explore (in a limited way) the districts of real-life Tokyo, a fun bonus. All wrapped up in a package that tries hard to be super stylish and cool -- and actually manages to pull it off!

2. Xenoblade Chronicles 3 (Switch) 

I enjoyed seeing the lore of the first two main games in the Xenoblade series woven together here into something completely different, yet still congruent with the original pairs of source material. A key innovation here was an RPG with a large party of player characters where some PCs aren't present during combat; all 6 (or 7) of them are present, contributing, and playable. The way the game handles, in consistent fashion with the game's world, a mixed-gender party of (essentially) late-teens / early-20s characters with romance between characters (mostly) not being a thing was also an enjoyable novelty. 

3. Crystal Project (Steam / Mac / Windows)

This game was a real treat for me, a fan of old-school Final Fantasy games and similar JRPGs. Layering challenging turn-based on top of exploration of a blocky Minecraft-like world worked remarkably well. Like Stardew Valley a few years back, Crystal Project is a game that managed to pleasingly surprise me on a number of occasions after I thought that I had what the game had to offer all figured out.

4. Knotwords (iPhone)

The best word game app I've ever played. It's Sudoku crossed with crossword puzzles in a really smart way. Wonderful UX design (which inspired a Vigil RPG haptics update!). More-than-fair premium pricing model with no ads, premium currencies, or other typical mobile-game dark patterns. Multiple game modes, including daily and monthly puzzles. Knotwords' ability for the player to "recover" lapsed daily streaks is another innovation that should be universally copied. Highly recommended to fans of word games!

5. Isle of Arrows (iPhone) 

Another premium iOS game done right. Combines tower defense, a light puzzle/card game, and a cool aesthetic. I 100%'ed the campaign.

6. Phoenotopia Awakening (Steam / Mac) 

A smartly-designed, difficult-but-fair, cute pixel-art hybrid of Metroidvania and Zelda 2: The Adventure of Link gameplay styles, with great settings/locations and story.

7. Kirby Forgotten Lands (Switch) 

I played through this cute first 3D foray of Kirby through start-to-finish with my 10-year-old, with her taking on the role of Kirby, and me, Bandana Waddle Dee. I appreciate how this game caters to a variety of skill levels, with the the main story being well suited for the pair of us to complete, and also including a post-game suitable for experts.

8. Chasm (Steam / Mac)

Like Phoenotopia, a pixel art melee Metroidvania game, whose weapon/combat system seems to have been particularly inspired by the more recent 2D games in the Castlevania series (see: next entry below!). Slightly thin on story, but solid enough in terms of level design and gameplay that I did complete the main story.

9. Castlevania Aria of Sorrow (Switch port of Game Boy Advance game) 

Played through this game which I'd never played in its original form as part of the Castlevania Advance Collection. I'd previously been most familiar with the more traditional series entries including the original Castlevania (NES) and Super Castlevania (SNES); hat tip to TouchArcade and freelance game writer Shaun Musgrave, whose recent Patreon ranking of all(!) of the Castlevania series games inspired me to give this a play-through. 

10. Super Auto Pets (Web / iPhone)

This is the type of multiplayer-game that I tend to prefer these days: Quick and asynchronous, meaning low-stress! You start each game from scratch to build a team of up to 5 of animals, each with their own unique combat ability, and watch them engage in a series of brief auto-battles against teams built by other players who have done the same.

Wednesday, October 26, 2022

Workaround: Input Lag with Nintendo Pro Controller playing Steam games on Mac

I was having a problem where, when playing the game Phoenotopia Awakening on my MacBook using a Nintendo Switch Pro Controller connected to the Mac via Bluetooth, I was getting pretty significant "input lag": About half a second would elapse between me pressing a button on the controller, and the character reacting on the screen, making the game nigh-unplayable.

Oddly, this issue would only occur with my MacBook "docked" -- connected on my desk to my multiple monitors, wired ethernet, and various other peripherals. When using the MacBook as a standalone laptop, the game and controller would play just fine.

Even in the "docked" configuration, however, outside of Steam, an in-browser gamepad tester showed that the controller was communicating with the MacBook with no lag.

I found the following to be an effective workaround for the problem:

  1. In Steam (not in "Big Picture" mode, and with the game not running), locate the game in your Library.
  2. Right-click the game and select Properties.
  3. On the Controller tab, select "Disable Steam Input".

Steam game properties dialog > controller tab
 Voila -- No more input lag with the Nintendo Switch Pro Controller, even when docked! 

 Hat tip to "Toms33" for posting this solution in a thread on the Steam Community forums. 

Friday, August 26, 2022

TimeSnapper: The power of being able to replay the past

To get this disclaimer out of the way right out front: I'm the author of TimeSnapper for MacOS. But like the guy in the 1980-something Hair Club for Men TV commercial: I'm not just the author, I'm also a client! A real event just happened where TimeSnapper enabled me to share a key bit of information with my team at work that I hadn't been able to come up with otherwise.

About a month ago, I came across a mention from a co-worker of a book as being a great introduction to the FinTech space: "The Anatomy of the Swipe" (2020) by Admed Siddiqui. At the time, I checked my local public library for a copy, but they didn't have one; so I took advantage of an awesome feature of the library, and put in a request via the library's website for the library to purchase a physical copy! They did so, and automatically put the book on hold for me! I picked up the book yesterday.

In a team meeting near the end of my work day yesterday, I mentioned the book, and promised to drop a link to the book in our Slack channel in the morning. I followed up on that this morning, intending to post the link along with the original post where I'd seen the book recommended internally.

I searched Slack for the earlier mention of the book that I remembered, and... nothing. 

No search results for "anatomy of the swipe". Or for "anatomy" "swipe". Or for "ahmed siddiqui".

Although I thought I'd seen the book recommendation via Slack, maybe it had actually been via shared Google Doc...? Or an email? I searched those places too, and... again, nothing.

At this point, I was momentarily stumped. I wanted to provide credit where it was due and share the original book recommendation I had followed... but where was it? Obviously I hadn't just imagined it; I had gotten the name and author of this particular book, in order to request my local library to order a copy, from somewhere.

And then, inspiration struck: I've got TimeSnapper! TimeSnapper, if you're not familiar with it, is a utility program that runs in the background on your computer, and saves a snapshot of the screen every few seconds. It also provides a "Play Your Day Like a Movie" feature, where you can rapidly scrub back and forth through an entire day's worth of these saved screenshots.

To get the time window for my TimeSnapper search, I went to my library's website, logged in, and and viewed the page with the status of my past purchase requests. Sure enough, it showed that my request for "The Anatomy of the Swipe" had been originally made on July 26, 2022 -- one month ago today.

I then fired up TimeSnapper's Play Your Day feature and navigated to July 26. Skimming through my day, I found the images of my visit to the library website where I'd initially keyed in my request for the book. Navigating a short way back in time from there, I saw myself visiting my company's Slack instance... and found the recommendation I had remembered -- not for the book itself, but a link to a blog article with a list of good FinTech introductory resources -- which is where the mention of the book has actually been located!

Thanks to TimeSnapper, I was able to post a link that Slack post with the link to the FinTech blog post along with the link to the book itself.

Obligatory closing promotional line: If this ability to go back in time and observe what you were seeing on your computer in the past sounds like a superpower you'd like to gain for yourself, you can read more about TimeSnapper, and buy a copy for Mac or for Windows, on the TimeSnapper website!

Friday, April 29, 2022

Nervous Flyer + Gamer? Try Playing Mario Kart

Hi! I'm Jon, and I'm a nervous flyer. 👋 Always have been!

Don't get me wrong: I totally understand and agree that riding on a commercial airliner is generally one of the safest activities that one can engage in. It's a lot safer than driving a car, for example.

Against my will, though, while flying -- especially during takeoffs, landings, and during turbulence -- some primitive part of my brain insistently alerts me that I'm in danger. Even though at the same time, I know that I'm not. It's pretty weird and annoying.

Past attempts to cope with this by distracting myself with movies, reading, or playing video games generally haven't helped.

In recent years, though, I've found a workaround that, quite oddly, actually is fairly helpful: Playing Mario Kart!

Luigi in a kart on the "Sunshine Airport" course of Mario Kart 8 Deluxe

For whatever reason, while I'm playing a Mario Kart race, that primitive part of my brain that normally sounds the alarm as sensations of motion are happening while in flight, instead seems to interpret those sensations as motions that are happening in the game -- and the unpleasant "danger" feelings are largely ameliorated!

I've found that this works for me both with the latest Mario Kart game, Mario Kart 8 for the Nintendo Switch, as well as with the older (and cheaper!) Mario Kart 7 for the Nintendo 3DS.

To maximize my time spent in races (instead of between races), as well as to enjoy the gameplay more, on my airplane ride Mario Kart 8 games, I've taken to using the "custom rules" game mode (found on the game's main menu under Single Player > VS Race):

  • Items: "Mushrooms Only." More opportunities to take those fun course shortcuts that require a speed-boosting mushroom. And I don't miss having my racing interrupted by lightning bolts and blue shells!
  • Race Count: Depending on the available time, I pick some high value, like 24 or 48.
  • Course Selection: "Automatic - In order." 

Would this trick work with other racing games, too? Good question! Maybe? Mario Kart is the only driver-perspective (or behind-driver-perspective) racing game that I've spent more than a short time with over the past few years.

If you're a fellow nervous flyer who is also a gamer, consider giving Mario Kart a try next time you find yourself up in the air!  (And don't forget to bring a USB battery pack to keep that Nintendo Switch charged!)