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-log (Ubuntu) on Ubuntu 14.04 Trusty Tahr.

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=utf8mb4

Open in new window


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:
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


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)

Open in new window


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)

Open in new window


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)

Open in new window


I really can't figure this out. Why are the values resetting themselves?

Please assist as this is driving me nuts!

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.

RobOwner (Aidellio)Commented:
I'm no expert with this but are you sure you're editing the correct config file?

What does this give you?

mysql --help | grep "Default options" -A 1
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

lupocatttivo@octane:~$ mysql --help | grep "Default options" -A 1
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Open in new window

0
badwolfffAuthor Commented:
So I was editing the wrong file?

I need to edit the /etc/my.cnf file?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RobOwner (Aidellio)Commented:
If it exists then yes! :)
0
badwolfffAuthor Commented:
Thanks, I will try it on Monday when I get back in office and let you know how it goes.
0
RobOwner (Aidellio)Commented:
Did you end up finding out if you were editing the wrong config file?
0
badwolfffAuthor Commented:
Will tell you today as I will have access to that VPS later this afternoon :D
I had not forgotten you
0
RobOwner (Aidellio)Commented:
*grin* just checkin in
0
badwolfffAuthor Commented:
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:

default-engine=InnoDB

innodb-file-format=barracuda
innodb-file-per-table=ON
innodb-large-prefix=ON

collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4

Open in new window


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-log (Ubuntu) on Ubuntu 14.04 Trusty Tahr.
0
RobOwner (Aidellio)Commented:
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.
0
badwolfffAuthor Commented:
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
0
RobOwner (Aidellio)Commented:
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
0
badwolfffAuthor Commented:
I will test it tonight as I will have the box to myself again

thanks
0
badwolfffAuthor Commented:
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!

:)
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
MySQL Server

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.