3.3.1 to 3.5.13 upgrade error

Re: 3.3.1 to 3.5.13 upgrade error

by Rob Nielson -
Number of replies: 14
Thank you for the detailed instructions. Before I do that, may I ask a few questions?

First, how does this command:

sed -e 's/`mdl_/`lhhs_/' backup2.sql > rename_all.sql

change all instances of LHHS to lhhs inside the tables?

Secondly, is it necessary to make the changes to the table backup rather than backing up the table and then doing the changes to the original? I'm a little concerned that by taking the extra steps there's an increased potential for error/breakage. I currently manage my databases and moodles with CPanel, Softalicious, etc as provided to me by my host. Will deleting, re-creating, and then restoring the edited backup over the newly created database cause issues?

Lastly, when I do a backup of the database it creates a .gz file and downloads that to my computer rather than leaving it on the server. My desktop runs Windows and my notebook runs Chrome OS. Is it ok to just unpack the .gz, and then do a "search and replace" for all instances of LHHS? Or can I just upload the .gz to my server and run the sed command against that (I'm assuming I'll need to unpack it to edit it)? I looked inside the file after I unpacked it on my computer and it looks like it is not just the raw data of the database, but also has the commands to create the database - DROP TABLE, CREATE TABLE, LOCK TABLE, WRITE, INSERT INTO, UNLOCK, etc.

Sorry for all the questions. This is definitely not an area that I am proficient in. *smile*

Also, inside the database backup, each time it will create a table it will make the default charset to be utf8. Will that conflict with utfmb4? Can I search and replace utf8 with utfmb4 as well? 

Here's a cut and paste of a table from the database backup:

