Month: March 2015

Whether you called it a fort or treehouse I think most of us can remember a time when you simply wanted a place of your own. Maybe it was something to escape the stresses of elementary school or maybe a place high in a tree for launching of water balloons […]

Read more on MAKE

via Make:
5 Basic Steps for Building Forts and Treehouses

There are a wealth of ways for kids to learn computer science and programming these days. Add to the mix: Google’s own CS First curriculum, a free program with a variety of themes for all kinds of kids’ interests.

CS First isn’t new (the program started last year), but as a program that’s meant for teachers and community volunteers to form computer science clubs for kids ages 9 to 14, you might not have heard about it.

Lesson themes include Music & Sound (create a music video and build an interactive music display), Fashion & Design (build fashion-themed apps), Storytelling (create interactive stories), Game Design (code a game), and so on.

All of the materials (videos, programming in Scratch, and lesson plans) are freely available online, so parents—whether homeschooling or not—and kids with a lot of initiative can check out the lessons themselves. Or, better yet, start a club in your community.

CS First

via Lifehacker
Google CS First Teaches Kids Programming and Computer Science for Free

Plastic bottles are made by blow-molding, a beautiful, elegant process that can create a full-bodied shape with a narrow little neck. But how the heck do they make scuba tanks, fire extinguishers and gas canisters, which have similarly narrow necks, but are of course made of non-blow-moldable metal?

My first thought was that they make them in two halves via metal drawing, then weld them together—but obviously that makes no sense. So I looked into and found that yes, a scuba tank comes from a single flat disc of metal alloy.

And while drawing is still the initial production method, I wasn’t aware that what they call "hot spinning" is how you get the bottleneck:

via Core77 Rss Feed
Production Methods: How to Turn a Flat Metal Disc into a Bottle-Necked Scuba Tank

