SWiK

» tagged pages

sorted by: recent | see : popular
Content Tagged with Engine + MySQL

A cool idea - Revision engine

Monday, September 29, 2008

Yesterday I saw an announcement in the MySQL internals@ list, about a new storage engine being released. DDengine has created a revision engine, a sort of embedded proxy inside MySQL that keeps track of the changes you do to your data.
The idea is clever. You write to your table, update and delete without any concern, and the revision engine stores your changes in the background.
I wanted to tried the engine on my laptop (Mac OSX), but there was no binary available for this architecture. I contacted the authors and I received prompt assistance until I had the product installed. Unfortunately, it crashes immediately. Well, it's to be expected for a version 0.1.
I then tried on Linux, and also here I received very quick assistance from Peter Benjamin Volk, Project Head at DDengine.
It now works on Linux, although it's very basic at the moment.
The documentation says that there are two ways of using the revision engine. One with embedded changes, which involves a silent alteration of your table structure to include the revision info, and one with two tables, one of which is created behind the scenes.
For example:

create table t1 (
 id int not null primary key,
 c char(10)
) engine=revision comment="InnoDB:DOUBLE";
show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t1_revision    |
+----------------+

desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   | PRI | NULL    |       |
| c     | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

desc t1_revision;
+--------------------+---------------------+------+-----+
| Field              | Type                | Null | Key |
+--------------------+---------------------+------+-----+
| id                 | int(11)             | NO   | PRI | 
| c                  | char(10)            | YES  |     |
| revision_id        | int(10) unsigned    | NO   | PRI |
| revision_timestamp | timestamp           | NO   |     | 
| revision_deleted   | tinyint(3) unsigned | NO   |     |
+--------------------+---------------------+------+-----+

The "InnoDB:DOUBLE" tells the engine to use a second table for revision info.
Operations on the table are transparent.

insert into t1 (id,c) values (1, 'aaa'), (2, 'bbb');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

insert into t1 (id, c) values (3, 'ccc'), (4, 'ddd');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

select * from t1;
+----+------+
| id | c    |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
|  4 | ddd  |
+----+------+
4 rows in set (0.00 sec)

select * from t1_revision;
Empty set (0.00 sec)

So far, nothing unusual. Let's try some changes.

update t1 set c ='changed' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

select * from t1;
+----+---------+
| id | c       |
+----+---------+
|  1 | aaa     |
|  2 | bbb     |
|  3 | changed |
|  4 | ddd     |
+----+---------+
4 rows in set (0.00 sec)

show variables like '%revision%';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| revision_select_mode | current |
+----------------------+---------+
1 row in set (0.00 sec)

select * from t1_revision;
+----+------+-------------+---------------------+------------------+
| id | c    | revision_id | revision_timestamp  | revision_deleted |
+----+------+-------------+---------------------+------------------+
|  3 | ccc  |           1 | 2008-09-30 05:45:49 |                0 |
+----+------+-------------+---------------------+------------------+
1 row in set (0.00 sec)

delete from t1 where id = 2;
Query OK, 1 row affected (0.01 sec)

select * from t1_revision;
+----+------+-------------+---------------------+------------------+
| id | c    | revision_id | revision_timestamp  | revision_deleted |
+----+------+-------------+---------------------+------------------+
|  3 | ccc  |           1 | 2008-09-30 05:45:49 |                0 |
+----+------+-------------+---------------------+------------------+
1 row in set (0.00 sec)

set revision_select_mode = 'deleted';
Query OK, 0 rows affected (0.01 sec)

select * from t1_revision;
+----+------+-------------+---------------------+------------------+
| id | c    | revision_id | revision_timestamp  | revision_deleted |
+----+------+-------------+---------------------+------------------+
|  2 | bbb  |           1 | 2008-09-30 05:47:14 |                1 |
+----+------+-------------+---------------------+------------------+
1 row in set (0.00 sec)

select * from t1;
+----+---------+
| id | c       |
+----+---------+
|  1 | aaa     |
|  3 | changed |
|  4 | ddd     |
+----+---------+
3 rows in set (0.00 sec)

