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:
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
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;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
ASKER
Yes, thank you. But how do I fix it?
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
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
ASKER
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
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
I just left my office. I'll get back to you shortly.
ASKER
thanks
ASKER
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=barracu
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.
On my server in file my.cnf I added:
innodb_large_prefix=on
innodb_file_format=barracu
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.
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.
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.
ASKER
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)ASKER
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:
please help
thanks
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;please help
thanks
ASKER
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 :(
Then I restart mysql and test it again using your command and I see the large prefix is off again
this is frustrating :(
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?
Did the settings in your my.cnf stay after you changed them?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
/** 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
That's great to hear. I like the WP fix as well!
http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html
.