InnoDB: running out of AUTO_INCREMENT values

Most InnoDB primary keys are built on integer columns with the AUTO_INCREMENT option (which is a very good practice for reasons that are outside of the purpose of this article). But we have to monitor that we are not going to run out of AUTO_INCREMENT value. If this happens, we will get errors like this:

ERROR 167 (22003): Out of range value for column 'a' at row 1

Obviously, when creating tables, we should use a type that is sufficiently big, and make it UNSIGNED to avoid wasting half of its space. But there are also some details about AUTO_INCREMENT that we should remember.

First, the values to monitor are not MAX(id), but they are the AUTO_INCREMENT column in information_schema TABLES. This column shows the next autoincr value.

A used value is lost. It doesn’t matter if you delete a row: its autoincr value will not be reused. This is why we should monitor the next values from information_schema: it is possible that we used all autoincr values, but MAX(id) is much lower.

Values are lost even for transactions that fail or are rolled back. This is the reason why MAX(id) is generally higher, sometimes much higher, than the next AUTO_INCREMENT value. This can happen especially if we have periodical bulk inserts inserting many rows in one transaction. If such operation fails, it will waste autoincr values. If it fails often, we could run out of values.

Easy trick

An easy trick could be use a command like this:

ALTER TABLE table_name AUTO_INCREMENT = 1000;

But this only works if all values in the table are lower than the specified AUTO_INCREMENT. This can be useful if a bulk insert failed and no new row was still added, but typically this only postpones a bit the problem.

Or again, we could change all id’s to fill all the holes, and then set AUTO_INCREMENT. This is possible if, for example, the server is not used by night, or during the week end. But in other cases, it is not a viable solution. Modifying a primary key is not a cheap operation for InnoDB, and most likely, we will have to do this with many rows. Also, we would need to lock the table to avoid that new rows (with high values) are inserted in the process.

So, what can we do?

If we have a write-heavy workload, lowering the autoincr values is not trivial, because new rows are being inserted any time. And changing an id for many rows can be a long operation. If we do it with one transaction to make things faster, it’s locking. But well, we can do something similar to what pt-online-schema-change does to alter a table without locks. Let’s see the procedure step by step.

For our example, we will assume the following trivial table:

CREATE TABLE employee
(
    id INT UNSIGNED AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
);

1)

First, let’s create a table with the same structure. The id’s in the new table will start from 1. And let’s add a column, which is a reference to the original table’s id.

CREATE TABLE tmp_employee LIKE employee;
ALTER TABLE tmp_employee ADD COLUMN orig_id INT UNSIGNED NULL;
ALTER TABLE tmp_employee ADD UNIQUE INDEX idx_orig_id (orig_id);

2)

Now, we need to add a trigger to the original table, to add the new rows in the new table:

DELIMITER ||

CREATE TRIGGER employee_ai
    AFTER INSERT
    ON employee
    FOR EACH ROW
BEGIN
    INSERT INTO tmp_employee (old_id, name) VALUES (NEW.id, NEW.name);
END ||

DELIMITER ;

Note that original table’s id and the new table’s old_id columns are kept in sync.
This is just an example. In the real world, I would also create triggers for DELETE and UPDATE. This triggers need a reference to the new table, that’s why old_id exists and is indexed.

What if the table already has triggers? Fortunately MySQL 5.7 and MariaDB 10.2 support multiple triggers per timing/event. With older versions, just modify them and add the queries you need.

3)

Copy rows from the original table to the new table. If the new table already has some rows (added by our INSERT trigger) we won’t try to copy it.

INSERT INTO tmp_employee (old_id, name)
    SELECT id, name
        FROM employee
        WHERE
            (SELECT COUNT(*) FROM tmp_employee) = 0
            OR id < (SELECT MIN(old_id) FROM tmp_employee);

We are still populating the old_id column for DELETE and UPDATE triggers.

We didn’t mention the new table’s primary key, so the values are automatically generated.

4)

Until now, I assume we didn’t cause any damage. But the next step is more dangerous. Before proceeding, verify that we didn’t do anything wrong and our transactions didn’t fail.

5)

If everything’s ok, let’s switch the tables:

RENAME TABLE
    employee TO old_employee,
    tmp_employee TO employee;

This operation is atomic, so no query is expected to fail.

6)

Drop the old table and the old_id column.

Enjoy!

Prepared Statements in MariaDB 10.2

Prepared statements are particularly useful in stored procedures. In fact, they are the only way to execute dynamic SQL, which means that, for example, the list of ORDER BY columns is in a variable. You can do this by composing an SQL string, as you do in other languages.