--
-- Table structure for table LHHS_block_instances`
--

DROP TABLE IF EXISTS `LHHS_block_instances`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `LHHS_block_instances` (
  `id` bigint(10) NOT NULL AUTO_INCREMENT,
  `blockname` varchar(40) NOT NULL DEFAULT '',
  `parentcontextid` bigint(10) NOT NULL,
  `showinsubcontexts` smallint(4) NOT NULL,
  `requiredbytheme` smallint(4) NOT NULL DEFAULT '0',
  `pagetypepattern` varchar(64) NOT NULL DEFAULT '',
  `subpagepattern` varchar(16) DEFAULT NULL,
  `defaultregion` varchar(16) NOT NULL DEFAULT '',
  `defaultweight` bigint(10) NOT NULL,
  `configdata` longtext,
  PRIMARY KEY (`id`),
  KEY `LHHS_blocinst_parshopagsub_ix` (`parentcontextid`,`showinsubcontexts`,`pagetypepattern`,`subpagepattern`),
  KEY `LHHS_blocinst_par_ix` (`parentcontextid`)
) ENGINE=InnoDB AUTO_INCREMENT=2676 DEFAULT CHARSET=utf8 COMMENT='This table stores block instances. The type of block this is';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `LHHS_block_instances`
--

LOCK TABLES `LHHS_block_instances` WRITE;
/*!40000 ALTER TABLE `LHHS_block_instances` DISABLE KEYS */;
INSERT INTO `LHHS_block_instances` (`id`, `blockname`, `parentcontextid`, `showinsubcontexts`, `requiredbytheme`, `pagetypepattern`, `subpagepattern`, `defaultregion`, `defaultweight`, `configdata`) VALUES (1,'admin_bookmarks',1,0,0,'admin-*',NULL,'side-pre',2,''),(4,'badges',1,0,0,'my-index','2','side-post',4,''),(5,'calendar_month',1,0,0,'my-index','2','side-post',3,''),(6,'calendar_upcoming',1,0,0,'my-index','2','content',-1,''),(8,'myoverview',1,0,0,'my-index','2','content',1,''),(2570,'calendar_month',21,0,0,'course-view-*',NULL,'side-pre',2,''),(2571,'calendar_month',23,0,0,'course-view-*',NULL,'side-pre',2,''),(2578,'navigation',1,1,1,'*',NULL,'side-pre',0,''),(2579,'settings',1,1,1,'*',NULL,'side-pre',0,''),(2592,'badges',5,0,0,'my-index','506','side-post',4,''),(2593,'calendar_month',5,0,0,'my-index','506','side-post',3,''),(2594,'calendar_upcoming',5,0,0,'my-index','506','content',-1,''),(2595,'myoverview',5,0,0,'my-index','506','content',1,''),(2596,'badges',3294,0,0,'my-index','588','side-post',4,''),(2597,'calendar_month',3294,0,0,'my-index','588','side-post',3,''),(2598,'calendar_upcoming',3294,0,0,'my-index','588','content',-1,''),(2599,'myoverview',3294,0,0,'my-index','588','content',1,''),(2600,'badges',3286,0,0,'my-index','593','side-post',4,''),(2601,'calendar_month',3286,0,0,'my-index','593','side-post',3,''),(2602,'calendar_upcoming',3286,0,0,'my-index','593','content',-1,''),(2603,'myoverview',3286,0,0,'my-index','593','content',1,''),(2604,'badges',3312,0,0,'my-index','597','side-post',4,''),(2605,'calendar_month',3312,0,0,'my-index','597','side-post',3,''),(2606,'calendar_upcoming',3312,0,0,'my-index','597','content',-1,''),(2607,'myoverview',3312,0,0,'my-index','597','content',1,''),(2608,'badges',3283,0,0,'my-index','600','side-post',4,''),(2609,'calendar_month',3283,0,0,'my-index','600','side-post',3,''),(2610,'calendar_upcoming',3283,0,0,'my-index','600','content',-1,''),(2611,'myoverview',3283,0,0,'my-index','600','content',1,''),(2612,'badges',3322,0,0,'my-index','602','side-post',4,''),(2613,'calendar_month',3322,0,0,'my-index','602','side-post',3,''),(2614,'calendar_upcoming',3322,0,0,'my-index','602','content',-1,''),(2615,'myoverview',3322,0,0,'my-index','602','content',1,''),(2620,'badges',3287,0,0,'my-index','606','side-post',4,''),(2621,'calendar_month',3287,0,0,'my-index','606','side-post',3,''),(2622,'calendar_upcoming',3287,0,0,'my-index','606','content',-1,''),(2623,'myoverview',3287,0,0,'my-index','606','content',1,''),(2628,'badges',3351,0,0,'my-index','614','side-post',4,''),(2629,'calendar_month',3351,0,0,'my-index','614','side-post',3,''),(2630,'calendar_upcoming',3351,0,0,'my-index','614','content',-1,''),(2631,'myoverview',3351,0,0,'my-index','614','content',1,''),(2632,'badges',3367,0,0,'my-index','616','side-post',4,''),(2633,'calendar_month',3367,0,0,'my-index','616','side-post',3,''),(2634,'calendar_upcoming',3367,0,0,'my-index','616','content',-1,''),(2635,'myoverview',3367,0,0,'my-index','616','content',1,''),(2636,'badges',3357,0,0,'my-index','617','side-post',4,''),(2637,'calendar_month',3357,0,0,'my-index','617','side-post',3,''),(2638,'calendar_upcoming',3357,0,0,'my-index','617','content',-1,''),(2639,'myoverview',3357,0,0,'my-index','617','content',1,''),(2644,'badges',3390,0,0,'my-index','620','side-post',4,''),(2645,'calendar_month',3390,0,0,'my-index','620','side-post',3,''),(2646,'calendar_upcoming',3390,0,0,'my-index','620','content',-1,''),(2647,'myoverview',3390,0,0,'my-index','620','content',1,''),(2648,'badges',3332,0,0,'my-index','627','side-post',4,''),(2649,'calendar_month',3332,0,0,'my-index','627','side-post',3,''),(2650,'calendar_upcoming',3332,0,0,'my-index','627','content',-1,''),(2651,'myoverview',3332,0,0,'my-index','627','content',1,''),(2652,'badges',3441,0,0,'my-index','652','side-post',4,''),(2653,'calendar_month',3441,0,0,'my-index','652','side-post',3,''),(2654,'calendar_upcoming',3441,0,0,'my-index','652','content',-1,''),(2655,'myoverview',3441,0,0,'my-index','652','content',1,''),(2660,'badges',3337,0,0,'my-index','662','side-post',4,''),(2661,'calendar_month',3337,0,0,'my-index','662','side-post',3,''),(2662,'calendar_upcoming',3337,0,0,'my-index','662','content',-1,''),(2663,'myoverview',3337,0,0,'my-index','662','content',1,''),(2664,'badges',3425,0,0,'my-index','663','side-post',4,''),(2665,'calendar_month',3425,0,0,'my-index','663','side-post',3,''),(2666,'calendar_upcoming',3425,0,0,'my-index','663','content',-1,''),(2667,'myoverview',3425,0,0,'my-index','663','content',1,''),(2668,'badges',3318,0,0,'my-index','664','side-post',4,''),(2669,'calendar_month',3318,0,0,'my-index','664','side-post',3,''),(2670,'calendar_upcoming',3318,0,0,'my-index','664','content',-1,''),(2671,'myoverview',3318,0,0,'my-index','664','content',1,''),(2672,'badges',3405,0,0,'my-index','666','side-post',4,''),(2673,'calendar_month',3405,0,0,'my-index','666','side-post',3,''),(2674,'calendar_upcoming',3405,0,0,'my-index','666','content',-1,''),(2675,'myoverview',3405,0,0,'my-index','666','content',1,'');
/*!40000 ALTER TABLE `LHHS_block_instances` ENABLE KEYS */;
UNLOCK TABLES;
Average of ratings: -
In reply to Rob Nielson

Re: 3.3.1 to 3.5.13 upgrade error

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

1. My command is wrong, apologies. I'm changing the commands I'm testing on my system to match your system and I missed this. It should be:

sed -e 's/`LHHS_/`lhhs_/' backup2.sql > rename_all.sql

2. So what I'm suggesting is using the above command to modify the text-based database backup so that both tables and indexes have the lhhs_ prefix. The challenge is that unlike with tables which you can rename you can't do this with indexes (you can but with a later MySQL version). The approach carries risk and you're right to be wary. It could be that there's something in the way that the server is set up that prevents the steps from working, something I've not taken into account, or an error (as above).

You're correct about needing to uncompress the database backup if that's being compressed. On the server you can use gzip -d for this, e.g. gzip -d backup.sql.gz would change the file to the uncompressed backup.sql.

The sed command is just doing a search and replace so you could do that in a text editor if it can handle the file (it might be too large). But note that the backtick (`) is part of the search and replace to try to more accurately match object names.

