problem upgrading bigbluebuttonbn plugin

problem upgrading bigbluebuttonbn plugin

by Gary Nevills -
Number of replies: 9

I just upgraded from 3.1.5+ 2016052305.12 to 3.4.4+ 2017111304.08 (and migrated from an old application server to a new application server & a old database server to a new database server) , when updating the plugins I'm receiving the following messages and bigbluebuttonbn seems to be the problem plugin at the moment. Is there a way to resolve this, if not can I somehow disable this plugin. would trying to install this plugin through the cli somehow possibly make a difference?

Debug info: Unknown column 'type' in 'mdl_bigbluebuttonbn'
ALTER TABLE mdl_bigbluebuttonbn MODIFY COLUMN welcome LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL after type
Error code: ddlexecuteerror
×Stack trace:
  • line 492 of /lib/dml/moodle_database.php: ddl_change_structure_exception thrown
  • line 1070 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 77 of /lib/ddl/database_manager.php: call to mysqli_native_moodle_database->change_database_structure()
  • line 595 of /lib/ddl/database_manager.php: call to database_manager->execute_sql_arr()
  • line 178 of /mod/bigbluebuttonbn/db/upgrade.php: call to database_manager->change_field_type()
  • line 47 of /mod/bigbluebuttonbn/db/upgrade.php: call to xmldb_bigbluebuttonbn_add_change_field()
  • line 802 of /lib/upgradelib.php: call to xmldb_bigbluebuttonbn_upgrade()
  • line 508 of /lib/upgradelib.php: call to upgrade_plugins_modules()
  • line 1850 of /lib/upgradelib.php: call to upgrade_plugins()
  • line 694 of /admin/index.php: call to upgrade_noncore()


Average of ratings: -
In reply to Gary Nevills

Re: problem upgrading bigbluebuttonbn plugin

by Ken Task -
Picture of Particularly helpful Moodlers

Don't run BBB so this response is not first hand experience ... but ...

Ok, you've updated core, but do you have the latest BBB for your core version?

https://moodle.org/plugins/pluginversions.php?plugin=mod_bigbluebuttonbn

2.2.4 (2017101012)
compat with Moodle 3.0, 3.1, 3.2, 3.3, 3.4, 3.5

There should be a version.php file in the BBB plugin directory.  Check that.

Upgrading that manually will hopefully fix what's going on now:

**Unknown column 'type'** in 'mdl_bigbluebuttonbn'

ALTER TABLE mdl_bigbluebuttonbn MODIFY COLUMN welcome LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL after type

It's trying to alter the mdl_bigbluebuttonbn table column welcome.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: problem upgrading bigbluebuttonbn plugin

by Gary Nevills -

Ken, thanks for the reply.

mod/bigbluebuttonbn/version.php contains the following info, seems to be the version you mentioned.

$plugin->version = 2017101012;
$plugin->release = '2.2.4';


when I initially went to the plugins check web page it had this info with an option to download the latest update. It said that it downloaded & verified successfully.

Current Version: 2014101004
New Version: 2017101012
status: to be upgraded


currently plugins check page still lists the following, but upgrade moodle database results in the original errors:

Current Version: 2014101004
New Version: 2017101012
status: to be upgraded

seems like BBB is in some kind of half installed/incomplete upgraded state?

Average of ratings: -
In reply to Gary Nevills

Re: problem upgrading bigbluebuttonbn plugin

by Ken Task -
Picture of Particularly helpful Moodlers

The DB user in config.php file may not have priv's to ALTER table ..

Error says:

**Unknown column 'type'** in 'mdl_bigbluebuttonbn'

ALTER TABLE mdl_bigbluebuttonbn MODIFY COLUMN welcome LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL after type

So suggest trying this ... in config.php copy the DBuser and DBpass lines.   Change the copied lines to DB super user ... the one that you know has all privs to do anything with any DB ... and DBpass.

Comment out the original DBuser and DBpass lines by adding // in front of them.

Then, since the plugin appears to already have the upgraded files, run from the command line the upgrade:

cd /path/to/moodlecode/admin/cli/

Then issue: php upgrade.php --non-interactive

That non-interactive switch just gets around the do you wish to upgrade stuff.

IF it upgrades successfully, check with web browser.

Then comment out the super user lines in config.php and make active the old lines you commented out.

Also, you might check into environment ... Admin Menu -> Server -> Environment.

There might be a suggestion about DB character set and collation for you to work on.

Which, BTW, you should be able to do with the scripts in moodlecode/admin/cli/

Fingers X'd.

