The 98th edition of Log Buffer, the weekly review of database blogs, has been published on Jeff’s SQL Server Blog.

Log Buffer is a shared project of the DBA blogging community, so you’re welcome to edit and publish an edition yourself. LB’s 100th anniversary edition is still up-for-grabs (and there’s plenty of room besides that), so read the Log Buffer homepage and the guidelines, and then email me.

Here’s Jeff Smith’s Log Buffer #98.

I have written a Configuration Tool for MySQL Cluster.
It is located on www.severalnines.com/config

The configuration tool lets you:

• Configure a Cluster based on MySQL Cluster 6.2 or MySQL Cluster 6.3
• The number of data nodes, mysqlds, and management servers
  
• Chose how much DataMemory to allocate for the data nodes
• Specify on which hosts to install the software on etc etc

Finally, configuration files (config.ini and my.cnf) and a set of scripts are generated based on your input and emailed to you.

With those scripts you can then:

• Download and compile 6.3
• Distribute the Cluster binaries onto the hosts you have specified)
  
• Bootstrap the system (create directories, mysql user account, install mysql database, configuration files etc)
• Start the cluster
• Stop the cluster
• Rolling restart
• Backups
• Stop/start nodes

All of this from a single location! It requires that you setup shared ssh keys on your root account or you will have to enter the password a lot of times..

Moreover, these scripts and the config files are not in any way related to MySQL or Sun Microsystem meaning that MySQL Support will not be able to support this. However, the config.ini file that is generated is Best Practice.

Good luck and let me know if there are any issues (the Configuration Tool is still quite Beta - if you make a mistake then you have to start from scratch again..).

To some, Google has long been a champion of open source, hiring top open-source developers and contributing to a range of open-source projects, in addition to its Summer of Code. To others, Google has been the worst enemy of open source, bumping AGPL-based code of its Code.Google.com and only selectively contributing back to the projects like Linux and MySQL from which it derives benefit.

I've been in both camps. One thing is increasingly clear to me, however: Google is opening up to open source.

Earlier this week, I noted its Google I/O Conference, which will serve open source's most important constituency: developers. CNET News.com reporter Steve Shankland writes of Google's Android as "Google's highest-profile attempt so far to use the collaborative programming method to change how computing is done outside the company's walls."

All good. But it's actually Google's promised transparency about its crown jewels--its search algorithms--that makes me think Google is finally ready to truly open up. Perhaps this newfound transparency derives from its 61 percent search market share, but the shift is welcome, if still hesitant:

...

I have finally publicly released Federated ODBC Storage Engine, something I did most of the development for last year, then became extremely busy with work and other projects. I have heard of interest for this engine from several people, and I really enjoyed working on this project as it enables having a storage engine that can actually connect to a variety of data sources.

Last year I was able to connect it to a PostgreSQL table, but ran into a lot of headaches trying to get other RDBMS ODBC drivers working with it. A lot of the problem is that I need to make the SQL statements the storage engine builds very standard. I had to revert the code a bit to get it to work with the latest MySQL 5.1.21, so I lost some of that work to make the SQL generic. The other issue is that to test with other databases, you need to be somewhat fluent in setting up each one and getting it's ODBC driver working correctly, which takes time away from development.

Some of the other issues in developing this are MySQL server changes from 5.1.18 to 5.1.21.

* every instance in the MySQL server of "byte" was changed to "uchar" (!)
* thd_get_ha_data vs. (ha_federated_odbc *)thd->ha_data[ht->slot];

I'm releasing this because I want to get the code out there so people can have the option of looking at it and contributing if they want to.

My goal is to move this project forward and eventually work it into FederatedX which now has the connection layer separated from the handler code. I would like to see this storage engine talk to PostgreSQL, Oracle, SQL Server, etc. There are a lot of great applications this engine could be used for in theory such as tying various data sources together from within MySQL, as well as a migration tool!

Please be aware that this is an ALPHA release.

The source code can be found at:

http://www.patg.net/downloads/federated_odbc_engine-0.1.tar.gz

Mercurial repository at:

http://hg.patg.net/federated-odbc-engine/

Enjoy!

A few years ago a backdoor was found in Firebird, the open source fork of Interbase, that already existed in the original Interbase product and was still in the version of Interbase that was sold at the time. Nowadays this is fixed, but it was kind of scary that a company would add a backdoor and then totally forget about it, why else would they not have removed it before open sourcing (after all a backdoor only works through obscurity)? Anyways the other day another security issue (this sort of thing happens to the best of them) in Interbase that was fixed in January in Firebird already.

The security issue has been long disclosed. Now why on earth would the Interbase developers not follow security alerts on Firebird. It seems there is a very high probability that any Firebird issue will also be present in Interbase. I guess Interbase developers just sit in their isolated ivory tower not bothering with what is going on in the rest of the world.

The sum of the matter is that the open source guys had a fix out that was mentioned in the report, while Interbase does not yet have a fix out. I do not know the details of the situation and how much forward warning each project had, but it does seem to validate a common perception, that OSS guys are quicker with getting fixes out the door.

BTW: I have never used Firebird or Interbase. I only had Firebird installed a while back to test the PEAR::MDB2 driver. But it does seem like a much more full featured RDBMS compared to MySQL, that shines as a very good embedded SQL database.

Performance improvement:

* Earlier MONyog was semaphore intensive. That could result in reaching the ‘limit of semaphores that can be created by the kernel’ in some systems. MONyog 2.04 will use less semaphores. Another symptom was that a large number of small and zero-size files could be left behind (in system TEMP folder on Windows and in MONyog/bin folder on Linux). This is also solved with this.

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

Bug fixes:

* Structure Sync could miss the concluding quote around column comments. This bug was introduced in 6.5.
* Migration from SQL Server could fail with empty tables.
* Migration (UPDATE and DELETE) triggers could fail with specific schemas on source (afffected were small tables with short column types only - like integers and very short string types). This bug was introduced early in 6.x with the full Unicode support added here.
* Migration could generate non-matching columns-count with specific schemas. This was also introduced early in 6.x
* Message about ?Successful rows? could display twice in Migration (sja.log). Note: This was a cosmetical issue with the message only. Rows were only migrated once!

Downloads: http://webyog.com/en/downloads.php
Purchase: http://webyog.com/en/buy.php

The Certified MySQL Associate Exam is a general overview of the MySQL database. It is designed for those new to databases and/or MySQL.

The subject of transactions has caused a few folks to stumble on their exams. For someone with a background in programming where you change data and it stays that way, transactions take a little effort to understand.

The usual example is a payment at a bank. A pays B 100 units. If something happens between the time the system takes the 100 out of A's account and puts it into B's account, there is a large chance that both A and B will not be happy. So the subtract 100 and add 100 are treated as one unit and either both have to be completed as a unit or not at all.


So all or nothing.

You type START TRANSACTION, input the needed changes, and COMMIT. And your changes are done as one to your database. Or half way through you realize that something is wrong and use ROLLBACK to get back to where you were when you typed START TRANSACTION.

Almost.

First, you have to use a storage engine that supports transactions. Try using START TRANSACTION and COMMIT with MyISAM and see how it reacts.

Second, some commands like CREATE TABLE, DROP TABLE, or UNLOCK TABLES (see MySQL Reference Manual section 12.4.3) can cause MySQL to implicitly issue a COMMIT on your behalf. You have better know these to help you pass the exam and to keep your sanity on the job.

During 2008 we are planning to run a series of interviews with interesting persons somehow related to the telecom field. In this first installment, we will have a chat with Juha Heinänen from Finland.

MySQL: Juha, you are a former professor of Computer Science and Communication technology, CTO (or similar positions) in at least Sonera, Telia and Song, former ATM specialist, responsible for bringing Internet to Finland and registering the .fi top-level domain, a consultant for many early network equipment startups some of which succeeded to be still with us today, and author of several RFC's. Nowadays you are a core contributor to the OpenSER SIP proxy, and you sell a VoIP platform called OpenSIPg based on that to Nordic operators. In addition you seem to live a life that would likely be a dream of many hackers, spending time in different parts of the world hacking on your favorite Open Source project. Even to this date, I don't think we've ever met in person.

MySQL: For many years now you have been working with the SIP protocol and OpenSER SIP Proxy. When did you first turn your eye to SIP and why did you become
interested in it?

JH: When working for Song Networks (now TDC) in late 1990 and early 2000, we saw a need for a hosted VoIP service for small businesses. Due to my IETF background, a natural protocol choice for me was SIP instead of the then dominant H.323. At first, we trialled a Swedish commercial SIP proxy called Hotsip, but didn't find it flexible enough for our purposes. I then heard about an Open Source SIP proxy project called SER, saw its great potential, and soon became a SER developer although I had not written a single line of code during the past 10 years.

MySQL: Knowing that you enjoy coding, it must have been great to return to it! By the way, what is your short, 2-3 sentence introduction to OpenSER?

JH: The OpenSER project is a spin off of the SER project. Our aim in OpenSER is to bring to the market a well tested new release of OpenSER SIP proxy every 10 months or so. Today OpenSER is a very successful project with many of high quality developers and a wide user community.

MySQL: How do you see the Internet vs the traditional telephone network? Will SIP (or some other Internet based protocol) eventually completely replace the Plain Old Telephone System?

JH: This is hard to answer, because there always exists the "dark side" that wants to retain the old walled garden style POTS service no matter what equipment or protocols they internally use. These people see telephony as something special, not just yet another Internet application.

MySQL: I remember once talking to you, that you were furious about an operator who insisted on you to implement minute based billing for OpenSER :-) I guess you never did that for them?

(Note to readers: Not that you would consider this for any other Internet protocols either, but this kind of requirement is especially ridiciluous for a peer-to-peer protocol like SIP, since most of the data in a VoIP call may not route through the operator network at all, so it would be hard to justify the operator charging for traffic that is actually happening in some other operators network!)

JH: I don't recall this, but time based billing of SIP calls would be very difficult to implement without also getting involved with routing of media. That, in turn, would mean that most of the advantages that SIP based telephony has over POTS would be lost.

MySQL: What do you think about the IP Multimedia Subsystem?

JH: IMS is a next generation implementation of walled garden telecommunication services. I let it live its own life. I don't care if some users are too rich or lazy and choose IMS instead of open Internet based services as long as I'm not forced to do so.

MySQL: What will happen to service providers (mobile and fixed)? Especially as VoIP companies provide much cheaper calls. And web companies like Google are
offering services. Will the carriers be reduced to bitpipes?

JH: Mobile or fixed Internet access is always worth the money and I gladly pay for it. What I don't like is when operators start to milk their cows without providing any real added value, e.g., by charging huge roaming fees for mobile Internet access. It is operators' own choice if they let companies like Skype and Google take away their customers by not providing their own Internet based telecommunication services.

MySQL: Or asking the same question differently, who will eventually be our service provider for voice calls? Google, Nokia, my current telecom operator, or the current VoIP service providers or maybe some decentralised non-commercial and free peer-to-peer VoIP network?

JH: To me telephony is just yet another Internet application. The same parties will be providing it in the future that today are providing email, web, etc. services. In case of my own company, TutPro Inc., it is TutPro Inc. itself, because I don't like the idea that someone else (perhaps with ties to government spy agencies) is storing my emails or routing my VoIP calls.

MySQL: What is your view on convergence? Or even simpler, what is convergence?

JH: Convergence is a term that I don't fully understand. My goal is to be able to use Internet for all my communication needs. What prevents it from happening today is too slow and (sometimes) too expensive mobile Internet access that is unsuitable for real-time communications.

MySQL: So, tell us more about your current projects. What are you working on now?

JH: I have OpenSER and SEMS based SIP platform called OpenSIPg that a few operators and organizations in Finland and Sweden use to offer their VoIP and presence services. Developing OpenSIPg keeps me busy, but thanks to mobile Internet access, does not tie me physically to some particular place or country.

One new thing that I have been working on is a simple, certificate free mechanism for reliable verification of trusted peers. It is based on Radius protocol and a broker model similar to what was used already long time ago for dial-up access.

MySQL: I know you recommend MySQL Cluster to your customers as the database to go with OpenSIPg. What is the database mainly storing, and what features make MySQL Cluster the best fit?

JH: Well, firstly OpenSER SIP proxy keeps all location and presence data in MySQL database tables. My own principle in developing OpenSIPg has been that my customers should not need to edit any text files when they provision users or the VoIP infrastructure itself. So all OpenSIPg information is kept in MySQL databases, where it can be accessed and manipulated via web based GUIs.

The databases should naturally be resilient and therefore a clustered implementation is the best fit.

MySQL: By the way, for the more technical readers, do you have any kind of numbers about the loads OpenSER and the database behind it must support? Like calls per second or SQL transactions per second? (I know the Finnish operators are not the biggest in the world, but still.)

JH: None of my customers have hit or even been close to any performance limits yet. Nevertheless, a good SIP proxy design tries to minimize the number database operations that need to be performed per request. We thus recommend MySQL cluster more for high availability rather than performance reasons.

MySQL: If you had 3 wishes - but restricted to MySQL Cluster - what would you wish for?

