Martin Brown’s blog shows a pretty good way of navigating the MySQL Reference Manual. It’s worth noting, however, that finding the different topics has been a lot easier since mysql.com started using a Google appliance for its search.

I use the documentation all the time and have been doing so for years (I won’t claim that I can remember +2000 pages worth of ever-changing content). A few years back, I stopped using the search box on dev.mysql.com because the result sets were enormous, with lots of unrelated references. My technique was to do a Google site search:

For replication use the expression: replication site:http://dev.mysql.com/doc/refman/5.0/en/index.html

The result set was smaller and I would find what I was looking for relatively easily, usually within the first page.

Since the documentation team implemented the Alphabetical Index, it has succeeded the Google search as my favorite way to get the information I needed. Things are easy to find and never more than a couple of URLs away.

(more…)

Welcome to the 117^th edition of Log Buffer, the weekly review of database blogs.

For those of you who don’t know me, my name is Nicklas Westerlund, and I’m a MySQL DBA with The Pythian Group. This is my first time writing Log Buffer, and I hope I’ll do it right.

Let’s start off with SQL Server, where Simon Sabin asks if you know what concurrency is and how to improve it. And on SatisticsIO, Jason Massie focuses on the SQL Server 2008 experience instead, which should provide more inside knowledge of the technology used.

Continuing on with SQL Server 2008, the engineering team is sending loads of engineers to the SQL PASS Conference, as the SQL Server Customer Advisory Team tells us in their post on what, in their opinion, just may be the best PASS Conference yet. And if you’re into meeting engineers, then perhaps you’d also like to know how that patching is done in SQL Server 2008, which PSS SQL informs us about.

The folks over at sqlserver-qa.net also give us an overview of the SQL Server Web Edition.

Let’s move over to Oracle, where there’s still a lot of buzz about Exadata, and let’s start with with the second part of the Exadata FAQ by Kevin Closson. In that post he also mentions his interview on the Exadata with Paul and Christo here at Pythian.

(more…)

Over the weekend, I worked on a client’s two computers, trying to get a slave in sync with the master. It was during this time that I began thinking about:

a) how this never should have happened in the first place.

b) how “slave drift” could be kept from happening.

c) how this is probably keeping some businesses from using MySQL.

d) how MySQL DBAs must spend thousands of hours a year wasting time fixing replication issues.

I’ll be the first person to tell you that the replication under MySQL is pretty much dead-simple to set up. My only complaint is that it is annoying to type in the two-line “CHANGE MASTER” command to set up a new slave. Even so, it makes sense.

It is also very easy, however, for a slave to end up with different data than the master server has. This can be caused by replication bugs, hardware problems, or by using non-deterministic functions. Without proper permissions, a user/developer/DBA can log into the slave server and mess the data up that way. This last is a database administrator problem, but it affects replication. There are probably other issues that astute readers will point out.

I would like to point out one common issue that would probably be categorized as a replication bug. If the master crashes for whatever reason (say, a hosting company accidentally punches the power button on a master server) it will often cause corruption of the binary log. When the master comes back up, the slave cries about a non-existent binary log position.

Possible solutions: (more…)

The unsung heroes of InnoDB are the logfiles. They are what makes InnoDB automatic crash recovery possible.

Database administrators of other DBMS may be familiar with the concept of a “redo” log. When data is changed, affected data pages are changed in the innodb_buffer_pool. Then, the change is written to the redo log, which in MySQL is the InnoDB logfile (ib_logfile0 and ib_logfile1). The pages are marked as “dirty”, and eventually get flushed and written to disk.

If MySQL crashes, there may be data that is changed that has not been written to disk. Those data pages were marked as “dirty” in the innodb_buffer_pool, but after a MySQL crash the innodb_buffer_pool no longer exists. However, they were written to the redo log. On crash recovery, MySQL can read the redo log (InnoDB log files) and apply any changes that were not written to disk.

That is the basic functionality of the InnoDB log files. Given this, let’s look at some of the different parameters and their ramifications.

innodb_log_files_in_group is set with a default of 2. The logfiles are written in a circular manner — ib_logfile0 is written first, and when it has reached its maximum size, then ib_logfile1 will be written to.

innodb_log_file_size is the size of each log file in the log group. The total, combined size of all the log files has to be less than 4 Gb (according to the MySQL manual). Because the logfiles contain changes in the buffer pool that have not been written to disk, the total, combined size of all the log files should not be more than the innodb_buffer_pool_size.

If all the log files in the group are full of changes that have not been written to disk, MySQL will start to flush dirty pages from the InnoDB buffer pool, writing the changes to disk. If the log files are small, changes will be written to disk more often, which can cause more disk I/O.

When InnoDB does a crash recovery, it reads the log files. If the log files are large, it will take longer to recover from a crash. If innodb_fast_shutdown is set to 0, the log files are purged when MySQL shuts down — larger files mean a longer shutdown time. The default for innodb_fast_shutdown is 1, which means that the log files are not purged before a shutdown. Starting in MySQL 5.0.5, you can set it to 2, which simulates a crash, and at the next startup InnoDB will do a crash recovery.

innodb_flush_log_at_trx_commit controls how often the log files are written to. A value of 0 causes the log files to be written and flushed to disk once per second. The default is 1, which causes the log buffer to be written and flushed to disk after every transaction commit. The value can also be set to 2, which causes the log buffer to be written after every transaction commit and flushes the log files to disk once per second. A value of 2 means that MySQL might think that some changes are written to the log file, but do not persist in the log file after an operating system crash, because the log file was not flushed to disk before a crash.

Note that some filesystems are not honest about flushing to disk, so even though you may have the default value of 1, your system may be acting as if it has a value of 2. Setting this parameter to 2 means that there will be less I/O, at the cost of not being able to recover data from a crash.

innodb_flush_method changes how InnoDB opens and flushes data and log files. See the manual for details; the end result is a tradeoff in I/O performance versus whether or not an operating system crash would leave the InnoDB log files in an inconsistent state.

innodb_log_buffer_size is the write buffer for InnoDB log files. The larger the buffer is, the less often the log files are written to. This can save I/O.

As I putter around the MySQL INFORMATION_SCHEMA, I am finding lots of undocumented behavior for fields that should be straightforward. For example, the VIEWS table holds information about views, and the VIEW_DEFINITION field contains the view definition, right?

Well, when I was looking at the VIEW_DEFINITION today, I noticed an odd thing. Even though I had permissions to see the view definition (as proven by the SHOW CREATE VIEW command), the INFORMATION_SCHEMA.VIEWS table sometimes came up blank for the VIEW_DEFINITION. I had to figure out why, and now that I know, I’m not sure if it’s a bug or a feature…..can you figure it out?

mysql> USE INFORMATION_SCHEMA;
Database changed
mysql> SELECT TABLE_NAME,VIEW_DEFINITION FROM VIEWS WHERE TABLE_SCHEMA='sakila';

+----------------------------+-----------------+
| TABLE_NAME                 | VIEW_DEFINITION |
+----------------------------+-----------------+
| actor_info                 |                 |
| customer_list              |                 |
| film_list                  |                 |
| nicer_but_slower_film_list |                 |
| sales_by_film_category     |                 |
| sales_by_store             |                 |
| staff_list                 |                 |
+----------------------------+-----------------+
7 rows in set (0.16 sec)

