engrenage-passion

  • Increase font size
  • Default font size
  • Decrease font size

Bientôt

No current events.

Newsfeeds

Planet MySQL

Planet MySQL - https://planet.mysql.com
  • Shinguz: First Docker steps with MySQL and MariaDB
    The Docker version of the distributions are often quite old. On Ubuntu 16.04 for example: shell> docker --version Docker version 1.13.1, build 092cba3 But the current docker version is 17.09.0-ce (2017-09-26). It seems like they have switched from the old version schema x.y.z to the new year.month.version version schema in February/March 2017. Install Docker CE Repository Add the Docker's official PGP key: shell> curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add - OK Add the Docker repository: shell> echo "deb [arch=amd64] https://download.docker.com/linux/ubuntu \ $(lsb_release -cs) \ stable" > /etc/apt/sources.list.d/docker.list shell> apt-get update Install or upgrade Docker: shell> apt-get install docker-ce shell> docker --version Docker version 17.09.0-ce, build afdb6d4 To test your Docker installation run: shell> docker run --rm hello-world Add Docker containers for MariaDB, MySQL and MySQL Enterprise Edition First we want to see what Docker containers are available: shell> docker search mysql --no-trunc --filter=stars=100 NAME DESCRIPTION STARS OFFICIAL AUTOMATED mysql MySQL is a widely used, open-source relational database management system (RDBMS). 5273 [OK] mariadb MariaDB is a community-developed fork of MySQL intended to remain free under the GNU GPL. 1634 [OK] mysql/mysql-server Optimized MySQL Server Docker images. Created, maintained and supported by the MySQL team at Oracle 368 [OK] percona Percona Server is a fork of the MySQL relational database management system created by Percona. 303 [OK] ... OK. It seems like MySQL Server Enterprise Edition is missing. So we have to create an account on Docker Store and get the MySQL Server Enterprise Edition Image from there: shell> docker login --username=fromdual Password: Login Succeeded Unfortunately one can still not see MySQL Server Enterprise Edition. But we can try anyway: shell> docker pull store/oracle/mysql-enterprise-server:5.7 shell> docker logout shell> docker pull mysql shell> docker pull mariadb shell> docker pull mysql/mysql-server To see what is going on on your local Docker registry you can type: shell> docker images REPOSITORY TAG IMAGE ID CREATED SIZE mariadb latest abcee1d29aac 8 days ago 396MB mysql latest 5709795eeffa 2 weeks ago 408MB mysql/mysql-server latest a3ee341faefb 5 weeks ago 246MB store/oracle/mysql-enterprise-server 5.7 41bf2fa0b4a1 4 months ago 244MB hello-world latest 48b5124b2768 10 months ago 1.84kB I personally do not like that all those images which are tagged with latest because I want a clear control over what version is used. MariaDB and MySQL community server have implemented this quite nicely but not MySQL Enterprise Edition: shell> docker pull mariadb:10.0 shell> docker pull mariadb:10.0.23 shell> docker pull mysql:8.0 shell> docker pull mysql:8.0.3 docker images | sort REPOSITORY TAG IMAGE ID CREATED SIZE hello-world latest 48b5124b2768 10 months ago 1.84kB mariadb 10.0.23 93631b528e67 21 months ago 305MB mariadb 10.0 eecd58425049 8 days ago 337MB mariadb latest abcee1d29aac 8 days ago 396MB mysql 8.0.3 e691422324d8 2 weeks ago 343MB mysql 8.0 e691422324d8 2 weeks ago 343MB mysql latest 5709795eeffa 2 weeks ago 408MB mysql/mysql-server latest a3ee341faefb 5 weeks ago 246MB store/oracle/mysql-enterprise-server 5.7 41bf2fa0b4a1 4 months ago 244MB Run a MariaDB server container Start a new Docker container from the MariaDB image by running: shell> CONTAINER_NAME=mariadb shell> CONTAINER_IMAGE=mariadb shell> TAG=latest shell> MYSQL_ROOT_PASSWORD=Secret-123 shell> MYSQL_ROOT_USER=root shell> docker run \ --name=${CONTAINER_NAME} \ --detach \ --env=MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \ ${CONTAINER_IMAGE}:${TAG} shell> docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 60d7b6de7ed1 mariadb:latest "docker-entrypoint..." 24 seconds ago Up 23 seconds 3306/tcp mariadb shell> docker logs ${CONTAINER_NAME} shell> docker exec \ --interactive \ --tty \ ${CONTAINER_NAME} \ mysql --user=${MYSQL_ROOT_USER} --password=${MYSQL_ROOT_PASSWORD} --execute="status" shell> docker image tag mariadb:latest mariadb:10.2.10 shell> docker exec --interactive \ --tty \ ${CONTAINER_NAME} \ bash shell> docker stop ${CONTAINER_NAME} shell> docker rm ${CONTAINER_NAME} Run a MySQL Community server container shell> CONTAINER_NAME=mysql shell> CONTAINER_IMAGE=mysql/mysql-server shell> TAG=latest shell> MYSQL_ROOT_PASSWORD=Secret-123 shell> docker run \ --name=${CONTAINER_NAME} \ --detach \ --env=MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \ ${CONTAINER_IMAGE}:${TAG} shell> docker stop ${CONTAINER_NAME} shell> docker rm ${CONTAINER_NAME} Run a MySQL Server Enterprise Edition container shell> CONTAINER_NAME=mysql-ee shell> CONTAINER_IMAGE=store/oracle/mysql-enterprise-server shell> TAG=5.7 shell> MYSQL_ROOT_PASSWORD=Secret-123 shell> docker run \ --name=${CONTAINER_NAME} \ --detach \ --env=MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \ ${CONTAINER_IMAGE}:${TAG} shell> docker ps --all CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 0cb4e6a8a621 store/oracle/mysql-enterprise-server:5.7 "/entrypoint.sh my..." 37 seconds ago Up 36 seconds (healthy) 3306/tcp, 33060/tcp mysql-ee 1832b98da6ef mysql:latest "docker-entrypoint..." 6 minutes ago Up 6 minutes 3306/tcp mysql 60d7b6de7ed1 mariadb:latest "docker-entrypoint..." 21 minutes ago Up 21 minutes 3306/tcp mariadb All my 3 docker containers are currently running as root: shell> ps -ef | grep docker root 13177 1 20:20 ? 00:00:44 /usr/bin/dockerd -H fd:// root 13186 13177 20:20 ? 00:00:04 docker-containerd -l unix:///var/run/docker/libcontainerd/docker-containerd.sock --metrics-interval=0 --start-timeout 2m --state-dir /var/run/docker/libcontainerd/containerd --shim docker-containerd-shim --runtime docker-runc root 24004 13186 21:41 ? 00:00:00 docker-containerd-shim 60d7b6de7ed1ff62b67e66c6effce0094fd60e9565ede65fa34e188b636c54ec /var/run/docker/libcontainerd/60d7b6de7ed1ff62b67e66c6effce0094fd60e9565ede65fa34e188b636c54ec docker-runc root 26593 13186 21:56 ? 00:00:00 docker-containerd-shim 1832b98da6ef7459c33181e9b9ddd89a4136c3b2676335bcbbb533389cbf6219 /var/run/docker/libcontainerd/1832b98da6ef7459c33181e9b9ddd89a4136c3b2676335bcbbb533389cbf6219 docker-runc root 27714 13186 22:02 ? 00:00:00 docker-containerd-shim 0cb4e6a8a62103b66164ccddd028217bb4012d8a6aad1f62d3ed6ae71e1a38b4 /var/run/docker/libcontainerd/0cb4e6a8a62103b66164ccddd028217bb4012d8a6aad1f62d3ed6ae71e1a38b4 docker-runc But the user running the process IN the container is not root: shell> docker exec \ --interactive \ --tty \ ${CONTAINER_NAME} \ grep ^Uid /proc/1/status Uid: 27 27 27 27 shell> docker exec \ --interactive \ --tty \ ${CONTAINER_NAME} \ bash -c "id 27" uid=27(mysql) gid=27(mysql) groups=27(mysql) Run a Docker container from mysql user shell> id uid=1001(mysql) gid=1001(mysql) groups=1001(mysql) shell> docker images Got permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.sock: Get http://%2Fvar%2Frun%2Fdocker.sock/v1.32/images/json: dial unix /var/run/docker.sock: connect: permission denied shell> sudo adduser mysql docker Adding user `mysql' to group `docker' ... Adding user mysql to group docker Done. Taxonomy upgrade extras:  docker mysql mariadb

  • Going Beyond Tabular EXPLAIN
    A while ago, Lukas Eder posted a very interesting article on query optimizations that do not depend on the cost model. We often call such optimizations query transformations since they can be applied by rewriting the query. In his blog post, Lukas investigated how different database systems handle different opportunities for query transformations. For MySQL, he complained that in some cases, the output from EXPLAIN is not sufficient to tell what is going on. However, as I will show in this blog post, there are other ways to get the information that he was looking for. The EXPLAIN Warning What may easily be overlooked when executing EXPLAIN for a query, is that it literally comes with a warning. This warning shows the query after the query transformations have been applied. Let's look at the query Lukas used to explore Transitive Closure: SELECT first_name, last_name, film_idFROM actor aJOIN film_actor fa ON a.actor_id = fa.actor_idWHERE a.actor_id = 1; If we run EXPLAIN for this query, and display the associated warning, we see: mysql> EXPLAIN SELECT first_name, last_name, film_id FROM actor a JOIN film_actor fa ON a.actor_id = fa.actor_id WHERE a.actor_id = 1;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+| 1 | SIMPLE | a | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | NULL || 1 | SIMPLE | fa | NULL | ref | PRIMARY | PRIMARY | 2 | const | 19 | 100.00 | Using index |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+2 rows in set, 1 warning (0,00 sec)mysql> show warnings;+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | /* select#1 */ select 'PENELOPE' AS `first_name`,'GUINESS' AS `last_name`,`sakila`.`fa`.`film_id` AS `film_id` from `sakila`.`actor` `a` join `sakila`.`film_actor` `fa` where (`sakila`.`fa`.`actor_id` = 1) |+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0,00 sec) If you scroll to the right, you will see that the warning contains: `sakila`.`fa`.`actor_id` = 1. In other words, the predicate actor_id = 1 has been applied to the film_actor table because of transitive closure. The above warning message also illustrates another aspect of MySQL query optimization. Since the query specifies the value for the primary key of the actor table, the primary key look-up will be done in the optimizer phase. Hence, the warning shows that columns from the actor table have been replaced by the column values for the requested row. Structured EXPLAIN MySQL 5.6 introduced Structured EXPLAIN. Its output describes the query plan in JSON format. This output contains additional information compared to traditional EXPLAIN. For example, while the tabular EXPLAIN only says "Using where" when a condition is applied when reading a table, the JSON output will display the actual condition. Let's look at the output for the first example on Removing “Silly” Predicates: mysql> EXPLAIN FORMAT=JSON SELECT * FROM film WHERE release_year = release_year;+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| { "query_block": { "select_id": 1, "cost_info": { "query_cost": "212.00" }, "table": { "table_name": "film", "access_type": "ALL", "rows_examined_per_scan": 1000, "rows_produced_per_join": 100, "filtered": "10.00", "cost_info": { "read_cost": "192.00", "eval_cost": "20.00", "prefix_cost": "212.00", "data_read_per_join": "78K" }, "used_columns": [ "film_id", "title", "description", "release_year", "language_id", "original_language_id", "rental_duration", "rental_rate", "length", "replacement_cost", "rating", "special_features", "last_update" ], "attached_condition": "(`sakila`.`film`.`release_year` = `sakila`.`film`.`release_year`)" } }} |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ The value for "attached_condition" shows that MySQL does not simplify the condition release_year = release_year to release_year IS NOT NULL. So Lukas is right in his educated guess that MySQL does not optimize this. However, if we look at a similar silly predicate on a NOT NULL column, we see that such predicates are eliminated by MySQL: mysql> EXPLAIN FORMAT=JSON SELECT * FROM film WHERE film_id = film_id;+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN |+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| { "query_block": { "select_id": 1, "cost_info": { "query_cost": "212.00" }, "table": { "table_name": "film", "access_type": "ALL", "rows_examined_per_scan": 1000, "rows_produced_per_join": 1000, "filtered": "100.00", "cost_info": { "read_cost": "12.00", "eval_cost": "200.00", "prefix_cost": "212.00", "data_read_per_join": "781K" }, "used_columns": [ "film_id", "title", "description", "release_year", "language_id", "original_language_id", "rental_duration", "rental_rate", "length", "replacement_cost", "rating", "special_features", "last_update" ] } }} |+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ In this case, there is no "attached_condition" in the JSON output. In other words, the silly predicate has been removed. Optimizer Trace Predicate Merging investigates whether the optimizer will merge two predicates on the same column. There are actually two different aspects here: Whether predicates are merged and evaluated as a single predicate Whether key ranges as specified by the query are merged when setting up index range scans The latter is the most important since it ensures that not more rows than necessary are accessed. Lukas concludes that MySQL does predicate merging based on the estimated number of rows that tabular EXPLAIN shows: mysql> EXPLAIN SELECT * -> FROM actor -> WHERE actor_id IN (2, 3, 4) -> AND actor_id IN (1, 2, 3);+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| 1 | SIMPLE | actor | NULL | range | PRIMARY | PRIMARY | 2 | NULL | 2 | 100.00 | Using where |+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+1 row in set, 1 warning (0,00 sec)Here EXPLAIN shows that the estimated number of rows to be read is 2, and this fits with how many rows satisfy the merged predicates. If we look at the output from structured EXPLAIN, we get a different picture: mysql> EXPLAIN FORMAT=JSON SELECT * FROM actor WHERE actor_id IN (2, 3, 4) AND actor_id IN (1, 2, 3);+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| EXPLAIN |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| { "query_block": { "select_id": 1, "cost_info": { "query_cost": "2.81" }, "table": { "table_name": "actor", "access_type": "range", "possible_keys": [ "PRIMARY" ], "key": "PRIMARY", "used_key_parts": [ "actor_id" ], "key_length": "2", "rows_examined_per_scan": 2, "rows_produced_per_join": 2, "filtered": "100.00", "cost_info": { "read_cost": "2.41", "eval_cost": "0.40", "prefix_cost": "2.81", "data_read_per_join": "560" }, "used_columns": [ "actor_id", "first_name", "last_name", "last_update" ], "attached_condition": "((`sakila`.`actor`.`actor_id` in (2,3,4)) and (`sakila`.`actor`.`actor_id` in (1,2,3)))" } }} |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+As you can see from "attached_condition", the predicates are not actually merged. It is only the key ranges that are merged. Structured EXPLAIN does not show what key ranges are used for the index range scan. However, we can use Optimizer Trace to find this information. (See instructions on how to obtain the optimizer trace.) The optimizer trace for the above query contains this part: "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 2, "ranges": [ "2 "3 ] }, "rows_for_plan": 2, "cost_for_plan": 2.41, "chosen": true }This shows that the range optimizer has actually set up two ranges here; one for actor_id = 2 and one for actor_id = 3. (In other words, the range optimizer does not seem to take into account that actor_id is an integer column.) It is the same story for Lukas' other query investigating predicate merging. That query specifies two overlapping key ranges: SELECT *FROM filmWHERE film_id BETWEEN 1 AND 100AND film_id BETWEEN 99 AND 200; Also in this case structured EXPLAIN shows that predicates are not merged, while optimizer trace shows that the key ranges are merged: "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "PRIMARY", "rows": 2, "ranges": [ "99 ] }, "rows_for_plan": 2, "cost_for_plan": 2.41, "chosen": true }Since this query has range predicates instead of equality predicates, MySQL will here set up a single range scan from 99 to 100. Concluding RemarksI have in this blog post shown some examples of how you can get additional information about a query plan, beyond what you can see in the output of tabular EXPLAIN. There is a lot of other information that you can deduct from looking at the EXPLAIN Warning, Structured EXPLAIN, or Optimizer Trace than what I have discussed here. That can be the topic for later blog posts.

  • MySQL blank users preventing slave I/O connection?
    Hi, Recently I have encountered a weird simple issue with starting Slave server. So created user as tried to run change master: 2017-10-30 14:03:59 DEBUG Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock --force test -e 'select @@port' 2017-10-30 14:03:59 DEBUG Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock --force test -e "CREATE USER 'repl'@'%' IDENTIFIED BY 'Baku12345'" 2017-10-30 14:03:59 DEBUG Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock --force test -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'" 2017-10-30 14:03:59 DEBUG Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/sock0.sock --force test -e "CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='repl', MASTER_PASSWORD='Baku12345', MASTER_PORT=10023, MASTER_AUTO_POSITION=1" 2017-10-30 14:03:59 DEBUG Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/sock0.sock --force test -e 'start slave' After that got the error: 2017-10-30 13:22:05 1314 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60 retries: 11, Error_code: 2003 2017-10-30 13:22:48 7182 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60 retries: 106, Error_code: 2003 2017-10-30 13:22:57 28984 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60 retries: 1316, Error_code: 2003 2017-10-30 13:23:05 1314 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60 retries: 12, Error_code: 2003 2017-10-30 13:23:48 7182 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60 retries: 107, Error_code: 2003 2017-10-30 13:23:57 28984 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60 retries: 1317, Error_code: 1045 2017-10-30 13:24:05 1314 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60 retries: 13, Error_code: 1045 2017-10-30 13:24:48 7182 [ERROR] Slave I/O: error connecting to master 'repl@localhost:10023' - retry-time: 60 retries: 108, Error_code: 1045 I got the same result with MySQL 5.5 today and remember previous case with MySQL 5.6 2017-11-24 10:14:15 DEBUG Running -> /home/shahriyar.rzaev/XB_TEST/server_dir/PS231117-5.5.58-38.10-linux-x86_64-debug/bin/mysql -A -uroot -S/home/shahriyar.rzaev/XB_TEST/server_dir/PS231117-5.5.58-38.10-linux-x86_64-debug/sock1.sock --force test -e 'show slave status\G' 2017-11-24 10:14:15 DEBUG <pid.PidFile object at 0x7fbe55751cc8> closing pidfile: /tmp/MySQL-AutoXtraBackup/autoxtrabackup.pid Traceback (most recent call last): File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/bin/autoxtrabackup", line 11, in <module> load_entry_point('mysql-autoxtrabackup==1.5.0', 'console_scripts', 'autoxtrabackup')() File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/core.py", line 722, in __call__ return self.main(*args, **kwargs) File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/core.py", line 697, in main rv = self.invoke(ctx) File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/core.py", line 895, in invoke return ctx.invoke(self.callback, **ctx.params) File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/core.py", line 535, in invoke return callback(*args, **kwargs) File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/click-6.7-py3.5.egg/click/decorators.py", line 17, in new_func return f(get_current_context(), *args, **kwargs) File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/mysql_autoxtrabackup-1.5.0-py3.5.egg/autoxtrabackup.py", line 209, in all_procedure File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/mysql_autoxtrabackup-1.5.0-py3.5.egg/prepare_env_test_mode/runner_test_mode.py", line 464, in wipe_backup_prepare_copyback File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/mysql_autoxtrabackup-1.5.0-py3.5.egg/prepare_env_test_mode/runner_test_mode.py", line 378, in run_change_master File "/home/shahriyar.rzaev/virtualenvs/py_3_5_3_autoxtrabackup/lib/python3.5/site-packages/mysql_autoxtrabackup-1.5.0-py3.5.egg/prepare_env_test_mode/runner_test_mode.py", line 152, in check_slave_status RuntimeError: Slave_IO_Running is not Yes 2017-11-24 10:14:15 DEBUG <pid.PidFile object at 0x7fbe55751cc8> closing pidfile: /tmp/MySQL-AutoXtraBackup/autoxtrabackup.pid So after that, I tried to connect manually via replication user: $ /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -urepl --password='Baku12345' --port=10023 --socket=/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'repl'@'localhost' (using password: YES) Removed blank users from master: 5.6.37>drop user ''@'localhost'; Query OK, 0 rows affected (0.01 sec) 5.6.37>drop user ''@'qaserver-02.ci.percona.com'; Query OK, 0 rows affected (0.00 sec) Now reconnecting: $ /home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/bin/mysql -urepl --password='Baku12345' --port=10023 --socket=/home/shahriyar.rzaev/XB_TEST/server_dir/PS231017-percona-server-5.6.37-82.2-linux-x86_64-debug/socket.sock Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 623 Server version: 5.6.37-82.2-debug-log MySQL Community Server (GPL) So it turned out, the blank users were preventing slave connection to master server Also there is a nice bug report Slave can’t reconnect after 2003 error when sha256_password is used

  • Deploying &amp; Managing MySQL NDB Cluster with ClusterControl
    In ClusterControl 1.5 we added a support for the MySQL NDB Cluster 7.5. In this blog post, we’ll look at some of the features that make ClusterControl a great tool to manage MySQL NDB Cluster. First and foremost, as there are numerous products with “Cluster” in their name, we’d like to say couple of words about MySQL NDB Cluster itself and how it differentiates from other solutions. MySQL NDB Cluster Related resources  Galera Cluster VS MySQL NDB Cluster  Memcached Access to MySQL Cluster  ClusterControl Tips & Tricks Manage and Monitor MySQL NDB Clusters  On-Demand MySQL Cluster Training MySQL NDB Cluster is a shared-nothing synchronous cluster for MySQL, based on the NDB engine. It is a product with its own list of features, and quite different from Galera Cluster or MySQL InnoDB Cluster. One main difference is the use of NDB engine, not InnoDB, which is the default engine for MySQL. In NDB cluster, data is partitioned across multiple data nodes while Galera Cluster or MySQL InnoDB Cluster contain the full data set on each of the nodes. This has serious repercussions in the way MySQL NDB Cluster deals with queries which use JOINs and large chunks of the dataset. When it comes to architecture, MySQL NDB Cluster consists of three different node types. Data nodes stores the data using NDB engine. Data is mirrored for redundancy, with up to 4 replicas of data. Note that ClusterControl will deploy 2 replicas per node group, as this is the most tested and stable configuration. Management nodes are intended to control the cluster - for high availability reasons, typically, you have two such nodes. SQL nodes are used as the entry points to the cluster. They parse SQL, ask for data from the data nodes and aggregate result sets when needed. ClusterControl features for MySQL NDB Cluster Deployment ClusterControl 1.5 supports deployment of MySQL NDB Cluster 7.5. It’s done through the same deployment wizard like with the remaining cluster types. In the first step, you need to configure how ClusterControl can login via SSH to the hosts - this is a standard requirement for ClusterControl - it is agentless so it requires root SSH access either directly, to the root account or via (password or passwordless) sudo. In the next step, you define management nodes for your cluster. Here, you need to decide how many data nodes you’d like to have. As we previously stated, every 2 nodes will be part of a node group so this should be an even number. Finally, you need to decide how many SQL nodes you’d like to deploy in your cluster. Once you click deploy, ClusterControl will connect to the hosts, install the software and configure all services. After a while, you should see your cluster deployed. Scaling of MySQL NDB Cluster For MySQL NDB Cluster, ClusterControl 1.5.0 supports scaling of SQL nodes. You can access the job from the Cluster jobs dropdown. There you can fill in the hostname of the node you’d like to add and that’s all you need - ClusterControl will take care of the rest. Management of MySQL NDB Cluster ClusterControl helps you manage MySQL NDB Cluster. In this section we’d like to go through some of the management features that we have. Backups Backups are crucial for any production environment. In case of disaster, only a good backup can minimize the data loss and help you to quickly recover from the issue. Replication might not always be a solution that works - DROP TABLE will drop the table on all of the hosts in the topology. Even a delayed slave can delay the inevitable only by so much. ClusterControl supports ndb backup for MySQL NDB Cluster. You can easily create a backup schedule to be executed by ClusterControl. Proxy layer ClusterControl lets you deploy a full high availability stack on top of the MySQL NDB Cluster. For the proxy layer, we support deployment of HAProxy and MaxScale. As shown on the screenshot above, deployment looks very similar to the other cluster types. You need to decide if you want to use an existing HAProxy or deploy a new one. Then you need to make a choice how to install it - using packages from repositories available on the node or compile it from the source code of the latest release. If you decide to use HAProxy, you will have the possibility to configure high availability using Keepalived and Virtual IP. The process is the following - you define a Virtual IP and the interface on which it should be brought up. Then, you can deploy it for every HAProxy that you have installed. One of the Keepalived processes will be determined as a “master” and it’ll enable VIP on its node. Your application then connects to this particular IP. When a current active HAProxy is not available, the VIP will be moved to another available HAProxy, restoring the connectivity. Recovery management While MySQL NDB Cluster can tolerate failures of individual nodes, it is important to promptly react to these. ClusterControl provides automated recovery for all components of the cluster. No matter what fails (management node, data node or SQL node), ClusterControl will automatically restart them. Monitoring of the MySQL NDB Cluster Any production-ready environment has to be monitored. ClusterControl provides you with a range of metrics to monitor. In the “Overview” page, we show graphs based on the most important metrics for your cluster. You can also create your own dashboards, showing additional data that would be useful in your environment. In addition to the graphs, the “Overview” page gives you insights into the state of the cluster based on some MySQL NDB Cluster metrics like used Index Memory, Data Memory and state of some buffers. It also provides monitoring of the host metrics, including CPU utilization, RAM, Disk or Network stats. Those graphs are also crucial in building a view of the health of the cluster. ClusterControl can also help you to improve performance of your databases by giving you access to the Query Monitor, which holds statistics about your traffic. As seen on the screenshot above, you can see what kind of queries are running against your cluster, how many queries of a given type, what are their execution times and the total execution times. This helps identify which queries are slow and which of them are responsible for the majority of the traffic. You can then focus on the queries which can provide you with the biggest performance improvement. Tags:  MySQL ndb telecom ndb cluster

  • From MySQL 5.6 partitioning to 5.7 and beyond
    As you may already know,  since MySQL 5.7.17, the generic partitioning handler in the MySQL server is deprecated, and is completely removed in MySQL 8.0. So now, in MySQL 5.7, the storage engine used for a given table is expected to provide its own (“native”) partitioning handler. Currently, only the InnoDB and NDB storage engines do. MySQL 5.7 supports generic partitions & native partitions What does that imply for users using partitions in an earlier version MySQL  migrating to 5.7 ? As the documentation describes it, the generic partitioning handler is still supported in all MySQL 5.7 releases, although it is deprecated in 5.7.17 and later. (Note that “deprecated” is not the same as “removed”.) Partitioned tables created in older versions of MySQL continue to work in 5.7 but if you modify them then they will be upgraded to the new native partition handler. You can also upgrade them manually. MySQL 5.6 Let’s have a look with an example of two tables that we will partition in MySQL 5.6. Initial state: mysql> show tables; +------------------+ | Tables_in_sbtest | +------------------+ | sbtest1 | | sbtest2 | +------------------+ 2 rows in set (0.08 sec) [root@mysql1 sbtest]# ls -lh total 432M -rw-rw----. 1 mysql mysql 65 Nov 22 12:20 db.opt -rw-rw----. 1 mysql mysql 8.5K Nov 22 12:24 sbtest1.frm -rw-rw----. 1 mysql mysql 232M Nov 22 12:24 sbtest1.ibd -rw-rw----. 1 mysql mysql 8.5K Nov 22 12:26 sbtest2.frm -rw-rw----. 1 mysql mysql 181M Nov 22 12:26 sbtest2.MYD -rw-rw----. 1 mysql mysql 20M Nov 22 12:26 sbtest2.MYI As you can see, we have two tables, one in InnoDB (sbtest1) and one in MyISAM (sbtest2). Let’s create the partitions: mysql> ALTER TABLE sbtest1 PARTITION BY KEY() partitions 5; mysql> ALTER TABLE sbtest2 PARTITION BY KEY() partitions 5; We can see the changes on the filesystem too: [root@mysql1 sbtest]# ls -lh total 499M -rw-rw----. 1 mysql mysql 65 Nov 22 12:20 db.opt -rw-rw----. 1 mysql mysql 8.5K Nov 22 12:32 sbtest1.frm -rw-rw----. 1 mysql mysql 40 Nov 22 12:32 sbtest1.par -rw-rw----. 1 mysql mysql 56M Nov 22 12:33 sbtest1#P#p0.ibd -rw-rw----. 1 mysql mysql 56M Nov 22 12:33 sbtest1#P#p1.ibd -rw-rw----. 1 mysql mysql 60M Nov 22 12:33 sbtest1#P#p2.ibd -rw-rw----. 1 mysql mysql 60M Nov 22 12:33 sbtest1#P#p3.ibd -rw-rw----. 1 mysql mysql 60M Nov 22 12:33 sbtest1#P#p4.ibd -rw-rw----. 1 mysql mysql 8.5K Nov 22 12:36 sbtest2.frm -rw-rw----. 1 mysql mysql 40 Nov 22 12:36 sbtest2.par -rw-rw----. 1 mysql mysql 36M Nov 22 12:36 sbtest2#P#p0.MYD -rw-rw----. 1 mysql mysql 3.9M Nov 22 12:36 sbtest2#P#p0.MYI -rw-rw----. 1 mysql mysql 36M Nov 22 12:36 sbtest2#P#p1.MYD -rw-rw----. 1 mysql mysql 3.9M Nov 22 12:36 sbtest2#P#p1.MYI -rw-rw----. 1 mysql mysql 37M Nov 22 12:36 sbtest2#P#p2.MYD -rw-rw----. 1 mysql mysql 4.0M Nov 22 12:36 sbtest2#P#p2.MYI -rw-rw----. 1 mysql mysql 37M Nov 22 12:36 sbtest2#P#p3.MYD -rw-rw----. 1 mysql mysql 4.0M Nov 22 12:36 sbtest2#P#p3.MYI -rw-rw----. 1 mysql mysql 37M Nov 22 12:36 sbtest2#P#p4.MYD -rw-rw----. 1 mysql mysql 4.0M Nov 22 12:36 sbtest2#P#p4.MYI We can use Information_Schema to identify the partitioned tables: mysql> SELECT count(*) as '# partitions', t1.TABLE_SCHEMA, t1.TABLE_NAME, ENGINE, CREATE_OPTIONS FROM INFORMATION_SCHEMA.PARTITIONS t1 JOIN INFORMATION_SCHEMA.TABLES t2 ON t2.TABLE_SCHEMA=t1.TABLE_SCHEMA AND t2.TABLE_NAME=t1.TABLE_NAME WHERE t1.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema') GROUP BY TABLE_SCHEMA, TABLE_NAME having count(*)> 1; +--------------+--------------+------------+--------+----------------+ | # partitions | TABLE_SCHEMA | TABLE_NAME | ENGINE | CREATE_OPTIONS | +--------------+--------------+------------+--------+----------------+ | 5 | sbtest | sbtest1 | InnoDB | partitioned | | 5 | sbtest | sbtest2 | MyISAM | partitioned | +--------------+--------------+------------+--------+----------------+ 2 rows in set (0.03 sec) So far so good. Let’s upgrade to MySQL 5.7 Upgrade to MySQL 5.7 When we upgrade to MySQL 5.7, in the error log we can see this information after booting mysqld: Version: '5.7.20' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) 2017-11-22T12:48:49.265005Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check. 2017-11-22T12:48:49.265007Z 0 [Note] Beginning of list of non-natively partitioned tables 2017-11-22T12:48:49.322838Z 2 [Warning] The partition engine, used by table 'sbtest.sbtest1', is deprecated and will be removed in a future release. Please use native partitioning instead. 2017-11-22T12:48:49.323335Z 2 [Warning] The partition engine, used by table 'sbtest.sbtest2', is deprecated and will be removed in a future release. Please use native partitioning instead. 2017-11-22T12:48:49.323662Z 0 [Note] End of list of non-natively partitioned tables During the mysqld’s start process, MySQL verifies the type of the partitioned tables. We can see in the error log the [Warning] messages about our 2 tables using deprecated generic-partitions. Note that this check will be removed from the start process in the furute 5.7 releases. We can also verify this in Information_Schema: SELECT count(*) as '# partitions', t1.TABLE_SCHEMA, t1.TABLE_NAME, ANY_VALUE(ENGINE), ANY_VALUE(CREATE_OPTIONS) FROM INFORMATION_SCHEMA.PARTITIONS t1 JOIN INFORMATION_SCHEMA.TABLES t2 ON t2.TABLE_SCHEMA=t1.TABLE_SCHEMA AND t2.TABLE_NAME=t1.TABLE_NAME WHERE t1.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema') GROUP BY TABLE_SCHEMA, TABLE_NAME having count(*)> 1; +--------------+--------------+------------+-------------------+---------------------------+ | # partitions | TABLE_SCHEMA | TABLE_NAME | ANY_VALUE(ENGINE) | ANY_VALUE(CREATE_OPTIONS) | +--------------+--------------+------------+-------------------+---------------------------+ | 5 | sbtest | sbtest1 | InnoDB | partitioned | | 5 | sbtest | sbtest2 | MyISAM | partitioned | +--------------+--------------+------------+-------------------+---------------------------+ 2 rows in set, 2 warnings (0.00 sec) Please note that the query is a bit different than in 5.6. This time, we have 2 warning messages, let’s check them: mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1287 Message: The partition engine, used by table 'sbtest.sbtest1', is deprecated and will be removed in a future release. Please use native partitioning instead. *************************** 2. row *************************** Level: Warning Code: 1287 Message: The partition engine, used by table 'sbtest.sbtest2', is deprecated and will be removed in a future release. Please use native partitioning instead. 2 rows in set (0.00 sec) This is indeed how we can verify the tables using the old generic partition engine. How to upgrade to native partitioning ? There are several ways to migrate from the old engine to the new native one: running mysql_upgrade using ALTER TABLE … UPGRADE PARTITIONING force altering the table modifying the partition structure mysql_upgrade Once MySQL is started, you should (I would like to say must) run mysql_upgrade. During this process, we can see that the generic partition tables are identified and those in InnoDB are directly upgraded to the new native partition engine: [root@mysql1 sbtest]# mysql_upgrade Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Checking system database. mysql.columns_priv OK mysql.db OK mysql.engine_cost OK ... Checking databases. sbtest.sbtest1 error : Partitioning upgrade required. Please dump/reload to fix it or do: ALTER TABLE `sbtest`.`sbtest1` UPGRADE PARTITIONING warning : The partition engine, used by table 'sbtest.sbtest1', is deprecated and will be removed in a future release. Please use native partitioning instead. sbtest.sbtest2 OK warning : The partition engine, used by table 'sbtest.sbtest2', is deprecated and will be removed in a future release. Please use native partitioning instead. sys.sys_config OK Upgrading tables Running : ALTER TABLE `sbtest`.`sbtest1` UPGRADE PARTITIONING status : OK Upgrade process completed successfully. Checking if update is needed. Note that even if the process first advice you to upgrade the partitions of the InnoDB table, it does it automatically just after. After this process we can see on the filesystem that the .par file is now gone for the InnoDB table: [root@mysql1 sbtest]# ls -lh total 492M -rw-rw----. 1 mysql mysql 65 Nov 23 08:14 db.opt -rw-r-----. 1 mysql mysql 8.5K Nov 23 08:25 sbtest1.frm -rw-rw----. 1 mysql mysql 56M Nov 23 08:17 sbtest1#P#p0.ibd -rw-rw----. 1 mysql mysql 56M Nov 23 08:17 sbtest1#P#p1.ibd -rw-rw----. 1 mysql mysql 60M Nov 23 08:17 sbtest1#P#p2.ibd -rw-rw----. 1 mysql mysql 60M Nov 23 08:17 sbtest1#P#p3.ibd -rw-rw----. 1 mysql mysql 60M Nov 23 08:17 sbtest1#P#p4.ibd -rw-rw----. 1 mysql mysql 8.5K Nov 23 08:25 sbtest2.frm -rw-rw----. 1 mysql mysql 40 Nov 23 08:17 sbtest2.par -rw-rw----. 1 mysql mysql 36M Nov 23 08:17 sbtest2#P#p0.MYD -rw-rw----. 1 mysql mysql 3.9M Nov 23 08:17 sbtest2#P#p0.MYI -rw-rw----. 1 mysql mysql 36M Nov 23 08:17 sbtest2#P#p1.MYD -rw-rw----. 1 mysql mysql 3.9M Nov 23 08:17 sbtest2#P#p1.MYI -rw-rw----. 1 mysql mysql 37M Nov 23 08:17 sbtest2#P#p2.MYD -rw-rw----. 1 mysql mysql 4.0M Nov 23 08:17 sbtest2#P#p2.MYI -rw-rw----. 1 mysql mysql 37M Nov 23 08:17 sbtest2#P#p3.MYD -rw-rw----. 1 mysql mysql 4.0M Nov 23 08:17 sbtest2#P#p3.MYI -rw-rw----. 1 mysql mysql 37M Nov 23 08:17 sbtest2#P#p4.MYD -rw-rw----. 1 mysql mysql 4.0M Nov 23 08:17 sbtest2#P#p4.MYI The MyISAM table is still untouched. We can verify this again with I_S : SELECT count(*) as '# partitions', t1.TABLE_SCHEMA, t1.TABLE_NAME, ANY_VALUE(ENGINE), ANY_VALUE(CREATE_OPTIONS) FROM INFORMATION_SCHEMA.PARTITIONS t1 JOIN INFORMATION_SCHEMA.TABLES t2 ON t2.TABLE_SCHEMA=t1.TABLE_SCHEMA AND t2.TABLE_NAME=t1.TABLE_NAME WHERE t1.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema') GROUP BY TABLE_SCHEMA, TABLE_NAME having count(*)> 1; +--------------+--------------+------------+-------------------+---------------------------+ | # partitions | TABLE_SCHEMA | TABLE_NAME | ANY_VALUE(ENGINE) | ANY_VALUE(CREATE_OPTIONS) | +--------------+--------------+------------+-------------------+---------------------------+ | 6 | sbtest | sbtest1 | InnoDB | partitioned | | 5 | sbtest | sbtest2 | MyISAM | partitioned | +--------------+--------------+------------+-------------------+---------------------------+ 2 rows in set, 1 warning (4.21 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1287 Message: The partition engine, used by table 'sbtest.sbtest2', is deprecated and will be removed in a future release. Please use native partitioning instead. 1 row in set (0.17 sec) As MyISAM doesn’t support native partitioning, the table is untouched. ALTER TABLE … UPGRADE PARTITIONING If for any reason, you don’t want to run mysql_upgrade yet, you can upgrade to the InnoDB partitioned tables using ALTER TABLE... UPGRADE PARTITIONING This is a metadata change and it’s a quick operation: mysql> ALTER TABLE sbtest1 UPGRADE PARTITIONING; Query OK, 0 rows affected, 1 warning (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1287 Message: The partition engine, used by table 'sbtest.sbtest1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1 row in set (0.00 sec) Unfortunately, we see a warning. This warning comes from when the table is open. Next time it won’t happen anymore. ALTER TABLE ENGINE=InnoDB Exactly like above, just forcing the ENGINE=InnoDB again will also perform the change but this time the operation is also quick but touches also all the partitions and no warning: mysql> ALTER TABLE sbtest3 ENGINE=InnoDB; Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0 If we compare the filesystem we cab see that only the frm file is touched in case of ALTER … UPGRADE PARTITIONING, and all files are touched in case of ALTER … ENGINE=InnoDB. Tables was created at 08:47 and the ALTER was done on sbtest1 at 15:16. On sbtest3, the table was created at 9.00 and the ALTER was run at 15:24 : -rw-rw----. 1 mysql mysql 65 Nov 23 08:45 db.opt -rw-r-----. 1 mysql mysql 8632 Nov 23 15:16 sbtest1.frm -rw-rw----. 1 mysql mysql 58720256 Nov 23 08:47 sbtest1#P#p0.ibd -rw-rw----. 1 mysql mysql 58720256 Nov 23 08:47 sbtest1#P#p1.ibd -rw-rw----. 1 mysql mysql 62914560 Nov 23 08:47 sbtest1#P#p2.ibd -rw-rw----. 1 mysql mysql 62914560 Nov 23 08:47 sbtest1#P#p3.ibd -rw-rw----. 1 mysql mysql 62914560 Nov 23 08:47 sbtest1#P#p4.ibd -rw-r-----. 1 mysql mysql 8632 Nov 23 15:24 sbtest3.frm -rw-r-----. 1 mysql mysql 114688 Nov 23 15:24 sbtest3#P#p0.ibd -rw-r-----. 1 mysql mysql 114688 Nov 23 15:24 sbtest3#P#p1.ibd -rw-r-----. 1 mysql mysql 114688 Nov 23 15:24 sbtest3#P#p2.ibd -rw-r-----. 1 mysql mysql 114688 Nov 23 15:24 sbtest3#P#p3.ibd -rw-r-----. 1 mysql mysql 114688 Nov 23 15:24 sbtest3#P#p4.ibd Modifying the partition structure As soon as we touch at the partitioning structure, the metadata gets changed to the new native partition engine. For example, if we modify the partition in this case adding a new partition the table gets directly updated to native partition if the engine supports it: mysql> ALTER TABLE sbtest1 PARTITION BY KEY() partitions 6; Query OK, 1000000 rows affected (1 min 9.46 sec) Records: 1000000 Duplicates: 0 Warnings: 0 MySQL 8.0 So we saw that if we have partitioned tables in 5.6, if they use an engine supporting native partitioning, the tables will be modified during the upgrade process, manually or when there is some structure change. But if you have MyISAM partitioned tables, they will stay intact and you will still be able to use. But what if we upgrade to 8.0 as the warning message explicitly says it will removed in the next release ? In fact, if we keep MyISAM partitioned tables, MySQL 8.0 won’t start with messages like these in the error log: 2017-11-22T17:08:37.351519Z 2 [ERROR] /usr/sbin/mysqld: The 'partitioning' feature is not available; you need to remove '--skip-partition' or use MySQL built with '-DWITH_PARTITION_STORAGE_ENGINE=1' 2017-11-22T17:08:37.353183Z 2 [ERROR] /usr/sbin/mysqld: Can't find file: './sbtest/sbtest2.frm' (errno: 0 - Success) 2017-11-22T17:08:37.353210Z 2 [ERROR] Error in reading file ./sbtest/sbtest2.frm 2017-11-22T17:08:37.356218Z 2 [ERROR] Error in creating TABLE_SHARE from sbtest2.frm file. 2017-11-22T17:08:37.861365Z 0 [ERROR] Failed to Populate DD tables. 2017-11-22T17:08:37.862162Z 0 [ERROR] Aborting The only viable solution will be then to downgrade to 5.7 and change the table’s engine to InnoDB (or any other engine supporting native partitioning). If you plan to migrate to MySQL 8.0, don’t forget then to check if you still have some MyISAM partitioned tables: mysql> SELECT count(*) as '# partitions', t1.TABLE_SCHEMA, t1.TABLE_NAME, ANY_VALUE(ENGINE) ENGINE, ANY_VALUE(CREATE_OPTIONS) OPTIONS, CONCAT(ROUND((ANY_VALUE(t2.index_length)+ ANY_VALUE(t2.data_length))/1024/1024),'MB') AS SIZE FROM INFORMATION_SCHEMA.PARTITIONS t1 JOIN INFORMATION_SCHEMA.TABLES t2 ON t2.TABLE_SCHEMA=t1.TABLE_SCHEMA AND t2.TABLE_NAME=t1.TABLE_NAME WHERE ENGINE='myisam' AND t1.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema') GROUP BY TABLE_SCHEMA, TABLE_NAME having count(*)> 1 +--------------+--------------+------------+--------+-------------+-------+ | # partitions | TABLE_SCHEMA | TABLE_NAME | ENGINE | OPTIONS | SIZE | +--------------+--------------+------------+--------+-------------+-------+ | 5 | sbtest | sbtest2 | MyISAM | partitioned | 200MB | +--------------+--------------+------------+--------+-------------+-------+ 1 row in set, 1 warning (0.02 sec) I hope you will enjoy the new native partitions !

Dedicated Cloud Hosting for your business with Joomla ready to go. Launch your online home with CloudAccess.net.