PlanetMysql.ru — информация о СУБД MySQL

Students in my training classes usually prefer to use some kind of visual editor for MySQL. Typically this would be the software they're using at work. Sometimes they just bring over their laptops with the software installed. Or they would use MySQL Workbench, which is what I usually have pre-installed on their desktops.

I see MySQL Workbench, SQLyog, Toad for MySQL, or several more.

I always humbly suggest they close down their software and open up a command line.

It isn't fancy. It may not even be convenient (especially on Windows, in my opinion). And repeating your last command with a minor modification requires a lot of key stroking. Or you would copy+paste from some text editor. Most students will give it a shot, then go back to their favorite editor.

Well, again and again I reach the same conclusion:

Visual editors are not as trustworthy as the command line.

Time and again students show me something on their editor. Behavior seems strange to me. Opening up a console shows a completely different picture.

Things like:

• The visual editor would open a new connection for every new query (oh, so the @user_defined_variable I've just assigned turns NULL, or the TEMPORARY TABLE disappears).
• The visual editor will only show 1,000 results, via LIMIT 0,1000. "But the same query runs so much faster on my machine!". Well, sure, a filesort of 1,000,000 rows that can satisfy the first 1,000 will quit early!
• The visual editor shows table definition graphically. "I didn't realize the index did(n't) cover this and that columns. I didn't realize it only covered first n characters of my VARCHAR.". That's because you can't beat SHOW CREATE TABLE, the definite table structure description.
• The visual editor allows for export/import/copy/transfer of tables and rows with just one click! "Why is it so complicated in the command line to purge 1,000,000 rows from a table?". Ummm, did you realize the visual editor would typically use a naive approach of doing everything in one huge transaction?
• The visual editor is smart. But sometimes you don't want smart. You just assume simple. I personally take great precaution with smart solutions. Luckily, with scripts you have so much greater control (i.e. command line options, "dry-run" mode, etc.) that I have greater confidence in them.

I do like it when a visual editor plays it both smart and safe, in such way that before doing its smart work it actually presents you with the query it's going to issue. Which is why I always considered MySQL Query Browser (now replaced by Workbench) to be the visual editor of choice in my classes.

But, at the end of the day, I strongly believe: if you don't know how to do it with command line, you can't really know how it's done.

PlanetMySQL Voting: Vote UP / Vote DOWN

Students in my training classes usually prefer to use some kind of visual editor for MySQL. Typically this would be the software they're using at work. Sometimes they just bring over their laptops with the software installed. Or they would use MySQL Workbench, which is what I usually have pre-installed on their desktops.

I see MySQL Workbench, SQLyog, Toad for MySQL, or several more.

I always humbly suggest they close down their software and open up a command line.

It isn't fancy. It may not even be convenient (especially on Windows, in my opinion). And repeating your last command with a minor modification requires a lot of key stroking. Or you would copy+paste from some text editor. Most students will give it a shot, then go back to their favorite editor.

Well, again and again I reach the same conclusion:

Visual editors are not as trustworthy as the command line.

Time and again students show me something on their editor. Behavior seems strange to me. Opening up a console shows a completely different picture.

Things like:

• The visual editor would open a new connection for every new query (oh, so the @user_defined_variable I've just assigned turns NULL, or the TEMPORARY TABLE disappears).
• The visual editor will only show 1,000 results, via LIMIT 0,1000. "But the same query runs so much faster on my machine!". Well, sure, a filesort of 1,000,000 rows that can satisfy the first 1,000 will quit early!
• The visual editor shows table definition graphically. "I didn't realize the index did(n't) cover this and that columns. I didn't realize it only covered first n characters of my VARCHAR.". That's because you can't beat SHOW CREATE TABLE, the definite table structure description.
• The visual editor allows for export/import/copy/transfer of tables and rows with just one click! "Why is it so complicated in the command line to purge 1,000,000 rows from a table?". Ummm, did you realize the visual editor would typically use a naive approach of doing everything in one huge transaction?
• The visual editor is smart. But sometimes you don't want smart. You just assume simple. I personally take great precaution with smart solutions. Luckily, with scripts you have so much greater control (i.e. command line options, "dry-run" mode, etc.) that I have greater confidence in them.

I do like it when a visual editor plays it both smart and safe, in such way that before doing its smart work it actually presents you with the query it's going to issue. Which is why I always considered MySQL Query Browser (now replaced by Workbench) to be the visual editor of choice in my classes.

But, at the end of the day, I strongly believe: if you don't know how to do it with command line, you can't really know how it's done.

PlanetMySQL Voting: Vote UP / Vote DOWN

Students in my training classes usually prefer to use some kind of visual editor for MySQL. Typically this would be the software they're using at work. Sometimes they just bring over their laptops with the software installed. Or they would use MySQL Workbench, which is what I usually have pre-installed on their desktops.

I see MySQL Workbench, SQLyog, Toad for MySQL, or several more.

I always humbly suggest they close down their software and open up a command line.

It isn't fancy. It may not even be convenient (especially on Windows, in my opinion). And repeating your last command with a minor modification requires a lot of key stroking. Or you would copy+paste from some text editor. Most students will give it a shot, then go back to their favorite editor.

Well, again and again I reach the same conclusion:

Visual editors are not as trustworthy as the command line.

Time and again students show me something on their editor. Behavior seems strange to me. Opening up a console shows a completely different picture.

Things like:

• The visual editor would open a new connection for every new query (oh, so the @user_defined_variable I've just assigned turns NULL, or the TEMPORARY TABLE disappears).
• The visual editor will only show 1,000 results, via LIMIT 0,1000. "But the same query runs so much faster on my machine!". Well, sure, a filesort of 1,000,000 rows that can satisfy the first 1,000 will quit early!
• The visual editor shows table definition graphically. "I didn't realize the index did(n't) cover this and that columns. I didn't realize it only covered first n characters of my VARCHAR.". That's because you can't beat SHOW CREATE TABLE, the definite table structure description.
• The visual editor allows for export/import/copy/transfer of tables and rows with just one click! "Why is it so complicated in the command line to purge 1,000,000 rows from a table?". Ummm, did you realize the visual editor would typically use a naive approach of doing everything in one huge transaction?
• The visual editor is smart. But sometimes you don't want smart. You just assume simple. I personally take great precaution with smart solutions. Luckily, with scripts you have so much greater control (i.e. command line options, "dry-run" mode, etc.) that I have greater confidence in them.

I do like it when a visual editor plays it both smart and safe, in such way that before doing its smart work it actually presents you with the query it's going to issue. Which is why I always considered MySQL Query Browser (now replaced by Workbench) to be the visual editor of choice in my classes.

But, at the end of the day, I strongly believe: if you don't know how to do it with command line, you can't really know how it's done.

PlanetMySQL Voting: Vote UP / Vote DOWN

Oracle Open World 2011 is approaching. MySQL is very well represented.Sheeri has put together a simple table of all the MySQL sessions at OOW, which is more handy than the Oracle schedule.I will be speaking in three sessions on Sunday, October 2nd. Sunday, 9am MySQL: Don't Be a Rookie Forever—Be in Command (Line)I have given this talk before, as a tutorial at the UC in 2010 and at FrOSCon one month ago. It is one of the most rewarding sessions ever. The attendee were very interested. This will be a short version of the tutorial. Sunday, 10:15am >MySQL: Jailbreaking MySQL Replication.This is related to my job at Continuent. It will a showcase of Tungsten Replicator, with quick examples of how to build replication clusters with multiple masters, multiple sources, chained clusters, parallel replication. Sunday, 2:45pm Bridging MySQL and Oracle Databases with Open Source Replication.In this session, together with Robert Hodges, I will show how you can replicate from MySQL to Oracle and PostgreSQl using Tungsten Replicator.

There are 47 MySQL sessions in total. You can see them in Technocation summary or get the Oracle focus on mysql pdf.There are huge expo halls at OOW. Among them, there is also MySQL. The MySQL Community booth, manned by volunteers, is at Moscone West, Level 2 Lobby. Other MySQL booths are listed in the Technocation summary.On the social side, Oracle ACEs will have a dinner on Sunday evening, and MySQL Oracle ACEs will have another gathering on Monday evening.On Tuesday, October 4th, there is a MySQL Community reception. It's free. You don't need a OOW pass to attend, but registration is required.
PlanetMySQL Voting: Vote UP / Vote DOWN

I have been doing quite a lot of benchmarking recently.
I needed to find a safe way of measuring the time spend by the database doing a long task, like catching up on a huge backlog of accumulated replication updates. The problem with measuring this event is that I can record when it starts, but I can't easily detect when it finishes. My initial approach was to monitor the database and count the tables rows to see when the task was done, but I ended up affecting the task performance with my additional queries. So I thought of another method.
Since I had control on what was sent from the master to the slave, I used the following:
The initial time is calculated as the minimum creation time of the databases that I know are created during the exercise. Let's say that I had 5 databases named from db1 to db5:

set @START = (select min(create_time) from information_schema.tables where table_schema like "db%")

Then, to make sure that I catch the exact moment that the task is finished, I added to the master a command for each database:

create table last_table db1.(i int);
create table last_table db2.(i int);
create table last_table db3.(i int);
create table last_table db4.(i int);
create table last_table db5.(i int);

To know if the task is done, I query the database as follows:

select count(*) from information_schema.tables where table_schema like "db%" and table_name="last_table";

If the count is less than 5 (the number of databases that were in my binary logs), I wait more.
Finally, when the count matches the expected one, I get the end time:

set @END = (select max(create_time) from information_schema.tables where table_schema like "db%" and table_name="last_table"');

Now I have two values, @START, and @END

select timediff(@END,@START) as elapsed;
+----------+
| elapsed  |
+----------+
| 00:09:44 |
+----------+

It does not matter if I query the database immediately, or hour after coming back from my errands. Using the table creation times makes sure that I get a clean start and finish time.
I put all the above in a script, and I can check the elapsed time without fear of mistakes.
PlanetMySQL Voting: Vote UP / Vote DOWN

I have been doing quite a lot of benchmarking recently.
I needed to find a safe way of measuring the time spend by the database doing a long task, like catching up on a huge backlog of accumulated replication updates. The problem with measuring this event is that I can record when it starts, but I can't easily detect when it finishes. My initial approach was to monitor the database and count the tables rows to see when the task was done, but I ended up affecting the task performance with my additional queries. So I thought of another method.
Since I had control on what was sent from the master to the slave, I used the following:
The initial time is calculated as the minimum creation time of the databases that I know are created during the exercise. Let's say that I had 5 databases named from db1 to db5:

set @START = (select min(create_time) from information_schema.tables where table_schema like "db%")

Then, to make sure that I catch the exact moment that the task is finished, I added to the master a command for each database:

create table last_table db1.(i int);
create table last_table db2.(i int);
create table last_table db3.(i int);
create table last_table db4.(i int);
create table last_table db5.(i int);

To know if the task is done, I query the database as follows:

select count(*) from information_schema.tables where table_schema like "db%" and table_name="last_table";

If the count is less than 5 (the number of databases that were in my binary logs), I wait more.
Finally, when the count matches the expected one, I get the end time:

set @END = (select max(create_time) from information_schema.tables where table_schema like "db%" and table_name="last_table"');

Now I have two values, @START, and @END

select timediff(@END,@START) as elapsed;
+----------+
| elapsed  |
+----------+
| 00:09:44 |
+----------+

It does not matter if I query the database immediately, or hour after coming back from my errands. Using the table creation times makes sure that I get a clean start and finish time.
I put all the above in a script, and I can check the elapsed time without fear of mistakes.
PlanetMySQL Voting: Vote UP / Vote DOWN

I am writing this blog post with Vim, my favorite editor, instead of using the online editor offered by blogger. And I am uploading this post to my Blogger account using Google CL a tool that lets you use Google services from the command line. I am a command line geek, and as soon as I saw the announcement, I installed it in my laptop. The mere fact that you are reading this blog post shows that it works.

GoogleCL is an apparently simple application. If you install it on Mac using macports you realize how many dependencies it has and how much complexity it gives under the hood.
Using an easy to understand syntax, it allows you to access your blog, pictures, calendar, contacts, videos, and online documents at your fingertips.
For example, let's query my blog for partitioning:

$  google blogger --blog="The Data Charmer" --title=partitioning list "title,url"

Hmm. No results. The manual doesn't help much, but something happened during this query. The first thing ist that I was asked to authorize the script to access my blog, and that was done by activating a key that I got in the command line. So far, so good. The second thing was a message informing me that a default configuration file was created in my home directory. Looking at that file, I saw an option saying "regex = True". Aha! So the title supports regular expressions. Let's try:

$  google blogger --blog="The Data Charmer" --title=".*partitioning" list "title"
Holiday gift - A deep look at MySQL 5.5 partitioning enhancements
The partition helper - Improving usability with MySQL 5.1 partitioning
A quick usability hack with partitioning
MySQL 5.1  Improving ARCHIVE performance with partitioning

OK. This gives me everything with the word "partitioning" in the title. But I know that some titles are missing. Comparing with the results that I get online, I see that the titles where "partitioning" is capitalized are not reported. So the search is case sensitive. What I need to do is to tell the regular expression that I want a case insensitive search. Fortunately, I know how to speak regular expressions. Let's try again.

$  google blogger --blog="The Data Charmer" --title="(?i).*partitioning.*" list "title"
Holiday gift - A deep look at MySQL 5.5 partitioning enhancements
Partitioning with non integer values using triggers
Tutorial on Partitioning at the MySQL Users Conference 2009
The partition helper - Improving usability with MySQL 5.1 partitioning
A quick usability hack with partitioning
MySQL 5.1  Improving ARCHIVE performance with partitioning

Now I feel confident enough to do some changes to my online contents.
To create this blog post, I used some of googlecl capabilities. After I created an image, I uploaded it to my Picasa album using this command:

$google picasa post -n "Blogger Pictures" -t googlecl ~/Desktop/google_cl.png 

Then I asked Picasa to give me the URL of the image:

$ google picasa list -n "Blogger Pictures" --query googlecl title,url_direct 
google_cl.png,http://lh6.ggpht.com/_gVfZHGgf5LA/TBzjaKiJJvI/AAAAAAAAA74/dthDDhybsmc/google_cl.jpg

And then I inserted that URL in this blog post. Finally, I uploaded the blog post with this command:

google blogger --blog="The Data Charmer" --draft --title "Welcome googleCL" --tags="google,mysql,partitioning,command line,blogging" post ~/blog/welcome_googlecl.html

(Now writing online) And after I checked that the post was looking as I wanted it, I hit the "PUBLICH POST" button.
Welcome, GoogleCL!
PlanetMySQL Voting: Vote UP / Vote DOWN

As everyone knows, I am a command line guy. I am very much comfortable with the shell prompt and the command line SQL client. I do most of my work that way, and I am very much productive.
However, there comes a time when even for a command line enthusiast a GUI can be helpful.
Here comes the latest MySQL Workbench 5.2.
There are two areas where I feel that WB can give me a hand:
The first is when looking at tables that contain BLOB columns. Sure I can deal with them at the command line, but this editor makes my life easier.

When a column contains a BLOB, you can open the field viewer.

At first glance, this is nothing more than what the command line could provide. I could get output in hexadecimal format quite easily in any client. But, looking more closely, there is a tab labeled "image" that is not as easy to come by at the command line prompt.

And there is Mike Hillyer, the main author of the Sakila database, who has stored his own image in the staff table for future generations. If you stick to the command line, you may easily miss this piece of self advertising.

The second area where I like having MySQL Workbench is when I need to change my configuration file with less than common options. Since no human (apart from Sheeri, perhaps) can remember all the options, I usually need to search the manual.

In WB, instead, I can edit the options file with the GUI, without need of remembering the exact names and spelling of the items I need.
Now, if I couple the above issues with the notion that MySQL Workbench is A Useful Tool to Centrally Manage Many MySQL Instances, I think that every command line enthusiast should give this tool a try.
Lastly, I should mention that Workbench 5.2 is becoming quite popular, as the downloads map shows.

PlanetMySQL Voting: Vote UP / Vote DOWN

For all of those linux users out there that have moved over to, or tried out, Solaris10 or OpenSolaris because they heard the tales of how MySQL is faster on Solaris… or perhaps you wanted to learn how to use Sol10 for the great features of Zones or the ZFS filesystem? Regardless of why you’re on it you are probably wondering why Linux has colored output of filenames and directories but Solaris does not. The question of ‘why?’ isn’t important, but how to enable colors is. It’s very simple, and here’s how I fixed it. This is a result of digging through multiple semi-related links on Google.

1. Download all packages from SunFreeware.com
   • dependency: libintl-3.4.0-sol10-x86-local
   • dependency: libiconv-1.13.1-sol10-x86-local
   • dependency: gmp-4.2.1-sol10-x86-local
   • dependency: gcc-3.4.6-sol10-x86-local or libgcc-3.4.6-sol10-x86-local depending on your system needs
   • coreutils-8.4-sol10-x86-local
2. Install ‘coreutils’ dependency packages using the command “pkgadd -d [package_name]
3. Install ‘coreutils’ packages using the command “pkgadd -d coreutils-8.4-sol10-x86-local
4. Enable color aliases in your rc file: “alias ls=’/usr/local/bin/ls –color=auto’”

PlanetMySQL Voting: Vote UP / Vote DOWN