mysql> SHOW CREATE VIEW sakila.actor_info\G
*************************** 1. row ***************************
                View: actor_info
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY INVOKER VIEW `sakila`.`actor_info` AS select `a`.`actor_id` AS `actor_i
d`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,group_concat(
distinct concat(`c`.`name`,': ',(select group_concat(`f`.`title` order by `f`.`t
itle` ASC separator ', ') AS `GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ',
 ')` from ((`sakila`.`film` `f` join `sakila`.`film_category` `fc` on((`f`.`film
_id` = `fc`.`film_id`))) join `sakila`.`film_actor` `fa` on((`f`.`film_id` = `fa
`.`film_id`))) where ((`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_
id` = `a`.`actor_id`)))) order by `c`.`name` ASC separator '; ') AS `film_info`
from (((`sakila`.`actor` `a` left join `sakila`.`film_actor` `fa` on((`a`.`actor
_id` = `fa`.`actor_id`))) left join `sakila`.`film_category` `fc` on((`fa`.`film
_id` = `fc`.`film_id`))) left join `sakila`.`category` `c` on((`fc`.`category_id
` = `c`.`category_id`))) group by `a`.`actor_id`,`a`.`first_name`,`a`.`last_name
`
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.02 sec)

mysql> SHOW CREATE VIEW sakila.customer_list\G
*************************** 1. row ***************************
                View: customer_list
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `sakila`.`customer_list` AS select `cu`.`customer_id` AS `
ID`,concat(`cu`.`first_name`,_utf8' ',`cu`.`last_name`) AS `name`,`a`.`address`
AS `address`,`a`.`postal_code` AS `zip code`,`a`.`phone` AS `phone`,`sakila`.`ci
ty`.`city` AS `city`,`sakila`.`country`.`country` AS `country`,if(`cu`.`active`,
_utf8'active',_utf8'') AS `notes`,`cu`.`store_id` AS `SID` from (((`sakila`.`cus
tomer` `cu` join `sakila`.`address` `a` on((`cu`.`address_id` = `a`.`address_id`
))) join `sakila`.`city` on((`a`.`city_id` = `sakila`.`city`.`city_id`))) join `
sakila`.`country` on((`sakila`.`city`.`country_id` = `sakila`.`country`.`country
_id`)))
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

mysql>

(some people read the dictionary, I read the data dictionary!)

A while ago, MySQL developed a Community Contribution Agreement for community contributions to the MySQL source code. While browsing the MySQL Forge Wiki I came across:

http://forge.mysql.com/wiki/Community_Contributions

This page shows that the Community Contribution Agreement has changed — it is no longer the document MySQL AB created. It is now Sun Microsystem’s standard Sun Contributor Agreement, which CEO Mårten Mickos recently explained to me was “more accepted than the agreement MySQL had come up with.”

I am happy to see some of the great Sun practices trickle down to replace some issues that MySQL did not handle smoothly. All in all, I agree with Mårten Mickos and think the Sun Contributor Agreement is much better….

….but what do you think?

A short post to direct people’s attention to and solicit comments on the following from someone who is admittedly a hero of mine, Richard Stallman:

But Richard Stallman, founder of the Free Software Foundation and creator of the computer operating system GNU, said that cloud computing was simply a trap aimed at forcing more people to buy into locked, proprietary systems that would cost them more and more over time.

“It’s stupidity. It’s worse than stupidity: it’s a marketing hype campaign,” he told The Guardian.

“Somebody is saying this is inevitable – and whenever you hear somebody saying that, it’s very likely to be a set of businesses campaigning to make it true.”

The 55-year-old New Yorker said that computer users should be keen to keep their information in their own hands, rather than hand it over to a third party.

His comments echo those made last week by Larry Ellison, the founder of Oracle, who criticized the rash of cloud computing announcements as “fashion-driven” and “complete gibberish”.

“The interesting thing about cloud computing is that we’ve redefined cloud computing to include everything that we already do,” he said. “The computer industry is the only industry that is more fashion-driven than women’s fashion. Maybe I’m an idiot, but I have no idea what anyone is talking about. What is it? It’s complete gibberish. It’s insane. When is this idiocy going to stop?”

That blockquote links to the article at the Guardian where Stallman is interviewed and quoted. Please follow it to read the article in its entirety.

What do you think?