JH: I would wish that MySQL 5.1 would become available also as Debian/Ubuntu packages, because cluster capabilities in 5.1 are more developed than those in 5.0. From maintenance point of view it is not a good idea to install any software to servers from tar files. My other wishes are related to ease of use. Setting up and running MySQL cluster should not require a high degree in database administration.

MySQL: So let's see, your product is based on Linux, OpenSER, PHP, FreeRADIUS and MySQL. What is the importance of Open Source in Telecom? What can Open Source do for Telecom?

JH: Open Source is important for everyone. Large developer and user communities of Open Source software can produce rapidly higher quality software than even the biggest companies can do on their own.

MySQL: Years ago, we had an email chat about a mobile application that was using SMS messages to communicate with a server. Your quick comment was: "Nice, if you want to use such legacy technology." As the pioneer spirit you are, where do you see the border between "legacy" and "modern" in 2008?

JH: I think I was referring to SMS as "legacy" technology because SMS was not terminal and underlying network independent Internet application. That is still true today and for some strange reason even Nokia has not yet made SIP based messaging available in its phones.

MySQL: And what will be legacy in 2011?

JH: I'm afraid that in 2011 there still exist mobile network specific services that do not work end-to-end unless each mobile operator has made a bilateral agreement with each other mobile operator. Such a service model simply does not scale nor lead to rapid development of innovative services.

MySQL: Thanks Juha for taking the time to talk to us, it has been a pleasure. And all the best to your future projects.

I guess I am bit late with these news... but.. IT IS GREAT NEWS!

Short summary (read more on Ramon's blog)

• Cluster was pulled out of 5.1.24


• Don't use 5.0 any longer


• MySQL Cluster 6.2 is what everyone should use (unless CGE 6.3 features are needed). It is a great piece of software, thoroughly tested by telcos and put in production around the world!
  

Why pull Cluster out of 5.1.24? As everyone knows 5.1.24 is delayed and at the same time CGE 6.2 has lived its own life and matured in production and in test labs.

Although the mysql server parts are based on 5.1.23 the Cluster parts of 6.2 is production quality (like e.g geo-redundancy), but the non-cluster parts such as innodb and myisam etc are still RC quality.

By releasing it independently it can be maintained and released more often instead of being stuck to the official MySQL release schedule.

What has been referred to as CGE 6.2 in my previous blog posts are applicable to MySQL Cluster 6.2, it is the same version, but now you can get binary builds of it.

MySQL Cluster 6.3 (previously known, and still known, as CGE 6.3) has not got any binary builds yet, and is still currently only delivered in source.

Look here for feature differences between MySQL Cluster 6.2 and MySQL Cluster 6.3!

Good luck with your clustering!

What a long name eh... the link is http://au.sun.com/sunnews/events/2008/horizons/

Jonathon and I will be there, and I am speaking in the business track on Tuesday. It won't be about training or the other stuff that Open Query is doing, but instead provide a high-level (business) overview of MySQL's unique architecture. As we know, MySQL is particularly capable at doing a lot of things, but for optimal results the underlying architecture needs to be right, too.

Of course it is an excellent opportunity for us to meet some more potential customers too, as we now have a lot more training modules for developers, DBAs and High Availability. Our public course schedule for the coming months covers Brisbane, Sydney, Canberra, Melbourne, Adelaide and Auckland, and meanwhile interest for in-house (custom) training courses is high also.

To summarise the current state of the business... one has to start slow, but no sense continuing in first gear when on the highway ;-)

Bruce Momjian says

…the volume of patches [to PostgreSQL] has risen dramatically during the past few years.

This is total hearsay — I don’t have hard numbers, haven’t verified it myself, etc etc. But consider the source!

What can be responsible for this increase in patches to PostgreSQL?

Bruce Momjian, Open Source development, patches, PostgreSQL

There’s a lot of debate lately about cell phones in the air. Do we want to be connected and productive with carryons securely stashed in overhead bins? Or do we like the excuse of being fully disconnected at 30,000 feet? Would we welcome the ability to make calls on our mobiles, or would we rather send a lynch mob to seat 19D?

Turns out 74% of consumers don’t want to hear you yacking with your office cohorts, but they wouldn’t mind a bit if you sent them a text message. A new survey commissioned by Yahoo! and conducted by Harris Interactive revealed that nearly three out of four consumers agree that mobile phone usage on planes should be restricted to silent features. The majority of us want to stay connected to our life on the ground. Email, text, IM, games — all good. But if we have to sit next to you making goo-goo with your girlfriend in Gilroy, game over.

