Kilg.us – Fantasy Stat Tracker …Tracker

A blog about the development of Kilg.us – The Fantasy Baseball Stat Tracker

Posts Tagged ‘database’

Daily Boxscore Email Update

Tuesday, May 26th, 2015

I apologize for the delay on getting this issue addressed.

As noted previously, daily boxscore emails stopped going out earlier this month. I’ve finally had time to dig into the issue.

First, if you are subscribed for daily emails, you should have received a boxscore email around 9:30am Mountain time today. If you didn’t receive such an email (and confirmed it isn’t in a SPAM folder), please comment here or email me.

To publish daily boxscore emails, Kilg.us goes through a few steps. First, it makes sure all the latest stats have been collected. It then starts a queue for composing the emails. Each team is checked for subscribers and email content is saved to a database table. Once complete, the queue is closed (the stages of opening and closing this queue are recorded). The next step is to systematically go through that DB table and send the emails. As each email is sent, it is deleted from the table. The emails are sent in batches, 50 at a time.

A few observations:

  • The database shows that the composition queue was completed each day of the outage.
  • The database is stuffed full of all the emails that haven’t gone out in the last few weeks.
  • SendGrid (the 3rd party that actually sends the emails at Kilg.us’ request) shows that a full batch (~550) emails went out on May 1. May 2 dropped to 50, followed by 200, 50, 100, and then to 0 in subsequent days. These increments of 50 suggest something is going wrong with our send batches.
  • I used the exact same scripts to successfully send the emails today (manually executed, rather than scheduled).

All told, I haven’t identified a specific cause of the outage. The send process can take a while so it is possible we ran into a timeout along the way. The server has been updated, scrubbed, and rebooted so it should be in tip-top shape for tomorrow. Fingers crossed that emails will go out as scheduled. If not, I at least know I can manually trigger the process daily until I have time to dig back in deeper.

Log-in Issues (User Sessions)

Sunday, May 11th, 2014

A number of people have reported issues logging in to Kilg.us today. After a little exploration, I found that the database table that records user session data was corrupted. After repairing the table, Kilg.us seems to be back to normal and properly logging users in.

This is the first time we’ve encountered this issue, so I’m not going to dig further into the cause of the corruption. If it happens again, though, I’ll dig a bit deeper.

For now, you should be able to log in normally. If you have any problems, please let me know.

Server Issues this Morning

Sunday, August 25th, 2013

This morning a lot of people awoke to find Kilg.us was kicking out errors and was showing all players had been lost from all team. Frankly, not a nice way to start the morning!

Luckily, the issue was resolved with a quick reboot of the server. It seems to have been an issue with one of the database tables, but I’ll be performing some additional diagnostics to try to determine what caused all the errors this morning. For now things should be back up and running.

Sorry for the inconvenience!

That’s a wrap!

Monday, October 29th, 2012

The 2012 baseball season has come to a close. As such, Kilg.us will no longer be sending out daily box score emails.

It’s been a tumultuous year for Kilg.us. In particular, we experienced a lot of accuracy issues with our data source (free MLB.com published XML feeds) and I heard more than ever about the lack of this stat or that stat.

I stopped using Kilg.us for my personal needs a couple years ago (I originally built it as a personal tool). Going into 2013, I’m on the fence as to whether to continue maintaining Kilg.us. Obviously there are costs associated with keeping the servers up and running (thanks to all of you that have donated to help cover hosting costs!), but even more important is the time it takes to field email questions and comments, fix things when they go wrong, and monitor the performance of servers, databases, and email services. We’ll see how that goes in a few months when the excitement of a new season ramps up.

In the meantime, keep your chins up. Baseball is only a few cold months away!

SendGrid Roll-out Successful

Tuesday, April 17th, 2012

Last night I upgraded the email sending mechanism in Kilg.us to use SendGrid. This morning all email boxscores look to have gone out as planned.

As a number people have noted, “as planned” may be less that what we all would like. One of the things SendGrid will help me do is see how many emails are being generated, sent, delivered, read, etc. This will allow me to better manage how daily boxscore emails work.

The first issue to address are those users that have signed up for boxscore emails but haven’t received them. The old email provider had a much lower limit on the number of messages that could be sent per batch and per day. This lead to some less than ideal compromises in designing the system. Basically, rather than just flooding the email server with all the boxscore emails, I had to throttle the process, building a queuing system and sending out small batches of emails. As the system neared its daily limits, some people’s emails would go un-sent.

In addition to the migration to SendGrid last night, this morning I have updated the queuing and sending system. It now queues everything the same, but tomorrow will send one large batch of messages (using an anti-flood function to ensure the email server doesn’t get overwhelmed). This SHOULD ensure that everyone receives every email they have requested! We’ll check back in tomorrow morning to see if it worked.

As a tack on, I switched this WordPress installation to also use SendGrid which should make it less likely that I miss comments and questions people post on here (sorry if I missed your question or comment!).

Internet Explorer Sign-up or Log-in Bug

Saturday, May 7th, 2011

Over the last couple weeks, I have received numerous emails from people having trouble signing-in to their accounts. With a little digging I was able to find a consistent theme: they were all using Internet Explorer 8 or 9.

Specifically what was happening was that a person would type their existing username into the sign-in form and would then be prompted to create an account. As everyone is probably aware, Kilg.us uses the same form for logging in and signing up. If you enter an existing username, it asks you to log-in. If you enter a username that doesn’t exist, it asks you to sign-up.

