Liveblog: How to Use Puppet Like an Adult

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, see drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, order so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, treat and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

Twice last week, erectile a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, malady which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

Twice last week, erectile a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, malady which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

We have a backup server that, ambulance from time to time, click gets errors when doing mysqldump backups (we do physical backups and logical backups, visit but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

Twice last week, erectile a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, malady which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

We have a backup server that, ambulance from time to time, click gets errors when doing mysqldump backups (we do physical backups and logical backups, visit but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

We have a backup server that, website like this from time to time, thumb gets errors when doing mysqldump backups (we do physical backups and logical backups, audiologist but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, drug and but I think I tackled it pretty well in the MySQL Administrator’s Bible. It is not a very long topic, so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

It started with a tweet from a coworker asking if I can recommend reading for making a master/master MySQL server. There are plenty of caveats about writing to only one master at a time, sildenafil so I made a PDF of the relevant pages. High Performance MySQL also has a few pages that I would recommend reading, and the third edition has similar information as the Bible, although it goes into more detail about why you might use master/master replication and what might go wrong. Unfortunately I could not find a resource for the few pages of text from High Performance MySQL, and I am not sure if this person needs the whole book for just a few pages.

Twice last week, medications a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

Twice last week, erectile a developer wanted to convert the existing datetime values in a database to UTC. The datetime values were the default for the server, malady which was the US/Pacific time zone, which is subject to Daylight Saving Time changes. Both developers for both applications wanted to convert all the times to UTC, so there would not be any changes due to Daylight Saving Time, and asked me for an easy query to know which times should be changed by adding 7 hours and which times should have 8 hours added to them.

The good news is that MySQL has some built-in functionality to make this easier. You may know about the CONVERT_TZ() function in MySQL, and that you can use it in a query to convert times like this:

mysql> SELECT CONVERT_TZ(NOW(),'-8:00','-0:00');
+-----------------------------------+
| CONVERT_TZ(NOW(),'-8:00','-0:00') |
+-----------------------------------+
| 2012-11-16 20:07:24 |
+-----------------------------------+
1 row in set (0.00 sec)

However, that is not much of a help, because if you know the timezone offset you can just add the right number of hours:

mysql> SELECT NOW()+INTERVAL 8 HOUR;
+-----------------------+
| NOW()+INTERVAL 8 HOUR |
+-----------------------+
| 2012-11-16 20:08:35 |
+-----------------------+
1 row in set (0.00 sec)

The Easier Way
A much easier way would be to set up the MySQL timezone tables so you could run a query like:

mysql> SELECT CONVERT_TZ(NOW(),'US/Pacific','UTC');
+--------------------------------------+
| CONVERT_TZ(NOW(),'US/Pacific','UTC') |
+--------------------------------------+
| 2012-11-16 20:10:30 |
+--------------------------------------+
1 row in set (0.00 sec)

And just to prove that this does the proper time conversion, consider this same time, 2 weeks ago, before the Daylight Saving Time change:

mysql> SELECT CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC');
+------------------------------------------------------+
| CONVERT_TZ(NOW()-interval 14 day,'US/Pacific','UTC') |
+------------------------------------------------------+
| 2012-11-02 19:10:52 |
+------------------------------------------------------+
1 row in set (0.00 sec)

MySQL knows when to add 8 hours, and when to add 7 hours. Magic!

Well, not quite magic. MySQL can only do this if you give it the timezone information. Luckily, servers have that information, and there is a tool that ships with MySQL that converts this timezone information to the right tables. Just follow the instructions on this page to populate the timezone tables. It’s typically as simple as running a command like this in the shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Once that table is populated you can use the CONVERT_TZ() function to update the existing values in the database, using the text values for time zones.

Note: If you are living somewhere with Daylight Saving Time or Summer Time, there may be several different choices for what text you use for the timezone. Make sure you know exactly what these timezones do. For example, PDT is Pacific Daylight Time, which is UTC-7. PST is Pacific Standard Time, which is UTC-8. US/Pacific is the name for the timezone that is PDT in the summer and PST in the winter, so if you wanted to automatically convert dates that might fall under either PDT or PST, you want to use the US/Pacific time zone.

We have a backup server that, ambulance from time to time, click gets errors when doing mysqldump backups (we do physical backups and logical backups, visit but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

We have a backup server that, website like this from time to time, thumb gets errors when doing mysqldump backups (we do physical backups and logical backups, audiologist but the physical backups work fine). The errors look like this:

mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'mozillians_org'': Out of resources when opening file '/tmp/#sql_3b63_0.MYI' (Errcode: 24) (23)
mysqldump: Error: 'Out of resources when opening file '/tmp/#sql_3b63_2.MYI' (Errcode: 24)' when trying to dump tablespaces

I tried restarting MySQL, and that helped, for a while. It helped to the point that we put in a cron job to restart MySQL every 4 hours so we would not run out of resources.

But that did not last forever. We tried restarting more frequently. We tried increasing ulimits. Again, this helped for a while, or seemed to.

When it happened again today, I decided to look around again for what other folks’ experience was. I ended up finding someone who had this problem on Windows, and what fixed it for them was changing table_cache (table_open_cache in MySQL 5.1 and higher).

Now, I am a staunch fighter for the Battle Against Any Guess. So I thought about it, and asked myself, “Does this make sense? Would changing this actually free up any resources?” and I decided to give it a try. It made sense, especially when I considered what might be happening when I rebooted or raised the ulimits – the resources were freed. I thought about it, and realized that if the resources were not tied up in the table_open_cache, that might also help.

I reduced the table_open_cache from 1024 to 200 – since the server in question is a backup server, it does not need such a large value. Well, as you can guess from the title, it worked!

My Mozilla coworkers Ben Kero and Dan Maher gave a standing-room only presentation at Linux Conf AU about “How to Use Puppet Like an Adult”. It was fantastic!

Data != logic
Business DATA does not belong in modules, erectile but business LOGIC is OK.

What are the data sources, then?
Hiera – lightweight pluggable, hierarchical databases. External to the modules, you can use many backends, including MySQL. New feature, standard in puppet 3.0. If you like YAML (and you should), you’ll like this.

$var = lookup('something') # unscoped (complicated)
$var = lookup('namespace::something') # scoped (yay!)

Another data source is puppetdb. This is a bigger topic, but the important thing is that it can be used for high performance store configs.

Where to find pre-built modules for puppet?
Github
Puppet Forge

Or you can write your own module
….but don’t waste time building your own, say, Apache module…someone else has a better one out there.

Is that module right for me?
What to check:
OS/distribution
Complexity – Can you read the module and understand what it does? If not, this might not be the module for you.
Popularity – the more people using/forking it, the more support is probably around. Also age of last commit.
What’s the documentation like?

Recommended pre-built modules – these work, and they’re good. Analyze how they work and learn from them:
puppetlabs/firewall
puppetlabs/cloud_provisioner

When rolling your own modules – if this is going to be a one-off, do whatever you want. If you want to make it open source, know that someone else will use it, and make it more generic.

Use parameterized classes. This allowed you to separate your business data from your business logic. You can avoid having passwords, ssh keys, etc in there, and then you CAN open source it.

Make sure it’s documented.

Module template
puppet module generate author-mod_name – gets you all the files you need with the necessary templares (e.g. README has the sections you need).

module template slide

Note: Everybody should be doing spec testing, not just puppet…..

Parameterized classes
Similar to definitions – they are passed in data. It’s how to separate data from logic. If you don’t get anything else, get this:

parameterized classes slide

These help you write your manifest one time for different nodes. If you have 10 web servers with different node names, write one manifest, and use logic and parameterized classes to instantiate that manifest 10 times. Don’t write 10 manifests.

USE A STYLE GUIDE
“Who here has written Perl code? Who here has written Perl code that someone else can read? USE A STYLE GUIDE”

Parser Validation
just run:
$ puppet parser validate manifest.pp

Parser validation example

Put this into your commit hook, so that parser errors don’t get committed.

Linting
A way of making sure code meets the style guide. External tool, but stable. Very customizable, you can use your own style guide, and you can have it ignore certain things (e.g. don’t care about quoting everything, so don’t error on that). You can put this into commit hooks too.

Linting slide

puppet-concat
Dynamically build files out of lots of parts. How you can build good config files for daemons that don’t support .d directories. Assume you have puppet-concat installed already, it’s widely used, because pre-built modules use it too.

puppet-concat slide

stdlib
Put out by puppetlabs, not actually part of the standard library, but contains lots of useful functions. This is also widely used. Can check if the variable is boolean, integer, strings, can collide hashes together, can check functions, etc.

Sanity preservation
Set default values for your variables – make sure they’re sane – you can pull variables out of facter.
Verify content – play it safe, don’t blithely act on user data. You can throw an error (e.g. if you have a string instead of an integer)
Mutually exclusive declarations – ensure when you start navigating down one logical path, it can’t go down the other path. This comes down to if/then programming, makes more layers to your manifest, but you can make accurate statements about what you want the module to do and predict what it WON’T do. Being able to predict what puppet will and won’t do is important.

Useful Log Output
Functions for each log level
e.g. notice(); warn(); err();
Make these informative and human-readable. What broke and why, can other people understand what’s going on with this?

Puppet As a Source of Truth
Build an ecosystem around puppet. The more you use puppet, the more it describes your infrastructure. How do you do this, though?
You can use the puppet data library (PDL) – a collection of services with an API so you can query puppet from other services – e.g. inventory system, dashboard, etc. You can also use it from within puppet.

You can build an inventory service to know all the facter information about all the machines. You can use the run report service for dashboards like puppetdashboard.

You can download a .dat file and visualize it with graphviz to see how your logic paths look. This .dat file comes within puppet (you do “gem install puppet” and then puppet with some options and you can get it).

The take-home:
take-home points

Comments are closed.