Moodle
  1. Moodle
  2. MDL-35129

Database Transfer Tool Transfers Tables/Schemas But No Data (MySQL -> Postgres)

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.3.1, 2.4
    • Fix Version/s: 2.3.3
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Testing Instructions:
      Hide

      1/ get some very large MySQL site which has table that can not fit available memory
      2/ try Development / Experimental/ Database migration with any database as target (by anything I mean something that works properly - not MSSQL or Oracle)

      It should not run out of memory any more.

      Show
      1/ get some very large MySQL site which has table that can not fit available memory 2/ try Development / Experimental/ Database migration with any database as target (by anything I mean something that works properly - not MSSQL or Oracle) It should not run out of memory any more.
    • Affected Branches:
      MOODLE_23_STABLE, MOODLE_24_STABLE
    • Fixed Branches:
      MOODLE_23_STABLE
    • Pull from Repository:
    • Pull Master Branch:
      w38_MDL-35129_m24_dbmigration
    • Rank:
      43758

      Description

      In attempting to use the database transfer tool to migrate my MySQL database to Postgres (the replication step here, which I've done several times, is simply "create a new Postgres database according to the instructions at http://docs.moodle.org/23/en/PostgreSQL, then use the database transfer tool with correct parameters), I have observed the following behavior:

      • the transfer tool reports its progress throughout the process, and seems to take significant time and significant CPU cycles/memory for postgres, mysqld, and php (this is a transfer to a new database on the same host), so it looks like it's doing its job
      • the transfer tool reports no error messages, even with maximal error reporting turned on
      • the resulting Postgres database contains all the required tables, but no data

      My original MySQL database is fairly large (29GB, with a 2.8GB gzipped mysqldump), and the various software components (in particular /usr/bin/php) seem to want to use a lot of memory - I have php limited to 1 GB for regular usage, and it pegs that limit during this process. Is it possible that a memory limitation is causing this not to work? In any event, the fact that it does not work and also does not give an error message of any kind is a problem...

      The software versions involved are Moodle 2.3.1+ (20120823), MySQL 5.5.27, and PostgreSQL 9.1.5.

        Activity

        Hide
        Daniel M. Zimmerman added a comment -

        As a followup: I increased the memory limit for PHP to 8 GB to see what would happen, and the migration process, while using reasonable amounts of memory all the way through "Copying table registration_hubs", blows up to the full 8 GB after "Done" is printed to the console for that last table. At that time, PHP and MySQL are competing for (and maxing out) the CPU; this continues for a considerable amount of time, after which the script simply exits without printing anything else, leaving me with the aforementioned empty Postgres tables.

        This behavior occurs whether I use the CLI or web-based invocation of the tool.

        Show
        Daniel M. Zimmerman added a comment - As a followup: I increased the memory limit for PHP to 8 GB to see what would happen, and the migration process, while using reasonable amounts of memory all the way through "Copying table registration_hubs", blows up to the full 8 GB after "Done" is printed to the console for that last table. At that time, PHP and MySQL are competing for (and maxing out) the CPU; this continues for a considerable amount of time, after which the script simply exits without printing anything else, leaving me with the aforementioned empty Postgres tables. This behavior occurs whether I use the CLI or web-based invocation of the tool.
        Hide
        Petr Škoda added a comment -

        This should not happen, last week I successfully ran a test migration of moodle.org database from MySQL to PostgreSQL. It should not need any extra memory because it operates with rows only.

        Which operating system do you use? What PHP version?

        Show
        Petr Škoda added a comment - This should not happen, last week I successfully ran a test migration of moodle.org database from MySQL to PostgreSQL. It should not need any extra memory because it operates with rows only. Which operating system do you use? What PHP version?
        Hide
        Daniel M. Zimmerman added a comment -

        This is on Fedora Core 17, with PHP 5.4.5.

        Show
        Daniel M. Zimmerman added a comment - This is on Fedora Core 17, with PHP 5.4.5.
        Hide
        Petr Škoda added a comment -

        I am sorry, I can not reproduce the problem.

        Show
        Petr Škoda added a comment - I am sorry, I can not reproduce the problem.
        Hide
        Daniel M. Zimmerman added a comment -

        Unfortunate. I do still have the MySQL database that caused the problem, so if there is a way to actually get some debugging information out of the tool (now or in a later revision), I can try it again. However, I have since used manual methods (cobbled together from various info on the moodle.org forums) to move my data to Postgres for the upcoming academic year, and everything seems to be working well, so thankfully there's no immediate need for a fixed tool on my end.

        Show
        Daniel M. Zimmerman added a comment - Unfortunate. I do still have the MySQL database that caused the problem, so if there is a way to actually get some debugging information out of the tool (now or in a later revision), I can try it again. However, I have since used manual methods (cobbled together from various info on the moodle.org forums) to move my data to Postgres for the upcoming academic year, and everything seems to be working well, so thankfully there's no immediate need for a fixed tool on my end.
        Hide
        Nathan Mares added a comment -

        I've seen simmilar behaviour in an MSSQL to Postgres conversion.

        It seems to fail in lib/dtl/database_exporter.php in the function export_database() on the line

        $rs = $this->mdb->get_recordset_sql('SELECT * FROM

        {'.$table->getName().'}

        ');

        This only happens on big tables like mdl_log (1.3 million records). If I add the following code before that line to skip the table then the import completes fine:

        if (in_array($table->getName(), array("log")))

        { print("Skipping " . $table->getName()); continue; }

        The transfer previously also failed on some other tables but increasing the PHP memory_limit significantly allowed those tables to be copied.

        I've seen this on Fedora 14 and Ubuntu 12.

        Show
        Nathan Mares added a comment - I've seen simmilar behaviour in an MSSQL to Postgres conversion. It seems to fail in lib/dtl/database_exporter.php in the function export_database() on the line $rs = $this->mdb->get_recordset_sql('SELECT * FROM {'.$table->getName().'} '); This only happens on big tables like mdl_log (1.3 million records). If I add the following code before that line to skip the table then the import completes fine: if (in_array($table->getName(), array("log"))) { print("Skipping " . $table->getName()); continue; } The transfer previously also failed on some other tables but increasing the PHP memory_limit significantly allowed those tables to be copied. I've seen this on Fedora 14 and Ubuntu 12.
        Hide
        Daniel M. Zimmerman added a comment -

        On my system, I had emptied out my mdl_log table beforehand... but I imagine I probably have some other large tables after running a Moodle for 5 years. Still it shouldn't fail silently.

        Show
        Daniel M. Zimmerman added a comment - On my system, I had emptied out my mdl_log table beforehand... but I imagine I probably have some other large tables after running a Moodle for 5 years. Still it shouldn't fail silently.
        Hide
        Petr Škoda added a comment -

        I found one unclosed database recordset that might be creating some problems. Could you please test the migration again with my patch from https://github.com/skodak/moodle/tree/w38_MDL-35129_m23_dbmigration ? (you can use git or there is also a button that downloads a zip package on github).

        Thanks in advance.

        Show
        Petr Škoda added a comment - I found one unclosed database recordset that might be creating some problems. Could you please test the migration again with my patch from https://github.com/skodak/moodle/tree/w38_MDL-35129_m23_dbmigration ? (you can use git or there is also a button that downloads a zip package on github). Thanks in advance.
        Hide
        Petr Škoda added a comment -

        Hmm, I just studied the difference between MYSQLI_STORE_RESULT and MYSQLI_USE_RESULT - this does not look good for our mysql driver at all.

        Show
        Petr Škoda added a comment - Hmm, I just studied the difference between MYSQLI_STORE_RESULT and MYSQLI_USE_RESULT - this does not look good for our mysql driver at all.
        Hide
        Petr Škoda added a comment -

        Thanks for the report, hopefully my patch will finally resolve this issue. Please test and report any problems.

        Show
        Petr Škoda added a comment - Thanks for the report, hopefully my patch will finally resolve this issue. Please test and report any problems.
        Hide
        Eloy Lafuente (stronk7) added a comment -

        Integrated, thanks (23 & master).

        Show
        Eloy Lafuente (stronk7) added a comment - Integrated, thanks (23 & master).
        Hide
        Adrian Greeve added a comment -

        I tested this on 2.3 and master.
        I didn't spot any regressions.
        I don't unfortunately have access to a 'Very large MySQL site" so I'm handing this over to Dan to complete the testing.

        Thanks.

        Show
        Adrian Greeve added a comment - I tested this on 2.3 and master. I didn't spot any regressions. I don't unfortunately have access to a 'Very large MySQL site" so I'm handing this over to Dan to complete the testing. Thanks.
        Hide
        Dan Poltawski added a comment -

        I've only got part way through this, but seemed to work, and as Adrian has tested for small table regressions i'm passing this.

        Show
        Dan Poltawski added a comment - I've only got part way through this, but seemed to work, and as Adrian has tested for small table regressions i'm passing this.
        Hide
        Dan Poltawski added a comment -

        Congratulations, you've done it!

        Thanks, this change is now in the latest weekly release!

        Join the crowds of people tomorrow from 8am and download this Moodle release from your local apple store!

        Show
        Dan Poltawski added a comment - Congratulations, you've done it! Thanks, this change is now in the latest weekly release! Join the crowds of people tomorrow from 8am and download this Moodle release from your local apple store!
        Hide
        Claus A. Us. added a comment -

        Hi,
        the problem still exists for large tables and perhaps for slow database connections. There seems to be a timeout. When producing an output during the transfer process, there won't be any timeout problem.

        Adding the following helped for me [not the best solution but it's works]:

        database_exporter.diff
        +++ b/lib/dtl/database_exporter.php
        @@ -149,8 +149,12 @@ abstract class database_exporter {
                         throw new ddl_table_missing_exception($table->getName());
                     }
                     $this->begin_table_export($table);
        +            $tmp_count = 0;
                     foreach ($rs as $row) {
                         $this->export_table_data($table, $row);
        +                $tmp_count++;
        +                if($tmp_count % 100000 == 0)
        +                    mtrace($tmp_count. " lines",$eol="<br/>");
                     }
                     $this->finish_table_export($table);
                     $rs->close();
        
        Show
        Claus A. Us. added a comment - Hi, the problem still exists for large tables and perhaps for slow database connections. There seems to be a timeout. When producing an output during the transfer process, there won't be any timeout problem. Adding the following helped for me [not the best solution but it's works] : database_exporter.diff +++ b/lib/dtl/database_exporter.php @@ -149,8 +149,12 @@ abstract class database_exporter { throw new ddl_table_missing_exception($table->getName()); } $ this ->begin_table_export($table); + $tmp_count = 0; foreach ($rs as $row) { $ this ->export_table_data($table, $row); + $tmp_count++; + if ($tmp_count % 100000 == 0) + mtrace($tmp_count. " lines" ,$eol= "<br/>" ); } $ this ->finish_table_export($table); $rs->close();

          People

          • Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: