Bug #69046 MySQL 5.7.1 ini file processing not OK / Can not run actual 5.7.1 milestone
Submitted: 24 Apr 2013 7:08 Modified: 3 May 2013 8:14
Reporter: Louis at oracle Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Installing Severity:S2 (Serious)
Version:5.7.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: my.ini

[24 Apr 2013 7:08] Louis at oracle
Description:
Hello,

Since I have severe problems with 5.6.11 (connection related problems), I decided to install 5.7 next to my 5.6 installation.

I do net even manage to start it. Main raison for that is probably that the ini file processing is not working yet

Below the problems I noticed before I stopped the exercise because the server did not understood it should use a notn standard port.

Louis  

How to repeat:
Below a grep on the changes I made in the ini file and behind that the error messages

00057: #20130424 port 3307 statement not processed still thinking need to use occupied port 3306 which is mysql56 
00208: #20130424 innodb_additional_mem_pool_size=32M ==> mysqld: unknown variable 'innodb_additional_mem_pool_size=32M'
00219: #20130424 innodb_flush_log_at_trx_commit=2; C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld: unknown variable 'innodb_flush_log_at_trx_commit=2'
00226: #20130424 innodb_log_buffer_size = 16M C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld: unknown variable 'innodb_log_buffer_size=16M'
00246: #20130424 innodb_log_file_size=350M C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld: unknown variable 'innodb_log_file_size=350M'
00251: #20130424 innodb_thread_concurrency = 8 C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld: unknown variable 'innodb_thread_concurrency=8'
00256: #20130424 innodb_file_per_table C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld: unknown option '--innodb_file_per_table'
00266: #20130424 innodb_log_files_in_group = 3 C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld: unknown variable 'innodb_log_files_in_group=3'
00290: #20130424 innodb_write_io_threads = 4 C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld: unknown variable 'innodb_write_io_threads=4'
00293: #20130424 innodb_read_io_threads = 4 C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld: unknown variable 'innodb_read_io_threads=4'
00295: #20130424 innodb_file_format = Barracuda C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld: unknown variable 'innodb_file_format=Barracuda'
00297: #20130421 not supported innodb_checksum_algorithm=strict_crc32
00322: #20130424 innodb_read_ahead_threshold=64 C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld: unknown variable 'innodb_read_ahead_threshold=64'
00325: #20130424 innodb_flush_neighbors = OFF C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld: unknown variable 'innodb_flush_neighbors=OFF'
[24 Apr 2013 7:10] Louis at oracle
Some times I make a typo or other mistake. The title should be MySQL 5.7.1 ini file processing not OK not 5.6. 

The bug reporting tool should allow me to correct that
[24 Apr 2013 18:54] Louis at oracle
More not OK in the 5.7.m11 

After reboot of my computer for some reason I did not get the port 3306 error any more, but I do get other errors

2013-04-24 20:48:45 2104 [Note] Plugin 'FEDERATED' is disabled.
2013-04-24 20:48:45 2104 [ERROR] Plugin 'InnoDB' init function returned error.
2013-04-24 20:48:45 2104 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2013-04-24 20:48:45 2104 [ERROR] Unknown/unsupported storage engine: INNODB
2013-04-24 20:48:45 2104 [ERROR] Aborting

so seeing this I changed the ini file
adding plugin-load=authentication_windows.dll

not a succes as well :)

2013-04-24 20:45:22 6744 [Note] Plugin 'FEDERATED' is disabled.
2013-04-24 20:45:22 6744 [ERROR] Can't open shared library 'C:\Program Files\MySQL\MySQL Server 5.7\lib\plugin\authentication_windows.dll' (errno: 126 Kan opgegeven module niet vinden.)
2013-04-24 20:45:22 6744 [ERROR] Plugin 'InnoDB' init function returned error.
2013-04-24 20:45:22 6744 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2013-04-24 20:45:22 6744 [ERROR] Unknown/unsupported storage engine: INNODB
2013-04-24 20:45:22 6744 [ERROR] Aborting

Louis
[29 Apr 2013 18:17] Erlend Dahl
Can you please upload a copy of your my.ini file? Then I will have somebody look into this problem.
[30 Apr 2013 10:39] Erlend Dahl
The underlying problem here is that InnoDB doesn't start - that is probably why it complains about the missing variables.

We suspect that it refuses to start because the data files are corrupt somehow (were they just copied over from the 5.6 installation, perhaps?).

Can you please upload the error logs?
[30 Apr 2013 10:40] Erlend Dahl
Btw, the reason it deson't find the Windows Authentication plugin is that this plugin is a commercial feature which is not present in the 5.7 community bundle.
[30 Apr 2013 15:09] Louis at oracle
Erlend, 

Given your reaction, I reinstalled the data files as delivered withing the mysql-5.7.1-m11-winx64.zip. Just to make sure I did not make a mistake.

