This Log Buffer travels wide and deep to scour through the Internet to bring some of the most valuable and value-adding blog posts from Oracle, SQL Server and MySQL.

Oracle:

What is SQLcl ? SQLcl is a new command line interface like SQL*PLUS coming along with SQL Developper 4.1 Early Adopter. It’s a lightweight tool (only 11MB) developed by the SQL Developer team, which is fully compatible with Windows and Unix/Linux.  Also, you don’t need to install it so it’s totally portable.

Find Users with DBA Roles.

Virtual Compute Appliance 2.0.2 Released.

In case you are not …

1) To find slow queries

pt-query-digest /var/lib/mysql/cloud-server-01-slow.log > slowqueroutput.log

2) Between a particular time range

pt-query-digest –since ’2015-04-10 00:00:00′ –until ’2015-04-10 23:59:59′ /var/lib/mysql/cloud-server-01-slow.log > slowqueryrange.log

3) For a particular database

pt-query-digest –filter ‘$event->{db} && $event->{db} =~ /employee/’ /var/lib/mysql/cloud-server-01-slow.log > slowqueroutputemp.log

pt-query-digest –filter ‘$event->{db} && $event->{db} =~ /market/’ /var/lib/mysql/cloud-server-01-slow.log > slowqueroutputmkt.log

4) For a particular user

pt-query-digest –filter ‘$event->{user} && $event->{user} =~ /root/’ /var/lib/mysql/cloud-server-01-slow.log > slowqueryoutputuser.log …

Recently i encounter an uprising among PHP Developers that , they must write a script additionally checking the strength of MySQL user password at creation time.
So, just for note that there is ready to use plugin in MySQL named: validate_password.so

The file location is default in plugin_dir in Linux:

mysql> select @@plugin_dir;
+--------------------------+
| @@plugin_dir             |
+--------------------------+
| /usr/lib64/mysql/plugin/ |
+--------------------------+
1 row in set (0,00 sec)

List this directory:

[root@linuxsrv3 plugin]# ls
adt_null.so     auth_test_plugin.so  innodb_engine.so      mypluglib.so          qa_auth_server.so   validate_password.so
auth.so         daemon_example.ini   libdaemon_example.so  qa_auth_client.so     semisync_master.so
auth_socket.so  debug                libmemcached.so       qa_auth_interface.so  semisync_slave.so

As you see there is a …

On Tuesday  Steaphan Greene  announced that all currently-pushed changes have now been rebased onto the newly-released upstream MySQL-5.6.24.

The new branch at this point is available on github.

Our .deb and .rpm builds are available in the PSCE repo as well as being browsable here http://repo.psce.com/download/webscalesql/

Instructions for using the repo are available for Debian …

Galera Cluster For System Administrators, DBAs And DevOps

Galera Cluster for MySQL, MariaDB and Percona XtraDB involves more effort and resource to administer than standalone systems. If you would like to learn how to best deploy, monitor, manage and scale your database cluster(s), then this new online training course is for you!

The course is designed for system administrators & database administrators looking to gain more in depth expertise in the automation and management of Galera Clusters.

What: A one-day, instructor-led, Galera Cluster management training course

When: The …

Complimenting the expanded CREATE USER syntax introduced in MySQL Server 5.7.6 is more useful ALTER USER syntax.  Before MySQL Server 5.7.6, ALTER USER could only be used to expire a user’s password.  That’s pretty limited.  With changes made in MySQL Server 5.7.6, a better distinction is made between privilege-level attributes (those which are managed via GRANT and REVOKE statements) and account-level attributes (those managed using CREATE USER and ALTER USER statements).  MySQL has a long history of confusing these – for example, requiring a GRANT …

I use a Fedora 20 VM image to teach Oracle and MySQL technology. Last week, I expanded the Fedora VM image to support a full LAMP stack. This blog shows you how to install Ruby on Fedora and successfully generate the Rails gems.

Connect as the root user and use yum to install the libraries. My approach is by library or small groups. Naturally, you start with the ruby library.

yum install ruby

You will see the following:

Loaded plugins: langpacks, refresh-packagekit
mysql-connectors-community                                  | 2.5 kB  00:00     
mysql-tools-community                                       | 2.5 kB  00:00     
mysql56-community                                           | 2.5 kB  00:00     
pgdg93                                                      | 3.6 kB  00:00     
updates/20/x86_64/metalink …

This topic is not for all situations but in my situation it helps.
The problem is with Slave is lagging behind master in very large numbers and the relay logs are continously growing.

Slave is using:

Relay_Log_File: mysql-relay-bin.000031

But in folder where relay log resides, was created up to:

mysql-relay-bin.000135

And it is continously growing.
So what i decide to do, again step-by-step i edited my.cnf file as follows:

1. I have 13 database so give to slave paralel workers 13:
slave-parallel-workers = 13

2. Disabled sync_binlog:
sync_binlog = 0

3. Changed at_trx_commit from 1 to 0:
innodb_flush_log_at_trx_commit = 0

4. Gave log_at_timeout (only from 5.6.6>) to 10:
innodb_flush_log_at_timeout = 10

5. Disabled slow query log( or commented out)

So …

At Percona Live MySQL Conference 2015 next week I’ll be presenting on “Towards One MILLION queries per second” on 14th April at 4:50pm in Ballroom A.

This is the story of work I’ve been doing to get MySQL executing ONE MILLION SQL queries per second. It involves tales of MySQL, tales of the POWER8 Processor and a general amount of fun in extracting huge amounts of performance.

As I speak, I’m working on some even more impressive benchmark results! New hardware, new MySQL versions and really breaking news on MySQL scalability.

Recently, I was working on one of the issue related to locks and deadlocks with InnoDB tables and I found very interesting details about how InnoDB locks and deadlocks works with or without index for different Isolation levels.

Here, I would like to describe a small test case about how SELECT ..FOR UPDATE (with and without limit) behave with INSERT/UPDATE and with READ-COMMITED and REPEATABLE-READ Isolation levels. I’m creating a small table data_col with few records. Initially, this test case was written by Bill Karwin to explain details to customer, but here I have used a bit modified test case.

CREATE TABLE data_col (dataname VARCHAR(10), period INT, expires DATE, host VARCHAR(10));

INSERT INTO data_col VALUES (‘med1′, …