Converting the webwork database from the latin1 to the utf8mb4 character set

From WeBWorK_wiki
Jump to navigation Jump to search

These instructions explain how to convert the webwork database from the latin1 to the utf8mb4 character set. This is something that you should do when upgrading from an earlier version of WeBWorK to WeBWorK version 2.15 or above and you want courses from the old version to run well on the new version.

Terminal Window Notation[edit]

In a terminal window some commands will have to be run as root whereas others should be run as a regular user. We will use # to indicate that the command is to be run as root e.g.

# mysql --database=webwork -B -N -e "SHOW TABLES LIKE '%\_setting' " | awk '{print "ALTER TABLE", $1, "MODIFY name varchar(240) NOT NULL; "}' | mysql --database=webwork

and $ to indicate that the command is to be run as a normal user e.g.

$ mysql -u root -p 

Of course you can use sudo to run most commands as root from a standard command prompt. In general we will use the same notation used in Installation_Manual_for_2.15_on_Ubuntu_20.04_Server.

Preliminaries[edit]

Check What the Current Character Set Is[edit]

Before we begin let's make use sure the webwork database is using the latin1 character set.

Log into mysql. Depending on your OS and mysql version, you will either use the command

$ mysql -u root -p 
Enter Password: <mysql root password>

or

$ sudo mysql
[sudo] password for wwadmin: <wwadmin password>

You should see something very similar to

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
...

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Now issue the following mysql commands:

mysql> Use webwork;
...
Database changed


mysql> SELECT @@character_set_database;

If the webwork database is using the latin1 character set you will see:

+--------------------------+
| @@character_set_database |
+--------------------------+
| latin1                   |
+--------------------------+
1 row in set (0.04 sec)

Independent of the above result, run the following command which will show the collation for every table. The collation specifies the character set.

mysql> SHOW TABLE STATUS FROM webwork;

Looking at the results you will see information on every table, e.g.

| Name                                 | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+--------------------------------------+--------+---------+------------+-------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| OPL_DBchapter                        | MyISAM |      10 | Dynamic    |   176 |             36 |        6348 |   281474976710655 |        16384 |         0 |            180 | 2021-03-07 16:25:08 | 2021-03-07 16:25:08 | 2021-03-07 16:25:08 | latin1_swedish_ci |     NULL |

From this we see that the OPL_BDchapter table uses the collation latin1_swedish_ci which means it is using the latin1 character set.

Now exit MySQL

mysql> exit
Bye
$

Assuming some or all of the tables in the webwork database are using the latin1 character set, continue reading these instructions.

Check what the default character set is for MySQL on your new or upgraded server[edit]

Log into mysql on your new or upgraded server. Depending on your OS and mysql version, you will either use the command

$ mysql -u root -p 
Enter Password: <mysql root password>

or

$ sudo mysql
[sudo] password for wwadmin: <wwadmin password>

You should see something very similar to

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
...

Now issue the following mysql command:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

and you should see

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_0900_ai_ci |
| collation_database       | utf8mb4_0900_ai_ci |
| collation_server         | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

mysql> 

Now exit MySQL

 mysql> exit
Bye
$


If your version of MySQL is not using utf8mb4 as listed above, I would strongly suggest that you upgrade MySQL to version 8. You can find out the version of MySQL on your server with the command

$ mysql -V

