I ran into an interesting issue today, while examining some EXPLAIN outputs, and wanted to share the findings, as some of this is undocumented.

Basically, you can start with a very simple InnoDB table – 2 INT columns, Primary Key (PK) on the 1st column, regular index on the 2nd:

CREATE TABLE `t1` (
  `id1` int(10) unsigned NOT NULL,
  `id2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`)
) ENGINE=InnoDB;

The query is:

SELECT id1 FROM t1;

This is a straight-forward query with no WHERE clause.

Given no WHERE clause, we know there will be a full table or index scan. Let’s look at EXPLAIN:

mysql> EXPLAIN SELECT id1 FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: id2
      key_len: 5
          ref: NULL
         rows: 1 …

Recently, I have worked on one of this case where customer wanted to convert master-slave to master-master replication. They wanted to write on one master and keep another master as a hotspare. Many people are using this kind of environment with MySQL replication.

I would like to explain here in very simple steps that how we can do that. You can also follow the same steps if you want to promote only slave as a master in master-slave replication. Sometimes, this process will be helpful if you have master-slave replication and you want to rebuild or upgrade both the servers one by one. In that case, you have to shift traffic from one server to the other, take down the first server, rebuild it and then reverse the process.

I would also like to mention here that  master-master (active-active) replication is quite risky and it can lead to data inconsistency between the servers if writes are enable on both masters at the same time. …

The Percona Live MySQL Conference and Expo 2014 runs April 1-4  at the Santa Clara Convention Center and Santa Clara Hyatt – but be sure to get there a day early for the inaugural Open Source Appreciation Day on March 31 – this event is free but because space is limited I suggest registering now to reserve your spot.

Interest is particularly high in OpenStack and its intersection with MySQL and Open Source Appreciation Day and the conference sessions and events reflect this. Last year, the Percona Live MySQL Conference included 4 talks with Trove (“Red Dwarf” at the time) as the …

Based in Luxembourg, AlSego is an Independent Software Vendor delivering Web, e-Commerce and financial applications including:

• A Rapid Application Development (RAD) and Business Process Oriented (BPO) Web Server Platform.
• E-payment and e-banking "Plug & Play" solutions.
• Archiving solutions for the banks' SWIFT data.

AlSego counts some of the largest banks in Luxembourg, Belgium and Switzerland among its clients and partners.

Challenges

• Supporting multiple database platforms for its products' master repositories generated a relatively high number of technical support incidents as end-users did not always master the various technologies involved. This in turn increased costs for both customers and AlSego, and negatively impacted the customer experience.
…

If you create a table using a numeric type like INT or BIGINT, you might have been surprised by the numbers that appear in the table's DESCRIBE:

mysql> CREATE TABLE test(a INT, b SMALLINT, c BIGINT);
Query OK, 0 rows affected (1.04 sec)
mysql> DESCRIBE test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | int(11)     | YES  |     | NULL    |       |
| b     | smallint(6) | YES  |     | NULL    |       |
| c     | bigint(20)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

I didn't put those values 11, 6, and 20 in there. Where did they come from and what are they? They're the columns' "Display Width"

Well, for an integer type (the value in parentheses called the display width of the field. This is …

One good thing about going to the MySQL UC is the fact that you will interact with many people and you benefit from social events in the evenings. In its heyday, I recall you get no more than 4 hours of sleep a night, because you’re busy with people for up to 20 hours a day. Meetings, drinks, the hallway track are also all very interesting. That’s the added value of going to an event besides just the learning.

Monday is open source appreciation day and I know there will be drinks planned on Monday evening (31.03) at least from the CentOS Dojo crew. Tuesday (01.04) brings on the welcome reception (4.30-6.30pm), while Wednesday is the …

I wrote previously about Percona Live Santa Clara 2014, and I want to bring to your attention something Percona has done that is very nice to open source communities: have an open source appreciation day.

Its before the conference (so on Monday), and you get a choice between the CentOS Dojo (great lineup there including many from Red Hat, Monty from MariaDB, and PeterZ from Percona) or the OpenStack Today (another great lineup …

Ah database security… the black sheep of topics and something you would really rather not have to deal with right?

I mean surely all the fanfare and paranoia is reserved for the neck beards with tinfoil hats whom live in their own D.I.Y Faraday cage … that must be it … it just has to be?

No, the hard reality is the world is not rose tinted and “they” are out to get you be it for fun or for profit; from defacements to theft compromising your applications, and more importantly your data is big business. For some these acts are nothing short of sheer entertainment for an otherwise boring evening. (I’ll be speaking about this topic next week in much more detail at the Percona Live MySQL Conference and Expo in Santa Clara, California. My session, “Security and why you need to review …

I’ll speak about MySQL 5.7 Performance & Benchmarks during the incoming Percona Live and will be happy to share with you all our latest finding, improvements, benchmark results, open issues and many other stuff keeping our brains in constant activity ) As you know, there is no a “silver bullet” solution for MySQL Performance tuning.. – only by a good understanding of what is going inside of MySQL and InnoDB you may configure your MySQL server in the most optimal way for your workloads. And this topic is endless, and progressing every month with new solutions and features – all parties are working hard here, and I’m very curious to hear from users during this Conference what are their biggest performance issues for today, and of course looking forward to discuss proposed changes from Percona, Facebook, …

As an update to the previous announcement from Feb 27, 2014 we would like to update you on the conferences which MySQL Community team is supporting with an attendance of MySQL technical staff and sometimes with Java, Oracle VM and Oracle Linux experts as well. You are invited to meet our engineers at any of the event below.  

EMEA 

• NEW! Open Source Days 2014, Bielsko, Poland, March 28-30, 2014
  • Our local MySQL colleague Marcin Szalowicz will attend this conference and represent MySQL there. Please come to hear his presentation on "MySQL Workbench …