Project

General

Profile

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)
    (1-6/6)