Why am I unable to turn innodb_large_prefix ON successfully? Every time I reboot mySql on my Ubuntu VPS, it resets to OFF!
I currently have this installed: Server version: 5.5.43-0ubuntu0.14.04.1-lo
I first did this:
sudo nano /etc/mysql/my.cnf
Then I added to the end of the file:
The I issue a server restart: sudo service mysql restart
Then I run: mysql -u root -p
and at the mysql prompt: show variables like 'innodb%';
I see:
So since it is OFF I try to turn it ON manually:
Now:
But, after I restart the mysql and run this last command again:
I really can't figure this out. Why are the values resetting themselves?
Please assist as this is driving me nuts!
thanks in advance
I first did this:
sudo nano /etc/mysql/my.cnf
Then I added to the end of the file:
default-engine=InnoDB
innodb-file-format=barracuda
innodb-file-per-table=ON
innodb-large-prefix=ON
collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4The I issue a server restart: sudo service mysql restart
Then I run: mysql -u root -p
and at the mysql prompt: show variables like 'innodb%';
I see:
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)So since it is OFF I try to turn it ON manually:
mysql> set global innodb_large_prefix = ON;
Query OK, 0 rows affected (0.00 sec)
mysql> set global innodb_file_per_table = ON;
Query OK, 0 rows affected (0.00 sec)
mysql> set global innodb_file_format = Barracuda;
Query OK, 0 rows affected (0.00 sec)Now:
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 | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
| 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 | ON |
| 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)But, after I restart the mysql and run this last command again:
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)I really can't figure this out. Why are the values resetting themselves?
Please assist as this is driving me nuts!
thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So I was editing the wrong file?
I need to edit the /etc/my.cnf file?
I need to edit the /etc/my.cnf file?
If it exists then yes! :)
ASKER
Thanks, I will try it on Monday when I get back in office and let you know how it goes.
Did you end up finding out if you were editing the wrong config file?
ASKER
Will tell you today as I will have access to that VPS later this afternoon :D
I had not forgotten you
I had not forgotten you
*grin* just checkin in
ASKER
Sorry for the wait. The damn box was in use by a colleague and could not get my paws on it till now!
So I tried to edit the first one: /etc/my.cnf
The file is empty. Does not exist.
Then I went for the /etc/mysql/my.cnf
At the end of this file was the code I put in last time:
The code was already there :(
So back to square one?
By the way I tried to find the /usr/etc/my.cnf but that does not exist either. And neither does ~/.my.cnf
What a mystery!
P.S. Just a thought... did any of the stuff I am trying to do get deprecated in some version of mySql and needs to be done in another way nowadays?
I currently have this installed: Server version: 5.5.43-0ubuntu0.14.04.1-lo
So I tried to edit the first one: /etc/my.cnf
The file is empty. Does not exist.
Then I went for the /etc/mysql/my.cnf
At the end of this file was the code I put in last time:
default-engine=InnoDB
innodb-file-format=barracuda
innodb-file-per-table=ON
innodb-large-prefix=ON
collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4The code was already there :(
So back to square one?
By the way I tried to find the /usr/etc/my.cnf but that does not exist either. And neither does ~/.my.cnf
What a mystery!
P.S. Just a thought... did any of the stuff I am trying to do get deprecated in some version of mySql and needs to be done in another way nowadays?
I currently have this installed: Server version: 5.5.43-0ubuntu0.14.04.1-lo
Oh well - needed to rule it out. Those are just the places mySQL looks for with it's config files, they don't need to exist.
Try changing another setting in your my.cnf and see if that changes so we can confirm that mysql IS reading that file? If it doesn't have the right permissions it may be loading default settings.
Try changing another setting in your my.cnf and see if that changes so we can confirm that mysql IS reading that file? If it doesn't have the right permissions it may be loading default settings.
ASKER
Will do that test today and let you know. Any suggestion on which setting I should change?
Since this is a deployment server, it has to be a harmless setting. Any suggestions would be appreciated.
By the way I just had a thought. Could I not create a cronjob that manually activates the large prefix at server restart and at mysql restart?
thanks
Since this is a deployment server, it has to be a harmless setting. Any suggestions would be appreciated.
By the way I just had a thought. Could I not create a cronjob that manually activates the large prefix at server restart and at mysql restart?
thanks
This shouldn't matter: https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_connections. It's only a matter of changing it and confirming that the database is using this config file.
By all means create a cron job, not pretty but it should work. It would be good to know what's actually going on as it could be surpressing another issue
By all means create a cron job, not pretty but it should work. It would be good to know what's actually going on as it could be surpressing another issue
ASKER
I will test it tonight as I will have the box to myself again
thanks
thanks
ASKER
Some stupid issue this was. I uninstalled and reinstalled mySql and now it all works based on your idea (on how to find which cnf file gets loaded in the correct order). So thanks... still never got to the bottom of it but what matters most is that it works now!
:)
:)
ASKER
Open in new window