MySQL Cheatsheet By Example

Tech-Evangelist has put together an excellent cheatsheet for MySQL. There’s lots of other MySQL cheatsheets out there, but this one is unique in the fact that it gives examples of usage rather than just snippets of code.

This cheatsheet also includes a summary of commonly used MySQL data types, which will be really helpful to me. I can’t tell you how many times in a week I hit the MySQL website looking for specs on a certain data type.

If you think this cheatsheet would be useful to you, head on over to Tech-Evangelist to download the PDF.

Wrapping Text Inside Pre Tags

I sometimes display little snippets of code on this site. For example, here, here, and here. To do this, I use the Code Markup wordpress plugin.

If you’re using Firefox, you may notice the long lines in my Digg Integrator fix post. It’s not really a problem for me having those really long lines in Firefox. Why? Because Firefox still displays my sidebar correctly. Internet Explorer is a totally different story though. When there’s long lines like that, my sidebar will appear at the very bottom of the page in IE.

The long lines are caused by use of the pre html tag. The pre tag preserves spaces and line breaks in a chunk of text. Perfect for displaying snippets of code. However, some lines of code are quite long and will run off the page. This is exactly why my sidebar was getting pushed to the bottom of the page in IE.

So, I set out looking for a method to wrap text contained within pre tags. Google found exactly what I was looking for. Wrapping text inside pre tags is quite easy, all that’s required is a simple addition to your css:

pre {
 white-space: pre-wrap;       /* css-3 */
 white-space: -moz-pre-wrap;  /* Mozilla, since 1999 */
 white-space: -pre-wrap;      /* Opera 4-6 */
 white-space: -o-pre-wrap;    /* Opera 7 */
 word-wrap: break-word;       /* Internet Explorer 5.5+ */
}

Quite simple. After adding that CSS, the text in pre tags still doesn’t wrap in Firefox, but I don’t care because Firefox displays the rest of my page as it should. Now, when you view a page in IE with a long line, the text is wrapped and my sidebar content appears at the top of the page instead of the bottom.

For consistency sake, let’s make these long lines wrap in Firefox too. This is extremely simple. It only requires adding a few characters to the CSS shown above. For text wrapping in Firefox, use this CSS:

pre {
 white-space: pre-wrap;       /* css-3 */
 white-space: -moz-pre-wrap !important;  /* Mozilla, since 1999 */
 white-space: -pre-wrap;      /* Opera 4-6 */
 white-space: -o-pre-wrap;    /* Opera 7 */
 word-wrap: break-word;       /* Internet Explorer 5.5+ */
}


Notice the only difference between the first and second examples is the addition of “!important” to the third line in example 2. After adding that little bit, your text between your pre tags should wrap well in basically every browser that’s currently in use.

UPDATE 3/10/2007

If you can’t seem to get the css above to work, give the css below a shot. I just set a width on the pre tag. When the width is set to 100%, you’ll get a horizontal scrollbar when viewing in IE7. That’s why I’ve set the width to 99%. The code will display just fine in IE6, IE6, and FireFox when width is set to 99%. I have not tested Opera. Try this updated CSS if you’re having issues with the original CSS from above.

pre {
 white-space: pre-wrap;       /* css-3 */
 white-space: -moz-pre-wrap !important;  /* Mozilla, since 1999 */
 white-space: -pre-wrap;      /* Opera 4-6 */
 white-space: -o-pre-wrap;    /* Opera 7 */
 word-wrap: break-word;       /* Internet Explorer 5.5+ */
 width: 99%;
}

UPDATE 6/4/2008

Markku Laine posted some css in a comment that seems to work better than the original css I posted. It works in IE, Safari, and FireFox. Try using Markku’s css below if the previous examples don’t work for you.

pre {
 overflow-x: auto; /* Use horizontal scroller if needed; for Firefox 2, not needed in Firefox 3 */
 white-space: pre-wrap; /* css-3 */
 white-space: -moz-pre-wrap !important; /* Mozilla, since 1999 */
 white-space: -pre-wrap; /* Opera 4-6 */
 white-space: -o-pre-wrap; /* Opera 7 */
 /* width: 99%; */
 word-wrap: break-word; /* Internet Explorer 5.5+ */
}

