Problem running Database transfer from MySQL to Postgres

Problem running Database transfer from MySQL to Postgres

by Petteri Itäkannas -
Number of replies: 2

Hi,

I have a problem with the Moodle database migration tool.

I need to move the data from a MySQL 8.0 database that is on the same server as the Moodle to a Postgres 12.4 database which is on a different server. Moodle version is 3.93, the web server used is Apache 2.4. 

The migration starts, creates some tables and then hangs, after a few minutes. It recognizes that it has failed as it goes on to remove the  /dataroot/climaintenance.html file that puts the server in maintenance mode and writes a message on a log: 'tool_dbtransfer: Interrupted database migration detected, switching off CLI maintenance mode.' There are no error messages on the screen, just that no more tables are listed as created. Switching on debugging does not produce more messages.

There is no sign of error in the Postgres end. Tables got created. If I try to restart migration with the same settings, it recognizes the tables created with the same prefix and refuses to go on.

The migration worked just fine in the test environment. Basically identical, just a lot smaller.

The obvious thing might be some value defined in php.ini, but I've tried adjusting them upwards and it has no effect on the point at which the migration stops (judging by the tables created), it stays the same. The last tabler id always user_enrolments, for some reason.

There's nothing to suggest that this is something caused by the data in the database (there was an extra, custom column in one table that I had to drop, but the same issue occurred in the test environment and there the migration worked fine). Still, production database is bigger than the test, has existed since at least Moodle 1.4 and has contained occasional surprises before, when some earlier upgrade hasn't upgraded all the database fields.

I'd greatly appreciate any suggestions on how to fix this.

Best Regards,

Petteri Itäkannas

Average of ratings: -
In reply to Petteri Itäkannas

Re: Problem running Database transfer from MySQL to Postgres

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

Are you using the web interface or the command line script? I would recommend the command line script as that would avoid PHP timeouts (mainly max_execution_time).

In my experience it's database settings that need adjusting. Check the values of MySQL's wait_timeout, net_read_timeout and net_write_timeout, and try increasing these. I'm not sure what the PostgreSQL settings are that need checking but generally organisations using PostgreSQL have the skills to support this so hopefully you do too.

If you're still having trouble enable debugging. Try to time how long it takes to fail as this might indicate if a timeout is being exceeded, for example, if you increased one of the above settings from 2 minutes to 5 minutes and it then took a further 3 minutes longer to fail then you'd know that the setting was having an effect.

Average of ratings: Useful (1)
In reply to Leon Stringer

Vs: Re: Problem running Database transfer from MySQL to Postgres

by Petteri Itäkannas -
Thank you very much for your advice. Switching to the command line script did the trick, the problem must have been a PHP timeout. (I tried changing the MySQL values and running the web interface version but that did not work.)
Average of ratings: -