Originally Posted by Stomps
!oracle ask weapon 39001 (to retrieve data for the uppercut, note no period used)
ORACLE MyIrcName: Was that a legit weapon id and period? I didn't get anything back. (error here)
|
15:24:17 @maradine | !oracle ask weapon 39001
15:24:18 ORACLE | maradine: TRS-12 Uppercut - kills: 4621 uniques: 355 kpu: 13.0 avgbr: 68.7 q1kpu: 6.2 q2kpu: 7.6 q3kpu: 12.4 q4kpu: 25.5
I can see the failed queries up in scrollback, so something's definitely up. It's a bug hunt!
edit: I think I see whats happening here - all of the failed examples are happening during nightly maintenance. That shouldn't cause them to fail (not in that failure mode, anyway), but now I know when to watch.
edit2: tricky tricky tricky. I call the assembly's attention to the nightly aggregation procedure:
Code:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`fkpk`@`%` PROCEDURE `kills_nightly`(out deleted_rows int, out time_elapsed int)
BEGIN
DECLARE start_stamp int;
DECLARE end_stamp int;
DECLARE period_id int;
SELECT UNIX_TIMESTAMP(NOW()) into start_stamp;
-- get the timestamps
CALL fkpk.get_time_brackets(@front, @back, @week);
-- create a new time period
INSERT INTO fkpk.v2_time_periods (start_time, end_time, is_daily) VALUES (@front, @back, 1);
SELECT last_insert_id() into period_id;
-- run aggregates over timestamps
CALL fkpk.aggregate_weapons(period_id, @front, @back);
-- delete before weekly
DELETE FROM fkpk.v2_kills
WHERE timestamp < @week;
SELECT row_count() INTO deleted_rows;
SELECT UNIX_TIMESTAMP(NOW()) into end_stamp;
SET time_elapsed = end_stamp - start_stamp;
END
Parts, in essence:
1. Figure out what time it is.
2. Build a new time period.
3. Aggregate every weapon for that time period.
4. Clean out any raw kill data older than a week.
Parts one and two are essentially instant. Parts 3 and 4 each take about an hour each for an RDS instance of this class. Now to the request:
Code:
public static HashMap<String, String> getKillAggregate(Properties props, int id) throws SQLException {
//figure out most revent daily and then run more granular call;
Connection conn = null;
ResultSet rs = null;
PreparedStatement stmt = null;
try {
conn = getConnection(props);
String sqlString = "SELECT id "+
"FROM fkpk.v2_time_periods "+
"WHERE is_daily = 1 "+
"ORDER BY end_time DESC "+
"LIMIT 1;";
stmt = conn.prepareStatement(sqlString);
rs = stmt.executeQuery();
rs.next();
int period = rs.getInt(1);
return getKillAggregate(props, id, period);
} catch (SQLException ex) { blah blah blah
Typos aside, a period-less call queries the database for the most recent period and then runs the more specific call with that period.
You are asking the Oracle for an aggregate immediately after the time period has been built, but before the aggregator has fought it's way to the weapon id in question. Fun!
A few possible simple fixes for this logic error. I'll probably just not write out the period until after the aggregator's done; I just need to make sure I'm not relying on that row being written already elsewhere.
Good catch, guys!
edit3: Your reward is a better period query:
15:58:54 @maradine | !oracle list periods
15:58:56 ORACLE | Listing Periods:
15:58:56 ORACLE | id: 8 start: 2013-09-21T06:31:40.000Z end: 2013-09-22T06:31:40.000Z daily? true
15:58:57 ORACLE | id: 7 start: 2013-09-20T06:31:38.000Z end: 2013-09-21T06:31:38.000Z daily? true
15:58:57 ORACLE | id: 6 start: 2013-09-19T06:31:36.000Z end: 2013-09-20T06:31:36.000Z daily? true
15:58:58 ORACLE | id: 5 start: 2013-09-18T06:31:34.000Z end: 2013-09-19T06:31:34.000Z daily? true
15:58:58 ORACLE | id: 4 start: 2013-09-17T06:31:32.000Z end: 2013-09-18T06:31:32.000Z daily? true
15:58:59 ORACLE | id: 3 start: 2013-09-16T06:35:30.000Z end: 2013-09-17T06:35:30.000Z daily? true
15:59:00 ORACLE | id: 2 start: 2013-09-15T06:31:28.000Z end: 2013-09-16T06:31:28.000Z daily? true
15:59:02 ORACLE | id: 1 start: 2013-09-14T06:31:26.000Z end: 2013-09-15T06:31:26.000Z daily? true