Update 1/20/2011

Some code Max posted in a comment also seems to be working well for many people. It may be worth trying as well:

height: 120px;
overflow: auto;
font-family: “Consolas”,monospace;
font-size: 9pt;
text-align:left;
background-color: #FCF7EC;
overflow-x: auto; /* Use horizontal scroller if needed; for Firefox 2, not
white-space: pre-wrap; /* css-3 */
white-space: -moz-pre-wrap !important; /* Mozilla, since 1999 */
word-wrap: break-word; /* Internet Explorer 5.5+ */
margin: 0px 0px 0px 0px;
padding:5px 5px 3px 5px;
white-space : normal; /* crucial for IE 6, maybe 7? */

Digg Integrator Plugin Fix

The Digg Integrator plugin v1.1 for WordPress hasn’t been working correctly. The author, WildBil, has been working on a fix for the last week or so. I got tired of waiting for a fix lastnight and took it upon myself to create one.

In addition to the referring Digg URL not being detected, I think there’s also a problem when submitting a site to Digg that has a “Preferred Digg Topic” set. The preferred topic is never sent along to Digg because the variable containing the preferred topic isn’t being called correctly in diggIntegrator.php.

All of the fixes I mention here are to be made within the wjt_diggThisPost function inside diggIntegrator.php. That function starts on line 225.

Now, moving on. The problem with the referring digg URL not being captured is extremely simple, I think. The function that captures the referring digg URL is simply not being called correctly. Basically, it’s not being run when it needs to. Look at line 278 in diggIntegrator.php:
Continue reading “Digg Integrator Plugin Fix”

The FEDERATED MySQL Storage Engine

The FEDERATED MySQL storage engine is the coolest thing EVER! Seriously. It’s already saved me from having to do a whole bunch of synchronization coding. I can only imagine how it’ll come in useful in the future.

So, here’s my situation. I have two mysql servers sitting behind a firewall at “location 1”. People at “location 2” need to write some software to connect to both mysql servers at location 1. However, MyODBC gets confused when connecting to the same hostname on two different tcp ports, or so I’m told.

Anyway, since I was basically told that there’s no way to connect to two seperate mysql servers behind one firewall, I got to thinking. So, I set off searching google for method for mirroring data in MySQL and came across the FEDERATED storage engine.

Now, the servers at location 1 are on a VPN with the network at location 3, my location. So, my network (at location 3) can see the network at location 1 without the firewall getting in the way. Since that’s the case here, I can connect to the default mysql port, 3306, on both servers because I can see their LAN IP, where the people at location 2 can’t (no VPN).

So, we’ve got the network flow figured out, now we can go about getting the FEDERATED storage engine in MySQL working. First, you’ll need MySQL 5.x. I chose MySQL 5.0.24 as it’s the latest stable 5.x release.

To enable the FEDERATED storage engine in mysql 5, you must pass the –with-federated-storage-engine option when running configure. That’s pretty much all that’s required to start using the FEDERATED storage engine. Most linux distributions probably have a mysql 5 package that comes with the FEDERATED engine on already, although Slackware does not currently. Continue reading “The FEDERATED MySQL Storage Engine”

More SSH Brute Force Protection

Stopping SSH Brute Force Attacks resulted in some really great comments and suggestions from readers.

So, this is a follow up to the last SSH brute force post. I didn’t realize there was such a wide selection of applications for dealing with this, but there is! The two best looking options in my opinion are Fail2ban and DenyHosts.

I’ve actually started using DenyHosts on two machines now, and it’s working very well. I chose to go with DenyHosts for a very simple reason. Community stats. I love stats.

Anyway, if you’re looking for something to protect against ssh brute force attacks, go with Fail2ban or DenyHosts, they’re still being actively developed. I can’t say the same for Breakinguard, as it appears to have been abandoned about 1 year ago. And like I said, DenyHosts does it’s job extremely well, I couldn’t ask for anything more.

If you’re looking for another solution, try using cryptographic keys instead of passwords. A tutorial on configuring SSH to look for keys instead of passwords can be found here. This was suggested by commenter pwyll.

Oh, and this is the 700th post. yay!