MySQL 中文网

What is confoo? It is the sequel to the PHP Quebéc Conference (2003 – 2009). This year PHP Quebec decided to team up with Montreal-Python, W3Quebéc and OWASP Montréal to produce confoo.

And now, on to Mark Pilgrim of Google speaking on HTML5.

Timeline
1991 – HTML 1
1994 – HTML 2
1995 – Netscape discovers web, ruins it
1996 – CSS1 + JavaScript
1996 – Microsoft discovers web, ruins it
1997 – HTML4 + EMCAScript1
1998 – CSS2 + EMCAScript2 + DOM1
2000 – XHTML1 + EMCAScript3 + DOM2
2001 – XHTML 1.1
[long break!]
2009 – HTML 5 + ECMA5 + CSS 2.1

HTML5 is not a spec, it’s a marketing term. It’s really HTML5 + CSS3 + JavaScript.

IsHTML5ReadyYet.com and IsHTML5Ready.com are both real websites that give different answers to the question “is HTML 5 ready?”

Semantics
HTML started as a semantic language (until Netscape came along).

New elements (html tags) that do not do anything – they are for semantic use only:

Instead of “div class=_____” use these tags….for example:

Caveat: This doesn’t work in IE but there is a workaround…..

This can help blind people navigate better….and bots too!

“Google is just another blind web user with 7 million friends”

Forms
Web forms 2.0
To make a slider from 0-50:

To use autofocus:

(works in 3 browsers)

Talking about blind users again: “Focus tracking is VERY important if you can’t see. You really need to know where on the page you are, if you start typing what will happen.”

Placeholder text — in a text box, that light text that goes away when you click:

(works in 2 browsers)

New input types
These are semantic types, do different things in different browsers

For all the inputs HTML5 supports and which browsers support them (Opera is leading the way) search for “HTML5 input support”

Accessibility
ARIA = “accessible rich internet applications”. Alt-text is technology that’s long behind. ARIA does stuff like making tree views accessible. For example, right now with a tree view you have to tab through each item, which is a pain. With code like this:

….keyboard users can tab to the treeview itself, then use arrow keys to navigate and spacebar to select. This makes selecting an item at the end of a tree view much easier, and also makes it easy to move beyond the tree view without having to press Tab a million times.

Use your favorite search engine for “ARIA accessibility” to learn more.

CSS
Mark threw this image up on the screen:

(image from http://www.zazzle.com/stevenfrank – on that site you can buy this coffee mug or a T-shirt with the design)

Web fonts finally work in CSS3 – you can use more than Times, Courier, Arial, and occasionally Helvetica. This works EVERYWHERE – Chrome, IE, Firefox, Opera, Safari, etc. Well, it’s true that they all use it, but they all have different fonts they support. Read Bulletproof font face for tips on how to get the font you want no matter what browser is used (yes, even IE).

Opacity is easy [author's note - it's just the "opacity" element, see examples at http://www.css3.info/preview/opacity/].

Rounded corners are EASY – Mark’s slide passed too fast for me, so I grabbed an example from http://24ways.org/2006/rounded-corner-boxes-the-css3-way:

Gradients are easy [author's note -- looks like you need webkit, there's examples at http://gradients.glrzad.com/]

To test CSS3 stuff, use www.css3please.com – “This element will receive inline changes as you edit the CSS rules on the left.”

[Author's note -- while searching I found http://www.webappers.com/2009/08/10/70-must-have-css3-and-html5-tutorials-and-resources/ which is definitely a "must have".]

Canvas
A canvas is a blank slate where you can draw whatever you want, use the canvas tag and id, width and height attributes, everything else is javascript. Pretty awesome. [Author's note -- Mark had examples but I did not have time to capture them. I did find a nice tutorial at https://developer.mozilla.org/en/Canvas_tutorial.]

Multimedia
Video with no flash! YouTube has HTML5 integration. Here’s sample code of how to do movies in HTML5:

Multimedia is in the DOM and responds to CSS effects, such as reflection:

(this code might be wrong, the slide flipped fast)

Of course the problem — codecs. Right now, .ogv and .mp4 (h264).

Audio inline too, same problem — only .oga and .mp3:

Geolocation
IsGeolocationPartofHTML5.com is a real site, go to it to get the answer.
Geolocation demos — very much the same, find your location and display it. Simple but cool.

Cache manifest
Get everything you need for offline usage…

search for “google for mobile HTML5 series” – good series of articles on using this stuff.

HTML 5 has much more
Local storage
Web workers
Web sockets (2way connections, like raw tcp/ip cxns over the web)
3D canvas (webgl)
Microdata (enhanced semantics)
Desktop notifications
Drag and Drop

Learn more:
whatwg.org/html5
diveintohtml5.org

1. Create the new table on the master. The new table might use MyISAM without indexes initially to make the insert as fast as possible and reduce the load on InnoDB.
2. Run set sql_log_bin=0 as what follows should not be written to the binlog
3. Run start transaction with consistent innodb snapshot to start an Innodb transaction and get current binlog offset of the master
4. Run insert into new_table select * from original_table on the master. Alas, this will get a transaction duration read lock on every row in original_table unless you use row based replication or hack InnoDB or set innodb_locks_unsafe_for_binlog.
5. Convert new_table to InnoDB and create indexes on it
6. Replay changes from the binlogs after the point in time recorded in step #3. This should extract changes to original_table and replay them against new_table.

Together with Berndt I’ll be presenting on the new features in MySQL Cluster 7.1 at this year’s MySQL Cluster User Conference – Santa Clara, on April 12th. If you’re interested in using MySQL Cluster but aren’t sure how to get started (or you’ve used it but would like some tips) then this is a great opportunity. Check out the presentation description.

If you register by 15 March then you get the early-bird price and if you use this ‘friend of a speaker’ code then you get an additional 25% off: mys10fsp

If you missed the recent webinar on running MySQL Cluster on Windows then you can watch/listen to the replay at http://www.mysql.com/news-and-events/on-demand-webinars/display-od-517.html

Working on mycheckpoint, I have the intention of adding custom monitoring. That is, letting the user define things to monitor. I have my own thoughts, I would be grateful to get more input!

Monitoring for the number of SELECT statements per second, InnoDB locks, slave replication lag etc. is very important, and monitoring utilities provide with this information. But what does that tell the end user? Not much.

The experienced DBA may gain a lot. The user would be more interested in completely other kind of information. In between, some information is relevant to both.

Say we were managing an on-line store. We want to monitor the health of the database. But the health of the database is inseparable from the health of the application. I mean, having little to no disk usage is fine, unless… something is wrong with the application, which leads to no new purchases.

And so a user would be interested in monitoring the number of purchases per hour, or the time passed since last successful purchase. This kind of data can only be generated by a user’s specific query. Looking at the charts, the user would then feel safer and confident in the wellness of his store app.

But let’s dig further. We want the store’s website to provide with good response. In particular, the query which returns the items in a customer’s cart must react quickly. Our user would not only want to see that purchases get along, but also that page load times (as in our example) are quick for those critical parts. And so a user should be able to monitor the time it took to execute a given query.

It can be of further interest to know how many times per second a given query is executed. This part is not easily done on the server side, and requires the user’s cooperation (or else we must analyze the general log, sniff, or set up a proxy). If the user is willing, she can log to some table each time she executes a certain query. Then we’re back to monitoring a regular table, as with the first example.

It is also possible to monitor for a query’s execution plan. Is it full scan? How many rows are expected? But given that we can monitor the time it took to execute a query, I’m not sure this is useful. If everything runs fast enough — who cares about how it executes?

Some of the above can be monitored on an altogether higher level: if  we’re talking about some web application, then we can use our Apache logs to determine load time for pages, or number of requests to our “cart items” page. But not always do we work with web servers, and we may be interested in checking the specific queries behind the scenes.

Custom monitoring can include:

• User defined queries (number of concurrent visitors; count of successful operations per second; number of rows per given table or condition; …)
• Execution time for user defined queries (time it takes to return cart items; find rows matching condition; sort a table; …)
• Number of executions for a given query, per second.

I intend to incorporate the above into mycheckpoint as part of its standard monitoring scheme.

Please share your thought below.

I'll be presenting "The Thinking Person's Guide to Data Warehouse Design" at the upcoming MySQL User conference. While a lot of people think that bad SQL code is the #1 wrecking ball of data warehouses and marts, the fact is that poor database design is the first cause of both downtime and bad performance. In my presentation, I'll do my best to show how up-front worRead More...
PlanetMySQL Voting: Vote UP / Vote DOWN

Video fragments of my session posted at the end — read on.

I arrived at Omni Mandalay Hotel on Sunday evening with Dan Norris. I was flying through Chicago and it turned out that Dan was on the same flight and only few rows behind me. Small world.

Preparations for the conference were very chaotic on my part and, of course, I didn’t have either of my presentations ready. I was very stressed and getting sick as well — it looked like a complete disaster waiting to happen. I’d like to say that I was feeling like Doug Burns as he often managed to get sick just before a conference. Of course, I worked on my slides for the last few days as well as on the flight and presentation was slowly getting there but boy was I tired!

I quickly said hello to the crowd in the bar on the way to my room and rushed away to do some more damage to my slides. And then I had a brilliant idea — I could still see one of my best mates and do something good about my presentation! I asked Doug if he was interested in the preview (he probably wasn’t interested but he couldn’t say it to me) especially that my session wasn’t on his original agenda. Of course, that would mean that he had to leave a bunch of other good friends and spend some time tete-a-tete. Knowing Doug, this is some of the hardest thing to ask from him but it shows how good of a friend he is! (Plus, everyone thinks that he is anti-social anyway. Shhhh!)

Doug has made my day — while he provided lots of ideas and feedback on few things that I was lucking, he generally approved the idea and confirmed that it wasn’t totally crazy. I guess that was all I needed back then and Doug knew how nervous I was about it. (Thanks mate!)

So I called Sunday a day very early and went to bed before midnight. I really needed some sleep. Woken up by the alarm at 5AM (I woke up few times during the night looking at the clock — making sure I didn’t sleep through) and slides were ready just before lunch. I even managed to do a test run and it took 65 minutes — a wee bit too long for one hour session. But it was good test and I knew I had to be just a bit more concise in few parts.

Mi morning was very productive. Unfortunately, I missed the opening keynote from Tom Kyte. Such a pity! If what Doug wrote is true, Tom was talking about the mistakes we make *because* of our experience and our assumptions. This was exactly one of the points I was making in my Battle Against Any Guess — experience is danger. I wish I could see Tom’s example. Oh well, maybe another time.

I managed to attend half of the Richard Foote’s session on indexes but my mind was far away — with my own slides. Though, I did manage to focus on bitmap indexes part and the myth of bitmap indexes not working well for columns with high cardinality. Very interesting conclusions. I’m still wondering how much overhead updates will do to such bitmap index.

After lunch, it was my turn. I ordered few copies of the latest OakTable book — Expert Oracle Practices: Oracle Database Administration from the Oak Table — that I co-authored with the bunch of other Oakies. I contributed chapter 1 in the book titled just like my presentation — Battle Against Any Guess. The plan was to give a copy away during the presentation and do a draw for another one at the end of the session. I was so nervous that I forgot about it until the end of the session so I just did a draw for two copies. The lucky winners were Lynn-Georgia Tesch and Surendra Anchula. Congratulations! For the rest of you who left the contact details — please stay tuned and we’ll organize few things online.

Now the main topic of this post — my presentation. What’s unusual about this session is that it’s not some technical stuff that I usually do but a more conceptual and motivational talk. Could I pull it off? Well, I think it went fairly well in general even though I did identify few rough places and my lack of English language mastering. Might need to work a little bit more on the flow of the presentation.

We had quite a few good laughs. Later, people in the next hall were asking about it and Dan was making the jokes on the stage so it must have been loud. Anyway, I think nobody fell asleep and I managed to get people thinking about the topic. I received many “thank you” notes yesterday and compliments on a good session so by the end of the day I was more and more pleased. Thanks everyone for attending and especially big thanks to those of you who brought to my attention examples from their own battles. If you have more to discuss — contact me by email (my last name) {at} pythian.com.

Thanks to Marco Gralike for recording some fragments and sharing them. I think he has more to come.

This is the introductory couple minutes. You can definitely notice how nervous I am starting on the stage:

Solving the wrong problem example:

That’s all for now. Stay tuned — more to come.

Video fragments of my session posted at the end — read on.

I arrived at Omni Mandalay Hotel on Sunday evening with Dan Norris. I was flying through Chicago and it turned out that Dan was on the same flight and only few rows behind me. Small world.

Preparations for the conference were very chaotic on my part and, of course, I didn’t have either of my presentations ready. I was very stressed and getting sick as well — it looked like a complete disaster waiting to happen. I’d like to say that I was feeling like Doug Burns as he often managed to get sick just before a conference. Of course, I worked on my slides for the last few days as well as on the flight and presentation was slowly getting there but boy was I tired!

I quickly said hello to the crowd in the bar on the way to my room and rushed away to do some more damage to my slides. And then I had a brilliant idea — I could still see one of my best mates and do something good about my presentation! I asked Doug if he was interested in the preview (he probably wasn’t interested but he couldn’t say it to me) especially that my session wasn’t on his original agenda. Of course, that would mean that he had to leave a bunch of other good friends and spend some time tete-a-tete. Knowing Doug, this is some of the hardest thing to ask from him but it shows how good of a friend he is! (Plus, everyone thinks that he is anti-social anyway. Shhhh!)

Doug has made my day — while he provided lots of ideas and feedback on few things that I was lucking, he generally approved the idea and confirmed that it wasn’t totally crazy. I guess that was all I needed back then and Doug knew how nervous I was about it. (Thanks mate!)

So I called Sunday a day very early and went to bed before midnight. I really needed some sleep. Woken up by the alarm at 5AM (I woke up few times during the night looking at the clock — making sure I didn’t sleep through) and slides were ready just before lunch. I even managed to do a test run and it took 65 minutes — a wee bit too long for one hour session. But it was good test and I knew I had to be just a bit more concise in few parts.

Mi morning was very productive. Unfortunately, I missed the opening keynote from Tom Kyte. Such a pity! If what Doug wrote is true, Tom was talking about the mistakes we make *because* of our experience and our assumptions. This was exactly one of the points I was making in my Battle Against Any Guess — experience is danger. I wish I could see Tom’s example. Oh well, maybe another time.

I managed to attend half of the Richard Foote’s session on indexes but my mind was far away — with my own slides. Though, I did manage to focus on bitmap indexes part and the myth of bitmap indexes not working well for columns with high cardinality. Very interesting conclusions. I’m still wondering how much overhead updates will do to such bitmap index.

After lunch, it was my turn. I ordered few copies of the latest OakTable book — Expert Oracle Practices: Oracle Database Administration from the Oak Table — that I co-authored with the bunch of other Oakies. I contributed chapter 1 in the book titled just like my presentation — Battle Against Any Guess. The plan was to give a copy away during the presentation and do a draw for another one at the end of the session. I was so nervous that I forgot about it until the end of the session so I just did a draw for two copies. The lucky winners were Lynn-Georgia Tesch and Surendra Anchula. Congratulations! For the rest of you who left the contact details — please stay tuned and we’ll organize few things online.

Now the main topic of this post — my presentation. What’s unusual about this session is that it’s not some technical stuff that I usually do but a more conceptual and motivational talk. Could I pull it off? Well, I think it went fairly well in general even though I did identify few rough places and my lack of English language mastering. Might need to work a little bit more on the flow of the presentation.

We had quite a few good laughs. Later, people in the next hall were asking about it and Dan was making the jokes on the stage so it must have been loud. Anyway, I think nobody fell asleep and I managed to get people thinking about the topic. I received many “thank you” notes yesterday and compliments on a good session so by the end of the day I was more and more pleased. Thanks everyone for attending and especially big thanks to those of you who brought to my attention examples from their own battles. If you have more to discuss — contact me by email (my last name) {at} pythian.com.

Thanks to Marco Gralike for recording some fragments and sharing them. I think he has more to come.

This is the introductory couple minutes. You can definitely notice how nervous I am starting on the stage:

Solving the wrong problem example:

That’s all for now. Stay tuned — more to come.

As part of my upcoming FREE my.cnf check advice I first need to ask people to provide the current MySQL configuration file commonly found as a file named my.cnf

If only that question was easy to answer!

MySQL will by default use at least one configuration file from the following defaults. MySQL also uses a cascade approach for configuration files. When you have multiple files in the appropriate paths you can see unexpected behavior when you override certain values in different files.

You can however for example specify –no-defaults to use no configuration file, or add options to your command line execution, so even looking at all configuration files is no guarantee of your operating configuration.

However for most environments, these complexities do not exist.

By default and on single instance MySQL servers you are most likely to find this file called my.cnf and found at:

• /etc/my.cnf
• /etc/mysql/my.cnf

These are known as the global options files.

MySQL has both instance specific and user specific locations. For the inclusion of an instance specific file, the location is:

• $MYSQL_HOME/my.cnf

where MYSQL_HOME is a defined environment variable. Historical MySQL versions also looked at [datadir]/my.cnf however I am unaware if this is applicable in 5.x versions.

You can also specific options on a per user basis for default inclusion. These are found at:

• $HOME/.my.cnf

Ubuntu for example also provides an ability to add options via an include directory.

While you may have these default files, you may elect to start mysql with a specific configuration file as specified by –defaults-file. This option will override all global/instance/user locations and use just this configuration file. You can also specify additional configuration that supplements and not overrides the default with –defaults-extra-file.

Again, assuming the default names you can perform a brute force check with:

This is actually worthwhile, especially if you find a /root/.my.cnf file which is default MySQL settings for the Operating System ‘root’ user.

MySQL by default provides a number of recommended files however these are generally outdated especially for newer hardware. These files include my-huge.cnf, my-large.cnf, my-medium.cnf, my-small.cnf and my-innodb-heavy-4G.cnf. Don’t assume replacing your configuration with one of these files will make your system perform better.

MySQL made some attempt to correct these and at least some very poor defaults with MySQL 5.4 however I am unsure what’s in MySQL 5.5

While several commands can help with identifying your configuration files and print defaults etc, it’s also possible to change your configuration at runtime. It’s possible that these changes are not reflected in your configuration files and pose an additional mismatch.

• Using Option Files
• Server Command Options

Data comparison is a difficult and resource-intensive process. For convenience, this process can be divided into several steps.
First, you should compare tables from one database on one server with the database on the other server. You should choose columns for data comparison, and also choose a column that will be a comparison key.
The next step is to choose all data from these tables or some specified part of the data.
The third and the most important step is comparison of the two tables by the selected comparison key itself. During this process the status of each record is set to “only in source”, “only in target”, “different”, or “equal”.
The final steps of the data comparison process are including records to the synchronization and synchronization itself. During these steps records needed for synchronization are chosen, update script is created, and after that the script is executed.
You can read a detailed description of the comparison process here.

Now let’s look at the third step (data comparison) thoroughly.

There are several ways of data comparison that differ only by the side where data comparison is going to be performed – on the server side or on the client PC.

Data comparison on the server side is performed using the resources of the server.
The algorithm of comparison is the following:
1. For each record of each of the two tables its checksum is calculated;
2. Then the checksum of every record from one table is compared to the checksum of the corresponding record from another table and conclusion if the records are equal or different is made;
3. The comparison result is stored in a temporary table on the server.

Performance indicators:
1. The speed of data comparison directly depends on the server capacity and occupancy;
2. The maximal size of database for comparison is limited by the resources of the server itself.

Advantages:
1. There is no need to transfer large amounts of data for comparison to the client PC through network. This way we save network traffic;
2. The speed of comparison does not depend on the client PC resources;
3. Ability to compare blob data of any size.

Disadvantages:
1. Because of the record checksum calculation algorithm in some cases different data can result in equal checksum, and instead of the expected “different” status the “equal” status will be received;
2. There is no flexibility in the synchronization and comparison options usage;
3. There is no possibility to view records differences and exclude a part of the records from the synchronization manually;
4. During the synchronization script creation you should perform data transfer from the server to the client side;
5. The control checksum calculation of a large amount of records consumes all server resources;
6. One should provide extra space on the server for the comparison results storage in the temporary table.

As we can see, this way of comparison has more disadvantages than advantages, that’s why this way is rarely used.

Data comparison on the client PC is performed using the client machine resources, and the server only provides data for comparison. In turn, this way of comparison can be divided into several more ways depending on the way how comparison information will be stored.

Comparing Data on local PC when comparison result is stored in RAM.
The comparison algorithm is the following:
1. Server passes all data from both tables to the local PC;
2. Every record of every table is placed to RAM and is compared without checksum calculation;
3. If a record gets “only in source”, “only in target” or “equal” status, only comparison key is stored in RAM. If records get “different” status, they are placed to RAM for storage completely.

Performance indicators:
1. The speed of data comparison directly depends on the client PC resources and on the speed of data transfer through the network;
2. Maximum size of the database for comparison is limited by the size of RAM on the client PC, and this maximum size also depends on the degree to which the databases that should be compared are different – the smaller is the amount of different records, the larger databases can be compared.

Advantages:
1. Minimal server occupancy – server performs only simple data selection;
2. The simplest algorithm of data comparison because records are sorted on the client side;
3. Flexibility in the comparison options usage;
4. Minimal size of the comparison data store;
5. Status of every record for any data is always correct.

Disadvantages:
1. To view records with “only in source”, “only in target”, or “equal” status an extra data selection is needed;
2. An extra data selection is needed to create a synchronization script;
3. OutOfMemory Exception may be arisen when there are a lot of differences in data in databases;
4. Possibility to compare blob data only of the size that equals to the size of free RAM.

This way of comparison is implemented in dbForge Data Compare for SQL Server v1.10, dbForge Data Compare for MySQL v2.00 and allows to compare databases of any size if data in these databases does not differ a lot.

Comparing Data on local PC when comparison result is stored as a cashed file on the disk.
The algorithm of comparison is the following:
The server passes all data from both tables sorted by comparison key to a local PC. Data is read by bytes, compared without checksum calculation and written to a file on the disk.

Performance indicators:
1. The speed of data comparison directly depends on the client PC resources and on the speed of data transferring through the network;
2. The maximum size of a database to compare is limited by free disk space and does not depend on the degree of data difference in databases.

Advantages:
1. Medium server occupancy – server performs data sorting and selection;
2. To view records and synchronization script creation extra requests to the server are not necessary;
3. The status for every record is always correct for any data;
4. Possibility to compare blob data of the size equal to the size of free space available on the disk.

Disadvantages:
1. Difficult algorithm of data comparison for the records comparison key of which is of the string data type;
2. Difficult algorithm of disk cash for temporary information storage creation.

We can see that in this case the only disadvantage of this way of comparison is the difficulty of implementation. There are more advantages than in the ways of comparison listed above. That’s why this way of comparison will be used in the new version of dbForge Data Compare for SQL Server v2.00 and dbForge Data Compare for MySQL v3.00 for data comparison.

Few weeks ago I was at FOSDEM.  It was really amazing experience. I meet many interesting people, learned quite some thing and I returned full of enthusiasm. Open Source events are really great.

But all the fun wasn't over even after the FOSDEM. I spent few more days in Bruxelles attending MySQL packagers meeting organized by SUN/Oracle. We spent quite some time talking to each other. We learned what MySQL people are doing and how. And they learned how do we deal with MySQL and what is troubling us. And many good things will come from this.

First but certainly not last of them is about to appear now. One very interesting thing we learned at meeting was MySQL release policy. What openSUSE and Ubuntu and maybe some others are doing is that after release date there is generaly no version updates allowed. We are only fixing serious bugs and security related issues. It takes quite some work. What we learned is that new releases in stable branch of MySQL are in fact maintanance updates. If you update from 5.1.43 to 5.1.44 you wouldn't get any new features. All you will get are bugfixes. And only bugfixes of serious or security related issues. Does it sound familiar? Yes it is the same thing we are doing! So I discussed it with our maintanance team. And we came to the conclusion that we want to give our users all serious fixes. Not only these few selected. And the best way to do it is to use maintanance updates provided by MySQL people themself. I'm not saying that I don't have enough confidence to play with MySQL sources, but I think that MySQL people can do it better

Yes, you are guessing right. What I'm trying to say is that we are going to update MySQL to the latest available version. This means 5.1.44 for openSUSE 11.2 and 5.0.90 for older openSUSE. We will start with 11.2 as version gap is smaller there and if everything will proceed smoothly, we will continue with 11.1 and 11.0. For 11.2 you can help by testing update. Currently 5.1.44 update is prepared for 11.2 in server: database: STABLE and I'm running some final tests. If you want, you can try it too (not recomended on production servers yet) and if you'll find any problems, please report them before it will hit official updates.

Remember, this is just the beginning. I've got some bigger plans regarding MySQL in 11.3

Changes (as compared to 8.22) include:

Features:
* Added an option to define a ‘color code’ for a connection. The color will be used as background color in the Object Browser.
* A Query Builder session can now be saved and resumed.
* In Query Builder a table alias can be defined for any table by double-clicking the title bar of the table symbol.
* In RESULT tab results can now be retrieved page-wise. This is ON as default with this build with a defined LIMIT of 1000 rows. For a specific query user can change and for this specific query the setting is persistent across sessions. Also read ‘miscellaneous’ paragraph below.
* Added a context menu to Query Builder canvas.

Bug Fixes:
* Deleting a user would leave non-global privileges orphaned in the ‘mysql’ database. Now we use DELETE USER syntax if server supports.
* Also using EDIT USER dialogue to change host or user specifier for a user would not move non-global privileges. We have split the old ALTER USER dialogue into two: a EDIT USER and RENAME USER dialogue. The latter will use RENAME USER syntax if server supports.
* On Wine Data Sync could generate a malformed XML-string what would case Data Sync to abort.
* Fixed an issue where SSH-tunneling failed with public/private key authentication. Technically the fix is in the PLINK binary shipped with SQLyog.
* SJA failed to send notification mails if Yahoo SMTP servers were used. Note that the fix disables encryption option with Yahoo SMTP servers – but it won’t work anyway due to a non-standard SMTP implementation server-side.
* When importing data from a Universe ODBC-source string data could be truncated.
* The fix in 8.22 for the issue that horizontal scrollbar in GRID would sometime not appear was not complete. It could still happen.
* SQLyog will now trim trailing whitespaces in Connection Manager and Create object dialogs to avoid MySQL Errors..
* Opening a file from ‘recent files’ list could crash SQLyog if a Query Builder or Schema Designer tab was selected and the file specified was not a valid XML file for that tab. This bug was introduced in beta 1.
* When calling a Stored Procedure with more than one SELECT statement from ‘Notifications Services’ only one result set was sent by mail.
* The sja.log file had no line-breaks between what was recorded for two jobs.
* On multi-monitor system resizable dialogues could open on the wrong monitor. New implementation is like this: on multi-monitor systems main program dialogue and ‘first child dialogue’ (example: ALTER TABLE) will open where they were closed (if possible), second and higher child dialogues (example: table advanced properties) will always open on top of its ‘parent’ dialogue. Non-resizable dialogues (such as confirmation boxes) will always open on top of their ‘parent’.
* With multiple SSH-tunnelled connections open stopping and re-executing queries in multiple connections in a fast manner could crash SQLyog.
* If more than one comment occurred before a SELECT statement in the editor, the statement was not identified as a SELECT statement by the Query Profiler and the Query Profiler TAB would not display.
* We did not validate client-side if user checked atoincrement option for a bit column with Create/Alter table dialog.
* If an error occurred while renaming a trigger then trigger was lost as SQLyog was not recreating it back.
* Small GUI fixes.

Miscellaneous:
* The default LIMIT setting for DATA tab has been removed. The setting is not required since we introduced table-level persistence for number of rows displayed. The default for new tables that have not been opened before is 50 – but when user changes the value and next ‘refresh’es SQLyog will save the LIMIT for that particular table persistently across sessions. This in combination with page-wise display in RESULT tab results in a more uniform User Interface for DATA and RESULT tabs.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

I’ve submitted a Birds of a Feather session for Maatkit at the upcoming MySQL conference. It’s not on the public schedule yet, but it has been accepted and scheduled for 19:00 on 13 Apr 2010. See you there!

Related posts:

1. Presentation uploaded for Maatkit talk at MySQL Conference The slides
2. I’ll be speaking at the O’Reilly MySQL Conference 2010 I’m
3. Learn about Maatkit at the MySQL Conference I’m

Related posts brought to you by Yet Another Related Posts Plugin.

TRUNCATE is usually a fast operation (much faster than DELETE FROM). But sometimes it just hangs; I’ve has several such uncheerful events with InnoDB (Plugin) tables which were extensively written to. The TRUNCATE hanged; nothing else would work; minutes pass.

TRUNCATE on tables with no FOREIGN KEYs should act fast: it translate to dropping the table and creating a new one (and it all depends on the MySQL version, see the manual).

What’s faster than TRUNCATE, then? If you don’t have triggers nor FOREIGN KEYs, a RENAME TABLE can come to the rescue. Instead of:

Do:

I found this to work well for me. Do note that AUTO_INCREMENT values can be tricky here: the “new” table is created with an AUTO_INCREMENT value which is immediately taken in the “working” table. If you care about not using same AUTO_INCREMENT values, you can:

Just before renaming.

I do not have a good explanation as for why the RENAME TABLE succeeds to respond faster than TRUNCATE.

I'm happy to announce that my talk "Making MySQL administration a breeze - a look into a MySQL DBA's toolchest" has been accepted for this year's edition of the MySQL Conference & Expo in Santa Clara, which will take place on April 12-15, 2010. The session is currently scheduled for Wednesday 14th, 10:50 in Ballroom E.

My plan is to provide an overview over the most popular utilities and applications that a MySQL DBA should be aware of to make his life easier. The focus will be on Linux/Unix applications available under opensource licenses that ease tasks related to user administration, setting up and administering replication setups, performing backups and security audits.

Of course I will cover the usual suspects (e.g. Maatkit), some of these are actually collections of different utilities by themselves. As it's impossible to go over each individual component in the given time frame, I will try to pick out the most popular/useful parts related to the scopes mentioned above. But I will also cover some lesser known gems that migh be worth taking a look at. What's your the most valued tool in your toolchest? I am still looking for more inspiration.

I look forward to being at the conference again and meeting with colleagues and friends in the MySQL community. Judging from the current schedule, it will be a very interesting mix of talks.

If you're interested in attending, you should consider registering soon! The early registration ends on March 15th. Until then, I encourage you to make use of this "Friend of Speaker" discount code (25% off): mys10fsp

At the 2010 O’Reilly MySQL Conference and Expo there will be a Drizzle BoF!

It’s currently scheduled for 7pm on April 13th.

Come along, it will be awesome.