Version 8 uses the character set utf8mb4 by default. If for whatever reason you can not upgrade to version 8, then you should edit the my.cnf file which is probably in the /etc/mysql/ directory. Actually my.cnf might be redirected to another file (e.g. mysql.cnf) so edit the appropriate file. You will probably have to be root to edit the file (e.g. "sudo gedit mysql.cnf). At the end of the file add the following

[client]
default-character-set=utf8mb4     

[mysql]
default-character-set=utf8mb4

[mysqld]
init-connect='SET NAMES utf8mb4'
character_set_server=utf8mb4

Then save the file and exit. Restart mysql

$ sudo /etc/init.d/mysql restart

and then log into mysql again and repeat the command

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

to check that mysql is now using utf8mb4.

Note that neither above nor in the commands below do we specify a particular collation. This means the the current default collation (utf8mb4_0900_ai_ci as of this writing) will be used.

Now any new MySQL tables created in the webwork database will use the utf8mb4 character set. So if you create a new WeBWorK course, it's associated tables will use the utf8mb4 character set. Similarly if you update the OPL (Open Problem Library) by running the OPL-update command, this creates all new OPL tables and so they will all the utf8mb4 character set. However any existing courses will have associated tables using latin1. Likewise if you use the admin course methods "Archive Course" and "Unarchive Course" to move a course from a server using the latin1 character set to a server using the utf8mb4 character set, the unarchived course will still have associated tables using latin1.

Backup the webwork database[edit]

IMPORTANT: Do not skip this step.

First we create a directory to hold the backup file and cd to the new directory

$ mkdir mysql_backups
$ cd mysql_backups

From now on often in these instructions I will assume you are using the older method of logging into mysql as root using a password rather than the newer method of sudoing into mysql. If this is not the case, use sudo wherever the mysql password is used in these instructions (i.e. use either mysqldump -u root -p or sudo mysqldump).

Now use the mysqldump command to create the backup file.

$ mysqldump -u root -p webwork > webwork_backup.sql
Enter Password: <mysql root password>

After the process finishes, the file webwork_backup.sql will be located in the mysql_backups directory.

Restore the webwork database[edit]

Hopefully this will not be necessary but here is how the restore the backedup webwork database.

First we have to log into MySQL and then DROP and recreate the webwork database.

$ mysql -u root -p 
Enter password: <mysql root password>
mysql> DROP DATABASE webwork;
mysql> CREATE DATABASE webwork;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, LOCK TABLES ON webwork.* TO 'webworkWrite'@'localhost';

Now exit MySQL

mysql> exit
Bye
$

cd to the mysql_backups directory that contains the backup sql file.

$ cd
$ cd mysql_backups

and use the following command to restore the database

mysql -u root -p webwork < webwork_backup.sql
Enter password: <mysql root password>

Now connect to WeBWorK and everything should be restored.

Maximum Key Length Issue[edit]

WeBWorK uses the MyISAM Engine for almost all of its MySQL tables and these tables have a maximum key length of 1000 bytes. In a number of tables in earlier versions of WeBWorK (prior to version 2.15), the maximum key length is set to 255 characters (or something similar). With the latin1 character set with 1 byte per character, there is no issue. However, with the utf8mb4 character set with 4 bytes per character, the maximum length in bytes is 4 x 255 which is greater than 1000 and is not allowed. Thus all the offending tables must be altered before converting from latin1 to utf8mb4.

The tables that need to be altered are location_addresses, coursename_setting for every course, coursename_past_answer for every course, and most of the OPL tables. Instead of altering the OPL tables directly, we will run OPL-update which will recreate the tables in the proper format. However, we may have to alter the location_addresses table (depending on your situation) and will have to alter the coursename_setting and coursename_past_answer tables for every course. If you are updating from a very old version of WeBWorK, say prior to version 2.12, you may also have to alter other tables (see NOTES below).

Methodology[edit]

The reason you are converting from latin1 to utf8mb4 character sets is that you are upgrading from an earlier version of WeBWorK to WeBWorK version 2.15 or above and you want to move your courses (some or all) from the old version of WeBWorK to the new one. There are basically three methods of doing this.

  1. Build a new server and use the admin course methods "Archive Course" and "Unarchive Course" to move courses from the old server to the new server. To build your new server, you can build it from scratch following the directions at Installation_Manual_for_2.15_on_Ubuntu_20.04_Server, use a pre built Virtual Machine Image (see Installing_from_WW2.15_Ubuntu20.04_Server Virtual Machine Image) or use a pre built AWS image (see WeBWorK_2.15_Ubuntu_Server_20.04_LTS_Amazon_Machine_Image). This may be the preferred method since your old server will remain fully operational until you switch over to the new server and you will end up with a new server with up to date versions of the OS, WeBWorK, MySQL and all other components. If you are not using Ubuntu, you can still follow Installation_Manual_for_2.15_on_Ubuntu_20.04_Server, just make the obvious changes. Check that WeBWorK is working properly on your new server before transferring old courses to the new server.
  2. Build a new server as above and move the whole webwork database and courses directory over to the new server. This may cause some issues with the admin course and other things that are easy to address.
  3. Update WeBWorK on your current server using git basically following the directions in Release notes for WeBWorK 2.14. The issues here include all the issues in 2 above a a few more.

Below we will give instructions covering these three methods.

Method 1: Build a new server and use the admin course methods "Archive Course" and "Unarchive Course" to move courses from the old server to the new server[edit]

Step 1[edit]

Build your new server from scratch following the directions at Installation_Manual_for_2.15_on_Ubuntu_20.04_Server, or use a pre built Virtual Machine Image (see Installing_from_WW2.15_Ubuntu20.04_Server Virtual Machine Image) or use a pre built AWS image (see WeBWorK_2.15_Ubuntu_Server_20.04_LTS_Amazon_Machine_Image). If you are not using Ubuntu, you can still follow Installation_Manual_for_2.15_on_Ubuntu_20.04_Server, just make the obvious changes. Check that WeBWorK is working properly on your new server before transferring old courses to the new server.

Step 2[edit]

On your old server log into the admin course (Course Administration), select "Archive Course", select the courses you want to move to the new server and click on "Archive Courses"

For each course, "Archive Courses" will create a coursename.tar.gz file in the /opt/webwork/courses directory.

Step 3[edit]

Transfer all the coursename.tar.gz files from your old server to your new server (e.g. by sftp) and put them in the /opt/webwork/courses directory on the new server.

Step 4[edit]

On your new server log into the admin course (Course Administration), select "Unarchive Course", select a course you want to unarchive and click on "Unarchive Courses". This is a process you have to perform course by course but it is fast. Follow any instructions. For example you may have to edit the course.conf file replacing the copyright symbol © by &copy; to prevent warning messages.

Step 5[edit]

Next in the admin course (Course Administration), select "Upgrade Courses", and upgrade all courses that require upgrading (don't skip this step). If you haven't done it above, you may have to edit the course.conf file replacing the copyright symbol © by &copy; to prevent warning messages.

Step 6[edit]

You might want to log into some or all of your transferred courses just to make sure that everything is OK so far.

Step 7[edit]

Before we start manually altering tables, now would be an excellent time to backup the webwork database if you have not done so already.

Step 8[edit]

Now we will handle the Maximum Key Length Issue mentioned above which occurs in the coursename_setting table and the coursname_past_answer table for every course we have transfered.

Log into mysql. Depending on your OS and mysql version, you will either use the command

$ mysql -u root -p 
Enter Password: <mysql root password>

or

$ sudo mysql
[sudo] password for wwadmin: <wwadmin password>

Now issue the following mysql commands:

mysql> USE webwork;
...
Database changed

Use the command SHOW TABLES; to list all tables and the command DESC tablename; to describe any table. For example

mysql> DESC test1_setting;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | NO   | PRI | NULL    |       |
| value | text         | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> DESC myTestCourse_setting;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(240) | NO   | PRI | NULL    |       |
| value | text         | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

test1 above is a course transferred from an old version of WeBWorK and myTestCourse is a course created on our new server. Notice that the key length is 240 for myTestCourse but 255 for test1.

Similarly, we see

mysql> desc test1_past_answer;
+----------------+---------------+------+-----+---------+----------------+
| Field          | Type          | Null | Key | Default | Extra          |
+----------------+---------------+------+-----+---------+----------------+
| answer_id      | int           | NO   | PRI | NULL    | auto_increment |
| course_id      | varchar(100)  | NO   | PRI | NULL    |                |
| user_id        | varchar(100)  | NO   | PRI | NULL    |                |
| set_id         | varchar(100)  | NO   | PRI | NULL    |                |
| problem_id     | int           | NO   | PRI | NULL    |                |
| source_file    | text          | YES  |     | NULL    |                |
| timestamp      | int           | YES  |     | NULL    |                |
| scores         | tinytext      | YES  |     | NULL    |                |
| answer_string  | varchar(5012) | YES  |     | NULL    |                |
| comment_string | varchar(5012) | YES  |     | NULL    |                |
+----------------+---------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

and

mysql> desc myTestCourse_past_answer;
+----------------+---------------+------+-----+---------+----------------+
| Field          | Type          | Null | Key | Default | Extra          |
+----------------+---------------+------+-----+---------+----------------+
| answer_id      | int           | NO   | PRI | NULL    | auto_increment |
| course_id      | varchar(80)   | NO   | PRI | NULL    |                |
| user_id        | varchar(80)   | NO   | PRI | NULL    |                |
| set_id         | varchar(80)   | NO   | PRI | NULL    |                |
| problem_id     | int           | NO   | PRI | NULL    |                |
| source_file    | text          | YES  |     | NULL    |                |
| timestamp      | int           | YES  |     | NULL    |                |
| scores         | tinytext      | YES  |     | NULL    |                |
| answer_string  | varchar(5012) | YES  |     | NULL    |                |
| comment_string | varchar(5012) | YES  |     | NULL    |                |
+----------------+---------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)

Notice that the key length is 80 for myTestCourse but 100 for test1.

logout of mysql (or use a new terminal session)

Now exit MySQL
mysql> exit
Bye
$

Now we will edit all coursename_setting tables and all coursename_past_answer tables setting the correct key length.

We need to create a temporary file mytmp.cnf in a working directory to hold login credentials for mysql. In our example we use the working directory temp. Do the following

$ cd
$ cd temp
$ nano mytmp.cnf

and then put the following in the file

[client]
user = webworkWrite                
password = "<webworkWrite password>"

where of course you should replace <webworkWrite password> with webworkWrite's password. Note that webworkWrite's password is contained in the /opt/webwork/webwork2/conf/site.conf file in the line $database_password ="******";.

Now run the commands

$ mysql --defaults-extra-file=mytmp.cnf --database=webwork -B -N -e "SHOW TABLES LIKE '%\_setting' " | awk '{print "ALTER TABLE \140" $1 "\140 MODIFY name varchar(240) NOT NULL; "}'  | mysql --defaults-extra-file=mytmp.cnf --database=webwork -v

$ mysql --defaults-extra-file=mytmp.cnf --database=webwork -B -N -e "SHOW TABLES LIKE '%\_past\_answer' " | awk '{print "ALTER TABLE \140" $1 "\140 MODIFY course_id varchar(80) NOT NULL, MODIFY user_id varchar(80) NOT NULL, MODIFY set_id varchar(80) NOT NULL; "}' | mysql --defaults-extra-file=mytmp.cnf --database=webwork -v


NOTES:[edit]

1. you are upgrading from WeBWorK version 2.12 or later, the above changes should suffice. If you are upgrading from an earlier version of WeBWorK, more tables may need to be modified. For example if you are upgrading from WeBWorK version 2.7, you will run into an error in Step 9 below and following the instructions there you will see that in WebWorK version 2.7 the coursename_past_answer tables has problem_id as Type varchar(100) but WebWorK version 2.15 the Type is int so in analogy with the above commands we need to run

$ mysql --defaults-extra-file=mytmp.cnf --database=webwork -B -N -e "SHOW TABLES LIKE '%\_past\_answer' " | awk '{print "ALTER TABLE \140" $1 "\140 MODIFY problem_id int NOT NULL; "}' | mysql --defaults-extra-file=mytmp.cnf --database=webwork -v

2. These commands should run without errors but if there is an error, the screen output will show you what table was being worked on when the error occurred. Look at it's description and/or data to try to figure out what the problem may be. See Step 9 below for more details.

Step 9[edit]

Now we are finally at the stage where we can convert all tables to the utf8mb4 character set. Note that if a table already uses utf8mb4, the command below will leave things unchanged. We will skip the OPL_ tables since they already use utf8mb4. We assume the setup is as in Step 8 above where you are working in your temp directory. Run the command

$ mysql --defaults-extra-file=mytmp.cnf -B -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='webwork' AND TABLE_NAME NOT LIKE 'OPL\_%'" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE \140" $1 "\140 CONVERT TO CHARACTER SET utf8mb4 ; SET foreign_key_checks = 1; "}' | mysql --defaults-extra-file=mytmp.cnf --database=webwork -v


