Who wants "in-memory" mode?

If you sign up for the Panorama beta, you can find the documentation for the new "in-memory mode" feature in the Help:

   SQL Anywhere Server
     Database Administration
       Starting and Connecting to Your Database
         The database server
           Database server options
             -im server option

What's the point of in-memory mode? Speed, at the cost of durability.

This isn't a "database file mode", it's a "server operation mode" which means you create and load a database file in "SQL Anywhere Classic" mode and then run it in one of the two in-memory modes: Never Write or Checkpoint Only.

And before anyone gets their knickers in a knot, Panorama isn't introducing any kind of cheesy non-transactional data store like MySQL.

SQL Anywhere is still a fully-relational database management system, fully transactional, almost fully ACId even when running in-memory mode.

ACId means Atomic, Consistent, Isolated and durable, the Four Horsemen Of The Transaction. Durability means a COMMIT is permanent. A little "d" is used here because the Checkpoint Only flavor of in-memory mode preserves all changes up to the last checkpoint, but doesn't allow forward recovery beyond that point. And no recovery at all for the Never Write flavor... the name kinda gives that away. More on this later...

Oh, yeah, it's the same engine, not some bogus Lite version with all sorts of syntactical and semantic differences. It is, however, a "separately licensed component" like high availability.

Who wants it?

What I'd like to know is, who wants "in-memory" mode? I think I do, for some things. Maybe for Foxhound which is a classic "high throughput" application doing huge numbers of inserts and deletes.

The Panorama Help talks about some of the reasons you might want to use in-memory mode, I won't repeat them here. What I will say is this, "Never Write" doesn't mean "Read Only", it just means if you want to save your changes to the disk you're gonna have to find another way; UNLOAD TABLE is one suggestion.

Here's how I see the in-memory modes stacking up against one another, and against Classic mode:

                   -im nw  -im c
                   Never   Checkpoint
                   Write   Only        Classic
                   ======  ==========  =======
Transactional       yes     yes         yes
.db file updated    no      yes         yes
Transaction log     no      no          yes
Temporary file      no      no          yes
Checkpoint log      no      yes         yes
Checkpoints         no      yes         yes
Dirty page flushes  no      yes(1)      yes
Unlimited growth    no      yes(2)      yes
Recoverable         no      yes(3)      yes

(1) Dirty pages are flushed only on checkpoint.

(2) Without a temporary file, checkpoints are the only way to keep the cache from being exhausted.

(3) After a crash, a restart will perform the usual automatic recovery to the last good checkpoint, but without a transaction log, it is not possible to run a forward recovery from the last good checkpoint to the last committed transaction.

What's missing from the table above is a column for "UltraLite"... will in-memory mode give UltraLite some competition?

Pax Firefox?

I have one client who still uses Netscape 4-point-something. I guess they haven't visited this blog recently 'cause they don't show up in StatCounter.com's "browser wars" listing":

57.43%  Firefox 2.0.0
22.57%  MSIE 6.0
13.66%  MSIE 7.0
 2.38%  Firefox 3.0b4
 1.19%  Opera 9.26
 0.79%  Opera 9.21
 0.79%  Safari 1.2
 0.59%  Netscape 7.2
 0.40%  Konqueror 3.5
 0.20%  Opera 9.25

At first glance, the Firefox numbers surprised me... more of you are using a Firefox beta than the most popular version of Opera.

At second glance, the fact that 22.57% of you are still using browser-without-tabs is a real shock... that's like TV-without-Tivo, how can anyone live like that?

On the other hand, it might also mean that 22.57% of you are working in shops where IT won't let you use IE7 [rude rhetorical question removed, asking "Do their knuckles hurt dragging on the floor like that?":].

Pax Google!

In the last 5 days, 98.57% of the hits on this blog that came from search engines, came from something called "Google".

All the rest came from "Google Blog Search".

Hmmmm, is there a trend here?

Lax Email Administrator

My company's email administrator is completely incompetent, he should be fired!

Here's the story: I was working at a client site and I went to check my old-fashioned POP3 email accounts, all of which get routed to one mailbox on my company's email server. One of these email addresses is very old, dating back to 1996, and it gets massive amounts of spam: bcarter@bcarter.com.