'spirit of sharing', Ken


Average of ratings: -
In reply to Ken Task

Re: problem upgrading bigbluebuttonbn plugin

by Gary Nevills -

Ken, very helpful advice so far. On Environment under Other checks we had the message about 'mysql_full_unicode_support', I followed the guidance here https://docs.moodle.org/34/en/MySQL_full_unicode_support/ which seemed to go ok until the script got to the mdl_log table, it's getting to that table and appearing to run for a while on that table then the script stops and we receive the following error in the cli: "Error: Tried to convert mdl_log, but there was a problem. Please check the details of this table and try again."

I will look around for more information, I'm not sure exactly what details to examine about the table mdl_log though.

we are running mysql 5.7.20-0

**updated info. seems like the record count in mdl_log is quite high, but I have no perspective to compare it against.

describe mdl_log;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | bigint(10)   | NO   | PRI | NULL    | auto_increment |
| time   | bigint(10)   | NO   | MUL | 0       |                |
| userid | bigint(10)   | NO   | MUL | 0       |                |
| ip     | varchar(45)  | NO   |     |         |                |
| course | bigint(10)   | NO   | MUL | 0       |                |
| module | varchar(20)  | NO   |     |         |                |
| cmid   | bigint(10)   | NO   | MUL | 0       |                |
| action | varchar(40)  | NO   | MUL |         |                |
| url    | varchar(100) | NO   |     |         |                |
| info   | varchar(255) | NO   |     |         |                |
+--------+--------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

select count(*) from mdl_log;
+-----------+
| count(*)  |
+-----------+
| 113462767 |
+-----------+
1 row in set (1 min 6.50 sec)

Average of ratings: -
In reply to Gary Nevills

Re: problem upgrading bigbluebuttonbn plugin

by Ken Task -
Picture of Particularly helpful Moodlers

That table is one of the tables that grow very large ... so not surprising converting it to another character set/collation would take a long time.

Do you have any data in mdl_log?   Think that's an old table that might not have been cleaned up - don't drop it, however, who knows if something you have won't be looking for it! :\

Moodle should be using mdl_logstore_standard_log now.

You could backup the DB  then truncate mdl_log ... or ...

If you run the script again it should move quickly though those that need no converting and hit the same table ... then grind away/chew on it for a while - still might take a long time ... have no idea of how large it might be ... millions of rows.

You could, however, run this query on your DB:

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

That should show the largest tables of the DB.

If linux server, suggest that installing mysqltuner and running it might give you some valuable info about the DB.

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: problem upgrading bigbluebuttonbn plugin

by Gary Nevills -

I did clear out around 95 million records in mdl_log, the conversion script included with moodle seemed to now process mdl_log successfully, it's now stuck on mdl_logstore_standard_log which contains around 280 million records. When running the script again it does process quickly through the other tables as "no change" and gets to mdl_logstore_standard_log pretty quickly. My concern is that if I just keep running the script it might be starting over completely on this table every time, not picking up where it left off in the table (perpetual loop). Does anyone happen to be familiar with how this script is working (starting over each time, or picking up where it left off in mdl_logstore_standard_log)

Below is the message the script is throwing. Maybe I can change a timeout setting or something on the MySQL server so the script keep running?

Warning: mysqli::multi_query(): MySQL server has gone away in /srv/www/lms.example.com/public/moodle34-2017111304.08/lib/dml/mysqli_native_moodle_database.php on line 1068

PHP Warning:  mysqli::multi_query(): Error reading result set's header in /srv/www/lms.example.com/public/moodle34-2017111304.08/lib/dml/mysqli_native_moodle_database.php on line 1068


Average of ratings: -
In reply to Gary Nevills

Re: problem upgrading bigbluebuttonbn plugin

by Ken Task -
Picture of Particularly helpful Moodlers

Think I suggested before ... but will again ... install mysqltuner and run it.

https://github.com/major/MySQLTuner-perl

Bet you'll see that  InnoDB buffer pool / data size indicates an issue ... data size requires more buffer pools.

The error - MySQL server has gone away - is typically max_packet_allowed setting for DB server is too low.   The table you are working with has columns with big chunks of data.

Here's link to MySQL:

https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html

Note that's version 5.5 of MySQL ...

Same page ... probably same info for 5.7 of MySQL:

https://dev.mysql.com/doc/refman/5.7/en/packet-too-large.html

Working with that table ... "mdl_logstore_standard_log which contains around 280 million records" ... and the fact that many rows could contain columns that could include a bunch of data ... means that table will probably take all resources the DB server has access to.