This should run without errors but if there is an error, the screen output will show you what table was being worked on when the error occurred. Look at it's description and/or data to try to figure out what the problem may be. After fixing the problem, run the above command again.

Log into mysql and try the commands

mysql> USE webwork;
mysql> DESC tablename;
mysql> SELECT * FROM tablename;

For example, use DESC to compare the structure of the table causing trouble to the structure of the analogous table for a course that already uses utf8mb4. Any new course you create will use utf8mb4, so if you do not have any courses using utf8mb4, you may have to create a new test course just to look the description of it's tables.


Now delete the temporary file mytmp.cnf

$ cd
$ cd temp
$ rm mytmp.cnf

Step 10[edit]

Check and possibly repair the database.

Run the command

$ sudo mysqlcheck webwork

and hopefully all tables will be OK. If not you can first backup the database and then try

$ sudo mysqlcheck -r webwork table_name

where obviously you should replace table_name by the name of the table you are trying to repair. If this fails look on the internet for further help.

Method 2: Build a new server and move the whole webwork database and courses directory over to the new server[edit]

Step 1[edit]

Build your new server from scratch following the directions at Installation_Manual_for_2.15_on_Ubuntu_20.04_Server, or use a pre built Virtual Machine Image (see Installing_from_WW2.15_Ubuntu20.04_Server Virtual Machine Image) or use a pre built AWS image (see WeBWorK_2.15_Ubuntu_Server_20.04_LTS_Amazon_Machine_Image). If you are not using Ubuntu, you can still follow Installation_Manual_for_2.15_on_Ubuntu_20.04_Server, just make the obvious changes. Note that if you are building from scratch following the directions at Installation_Manual_for_2.15_on_Ubuntu_20.04_Server, you should stop after step 20, "Test your configuration".

