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

Moodle should never issue TRUNCATE TABLE statements

    XMLWordPrintable

Details

    • Bug
    • Status: Development in progress
    • Minor
    • Resolution: Unresolved
    • 3.1.16, 3.4.7, 3.5.4, 3.6.2
    • None
    • 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

                lukecarrier Luke Carrier
                lukecarrier Luke Carrier
                Andrew Lyons Andrew Lyons
                Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
                Votes:
                0 Vote for this issue
                Watchers:
                5 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