Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 151 to 180 of 32937 Next 30 Older Entries
Using innodb_large_prefix to avoid ERROR 1071
+2 Vote Up -0Vote Down

If you've ever tried to add an index that includes a long varchar column to an InnoDB table in MySQL, you may have seen this error:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

The character limit depends on the character set you use. For example if you use latin1 then the largest column you can index is varchar(767), but if you use utf8 then the limit is varchar(255). There is also a separate 3072 byte limit per index. The 767 byte limit is per column, so you can include multiple columns (each 767 bytes or smaller) up to 3072 total bytes per index, but no column longer than 767 bytes. (MyISAM is a little different. It has a 1000 byte index length limit, but no separate column length limit within that).

One workaround for these limits is to

  [Read more...]
Early Bird Pricing for MySQL Central @ Oracle Open World Extended to August 1st
Employee +1 Vote Up -1Vote Down

Register before August 1st for early bird pricing!

Millions of organizations around the world trust MySQL to power their business-critical web, cloud, and embedded applications. Want to learn best practices to develop next-generation applications with MySQL? Joins us at MySQL Central @ OpenWorld.

Learn new skills
Share and network with the global MySQL community
Hear about new MySQL features directly from Oracle
Get insight on product roadmaps
Have fun

OSCON 2014: Wrap Up
+0 Vote Up -0Vote Down
Tue, 2014-07-29 19:36Marc Sherwood

Another OSCON has been wrapped up. While these year was slightly smaller than last year it was still an amazing event. The interesting part was that MySQL and MariaDB seemed to be bigger topics than in years gone by.

Now that I have been back in the office for a few days, I am getting caught up on my overloaded inbox, and have had some time to reflect on the event.

The first thing that comes to mind is that MariaDB adoption rate is growing rapidly, and we hear about more amazing deployments at each event we attend. This year we met with a number of people who have made the switch from MySQL to MariaDB for their corporate use, as well as a good number of hosting companies who have migrated to MariaDB. We are working on getting some of these stories ready to share so stay tuned for more on that!

We aim to have fun in the MariaDB booth - how else could we

  [Read more...]
Prevent MySQL downtime: Set max_user_connections
+2 Vote Up -0Vote Down

One of the common causes of downtime with MySQL is running out of connections. Have you ever seen this error? “ERROR 1040 (00000): Too many connections.” If you’re working with MySQL long enough you surely have. This is quite a nasty error as it might cause complete downtime… transient errors with successful transactions mixed with failing ones as well as only some processes stopping to run properly causing various kinds of effects if not monitored properly.

There are number of causes for running out of connections, the most common ones involving when the Web/App server is creating unexpectedly large numbers of connections due to a miss-configuration or some script/application leaking connections or creating too many connections in error.

The solution I see some people employ is just to increase

  [Read more...]
MySQL Cluster latest developments – webinar replay + Q&A
Employee +2 Vote Up -0Vote Down