Well, they all get spam, and I have turned off all the spam filters because I just can't bear false positives: I don't ever want to miss anything real. I use MailWasher to browse the mailbox on the server side, a few minutes a day checking "Delete" is a cost of doing business.

Anyway, I hadn't checked that mailbox for a day, but... there were only FIVE MESSAGES WAITING!

That's impossible! I get that many 419 fraud letters in one day, never mind the endless promises that "She’ll never laugh at you anymore!"

To make a long story short, I used Google Mail to send a test email to the POP3 account, and it got bounced:

521 Mail rejected - you are listed in Open Relay Database (Open Relays) [FREE] - http://www.ordb.org/

Arrrgh! Was the company email server blacklisted by ORDB? Those stinking b@&^@#d$!!!

But wait a minute, www.ordb.org got "404 Not Found"... and a Google Search told me that the ORDB anti-spam service was shut down in 2006.

The truth emerges: Spam blocker rises from the dead to bite lax email administrators.

Yes, my company's email server was still configured to check ORDB a year after that service shut down:

So when ORDB rose from the dead, and started replying "bad" instead of "good" every time our email server checked an incoming email message, all the spam got blocked... along with everything else.

Yes, my company has one of those "lax email administrators"... I should be fired!

Follow Those Links

Here's the deal: I promise to try harder when picking links to put in these blog entries.

That means no more links to Wikipedia, at least not just a link for the sake of including a link. If you want to look up the definition of something, you can use en.wikipedia.org yourself, or dictionary.com, or thesaurus.com (I love that last one!)

In return, I want you to check out the links I do put in. I promise to try to make them highly relevant, and/or highly interesting, and/or just highly hilarious.

Yes, all of that: I promise to try.

Oh, and one more thing... Yes, all the links do open up the new page in a new window. If you're not using Firefox or IE7, and you don't have them set up to "open new pages in a new tab" instead of a whole new window, you might not be happy.

No apologies for that one. It's the motto of customer service professionals everywhere: "We're not happy until you're not happy!" <g>

This is SO cool!

I'm like a kid with a new toy, three days ago I plopped StatCounter.com's JavaScript thingie on this blog, to count visits, and it looks like "We Are Not Alone!"



Here's what I'm thinking: Folks in China must all be getting ready for the Olympics, because 6,000 copies of my book sold in China and nobody there has visited here in the past three days (my apologies to the one visitor from Taipei, you're not nobody... hey, you beat them all! :)

Of course, it might also have something to do with the fact this blog's in English and the books sold by dearbook.com.cn aren't.

Seriously, if you think I'm bragging about book sales, don't... I have no idea what's going on in the PRC 'cause "6,000" is a lot bigger than all the sales in the rest of the world, combined, total, in English and Japanese. If you're thinking "Breck must be getting rich!", stop that too: I still owe the U.S. publisher on the advance money. And no, the advance money wasn't anything to write home about; I tell people two different stories: "It paid for my long-distance phone calls to Glenn Paulley to talk about features" and "I think I earned a dollar-fifty an hour writing the book." Hey, that's big money in some parts of the world, I ain't complaining! :)

Anyway, if you're the one person in Johannesburg, or the one person in Bangalore, or Fairbanks, or ... You rock!

I have a feeling you won't be alone much longer, not with the way things are going...

Unpublished MySQL FAQ (2)

(continued from Unpublished MySQL FAQ)

Q: What motivates the people who develop MySQL?
A: MySQL is free!

Q: Who is responsible for the architecture of MySQL?
A: MySQL is free!

Q: Who decides what behavior changes are made in each new release?
A: MySQL is free!

Q: What does a "zero value date" mean?
A: MySQL is free!

Q: What happens when I add 1 to a date? e.g., SELECT CURTIME()+1?
A: MySQL is free!

Q: What is the range of the TIMESTAMP data type?
A: MySQL is free!

Q: Do the properties of the TIMESTAMP data type depend on the storage engine?
A: MySQL is free!

Q: What are "silent column specification changes"
A: MySQL is free!

Q: What is the difference between "strict SQL mode" enabled and disabled?
A: MySQL is free!

Q: Can I use DEFAULT CURRENT_DATE for a date column?
A: MySQL is free!

Q: Can I specify a DEFAULT value for BLOB and TEXT columns?
A: MySQL is free!

Q: What are "implicit default values"?
A: MySQL is free!

