Was pondering why a simple insert statement wasnt working on a table called release. Googled for it and got the following from mysql docs.

Reserved Words in MySQL 5.0

The latest avatar of the next version of the World’s Most Popular Database Mysql , MySQL v6.0 is slated to release.

The following features have been or are expected to be added to MySQL 6.0:

• The new Falcon transactional storage engine

• Optimizer enhancements for faster subqueries and joins, including batched index access of table rows for sequences of disjoint ranges by the MyISAM and InnoDB storage engines.

• RESET SLAVE no longer changes replication connection parameters; previously, it reset them to the values specified on the command line

• The syntax for the LOCK TABLES statement has been extended to support transactional table locks that do not commit transactions automatically. Following LOCK TABLES ... IN SHARE MODE or LOCK TABLES ... IN EXCLUSIVE MODE, you can access tables not mentioned in the LOCK TABLES statement. You can now also issue these extended LOCK TABLES statements many times in succession, adding additional tables to the locked set, and without unlocking any tables that were locked previously. When using LOCK TABLES with IN SHARE MODE or IN EXCLUSIVE MODE, tables are not unlocked until the transaction ends.

  The behavior of LOCK TABLES when not using IN SHARE MODE or IN EXCLUSIVE MODE remains unchanged.

• Enhancements to XML functionality, including a new LOAD XML statement
• Support for extended comments for tables, columns, and indexes.

For a full list of the new features and downloads check here http://dev.mysql.com/doc/refman/6.0/en/mysql-nutshell.html

The mod_ndb 1.0 release candidate is now available from mod-ndb.googlecode.com. mod_ndb is a “web services node” for MySQL Cluster: an NDB API node that runs as an Apache web server module and handles requests over HTTP. It supports MySQL Cluster 5.0 and 5.1, and Apache 1.3, 2.0, and 2.2.

A few months ago, I felt that mod_ndb’s configuration parameters were getting too complex to remember, and realized that a SQL-like configuration language (”N-SQL”) would be more intuitive. It’s not quite as simple as SQL — it does not have an optimizer, so it still requires you to dictate an access plan — but it is more concise and readable than the strict Apache-style configuration that mod_ndb started with.

A lot of other details have fallen into place in the last two months, especially regarding error handling, HTTP response codes, and documentation, so the newest release is the release candidate and will hopefully become mod_ndb 1.0.

Often enough, it seems like you can begin working on software project with the assumption that you won’t need to, say, design a little language and build a parser for it — and yet, before long, that’s exactly what you end up doing.

In the case of mod_ndb, my web services gateway for MySQL Cluster, the little language turns out to involve page-description — it describes how to take a table of results from a database query and present it within an HTTP response. Inside an httpd.conf file, it looks something like this:

<OutputFormat XML> 
   Table  scan = '<NDBScan>\n$row$\n...\n</NDBScan>\n' 
   Row    row  = ' <NDBTuple> $attr$ \n  ...  </NDBTuple>'
   Record attr = '<Attr name=$name/Q$ value=$value/Qx$ />' or '<Attr name=$name/Q$ isNull="1" />'
</OutputFormat> 

<OutputFormat JSON> 
   Table  array  = '[\n $object$,\n ... \n]\n'
   Row    object = ' { $pair$ , ... }' 
   Record pair   = '$name/Q$:$value/qj$' or '$name/Q$:null'
</OutputFormat>

The three data types are a Table, representing a result table, a Row of data, and a Record, which (though it’s probably not a very good term for it) is a column name/value pair.

The record description breaks down to $name$ and $value$, with some flags: $value/Q$ means to put the value in quotes; $value/q$ means to quote it only if it’s character (not numeric); $value/j$ means to encode the value JSON-style with backslash escapes, while $value/x$ means to encode it XML-style using HTML entities (> and so forth).

A record description contains both non-null and null varieties. A row description describes how to loop over the columns in the row, and a table description describes how to loop over the rows of the table. Everything except the $variables$, the elipses, and the \n is plain text.

The format is (I hope) concise and intuitive, and I can parse it with a simple little two-pass compiler. First read each line and build a symbol table; then walk the table and parse each format. It’s not exactly how I had intended to spend the weekend, but it mostly works. Now I’ve got three weeks to test it and get the bugs out before the MySQL Conference.

Last august, I started wanting a new API for MySQL Cluster that could solve a few problems. Frist, if you were to use MySQL Cluster in a LAMP application, you would be turning a two-layer architecture (Apache and MySQL) into three layers (Apache, MySQL, and NDB). Adding that extra tier requires more hardware and means slower response times. Second, the one way around this was to write native NDB API code in C++, but learning the API and developing low-level code can require a big commitment of time. I wanted an easier way to eliminate the extra layer.

My solution is called mod_ndb, and it is an Apache module that runs inside a web server and connects directly to the MySQL Cluster data nodes. It supports a “RESTful” API — applications make GET, POST, and DELETE requests over HTTP, and mod_ndb provides appropriate HTTP responses. Because it is HTTP, responses can be cached by proxies in a well-defined way, and data can be delivered “straight from the database to the browser” in an AJAX application.

The code is now available at http://code.google.com/p/mod-ndb, with documentation hosted on the MySQL Forge Wiki at http://forge.mysql.com/wiki/Mod_ndb.

While I don’t have lots of performance measurements, the benchmarks so far show that if you simply replace a mysqld with an httpd and query it using HTTP instead of SQL, mod_ndb has performance very similar to or slightly better than mysql. (However, you have to use persistent HTTP, so that if you are making five queries you can make them all in one TCP connection, rather than five). And if mod_ndb allows you to improve the overall architecture, a very big performance improvement can be gained — maybe even a factor of ten.

