Ever since I submitted the "External Language Stored Procedures" project which Eric and myself have been working on to be listed on FreshMeat, it has gained a small number of interested users and we have just started to get some feedback. Yay! Always nice to know when people are using your code. Okay, so one of the emails contained a feature request: Support for PHP stored procedures. I think I

Take a look at these two numbers:


CREATE TABLE my_table (
number_1 BIGINT(4),
number_2 INT (10)
);


Which one do you think takes up less space? The answer is that BIGINT always takes 8 bytes and that INT always takes 4 bytes. The value in the parenthesis has nothing to do with storage. The only use I am aware of that it has, is with the ZEROFILL feature (which I never use):


mysql> CREATE TABLE a (a INT(5) ZEROFILL);
Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO a VALUES (1);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM a;
+-------+
| a |
+-------+
| 00001 |
+-------+
1 row in set (0.00 sec)


It seems that there are a lot of open source packages with INT(4) (or similar). If you only need 4 digits, you …

A lot of you may already know this, but I am willing to bet there are more that don’t. I’m talking about the tee command in the bash shell, and in MySQL. For our purposes, we’ll talk about the tee command in MySQL.

Problem: You have a series of SQL statements whose results take up a few screens worth of output, and you need to take this output and send it to someone else (A DBA, MySQL Support, your mentor). You could just do a copy/paste from your terminal, but what if you realized in the end that your scroll back buffer isn’t as large as you thought it was?

Solution: Tee. Apparently, the mysql client comes with tee.

mysqlshell> tee mysqlog.sql ;
Logging to file ‘mysqlog.sql’
use dbname;
select foo from bar;
….
mysqlshell> notee;
…

Let's say you have a pool of ~100 connections to your database. A thread in your application does something like this:


START TRANSACTION;
INSERT INTO my_table (col1) VALUES ('val1');
COMMIT;
START TRANSACTION;


That last transaction was never committed and remains open. This means that InnoDB (which keeps multiple versions of each row) can not clean up and free space:


mysql> SHOW INNODB STATUS;
...
------------
TRANSACTIONS
------------
Trx id counter 0 981654
Purge done for trx's n:o < 0 979531 undo n:o < 0 0
....


One 'symptom' of this problem may be if the user complains that their database is much faster after they restart, since …

With 130 entries in the “MySQL” category and no MySQL-related subcategories, my blog had become impossible to search and navigate easily.

And thus I created a number of new categories for the MySQL entries within my blog. They’re listed in the left navigation bar, below the months, as well as below:

• MySQL Server, MySQL Cluster, Falcon
• Connectors: PHP, Ruby on Rails
…

I've been talking to some Montrealers about using Amazon EC2. One of the small issues with EC2 is that you have no fixed IP addresses for your instances. This means that people are having to use DNS, which works well 99% of the time[1].

One of the problems for me, is that I can't as easily have a virtual interface, which is the IP address of the current master database server - I have to have a hostname like mysql-master.mydomain.com. Which means that if I switch masters, I have to wait for the TTL length for old clients to stop connecting to the previous master (In talking to Rightscale, they set it to 45 seconds, PeterZ has blogged about with not to use 0 second TTLs here).

The possible solution - when you switch masters, the previous master shuts …

I figure while I'm in the blogging spirit, I should announce where you can catch me presenting early next year:

March 12-14 - PHP Quebec
* Security from the Database Perspective
* Breaking the Rules

April 14-17th - MySQL Conference
* Exploring Amazon EC2 for Scale Out Applications

May 21st-23rd php|tek
* Performance Tuning MySQL
* Designing for High Availability

I decided to do something different and submit about 10 outlines for possible talks, so it's interesting to see what each of the organizers eventually arrive at.

With MySQL 5.0, it's much easier to do SQL Injection attacks, because you can use UNION against information schema. You couldn't do this with the old SHOW commands.

i.e.

SELECT * FROM users WHERE id = $id;

becomes:

SELECT * FROM users WHERE id = 0 UNION 
SELECT group_concat(table_name) FROM information_schema.tables  
WHERE table_schema=DATABASE() group by table_schema;

The rules to a union in MySQL is that the second query must match the same number of columns as the first query. In this case, you can just keep changing it till you get the column count right:

SELECT group_concat(table_name), 1, 2, 3, 4 FROM information_schema.tables 
WHERE table_schema=DATABASE() group by table_schema;

I presented on these types of attacks here

mysql> create database attack;
Query OK, 1 row affected (0.00 sec)

mysql> use attack;
Database changed

mysql> create table users (id INT UNSIGNED NOT NULL PRIMARY KEY auto_increment, username varchar(30) NOT NULL, 
password char(32) NOT NULL, UNIQUE KEY (username));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT into users (username, password) VALUES ('morgo', MD5('my_password')),
 ('ted', MD5('another_password'));
Query OK, 2 rows affected (0.41 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM users;
+----+----------+----------------------------------+
| id | username | password                         |
+----+----------+----------------------------------+
|  1 | morgo    | a865a7e0ddbf35fa6f6a232e0893bea4 |
|  2 | ted      | 280fb9194368f9d1d44f8ddcc13f2717 |
+----+----------+----------------------------------+
2 rows in set (0.00 sec)

mysql> CREATE TEMPORARY TABLE users_copy LIKE users;
Query OK, 0 rows affected (0.04 sec)

mysql> …

I spent Thursday and Friday in Zurich, Switzerland visiting my friends Marcus and Caitlin and attending the Google Open Source Jam.

On Thursday, I arrived in the early afternoon in Zurich. Getting to Zurich from Siegburg is easy and takes less than five hours as there is a direct ICE connection to Basel and from there it is just one more stop with an …