Q: Who do I talk to about getting an enhancement made?
A: MySQL is free!

Q: Do I need to read and understand the source code for MySQL itself?
A: MySQL is free!

Q: Do I need to modify and compile the source code for MySQL itself?
A: MySQL is free!

Q: What is the importance of MySQL being open source?
A: MySQL is free!

Q: How do I embed a MySQL database in my commercial software and/or hardware product?
A: MySQL is free!

Q: Can I hide the fact that my commercial product contains a MySQL database?
A: MySQL is free!

Q: How do I protect my intellectual property when I distribute a MySQL database to customers?
A: MySQL is free!

Capacity Planning

Ivan(Anywhere) isn't just a pretty face, he's on the team that wrote the much-anticipated white paper on capacity planning...



...and you thought you didn't have anything to read this weekend!

The IvanAnywhere Odyssey

"Episode 1 - 2008: A Sybase Odyssey" is now playing on YouTube:



For more about IvanAnywhere, go here.

Please Print The Data

"Please print the data for me, in columns, so I can look at it."

That's a real request, from a real end user... and in a lot of shops that's what passes for specifications.

And there is nothing wrong with that... in this era of high efficiency and low overhead there's often no need to study every user request and examine its entrails for deep significance, before giving the user something to look at.

A quick question deserves a quick answer, and here's one from SQL Anywhere... a real answer, at a real client site just yesterday:

Step 1: Add the "-xs http(port=54321)" parameter the SQL Anywhere server command line to enable the built-in HTTP server, if it isn't already enabled:

"%ASANY9%\win32\dbsrv9.exe" -c 500M -o dbsrv9_log_ddd.txt -os 10M -x tcpip -xs http(port=54321) ddd.db

...watch out for wrapping, that's all on one line.

The default HTTP port is 80 and if that's what you want just code "-xs http". In this example 54321 is used to avoid collisions with other HTTP servers already running on the same machine.

Step 2: Restart the server, and run dbisql to create one web service for each columnar printout you want to produce:

CREATE SERVICE Indexes TYPE 'HTML'
   AUTHORIZATION OFF USER DBA
   AS SELECT tname AS table_name,
             iname AS index_name,
             indextype,
             colnames
        FROM SYS.SYSINDEXES
       ORDER BY table_name,
             index_name;

Step 3: Show the end user how to display and print the result in a browser:

http://pavilion2:54321/Indexes

where "pavilion2" is the computer running the SQL Anywhere network server (dbsrv9), "54321" is the HTTP port we chose, and "Indexes" is the name we gave the web service.

Bingo! Here's what the output looks like, in FireFox...

High Availability and MobiLink

Here's a recent article about using MobiLink with Hot Failover (er, Database Mirroring, um, High Availability):

A High Availability Data Synchronization Architecture, Joshua Savill, March 4, 2008

This is an overview article, with some code snippets and lots of extra study required if you're going to put it into practice: Database synchronization is complex no matter what software you use, and while it's a wonderful thing to add High Availability at the central and/or remote sites, the complexity just got more so.

As an overview, the article's excellent. So it's not really fair for me to rant about one single statement:

"A database mirroring system is a configuration of either two or three database servers, each running on separate computers, which cooperate to maintain copies of a database."

That statement is lifted from the Help (not the whole article, just that sentence), and it is actually correct as written, sort of... if you're a lawyer and you're reading it out of context.

First of all, you have to "configure" or set up three software servers, not two: the arbiter, the primary database, and the secondary database.

Second, it is possible to put those three software servers on one, two or three separate hardware computers (more on the "correct" number later).

Third, when the database mirroring system is up and running, it is possible for it to keep running when one of the three software servers goes missing. That may be where the "either two or three" is coming from, but it's an important distinction: Mirroring needs three software servers to start running, and it can keep going on two.

I propose this edit:

"A database mirroring system is a configuration of three software servers which cooperate to maintain two copies of a database and provide continuous availability to one copy if one of the three software servers becomes unavailable."

Can you tell I spent two weeks in a Texas courtroom as "expert witness" in a civil case involving database software? Can you say "Denny Crane"?

Now, the revised statement might confuse the reader a bit (three servers? two copies? which is it, two or three?) but that's OK if the next few statements clarify other points:

• How many physical hardware computers can, and should, be used (hint: the correct answer is "three"),

