Moodle
  1. Moodle
  2. MDL-16106

Backup report fails due to 'ambiguous column name'

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.9.2
    • Fix Version/s: 1.9.10
    • Component/s: Backup
    • Labels:
      None
    • Environment:
      Moodle 1.9.2+ (Build: 20080806)
      Apache/2.2.9 (Win32) mod_ssl/2.2.9 OpenSSL/0.9.8h PHP/5.2.6
      Windows 2003 SP1
      SQL 2005 (via FreeTDS)
    • Database:
      Microsoft SQL
    • Affected Branches:
      MOODLE_19_STABLE
    • Fixed Branches:
      MOODLE_19_STABLE
    • Rank:
      31194

      Description

      When viewing Backup reports in Moodle, you receive the following error:

      Ambiguous column name 'laststarttime'.

      SELECT DISTINCT laststarttime,laststarttime FROM mdl_backup_log WHERE courseid = '16' ORDER BY laststarttime DESC
      ? line 686 of lib\dmllib.php: call to debugging()
      ? line 966 of lib\dmllib.php: call to get_recordset_sql()
      ? line 102 of admin\report\backups\index.php: call to get_records_sql()
      No logs have been found

      The fix simply appears to be removing the extraneous 'laststarttime' from the SQL query on line 99 of admin\report\backups\index.php.

        Activity

        Hide
        Luis de Vasconcelos added a comment -

        I have enabled the automated backups function in Moodle 1.9.9+ (Build: 20100908) and they run nicely, except for a few "Error" messages that I see in the Last execution log of the Backup report. This implies that there is something wrong with the backup files that Moodle is creating. I was obviously eager to determine what these errors are so I tried view the Detailed execution log for the courses that are showing the "Error" message, but the only message I ever get is "No logs have been found".

        Eventually I turned the Debug messages option up to the maximum level (Developer) and tried to view the Detailed execution logs for the problem courses again. The debug text shows the following message:

        Ambiguous column name 'laststarttime'.

        SELECT DISTINCT laststarttime,laststarttime FROM mdl_backup_log WHERE courseid = '1' ORDER BY laststarttime DESC
        line 686 of lib\dmllib.php: call to debugging()
        line 966 of lib\dmllib.php: call to get_recordset_sql()
        line 99 of admin\report\backups\index.php: call to get_records_sql()

        I then went into \admin\report\backups\index.php and changed line 96 from:

        $executions = get_records_sql("SELECT DISTINCT laststarttime,laststarttime
        FROM {$CFG->prefix}backup_log
        WHERE courseid = '$courseid'
        ORDER BY laststarttime DESC");

        to:

        $executions = get_records_sql("SELECT DISTINCT laststarttime
        FROM {$CFG->prefix}backup_log
        WHERE courseid = '$courseid'
        ORDER BY laststarttime DESC");

        In other words, I removed one of the "laststarttime" columns from the sql statement. Now when I view the Detailed execution logs for any of my courses I see the whole log instead of a "No logs have been found" message.

        I'm happy to make this change on my production box so that I can troubleshoot the problem backups, but will this change cause any regressions?

        I see that the admin\report\backups\index.php file in Moodle 2.0 Preview 4+ (Build: 20100902) (2010082600) the code has changed to:

        $executions = $DB->get_records_sql("SELECT DISTINCT laststarttime,laststarttime
        FROM

        {backup_log}

        WHERE courseid = ? AND backuptype = ?
        ORDER BY laststarttime DESC", array($courseid,'scheduledbackup'));

        but it still selects the laststarttime column twice.

        Is
        SELECT DISTINCT laststarttime,laststarttime FROM...
        valid or should it be
        SELECT DISTINCT laststarttime FROM...?

        Show
        Luis de Vasconcelos added a comment - I have enabled the automated backups function in Moodle 1.9.9+ (Build: 20100908) and they run nicely, except for a few "Error" messages that I see in the Last execution log of the Backup report. This implies that there is something wrong with the backup files that Moodle is creating. I was obviously eager to determine what these errors are so I tried view the Detailed execution log for the courses that are showing the "Error" message, but the only message I ever get is "No logs have been found". Eventually I turned the Debug messages option up to the maximum level (Developer) and tried to view the Detailed execution logs for the problem courses again. The debug text shows the following message: Ambiguous column name 'laststarttime'. SELECT DISTINCT laststarttime,laststarttime FROM mdl_backup_log WHERE courseid = '1' ORDER BY laststarttime DESC line 686 of lib\dmllib.php: call to debugging() line 966 of lib\dmllib.php: call to get_recordset_sql() line 99 of admin\report\backups\index.php: call to get_records_sql() I then went into \admin\report\backups\index.php and changed line 96 from: $executions = get_records_sql("SELECT DISTINCT laststarttime,laststarttime FROM {$CFG->prefix}backup_log WHERE courseid = '$courseid' ORDER BY laststarttime DESC"); to: $executions = get_records_sql("SELECT DISTINCT laststarttime FROM {$CFG->prefix}backup_log WHERE courseid = '$courseid' ORDER BY laststarttime DESC"); In other words, I removed one of the "laststarttime" columns from the sql statement. Now when I view the Detailed execution logs for any of my courses I see the whole log instead of a "No logs have been found" message. I'm happy to make this change on my production box so that I can troubleshoot the problem backups, but will this change cause any regressions? I see that the admin\report\backups\index.php file in Moodle 2.0 Preview 4+ (Build: 20100902) (2010082600) the code has changed to: $executions = $DB->get_records_sql("SELECT DISTINCT laststarttime,laststarttime FROM {backup_log} WHERE courseid = ? AND backuptype = ? ORDER BY laststarttime DESC", array($courseid,'scheduledbackup')); but it still selects the laststarttime column twice. Is SELECT DISTINCT laststarttime,laststarttime FROM... valid or should it be SELECT DISTINCT laststarttime FROM...?
        Hide
        Mark Berthelemy added a comment -

        I'm seeing this bug too. Is there a work-around?

        Show
        Mark Berthelemy added a comment - I'm seeing this bug too. Is there a work-around?
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Change applied to CVS, that was one "old trick" needed in previous versions to be able to get only one field, but in 1.9.something we fixed the limit and all the occurrences (usually "id, id"). It seems that we forgot this ("laststarttime, laststarttime").

        Also have fixed 2.0 although that report surely will change because logs aren't stored there anymore.

        Thanks and ciao

        Show
        Eloy Lafuente (stronk7) added a comment - Change applied to CVS, that was one "old trick" needed in previous versions to be able to get only one field, but in 1.9.something we fixed the limit and all the occurrences (usually "id, id"). It seems that we forgot this ("laststarttime, laststarttime"). Also have fixed 2.0 although that report surely will change because logs aren't stored there anymore. Thanks and ciao
        Hide
        Mark Berthelemy added a comment -

        Eloy, not sure if I should raise this here on in the forums - but why would I see this error now when all I have done is move a fully functioning Moodle site onto a new webserver? The DB server has remained the same.

        Thanks,

        Mark

        Show
        Mark Berthelemy added a comment - Eloy, not sure if I should raise this here on in the forums - but why would I see this error now when all I have done is move a fully functioning Moodle site onto a new webserver? The DB server has remained the same. Thanks, Mark
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Running exactly the same moodle version?

        Somewhere in the 1.9.x life the change happened so, for example, if your original server was running 1.9.5, then it could be accepting the double "laststarttime", but if the new server is 1.9.7, then it has stopped accepting the doubles.

        (note the versions above, 1.9.5 and 1.9.7, are invented, I haven't looked exactly when the change happened)

        Could that be your case?

        Ciao

        Show
        Eloy Lafuente (stronk7) added a comment - Running exactly the same moodle version? Somewhere in the 1.9.x life the change happened so, for example, if your original server was running 1.9.5, then it could be accepting the double "laststarttime", but if the new server is 1.9.7, then it has stopped accepting the doubles. (note the versions above, 1.9.5 and 1.9.7, are invented, I haven't looked exactly when the change happened) Could that be your case? Ciao
        Hide
        Mark Berthelemy added a comment -

        Hi Eloy,

        It's exactly the same Moodle version. We just moved the code from one server to the next.

        Mark

        Show
        Mark Berthelemy added a comment - Hi Eloy, It's exactly the same Moodle version. We just moved the code from one server to the next. Mark
        Hide
        Eloy Lafuente (stronk7) added a comment -

        wow, and in the old one you're getting the logs displayed and in the other you get the error?

        same code-base, same DB server, same... everything? Crazy! I cannot imagine any cause for that. Sorry.

        Ciao

        Show
        Eloy Lafuente (stronk7) added a comment - wow, and in the old one you're getting the logs displayed and in the other you get the error? same code-base, same DB server, same... everything? Crazy! I cannot imagine any cause for that. Sorry. Ciao

          People

          • Votes:
            2 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: