Saved for Later – Page 50

September 30, 2015

Prepping your MySQL indexes for a character set change

When changing a MySQL table or column to a new character set that uses more bytes than the old character set, you need to first check if any schema changes are needed to accomodate the change. For example, changing character sets from latin1 to utf8 is an increase from 1 to 3 bytes, and changing from utf8 to utf8mb4 is an increase from 3 to 4 bytes. The MySQL reference manual has a helpful page with some details on this, but I want to add some examples to show how this schema prep can be accomplished.
There are three different types of length limits to take into consideration:
Index
Column
Row
In this post I will focus on index length limits, and I’ll save columns and rows for future posts. Read on for details.
Index Length Limits
The specific limits depend on which MySQL version and storage engine is used, and whether innodb_large_prefix is enabled (assuming MySQL 5.5 or higher). If you want to learn from about innodb_large_prefix you may want to read this post. A common example here is an index on a varchar(255) column in the utf8 character set without innodb_large_prefix. That index uses 765+2=767 bytes, so it conforms to the 767 byte limit per column of an InnoDB index without innodb_large_prefix. If I change that column to utf8mb4 then the index requires 1022 bytes, which causes the DDL to fail.
Here’s an example:
“`
mysql> create table if not exists varchar_test (
-> id int auto_increment primary key,
-> str varchar(255)
-> ) engine = InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)
mysql> alter table varchar_test add index str_index (str);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table varchar_test DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table varchar_test modify column str varchar(255) CHARACTER SET utf8mb4;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
“`
For the record, here are the index length limits I need to worry about in a character set change:
InnoDB indexes are limited to 3072 bytes
MyISAM indexes are limited to 1000 bytes
Without innodb_large_prefix, InnoDB index columns are limited to 767 bytes
In order to identify all of the indexes that I need to change, I run the following two queries on information_schema. (I tried to add the correct size for all relevant data types, but in case I forgot a data type or a new data type is added I put in a large default value to return false positives so I can go in and fix the query). These queries can be easily modified for a change to another character set such as utf8, utf16, utf32, etc.
Query #1:
“`
— Show me all index columns that will exceed the length limit if I change to utf8mb4
set @new_character_set = ‘utf8mb4’;
select table_schema, table_name, engine, index_name, column_name, column_type,
sub_part, index_column_length_in_bytes
from
(
select t.table_Schema,t.table_name,t.engine,s.index_name,c.column_name,
c.column_type,c.data_type,c.character_maximum_length,c.character_octet_length,
s.sub_part,c.character_set_name,cs.maxlen,
coalesce(
(
case
when s.sub_part is null then (cs.maxlen * c.character_maximum_length)
else (cs.maxlen * s.sub_part)
end
),
(case (c.data_type)
when ‘tinyint’ then 1
when ‘smallint’ then 2
when ‘mediumint’ then 3
when ‘int’ then 4
when ‘bigint’ then 8
when ‘decimal’ then 4
when ‘float’ then 4
when ‘year’ then 1
when ‘date’ then 3
when ‘datetime’ then 8
when ‘time’ then 3
when ‘timestamp’ then 4
else 1000000
end)) as index_column_length_in_bytes
from information_schema.statistics s
inner join information_schema.columns c on c.table_schema = s.table_schema
and c.table_name = s.table_name
and c.column_name = s.column_name
inner join information_schema.tables t on t.table_schema = c.table_schema
and t.table_name = c.table_name
inner join information_schema.character_sets cs on cs.character_set_name = @new_character_set
where s.index_type != ‘FULLTEXT’
) sub_query
where index_column_length_in_bytes > (
select case
when engine = ‘MyISAM’ then 1000
when engine = ‘InnoDB’ and (select max(innodb_large_prefix) from (
select variable_value=’ON’ as innodb_large_prefix from information_schema.global_variables where variable_name = ‘innodb_large_prefix’ union select 0 as innodb_large_prefix
) sub_query) = 1 then 3072 else 767
end
)
order by table_schema, table_name, index_name;
“`
Query #2:
“`
— show me all indexes that will exceed the length limit if I change to utf8mb4
set @new_character_set = ‘utf8mb4’;
select table_schema, table_name, engine, index_name, columns_in_index, index_length_in_bytes
from
(
select t.table_schema,t.table_name,t.engine,s.index_name,
group_concat(concat(s.column_name,
case
when s.sub_part is null
then ”
else concat(‘(‘,s.sub_part,’)’)
end
) order by s.seq_in_index) as columns_in_index,
sum(coalesce(
(
case
when s.sub_part is null
then (cs.maxlen * c.character_maximum_length)
else (cs.maxlen * s.sub_part)
end
),
(
case (c.data_type)
when ‘tinyint’ then 1
when ‘smallint’ then 2
when ‘mediumint’ then 3
when ‘int’ then 4
when ‘bigint’ then 8
when ‘decimal’ then 4
when ‘float’ then 4
when ‘year’ then 1
when ‘date’ then 3
when ‘datetime’ then 8
when ‘time’ then 3
when ‘timestamp’ then 4
else 1000000
end
)
)) as index_length_in_bytes
from information_schema.statistics s
inner join information_schema.columns c on c.table_schema = s.table_schema
and c.table_name = s.table_name
and c.column_name = s.column_name
inner join information_schema.tables t on t.table_schema = c.table_schema
and t.table_name = c.table_name
inner join information_schema.character_sets cs on cs.character_set_name = @new_character_set
where s.index_type != ‘FULLTEXT’
group by t.table_schema,t.table_name,t.engine,s.index_name
) sub_query
where index_length_in_bytes > (
select case
when engine = ‘MyISAM’ then 1000
else 3072
end
)
order by table_schema, table_name, index_name;
“`
Once I identify the affected indexes, I need to resolve each one either by shortening the sub-part of one or more columns in the index, removing columns from the index, decreasing the length of the column, or deciding not to change the character set of the relevant tables/columns.
via Planet MySQL
Prepping your MySQL indexes for a character set change

