Running script failed with error
MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inRowCount; end if; select 'dbr.subtitle', concat(inRowCount, ' Most used repor' at line 52
CREATE PROCEDURE `sp_DBR_StatisticsSummary`(
inRowCount int,
inStartDate date,
inEndDate date
)
BEGIN
declare vEndTime datetime;
declare vOldMySQL boolean;
/* MySQL/MariaDB old versions (prior 5.5) do not allow limit with variable */
set vOldMySQL = cast(substr(version(), 1, locate('.', version() )-1) as unsigned) > 8;
select 'dbr.parameters.show';
select 'dbr.title', concat('Statistics summary ', inStartDate, ' - ', inEndDate);
set vEndTime = addtime(cast(inEndDate as datetime), '23:59:59');
select 'dbr.subtitle', concat(inRowCount, ' Most active users');
/* Not included in distribution */
select 'dbr.report', 'sp_DBR_userusage', 'popup', '[Name]', 'inUser=Username', 'inStartDate=(inStartDate)', 'inEndDate=(inEndDate)';
select 'dbr.hidecolumn', 'Username';
select 'dbr.sum', 'Cnt';
if (vOldMySQL) then
set @num = 0;
select
'dbr.rownum' as '#',
ifnull(u. name , s.username) as 'Name',
count(*) as 'Cnt',
s.username as 'Username'
from (select @rows := 0) as x
join mydbr_statistics s on 1=1
left join mydbr_userlogin u on s.username= u.user
where s.start_time between inStartDate and vEndTime
group by 2, s.username
having ((@rows := @rows + 1) <= inRowCount )
order by 3 desc;
else
select
'dbr.rownum' as '#',
ifnull(u. name , s.username) as 'Name',
count(*) as 'Cnt',
s.username as 'Username'
from mydbr_statistics s
left join mydbr_userlogin u on s.username= u.user
where s.start_time between inStartDate and vEndTime
group by 2, s.username
order by 3 desc
limit inRowCount;
end if;
select 'dbr.subtitle', concat(inRowCount, ' Most used reports');
select 'dbr.report', 'sp_DBR_StatisticsReport','[Name]','inReportID=report_id','inStartDate=(inStartDate)', 'inEndDate=(inEndDate)';
select 'dbr.hidecolumns', 'report_id';
select 'dbr.sum', 'Count';
if (vOldMySQL) then
set @num = 0;
select
'dbr.rownum' as '#',
r.name as 'Name',
s.proc_name as 'Stored procedure',
count(*) as 'Count',
r.report_id
from mydbr_statistics s
join mydbr_reports r on r.proc_name=s.proc_name
join (select @rows := 0) as x
where s.start_time between inStartDate and vEndTime
group by r.name, s.proc_name, r.report_id
having ((@rows := @rows + 1) <= inRowCount )
order by 4 desc;
else
select
'dbr.rownum' as '#',
r.name as 'Name',
s.proc_name as 'Stored procedure',
count(*) as 'Count',
r.report_id
from mydbr_statistics s
join mydbr_reports r on r.proc_name=s.proc_name
where s.start_time between inStartDate and vEndTime
group by r.name, s.proc_name, r.report_id
order by 4 desc
limit inRowCount;
end if;
select 'dbr.subtitle', concat(inRowCount, ' Slowest reports');
select 'dbr.report', 'sp_DBR_StatisticsReport','[Report]','inReportID=report_id','inStartDate=(inStartDate)', 'inEndDate=(inEndDate)';
select 'dbr.hidecolumns', 'report_id';
select 'dbr.colstyle', 'Min', 'hh:mm:ss';
select 'dbr.colstyle', 'Avg', 'hh:mm:ss';
select 'dbr.colstyle', 'Max', 'hh:mm:ss';
if (vOldMySQL) then
set @num = 0;
select
'dbr.rownum' as '#',
r.name as 'Report',
s.proc_name as 'Stored procedure',
count(*) as 'Count',
sec_to_time(min(time_to_sec(timediff(s.end_time,s.start_time)))) as 'Min',
sec_to_time(cast(avg(time_to_sec(timediff(s.end_time,s.start_time))) as signed)) as 'Avg',
sec_to_time(max(time_to_sec(timediff(s.end_time,s.start_time)))) as 'Max',
r.report_id
from mydbr_statistics s
join mydbr_reports r on r.proc_name=s.proc_name
join (select @rows := 0) as x
where s.start_time between inStartDate and vEndTime
group by r.name, s.proc_name, r.report_id
having ((@rows := @rows + 1) <= inRowCount )
order by 7 desc;
else
select
'dbr.rownum' as '#',
r.name as 'Report',
s.proc_name as 'Stored procedure',
count(*) as 'Count',
sec_to_time(min(time_to_sec(timediff(s.end_time,s.start_time)))) as 'Min',
sec_to_time(cast(avg(time_to_sec(timediff(s.end_time,s.start_time))) as signed)) as 'Avg',
sec_to_time(max(time_to_sec(timediff(s.end_time,s.start_time)))) as 'Max',
r.report_id
from mydbr_statistics s
join mydbr_reports r on r.proc_name=s.proc_name
where s.start_time between inStartDate and vEndTime
group by r.name, s.proc_name, r.report_id
order by 7 desc
limit inRowCount;
end if;
END
