The latest from the MySQL community

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.

As described in the MySQL 5.7 manual here, we can now resize the buffer pool without restarting the mysqld process starting with MySQL 5.7.5.

You can now use the "SET GLOBAL innodb_buffer_pool_size = xxxx" command which causes a resizing job to begin in background. We can monitor the progress and state of the resizing work using the …

VividCortex’s SaaS backend is a service-oriented architecture, which means that in addition to our external APIs that our agents and web UI use, we also have internal APIs. Deploy a bunch of API servers across a cluster of machines, hook everything up and make all the parts talk to each other, and what do you have? You have a distributed system that’s hard to troubleshoot, that’s what.

One of the primary problems in this type of system is finding out what’s happening, right now. Not status counters or metric rates, but what requests are in-flight and what status they are in, and most especially what they’re waiting for. At VividCortex we have built several relatively simple pieces of plumbing to help us get that kind of visibility. It’s been out there a while but we’ve never spent much time to explain what we did and what the benefits are. …

I ran into this the other day, and while the solution is documented in the manual, it was kind of buried, so I thought I’d mention it here (and hopefully make it easier for others searching for this in the future).

In this specific instance, one was running a huge LOAD DATA (10 hours into it) and had to terminate the LOAD DATA command.

Terminating the command is one thing, but preventing InnoDB from trying to undo the rows that were inserted is another.

In this case, the LOAD DATA will easily re-create the table in question, so the existing table can be “thrown out”, so to speak. This, of course, is a *critical* requirement for this operation (i.e., that you can easily re-create the table – either from a load, backup, dump, etc.).

Per the manual:

“If you know that a given table is causing a crash on rollback, you can drop it. You can also use this to stop a runaway rollback caused by a failing …

You can find a ‘Quick Usage Tour’ in our documentation. In this post, I’m going to walk you through that tour and elaborate on a few things. Enjoy! Things to consider I’m assuming you’ve already installed MySQL. Sphinx does not require that you use MySQL, but the following examples do. I’m installing Sphinx on Ubuntu [...]

Recently while writing a data loading application for a customer, we’ve come into a problematic situation with the Python MySQLdb module that can be installed with base RHEL repository or DVD. As a little background, this application uses an HA architecture where a Virtual IP can be assigned to different servers during a failover scenario. However, during failover, as long as the VIPs are not re-assigned, the application could hang waiting for query response. A quick strace reveals something like:

sendto(5, "W\0\0\0\3SELECT * FROM dataloader.bo"..., 91, 0, NULL, 0) = 91
recvfrom(5,

Further test reveals that this problem will not timeout until after about 15 minutes which matches the default tcp_retries2 value on the system (see man 7 tcp):

[root@node1 ~]# cat /proc/sys/net/ipv4/tcp_retries2
15

It turns out that this version of MySQLdb module has client net read and write timeouts set to 0 allowing the TCP setting …

After some grueling IO testing on 7200rpm disks, I got my hands on some shiny new Samsung 840 SSDs and wanted to share the performance results in similar fashion.

Dell r630, E5-2630 v3 @ 2.40GHz (16 cores), 256GB RAM, Samsung 840 EV SSD 960GB.

Testing was sysbench, fileio-rndrw, async+direct io, 16 threads, 5.5TB RAID-6 using XFS mounted with noatime,inode64 and using the deadline scheduler.