• What roles are played by the arbiter, primary and secondary software servers.

It's not the role of Joshua's article to go into that detail, and the SQL Anywhere Help does an excellent job once you plow into it.

My only quibble with the Help is the High Availability tutorial uses a single computer instead of three, as if there is some kind of shortage of computers these days...

But enough ranting! Go read Joshua's article if want to get started removing those single-points-of-failure in a MobiLink environment.

And for what it's worth, here's a somewhat older article that includes a three-computer demonstration of High Availability (not Mobilink, just the database mirroring part):

Top 10 Cool New Features In SQL Anywhere 10

If it comes up all blue on your screen, scroll down or shrink the display to see more of the page. Then scroll down some more, to point 10 on page 10, "Hot Failover (Database Mirroring)".

Release-Defining Features

Someone asked me, "Why haven't you talked about the really big features in the Panorama beta?"

Well, I don't think CREATE EVENT ... TYPE DEADLOCK is any small thing, but you're right, I have not talked about Panorama's important release-defining features, features like [deleted], [erased] and [redacted] to name just three.

And therein lies my problem: I've agreed to the Panorama EULA which includes this clause:

5. CONFIDENTIALITY.
"Confidential Information" shall include the Program (including methods or concepts utilized therein) ... yadda, yadda, yadda.

Plus, I've signed more than one NDA, one that even includes a "morals clause", so I really can't talk about secret stuff unless I get written permission.

Don't get me wrong, I do have permission to write about some of the features, just not the big ones, like the three I mentioned above, and others like "In-Memory Mode" server operation which will compete for speed with MySQL's cheesy non-transactional data store but still be transactional.

In fact, there are two kinds of In-Memory Mode: Really Fast, and Faster Still, which are really great for...

"Mr. Carter, you'll have to come with us now. No, don't take anything with you. Now, put your hands behind your back, you have the right to remain silent..."

[cue the Doink Doink sound]

Calling Other People's Web Services

Here's a recent article about calling an external (internet) web service from inside a database:

Using Web Services in a PocketBuilder Application, Reed Shilts, Mar. 22, 2008

The title says "PocketBuilder" but most of the real work is performed by a few lines of SQL inside SQL Anywhere:

• CREATE FUNCTION f ( ... ) TYPE 'SOAP:DOC' to pass parameters to the external web service, receive the XML data it sends back, and return the XML to the caller as a LONG VARCHAR string,

• SELECT ... FROM OPENXML ( f ( ... ) ) WITH ... to invoke the function, pass it parameter values and parse the XML string it returns into a relational result set, and

• CREATE PROCEDURE to do the SELECT and return the result set to the application program (in this case, a PocketBuilder DataWindow).

You can use this facility from any application development environment that can call a stored procedure to get a result set, not just PocketBuilder.

I don't much like the formatting (or lack thereof) in the sample code, but Sys-Con Publishing has never pretended to be reader-friendly... Reed's a good guy, I'm not blaming him, and at least the samples have line breaks :)

Here is the other article that Reed's article refers to (but doesn't link to... thanks again, Sys-Con! do you even get "the internet"?):

Consuming Amazon Web Services Using PowerBuilder and SQL Anywhere 10.0.1, Deanne M. Chance, Jan. 4, 2008

Deanne's article contains more detailed sample code. The four "Listings" are stored together in one separate file so just jump to "Listing 1" and print what you see.

Alas, the one "Table" and five "Figures" are stored in 6 separate files... maybe Sys-Con used up their quota of hypertext links inside this article, and couldn't affort an external link to this article.

Apparently they also used up their quota of disk space because one of the Figures is hopelessly blurred, another barely readable.

Anyway, once again it's not the author's fault, AND THE BOTH ARTICLES ARE WORTH THE EFFORT! [shouting intended]

Monday Morning Work Avoidance

Warning: Some readers may find this material out of character.

Or not.

I don't know which is worse.

Maybe both.

---

ANSI Standard Haiku

12 lines, 6-8-5-7-9-9-10-3-3-8-5-2 syllables

You're sitting in your cube
your co-workers are all talking
about March Madness
but you don't know anything
about U.S. college basketball
and you've read I Am Charlotte Simmons
and it made you vaguely nauseated
here's your chance
to fit in
by wasting the company's time
like everyone else
read on!

---

Easter isn't just about garbage pickup being on Saturday instead of Friday.

