Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

db migrate error with utfmb4 #302

Closed
vince2bir opened this issue Apr 19, 2020 · 16 comments
Closed

db migrate error with utfmb4 #302

vince2bir opened this issue Apr 19, 2020 · 16 comments

Comments

@vince2bir
Copy link

@vince2bir vince2bir commented Apr 19, 2020

I tried to upgrade to v0.12.0rc1

"db migrate" gives the error
ERROR: 2020/04/19 17:16:57 main.go:121: migrate: Error 1709: Index column size too large. The maximum column size is 767 bytes.

I suspect this is due to my mysql being setup in utf8mb4.

@thebaer
Copy link
Member

@thebaer thebaer commented Apr 20, 2020

Thanks for reporting this. Do you have the log messages that occurred before this error? We'll need to know which migration this occurred on. Also, which version of MySQL are you running?

@vince2bir
Copy link
Author

@vince2bir vince2bir commented Apr 20, 2020

MariaDB 10.1.44

Sorry, I did not keep the log.
I had just installed the stable release (with the DB configured in utf8mb4), created an admin, added several blogs and posts for testing.

I wanted to try out the 0.12rc1 and had the reported error.

As I did not have anything of value, I started out from a clean database (using utf8 this time...)

@thebaer
Copy link
Member

@thebaer thebaer commented Apr 20, 2020

Could you try running it again and giving the full logs? And then can you set your database character set to latin1 instead and see if that works? I believe this is the default setting, and shouldn't cause any problems.

Either way, we'll need more information to determine if this is a bug we can / should fix.

@thebaer thebaer added the bug? label Apr 24, 2020
@kushaldas
Copy link

@kushaldas kushaldas commented Apr 24, 2020

 ./writefreely db migrate
2020/04/24 14:04:20 Loading config.ini configuration...
2020/04/24 14:04:20 Connecting to mysql database...
2020/04/24 14:04:20 Migrating to V4: support oauth
2020/04/24 14:04:20 Closing database connection...
ERROR: 2020/04/24 14:04:20 main.go:121: migrate: Error 1071: Specified key was too long; max key length is 767 bytes

I am right now in the middle of upgrade to 0.12.0 from 0.11.2 and got the above error. @thebaer

@thebaer
Copy link
Member

@thebaer thebaer commented Apr 24, 2020

Thanks for including the logs, @kushaldas. Is your database also configured for utf8mb4?

@kushaldas
Copy link

@kushaldas kushaldas commented Apr 24, 2020

Thanks for including the logs, @kushaldas. Is your database also configured for utf8mb4?

Let me try to figure that out, do you have the command handy to get that information?

@thebaer
Copy link
Member

@thebaer thebaer commented Apr 24, 2020

Yep:

USE writefreelydatabasename;
SELECT @@character_set_database, @@collation_database;
@kushaldas
Copy link

@kushaldas kushaldas commented Apr 24, 2020

> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_general_ci   |
+--------------------------+----------------------+
1 row in set (0.00 sec)
@thebaer
Copy link
Member

@thebaer thebaer commented Apr 24, 2020

Thanks, so this does seem to be the same issue. I'll update our installation and upgrade docs to make sure this works in the future.

But for your case, this database change should fix it:

ALTER DATABASE writefreelydatabasename CHARACTER SET latin1 COLLATE latin1_swedish_ci;

And then you can run writefreely db migrate again.

@thebaer thebaer added bug documentation and removed bug? labels Apr 24, 2020
@kushaldas
Copy link

@kushaldas kushaldas commented Apr 24, 2020

@thebaer next error while trying to migrate again:

 ./writefreely db migrate
2020/04/24 14:43:35 Loading config.ini configuration...
2020/04/24 14:43:35 Connecting to mysql database...
2020/04/24 14:43:35 Migrating to V4: support oauth
2020/04/24 14:43:35 Closing database connection...
ERROR: 2020/04/24 14:43:35 main.go:121: migrate: Error 1050: Table 'oauth_users' already exists
@thebaer
Copy link
Member

@thebaer thebaer commented Apr 24, 2020

This last query should fix that:

DROP TABLE oauth_users;

(You won't lose any data since this new table was part of the V4 migration that failed to complete.)

@kushaldas
Copy link

@kushaldas kushaldas commented Apr 24, 2020

Problems solved. Thank you @thebaer.

@thebaer
Copy link
Member

@thebaer thebaer commented Apr 24, 2020

Awesome! Happy to help.

I've adjusted the upgrade instructions in our v0.12.0 notes, as well as on the install guide. So I'll close this issue now.

@thebaer thebaer closed this Apr 24, 2020
@mrvdb
Copy link
Collaborator

@mrvdb mrvdb commented Apr 24, 2020

while the latin1 character set may not cause problems, I'd suggest moving towards instructions that let mysql also handle things in full utf-8 mode. Granted, utf-8 in mysql is not the easiest, but it gets better over time.

The Index column size too large. The maximum column size is 767 bytes. error is fairly well known and caused by an older InnoDB restricting on the index size. There used to be a parameter innodb_large_prefix (See: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix) which is now the default if I recall correctly.

The direct error can be solved by different means (limiting the index prefix or upgrading to newer versions of mysql/mariadb and setting the parameter) but not having full utf-8 encoding in the database does not sound like the right direction.

I've been running writefreely in 'full utf-8', i.e. using utf8mb4 + utf8mb4_unicode_ci ) using mariadb 10.4.12-1 without any issues so far, so perhaps there's still something else going on what is causing the errors
Just my 2 cents.

@thebaer
Copy link
Member

@thebaer thebaer commented Apr 24, 2020

Agreed there could definitely be something else at play here... So you haven't seen the same issue with your database config?

I'd like to know more about the upsides / downsides of getting everything on UTF-8 before changing over. Right now we use utf8mb4 on columns where it's needed, like for public-facing data -- post content, collection titles, etc. Otherwise we don't really have a need for it, especially for columns like the post slug, where the application automatically converts all information to ASCII. If our current setup means overall lower disk space usage, that seems like a positive. But if there are other benefits for switching, without major downsides compared to latin1, then maybe we move to utf8 for everything.

@mrvdb
Copy link
Collaborator

@mrvdb mrvdb commented Apr 24, 2020

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
4 participants
You can’t perform that action at this time.