If the character set is currently utf8 then continue to use that (with corresponding collation utf8_unicode_ci). utf8mb4 is the recommended setting but switching to that is a job in itself so can be deferred for now!

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

Re: 3.3.1 to 3.5.13 upgrade error

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

Here's an alternative which changes the indexes only. This assumes that you've got the uncompressed database backup on the server and the server has the stndard command awk installed. You'll need the attached file on the server too.

1. Put the uncompressed database backup, e.g. backup.sql, and the attached file, rename_indexes.awk on the server.

2. At the shell run the command:

awk -f rename_indexes.awk backup.sql > rename_indexes.sql

This should create file rename_indexes.sql containing an ALTER TABLE … DROP INDEX/ALTER TABLE … ADD INDEX for every index, changing the case of the prefix.

3. Run the following command to run the above statements.

mysql -u root -p aslwithr_lhhs < rename_indexes.sql

Caveat: recreating all indexes could be time consuming on all but the smallest Moodle sites. It may also cause high server load while it's running.

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

Re: 3.3.1 to 3.5.13 upgrade error

by Rob Nielson -
Thank you again for all your help. Sorry so long to respond - back to school training meetings have started.

I have created a backup (it downloaded to my computer). I uploaded it to my home directory on my server and uncompressed it.

I tried to run the sed command but apparently my version of SQL doesn't have that? Either that or I'm not authorized to use it? I opened the database on my desktop and did a search and replace for LHHS to lhhs. Took a long time to do it, but was able to complete it.

