Category Archives: MySQL

Can’t connect to MySQL server on ‘127.0.0.1’ (61)

I downloaded the MySQL server and installed it on my Mac, but every time I tried to connect to it, I got the following error:

Can’t connect to MySQL server on ‘127.0.0.1’ (61)

There can be many reasons for this error, most of which are generously covered online, but in my case it was because MySQL was running on port 3307. I think the port was updated in a recent MySQL server update, because I’ve never had this issue with a previous MySQL server version. Here’s how to check if you have the same problem, and how to change it to port 3306 (unless you want to keep it running on port 3307).

Continue reading

Monitor database availability with Pingdom

Preface

I’m testing out a new VPS with a couple sites on it, and after several hours of uptime, the mysqld service (database) was sacrificed in order to clear up RAM. As it turns out, I didn’t have a swap space for the VPS to offload inactive pages in memory.

I wrote about how to add a swap space to your VPS yesterday, but today I wanted to share how you can get alerted by Pingdom that your database is down. If you simply check for a 200 OK response, Pingdom would report that everything is OK, however if your site is database-dependent, just because the server is up doesn’t mean the site is functional.

Now, it is possible that you could have a database issue with one site, but not the other, in other words, you’d eventually want to monitor both sites using two different checks, however the goal here is to ensure that the database is generally available.

The following describes how you can setup a page on your VPS to report whether the database is up, and how you can then have Pingdom look for that information to determine whether your server is considered up.

Continue reading

Dynamically insert data into MySQL table based on select query result

I was working on a new feature for a site, and while it closely resembles WordPress’ native post formats, it offers more flexibility and control. Users can choose from predefined post layouts and it will display the post using a custom layout and branding.

Since this is a new feature, none of the existing posts have a post layout associated with them, and while there is a default, I actually want my existing posts to be specified.

Here is a MySQL query that performs an insert based on the results of a select query. It’s pretty straightforward, but wanted to share it nevertheless.

1
2
3
4
5
INSERT INTO wp_postmeta(post_id, meta_key, meta_value)
  SELECT ID AS post_id, '_post_layout' AS meta_key, 2 AS meta_value
  FROM wp_posts
  WHERE post_status = 'publish'
  AND post_type = 'post';
INSERT INTO wp_postmeta(post_id, meta_key, meta_value)
  SELECT ID AS post_id, '_post_layout' AS meta_key, 2 AS meta_value
  FROM wp_posts
  WHERE post_status = 'publish'
  AND post_type = 'post';

We basically specify the table (wp_postmeta) and the columns (post_id, meta_key and meta_value) we wish to insert data to, then we select that data from another table, where the post_id, in this case, is dynamic per row, but the meta_key and meta_value are statically set to _post_layout and 2 respectively.

You can adjust this query to your liking, as long as you end up with the desired columns that correspond to the columns you wish to insert data to.

MySQL query for getting three most recent posts for last 12 months in WordPress

I recently had a need to retrieve the last three posts for the last 12 months that had at least one or more posts in WordPress. Another requirement was to display a “more” link that would redirect the user to the archive of that month, provided there was at least one additional post to show for.

Before we go over how we can do that, here is an excerpt of what the array ultimately will look like:

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
51
52
53
Array
(
    [201303] => Array
        (
            [month_year] => March 2013
            [more_posts] => 1
            [posts] => Array
                (
                    [0] => Array
                        (
                            [post_title] => Test Post 1
                        )
 
                    [1] => Array
                        (
                            [post_title] => Test Post 2
                        )
 
                    [2] => Array
                        (
                            [post_title] => Test Post 3
                        )
 
                )
 
        )
 
    [201302] => Array
        (
            [month_year] => February 2013
            [more_posts] => 1
            [posts] => Array
                (
                    [4] => Array
                        (
                            [post_title] => Test Post 4
                        )
 
                    [5] => Array
                        (
                            [post_title] => Test Post 5
                        )
 
                    [6] => Array
                        (
                            [post_title] => Test Post 6
                        )
 
                )
 
        )
    ...
)
Array
(
    [201303] => Array
        (
            [month_year] => March 2013
            [more_posts] => 1
            [posts] => Array
                (
                    [0] => Array
                        (
                            [post_title] => Test Post 1
                        )

                    [1] => Array
                        (
                            [post_title] => Test Post 2
                        )

                    [2] => Array
                        (
                            [post_title] => Test Post 3
                        )

                )

        )

    [201302] => Array
        (
            [month_year] => February 2013
            [more_posts] => 1
            [posts] => Array
                (
                    [4] => Array
                        (
                            [post_title] => Test Post 4
                        )

                    [5] => Array
                        (
                            [post_title] => Test Post 5
                        )

                    [6] => Array
                        (
                            [post_title] => Test Post 6
                        )

                )

        )
    ...
)

It’s an array with 12 elements, if there are, in fact, 12 months with posts, where each element contains the name of the month with a corresponding year, a boolean of whether there are more posts to show for, and another array with three post titles linking to the full post.

Continue reading