Month: September 2016

Roles on MySQL 8.0

This is a blogpost about MySQL 8.0.0, the content here could be subject to changes in the future since this is not a stable release.

This is a feature that will make life the many DevOps and DBAs easier. Believe it or not, a lot of people control access to the database by sharing the same username and password. Which is completely insecure for a number of reasons:

  • If someone is fired, they still have access
  • If you get hacked for using the same password, well, I only can say: good luck

That also means: no more querying to Stack Overflow to get that giant GRANT statement for each user you need to create. (Thank you @mwop for reminding me of this).

Creating a Role

This is a group of privileges that will be assigned to users:

CREATE ROLE 'admin';

You can also create more than one role at once, however if one of them is already created, the whole statement fails and it won’t be created:

CREATE ROLE 'dba', 'developer', 'readonly';


mysql> CREATE ROLE 'dba', 'developer', 'readonly';
Query OK, 0 rows affected (0.01 sec)

If you try to run:

mysql> CREATE ROLE 'dba';
ERROR 1396 (HY000): Operation CREATE USER failed for 'dba'@'%'

It causes an error because the role already exists.

Granting privileges

Notice you didn’t state the privileges at this point, you need use GRANT to do so, and it works the same way as granting privileges to a user:

-- Grants access to all databases and tables to the role "dba"
GRANT ALL ON *.* TO 'dba';
-- Grants access to the database "app" to the role "readonly"
GRANT SELECT ON app.* TO 'readonly';
-- Grants access to the database "app" to the role "datawrite"
-- Grants access to developers

For each of the responses you will get something similar to this:

Query OK, 0 rows affected (0.00 sec)

Creating Users

In my example I will add Lisa Simpson as ‘dba’, Millhouse V. Houten as ‘developer’ and Bart Simpson and Homer Simpson as ‘readonly’.

So this is the proposed idea:

lisa_simpson dba
millhouse_houten developer
homer_simpson readonly
bart_simpson readonly

The generated SQL will be:

CREATE USER 'lisa_simpson'@'%' IDENTIFIED BY 'lisa_pwd';
CREATE USER 'millhouse_houten'@'localhost' IDENTIFIED BY 'millhouse_pwd';
CREATE USER 'homer_simpson'@'localhost' IDENTIFIED BY 'homer_pwd';
CREATE USER 'bart_simpson'@'localhost' IDENTIFIED BY 'bart_pwd';

After the user is created is time to tell which roles they are using, a role can have more than one user and a user can have more than one role. To attribute users to a role, do the following:

-- Attributes the users to existing roles
GRANT 'dba' TO 'lisa_simpson'@'%';
GRANT 'developer' TO 'millhouse_houten'@'localhost';
GRANT 'readonly' TO 'homer_simpson'@'localhost', 'bart_simpson'@'localhost';

Granting the roles doesn’t mean they will start automatically using them. Remember, a user can have multiple roles, so you need to tell the server which role the user is using:


This way, the default role for that user on that host will be the one used, as shown at the example below:

mysql> SHOW GRANTS FOR millhouse_houten@'localhost';
2 rows in set (0.00 sec)


| Grants for millhouse_houten@localhost                   |
| GRANT USAGE ON *.* TO `millhouse_houten`@`localhost`    |
| GRANT `developer`@`%` TO `millhouse_houten`@`localhost` |


This way is simpler to change user permissions in batch, not having to go user by user. If the user is using a different role for any reason, it must be informed before any SQL manipulation.

Source: MySQL 8.0.0 manual.


Previously I had use FLUSH PRIVILEGES when granting permissions to users, this however is not needed because I didn’t manipulate the table users using INSERT, UPDATE, DELETE. In this case I manipulated the creation and grating using the specific commands for it.

Thank you, Paul DuBois, for the reminder.

MySQL 8.0 (dev): what to look for

MySQL 8.0 (dev): what to look for

This is an unstable release, please don’t use in production.

It was rumored that the new MySQL version would be 8 and not as 5.8 as a lot of people thought, and it appears the rumors were true.

Below are some of the features that caught my eye at first glance:


Although password expiration was implemented 5.7, the newer version bring a set of collective privileges as a Role. No need to have to copy paste that massive GRANT command you had when creating new users.

UTF-8 as default Charset

