MySQL has unfortunate rules for dates with two-digit years, for the date range, for post-decimal digits, for timestamp defaults ... But other DBMSs can be equally bizarre

Two-digit years

Start with two-digit years. Summarizing from the manual:

If the two-digit value is <= 69, add 2000. Else add 1900.
For example, if it's 65, then it's 2065.

It's an arbitrary extra detail that users have to learn. But PostgreSQL has exactly the same rule. And three other DBMSs -- DB2, …

This week I’ve been writing based on my linux.conf.au 2014 talk, which you can watch the recording of. Also see Part 1, Part 2, Part 3, Part 4 and Part 5. My feed feel off Planet MySQL …

Hi there,

As I was mentioning below, PHPMyAdmin or MySQL WorkBench may not have this authentication module, so that’s why you are running into problems.

Have you tried using the clients I mentioned from a remote client?

For instance, by the time I was working on this I struggled with many MySQL Clients and that also include PHPMyAdmin tool because of the lack of this authentication plugin, so even SQLyog was having this problem but after contacting them they released a snapshot version which included that option (I’ve seen that most clients removed this option in older releases), and then finally worked.

Give me a shout if that works out for you, otherwise give me a shout here again and we can troubleshoot this.

Hi,

In the last part of the tutorial I mention that you need to use a client in order to connect as it requires that and connecting locally will not work. That’s why I mentioned having a SSH tunnel first and then it will make sense the GRANT statement for localhost, otherwise you will fail once it’s not supported.

Try from a remote client that I also mentioned at the end, so you can use a proper MySQL client that supports this kind of authentication.

In case you run into issues here post again and I can help you.

I’m frequently tracking semaphores waits, and if you’ve examined them before, it can be a little matching up the threads listed in the SEMAPHORES section with the transactions in the TRANSACTIONS section.

Semaphore waits are related to internal synchronization between threads in mysqld, and not directly to row locks or other items associated with user queries, so that’s why the SEMAPHORES section only reports the OS thread id.

Fortunately, the TRANSACTIONS sections also reports the OS thread handle, but in hex format.

Here is an example semaphore wait:

--Thread 1079654736 has waited at ibuf0ibuf.c line 3549
for 943.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x7f2a48830bf8 '&block->lock'
a writer (thread id 1079654736) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file buf0flu.c line 1318

So how do …

A while back I blogged about JSON parsing in MySQL using Common_schema. Given the number of page views that post continues to get I think it's worth posting an update about how I solve that problem today. I now use the json_extract() function from MySQL JSON UDFs, which is available at MySQL Labs. There are multiple warnings on the download page that these UDFs should not be used on a production site, so consider yourself warned. In my case I'm running these reports on a read-only reporting replica of my production DB, so if they crash my server it wouldn't have direct customer impact. I can live with the risk for now.

Why I made the switch

I made the switch because the common_schema …

One of the more popular support requests we get involves setting up the official Percona rsync repositories that can be used to set up local mirrors. I’m glad to announce that this was implemented recently. You can now access the rsync repository at:

rsync://rsync.percona.com/rsync/

Setting up the local mirror can be useful if you have a larger number of systems and you don’t want to download packages from the remote repository multiple times, it can also be useful if the original repository is slower or inaccessible.

Setting up the local repository

Create a folder on disk where the files will be downloaded. Make sure you have enough space for this, currently repository for CentOS 6 is almost 2GB.

[root@percona1]# mkdir -p /data/repo/percona/

After this use createrepo to create all the necessary repository …

Next Wednesday, Severalnines CTO Johan Andersson and I will co-present a webinar about ClusterControl, a cluster management tool created by Severalnines that can monitor Percona XtraDB Cluster. It provides DBAs with the right metrics to manage and optimize applications during development and production.

In the webinar, titled “Performance Monitoring and Troubleshooting of Percona XtraDB Cluster,” we will discuss ClusterControl in general, and then we will explain how to install ClusterControl 1.2.5 in agentless mode. This is a particularly interesting use-case for me because in this …

One of the new features of MySQL 5.6 is the optimizer trace. What does it do? Well, you know Explain, right? Just add Explain before a query and MySQL will print out how it would execute the query. The optimizer trace does that, except it gives you a little more background into to how it makes its decisions. Think of it as “It’s a Wonderful Life” for indexes – it shows you what the world would be like without that primary key or index.

If you ask, why did MySQL choose to run a full table scan vs. an index seek, the optimizer trace can help you get a better picture of the situation. The other big deal with the optimizer trace is that it’ll show you the relative costs of operations. Yeah, MySQL already tells you how many rows it’ll have to read, but now you know how much those cost given the access path (index seek, scan, etc.)

Example

Let’s jump in to see what it looks like. First, enable the optimizer trace …

I’m very excited to share that Ubuntu Bug #1256931 has just been updated. MySQL 5.6 will be installable from the universe archive in trusty, with 5.5 remaining in the main archive.

Trusty Tahr (14.04) is a long term release (LTS) for Ubuntu, with support until 2019. It’s due out in April.