Same query, 3 databases, 3 different results

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     | ?column?
----------+----------
 00:05:10 | 00:07:45
(1 row)

I think that the behaviour from PostgreSQL is the correct one. MySQL will just remove the :'s to get the string 000510 and then multiplies that value. The behaviour from SQLite is even more strange.

Of course for MySQL you could use the time_to_sec and sec_to_time functions.

mysql> select t,sec_to_time(time_to_sec(t)*1.5) from t1;
+----------+-------------------------------+
| t        | sec_to_time(time_to_sec(t)*2) |
+----------+-------------------------------+
| 00:05:10 | 00:07:45                      |
+----------+-------------------------------+
1 row in set (0.00 sec)

But those functions are not available on SQLite.

sqlite> select t,sec_to_time(time_to_sec(t)*1.5) from t1;
Error: no such function: sec_to_time

How not to grant permissions

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 the grant looks like this:

GRANT USAGE ON *.* TO 'logicaldoc'@'%' IDENTIFIED BY PASSWORD '*0CD1AE57344BB752E3A08B733916948E5A4BF96C'

Now the user is protected by a password, that's mutch better!

After the third statement the grants looks like this:

GRANT USAGE ON *.* TO 'logicaldoc'@'%' IDENTIFIED BY PASSWORD '*0CD1AE57344BB752E3A08B733916948E5A4BF96C'

GRANT ALL PRIVILEGES ON `logicaldoc`.* TO 'logicaldoc'@'%'

Any user who managed to get a connection after the first statement will still be connected and will now be granted ALL PRIVILEGES including SUPER.
Update: This will not include the SUPER privilege (Thanks to Shlomi Noach for noticing this.)

And of course the flush privileges statement was not necessary because they didn't directly modify the mysql.* tables.

It should have been like this:
CREATE USER 'logicaldoc'@'localhost' IDENTIFIED BY 'password';
CREATE DATABASE logicaldoc;
GRANT SELECT, UPDATE, INSERT,… ON `logicaldoc`.* TO 'logicaldoc'@'localhost'; 

This will continue to work if the NO_AUTO_CREATE_USER sql_mode is set.
It assumes that the database connection will use a UNIX socket.

Granting specific rights instead of a simple GRANT ALL is better for security and allows mutch more functionality to work like the read_only setting for slaves, reserved connections for emergencies and the init_connect setting.

The statements seem to have been copy-pasted from the LogicalDOC online documentation. There they also suggest to supply the root password on the commandline with -ppassword instead of using -p and let MySQL prompt for the password.

This doesn't mean that LogicalDOC is insecure. It only means that the person writing the documentation probably didn't fully understand MySQL Permissions.

The OurSQL podcast episode 59 has many more information about MySQL and Security.

MyISAM Key Buffer Usage

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_blocks_used)

If the the key buffer is empty:
key_buffer_coefficient = key_cache_block_size/(key_buffer_size/Key_blocks_unused)

The 'fixed' formula:

1 - ((Key_blocks_unused * key_cache_block_size) / (key_buffer_size * key_buffer_coefficient))

The result is shown below:

The overhead in the key buffer will be allocated on startup, so the larger overhead for a larger key buffer will result in more memory usage, even if MyISAM is only used for the internal mysql tables.

Working with IP's in MySQL and MariaDB - Part 2

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.

1. Build udf_example.so which is in your mysql source. (make udf_example.so)
2. Copy the udf_example.so file from your build directory to your plugin_dir.
3. 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 request for IPv6 support to these functions.

Working with IP's in MySQL and MariaDB

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) DEFAULT NULL,
`domainname` varchar(255) DEFAULT NULL,
`ip` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `ip` (`ip`)
)