For Info, in the past I Always used the <Mysql-version_64>.msi to install MySQL. But regrettable at this moment it is either the zip or the messy install file. So I used the zip for the install.

Some thing like:
1) download zip
2) unzip to "C:\Program Files\MySQL\MySQL Server 5.7\"
3) copy C:\Program Files\MySQL\MySQL Server 5.7\data to <your data dir>
4) purge (non matching) logfiles from <your data dir>
5) cd "C:\Program Files\MySQL\MySQL Server 5.7\bin"
6) mysqld --install MySQL57 --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini"
7) start the service
8) set root password

So due to your comment:
- I restored my original my.ini (as it should be)
- I repeated steps above related to unzip and zip included data dir to <your / my data dir>

Exectly the same Original problem.

- There is an error during the first start attemp 
Unknown suffix 'O' used for variable 'innodb-flush-neighbors' (value 'OFF')
Before the second start attemp I removed 'innodb-flush-neighbors' 
But this is another error to be corrected

I will send the error file as next comment

Louis
[30 Apr 2013 15:10] Louis at oracle
Error file Two start attemps first attemp including 'innodb-flush-neighbors'

Attachment: MAIN.err (text/plain), 7.05 KiB.

[30 Apr 2013 15:35] Louis at oracle
Hello,

I just looked into the slow log as well. 

Something not correct there.

Dispite that the ini file very explicitly state port 3307 should be used,
The slow log is talking about port 3306

See below.

Louis

** my.ini **
# CLIENT SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]
no-beep

# pipe
# socket=mysql
#20130423 port=3306 in gebruik door 56 ==> port 3307
port=3307

[mysql]

default-character-set=utf8

** slow.log **
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld, Version: 5.7.1-m11-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld, Version: 5.7.1-m11-log (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: (null)
Time                 Id Command    Argument
[30 Apr 2013 23:17] Louis at oracle
Hello,

I discovered three problems, which together caused the problem:
1) innodb_checksum_algorithm=strict_crc32
==> provided tables are not crc32 which cause a problem (see other error report)
==> work arround innodb_checksum_algorithm=crc32
2) innodb_temp_data_file_path as described in 5.7 manual not yet working
==> by the way I disagree with the default path which should IMHO be the database path
3)  innodb_flush_neighbors = OFF not yet working 
==> I tried that because the DB is located on a SSD

So removing those settings solved the problem. 

Sincerely,

Louis
PS. I also had some minor inconsistencys (double entrys) in the my.ini I send you, but those did not cause the problem.
[2 May 2013 7:26] Erlend Dahl
Good to hear that you managed to work around your problem.

I see for 1) Bug#69020 CRC32 not handled correctly + MySQL tables not CRC32 compliant.

About --innodb_temp_data_file_path, the default is actually to put the files in datadir parallel with the ibdata* files. Hence the release notes are wrong. 

I have filed bug#69123 for this.

Shall I then close this bug report?
[2 May 2013 19:18] Louis at oracle
Hello Erdend,

Thanks for the effort and the replay.

Related to closing the bug, is there is a solution for the third point I mentioned: errors related to innodb_flush_neighbors = OFF ?

Sincerely,

Louis
[3 May 2013 5:49] Erlend Dahl
Regarding innodb_flush_neighbors, it turns out this is another doc bug.

According to 

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_neighbor...

the variable is boolean, but in reality it can take three values:

0 - don't flush neighbors from buffer pool
1 - flush contiguous neighbors from buffer pool
2 - flush neighbors from buffer pool

I have filed bug#69129 for this.

I guess this clears out the remaining issue we have?
[3 May 2013 6:41] Louis at oracle
Erlend,

You can close the bug now, no problem but take care that there is a clear description of the setting, not just what the setting means, but what the impact is.

My actual feeling:

the variable is boolean, but in reality it can take three values:

My Feeling(!): 

Prefered setting for SSD (Seek time is no issue ant it spread out the write operations):
0 - don't flush neighbors from buffer pool

HDD related settings ( flushing such neighbor pages in one operation reduces I/O overhead (primarily for disk seek operations) 

In case of HDD'config type "X" then best option is because ......
1 - flush contiguous neighbors from buffer pool

In case of HDD'config type "Y" then best option is because ......
2 - flush neighbors from buffer pool

Louis
[3 May 2013 6:46] Louis at oracle
Oeps!

Too fast! Just tried, but it is not only documentation!

with innodb_flush_neighbors = 0 added to the ini file the server does not start!

Louis
[3 May 2013 6:53] Erlend Dahl
It works for me. Any messages in the error log?
[3 May 2013 7:01] Louis at oracle
Sorry,

My Fault, the DB partition was not mounted, so logical the server did not start :)

Louis
[3 May 2013 8:14] Erlend Dahl
OK, closing this bug report then. Thanks for your patience in trying out MySQL Server 5.7.1 :)