Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-64763

Moodle should never issue TRUNCATE TABLE statements

    XMLWordPrintable

Details

    • Bug
    • Resolution: Unresolved
    • Minor
    • None
    • 3.1.16, 3.4.7, 3.5.4, 3.6.2
    • Database SQL/XMLDB
    • None

    Description

      At present moodle_database::delete_records() issues TRUNCATE TABLE queries to clear data from tables where no $conditions are provided. Whilst this may result in quicker deletions there are semantic differences between the two instructions:

      • Truncations remove all data and reset the primary key
      • Deletions remove the specified data and don't reset the primary key

      This causes practical issues with at least SQL Server databases (I haven't tested others) with replication enabled:

      SQLState: 42000
      Error Code: 4711
      Message: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot truncate table '[snip]' because it is published for replication or enabled for Change Data Capture.
      

      I raised this in developer chat a few weeks ago and opinion on a fix seemed mixed (see attached or link).

      Balsamiq Wireframes

        Attachments

          Issue Links

            Activity

              People

                Unassigned Unassigned
                lukecarrier Luke Carrier
                Andrew Lyons Andrew Lyons
                Votes:
                1 Vote for this issue
                Watchers:
                13 Start watching this issue

                Dates

                  Created:
                  Updated:

                  Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - 0 minutes
                    0m
                    Logged:
                    Time Spent - 35 minutes
                    35m

                    Clockify

                      Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.