As you probably already know, yesterday the GA version of MySQL™ 5.1 has been released :)

To celebrate this great event, I have organized a little event with some of my friends @ Trieste University, Italy.

Since I am a physics alumn and I still have some friends who study physics, I decided to organize a sort of unofficial “Trieste MySQL™ 5.1 GA Day” to have some funny moments with them at the Physics Department ;)

I am using MySQL™ 5.1 since the third public version (5.1.5-beta, January 2006) and I have installed and tested all the 24 versions from 5.1.5-beta :)

For the occasion, I bought a chocolate pie and a good Italian sweet wine (Ramandolo).

I also bring:

• the Limited Edition ;) “MySQL™ Contributor” polo shirt I won for the position 10 of the MySQL™ Use Case Competition
•  the Sun | MySQL™ bag I received during the latest MySQL™ Central Europe Conference
•  one of the Sakila Dolphins Giuseppe threw to the roman students attending the event organized by the Sun | MySQL™ Italian Team last May, when Marten Mikos came in Italy

The pie dedication I agreed with the confectioner was “Welcome MySQL 5.1 G.A. November 27, 2008″ but I don’t know why ;) he changed “MySQL 5.1 G.A.” in “5.1. G.A. - M.y.S.Q.L.” :-/

Also I gave a good splash of the Dolphin but it seems that he drew a ornithorhynchus rather than a dolphin ;)

BTW, the pie was delicious!

I would like to virtually dedicate this pie to all Developers, Q.A. Engineers, Support Engineers and the other friends @ Sun | MySQL™.

Especially, I would like to cite Miguel Solorzano & Sveta Smirnova @ MySQL™ Bug Tracking and, of course, friends @ Community Team.

Unfortunately, I didn’t have the time to organize a bigger event and invite you to join :( since I am very busy with my work and the MBA in this period. It has been a sort of private event (thanks Luca for the photos and Enrico for placing your car to my disposal) but it has been very fun!

I have uploaded some pics here, enjoy!

Dear Kaj, Giuseppe, Jay, Lenz, Colin,
MySQL™ Community Team,

This is my Report for the MySQL™ 5.1 Use Case Competition.

Before start reporting on my use of the new 5.1 Features, just a note.

I was quite sure on early August 2008 that including similar posts in my Report would be allowed. But during an email conversation, Giuseppe told me that similar Reports won’t qualify for the competition because the Community Team was looking for Reports on ‘Real Word’ usage of MySQL™ 5.1 in production systems rather than a description of software specific features though implemented on MySQL™ 5.1 features.

BTW, I decided to go on with my series of articles as I wrote in this post (I published it only today, before it was just a draft).

Nevertheless, few weeks ago, after reading this post by Lenz I changed my mind once again about the real stuff a UC Report should contain and today I think that my Report could be admitted (if that has been admitted).

So here’s my report ;-)

http://www.honeysoftware.it/sleto/blog/ucc/sleto-MySQL-5.1-Use-Case-Reports.pdf

Username and password has been sent to the Community email address.

Enjoy!

This is a short tutorial on the features implemented in HoneyMonitor for the use and administration of Partitioning in MySQL™ 5.1. 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 Table Partitioning in MySQL™ 5.1.

Contents

1. Introduction
2. Partitioning a Table
2.1 Creating a Partitioned Table using the Create Table Wizard and the Partition Editor - A Range Partitioning Example
2.2 Partitioning a Table that already exits - An Hash Partitioning Example
3. Creating and Dropping Partitions
3.1 Adding Partitions
3.2 Dropping Range (or List) Partitions
3.2.1 Multi-Dropping of Partitions
3.3 Aggregating Hash (or Key) Partitions
4. Reorganizing Partitions
5. Repartitioning a Table
6. Removing Partitioning
7. Retrieving Information about Partitions
7.1 Partitions’ Metadata
7.2 Partition Editor’s Charts
8. Partition’s Maintenance
9. Other Resources

1. Introduction

The support for table partitioning has been added in MySQL™ v.5.1. Partitioning allows you to distribute a portion of table’s rows across a filesystem according to specific rules that you can set (the so called partitioning function).

This article can’t be exhaustive about the Partitioning Feature of MySQL™ 5.1. We will have just a GUI approach. For more information about Table Partitioning, please visit http://dev.mysql.com/doc/refman/5.1/en/partitioning.html.

HoneyMonitor provides you with a useful GUI for managing table partitioning. In this short article we will see how to add / edit / drop partitioning, how to add / drop (or coalesce) partitions / subpartitions, how to retrieve information about partitions and the basis of partition’s maintenance.

More detail on HoneyMonitor on its Reference Manual, at http://www.honeysoftware.com/honeymonitor/doc.

2. Partitioning a Table

You can add partitioning to a table in 2 ways:

1. If you want to create a partitioned table (and the table has not been created yet) you can use the Create Table Wizard.
2. If the table has been already created, you may open it the the Table Editor and then click on the Partitions Tab.

2.1 Creating a Partitioned Table using the Create Table Wizard and the Partition Editor - A Range Partitioning Example

The Create Table Wizard allows you creating tables in a simple and fast way (Fig. 1).

Fig. 1: The Create Table Wizard

One step of the Wizard is dedicated to Table Partitioning (Fig. 2).

