One of the latest labs releases of Oracle MySQL brings multi source replication. This lifts the limitation found in earlier releases that a MySQL slave can only have one master. To be fair, there were other ways of doing this already: Using a time based switch as described in MySQL High Availability Using the multi source feature in the yet-to-be released MariaDB 10 Using Tungsten Replicator There are many good uses of multi source replication. You could use it to combine data from multiple shards or applications. If MySQL is used with a loadbalancer the most easy to build setup is a 2-way multi master. This makes it possible to use the InnoDB storage engine. Using MySQL Cluster is another alternative, but MySQL Cluster uses the NDB storage engine, and might not be a supported option for your application. A MySQL Cluster setup also needs at least 4 machines to be fully redundant and MySQL Multi Master only needs two machines. There is little intelligence required in the load...

There is a new feature in the MariaDB 10 Beta which caught my eye: support for returning a result set on delete . With a 'regular' DELETE operation you only get to know the number of affected rows. To get more info or actions you have to use a trigger or a foreign key. Anoter posibility is doing a SELECT and then a DELETE and with the correct transaction isolation a transactional support this will work. With the support for the RETURNING keyword this has become easier to do and it will probably bennefit performance and save you a few roundtrips and a few lines of code. There is already support for RETURNING in PostgreSQL. And PostgreSQL has an other nifty feature for which RETURNING really helps: CTE or common table expressions or the WITH keyword. I really hope to see CTE support in MySQL or MariaDB some day. An example from RETURNING and CTE in PostgreSQL: demo=# select * from t1; id | name ----+------- 1 | test1 2 | test2 3 | test3 4 | test1 5 | test2 ...

The procedure for using the PAM authentication plugin as documented doesn't work flawlessly on Ubuntu. So here is how it works on Ubuntu (and probably also on other Debian based systems). Please note that the PAM authentication plugin is an enterprise feature. 1. Make sure the plugin is loaded This can be done by adding the following to the mysqld section of my.cnf (Don't forget to restart). You could also use INSTALL PLUGIN to load it without restart. plugin-load=authentication_pam.so 2.  Add a user which will use the plugin mysql> CREATE USER 'dveeden'@'localhost' IDENTIFIED WITH authentication_pam; Query OK, 0 rows affected (0.00 sec) 3. Add a pam config file for 'mysql': Create /etc/pam.d/mysql with the following contents: @include common-auth @include common-account @include common-session-noninteractive 4. Login with the user mysql -p --enable-cleartext-plugin 5. Verify if you're really connected as the correct user. mysql...

I was a bit wrong in my previous post . MySQL 5.6 does allow you to supply a fsp with CURRENT_TIMESTAMP (thanks Roy). mysql> SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(6); +---------------------+----------------------------+ | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP(6) | +---------------------+----------------------------+ | 2013-10-27 10:38:59 | 2013-10-27 10:38:59.182530 | +---------------------+----------------------------+ 1 row in set (0.00 sec) It however feels a bit weird to me as the CURRENT_TIMESTAMP is often used without () and doesn't look like a function. So when I tried to use a CURRENT_TIMESTAMP with a fsp of 6 it was not behaving how I expected it to be: mysql> CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6)); ERROR 1067 (42000): Invalid default value for 'ts' mysql> CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t1 VALUES(CURRENT_TIMESTAMP(6)); Query OK,...

MySQL 5.6 includes support for microsecode timestamp resolution , which is a great new feature. To get the current timestamp in MySQL 5.5 you could use NOW(), SYSDATE() or CURRENT_TIMESTAMP. mysql_5.5> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP; +---------------------+---------------------+---------------------+ | NOW() | SYSDATE() | CURRENT_TIMESTAMP | +---------------------+---------------------+---------------------+ | 2013-10-26 15:46:24 | 2013-10-26 15:46:24 | 2013-10-26 15:46:24 | +---------------------+---------------------+---------------------+ 1 row in set (0.01 sec) If we run the same statement in MySQL 5.6 the output is the same. This is great for compatibility, but what if we want those microsecond timestamps? mysql_5.6> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP; +---------------------+---------------------+---------------------+ | NOW() | SYSDATE() | CURRENT_TIMESTAMP | +---------------------+----------------...

Today when I was studying for the MySQL 5.6 exams. I was studying for these two items: Create and utilize table partitioning Obtain MySQL metadata from INFORMATION_SCHEMA tables The first step is to create a table, partition it with a hash. mysql> CREATE TABLE pfoo (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255)) -> PARTITION BY HASH(id) PARTITIONS 4; Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO pfoo(name) VALUES('test01'),('test02'),('test03'),('test04'), -> ('test05'),('test06'),('test07'),('test08'),('test09'),('test10'),('test11'); Query OK, 11 rows affected (0.00 sec) Records: 11 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM pfoo; +----+--------+ | id | name | +----+--------+ | 4 | test04 | | 8 | test08 | | 1 | test01 | | 5 | test05 | | 9 | test09 | | 2 | test02 | | 6 | test06 | | 10 | test10 | | 3 | test03 | | 7 | test07 | | 11...

I've registered for the MySQL 5.6 beta exams, just like many more on Planet MySQL have done. One of the topics for the MySQL 5.6 Developer exam is: "Write a basic .NET application that uses MySQL". I have used Connector/Net a few times the last few years, but the last time I looked at some code involving Connector/Net was when I took the MySQL 5 Developer exam, which was long ago. I'm using Linux on both my laptop and on servers for most projects. So I don't use or see many .NET code. So I need a short refresh. To use Connector/Net on Linux you will need to install Mono. The base for .NET is CIL, which exists in at least 4 versions: 1.0, 2.0 and 4.0. To compile C# code with mono you need the command which correspondends with the CIL version you are using. CIL 1.0: mcs CIL 2.0: gmcs CIL 4.0: dmcs For Ubuntu you'll need these packages: libmono-system-data4.0-cil libmysql-cil-dev mono-runtime mono-dmcs pkg-config So now we should be able...