Upgrade from 3.2.0 to 4.1.0 - encoding ??
Added by Anne Hammond over 4 years ago
My previous mysql database was
encoding: utf8.
The instructions for 4.1.0 say to use
# Use "utf8" instead of "utfmb4" for MySQL prior to 5.7.7 encoding: utf8mb4
I am using
mysql Ver 8.0.17 for Linux on x86_64 (Source distribution)
I loaded the database:
mysql -u redmine redmine -p < /tmp/redmine-mysql-backup.0.sql
Will using a different encoding from my loaded database be a problem?
Thanks in advance,
Replies (6)
RE: Upgrade from 3.2.0 to 4.1.0 - encoding ?? - Added by Anne Hammond over 4 years ago
Yes, you have to convert your utf8 database to utfmb4.
I found this:
https://www.redmine.org/issues/10772
Then I did
mysql> use redmine; mysql> ALTER DATABASE redmine CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; mysql> show tables;
From the output, I created a convert.sql script:
USE redmine; ALTER TABLE agile_data CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; (one line for each table)
Then
mysql -u redmine -p < convert.sql mysql> show variables where Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+--------------------+ | 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_unicode_ci | | collation_server | utf8mb4_0900_ai_ci | +--------------------------+--------------------+ 10 rows in set (0.01 sec)
I am researching some of the values above.
This page
https://mathiasbynens.be/notes/mysql-utf8mb4
indicates to
$ mysqlcheck -u root -p --auto-repair --optimize --all-databases
Stay tuned.
RE: Upgrade from 3.2.0 to 4.1.0 - encoding ?? - Added by Anne Hammond over 4 years ago
After upgrading my database to utf8mb4, I get "Internal error" regarding Tracker
Completed 500 Internal Server Error in 161ms (ActiveRecord: 103.9ms)
ActionView::Template::Error (undefined method `description' for #<Tracker:0x000055dfa2661d68>):% @trackers.each do |tracker| >
66: <tr>
67: <td class="name">
68: <= link_to tracker.name, project_issues_path(Herve Harster, :set_filter => 1, :tracker_id => tracker.id), :title => tracker.description >
70: <td>
71: <= link_to @open_issues_by_tracker[tracker].to_i, project_issues_path(Herve Harster, :set_filter => 1, :tracker_id => tracker.id) %>
app/views/projects/show.html.erb:68:in `block in app_views_projects_show_html_erb_1864405475639700376_70049391965740'`_app_views_projects_show_html_erb__1864405475639700376_70049391965740' `sudo_mode'
Is this core redmine ??
RE: Upgrade from 3.2.0 to 4.1.0 - encoding ?? - Added by Liane Hampe over 4 years ago
Hi Anne
Yes, the tracker description is an attribute of the Tracker model in Redmine core. If it is not defined, it may have different reasons.
Sometimes it is a loading problem. Therefore, restart the application server.
If the error remains, it is possible that the table column does not exists. The description column is new and needs to be migrated. Try to migrate again. If not successful try to redo the last migration and migrate again.
When you have a couple of problems it is possible that the migration with the respective missing column is marked as done and won't get repeated before it is reverted.
Long way for you. Hang in there, you will make it!
Best,
Liane
RE: Upgrade from 3.2.0 to 4.1.0 - encoding ?? - Added by Anne Hammond over 4 years ago
Thanks Liane,
I believe that I removed all the plugins from the redmine/plugins directory and then
cd /var/www/redmine
RAILS_ENV=production bundle exec rake db:migrate
systemctl restart httpd
That eliminated the Tracker error message.
Thank you so much,
Anne
RE: Upgrade from 3.2.0 to 4.1.0 - encoding ?? - Added by Anne Hammond over 4 years ago
I now can see all my issues and contacts. I did not ALTER all the tables in my utf8 database to be utf8mb4. That still remains a question.
This was the process:
After recreating the redmine database:
mysql> create database redmine character set utf8mb4;
mysql> grant all privileges on redmine.* to 'redmine'@'localhost';
mysql> flush privileges;
RAILS_ENV=production bundle exec rake db:migrate
reloaded each plugin one by one to make sure they all migrate without error. In my case I had one plugin that didn't migrate, and I had to ask for a revised plugin.
Then I recreated the redmine database again:
mysql> use redmine;
mysql> drop database redmine:
mysql> create database redmine character set utf8mb4;
mysql> grant all privileges on redmine.* to 'redmine'@'localhost';
mysql> flush privileges;
mysql> quit
RAILS_ENV=production bundle exec rake db:migrate
reload utf8 database from redmine 3.1.0:
mysql -u redmine redmine -p < /tmp/redmine-mysql-backup.0.sql
migrate all plugins one by one (did this for each redmineup plugin):
RAILS_ENV=production bundle exec rake redmine:plugins NAME=redmine_checklists
migrate database to redmine 4.1.0:
RAILS_ENV=production bundle exec rake db:migrate
I can now access issues and contacts.
As I said, I did not ALTER the tables to utf8mb4, but accept any advice.
Thanks in advance,
Anne
RE: Upgrade from 3.2.0 to 4.1.0 - encoding ?? - Added by Gary Aitken almost 4 years ago
So, 9 months on, is your redmine 4.1.1 working properly with utf8 charsets even though the db was defined to use utf8mb4? Or did you eventually migrate the tables to utf8mb4? I have done a similar upgrade and see the only utf8mb4 encoding is that for the additional table ar_internal_metadata. Is the utf8mb4 encoding only needed if actual data includes 4-byte characters? And if so, does all of redmine now work properly with 4-byte characters? Are there problems with a redmine where charsets are utf8mb4 that are not there for utf8 charsets?
mysql> SELECT CCSA.character_set_name, T.table_name FROM tables T, -> collation_character_set_applicability CCSA WHERE CCSA.collation_name=T.table_collation -> AND T.table_schema="redmine"; +--------------------+-------------------------------------+ | character_set_name | table_name | +--------------------+-------------------------------------+ | utf8 | attachments | ... | utf8 | workflows | | utf8mb4 | ar_internal_metadata | +--------------------+-------------------------------------+ 56 rows in set (0.00 sec)