Slashdot Mirror

← Back to Stories (view on slashdot.org)

MySQL 5.1 Plugin Development

Posted by samzenpus on from the read-all-about-it dept.

Michael J. Ross writes "If you were to ask some database developers to cite their favorite strategies for expanding the functionality of the relational database management systems with which they work, you would probably hear a variety of answers. One individual might recommend the use of an alternate database engine optimized for the given application. Another might explain the many advantages of using stored procedures to replace SQL queries embedded in the source code of any programs that connect to databases. But one answer you likely would not receive involves changing the internals of the database engine itself. With the latest major release of MySQL, developers using that particular RDBMS are now able to extend the capabilities of the built-in database engines, by creating plug-ins. This is the topic of a book by Sergei Golubchik and Andrew Hutchings: MySQL 5.1 Plugin Development." Read on for the rest of Michael's review. MySQL 5.1 Plugin Development author Sergei Golubchik and Andrew Hutchings pages 288 pages publisher Packt Publishing rating 9/10 reviewer Michael J. Ross ISBN 978-1849510608 summary A practical and example-rich introduction to developing MySQL plug-ins. This title was released by Packt Publishing on 26 August 2010, under the ISBN 978-1849510608. On the publisher's page for the book, visitors will find a detailed description, the table of contents, a sample chapter (the fourth one in the book, "Information Schema Plugins," as a PDF file), and links to purchase the print and/or electronic versions of the book. There is also a link for downloading all of the sample code used in the book, except for the commands found in the first chapter. The sample code is sufficient to demonstrate the complexity of the subject area, and thus it is good that both authors possess a lot of experience in all of the primary technologies discussed in the book — particularly MySQL. In fact, Sergei Golubchik was one of the principal architects of the MySQL Plug-in application programming interface (API). Incidentally, in the "About the Authors" section, we read that he "has continued as a MySQL AB employee since 2000," which makes it sound as though he is still employed there (now Oracle); but then we are told that he resigned to join a startup firm, which is a bit confusing.

The book spans 288 pages, most of which are organized into ten chapters, followed by an appendix and an index. The first chapter explains the details of how to compile, link, and install MySQL User Defined Functions (UDFs), as well as proper MySQL plug-ins. For those people working on Windows platforms, numerous screenshots are provided, showing how to work with Microsoft Visual Studio (which is freely available). Readers learn how to use MySQL command-line utilities for building and packaging plug-ins, and the options needed to do so. The subsequent chapter focuses on UDFs — both normal and aggregate ones — which are technically not part of the MySQL Plugin API, although they may be in the future. However, they can be thought of as precursors to true plug-ins, because they are written in C/C++, loaded dynamically at runtime, and extend the capabilities of the MySQL server — in this case, by being callable from within SQL statements. The authors explicate how to install and utilize UDFs, and provide several examples.

The book's remaining chapters explore different types of plug-ins, starting with the most basic kind of all, Daemon plug-ins, which can run code utilizing a dedicated thread in the mysqld server process. Readers are shown how Daemon plug-ins are structured — including their declarations, types, status variables, and configuration system variables. To demonstrate these components, the authors dissect four separate sample plug-ins, line by line, with a great deal of helpful commentary. The next two chapters, 4 and 5, delve into schema-related plug-ins, starting with those that create tables, and ending with more advanced topics, such as how to access and output information about a server's internal data structures. These two chapters present almost half a dozen examples, as equally detailed as those of the earlier material.

During the past several years, all relational database systems are seeing increased use of full-text parsing, for various purposes. Chapters 6 and 7 show the reader how to create plug-ins that supplement the full-text search capabilities already baked into MySQL. The first sample plug-in presented by the authors could be used by PHP programmers for parsing their scripts, while another sample could be used by developers who need to match user input (which may include typos), using a Soundex algorithm. The final three chapters cover many aspects of storage engines, ranging from a basic read-only engine to a more complex one that supports indexes. The book concludes with an appendix that surveys the primary enhancements to the Plug-in API that database developers may see in versions of MySQL after 5.1.

On the publisher's site, there are no reported errata, but here are some that I found in just the first couple pages, to get the list started: "class [a] to" (page 1), "on [a] MySQL fork" (page 2), and "ask [the] questions" (page 2). Also, countless phrases and sentences in the book are oddly constructed, with multi-word adjectives missing hyphens, commas used where semicolons are called for, and sometimes both mistakes committed in the same sentence, such as the very first sentence of the preface. In fact, the lead author admits that he prefers reading fiction to a dictionary. Regardless, the information and instruction provided by the authors are generally clear to the reader, and ably illustrated with the sample code.

