Compatibility problems with MySql version 8

[ribal-aladeeb]

I have tried installing Cbrain locally. I was using:

• MySQL version 8.0.22 (deployed in a docker container)
• rvm version 1.29.10
• rails version 5.0.7.2
• ruby 2.4.1

I followed the steps to setup the BrainPortal and got to the schema init step.

running the command: rake db:schema:load RAILS_ENV=development creates 29 tables (when I think there should be 31) and results in the following error:

ActiveRecord::StatementInvalid: Mysql2::Error: Unknown collation: 'utf8_0900_ai_ci': CREATE TABLE `schema_migrations` (`version` varchar(255) COLLATE utf8_0900_ai_ci PRIMARY KEY) ENGINE=InnoDB

The database looks like this:

One of the devs pointed out to me that there was possibly an issue with rails itself (see the following stack link explaining this).

I then changed the version of rails in the Gemfile to 5.1.7, ran bundle update (I understand that this is not a safe way to upgrade rails), and rand the db schema init script again and it worked. 31 tables exist in the cbrain_dev db.

I don't know if using MySQL 8 will be problematic in the future (when installing the Bourreau) but so far it seems to work.

[prioux]

Hello Ribal, I'm not sure I understand. According to the current schema, there are only 29 tables in CBRAIN:

> grep -c create_table schema.rb 
29

So where does your 31 come from?

That being said, your error message was clearly about a unsupported collation in your DB. Which is also weird, because the schema clearly specifies a collation of "utf8_unicode_ci" for all 29 tables. Your own report even shows that.

> grep create_table schema.rb | cut -d= -f2-99 | uniq -c
  29 InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci" do |t|

Now, here's what I think is happening. On top of the 29 data tables of CBRAIN, Rails also create another table called 'schema_migrations' which is not defined in the schema file proper. It's for internal maintenance. And your database was created with a setting for a default collation set to "utf8mb4_0900_ai_ci" which is not supported/configured for your MySQL server. So just basically set the default collation on your DB to be "utf8_unicode_ci" and the error will disappear. Alternatively, add the MySQL pack that provides "utf8mb4_0900_ai_ci" support (I'm assuming it's some sort of optional external package).

[prioux]

The raw mysql command would be:

ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_unicode_ci;

(Adjust the database name; might require quotes around the values, too)