The current release is not quite beta: some HTTP response codes need to be corrected, along with some formatting details of particular data types. Also, for now, mod_ndb can provide output either in JSON or in a simple XML format, but the big improvement I hope to make before the MySQL Conference next month is to support user-defined output formats. I’ll be presenting mod_ndb at the conference on Tuesday, April 24th at 5:30 PM.

Most web applications use something like five languages. One of these is a big programming language, like Java or PHP, running on a server, and the other four are HTML, CSS, Javascript, and SQL.

SQL and HTML are “declarative”: they let you state what you want, and the browser (for HTML) or database (for SQL) has to decide how to cook it up for you. This makes them easy languages for people to grasp; it also ensures that the browser and database server will be exceptionally large and complex pieces of software. (And apparently programmers are never really satisfied with declarative languages, so database servers grew stored procedures and browsers got Javascript.) CSS is a “little language,” a vocabulary for design, more lexicon than grammar. Over the last ten years, a lot of presentation code has moved out of HTML into CSS, while today’s HTML is full of DOM signposts (”id” attributes and “div” tags) that didn’t used to be there.

The full-fledged language at the center of a web app — maybe PHP, Java, or Ruby — is the one language in the bunch that is well-suited for a large software engineering project. Ironically, with AJAX, the user interface on the browser gets better, but the role of this central language in the whole scheme of the application gets smaller and smaller.

Web apps still aren’t as good as desktop apps. They are tooday’s dominant software platform (or at least the one with the most momentum.) People are throwing out perfectly good CRM systems so they can pay per-employee, per-month, for Salesforce.com — which, on it’s own part, sees some big advantages: nobody there has to press CDs, help customers install the software, or worry about people who run old versions and refuse to upgrade. These advantages are all fortunate side effects of the design of the Web, but they are not fundamental to it. The World Wide Web was intended to be a big network of hyperlinked global public information. If it also revolutionized the distribution of data-driven business software, that’s just an afterthought. Out of all those languages, only HTML was in the original plan.

If, today, you really set out to create an internet application framework on top of HTTP, would you build it around a hodgepodge of dissimilar interpreted languages? As web UIs get better, can’t you imagine that the web development stack will get even messier? Netfrastruture — Jim Starkey’s last project, which MySQL AB now owns — was an interesting experiment in simplifying the server side of web apps. Maybe the rest of the stack can be cleaned up some, too.

Next Monday’s MySQL Meetup in San Francisco should be lots of fun. We’ll meet at JasperSoft (303 2nd Street, Suite 450) at 7:00 PM, Mon 11 Sep., and have special guest Teodor Danciu, the creator and architect of Jasper Reports. The guys from JasperSoft will also show off some freely available reports for Bugzilla, and I’m going to give a quick 5-slide presentation on the MySQL Roadmap.

My talk at the MySQL Users Conference is officially called “Advanced User Defined Functions in MySQL 5,” but if it weren’t too late I’d change the name and call it “SQL as a Glue Language.”

Sometimes logic should be as close to the data as possible; this is why triggers exist in SQL. MySQL 5 gives you a full-fledged programming language (SQL/PSM), lots of utility functions, and (this is where the “glue” comes in) the ability (using UDFs) to plug into existing C and C++ libraries with ease.

I hope to see you all there Tuesday at 4:30, and then at Jeremy & Hartmut’s UDF Hackfest Tuesday night.

The Spread Toolkit is one of my favorite Open Source projects. Spread lets you send multicast group messages between applications. It provides reliable, ordered, asynchronous message delivery, and it’s efficient, works on large wide-area networks, and can survive and recover from network failures.

What kind of things could you do if a MySQL server could send and receive messages using Spread? I started a new project, the MySQL Message API, to find out. For a few ideas to get started with: if you want to notify a bunch of application servers whenever a particular table changes, you can write a simple trigger to send them all a message. Or if you have lots of different specialized database servers powering your 30-million-user social networking site, you might want to send messages from one server process them (with stored procedure code) on another.

The Message API is a set of MySQL UDFs that enable you to join message groups and to send and receive messages. The first beta release is available now on MySQL Forge. I have been working with the code and testing it pretty extensively for the last three months — but only on Mac OS X and FreeBSD. I wouldn’t be surprised to hear about problems compiling and linking the code on Linux, Solaris, and other platforms, so I’m especially hoping to get some feedback about those.

I mentioned a few weeks ago that Jan Kneschke has posted some MySQL user-defined functions — available here — for communicating with memcache. I want to outline an example of how to automate cache maintenance by calling these functions from triggers. In a big web application, you have critical high-performance code (which, if you are running memcache, is no doubt already cache-aware), but you probably also have a bunch of little scripts for performing sundry administrative tasks. With triggers in the database, you can keep these scripts the way they are, and even run ad hoc SQL statements, without worrying about leaving inconsistent records in memcache.

This all requires running MySQL 5 with dynamically loaded UDF code — but of course you don’t have to upgrade the whole database farm to MySQL 5, just a single replication slave to run the triggers. On this one slave, create AFTER DELETE and AFTER UPDATE triggers that will delete a record from memcache whenever it gets changed or deleted in the database, like this:

CREATE trigger cache_expire_delete AFTER DELETE on table1 FOR EACH ROW DO memcache_delete(”memcache_host:11211″,OLD.pk) ;

Here are the details.
Continue reading “MySQL triggers and memcache”