# Just upgraded Mysql from 5.6 to 5.7



## tony33 (Jul 29, 2016)

I just upgraded the 5.6 version to the latest 5.7 version.  Everything worked before doing this. Now, when I try to run mysql-server by service mysql-server start. It  shows  starting mysql. It hangs there for about maybe 1 min or two. Then it takes me back to the console. I then run service mysql-server status and it says mysql not running. 

I look into the error logs and find this:


```
2016-07-29T08:38:41.926090Z 0 [ERROR] InnoDB: The Auto-extending innodb_system data file '/var/db/mysql/ibdata1' is of a different size 1152 pages (rounded down to MB) than specified in the .cnf file: initial 8192 pages, max 0 (relevant if non-zero) pages!
2016-07-29T08:38:41.926141Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2016-07-29T08:38:42.135842Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2016-07-29T08:38:42.135865Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2016-07-29T08:38:42.135875Z 0 [ERROR] Failed to initialize plugins.
2016-07-29T08:38:42.135883Z 0 [ERROR] Aborting
```

That is the only error I get. How do I fix this error?

I done a google search and found people had similar issues with MySQL 5.6. I seen them say to make changes to the my.cnf file. It said to put 10M  but the location I looked at mine and it was already there but it had 128M instead of 10M. I changed it to 10M and still got the same issue.

I would like to know what I need to do to fix this.


----------



## Murph (Jul 29, 2016)