CREATE TABLE `networks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`network` int(10) unsigned NOT NULL,
`mask` int(10) unsigned NOT NULL,
`name` varchar(255) DEFAULT '',
PRIMARY KEY (`id`),
KEY `network` (`network`),
KEY `mask` (`mask`)
)

Insert and retrieve entries

mysql> INSERT INTO `hosts`(`hostname`,`domainname`,`ip`) VALUES('test6','example.com',INET_ATON('192.168.1.2'));
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM `hosts` WHERE `id`=LAST_INSERT_ID();
+----+----------+-------------+------------+
| id | hostname | domainname  | ip         |
+----+----------+-------------+------------+
|  6 | test6    | example.com | 3232235778 |
+----+----------+-------------+------------+
1 row in set (0.00 sec)

mysql> SELECT hostname,domainname,INET_NTOA(ip) ip FROM `hosts` WHERE `id`=LAST_INSERT_ID();
+----------+-------------+-------------+
| hostname | domainname  | ip          |
+----------+-------------+-------------+
| test6    | example.com | 192.168.1.2 |
+----------+-------------+-------------+
1 row in set (0.00 sec)

Convert a subnetmask to CIDR notation.

mysql> SELECT INET_ATON('255.255.255.0'),BIT_COUNT(INET_ATON('255.255.255.0'));
+----------------------------+---------------------------------------+
| INET_ATON('255.255.255.0') | BIT_COUNT(INET_ATON('255.255.255.0')) |
+----------------------------+---------------------------------------+
|                 4294967040 |                                    24 |
+----------------------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE FUNCTION mask_to_cidr (mask CHAR(15)) RETURNS INT(2) DETERMINISTIC RETURN BIT_COUNT(INET_ATON(mask));
Query OK, 0 rows affected (0.00 sec)

mysql> SET @mask='255.255.252.0';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @mask,mask_to_cidr(@mask);
+---------------+---------------------+
| @mask         | mask_to_cidr(@mask) |
+---------------+---------------------+
| 255.255.252.0 |                  22 |
+---------------+---------------------+
1 row in set (0.00 sec)

And convert a CIDR mask back to dot-decimal notation

mysql> CREATE FUNCTION cidr_to_mask (cidr INT(2)) RETURNS CHAR(15) DETERMINISTIC RETURN INET_NTOA(CONV(CONCAT(REPEAT(1,cidr),REPEAT(0,32-cidr)),2,10));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT cidr_to_mask(22);
+------------------+
| cidr_to_mask(22) |
+------------------+
| 255.255.252.0    |
+------------------+
1 row in set (0.00 sec)

Use the & operator to find the network address using an IP and mask.

mysql> SELECT INET_NTOA(INET_ATON('255.255.255.0') & INET_ATON('192.168.2.3'));
+------------------------------------------------------------------+
| INET_NTOA(INET_ATON('255.255.255.0') & INET_ATON('192.168.2.3')) |
+------------------------------------------------------------------+
| 192.168.2.0                                                      |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @netmask='255.255.255.0';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @ipaddr = '192.168.2.6';Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONV(INET_ATON(@ipaddr),10,2) ip,CONV(INET_ATON(@netmask),10,2) mask,CONV(INET_ATON(@ipaddr) & INET_ATON(@netmask),10,2) network\G
*************************** 1. row ***************************
ip: 11000000101010000000001000000110
mask: 11111111111111111111111100000000
network: 11000000101010000000001000000000
1 row in set (0.00 sec)

This can be used to join a table with host ip's with a table of networks. (Remember the netmasks table in Solaris and/or NIS?)

mysql> SELECT CONCAT(h.hostname,'.',h.domainname) fqdn, INET_NTOA(h.ip) ip, INET_NTOA(n.mask & h.ip) network, INET_NTOA(n.mask) mask FROM hosts h INNER JOIN networks n ON (n.mask & h.ip) = n.network;
+-------------------+-------------+-------------+---------------+
| fqdn              | ip          | network     | mask          |
+-------------------+-------------+-------------+---------------+
| test1.example.com | 192.168.0.1 | 192.168.0.0 | 255.255.255.0 |
| test2.example.com | 192.168.0.2 | 192.168.0.0 | 255.255.255.0 |
| test3.example.com | 192.168.0.3 | 192.168.0.0 | 255.255.255.0 |
| test4.example.com | 10.0.0.1    | 10.0.0.0    | 255.0.0.0     |
| test5.example.com | 10.0.0.2    | 10.0.0.0    | 255.0.0.0     |
+-------------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)

With the ~ operator the mask can be inverted and then be used to find the broadcast address using the XOR operator |.

mysql> SELECT CONV(INET_ATON(@ipaddr),10,2) ip,CONV(INET_ATON(@netmask),10,2) mask,CONV(INET_ATON(@ipaddr) & INET_ATON(@netmask),10,2) network, CONV(CONV(SUBSTRING(CONV(~INET_ATON('255.255.255.0'),10,2),-32),2,10) | INET_ATON(@ipaddr),10,2) broadcast\G
*************************** 1. row ***************************
ip: 11000000101010000000001000000110
mask: 11111111111111111111111100000000
network: 11000000101010000000001000000000
broadcast: 11000000101010000000001011111111
1 row in set (0.00 sec)

You could use a view to make it easier for users to select the rows they wanted without using functions. But that will result in a full scan if you search using an IP. MySQL does not optimize this by first converting the search value to an INT but converts all rows to a dot-decimal notation and then compares it. MariaDB has a nifty feature called persistent virtual columns which allow indexing. For the example below this means that instead of scanning all four rows it just uses the index to find the row we need.

mysql> CREATE TABLE `iptest`(`ip_n` int(11) UNSIGNED NOT NULL, `ip_a` CHAR(15) AS (INET_NTOA(`ip_n`)) PERSISTENT, PRIMARY KEY(`ip_n`), KEY `ip_a` (`ip_a`));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `iptest`(`ip_n`) VALUES (INET_ATON('192.168.1.1')),(INET_ATON('192.168.1.2')),(INET_ATON('192.168.1.3')),(INET_ATON('192.168.1.4'));
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `iptest`;
+------------+-------------+
| ip_n       | ip_a        |
+------------+-------------+
| 3232235777 | 192.168.1.1 |
| 3232235778 | 192.168.1.2 |
| 3232235779 | 192.168.1.3 |
| 3232235780 | 192.168.1.4 |
+------------+-------------+
4 rows in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM `iptest` WHERE `ip_a`='192.168.1.2';
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | iptest | ref  | ip_a          | ip_a | 16      | const |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> CREATE VIEW v_iptest AS SELECT ip_n,INET_NTOA(ip_n) ip_a FROM `iptest`;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM `v_iptest` WHERE `ip_a`='192.168.1.2';
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | iptest | index | NULL          | PRIMARY | 4       | NULL |    4 | Using where; Using index |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

MySQL privileges and replication

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'\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'
*************************** 3. row ***************************
Grants for user@host: GRANT SELECT, INSERT, UPDATE, DELETE ON `anotherdb`.* TO 'user'@'host'
3 rows in set (0.00 sec)

And on the slave:

slave> SHOW GRANTS FOR 'user'@'host'\G
*************************** 1. row ***************************
Grants for user@host: GRANT USAGE ON *.* TO 'user'@'host'
*************************** 2. row ***************************
Grants for user@host: GRANT SELECT, INSERT, UPDATE, DELETE ON `anotherdb`.* TO 'user'@'host'
2 rows in set (0.00 sec)

This could have happened if the user was dropped on the slave to prevent it from inserting on a ro-slave. The cure for this issue is setting sql_mode to NO_AUTO_CREATE_USER.

Another issue is mysql-proxy. MySQL proxy can be used for simple read/write splitting, failover and a lot more. But it could also make your database less secure:

• root is limited to localhost to prevent remote logins
• mysql proxy is installed on the database machine
• if you connect from a remote machine to mysql-proxy this will be proxied to mysql and the host will be localhost.

And the questions for the readers are:

• Do you allow access on your read-only slaves?
• Do you replicate mysql.* or not?

Regularly flushing the MySQL Query Cache without cron

This is a reply on Regularly flushing the MySQL Query Cache.


The original acticle is about regulary flushing the MySQL Query Cache as it will fragment over time.

There are some drawbacks for the cron method for flushing the query cache:

• It will only work on UNIX like platforms as MS Windows uses the task scheduler to schedule tasks.
• It needs credentials to login to the database.
• It's not included in your database backup

There is another method, which is native to MySQL: the event scheduler.

Step 1: Enable the event scheduler:

mysql> SET GLOBAL event_scheduler=ON;
Query OK, 0 rows affected (0.00 sec)

And don't forget to set/change this in your my.cnf or my.ini

Step 2: Create the event:

mysql> CREATE EVENT flush_query_cache ON SCHEDULE EVERY 1 HOUR DO FLUSH QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW EVENTS\G
*************************** 1. row ***************************
Db: test
Name: flush_query_cache
Definer: msandbox@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: HOUR
Starts: 2011-06-19 12:57:46
Ends: NULL
Status: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)

Please keep in mind that the query cache doesn't always give you a performance benefit due to mutex contention. See also the query cache tuner from Domas Mituzas.

You schould create a stored procedure for multi statement and/or complex statements and call the procedure from your event instead of putting it directly in your event.