Out of curiosity, I wanted to see if I could restore the database on top of the existing one. It worked!

I checked out my site and everything seemed to be fine. I opened up some of the tables and the KEYS were now lhhs instead of LHHS.

So, I went ahead and upgraded to 3.5.13. The install went very smoothly. Thank you!

Then I went ahead and upgraded to 3.9.1. The install went smoothly as well. However, All the formatting is gone now. I've seen that happen before at random times and refreshing page usually does the trick. But doesn't seem to be doing that this time. Really weird! Any ideas?

Here's my website - www.ASLwithRobNielson.com/LHHS

I truly appreciate all the help you've given me.

EDIT - Ah, just figured it out. My theme ("More") is apparently not compatible with 3.9.1. So I just selected "Clean" to get it to work. I'll format it and play with the customization of the theme later.  Thanks again for all the help! Wow, that was a pain in the butt but at least it's all upgraded in time for the upcoming school year.
Average of ratings: -
In reply to Rob Nielson

Re: 3.3.1 to 3.5.13 upgrade error

by Rob Nielson -
Me again. *grin*

I'm trying to edit my site, but when I click on the "edit" links, it just refreshes. If I click on "add an activity or resource" nothing happens. 

The embedded link for the "edits" are like this:

https://aslwithrobnielson.com/LHHS/#

So basically the site page maps are gone. But if I click on the link of the site page, it takes me there.

As for the "add..." link, there is embedded URL. 

Your thoughts?




Average of ratings: -
In reply to Rob Nielson

Re: 3.3.1 to 3.5.13 upgrade error

by Ken Task -
Picture of Particularly helpful Moodlers

Turn on debugging ... all the way to developer.

Also check web service error logs.

Have you double checked ownerships/permissions of new code files/directories?

What is value/variable for cfg wwwroot?  (url) ... does that match how web server will respond to any request?

'SoS', Ken

Average of ratings: -
In reply to Rob Nielson

Re: 3.3.1 to 3.5.13 upgrade error

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

Something is breaking the JavaScript for the page, there's an error in the browser console. Can you tell us what additional plugins are installed (from Site administration ▶ Plugins ▶ Plugins overview click Additional plugins)? Are they all compatible with Moodle 3.9 and are the versions up-to-date? It's possible there's a plugin that's not compatible causing a conflict.

The other place to check is Site administration ▶ Appearance ▶ Additional HTML to see if any scripts have been added but I don't think that's the case.

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

Re: 3.3.1 to 3.5.13 upgrade error

by Rob Nielson -
There are 13 "additional plugins." But none of them are ones I've added. I haven't customized my installation other than the theme, colors, logo.

In the Navigation block, the words "Site pages" do not have an embedded url. You can see that as a guest by looking at my front page.

I'm unable to turn on editing and edit them or to add an activity/resource. The only way to edit the site pages is to actually go to that page and then "edit settings."

I can move around on the site fine. The courses appear to work. Most everything appears in order except for the ability to click on "edit" when editing has been turned on, unable to add resources/activities, and the embedded url for "site pages" is not there in Navigation block.

EDIT - Lol. I just realized that there are no embedded links in the upper right corner either (profile, notifications, chat). Ha. I'm logged in right now, but I'm unable to log  out. This is really weird. 
Average of ratings: -
In reply to Rob Nielson

Re: 3.3.1 to 3.5.13 upgrade error

by Rob Nielson -
Some more information:

