The day before my mother's wedding, while I was out running errands, I checked my email on my iPhone to find this message:
Hi. I play this puzzle every day & for yesterday & today I can't download the daily time challenge. Any suggestions?
The iPhone version of my word puzzle game, CryptoQuote, has a feature where you can compete in a timed challenge each day. The app will download the puzzle of the day from my server. When I first released CryptoQuote for the iPhone last year, I had filled the server with plenty of quotes to last. I was certain that I had 2 years worth of quotes, but I also knew I couldn't easily address this problem until I had some time to sit down and debug what was happening on the server. My quick reply:
Thank you for buying CryptoQuote and for this bug report.
I am currently on vacation but I can hopefully fix this problem later tonight.
I'm sorry for the trouble this has caused. Rest assured the server has quotes for 2 years already.
Once I get to my mom's house for the evening, I disappear to her den and use her iMac to investigate. What?! The quotes did run out. By now it was the evening, so I put an informational quote online for today and began rewriting a tool to scramble up a quote (and talking with my family). My temporary quote was:
My apologies. The daily quotes were erased from yesterday into the future. A real quote will be ready tomorrow and on into the future. Thank you for your patience.
I was still so certain that I had loaded multiple years of quotes, some 900+ quotes. I spent the better part of the next hour writing a simple script to "scramble" a quote for use by CryptoQuote. I finished this, found a new quote, scrambled it and added it to the database for tomorrow, my mother's wedding day. I saved the scramble script to my server so I could download and use it later that night to fill in a few more quotes to get thru the rest of my trip.
By the time we got home that evening, and I got my 3-year-old daughter to bed, I was falling asleep myself and populating quotes slipped my mind. The next day, I saw this email just as I was about to head out to begin the wedding day:
I just tried to complete the cryptoquote for 8/1. Finally gave up.
When I went to look at the solution, n represented n so it is
impossible to solve. What's the deal?
One mistake on top of another, I had a bug in my quickly rewritten "scramble" code. Since nearly one third of the usual number of people had "given up" on this puzzle, I opted not to fix it to be fair to those early birds. I'll have a good quote and test the scramble for tomorrow, I told myself as I rushed out the door after sending this response:
You are right. I had a problem with the server and am on vacation, so I had to do re-write some software to scramble a quote. Apparently there was a bug because it should not have mapped N to N.
I won't change it for today since then some would be able to solve the puzzle while others would have given up.
Should be better tomorrow. Thank you for your patience.
My mother's wedding day was great and again I was exhausted and sleep beckoned. The next day we woke up late for brunch and spent the day with family before packing up and finally making the trip back home. I realized after arriving back home at 11:30pm that I hadn't posted a new quote. Finally after changing the first diaper this morning at 6:00am, I posted a new quote and populated the database. I can proudly say that I have verified, there are over 1900 quotes in the online database, and this will take the CryptoQuote iPhone daily challenges thru the year 2014, including February 29, 2012.
Problem solved.
So what happened? When I first uploaded the quotes, I neglected to account for how sql handles single quotes. So I created a giant file of SQL INSERT commands, one for each quote. The first quote that happened to have this parsing problem wasn't until July 31, 2009, so only about one-third of my quotes were entered into the database. Everything up until the syntax error.
How could I have prevented this problem?
- Run the sql command as a transaction. This way, everything would have to work or else the database would not accept any of them, which would have made the syntax error more disastrous and caused me to seek out the source of the problem right away.
- Made an iCal event to remind me in every possible way (email, popup notifications, etc.) that the quotes were going to run out.
- Kept a copy of the "scramble" tool available on my server so I could add an emergency quote very quickly. Even better, make the tool web-based so I could scramble a quote from my iPhone and solve these types of problems via any machine.
- Active monitoring. I'll be setting up a script to run on my iMac now, to probe the next few day's puzzles, and send me an email if there is any problem.
