After a long day packed with sessions at the MySQL Users Conference it seems like the evening would be a time to relax, right? Wrong.

The evenings seem to be more packed than the days. Four birds of a feather sessions that all were equally interesting but overlapping tonight. Didn't make it to the "Server Failover" BoF because I wanted to check out the Perl BoF. Had to slip out of that to hit the "building a new SHOW command." Just when that was getting underway (and was looking really cool) I had to slip out to attend the "How to have a good MySQL meetup." This week is turning out to be one of the most packed weeks I've had in a long time.

But this is a very good kind of busy.

Oh, and I finally crossed paths with Frank after a lot of hit and miss. So very cool to have met and hung out …

This happened to me 2 weeks ago on a site I run pro bono, and I forgot to blog about it. A comment in the “MySQL Automated Failover with Scripts” BOF reminded me about it, so here goes.

Basically, the web application stopped being able to talk to the database. I run the staging/test server AND the database, and the production site is run by someone else. So I checked out the logs, and indeed, there are many of the following:

050814 18:16:42 Aborted connection 241474 to db: ‘database’ user: ‘userhere’ host: `localhost’ (Got an error reading communication packets)
050814 23:58:43 Aborted connection 241487 to db: ‘database’ user: ‘userhere’ host: `localhost’ (Got timeout reading communication packets)

(and of course going up through the time it crashed, and even today). I believe –log-warnings is ON by default, because it’s not in my my.cnf and show variables shows that it’s on. But if …

Last presentation of Tuesday at MySQL UC is Giuseppe Maxia talking about cool things you can do with dynamic SQL in MySQL. I've had a chance to hang out with Giuseppe a bit during the conference, a fun person to be around (great stories). Just learned that Giuseppe is a perlmonk, even more cool than before.

(Sheeri's doing a writeup two seats over, I think I'll just kick back and enjoy this!)

Advanced Stored Routines techniques

Higher-order functions in math and computer science are functions which:
take one or more functions as an input
OR
output a function
in MySQL, we’re talking about stored routines that create other stored routines.

Why?
Pivot tables,
Global table checksums
Getters and setters
Multi-row insert query makers
Iterators
Dynamic functions allow you to access data from INFORMATION_SCHEMA and create dedicated dynamic routines and overcome syntax that doesn’t accept variables.

Officially, MySQL stored routines can’t do it.

However, Guiseppe is a really smart guy (as I’m learning firsthand this week)…

One way:
You can create a text definition for a stored procedure from a routine, even though you can’t actually create the stored procedure. Get the text back into the …

While attending Lars Thalmann’s session on Row Based Replication I began to think about how the new features will allow us to do creative new things with MySQL Replication. For example we can now issue an update query with a join and have one slave update only rows from table a and another slave only update rows from table b. This is very powerful but also very dangerous.

My experience has been that the average php developer can write average sql but doesn’t tend to think about the overall impact on a system when writing code. This is where system architects apply. A system architect can view an entire system and design it’s components to scale well, be fault tolerant and easily maintained. An application level developer will implement the system based on the design. Replication is a very deceptive convergence between the architect role and the developer …

John David Duncan is talking about UDFs in MySQL 5 (or SQL as a Glue Language) at the MySQL Users Conference. I just realized that this is JD.

Why do we like MySQL? Because it's open source fast, reliable, and easy to use. This has been true since the early days of MySQL.

For many years MySQL did not have stored procedures which can make client/server apps more efficient. MySQL made up for this with lots of internal functions and UDFs.

In SQL standard (SQL92) there are 7 defined string functions. Oracle 8i had 11 string functions. MySQL 4 had 40+ string functions.

JD looks at PHP, Perl and MySQL and how they all are a part of a stack.

User-Defined Functions are fairly simple C programs loaded into the server at runtime. In MySQL you do CREATE FUNCTION metaphon RETURNS STRING SONAME "udf_example.so".

In MySQL 5 there are triggers, …

This is cool, although I haven't used it so it will have to be a theoretical coolness.

A few months back Anders Karlsson posted a UDF and accompanying application for debugging stored procedures on sourceforge. The idea is that you can insert statements into your stored procedure for debugging purposes. The statements use a UDF that sends UDP messages to a debug server (provided with the UDF) to allow you to discover information about what is happening as your stored procedure is executed.

I can relax now, My talk is done and I’ve been talkinq to people about more specific things. Several have congratulated me on it.

Looking forward to the BOF, although sleep would be nice.oh how I’m going to sleep on the plane

Why is this needed?
“world” database current sample is 3 tables, cities, countries and langs.
Not the Intellectual Property of MySQL, so it can’t be bundled with the software
No table type diversity, not a lot of data type diversity
No MySQL 5 features
Not a lot of data
Not really used in tutorials and articles because it’s not robust enough

Scenario
Basically, needed a sample database that was new, that allowed for many queries.
Used an idea from a Dell Sample DB
It’s 2 stores, in 2 different countries.

Designing the Database
Designed with MySQL Query Browser
Normalized
Community provided feedback and content
Trick was balance between simplicity and completeness
Learning db, so people need to understand it
After schema was approved, data was loaded
Stored …

MySQL Users Conference 2006, SANTA CLARA, Calif. - MySQL AB today announced plans for a certification program for the many powerful database storage engines being developed for its flagship MySQL database server by third-party partners and the open source developer community. Several industry-leading companies -- including Innobase OY (a subsidiary of Oracle Corporation) and Solid Information Technology -- are supporting the new program by joining MySQL AB in producing open source engines for the millions of MySQL database users worldwide.