Based on the official Ubuntu 8.04 package from Sun (thanks to Alfredo Kojima) I prepared a package of MySQL Workbench 5.1.7 for Debian. I am going to upload it to experimental soon, in the meanwhile it is available (amd64 only) from here:

http://people.debian.org/~nobse/mysql-workbench/

Feedback appreciated!

HTTP authentication is quite popular for web applications. It is pretty easy to implement and works for a range of http applications; not to mention your browser.

Basic Auth

The two main authentication schemes are 'basic' and 'digest'. Basic is pretty easy to implement and appears to be the most common:

<?php

$username = null;
$password = null;

// mod_php
if (isset($_SERVER['PHP_AUTH_USER'])) {
    $username = $_SERVER['PHP_AUTH_USER'];
    $password = $_SERVER['PHP_AUTH_PW'];
    
// most other servers
} elseif (isset($_SERVER['HTTP_AUTHENTICATION'])) {
    
…

The frustration builds.

I have come to despise MySQL's sql_mode. It is a hack of the most gargantuan proportions.

Basically, the optimizer just ignores the sql_mode whenever it is convenient for it to do so. More importantly, the optimizer silently ignores bad datetime input in various places. The reason for this is because of my statement above: sql_mode is a big ole' hack. Instead of fixing the runtime executor in MySQL to use real ValueObject types — that are immutable and know how to convert (and not convert) between each other, the runtime is a mess of checks for various runtime codes, warning modes, "count_cuted_field" crap and other miscellany that obfuscates the executor pipeline almost beyond recognition.

Slowly, I am attacking the mess, but the executor is so fragile that even tiny changes can wreak havoc on the system, so the going is slow and painful. It's no wonder that the release cycle for the MySQL …

One of InnoDB's features is that memory allocated for internal tables definitions is not limited and may grow indefinitely. You may not notice it if you have an usual application with say 100-1000 tables. But for hosting providers and for user oriented applications ( each user has dedicated database / table) it is disaster. For 100.000+ tables InnoDB is consuming gigabytes of memory, keeping definition in memory all time after table was once opened. Only way to cleanup memory is to drop table or restart mysqld - I can't say this is good solution, so we made patch which allows to restrict memory dedicated for data dictionary.

Patch was made by request of our customer Vertical Response and released under GPL, so you can download it there …

One of the most common patterns I see in my consulting work is identifiers that are generated by MD5() or UUID(). Many times this is done in an application framework or something similar – not software the client has written. From the application programmer’s point of view, it’s just an incredibly handy idiom: generate a unique value and use it, you’re done. Those values tend to appear in session identifiers, but that’s not the only place; I especially notice them in apps that use Java’s Hibernate interfaces, whether session IDs are involved or not.

MySQL by default is vary lax with data validation. Silent conversions is a concept that is not a common practice in other databases. In MySQL, instead of throwing an error, a warning was thrown and many applications simply did not handle warnings. With the introduction of sql_mode=STRICT_ALL_TABLES (or TRADITIONAL), in MySQL 5, a better level of validation now exists.

My understanding was that Warnings are now thrown as Errors, therefore eliminating the need to do a SHOW WARNINGS to confirm any problems after every query (this is a performance overhead on a high volume system due to the round trip latency).

However I found an instance where MySQL in STRICT Mode still throws warnings, leading to the question, are there any other areas, and does the earlier statement “Warnings are now thrown as Errors” hold true.

Here is my seeding process to showing the problem.

mysql> create table i(i tinyint, unique key( …

I've been working as a Ruby on Rails developer the last couple of months. It's interesting to see how my impression of MySQL changes when I'm on the other side - and using a development environment I am less familiar with. Here are a two things I wished I could have been able to do:

• When --log-warnings=2 is enabled, log all statements the server receives that cause warnings or syntax errors.
• When --log-warnings=2 is enabled and --some-other-setting, log all statements which return empty results.

Not that it caused me too much pain - but I think I could have benefited. I think I've read something about both of these before too... anyone know if it was in the Drizzle or Google patches?

I've been working as a Ruby on Rails developer the last couple of months. It's interesting to see how my impression of MySQL changes when I'm on the other side - and using a development environment I am less familiar with. Here are a two things I wished I could have been able to do:

• When --log-warnings=2 is enabled, log all statements the server receives that cause warnings or syntax errors.
• When --log-warnings=2 is enabled and --some-other-setting, log all statements which return empty results.

Not that it caused me too much pain - but I think I could have benefited. I think I've read something about both of these before too... anyone know if it was in the Drizzle or Google patches?

I just finished updating my website with new information about a training class I will be teaching in the Metro Atlanta area next month.  This five-day class will begin on March the 30th and will cover everything that a beginner to intermediate-level database administrator needs to know to work effectively with MySQL Server.

Every student will receive a free laptop for use during the class. When the class is done, the student gets to take the laptop home!

The curriculum is available at http://www.paragon-cs.com/training

I limit the class size to twelve students in order to provide individual attention to each student so spacing is definitely limited.  In addition, if you sign up before the end of day on March the 2nd there is a $200 discount.

This is #5 in a series of blog postings about MySQL Performance Schema.

I mentioned the PERFORMANCE_SCHEMA.SETUP_INSTRUMENTS table in an earlier post. Recently I saw a bug report where I could actually use it in a meaningful way. So today I can talk about SETUP_INSTRUMENTS and with a “realistic” example.

The table has three columns: NAME, ENABLED, TIMED.
mysql> SELECT column_name,column_type FROM information_schema.columns WHERE table_name=’setup_instruments’;

+-------------+------------------+
| column_name | column_type      |
+-------------+------------------+
| NAME        | varchar(128)     |
| ENABLED     | enum('YES','NO') |
| TIMED       | enum('YES','NO') |
+-------------+------------------+
3 rows in set (0.00 sec)

The NAME column corresponds to the NAME column in EVENTS_WAITS_CURRENT.
The ENABLED column is ‘YES’ to enable instrumenting, ‘NO’ to disable.
The TIMED column …