Error setting up FusionAuth free version

[nishant]

Hi,

I'm new to FusionAuth and trying to setup the community version. I'm using MySQL 5.6 as my database. I'm unable to complete the initial setup. The logs report the error below:

Cause: java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

The query being executed is this:

INSERT INTO form_fields (id, data, insert_instant, last_update_instant, name)
  VALUES (SUBSTR(CONCAT(MD5(RAND()), MD5(RAND())), 3, 16), '{"key": "user.email", "control": "text", "required": true, "type": "email", "data": {"leftAddon": "user"}}', FLOOR(UNI
X_TIMESTAMP(NOW(3)) * 1000), FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000), 'Email'),
  (SUBSTR(CONCAT(MD5(RAND()), MD5(RAND())), 3, 16), '{"key": "user.password", "control": "password", "required": true, "type": "string", "data": {"leftAddon": "lock"}}', FLOOR(UN
IX_TIMESTAMP(NOW(3)) * 1000), FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000), 'Password'),
  (SUBSTR(CONCAT(MD5(RAND()), MD5(RAND())), 3, 16), '{"key": "user.firstName", "control": "text", "required": false, "type": "string", "data": {"leftAddon": "info"}}', FLOOR(UNIX
_TIMESTAMP(NOW(3)) * 1000), FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000), 'First name'),
  (SUBSTR(CONCAT(MD5(RAND()), MD5(RAND())), 3, 16), '{"key": "user.middleName", "control": "text", "required": false, "type": "string", "data": {"leftAddon": "info"}}', FLOOR(UNI
X_TIMESTAMP(NOW(3)) * 1000), FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000), 'Middle name'),
  (SUBSTR(CONCAT(MD5(RAND()), MD5(RAND())), 3, 16), '{"key": "user.lastName", "control": "text", "required": false, "type": "string", "data": {"leftAddon": "info"}}', FLOOR(UNIX_
TIMESTAMP(NOW(3)) * 1000), FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000), 'Last name'),
  (SUBSTR(CONCAT(MD5(RAND()), MD5(RAND())), 3, 16), '{"key": "user.fullName", "control": "text", "required": false, "type": "string", "data": {"leftAddon": "info"}}', FLOOR(UNIX_
TIMESTAMP(NOW(3)) * 1000), FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000), 'Full name'),
  (SUBSTR(CONCAT(MD5(RAND()), MD5(RAND())), 3, 16), '{"key": "user.birthDate", "control": "text", "required": false, "type": "date", "data": {"leftAddon": "calendar"}}', FLOOR(UN
IX_TIMESTAMP(NOW(3)) * 1000), FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000), 'Birthdate'),
  (SUBSTR(CONCAT(MD5(RAND()), MD5(RAND())), 3, 16), '{"key": "user.mobilePhone", "control": "text", "required": false, "type": "string", "data": {"leftAddon": "mobile"}}', FLOOR(
UNIX_TIMESTAMP(NOW(3)) * 1000), FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000), 'Mobile phone'),
  (SUBSTR(CONCAT(MD5(RAND()), MD5(RAND())), 3, 16), '{"key": "user.username", "control": "text", "required": true, "type": "string", "data": {"leftAddon": "user"}}', FLOOR(UNIX_T
IMESTAMP(NOW(3)) * 1000), FLOOR(UNIX_TIMESTAMP(NOW(3)) * 1000), 'Username');

Looks like the issue is related to this question, and I did notice that the name column in the form_fields is of type varchar(191) and the character set being used is utf8mb4. One of the resolutions is to use a max length of 190. I can't change the FusionAuth code. Has anyone faced this issue? Is there a known resolution or alternatively how do I solve this? Appreciate any help.

Thanks,
-N.

[dan]

Hiya,

The minimum version of mysql for FusionAuth is 5.7.9. This and other system requirements are documented here: https://fusionauth.io/docs/v1/tech/installation-guide/system-requirements

Any chance you can upgrade mysql or use postgresql 9.5+?

[nishant]

Hi Dan,

Thanks for the quick response! Yes I'll look into upgrading MySQL.

Cheers,
-N

[dan]

Great! If you end up sticking with mysql, make sure you check out this:

https://fusionauth.io/community/forum/topic/109/what-are-the-implications-of-turning-off-enforce-utf8mb4-for-mysql

and the database.mysql.enforce-utf8mb4 section of https://fusionauth.io/docs/v1/tech/reference/configuration

[dan]

@nishant did you get this working?

[nishant]

@dan thanks for following up! Yes I upgraded our database to 5.7 with the correct options for character set and I was able to setup FusionAuth app as well as setup a sample app to demonstrate the login/logout functionality. Will dig into registering new users, password management etc. If you have some resources for creating a custom theme please do share. Thanks again!

[dan]

Hiya,

This is probably the best resource for themes: https://fusionauth.io/docs/v1/tech/themes/

The video is a bit out of date in terms of where you go in the admin interface, but the functionality is much the same.

Cheers!

[nishant]

Thanks!

[dan]

Great. Marking this solved. Let me know if that's not ok .