September 30, 2015

Freevolt generates power from thin air

What you see above may look like an unremarkable slice of electronics, but it can theoretically power a low-energy device forever, and for free. If that sounds like a big deal, well… that’s because it is. Drayson Technologies today announced Freevolt, a system that harvests energy from radio frequency (RF) signals bouncing around in the ether and turns it into usable, "perpetual power." Drayson isn’t exactly a household name, but the research and development company has a particular interest in energy, especially where all-electric racing is concerned. And now it’s developed the first commercial technology that literally creates electricity out of thin air.

We’re constantly surrounded by an ever-denser cloud of RF signals. They’re the reason your smartphone gets 2G, 3G and 4G coverage, your laptop gets WiFi, and your TV receives digital broadcasts. Capturing energy from this background noise is nothing new, but most proof-of-concept scenarios have employed dedicated transmitters that power devices at short ranges. Furthermore, research into the field has never really left the lab, though a company called Nikola Labs is hoping to release an iPhone case that’s said to extend battery life using RF energy harvesting.

According to Drayson, Freevolt is the first commercially available technology that powers devices using ambient RF energy, no dedicated transmitter required. The key to Freevolt is said to be the efficiency of its three constituent parts. A multi-band antenna scavenges RF energy from any source within the 0.5-5GHz range, which is then fed through an "ultra-efficient" rectifier that turns this energy into DC electricity. A power management module boosts, stores and outputs this electricity — and that’s all there is to it.

Freevolt may well be the most efficient system of its kind, but it’s still only viable for devices that require very little power. In a location where lots of RF signals are flying around, like in an office, a standard Freevolt unit can produce around 100 microwatts of power. That’s nowhere near enough to say, run your smartphone, but Drayson has some specific use cases in mind. The company thinks Freevolt can be the backbone of the connected home, and in a broader sense, the internet of things. Sensor-based devices, such as a smart smoke alarm, can be powered by Freevolt indefinitely. Beacons that provide indoor mapping and targeted advertising are also perfect candidates.

While it’s easy to visualize specific examples — a smoke alarm that never needs a new battery, or a low-power security camera that isn’t bound to a mains outlet — the true potential of Freevolt is hard to grasp. We’re talking about free energy here: devices that never need charging, cost nothing to run, and aren’t limited by the location of an external power source. An entire smart city — where roads know when they’re busy and bins know when they’re full — could be devised using countless sensors that require no upkeep, and have no overheads beyond the price of the hardware itself. It’s a powerful idea, and beyond sensors, Drayson imagines Freevolt being used to trickle-charge all kinds of hardware, significantly extending the battery life of a wearable, for instance.

