Typing umlauts and Eszet on GNU/Linux

I started studying Deutsche (German). Since learning a language requires input and output in both their forms ({listening, reading}, {speaking, writing}), I needed to configure my Debian system to write the Deutsche-specific characters:

  • The umlaut on certain vowels: ÄÖÜ äöü
  • The Eszett (ess zed) or scharfes S (double S): ß
    • Capital ß does not exist in German, so I don’t use it. But if I want to type a non-existing letter, now I can: ẞ. For some reason, this only works with some programs, not with others.

To write these chars, you need to have a compose key. Debian (and all other distros? who knows) doesn’t have it by default, so you configure it in your desktop:

KDE

See this page.

GNOME

You have a GUI: Preferences/Keyboard.

Xfce

On Debian, edit /etc/default/keyboard.

Add/edit this line to use the left Microsoft key:
XKBOPTIONS="compose:lwin"

Other key names I know: rwin, menu, lctrl, rctrl, caps, paus, prsc, sclk.

Now you can type!

Restart the DE session, of course.

Typing umlauts:

Hit the compose key, THEN type double quotes, THEN type a lowercase or uppercase vowel. Then means that you are not supposed to press 999 keys altogether.

Typing Eszett:

Hit the compose key, THEN type s, THEN type s. If both the s are uppercase and your software supports stupidity, a non-existing letter will appear.

Stupidity matters

MariaDB [(none)]> SELECT 'ß'='ẞ' stupidity1, LOWER('ẞ')='ẞ' stupidity2;
+------------+------------+
| stupidity1 | stupidity2 |
+------------+------------+
|          0 |          1 |
+------------+------------+
1 row in set (0.00 sec)

UPDATE: I filed a bug for this. It’s now “Verified”, so they seem to agree this behavior is not optimal.

Book review: Getting started with MariaDB

getting_started_with_mariadb Getting started with MariaDB, by Daniel Bartholomew, is a good book for people who wants to approach MariaDB without knowing MySQL. While this book covers all basic topics, it provides a vast overview of what MariaDB is and can do. In other words: the text is not just about SQL queries.

The book also mentions topics that are not strictly related to MariaDB, but are important for MariaDB users; for example the importance of phisically securing a server, or how to secure a LAN.

The fundamental topics for a MariaDB newbie are covered: installation, configuration basics, user management, the most important SQL statements, backups, logs and more.

What is not in this book? A lot of things – that’s why it’s called Getting started with MariaDB! Many users will probably want learn more about each topic in the book, and should probably learn some MariaDB parts that are not explained in this book: keys, the optimizer, InnoDB. So, don’t stop learning after reading this book. But if you want to learn MariaDB, you can start from here.

For more books about MariaDB: Recommended MariaDB / MySQL Books.

The most complete source of information is the MariaDB KnowledgeBase.

Enjoy!

MariaDB 10: Performing fast & inaccurate statistics

As I already wrote, COUNT(*) can be expensive in InnoDB, because this value is not stored in indexes. Some other storage engines may not store that value. Also, COUNT(), MAX(), MIN() are immediate only if executed on an indexed column. But we don’t want too many indexes, and some exotic storage engines do not support indexes – thus, we may need to execute an aggregate function on a non-indexed column.

Now, the question is: do you really need such values to be exact? If you need to report statistical values from your database, probably an error is acceptable. In this case, MariaDB 10 provides a solution: engine-independent statistics.

Note that using this feature may have a cost. Updating statistics for a table means that the server does a full table scan. That is probably why engine-independent statistics are disabled by default. But if you are already use them, or if you think you can benefit from them, you can use the statistics to retrieve the results of some aggregate queries, in a way which will not be always accurate.

Here is how to translate the queries.

COUNT():

SELECT `cardinality` FROM `mysql`.`table_stats` WHERE db_name='test' AND table_name='t';

MIN() and MAX():

SELECT `min_value`, `max_value` FROM `mysql`.`column_stats` WHERE db_name='test' AND table_name='t' AND column_name='a';