It's also about the Quartodeciman Controversy.

---

You may be among the fifty zillion people who've seen this already, but apparently I live under a rock: Human Tetris just won a Youtube award, and I can see why.

---

Many years ago I met some "Anti-Comma People" [citation needed] at a conference. Now THERE'S a frightening cult, they believe you should never use commas for anything, ever. I escaped their grip in the nick of time, and not a day goes by that I don't agonize over whether or not to use a comma. Like several times in this paragraph.

---

Just too funny: Alan Greenspan Loses His Mind

---

"Microwave McDonald's" - If you stand in front of one McDonald's, you can see the next one - a word play on the line-of-sight property of microwave communications, applied to the proliferation of McDonald's and other franchises in certain areas like near the Miami airport

Go ahead and Google it, but I just made it up.

---

I used to be an Obama fan.

Now I'm just a "Typical Person".

I'm waiting for the t-shirt, it's not exactly what I want yet.

---

Continuing with the Democratic Party's race to pick The Best Loser...

Hillary Clinton's media people have learned a brand-new, very valuable lesson: never use stock footage, for anything, ever.

---

My two beagles had an "off-books breakfast" this morning. That's when I've forgotten to refill their daily food bottles with carefully measured portions of dry kibbles, and it's early morning, and I'm trying to get to a coffee, and they're bouncing around saying "Feed me! Feed me!" in Uno-Speak, so I just reach into the big storage bins and give them unmeasured handfuls of the stuff. If they had a brain, they'd be thanking me, but they don't, so they don't.

I'm just happy they've stopped jumping up and down.

---

Is it Tourette's, or is it Asperger's?

I keep repeating "MySQL is free!"

---

Trust me, go look at Little Bobby Tables

---

You know it's gonna be very bad news when you hear this: "We regret to inform you..."

Not so bad, but pretty bad: "In order to serve you better..."

As in, "In order to serve you better we are reducing our hours of service."

---

Is XML a sacred cow?

Well, that usage is offensive to many who hold life sacred. Not to mention cows. XML is nothing like a cow. Cows are useful.

Cows are also simple and easy to understand.

---

XML is not a programming language. It does not contain any way to execute algorithms or control the flow of execution.

A cow is not a programming language either. However, a cow can perform certain tasks like walking, eating, contributing greenhouse gases, and so on. A cow can also control the flow of these tasks; for example, it can decide to eat the grass over here, or walk over there and let one rip.

---

Did you enjoy Little Bobby Tables?

Now try this: Are you coming to bed?

---

And again: Make me a sandwich

If you didn't get that one, you're not a Linux geek... which may be a good thing.

On the other hand, if you did get it, you might already have the shirt.

---

Winter must be over... I just tasted a tomato that tasted like a tomato.

---

Oh! Oh! A new toy! XSD! Can I use it on this project, can I please?

Huh? Can I?

Please?

Oh, PLEASE let me use it, I just learned about in school!

Oh, LOOK at what we can do with it, we can put it on top of XML and Xpath and XQuery, and then we'll have a great big pile!

---

From a recent newsgroup thread that drifted over to a discussion of SQL Anywhere's limit on the number of rows in a single table: 1,099,511,627,776:

[long-winded fanboi justification of the limit by yours truly, ending with...]

For all practical purposes, SQL Anywhere limits are the same as "infinity".

Response...

That reminded me of this:

"2 plus 2 equals 5 for sufficiently large values of 2"

see: here

Kerry Liles
Senior Technical Writer
iAnywhere

OK Kerry, I get the 2 plus 2 equals 5 (LOL), but not the link here.

Is it a clever shibboleth serving to separate the smart (Waterloo) from the not-so-much (Toronto)?

Or am I in a FedEx commercial?

---

I'm not laughing at you, I'm laughing near you.

---

Oh, gosh, I get it now, I'm supposed to press the Search Books button to go here!

Doh! (the shibboleth worked)

---

I used to think "blog" was Old English for "too much time on your hands."

Now I know it's true.

And so do all of you. <g>

Sometimes It's The Little Things That Count (3)

The Panorama beta now lets you code SET column = DEFAULT in an UPDATE statement.

"So what?" you say? Well, let's say you write some code that resets a column to have the default value as defined for that column in the original CREATE TABLE.