I recently hosted hosting a webinar which explained what MySQL Clusrter is, what it can deliver and what the latest developments were. The “Discover the latest MySQL Cluster Developments” webinar is now available to view here (http://www.mysql.com/news-and-events/web-seminars/discover-the-latest-mysql-cluster-developments/" target="_blank). At the end of this article you’ll find a full transcript of the Q&A from the live session.


View this webinar to learn how MySQL Cluster 7.3, the latest GA release, enables developer agility by

  [Read more...]
Orchestrator 1.0.4 released
+1 Vote Up -0Vote Down

Outbrain's orchestrator Version 1.0.4 is released.

Quick links: Orchestrator Manual, FAQ, Downloads

What's new?


orchestrator now does a much better visualization of Master-Master replication:

  [Read more...]
PECL/mysqlnd_ms: how failed XA transactions get fixed
Employee +0 Vote Up -0Vote Down

XA transactions are an open standard for distributed transactions. A distributed or global transaction can spawn multiple databases. XA transactions come handy, for example, when data sets are sharded over multiple servers and a business transaction affects multiple shards. The free MySQL Fabric tools help to setup and manage a sharded MySQL cluster. The development version of PECL/mysqlnd_ms 1.6 helps with XA transactions. It abstracts SQL details and acts as a transaction manager. The PHP MySQL driver kicks in when things go wrong and XA transactions show their nasty side: blocked servers. Good news: this is a rare case. Bad news: a

  [Read more...]
MaxScale, from proxy to replication relay. Part 2, the slave side
+0 Vote Up -0Vote Down
Tue, 2014-07-29 10:00mriddoch

Part 1 of this blog post told the story of creating a binlog router for MaxScale that could connect to a MySQL Replication Master, download the binlog file from that master and store them locally on the MaxScale server. This post will concentrate on the other side of the router, the interaction with the MySQL slaves that will see MaxScale as the replication master.

Router Structure

In some ways the role of master for MaxScale is much closer to the more expected style of interaction that MaxScale was designed to deliver; a connection originates from a client to a MaxScale service. MaxScale then processes that requirement and returns a result back to the client. The most obvious difference is of course that the processing does not involve

  [Read more...]
Galera Cluster using GTID: MySQL vs. MariaDB
+0 Vote Up -0Vote Down

Using GTID to attach an asynchronous Slave sounds promising. Lets have a look at the two existing GTID implementations and their integration with Galera.


There is one GTID used by the cluster and every node increments the common seqno by itself. This works well as long all transactions are replicated by Galera (simplified InnoDB). Because Galera takes care of the Commit Order of the transactions on all nodes. So having identical GTID/seqno from the start there are no problems.

  node1> show global variables like 'gtid_binlog_pos';
  | Variable_name   | Value   |
  | gtid_binlog_pos | 0-1-504 |

  node2> show global variables like 'gtid_binlog_pos';
  [Read more...]
Replacing pt-slave-delay with MASTER_DELAY in MySQL 5.6
+1 Vote Up -0Vote Down

In the past I have used pt-slave-delay when I want to maintain an intentionally delayed replica. Now that I have upgraded to MySQL 5.6 I am switching over to use MASTER_DELAY, which is a built-in feature that does the same thing.

For example I can replace this:

``` pt-slave-delay --delay 7d --interval 1m --daemonize


With this:


The implementation is similar: the IO thread copies the events to the relay log as fast as normal, but the SQL thread only executes events older than the defined lag. The process to

  [Read more...]
Getting Started with the MariaDB HandlerSocket Plugin
+0 Vote Up -0Vote Down
Mon, 2014-07-28 13:52adonnison

About 4 years ago, the guys at DeNA created the HandlerSocket plugin for MySQL. In Yoshinori Matsunobu's blog, he benchmarks HandlerSocket as more than 7 times the throughput of using the standard libmysql, and nearly twice that of a memcache front end.

So what is HandlerSocket? It is a plugin that bypasses the SQL layer and therefore misses out on the overhead required to parse the SQL. This does mean that you don't have access to SQL statements, but it does provide CRUD (Create, Read, Update, Delete) operations that effectively gives you a NoSQL access to your database.

HandlerSocket, when started, creates a set of listener processes that wait for connections. Two

  [Read more...]
MariadB Galera: Attaching an asynchronous Slave using GTID
+0 Vote Up -0Vote Down

Galera the synchronous Master-Master replication is quite popular. It is used by Percona XtraDB Cluster, MariaDB Galera Cluster and even patched MySQL binaries exist. Quite often you want to add a slave to a Galera Cluster. This is going to work quite well. All you need is at least configure log_bin, log_slave_updates and server_id on the designated Masters and attach your Slave.


Even you can use traditional (non GTID) replication. Using non GTID replication is a hassle. As you need to search for the right offset on the new Master to attach your Slave on.

Using GTID promises to be easier. As you simply switch to the new Master and the replication framework finds the new position based on the GTiD automatically.

As a fact we have two GTID

  [Read more...]
What I learned while migrating a customer MySQL installation to Amazon RDS
+1 Vote Up -0Vote Down

Hi, I recently had the experience of assisting with a migration of a customer MySQL installation to Amazon RDS (Relational Database Service). Amazon RDS is a great platform for hosting your MySQL installation and offers the following list of pros and cons:

  • You can scale your CPU, IOPS, and storage space separately by using Amazon RDS. Otherwise you need to take downtime and upgrade physical components of a rack-mounted server.
  • Backups, software version patching, failure detection, and (some) recovery is automated with Amazon RDS.
  • You lose shell access to your DB instance
  • You lose SUPER privilege for regular users. Many SUPER-type statements and commands are provided for as a Stored Procedure.
  • It is easy to set up multiple read
  [Read more...]
Multisource Replication: How to resolve the schema name conflicts
+0 Vote Up -0Vote Down
Mon, 2014-07-28 10:22claudionanni

MariaDB 10.0 has introduced the functionality to replicate data from more than one Master server. This is what many have been waiting for, especially those who do business intelligence analysis, aggregation and reporting on data coming from different and sometimes related applications.

The way multi-source replication is implemented is extremely simple - for every Master you need what is called a 'connection'. Previously you just had one, the default. Each 'connection' points to a Master and it has, just like regular replication, two threads: IO Thread and SQL Thread. This means that if you have two different Masters using the same Schema name, the two 'connections' associated with the separate Masters would operate on the same Schema on the Slave (specifically the Slave SQL Thread of both connections would work in the same

  [Read more...]
Some MySQL security tips
+1 Vote Up -0Vote Down

This is a brief list of security tips for MySQL. It is by no means complete.

  • Follow the sudo example. Don't let all you DBAs and Ops have the password for the root account. Have each and every one of them have their own personal super-duper account, with their own personal and private password. This makes it so easy when someone leaves the company. No need to change passwords, just to remove the employee's account.
  • Block root. Either remove it completely or forbid it from logging in. Yes, there's a way hack in MySQL to have a valid account blocked from logging in. One way of making this happen is via common_schema's sql_accounts. Here's how to block root account using common_schema:
  [Read more...]
Prewarm your EBS backed EC2 MySQL slaves
+1 Vote Up -0Vote Down

This is the story of cold blocks and mismatched instances and how they will cause you pain and cost you money until you understand why.

Most of the clients that we support run on the Amazon cloud using either RDS or running MySQL on plain EC2 instances using (Provisioned IOPS) PIOPS EBS for data storage.

As expected the common architecture is running a master with one or more slaves handling the read traffic.

A common problem is that after the slaves are provisioned (normally created from an EBS snapshot) they lag badly due to slow IO performance.

Unfortunately what tends to be lost in the “speed of provisioning new resources” fetish is some limitations in terms of data persistence layer (EBS).

If you are using EBS and you have created the EBS volume from snapshot or created a new volume you have to pre-warm the EBS volume

  [Read more...]
What Privileges Does EXPLAIN Require in MySQL?
+0 Vote Up -0Vote Down

Here’s a simple question that I wasn’t able to solve with Google or the MySQL documentation (which is normally excellent). Perhaps it’s mentioned somewhere, but I can’t find it. Here’s the question:

What privileges must a user have to run EXPLAIN for a query?

Is there an EXPLAIN privilege? No, there’s not.

The answer turns out to be really simple: you need the same privileges that you’d need to execute the query itself. At least, as far as I know, that’s the case. I haven’t been able to find a counter-example, and from what I know of the MySQL query execution process, this makes perfect sense.

If you’re not familiar with how EXPLAIN works, it’s roughly like the following.

  [Read more...]
FromDual.en: FromDual: Tools for MySQL and Galera - Backup - Monitoring - Operations
+0 Vote Up -0Vote Down
Taxonomy upgrade extras: galeraBackupmanagermonitoringOperations

FromDual tools provide valuable additional functionality which facilitate and optimize daily operations of your MySQL databases. Since our last newsletter a lot of things have changed in the FromDual tools.

To the MySQL Environment (MyEnv) numerous improvements and suggestions of our customers were added. The most important changes were introduced to the MySQL Backup Manager (mysql_bman).

  [Read more...]
Repair Corrupted InnoDB Table with Corruption in Secondary Index
+2 Vote Up -0Vote Down

InnoDB provides no means to repair corrupted table space. Once a table got corrupt the only way to repair MySQL files is to start it with innodb_force_recovery={4,5,6} in hope that you can dump the table, so you can rebuild the table space from scratch. At least this is what the manual says. But let’s take a closer look at InnoDB corruption. In some case you can repair InnoDB table space much faster.

What InnoDB provides to repair tablespace corruption

InnoDB doesn’t let you repair the table space, but you can rebuild secondary indexes with ALTER TABLE DROP/ADD KEY. That may be very useful in case corruption malformed pages where secondary index is stored.

So, before starting a whole story with innodb_force_recovery,

  [Read more...]
HowTo: Using MySQL for Visual Studio in you first ASP.NET MVC Application with EF 6
Employee +0 Vote Up -0Vote Down
Last week it was released the RC version of MySQL for Visual Studio 1.2.2. In this blog post we'll be showing one of the new features in this release. And we will be doing a short demo about some of the first steps when starting to use MySQL with .NET applications.
MySQL Connector/Python v2.0.0 alpha
Employee +2 Vote Up -0Vote Down

A new major version of Connector/Python is available: v2.0.0 alpha has been been released and is available for download! As with any alpha-software, it’s probably not good to throw it in production just yet.

Our manual has the full change log but here’s an overview of most important changes for this relase.

Some incompatibilities

The world evolves, at least the software does, and Python is not different. I’m not as bold as the guys at Django who dropped support of Python v2.6 with the Django v1.7 release. I’m leaving it in because I’m nice.

  [Read more...]
Monitoring MySQL flow control in Percona XtraDB Cluster 5.6
+2 Vote Up -0Vote Down

Monitoring flow control in a Galera cluster is very important. If you do not, you will not understand why writes may sometimes be stalled. Percona XtraDB Cluster 5.6 provides 2 status variables for such monitoring: wsrep_flow_control_paused and wsrep_flow_control_paused_ns. Which one should you use?

What is flow control?

Flow control does not exist with regular MySQL replication, but only with Galera replication. It is simply the mechanism nodes are using when they are not able to keep up with the write load: to keep replication synchronous, the node that is starting to lag instructs the other nodes that writes should be paused for some time so it does not get too far behind.

If you are not familiar with this notion, you

  [Read more...]
Prepared Statement Samples
+2 Vote Up -0Vote Down

One of our most useful features is Top Queries, which allows users to examine various metrics for families of similar queries. Once a query family is selected, we can drill down into its query samples. Individual samples are shown on a scatterplot. Selecting individual samples will display its captured SQL, latency, errors, and more.

We collect query samples by decoding the MySQL protocol over TCP and extracting the query text. Not only that, we also capture errors, warnings, and more by inspecting traffic over the wire.

Until now, query samples excluded prepared statements. This is because prepared statements are not sent over the wire as strings of SQL text. Statement preparation and execution is quite different from regular query execution, but in the end we

  [Read more...]
Putting MySQL Fabric to Use: July 30 webinar
+1 Vote Up -0Vote Down

Martin and I have recently been blogging together about MySQL Fabric (http://www.mysql.com/products/enterprise/fabric.html) (in case you’ve missed this, you can find the first post of the series here), and on July 30th, we’re going to be presenting a webinar on this topic titled “Putting MySQL Fabric to Use.”

The focus of the webinar is to help you get started quickly on this technology, so

  [Read more...]
New webinar-on-demand: Geographically distributed multi-master MySQL clusters
+1 Vote Up -0Vote Down
Global data access can greatly expand the reach of your business. Continuent Tungsten multi-site multi-master (MSMM) solutions enable applications to accept write traffic in multiple locations across on-premises and cloud providers. This includes the following important real-world use cases: Improve performance for globally distributed users registering hardware devices by permitting updates
Docker: Containers for the Masses -- Getting terms straight
+1 Vote Up -0Vote Down

I was recently discussing container technologies with my team and was trying to explain the various container-related projects including LXC, libcontainer, Docker, Kubernetes, CoreOS, and how all these fit together.

I agreed that a blog post would be a good way to further clarify some terms. I wanted to continue my more in-depth example-laden blog posts, particularly about Ansible and Docker as well as the Ansible dynamic inventory plugin for Docker, though wanted to get my thoughts out before I forget (I have young children)!

This blog post is the latest in the series:

  [Read more...]
How MariaDB makes Stored Procedures usable
+4 Vote Up -0Vote Down

I already wrote how MariaDB makes the debug of stored procedures much easier via the SQL Error Log. But I recently found out that MariaDB provides a usable workaround for some big limitations of their procedural SQL.

First, SELECT is not the only SQL statement which returns a resultset. Other examples are DELETE RETURNING, CALL, SHOW, EXPLAIN and administrative commands like ANALYZE TABLE or CHECK TABLE. But these commands cannot be used in place of SELECT in the following contexts:

  • Subqueries, derived tables, JOINs,

  [Read more...]
DBaaS, OpenStack and Trove 101: Introduction to the basics
+0 Vote Up -0Vote Down

We’ll be publishing a series of posts on OpenStack and Trove over the next few weeks, diving into their usage and purpose. For readers who are already familiar with these technologies, there should be no doubt as to why we are incredibly excited about them, but for those who aren’t, consider this a small introduction to the basics and concepts.

What is Database as a Service (DBaaS)?
In a nutshell, DBaaS – as it is frequently referred to – is a loose moniker to the concept of providing a managed cloud-based database environment accessible by users, applications or developers. Its aim is to provide a full-fledged database environment, while minimizing the administrative turmoil and pains of managing the surrounding infrastructure.

Real life example: Imagine you are working

  [Read more...]
Connector/Python 2.0
+1 Vote Up -0Vote Down
Connector/Python 2.0 (2.0.0 alpha, published on Thursday, 24 Jul 2014)
MySQL Fabric – Part 1 – Installing
+2 Vote Up -0Vote Down
Send to Kindle

MySQL Fabric is a tool included on MySQL Utilities that helps you to manage your MySQL instances.
It works by basically adding a new layer between your application and MySQL instances, which can provide an easy way to use sharding and build a high available system.

For More information about what is MySQL Fabric, please follow the documentation.

To install our Fabric environment, we will have to configure 4 servers, I will use the follow names and IP on this tutorial:

fabric1 ( - fabric
mysql1 ( - mysql master
mysql2 ( - mysql slave
mysql3 ( - mysql slave

Note: I’m running CentOS

  [Read more...]
Previous 30 Newer Entries Showing entries 151 to 180 of 32937 Next 30 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.