Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 36 Next 6 Older Entries

Displaying posts with tag: udf (reset)

MySQL and PostgreSQL JSON functions: do they differ much?
Employee_Team +2 Vote Up -0Vote Down
As author of MySQL JSON functions I am also interested in how development goes in another parties. JSON functions and operators in PostgreSQL, indeed, have great features. Some of them, such as operators, I can not do using UDF functions only. But lets see how these functions are interchangeable.

Note: all PostgreSQL examples were taken from PostgreSQL documentation.

First topic is syntax sugar.

 PostgreSQL MySQL

Operator ->

postgres=# select '[1,2,3]'::json->2;
 ?column?
----------




  [Read more...]
How to install MySQL 10.0.6-MariaDB and to compile lib_mysqludf_preg on CentOS 6.4
+0 Vote Up -0Vote Down

In short, during an optimization or a MySQL query involving a lot of replace(replace(replace…))) I decided to compile PREG as lib_mysqludf_preg from UDF Repository for MySQL in a view to use PCRE functions directly in MySQL.

Too bad MySQL still cant not use functions for index creation, but this is not the theme of this post.

So, fist install MariaDB 10.0.6:
set the MariaDB 10.0.6 repo, I used the ftp mirror at ftp.ulak.net.tr


root@seik-centos-01:[Wed Dec 18 23:55:12][/tmp]$ cat /etc/yum.repos.d/MariaDB.repo 
# MariaDB 10.0 CentOS repository list - created 2013-12-18 18:07 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
#baseurl = http://yum.mariadb.org/10.0/centos6-amd64
baseurl =

  [Read more...]
JSON UDFs: is it hard to type keys?
Employee_Team +4 Vote Up -0Vote Down

Currently, if you want to search a key using JSON UDFs you have to specify each its part as a separate argument to the function: JSON_CONTAINS_KEY(doc, 'root', 'child', 'child of child', 'child of child of child', etc.....). This way of working with parameters is easy for developer, less error-prone, but can be not very beautiful.

I was suggested by some of users to change it to '/parent/child/child-of-child/...' or to 'parent:child:child-of-child:...' There are, probably, can be other suggestions. What do you like the best? How do you feel about current style with separate argument for each key element? Should we change or extend this syntax?

New cmake options for JSON UDFs.
Employee_Team +1 Vote Up -0Vote Down

Two weeks ago I announced new maintenance release of JSON UDFs: 0.2.1. It not only contains bug fixes, how you can expect from a maintenance release, but also contains improvements in build and test scripts.

First improvement is the easier way to build JSON UDFs on Windows. In the first version building on Windows was a pane: you had to build PCRE library, copy four files to directories where Visual Studio can find them and only then build JSON functions themselves. Now you can build this way too, but only if you really wish.

By default cmake will create appropriate makefiles for bundled PCRE sources and for JSON functions. Only command you need to prepare sources is:

"C:\Program Files (x86)\CMake 2.8\bin\cmake.exe" -G "Visual Studio 11 Win64" . -DMYSQL_DIR="/path/to/mysql/installation"   





  [Read more...]
Last element for JSON array: what do you think?
Employee_Team +2 Vote Up -0Vote Down

After I released maintenance release of JSON UDFs last week it is time to think about which features I should implement in upcoming major version.

Many users asked me about the possibility to explicitly specify if they want to append last element to a JSON array. This feature can be made for two functions: json_append and json_set.