Now let's suppose the DEFAULT is changed via ALTER TABLE... if you had used the SET column = DEFAULT in your UPDATE you don't have to fix anything.

Can I have another "Woohoo!" please?

CREATE TABLE t (
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL DEFAULT '1' );

INSERT t VALUES ( 1, DEFAULT );
INSERT t VALUES ( 2, DEFAULT );

UPDATE t SET data = 98 WHERE pkey = 1;
UPDATE t SET data = 99 WHERE pkey = 2;

UPDATE t SET data = DEFAULT WHERE pkey = 1;

ALTER TABLE t ALTER data DEFAULT 2;

UPDATE t SET data = DEFAULT WHERE pkey = 2;

SELECT * FROM t ORDER BY pkey;

pkey,data
1,1
2,2

Sometimes It's The Little Things That Count (2)

Starting with the Panorama beta, ENDIF and END IF can be used interchangeably, as can END CASE versus END, in expressions and statements.

Can I have a "Woohoo!" please?

SELECT IF dummy_col = 0
          THEN 'zero'
          ELSE 'not zero'
       END IF AS a,

       IF dummy_col = 0
          THEN 'zero'
          ELSE 'not zero'
       ENDIF AS b,

       CASE dummy_col
          WHEN 0 THEN 'zero'
          ELSE 'not zero'
       END CASE AS c,

       CASE dummy_col
          WHEN 0 THEN 'zero'
          ELSE 'not zero'
       END AS d;

IF 0 = 0 THEN
   MESSAGE 'zero';
ELSE
   MESSAGE 'not zero';
END IF;

IF 0 = 0 THEN
   MESSAGE 'zero';
ELSE
   MESSAGE 'not zero';
ENDIF;

CASE
   WHEN 0 = 0 THEN
      MESSAGE 'zero';
   ELSE
      MESSAGE 'not zero';
END CASE;

CASE
   WHEN 0 = 0 THEN
      MESSAGE 'zero';
   ELSE
      MESSAGE 'not zero';
END;

Unpublished MySQL FAQ

Q: How much does MySQL cost?
A: MySQL is free!

Q: Why should I use MySQL?
A: MySQL is free!

Q: Who do I call if I find a bug in MySQL?
A: MySQL is free!

Q: Is database recovery automatic after the server crashes?
A: MySQL is free!

Q: Are all data changes recorded in the binary log?
A: MySQL is free!

Q: How do I get referential integrity?
A: MySQL is free!

Q: How do I get multi-user transaction control?
A: MySQL is free!

Q: What happens when ALTER TABLE ... SET NOT NULL encounters a NULL value?
A: MySQL is free!

Q: What happens to foreign keys when InnoDB is not running?
A: MySQL is free!

Q: How do I get snapshot isolation (multiversion concurrency control)?
A: MySQL is free!

Q: What happens when you divide by zero in MySQL?
A: MySQL is free!

Q: What happens when an application tries to insert a NULL value into a NOT NULL column with no DEFAULT?
A: MySQL is free!

Q: What happens when an application tries to insert a NULL value into an AUTO_INCREMENT PRIMARY KEY column?
A: MySQL is free!

Q: What happens to the table when you add or drop an index?
A: MySQL is free!

Q: What rows will WHERE column IS NULL match, when applied to a NOT NULL column?
A: MySQL is free!

Q: Does MySQL let you code spaces in function references, as in SELECT MIN ( salary )?
A: MySQL is free!

Q: How do I get to use all the column and index types?
A: MySQL is free!

Q: What happens when you try to insert 'xxxxxxxxxx' into a VARCHAR(5) column?
A: MySQL is free!

Q: What happens when you try to insert 123456.78 into a NUMERIC(5,2) column?
A: MySQL is free!

Q: What happens when you try to insert '2008-02-31' into a DATE column?
A: MySQL is free!

Q: What happens when you try to insert '2008-03-00' into a DATE column?
A: MySQL is free!

Q: What happens when you try to insert '0000-00-00' into a DATE column?
A: MySQL is free!

Q: What happens when you try to insert 'xxx' into a DATE column?
A: MySQL is free!

Q: What happens when you try to insert 99999999999 into a INT column?
A: MySQL is free!

Q: What happens when you try to insert 'xxx' into a INT column?
A: MySQL is free!

