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

Database migration from 1.5 to 1.6 to 1.7 to 1.8 - some int columns should be changed to bigint. Bug triggered by block "course_format_page"



    • Bug
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 1.6, 1.7, 1.8
    • 1.9
    • Blocks, Database SQL/XMLDB
    • None
    • n/a
    • MySQL
    • n/a


      I apologize if this has been reported already! I searched but I just couldn't find it.

      I recently had to migrate a v1.5.3 site to a v1.8.2 codebase. The Moodle.org recommended steps are to migrate incrementally through versions: 1.5->1.6->1.7->1.8

      I did that, and was successful. EXCEPT one block, "course_format_page", which has an unusual piece of code for its version checking section in that it adds a 1 to the end of the version number, which makes the version number bigger than an int().

      The problem is twofold:

      1) Somewhere in the database migration, the int() columns for the mdl_block table should all be changed to bigint(). (And, presumably, many other tables, but only the mdl_block table was the problem in this instance).

      2) The code in <MOODLE>/blocks/course_format_page/block_course_format_page.php on line 24 triggers the bug if the int() column "mdl_blocks.version" has not been changed to bigint()

      What happens is that the column gets "truncated" to the maximum int() value, or 2^32-1: 2147483647

      mysql> select * from mdl_block where version not like '200%';

      id name version cron lastcron visible multiple


      40 course_format_page 2147483647 0 0 1 0

      1 row in set (0.00 sec)

      This value always gets tested as a newer value than the existing block, and the upgrade process hangs with "a newer version of course_formata_page exists"

      Actually, there are several possible solutions.

      1) The most generally applicable one is to change the int() column to a bigint(). This is what I did and it worked.

      2) Fix the code in the upgrade process to make sure all int() columns that need to be are changed to bigint(). I don't know where this process broke down for me, unfortunately.

      3) Fix the code in the block_course_format_page.php file to not just add a one, but instead to change the number to a valid int that is the right size. This should probably be done for this block regardless, as it is a bit divergent from the way version checking is done elsewhere.

      4) Change all "version" columns to "date" types. I don't think this is really feasible at this point.

      5) Change version usage to Unix timestamp value. Also probably not feasible at this point.

      Thanks! Have a great day, as my son would say...




            stronk7 Eloy Lafuente (stronk7)
            jsilve1 Jeffrey Silverman
            David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo, David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
            0 Vote for this issue
            1 Start watching this issue