The other day we were doing some peer review on a schema change at VividCortex and the topic of character set, column length, and the potential downsides of using utf8 came up. I knew from experience that there are some hidden gotchas with this, and usually I’ll just say a couple words and send a link with further reading. But Google turned up nothing, so I am writing this post to try to help fix that.
TL;DR version is that when MySQL can’t use an index for operations such as ORDER BY, it may allocate fixed-length memory buffers large enough to hold the worst-case values, and the same might apply to temporary tables on disk. This can be seen in EXPLAIN as “Using filesort; using temporary” and similar. And when this happens, you might end up making MySQL do gigabytes worth of work to finish a query on a table that’s only a fraction of that size.
Let’s see why this can happen.
The Theory of UTF8
In theory, you can “just use utf8 for everything,” and there is lots of advice on the Internet suggesting you should. There are good reasons for this. If you use a 1-byte character set, such as the default latin1, and put multibyte data into it (which is very easy to do), you can end up with quite a mess that’s pretty hard to undo. Lots of funny-looking characters can result. You know, those little diamonds with a question mark in the middle, or squares with four little digits inside instead of a character.
In theory, there’s no downside to using utf8 for ASCII data. The “normal” characters all fit in one byte in utf8 anyway, so the bytes end up being the same, regardless of the character set. You’d only get multi-byte characters when you go outside the ASCII range. (I’m being a bit vague with terms like ASCII to avoid the verbosity of being precise. Please correct me, or ask questions, if I’m taking too much of a shortcut.)
And in theory, you can use VARCHAR for everything, too. A VARCHAR begins with a little header that says how long the value is. For VARCHAR(255), for example, there will be 2 bytes that say how long the value is, followed by the data itself. If you use, say, VARCHAR(255) for a column, even if it’s not really going to store values that long, you theoretically pay no extra cost.
So in theory, this is a fine table definition:
CREATE TABLE `t1` (
`a` varchar(255) CHARACTER SET utf8 NOT NULL,
`b` varchar(255) CHARACTER SET utf8 NOT NULL,
KEY `a` (`a`)
)
Now let’s talk about the reality.
Temporary Tables and Filesorts
Suppose you write the following query against the table shown above:
SELECT a FROM t1 WHERE a > ‘abc’ ORDER BY a LIMIT 20;
MySQL can execute this query by looking into the index on the a column, finding the first value that’s greater than “abc”, and reading the next 20 values. The index is already sorted, so the ORDER BY is automatically satisfied. This is excellent.
The difficulty comes when, for example, you ORDER BY a different column.
SELECT a FROM t1 WHERE a > ‘abc’ ORDER BY b LIMIT 20;
In this case, MySQL will have to use a so-called “filesort.” Filesort doesn’t really mean files are sorted. It should be called “sort, which may use a file if it overflows the buffer.”
MySQL has a couple of sort algorithms. These are covered in some detail in High Performance MySQL, especially in Chapter 6 under Sort Optimizations. The manual also discusses them, as does Sergey Petrunia’s blog post. In brief, though, MySQL does a sort by putting values into a buffer in memory. The size of this buffer is specified by the sort_buffer_size server variable. If all the values fit into the buffer, MySQL sorts in-memory; if not, then it writes to temp files on disk and does a merge-sort.
This is where the gotchas start to appear and theory diverges from reality in two important ways:
VARCHAR isn’t variable-length anymore. The table’s on-disk storage may be variable, but the values are stored in fixed-length in memory, at their full possible width. Ditto for the temporary files.
utf8 isn’t single-byte anymore. The fixed-length values are created large enough to accomodate rows that are all 3-byte characters.
So your “hello, world” row that consumes 2+12 bytes on disk suddenly consumes 2+(3*255)=767 bytes in memory for the sort. Or on disk, if there are too many rows to fit into the sort buffer.
It can potentially get worse than this, too. The dreaded “Using filesort” is bad enough, but it could be “Using temporary; Using filesort” which should strike fear into your heart. This means MySQL is creating a temporary table for part of your query. It might use an in-memory temporary table with the MEMORY storage engine, which pads rows to full-length worst-case; it might also use on-disk MyISAM storage for the temp table too. There’s more about this in High Performance MySQL as well.
Summary
This is a fairly involved topic, with lots of good reading in the manual, the Internet, and The Book Whose Name I Shall Not Keep Repeating.
Details aside, the point is it’s fairly easy (and not uncommon) to create a situation where your temp partition fills up with 10GB of data from a single innocent-looking query against a table that’s a fraction of that size on disk. All because of utf8 and/or long VARCHAR lengths!
My advice is generally to consider things like character sets and VARCHAR length limits in three ways:
Constraints on what data can/should be stored.
Accomodation for the data you want to store; make sure your database can hold what you want to put into it.
Hints and worst-case bounds for the query execution process, which will sometimes be pessimistic/greedy and do the work the schema indicates might be needed, rather than the work that’s actually needed for the data that’s stored.
There’s some balance between overly tight schema constraints, which might force you to do an ALTER in the future (yuck, especially on large tables) versus overly loose constraints, which might surprise you in a bad way. It comes a bit from experience and unless you have a crystal ball, even with experience you’ll get bitten sometimes!
A relevant tweet I saw today:
Bad schemas < no schemas < good schemas – @jaykreps #StrataHadoop #Avro #Kafka— Tom White (@tom_e_white) February 19, 2015
Happy schema optimizing!
Thumbnail Cred
via Planet MySQL
Temp Tables, Filesorts, UTF8, VARCHAR, and Schema Design in MySQL

