Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 17 7 Older Entries

Displaying posts with tag: hacks (reset)

Quick and dirty concurrent operations from the shell
+1 Vote Up -0Vote Down

Let’s say that you want to measure something in your database, and for that you need several operations to happen in parallel. If you have a capable programming language at your disposal (Perl, Python, Ruby, PHP, or Java would fit the bill) you can code a test that sends several transactions in parallel.

But if all you have is the shell and the mysql client, things can be trickier. Today I needed such a parallel result, and I only had mysql and bash to accomplish the task.

In the shell, it’s easy to run a loop:

for N in $(seq 1 10)
do
mysql -h host1 -e "insert into sometable values($N)"
done

But this does run queries sequentially, and each session will open and close before the next one starts. Therefore there is no concurrency at all.
Then I thought that the method for





  [Read more...]
A few hacks to simulate mysqldump --ignore-database
+4 Vote Up -0Vote Down

A few days ago, Ronald Bradford asked for a mysqldump –ignore-database option.

As a workaround, he proposes:
mysqldump --databases `mysql --skip-column-names \
-e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') \
FROM information_schema.schemata WHERE schema_name \
NOT IN ('mysql','performance_schema','information_schema');" \
>` >/mysql/backup/rds2.sql

It's a clever solution, but unfortunately it only works if you have a handful of schemas. If your databases happens to have several dozens (or hundreds or thousands) of schemas (which is where you need this option more), then the output will be truncated to the length of group_concat_max_len (by default, 1024.)

There are two






  [Read more...]
21st century presentation technology at Percona Live
+2 Vote Up -0Vote Down

After 15 years of slide show technology, I thought that we need to change the way we do presentations. And since I am advocating radical changes, I will eat my own dog food and be the first to present a MySQL session using 3D technology.

Since watching Avatar a few years ago, I thought that using this technology would make my presentations truly amazing. However, two years ago a 3d projector was prohibitively expensive. Now, instead, it is affordable, and fits in my briefcase!

What I needed, though, was a compelling reason for using 3d vs. traditional presentations. And I found it. As I have mentioned recently, I am working with the coolest replication technology on earth. Explaining this technology

  [Read more...]
Reading results of SHOW statements, on server side
+5 Vote Up -0Vote Down

SHOW statements are show stoppers on server side. While clients can get a SHOW statement as a result set just as any normal SELECT, things are not as such on server side.

On server side, that is, from within MySQL itself, one cannot:

SELECT `Database` FROM (SHOW DATABASES);

One cannot:

DECLARE show_cursor CURSOR FOR SHOW TABLES;

One cannot:

SHOW TABLES INTO OUTFILE '/tmp/my_file.txt';

So it is impossible to get the results with a query; impossible to get the results from a stored routine; impossible to get the results by file reading...

Bwahaha! A hack!

For some SHOW

  [Read more...]
CodeBits - An event of competitive innovation
+1 Vote Up -0Vote Down
It was my pleasure and privilege to attend Codebits in 2009. As Roland Bouman says, its talk choice method is based on public voting, and therefore everyone cha have contribute to the schedule.But that is not the main reason for attending this extraordinary event. It is not just a conference. It's an innovation fest. For 1 and 1/2 days, it's a conference, where the speakers are encouraged to bring to their audience the most innovative and inspiring talks. In the afternoon of the second day, the event becomes a competition, where the teams that have registered will have 24 hours to bring a project to completion, and they have  [Read more...]
Gearman for MySQL
+2 Vote Up -0Vote Down


If you haven't yet heard about Gearman, it's time to have a look at it. Its distributed client server architecture are a perfect match for today's cloud oriented applications.
When talking about Gearman, much stress is often given on its scalability features, such as map/reduce and distributed loads. But Gearman has also a distinctive advantage in the feature department, because of its design that I like to describe as cooperation of the fittest.
While a traditional application is written entirely in a given language, or it must find ways of integrating multiple languages parts, Gearman encourages easy cooperation between parts written in different



  [Read more...]
Cleaning up Wordpress comment tables
+1 Vote Up -0Vote Down



In Montreal with Dups and Kaj, we were looking at a number of technical problems, and each one of you got something valuable from the meeting.
One of Kaj's problems was a collection of Wordpress blogs infested by spam. Kaj has done something already but the situation was critical. Before applying Akismet to his comments, he needed to cleanup the majority of the spam in same easy way.

It is not rocket science, really, but it needs some care in the implementation.
The comment






  [Read more...]
Another command line tip
+0 Vote Up -0Vote Down
Encouraged by Baron Schwartz tip on result set comparison, here are a few more, on the same vein.
First, you can send a result set to a file. Probably you will say "yeah, I know, using SELECT INTO OUTFILE". Correct. Except that you can't rewrite to an existing file, if you want to, and you will get a raw output, not the well formatted one that you usually see on the command line. For example:

mysql > select 1 into outfile '/tmp/f1.txt';
mysql > \! cat /tmp/f1.txt
1

mysql > select 1 into outfile '/tmp/f1.txt';
ERROR 1086 (HY000): File '/tmp/f1.txt' already exists

BTW, \! command is a handy shortcut for executing a shell command.
Let's see what










  [Read more...]
Adding a value to enum Column - A Dirty Hack
+0 Vote Up -0Vote Down

It is the first time I am going to write something serious after registering this domain. Probably I was too lazy to blog or I was working hard that I had no time to write blogs.

Any way I have decided to break the silence

Today I came across a problem where I had to add a new value to an enum column in a table with a few million rows. The traditional method of getting that done will take ages to finish and I could not afford to have a such a long window. Fortunately for me I was dealing with a MyISAM table and I had a dirty trick in mind ]:< .

I will list out a small example showing how I did it:

Suppose my table is

create table a( id int,
choice enum('agree','disagree')
)Engine=MyISAM

and I want add a



  [Read more...]
A quick usability hack with partitioning
+0 Vote Up -0Vote Down
A few days ago I was describing a common grievance when using partitions.
When you care at a table, like the following
CREATE TABLE t1 ( d DATE ) 
PARTITION by range (to_days(d))
(
partition p001 VALUES LESS THAN (to_days('2001-01-01'))
, partition p002 VALUES LESS THAN (to_days('2001-02-01'))
, partition p003 VALUES LESS THAN (to_days('2001-03-01'))
);

Then you have the problem of finding out the original values. SHOW CREATE TABLE doesn't help.
show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (to_days(d)) (PARTITION p001 VALUES LESS THAN (730851) ENGINE = MyISAM, PARTITION p002














  [Read more...]
Showing entries 1 to 10 of 17 7 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.