AVG(LENGTH()):

SELECT `avg_length` FROM `mysql`.`column_stats` WHERE db_name='test' AND table_name='t' AND column_name='a';

COUNT(*) WHERE IS NULL:

SELECT COUNT(*) / (SELECT `nulls_ratio` FROM `mysql`.`column_stats` WHERE db_name='test' AND table_name='t' AND column_name='a') FROM t2;

Note that this could be a division by 0, so you should handle NULL as a result.

MariaDB: DELETE … RETURNING

MariaDB 10.0.5 supports DELETE ... RETURNING, like PostgreSQL. It deletes some rows and returns the specified columns (or, more generally, the specified SELECT expressions) from the deleted rows. As for regular DELETEs, Com_delete is incremented and Com_select is not.

Let’s see what this feature does and what it does not.

You cannot:

  • Cannot use it with INSERT or CREATE TABLE to create a table containing data from the deleted rows (a sort of delete log, or easy-to-restore backup).
  • Cannot use it as a subquery.
  • Cannot use it as a cursor, and there is no INTO. So you cannot process the results in a stored routine, because there is no way to copy deleted data into variables.
  • Cannot use aggregating functions. But why should you? If the purpose is to get a row count, you can use SELECT ROW_COUNT().

You can:

  • Include a subquery, virtual column, stored function, or anything else in the RETURNING clause.
  • RETURNING * works, AS works.
  • Use it as a prepared statement.
  • Use it in a stored procedure. But in practice, you can only return a resultset.

Some of the things I included in this list should be obvious: I just stated that some documented features can work together. But I’m afraid this is not obvious in MySQL and MariaDB: they have great features, but in many cases they don’t work together.

Thanks, MariaDB team!

DELETE RETURNING is another reason to use MariaDB. And I see a task for UPDATE ... RETURNING.

Enjoy!

MariaDB: ALTER TABLE IF [NOT] EXISTS

Since when I remember (and I can remember the 3.23 version!) MySQL always supported the IF EXISTS and IF NOT EXISTS options for many DDL statements. They help developers in writing install and uninstall scripts: with IF NOT EXISTS, a table is created if needed, and there is no error (so, the script’s execution goes on) if the table is already there.

However, the case when a table exists but must be modified is problematic, because the ALTER statements never supported those options… until MariaDB 10. The old way to avoid errors was:

DROP TABLE IF EXISTS tab_name;
CREATE TABLE tab_name ... ;

But this way it is not possible to preserve current table’s data.

The cleanest solution was probably writing a script, or a Stored Procedure, which queries the information_schema database to check if a column exists, and conditionally CREATE/DROP it. Or, to avoid such queries, a more error-prone script could read the database version in use and upgrade it.

MariaDB 10.0.2 supports IF EXISTS and IF NOT EXISTS options for several ALTER TABLE sub-commands. Here’s the list of those clauses (copied from the MariaDB KnowledgeBase):

ADD COLUMN
ADD INDEX
ADD FOREIGN KEY
ADD PARTITION
 
DROP COLUMN
DROP INDEX
DROP FOREIGN KEY
DROP PARTITION
 
CHANGE COLUMN
MODIFY COLUMN

Also, these options are supported for CREATE INDEX and DROP INDEX.

Note that the rarely used ALTER COLUMN syntax has not an IF EXISTS option, so the longer MODIFY syntax must be used instead.

Also note that the PostgreSQL syntax is not supported; thus, this will not work:

ALTER TABLE IF EXISTS tab_name ... ;

At this point, you are probably wondering: “But then, is this feature totally useless for me? Is there still no way to alter a table only if it exists?”. Hey, don’t be dramatic! The solution is easy, and it’s probably better than Postgres-style:

-- create the table as we want it to be
CREATE TABLE IF NOT EXISTS tab_name ... ;
-- if an older table version exists, update it
ALTER TABLE name
    DROP COLUMN IF NOT EXISTS col_name col,
    DROP INDEX IF EXISTS idx_name;