This is not yet the default charset coming with the server, but utf8mb4 will be the main charset instead of latin1, and the default collation will change from latin1_swedish_ci to utf8mb4_800_ci_ai. The plan is to do that before General Availability.

Invisible Indexes

Giving an index already exists, you can make it active or inactive. It is a toggling feature which enables the debugging work to see if an index really can be dropped (if it is not being used). This is for the search only, on write operations the index is still maintained.

IPv6 and UUID Manipulation

MySQL do not support those fields natively, however, it is recommended to store those items with the VARBINARY(16) type. MySQL now provides functions to manipulate textual representations of IPv6/UUID and to use bit-wise operations, to test, extract or compare.

Having those function built in, you can use a generated column to index that data.


A more comprehensive list can be found at the MySQL Server Blog. It is worth the read.

™MySQL is a trademark of Oracle.

Varchar fields on MySQL 5.7

Disclaimer: this post takes into consideration that strict mode is enabled on the server

VARCHAR  and  CHAR  are used to store strings. VARCHAR stores varying length and CHAR always use the same exact size no matter the size of the string. For example, CHAR(4) will always store 4 bytes, whereas VARCHAR(4) will store up to 5 bytes. See documentation.

When we create a table like this one:

CREATE TABLE `blog`.`users` (
`name` VARCHAR(45) NOT NULL,
`email` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`));

We put inside the parentheses the length of the field in characters for the VARCHAR field. However, the maximum size in bytes of the field will depend on the CHARSET and COLLATION of the table. You can also specify a different collation for a column.

For instance:

  • latin1: 1 to 2 bytes per character.
  • utf8: 1 to 4 bytes per character.

Why this is important to know

The new Online DDL changes for VARCHAR fields are documented as follows:

The number of length bytes required by a VARCHAR column must remain the same. For VARCHAR values of 0 to 255, one length byte is required to encode the value. For VARCHAR values of 256 bytes or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR size from 0 to 255 bytes or increasing VARCHAR size from a value equal to or greater than 256 bytes. In-place ALTER TABLE does not support increasing VARCHAR size from less than 256 bytes to a value equal to or greater than 256 bytes. In this case, the number of required length bytes would change from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY) (…)

The section highlighted is true, however, a bit misleading, changes between VARCHAR(1) and VARCHAR(255) will only be INPLACE if you are using latin1 charset. If you are using utf8 for instance that range drops from VARCHAR(1) to VARCHAR(63). The reason behind this is because in worst case scenario that field with utf8 will count each character as 4 bytes, making VARCHAR(63) < 256 bytes  and VARCHAR(>63) >= 256 bytes.

More clarification on the Source Code for MySQL:

Copy a field part into an output buffer.
buff [out] output buffer
length output buffer size
type itMBR for geometry blobs, otherwise itRAW
This function makes a copy of field part of size equal to or
less than "length" parameter value.
For fields of string types (CHAR, VARCHAR, TEXT) the rest of buffer
is padded by zero byte.
For variable length character fields (i.e. UTF-8) the "length"
parameter means a number of output buffer bytes as if all field
characters have maximal possible size (mbmaxlen). In the other words,
"length" parameter is a number of characters multiplied by
Number of copied bytes (excluding padded zero bytes -- see above).
virtual size_t get_key_image(uchar *buff, size_t length, imagetype type)
get_image(buff, length, &my_charset_bin);
return length;
virtual void set_key_image(const uchar *buff, size_t length)
{ set_image(buff,length, &my_charset_bin); }
inline longlong val_int_offset(uint row_offset)
longlong tmp=val_int();
return tmp;
inline longlong val_int(const uchar *new_ptr)
uchar *old_ptr= ptr;
longlong return_value;
ptr= (uchar*) new_ptr;
return_value= val_int();
ptr= old_ptr;
return return_value;
inline String *val_str(String *str, const uchar *new_ptr)
uchar *old_ptr= ptr;
ptr= (uchar*) new_ptr;
ptr= old_ptr;
return str;
virtual bool send_binary(Protocol *protocol);
virtual bool send_text(Protocol *protocol);
virtual uchar *pack(uchar *to, const uchar *from,
uint max_length, bool low_byte_first);


Online DDL changes are supported, but you must pay attention to your field size in bytes. Which it doesn’t mean it is the size inside the parentheses or the character count.

I found this while meddling with some change in size fields and I didn’t think the documentation was clear enough in the highlighted example.