However, in MySQL and up to MariaDB 10.1, there are some annoying limitations:

  • Prepared statements exist at a connection level. Even if you declare them in a stored procedure, they are global. If you are writing a stored procedure which should be usable in many contexts (maybe it’s part of an open source library), there is a risk that you overwrite existing prepared statements. There is no way to get a list of existing prepared statements.
  • A prepared statement is prepared from a literal string or a user variable. A literal string cannot contain variables. A user variable exists at a session level, so again, there is a risk to overwrite something. Especially if you always call the variable @sql, because a lot of people tends to do so.
  • Prepared statements are verbose. You need to run 3 different statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) just to execute a dynamic SQL statement.

First, let me say that the first problem is not solved. I hope it will be solved in a future version, so it will be easier to distribute stored procedures libraries. After all, MariaDB 10.3 will have several improvements to stored procedures.

The second problem is solved in 10.2. You can pass any SQL expression (or, at least, all expressions I tested) to PREPARE. Including a local variable, which means that there will be no conflicts with variables declared by the user. For example:

DECLARE v_query TEXT DEFAULT 'SELECT COUNT(DISTINCT user) FROM mysql.user';
PREPARE stmt FROM v_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

But this example is verbose. It demonstrates what I stated above: you need at least 3 statements just to execute a dynamic query. But MariaDB 10.2 solves this problem introducing EXECUTE IMMEDIATE, which is basically a shortcut:

DECLARE v_query TEXT DEFAULT 'SELECT COUNT(DISTINCT user) FROM mysql.user';
EXECUTE IMMEDIATE v_query;

As you can see, we don’t need PREPARE or DEALLOCATE PREPARE if we use EXECUTE IMMEDIATE. This is nice to have in stored procedures, but is not useful outside of procedures, because the prepared statement is always reprepared, even if we executed it before.

Enjoy!

 

Thoughts on MaxScale new license

MaxScale has been open source until now, just like all MariaDB projects. But the 2.0 version is released under a new license called BSL, which basically makes the covered work non-free until the Change Date (in this case 2019-01-01), when the license will be converted to GPL.

Looks like open source friendly, after all. The license will be GPL, just be patient. And the code is available. Right?

No. Cmpletely wrong. For plenty of reasons.

Some reasons

It is a lock-in. No matter how many times Monty repeats that there is no lock-in, we have a brain. If you don’t allow anyone to fix bugs except for yourself, it is a lock-in. If you force your users to buy your support, they won’t buy your competitors support.

MariaDB business moves to a non-free product. Yes, 1.4 is free an this won’t change. And yes, when 3.0 will be out, 2.0 will be free. But why should they maintain a free version, if money comes from non-free versions? Monty says that open source religion doesn’t put bread on the table. I suppose that maintaining free branches also doesn’t put bread on the table.

I wasn’t able to find any official EOL date for any MaxScale version – if there is one, please comment below.

MariaDB moves innovation to the non-free world. New features are non-free. When they will be old, they will be free. Monty also stated that this is the correct way to make money for a lot of projects. And he seems to advice this model to start-ups that use his venture capital, OpenOcean. Suddenly, BSL seems to be the only way for projects to survive. Is he protecting others projects interests, or using them for his own marketing?

MariaDB accused Oracle several times. When Oracle implemented a couple features and only distributed them in a non-GPL edition (threadpool, PAM authentication), MariaDB told that they had the same features as open source. Which was great. Except that… now MySQL Router is open source, MaxScale 2.0 is not. Now Monty has several justifications for this. But I fail to understand why open core is evil and BSL is good.

I mentioned Monty too many times. Is this an attack against Monty? Definitely not, but all articles I could find express Monty’s opinion, not MariaDB Corporation or anyone else’s opinion. I cannot answer the silence.

What is the MariaDB Foundation?

MariaDB Corporation has the legal right to make MaxScale non-free. They own it. They sometimes call it MariaDB MaxScale. They can: they also own MariaDB trademark.

So, what’s the role of MariaDB Foundation?

They claim they safeguard MariaDB. They don’t mention the ecosystem, the community, or other tools. They don’t mention, of course, MaxScale. Which is quite strange: they claimed that their model is Apache Foundation, which supports an entire ecosystem in many ways, and owns the trademarks.

Also, the board of directors has 6 members. 3 are from MariaDB Foundation. In this situation, they cannot have an independent opinion on MariaDB Corporation actions.

A curious aspect is that they declare they follow Ubuntu Code of Conduct. Please read its last paragraph and drawn your own conclusions.

My position on MariaDB and MaxScale

I am still grateful to MariaDB Corporation for creating and maintaining MariaDB (and to some of their engineers for creating MySQL).

