Data no longer accessible after upgrade from MySQL 5.6 to 5.7

FKEinternet

FKEinternet

I upgraded my server from MySQL 5.6 to 5.7 via pkg install mysql57-server. After getting the changed configuration issues sorted out so I could run mysql_upgrade -u root -p successfully, it reported that practically all of my existing tables don't exist - because they are stored on disk with StudlyCaps file names.

I have
Code: 
lower_case_table_names = 0
in the current and active my.cnf, but when I run show variables like 'lower_case_table_names'; in the mysql interpreter I get
Code: 
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.00 sec)
I also tried
Code: 
root@localhost [none]> set lower_case_table_names=0;
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable
Is there a way to fix this short of reverting to MySQL 5.6? Does the binary installed by the pkg command force lower_case_table_names to be set to 1? If so, what do I have to modify in the configuration so I can build 5.7 from the ports and not have it forced to 1?
 
in the 3-second google search I did, it was indicated that lower_case_table_names is configurable. Logically, if setting it in the my.cnf doesn't work as it should, then mysql isn't looking at my.cnf.

Actually, come to think of it, the default location of my.cnf changed. Maybe set it in rc.conf or physically move it to the (new) expected location. If I figure out where I'll post back.
 
when in doubt, look at UPGRADING file first!

Code: 
20160704:
  AFFECTS: users of databases/mysql57-*
  AUTHOR: riggs@FreeBSD.org

  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.

There's your answer.
 
marino@ said:
in the 3-second google search I did, it was indicated that lower_case_table_names is configurable. Logically, if setting it in the my.cnf doesn't work as it should, then mysql isn't looking at my.cnf.
Click to expand...

Yes, the documentation, etc. says lower_case_table_names is configurable - the issue I'm having is that even though I updated the correct my.cnf, MySQL is ignoring the setting.
 
marino@ said:
when in doubt, look at UPGRADING file first!

Code: 
20160704:
  AFFECTS: users of databases/mysql57-*
  AUTHOR: riggs@FreeBSD.org

  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.
There's your answer.
Click to expand...

No, that's not the answer - if you had fully read my post, it says
FKEinternet said:
I have
Code: 
lower_case_table_names = 0
in the current and active my.cnf,
Click to expand...
i.e., in /usr/local/etc/mysql/my.cnf. I did RTFM - the reason I posted my question is MySQL is not behaving as described in the manual.
 
You must log in or register to reply here.
Back
Top