One of the features that distinguishes MySQL 5 from it's predecessors is the increased ability to control how the server interprets SQL and how strict or relaxed it should behave towards errors. This is done by setting the server SQL mode.

Choosing an appropriate Server SQL Mode can be an important tool to ease the task of porting from database products like Oracle or Microsoft Sequal Server. Despite the terrific documentation on the MySQL Reference manual, I really felt the need to have some kind of quick reference around to make effective use of the server sql modes. So, I made up one myself:



Enjoy!

After implementing feedback from the 0.7 version and fleshing out the documentation some more, I present the 0.8 version of the Sakila sample database. Feedback is always appreciated, especially regarding the documentation and the data itself regarding appropriateness and consistency.

Changes

• Added actor_info view.
• Changed error handler for inventory_held_by_customer function. Function now has an exit handler for NOT FOUND instead of the more cryptic 1329.
• Added template for new BSD license to schema and data files.
• Added READS SQL DATA to the procedures and functions where appropriate to support loading on MySQL 5.1.
• Fixed date range issue in rewards_report procedure (thanks Goplat).

DOWNLOAD

Hi everyone,

I know it's next to nothing, but in case you happen to need it, I've got some XML-related MySQL utilities.

• 
  
• A documented XML Schema of the mysql command line utility xml format
  
• A little windows batch file that exports schema-related metadata in the ordinary mysql command line utility xml format
  

It's all as-is of course but this time, I explicitly included a couple of lines saying that the usage is all under GPL license.

Regarding the information_schema dump, there are some more tools in the works, among which is a xslt stylesheet that …

As demonstrated in Gripe: MySQL 5 - a VIEW could replace a MERGE table, MySQL 5 falls a little short when it comes to using indexes in a VIEW containing UNION. So I've tried PostgreSQL out, and much to my chagrin (being a happy little MySQL developer from way back), Postgres handles indexes in this scenario perfectly. Doh!

Yet ultimately, I'm still up the proverbial creek. What I really want is the PostgreSQL query designer (equiv of MySQL query optimizer) coupled with MySQL's modular storage engine architecture, so FEDERATED tables could be used with a VIEW and a UNION as a form of distributed partitioning. Oh well. Back to the drawing board.

There are a lot of articles around that explain that relations (tables) should be normalized (an article by Mike Hillyer will get you started) before you can store them in a relational database. The complete normalization process consists of two steps:

1. 
   
2. elimination of multi-valued attributes ('at most one value per column')
   
3. elimination of redundancy ('no duplication of data')
   

Although these are two distinct activities that solve different problems, the methods used to perform them have a great deal in common. In both cases, some columns from the original table are moved to a new table, and a foreign key relationship is constructed to be able to relate the two tables.

The …

I just tried to upgrade my MySQL 5.1 server from the development source tree. Unfortunately, with no success.

First I tried to get the latest updates via BitKeeper, but the links seem to be broken: http://mysql.bkbits.net:8080/mysql-5.0 and http://mysql.bkbits.net:8080/mysql-5.1-new.

So I decided to download the latest nightly snapshot and got me the file http://downloads.mysql.com/snapshots/mysql-5.1/mysql-5.1.10-beta-nightly-20060415.tar.gz. I extracted the file with "tar xvfz mysql-5.1.10-beta-nightly-20060415.tar.gz" (which worked without problems) and changed into the just created directory. From there, I tried to compile the source code:

…

The bulk of the OS2 code I removed last week, but since then I have found a few more thousand lines of code that needed to be removed. All of this is a rat's nest of #ifdef which will all soon to be gone. Its given me a good chance to look over a lot of code I have not touched in a while.

Inside of the MySQL server there are essentially three ports. Unix, Windows, and Netware. There are also remnants of DOS, VMS, UnixWare, and then a few other pieces laying around. Things like ifdefs around CYGWIN (which doesn't work, and we don't support it) and some pieces to support the old mit-pthreads library. Most of this is decently documented in the code but it is still there.

Why not just leave it?

1) More code for a new developer to look at and consider. An example is this is that I found an OS2 define in the new events code. I am sure that it was copied and pasted.
2) People will think it will work. …

I recently talked about the idea of a different privilege system that separates the host information from the user name. Of course, it would break backwards compatibility and so it's almost impossible to change the current behaviour entirely. Also, Beat Vontobel has suggested that there are situations where it is desireable to have user and host information combined.

However, there might be a (not too complicated) solution. I'm thinking of a separate database that manages the user privileges according to the alternative system. Stored Procedures and Triggers could sync the information in the new database to the mysql schema database - and maybe also the other way around. This would require no changes in the MySQL server at all and every user could decide for himself, whether or not to use the alternative system. And finally, the users managed by the new system should not …

Fast Database App = Hybrid Database App

Ok, so you're trying to figure out which transactional general purpose
database engine is the fastest to build/deploy your app with?

Now, since all engines (in the last 10 years) are MVCC, ARIES, ACID... it's
going to be hard to find a database (or database storage engine) that is a head
and shoulder above the rest... You can find, perhaps up to a 2:1 ratio, for a
specialized situation.

Remember now, TPC benchmarking was invented to standardiz e the test, while
factoring in the cost of hardware — which levels the playing field of
specialized hardware vs. unusual performance.

On the other hand, you might find a database, that outperforms in a particular
niche table design due to a specialized indexing mechanism.

What does all this specialized hardware, indexing or such buy you? …

Fast Database App = Hybrid Database App

Ok, so you're trying to figure out which transactional general purpose
database engine is the fastest to build/deploy your app with?

Now, since all engines (in the last 10 years) are MVCC, ARIES, ACID... it's
going to be hard to find a database (or database storage engine) that is a head
and shoulder above the rest... You can find, perhaps up to a 2:1 ratio, for a
specialized situation.

Remember now, TPC benchmarking was invented to standardiz e the test, while
factoring in the cost of hardware — which levels the playing field of
specialized hardware vs. unusual performance.

On the other hand, you might find a database, that outperforms in a particular
niche table design due to a specialized indexing mechanism.

What does all this specialized hardware, indexing or such buy you? …