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!

Quickly shutdown MariaDB 10

Italian version

How do you normally shutdown a MariaDB 5.5 or MySQL server?

mysqladmin shutdown -uroot -p

But MariaDB 10 (beta quality, at the time of this writing) has a quick way to do the same:

C:\Documents and Settings\utente1>mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.4-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT 'I\'m alive!';
+------------+
| I'm alive! |
+------------+
| I'm alive! |
+------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT 'I\'m gone away';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061 "Unknown
 error")
ERROR: Can't connect to the server

unknown [(none)]> \q
Bye

Update: SHUTDOWN works in stored programs. This allows to write an event which turns the server off everyday at a certain time, or when a task is finished. This is safer than a cron job which kills it, and works on any system.

DELIMITER ||
CREATE EVENT `test`.`shutd`
    ON SCHEDULE
        EVERY 1 DAY
        STARTS '2014-01-01 20:00:00'
    COMMENT 'Shutdown Maria when the office closes'
DO BEGIN
    SHUTDOWN;
END;
||
DELIMITER ;

Enjoy!

STK/Unit 1.0 Release Candidate 1 released

Annuncio italiano

STK/Unit 1.0 Release Candidate 1 is out!

STK stands for SQL ToolKit. It’s a family of proects for MariaDB, MySQL and Percona Server. STK/Unit is the first STK project that has been publicly release; more tools will come in the next future. The long-term purpose of STK is making SQL programming much easier and reliable on MariaDB and her sisters.

STK/Unit is a Unit Test framework for MariaDB, entirely written in SQL and inspired by SimpleTest and JUnit. Test Cases and Test Suites written by the user can set a test environment and check that all operations work as expected. The results can be retrieved as a human-readable string, in HTML format, or examined in the tables they are stored in. Both developers and database administrators can benefit from such tests.

Errors in applications can be originated by errors in databases. STK/Unit is designed to mainly test active structures: Stored Routines, Triggers, integrity constraints and Views. But also Tables must use the correct datatypes, column sizes and character sets to be able to contain data from the Real World. And DBMS updates, new plugins or even configuration changes can break the complex, delicate logics of a relational database. But a good set of tests can show any problem as soon as it raises!

STK/Unit is still under development and is expanding the list of supported platform; currently, the folloing are supported:
* MariaDB 5.5 and 10.0 – work good
* MariaDB 5.3, 5.2, 5.1 – with documented minor problems
* MySQL 5.1 – using MyISAM place of Aria, with minor problems (undocumented)

Documentation and Downloads for STK/Unit and others STK tools to come, are avaible from here:
http://stk.wikidot.com/

The public Mailing List can be found here:
https://launchpad.net/~stk-discuss

The STK team encourage you to try STK/Unit in your databases, report any bugs you may find, ask for help in the list when needed, and let us know any comments. Your feedback is valuable for us!

The STK Team

What if root can’t CREATE or DROP a user?

The following situation is a bit confusing:

MariaDB [(none)]> CREATE USER 'obj_stk_sequence'@'localhost';
ERROR 1396 (HY000): Operation CREATE USER failed for 'obj_stk_sequence'@'localhost'
MariaDB [(none)]> drop user obj_stk_sequence;
ERROR 1396 (HY000): Operation DROP USER failed for 'obj_stk_sequence'@'%'
MariaDB [(none)]> select current_user;
+----------------+
| current_user   |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

Sure, if the user already exists you are unable to CREATE him, even if you’re root. And if he doesn’t exists, you can’t DROP him. But, WTF, it’s impossible that he EXISTS and NOT EXISTS at the same time!

Uhm. Maybe it’s possible… if some system tables are damaged. In fact, I’ve CHECKed all tables in `mysql` and retried, and everything was ok:

MariaDB [(none)]> create user obj_stk_sequence;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> drop user obj_stk_sequence;
Query OK, 0 rows affected (0.00 sec)

I’ve noticed that if the `mysql`.`proc` system table is corrupted, a clear error informs you about the problem. However, if you can’t CREATE and DROP Continue reading

Did you lose your MariaDB root password? (GNU/Linux)

Don’t even think to drastical solutions. If you can log into GNU/Linux as root, you can always recover MariaDB root password.

Did you never know the password?

Maybe you installed MariaDB, or you bought a new server, but you don’t know the root password. Don’t panic! It’s ok!

Probably there is no password. Well, this is false; MariaDB asks for a password, and you won’t be able to logon if the password is incorrect; but the password is an empty string. On the CLI, just press enter to access.

Change it: it is insecure. If I had to break into a MariaDB/MySQL installation as root, I would first try an empty password. Don’t let me break into your system so easily!

Ok, you lost the password

Ok, you lost it. This is not the simplest case, but… it’s simple!

1) Log into your GNU/Linux system as the user used by MySQL (usually ‘mysql’) or root.

2) Restart MariaDB with the grant tables disabled:
mysqld_safe --skip-grant-tables --skip-networking

mysqld_safe will shut down mysqld for you.
With --skip-grant-tables, no password is needed to logon.
This is unsafe, so until the password is reset MariaDB should not accept network connections (--skip-networking).

3) Logon with no password:
mysql -u root

4) Set your new password.

Exec these 2 SQL statements and exit the client:

-- change pwd
UPDATE `mysql`.`user`
	SET `Password` = PASSWORD('new_password')
	WHERE `User` = 'root';
-- tell the server to read the grant tables
FLUSH PRIVILEGES;
\quit

(replace ‘new_password’ with the new password)

5) Stop mysqld_safe and restart mysqld:

mysqladmin shutdown
/etc/init.d/mysql start

(depending from your system, you may need to replace ‘/etc/init.d‘ with the correct MySQL path)

6) Logoff from you system (because you are now root or someone very powerful).

See also:

Enjoy!