This feature is not compatible with Oracle MySQL, and MariaDB’s executable comments cannot help here. You may write two different upgrade scripts, one for MariaDB and one for Oracle MySQL… but this feature should simplify your work, not make it harder.

So you can just switch to MariaDB and leave Oracle MySQL. This is an option even for public software projects. MariaDB has more interesting features, it is more open, and is probably the future. Also, migrating to MariaDB is amazingly simple: in most cases, all you need to do is to install MariaDB. So, don’t be prisoner of Oracle just because MySQL is still the most widely used DBMS: things will change.

Enjoy!

How to find unused MariaDB/MySQL accounts

In MariaDB and Oracle MySQL, an account is a user_name@host combination, where host can be an ip address (v4 or v6) or a hostname. Also, the host part can be specified as a LIKE pattern (such as '161.58.%'). The account used by the connection determines the privileges for that connection. When a connection matches more than one account, only one is used, and privileges assigned to other accounts will be ignored.

It can happen that you think that a certain client uses an account, but it uses a different account. For this reason, in this post I’ll show the queries to get all defined accounts, plus the accounts that were not used since last server startup.

The second query will use performance_schema. Audit plugins and the General Log can be used to get more information, such as the accounts that are not used since 1 month. However, if performance_schema is activated in your server, these queries are useful to “debug” your accounts.

How to list all accounts:

MariaDB [(none)]> SELECT User, Host FROM mysql.user ORDER BY User;
+-----------+-------------+
| User      | Host        |
+-----------+-------------+
| arancia   | computadora |
| drupal    | hal         |
| drupal    | %           |
| genoveffa | computadora |
| genoveffa | hal         |
| mandarino | hal         |
| mandarino | computadora |
| root      | ::1         |
| root      | 127.0.0.1   |
| root      | n           |
| root      | localhost   |
| test      | 127.%       |
| wp        | %           |
| wp        | hal         |
+-----------+-------------+
14 rows in set (0.00 sec)

How to list accounts which never connected since last restart:

MariaDB [(none)]> SELECT DISTINCT m.User, m.Host FROM mysql.User m LEFT JOIN performance_schema.ACCOUNTS p ON m.User = p.USER AND p.HOST LIKE m.Host  WHERE p.USER IS NULL ORDER BY User;
+-----------+-------------+
| User      | Host        |
+-----------+-------------+
| arancia   | computadora |
| drupal    | hal         |
| genoveffa | hal         |
| genoveffa | computadora |
| mandarino | hal         |
| mandarino | computadora |
| root      | n           |
| root      | ::1         |
| root      | 127.0.0.1   |
| test      | 127.%       |
| wp        | hal         |
+-----------+-------------+
11 rows in set (0.01 sec)

Of course, if an account is unused, it is also possible that the user associated to that account never connected. To get a list of users which never connected since last startup:

MariaDB [(none)]> SELECT DISTINCT m.User FROM mysql.User m LEFT JOIN performance_schema.USERS p ON m.User = p.USER WHERE p.USER IS NULL ORDER BY User;
+-----------+
| User      |
+-----------+
| mandarino |
+-----------+
3 rows in set (0.00 sec)

This doesn’t always mean that the user is unused! An account which never connects could be the definer of some Views or Stored Programs, and may be used to check those programs privileges (SQL SECURITY DEFINER); in that case you don’t want to drop him. So, let’s see how to get a list of totally unused accounts (never connected since last restart and not used to check Stored Programs/Views privileges):

