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 .