Q: What happens when you try to insert a numeric value into an ENUM column?
A: MySQL is free!

Q: What happens when you try to insert an out of range index value into an ENUM column?
A: MySQL is free!

Q: How does AUTO_INCREMENT work?
A: MySQL is free!

Q: How does the TIMESTAMP data type work?
A: MySQL is free!

Q: How do I use time values with fractional seconds?
A: MySQL is free!

Q: How do I use time zones?
A: MySQL is free!

Q: Is MySQL a relational database management system?
A: MySQL is free!

Q: Will MySQL handle my database?
A: MySQL is free!

(continued at Unpublished MySQL FAQ (2))

CREATE EVENT ... TYPE DEADLOCK

Panorama has introduced a new EVENT type for catching deadlocks. The most common type of deadlock is cyclical deadlock, and it happens when each connection has a lock on something the other connection wants.

For example, let's say connection 1 has updated row 1 in a table, and connection 2 has updated row 2. If connection 1 tries to update row 2, it will be blocked by the lock held by the other connection... that's a fairly common occurrence, no big deal unless the other user goes out to lunch without committing.

But let's suppose connection 2 now tries to update row 1... suddenly both connections are blocked, neither can do anything at all, not even commit or rollback. They are completely stuck, in what is sometimes called a "deadly embrace" or deadlock.

Cyclical deadlocks are almost always the result of application program design errors. If two different pieces of code can update the same set of two or more rows, usually in two different tables, they should perform the updates in the same order: UPDATE A first, then UPDATE B, in both pieces of code. That way, when two connections collide, one of the connections will get to the UPDATE A first, and the other connection will be blocked when it reaches UPDATE A... before it gets any locks that might block the first connection... blocking good, deadlocking bad.

SQL Anywhere instantly detects and resolves deadlocks: it executes a ROLLBACK on the last connection that became blocked, and returns an almost-useless error message to the connection that was picked as the victim. Once that happens, all further trace of the deadlock vanishes, and unless you make the effort to record them it's gonna be hard for you to figure out what happened.

"Almost-useless" might sound a bit harsh, but all it says is "deadlock detected"; scroll down to see an example. Even if it did include extra diagnostic information, a message sent back to a client application often doesn't help the administrator or developer sitting thousands of miles away.

Panorama makes it easy for you to record deadlocks by providing the new CREATE EVENT ... TYPE DEADLOCK that gets executed whenever a deadlock is detected and resolved.

Let's plunge into the code:

CREATE EVENT deadlock TYPE deadlock HANDLER
BEGIN
   -- Copy fresh entries from sa_report_deadlocks()
   INSERT deadlock (
      snapshotId,
      snapshotAt,
      waiter,
      who,
      what,
      object_id,
      record_id,
      owner,
      is_victim,
      rollback_operation_count )
   SELECT *
     FROM sa_report_deadlocks()
    WHERE NOT EXISTS ( 
       SELECT * FROM deadlock
        WHERE deadlock.snapshotId
            = sa_report_deadlocks.snapshotId        
          AND deadlock.snapshotAt
            = sa_report_deadlocks.snapshotAt );         
   COMMIT;
   MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, 
      ' deadlock' ) TO CONSOLE;
END;

The builtin sa_report_deadlocks() procedure returns a result set containing at least two rows for each deadlock that has occurred since the server started: one for the victim, one for the other connection. The INSERT above copies that data into a permanent user-defined table called "deadlock". The WHERE NOT EXISTS is needed to prevent old rows from being copied again when another deadlock happens.

Caution: This is not your father's sa_report_deadlocks. That procedure might have been introduced in SQL Anywhere 9.0.2 but its result set has been improved a lot in Panorama.

Here's what the permanent table looks like; it has one extra column "row_number" to act as an artificial primary key, plus columns for everything returned by sa_report_deadlocks():

CREATE TABLE deadlock (
   row_number      BIGINT NOT NULL 
                      DEFAULT AUTOINCREMENT,
   snapshotId      BIGINT NOT NULL,
   snapshotAt      TIMESTAMP NOT NULL,
   waiter          INTEGER NOT NULL,
   who             VARCHAR ( 128 ) NOT NULL,
   what            LONG VARCHAR NOT NULL,
   object_id       UNSIGNED BIGINT NOT NULL,
   record_id       BIGINT NOT NULL,
   owner           INTEGER NOT NULL,
   is_victim       BIT NOT NULL,
   rollback_operation_count UNSIGNED INTEGER NOT NULL,
   PRIMARY KEY ( row_number ) );