Step 2[edit]

On your old server tar up the courses directory. First cd to a working directory, e.g. temp and then tar up and gzip the courses directory

$ cd
$ cd temp
$ sudo tar -zcvf courses-directory.tar.gz /opt/webwork/courses
[sudo] password for wwadmin: <wwadmin password>

Step 3[edit]

On your old server, make a backup copy of the webwork database. Depending on how you log into mysql, use either mysqldump -u root -p or sudo mysqldump. E.g.

$ cd
$ cd temp
$ mysqldump -u root -p webwork > webwork-database.sql
Enter Password: <mysql root password>

Step 4[edit]

Transfer the courses-directory.tar.gz and webwork-database.sql files from your old server to your new server (e.g. by sftp) and put them in a working directory, e.g. temp

Step 5[edit]

Backup the current courses directory to a working directory, e.g. temp, and untar the one from your old server

$ cd /opt/webwork
$ sudo mv courses ~/temp/courses.bak
[sudo] password for wwadmin: <wwadmin password>
$ cd /
$ sudo tar -xvf ~/temp/courses-directory.tar.gz

You should compare your old modelCourse with the current one

$ cd /opt/webwork/courses
$ diff -r -b --suppress-common-lines  modelCourse/ ~/temp/courses.bak/modelCourse/