What’s more, Freevolt can be scaled up for applications that require higher power outputs, and Drayson is currently working on miniaturizing its initial reference design and creating a flexible version that can be integrated into clothing, among other things. There are limitations to the technology, of course. The amount of power Freevolt can harness depends on the density of ambient RF signals, which are way more prevalent in urban areas than the countryside. A sensor-based product could still operate in these lower-yield environments, though, by monitoring a value every five minutes instead of every five seconds, for example.

Drayson’s business model involves selling licenses to Freevolt and its related patents, as well as offering guidance and technical support to interested parties. Development kits are also available to pre-order from today, so advanced tinkerers can get their hands on the tech too. It might take some time before Freevolt finds its way into products, as Drayson is relying primarily on other companies to dream up and develop real-world applications. That said, Drayson has created a consumer product of its very own that’s powered solely by Freevolt: an air pollution monitor called CleanSpace.

The CleanSpace Tag is a continuous carbon monoxide monitor that sends data back to your smartphone via Bluetooth. From the companion app, you can see real-time air pollution levels, and review your exposure during that day, recent weeks and further. The app also keep tabs on your travels, encouraging you to build up "CleanMiles" by walking and cycling rather than taking motorized transport. These banked CleanMiles can then be exchanged for rewards provided by partners such as Amazon, incentivizing you to travel in non-polluting ways.

Air pollution is of particular interest to Lord Drayson, chairman and CEO of Drayson Technologies, who hopes to increase awareness of the invisible health risk. But, there’s also a bigger picture. The CleanSpace app uses data from the 110 static sensors dotted around London to build a pollution map of the capital. Each CleanSpace Tag also feeds anonymized data into this system, with the idea being the more tags in the wild, the more locally relevant and robust that UK pollution map can become. CleanSpace users can therefore decide on the fly to avoid more polluted areas in favor of cleaner routes. The plan is to expand the crowdsourced data concept elsewhere if it’s well received, but for now the CleanSpace Tag is only available in the UK through a crowdfunding campaign. Pricing starts at £55 per tag, though you might want to buy one just to rip it open and see the Freevolt backbone hidden inside.

Source: Drayson Technologies

via Engadget
Freevolt generates power from thin air

September 30, 2015

The Flash Season 2 Teaser Trailer Is All About Zoom

Here we have a new promo spot for Season 2 of The Flash. This one gives us our first look at the villain Zoom. Barry Allen and Jay Garrick will have to team up to stop…
via MightyMega
The Flash Season 2 Teaser Trailer Is All About Zoom

September 30, 2015

How to Organize Your Photos in Lightroom

The Lightroom Catalog is a database containing all the relevant information that Lightroom needs about your photos in order to process your images and sit at the centre of your workflow. Lightroom is a digital asset management (DAM) tool – you can use it to organize and search your photos, as well as process them. […]

The post How to Organize Your Photos in Lightroom by Andrew S. Gibson appeared first on Digital Photography School.

via Digital Photography School
How to Organize Your Photos in Lightroom

September 30, 2015

Singularity University Launches Accelerator To Seize Academia’s Innovation Monopoly | TechCrunch

via TechCrunch
Singularity University Launches Accelerator To Seize Academia’s Innovation Monopoly | TechCrunch

September 29, 2015

Dilbert’s Dad is Pro-Gun (Because It Feels Right)

Scott Adams and friend. Photo credit: ew.com.

Scott Adams is the man behind (or next to) Dilbert, the socially awkward engineer chronicled in the comic strip of the same name. Last week, in a blog post, Adams declared his supports legal gun ownership. The cartoonist empathized … Read More

The post Dilbert’s Dad is Pro-Gun (Because It Feels Right) appeared first on The Truth About Guns.

via The Truth About Guns
Dilbert’s Dad is Pro-Gun (Because It Feels Right)

September 29, 2015

OS X 10.11 El Capitan: The Ars Technica Review

Apple brings refinement and under-the-hood changes to Yosemite’s new design.
via Ars Technica
OS X 10.11 El Capitan: The Ars Technica Review

September 29, 2015

How to make your own bootable OS X 10.11 El Capitan USB install drive

Setting up lots of Macs? Want an offline installer? Here’s how to roll your own.
via Ars Technica
How to make your own bootable OS X 10.11 El Capitan USB install drive

September 29, 2015

34 Amazing Photos Of The Super Blood Moon

Sunday night’s sky was a rare treat, wasn’t it? We won’t even see another lunar eclipse until 2018. But in the meantime, we have a lot of fantastic photos, taken by you, of the blood super moon.