FromDual TechFeed (en)

Typically, on a single machine (be it a physical or a virtual one) only a single MySQL instance (process) is running. This is perfectly ok for all those situations where a single instance is sufficient, like for storing small amounts of data (RedHat using MySQL for postfix, KDE using it for akonadi, ...), as well as those where a dedicated machine per MySQL instance is appropriate (high CPU load, memory fully loaded, availability requirements).

But there are also those users who want to (or would like to) have multiple instances which would still fit into a single machine. Even among them, a single instance per machine is typical. For this, there are good reasons:

• MySQL comes with defaults for files (config file, error log, ...) and directories (data directory, binlogs, ...) which would cause conflicts between multiple instances (unless they are changed).
• The scripts coming with MySQL, especially the automated start/stop with machine reboot/shutdown, are written for a single instance only.
• Last but not least: The instructions. those in the manual as well as the many "How to setup ..." in the Web, cover a single instance only.

Because of this, users often either restrict themselves to a single instance, or they set up several virtual machines (or containers) holding a single instance each.

But that overhead (both in software and in labour) isn't necessary: There is a way out, supporting easy handling of multiple MySQL instances on a single machine directly, without containers or VMs. This is our "MyEnv" package, available for download here, licensed under the GPL.

What Does MyEnv Do?

MyEnv cares about two aspects which in combination provide easy use of multiple instances:

• It helps to configure multiple MySQL instances without overlap, so they won't collide with each other.
• It maintains separate environments, each to manage and access one specific instance.

Each environment contains the path to the binaries (so the instances can use different versions), the config file, the socket and port number, data directory, error log etc. The environment is specified by a name (choose a meaningful one!), and it is switched by using its name as a shell command. (MyEnv creates an alias for that.)

Administrative commands like "start" and "stop" will manage the instance of the current environment. MySQL client programs like "mysql" or "mysqldump" will access that instance.

MyEnv supports the autostart of instances at machine boot, configurable per instance - something which is impossible using only the tools of a MySQL distribution.

Of course, an instance started via MyEnv (either manually or via autostart) can be accessed by any other client program on the machine, or from any other machine in the network - all that is needed is the specification of the proper socket or network port.

Handling Multiple Binaries

In the previous section, I wrote the instances can use different versions. This is done by installing those different versions into different locations, controlled by MyEnv, and the directory with the binaries will become a component of the user's PATH variable, switched when the environment is switched. Obviously, this works only if the destination path of the installation can be controlled, which implies the tar.gz format - RPM or DEB packages have fixed destinations, so different versions would overwrite each other on installation.
But that is no severe limitation, as all MySQL versions are available in tar.gz format, and these are sufficiently generic to run on any reasonably current Linux distribution.
(Yes, that is something I forgot to mention: MyEnv is developed and tested on Linux only. You are welcome to try it on any other Unix platform, and we will gladly listen to your experiences and accept your contributions, but we do not actively pursue non-Linux platforms.)

This support for multiple versions makes MyEnv the perfect tool for application development: Using a single machine, you can let your application access the MySQL servers of different versions and can verify it works the way you want it to.

Similar, you can install binaries of MySQL (Oracle), Percona Server, or MariaDB, and verify your application is portable across them.

And the adventurous among us can use different binaries, from the same or different vendor(s), to test whether replication works across versions and/or vendors, all without the effort of installing a separate VM or container setup.

MyEnv and Galera Cluster Till now, I mentioned MySQL (and its variants), and many readers may associate that term with a traditional single instance. So I better state explicitly: Of course, such an instance can take part in replication, in any role: master, slave, or intermediate in multi-level replication.

But besides single instances and replication, there exists a different MySQL setup: Nodes combined to form a Galera Cluster. And again, let me state explicitly: Again of course, an instance controlled by MyEnv can be a node participating in a Galera Cluster.

Those readers who have experiance with Galera Cluster (or who have just read the documentation or blogs about it) know that to start the first node of a cluster a special command is needed, called "bootstrap" - a simple "start" will not do. So this command was also added to MyEnv, it can manage a Galera Cluster completely by its builtin commands.

RPM and DEB packages Above, I wrote that to install different versions you cannot use RPM or DEB packages. I did not write that MyEnv cannot use RPM or DEB - in fact it can, the absolute path names in these formats just limit this to a single version.

So you can install the RPM or DEB of your choice, disable its autostart, and then call MyEnv to create multiple instances. You will give them different names, specify different sockets and ports and use different data directories, but for all of them you will specify the same path "/usr". As a result, MyEnv will simply manage multiple instances of the same version.

You can configure them differently to test the consequences, or you can set them up to replicate among them - master and slave can run on the same machine. Of course, this will not give you the "high availability" or the "scale-out" benefits which are the typical reasons to use replication, but I trust this wasn't your purpose for this test.

Using binaries that include Galera, and configuring them properly, you can even run all nodes of a Galera Cluster as separate instances on a single machine. That may be considered to stretch the concept, because a single machine is a very different setup than separate machines, but it gives an idea of the possibilities opened by MyEnv.

Typical Use of MyEnv

Admitted: The claim to know what MyEnv is used for by others would be arrogant, and I do not uphold it. Nonetheless, we do know some use cases of people who downloaded MyEnv, and they are close to our internal use of the tool.

MyEnv allows to have multiple MySQL instances on the same machine, to manage them separately, and to access them using MySQL client programs or other applications. So it is the perfect setup for all those who need to access different versions: developers and software testers.

When we encounter some unexpected behaviour, we often want to know whether it is specific to some version or series, or is widespread. To check that, MyEnv is the perfect infrastructure: You write a test case to provoke the effect and run it on several versions, then you note the result and can tell whether it exists "since ages" or is new, whether it still occurs in current versions or will change with an upgrade - exactly the information you need to decide about an upgrade or write a bug report.

Database administrators and application developers use it to avoid nasty surprises with new versions, so their production instances will not suffer from unexpected functional changes. Setting up a test environment, especially for multiple versions, becomes cheap, much less ressources are needed. You don't need to copy your test code onto different machines, and you are sure you are running identical tests, so that you won't compare apples and oranges.

Invitation

If all that made you curious, I invite you to look into the instructions, to download MyEnv and to try it. And of course, your feedback and reports are very welcome.

Take care!

Appendix: Where to Meet Us

All FromDual colleagues will deliver talks at the FrOSCon in St. Augustin near Cologne, Germany, on August 20 and 21, so that is a good opportunity for personal contact. As several talks will be delivered in English, the conference also meets the needs of attendants who cannot follow a German talk - check the programme. Froscon is a famous event, very interesting talks are promised, and I look forward to enjoy the community atmosphere there.

I will deliver a talk at the "Open Source Backup Conference" in Cologne, Germany, on September 26 and 27; this conference is held in English.

I do not have feedback yet about Percona Live in Amsterdam, I may attend that also.

And finally, FromDual will again have a booth and deliver talks at the DOAG conference on November 15 - 18 in Nuremberg, Germany. This is "the" event for Oracle users (at least in Germany, maybe in all Europe), and it has a separate track dealing with MySQL only.

We will be delighted to meet you face to face!

Taxonomy upgrade extras: MyEnv

Typically, on a single machine (be it a physical or a virtual one) only a single MySQL instance (process) is running. This is perfectly ok for all those situations where a single instance is sufficient, like for storing small amounts of data (RedHat using MySQL for postfix, KDE using it for akonadi, ...), as well as those where a dedicated machine per MySQL instance is appropriate (high CPU load, memory fully loaded, availability requirements).

But there are also those users who want to (or would like to) have multiple instances which would still fit into a single machine. Even among them, a single instance per machine is typical. For this, there are good reasons:

• MySQL comes with defaults for files (config file, error log, ...) and directories (data directory, binlogs, ...) which would cause conflicts between multiple instances (unless they are changed).
• The scripts coming with MySQL, especially the automated start/stop with machine reboot/shutdown, are written for a single instance only.
• Last but not least: The instructions. those in the manual as well as the many "How to setup ..." in the Web, cover a single instance only.

Because of this, users often either restrict themselves to a single instance, or they set up several virtual machines (or containers) holding a single instance each.

But that overhead (both in software and in labour) isn't necessary: There is a way out, supporting easy handling of multiple MySQL instances on a single machine directly, without containers or VMs. This is our "MyEnv" package, available for download here, licensed under the GPL.

What Does MyEnv Do?

MyEnv cares about two aspects which in combination provide easy use of multiple instances:

• It helps to configure multiple MySQL instances without overlap, so they won't collide with each other.
• It maintains separate environments, each to manage and access one specific instance.

Each environment contains the path to the binaries (so the instances can use different versions), the config file, the socket and port number, data directory, error log etc. The environment is specified by a name (choose a meaningful one!), and it is switched by using its name as a shell command. (MyEnv creates an alias for that.)

Administrative commands like "start" and "stop" will manage the instance of the current environment. MySQL client programs like "mysql" or "mysqldump" will access that instance.

MyEnv supports the autostart of instances at machine boot, configurable per instance - something which is impossible using only the tools of a MySQL distribution.

Of course, an instance started via MyEnv (either manually or via autostart) can be accessed by any other client program on the machine, or from any other machine in the network - all that is needed is the specification of the proper socket or network port.

Handling Multiple Binaries

In the previous section, I wrote the instances can use different versions. This is done by installing those different versions into different locations, controlled by MyEnv, and the directory with the binaries will become a component of the user's PATH variable, switched when the environment is switched. Obviously, this works only if the destination path of the installation can be controlled, which implies the tar.gz format - RPM or DEB packages have fixed destinations, so different versions would overwrite each other on installation.
But that is no severe limitation, as all MySQL versions are available in tar.gz format, and these are sufficiently generic to run on any reasonably current Linux distribution.
(Yes, that is something I forgot to mention: MyEnv is developed and tested on Linux only. You are welcome to try it on any other Unix platform, and we will gladly listen to your experiences and accept your contributions, but we do not actively pursue non-Linux platforms.)

This support for multiple versions makes MyEnv the perfect tool for application development: Using a single machine, you can let your application access the MySQL servers of different versions and can verify it works the way you want it to.

Similar, you can install binaries of MySQL (Oracle), Percona Server, or MariaDB, and verify your application is portable across them.

And the adventurous among us can use different binaries, from the same or different vendor(s), to test whether replication works across versions and/or vendors, all without the effort of installing a separate VM or container setup.

MyEnv and Galera Cluster Till now, I mentioned MySQL (and its variants), and many readers may associate that term with a traditional single instance. So I better state explicitly: Of course, such an instance can take part in replication, in any role: master, slave, or intermediate in multi-level replication.

But besides single instances and replication, there exists a different MySQL setup: Nodes combined to form a Galera Cluster. And again, let me state explicitly: Again of course, an instance controlled by MyEnv can be a node participating in a Galera Cluster.

Those readers who have experiance with Galera Cluster (or who have just read the documentation or blogs about it) know that to start the first node of a cluster a special command is needed, called "bootstrap" - a simple "start" will not do. So this command was also added to MyEnv, it can manage a Galera Cluster completely by its builtin commands.

RPM and DEB packages Above, I wrote that to install different versions you cannot use RPM or DEB packages. I did not write that MyEnv cannot use RPM or DEB - in fact it can, the absolute path names in these formats just limit this to a single version.

So you can install the RPM or DEB of your choice, disable its autostart, and then call MyEnv to create multiple instances. You will give them different names, specify different sockets and ports and use different data directories, but for all of them you will specify the same path "/usr". As a result, MyEnv will simply manage multiple instances of the same version.

You can configure them differently to test the consequences, or you can set them up to replicate among them - master and slave can run on the same machine. Of course, this will not give you the "high availability" or the "scale-out" benefits which are the typical reasons to use replication, but I trust this wasn't your purpose for this test.

Using binaries that include Galera, and configuring them properly, you can even run all nodes of a Galera Cluster as separate instances on a single machine. That may be considered to stretch the concept, because a single machine is a very different setup than separate machines, but it gives an idea of the possibilities opened by MyEnv.

Typical Use of MyEnv

Admitted: The claim to know what MyEnv is used for by others would be arrogant, and I do not uphold it. Nonetheless, we do know some use cases of people who downloaded MyEnv, and they are close to our internal use of the tool.

MyEnv allows to have multiple MySQL instances on the same machine, to manage them separately, and to access them using MySQL client programs or other applications. So it is the perfect setup for all those who need to access different versions: developers and software testers.

When we encounter some unexpected behaviour, we often want to know whether it is specific to some version or series, or is widespread. To check that, MyEnv is the perfect infrastructure: You write a test case to provoke the effect and run it on several versions, then you note the result and can tell whether it exists "since ages" or is new, whether it still occurs in current versions or will change with an upgrade - exactly the information you need to decide about an upgrade or write a bug report.

Database administrators and application developers use it to avoid nasty surprises with new versions, so their production instances will not suffer from unexpected functional changes. Setting up a test environment, especially for multiple versions, becomes cheap, much less ressources are needed. You don't need to copy your test code onto different machines, and you are sure you are running identical tests, so that you won't compare apples and oranges.

Invitation

If all that made you curious, I invite you to look into the instructions, to download MyEnv and to try it. And of course, your feedback and reports are very welcome.

Take care!

Appendix: Where to Meet Us

All FromDual colleagues will deliver talks at the FrOSCon in St. Augustin near Cologne, Germany, on August 20 and 21, so that is a good opportunity for personal contact. As several talks will be delivered in English, the conference also meets the needs of attendants who cannot follow a German talk - check the programme. Froscon is a famous event, very interesting talks are promised, and I look forward to enjoy the community atmosphere there.

I will deliver a talk at the "Open Source Backup Conference" in Cologne, Germany, on September 26 and 27; this conference is held in English.

I do not have feedback yet about Percona Live in Amsterdam, I may attend that also.

And finally, FromDual will again have a booth and deliver talks at the DOAG conference on November 15 - 18 in Nuremberg, Germany. This is "the" event for Oracle users (at least in Germany, maybe in all Europe), and it has a separate track dealing with MySQL only.

We will be delighted to meet you face to face!

Taxonomy upgrade extras: MyEnv

Typically, on a single machine (be it a physical or a virtual one) only a single MySQL instance (process) is running. This is perfectly ok for all those situations where a single instance is sufficient, like for storing small amounts of data (RedHat using MySQL for postfix, KDE using it for akonadi, ...), as well as those where a dedicated machine per MySQL instance is appropriate (high CPU load, memory fully loaded, availability requirements).

But there are also those users who want to (or would like to) have multiple instances which would still fit into a single machine. Even among them, a single instance per machine is typical. For this, there are good reasons:

• MySQL comes with defaults for files (config file, error log, ...) and directories (data directory, binlogs, ...) which would cause conflicts between multiple instances (unless they are changed).
• The scripts coming with MySQL, especially the automated start/stop with machine reboot/shutdown, are written for a single instance only.
• Last but not least: The instructions. those in the manual as well as the many "How to setup ..." in the Web, cover a single instance only.

Because of this, users often either restrict themselves to a single instance, or they set up several virtual machines (or containers) holding a single instance each.

But that overhead (both in software and in labour) isn't necessary: There is a way out, supporting easy handling of multiple MySQL instances on a single machine directly, without containers or VMs. This is our "MyEnv" package, available for download here, licensed under the GPL.

What Does MyEnv Do?

MyEnv cares about two aspects which in combination provide easy use of multiple instances:

• It helps to configure multiple MySQL instances without overlap, so they won't collide with each other.
• It maintains separate environments, each to manage and access one specific instance.

Each environment contains the path to the binaries (so the instances can use different versions), the config file, the socket and port number, data directory, error log etc. The environment is specified by a name (choose a meaningful one!), and it is switched by using its name as a shell command. (MyEnv creates an alias for that.)

Administrative commands like "start" and "stop" will manage the instance of the current environment. MySQL client programs like "mysql" or "mysqldump" will access that instance.

MyEnv supports the autostart of instances at machine boot, configurable per instance - something which is impossible using only the tools of a MySQL distribution.

Of course, an instance started via MyEnv (either manually or via autostart) can be accessed by any other client program on the machine, or from any other machine in the network - all that is needed is the specification of the proper socket or network port.

Handling Multiple Binaries

In the previous section, I wrote the instances can use different versions. This is done by installing those different versions into different locations, controlled by MyEnv, and the directory with the binaries will become a component of the user's PATH variable, switched when the environment is switched. Obviously, this works only if the destination path of the installation can be controlled, which implies the tar.gz format - RPM or DEB packages have fixed destinations, so different versions would overwrite each other on installation.
But that is no severe limitation, as all MySQL versions are available in tar.gz format, and these are sufficiently generic to run on any reasonably current Linux distribution.
(Yes, that is something I forgot to mention: MyEnv is developed and tested on Linux only. You are welcome to try it on any other Unix platform, and we will gladly listen to your experiences and accept your contributions, but we do not actively pursue non-Linux platforms.)

