In this article we describe benefits and shortcomings of different ways of exporting data from a MySQL table to the CSV format.

Permalink: http://bit.ly/1o0NdpY



When an application asks MySQL for the Primary Key of a table, there are several ways to go about doing this. A fast way would be to use these statements:

DESCRIBE `dbName`.`tableName`;
-- or
SHOW INDEX FROM `dbName`.`tableName` 
WHERE `Key_name` = 'PRIMARY';

The result set would have to be parsed in order to get the column names. This is not a recommended way to get the PK columns due to its limited usefulness as the column names cannot be returned INTO a variable.

Another method often used is this SELECT statement that uses a table JOIN:

SELECT k.`COLUMN_NAME`
FROM `information_schema`.`TABLE_CONSTRAINTS` t
JOIN `information_schema`.`KEY_COLUMN_USAGE` k
USING (`CONSTRAINT_NAME`, `TABLE_SCHEMA`, `TABLE_NAME`) …

“Our experience from PNUTS also tells that these systems are hard to build: performance, but also scaleout, elasticity, failure handling, replication. You can’t afford to take any of these for granted when choosing a system. We wanted to find a way to call these out.” – Adam Silberstein and Raghu Ramakrishnan, Yahoo! Research. ___________________________________ A [...]

Notes:
1. The script prints out elapsed time since transaction started, MySQL thread id, and the kill statement for transactions running longer than a defined threshold value, in seconds. Just copy, paster, and then execute the kill statement if you want to terminate the long transaction(s);
2. Adjust shellCmd variable;
3. Adjust longRunningThreshold value as needed. It is measured in seconds;
4. No special libraries/modules needed, as long as there is a working mysql client;
5. re module is used for regex processing. Good place to find examples of regular expression search and grouping. A status variable is used to assist locating MySQL thread id once a transaction running longer than the defined threshold is found.

import re, shlex, subprocess 

def runCmd(cmd):
    proc = subprocess.Popen(shlex.split(cmd), stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=False)
    out, err = proc.communicate()
    ret = …

LVM (Logical Volume Management) is a very important tool to have in the toolkit of a MySQL DBA. It allows you to create and extend logical volumes on the fly. This allows me to, say, add another disk and extend a partition effortlessly. The other very important feature is the ability to take snapshots, that you can then use for backups. All in all its a must have tool. Hence, this guide will allow you to understand various terminologies associated with LVM, together with setting up LVM volumes and in a later part will also show you how to extend...

Yesterday, Percona held Percona Live NYC, which they describe as an “intensive one-day MySQL summit.” They meant it. It was like drinking from a firehose. There was too much for me to give a complete report, so I’d like to highlight two sessions that stuck out for me.

Why SQL Wins

Sergei Tsarev (Clustrix) gave a great overview of the last 50 years of database development. He talked about the early days, in which what we now think of as database functionality had to be implemented in each application. Programmer productivity was therefore low.

As modern SQL databases emerged, productivity shot up since databases bundled up common functionality with an easy-to-code interface. This now seems like a golden age of databases, in which transactional semantics were hashed out.

Fast forward to today. Database performance has failed to keep up with …

[...] a busy week – between O’Reilly and Collaborate we gave a number of talks, sponsored a booth, and took some long flights in between the shows. But what an interesting week. The MySQL [...]

As the birds have started their yearly migration back to their homes from the warmer areas to the relative less cooler areas in summer, bloggers are also touching base with the technologies which they cherish most and coming back with some master strokes. This new cool edition of Log Buffer, the coolest blog carnival covering hottest topics encompass that home coming. Now Chill with Log Buffer #222!!!

Oracle:

Charles Hooper blogs about an Overly Complicated Use Case Example regarding Row Values to Comma Separated Lists.

…

At MySQL I think I once instituted something which became sort of a tradition. If you are not a current or former MySQLer, the rest of this post may not be too interesting, and possibly it's not that interestying in the general sense either. But as I no longer work for MySQL, and many other MySQLers who remember this are also former MySQLers today, I use this medium, my blog, for this years review of the #1 swedish delicatessen, Swedish New Potatoes. And yes, I am aware that this has little to do with the MySQL product, although it does have something to do with the culture of the old MySQL (i.e. you could email company-wide about potatoes and still keep your job).

So, what about this years new potatoes? They are kinda early this year, it's not even June yet, and I had my first last night. Newly, and lightly, boiled, eaten with some butter and a pinch of salt. The verdict? In potato-technical terms, the term is Yummy! This exquisite …

Check out the latest edition of the MySQL Newsletter. It is chalk chock full of great information for MySQL developers and users. Here are some examples of what is included in the current edition:

• DevZone Article: MySQL 5.5 Pluggable Authentication API - Interview with Georgi "Joro" Kodinov
• White Paper: What's New in MySQL 5.5
• White Paper: MySQL Replication - Enhancing Scalability and Availability with MySQL 5.5
• Planet MySQL Blog Posts

Read the entire newsletter.