From a technical point of view, they have many interesting features that are not in MySQL. Some of them come from the community, for example the CONNECT engine and their implementation of encryption. And the reason is that MariaDB is very open to the community.

Which brings us to a less technical point of view: MariaDB openness. Their JIRA account allows us to see the bugs (including their current status…). You can also see who is working on what, when next versions will be released, and what they will have. The team is active on the mailing lists and IRC. The documentation is a wiki and the license is free.

I have been a MariaDB supporter for years. I wrote Mastering MariaDB and I am one of their Community Ambassadors chosen by Colin Charles (who recently left MariaDB). Will my position about MariaDB project change? I don’t know, it’s too early to answer. For sure, I won’t deny that its openness is amazing and should be a model for everyone. (And I hope this won’t change)

And my position about MaxScale has changed? Of course it did. I wouldn’t use it for personal projects. Of course I could provide support but, given the license change, it seems to me unlikely. There are free alternatives: ProxySQL, MySQL Router, HAProxy. PoxySQL is by far the most interesting, if you ask me.

My position has changed forever? The answer depends on another question: will MariaDB fix its big mistake? I have no logic reasons to be optimistic, but I still hope it will. In the past they have apparently been open to criticism. After a complain in this blog, they made MaxScale binaries freely available, and I wrote a thank you post. What I couldn’t know is that they were preparing to close MaxScale next versions.

 

MySQL/MariaDB cursors and temp tables

In MariaDB and MySQL, cursors create a temporary table.

Does this statement deserve a whole blog post? Apparently not. However, in some cases one does not expect a temporary table to be created:

  • SELECT ... FOR UPDATE: An exclusive lock is created, yes, but you still read data from a temporary table.
  • SELECT FROM a temporary table: you are reading from a temporary tables, yes, but an internal temporary table is created anyway.
  • Impossible WHERE and LIMIT 0.

A quick example:

CREATE TEMPORARY TABLE t
(
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);

CREATE PROCEDURE p()
BEGIN
        DECLARE c CURSOR FOR
                SELECT id FROM t WHERE 0 LIMIT 0 FOR UPDATE;
        OPEN c;
        CLOSE c;
END;

MySQL [test]> SHOW STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 31    |
+--------------------+-------+
1 row in set (0.00 sec)

MySQL [test]> CALL p();
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> SHOW STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 32    |
+--------------------+-------+
1 row in set (0.00 sec)

I am not complaining, and I don’t even know if this behavior can be changed. But one should certainly be aware of this behavior. For example, one could think that creating a temporary table one time and then loop on that table with cursors several times is an optimization – but that’s not the case.

Federico

MaxScale binaries are now available

UPDATE: Short story: ignore the next update and read the post. Long story: the original post was a mistake, as explained in the next update. But then, MariaDB released free MaxScale binaries and everything I wrote in the post is now correct.

UPDATE 2016-04-14: It seems that I was mistaken. MaxScale download page is a bit different from MariaDB Enterprise page, and does not explicitly require us to accept terms of use before download. But we accept those terms while creating an account.

So, MaxScale binaries cannot be used in production without paying for MariaDB enterprise. Thanks to the persons who commented this post and pointed my mistake. My apologies to my readers.

I won’t delete this post because I don’t want the comments to disappear, as they express opinions of some community members.

My jestarday’s post Comments on MaxScale binaries followed up a post from Percona’s blog. It had much more visits than any other post I wrote before. It was linked by Peter Zaitsev and Oli Senhauser on social networks. No, this is not a self-advertisement, I’m just saying that the problem I’ve talked about is considered important by the community.

Today, MaxScale binaries are available! Not because of me (obviously), but because MariaDB must have found out that the community badly wants those binaries.

MaxScale 1.4.1 was released today, and it is available from the Database Downloads page on MariaDB.com. You can click on MaxScale and then you can select the version (1.4.1, 1.3.0, 1.2.1) and the system (Debian, Ubuntu, RHEL/CentOS, SLES, both available as deb/rpm or tarball). Registration is required for download, but this is acceptable, as long as the binaries are freely available.

There are no restrictive terms of use. Here is how the copyright note starts:

This source code is distributed as part of MariaDB Corporation MaxScale. It is free
software: you can redistribute it and/or modify it under the terms of the
GNU General Public License as published by the Free Software Foundation,
version 2.

The only problem is the lack for a repository – but now that the binaries are freely available, I expect most Linux distros to provide their packages.

I downloaded the Ubuntu 14.04 version on my Mint machine, and everything worked as expected:

fede-mint-0 ~ # dpkg -i /home/federico/Downloads/maxscale-1.4.1-1.ubuntu_trusty.x86_64.deb 
Selecting previously unselected package maxscale.
(Reading database ... 184280 files and directories currently installed.)
Preparing to unpack .../maxscale-1.4.1-1.ubuntu_trusty.x86_64.deb ...
Unpacking maxscale (1.4.1) ...
Setting up maxscale (1.4.1) ...
Processing triggers for man-db (2.6.7.1-1ubuntu1) ...
Processing triggers for libc-bin (2.19-0ubuntu6.7) ...
fede-mint-0 ~ # maxadmin -uadmin -pmariadb
MaxScale> show servers
Server 0x1b7a310 (server1)
	Server:                              127.0.0.1
	Status:                              Auth Error, Down
	Protocol:                    MySQLBackend
	Port:                                3306
	Node Id:                     -1
	Master Id:                   -1
	Slave Ids:                   
	Repl Depth:                  -1
	Number of connections:               0
	Current no. of conns:                0
	Current no. of operations:   0

So, thanks MariaDB! I love software projects that listen to their community needs. This should be a lesson for another company – we all know who I am talking about.

Federico

Comments on MaxScale binaries

I’m writing this post after reading Downloading MariaDB MaxScale binaries, from Percona’s MySQL Performance Blog.

I was already aware about the problem: MaxScale is open source, but the binaries are not free. You can download them and use them for testing, but if you want to use them in production, you’ll need to buy MariaDB Enterprise.

Note that MaxScale Docker images seem to have the same problem. I’ve tried some of them, but all those I’ve tried were running MariaDB Enterprise binaries, so using them in production is illegal (unless you pay).

The alternative is… compiling MaxScale. I had problems in doing so and couldn’t solve those problems myself. From the MaxScale Google Group, I see that Justin Swanhart had the same problems… so I don’t feel particularly stupid for that.

After some questions on that group, 2 posts were published on MariaDB blog:

When I tried them, the first one worked for me, while the former didn’t.

But in any case, even if you are able to compile MaxScale on your Linux/BSD of choice, updates will be a problem. You will need to compile all next releases of MaxScale, which is simply not a viable solution for many companies.

This prevents MaxScale from being as widely used as it could be. The lack of free binaries is a problem. I understand their commercial choice – it is legit, but I don’t agree. First, because open source shouldn’t work in this way. Second, because the lack of free binaries could bring some customers to them, but… most probably, it simply prevents lots of people from trying MaxScale at all.

This would be negative for any software project, in my opinion. But I think that it is particularly negative for MaxScale. Why? Because it is amazingly versatile and writing a new module is amazingly simple. This combination of characteristics would make it greatly benefit from being widely adopted – people could write new modules and distribute them.

Please, MariaDB, reconsider this choice.

Federico

MariaDB/MySQL missing features: View comments

The COMMENT clause

All database components (and database themselves) should have a COMMENT clause in their CREATE/ALTER statements, and a *_COMMENT column in the information_schema. For example:

CREATE PROCEDURE do_nothing()
        COMMENT 'We''re lazy. Let''s do nothing!'
BEGIN
        DO NULL;
END;
SELECT ROUTINE_COMMENT FROM information_schema.ROUTINES;

In fact most database objects have those clauses in MySQL/MariaDB, but not all. Views are an exception.

Comments in code

MariaDB and MySQL have multiple syntaxes for comments. Including executable comments (commented code that is only executed on some MySQL/MariaDB versions).

One can use comments in stored procedures and triggers, and those codes are preserved:

CREATE PROCEDURE do_nothing()
BEGIN
        -- We're lazy. Let's do nothing!
        DO NULL;
END;

But, there are a couple problems:

  • This doesn’t work for views.
  • mysql client strips comments away, unless it’s started with --comments parameter. So, by default, procedures created with mysql have no comments.

So…

Views have no comment. No comments in metadata, no comments in code.

This prevents us to create self-documenting databases. Even if names are self-documenting, we may still need to add notes like “includes sold-out products”, or “very slow”.

Criticism makes us better

As a final note, let me say that this post is not an attack against MariaDB or MySQL. It is criticism, yes, because I like MariaDB (and MySQL). Criticism helps, keeps projects alive, encourages discussions.

To explain what I mean, I’ll show you a negative example. Recently I’ve attended a public talk from a LibreOffice Italia’s guy. It shown us a chart demonstrating that, according a generic “independent American study”, LibreOffice has no bug, while MS Office is full of bugs. The guy seems to think that software lint-like can automatically search for bugs. First I wondered how can LibreOffice survive with a community that is totally unable to produce criticism. Then I realized why it is the most buggy piece of software I’ve ever tried.

Hiding problems is the safest way to make those problems persist.

I’m happy that my favorite DBMS’s get the necessary amount of criticism.

Federico