Repairing a MySQL Database

September 3rd, 2010

There’s nothing worse than opening up your site and getting a nice fat error message, such as “Error opening a database connection.” Even worse is the fact that you haven’t messed with anything recently.

If you run a Web application that uses a MySQL backend (such as WordPress, in my case), you may run into occasional database corruption. Now, WordPress can fix most of its own database corruption if you let it, but I wasn’t that lucky.

Naturally, I had no backup. (I make backups before major WordPress upgrades, but delete them afterwards.) If you find yourself in my shoes, here’s how I repaired the damage.

  1. Log into MySQL. The command-line client is fine, though I use Sequel Pro for my database administration duties.
  2. Check each table. The code is CHECK TABLE `table_name` EXTENDED. (In my case, the wp_options table was corrupt.) You don’t need to include the EXTENDED option, but that will make MySQL perform extra (and potentially time-consuming) checking. I’d go for it.
  3. Fix the problem with REPAIR TABLE `table_name`.

My site came right back up after that. Excuse me while I go create a backup.

Styling the HTML5 <meter>

July 25th, 2010

HTML5 introduced several new tags, one of my favorites being <meter>, which is used to indicate scalar measurements. However, most browsers won’t do anything fancy with a <meter>.

But for everyone using another browser, we can easily fix that with a little CSS and JavaScript. Basically, we’re going to display each <meter> as a block element, and then insert a <div> element, whose size is determined by the <meter>‘s value attribute.

  1. First, decide how you want your <meter> styled. I opted to use the CSS system colors for a native look and feel, and -webkit-gradient to give it a nice sheen in Chrome and Safari.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    meter {
        /* You can customize the following. */
        background-color: appworkspace;
        border: thin inset menu;
        height: 1em;
        width: 100%;

        /* Leave the rest alone. */
        box-sizing: border-box;
            -moz-box-sizing: border-box;
            -webkit-box-sizing: border-box;
        clear: both;
        display: block;
        overflow: hidden;
        position: relative;
    }

    /* Style the inner bar. */     
    meter > div {
        /* You can customize the following. */
        background-color: highlight;
        background-image: -webkit-gradient(linear, 0% 0%, 90% 0%,
            from(rgb(28, 155, 91)), to(rgb(108, 255, 108)));
        box-shadow: 2px 0 8px grey;
            -moz-box-shadow: 2px 0 8px grey;
            -webkit-box-shadow: 2px 0 8px grey;

        /* Okay, that's enough customizing for now. */
        box-sizing: border-box;
            -moz-box-sizing: border-box;
            -webkit-box-sizing: border-box;
        float: none !important;
        height: 100%;
        margin-left: 0 !important;
        min-width: 1px;
        position: relative;
            bottom: 0;
            left: 0;
            top: 0;
        text-overflow: none !important;
        vertical-align: inherit !important;
    }

    meter.toohigh > div, meter.toolow > div {
        background-color: red !important;
        background-image: -webkit-gradient(linear, 0% 0%, 90% 0%,
            from(rgb(155, 28, 91)), to(rgb(255,108,108)))  !important;
    }
  2. Next, we’re going to use JavaScript to create the bar inside the <meter>. I opted to use the MooTools framework, but if you’re more adept with another framework, it should be fairly simple to port or rewrite my code.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    function updateMeter(m) {
        // Does this meter already have a bar?
        // If not, make one.  Otherwise, use the existing one.
        if (m.getChildren().length == 0) {
            bar = new Element("div");
        } else {
            bar = m.getChildren()[0];
        }

        // Set the bar's width to be a percentage of the meter, based
        // on the min and max values.
        width = m.getStyle("width").replace("px","").toInt();
        if (m.get("min")) {
            width *= m.get("data-value") / (m.get("max") - m.get("min");
        } else {
            width *= m.get("data-value") / m.get("max");
        }
        bar.setStyle("width", Math.ceil(width));
       
        // If we've set the high attribute, and the meter's value is
        // greater than high, give it the class toohigh.
        if (m.get("high")
            && m.get("data-value").toInt() >= m.get("high").toInt())
        {
            m.addClass("toohigh");
        }
        // Same thing, but with the low value.
        else if (m.get("low")
            && m.get("data-value").toInt() < = m.get("low").toInt())
        {
            m.addClass("toolow");
        } else {
            m.removeClass("toolow");
            m.removeClass("toohigh");
        }
       
        // A meter should contain a description of the measurement
        // inside of it.  Move that into the tooltip.
        m.set("title", m.get("text"));
       
        // Inject the bar.
        if (m.getChildren().length == 0) {
            m.empty().grab(bar);
        }
    }
    window.addEvent("domready",function(){
        $$("meter").each(function(m){
            updateMeter(m);
        });
    });
  3. Finally, mark up your <meter> tags. Your <meter> must have at least the value and max attributes set.

    (Note that due to a strange bug in MooTools, I had to duplicate the value attribute as a data-value attribute. It’s still legal HTML5, though. If you choose not to use MooTools, you can omit the data-values.)

    1
    2
    3
    <meter min="0" max="100" low="10" value="40" data-value="40">
        This meter is set to 40%, thankfully;  10% is too low!
    </meter>

To demonstrate this, I needed a good real-world data set. What’s more real world than my own Web server? With a little bit of PHP, I was able to whip together a little server status widget that shows CPU load and memory and swap usage.

Now that’s cool. Google Chrome 6 will do this to all <meter> tags automatically, so you won’t need JavaScript; however, this trick will still work, and (as always) you can still use CSS to override Chrome 6′s stylistic choices.

CSS Table Layout

April 29th, 2010

While HTML5 and CSS 3.0 continue to evolve, coding practices have not. For years, it was perfectly acceptable to use the <table> tag and its children to quickly and easily design grid layouts. Now, it looks fine and dandy — I admit to doing that back in the bad old days of IE6.

So, what’s the problem, you ask?

Accessibility. Most users browse the Web with a screen, keyboard, and mouse at their disposal. However, most Web designers fail to take into account blind users who use spoken or Braille interfaces instead of a screen, users with limited mobility who may use a joystick or keyboard in lieu of a mouse, or even color-blind users (although <table>-versus-CSS argument has nothing to do with colors). Screen readers and other assistive devices tend to fall all over themselves with <table>‘s used for layout.

Mobility. Even if accessibility is not a concern of yours, perhaps mobility is. It’s no surprise that the iPhone is penetrating society like a hot knife through butter; devices powered by Google’s Android and Palm’s webOS are right behind. Smartphone browsers don’t have a problem with layout <table>‘s, but take into consideration all of the PDA’s and regular cell phone browsers that are still in existence. Layout <table>‘s rarely degrade gracefully enough.

Bandwidth. No matter how fast Internet connections, servers, and backbones get, common sense doesn’t change — smaller things still download faster. Stripping out the excess HTML code will slim your pages’ sizes down. Your users on 3G or EDGE connections, as well as the stragglers on dial-up, will thank you.

Code refactoring. I’ve never heard the term “refactoring” applied to a markup language, but whatever you call it, eventually, you’ll want to redesign your site. CSS tables are much easier to work with, because they need little or no additional HTML code. Separating design (CSS) from content (HTML) is a major reason why Web development has gone in the direction that it has.

You can probably come up with more reasons on your own.

Now, CSS 2 added some relevant values to the display property: table, table-row, and table-cell (among others). Elements with these rules will replace <table>, <tr>, and<td> and <th>, respectively.

There are two ways to create CSS tables. Firstly, you can take the easy way out and use class names.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<style type="text/css">
  .table {
    display: table;
  }

  .tr {
    display: table-row;
  }

  .th, .td {
    display: table-cell;
  }

  /* Make our header cells bold, like HTML tables. */
  .th {
    font-weight: bolder;
  }
</style>
<div class="table">
    <div class="tr">
        <div class="th">Header A</div>
        <div class="th">Header B</div>
        <div class="th">Header C</div>
    </div>
    <div class="tr">
        <div class="th">foo</div>
        <div class="td">bar</div>
        <div class="td">baz</div>
    </div>
    <div class="tr">
        <div class="th">lorem</div>
        <div class="td">ipsum</div>
        <div class="td">dolor</div>
    </div>
</div>

That’s all we need to do.

Now, some of you are probably wondering how CSS tables can be less markup. I mean, if you check the code above, you’ll see that we had to add a class attribute to every single <div> in the table. That’s the easy way to do it, but you don’t really reap too many of the benefits. Let’s try using CSS selectors to jazz things up a bit, taking advantage of the fact that table elements are all nested.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<style type="text/css">
  .table {
    display: table;
  }

  /* All elements inside a .table will be a row container. */
  .table > * {
    display: table-row;
  }

  /* The first row contains header cells.  Make them all bold. */
  .table > *:first-child {
    font-weight: bolder;
  }

  /* All elements inside a table row will be a table cell. */
  .table > * > * {
    display: table-cell;
  }

  /* The first cell of each row will be a header as well. */
  .table > * > *:first-child {
    font-weight: bolder;
  }
</style>
<div class="table">
    <div>
        <div>Header A</div>
        <div>Header B</div>
        <div>Header C</div>
    </div>
    <div>
        <div>foo</div>
        <div>bar</div>
        <div>baz</div>
    </div>
    <div>
        <div>lorem</div>
        <div>ipsum</div>
        <div>dolor</div>
    </div>
</div>

Of course, you don’t have to use <div>’s; you can use any element you want — thank you, CSS rules! This is where CSS tables really come in handy for layout purposes. Take a look at this sample HTML5 page.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Sample page featuring CSS layout</title>
    <!-- copy the <style> tag from the last example -->
</head>

<body class="table">
    <div>
        <aside>sidebar goes here</aside>
        <article>main body goes here</article>
    </div>
    <div>
        <img src="companylogo.png" alt="Logo of Company, Inc.">
        <footer>footer text, links, etc.</footer>
    </div>
</body>
</html>

This is a crude introduction to CSS tables. Remember that CSS tables are not meant to replace the <table> tag for its legitimate uses: actual tabular data, like spreadsheets and calendars. After all, you don’t make a Web site in Excel, so don’t lay it out with spreadsheet tags.