The session variable revision_select_mode changes the revision data that to be shown.
At the moment, there is no revert mechanism (or if there is, it's undocumented), but the idea is cool, and I think that this engine will become very useful.

MySQL: Planet MySQL

Engine MySQL storage revision

Monty speaks about Maria

Tuesday, August 05, 2008

Michael Widenius, commonly referred to as Monty, gave a very interesting talk on Maria at OSCON 2008. He not only had a talk in the main session, that was well attended, titled Architecture of Maria, the New Transactional Storage Engine for MySQL (slides are available in ODP there), he also gave one at the Sun booth, where we were running our own little “unconference”.

For those reading this in a feed reader, there’s a 23 minute video of Monty telling us more about Maria, a bit about its motivations, architecture, and where the team is at now. If you’re interested in grabbing the code, check out the MySQL + Maria Storage Engine branch on Launchpad.

MySQL: Planet MySQL

Engine MySQL video storage oscon maria monty

Using Federated Servers in MySQL? 5.1

Friday, August 01, 2008

[HoneyMonitor is a MySQL? GUI Tool for Windows? Systems. Server Administration, Database and Database Objects Administration, Code Development, Server Monitoring, Performance Monitoring, Performance Auditing. Read more on http://www.honeysoftware.com]

This is a short tutorial on the features implemented in HoneyMonitor for the use and administration of Federated Servers . It could be useful for Users who are new to the product and want to get start with it or to whom want to learn the basis of Federated Servers in MySQL? 5.1

More details on the HoneyMonitor Reference Manual at http://www.honeysoftware.com/honeymonitor/doc.

Introduction
Federated Server Metadata
Retrieving the list of Federated Servers
The Federated Server Editor
Creating a Federated Table Using the Create Table Wizard
Other Resources
About the Author

1. Introduction

Starting from MySQL? v.5.1.15-beta you can use the CREATE / ALTER / DROP SERVER syntax to manage your Federated Servers.

This can help you creating a FEDERATED table in a fast and more efficient way. In fact if you first create a Federated Server using the CREATE SERVER syntax, then you can create a federated table using the following simple create table option

CONNECTION = ‘federated_server_name’

instead of the longer

CONNECTION = ‘mysql://user_name [:password]@host_name[:port_num] /db_name/tbl_name’

Note that starting from version 5.1.26-rc, the FEDERATED storage engine is disabled by default in binary distributions. The engine is still available and can be enabled by starting the server with the –federated option.

For more information about Federated Server, please visit http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html.

2. Federated Server Metadata

In the database mysql there is a table - mysql.servers ? where all the information about the Federated Servers are stored:

mysql> SELECT * FROM mysql.servers LIMIT 1\G
*************************** 1. row ***************************
Server_name: server1
       Host: localhost
         Db: mysql
   Username: root
   Password:
       Port: 6005
     Socket:
    Wrapper: mysql
      Owner:
1 row in set (0.00 sec)

3. Retrieving the list of Federated Servers

Federated Servers are listed in the Federated Servers node of the Server Objects List (Fig. 1).

Fig.1: Server Objects List - Federated Servers

You can create / edit / drop a Federated Server using node’s popup menu.

4. The Federated Server Editor

The Federated Server Editor (Fig. 2), allows you to create / edit a Federated Server.

Fig. 2: The Federated Server Editor - Advanced Tab

You have to set the server name and (at least) one option to create a Federated Server.

After you set the Server’s parameters you can click on the button ‘Test Connection’ to test the connection to the MySQL? Server.

After the creation of the Server, the SQL Tab (Fig. 3) ? now enabled - will contain the SQL syntax for recreating the current Server.

Fig. 3: The Federated Server Editor - SQL Tab

5. Creating a Federated Table Using the Create Table Wizard

The Create Table Wizard helps you creating tables in a simply and fast way (Fig. 4).

Fig. 4: The Create Table Wizard

The Wizard includes a ‘Connection’ option; when creating a Federated Table you can fill it with the connection string (’mysql://user_name[:password]@ host_name [:port_num]/db_name/tbl_name’) or just with the name of the Federated Server you created with Federated Server Editor.

By clicking on button at the right of the ‘Connection’ field, the Federated Server List will be opened thus helping you choosing an existing Federated Server - without the need of writing its name - or creating one more (Fig. 5).

Fig. 5: The Federated Server List

6. Other Resources

If you want to learn more on HoneyMonitor please visit the Project Home Page, at http://www.honeysoftware.com/honeymonitor, or check out one of the following resources:

7. About the Author

Santo Leto is a two years experience MySQL? DBA and Developer.
Leader and main programmer of the HoneyMonitor Project, he graduated in physics and he lives in Italy, where he works from home.

MySQL: Planet MySQL

Engine MySQL metadata storage tutorials Tables servers

Sphinx - Free open-source SQL full-text search engine

Tuesday, July 22, 2008

sorted by: recent | see : popular Content Tagged with Engine + MySQL

[HoneyMonitor is a MySQL? GUI Tool for Windows? Systems. Server Administration, Database and Database Objects Administration, Code Development, Server Monitoring, Performance Monitoring, Performance Auditing. Read more on http://www.honeysoftware.com]

Contents

1. Introduction

2. Federated Server Metadata

3. Retrieving the list of Federated Servers

4. The Federated Server Editor

5. Creating a Federated Table Using the Create Table Wizard

6. Other Resources

7. About the Author

sorted by: recent | see : popular
Content Tagged with Engine + MySQL