To manage this functionality, Kilg.us generates an AJAX request after you enter your username then move focus to the password field. This is creates a “change” event on the username field. When that event happens, Kilg.us asks the database whether the username you entered exists. The database returns a response in the form of XML. The XML has one message: the username is valid (it exists in the database) or it is invalid (it doesn’t exist in the database).

Once the XML response is returned, some JavaScript parses it to read whether it says “valid” or “invalid”. If the value is “invalid” the form asks you to create an account. The JavaScript to read this follows standard practices to navigate the document object model (DOM) and find the value in question. This works in Firefox, Chrome, Safari, Konqueror, Mobile Safari, Opera, Android Browser, Blackberry Browser, etc., etc. Apparently, how Internet Explorer handles the XML is different. It doesn’t create all the parameters that the standard approach would.

This shouldn’t come as a surprise to anyone.

What I ultimately found is that Internet Explorer will parse the value out and save it in a “text” parameter for the XML object. Rather than navigating the DOM in a standard fashion, I’m now taking a shortcut and checking if that “text” parameter exists on the XML object. If it does, I read it in as the returned value. If it doesn’t (obviously, all the other browsers to generate this rogue parameter), I continue to navigate the tree and get the value in the proper manner. This only works because the only value of the XML is the “valid” or “invalid” message.

The end result: the log-in/sign-up form logic should now be working again in IE. And still working in every other browser. If you find that not to be the case, please let me know (comment, email, Facebook, Tweet).

Database back up and running

Wednesday, May 4th, 2011

I have moved Kilg.us back to referencing the database on the new servers. Once again, things seem to be going well. Hopefully that will still be the case in a couple hours!

I did not attempt to copy over the old database. There is a lot of complexity involved in merging databases and I just didn’t have the patience after yesterday. As a result, if you added or removed players, moved them on or off the bench, or manipulated a team or your account in the last 24 hours, those changes will be lost. You will need to re-apply any changes.

To the couple people that signed up for Kilg.us today–I’m sorry, but you’ll need to do it again! I promise this isn’t a regular occurrence. In fact, I’ve never deleted an account before!

The only outstanding issue that I am aware of from the migration is the appearance of players that are not on the bench. They are rendering as gray and “benched” right now, but the team is still calculating their stats in totals. I’m working on that now and assume it will be a quick fix.

If you notice any other anomalies, please comment here, email me, or post to Facebook or Twitter (@kilgustracker).

**UPDATE 10:40PM**

It appears that batter stats are being collected properly for individuals, but tallied improperly for teams.

It appears that pitcher stats are not being collected properly for individuals, but tallies are correct for teams (based on day-old data).

Still investigating.

**UPDATE 11:19PM**

The culprits seem to be the stored procedures that Kilg.us tries to use for storing stats. All the procedures seem to be failing with the move to the new server. Luckily, Kilg.us included fallback string concatenation mechanisms for exactly this situation. I have disabled all the stored procedure calls and everything now seems to be working.

Database Failure

Wednesday, May 4th, 2011

Well, things seemed to go great with the migration. I posted to the blog that everything was running great. An hour later the database server crapped out. I’ve spent the last 4 hours trying to recover with no luck. As best I can tell, the permissions tables in the database got screwed up. I’m completely baffled as to how it could have happened. I’m very frustrated by this, but I need to sleep so it will have to wait for tomorrow.

In the meantime, I have pointed to the old database server. Kilg.us code is still running on the new servers, but it is looking to the old servers for data. This probably means that Kilg.us will continue to run at about the speed it used to.

Tomorrow I will attempt to export the databases from the other server again and re-import into the new database servers.

Sorry for the unexpected, additional down-time after I re-launched Kilg.us. If I can find a cause for the problem, I’ll definitely pass it along with any update tomorrow evening.

Kilg.us Migration

Monday, May 2nd, 2011

I intended to migrate Kilg.us to its new servers tomorrow, May 3. If all goes well, the limiting factor on how long Kilg.us will be down is the domain transfer. This can take up to 48 hours, but will go through in 1-3 hours for most people. I intend to take Kilg.us off-line as soon as I get home from work tomorrow (~6pm mountain time). With this approach, I expect the migration and any subsequent troubleshooting should be finished tomorrow night.

Please check Facebook and Twitter (@kilgustracker) for updates on the migration.

Well that’s embarrassing

Sunday, April 24th, 2011

I got a couple emails yesterday that some daily boxscore email stats were incorrect. Looking into the matter, it was immediately obvious what had happen. As I worked on the transition of Kilg.us to its new servers, I had accidentally left a database connection pointing to the new dev database, instead of the old, production database. As such, the stats weren’t up-to-date when the daily emails were generated. I simply pointed the connection back to the correct database and figured all was well.

This morning there have again been reports of incorrect stats. I double-checked that the database connection was pointing to the correct IP address–it was. Then I remembered one of the many reasons I’m moving Kilg.us: the hosting company uses a non-standard port for database connections! Why? Who the hell knows. But I had forgotten to explicitly change back to their port. I’ve done that now. I’ve tested the connection. All seems to be well.

On the migration topic, Kilg.us is up and fully functional on its new servers. I’m testing manually with day-to-day usage to try to identify any aspects or configurations I may have missed. Right now, my simple tests are showing that Kilg.us runs between 2.2 and 5 times as fast on the new servers (adjusting as best I can for load considerations). I’m working on some minor changes that will decrease the bandwidth Kilg.us requires because the new servers are billed by data transfer. I’ll give at least 24 hours notice before I make the migration. Depending on how quickly the domain propagates, Kilg.us may be down for some of you for up to 24 hours during the migration. More details to come.