This support for multiple versions makes MyEnv the perfect tool for application development: Using a single machine, you can let your application access the MySQL servers of different versions and can verify it works the way you want it to.

Similar, you can install binaries of MySQL (Oracle), Percona Server, or MariaDB, and verify your application is portable across them.

And the adventurous among us can use different binaries, from the same or different vendor(s), to test whether replication works across versions and/or vendors, all without the effort of installing a separate VM or container setup.

MyEnv and Galera Cluster Till now, I mentioned MySQL (and its variants), and many readers may associate that term with a traditional single instance. So I better state explicitly: Of course, such an instance can take part in replication, in any role: master, slave, or intermediate in multi-level replication.

But besides single instances and replication, there exists a different MySQL setup: Nodes combined to form a Galera Cluster. And again, let me state explicitly: Again of course, an instance controlled by MyEnv can be a node participating in a Galera Cluster.

Those readers who have experiance with Galera Cluster (or who have just read the documentation or blogs about it) know that to start the first node of a cluster a special command is needed, called "bootstrap" - a simple "start" will not do. So this command was also added to MyEnv, it can manage a Galera Cluster completely by its builtin commands.

RPM and DEB packages Above, I wrote that to install different versions you cannot use RPM or DEB packages. I did not write that MyEnv cannot use RPM or DEB - in fact it can, the absolute path names in these formats just limit this to a single version.

So you can install the RPM or DEB of your choice, disable its autostart, and then call MyEnv to create multiple instances. You will give them different names, specify different sockets and ports and use different data directories, but for all of them you will specify the same path "/usr". As a result, MyEnv will simply manage multiple instances of the same version.

You can configure them differently to test the consequences, or you can set them up to replicate among them - master and slave can run on the same machine. Of course, this will not give you the "high availability" or the "scale-out" benefits which are the typical reasons to use replication, but I trust this wasn't your purpose for this test.

Using binaries that include Galera, and configuring them properly, you can even run all nodes of a Galera Cluster as separate instances on a single machine. That may be considered to stretch the concept, because a single machine is a very different setup than separate machines, but it gives an idea of the possibilities opened by MyEnv.

Typical Use of MyEnv

Admitted: The claim to know what MyEnv is used for by others would be arrogant, and I do not uphold it. Nonetheless, we do know some use cases of people who downloaded MyEnv, and they are close to our internal use of the tool.

MyEnv allows to have multiple MySQL instances on the same machine, to manage them separately, and to access them using MySQL client programs or other applications. So it is the perfect setup for all those who need to access different versions: developers and software testers.

When we encounter some unexpected behaviour, we often want to know whether it is specific to some version or series, or is widespread. To check that, MyEnv is the perfect infrastructure: You write a test case to provoke the effect and run it on several versions, then you note the result and can tell whether it exists "since ages" or is new, whether it still occurs in current versions or will change with an upgrade - exactly the information you need to decide about an upgrade or write a bug report.

Database administrators and application developers use it to avoid nasty surprises with new versions, so their production instances will not suffer from unexpected functional changes. Setting up a test environment, especially for multiple versions, becomes cheap, much less ressources are needed. You don't need to copy your test code onto different machines, and you are sure you are running identical tests, so that you won't compare apples and oranges.

Invitation

If all that made you curious, I invite you to look into the instructions, to download MyEnv and to try it. And of course, your feedback and reports are very welcome.

Take care!

Appendix: Where to Meet Us

All FromDual colleagues will deliver talks at the FrOSCon in St. Augustin near Cologne, Germany, on August 20 and 21, so that is a good opportunity for personal contact. As several talks will be delivered in English, the conference also meets the needs of attendants who cannot follow a German talk - check the programme. Froscon is a famous event, very interesting talks are promised, and I look forward to enjoy the community atmosphere there.

I will deliver a talk at the "Open Source Backup Conference" in Cologne, Germany, on September 26 and 27; this conference is held in English.

I do not have feedback yet about Percona Live in Amsterdam, I may attend that also.

And finally, FromDual will again have a booth and deliver talks at the DOAG conference on November 15 - 18 in Nuremberg, Germany. This is "the" event for Oracle users (at least in Germany, maybe in all Europe), and it has a separate track dealing with MySQL only.

We will be delighted to meet you face to face!

When analysing MySQL configuration and status information at customers it is always interesting to see how the applications behave. This can partially be seen by the output of the SHOW GLOBAL STATUS command. See also Reading MySQL fingerprints.

Today we wanted to know where the high Com_create_table and the twice as high Com_drop_table is coming from. One suspect was TEMPORARY TABLES. But are real temporary tables counted as Com_create_table and Com_drop_table at all? This is what we want to find out today. The tested MySQL version is 5.7.11.

Caution: Different MySQL or MariaDB versions might behave differently!

Session 1 Global Session 2 CREATE TABLE t1 (id INT);
Query OK, 0 rows affected     Com_create_table +1
Opened_table_definitions +1 Com_create_table +1
Opened_table_definitions +1    CREATE TABLE t1 (id INT);
ERROR 1050 (42S01): Table 't1' already exists     Com_create_table +1
Open_table_definitions +1
Open_tables +1
Opened_table_definitions +1
Opened_tables +1 Com_create_table + 1
Open_table_definitions +1
Open_tables +1
Opened_table_definitions +1
Opened_tables +1    CREATE TABLE t1 (id INT);
ERROR 1050 (42S01): Table 't1' already exists     Com_create_table + 1 Com_create_table + 1    DROP TABLE t1;
Query OK, 0 rows affected     Com_drop_table +1
Open_table_definitions -1
Open_tables -1 Com_drop_table +1
Open_table_definitions -1
Open_tables -1    DROP TABLE t1;
ERROR 1051 (42S02): Unknown table 'test.t1'     Com_drop_table -1 Com_drop_table -1    CREATE TEMPORARY TABLE ttemp (id INT);
Query OK, 0 rows affected     Com_create_table +1
Opened_table_definitions +2
Opened_tables +1 Com_create_table +1
Opened_table_definitions +2
Opened_tables +1    CREATE TEMPORARY TABLE ttemp (id INT);
ERROR 1050 (42S01): Table 'ttemp' already exists     Com_create_table +1 Com_create_table +1    DROP TABLE ttemp;
Query OK, 0 rows affected     Com_drop_table +1 Com_drop_table +1    CREATE TEMPORARY TABLE ttemp (id int);
Query OK, 0 rows affected   CREATE TEMPORARY TABLE ttemp (id int);
Query OK, 0 rows affected Com_create_table +1
Opened_table_definitions +2
Opened_tables +1 Com_create_table +2
Opened_table_definitions +4
Opened_tables +2 Com_create_table +1
Opened_table_definitions +2
Opened_tables +1  DROP TABLE ttemp;
Query OK, 0 rows affected   DROP TABLE ttemp;
Query OK, 0 rows affected Com_drop_table +1 Com_drop_table +2 Com_drop_table +1
Conclusion

• A successful CREATE TABLE command opens and closes a table definition.
• A non successful CREATE TABLE command opens the table definition and the file handle of the previous table. So a faulty application can be quite expensive.
• A further non successful CREATE TABLE command has no other impact.
• A DROP TABLE command closes a table definition and the file handle.
• A CREATE TEMPORARY TABLE opens 2 table definitions and the file handle. Thus behaves different than CREATE TABLE
• But a faulty CREATE TEMPORARY TABLE seems to be much less intrusive.
• Open_table_definitions and Open_tables is always global, also in session context.

Taxonomy upgrade extras: statustemporary table

Recently I was working on a clients question and stumbled over an issue with replication and mixed character sets. The client asked, wether it is possible to replicate data to a table on a MySQL slave, where one column had a different character set, than the column in the same table on the master.

 

I set up two servers with identical table definitions and changed the character set on one column on the slave from latin1 to utf8.

 

Master:

CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(255) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Slave:

CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

So far no problem, I was able to start the replication and set off some INSERT statements with special characters (like ä, ö, ü, ...). But when I went to look for them in the slave's table, I could not find them.

 

"SHOW SLAVE STATUS", showed me this error:

Column 1 of table 'test.test' cannot be converted from type 'varchar(255)' to type 'varchar(255)'

 

