In this blog post, we’ll look at some of the basic housekeeping steps for MySQL indexes.

We all know that indexes can be the difference between a high-performance database and a bad/slow/painful query ride. It’s a critical part that needs deserves some housekeeping once in a while. So, what should you check? In no particular order, here are some things to look at:

1. Unused indexes

With sys schema, is pretty easy to find unused indexes: use the schema_unused_indexes view.

mysql> select * from sys.schema_unused_indexes;
+---------------+-----------------+-------------+
| object_schema | object_name     | index_name  |
+---------------+-----------------+-------------+
| world         | City …

In this blog, I will provide answers to the Q & A for the MySQL Replication Troubleshooting webinar.

First, I want to thank everybody for attending the August 25 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: Hi Sveta. One question: how is it possible to get N previous events using the SHOW BINLOG EVENTS command? For example, the position is 999 and I want to analyze the previous five events. Is it possible?

A: Not, there is no such option. You cannot get the previous five events using

SHOW BINLOG EVENTS

. …

Disclaimer: this post takes into consideration that strict mode is enabled on the server

VARCHAR  and  CHAR  are used to store strings. VARCHAR stores varying length and CHAR always use the same exact size no matter the size of the string. For example, CHAR(4) will always store 4 bytes, whereas VARCHAR(4) will store up to 5 bytes. See documentation.

When we create a table like this one:

We put inside the parentheses the length of the field in characters for the VARCHAR field. However, the maximum size in bytes of the field will depend on the CHARSET and COLLATION of the table. You can also specify a different collation for a column.

For instance:

• latin1: 1 to 2 bytes per …

Dear MySQL users,

MySQL Connector/Net 7.0.5 is the second development release that expands cross-platform support to Linux and OS X when using Microsoft’s .NET Core framework. Now, .NET developers can use the X DevAPI with .NET Core and Entity Framework Core (EF Core) 1.0 to create server applications that run on Windows, Linux and OS X. We are very excited about this change and really look forward to your feedback on it!

MySQL Connector/Net 7.0.5 is also the fourth development release of MySQL Connector/Net to add support for the new X DevAPI.  The X DevAPI enables application developers to write code that combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see …

There is not yet an equivalent to GROUP_CONCAT that produces a JSON array. (There is in MySQL 8, but that's not GA yet.) Until then, you can hack it together with string functions:

SELECT * FROM t;
+------+--------+
| id   | data   |
+------+--------+
|    1 | First  |
|    2 | Second |
+------+--------+

SELECT CONCAT('[', GROUP_CONCAT(JSON_OBJECT('id', id, 'value', data) SEPARATOR ', '), ']') AS j FROM t;
+-------------------------------------------------------------+
| j                                                           |
+-------------------------------------------------------------+
| [{"id": 1, "value": "First"}, {"id": 2, "value": "Second"}] |
+-------------------------------------------------------------+

Or you can use all JSON functions but hack the grouping:

SELECT j FROM (
       SELECT
         @c := …

I’m happy to speak at Percona Live Europe Amsterdam 2016 again this year (just look at the awesome schedule). On my agenda:

• Monday Oct 3 Tutorial: Best Practices of MySQL High Availability
• Monday Oct 3 Tutorial: The Complete MariaDB Tutorial
• Wednesday Oct 5 Talk: Securing your MySQL/MariaDB data with my friend, Ronald Bradford.
• Wednesday Oct 5 Talk: …

During Oracle OpenWorld, on Tuesday, September 20th, at 7.00pm, the place to be will be at the MySQL Community Reception !
OpenWorld attendees, members of local MySQL user groups, MySQL users in the Bay Area – you’re all invited to Oracle’s MySQL Community Reception to meet the MySQL Team !

Mingle with your peers, run into old friends from around the MySQL Community and/or meet the MySQL engineers to discuss the new features of MySQL 8.0, discuss performance with DimitriK or Group Replication with the replication team !

And don’t forget to chat with the MySQL Oracle ACEs that will be present too.

You can already register to the event’s page!

We are looking forward to see you there.

We are pleased to announce the next development release of MySQL Connector/J 6.0 which supports both JDBC 4.2 API and the new X DevAPI.

MySQL Connector/J 6.0.4 can be downloaded from the official distribution channels MySQL Downloads (see the “Development Releases” tab) and The Central repository. MySQL Connector/J source is also available on GitHub.

As always, we recommend that you check the CHANGES file in the download archive and/or the release notes to be aware of …

.NET Core and MySQL Connector NET

It is exciting times!  Recently Microsoft released .NET Core 1.0, a cross platform implementation of the .NET Framework.  .NET Core works on Windows, Mac OS, and Linux and is fully open source!

We wanted to enable users of Connector/Net to be able to use .NET Core so we have been working hard for the past few months to make our provider fully .NET Core compatible.  Because .NET Core does not yet fully replace the traditional .NET Framework, we will continue to ensure our provider is compatible with both frameworks.

Along with these exciting changes, we are also including in our most recent release support for Entity Framwork 7.0  and Entity Framework Core 1.0.

In this article I will explain how to install and setup .NET Core and how to make a simple sample that connects and retrieve data in screen.

Setup

Further information about …

This post will tell you how to get MySQL passwords in plain text using the .mylogin.cnf file.

Since MySQL 5.6.6, it became possible to store MySQL credentials in an encrypted login path file named .mylogin.cnf, using the mysql_config_editor tool. This is better than in plain text anyway.

What if I need to read this password in plain text?

Perhaps because I didn’t save it? It might be that I don’t need it for long (as I can reset it), but it’s important that I get it.

Unfortunately (or intentionally),

mysql_config_editor

 doesn’t allow it.

[root@db01 ~]# cat /root/.mylogin.cnf
????uUd????ٞN??3k??ǘ);??Ѻ0
                         ?'?(??W.???Xܽ<'?C???ha?$
?? …