# Data no longer accessible after upgrade from MySQL 5.6 to 5.7



## FKEinternet (Aug 13, 2016)

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 
	
	



```
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 
	
	



```
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.00 sec)
```
 I also tried 
	
	



```
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 (Aug 13, 2016)

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 (Aug 13, 2016)

when in doubt, look at UPGRADING file first!


```
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 (Aug 13, 2016)

Also, you can try `pkg updating` command

I always forget about that one.


----------



## FKEinternet (Aug 14, 2016)

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.



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 (Aug 14, 2016)

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


----------



## FKEinternet (Aug 14, 2016)

marino@ said:


> when in doubt, look at UPGRADING file first!
> 
> 
> ```
> ...



No, that's not the answer - if you had fully read my post, it says


FKEinternet said:


> I have
> 
> 
> 
> ...


 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_.


----------



## FKEinternet (Aug 14, 2016)

marino@ said:


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



Wrong.


----------



## FKEinternet (Aug 14, 2016)

After my original posting, I found this comment 





> If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.


 on https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_lower_case_table_names  Apparently someone decided that "you should set this variable to 1" means "this variable *must* be 1" and removed the capability of changing it.


----------