You might ask yourself now: But the columns have the same type, what is the problem? What is not shown in the error is the fact, that there are two different character sets.

 

The log file is of no help either. It only shows the same error and tells you to fix it.

2016-05-26 15:51:06 9269 [ERROR] Slave SQL: Column 1 of table 'test.test' cannot be converted from type 'varchar(255)' to type 'varchar(255)', Error_code: 1677 2016-05-26 15:51:06 9269 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'valkyrie_mysqld35701_binlog.000050' position 120 2016-05-26 15:53:39 9269 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

 

Skipping the statement will not work, as the server will just fail again, when the next statement shows up.

 

For all those who are now running to change the character set: STOP!

Changing characters set of columns or tables containing data can be fatal when done incorrectly. MySQL offers a statement to convert tables and columns to the character set you wish to have.

 

To convert the entire table, you can write:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

 

To convert a single column, you can write:

ALTER TABLE tbl_name MODIFY latin1_column TEXT CHARACTER SET utf8;

 

More details can be found in the ALTER TABLE documentation of MySQL. (Converting character sets is at the end of the article.)

 

Just to be clear, this is no bug! MySQL replication was never intended to work with mixed character sets and it makes a lot of sense, that the replication is halted when differences are discovered. This test was only an experiment.

Taxonomy upgrade extras: replicationcharacter setutf8utf8mb4

Recently I was working on a clients question and stumbled over an issue with replication and mixed character sets. The client asked, wether it is possible to replicate data to a table on a MySQL slave, where one column had a different character set, than the column in the same table on the master.

 

I set up two servers with identical table definitions and changed the character set on one column on the slave from latin1 to utf8.

 

Master:

CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(255) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Slave:

CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

So far no problem, I was able to start the replication and set off some INSERT statements with special characters (like ä, ö, ü, ...). But when I went to look for them in the slave's table, I could not find them.

 

"SHOW SLAVE STATUS", showed me this error:

Column 1 of table 'test.test' cannot be converted from type 'varchar(255)' to type 'varchar(255)'

 

You might ask yourself now: But the columns have the same type, what is the problem? What is not shown in the error is the fact, that there are two different character sets.

 

The log file is of no help either. It only shows the same error and tells you to fix it.

2016-05-26 15:51:06 9269 [ERROR] Slave SQL: Column 1 of table 'test.test' cannot be converted from type 'varchar(255)' to type 'varchar(255)', Error_code: 1677 2016-05-26 15:51:06 9269 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'valkyrie_mysqld35701_binlog.000050' position 120 2016-05-26 15:53:39 9269 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

 

Skipping the statement will not work, as the server will just fail again, when the next statement shows up.

 

For all those who are now running to change the character set: STOP!

Changing characters set of columns or tables containing data can be fatal when done incorrectly. MySQL offers a statement to convert tables and columns to the character set you wish to have.

 

To convert the entire table, you can write:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

 

To convert a single column, you can write:

ALTER TABLE tbl_name MODIFY latin1_column TEXT CHARACTER SET utf8;

 

More details can be found in the ALTER TABLE documentation of MySQL. (Converting character sets is at the end of the article.)

 

Just to be clear, this is no bug! MySQL replication was never intended to work with mixed character sets and it makes a lot of sense, that the replication is halted when differences are discovered. This test was only an experiment.

Recently I was working on a clients question and stumbled over an issue with replication and mixed character sets. The client asked, wether it is possible to replicate data to a table on a MySQL slave, where one column had a different character set, than the column in the same table on the master.

 

I set up two servers with identical table definitions and changed the character set on one column on the slave from latin1 to utf8.

 

Master:

CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(255) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Slave:

CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

So far no problem, I was able to start the replication and set off some INSERT statements with special characters (like ä, ö, ü, ...). But when I went to look for them in the slave's table, I could not find them.

 

"SHOW SLAVE STATUS", showed me this error:

Column 1 of table 'test.test' cannot be converted from type 'varchar(255)' to type 'varchar(255)'

 

You might ask yourself now: But the columns have the same type, what is the problem? What is not shown in the error is the fact, that there are two different character sets.

 

The log file is of no help either. It only shows the same error and tells you to fix it.

2016-05-26 15:51:06 9269 [ERROR] Slave SQL: Column 1 of table 'test.test' cannot be converted from type 'varchar(255)' to type 'varchar(255)', Error_code: 1677 2016-05-26 15:51:06 9269 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'valkyrie_mysqld35701_binlog.000050' position 120 2016-05-26 15:53:39 9269 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)

 

Skipping the statement will not work, as the server will just fail again, when the next statement shows up.

 

For all those who are now running to change the character set: STOP!

Changing characters set of columns or tables containing data can be fatal when done incorrectly. MySQL offers a statement to convert tables and columns to the character set you wish to have.

 

To convert the entire table, you can write:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

 

To convert a single column, you can write:

ALTER TABLE tbl_name MODIFY latin1_column TEXT CHARACTER SET utf8;

 

More details can be found in the ALTER TABLE documentation of MySQL. (Converting character sets is at the end of the article.)

 

Just to be clear, this is no bug! MySQL replication was never intended to work with mixed character sets and it makes a lot of sense, that the replication is halted when differences are discovered. This test was only an experiment.

This week I was preparing the exercises for our MySQL/MariaDB for Beginners training. One of the exercises of the training is about MySQL spatial (GIS) features. I always tell customers: "With these features you can answer questions like: Give me all points of interest around me!"

Now I wanted to try out how it really works and if it is that easy at all...

To get myself an idea of what I want to do I did a little sketch first:

   My position   Shops   Restaurants   Cafes

To do this I needed a table and some data:

CREATE TABLE poi ( id INT UNSIGNED NOT NULL AUTO_INCREMENT , name VARCHAR(40) , type VARCHAR(20) , sub_type VARCHAR(20) , pt POINT NOT NULL , PRIMARY KEY (id) , SPATIAL INDEX(pt) ) ENGINE=InnoDB; INSERT INTO poi (name, type, sub_type, pt) VALUES ('Shop 1', 'Shop', 'Cloth', Point(2,2)) , ('Cafe 1', 'Cafe', '', Point(11,2)) , ('Shop 2', 'Shop', 'Cloth', Point(5,4)) , ('Restaurant 1', 'Restaurant', 'Portugies', Point(8,7)) , ('Cafe 2', 'Cafe', '', Point(3,9)) , ('Shop 3', 'Shop', 'Hardware', Point(11,9)) ;

This looks as follows:

SELECT id, CONCAT(ST_X(pt), '/', ST_Y(pt)) AS "X/Y", name, type, sub_type FROM poi; +----+-----------+--------------+------------+-----------+ | id | X/Y | name | type | sub_type | +----+-----------+--------------+------------+-----------+ | 1 | 2/2 | Shop 1 | Shop | Cloth | | 2 | 11/2 | Cafe 1 | Cafe | | | 3 | 5/4 | Shop 2 | Shop | Cloth | | 4 | 8/7 | Restaurant 1 | Restaurant | Portugies | | 5 | 3/9 | Cafe 2 | Cafe | | | 6 | 11/9 | Shop 3 | Shop | Hardware | +----+-----------+--------------+------------+-----------+

Now the question: "Give me all shops in a distance of 4.5 units around me":

SET @hereami = POINT(9,4); SELECT id, ST_AsText(pt) AS point, name, ROUND(ST_Distance(@hereami, pt), 2) AS distance FROM poi WHERE ST_Distance(@hereami, pt) < 4.5 AND type = 'Shop' ORDER BY distance ASC ; +----+------------+--------+----------+ | id | point | name | distance | +----+------------+--------+----------+ | 3 | POINT(5 4) | Shop 2 | 4.00 | +----+------------+--------+----------+ 1 row in set (0.37 sec)

The query execution plan looks like this:

id: 1 select_type: SIMPLE table: poi partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 650361 filtered: 10.00 Extra: Using where; Using filesort

