How do I fix this wordpress database error: Specified key was too long; max key length is 767 bytes?

I am trying to install a wordpress plugin called CSSJockey Membership Modules. When I try to activate it I get these two fatal errors:

WordPress database error: [Specified key was too long; max key length is 767 bytes]
CREATE TABLE IF NOT EXISTS `Or6An1Za7Io4Ev3Nt5_cjfm_custom_forms` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `form_name` varchar(200) NOT NULL DEFAULT '', `default_user_role` varchar(200) NOT NULL DEFAULT '', `can_remove` INT(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `form_name` (`form_name`) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

WordPress database error: [Specified key was too long; max key length is 767 bytes]
CREATE TABLE IF NOT EXISTS `Or6An1Za7Io4Ev3Nt5_cjfm_custom_forms` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `form_name` varchar(200) NOT NULL DEFAULT '', `default_user_role` varchar(200) NOT NULL DEFAULT '', `can_remove` INT(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `form_name` (`form_name`) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Open in new window


I made a search for keywords inside the plugin folder and I am attaching the two php files inside the extension that had the keywords:
"CREATE TABLE IF NOT EXISTS"
item-setup.phpdb-upgrade.php

How do I fix this problem? I would like to fix this problem at the source as if ever I wanted to reinstall this plugin again I would like to keep a modified version of it or know how to prepare my database beforehand.

thanks in advance
badwolfffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LajuanTaylorCommented:
It appears that the plugin is trying to create a table in MySQL that violates Limits on InnoDB Tables -
http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html
An index key for a single-column index can be up to 767 bytes. The same length limit applies to any index key prefix
.
0
badwolfffAuthor Commented:
Yes, thank you. But how do I fix it?
0
LajuanTaylorCommented:
In utf8mb4 column, each character needs 4 bytes. So 255 characters means 1020 bytes. The max key length is 767 bytes.

If you need all 255 characters, consider using this innodb setting.
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

badwolfffAuthor Commented:
Hi,

thanks again. I am not very experience with MySql. Could you please tell me the steps to follow?
That link is too technical for me.

thanks
0
LajuanTaylorCommented:
I just left my office. I'll get back to you shortly.
0
badwolfffAuthor Commented:
thanks
0
badwolfffAuthor Commented:
By the way, in the meantime, I have done this already:

On my server in file my.cnf I added:

innodb_large_prefix=on
innodb_file_format=barracuda
innodb_file_per_table=true

I restarted both MySql and Apache services on the VPS and then retried but the plugin still throws the same error.
0
LajuanTaylorCommented:
Can you check your settings your innodb settings by executing the following SQL command?
show variables like 'innodb%';

The MySQL documentation states that this is a Global system variable that can be  from command line or dynamically at run-time.
0
badwolfffAuthor Commented:
mysql> show variables like 'innodb%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| innodb_adaptive_flushing        | ON                     |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 8388608                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_instances    | 1                      |
| innodb_buffer_pool_size         | 134217728              |
| innodb_change_buffering         | all                    |
| innodb_checksums                | ON                     |
| innodb_commit_concurrency       | 0                      |
| innodb_concurrency_tickets      | 500                    |
| innodb_data_file_path           | ibdata1:10M:autoextend |
| innodb_data_home_dir            |                        |
| innodb_doublewrite              | ON                     |
| innodb_fast_shutdown            | 1                      |
| innodb_file_format              | Antelope               |
| innodb_file_format_check        | ON                     |
| innodb_file_format_max          | Antelope               |
| innodb_file_per_table           | OFF                    |
| innodb_flush_log_at_trx_commit  | 1                      |
| innodb_flush_method             |                        |
| innodb_force_load_corrupted     | OFF                    |
| innodb_force_recovery           | 0                      |
| innodb_io_capacity              | 200                    |
| innodb_large_prefix             | OFF                    |
| innodb_lock_wait_timeout        | 50                     |
| innodb_locks_unsafe_for_binlog  | OFF                    |
| innodb_log_buffer_size          | 8388608                |
| innodb_log_file_size            | 5242880                |
| innodb_log_files_in_group       | 2                      |
| innodb_log_group_home_dir       | ./                     |
| innodb_max_dirty_pages_pct      | 75                     |
| innodb_max_purge_lag            | 0                      |
| innodb_mirrored_log_groups      | 1                      |
| innodb_old_blocks_pct           | 37                     |
| innodb_old_blocks_time          | 0                      |
| innodb_open_files               | 300                    |
| innodb_print_all_deadlocks      | OFF                    |
| innodb_purge_batch_size         | 20                     |
| innodb_purge_threads            | 0                      |
| innodb_random_read_ahead        | OFF                    |
| innodb_read_ahead_threshold     | 56                     |
| innodb_read_io_threads          | 4                      |
| innodb_replication_delay        | 0                      |
| innodb_rollback_on_timeout      | OFF                    |
| innodb_rollback_segments        | 128                    |
| innodb_spin_wait_delay          | 6                      |
| innodb_stats_method             | nulls_equal            |
| innodb_stats_on_metadata        | ON                     |
| innodb_stats_sample_pages       | 8                      |
| innodb_strict_mode              | OFF                    |
| innodb_support_xa               | ON                     |
| innodb_sync_spin_loops          | 30                     |
| innodb_table_locks              | ON                     |
| innodb_thread_concurrency       | 0                      |
| innodb_thread_sleep_delay       | 10000                  |
| innodb_use_native_aio           | ON                     |
| innodb_use_sys_malloc           | ON                     |
| innodb_version                  | 5.5.43                 |
| innodb_write_io_threads         | 4                      |
+---------------------------------+------------------------+
60 rows in set (0.00 sec)

Open in new window

0
badwolfffAuthor Commented:
Hi,

I could clearly see from above that the large_prefix was not on so I did this and turned it ON successfully:
In the mysql command line I ran these commands
set global innodb_file_format = BARRACUDA;
set global innodb_large_prefix = ON;

Now when I try to activate the plugin I don't get the previous error anymore but I get this:
WordPress database error: [Index column size too large. The maximum column size is 767 bytes.]
CREATE TABLE IF NOT EXISTS `Or6An1Za7Io4Ev3Nt5_cjfm_custom_forms` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `form_name` varchar(200) NOT NULL DEFAULT '', `default_user_role` varchar(200) NOT NULL DEFAULT '', `can_remove` INT(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `form_name` (`form_name`) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Open in new window


please help

thanks
0
badwolfffAuthor Commented:
Ah! Another problem. When I run those commands the large prefix goes on
Then I restart mysql and test it again using your command and I see the large prefix is off again
this is frustrating :(
0
LajuanTaylorCommented:
What version of MySQL and WordPress are you using?  I didn't see much on the plugin site that could help resolve your issue. http://docs.cssjockey.com/cjfm/installation-activation/

Did the settings in your my.cnf stay after you changed them?
0
LajuanTaylorCommented:
I was able to get the query working by changing the query collation from "utf8mb4_unicode_ci" to "utf8_unicode_ci", which is the default setting for MySQL 5.6.24. I'm using Windows 7 64Bit, IIS 7.5, and PHP 5.6.8.

CREATE TABLE IF NOT EXISTS `Or6An1Za7Io4Ev3Nt5_cjfm_custom_forms` (
		`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
		`form_name` varchar(200) NOT NULL DEFAULT '', 
		`default_user_role` varchar(200) NOT NULL DEFAULT '', 
		`can_remove` INT(1) NOT NULL DEFAULT 0, 
		PRIMARY KEY (`id`), 
		UNIQUE KEY `form_name` (`form_name`)
	) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Open in new window


Please screen shots of the queries that I ran from within phpMyAdmin.

BTW, here's a good article about how to support full Unicode in MySQL databases
https://mathiasbynens.be/notes/mysql-utf8mb4

I suspect that root cause of your problem is the collation specified in the plugin versus what your database is actually using.
2015-05-17-0117-GlobalsChanged.png
2015-05-17-0123-GlobalsNotChanged.png
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
badwolfffAuthor Commented:
Thanks. I got it working based on one of your suggestions. Everything you suggests works but I found a simpler solution too thanks to you. In the wp-config.php of the wordpress installation just comment these two out:

/** Database Charset to use in creating database tables. */
//define('DB_CHARSET', 'utf8');

/** The Database Collate type. Don't change this if in doubt. */
//define('DB_COLLATE', '');

and it works!

:D
0
LajuanTaylorCommented:
That's great to hear. I like the WP fix as well!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
WordPress

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.