This is just from the info above, without actually researching the issue.  I abandoned/deprecated MySQL long ago (can't rely on the future of any open source where Oracle is involved in it), so no current experience on it.  The default page size on your system is probably 4KB (verify with `sysctl hw.pagesize`).  1152 pages is a 4608KB.  Try 4MB.


----------



## tony33 (Jul 29, 2016)

Murph said:


> This is just from the info above, without actually researching the issue.  I abandoned/deprecated MySQL long ago (can't rely on the future of any open source where Oracle is involved in it), so no current experience on it.  The default page size on your system is probably 4KB (verify with `sysctl hw.pagesize`).  1152 pages is a 4608KB.  Try 4MB.



That command prints out :
4096    
 So, how do I increase the size? Since, this is set at default?


----------



## Murph (Jul 29, 2016)

tony33 said:


> That command prints out :
> 4096
> So, how do I increase the size? Since, this is set at default?



You misunderstood me.  I just mentioned the system page size to show the working for interpreting "1152 pages" in your log output, not as something that should be changed.  Try telling MySQL to use 4MB for that data file (with auto extend still enabled).


----------



## tony33 (Jul 29, 2016)

tony33 said:


> That command prints out :
> 4096





Murph said:


> You misunderstood me.  I just mentioned the system page size to show the working for interpreting "1152 pages" in your log output, not as something that should be changed.  Try telling MySQL to use 4MB for that data file (with auto extend still enabled).



I understood you. I was asking how to make the changes. The problem is that there's a page difference and need to make changes so it can match.  I don't know how to do this but found this person that had the same issue:

http://ximalas.info/2016/05/18/mysq...-data-file-vardbmysqlibdata1-being-too-small/

He says to set it to this: `innodb_data_file_path = ibdata1:10M:autoextend`

I tried that and it didn't work and in my config file. It was  128M instead of 10M and that line was already there.

Should I change 128 to 140?


----------



## Murph (Jul 29, 2016)

tony33 said:


> He says to set it to this: `innodb_data_file_path = ibdata1:10M:autoextend`



Ok, so try `innodb_data_file_path = ibdata1:4M:autoextend`.


----------



## tony33 (Jul 29, 2016)

Murph said:


> Ok, so try `innodb_data_file_path = ibdata1:4M:autoextend`.


I tried 4 and even 140. still get the same error.


----------



## Murph (Jul 29, 2016)

tony33 said:


> I tried 4 and even 140. still get the same error.



Well, if the error message is exactly the same (i.e. it says "… initial 8192 pages …" every time), you are not editing the config file that MySQL is actually using.  Where dd the number 140 come from?


----------



## tony33 (Jul 29, 2016)

Murph said:


> Well, if the error message is exactly the same (i.e. it says "… initial 8192 pages …" every time), you are not editing the config file that MySQL is actually using.  Where dd the number 140 come from?



I will have to check to see if mysql changed the config file. I modified the config file I used before the upgrade.  The 140  came from 8192 pages  x 16KB  = KB convert to MB. Does anyone know the command to make mysql spit out the config file location?


----------



## Murph (Jul 29, 2016)

tony33 said:


> I will have to check to see if mysql changed the config file. I modified the config file I used before the upgrade.  The 140  came from 8192 pages  x 16KB  = KB convert to MB.



Ahhh, ok.  Something is off with your maths, however, as 8192 * 16KB pages is 128MB.  The other problem is that 8192 is not your target number.  1152 pages is the size it wants to be, 8192 is reporting the size configured in the config file.  So, if MySQL is actually using 16KB pages, 1152 pages is 18MB.


----------



## tony33 (Jul 29, 2016)

Murph said:


> Ahhh, ok.  Something is off with your maths, however, as 8192 * 16KB pages is 128MB.  The other problem is that 8192 is not your target number.  1152 pages is the size it wants to be, 8192 is reporting the size configured in the config file.  So, if MySQL is actually using 16KB pages, 1152 pages is 18MB.



So, you want me to give it a try? use 18 instead?


----------



## Murph (Jul 29, 2016)

tony33 said:


> So, you want me to give it a try? use 18 instead?



Well, first you need to find the correct config file, the one which causes "… initial 8192 pages …" to change.  It's been quite a while since I last used InnoDB, but I believe that number in the config file is the initial/minimum size, so any number less than or equal to the current size should work (with auto extend enabled).  The way the error message is phrased says to me that it is looking for a number less than or equal to the current size.


----------



## Murph (Jul 29, 2016)

Ahh, I checked the databases/mysql57-server port on a whim.  Did you read the pkg-message when you installed it (it should have been automatically shown to you)?


```
*****************************************************************************

Remember to run mysql_upgrade the first time you start the MySQL server
after an upgrade from an earlier version.

Initial password for first time use of MySQL is saved in $HOME/.mysql_secret
ie. when you want to use "mysql -u root -p" first you should see password
in /root/.mysql_secret

*****************************************************************************

*****************************************************************************

The default location for my.cnf has changed from "/var/db/mysql/my.cnf" to
"/usr/local/etc/mysql/my.cnf". Existing my.cnf files must be merged manually
with the new default and moved to the new location. To continue using the
my.cnf file at the old location, set "mysql_optfile" in /etc/rc.conf to
point to the location of the existing my.cnf file.

*****************************************************************************
```

The top section about `mysql_upgrade` is not relevant to your issues here.  The change to the config file location very likely is part of the problem here.


----------



## tony33 (Jul 29, 2016)

Murph said:


> Ahh, I checked the databases/mysql57-server port on a whim.  Did you read the pkg-message when you installed it (it should have been automatically shown to you)?
> 
> 
> ```
> ...



Yes, but that's old. That message is also shown in the mysql 5.6 version. That's the location where I made the changes. I have the config file in that location.  However, ,I found in the /usr/db/mysql/auto.cnf  That file was created during the install. I use webmin and it had by default set the config file to that location. That was after the upgrade. I had to change it to the current one at /usr/local/etc/mysql/my.cnf I made the changes via webmin and not sure if those changes were actually made or not.

Is their a way I can just go to that other file and write a line to include the old orignal file?

I ran this: `mysql --help`

It spat out this:

```
Default options are read from the following files in the given order:
/usr/local/etc/my.cnf /usr/local/etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysql client
```

It's a long list of commands, options and variables set. The above is the only place talking about the config file.


Does /tmp/mysql.sock have to exist before starting the server? If so I don't see it there anymore.


----------



## tony33 (Jul 29, 2016)

i comment the line out and now mysql runs. However, just checked my database and it shows all my datatables. However, at least 4 or 5 have missing data inside the tables. So, I can confirm that config file is what the program is using. It's just I need to find out how to fix the problem. If there's a way I can run mysql commands and fix this issue.  It looks like the page is too small. So, it cuts off the data that's stored in it. That's why I am not seeing any data in some of my tables.


----------



## ab2k (Jul 29, 2016)

Hi, location of config file in databases/mysql57-server have been changed to /usr/local/etc/mysql/my.cnf - you have to do a changes here. Also please note that many run options are deprecated with that release and they have been added a "tmp" file directive too. Please read about all changes - it's a massive upgrade. Did you made backup for all your databases ? If yes - just rename directory with a databses (probably it's - /var/db/mysql) to smth else, create it with a mysql:mysql owner and start database service - it will recreate everything. After it you will have to setup it as new instance and add your databases at end.. Well, I had smth like this, but I had backups before I upgraded it - so main upgrade process wasn't be a problem. Just spended few hours reading changes and making a new config file.

Small update: whoopps... you have already got solution while i was typing. well gratz on it! and again - check the changes - they actually rock.


----------



## tony33 (Jul 29, 2016)

ab2k said:


> Hi, location of config file in databases/mysql57-server have been changed to /usr/local/etc/mysql/my.cnf - you have to do a changes here. Also please note that many run options are deprecated with that release and they have been added a "tmp" file directive too. Please read about all changes - it's a massive upgrade. Did you made backup for all your databases ? If yes - just rename directory with a databses (probably it's - /var/db/mysql) to smth else, create it with a mysql:mysql owner and start database service - it will recreate everything. After it you will have to setup it as new instance and add your databases at end.. Well, I had smth like this, but I had backups before I upgraded it - so main upgrade process wasn't be a problem. Just spended few hours reading changes and making a new config file.
> 
> Small update: whoopps... you have already got solution while i was typing. well gratz on it! and again - check the changes - they actually rock.



Yes, I have it in the file. They made the change with msyql 5.6. It's already in that spot when I upgraded to mysql 5.6.  That isn't the issue since I  just confirmed mysql is using that config file. I comment out that line and the program runs. However, there's missing data in the tables and I think it's due to this error / issue I been having.  There's nothing more about the upgrade that's needed.

Read here: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-13.html#mysqld-5-7-13-configuration


----------



## ab2k (Jul 29, 2016)

Hmm, thats strange it's just a hot-start feature.. but maybe smth else changed... You mean this - innodb-temp-data-file-path ?


----------



## tony33 (Jul 29, 2016)

ab2k said:


> Hmm, thats strange it's just a hot-start feature.. but maybe smth else changed... You mean this - innodb-temp-data-file-path ?


No, I commented out `innodb_data_file_path =` one. I have the one you speak off still uncommented. Should I comment that one out too?  Right now I can run mysql. However, when i go to check my datatables. Their all there but the content in some large tables are not there at all. The tables that don't have that much data are still there.

I am going to comment that one you speak of too and see if it fixes the problem.
I did that and still have the same issue.


----------



## ab2k (Jul 29, 2016)

Don't think it will fix your problem.. it just a hot start feature for tmp tables. You have commented database config line - it worked for me too, but after that i have got it working i deleted everyhting and started from scratch just to be absolutelly sure that everyhting will work as it was before upgrade.

small update: check collation and previous and current locale settings. probably you have misconfiguration here...


----------



## tony33 (Jul 30, 2016)

ab2k said:


> Don't think it will fix your problem.. it just a hot start feature for tmp tables. You have commented database config line - it worked for me too, but after that i have got it working i deleted everyhting and started from scratch just to be absolutelly sure that everyhting will work as it was before upgrade.
> 
> small update: check collation and previous and current locale settings. probably you have misconfiguration here...



I checked the error logs and found this:

took info down due to privacy concerns.


----------



## ab2k (Jul 30, 2016)

Now you see why i did it from scratch.

Small update - you always can try to run `/usr/local/bin/mysql_upgrade` command to try to resolve all problems with databases after major upgrade. I never using it. Just prefer to remove everything and place databases from backups to new place. It minimizes my deploying time.. After it i just fix mini-bugs, nothing more, and keeping my eye on log files.


----------



## tony33 (Aug 3, 2016)

ab2k said:


> Now you see why i did it from scratch.
> 
> Small update - you always can try to run `/usr/local/bin/mysql_upgrade` command to try to resolve all problems with databases after major upgrade. I never using it. Just prefer to remove everything and place databases from backups to new place. It minimizes my deploying time.. After it i just fix mini-bugs, nothing more, and keeping my eye on log files.



I tried that but it fails at the mysql_check  and notice it's using mysql_check instead of mysqlcheck. So, the error says no such command. How would I fix that?

I then ran mysqlcheck with --all-databases. the first database it said no such database error and then exists with that error.  When I look at where the databases are located. I see that they exist.

The mysqlcheck spits out a error 1049 unknown database.
I didn't backup the database via mysql. I ran freebsd dump tool to do a partition backup.  I'm wondering if I can restore the files for the databases needed via dump?


----------



## tony33 (Aug 4, 2016)

Just an update. Got the database back up and running. I did have to restore backups with mysql. I use the dump file to do the restore.


----------



## stefanlasiewski (Aug 31, 2016)

I ran into this error message as well with MySQL 5.7, and I thought I'd share my solution. We've been running MySQL 5.7 for a few months, but after a `pkg update`, MySQL failed to start on one of my servers.

In our case, it seems that mysql-server was ignoring the value of `mysql_optfile`.

It's here,


```
# grep mysql /etc/rc.conf
mysql_enable="YES"
mysql_dbdir="/data/mysql"
mysql_optfile="/usr/local/etc/my.cnf"
```

But my.cnf wasn't being read, and I got scary errors like this:


```
2016-08-30T23:25:22.779093Z 0 [ERROR] InnoDB: The Auto-extending innodb_system data file '/var/db/mysql/ibdata1' is of a different size 1152 pages (rounded down to MB) than specified in the .cnf file: initial 8192 pages, max 0 (relevant if non-zero) pages!
```

It took me a bit to notice the MySQL was trying to use files in `/var/db/mysql`, when we specify `/data/mysql` as the dbdir, and the ibdata* files should use that as their root directory.

I confirmed this further by adding intentional gibberish to `/usr/local/etc/mysql/my.cnf`. MySQL would fail due to errors in this file, when */usr/local/etc/*my.cnf itself worked fine.

On a second server, MySQL used the default setting of `bind-address  = 127.0.0.1` from /usr/local/etc/mysql/my.cnf, instead of our local setting. This also points to MySQL ignoring `mysql_optfile`.

In my case, I simply moved /usr/local/etc/my.cnf to /usr/local/etc/mysql/my.cnf and MySQL started up without any issues.

Frustrating, but in the end, I found a simple workaround.

-= Stefan


----------

