MySQL 6.0 Feature #2: Online Backup

Online Backup
MySQL 6.0 New Features Document#2
2008-05-18

By Alexander Nozdrin, Chuck Bell, Lars Thalmann, Peter Gulutzan, Rafal Somla

High Executive Summary

Online backup is a new feature in MySQL 6.0.
It protects from database loss.
It does not block all other concurrent MySQL connections.
It will interest Database Administrators.

Database Administrators Summary

BACKUP DATABASE copies all data and metadata in one or more MySQL databases, into an “image file”. RESTORE reads an image file and rewrites all the data and metadata in one or more MySQL databases. So if you lose a database, you can recover all of it as of the time of the last BACKUP DATABASE statement.And then you can re-run the statements in MySQL’s binary log to recover “from the time of …

I can find most of MySQL’s “timeout” variables with a SHOW statement:

mysql> show variables like '%timeout%';+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| connect_timeout            | 10    |

| delayed_insert_timeout     | 300   |

| falcon_lock_wait_timeout   | 50    |

| innodb_lock_wait_timeout   | 50    |

| ...................................|

| interactive_timeout        | 28800 |

| net_read_timeout           | 30    |

| net_write_timeout          | 60    |

| slave_net_timeout          | 3600  |

| table_lock_wait_timeout    | 50    |

| wait_timeout               | 28800 |

+----------------------------+-------+

11 rows in set (0.01 sec)

The straightforward list conceals several anomalies and buried controversies.

* Should we allow non-integer values like 0.55 seconds, or perhaps make all values milliseconds rather than seconds, in order to …

In the earlier article about upcoming features, “Roadmap”, there was no mention of materialized views. The explanation is simple: they’re not on the roadmap. Yet.

However, this is one of the things that might change. There is a strong desire for materialized views, and one of MySQL’s architects has a strong interest (he wrote his PhD thesis on the topic). So if their absence from the “Roadmap” article troubled you, despair not. Keep watching this space and maybe there will be further news someday.

For those who haven’t memorized the names of every symbol: the tilde is the wavy line that appears occasionally over specific characters, particularly over N in Spanish. But it can appear alone. If you’re North American and you look at the top left corner of your keyboard, you’ll probably see it:

~

Anyway, we ran into this bug during a comprehensive re-test of all the Chinese character sets:

mysql> create table tbig5 (s1 char(5) character set big5);

Query OK, 0 rows affected (0.11 sec)mysql> insert into tbig5 values ('Y'),('~');

Query OK, 2 rows affected (0.08 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tbig5 where s1 = 'Y';

+------+

| s1   |

+------+

| Y    |

| ~    |

+------+

2 rows in set (0.04 sec)

This is Bug#25420 “Tilde = ‘Y’ in Chinese”. It has been around for years, ever since the contribution of the BIG5 Chinese character set. No Chinese …

The verdict is in: DTrace will be on by default.

This is a wildly different thing from saying “DTrace will be there”. No right-minded person would want to eschew the benefits of DTrace, the performance-analysis tool par excellence from Sun Microsystems for all varieties of Solaris operating systems. Even if you aren’t a Solaris user, I’d suggest you have a quick glance at the literature about it because it’s the concept that non-Sun folk will surely try to imitate someday. And who knows, maybe you’ll become a Solaris user just because of this. I’ve heard of a consultant in California who does Oracle gigs thus: he goes into the shop where they’re running (say) some other Unix variant, asks them to copy their data to a Solaris machine that he handily brings with him, and re-runs their problem queries with DTrace to find the bottlenecks.

But the controversy (if I can call it that) was over whether the standard MySQL …

Occasionally I hear the worry that MySQL might plan a feature that’s “half baked”. The term’s users include some of the world’s top MySQL experts so I’ll avoid a technical argument.

“In future we should not release a version with half-baked features and call it enterprise-ready.”
– Konstantin Osipov
http://www.xaprb.com/blog/2007/08/12/what-would-make-me-buy-mysql-enterprise/

“will Falcon be pushed hard as Innodb replacement even if it is half baked?”
– Peter Zaitsev
http://www.mysqlperformanceblog.com/page/2/?s=falcon&search_posts=true

As for me, though, I believe in features that others call “half baked”. I just don’t use the term much myself because it seems to convey a wistful regret. While “we’re halfway there” would generally be taken as a report of good progress.

The analogy seems to be with a cake: if you take a cake out of the oven and let …

While we were batting forth ideas about the SIGNAL statement (MySQL 7.0, we appreciate your patience), the question arose: what do we call the thingabummie number that MySQL uses for errors and warnings? You know, this thing …

mysql> crete table t;
ERROR 1064 (42000): You have an error in your SQL syntax; …

What do we call that ‘1064′? An old-timer might say SQLCODE but actually the SQL standard committee decided a decade ago to dump SQLCODE in favour of SQLSTATE, which is a string rather than a number. Besides, MySQL’s number doesn’t follow the old standard conventions for SQLCODE.

There were 7 choices for the term for the numeric return, that we’ll use eventually in SIGNAL / RESIGNAL / GET DIAGNOSTICS syntax and descriptions.

1. SQLCODE.
SQLCODE does not appear in the standard, but it is the DB2 term
…

A recent patch for 6.0 has given users the ability to delay SELECT statements in certain circumstances.

If there’s an INSERT DELAYED going on, then the SELECT can wait for it, or not, depending on the option that the user chooses. This is not the same as saying “there will be a SELECT DELAYED like INSERT DELAYED” — the conditions are not the same. But the new option (whatever it’s finally called) will help out in some debugging and synchronization scenarios.

A bunch of people held a meeting in California just before the last MySQL User Conference. They decided that tablespaces are not inside databases, and databases are not part of tablespaces. They are separate objects which overlap, for example table A can belong to tablespace X and database Y, while table B can belong to tablespace Y and database X. The result will be some restrictions regarding what you can do with tablespace maintenance statements, particularly for Falcon tablespaces.

The meeting participants also decided that there must be a new privilege, CREATE TABLESPACE, which will be required for CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE.

And there was talk about a new table for metadata: INFORMATION_SCHEMA.TABLESPACES.

Suppose you use Linux and suppose you have two tables, and their names are alike except for an uppercase / lowercase difference: ‘City’ and ‘CITY’.

Consider the statement
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘City’;

In MySQL 5.0 you’ll see both tables.

In recent versions of MySQL 5.1 you’ll see only one table: ‘City’.

In later versions there’s a proposal — definitely not an accepted proposal, but this blog can be speculative — that the collation for INFORMATION_SCHEMA.TABLES.TABLE_NAME should be changed to reflect this fact. Currently TABLE_NAME is UTF8_GENERAL_CI. If the proposal goes through, then the collation becomes UTF8_BIN for TABLE_NAME and for a few other columns that end with _NAME (but not all of them), except for Falcon tables, but only on some platforms where case is significant. On other platforms, such as Windows, the collation would be …