Updating to 6.4.4 and receiving MySQL error 1064

(5 posts) (2 voices)

Tags:

No tags yet.

  1. cwellskc, Member

    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

  2. myDBR Team, Key Master

    What is the MySQL version you are running?

    --
    myDBR Team

  3. cwellskc, Member

    I'm running 5.1.60 on that server.

  4. myDBR Team, Key Master

    You can run the updater again to fix the missing statistics report.

    Would recommend upgrading your MySQL database. The 5.1.60 is more than a decade old.

    --
    myDBR Team

  5. cwellskc, Member

    That did it. Thanks for your help.


Reply

You must log in to post.