I have four ideas of how to implement this. All have pros and contras.



  • Create new function called json_append_last which will work exactly like json_append, but it will add the element to the end of array. I.e., for JSON document {"colors": ["red", "green", "blue"]} json_append_last(doc, 'colors', 'orange') returns








  •   [Read more...]
    JSON UDF functions version 0.2.1 have been released.
    Employee_Team +4 Vote Up -0Vote Down

    Today new version of JSON UDF functions: 0.2.1 was released. This is maintenance release which added no new functionality and only contains bug fixes. However, it also includes improvements for build ans test procedures. As usual, you can download source and binary packages at MySQL Labs. Binary packages were build for MySQL server 5.6.14. If you want to use other version of the server, you need to recompile functions.

    What was changed? Let me quote the ChangeLog.

    Functionality added or changed:

    Added cmake option WITH_PCRE which alolows to specify if existent or bundled version of PCRE  should be used. Bundled is default on Windows. To compile with bundled version, run: "cmake . -DMYSQL_DIR=/path/to/mysql/dir -DWITH_PCRE=bundled",





      [Read more...]
    To be safe or to be fast?
    Employee_Team +0 Vote Up -0Vote Down

    When I designed first version of JSON UDFs which was reviewed only internally, I let all functions to validate input and output JSON. But my colleagues told me to remove this functionality, because it makes such functions, as json_search, json_replace or json_contains_key deadly slow if they find the occurrence in the beginning of the long document. And first published version of JSON UDFs: 0.2.0 has not this functionality. What we expected is that users would call json_valid if they want to be 100% sure the document is valid.

    But I was not surprised that some users expect JSON functions to work as it was in the first version: validate first, then process. For example, Ammon Sutherland writes: "json_set - according to the documentation a

      [Read more...]
    JSON UDFs have own bugs.mysql.com category
    Employee_Team +0 Vote Up -0Vote Down

    JSON UDFs got own category at MySQL Bugs Database: "Server: JSON UDF"


    Use this category to post new bug reports and vote for existent.

    Yet another UDF tutorial
    Employee_Team +1 Vote Up -0Vote Down

    Some time ago I wrote a blog post describing a way I use to verify MySQL Server bugs. But my job consists not only of bugs which can be verified just by passing SQL queries to the server.

    One of such examples is UDF bugs.

    MySQL User Reference Manual is good source of information for those who want to write UDF functions, as well as book "MySQL 5.1 Plugin Development" by  Sergei Golubchik and Andrew Hutchings. But while the book describes in details how to write UDFs it was created in time when current MySQL version was 5.1 and does not contain information about



      [Read more...]
    Statistical functions in MySQL
    +3 Vote Up -0Vote Down

    Even in times of a growing market of specialized NoSQL databases, the relevance of traditional RDBMS doesn't decline. Especially when it comes to the calculation of aggregates based on complex data sets that can not be processed as a batch like Map&Reduce. MySQL is already bringing in a handful of aggregate functions that can be useful for a statistical analysis. The best known of this type are certainly:

    Read the rest »

    MySQL Infusion UDF for statistical analysis
    +0 Vote Up -0Vote Down

    Since I use MySQL for the statistical analysis on a project, I wanted to optimize the database queries and learned a lot about stuff like number theory, set theory and partial sums. I took my MySQL UDF, I've published two years ago, for this purpose and added new functions for a deeper statistical analysis. The project is around for a while, so it's time to share things with the public to start a discussion of how things could be further optimized. The source and a small documentation can be found on Github:

    Read the rest »

    Working with IP's in MySQL and MariaDB - Part 2
    +0 Vote Up -0Vote Down
    Use DNS directly from your database

    mysql> SELECT lookup('localhost');
    +---------------------+
    | lookup('localhost') |
    +---------------------+
    | 127.0.0.1 |
    +---------------------+
    1 row in set (0.00 sec)

    mysql> SELECT reverse_lookup('127.0.0.1');
    +-----------------------------+
    | reverse_lookup('127.0.0.1') |
    +-----------------------------+
    | localhost |
    +-----------------------------+
    1 row in set (0.00 sec)

    This is how you install these functions.
  • Build udf_example.so which is in your mysql source. (make udf_example.so)
  • Copy the udf_example.so file from your build directory to your plugin_dir.
  • Create the lookup and reverse_lookup


















  •   [Read more...]
    Memcached Functions for MySQL 1.1 released!
    +3 Vote Up -0Vote Down

    I’m pleased to announce the release of Memcached Functions for MySQL, version 1.1. It’s been a while since the 1.0 release, and I’ll use writing a book and having a baby as an excuse, but nevertheless, this release has several changes and new features:

    • Fixed all the calls to libmemcached that were broken when libmemcached changed. This release works with Libmemcached 0.44
    • Added my own string functions as the libmemcached string functions I relied on before were made local/private from the shared library
    • Added memc_get_cas() and memc_get_cas_by_key() functions. You can now obtain the CAS value of an item! For instance:
    mysql> select memc_servers_set('localhost:11211');
    +-------------------------------------+
    | memc_servers_set('localhost:11211') |
    +-------------------------------------+
    |
      [Read more...]
    MySQL - My new playground
    +0 Vote Up -0Vote Down

    Note: It was my first attempt using the MySQL UDF Api back then. The library is still maintained and got some new features. Check out the new MySQL Infusion UDF.

    Read the rest »

    Book review: MySQL 5.1 plugin development
    +8 Vote Up -0Vote Down
    MySQL 5.1 Plugin Development,
    by Sergei Golubchik and Andrew Hutchings.
    Packt Publishing, 2010.
    Executive summary: Highly recommended. If you want to develop MySQL extensions, buy this book. It's a must, written by two expert professionals who probably know more than anyone else on this matter. The book is full of practical examples explained with the theoretical information necessary to make it stick.

    This book fills a gap in the world of MySQL documentation. Although the MySQL docs are extensive and thorough, to the point that sometimes you wished that the




      [Read more...]
    A review of MySQL 5.1 Plugin Development by Golubchik and Hutchings
    +11 Vote Up -0Vote Down

    MySQL 5.1 Plugin Development

    MySQL 5.1 Plugin Development, by Sergei Golubchik and Andrew Hutchings, Packt 2010. About 250 pages. (Here’s a link to the publisher’s site.)

    This book is well worth reading for anyone interested in MySQL internals. I learned a lot from it. It is well-written and understandable. I cannot say that I’m planning to write storage engines or more advanced plugins, but I have a great many ideas how to improve MySQL, and I now understand more clearly which of those are suitable to

      [Read more...]
    Multi dimensional cubes in MySQL through Gearman
    +4 Vote Up -0Vote Down



    I gave two presentations about Gearman at the Linux.conf.au. As part of the preparation for these talks, I created several sample applications. One of them, about remote replication administration, I will cover in a separate post. The most amazing one, which I cover here, is a quick and painless solution for multiple level crosstabs in MySQL.

    Some background is needed. Crosstabs (also called data cubes or pivot tables, have been one of my favorite hacks for long time. In 2001 I wrote an article about a simple




      [Read more...]
    MySQL University: Memcached Functions for MySQL
    Employee +0 Vote Up -0Vote Down

    This Thursday (November 19th, 14:00 UTC), Patrick Galbraith will present memcached Functions for MySQL (UDFs). This session is about a suite of functions available to use with MySQL that allow you to store, retrieve and delete data, as well as most of the functions and operations that are available with libmemcached, such as server connectivity to the client, server status, client behaviors, and more. You can combine the fetching of data from one or more tables with the fetching of data from memcached and be able to apply any SQL operations on that result set such as LIMIT, sorting and other conditional operations.

    For MySQL University sessions,

      [Read more...]
    MySQL University: Memcached Functions for MySQL
    Employee +0 Vote Up -0Vote Down

    This Thursday (November 19th, 14:00 UTC), Patrick Galbraith will present memcached Functions for MySQL (UDFs). This session is about a suite of functions available to use with MySQL that allow you to store, retrieve and delete data, as well as most of the functions and operations that are available with libmemcached, such as server connectivity to the client, server status, client behaviors, and more. You can combine the fetching of data from one or more tables with the fetching of data from memcached and be able to apply any SQL operations on that result set such as LIMIT, sorting and other conditional operations.

    For MySQL University

      [Read more...]
    MySQL University: Memcached Functions for MySQL
    Employee +0 Vote Up -0Vote Down

    This Thursday (November 19th, 14:00 UTC), Patrick Galbraith will present memcached Functions for MySQL (UDFs). This session is about a suite of functions available to use with MySQL that allow you to store, retrieve and delete data, as well as most of the functions and operations that are available with libmemcached, such as server connectivity to the client, server status, client behaviors, and more. You can combine the fetching of data from one or more tables with the fetching of data from memcached and be able to apply any SQL operations on that result set such as LIMIT, sorting and other conditional operations.

    For MySQL University

      [Read more...]
    Gearman for MySQL
    +2 Vote Up -0Vote Down


    If you haven't yet heard about Gearman, it's time to have a look at it. Its distributed client server architecture are a perfect match for today's cloud oriented applications.
    When talking about Gearman, much stress is often given on its scalability features, such as map/reduce and distributed loads. But Gearman has also a distinctive advantage in the feature department, because of its design that I like to describe as cooperation of the fittest.
    While a traditional application is written entirely in a given language, or it must find ways of integrating multiple languages parts, Gearman encourages easy cooperation between parts written in different



      [Read more...]
    memcached Functions for Drizzle now in main tree!
    +0 Vote Up -0Vote Down
    What a great day! I see that the drizzle team has merged in my (and Padraig O'Sullivan's) memcached Functions for Drizzle (UDFs). I'm really glad to have this in drizzle now as it adds a means of interacting with memcached from within Drizzle. I have most functions from the MySQL branch implemented now as well. I'm extremely grateful to Padraig O'Sullivan for getting this project off the ground. I was a bit stuck with the new API and C++ when I first attempted these and he designed the class setup and had the major functions working which I then picked up and added more functions as well as tests. I was also glad to have the drizzle team add in my sleep() UDF which allowed me to test expirations in these memcached functions.

    These functions have some similar, but very little code from the memcached Functions for MySQL. The new UDF API is completely different than MySQL's UDF API. You have to

      [Read more...]
    Having fun with Tokyo Tyrant
    +0 Vote Up -0Vote Down
    I decided to thee other day to investigate using Tokyo Tyrant because I was interested in the fact it has a memcached protocol and I wanted to get a feel for how it works with my memcached functions for MySQL (UDF). Matt Ingenthron came up with a good term recently: Mem-capable, which Tokyo Tyrant is. I find any key/value storage to be of great interest, particularly those that you simply change the port your memcached client is connecting and simply use it the same way you would memcached.

    So, just what is Tokyo Tyrant?

    Tokyo Tyrant is a database server, written by Mikio Hirabayashi, for Tokyo Cabinet. It provides for concurrent remote connections to Tokyo Cabinet . It provides its own binary protocol as well as a memcached and HTTP compatibility protocols. It also has C, Perl, PHP, Java, Erlang, Python and Ruby bindings. Other features Tokyo Tyrant offers:

    * Hot backup and





      [Read more...]
    Memcached Functions for MySQL and Moxi: a great combination
    +0 Vote Up -0Vote Down
    Working at Northscale has been a lot of great fun lately! I have finally figured out how to get puppet to set up a stock Amazon instance with everything we need and have been impressed with how you can automate system setup with puppet. I remember when I worked at Grazr how much of a hassle it was for us to have to rebuild systems. Something like Puppet would have been a godsend.

    Today I though I would post about how cool moxi is. If you don't know, moxi is a memcached proxy (http://labs.northscale.com/moxi/) which allows you to move any complexity of having to set up the list of memcached servers you are using. Also, moxi has some great features such as:

    * De-duplication of requests for popular keys
    * A front cache, L1 cache to avoid network hops
    * Fire and forget SET (Like an async SET) this means "set a value, but don't wait to





      [Read more...]
    capttofu @ 2009-07-31T12:23:00
    +0 Vote Up -0Vote Down
    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











      [Read more...]
    Memcached Functions For MySQL 0.9 Released
    +0 Vote Up -0Vote Down
    I'm glad to announce the release of Memcached Functions for MySQL version 0.9. This version contains a couple bug fixes:

    * Switched to using calloc() instead of malloc(), check for return value
    * Changed memc_servers_set() to not create a new connection when one already exists, free first

    Also added:

    * memc_server_version()

    mysql> select memc_server_version('127.0.0.1:11211')\G
    *************************** 1. row ***************************
    memc_server_version('127.0.0.1:11211'): Server: 127.0.0.1 (11211) version 1.2.6

    I'm glad to get development moving along again. The book really took a ton of time, so now I can get this project moving along. Version 0.10 I'll be adding even more functionality. I'll get some ideas at the user's conference next week as well. One thing I need to get working properly is the ability to set a value in a












      [Read more...]
    Memcached Functions for MySQL version 0.8 Released
    +0 Vote Up -0Vote Down
    I'm pleased to announce the release of the Memcached Functions for MySQL, version 0.8.

    This version includes a bunch of fixes for behavior setting and retrieval. You now have:

    Ability to fetch a behavior:

    memc_server_behavior_get();

    All behaviors can be set now successfully with memc_server_behavior_set()

    Boolean:

    select memc_behavior_set('MEMCACHED_BEHAVIOR_BUFFER_REQUESTS', '1');

    Name of a value (string):

    select memc_behavior_set('MEMCACHED_BEHAVIOR_HASH','MEMCACHED_HASH_MD5' );

    Numeric:

    select memc_behavior_set('MEMCACHED_BEHAVIOR_SOCKET_SEND_SIZE', 60000);

    Also, you can now list the distribution types to use when calling memc_server_behavior_set('MEMCACHED_BEHAVIOR_DISTRIBUTION', ''), which produces a list of all the distrubution types:

    select























      [Read more...]
    Repository for MySQL UDFs: new sys_eval() function
    +0 Vote Up -0Vote Down
    Thanks to Bernardo Damele, the lib_mysqludf_sys package hosted by the Repository for MySQL UDFs can now boast a new sys_eval() function. You can read the details here on his blog. Bernardo also created a similar function for PostgreSQL.

    Thank you,

      [Read more...]
    Memcached UDFs for MySQL version 0.6 Released
    +0 Vote Up -0Vote Down
    I've taken some time out of writing my book to get some coding down, and I'm pleased to announce the release of the Memcached Functions for MySQL, version 0.6. This release includes:

    * Complete rewrite of error handling
    * Build configuration fixes/improvements from Trond Nordby (Thanks!)
    * Fixed memc_server_count
    * More tests

    You can find the release information-- repository and source at:

    http://tangent.org/586/Memcached_Functions_for_MySQL.html

    As well as

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

    Have fun! More to come...
    Writing to the MySQL error log
    +0 Vote Up -0Vote Down
    In almost all application development situations, one needs to log debug information now and then. In almost all production systems, one needs to log serious error events somewhere too.

    So, what can you do? Create a log table? Sprinkle your code with SELECT 'Debug: ...' statements?

    At the UDF Repository for MySQL, we now have a solution to log messages to the MySQL error log: a user-defined function called log_error().

    Currently it is all very crude: the log_error function takes one argument and writes it to the mysql_error





      [Read more...]
    Showing entries 1 to 30 of 36 Next 6 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.