PlanetMysql.ru — информация о СУБД MySQL

MySQL GIS functions don't seem to include a Midpoint function; it's trivial to write one, but I thought posting this might save someone a few minutes. Enjoy!

DROP FUNCTION IF EXISTS midpoint;
DELIMITER //
CREATE FUNCTION midpoint(line GEOMETRY)
RETURNS POINT DETERMINISTIC
BEGIN

IF NumPoints(line) != 2 THEN
RETURN NULL;
END IF;

RETURN GeomFromText(CONCAT('POINT(',(X(StartPoint(line)) +X(EndPoint(line))) / 2,' ',(Y(StartPoint(line))+Y(EndPoint(line))) / 2,')'));
END //
DELIMITER ;

The feature I announced some time ago http://www.mysqlperformanceblog.com/2009/06/08/impossible-possible-moving-innodb-tables-between-servers/ is now available in our latest releases of XtraBackup 0.8.1 and XtraDB-6.

Now I am going to show how to use it (the video will be also available on percona.tv).
Let's take tpcc schema and running standard MySQL ® 5.0.83, and assume we want to copy order_line table to different server. Note I am going to do it online, no needs to lock or shutdown server.

To export table you need XtraBackup, and you can just specify table or table by mask:

Now in /data/vadim/mysql/export we have backup but only with ibdata1 and order_line.ibd files

Second step is to prepare backup, but with special option "export"

When it's done we have two files order_line.ibd and order_line.exp in directory /data/vadim/mysql/export/tpcc. ibd is regular InnoDB ® file, and exp is file with special export information. Both files should be copied to remote server.

Now on remote server you have to run MySQL server ® with XtraDB6 storage engine, you can take one of our binary builds with 5.1.36.

On new server we run
set global innodb_expand_import=1; to put XtraDB into extended import mode, and now we need to create empty table with the same table definition as on old servers:

Next step is to remove created .ibd file, it's done by command:

And now you copy both order_line.ibd and order_line.exp to MysqlDataDir/testimport dir. When it's done, final import command:

now some magic happens and you can see progress of import in error.log

When finished quick check

New table has been imported!

---

Entry posted by Vadim | No comment

Add to: | | | |

I'm pleased to announce the release of the Memcached Functions for MySQL, version 1.0.

This release contains several changes, per the ChangeLog:

1.0 Thursday, July 30, 2009 12:00:00 EST 2009
* Fixed issue of setting NULLs with user-defined variables (Thanks to
Jean-Jacques Moortgat at aol dot com !)
* Fixed issue of obtaining a NULL value FROM memcached
* All set functions now return 0 (failure) or 1 (success)
* Other cleanups
* More tests

Importantly, there was an issue that I blogged about several days ago where in the UDF API, if you pass a user-defined variable that is set to NULL to memc_set(), the length of the argument is 8192 even though the value of the argument itself is NULL, which caused much unhappiness in MySQL (crash). That is fixed by setting the length to 0 if the argument itself is NULL. Also fixed is obtaining the NULL value from memcached using the memc_get() UDF. If the value is NULL (meaning libmemcached sees a value stored but the length is 0), the "is_null" boolean pointer in the value function must be set to true.

This bug is a great find and possible due to the work of Jean-Jacques Moortgat from AOL. I added a test containing this bug and can happily report that there is no longer a crash.

Another change, and this is one you may want to check any triggers you use or functionality where you rely on the return value of any set function - set, append, prepend, delete, add, replace. Formerly, the return value from the C underlying C libmemcached function was returned. This resulted in success being 0, and any > 0 number was the error code from the libmemcached library. While I was working on the memcached UDFs in Drizzle, I decided to use 1 for success, 0 for error, which makes more sense logically.

You can get the latest source via Launchpad Bazaar repo:

lp:memcached-udfs

There is also a source download at Launchpad:

https://launchpad.net/memcached-udfs/+download

As well as:

http://patg.net/downloads/memcached_functions_mysql-1.0.tar.gz

Have fun, and thanks for any feedback or bug reports!

I realized the other day that I need the sleep() function, which up until this morning, Drizzle did not have, for testing the memcached Drizzle UDFs. Well, now it does:

drizzle> select sleep(3);
+----------+
| sleep(3) |
+----------+
| 0 |
+----------+
1 row in set (3 sec)

drizzle> select sleep(0);
+----------+
| sleep(0) |
+----------+
| 0 |
+----------+
1 row in set (0 sec)

drizzle> select sleep(20);
+-----------+
| sleep(20) |
+-----------+
| 0 |
+-----------+
1 row in set (20 sec)

As you see, it returns zero, just as the MySQL sleep() function does.

The code can be had at:

lp:~capttofu/drizzle/sleep

I’m going to be giving a talk at the PHP user group here in Portland, OR on August 11th. Details can be found here. If you’re in the Portland area please join the group and come check it out! This will be similar to the Boston MySQL Meetup group talk I gave earlier this month with Patrick, but with more focus on PHP.

There is a lot happening with Gearman right now. Last week at OSCON we received quite a bit of good feedback with the project which will certainly help direct our priorities moving forward. In the past couple weeks we’ve had the following Gearman releases:

• Gearman C Server and Library 0.9 - This added libsqlite3 and libpq (PostgreSQL) persistent queue support. Thanks to Cory Bennett for the SQLite patch!
• Gearman PHP Extension 0.5.0 - This release includes a few more functions from the C library and bug fixes. Thanks James!
• Gearman Perl XS 0.4 - This release includes a few more functions from the C library and bug fixes too. Thanks Dennis!
• Gearman PostgreSQL User Defined Functions 0.1 - This is another set of UDFs like the Drizzle and MySQL UDFs. Thanks to Selena for helping put these together for the pgDay talk!

Monty Taylor has a new SWIG wrapper for the Gearman C library that will give us new APIs for things like Python, Ruby, and LUA. Dustin Sallings created a twisted module for Gearman and a OCaml API. I’m also helping a group of students at Portland State University with a Java Messenging Service (JMS) provider that uses Gearman. At the last meeting the group reported it is now functional so I hope to see a 0.1 release sometime soon. :)

Brian and I received good feedback on our OSCON talks as well. Many people were excited, but a few folks thought the presentations lacked detailed examples. The goal of the talks were to give a good overview of the server and APIs, but we’ll certainly try to provide better examples both through the website and in future presentations. Thanks to everyone who attended!

I was actually exploring inotify-tools for something else, but they can also be handy for seeing what goes on below a mysqld process. inotify hooks into the filesystem handlers, and sees which files are accessed. You can then set triggers, or just display a tally over a certain period.

It has been a standard Linux kernel module since 2.6.13 (2005, wow that’s a long time ago already) and can be used through calls or the inotify-tools (commandline). So with the instrumentation already in the kernel, apt-get install inotify-tools is all you need to get started.

 # inotifywatch -v -t 20 -r /var/lib/mysql/* /var/lib/mysql/zabbix/*
Establishing watches...
Setting up watch(es) on /var/lib/mysql/mysql/user.frm
OK, /var/lib/mysql/mysql/user.frm is now being watched.
[...]
Total of 212 watches.
Finished establishing watches, now collecting statistics.
Will listen for events for 60 seconds.
total  modify  filename
2371   2371    /var/lib/mysql/relay-log.info
2148   2148    /var/lib/mysql/master.info
1157   1157    /var/lib/mysql/ib_logfile0
24     24      /var/lib/mysql/zabbix/
24     24      /var/lib/mysql/zabbix/history.ibd
8      8       /var/lib/mysql/zabbix/trends_uint.ibd
6      6       /var/lib/mysql/zabbix/items.ibd
5      5       /var/lib/mysql/ibdata1

This is just a limited example from a dev box, but you can see the benefit. You can see which files have been accessed, in what way, and how many times over the specified period. Consequently this provides the most insight if you’re using innodb-file-per-table (or MyISAM) rather than a single InnoDB tablespace. But of course it depends a bit on what you’re looking for.

I ran into this little problem today; when dividing two whole number the result MySQL gave me was nowhere near as precise as I needed it to be. The values were truncated which caused me some errors in my application.

Example:
mysql> select 1*0.00001;
+-----------+
| 1*0.00001 |
+-----------+
| 0.00001 |
+-----------+

Ok, that looks fine. Let's do the same thing using division:

mysql> select 1/100000;
+----------+
| 1/100000 |
+----------+
| 0.0000 |
+----------+

Oops! What happened?

The issue is that the maximum precision of the result value depends on the number of decimal places in the arguments. Since the second version uses two whole numbers, the result uses the default number of decimal places, which is 4. So you really have to take care to make sure you're getting the precision you want out of your math operations!

To MySQL's credit, the behavior is clearly documented (however, I maintain it's still a bit troublesome because how often would you expect the division operator to have a list of instructions and caveats?)

There are at least a couple simple solutions:
mysql> SELECT CAST(1/100000 AS DECIMAL(8,5) );
+---------------------------------+
| CAST(1/100000 AS DECIMAL(8,5) ) |
+---------------------------------+
| 0.00001 |
+---------------------------------+

Or

mysql> SET div_precision_increment=5;
mysql> SELECT 1/100000;
+----------+
| 1/100000 |
+----------+
| 0.00001 |
+----------+

I’ve been editing a task description in our worklog:
WL#706 Add EXPLAIN support for other statements (UPDATE/DELETE).

Currently MySQL supports EXPLAIN SELECT but there have been requests for EXPLAIN UPDATE, EXPLAIN DELETE, and so on. In fact it’s my impression that the proposition in WL#706 (transform the statement to a SELECT and then say EXPLAIN SELECT) is not what people want. But that’s okay, while a worklog is an early stage we allow simple ’solutions’ that might disappear later.

The latest release has a lot of great improvements that you’ll be sure to enjoy.

• ExtJS upgraded to version 3
• Host, User, Client, Alerts pages all updated to use GridPanels
• Documentation on the VMware Virtual Appliance added to docs
• More JSON responders to replace static page status events
• Login no longer has an “OK” on successful authentication
• New CSS UI theme – Slate

As usual you can download here: http://kontrollsoft.com/software-downloads