Failed upgrade mysql 5.6.34 to 5.6.35 (portupgrade)

[formaltux]

Hi,

Yesterday morning around 8 am (GMT+1), I performed what should of been standard MySQL upgrade from MySQL 5.6.34 to MySQL 5.6.35 using portupgrade(8), but ended up losing InnoDB tables completely and spending whole day getting them restored. I ran mysql_upgrade after database restart and noticed that the upgrade reported the InnoDB tables as missing.

I somehow ended up with two ibdata1 files, one in /var/db/mysql and another in /usr/local/var/db/mysql/. In /usr/local/etc/mysql/my.cnf was pointing to /var/db/mysql for the InnoDB data and log group files). The missing data were contained in the /usr/local/var/db/mysql/ibdata1 one but the MySQL would not start using this data file (changing my.cnf) with the following error:

InnoDB: auto-extending data file /usr/local/var/db/mysql/ibdata1 is of a different size 7296 pages (rounded down to MB) than specified in the .cnf file: initial 8192 pages, max 0 (relevant if non-zero) pages!

So I installed 5.6.35 on windows and was able to start mysql using ibdata1 and managed to dump missing InnoDB data.

I ended up with:

1) imported the missing data.
2) changed entries in my.cnf like so:

   innodb_data_home_dir            = /usr/local/var/db/mysql
   innodb_log_group_home_dir       = /usr/local/var/db/mysql

(pointing to correct and working ibdata1)

3) made symlink:

  /var/db/mysql -> /usr/local/var/db/mysql

Port maintainer confirmed the issue, there should be coming up 5.6.35_2 version so beware.

D.

 

[SirDice]

Note that you only need to run mysql_upgrade when upgrading versions, from 5.5.x to 5.6.x or 5.6.x to 5.7.x for example. You don't need to run it for patch releases. MySQL 5.6.34 to 5.6.35 is an update, not an upgrade.

 

[vejnovic]

Code:
InnoDB: auto-extending data file /usr/local/var/db/mysql/ibdata1 is of a different size 7296 pages (rounded down to MB) than specified in the .cnf file: initial 8192 pages, max 0 (relevant if non-ze

Please keep in mind that the default location for my.cnf will be changed
from "/var/db/mysql/my.cnf" to "/usr/local/etc/mysql/my.cnf" in the near
future.  If you do not want to move your my.cnf to the new location then
you must set "mysql_optfile" in /etc/rc.conf to "/var/db/mysql/my.cnf".

Move your my.cnf into the new location: /usr/local/etc/mysql

 

[CyberCr33p]

The port maintainer fix the issue. Also the default my.cnf location is /var/db/mysql/ if you don't have mysql_optfile in /etc/rc.conf.