WeBWorK Main Forum

Data too long for column error while using upgrade-database-to-utf8mb4.pl

Data too long for column error while using upgrade-database-to-utf8mb4.pl

by Andras Balogh -
Number of replies: 8

I was trying to use upgrade-database-to-utf8mb4.pl after transferring courses from 2.17 to 2.19 and after following the discussion at https://webwork.maa.org/moodle/mod/forum/discuss.php?d=8607

 The database upgrade worked for one course, and now LTI works, but  upgrade-database-to-utf8mb4.pl broke down for another course with error message 

-----------

Failed to modify 'set_id' in 'my_course_id' from 'tinyblob' to 'varchar(100).

It is recommended that you restore a database backup.  Make note of the

error output below as it may help in diagnosing the problem.  Note that

the most common reason for this error is the existence of a data value

in a column that does not fit into the smaller size data type that was

needed for the utf8mb4 change.

DBD::MariaDB::db do failed: Data too long for column 'set_id' at row 79 at ./upgrade-database-to-utf8mb4.pl line 247, <> line 1.

----------

What do I do with this course? 

Is the data too long due to long set names like  2-Higher-Order-Equations-04-Linear-2nd-Order-Homogeneous-Equations-Cauchy-Euler-Equations ?

Do I really have to restore a database backup now?


In reply to Andras Balogh

Re: Data too long for column error while using upgrade-database-to-utf8mb4.pl

by Glenn Rice -

I would highly recommend restoring a database backup when you get that error.  This is not hard to do with the database backup file created by the script.  Just run

mysql webwork -u databaseUsername -p < webwork.sql

That assumes that your database is name "webwork" and that you created a "webwork.sql" database dump when you ran the script.

Somewhere in the database there apparently is a set_id that fit into the TINYBLOB data type but is too long to fit into a VARCHAR(100) data type.  This can be tricky to fix.  The set name you gave is not long enough to cause a problem (it is only 89 characters), so look for one that is longer than 100 characters.  Then try exporting the set, renaming the export to a shorter name, and importing it again.

After that, try the script again.

I recommend that you keep the original database backup around, and don't accidentally overwrite it when you run the script again.

In reply to Glenn Rice

Re: Data too long for column error while using upgrade-database-to-utf8mb4.pl

by Andras Balogh -

Thanks. 

It sounds like a lot of work to use upgrade-database-to-utf8mb4.pl for 50-100 courses that I transferred. The upgrade script takes several minutes. It might be easier to create new courses in 2.19 and import the sets from exported def files. Do I understand this correctly?

Will restoring a database backup erase students' progress in other courses since the unsuccessful upgrade?

If I understand correctly, the issue is with course that were created years ago, and then at some point transferred to 2.17 and now to 2.19. I don't see any problem with the courses that were created in 2.17 and then transferred.

In reply to Andras Balogh

Re: Data too long for column error while using upgrade-database-to-utf8mb4.pl

by Glenn Rice -
Restoring the database from the backup file won't erase anything if the backup was just made. It will put everything back to what it was at the time that the backup was made. So if it has been a while, and students have been working problems, then it will erase what has been done since the backup was made.

It is unfortunate that you attempted to use this script on a course that is live.  This is something that should only be done immediately after restoring a course from a previous webwork version and before the course goes live.
In reply to Glenn Rice

Re: Data too long for column error while using upgrade-database-to-utf8mb4.pl

by Andras Balogh -
1. I am sorry, but you mention "a" course that is live. That particular course was not live, but others are. My question is whether or not the other course data will be affected by restoring the backup. Since the webwork.sql file is 2.5G, I suspect that it is the backup of all the courses, not just the one specific course, and restoring will affect the other courses.

2. When the upgrade-database-to-utf8mb4.pl bailed out on the one course, was the data of the other courses effected, or can I just simply delete the specific one course and go on with the rest of the courses without restoring from the hours old backup?
In reply to Andras Balogh

Re: Data too long for column error while using upgrade-database-to-utf8mb4.pl

by Danny Glin -
Yes, restoring the backup will overwrite all courses on the server.

There are no guarantees as to what state the database is in if the upgrade script exited with errors. There's a pretty good chance that the other courses are fine, but it's still possible that the error caused some actions not to be completed on other courses.

Remember that if you delete the set with a name longer than 100 characters that you are also deleting all student progress associated with that set, so save any necessary data first.
In reply to Andras Balogh

Re: Data too long for column error while using upgrade-database-to-utf8mb4.pl

by Glenn Rice -

To add to what Danny said and to answer your second question, yes, you can just delete the course and go on with the rest of the courses without restoring the backup.  The script will only touch the course you specified when you ran the script (assuming you specified a course and weren't running the script for all courses).  Most likely it will be okay even if you were running the script for all courses, but no guarantees there.

In reply to Glenn Rice

Re: Data too long for column error while using upgrade-database-to-utf8mb4.pl

by Andras Balogh -
There is a set with name longer than 100 character, by the way.