If you decide to use the new modelCourse (which you should), do the following

$ mv modelCourse ~/temp/modelCourse.old
$ mv ~/temp/courses.bak/modelCourse/ modelCourse

If your are building a new server from scratch, then you need to fix the bugs in the new modelCourse following the directions in Installation_Manual_for_2.15_on_Ubuntu_20.04_Server#Fix_the_modelCourse and put in the correct links following the directions in Installation_Manual_for_2.15_on_Ubuntu_20.04_Server#Set_up_the_access_to_the_Contrib_directory and Installation_Manual_for_2.15_on_Ubuntu_20.04_Server#Install_and_Set_Up_the_CAPA_Library.

If you are using a prebuilt image, then these bug fixes have already been made.

Note that all permissions and ownerships should be fine assuming both your old and new servers are set up the same way. If you have permission issues, look at Installation_Manual_for_2.15_on_Ubuntu_20.04_Server#Setting_Permissions.

Step 6[edit]

Install the webwork database from your old server into your new server.

Log into MySQL and then DROP and recreate the webwork database. Note use either sudo or the mysql root password.

$ mysql -u root -p 
Enter password: <mysql root password>
mysql> DROP DATABASE webwork;
mysql> CREATE DATABASE webwork;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, LOCK TABLES ON webwork.* TO 'webworkWrite'@'localhost';

