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

Upgrade fails with error 'number of parameters must be between 0 and 65535' on large sites

    XMLWordPrintable

Details

    • Any
    • MOODLE_36_STABLE, MOODLE_37_STABLE, MOODLE_38_STABLE
    • MOODLE_36_STABLE, MOODLE_37_STABLE
    • master_MDL-66864
    • Hide

      Requirements:

      • A PostgreSQL database
      • The ability to execute SQL sentences (psql, phpadmin...) against that database.

      Before patch (with the changes not applied):

      1. Create a fresh Moodle instance from MOODLE_35_STABLE branch.
      2. Add 70k records to mdl_message_contacts table: 

        insert into mdl_message_contacts (userid, contactid, blocked) select i, i+100000, 0 from generate_series(1,70000) i;

      3. Checkout the branch you're testing with the patch applied (e.g. if you're testing 36. "git checkout MOODLE_36_STABLE")
      4. Run upgrade 

        php admin/cli/upgrade.php

      5. Confirm that upgrade finished successfully.

       

      Show
      Requirements: A PostgreSQL database The ability to execute SQL sentences (psql, phpadmin...) against that database. Before patch (with the changes not applied): Create a fresh Moodle instance from MOODLE_35_STABLE branch. Add 70k records to mdl_message_contacts table: insert into mdl_message_contacts (userid, contactid, blocked) select i, i+ 100000 , 0 from generate_series( 1 , 70000 ) i; Checkout the branch you're testing with the patch applied (e.g. if you're testing 36. " git checkout MOODLE_36_STABLE ") Run upgrade php admin/cli/upgrade.php Confirm that upgrade finished successfully.  

    Description

      Hi all,

      Upgrading Moodle from 3.5 up to 3.7 I got an error:

      : -->System
      		 
      !!! Error writing to database !!!
      		 
      !! number of parameters must be between 0 and 65535
      		 
      DELETE FROM mdl_message_contacts WHERE id IN ($1,$2,$3,$4,$5,$6
      		 
      ~
      		 
      ~
      		 
      ~
      		 
        66625 => 89631,
      		 
        66626 => 89499,
      		 
        66627 => 89235,
      		 
        66628 => 89268,
      		 
      )]
      		 
      Error code: dmlwriteexception
      		 
      * line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown
      		 
      * line 300 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
      		 
      * line 1322 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
      		 
      * line 2039 of /lib/db/upgrade.php: call to pgsql_native_moodle_database->delete_records_select()
      		 
      * line 1809 of /lib/upgradelib.php: call to xmldb_main_upgrade()
      		 
      * line 187 of /admin/cli/upgrade.php: call to upgrade_core()

       

      Attachments

        Issue Links

          has been marked as being related by

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-62503 Upgrade Moodle 3.5: Error reseting user dashboard in PostgreSQL very large sites

          • Blocker - Blocks development and/or testing, prevents Moodle from running (applicable to bugs only)
          • Open

          Bug - A problem which impairs or prevents Moodle from functioning correctly. MDL-53425 Unable to reset Dashboard for all users - postgres fails if number of parameters over 65535

          • Critical - Crashes server, loss of data, severe memory leak
          • Closed

          Improvement - An enhancement to an existing Moodle feature. MDL-53735 Split very large SQL-IN parameters to chunks

          • Minor - Minor loss of function where workaround is possible
          • Closed

          Activity

            People

              mikhailgolenkov Misha Golenkov
              mikhailgolenkov Misha Golenkov
              Dmitrii Metelkin Dmitrii Metelkin
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              Janelle Barcega Janelle Barcega
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                11/Nov/19

                Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 hour
                  1h