In a previous post about different output for the same query there were 3 databases (MySQL, PostgreSQL and SQLite) and 3 different results. I attended the " Fixed in Drizzle: No more GOTCHA's " talk during Percona Live London. The talk was full of issues which I've encountered many times and which were all fixed. So I wondered whether or not this is already fixed in Drizzle. Here is the results for Drizzle: drizzle> select version(); +------------+ | version() | +------------+ | 2011.03.13 | +------------+ 1 row in set (0.000418 sec) drizzle> create database test; Query OK, 1 row affected (0.000622 sec) drizzle> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Schema changed drizzle> create table t1 (id serial, t time); Query OK, 0 rows affected (0.001479 sec) drizzle> insert into t1(t) values ('00:05:10'); Query OK, 1 row affected (0.001717 se...

The SQL standard leaves a lot of room for different implementations. This is a little demonstration of one of such differences. SQLite  3.7.4 sqlite> create table t1 (id serial, t time); sqlite> insert into t1(t) values ('00:05:10'); sqlite> select t,t*1.5 from t1; 00:05:10|0.0 MySQL 5.6.4-m5 mysql> create table t1 (id serial, t time); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1(t) values ('00:05:10'); Query OK, 1 row affected (0.00 sec) mysql> select t,t*1.5 from t1; +----------+-------+ | t        | t*1.5 | +----------+-------+ | 00:05:10 |   765 | +----------+-------+ 1 row in set (0.00 sec) PostgreSQL 9.0.3 test=# create table t1 (id serial, t time); NOTICE:  CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id" CREATE TABLE test=# insert into t1(t) values ('00:05:10'); INSERT 0 1 test=# select t,t*1.5 from t1;     t ...

I went to EuroBSDcon in Maarssen, the Netherlands. It was a great conference and I might write another blog about it. I the conference bag there was a copy of the dutch Linux Magazine . The magazine is very nice and covers a broad range of topics. One article about LogicalDOC caught my attention. The LogicalDOC software uses a MySQL database. and the magazine listed some SQL code about how to create the database user: create user logicaldoc; set password FOR logicaldoc@´%´= PASSWORD('wachtwoord´); grant all privileges on logical- doc.* to logicaldoc@´%´ identified by  'wachtwoord´; create database logicaldoc; flush privileges; These statements won't work as some of the quotes are wrong. But let's ignore that. After executing the first line the grant is like this: GRANT USAGE ON *.* TO 'logicaldoc'@'%' So the user logicaldoc is allowed to connect from ANY host WITHOUT password. And yes that does work. After the second statement th...

For MyISAM one of the most important variables is the Key Buffer.  The Key Buffer is sometimes called the Key Cache. It's used as a buffer for the indices of MyISAM tables. There is some overhead in the buffer depending on the configured key block size. The official way to calculate the key buffer usage as documented in the MySQL Reference manual: 1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)  This will return the factor, so you have to multiply it with 100 to get the percentage. The Key_blocks_unused is used instead of the more obvious Key_blocks_used. This is due to the fact that Key_blocks_used is the maximum number of key blocks ever used. It will not return to 0 after a FLUSH TABLES. This calculation does not take the overhead in account. The key buffer efficiency can be calculated if the key buffer is empty or (has been) completely full. If the the key buffer is full: key_buffer_coefficient = key_cache_block_size/(key_buffer_size/Key_block...

Use DNS directly from your database mysql> SELECT lookup('localhost'); +---------------------+ | lookup('localhost') | +---------------------+ | 127.0.0.1 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT reverse_lookup('127.0.0.1'); +-----------------------------+ | reverse_lookup('127.0.0.1') | +-----------------------------+ | localhost | +-----------------------------+ 1 row in set (0.00 sec) This is how you install these functions. Build udf_example.so which is in your mysql source. ( make udf_example.so ) Copy the udf_example.so file from your build directory to your plugin_dir. Create the lookup and reverse_lookup functions mysql> CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.so'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE FUNCTION reverse_lookup RETURNS STRING SONAME 'udf_example.so'; Query OK, 0 rows affected (0.00 sec) I've created a feature ...

For MySQL it's a best practice to store IP addresses in a INT column rather than in a VARCHAR. This allows for more compact storage. This best practice is only for MySQL as PostgreSQL does have data types like inet and cidr . MySQL is equiped with the INET_ATON() and INET_NTOA() functions since version 3.23.15. In MySQL 5.6.3 the INET6_ATON() and INET6_NTOA() functions were addes to finaly add IPv6 address support. To make it easier to work with IPv4 and IPv6 addresses the IS_IPV4_COMPAT(), IS_IPV4_MAPPED(), IS_IPV4() and IS_IPV6() functions were added. The difference between the new and old functions is that the old functions use INT UNSIGNED to store IP addresses and the new function uses VARBINARY(16) for IPv6 addresses and VARBINARY(4) for IPv4 addresses. Here are some examples about how you could do nifty things with IP addresses in MySQL. Store hosts and networks in MySQL CREATE TABLE `hosts` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `hostname` varchar(255) DEFA...

This is a response on MySQL security: inconsistencies and Less known facts about MySQL user grants . As far as I know the privilege to grant PROXY privileges is also not very well understood. I blogged about that some time ago. In addion to the already highlighted issues with GRANT replication and grants can very well create an unwanted situation: master> SHOW GRANTS FOR 'user'@'host'\G *************************** 1. row *************************** Grants for user@host: GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY PASSWORD '*4994A78AFED55B0F529C11C436F85458C1F8D4C2' *************************** 2. row *************************** Grants for user@host: GRANT SELECT, INSERT, UPDATE, DELETE ON `somedb`.* TO 'user'@'host' 2 rows in set (0.00 sec) master> GRANT SELECT,INSERT,UPDATE,DELETE ON anotherdb.* TO 'user'@'host'; Query OK, 0 rows affected (0.00 sec) master> SHOW GRANTS FOR 'user'@'host...