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.

Sign up for GitHub

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

Jump to bottom

The wp_eo_venuemeta table is not created because of exceeded index size limit #404

Closed
marcin-kruk opened this issue Mar 5, 2017 · 2 comments
Closed

The wp_eo_venuemeta table is not created because of exceeded index size limit #404

marcin-kruk opened this issue Mar 5, 2017 · 2 comments
Labels
bug

Comments

@marcin-kruk
Copy link
Contributor

Installing Event Organiser when using MySQL 5.6.35 (see below for more details) results in wp_eo_venuemeta database table not being created.

Test environment:

  • WordPress version: 4.7.2 (latest at the time of writing)
  • Event Organiser version: 3.1.7 (latest at the time of writing)
  • MySQL version: 5.6.35

Steps to reproduce:

  • Install Event Organiser plugin when using MySQL 5.6.35 as a database

Plugin installation ends successfully:

Unpacking the package…
Installing the plugin…
Plugin installed successfully.

but activating plugin fails with message:

There has been an error with Event Organiser. One or more tables are missing:
wp_eo_venuemeta
Please try re-installing the plugin.

As a result a venue can be created but it's fields cannot be saved.

This problem can be reproduced with clean, default installation of WordPress on MySQL 5.6.35, but possibly it also affects other 5.5 and 5.6 versions.
I confirmed it to work as expected on 5.7.17.
@marcin-kruk
Copy link
Contributor Author

After investigation it turned out that CREATE TABLE {$wpdb->eo_venuemeta} query, executed by event-organiser-install.php, fails with an error:

#1709 - Index column size too large. The maximum column size is 767 bytes.

The reason why everything works fine for MySQL 5.7.17 version is probably the fact, that since 5.7.7 default value of innodb_large_prefix setting, which allows "index key prefixes longer than 767 bytes (up to 3072 bytes)", was changed from OFF to ON.
Also, there needs to be correct ROW_FORMAT option specified (DYNAMIC or COMPRESSED) for table when creating it, and the default value (used when not specified, as it is the case for EO) was changed from COMPACT in 5.6 to DYNAMIC in 5.7 (well, actually to value of innodb_default_row_format setting which defaults to DYNAMIC).

It seems that WordPress handles this case by limiting the index prefix length of string fields to 191 characters (191 chars * 4 bytes/char = 764 bytes < 767 bytes) - see here.

WordPress supports this setup, so I would say that Event Organiser could handle it exactly the same.

marcin-kruk added a commit to marcin-kruk/Event-Organiser that referenced this issue Mar 5, 2017
@marcin-kruk
[stephenharris#404] Handle 767 byte index size limit in MySQL
a906cca
@marcin-kruk marcin-kruk mentioned this issue Mar 5, 2017
Merged
@stephenharris
Copy link
Owner

Thanks for reporting this @marcin-kruk and your subsequent investigation. This shall be fixed in the next patch update.

stephenharris added a commit that referenced this issue Mar 8, 2017
@stephenharris
Merge pull request #405 from marcin-kruk/bugfix/404-handle-mysql-inde…
f11019e 
…x-size-limit

[#404] Handle 767 byte index size limit in MySQL
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@stephenharris @marcin-kruk