One could shutdown apache completely ... if web,db on same server ... then run the CLI scripts ... those only require CLI php and apache doesn't have to be running to execute them - just  php and the DB server then.

Suggest one might be able to cul rows if one cuts off what data you need to the date columns.  Epoch time stamps ... use https://www.epochconverter.com/ to find the value for cutoff/culling.  How far back in time does one need to keep data?  Probably the same date used for the other table.

'spirit of sharing', Ken


Average of ratings: -
In reply to Ken Task

Re: problem upgrading bigbluebuttonbn plugin

by Gary Nevills -

I got the rest of my issues regarding changing character set/collation to utf8mb4, had to truncate the log tables that were filled with 8 years of logs! Going to have to enable some sort of routine pruning in the Moodle settings.

I am still receiving the above errors when trying to upgrade bigbluebutton. I am noticing that if I execute "ALTER TABLE mdl_bigbluebuttonbn MODIFY COLUMN welcome LONGTEXT COLLATE utf8mb4_unicode_ci NOT NULL after type" as root from a mysql cli I receive a similar error "ERROR 1054 (42S22): Unknown column 'type' in 'mdl_bigbluebuttonbn'" to what the moodle upgrade is throwing me.

If I run "ALTER TABLE mdl_bigbluebuttonbn MODIFY COLUMN welcome LONGTEXT COLLATE utf8mb4_unicode_ci NOT NULL" I receive a query OK acknowledgment. It seems like it maybe shouldn't be putting " after type" on the query string. "type" is indeed not a column in mdl_bigbluebuttonbn.

Average of ratings: -
In reply to Gary Nevills

Re: problem upgrading bigbluebuttonbn plugin

by Ken Task -
Picture of Particularly helpful Moodlers

Uhhhh ... thought you had gotten BBB upgraded and were working on character set/collaton?

Oh ... well ...

*** Did you install MySQLTuner and run it? ***

As a test, successfully installed a fresh copy of the same version of BBB to a 3.4 via command line admin/cli/upgrade.php

mysql  Ver 14.14 Distrib 5.7.23

2 tables ... the one below has the 'welcome' field ...  * is calling your attention to a row .... not something in table or that MySQL did.

mysql> explain mdl_bigbluebuttonbn;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | bigint(10)   | NO   | PRI | NULL    | auto_increment |
| course        | bigint(10)   | NO   |     | 0       |                |
| name          | varchar(255) | NO   |     |         |                |
| intro         | longtext     | YES  |     | NULL    |                |
| introformat   | smallint(4)  | NO   |     | 1       |                |
| meetingid     | varchar(256) | YES  |     | NULL    |                |
| moderatorpass | varchar(255) | NO   |     |         |                |
| viewerpass    | varchar(255) | NO   |     |         |                |
| wait          | tinyint(1)   | NO   |     | 0       |                |
| record        | tinyint(1)   | NO   |     | 0       |                |
| tagging       | tinyint(1)   | NO   |     | 0       |                |
* | welcome       | longtext     | YES  |     | NULL    |                |
| voicebridge   | mediumint(5) | NO   |     | 0       |                |
| openingtime   | bigint(10)   | NO   |     | 0       |                |
| closingtime   | bigint(10)   | NO   |     | 0       |                |
| timecreated   | bigint(10)   | NO   |     | 0       |                |
| timemodified  | bigint(10)   | NO   |     | 0       |                |
| presentation  | longtext     | YES  |     | NULL    |                |
| participants  | longtext     | YES  |     | NULL    |                |
| userlimit     | smallint(3)  | NO   |     | 0       |                |
+---------------+--------------+------+-----+---------+----------------+
20 rows in set (0.00 sec)

Again, the Type is 'longtext'

In the install.xml

<FIELD NAME="welcome" TYPE="text" NOTNULL="false" SEQUENCE="false"/>

In the upgrade.php file there is this section:

        //// Change welcome, allow null
        $field = new xmldb_field('welcome');
        $field->set_attributes(XMLDB_TYPE_TEXT, null, null, null, null, null, null, null, 'type');
        if( $dbman->field_exists($table, $field) ) {
            $dbman->change_field_notnull($table, $field, $continue=true, $feedback=true);
        }

Note the comment ... change welcome to allow null.

In a fresh install the explain on that table for welcome column does have

allow nulls yes and the default value is NULL

Since I'd rather not step through setting it up, how about you check what data there is in that table, welcome and the attributes of that table ... mdl_bigbluebuttonbn

'spirit of sharing', Ken

Average of ratings: -