Fig. 2: Partitioning Step of the Create Table Wizard

Figures below explain the creation process of a Range Partitioning.

2.2 Partitioning a Table that already exits - An Hash Partitioning Example

If a table is not partitioned, and you open it in the Table Editor, you can add partitioning by clicking on the button ”Add Partitioning” available from the Partitions Tab (Fig. 3)

Fig. 3: Table Editor - Partitions Tab (no partitioning)

The Partition Editor will be opened and you can follow the same steps as in the example above (if you want to create a Range-Partitioned Table).

For our tutorial we will create a Hash partitioned table (Fig. 4 and 5). For this kind of partitioning, you can set the partition number and then just click ”OK” without the need of creating partition by partition.

Fig. 4: Using the Partition Editor to create a Hash Partitioned Table

Fig. 5: Table Editor - Partitions Tab (list of the hash partitions)

3. Creating and Dropping Partitions

Suppose now that you have a partitioned table and you want to add or drop a partition. You can do this in a simple way using the popup menu of the Table Editor (Fig. 6):

Fig. 6: Table Editor - Popup Menu of the Partition Tab

3.1 Adding Partitions

To add a partition you can use the popup menu of the Partitions Tab of the Table Editor (and then use the ”Add Partition Window”) but you can also add a partition without the need to open that Editor, just using the popup menu of the Server Objects List (Fig. 7).

Fig. 7: Server Objects List - Popup Menu for Partitions’ Administration

3.2 Dropping Range (or List) Partitions

To drop a partition just select it in the grid of the Table Editor and use the popup menu (Fig. 6).

Please be aware of the fact that when dropping a (Range or List) partition all its subpartitions will be removed and the data contained in that partition will be deleted as well (deleting a partition is a fast way for deleting a large data set).

3.2.1 Multi-Dropping of Partitions

To drop more than one partition at once, you can use the Multi-Drop Wizard (Fig. 7).

Fig. 7: Multi-Drop of Range Partitions using the Multi-Drop Wizard

3.3 Aggregating Hash (or Key) Partitions

To reduce the number of partitions in our Hash partitioned Table we can’t ”drop” a partition but we have to ”aggregate” two or more partitions (Fig. 8, 9).

Fig. 8: Aggregating 3 Hash Partitions

Fig. 9: Table Editor - Partition List after the aggregation

4. Reorganizing Partitions

The Reorganize Partitions Window (Fig. 10) allows you to reorganize some partitions of a partitioned table.

Fig. 10: Reorganize Partitions Window: Choosing Partitions to be reorganize

Note: if you want to do a complete repartition of the table, editing the partition function as well, please use the Partition Editor (see below).

After choosing a set of partitions, you can write the SQL to reorganize them or you can click on the button ”…” to use the Partition Editor to reorganize them in a graphical way (Fig. 11).

Fig. 11: Reorganize Partitions Window: Ready to execute the query

5. Repartitioning a Table

If you want to do a complete repartition of a partitioned table you can use the ”Repartitioning” Feature of the Partition Editor (Fig. 12):

1. Open a partitioned table in the Table Editor and click on the ”Partitions” Tab.
2. Click on the button ”Edit Partitioning” -> the Partition Editor will be opened.
3. Click on the button ”Table Repartitioning” and use the Partition Editor in a normal way (as when you add partitioning for the first time).

No changes will be ”committed” before you click on the ”Post Changes” button. If you are not satisfied with the new partition’s structure just created, just don’t save and click on the ”Undo” button. The old partition’s structure will be re-loaded in the Partition Editor.

Note: repartitioning preserve the table’s data. No data will be lost.

Fig. 12: Repartitioning a Partitioned Table

6. Removing Partitioning

You can remove the partitioning by clicking on the button ”Remove Partitioning” of the ”Partitions” Tab of the Table Editor. All Partitions will be removed but the data are preserved.

7. Retrieving Information about Partitions

HoneyMonitor allows you retrieving partition metadata in (at least) two ways. You can use the Metadata Catalog of the Server Objects List or the Partition Editor.

When searching partition’s metadata, please be aware of bug #33287.

7.1 Partitions’ Metadata

Please use the drill-down feature of the Metadata Catalog to find the metadata you need (Fig. 15, 16, 17).

Fig. 15	Fig. 16	Fig. 17

7.2 Partition Editor’s Charts

If you open the Partition Editor, and you click on a partition name in the Partition Tree, useful information about that partition will be shown. This include also 3 charts: ”Rows #”, ”Data Length” and ”Index Length”. You can use those charts to check the portion of table’s rows included in a specific partition, as well as the used Data and Index MB (Fig. 19).

Fig. 19: Partition Editor - Partition Statistics

8. Partition’s Maintenance

As regards maintenance of partitions, again you can use (at least) 2 methods:

1. A single-partition one-click method from the Partition Editor (Fig. 20).
2. A more general multi-partitions wizard-method (Fig. 21).

Fig. 20:Using the Partition Editor for Partition’s Maintenance

Fig. 21:  Using the top-level HoneyMonitor’s Menu for Partition’s Maintenance

9. 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:

• Getting Started Guide
• Video Tutorials
• User’s Guide

