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

Moodle should never issue TRUNCATE TABLE statements

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Development in progress
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.1.16, 3.4.7, 3.5.4, 3.6.2
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB
    • Labels:
      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

                • 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