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

Jump to navigation Jump to search

Warning: You are not logged in. Your IP address will be publicly visible if you make any edits. If you log in or create an account, your edits will be attributed to your username, along with other benefits.

The edit can be undone. Please check the comparison below to verify that this is what you want to do, and then save the changes below to finish undoing the edit.

Latest revision Your text
Line 310: Line 310:
 
Now we will edit all coursename_setting tables and all coursename_past_answer tables setting the correct key length.
 
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 <code>mytmp.cnf</code> in a working directory to hold login credentials for mysql. In our example
 
  +
How we do this depends on how you log into mysql. If use use
we use the working directory temp. Do the following
 
  +
sudo mysql
  +
to log into mysql, then you need to become root
  +
sudo su
  +
[sudo] password for wwadmin: <wwadmin password>
  +
and then run the commands below as root
  +
# mysql --database=webwork -B -N -e "SHOW TABLES LIKE '%\_setting' " | awk '{print "ALTER TABLE \140" $1 "\140 MODIFY name varchar(240) NOT NULL; "}' | mysql --database=webwork -v
  +
  +
# mysql --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 --database=webwork -v
  +
  +
On the other hand if you log into mysql with the command
  +
$ mysql -u root -p
  +
Enter Password: <mysql root password>
  +
then we have a make a temporary change to 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). First make a backup. For example do the following
   
 
$ cd
 
$ cd
 
$ cd temp
 
$ cd temp
 
$ nano mytmp.cnf
 
$ nano mytmp.cnf
and then put the following in the file
 
  +
 
[client]
 
[client]
 
user = webworkWrite
 
user = webworkWrite
 
password = "<webworkWrite password>"
 
password = "<webworkWrite password>"
where of course you should replace <code><webworkWrite password></code> with webworkWrite's password. Note that webworkWrite's password is contained in the <code>/opt/webwork/webwork2/conf/site.conf</code> file in the line <code>$database_password ="******";</code>.
 
  +
Then save the file and quit. Now run the commands
 
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 '%\_setting' " | awk '{print "ALTER TABLE \140" $1 "\140 MODIFY name varchar(240) NOT NULL; "}' | mysql --defaults-extra-file=mytmp.cnf --database=webwork -v
Line 332: Line 342:
   
 
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|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
 
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|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
+
# 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|Step 9]] below for more details.
 
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|Step 9]] below for more details.

Please note that all contributions to WeBWorK_wiki may be edited, altered, or removed by other contributors. If you do not want your writing to be edited mercilessly, then do not submit it here.
You are also promising us that you wrote this yourself, or copied it from a public domain or similar free resource (see The WeBWorK Project wiki:Copyrights for details). Do not submit copyrighted work without permission!

Cancel Editing help (opens in new window)