Changing System Variables in MySQL

Lots of fun today, dealing with what feels like the innards of MySQL but probably just barely scratches the epidermis. What I did learn today though was how to set MySQL system variables using a configuration file on Mac OSX. I figure that this will come up again and I’ll have to learn it all over again unless I write it down, thus, I’m writing it down while a 22 minute query runs on the other machine.

Today, I needed to increase the innodb_buffer_pool_size value from the default 8MB to something useful for my purposes like 2 GB. You can’t do that from SqlYog or from the command line as it happens to be a readonly variable. So you need to create a configuration file. That file lives in root directory at /etc/my.cnf. I first tried to create this file using vi, got it all typed up and then when I saved it, was told that wasn’t going to fly, you don’t have the requisite permissions. Stupid *nix operating system. Not that I’m complaining but after the day I’ve had, I would have liked to have just created the file.

So back to the command prompt and try sudo vi my.cnf. Lo and behold that works like a champ. The file looked like this when I was done:
[mysqld]
innodb_buffer_pool_size=2G

Saved that, restarted the MySQL server and it had updated correctly as seen using SHOW VARIABLES;

Probably all very elementary stuff but for a guy who prefers not to get his hands dirty with database stuff, good to know for the future. Also learned about profiling which you can enable in a script in SqlYog with a SET profiling=1; at the beginning of your script and a SHOW profiles; at the end.

Mac Computers, Microsoft Keyboards and a Lost 3 Hours

I do my primary development at work on a Mac computer using a Microsoft Natural 4000 ergonomic keyboard. Because I’m predominantly a Microsoft .Net developer, function keys are reasonably integral to my day to day life, not just in the Visual Studio IDE but also in SqlYog, the MySQL client we use. Having to execute queries or debugging steps using menus with the mouse drives me insane. I’m no shortcut master but I know the main ones that I run into in my daily life. So when the function keys stopped working in an expected way, I pretty much had to shut down work and fix it.

For those following along at home, you can get function keys to work on a Mac by going to System Preferences -> Keyboard and then checking the “Use F1, F2, etc as standard function keys.” I had done that early on and everything worked fine. Suddenly, last Friday, those keys started opening up the Start menu in Parallels and Finder on the Mac. Not good. So I started digging around and didn’t really run into much on the interwebs. I then decided to use my extensive Twitter network and luckily, with some long distance debugging, David O’Hara got me to the right solution which was to turn off the F Lock key on the Microsoft keyboard.

Somewhere along the way, I guess that I had managed to hit that key and what that did was disable function keys from the Microsoft keyboard’s perspective. When that happened, the “Use F1, F2, etc. . .” checkbox in System Preferences disappeared because Mac OS thought the keyboard didn’t support it. Even reinstalling the keyboard software didn’t help. Once I hit that key (which is dangerously close to the Backspace key in my unbalanced UI design opinion), all bets were off.

So if you are having trouble with function keys on a Mac, try toggling the F Lock key and quietly curse whoever thought it was a good idea to have a key that changed the behavior of 12 other keys. This is terrible UI design in my opinion, you should have to work to change something like that, i.e. put this in the control panel. There are also ways to disable it should you so choose.

Learning MySql 45 Minutes at a Time

This and all future MySql posts are really for my own record keeping so that when dumb things happen that take me 45 minutes to fix, I have a record of them for future such incidents.

Today, for some reason, I couldn’t log in remotely to the MySql instance running on my Mac. This has been working just fine for the past month so needless to say, I had no idea what happened. As it turned out, the IP address of my Parallels instance had changed, rendering all the entries in my user table pointless. I had to update the user table with the new IP, restart the MySql instance and boom, up and running again.

Login from the terminal first:

mysql -u root -p

Then:

USE mysql;

select Host, User from user;

update user Set Host = 'NEW IP' where Host = 'OLD IP';

select Host, User from user;