What’s more, more than two-thirds think there should be a designated talking section. Hey, it worked so well with smoking. Why not?

So how about you? Want to participate in our entirely statistically-invalid random sampling?

If I could use my mobile phone on a plane, I’d most want to:

• Text message
• Play (silent) games
• Do email
• Make phone calls
• I don’t want anyone using a mobile phone

View Results

Will Yodel Anecdotal readers agree that silence is golden?

Nicki Dugan
Blog Editor

A couple of months ago, I wrote about the hierarchy of enterprise software needs, and today I would like to share with you how I think those needs have changed.

A long, long time (say ten years) ago, a company might decide that it needs to manage its sales effort more effectively. It might decide that a CRM system is what it needed, then buy servers, license a package, and then train, or at least try to train, its salespeople to use the system. They are told that they need to enter all their account and contact information into the system, log all their calls and meetings with clients, and fill out forms to record the sales opportunities. They will also have to make periodic forecasts with the system.

Needless to say, some sales people might resist, but it's up to management to make sure that they use the system. Some of these measures might be draconian -- I once read about a company who would not pay sales commissions on orders which were not first created as opportunities in its CRM system! If the salespeople ultimately use the system, however, it could be very helpful, because with it management could see what's going on -- how many leads there are in the pipeline, what stage all the sales opportunities are, which customers have gone dormant, etc.

Today, we're in a different world. Thanks to the digital explosion of the past decade, important data such as accounts and contacts, not to mention e-mails and appointments, could be spread out over Blackberries, cellular phones, Facebook, LinkedIn, Google calendars, Gmail, Hotmail, Yahoo Mail, and a thousand other places. While there's still a core organization whose data is maintained in accounting, order entry, and warehouse management systems, data vital to the success of the organization is also increasingly spreading out like a web into other systems and devices.

This means that the enterprise software of the future could no longer meet an organization's needs by solely looking inward to the data that's available inside of the organization. Rather, it will need to fan out into this web of critical data, much like a search engine spider would, and connect with all the important online services and devices used by key employees. It must be able to bring all that information together with data from core systems like accounting, order entry, and warehouse management, to coordinate activities and provide analysis.

This is the next evolution of enterprise software, and it's where we will be taking opentaps Open Source ERP + CRM. We've got a good start already:

• We can sync data with mobile devices and online service providers
• We have a very robust, normalized, operational data model that could captured the true richness of business relationships and transactions.
• Finally, we have some very strong open-source business intelligence tools bundled with opentaps that can make sense of it all.

Our next step is to tie all those pieces together, so that our users could see not only what is tracked by traditional business systems but what is happening in the entire web around their organization, perform detailed analysis, and take actions based on that information.

Just a day at the office, at MySQL Sun
Originally uploaded by FallenPegasusOne of the perqs of working for MySQL has always been working from home, or wherever. When the weather is really nice, the wherever can be the porch in the back yard.

Recycling old MySQL business cards
Originally uploaded by Geert JM VanderkelenWell, we don't have a policy on what to do with them, so I went creative. Not as creative as Harrison did (where is that picture???), but I like it. It's hanging next to my MySQL certificates. Yes, I'm proud on these things, maybe I'm a bit vain.. But it does look good on the wall of my storage room.

(btw, this flickr to Google blogging is great!)

Just a quick note to let you know that you can download and test the new MyISAM online backup that is part of the overall new backup utility that is shipping with MySQL 6.0.  The backup is native (not logical like a mysqldump backup) and online so you should get no interruptions in activity while the backup is running and it should be faster than logical-styled backups.  Download the preview and let the backup team know what you think.  Do keep in mind this is a preview release so you may/will find bugs.  Finally, kudos to the backup team for getting this out far ahead of the original  schedule.

The petabyte is the new petaflop. Take Yahoo's specially built data warehouse, which it uses to analyze the behavior of its half a billion Web visitors a month.

There is one very easy way to find database queries that might be performance bottlenecks on properly running servers, without using any sophisticated tools or using precision slowlogs,

Just overload your server, and spam ‘SHOW FULL PROCESSLIST’. :)

Sun announced two weeks ago, during JavaOne, and, since then, the team has written a number of Posts@BSC; I'll try to start catching up.

A recent post explains how to configure WebSynergy to use MySQL; the detailed instructions are at here, and there is also an associated . Thanks to Gopal for the tip