The book and thus the reader benefit greatly from the extensive MySQL experience and knowledge of the authors, reflected in the depth of coverage of the various topics. MySQL 5.1 Plugin Development brings together valuable information that is otherwise tedious to find — scattered throughout the API source code, official documentation, and online forum threads. For any programmer interested in unleashing the full potential of their MySQL servers through the creation and use of plug-ins, this book is an essential resource.

Michael J. Ross is a freelance website developer and writer.

You can purchase MySQL 5.1 Plugin Development from amazon.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.

44 comments

  1. Awesome! by Anonymous Coward · · Score: 1, Insightful

    Cram business logic that should properly exist at the application level into a crappy SQL platform!

    1. Re:Awesome! by omnichad · · Score: 2, Interesting

      That's only partly true. For example - say you have "products" and "categories." When you delete a category, all products should either revert to a default category, no category, or move to an archive. That's a database question, not business logic. Why shouldn't the database handle the delete so that it handles the little details across multiple implementations.

    2. Re:Awesome! by magarity · · Score: 4, Insightful

      Cram business logic that should properly exist at the application level into a crappy SQL platform!

      After you work in BI for a while you'll come to realize that where business logic is kept isn't nearly as important as that it's only kept in one place.

    3. Re:Awesome! by JonySuede · · Score: 4, Insightful

      you can use a trigger and a stored proc for this.... but if you forget to document it in the written design doc, I will give you a terrible beating

      and by the way it is a business logic question, as an exercise I will let you find why

      --
      Jehovah be praised, Oracle was not selected
    4. Re:Awesome! by omnichad · · Score: 0

      Didn't say it had to be a plugin - just arguing against this sort of thing being in application code.

    5. Re:Awesome! by Anonymous Coward · · Score: 0

      Sometimes it is a lot more efficient to perform business logic on the database itself rather than shipping a lot of data back and forth over a network connection.

    6. Re:Awesome! by Angst+Badger · · Score: 3, Informative

      After you work in BI for a while you'll come to realize that where business logic is kept isn't nearly as important as that it's only kept in one place.

      God yes. I used to hate the idea of stored procedures and the like because that meant losing control (as an application programmer). But over time, I've come to realize that what it really means is that I don't have to worry about all the other application programmers implementing the same goddamn thing in a dozen different places.

      --
      Proud member of the Weirdo-American community.
    7. Re:Awesome! by thePowerOfGrayskull · · Score: 1

      When you delete a category, all products should either revert to a default category, no category, or move to an archive. That's a database question, not business logic. W

      A rule like that can only come from a business requirement in the first place -- or did the developer or DBA arbitrarily make that decision?

    8. Re:Awesome! by Bill,+Shooter+of+Bul · · Score: 1

      This book isn't about that. This book is about changing the storage and retrieval mechanism to best fit your applications data needs. Its the same sort of change as switching from a MS access database to a MS SQL Server, if you were moving your product from a desktop app to the web.

      --
      Well.. maybe. Or Maybe not. But Definitely not sort of.
  2. inb4 by arndawg · · Score: 0, Troll

    toy RDBMS... blablabla

  3. this sounds like a maintenance hell by Anonymous Coward · · Score: 0

    This has the potential to rapidly turn into a maintenance hell. And unless you have especially disciplined devs, it is a good way to corrupt a database...

    1. Re:this sounds like a maintenance hell by nschubach · · Score: 1

      You could think of it like MSSQL or Oracle... both of them add their own "enhancements" to the standard SQL.

      This sounds like it's just moving that "power" into the hands of the DB Admins. Sure, it could spawn some ugly stuff... but it could give you something the big players didn't think of.

      --
      Every time I start to have faith in humanity, I ruin it by driving to work between 7 and 8 am.
  4. Mozilla Firefox addons? by satuon · · Score: 1

    This might be a hit, like addons in Mozilla Firefox.

  5. Potential Security Nightmare by csciborg · · Score: 2, Interesting

    I think the ability to modify the functionality of the RDBMS could be very useful in some instances, however if the plugin is poorly crafted or maliciously crafted it could open up interesting new attack surfaces. Any thoughts from the security minded out there?

    1. Re:Potential Security Nightmare by satuon · · Score: 1

      It's true, but that is the problem of third-party software. The same issues hold for Firefox addons, and for the ability to run programs written by anyone in your PC, as opposed to, say, the iPad. It's up to the end-user to decide what to install and what not to install.

    2. Re:Potential Security Nightmare by Anonymous Coward · · Score: 0

      yo dawg,

      I heard you like programming, so I put an API in your RDBMS,
      so you can write plug-ins while you program

      meme attack.

    3. Re:Potential Security Nightmare by TheLinuxJedi · · Score: 1

      Yes, as with many plugin APIs there is the possibility of poor or malicious code opening up an attack vector. Off the top of my head I suspect the biggest risk would be from say bad string process in the Full-Text parser plugins which crashes the server. I would expect most plugins to come from in-house development where there is a special need for a unique setup and this type of user would be hacking code on the server anyway with the same (or worse) security implications. Andrew Hutchings (co-author)

    4. Re:Potential Security Nightmare by thePowerOfGrayskull · · Score: 1

      No more or less than any other plugin architecture; or even executing third party code at all. If you don't trust the provider, don't install it.

    5. Re:Potential Security Nightmare by csciborg · · Score: 1

      From your experience with the platform, are there any safeguards in place that prevent plugins from breaking out of their place in memory and reeking havoc? Are the plugins allowed to modify the underlying data structure or is there a specific interface they use to access the data?

    6. Re:Potential Security Nightmare by TheLinuxJedi · · Score: 1

      Unfortunately no... Well, you have the standard private/protected/etc... for the C++ parts. But I suspect it would not be difficult to get around those. The basic API has basic pre-defined functions, but for the more complex types (such as storage engines) you need to include some aspects of the original MySQL source code when compiling. The original idea of the MySQL Plugin API was to make it easier for people who already (or intend to) hack in raw code to the MySQL source. So the plugin is dynamically linked in like any other library, and if it crashes then the MySQL daemon crashes. I suspect (without any firm evidence) that the kind of security you are talking about would have a significant performance trade-off. It should also be noted that (at the moment) the plugins need to be compiled specifically for your version of MySQL, so in most cases you will see the source first. There is the initial work for 'services' which will end the need for this in many cases in MySQL 5.5 and MariaDB and this is talked about in the Appendix. I hope that kind of answers your question there :)

    7. Re:Potential Security Nightmare by csciborg · · Score: 1

      It answers the question nicely, thanks for the feedback.

  6. Normalisation... by Dexter+Herbivore · · Score: 1

    but do the addons account for full normalisation?

    1. Re:Normalisation... by iluvcapra · · Score: 1

      As with all things database, it's only as normalized as the meat between the chair and the keyboard wants it to be.

      --
      Don't blame me, I voted for Baltar.
  7. authentication plugins? by Culture20 · · Score: 1

    It would be nice to have something like mod_auth_kerb/ldap that worked with mysql. It's been a "bug" since 2004, and a requested feature for longer than that. I know not everyone has multiple users in a mysql DB, but quite a few do.

    1. Re:authentication plugins? by TheLinuxJedi · · Score: 3, Informative

      Yes, this plugin type is in MariaDB and should be coming to MySQL soon. There are examples in the Appendix of the book. As a side note we already have authentication plugins in Drizzle (what I work on now). Andrew Hutchings (co-author)

  8. Plug-ins to MySQL - bad idea by Animats · · Score: 1

    Database engines have to be highly reliable, and MySQL has some good ones. They're very difficult to write and get correct. There's considerable theory and formalism involved. Real Q/A is needed. This is not a place for "crowdsourcing".

  9. Plugins and datatypes by karavelov · · Score: 0

    Is it possible to define new data types in MySQL, using plugins or by other means?

  10. MySQL goes Home Cinema surround by roger_pasky · · Score: 1

    I' guessing in which table to put the subwoofer...

  11. Table Index Plugin? by Doc+Ruby · · Score: 2, Interesting

    If all I wanted to change in MySQL was the function that searched a table index for matching records, how would I go about writing a plugin for just that operation? How about a plugin that only creates/updates an index table (perhaps in a nonstandard format, to be read by my index search plugin)?

    Is it feasible to replace just those two feature groups with a plugin, rather than the entire database engine?

    --

    --
    make install -not war

    1. Re:Table Index Plugin? by TheLinuxJedi · · Score: 2, Insightful

      At the moment you have two options. The first is the Full-Text parser plugins, but this probably won't help you if you are handling INTs and may not quite be flexible enough for what you are looking for. If this is what you are interested in you may want to look at how products like Sphinx search engine does it, as that has a MySQL plugin. The second is indeed the storage engine plugin API. You could theoretically take an existing engine (such as the InnoDB plugin) and modify it in plugin form to work however you choose. Andrew Hutchings (co-author)

  12. Using Existing Plugins by Doc+Ruby · · Score: 1

    How about a plugin that replaces any replaceable default MySQL feature with the execution of a stored procedure that's stored in that MySQL server instance?

    --

    --
    make install -not war

    1. Re:Using Existing Plugins by TheLinuxJedi · · Score: 1

      'Feature' is a very broad definition. But if you are talking about functions you could use Stored Functions and/or UDFs. These would, however, need a different name for the function call. Andrew Hutchings (co-author)

    2. Re:Using Existing Plugins by Doc+Ruby · · Score: 1

      For example, could I write a plugin that intercepts the call to the implementation of "=" (eg. in "SELECT cola FROM tableb WHERE colb = colc"), that executes a Stored Function or UDF returning a boolean?

      --

      --
      make install -not war

    3. Re:Using Existing Plugins by TheLinuxJedi · · Score: 1

      Ah, I see what you mean. Not yet, it would be a real gem if you could alter the lexical parser in a plugin but it is really difficult to do at the moment. The closest alternative would probably be mysql-proxy, in which you could write a LUA script to intercept that and do what you wanted.

    4. Re:Using Existing Plugins by Doc+Ruby · · Score: 1

      So I take it that there's no option to replace the entire lexical parser code, or perhaps some larger functional block that includes the lexical parser, with a "plugin" (install-time selection, not compile-time) that just rewrites the "=" operator, replacing the default code that executes "=" the conventional way.

      --

      --
      make install -not war

    5. Re:Using Existing Plugins by TheLinuxJedi · · Score: 1

      Not yet unfortunately, it would be quicker to edit the parts of the MySQL source than to try this via. a plugin right now. There is a book called Understanding MySQL Internals that could help with this if it something you wanted to look in to. I know that is something they want to eventually do at MySQL (and something we want to eventually do for Drizzle too), but it is much harder to implement than it sounds.

    6. Re:Using Existing Plugins by Doc+Ruby · · Score: 1

      Thanks for this info.

      I've tried a few times to start up a project that inserts a MySQL engine between Linux filesystem calls and the storage subsystem, so the filesystem has a SQL API, but the teams quickly fell apart.

      Maybe with that UMI book and your plugin book I might find a better approach that would produce a result this time.

      --

      --
      make install -not war

    7. Re:Using Existing Plugins by Anonymous Coward · · Score: 1, Interesting

      Postgresql and Oracle let you overload equality on user-defined types in a manner that does precisely what you want. Postgresql's support for this is particularly robust, letting you overload any operator or define new ones, and overloaded equality and comparison operations play nice with indexes, in a way that the portable solution to this problem (selecting from a view) does not.

      Not trying to be a platform weenie here, just pointing out a solution you might be able to use if you're really looking for that sort of thing.

  13. Storing BLOBs as Files? by Doc+Ruby · · Score: 1

    MS SQLServer has a feature which structures a column as a BLOB type, that can be specified to store each record's field in a separate file managed by SQLServer. Those files in that column can have a separate fulltext index supporting matching and proximity operators. Is there a plugin for MySQL that delivers those kinds of features?

    --

    --
    make install -not war

    1. Re:Storing BLOBs as Files? by TheLinuxJedi · · Score: 2, Insightful

      There is a third party plugin called PBMS which I think has what you are looking for http://www.blobstreaming.org/ has more information.

  14. .XLSX Parser Plugin? by Doc+Ruby · · Score: 1

    Is there a MySQL plugin that allows bulk insertion or other simple processing of MS Excel .XLS and .XLSX format files?

    --

    --
    make install -not war

  15. Extend the SQL language? by physburn · · Score: 1
    So does this mean we can custom extend the SQL language in MySQL, personally I like to be more aggregate functions, and ability to program aggregate functions at will.

    ---

    SQL Programming Feed @ Feed Distiller

    1. Re:Extend the SQL language? by TheLinuxJedi · · Score: 1

      As far as aggregate functions go, yes. The UDFs covered in Chapter 2 will do this. But if you want to modify the underlying SQL language (by adding new keywords that aren't functions for example) then no, this cannot yet be done with plugins.

  16. Ported Plugins? by Doc+Ruby · · Score: 1

    It would be useful if there were a way to use all kinds of existing Apache plugins to deliver their functions to MySQL. Auth plugins are an obvious win; other functions not so obvious could still make code reuse deliver real productivity and wider functionality.

    Indeed, an interesting MySQL plugin would be an Apache httpd that could run regular Apache plugins. Or MySQL packaged as an Apache plugin, for inline SQL embedded in Apache server side scripting.

    In a similar vein, JBoss running as a plugin exposing its API to MySQL, or MySQL running managed by JBoss (hard unless MySQL is ported to Java ;).

    More interop between different app plugin APIs would mean more code sharing, and less code rewrite to integrate one app's functions into another's. Plus possible performance and security/management benefits.

    --

    --
    make install -not war