In order to make this work, you have to tell the server to gather the report data:

SET OPTION PUBLIC.LOG_DEADLOCKS = 'ON';

It also helps a lot if you enable the 'LastStatement' connection property so the report data will show the actual SQL statements that were blocked. You do this by using the dbsrv11 -zl parameter; that's "dash-zee-el", not "dash-zee-one".

(that's "dash-zed-el" for fans of "zed-zed-top" :)

To see how it works, we'll force a deadlock using two dbisql sessions. First, here is command file that creates and starts a database and two dbisql's, updated for Panorama (beware the wrapping, there are only 4 commands):

"%SQLANY11%\bin32\dbinit.exe" -et -s ddd11.db

"%SQLANY11%\bin32\dbspawn.exe" -f "%SQLANY11%\bin32\dbsrv11.exe" -o dbeng11_log_ddd11.txt -os 10M -x tcpip -zl -zp -zt ddd11.db

"%SQLANY11%\bin32\dbisql.exe" -c "ENG=ddd11;DBN=ddd11;UID=dba;PWD=sql;CON=ddd11-1"

"%SQLANY11%\bin32\dbisql.exe" -c "ENG=ddd11;DBN=ddd11;UID=dba;PWD=sql;CON=ddd11-2"

After you run the CREATE EVENT, CREATE TABLE and SET OPTION statements from above, you can run these commands to see a deadlock:

-- Cyclical Deadlock

-- dbisql Session 1, Step 1.
UPDATE RowGenerator SET row_num = 1 WHERE row_num = 1;

-- dbisql Session 2, Step 1.
UPDATE RowGenerator SET row_num = 2 WHERE row_num = 2;

-- Session 1, Step 2.
UPDATE RowGenerator SET row_num = 2 WHERE row_num = 2;

-- ...Session 1 will be blocked by Session 2.

-- Session 2, Step 2.
UPDATE RowGenerator SET row_num = 1 WHERE row_num = 1;

-- ...deadlock will be detected and resolved.

Here's how dbisql Session 2 reports the error:



Here's a query showing what happened; "waiter" is the connection number, "who" is the user name, "what" is the CONNECTION_PROPERTY ( 'LastStatement' ) and "is_victim" tells you which connection got the ROLLBACK:

SELECT waiter,
       who,
       what,
       is_victim
  FROM deadlock 
 ORDER BY row_number;

And now a correction from Ivan Bowman: "It is not always the last connection that became blocked that is selected as a victim. In particular, if any of the connections participating in a deadlock cycle has the Blocking_timeout option set, then the connection in the cycle that would timeout soonest is selected."

Plus, a pop quiz for Panorama participants, courtesy Anil Goel: What is strange about the cyclical deadlock example shown above, and what effect does that strangeness have on the new rollback_operation_count column returned by sa_report_deadlocks?

And a request: Can someone please tell me what sa_report_deadlocks returns for a deadlock involving three or more connections? Or is the question moot?

Sometimes It's The Little Things That Count

In Panorama, the SQL Statements frame in dbisql shows line numbers:



I know, that's like talking about the cup holders before any other feature of a new car... but like the subject says, sometimes it's the little things that count.

SQL Anywhere "Panorama" Beta

For a long time the names of ski resorts have been used as code names for each new version of SQL Anywhere. Personally I've been hoping for Breckenridge but no luck so far: the next version is named after Panorama Mountain Village in British Columbia.

Here's where you can register to get your own copy of Panorama.

IvanAnywhere

Finally, IvanAnywhere has
his own webpage!

Today's Quiz: What's Wrong With This Picture?

Ah, the life of the database consultant is truly magical, always dealing with the best and brightest of implementations!

...and now we return to Planet Earth :)

Here's some information about a production SQL Anywhere 8 database; can you see anything, um, unusual about these numbers?

Hint: It has nothing to do with the number of rows; four million is not a big number.

I'm Not Worthy!

29 postings in 29 days, I thought this blog was doing pretty well in it's first month.

Yeah, right... until I saw Jeff Atwood's Coding Horror... truly awesome!

Hence the title of this posting, a quote from Garth and Wayne.