In MySQL Character encoding – part 1 we stated that the myriad of ways in which character encoding can be controlled can lead to many situations where your data may not be available as expected.
UTF8 was designed on a placemat in a New Jersey diner one night in September or so 1992.
Setting MySQL Client and Server Character encoding.
Lets restart MySQL with the correct setting for our purpose, UTF8. Here we can see the setting in the MySQL configuration file, in this case /etc/mysql/my.cnf.
character-set-server = utf8
This change is then reflected in the session and global variables once the instance is restarted with the new configuration parameter.
mysql> SELECT @@global.character_set_server, @@session.character_set_client;
+——————————-+——————————–+
| @@global.character_set_server | @@session.character_set_client | +——————————-+——————————–+
| utf8 | utf8 | +——————————-+——————————–+ 1 row in set (0.00 sec)
Now we have verified the server and client are set to use UTF8, we can go ahead, continue developing our application and create a new table people.
mysql> CREATE TABLE people (first_name VARCHAR(30) NOT NULL, last_name VARCHAR(30) NOT NULL); Query OK, 0 rows affected (0.13 sec)
Now let’s enter some data into the new table, which was created with the server and client configured for UTF8.
Something appears to have gone terribly wrong, the accent in Maciek’s surname now appears as a question mark.
mysql> SELECT @@session.character_set_server, @@session.character_set_client; +——————————–+——————————–+
| @@session.character_set_server | @@session.character_set_client | +——————————–+——————————–+
| utf8 | utf8 | +——————————–+——————————–+ 1 row in set (0.00 sec)
The database settings are still UTF8, this should have worked.
mysql> USE fosdem;
mysql> SHOW CREATE TABLE people\G
*************************** 1. row ***************************
Table: people
Create Table: CREATE TABLE `people` (
`first_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
Looking at the table, we see that despite being created under a server set to use UTF8, it appears to be set to use latin1.
How can this be?, Let’s look at the session settings.
mysql> SHOW SESSION VARIABLES LIKE ‘character_set_%’; +————————–+—————————-+ | Variable_name | Value | +————————–+—————————-+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+ 8 rows in set (0.00 sec)
We can see the server and client values are as expected, but database is set to something else.
mysql> SHOW CREATE DATABASE fosdem\G *************************** 1. row ***************************
Database: fosdem
Create Database: CREATE DATABASE `fosdem` /*!40100 DEFAULT CHARACTER SET latin1 */ 1 row in set (0.00 sec)
Since the database was created when the server was set to latin1 it inherited that charset setting, which persists even when the server setting changes.
Can we fix this?
mysql> SET NAMES utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT last_name, HEX(last_name) FROM people; +————+———————-+
| last_name | HEX(last_name) | +————+———————-+
| Lemon | 4C656D6F6E | | Müller | 4DFC6C6C6572 | | Dobrza?ski | 446F62727A613F736B69 | +————+———————-+ 3 rows in set (0.00 sec)
mysql> SET NAMES latin2;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT last_name, HEX(last_name) FROM people; +————+———————-+
| last_name | HEX(last_name) | +————+———————-+
| Lemon | 4C656D6F6E | | Müller | 4DFC6C6C6572 | | Dobrza?ski | 446F62727A613F736B69 | +————+———————-+ 3 rows in set (0.00 sec)
Unfortunately, no matter how I try to read the data, 0x3F is ‘?’, so the ‘ń’ has been lost forever. Therefore it may not be enough to reconfigure the server, as a mismatch between client and server can permanently break data, due to the implicit conversion inside the MySQL server.
Implicit conversions happen silently when characters of one character set are inserted into a column with a different character set. This behaviour can be controlled by SQL_MODE, which allows you force MySQL to raise an error instead.
In MySQL Character encoding – part 1 we established there were a number of places you can control the character settings, now we can add a couple of important observations to our view of Character encoding settings.
Session settings
character_set_server
character_set_client
character_set_connection
character_set_database
character_set_result
Schema level Defaults – Affects new tables
Table level Defaults – Affects new columns
Column charsets
We have seen how a table created with no explicit charset declaration inherits the database (schema) charset, but what happens to a column when the table charset is changed?.
mysql> USE fosdem;
mysql> CREATE TABLE test (a VARCHAR(300), INDEX (a)); Query OK, 0 rows affected (0.62 sec)
mysql> SHOW CREATE TABLE test\G *************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`a` varchar(300) DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
mysql> ALTER TABLE test DEFAULT CHARSET = utf8; Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE test\G *************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`a` varchar(300) CHARACTER SET latin1 DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
The columns in a table inherit their default charset value when the table is created, but do not change when the table is changed, however new columns added after the ALTER TABLE would inherit UTF8.
mysql> ALTER TABLE test ADD b VARCHAR(10); Query OK, 0 rows affected (0.74 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE test\G *************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`a` varchar(300) CHARACTER SET latin1 DEFAULT NULL, `b` varchar(10) DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec
What can you do if you detect inconsistencies in your MySQL Character encoding settings
First of all, keep calm and don’t start by changing something. Analyse the situation and make sure you understand what settings you have and what your application understands regarding reading and writing data from the database.
Once you detect a problem, try to assess the extent of the damage. Firstly, what is the scope of the damage and is it consistent. Are all the rows bad or is it just a subset such as the last days worth of inserts. Are all the bad rows broken in the same way or are there actually a mixture of problems affected different sets of rows. Are the rows actually repairable – could be that recovering from backup and rolling forward is necessary as the inserted data has already been destroyed. Has any character mapping occurred during writes (e.g. unicode over latin1/latin1) – all of this is necessary to get a good picture of where you are starting from.
Take care not to do not do any of the following:
Try to fix this table by table unless you really only have a single table. – Any fix will affect the application and database as a whole, therefore fixing a single table may lead to inconsistencies and further problems elsewhere.
ALTER TABLE … DEFAULT CHARSET = as it only changes the default character set for new columns.
ALTER TABLE … CONVERT TO CHARACTER SET … It’s not for fixing broken encoding.
ALTER TABLE … MODIFY col_name … CHARACTER SET …
What needs to be fixed?
Schema default character set
ALTER SCHEMA fosdem DEFAULT CHARSET = utf8;
Tables with text columns: CHAR, VARCHAR, TEXT, TINYTEXT, LONGTEXT
What about ENUM?
The information schema can provide a list of candidate tables.
SELECT CONCAT(c.table_schema, ‘.’, c.table_name) AS candidate_table FROM information_schema.columns c
WHERE c.table_schema = ‘fosdem’
AND c.column_type REGEXP ‘^(.*CHAR|.*TEXT|ENUM)(\(.+\))?$’ GROUP BY candidate_table;
You must also ensure the database and application configuration is correct also, to avoid having the newly fixed tables broken by new data being introduced incorrectly (for the settings) into the tables.
How do I fix this?
Option 1. Dump and restore (Requires downtime)
Dump the data preserving the bad configuration and drop the old database
# mysqldump -u root -p –skip-set-charset –default-character-set=latin1 fosdem > fosdem.sql
mysql> DROP SCHEMA fosdem;
Correct table definitions in the dump file by editing DEFAULT CHARSET in all CREATE TABLE statements, then create the database again and import the data.
mysql> CREATE SCHEMA fosdem DEFAULT CHARSET utf8;
# mysql -u root -p –default-character-set=utf8 fosdem < fosdem.sql
Option 2. Two step conversion (Requires downtime)
Perform a two step conversion with ALTER TABLE, converting the original encoding to VARBINARY/BLOB and then from there to the target encoding. Conversion from/to BINARY/BLOB removes character set context.
Stop applications
On each table, for each text column perform:
ALTER TABLE tbl MODIFY col_name VARBINARY(255);
ALTER TABLE tbl MODIFY col_name VARCHAR(255) CHARACTER SET utf8;
You may specify multiple columns per ALTER TABLE
Fix the problems (application and/or db configs)
Restart applications
Option 3. – Online character set fix; (Minimal downtime, Approximately 1 min)
Using pt-online-schema-change with the PSCE plugin and a small patch for pt-online-schema-change, you can convert columns online in the live database.
Start pt-online-schema-change on all tables – one by one with table rotation disabled (–no-swap-tables) or drop pt-online-schema-change triggers
Wait until all tables have been converted
Stop applications
Fix the problems (application and/or db configs)
Rotate the tables – should take a minute or so
Restart applications
Currently the patch to pt-online-schema-change and plugin are available on bitbucket.
In MySQL Character encoding part 3 we will cover the gotchas in the process of fixing broken encoding, and what best practise to follow to get it right each time you setup a new server or create a new database.
via Planet MySQL
MySQL Character encoding – part 2

Archaeologists have excavated a house in Nazareth, Jesus’ home town, that dates back to the first century. Local Christians have long believed it was Jesus’ childhood home, but scientists say that’s impossible to know for sure. What the house reveals about life during Jesus’ childhood, however, is fascinating.

University of Reading archaeologist Ken Dark and his colleagues spent several years excavating this house, which is currently located beneath a convent — itself built on top of a Byzantine church that is roughly 1500 years old. The Byzantines controlled Nazareth and its environs until the 7th century, and they put their church on top of the house because they believed it was where Jesus had spent his infancy. So there is already a long history of people in the area thinking of the spot as Jesus’ childhood home.

The house itself, pictured above, was built into the limestone face of a hillside, its stone walls and courtyard jutting out into what would have probably been a lively street. It had several rooms with chalk floors, and archaeologists have also found the remains of a stairway. Inside the home, they found limestone dishware along with other household items. In a paper out this week in Biblical Archaeology Review, Dark points out that the limestone vessels suggest the home belonged to a Jewish family because Jews at the time believed limestone could not become impure. Thus it would have been an ideal material for holding food.

Dark compared what he’s discovered about first century Nazareth with another local settlement, Sepphoris. What he’s found undermines the traditional idea of Nazareth as a tiny village at the time Jesus was born. Instead, it appears to have been a lively town, bustling with activity. What set it apart from Sepphoris is its lack of Roman iconography. While Sepphoris is full of Roman writing and art, Nazareth seems to have rejected Roman culture. In his paper, Dark suggests this might mean that Nazareth was a largely Jewish town, holding out against Roman influences that dominated the area at the time. Romans of the first and second centuries were extremely anti-Semitic, often banning Jewish religious expression, so it’s no surprise that Jewish towns might not look kindly on Roman culture.

Though very little remains of first century Nazareth, this home has remained relatively well preserved. LiveScience explains that this is probably because both the Byzantines and Crusaders believed it was a holy place:

The fact that the house was protected explains its "excellent preservation," Dark wrote. "Great efforts had been made to encompass the remains of this building within the vaulted cellars of both the Byzantine and Crusader churches, so that it was thereafter protected," he said.

"Both the tombs and the house were decorated with mosaics in the Byzantine period, suggesting that they were of special importance, and possibly venerated," he wrote.

We will never know if this is the home where a Jewish rebel named Jesus grew up, before challenging the Roman government and founding one of the most popular religions in the west. But we can be certain that the home dates to the time when Jesus is said to have been born. It offers us a window on the past, and a glimpse of what Jewish villages were like under the Roman Empire.

Read the full scientific paper via the Biblical Archaeology Review.

via Gizmodo
This House in Nazareth Offers Hints About Jesus’ Childhood Town

We’ve screened your monitor mount nominations and come up with the four pack leaders. Now it’s time to find a winner. Read up and vote.

Find more desk upgrade recommendations in our Office Gear Guide.

---

Ergotron LX Dual and Single Mounts

It’s hands down the best monitor arm I’ve used. You can easily move the monitors around if you want to try out a new layout or distance, rotate the monitors if you want to swap between portrait and landscape. I use them at home, I use them at the office. They’re well worth the money. – matthewdtwo

---

Halter Dual LCD Monitor Stand

I use this at work. Good build quality, solid, lots of adjustment options, and cheap. Gets the job done without breaking the bank. – Cixelsyd

---

UPLIFT Monitor Arm

The UPLIFT Monitor Arms offer some serious bang for their buck. $109 gets you a height-adjustable counterbalanced arm with a huge reach you can adjust by just grabbing the display, and an easily installed clamp mount. Rock solid construction as well. There’s also a dual version for slightly more ($159). – thsjb

---

Monoprice 3-Way Desk Mount Bracket

The Monoprice 3way adjustable monitor stand. I had been looking for a monitor stand that ran under 100 bucks. This one fit the bill, and I think I paid a similar price to what it is now. Its not the prettiest, but it gets the job done and its not hard on the pocket book. – Julio Libre

---

---

Commerce covers the best products on Kinja Gear, finds you deals on those products on Kinja Deals, and asks you about your favorites on Kinja Co-Op, click here to learn more. We operate independently of Editorial and Advertising, and if you buy something through our posts, we may get a small share of the sale. We want your feedback.

Follow us for the best deals on the Internet, and check out http://t.co/w8ke7mw7nT

— Kinja Deals (@KinjaDeals) June 9, 2014

via Gizmodo
Four Best Monitor Mounts

We’ve screened your monitor mount nominations and come up with the four pack leaders. Now it’s time to find a winner. Read up and vote.

Find more desk upgrade recommendations in our Office Gear Guide.

---

Ergotron LX Dual and Single Mounts

It’s hands down the best monitor arm I’ve used. You can easily move the monitors around if you want to try out a new layout or distance, rotate the monitors if you want to swap between portrait and landscape. I use them at home, I use them at the office. They’re well worth the money. – matthewdtwo

---

Halter Dual LCD Monitor Stand

I use this at work. Good build quality, solid, lots of adjustment options, and cheap. Gets the job done without breaking the bank. – Cixelsyd

---

UPLIFT Monitor Arm

The UPLIFT Monitor Arms offer some serious bang for their buck. $109 gets you a height-adjustable counterbalanced arm with a huge reach you can adjust by just grabbing the display, and an easily installed clamp mount. Rock solid construction as well. There’s also a dual version for slightly more ($159). – thsjb

---

Monoprice 3-Way Desk Mount Bracket

The Monoprice 3way adjustable monitor stand. I had been looking for a monitor stand that ran under 100 bucks. This one fit the bill, and I think I paid a similar price to what it is now. Its not the prettiest, but it gets the job done and its not hard on the pocket book. – Julio Libre

---

---

Commerce covers the best products on Kinja Gear, finds you deals on those products on Kinja Deals, and asks you about your favorites on Kinja Co-Op, click here to learn more. We operate independently of Editorial and Advertising, and if you buy something through our posts, we may get a small share of the sale. We want your feedback.

Follow us for the best deals on the Internet, and check out http://t.co/w8ke7mw7nT

— Kinja Deals (@KinjaDeals) June 9, 2014

via Lifehacker
Four Best Monitor Mounts