Category Archives: MySQL

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

5 Replies

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

4 Replies

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

Leave a reply

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

Leave a reply

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 →

Update a field in a MySQL table based on two fields from another

Leave a reply

I have a custom post type in WordPress for employees and used a combination of first and last name as the post title. Both the first and last name are also stored in individual fields in the post meta table.

Later I decided to change the structure of the post title from last name comma first name to first and last name. That change was easily made and enforced for all future records, but I needed a quick way to update the post titles for all existing employees.

I wrote a little MySQL statement that accomplished this and thought I’d share, as it might come in handy to someone else in the future.

1
2
3
4
5
6
7
8
9

UPDATE wp_posts 
SET post_title = (SELECT concat(c0.meta_value, ' ', c1.meta_value) 
FROM wp_postmeta AS c0 
JOIN wp_postmeta AS c1 
ON c0.post_id = c1.post_id 
WHERE c0.meta_key = '_first_name' 
AND c1.meta_key = '_last_name' 
AND wp_posts.ID = c0.post_id) 
WHERE post_type = 'employee';
UPDATE wp_posts 
SET post_title = (SELECT concat(c0.meta_value, ' ', c1.meta_value) 
FROM wp_postmeta AS c0 
JOIN wp_postmeta AS c1 
ON c0.post_id = c1.post_id 
WHERE c0.meta_key = '_first_name' 
AND c1.meta_key = '_last_name' 
AND wp_posts.ID = c0.post_id) 
WHERE post_type = 'employee';

Let’s break this statement down:

  • Line 1 refers to the wp_posts table that we’ll be updating.
  • Line 2 sets the post_title field to the result of the following subquery, which selects and concatenates two fields (meta_value and meta_value) from two other tables (wp_postmeta and wp_postmeta), which are really the same table, but we have two instances so we can get values from two different rows.
  • Line 3 lists the other table (wp_postmeta) that we’re getting the value from and gives it a temporary name of c0.
  • Line 4 joins that table to itself in another instance (c1), so we can get another value from the same field, but a different row.
  • Line 5 combines those two rows based on the value of post_id.
  • Line 6 and 7 indicate which column (meta_key) we’re looking at and which meta_value we’re looking for, which is _first_name and _last_name.
  • Line 8 brings the subquery home by connecting the row from wp_posts with our rows from wp_postmeta based on the post ID.
  • Line 9 indicates that we only want to overwrite post titles for the employee post type.

If you have any questions or found this helpful, feel free to leave a comment below.