Showing entries 1 to 10 of 228
10 Older Entries »
Displaying posts with tag: MySQL 5.6 (reset)
The story of MySQL Bug #86664

This is a story about why it's a good idea to test and verify the behavior of new software releases, even if the change log says that a particular bug was already fixed.
Background MySQL 5.6 and 5.7 both support the following CREATE USER syntax:

CREATE USER 'user'@'host'
IDENTIFIED BY 'password';

This syntax create a user with the default authentication plugin (mysql_native_password unless configured otherwise) and the password provided. The IDENTIFIED BY syntax is also supported for GRANT command.
Both major versions also support the following syntax:

CREATE USER 'user'@'host'
IDENTIFIED WITH mysql_native_password AS 'hash_string';

The password hash for "passw0rd" is "*74B1C21ACE0C2D6B0678A5E503D2A60E8F9651A3", you might then expect …

[Read more]
InnoDB Locks Analysis: Why is Blocking Query NULL and How To Find More Information About the Blocking Transaction?

This post was originally published on the MySQL Support Team Blog at on 14 April 2017.

Consider the scenario that you execute a query. You expect it to be fast – typically subsecond – but now it take not return until after 50 seconds (innodb_lock_wait_timeout seconds) and then it returns with an error:

mysql> UPDATE world.City SET Population = Population + 999 WHERE ID = 130;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

You continue to investigate the issue using the sys.innodb_lock_waits view or the underlying Information Schema tables (INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS).

Note: The above Information Schema tables with lock and lock waits information have been moved to the Performance Schema in 8.0 as the data_locks and data_lock_waits tables. The sys schema view however …

[Read more]
My two parallel replication talks at Percona Live Santa Clara 2017

Yes, another post about my talks at Percona Live Santa Clara: I obviously still have things to share.  This time, I will focus on my parallel replication talks by giving a short preview.

I have two parallel replication talks at Percona Live:

MySQL/MariaDB Parallel Replication: inventory, use cases and limitations (Wednesday talk) MySQL Parallel Replication: all the 5.7 (and some of the 8.0)

My talks at Percona Live Santa Clara 2017

In a previous post, I listed all the talks at Percona Live.  In this post, I will give more details about my talks.

As a reminder, the list of my talks is the following:

Monitoring without looking at MySQL (Thursday keynote) The two little bugs that almost brought down (Tuesday Lightning Talk) MySQL/MariaDB Parallel Replication: inventory, use cases and talks at Percona Live Santa Clara 2017

In a week, me and some colleagues will be in Santa Clara for Percona Live. is sponsoring the conference and we will be present at the Monday Evening Reception.  You do not need a tutorial pass to attend the dinner (even if it is on the tutorial day): any valid pass will do.  If you do not have your ticket yet, it is time to register (you can use the discount code “

Oracle MySQL and the funny replication breakage of Friday, January 13

In my previous post, I talked about a funny replication breakage that I experienced with MariaDB.  So what about different versions of MySQL... > SELECT version(); +------------+ | version() | +------------+ | 5.6.35-log | +------------+ 1 row in set (0.00 sec) > SELECT * FROM test_jfg; +----+--------+-------------+ | id | status

Performance of Inserts on Partitions – MySQL 5.6 v/s MySQL 5.7

Recently, I was discussing with one of my colleagues about how insert statement performs for MySQL partitioned tables. General prediction is that it should be slower than for non-partitioned tables, but how much that we didn’t know. So, I thought let’s test with different types of partitions (i.e range, list and hash) and also with different number of partitions and check how’s performance. As people says, MySQL 5.7 is must faster than old one, so I also tested partitions with it.

So, I took simple table with 3.2M records on Centos 6.7 VM (4 core with 2GB RAM) with default my.cnf settings and then created tables for range, list and hash partitioning with 5,25,50 and 100 partitions. i.e with 5 partition (range and list), the table structures were like

CREATE TABLE emp_range_5(
 id int,
 fname varchar (30),
 lname varchar (30),
 hired_date date not null,
 separated_date date not null,
 job_code int,
 store_id int
 ) …
[Read more]
Is MySQL X faster than MySQL Y? – Ask queryprofiler

When trying out new software there are many other questions you may ask and one of those is going to be the one above. The answer requires you to have built your software to capture and record low level database metrics and often the focus of application developers is slightly different: they focus on how … Continue reading Is MySQL X faster than MySQL Y? – Ask queryprofiler

Which accounts can access this data?

Knowing which privileges a given account has is easy – just issue SHOW GRANTS FOR user@host.  But what about when you need visibility into privileges from the other direction – which accounts can access specific data?  If you’re a DBA – or perform DBA duties, regardless of your title – you may have been asked this question.  It’s an important question to ask in an audit or compliance review – but it can be a difficult question to answer.    This post will walk through how to assess this, but if you’re impatient and need answers to this question immediately, jump to the end – there’s a simple shortcut.

Things to consider

There are a few things you’ll want to consider about the implementation of the MySQL privilege system as you try to sort out who has access to certain data.

Access type

MySQL can restrict privileges based on operations – somebody who has …

[Read more]
Practical P_S: Find Client JRE Version Using SQL

MySQL Connector/Java supports connection attributes since version 5.1.25.  This projects useful metadata about the client environment into the database, where MySQL administrators can query PERFORMANCE_SCHEMA tables to remotely survey application deployment environments.  One useful piece of information exposed is the version and vendor of the JVM in use by the client.  This very short blog demonstrates how to get this information from PERFORMANCE_SCHEMA.

The metadata including the Java runtime environment version and vendor can be found in PERFORMANCE_SCHEMA.SESSION_CONNECT_ATTRS table.  Here’s the full contents of that table for a single connection from Connector/Java:

mysql> SELECT *
    -> WHERE processlist_id = 31\G
*************************** 1. row ***************************
       ATTR_NAME: _runtime_version
      ATTR_VALUE: …
[Read more]
Showing entries 1 to 10 of 228
10 Older Entries »