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"


    • Icon: Bug Bug
    • Resolution: Fixed
    • Icon: Minor Minor
    • 1.9
    • 1.6, 1.7, 1.8
    • 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
            0 Vote for this issue
            1 Start watching this issue


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