Twice last week, 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');
+-----------------------------------+
| …

The short answer is: No. The longer answer is: It depends. With the latest version, most of the time, you can run:

pip install MySQL-python

and you're done... if you have the prerequisites. That's where it gets sticky.
The dreaded prerequisitesSince MySQLdb uses a C module to link against MySQL's client library, you will need:

• A C compiler and associated toolchain
• Python development headers and libraries
• MySQL development headers and libraries

LinuxMySQLdb was originally developed specifically for use on Linux, as it was the only platform I cared about at the time. Over time, several Linux distributions (Red Hat, Debian, Ubuntu, etc.) picked it up, created their own packages, and distributed them. If you are a Linux user, I recommend you use your distributions packages. But what if you can't, and you really do need to build your own? Install using pip. …

I’ve got the great response to my two previous polls (1,2) so lets continue learning about how we all use MySQL. The question of this week is What is the maximum number of tables per MySQL instance do you use ?

Note: There is a poll embedded within this post, please visit the site to participate in this post's poll.

Thank you for participating!

Since the latest changes made recently within InnoDB code (MySQL 5.6) to improve OLTP Read-Only performance + support of full text search (FTS), I was curious to compare it now with MyISAM..

While there was no doubt that using MyISAM as a storage engine for a heavy RW workloads may become very quickly problematic due its table locking on write design, the Read-Only workloads were still remaining favorable for MyISAM due it's extreme simplicity in data management (no transaction read views overhead, etc.), and specially when FTS was required, where MyISAM until now was the only MySQL engine capable to cover this need.. But then FTS came into InnoDB, and the open question for me is now: is there still any reason to use MyISAM for RO OLTP or FTS wokloads from performance point of view, or InnoDB may now cover this stuff as well..

For my test I will use:

• Sysbench for OLTP RO workloads
…

Horizon is getting cloudier. But these low hanging clouds are not darker or ominuous in any way. These are the clouds of enabling technologies providing mortals, drops of technologies according to their wishes. Behind this seamless information troposphere, databases are floating, again on demand. This Log Buffer Edition showers the droplets of blogs from databases...

The Percona Toolkit team is happy to announce the release of Percona Toolkit version 2.1.6. This is the sixth stable release in the 2.1 series, and primarily a bug-fix release; We suggest that users upgrade to the latest version of the tools.

The complete list of changes is on the Launchpad milestone for 2.1.6, but here are some highlights the release:

• pt-online-schema-change can now handle column renames
• pt-online-schema-change’s DELETE trigger no longer fails when altering a primary key
• pt-stalk got a plugin hook interface
• pt-show-grant support for column-level grants
• pt-table-checksum will warn if slaves have a differing binlog_format

Remember that if you need help with Percona Toolkit, we provide flat-fee, unlimited support services with rapid 24×7 response on a …

With a handful of exceptions, few people deliberately choose to deploy MySQL on read-only media – but there are cases where being able to access InnoDB data that way comes in handy.  As it happened, I had exactly this need a few months back, and the excellent InnoDB development team at Oracle has recently implemented this feature in MySQL 5.6.

First, some background.  We had a need to migrate  legacy systems from a 3rd party data center into new corporate data centers.  These systems were redundant, but we wanted to retain access to the data for archival purposes.  All went well, except one small detail:  Because the machines were originally housed outside the new data center, we weren’t allowed to stand up the servers inside the new data center – except in read-only mode.

Therein was the problem – we had many GB worth of data stored in MySQL using InnoDB, and we could access the data files – …

Keith Larson & myself (and now Lenka Kasparova) have been the MySQL Community Team for the past few years and we have traveled to a great many conferences and spoken many hours on MySQL. But we need your help. There are conferences all over that we can not attend or in many cases we are speaking but would like others to speak too! And many conferences are in a big need for speakers such as yourself.

Three upcoming shows are excellent opportunities and are actively seeing you as a presenter.

• FOSDEM is February 2nd and 3rd in Brussels, Belgium. There will be a special MySQL dev room and the call for papers ends on December 21st.
• There is a new PHP conference in Florida! …

Taxonomy upgrade extras: temporarymemory tablemyisam

MySQL temporary tables are created either in memory (as MEMORY tables) or on disk (as MyISAM tables). How many tables went to disk and how many tables went to memory you can find with:

mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 49094    |
| Created_tmp_tables      | 37842181 |
+-------------------------+----------+

Tables created in memory are typically faster than tables created on disk. Thus we want as many as possible tables to be created in memory.

…

With the Big Data craze that’s sweeping the world of technology right now, I often ask myself whether we’re deficit-spending, so to speak, with our data consumption habits. I’ve seen repeated examples of being unwilling to get rid of data, even though it’s unused and nobody can think of a future use for it. At the same time, much Big Data processing I’ve seen is brute-force and costly: hitting a not-very-valuable nut with a giant, expensive sledgehammer. I think the combination of these two problems represents a giant opportunity, and I’m going to call the solution Smart Data for lack of a better word.

What’s the problem, in 25 words or less? I think it’s that we’re collecting a lot of data simply because we can. Not because we know of any good use for it, but just because it’s there.

What is the real cost of all of this data? I think we all know we’re well behind the curve in making use of it. A huge …