11/15/2012
Step 1:
What is the ouput of the following mysql command on the
RA machine:
(works as is) mysql -P3308 reporter
(works as is) mysql> select count(*) from address_definitions;
I found another issue where similar errors were reported
when the count returned was over 2million.
That issue was resolved by truncating the address_definitions
table. I would like to see if your issue
is something similar.
Step 2:
I would like to try truncating the address_definitions
table.
First, backup the table with:
(works as is) mysqldump -P3308 reporter address_definitions >
d:\addressdefinitions.bak
Then, use the following commands to truncate it:
mysql -P3308 reporter
truncate address_definitions;
Finally, recycle all of the NetQoS services including
MySQL51.
Does this fix the problem? We can also check the latest version of:
ReporterAnalyzerManagerServiceLog*.log
D:\netqos\reporter\logs or Netqos logs\Reporter logs
to see if these changes stop the "Timeout
Expired" messages from coming in.
END --------------------------------------------------------------------------------------------- I loaded the database table and it doesn't look like any of the queries from the Wiki article were run yet.
The database entries match up exactly with the problems
mentioned in the article.
The database has entries for monthly reports to run on
days 0 which is invalid and also scheduled on days greater then 31 which is
also invalid.
Please run all of the queries below on the RA console
server.
mysql -P 3308 reporter
update report_definitions set
schedule_days=log(2,schedule_days) where schedule_type = 5;
update report_definitions set
schedule_days=schedule_days+2 where schedule_type = 6;
update report_definitions set schedule_days=1 where
schedule_days=13 and schedule_type = 6;
update report_definitions set schedule_days =
schedule_days - 1 where schedule_type = 3;
update report_definitions set schedule_next = 0;
***For the query below I am setting the schedule date to
'1' which for monthly reports woudl schedule it the first day of the month,
they can edit the scheduling in the RA GUI after if desired, or you can pick a
different
day of the month where it says "set
schedule_days=1" ******
update report_definitions
set schedule_days=1
where schedule_type=4
and (schedule_days < 1 or schedule_days > 31);
Then recycle the "NetQoS Reporter Manager
Service".
Check the same log for any new errors.
At this point monthly reports should be working fine.
There may still be issues with the weekly reports or the
daily, but any new reports should be working at this point.
Or any report that the schedule is edited should also now
work.
Please test after this and let me know how it goes.
If more work needs to be done on the weekly or daily
reports let me know I can see if there are any other updates to the database is
needed.
------------------------------------------Contents[hide] |
The log file would be \NetQos\Reporter\logs..
ReporterAnalyzerManagerServiceLog<date>.log
Also if you can export the report_definitions table by
running the command below and upload the results I can take a look at the table
to see if I can find any problem.
mysqldump -u -p reporter report_definitions > d:\report_definitions.bak
ISSUE
After an upgrade to RA 9.0, custom reports do not run on their schedule any longer. When the Reporter Manager service is trying to reschedule the report, you'll see this error in the Manager service log every 2 minutes:12:59:31 1 - Report Scheduler - : Year, Month, and Day parameters describe an un-representable DateTime. System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime. at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day) at NetQoS.Email.Smtp.ScheduleItem.SetWeekly(DayOfWeek dayOfWeek, Int32 hour, Int32 minute, Boolean calendarAligned) at NetQoS.ReporterAnalyzer.Business.ReportScheduleItem.Set(DataRow dr) at NetQoS.ReporterAnalyzer.Business.ReportSchedule.GetUnscheduled(DateTime utcNow, Double minutesOverdue) at NetQoS.ReporterAnalyzer.Business.ReportSchedulerThread.CreateNewSchedules(IE mailSchedule schedules) at NetQoS.ReporterAnalyzer.Business.ReportSchedulerThread.ThreadHandler()
DETAILS
The custom report migration portion probably failed because RA was upgraded all the way from RA 7.x. See defect 24866 (which was closed because dev thinks it won't happen again).schedule_type definitions for report_definitions
- None = 0
- Date = 1, // Schedules at a single specific date
- Daily = 2, // Occurs daily for days of the week indicated
- Monthly = 3, // Occurs on a week day instance every month (ex: 1st Sunday of every month)
- MonthlyDay = 4, // Occurs on a specific monthly day (ex: 4th of every month)
- Weekly = 5, // Occurs once a week on a specific day of the week (every tuesday)
- Quarterly = 6,
- Yearly = 7
RESOLUTION
1. Backup the report_definitions table:mysqldump -u -p reporter report_definitions > d:\report_definitions.bak2. To fix, run this in a command prompt on the RA server
mysql -P3308 reporter -- Migrate weekly day reports from 1,2,4,8,16,32,64 to 0..6 update report_definitions set schedule_days=log(2,schedule_days) where schedule_type = 5; -- Migrate quarterly month from 0..11 to 2..12,1 update report_definitions set schedule_days=schedule_days+2 where schedule_type = 6; update report_definitions set schedule_days=1 where schedule_days=13 and schedule_type = 6; -- Migrate Montly Nth Weekday from 1..7 to 0..6 update report_definitions set schedule_days = schedule_days - 1 where schedule_type = 3; update report_definitions set schedule_next = 0;3. Restart the NetQoS Reporter Manager Service and you should not see report scheduler errors anymore.
If you are still seeing errors, run the following:
select reportID, schedule_type, schedule_days from report_definitions where schedule_type=4;If there are any schedule_days that fall outside the range of 1-31, change them to be within this range.
Reports with schedule_type=4 are reports that are scheduled monthly on the #th day of the month. For example, reports with schedule_type=4 and schedule_days=0 are set to run on the 0th day of the month each month. This is OBVIOUSLY invalid and RA will throw that un-representable datetime error about it. Same thing for schedule_type=4, schedule_days=127 reports (127th day of each month). Fix these to something like schedule_days=1 (1st day of each month).
Example fix:
update report_definitions set schedule_days=1 where schedule_type=4 and (schedule_days < '1' or schedule_days > '31');
To test
Schedule a report that is supposed to run in the next 4 minutes. You'll get the darn report.Also, check
select reportid,name,from_unixtime(scheduled_next) from report_definitions where schedule_type<>0;The scheduled_next column should be non-zero/non-unix-epoch.