So no use of the spatial index yet. :-(

Reading the MySQL documentation Using Spatial Indexes provides some more information:

The optimizer investigates whether available spatial indexes can be involved in the search for queries that use a function such as MBRContains() or MBRWithin() in the WHERE clause.

So it looks like the optimizer CAN evaluate function covered fields in this specific case. But not with the function ST_Distance I have chosen.

So my WHERE clause must look like: "Give me all points within a polygon spanned 4.5 units around my position..."

I did not find any such function in the short run. So I created a hexagon which is not too far from a circle...

With this hexagon I tried again:

SET @hereami = POINT(9,4); SET @hexagon = 'POLYGON((9 8.5, 12.897 6.25, 12.897 1.75, 9 -0.5, 5.103 1.75, 5.103 6.25, 9 8.5))'; SELECT id, ST_AsText(pt) AS point, name, ROUND(ST_Distance(@hereami, pt), 2) AS distance FROM poi WHERE MBRContains(ST_GeomFromText(@hexagon), pt) AND ST_Distance(@hereami, pt) < 4.5 AND type = 'Shop' ORDER BY distance ASC ; Empty set (0.03 sec)

And tadaaah: Damned fast, but the result is not the same! :-( When you look at the graph above it is obvious why: The missing shop is 0.103 units outside of our hexagon search range but within our circle range. So an octagon would have been the better approach...

At least the index is considered now! :-)

id: 1 select_type: SIMPLE table: poi partitions: NULL type: range possible_keys: pt key: pt key_len: 34 ref: NULL rows: 31356 filtered: 10.00 Extra: Using where; Using filesort

For specifying a an "outer" hexagon I was too lazy. So I was specifying a square:

SET @hereami = POINT(9,4); SET @square = 'POLYGON((4.5 8.5, 13.5 8.5, 13.5 -0.5, 4.5 -0.5, 4.5 8.5))'; SELECT id, ST_AsText(pt) AS point, name, ROUND(ST_Distance(@hereami, pt), 2) AS distance FROM poi WHERE MBRContains(ST_GeomFromText(@square), pt) AND ST_Distance(@hereami, pt) < 4.5 AND type = 'Shop' ORDER BY distance ASC ; +----+------------+--------+----------+ | id | point | name | distance | +----+------------+--------+----------+ | 3 | POINT(5 4) | Shop 2 | 4.00 | +----+------------+--------+----------+ 1 row in set (0.02 sec)

So, my shop is in the result again now. And even a bit faster!

Now I wanted to find out if this results are any faster than the conventional method with an index on (x) and (y) or (x, y):

SELECT id, ST_AsText(pt) AS point, name, ROUND(ST_Distance(@hereami, pt), 2) AS distance FROM poi WHERE x >= 4.5 AND x <= 13.5 AND y >= -0.5 AND y <= 8.5 AND ST_Distance(@hereami, pt) < 4.5 AND type = 'Shop' ORDER BY distance ASC ; 1 row in set (0.15 sec)

Here the optimizer chooses the index on x. But I think he could do better. So I forced to optimizer to use the index on (x, y):

SELECT id, ST_AsText(pt) AS point, name, ROUND(ST_Distance(@hereami, pt), 2) AS distance FROM poi FORCE INDEX (xy) WHERE x >= 4.5 AND x <= 13.5 AND y >= -0.5 AND y <= 8.5 AND ST_Distance(@hereami, pt) < 4.5 AND type = 'Shop' ORDER BY distance ASC ; 1 row in set (0.03 sec) id: 1 select_type: SIMPLE table: poi partitions: NULL type: range possible_keys: xy key: xy key_len: 10 ref: NULL rows: 115592 filtered: 1.11 Extra: Using index condition; Using where; Using filesort

Same performance than with the spatial index. So it looks like for this simple task with my data distribution conventional methods do well enough.

No I wanted to try a polygon which comes as close as possible to a circle. This I solved with a MySQL stored function which returns a polygon:/p>

DROP FUNCTION polygon_circle; delimiter // CREATE FUNCTION polygon_circle(pX DOUBLE, pY DOUBLE, pDiameter DOUBLE, pPoints SMALLINT UNSIGNED) -- RETURNS VARCHAR(4096) DETERMINISTIC RETURNS POLYGON DETERMINISTIC BEGIN DECLARE i SMALLINT UNSIGNED DEFAULT 0; DECLARE vSteps SMALLINT UNSIGNED; DECLARE vPolygon VARCHAR(4096) DEFAULT ''; -- Input validation IF pPoints < 3 THEN RETURN NULL; END IF; IF pPoints > 360 THEN RETURN NULL; END IF; IF pPoints > 90 THEN RETURN NULL; END IF; if (360 % pPoints) != 0 THEN RETURN NULL; END IF; -- Start SET vSteps = 360 / pPoints; WHILE i < 360 DO SET vPolygon = CONCAT(vPolygon, (pX + (SIN(i * 2 * PI() / 360) * pDiameter)), ' ', (pY + (COS(i * 2 * PI() / 360) * pDiameter)), ', '); SET i = i + vSteps; END WHILE; -- Add first point again SET vPolygon = CONCAT("POLYGON((", vPolygon, (pX + (SIN(0 * 2 * PI() / 360) * pDiameter)), " ", (pY + (COS(0 * 2 * PI() / 360) * pDiameter)), "))"); -- RETURN vPolygon; RETURN ST_GeomFromText(vPolygon); END; // delimiter ; SELECT ST_AsText(polygon_circle(9, 4, 4.5, 6)); -- SELECT polygon_circle(9, 4, 4.5, 8);

Then calling the query in the same way:

SET @hereami = POINT(9,4); SELECT id, ST_AsText(pt) AS point, name, ROUND(ST_Distance(@hereami, pt), 2) AS distance FROM poi WHERE MBRContains(polygon_circle(9, 4, 4.5, 90), pt) AND ST_Distance(@hereami, pt) < 4.5 AND type = 'Shop' ORDER BY distance ASC ; +----+------------+--------+----------+ | id | point | name | distance | +----+------------+--------+----------+ | 3 | POINT(5 4) | Shop 2 | 4.00 | +----+------------+--------+----------+ 1 row in set (0.03 sec)

This seems not to have any significant negative impact on performance.

Results Test#rowsoperationlatencyTotal655360FTS1300 msSpatial exact Circle4128FTS520 msSpatial inner Hexagon3916range (pt)20 msSpatial outer Square4128range (pt)30 msConventional outer Square on (x)4128range (x) or (y)150 msConventional outer Square on (xy)4128range (x,y)30 msSpatial good Polygon4128range (pt)30 msTaxonomy upgrade extras: spatialgis

A customer reported last month that MySQL does a full table scan (FTS) if a query was filtered by a INT value on a VARCHAR column. First I told him that this is not true any more because MySQL has fixed this behaviour long time ago. He showed me that I was wrong:

CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `data` varchar(64) DEFAULT NULL, `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `data` (`data`) ) ENGINE=InnoDB; EXPLAIN SELECT * FROM test WHERE data = 42\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test partitions: NULL type: ALL possible_keys: data key: NULL key_len: NULL ref: NULL rows: 522500 filtered: 10.00 Extra: Using where EXPLAIN SELECT * FROM test WHERE data = '42'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test partitions: NULL type: ref possible_keys: data key: data key_len: 67 ref: const rows: 1 filtered: 100.00 Extra: NULL

When I executed the query I got some more interesting information:

SELECT * FROM test WHERE data = '42'; Empty set (0.00 sec) SELECT * FROM test WHERE data = 42; +--------+----------------------------------+---------------------+ | id | data | ts | +--------+----------------------------------+---------------------+ | 1096 | 42a5cb4a3e76857a3efe7af44ba9f4dd | 2016-05-25 10:26:59 | ... | 718989 | 42a1921fb2df42126d85f9586532eda4 | 2016-05-25 10:27:12 | +--------+----------------------------------+---------------------+ 767 rows in set, 65535 warnings (0.26 sec)

Looking at the warnings we also find the reason: MySQL does the cast on the column and not on the value which is a bit odd IMHO:

show warnings; | Warning | 1292 | Truncated incorrect DOUBLE value: '80f52706c2f9de40472ec29a7f70c992' |

A bit suspicious I looked at the warnings of the query execution plan again:

show warnings; +---------+------+---------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------+ | Warning | 1739 | Cannot use ref access on index 'data' due to type or collation conversion on field 'data' | | Warning | 1739 | Cannot use range access on index 'data' due to type or collation conversion on field 'data' | +---------+------+---------------------------------------------------------------------------------------------+

I thought this was fixed, but it seems not. The following releases behave like this: MySQL 5.0.96, 5.1.73, 5.5.38, 5.6.25, 5.7.12 and MariaDB 5.5.41, 10.0.21 and 10.1.9

The other way around it seems to work in both cases:

SELECT * FROM test WHERE id = 42; +----+----------------------------------+---------------------+ | id | data | ts | +----+----------------------------------+---------------------+ | 42 | 81d74057d7be8f20563da404bb1b3ab0 | 2016-05-25 10:26:56 | +----+----------------------------------+---------------------+ SELECT * FROM test WHERE id = '42'; +----+----------------------------------+---------------------+ | id | data | ts | +----+----------------------------------+---------------------+ | 42 | 81d74057d7be8f20563da404bb1b3ab0 | 2016-05-25 10:26:56 | +----+----------------------------------+---------------------+ EXPLAIN SELECT * FROM test WHERE id = 42\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL
Taxonomy upgrade extras: query tuningexplaindata typesql

I recently managed to get my certification as MySQL 5.6 DBA, and was asked to write a blog about it, because I had trouble getting the informations I needed.

You may have figured out too, that Oracle does not really supply you with information about the certification. At least, there is the MySQL documentation. It contains all the information you need.

Further, I recommend to use a virtual linux machine in combination with our tool MyEnv. This way you can simulate multiple scenarios, including replication set-ups, and if one or two servers die during your exercises, nobody gets mad at you.

When learning, make sure to have a look at the following topics:

• Query tuning
• Parameters tuning
• MySQL client tools (mysqldump, mysqladmin, ...)
• MySQL Audit Plugin
• How to secure MySQL (Especially, the correct assignment of privileges.)
• How to use the Performance and Information Schema
• Partitions
• Replication
• Backup and Recovery (Both, physical and logical variant.)

The certification takes 150 minutes and contains 100 questions. 60% of your answers have to be correct, in order to pass. If you keep a pace of one answer per minute, you will also have enough time to go over those answers you were not entirely sure at the first time.

I recently managed to get my certification as MySQL 5.6 DBA, and was asked to write a blog about it, because I had trouble getting the informations I needed.

You may have figured out too, that Oracle does not really supply you with information about the certification. At least, there is the MySQL documentation. It contains all the information you need.

Further, I recommend to use a virtual linux machine in combination with our tool MyEnv. This way you can simulate multiple scenarios, including replication set-ups, and if one or two servers die during your exercises, nobody gets mad at you.

When learning, make sure to have a look at the following topics:

• Query tuning
• Parameters tuning
• MySQL client tools (mysqldump, mysqladmin, ...)
• MySQL Audit Plugin
• How to secure MySQL (Especially, the correct assignment of privileges.)
• How to use the Performance and Information Schema
• Partitions
• Replication
• Backup and Recovery (Both, physical and logical variant.)

The certification takes 150 minutes and contains 100 questions. 60% of your answers have to be correct, in order to pass. If you keep a pace of one answer per minute, you will also have enough time to go over those answers you were not entirely sure at the first time.

I recently managed to get my certification as MySQL 5.6 DBA, and was asked to write a blog about it, because I had trouble getting the informations I needed.

You may have figured out too, that Oracle does not really supply you with information about the certification. At least, there is the MySQL documentation. It contains all the information you need.

Further, I recommend to use a virtual linux machine in combination with our tool MyEnv. This way you can simulate multiple scenarios, including replication set-ups, and if one or two servers die during your exercises, nobody gets mad at you.

When learning, make sure to have a look at the following topics:

• Query tuning
• Parameters tuning
• MySQL client tools (mysqldump, mysqladmin, ...)
• MySQL Audit Plugin
• How to secure MySQL (Especially, the correct assignment of privileges.)
• How to use the Performance and Information Schema
• Partitions
• Replication
• Backup and Recovery (Both, physical and logical variant.)

The certification takes 150 minutes and contains 100 questions. 60% of your answers have to be correct, in order to pass. If you keep a pace of one answer per minute, you will also have enough time to go over those answers you were not entirely sure at the first time.

MariaDB 10.2 has introduced some Window Functions for analytical queries.

See also: Window Functions, Window Functions, Window function and Rows and Range, Preceding and Following

Function ROW_NUMBER()

Simulate a row number (sequence) top 3

SELECT ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY category_id) AS num , category.category_id FROM category LIMIT 3 ;

or

SELECT ROW_NUMBER() OVER (ORDER BY category_id) AS num , category.category_id FROM category LIMIT 3 ; +-----+-------------+ | num | category_id | +-----+-------------+ | 1 | ACTUAL | | 2 | ADJUSTMENT | | 3 | BUDGET | +-----+-------------+
ROW_NUMBER() per PARTITION SELECT ROW_NUMBER() OVER (PARTITION BY store_type ORDER BY SUM(sf.store_sales) DESC) AS Nbr , s.store_type AS "Store Type", s.store_city AS City, SUM(sf.store_sales) AS Sales FROM store AS s JOIN sales_fact AS sf ON sf.store_id = s.store_id GROUP BY s.store_type, s.store_city ORDER BY s.store_type, Rank ; +-----+---------------------+---------------+------------+ | Nbr | Store Type | City | Sales | +-----+---------------------+---------------+------------+ | 1 | Deluxe Supermarket | Salem | 1091274.68 | | 2 | Deluxe Supermarket | Tacoma | 993823.44 | | 3 | Deluxe Supermarket | Hidalgo | 557076.84 | | 4 | Deluxe Supermarket | Merida | 548297.64 | | 5 | Deluxe Supermarket | Vancouver | 534180.96 | | 6 | Deluxe Supermarket | San Andres | 518044.80 | | 1 | Gourmet Supermarket | Beverly Hills | 619013.24 | | 2 | Gourmet Supermarket | Camacho | 357772.88 | | 1 | Mid-Size Grocery | Yakima | 304590.92 | | 2 | Mid-Size Grocery | Mexico City | 166503.48 | | 3 | Mid-Size Grocery | Victoria | 144827.48 | | 4 | Mid-Size Grocery | Hidalgo | 144272.84 | +-----+---------------------+---------------+------------+
Function RANK()

Ranking of top 10 salaries

SELECT full_name AS Name, salary AS Salary , RANK() OVER(ORDER BY salary DESC) AS Rank FROM employee ORDER BY salary DESC LIMIT 10 ; +-----------------+----------+------+ | Name | Salary | Rank | +-----------------+----------+------+ | Sheri Nowmer | 80000.00 | 1 | | Darren Stanz | 50000.00 | 2 | | Donna Arnold | 45000.00 | 3 | | Derrick Whelply | 40000.00 | 4 | | Michael Spence | 40000.00 | 4 | | Maya Gutierrez | 35000.00 | 6 | | Pedro Castillo | 35000.00 | 6 | | Laurie Borges | 35000.00 | 6 | | Beverly Baker | 30000.00 | 9 | | Roberta Damstra | 25000.00 | 10 | +-----------------+----------+------+
Function DENSE_RANK() SELECT full_name AS Name, salary AS Salary , DENSE_RANK() OVER(ORDER BY salary DESC) AS Rank FROM employee ORDER BY salary DESC LIMIT 10 ; +-----------------+----------+------+ | Name | Salary | Rank | +-----------------+----------+------+ | Sheri Nowmer | 80000.00 | 1 | | Darren Stanz | 50000.00 | 2 | | Donna Arnold | 45000.00 | 3 | | Derrick Whelply | 40000.00 | 4 | | Michael Spence | 40000.00 | 4 | | Maya Gutierrez | 35000.00 | 5 | | Pedro Castillo | 35000.00 | 5 | | Laurie Borges | 35000.00 | 5 | | Beverly Baker | 30000.00 | 6 | | Roberta Damstra | 25000.00 | 7 | +-----------------+----------+------+
Aggregation Windows SELECT full_name AS Name, salary AS Salary , SUM(salary) OVER(ORDER BY salary DESC) AS "Sum sal" FROM employee ORDER BY salary DESC LIMIT 10 ; +-----------------+----------+-----------+ | Name | Salary | Sum sal | +-----------------+----------+-----------+ | Sheri Nowmer | 80000.00 | 80000.00 | | Darren Stanz | 50000.00 | 130000.00 | | Donna Arnold | 45000.00 | 175000.00 | | Derrick Whelply | 40000.00 | 255000.00 | | Michael Spence | 40000.00 | 255000.00 | | Laurie Borges | 35000.00 | 360000.00 | | Maya Gutierrez | 35000.00 | 360000.00 | | Pedro Castillo | 35000.00 | 360000.00 | | Beverly Baker | 30000.00 | 390000.00 | | Roberta Damstra | 25000.00 | 415000.00 | +-----------------+----------+-----------+
Function CUME_DIST() and PERCENT_RANK() SELECT s.store_state AS State, s.store_city AS City, SUM(e.salary) AS Salary , CUME_DIST() OVER (PARTITION BY State ORDER BY Salary) AS CumeDist , PERCENT_RANK() OVER (PARTITION BY State ORDER BY Salary) AS PctRank FROM employee AS e JOIN store AS s on s.store_id = e.store_id WHERE s.store_country = 'USA' GROUP BY s.store_name ORDER BY s.store_state, Salary DESC ; +-------+---------------+-----------+--------------+--------------+ | State | City | Salary | CumeDist | PctRank | +-------+---------------+-----------+--------------+--------------+ | CA | Alameda | 537000.00 | 1.0000000000 | 1.0000000000 | | CA | Los Angeles | 221200.00 | 0.8000000000 | 0.7500000000 | | CA | San Diego | 220200.00 | 0.6000000000 | 0.5000000000 | | CA | Beverly Hills | 191800.00 | 0.4000000000 | 0.2500000000 | | CA | San Francisco | 30520.00 | 0.2000000000 | 0.0000000000 | | OR | Salem | 260220.00 | 1.0000000000 | 1.0000000000 | | OR | Portland | 221200.00 | 0.5000000000 | 0.0000000000 | | WA | Tacoma | 260220.00 | 1.0000000000 | 1.0000000000 | | WA | Spokane | 223200.00 | 0.8571428571 | 0.8333333333 | | WA | Bremerton | 221200.00 | 0.7142857143 | 0.6666666667 | | WA | Seattle | 220200.00 | 0.5714285714 | 0.5000000000 | | WA | Yakima | 74060.00 | 0.4285714286 | 0.3333333333 | | WA | Bellingham | 23220.00 | 0.2857142857 | 0.1666666667 | | WA | Walla Walla | 21320.00 | 0.1428571429 | 0.0000000000 | +-------+---------------+-----------+--------------+--------------+
Function NTILE() SELECT promotion_name, media_type , TO_DAYS(end_date)-TO_DAYS(start_date) AS Duration , NTILE(4) OVER (PARTITION BY promotion_name ORDER BY DURATION) AS quartile , NTILE(5) OVER (PARTITION BY promotion_name ORDER BY DURATION) AS quintile , NTILE(100) OVER (PARTITION BY promotion_name ORDER BY DURATION) AS precentile FROM promotion WHERE promotion_name = 'Weekend Markdown' LIMIT 10 ; +------------------+-------------------------+----------+----------+----------+------------+ | promotion_name | media_type | Duration | quartile | quintile | precentile | +------------------+-------------------------+----------+----------+----------+------------+ | Weekend Markdown | In-Store Coupon | 2 | 1 | 1 | 9 | | Weekend Markdown | Daily Paper | 3 | 3 | 4 | 29 | | Weekend Markdown | Radio | 3 | 4 | 4 | 36 | | Weekend Markdown | Daily Paper, Radio | 2 | 2 | 2 | 13 | | Weekend Markdown | Daily Paper, Radio, TV | 2 | 2 | 3 | 20 | | Weekend Markdown | TV | 2 | 3 | 3 | 26 | | Weekend Markdown | Sunday Paper | 3 | 3 | 4 | 28 | | Weekend Markdown | Daily Paper, Radio, TV | 3 | 3 | 4 | 34 | | Weekend Markdown | Daily Paper | 2 | 1 | 2 | 10 | | Weekend Markdown | Street Handout | 2 | 2 | 2 | 18 | | Weekend Markdown | Bulk Mail | 3 | 4 | 5 | 37 | | Weekend Markdown | Cash Register Handout | 2 | 2 | 2 | 14 | | Weekend Markdown | Daily Paper, Radio, TV | 3 | 3 | 4 | 31 | | Weekend Markdown | Sunday Paper | 2 | 3 | 3 | 27 | | Weekend Markdown | Sunday Paper, Radio, TV | 1 | 1 | 1 | 4 | +------------------+-------------------------+----------+----------+----------+------------+
Taxonomy upgrade extras: mariadbdwhreportingAnalyticsWindow FunctionOLAPData Mart

One of our customers recently ran into a problem, where he wanted to have a preferred donor for SST, whenever a node came up. The problem was, that the node did not come up, when the preferred donor was not running.

In the documentation, you can find the parameter wsrep_sst_donor, which prefers the specified node as SST donor. This is great, as long as the donor is actually running.

The problem can be fixed by adding a comma to the end of the value of wsrep_sst_donor, what would look like this:

wsrep_sst_donor="galera2,"

Note the comma at the end of the value. This trailing comma basically tells this node, that galera2 is the preferred donor, if galera2 is not available, any other available node will be used as donor.

You could also specify a secondary node, which is needed to be available for the node to come up:

wsrep_sst_donor="galera2,galera1"

In this case, galera1 wil be used as secondary donor if galera2 is not available. If both are not available, the node will refuse to come up.

Taxonomy upgrade extras: Galera Cluster

One of our customers recently ran into a problem, where he wanted to have a preferred donor for SST, whenever a node came up. The problem was, that the node did not come up, when the preferred donor was not running.

In the documentation, you can find the parameter wsrep_sst_donor, which prefers the specified node as SST donor. This is great, as long as the donor is actually running.

The problem can be fixed by adding a comma to the end of the value of wsrep_sst_donor, what would look like this:

wsrep_sst_donor="galera2,"

Note the comma at the end of the value. This trailing comma basically tells this node, that galera2 is the preferred donor, if galera2 is not available, any other available node will be used as donor.

You could also specify a secondary node, which is needed to be available for the node to come up:

wsrep_sst_donor="galera2,galera1"

In this case, galera1 wil be used as secondary donor if galera2 is not available. If both are not available, the node will refuse to come up.

Taxonomy upgrade extras: Galera Cluster

One of our customers recently ran into a problem, where he wanted to have a preferred donor for SST, whenever a node came up. The problem was, that the node did not come up, when the preferred donor was not running.

In the documentation, you can find the parameter wsrep_sst_donor, which prefers the specified node as SST donor. This is great, as long as the donor is actually running.

The problem can be fixed by adding a comma to the end of the value of wsrep_sst_donor, what would look like this:

wsrep_sst_donor="galera2,"

Note the comma at the end of the value. This trailing comma basically tells this node, that galera2 is the preferred donor, if galera2 is not available, any other available node will be used as donor.

You could also specify a secondary node, which is needed to be available for the node to come up:

wsrep_sst_donor="galera2,galera1"

In this case, galera1 wil be used as secondary donor if galera2 is not available. If both are not available, the node will refuse to come up.

Taxonomy upgrade extras: Galera Cluster

Time flies, and my blogging frequency is quite low. More frequent would be better, but knowing myself I'll rather not promise anything ;-)

Still, it is appropriate to write some notes about CeBIT, the "Chemnitzer Linuxtage 2016", and future events.

CeBIT

CeBIT was running from March 14 to 18 (Monday till Friday) in Hannover, Germany, and I will leave the general assessment to the various marketing departments as well as to the regular visitors (to which I do not belong).

In order to meet our current customers as well as potential future ones, FromDual had a booth in the "Open Source Forum". We displayed a Galera Cluster, running on three tiny headless single-board Linux machines, and showed how it reacts to node failures and then recovers all by itself, without any administrator intervention. Many of our visitors were fascinated, because a HA solution would be a good fit in their solution architecture. We had got several stuffed dolphins "Sakila", the traditional MySQL symbol, and all of them found new homes (typically with the words "for my grandchild"). :-)

IMHO, the "Open Source Forum" had deserved a better visitor attraction than it really got - placing it into one hall with document management systems was no good fit, research and development might have been more appropriate.
The forum had an area for talks which were running all five days, I consider John "Maddog" Hall (who had provided an Alpha machine to Linus Torvalds decades ago) and Prof. Klaus Knopper (who is maintaining the "Knoppix" live distribution) the most prominent speakers. FromDual's Oli Sennhauser talked about the new features of MySQL 5.7, you can get the slides via the FromDual download page.

Chemnitzer Linux-Tage

The weekend following CeBIT, March 19 and 20, had been selected for the Chemnitzer Linux-Tage. Like in the previous years, the conference attracted many visitors from all over Germany as well as from some neighbouring countries, and both John Hall and Klaus Knopper had come there directly from Hannover - like me and several others.

As usual, the conference programme covered all aspects of Linux, the headline was "It is your project!". Databases are definitely not one of the major topics there, it is more about overall trends, distributions, communication, and many other aspects.

I delivered a talk about "RPM conventions - a Modern Tower of Bable", and it was well received. I am using various MySQL RPMs (from MySQL AB, Oracle, or RedHat) as examples to show different opinions about packaging, dependencies, installation actions, and compatibility issues, which partly originate from the diverging positions of software developer vs distributor. MySQL was used as a well-known example (but will interest my readers here), most of the items are also applicable to almost any software. Again, the slides (in German) are available on the FromDual web site. Your comments are welcome!

Open Source Data Center Conference

We all know that Open Source has become a major force in computing, so it is no surprise to have it as the subject for various conferences.

One of them is the "Open Source Data Center Conference" "OSDC", to be held in Berlin on April 26 - 28. Open Source database systems are one of the topics, and the programme committee accepted my talk "MySQL-Server in Teamwork - Replication and Galera Cluster". After the conference, I will upload it on the FromDual site and make it available for download.

Now, having told you all this, i will turn to customer issues again ...

Enjoy!

Time flies, and my blogging frequency is quite low. More frequent would be better, but knowing myself I'll rather not promise anything ;-)

Still, it is appropriate to write some notes about CeBIT, the "Chemnitzer Linuxtage 2016", and future events.

CeBIT

CeBIT was running from March 14 to 18 (Monday till Friday) in Hannover, Germany, and I will leave the general assessment to the various marketing departments as well as to the regular visitors (to which I do not belong).

In order to meet our current customers as well as potential future ones, FromDual had a booth in the "Open Source Forum". We displayed a Galera Cluster, running on three tiny headless single-board Linux machines, and showed how it reacts to node failures and then recovers all by itself, without any administrator intervention. Many of our visitors were fascinated, because a HA solution would be a good fit in their solution architecture. We had got several stuffed dolphins "Sakila", the traditional MySQL symbol, and all of them found new homes (typically with the words "for my grandchild"). :-)

IMHO, the "Open Source Forum" had deserved a better visitor attraction than it really got - placing it into one hall with document management systems was no good fit, research and development might have been more appropriate.
The forum had an area for talks which were running all five days, I consider John "Maddog" Hall (who had provided an Alpha machine to Linus Torvalds decades ago) and Prof. Klaus Knopper (who is maintaining the "Knoppix" live distribution) the most prominent speakers. FromDual's Oli Sennhauser talked about the new features of MySQL 5.7, you can get the slides via the FromDual download page.

Chemnitzer Linux-Tage

The weekend following CeBIT, March 19 and 20, had been selected for the Chemnitzer Linux-Tage. Like in the previous years, the conference attracted many visitors from all over Germany as well as from some neighbouring countries, and both John Hall and Klaus Knopper had come there directly from Hannover - like me and several others.

As usual, the conference programme covered all aspects of Linux, the headline was "It is your project!". Databases are definitely not one of the major topics there, it is more about overall trends, distributions, communication, and many other aspects.

I delivered a talk about "RPM conventions - a Modern Tower of Bable", and it was well received. I am using various MySQL RPMs (from MySQL AB, Oracle, or RedHat) as examples to show different opinions about packaging, dependencies, installation actions, and compatibility issues, which partly originate from the diverging positions of software developer vs distributor. MySQL was used as a well-known example (but will interest my readers here), most of the items are also applicable to almost any software. Again, the slides (in German) are available on the FromDual web site. Your comments are welcome!

Open Source Data Center Conference

We all know that Open Source has become a major force in computing, so it is no surprise to have it as the subject for various conferences.

One of them is the "Open Source Data Center Conference" "OSDC", to be held in Berlin on April 26 - 28. Open Source database systems are one of the topics, and the programme committee accepted my talk "MySQL-Server in Teamwork - Replication and Galera Cluster". After the conference, I will upload it on the FromDual site and make it available for download.

Now, having told you all this, i will turn to customer issues again ...

Enjoy!

Time flies, and my blogging frequency is quite low. More frequent would be better, but knowing myself I'll rather not promise anything ;-)

Still, it is appropriate to write some notes about CeBIT, the "Chemnitzer Linuxtage 2016", and future events.

CeBIT

CeBIT was running from March 14 to 18 (Monday till Friday) in Hannover, Germany, and I will leave the general assessment to the various marketing departments as well as to the regular visitors (to which I do not belong).

In order to meet our current customers as well as potential future ones, FromDual had a booth in the "Open Source Forum". We displayed a Galera Cluster, running on three tiny headless single-board Linux machines, and showed how it reacts to node failures and then recovers all by itself, without any administrator intervention. Many of our visitors were fascinated, because a HA solution would be a good fit in their solution architecture. We had got several stuffed dolphins "Sakila", the traditional MySQL symbol, and all of them found new homes (typically with the words "for my grandchild"). :-)

IMHO, the "Open Source Forum" had deserved a better visitor attraction than it really got - placing it into one hall with document management systems was no good fit, research and development might have been more appropriate.
The forum had an area for talks which were running all five days, I consider John "Maddog" Hall (who had provided an Alpha machine to Linus Torvalds decades ago) and Prof. Klaus Knopper (who is maintaining the "Knoppix" live distribution) the most prominent speakers. FromDual's Oli Sennhauser talked about the new features of MySQL 5.7, you can get the slides via the FromDual download page.

Chemnitzer Linux-Tage

The weekend following CeBIT, March 19 and 20, had been selected for the Chemnitzer Linux-Tage. Like in the previous years, the conference attracted many visitors from all over Germany as well as from some neighbouring countries, and both John Hall and Klaus Knopper had come there directly from Hannover - like me and several others.

As usual, the conference programme covered all aspects of Linux, the headline was "It is your project!". Databases are definitely not one of the major topics there, it is more about overall trends, distributions, communication, and many other aspects.

I delivered a talk about "RPM conventions - a Modern Tower of Bable", and it was well received. I am using various MySQL RPMs (from MySQL AB, Oracle, or RedHat) as examples to show different opinions about packaging, dependencies, installation actions, and compatibility issues, which partly originate from the diverging positions of software developer vs distributor. MySQL was used as a well-known example (but will interest my readers here), most of the items are also applicable to almost any software. Again, the slides (in German) are available on the FromDual web site. Your comments are welcome!

Open Source Data Center Conference

We all know that Open Source has become a major force in computing, so it is no surprise to have it as the subject for various conferences.

One of them is the "Open Source Data Center Conference" "OSDC", to be held in Berlin on April 26 - 28. Open Source database systems are one of the topics, and the programme committee accepted my talk "MySQL-Server in Teamwork - Replication and Galera Cluster". After the conference, I will upload it on the FromDual site and make it available for download.

Now, having told you all this, i will turn to customer issues again ...

Enjoy!

To synchronize the data between the Galera Cluster and a new or re-entering Galera node Galera Cluster uses 2 different mechanisms:

• For full synchronization of data: Snapshot State Transfer (SST).
• For delta synchronization of data: Incremental State Transfer (IST).

The Incremental State Transfer (IST) is relevant when a node is already known to the Galera Cluster and just left the cluster short time ago. This typically happens in a maintenance window during a rolling cluster restart.

The Galera Cache is a round-robin file based cache that keeps all the write-sets (= transactions + meta data) for a certain amount of time. This time, which should be bigger than your planned maintenance window, depends on the size of the Galera Cache (default 128 Mbyte) and the traffic which will happen during your maintenance window.

If your traffic is bigger than the Galera Cache can keep Galera Cluster will fall-back from IST to SST which is a very expensive operation for big databases.

The size of the Galera Cache can be calculated of the delta of the sum of the following 2 Galera status informations before and after the maintenance window:

Galera Cache size = delta(wsrep_replicated_bytes + wsrep_received_bytes)

Ideally you determine these values before your change happens in a time window where you have roughly the same traffic as during your maintenance window.

If you do not have a Galera Cluster in place yet or if you do not have those values available you can also use the numbers of the traffic written to the binary log or the number of the traffic written to InnoDB transaction log (Innodb_os_log_written).

As a rough estimate we have evaluated the following formulas for you:

Binary Log Traffic x 1.3 = Wsrep traffic (+/- 10%)

or

InnoDB Log File traffic x 0.6 = Wsrep traffic (+/- 10%)
Taxonomy upgrade extras: Galera Clustercachesizing