There are several approaches to count the occurrences of a substring inside a larger string in MySQL. Some people use replace() and length() (I would use char_length() instead) to do it. Others use stored functions. Recently I had to count the occurrences of a certain key across many rows of JSON. Instead of writing my own query using one of the approaches mentioned above I decided to use common_schema.

In the past I've discussed JSON parsing in MySQL using common_schema.

I posted the slides to my talk on SlideShare.  This talk includes high level information about Shard-Query, why it is needed, and the kind of schema it works well with.

You can also find a more technical information about Shard-Query and the how it works here:

As mentioned here and after talking to a few people we have created a meetup page, http://www.meetup.com/Madrid-MySQL-users-group/ and proposed the first meeting on Thursday, 4th July. If you are interested and in the area come along and say hello. It should be interesting to see a few others who work in the field.  If you can let us know you are coming so we have an idea of how much interest there is.

Read the original article at 3 Ways to Optimize for Paging in MySQL

Join 6100 others and follow Sean Hull on twitter @hullsean. Lots and lots of web applications need to page through information. From customer records, to the albums in your itunes collection. So as web developers and architects, it’s important that we do all this efficiently. Start by looking at how you’re fetching information from your [...]

For more articles like these go to Sean Hull's Scalable Startups

Timing is everything. I wrote about how MySQL man pages were silently relicensed away from the GPL. It was picked up by a lot of sites: Hacker News, Slashdot, LWN, and probably more. That led to a bug report in Debian (#712730) to complain that MySQL is no longer compliant with the Debian Free Software Guidelines (DFSG). That prompted Norvald Ryeng who’s active in Debian (thanks Oracle!) to file MySQL bug

Many application developers would know of New Relic. A SaaS performance and monitoring tool targeted towards your web application monitoring including PHP, Ruby, Java, .Net, Python and Node.

With the release today (June 19, 2013) of the New Relic Platform, custom monitoring of data stores including MySQL are now possible. Try it now free. This link will provide you a free standard account (no cost, no billing details necessary), that enables you to perform application monitoring, server monitoring, MySQL instance monitoring and monitoring of many other products via many plugins.

Over the next few posts I will be discussing some of the design decisions I made for this MySQL plugin. New

I just installed 5.7.1 on Windows and next up was to build it from source on Windows, so that’s what I did, and thought I’d share the steps.

** Prerequisites **

1. Install Microsoft Visual Studio (I installed VS 2010 Express – free)
2. Install cmake
3. Install bison (make sure no spaces in path)

** Build Instructions **

cd c:\mysql\mysql-5.7.1
mkdir bld
cd bld
cmake ..
cmake --build . --config relwithdebinfo --target package

And here is the full output for anyone who might be interested:

C:\Windows\system32>cd C:\mysql\mysql-5.7.1

C:\mysql\mysql-5.7.1>mkdir bld

C:\mysql\mysql-5.7.1>cd bld

C:\mysql\mysql-5.7.1\bld>cmake ..
-- Building for: Visual Studio 10
-- Running cmake version 2.8.11.1
-- Configuring with MAX_INDEXES = 64U
-- The C compiler identification is

Today I had intended to talk about my approach and thought process in configuring a fresh MySQL server running 5.6 with the InnoDB storage engine. However, it didn’t quite work out that way. I managed to get side-tracked by decision #0 - whether or not durability is important.

Introduction

By default, MySQL is configured to lie to you. That is that when you modify data there are no guarantees that your changes will be able to survive a catastrophic failure, or to put in simple terms: a power loss. The advantage of doing this, is MySQL is able to perform additional buffering and merging and consequently achieve better performance. However, the downside is also very clear: losing data is problematic, so you need to decide what is appropriate for your

As documented in the MySQL Reference Manual:

a UNIQUE index permits multiple NULL values for columns that can contain NULL.

There are many scenarios in which this behavior is desirable. For example imagine you create a user table and require an email address, but you want username to be optional. You make the username column nullable, but you also want to make sure all non-null values in the column are unique, so it makes sense to add a UNIQUE INDEX. If the UNIQUE INDEX didn't allow multiple NULL values then you would need to move the username column to a separate table in order to both maintain uniqueness and allow users without usernames.

But there are also plenty of scenarios where this behavior can cause

A fairy tale

Once upon a time I did an operating system upgrade, a minor one that should do no harm, but just get me up to date by fixing any bugs in the version I had been using. It seemed like a good idea.

All seemed to be fine. I use a package provided by an external vendor and not the one produced by the operating system provider as this vendor provides a newer version of the package and I need that. The vendor has to make his package fit in the os environment his package is built for and normally does a pretty good job.

I use automation to build my systems and when I built a new one some issues appeared. Related to the new version of the OS the provider had enhanced one of his packages and the installation pulled in new dependencies. The install of the external package I use then broke as it conflicted with the new dependency

This is a follow-up, troubleshooting article that goes hand-in-hand with my “Building MariaDB 5.1 on Windows Revisited” post, and covers some problems and their solutions that one might encounter when attempting to build MariaDB from source code on Microsoft Windows.

The first error I ran into was this, on my main compile step (i.e., the 2nd cmake command):

C:\Program Files (x86)\MSBuild\Microsoft.Cpp\v4.0\Microsoft.Cpp
Common.targets(151,5): error MSB6006: "cmd.exe" exited with code 1.
[C:\mysql\mariadb-5.1.67\bld\scripts\GenFixPrivs.vcxproj]

That in itself doesn’t tell us too much. Searching the full compile output, we find:

Generating ../../scripts/mysql_fix_privilege_tables.sql
The system cannot find the file specified.
C:\Program Files (x86)\MSBuild\Microsoft.Cpp\v4.0\Microsoft.Cpp
Common.targets(151,5):

I previously wrote an article on Building MariaDB 5.1 on Windows, but it was about 1.5 years ago (for 5.1.60; this is for 5.1.67), so I wanted to update this a little since I’ve built it entirely from the command-line this time (as well as highlight a couple problems/solutions – which I’ll cover in a subsequent troubleshooting post). This process it quite similar to my steps for building MariaDB 5.5 and MariaDB 10.0, however it’s slightly different since 5.1 requires you run the configure.js script before running cmake. Aside from that though, things are mostly the same.

Here is the full procedure:

Download 5.1.67

I wanted install MySQL 5.7.1 (1st Milestone Release) on Windows 7 and test it out a bit, so I did, and since things didn’t go as smooth as expected, I thought I’d share my experience, in case anyone else runs into the same issues.

I downloaded the .msi (mysql-5.7.1-m11-winx64.msi) from http://dev.mysql.com/downloads/mysql/ (then click the “Developmental Releases” tab) and installed it following the prompts. That seemed to complete fine, which was great. However, that was it – and not in a good way. I mean, the “installer” basically only unpacked the files to a location.

I was expecting the “configuration” tool to run, but it didn’t. It was not installed, and not an option.

I quickly read through the 5.7.1 changelog and found the config tool is not part of the