A long-ago-discussed and much-requested feature, "dynamic compound statements", is working at last.

It's been eleven years since the original discussion of dynamic compound statements for MySQL, as you can see by looking at the worklog task on the wayback machine. (As usual, you have to click the "high level architecture" box to see the meat of the specification.) The essential idea is that one can directly enter compound statements like BEGIN ... END and conditional statements like "IF ... END IF" and looping statements like "WHILE ... END WHILE" without needing a CREATE PROCEDURE or CREATE FUNCTION statement.

The advantages are that one can run conditional or complex sequences of statements without needing an EXECUTE privilege, or affecting the database metadata. This has been a popular feature request, as …

There are a few circumstances where one will not want to run with only MyISAM tables. In this case, it can be beneficial to completely disable InnoDB.

As InnoDB has become more prevalent, disabling it in MySQL requires a little more effort than before.

In MariaDB 10.0, you can still completely disable it as you have done in the past (just add the –skip-innodb option, specify default-storage-engine=MyISAM, and comment out other InnoDB options):

[mysqld]
skip-innodb
default-storage-engine=MyISAM

Alternatively, instead of –skip-innodb, you can instead use “innodb=OFF”:

[mysqld]
skip-innodb
default-storage-engine=MyISAM

In MySQL 5.6, the –skip-innodb option has been deprecated (though still currently works), and since InnoDB is the new “default” storage engine, you must set both “default-storage-engine” and the new “default-tmp-storage-engine” options to “MyISAM”. If you …

I know, what, MyISAM-only? Yes, it’s true, and it occurs more than you might think.

If you do run MyISAM-only, it’s common to have disabled InnoDB altogether (–skip-innodb) to avoid allocating any RAM to it, and to avoid unnecessary files required for backups, and so forth. (See this post if interested in disabling InnoDB in MySQL 5.6 and/or MariaDB 10.0.)

One improvement in MySQL 5.6 and MariaDB 10.0 is the addition of some system tables. The 2 that are in both are `innodb_table_stats` and `innodb_index_stats`, both of which have an engine type of InnoDB. (The 1 additional system table in MariaDB 10.0 is `gtid_slave_pos`, and the 3 additional system tables in MySQL 5.6 are `slave_master_info`, `slave_relay_log_info`, and `slave_worker_info`.)

The issue, with regards to running MyISAM-only, is that these new …

#DBHangOps 11/13/14 -- More Orchestrator, Dealing with logical corruption, and more!

Hello everybody!

Join in #DBHangOps this Thursday, November, 13, 2014 at 11:00am pacific (18:00 GMT), to participate in the discussion about:

• More uses of Orchestrator for MySQL
• Dealing with logical corruptions (a.k.a. a bug in my application changed data it shouldn't have...)
• Mixing data and metadata

You can check out the event page at https://plus.google.com/events/cuuu7aua9sg2q7k5krlvhgqgntg on Thursday to participate.

As always, you can still watch the #DBHangOps twitter search, the …

Oracle have just made availble the new MySQL Cluster 7.4.2 Development Milestone Release – it can be downloaded from the development release tab here. Note that this is not a GA release and so we wouldn’t recommend using it in production.

This is the second DMR for MySQL 7.4; the delta between this DMR and 7.4.1 can be viewed in the MySQL Cluster 7.4.2 Release Notes

There are three main focus areas for this DMR and the purpose of this post is to briefly introduce them:

• Active-Active (Multi-Master) Replication
• Performance
• Operational improvements (speeding up of restarts; enhanced …

The Perfect Server - Ubuntu 14.10 (nginx, BIND, Dovecot, ISPConfig 3)

This tutorial shows how to prepare an Ubuntu 14.10 (Utopic Unicorn) server (with nginx, BIND, Dovecot) for the installation of ISPConfig 3, and how to install ISPConfig 3. ISPConfig 3 is a webhosting control panel that allows you to configure the following services through a web browser: Apache or nginx web server, Postfix mail server, Courier or Dovecot IMAP/POP3 server, MySQL, BIND or MyDNS nameserver, PureFTPd, SpamAssassin, ClamAV, and many more. This setup covers nginx (instead of Apache), BIND (instead of MyDNS), and Dovecot (instead of Courier).

This Log Buffer Edition lists down some of valuable blog posts from across the arenas of Oracle, SQL Server, and MySQL.

Oracle:

OOW Recap: Industrial Innovation with IoT, enabled by Oracle Fusion Middleware and JD Edwards.

PeopleSoft HCM 9.2 Update Image 9 is Now Available! – Simple and Intuitive User Interface on Multiple Devices.

API Catalog (OAC) allows organizations to easily build a catalog of their APIs to provide visibility to those APIs for application development.

Oracle Announces …

November 7, 2014 By Severalnines

PrestaShop is a popular open source e-commerce software powering over 200,000 online stores, according to the company. We’ve seen a bit of interest into high availability PrestaShop setups, so this post will show you how to achieve that on multiple servers. Note that this setup not only caters for failures, but by load balancing traffic across multiple servers, it also allows the system to scale and handle more users.

This post is similar to our previous posts on web application scalability and high availability:

• Magento - nginx, MySQL Galera Cluster, OCFS2
• Drupal - Apache, MySQL Galera Cluster, csync2 with …

Can you use the binary logs to undo a DELETE? Maybe, if you're using ROW format logging. The difference between a delete and an insert event could be just one byte - the one that maps it as a DELETE_ROWS_EVENT or a WRITE_ROWS_EVENT. Let's try it.

I've already populated this table with a few rows:

CREATE TABLE `undo_test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`t` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`v` varchar(20) DEFAULT NULL,
`b` blob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Save a hash so we can see if the undo really works later:

mysql -e "SELECT * FROM test.undo_test" | md5sum > before.md5

Delete an unwanted row:

DELETE FROM undo_test;
Query OK, 1693 rows affected (0.14 sec)

Oops! Forgot the WHERE clause! And of course I don't have any recent …

Ubuntu 14.10 LAMP server tutorial with Apache 2, PHP 5 and MySQL (MariaDB)

LAMP is short for Linux, Apache, MySQL, PHP. This tutorial shows how you can install an Apache 2 webserver on an Ubuntu 14.10 server with PHP5 support (mod_php) and MySQL support. Additionally, I will install phpmyadmin to make MySQL administration easier. A LAMP setup is the perfect basis for CMS systems like Joomla, Wordpress or Drupal.