Data no longer accessible after upgrade from MySQL 5.6 to 5.7

FKEinternet

Member

Reaction score: 5
Messages: 65

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?
 

marino

Daemon

Reaction score: 447
Messages: 1,091

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.
 

marino

Daemon

Reaction score: 447
Messages: 1,091

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

Daemon

Reaction score: 447
Messages: 1,091

Also, you can try pkg updating command

I always forget about that one.
 
OP
FKEinternet

FKEinternet

Member

Reaction score: 5
Messages: 65

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.

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

Daemon

Reaction score: 447
Messages: 1,091

Please re-read all the posts. Your problem has been solved.
 
OP
FKEinternet

FKEinternet

Member

Reaction score: 5
Messages: 65

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.

No, that's not the answer - if you had fully read my post, it says
I have
Code:
lower_case_table_names = 0
in the current and active my.cnf,
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.
 
OP
FKEinternet

FKEinternet

Member

Reaction score: 5
Messages: 65

Top