Making The Numbers Table Useful

It’s not easy to find a solution to a very simple problem in MySQL: converting a comma separated list of values into rows. Oracle database users find gobs of tutorials on using REGEXP and CONNECT BY LEVEL to make this happen. MySQL doesn’t have that. So, use the numbers table from the previous post!

The transposing is made possible by (ab)using the SUBSTRING_INDEX function. I love this function. It is right up there with GROUP_CONCAT when mixing NULL and non-null strings.

I will be using comma separated values. You can use any delimiter character you like.

Bonus: This works with empty strings, strings with one value only, and empty delimiters (i.e. “my value,,previous is empty”). No extra code needed.

Making Magic Happen

SET @mycsv = …

Percona XtraDB Cluster (PXC) has become a popular option to provide high availability for MySQL servers. However many people are still having a hard time understanding what will happen to the cluster when one or several nodes leave the cluster (gracefully or ungracefully). This is what we will clarify in this post.

Nodes leaving gracefully

Let’s assume we have a 3-node cluster and all nodes have an equal weight, which is the default.

What happens if Node1 is gracefully stopped (service mysql stop)? When shutting down, Node1 will instruct the other nodes that it is leaving the cluster. We now have a 2-node cluster and the remaining members have 2/2 = 100% of the votes. The cluster keeps running normally.

What happens now if Node2 is gracefully stopped? Same thing, Node3 knows that Node2 is no longer part of the …

This Log Buffer Edition covers various blog posts from the last week regarding Oracle, SQL Server and MySQL.

Oracle:

• Merging Overlapping Date Ranges with MATCH_RECOGNIZE
• The latest version of Enterprise Manager, EM 12.1.0.5, has been announced!
• Kdump is the Linux kernel crash-dump mechanism. In the event of a server crash, Kdump creates a memory image (vmcore) that can help in determining the cause of the crash.
• …

Why You Care About Numbers

I have worked a bit with Oracle. As such, I hang out around Oracle developers. There seems to be a common complaint among our kind when it comes to MySQL–”There aren’t any sequences!”

It never really bothered me. I wasn’t always an Oracle guy, so I didn’t always have sequences. I’m the kind of person who likes to experiment and make things happen. It just so happens there is a nice tool to help with this perceived absence: the “numbers table.”

It is really easy to set up. And, regardless of your database background, I think you will grow to love your utility.

There are multiple ways to achieve the result you want. For me, the easiest way is to work with decimal numbers. Why? Because that’s how we think. That’s pretty much standard for humans. Yeah, I know. Geeks think hexadecimal. Let’s not go there.

Without further ado, here is how you can create …

Analyzing transactional data is becoming increasingly common, especially as the data sizes and complexity increase and transactional stores are no longer to keep pace with the ever-increasing storage. Although there are many techniques available for loading data, getting effective data in real-time into your data warehouse store is a more difficult problem. VMware Continuent provides

MySQL is getting native support for JSON.  This blog post will show you how to quickly get the MySQL server with these new features running on your Windows rig and how to write a small C# program in Visual Studio 2015 that stores a JSON document using the new native JSON data type.

Schema or Schemaless

The upcoming 5.7 version of MySQL introduces a ton of new features, some of which I am quite excited about—in particular the …

Download MariaDB 5.5.44

Release Notes Changelog What is MariaDB 5.5?

MariaDB APT and YUM Repository Configuration Generator

The MariaDB project is pleased to announce the immediate availability of MariaDB 5.5.44. This is a Stable (GA) release.

See the Release Notes and …

ERROR 1146 (42S02): Table ‘information_schema.tables’ doesn’t exist
Any idea how to do this on mysql server 4.x?

I think you’ve made an error in adding seconds and milliseconds:

> I have to add the 12ms to the 0.002, which will give us 0.122 sec.

12ms = 0.012s

0.002s ( =2ms)
+0.012s (=12ms)
——-
0.014s

that’s nearly 9 times less!

Percona Live is moving from London to Amsterdam this year and the event is also expanding to three full days. Percona Live Europe 2015, September 21-23, will be at the Mövenpick Hotel Amsterdam City Centre. The call for speakers and Super Saver registration are now open. Hurry though because the deadline for submitting a speaking proposal is June 21st and Super Saver registration ends July 5th!

This year’s conference will feature one day of tutorials and two days of keynote talks and breakout …