The logout link at the bottom of the moodle page works (just not in the upper right corner). When I'm not logged in I see the  individual site pages listed under the HOME link in the Navigation box. But once I login, that changes to site pages and has no url attached to it. I'm unable to expand that drop down.

I've also realized that in the administration block the only two options are front page settings and site administration. Nothing else exists. Course administration is gone. The front page settings drop down is automatically extended and there is no embedded url to collapse it. Front page settings:Users and front page settings:Reports also have no embedded url. So I'm unable to click on those. 

Looks like more is broken than I originally thought. I hope what I've shared is helpful in figuring out a fix.
Average of ratings: -
In reply to Rob Nielson

Re: 3.3.1 to 3.5.13 upgrade error

by Rob Nielson -
Hmmmm... Ok, on a whim I went back to the original 3.3.1 install. Works fine.

Upgraded to 3.5. Works fine. Made sure to change the theme so as to not run into compatibility problems.

Upgraded to 3.6. Works fine.

Upgraded to 3.7.7. Works fine.

Upgraded to 3.9.1. Problems. I'm beginning to suspect that perhaps there is an issue with the theme? I'm using "Classic." I've also tried "Boost" and "Clean."

Any ideas?
Average of ratings: -
In reply to Rob Nielson

Re: 3.3.1 to 3.5.13 upgrade error

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

Are you using Softaculous to perform the upgrade? I think this isn't removing the old Moodle files beforehand. This is why you're seeing additional plugins listed, they're old core plugins that have been retired. Removing old source code files is an important step in upgrading: "Do NOT copy new files over the old files".

Assuming the above is correct you'll need to get whoever is providing services with Softaculous to resolve this.

Or you can follow the instructions for upgrading, and extract the source code to a clean directory.

The problem with the links not working could probably be fixed by:

  1. Deleting files theme/boost/amd/src/tether.js and theme/boost/amd/build/tether.min.js.
  2. Going to admin/purgecaches.php on your site and clicking Purge all caches.

This should fix the navigation problems but I don't recommend it for a production site as you would still have the retired plugins and who-knows-what other files left behind.

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

Re: 3.3.1 to 3.5.13 upgrade error

by Rob Nielson -
Thanks for the suggestion. I bypassed softalicious completely and installed according to moodle's standard installation instructions.

Renamed the original directory to a backup name.
Created a new one with the original name.
Copied the install package in the new directory.
Upgraded.

That fixed the missing url issues. Thank you! But to be sure I STILL didn't have other issues, I started testing things out. When trying to add a new activity/resource, I click on it, the window opens as expected, but then I get the following error:

Coding error detected, it must be fixed by a programmer: Invalid component used in plugin/component_callback():quizaccess_safebrowser

Other than this error everything seems to be working so far. (Still testing other areas of moodle.) 

I looked in the plugins overview and it is listed as "disabled."

I just can't get a break! Ha ha! Again, I appreciate all your help. Ideas on what I need to do next?
Average of ratings: -
In reply to Rob Nielson

Re: 3.3.1 to 3.5.13 upgrade error

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

I'll point you in the direction of this reply.

This row is from one of the plugins removed between 3.3 and 3.9. The row should get removed as part of the upgrade process. I've confirmed that it does using a test site but clearly you're not the only person ever to encounter this.

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

Re: 3.3.1 to 3.5.13 upgrade error

by Rob Nielson -
*BOOM!*

And now, everything seems to be working. I'll continue to check and test to make sure.

I've been using Moodle since 2009 and have upgraded many times, this one was the absolute hardest!

You have been incredibly helpful through all of this. Thank you SO much!
Average of ratings: -
In reply to Rob Nielson

Re: 3.3.1 to 3.5.13 upgrade error - to 3.9.highest

by Ken Task -
Picture of Particularly helpful Moodlers

Congrats!   Now let's look to the future!   What are some of the take away's from this 'hardest' upgrade?

'SoS', Ken

Average of ratings: -