Top Menu

Jump to content
Home
    • Projects
    • Work packages
    • News
    • Boards
    • Getting started
    • Introduction video
      Welcome to OpenProject Community
      Get a quick overview of project management and team collaboration with OpenProject. You can restart this video from the help menu.

    • Help and support
    • User guides
    • Videos
    • Shortcuts
    • Community forum
    • Enterprise support

    • Additional resources
    • Data privacy and security policy
    • Digital accessibility (DE)
    • OpenProject website
    • Security alerts / Newsletter
    • OpenProject blog
    • Release notes
    • Report a bug
    • Development roadmap
    • Add and edit translations
    • API documentation
  • Sign in
      Forgot your password?
      Create a new account

      or sign in with your existing account

      Google

Side Menu

  • Overview
  • Activity
  • Roadmap
  • Work packages
  • Calendars
  • Team planners
  • Boards
  • Forums
  • Feature tour
    • Table of contents
      • Expanded. Click to collapseCollapsed. Click to showDeveloper
        • Hierarchy leafAccessibility Checklist
        • Hierarchy leafCode Review Guidelines
        • Expanded. Click to collapseCollapsed. Click to showContribution
          • Hierarchy leafGit Workflow
          • Hierarchy leafTranslations
        • Expanded. Click to collapseCollapsed. Click to showDeveloping Plugins
          • Hierarchy leafDeveloping an OmniAuth Authentication Plugin
        • Hierarchy leafRelease Process
        • Hierarchy leafReport a bug
        • Hierarchy leafSecurity
        • Hierarchy leafSetting up an OpenLDAP server for testing
        • Hierarchy leafTheme Features
      • Hierarchy leafDownload
      • Expanded. Click to collapseCollapsed. Click to showFeature tour
        • Hierarchy leafRelease Notes OpenProject 30
        • Expanded. Click to collapseCollapsed. Click to showRelease Notes OpenProject 30 - Overview
          • Hierarchy leafGlossary
          • Hierarchy leafRelease Notes - Accessibility
          • Hierarchy leafRelease Notes - Accessibility changes
          • Hierarchy leafRelease Notes - Add work package queries as menu items to sidebar
          • Hierarchy leafRelease Notes - Copy projects based on Templates
          • Hierarchy leafRelease Notes - Design changes
          • Hierarchy leafRelease Notes - Fixed Bugs
          • Hierarchy leafRelease Notes - Keyboard Shortcuts
          • Hierarchy leafRelease Notes - Project settings
          • Hierarchy leafRelease Notes - Ruby&Rails Update
          • Hierarchy leafRelease Notes - Security
          • Hierarchy leafRelease Notes - Timelines
          • Hierarchy leafRelease Notes - Work packages
      • Hierarchy leafHowto create animated gifs
      • Hierarchy leafMigration Squashing
      • Hierarchy leafMod security
      • Hierarchy leafNew work package page
      • Hierarchy leafOP3 to OP4 Debian upgrade
      • Hierarchy leafOP4 Ubuntu1404 Stable with MySQL in production
      • Hierarchy leafOpenProject 40 Development Setup
      • Expanded. Click to collapseCollapsed. Click to showOpenProject Foundation
        • Hierarchy leafBoards
        • Hierarchy leafMembers
        • Hierarchy leafOPF-Meetings
        • Hierarchy leafStatutes
      • Expanded. Click to collapseCollapsed. Click to showRelease Notes
        • Hierarchy leafOpenProject released on Bitnami
      • Expanded. Click to collapseCollapsed. Click to showRelease Notes OpenProject 40 - Overview
        • Hierarchy leafRelease Notes OpenProject 40 - Accessibility improvements
        • Hierarchy leafRelease Notes OpenProject 40 - Column header functions in work package table
        • Hierarchy leafRelease Notes OpenProject 40 - Improved Design
        • Hierarchy leafRelease Notes OpenProject 40 - Integrated query title on work package page
        • Hierarchy leafRelease Notes OpenProject 40 - Integrated toolbar on work package page
        • Hierarchy leafRelease Notes OpenProject 40 - OmniAuth integration for OpenProject
        • Hierarchy leafRelease Notes OpenProject 40 - Work package details pane
      • Expanded. Click to collapseCollapsed. Click to showSecurity and privacy
        • Hierarchy leafFAQ
      • Expanded. Click to collapseCollapsed. Click to showSupport
        • Expanded. Click to collapseCollapsed. Click to showDownload and Installation
          • Hierarchy leafInstallation MacOS
          • Expanded. Click to collapseCollapsed. Click to showInstallation OpenProject 3 0
            • Hierarchy leafDebian Stable with MySQL in production
            • Hierarchy leafInstallation Ubuntu
            • Hierarchy leafInstallation Windows
            • Hierarchy leafInstallation on Centos 65 x64 with Apache and PostgreSQL 93
          • Expanded. Click to collapseCollapsed. Click to showInstallation OpenProject 40
            • Hierarchy leafOP4 Debian Stable with MySQL in production
          • Expanded. Click to collapseCollapsed. Click to showMigration paths
            • Hierarchy leafFrom Chilliproject to OpenProject
            • Hierarchy leafMigration 15 to 30
            • Hierarchy leafMigration 24 to 30
            • Hierarchy leafMigration Redmine 2x › OpenProject 30
            • Hierarchy leafOpenProject 3 Migration
          • Hierarchy leafOpenProject 40
        • Expanded. Click to collapseCollapsed. Click to showNews
          • Hierarchy leafNew OpenProject Translations Plugin
          • Hierarchy leafNew Plugin on OpenProjectorg Local Avatars
          • Hierarchy leafNew design for OpenProject
          • Hierarchy leafNews Accessibility workshop for OpenProject
          • Hierarchy leafNews Glossary for OpenProject
          • Hierarchy leafNews Heartbleed fixed
          • Hierarchy leafNews Icon Fonts
          • Hierarchy leafNews OpenProject 30 Release
          • Hierarchy leafNews Release GitHub Integration Plugin
          • Hierarchy leafNews Success Story Deutsche Telekom
          • Hierarchy leafNews Timelines
          • Hierarchy leafOpenProject 3013 released
          • Hierarchy leafOpenProject 3017 released
          • Hierarchy leafOpenProject 40 released
          • Hierarchy leafOpenProject 40 will be coming soon
          • Hierarchy leafOpenProject 405 released
          • Hierarchy leafOpenProject and pkgrio
          • Hierarchy leafOpenProject news moved to a new blog
          • Hierarchy leafOpenProjectBitnami
          • Hierarchy leafPackager version with plugins released ("Community edition")
          • Hierarchy leafRegistration OpenProject-Foundation
          • Hierarchy leafRelease OpenProject AuthPlugins
          • Hierarchy leafUpdates on OpenProject
          • Hierarchy leafWe need your feedback for the the new fullscreen view for work packages
        • Hierarchy leafOpenProject Plug-Ins
      • Expanded. Click to collapseCollapsed. Click to showWiki
        • Hierarchy leaf2nd Level Support
You are here:
  • Forums
  • Support Installation & Updates

Content

error with "openproject configure" (convert mysql db from utf to utf8mb4, mariadb 10.3)

Added by Gustav Gans about 4 years ago

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


Loading...