So you want to store URLs in MySQL, and the URLs have those annoying %20%27%7C%26%5E%2B%2D%25 symbols? And you want to be able to show your users some kind of human-readable information. You might want to consider using this trick. Take this list of commonly escaped characters as an example:

%20 - space
%27 - '
%7C - |
%26 - &
%5E - ^
%2B - +
%2D - -
%25 - %

So, how about we do some search’n'replace on that?

mysql> SET @url:='%20%27%7C%26%5E%2B%2D%25';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @url as original,
    ->        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    ->        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    ->        @test,'%20',' '),
    ->        '%27','\''),
    ->        '%7C','|'),  -- REPLACE() is case sensitive
    ->        '%7c','|'),  -- so we have
    ->        '%26','&'),
    ->        '%5E','^'),
    ->        '%5e','^'),  -- to repeat
    ->        '%2D','-'),
    ->        '%2d','-'),  -- ourselves
    ->        '%2B','+'),
    ->        '%2b','+'),  -- sometimes
    ->        '%25','%') as replaced;
+--------------------------+----------+
| original                 | replaced |
+--------------------------+----------+
| %20%27%7C%26%5E%2B%2D%25 |  '|&^+-% |
+--------------------------+----------+
1 row in set (0.01 sec)

mysql>

We can easily turn this into a function: (more…)

Welcome to the 116^th edition of Log Buffer, the weekly review of database blogs.

This was the week of Oracle Open World (OOW), Oracle’s gigantic annual get-together in San Francisco — always the heaviest week in Oracle blogs, so let’s start there.

For day-by-day coverage of OOW on the ground, I recommend Doug’s Oracle Blog: OOW Day 1, OOW Day 1.5, OOW Day 2, OOW Day 3.

Tom Kyte shared a podcast from OOW 2008, and interview with Oracle Magazine editor Tom Haunert, in which Tom, “ . . . stirs things up in this conversation about Oracle OpenWorld happenings, a new approach to publishing, and the trouble with triggers.”

Oracle teased everyone right at the beginning with word that CEO Larry Ellison’s keynote, carrying the title “Extreme Performance,” would introduce something big and new. And there was much speculation in the blogging world, some of it quite perspicacious. “Big and new” was soon going by the tantalizing nom-de-hype “X”. And before Larry’s keynote was even over (before he mothballed the black mock-turtleneck for another year), X was no longer unknown.

Writes Lucas Jellema on the AMIS Technology blog — The secret is out: Oracle launches “The Database Machine” - becoming a hardware vendor! “The big announcement that had loomed over the conference has been made. Oracle - in joint partnership with HP - introduces the world’s fastest hardware for running databases and especially data warehouses: the Exadata Storage Server.” Click through for Lucas’s précis of what it’s all about.

On blogs.oracle.com, Jack Flynn has some video excerpted from the keynote.

Lucas’s story has a picture of the thing itself, albeit a somewhat blurry one. Here’s a better image of one of the two new machines, the Exadata. Oooh, just look at it! Cor!

(more…)

Over the last few weeks I’ve been looking at several customers’ slow query logs, and I found in many of them an odd type of query. These are SELECT statements that contain an IN clause that includes dozens, sometimes hundreds of values. These statements often end in the slow query log. I’m not sure if these queries are this way by design or if they are generated by a specific database development tool.

I did some tests in one of my own databases, one with only around 10K rows in its largest table. The database corresponds to the Amarok media player. For example, I queried for songs by B. B. King (spelled “BB King”, “B.B. King”, etc. or with other artists: “B. B. King & Eric Clapton”).

The first query used a JOIN and an IN clause with all the spellings in my db; the second used the same JOIN and WHERE ... name LIKE "BB%" OR name LIKE "B.%". Both had the same execution plan, and both retrieved the same number of results. In MySQL version 4.1 there were some enhancements to the optimizer for treating these massive IN clauses, which means that for smaller databases, this is expected.

With bigger databases and more complex queries, things are different. (more…)