Now exit MySQL

mysql> exit
Bye
$

cd to the directory that contains the backup sql file from your old server.

$ cd
$ cd temp

and use the following command to install the database on your new server

mysql -u root -p webwork < webwork-database.sql
Enter password: <mysql root password>

or if your use sudo to log into mysql, the command is

sudo mysql webwork < webwork-database.sql


Step 7[edit]

Now connect to WeBWorK and and try logging into Course Administration (the admin course). This will probably fail unless unless you are upgrading from a very recent version of WeBWorK. If it fails run the following script

$ upgrade_admin_db.pl

and then try logging into the admin course again. This time it should work but you may get a warning about the copyright symbol. If so you have to edit the course.conf file replacing the copyright symbol © by &copy; to prevent this message. Note that this is something you may have to do for all the courses you moved over from your old server.

Step 8[edit]

Now we need to edit the location_addresses table. Log into mysql. Depending on your OS and mysql version, you will either use the command

$ mysql -u root -p 
Enter Password: <mysql root password>

or

$ sudo mysql
[sudo] password for wwadmin: <wwadmin password>

Now issue the following mysql commands:

mysql> USE webwork;
...
Database changed
mysql> ALTER TABLE location_addresses MODIFY ip_mask varchar(180) NOT NULL;

and then exit mysql

mysql> exit
Bye
$

Step 9[edit]

Now we update the OPL. There are a lot of OPL database tables that need to be modified. Rather than doing this manually, we will update the OPL which is a good thing to do anyway and will in the process create all new OPL tables that will use utf8mb4.

cd /opt/webwork/libraries/webwork-open-problem-library
git pull origin

Then run the OPL-update script.

$ OPL-update

This has to convert a lot of data for over 37,000 problems so please be patient; it can take a long time.

Now run the command

$ update-OPL-statistics.pl

to modify the OPL_local_statistics table.

Step 10[edit]

Now continue by following the instructions in Method 1 above starting at Step 5.

Method 3: Update WeBWorK on your current server using git[edit]

Step 1[edit]

Make sure MySQL on your server is using the utf8mb4 character set as it's default. See Check what the default character set is for MySQL on your new or upgraded server above.

Step 2[edit]

Update WeBWorK on your current server basically following the directions in Release notes for WeBWorK 2.14. Note that you should check and see if there are newer instructions. Make sure you follow the instructions about updating the OPL.

Step 3[edit]

You should compare your current, old modelCourse with the one from the new version of WeBWorK

$ cd /opt/webwork/courses
$ diff -r -b --suppress-common-lines  modelCourse/ /opt/webwork/webwork2/courses.dist/modelCourse/

If you decide to use the new modelCourse (which you should), do the following. Move your current model course to a working directory, e.g. temp and then copy the one from courses.dist to courses

$ cd /opt/webwork/courses
$ mv modelCourse ~/temp/modelCourse.old
$ cd /opt/webwork/webwork2/courses.dist
$ rsync -a modelCourse /opt/webwork/courses/

Then follow the instructions for fixing bugs in the modelCourse in Step 5 of Method 2 above.

Step 4[edit]

Before we to alter the database and tables, now would be an excellent time to backup the webwork database if you have not done so already.

Step 5[edit]

Change the default chacter set of the webwork database to utf8mb4.

Log into mysql and run the command

mysql> ALTER DATABASE webwork CHARACTER SET utf8mb4;

Step 6[edit]

Now we need to edit the location_addresses table. Log into mysql. Depending on your OS and mysql version, you will either use the command

$ mysql -u root -p 
Enter Password: <mysql root password>

or

$ sudo mysql
[sudo] password for wwadmin: <wwadmin password>

Now issue the following mysql commands:

mysql> USE webwork;
...
Database changed
mysql> ALTER TABLE location_addresses MODIFY ip_mask varchar(180) NOT NULL;

and then exit mysql

mysql> exit
Bye
$

Step 7[edit]

Now continue by following the instructions in Method 1 above starting at Step 5.


-- Main.ArnoldPizer - 1 April 2021