Content
error with "openproject configure" (convert mysql db from utf to utf8mb4, mariadb 10.3)
Hello,
i got this error after every update from OP:
[...]
Module filter already enabled
Site openproject already enabled
Job for apache2.service failed because the control process exited with error code.
See "systemctl status apache2.service" and "journalctl -xe" for details.
[repositories] ./bin/postinstall
No repositories have been configured. Skipping configuration.
[smtp] ./bin/postinstall
[memcached] ./bin/postinstall
[openproject] ./bin/postinstall
* * * * * * * * * * * * * * * * * * * * * * * * *
* OpenProject requires UTF-8 encoding set on MySQL < 5.7 *
and UTF-8mb4 beyond that. Please ensure having set
* *
production:
* encoding: utf8mb4 *
* in config/database.yml. *
Otherwise, you WILL run into encoding issue when using 4 byte
* UTF-8 characters since utf8 encoding in MySQL doesn't support them. *
* If you have been running utf8 in the past, please see this guide *
on how to convert your database:
* https://mathiasbynens.be/notes/mysql-utf8mb4 *
* * * * * * * * * * * * * * * * * * * * * * * * *
W, [2019-04-16T14:53:21.096891 #24680] WARN -- : [auth_saml] Missing settings from '/opt/openproject/config/plugins/auth_saml/settings.yml', skipping omniauth registration.
W, [2019-04-16T14:53:21.166670 #24680] WARN -- : hook registered
I, [2019-04-16T14:53:22.386143 #24680] INFO -- : Migrating to CreateDoorkeeperTables (20181112125034)
== 20181112125034 CreateDoorkeeperTables: migrating ===========================
-- adapter_name()
-> 0.0000s
-- create_table(:oauth_applications, {:options=>"ENGINE=InnoDB"})
rake aborted!
StandardError: An error has occurred, all later migrations canceled:
Mysql2::Error: Table 'oauth_applications' already exists: CREATE TABLE `oauth_applications` (`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(255) NOT NULL, `uid` varchar(255) NOT NULL, `secret` varchar
(255) NOT NULL, `owner_type` varchar(255), `owner_id` int, `client_credentials_user_id` int, `redirect_uri` text NOT NULL, `scopes` varchar(255) DEFAULT '' NOT NULL, `confidential` tinyint(1) DEFAULT TRUE NOT NULL, `c
reated_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB
/opt/openproject/vendor/bundle/ruby/2.6.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `_query'
/opt/openproject/vendor/bundle/ruby/2.6.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in `block in query'
/opt/openproject/vendor/bundle/ruby/2.6.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `handle_interrupt'
/opt/openproject/vendor/bundle/ruby/2.6.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in `query'
/opt/openproject/vendor/bundle/ruby/2.6.0/gems/activerecord-5.2.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in `block (2 levels) in execute'
/opt/openproject/vendor/bundle/ruby/2.6.0/gems/activesupport-5.2.2.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
/opt/openproject/vendor/bundle/ruby/2.6.0/gems/activesupport-5.2.2.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
[...]
so i follow the guide from https://mathiasbynens.be/notes/mysql-utf8mb4
1. create backup
2. then "ALTER DATABASE openproject CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;"
2a. check in phpmyadmin -> ok
3. copy output from "show tables;" to a texteditor and remove the | and add "ALTER TABLE" and "CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" before and behind from every table.
3a. looks good:
[...]
MariaDB [openproject]> ALTER TABLE boards CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 126 rows affected (0.62 sec)
Records: 126 Duplicates: 0 Warnings: 0
MariaDB [openproject]> ALTER TABLE categories CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
R SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 249 rows affected (0.59 sec) of 2 'enabling keys' 0% of stage done
Records: 249 Duplicates: 0 Warnings: 0
MariaDB [openproject]> ALTER TABLE changes CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.55 sec)
Records: 0 Duplicates: 0 Warnings: 0
[...]
4. is everything converted to utf8mb4?
MariaDB [(none)]> 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_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)
MariaDB [(none)]>
OK, next step: mysqlcheck -u root -p --auto-repair --optimize --all-databases
run without errors.
5. is really all set to utf8mb4?
root@openproject /etc/mysql # grep -r utf8 *
mariadb.conf.d/50-server.cnf:# utf8 4-byte character set. See also client.cnf
mariadb.conf.d/50-server.cnf:character-set-server = utf8mb4
mariadb.conf.d/50-server.cnf:collation-server = utf8mb4_general_ci
mariadb.conf.d/50-client.cnf:default-character-set = utf8mb4
mariadb.conf.d/50-mysql-clients.cnf:default-character-set = utf8mb4
root@openproject /etc/mysql #
6. the file /opt/openproject/config/database.yml
production:
url: <%= url %>
<% if encoding = ENV['DATABASE_ENCODING'] %>
encoding: utf8mb4 (try this with '' and without, no matter)
Even so, i got the error from above if I run "openproject configure".
7. I have not convert the columns, because in phpmyadmin, there are no collate at columns (its empty).
OpenProject works and I can see the little pictures from the names for example Max Mustermann a circle with "MM", it wasn't like that before.
OS is Debian 9 with MariaDB 10.1.37, OP is version 8.3.1-1555059463.a233c1c8.stretch.
EDIT: now with MariaDB 10.3.14+maria~stretch
But why does this error appear?
Gustav