[HoneyMonitor is a GUI for MySQL™. 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 Table Logs in MySQL™ 5.1 . 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 Table Logging in MySQL™ 5.1.

Contents

1. Introduction
2. Brief Introduction to the Server Logs Management Window
3. The General Log Table
4. General Log Administration
5. General Log Data: Selecting, Sorting, Filtering Log Data
6. Quick Tour of the other features of the Server Logs Management Window
7. Other Resources

1. Introduction

One of the main new features of MySQL™ 5.1 is Table Logging. Beginning with v.5.1.6, in fact, it is possible to choose a new destination for General and Slow Logs: not only log files but also log tables.

In general, if the log output is a table and all log statements are stored in a table rather than in a text file you can use a SELECT syntax to select over the log table thus improving and speed-up the way you can search information on your logs.

In MySQL™ 5.1 there are two log tables: general_log and slow_log. In this article we typically will refer to general_log but many of the considerations we make apply to slow_log as well.

A server’s variable, log_output, takes care of the destination of the output of the general_log and the slow_log.

log_output is a global, dynamic, string variable.

In general, a dynamic variable is a variable that you can change at run-time without the need to stop and restart the server. This is very useful if applied to log: you can switch from log-table to log-file at runtime.

Possibles values for log_output are “FILE”, “TABLE” and “NONE” but you can also use a combination between values. For example if you set “FILE,TABLE” all log statements will be written both in the file-log and in the table-log.

In early versions of MySQL™ 5.1, the default value for log_output was “TABLE” but then the default value has been “restored” to “FILE” (as for MySQL™ 5.0).

“NONE”, if specified, causes the server not to write log even if the log is enabled. Please pay attention at the following difference: setting a log output does not imply enabling log.

To enable general_log you have to set general_log = ON and, in a similar way, to enable slow_log you have to set log_slow_queries = ON. After log is enabled you can choose log_output but, while there are two separate variables for enabling general and slow_log, there is only one variable related to log output. So you can have the general_log enabled and the slow_log disabled but if you choose an output (and both logs are enabled) that output (”FILE“, “TABLE“, or “FILE,TABLE“) applies to both the logs.

If the logs are disabled, no logging occurs even if the value of log_output is not “NONE“.

Another thing you have to consider is that the slow_log is not an absolute, independent feature like general_log.

slow_log is in fact deeply related to the value of the variable long_query_time (if a query takes more than long_query_time to be executed, and slow_log is enabled, then it will be logged as a slow query).

If you start the server with the option –-log, the general_log is enabled by default (but not the slow_log).

Other variables of interest for log management are general_log_file (the location of the general_log if log_output is “FILE“), slow_query_log_file (the location of the slow_log if log_output is “FILE“) and log_error (the location of the error log).

This article can’t be exhaustive about log management in MySQL™ and we will try to cover only some aspects relating to the general and slow logs, leaving out errors log and binary logs from our treatment.

For more information about Server Logs, please visit http://dev.mysql.com/doc/refman/5.1/en/log-files.html.

2. Brief Introduction to the Server Logs Management Window

In HoneyMonitor, our GUI for MySQL™, the Server Logs Management Window allows you to view and manage the server’s logs (general log, slow log, error log, binary log, relay log).

For this tutorial we will use HoneyMonitor v.1.0.14-alpha, MySQL™ v.5.1.28-rc, the latest version of the 5.1 rc series of the server, and MySQL™ v.5.1.26-rc (we use the old version 5.1.26-rc for the sole reason that the v.5.1.28-rc has just been released few hours ago and, in my laptop, the log tables of that version contain only few rows).

The version 1.0.14-alpha of HoneyMonitor includes many improvements in the log management if compared with v.1.0.13-alpha but we also plan to add other two main features in this Window in next versions so stay tuned for a better management of log in future versions of our software.

When opening the Server Logs Management Window, the Status Tab is selected (Fig. 1).

Fig. 1: Server Logs Management

In this Tab some quick information about the status of the logs are shown (for example, the number of records contained in the General and Slow Log Tables).

Furthermore, server’s variables that are related to the logs management are listed. Dynamic variables, in the list, are identified with a different icon and can be edited just double-clicking on their names or using the popup menu of the list.

When clicking on a variable, a useful tooltip with the variable’s description will appear.

As you can see from Fig. 1, you can ”Flush” the logs by clicking on the ”Flush Logs” button.

If we click on the “General Log” Tab we can see that the general log is enabled but the log output isn’t “TABLE” (Fig. 2)

Fig. 2: Server Logs Management Window – General Log Tab

so the first thing we must do in order to continue our tutorial is changing the log_output (Fig. 3).

Fig. 3: Setting log_output

and then checking that the (orange) warning disappear (Fig. 4).

Fig. 4: General Log Entries

3. The General Log Table

The General Log Table, as well as the Slow Log Table is included in the mysql database:

mysql> use mysql;
Database changed
mysql> describe general_log\G
*************************** 1. row ***************************
Field: event_time
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
*************************** 2. row ***************************
Field: user_host
Type: mediumtext
Null: NO
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: thread_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 4. row ***************************
Field: server_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 5. row ***************************
Field: command_type
Type: varchar(64)
Null: NO
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Field: argument
Type: mediumtext
Null: NO
Key:
Default: NULL
Extra:
6 rows in set (0.11 sec)

mysql> describe slow_log\G
*************************** 1. row ***************************
Field: start_time
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
*************************** 2. row ***************************
Field: user_host
Type: mediumtext
Null: NO
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: query_time
Type: time
Null: NO
Key:
Default: NULL
Extra:
*************************** 4. row ***************************
Field: lock_time
Type: time
Null: NO
Key:
Default: NULL
Extra:
*************************** 5. row ***************************
Field: rows_sent
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 6. row ***************************
Field: rows_examined
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 7. row ***************************
Field: db
Type: varchar(512)
Null: NO
Key:
Default: NULL
Extra:
*************************** 8. row ***************************
Field: last_insert_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 9. row ***************************
Field: insert_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 10. row ***************************
Field: server_id
Type: int(11)
Null: NO
Key:
Default: NULL
Extra:
*************************** 11. row ***************************
Field: sql_text
Type: mediumtext
Null: NO
Key:
Default: NULL
Extra:
11 rows in set (0.00 sec)

mysql>

You can see the table’s DDL as well as the table data from the General Log Tab of the Server Logs Management Window included in HoneyMonitor, too (Fig. 5).

Fig.5: General Log DDL

4. General Log Administration

You can enable / disable the General Log directly through the General Log Tab. Also you can clear all log entries and change the Engine of the Table form CSV to MyIsam and vice versa. Please, be aware of bug #39133 when altering Log Tables on master servers.

5. General Log Data: Selecting, Sorting, Filtering Log Data

The grid included in the General Log Tab allows you to see, sort and filter (general) log data in an easy way. Of course, data can’t be edited.

Pagination helps you manage large log tables (Fig. 6).

Fig. 6: Data Pagination

Simple sorting (i.e. on just one column) or advanced sorting (i.e. on more than one columns simultaneously) can help you too for your administration tasks (Fig. 7 and 8)

Fig. 7: Simple Sorting

Fig. 8: Advanced Sorting

as well as defining specific filters (Fig. 9).

Fig. 9: Filtering Log Data

6. Quick Tour of the other features of the Server Logs Management Window

The following images can help you understanding the other features implemented by the Server Logs Management Window.

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

Fig. 10: General (or Slow) Query File

Fig. 11: Slow Table Log

Fig. 12: Binary and Relay Logs

Fig. 13: Errors Log

Fig. 14: Binlog Events

7. 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:

• Getting Started Guide
• Video Tutorials
• User’s Guide

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

Contents

• Introduction
• Log File Group & UndoFiles
• TableSpaces & DataFiles
• Cluster Disk Objects Metadata
• Creating Cluster Disk Tables
• Dropping Cluster Disk Data Objects
• The Cluster Monitor
• Cluster Disk Data Limitations
• Cluster Disk Data Quiz
• Other Resources

1. Introduction

Cluster Disk Data is a new feature of MySQL™ 5.1. Beginning with MySQL™ 5.1.6, in fact, it is possible to store the non-indexed columns of NDBCLUSTER tables on disk, rather than in RAM  this allowing MySQL™ Cluster to scale upward with fewer RAM requirements than previously.

This is a short tutorial on the features implemented in HoneyMonitor for the use and administration of Cluster Disk Data Objects . 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 Cluster Disk Data in MySQL™ 5.1.

For more information about Cluster Disk Data, please visit http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-disk-data.html, while, if you want to learn more on HoneyMonitor, please visit its Project Home Page at http://www.honeysoftware.com/honeymonitor.

For this tutorial we will suppose you have a MySQL™ Cluster up and running and we will connect to the Linux machine, from HoneyMonitor, through a SSH Tunneling connection.

If you are new to MySQL™ Cluster and want to set up a testbed (all nodes on localhost), you can use this very useful configuration tool (created by Johan Andersson) or, if you want to do a more complicated (but nice) exercise, you can set up the Cluster in 4 Solaris™ Zones (a tutorial can be found here).

For our Tutorial, we will follow the following steps:

• Create a LogFile Group
• Add one more UndoFile to the LogFile Group
• Create a TableSpace that uses the LogFile Group just created
• Add one more DataFile to the TableSpace
• Create a Cluster Disk Table

You can skip steps no. 2 and 4 as they are just to practise.

2. Log File Group & UndoFiles

The LogFile Group Editor (Fig. 1) allows you create / edit / drop a LogFile Group.

Fig. 1: Creating a LogFile Group

It implements the following Data Definitions Statements:

1) [http://dev.mysql.com/doc/refman/5.1/en/create-logfile-group.html]

CREATE LOGFILE GROUP logfile_group
ADD UNDOFILE ‘undo_file’
[INITIAL_SIZE [=] initial_size]
[UNDO_BUFFER_SIZE [=] undo_buffer_size]
[REDO_BUFFER_SIZE [=] redo_buffer_size]
[NODEGROUP [=] nodegroup_id]
[WAIT]
[COMMENT [=] comment_text]
ENGINE [=] engine_name

2) [http://dev.mysql.com/doc/refman/5.1/en/alter-logfile-group.html]

ALTER LOGFILE GROUP logfile_group
ADD UNDOFILE ‘file_name’
[INITIAL_SIZE [=] size]
[WAIT]
ENGINE [=] engine_name

3) [http://dev.mysql.com/doc/refman/5.1/en/drop-logfile-group.html]

DROP LOGFILE GROUP logfile_group
ENGINE [=] engine_name

As you can see from Fig. 1, some options are disabled as they are parsed but not yet implemented by the  MySQL™ Server.

An UndoFile is required for the LogFile Group creation. In our example we will use an UndoFile named ‘undofile1′.

After the creation  of the LogFile Group the Tabs ‘UndoFiles’ and SQL will be enabled (Fig. 2, 3, 4).

Fig. 2: LogFile Group Editor - Advanced Tab

Fig. 3: LogFile Group Editor - UndoFiles Tab

Fig. 4: LogFile Group Editor - SQL Tab

In the UndoFiles Tab you can see UndoFiles’ details or you can create one more UndoFile, using the Add UndoFile Dialog (Fig. 5).

Fig. 5: Add UndoFile Dialog

The SQL Tab contains the SQL statements to be executed for re-creating the LogFile Group (and all its UndoFiles!).

Note: you can’t delete UndoFiles.

Note 2: beginning with MySQL™ 5.1.8, you can have only one LogFile Group per Cluster at any given time (see Bug#16386).

Let’s now create a TableSpace that uses the LogFile Group just created.

3. TableSpaces & DataFiles

The TableSpace Editor (Fig. 6) allows you create / edit / drop TableSpaces.

Fig. 6: Creating a TableSpace

It implements the following Data Definitions Statements:

1) [http://dev.mysql.com/doc/refman/5.1/en/create-tablespace.html]

CREATE TABLESPACE tablespace_name
ADD DATAFILE ‘file_name’
USE LOGFILE GROUP logfile_group
[EXTENT_SIZE [=] extent_size]
[INITIAL_SIZE [=] initial_size]
[AUTOEXTEND_SIZE [=] autoextend_size]
[MAX_SIZE [=] max_size]
[NODEGROUP [=] nodegroup_id]
[WAIT]
[COMMENT [=] comment_text]
ENGINE [=] engine_name

2) [http://dev.mysql.com/doc/refman/5.1/en/alter-tablespace.html]

ALTER TABLESPACE tablespace_name
{ADD|DROP} DATAFILE ‘file_name’
[INITIAL_SIZE [=] size]
[WAIT]
ENGINE [=] engine_name

3) [http://dev.mysql.com/doc/refman/5.1/en/drop-tablespace.html]

DROP TABLESPACE tablespace_name
ENGINE [=] engine_name

Note: The Editor can be used to create both NDBCLUSTER and FALCON TableSpaces. When creating FALCON TableSpaces, the ‘LogFile Group’ option isn’t visible.

As you can see from Fig. 6, some options are disabled as they are parsed but not yet implemented by the  MySQL™ Server.

A DataFile is required for the TableSpace creation. In our example we will use a DataFile named ‘datafile-11′.

After the creation  of the TableSpace the Tabs ‘DataFiles’ and SQL will be enabled (Fig. 7, 8, 9).

Fig. 7: TableSpace Editor - Advanced Tab

Fig. 8: TableSpace Editor - DataFiles Tab

Fig. 9: TableSpace Editor - SQL Tab

In the DataFiles Tab you can see DataFiles’ details or you can create one more DataFile, using the Add DataFile Dialog (Fig. 10). You can also drop a DataFile.

Fig. 10: Add DataFile Dialog

The SQL Tab contains the SQL statements to be executed for re-creating the TableSpace (and all its DataFiles!).

4. Cluster Disk Objects Metadata

The source for Cluster Disk Object Metadata in MySQL™ 5.1 is the  information_schema.files table. I’ll try to post a more detailed article on Disk Object Metadata later on.

In the HoneyMonitor’s Metadata Catalog there is a visualization dedicated to Cluster Disk Objects (Fig. 11).

Fig. 11: Metadata Catalog - Cluster Disk Objects

That visualization is very useful to know, with just a look, the Cluster Disk Objects’  hierarchy. You can use popup menus to create / edit / drop the objects.

5. Creating Cluster Disk Tables

Now that our TableSpace has been created, we can create a Cluster Disk Table. We can use the command line client

CREATE TABLE my_disk_data_table (
….
)
TABLESPACE tablespace1 STORAGE DISK
ENGINE = NDBCLUSTER;

or the Create Table Wizard (Fig. 12).

Fig. 12: Creating a Cluster Disk Table

6. Dropping Cluster Disk Data Objects

Please, note that there are particular rules for dropping Cluster Disk Data Objects. You have to follow a logical order. For more information, please refer to http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-disk-data-objects.html.

7. The Cluster Monitor

The Cluster Monitor (Fig. 13) allows you to monitor the status of your Cluster.

Fig. 13: The Cluster Monitor

At this stage of development it’s a simple (but useful) Window which shows information about the NDB Engine Status (at a glance or category by category) and the status, dynamic and system variables of the Server related to Cluster.

We plan to add new features for Cluster Management and Monitoring in HoneyMonitor, so stay tuned!

8. Cluster Disk Data Limitations

Disk Data Objects are subject to the following maximums:

1. Maximum number of TableSpaces: 2^32 (4294967296)
2. Maximum number of DataFiles per TableSpace: 2^16 (65535)
3. The theoretical maximum number of extents per TableSpace data file is 2^16 (65525); however, for practical purposes, the recommended maximum number of extents per DataFile is 2^8 (32768).
4. Maximum DataFile size: The theoretical limit is 64G; however, in MySQL™ 5.1, the practical upper limit is 32G. This is equivalent to 32768 extents of 1M each.The minimum and maximum possible sizes of extents for TableSpace data files are 32K and 2G, respectively.

For known limitations of MySQL Cluster, please refer to http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations.html.

9. Cluster Disk Data Quiz

Create a LogFile Group, some TableSpaces and some Cluster Disk Tables. Then choose a TableSpace and write a query to find the list of the tables which are using that TableSpace.

10. Other Resources

It’s now time to close this article. 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:

• Getting Started Guide
• Video Tutorials
• User’s Guide

Enjoy your MySQL™ Cluster!

[HoneyMonitor is a GUI for MySQL™. 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 Plugins. 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 Plugins in MySQL™ 5.1

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

Contents

• Introduction
• Plugins Metadata
• Retrieving the list of Plugins
• The Plugins Editor
• The Plugins Tab of the Server Properties Window
• An example of use:  a  FULLTEXT parser plugin
• Other Resources

1. Introduction

There are three ways to add new functions to the MySQL™ Server:

• through the User-Defined Function (UDF) interface;
• by creating Stored Functions;
• starting from v.5.1, through the MySQL™ Plugin Interface (the plugin interface is intended as the successor to the older UDF interface; in some respects, the plugin API is similar to the older UDF API that it supersedes, but the plugin API has several advantages over the older interface).

The UDFs are compiled as object files (.dll, .so) and then added to and removed from the server dynamically.
The Stored Functions are written using SQL statements rather than by compiling object code.
The Plugins are compiled as object files (.dll, .so) and then installed to and uninstalled from the server dynamically.

For more information about Plugins, please visit http://dev.mysql.com/doc/refman/5.1/en/plugin-api.html.

2. Plugins Metadata

In the mysql database there is a table -  mysql.plugin – where all the information about your Plugins are stored:

mysql> DESCRIBE mysql.plugin;
+——-+———–+——+—–+———+——-+
| Field | Type      | Null | Key | Default | Extra |
+——-+———–+——+—–+———+——-+
| name  | char(64)  | NO   | PRI |         |       |
| dl    | char(128) | NO   |     |         |       |
+——-+———–+——+—–+———+——-+
2 rows in set (0.01 sec)

There is also a table in the information_schema database:

mysql> DESCRIBE INFORMATION_SCHEMA.PLUGINS;
+————————+————-+——+—–+———+——-+
| Field                  | Type        | Null | Key | Default | Extra |
+————————+————-+——+—–+———+——-+
| PLUGIN_NAME            | varchar(64) | NO   |     |         |       |
| PLUGIN_VERSION         | varchar(20) | NO   |     |         |       |
| PLUGIN_STATUS          | varchar(10) | NO   |     |         |       |
| PLUGIN_TYPE            | varchar(80) | NO   |     |         |       |
| PLUGIN_TYPE_VERSION    | varchar(20) | NO   |     |         |       |
| PLUGIN_LIBRARY         | varchar(64) | YES  |     | NULL    |       |
| PLUGIN_LIBRARY_VERSION | varchar(20) | YES  |     | NULL    |       |
| PLUGIN_AUTHOR          | varchar(64) | YES  |     | NULL    |       |
| PLUGIN_DESCRIPTION     | longtext    | YES  |     | NULL    |       |
| PLUGIN_LICENSE         | varchar(80) | YES  |     | NULL    |       |
+————————+————-+——+—–+———+——-+
10 rows in set (0.01 sec)

3. Retrieving the list of Plugins

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

Fig. 1: Server Objects List - Plugins

You can install / uninstall a Plugin using node’s popup menu.

4. The Plugins Editor

The Plugins Editor (Fig. 2), allows you to install a Plugin. To successfully install a Plugin, you must copy it in the Plugin Directory of the Server (the Editor helps you because it includes a specific field for the Plugin Directory – not editable, of course).

Fig. 2: The Plugins Editor

Let’s install a plugin using the Plugin Editor.

For this tutorial I will use the “Simple N-Gram (bi-gram) FULLTEXT parser plugin for MySQL 5.1+” - http://mysqlbigram.googlepages.com/.

We will connect to a MySQL™ Server running on a Linux Machine (Ubuntu) through a SSH Connection.

1. open the Plugins Editor and read your Plugin Directory
2. download the plugin library to the Plugin Directory (or ask your system administration to do that) – Fig. 3

Fig. 3: Getting the Plugin

1. insert the “Plugin Name” and the “Library Name” in the Plugin Editor and click on the save button (Fig. 2)

After the installation of the Plugin, the SQL Tab (Fig. 4) – now enabled - will contain the SQL syntax for re-installing the current Plugin.

Fig. 4: The Plugin Editor - SQL Tab

Note: you can open the “Server Properties Window” to read the value of the variable plugin_dir (Fig. 5).

Fig. 5: Server Properties Window - Variables Tab

5. The Plugins Tab of the Server Properties Window

In the “Server Properties Window” there is a Tab for Plugins’ management. As you can see from Fig. 6

Fig. 6: Server Properties Window - Plugins Tab

in the Server Objects List are listed only your plugins while in the Plugins’ Tab of the Server Properties Windows the “system” plugins are listed too. The 2 Windows use different queries:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.1.26-rc MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> SELECT `name` FROM `mysql`.`plugin`; #### The Server Objects List
+———+
| name    |
+———+
| bi_gram |
+———+
1 row in set (0.00 sec)

mysql> SHOW PLUGINS; #### The Server Properties Window
+————+——–+—————-+—————+———+
| Name       | Status | Type           | Library       | License |
+————+——–+—————-+—————+———+
| binlog     | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| partition  | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| ARCHIVE    | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| BLACKHOLE  | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| CSV        | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| MEMORY     | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| InnoDB     | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| MyISAM     | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL          | GPL     |
| bi_gram    | ACTIVE | FTPARSER       | bi_gramlib.so | GPL     |
+————+——–+—————-+—————+———+
10 rows in set (0.00 sec)

6. An example of use:  a  FULLTEXT parser plugin

Our plugin has been installed and it’s ready for use. Let’s test the ‘WITH PARSER’ option of the INDEX syntax:

First we create a Table – using the command line client or the Create Table Wizard (Fig. 7)

mysql> USE test;
Database changed
mysql> CREATE TABLE t (c VARCHAR(255));
Query OK, 0 rows affected (0.01 sec)

Fig. 7: Create Table Wizard

And then we add an Index, using the command line or the Index Editor (Fig. 8)

mysql> CREATE FULLTEXT INDEX c ON t(c) WITH PARSER bi_gram;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Fig. 8: Index Editor

Of course we can add the Index while we are creating the table, but for this tutorial I decided to to that in two steps to let you see the CREATE INDEX syntax.

7. 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:

• Getting Started Guide
• Video Tutorials
• User’s Guide

Today (August 1st 2008) I started write a series of articles to be referenced in my MySQL™ 5.1 Use Case Report that I will submit later this month to the Community Team for the MySQL™ 5.1 Use Case Competition.

I guess UC Reports should contain real world use of MySQL™ 5.1. So if I am not mistaken you can submit a Report on partitioning, for instance, if you are using v. 5.1 because you need partitioning and you can include in your Report the reason you needed partitioning and a description of your development and deployment environment.

Now, I am not using Partitioning (or Events or Plugins) because I *need* partitioning in my production system for a specific reason. I am a developer so I developed software on those features ;-). So I will submit some posts about new features of the version 5.1 and let others decide if my stuff can be qualified for the competition or not.

The posts already on-line are:

1. Using Federated Server in MySQL™ 5.1
2. Using Scheduled Events in MySQL™ 5.1

Next posts will be:

1. Using Plugins in MySQL™ 5.1
2. Using Partitioning in MySQL™ 5.1
3. Using Table Logging in MySQL™ 5.1
4. Using Cluster Disk Data in MySQL™ 5.1

Contents are subject to change.

[HoneyMonitor is a GUI for MySQL™. 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.

Contents

• 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> DESCRIBE mysql.servers;
+————-+———-+——+—–+———+——-+
| Field       | Type     | Null | Key | Default | Extra |
+————-+———-+——+—–+———+——-+
| Server_name | char(64) | NO   | PRI |         |       |
| Host        | char(64) | NO   |     |         |       |
| Db          | char(64) | NO   |     |         |       |
| Username    | char(64) | NO   |     |         |       |
| Password    | char(64) | NO   |     |         |       |
| Port        | int(4)   | NO   |     | 0       |       |
| Socket      | char(64) | NO   |     |         |       |
| Wrapper     | char(64) | NO   |     |         |       |
| Owner       | char(64) | NO   |     |         |       |
+————-+———-+——+—–+———+——-+
9 rows in set (0.01 sec)

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:

• Getting Started Guide
• Video Tutorials
• User’s Guide

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.

[HoneyMonitor is a GUI for MySQL™. 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 Scheduled Events . 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 Scheduled Events in MySQL™ 5.1

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

Contents

• Introduction
• The Scheduled Event Editor
  1. Performed Action
  2. Advanced
  3. Comment
  4. SQL
• Other way to create Events in HoneyMonitor
• Editing Events
• Information about the Execution of the Events
• Do you want to know / edit the value of the variable event_scheduler?
• Event Metadata
• Other Resources
• About the Author

1. Introduction

Scheduled Events have been added in MySQL™ v.5.1.6.

An Event is a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Scheduled tasks of this type are also sometimes known as “temporal triggers”, implying that these are objects that are triggered by the passage of time.

An Event can be enabled or disabled (there is also a third state: disabled on slave); if disabled the event is still present in the database but it is not executed.

For more information about Events, please visit http://dev.mysql.com/doc/refman/5.1/en/events.html

In HoneyMonitor, Events of a specific database are listed in the Server Objects List (Fig. 1)

Fig. 1: Server Objects List - Events

For more information about the Server Objects List, please refer to this post.

2. The Scheduled Event Editor

HoneyMonitor includes a Scheduled Event Editor (Fig. 2) that allow you to create, alter, rename and drop your Events.

Fig. 2: The Scheduled Event Editor

Let’s see how to use it.

No special privileges are required to use this Editor as HoneyMonitor retrieves the data it need from the information_schema database (it is not necessary that users have privileges on the mysql database). By the way, note that there is a privilege that govern the creation, modification, and deletion of events, the EVENT privilege.

I used some queries similar to the following to create a database and a user to test the Scheduled Event Editor:

CREATE DATABASE `sleto`;
CREATE USER ’sleto’@'localhost’;
FLUSH PRIVILEGES;
GRANT USAGE ON *.* TO ’sleto’@'localhost’ REQUIRE NONE;
FLUSH PRIVILEGES;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATE TEMPORARY TABLES,LOCK TABLES,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,EXECUTE,EVENT,TRIGGER ON `sleto`.* TO ’sleto’@'localhost’;
FLUSH PRIVILEGES;

Events are executed by a special event scheduler thread; the global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server. If you don’t know the value of this variable – or if you don’t know that such a variable exists – don’t worry: look at the status-bar of the Editor, and click on it to enable the Event Scheduler (Fig. 3).

Fig. 3: Event Scheduler - ON | OFF

Try to ask your system administration if you haven’t the right privileges to perform this operation.
Let’s suppose that the Event Scheduler is running (ON) and let’s create our first Event by filling the required information on the Editor’s Tabs and clicking on the ‘Save’ button.

2.1 Performed Action

The ”Performed Action” Tab (Fig. 4) allows you to edit the event statement, i.e. the specific action performed by the event.

Fig. 4: Event Editor - Performed Action

2.2 Advanced

The ”Advanced” Tab (Fig. 5) allows you to set the event properties.

Fig. 5: Event Editor - Advanced

There are two type of event:

1. one-time event (”at”): a one-time event executes one time only;
2. recurrent event (”every”): a recurrent event repeats its action at a regular interval, and the schedule for a recurring event can be assigned a specific start day and time, end day and time, both, or neither.

The ”Schedule details” frame changes according to the type of the event that is opened in the Events Editor (you can choose the event type using the ”Schedule form” combo).

When creating a new event, you may click on the ”if not exist” check to prevent an error if the event already exists.

2.3 Comment

The ”Comment” Tab (Fig. 6) allows you to set the event comment.

Fig. 6: Event Editor - Comment

2.4 SQL

The ”SQL” Tab (Fig. 7) contains the SQL syntax for recreating the current event. This tab will be enabled only after the Event creation.

Fig. 7: Event Editor - SQL

3. Other ways to create Events in HoneyMonitor

There are other ways to create an Event in HoneyMonitor. For instance, you can open the Script Editor and create the Event writing and executing a SQL query. You can also use the command builder if you don’t remember exactly the syntax (Fig. 8)

Fig. 8: Script Editor - Writing the syntax for Event’s creation

4. Editing Events

You can open an Event in the Event Editor to enable / disable it or change some of its properties (and rename it as well). You can see also some metadata like the creation date (Fig 9).

Fig. 9: Event Editor - Editing an Event

5. Information about the Execution of the Events

Is there a way to know if an event has been executed successfully?

Sure. You can check the Server Error Log. To do this in HoneyMonitor, open the Server Logs Management Window. You can also click on the button “Print Debug Information” to get more info  - but make sure you have the right privileges to perform this operation -  (Fig. 10).

Fig. 10: Server Logs Management - Error Log

6. Do you want to know / edit the value of the variable event_scheduler?

HoneyMonitor allows you to choose your preferred way to perform a particular task: you choose the way you prefer.

Below you find some possible solutions:

1. Open the Server Properties Window and go to the Tab “Variables”;

2. Use the Metadata Catalog (Global Variables visualization);

3. Open the Performance Tuning Monitor and go to the Tab “Variables”;

4. Open the Event Editor and check its status bar;

5. Open the Script Editor and create and execute a one-line script which contains the query “SHOW VARIABLES LIKE ‘event_scheduler’;”

6. Click on the Utility / MySQL Command Line Client menu to open a command line client and execute the query “SHOW VARIABLES LIKE ‘event_scheduler’”;

Do you want to know/edit the value of the variable event_scheduler (or other variable) but you don’t remember the Type, the Handle and the Description of that variable?

Open the Server Properties Window and go to the Tab “Variables” (Fig. 11) to see those information!

Fig. 11: Server Properties Window - Variables’ List

7. Event Metadata

The source of metadata for a Scheduled Event is the table information_schema.events (Fig. 12).

Fig. 12: Event Metadata

8. 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:

• Getting Started Guide
• Video Tutorials
• User’s Guide

9. 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.

I hear now about a new competition for the MySQL® Users. The ‘MySQL® 5.1 Use Case Competition’ has been just launched by the Community Team @ SUN|MySQL.

If you submit a MySQL® 5.1 Use Case Report to the Community Team by 31 August 2008 you will have a chance of winning:

• 1st-3rd prize: A MySQL Conference & Expo 2009 Pass, including a dinner with MySQL co-founder Michael “Monty” Widenius
• 4th-10th prize: MySQL® Community Contributor T-shirts
• 11th-20th prize: A Sakila mascot (MySQL®’s pet dolphin)

More information at http://forge.mysql.com/wiki/MySQL_5_1_Use_Case_Competition.

[Reminder for the Community Team: the page http://forge.mysql.com/wiki/FullContributorsList should be updated.]