MySQL 5.7 doesn't start after settings for utf8mb4 are applied

[Kash Santos]

I am trying to setup MySQL 5.7.26 to move my data from H2.

I am following the instructions from Jira (https://confluence.atlassian.com/adminjiraserver/connecting-jira-applications-to-mysql-5-7-966063305.html), but when I update the my.ini and restart the server I get an error saying "Windows could not start the MySQL57 service on Local Computer. Error 1053: The service did not respond to start or control request in a timely fashion."

I've tried multiple times with the new settings in different locations of the ini file, adding character-set configuration in the [client] and [mysql] sections also, but nothing works.

Any suggestions

[Kash Santos · Accepted solution]

I found a workaround. 

In MySQL Workbench I went to Server / Status and System Variables / System Variables Tab. In the left menu, select "Other". I Manually changed the character set variables as shown below:

character_set_client = utf8mb4

character_set_connection = utf8mb4

character_set_database = utf8mb4

character_set_results = utf8mb4

character_set_server = utf8mb4

The system automatically updated the collation variables below:

collation_connection = utf8mb4_general_ci

collation_database = utf8mb4_general_ci

collation_server = utf8mb4_general_ci

After that, I created a new dbconfig.xml file using the Jira config tool and restarted Jira. I was able to import my backup and log in. Everything is working properly so far.

Note: if MySQL is restarted for any reason this configuration needs to be done again.

I hope this helps someone.

[Alexander Badich]

I AM GOING TO SAY THANK YOU SO MUCH TO @G Morrow

I have been trying everything to get my .ini file to stick with certain FREAKING modifications...

Changing the location of the file, and making edits someplace else then saving as ANSI worked.  I have spent all day trying to fix this character_set/collation_set on my database and you just saved my life!  Something to note now, if anyone was having issues with Jira not being able to start with this parameter set:  <databasetype>mysql57<databasetype> even though you were running 8.0+ jira software and MySQL 5.7+...

G_morrows and Bob S adviced helped the issue:

default-storage-engine = INNODB
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb_default_row_format = DYNAMIC
innodb_large_prefix = ON
innodb_file_format = Barracuda

https://jira.atlassian.com/browse/JRASERVER-71060?filter=12844 --- issue I was speaking about...

Second Issue I was speaking about:

https://community.atlassian.com/t5/Jira-questions/Issues-with-MySQL-5-7-and-Jira-8-12/qaq-p/1513631#M453618

Thank you again!

[G Morrow]

Little things matter apparently lol, don't worry it took me almost 2 days to figure that out.  Glad it is helping someone besides me.

[G Morrow]

I was having a similar problem to what was above. Turns out anytime I made changes to the my.ini file in Notepad it would default to UTF8 encoding and it needs to be ANSI.

To fix it I copied file to another folder, made all my changes, save as and changed Encoding to be ANSI.  Copy/Paste back into the correct directory and then it works fine no issues.

Was kind of a pain to find, but I assume it has to do with the editor you use to modify the my.ini file which in my case is just plain old notepad... 

[Bob Schleiger]

Here are my settings in mysql config file:

default-storage-engine = INNODB
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb_default_row_format = DYNAMIC
innodb_large_prefix = ON
innodb_file_format = Barracuda

Database for jira created according to Atlassian documentation. 

Database variables:

mysql> SHOW GLOBAL 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_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.01 sec)

Set database type to mysql57 in db.config in Jira home directory. 

This is for Jira 8.X

[Kurt Klinner]

@Kash Santos 

Hi Kash

did you check the log file for a more detailed error description

https://dev.mysql.com/doc/refman/8.0/en/windows-troubleshooting.html might be useful

Cheers

Kurt

[Kash Santos]

Thank you for the tip. Here is the error logs:

2019-05-07T16:20:22.958784Z 0 [Note] InnoDB: Waiting for purge to start
2019-05-07T16:20:23.021395Z 0 [Note] InnoDB: 5.7.26 started; log sequence number 2525675
2019-05-07T16:20:23.023071Z 0 [Note] InnoDB: Loading buffer pool(s) from C:\ProgramData\MySQL\MySQL Server 5.7\Data\ib_buffer_pool
2019-05-07T16:20:23.023283Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-05-07T16:20:23.046938Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2019-05-07T16:20:23.047693Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2019-05-07T16:20:23.048535Z 0 [Note] IPv6 is available.
2019-05-07T16:20:23.049122Z 0 [Note] - '::' resolves to '::';
2019-05-07T16:20:23.049786Z 0 [Note] Server socket created on IP: '::'.
2019-05-07T16:20:23.072402Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190507 9:20:23
2019-05-07T16:20:23.279096Z 0 [Note] Failed to start slave threads for channel ''
2019-05-07T16:20:23.334425Z 0 [Note] Event Scheduler: Loaded 0 events
2019-05-07T16:20:23.335581Z 0 [Note] C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe: ready for connections.
Version: '5.7.26-log' socket: '' port: 3306 MySQL Community Server (GPL)
2019-05-07T16:20:28.745104Z 4 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2019-05-07T16:53:26.047463Z 5 [Note] Aborted connection 5 to db: 'unconnected' user: 'root' host: 'localhost' (Got an error reading communication packets)
2019-05-07T16:53:56.411200Z 0 [Note] C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe: Normal shutdown

I wonder if the problem is related to the IP. I didn't have to set it up anywhere since by default it uses 127.0.0.1 (localhost).

Any ideas?