SELECT DISTINCT
	m.User, m.Host
	FROM mysql.User m
	LEFT JOIN performance_schema.ACCOUNTS p
	ON m.User = p.USER AND p.HOST LIKE m.Host
	LEFT JOIN information_schema.VIEWS is_v
	ON is_v.SECURITY_TYPE = 'DEFINER' AND is_v.DEFINER LIKE CONCAT(m.User, '@', m.Host)
	LEFT JOIN information_schema.ROUTINES is_r
	ON is_r.SECURITY_TYPE = 'DEFINER' AND is_r.DEFINER LIKE CONCAT(m.User, '@', m.Host)
	LEFT JOIN information_schema.EVENTS is_e
	ON is_e.DEFINER LIKE CONCAT(m.User, '@', m.Host)
	LEFT JOIN information_schema.TRIGGERS is_t
	ON is_t.DEFINER LIKE CONCAT(m.User, '@', m.Host)
	WHERE p.USER IS NULL
		AND is_v.DEFINER IS NULL
		AND is_r.DEFINER IS NULL
		AND is_e.DEFINER IS NULL
		AND is_t.DEFINER IS NULL
	ORDER BY User, Host;

Enjoy!

Stored Routines to easily work with the SQL_MODE

Versione italiana

Working with the SQL_MODE can be tricky, because it’s a command separated list involving long keywords. To be honest, I hate to rewrite the flags, trying to read an unreadable list, etc. Of course some string functions can help (Justin Swanhart suggests to use REPLACE() to make comma-separated lists readable).

I made a small set of Stored Routines wich allow me to easily show SQL_MODE, add a flag, drop a flag and check if a flag is set. These routines work with the GLOBAL SQL_MODE; if you don’t like this, simply replace “@@global.” with “@@session.” in the SQL file before installing.

You can download (or improve) my routines using this SQL_MODER GitHub repo.

And now, some examples.

Show currently selected flags:

MariaDB [(none)]> CALL _.sql_mode_show();
+----------------------------+
| FLAG                       |
+----------------------------+
| ERROR_FOR_DIVISION_BY_ZERO |
| NO_AUTO_CREATE_USER        |
| NO_ENGINE_SUBSTITUTION     |
| NO_ZERO_DATE               |
| NO_ZERO_IN_DATE            |
| ONLY_FULL_GROUP_BY         |
| STRICT_ALL_TABLES          |
| STRICT_TRANS_TABLES        |
+----------------------------+
8 rows in set (0.46 sec)

(this is the SQL_MODE I suggest to use)

Check wether a flag is set:

MariaDB [(none)]> SELECT _.sql_mode_is_set('HIGH_NOT_PRECEDENCE');
+------------------------------------------+
| _.sql_mode_is_set('HIGH_NOT_PRECEDENCE') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.38 sec)

Add one or more flags:

MariaDB [(none)]> CALL _.sql_mode_set('NO_UNSIGNED_SUBTRACTION,HIGH_NOT_PRECEDEN
CE');
Query OK, 0 rows affected (0.07 sec)

MariaDB [(none)]> CALL _.sql_mode_show();
+----------------------------+
| FLAG                       |
+----------------------------+
| ERROR_FOR_DIVISION_BY_ZERO |
| HIGH_NOT_PRECEDENCE        |
| NO_AUTO_CREATE_USER        |
| NO_ENGINE_SUBSTITUTION     |
| NO_UNSIGNED_SUBTRACTION    |
| NO_ZERO_DATE               |
| NO_ZERO_IN_DATE            |
| ONLY_FULL_GROUP_BY         |
| STRICT_ALL_TABLES          |
| STRICT_TRANS_TABLES        |
+----------------------------+
10 rows in set (0.13 sec)

Query OK, 0 rows affected (2.09 sec)

Drop a flag:

MariaDB [(none)]> CALL _.sql_mode_unset('HIGH_NOT_PRECEDENCE');
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT _.sql_mode_is_set('HIGH_NOT_PRECEDENCE');
+------------------------------------------+
| _.sql_mode_is_set('HIGH_NOT_PRECEDENCE') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)

If you mistyped the flag (or it wasn’t set):

MariaDB [(none)]> CALL _.sql_mode_unset('hello world');
ERROR 1644 (45000): Flag 'hello world' was not set

Enjoy!