mysql5 case sensitivity, starting server etc etc

[Michael Thon]

On Dec 5, 2007, at 11:35 AM, Ryan Schmidt wrote:

> On Dec 5, 2007, at 03:28, Michael Thon wrote:
>
>> I am having a problem with mysql tables that are created via php  
>> occasionally appearing in mysql with lower case names.  I now have  
>> to solve two problems:
>>
>> 1) how do I restart the sever via the command line?  At the moment  
>> it starts at boot via the launchctl file.  If I issue:
>>
>>    sudo launchctl unload /Library/LaunchDaemons/ 
>> org.macports.mysql5.plist
>>
>> and then the load it again with
>>    sudo launchctl load /Library/LaunchDaemons/ 
>> org.macports.mysql5.plist
>>
>> and then try to connect to the server with the mysql5 utility, I  
>> get the error:
>> ERROR 2002 (HY000): Can't connect to local MySQL server through  
>> socket.....<snip>
>> I think, if I reboot the machine (which I can't do right now) it'll  
>> start up the right way.  but how do I start it the right way from  
>> the command line?
>
> That is the correct way to stop and start the MySQL server. But  
> after using "launchctl unload" (or stopping the server via any other  
> means), be sure that all mysqld processes have exited before you try  
> to start mysqld again using "launchctl load" (or any other means).
>
That did it.  thanks.

>
>> 2) To fix the case sensitivity problem,  I might need to set the  
>> variable lower_case_table_names=0 in my.cnf.  which I will create  
>> in /opt/local/etc/mysql5 as soon as I figure out how to restart the  
>> server... unless anyone has another suggestion.
>
> The correct value for Mac OS X MySQL servers using the default HFS+  
> filesystem is lower_case_table_names=2. MySQL sets  
> lower_case_table_names to 2 by default on Mac OS X. What this means  
> is explained here:
>
> http://dev.mysql.com/doc/refman/5.0/en/identifier-case- 
> sensitivity.html
>
> My personal recommendation is that the letters in your table and  
> database names should be limited to lowercase (not any uppercase)  
> characters, so as to avoid any portability problems.
>
After a little more research, it seems that lower case letters are  
more or less a convention among mysql programmers so that is what I  
should use too.  this does seem to be a bug in mysql though, since  
some letters in table names are converted to lower case, while others  
are not.  I don't have time to work out a good test case so we'll just  
change to lower case and act like nothing happened.
cheers
Mike