db migrate error with utfmb4

[vince2bir]

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]

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]

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]

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.

[kushaldas]

 ./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]

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

[kushaldas]

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]

Yep:

USE writefreelydatabasename;
SELECT @@character_set_database, @@collation_database;

[kushaldas]

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

[thebaer]

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.

[kushaldas]

@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]

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]

Problems solved. Thank you @thebaer.

[thebaer]

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.

[mrvdb]

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]

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]

So you haven't seen the same issue with your database config?

No, I haven't seen that particular issue on wf, but I paid specific attention to it when setting up the database, because I *have* seen it on many, many other databases I had. ;-) Is the current stanza: "latin1 unless we need utf-8 ..."? If so, was this just because of the disk space savings? Are there actual relevant savings here with mysql or are they just theoretical? (I would be a bit surprised if there were very significant differences given the overhead of mysql, but I may be wrong.) Are there other considerations to take latin1 as the default? The main advantage would be simplicity and compatibility in my view. Assuming utf-8 everywhere is a simple rule and easy to work with. There's no administrative overhead keeping track of what needs to be encoded how and instructions / limitations can be simple: "Support utf-8 everywhere".