Last week (09\/12\/2016) was announced the new MySQL 8.0.0 DMR (Development Milestone Release)
\nIt is now available for download at dev.mysql.com
\nI downloaded and installed it
\nFollowing are my first impression on some new features
\nFirst of all, when you remember the size of earlier versions, it is more & more gluttonous, see below
\nmysql-5.0.96-linux-x86_64-glibc23.tar.gz\u00a0\u00a0 \u00a0121.2M
\nmysql-5.1.72-linux-x86_64-glibc23.tar.gz\u00a0\u00a0 \u00a0127.8M
\nmysql-5.5.52-linux2.6-x86_64.tar.gz\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0177.2M
\nmysql-5.6.33-linux-glibc2.5-x86_64.tar.gz\u00a0\u00a0 \u00a0299.7M
\nmysql-5.7.15-linux-glibc2.5-x86_64.tar.gz\u00a0\u00a0 611.8M
\nmysql-8.0.0-dmr-linux-glibc2.12-x86_64.tar.gz is around 1Gb and uncompressed 3.6Gb (Oups…)<\/p>\n
At first sight , it seems that nothing has changed, same schemas and almost the same tables
\n– 2 new tables related to roles in the mysql schema : default_roles, role_edges
\n– 6 new tables in the information_schema & 6 also in the performance_schema<\/p>\n
When you scan your Data directory (datadir), you will find new “.SDI” files (Serialized Dictionary Information) for every schema except for MySQL Data dictionary<\/strong> has been improved, almost all the system tables have been moved from MyISAM to the transactional InnoDB storage engine to increase reliability The new feature “Invisible Indexes”<\/strong> is really great. As a temporary workaround, you can either start it manually or use the mysqld_multi from a previous version Last week (09\/12\/2016) was announced the new MySQL 8.0.0 DMR (Development Milestone Release) It is now available for download at dev.mysql.com I downloaded and installed it Following are my first impression on some new features First of all, when you remember the size of earlier versions, it is more & more gluttonous, see below mysql-5.0.96-linux-x86_64-glibc23.tar.gz\u00a0\u00a0 […]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[23,144],"type_dbi":[],"class_list":["post-8892","post","type-post","status-publish","format-standard","hentry","category-database-management","tag-dba","tag-mysql"],"acf":[],"yoast_head":"\n
\nmysql@MYSQL:\/u01\/mysqldata\/mysqld9\/ [mysqld9] ll *.SDI
\n-rw-r-----. 1 mysql mysql 215 Sep 13 21:34 employees_4.SDI
\n-rw-r-----. 1 mysql mysql 225 Sep 13 21:37 performance_sche_7.SDI
\n-rw-r-----. 1 mysql mysql 209 Sep 13 21:34 sys_6.SDI
\n-rw-r-----. 1 mysql mysql 209 Sep 13 21:34 TBS_3.SDI<\/code>
\nIf you have a look in one of these files, you will see that they all provide data dictionary information in a serialized form (JSON formatted text)
\nmysql@MYSQL:\/u01\/mysqldata\/mysqld9\/ [mysqld9] cat employees_4.SDI
\n{
\n\"sdi_version\": 1,
\n\"dd_version\": 1,
\n\"dd_object_type\": \"Schema\",
\n\"dd_object\": {
\n\"name\": \"employees\",
\n\"default_collation_id\": 8,
\n\"created\": 0,
\n\"last_altered\": 0
\n}<\/code>
\nOld “.frm” files containing metadata\u00a0 have been removed, the same for “.par” file (Partition definition), “.trg file” (Trigger parameter) and “.isl” file (InnoDB Symbolic Link)
\nWhen you create a new INNODB table, metadata are stored now in the InnoDB file (.ibd)
\nFor all the other storage engine (memory, CSV,Blackhole, etc…), we get now a new “.SDI” file but more complex
\nmysql@MYSQL:\/u01\/mysqldata\/mysqld9\/Newfeatures\/ [mysqld9] cat t3_617.SDI
\n{
\n\"sdi_version\": 1,
\n\"dd_version\": 1,
\n\"dd_object_type\": \"Table\",
\n\"dd_object\": {
\n\"name\": \"t3\",
\n\"mysql_version_id\": 80000,
\n\"created\": 20160914075838,
\n\"last_altered\": 20160914075838,
\n\"options\": \"avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_ auto_recalc=0;stats_sample_pages=0;\",
\n\"columns\": [
\n{
\n\"name\": \"c1\",
\n\"type\": 29,
\n\"is_nullable\": true,
\n\"is_zerofill\": false,
\n\"is_unsigned\": false,
\n\"is_auto_increment\": false,
\n\"is_virtual\": false,
\n\"hidden\": false,
\n\"ordinal_position\": 1,
\n\"char_length\": 20,
\n\"numeric_precision\": 0,
\n\"numeric_scale\": 0,
\n\"datetime_precision\": 0,
\n\"has_no_default\": false,
\n\"default_value_null\": true,
\n\"default_value\": \"\",
\n\"default_option\": \"\",
\n\"update_option\": \"\",
\n\"comment\": \"\",
\n\"generation_expression\": \"\",
\n\"generation_expression_utf8\": \"\",
\n\"options\": \"interval_count=0;\",
\n\"se_private_data\": \"\",
\n\"column_key\": 1,
\n\"column_type_utf8\": \"char(20)\",
\n\"elements\": [],
\n\"collation_id\": 8
\n}
\n],
\n\"schema_ref\": \"Newfeatures\",
\n\"hidden\": false,
\n\"se_private_id\": 18446744073709551615,
\n\"engine\": \"MEMORY\",
\n\"comment\": \"\",
\n\"se_private_data\": \"\",
\n\"row_format\": 1,
\n\"partition_type\": 0,
\n\"partition_expression\": \"\",
\n\"default_partitioning\": 0,
\n\"subpartition_type\": 0,
\n\"subpartition_expression\": \"\",
\n\"default_subpartitioning\": 0,
\n\"indexes\": [],
\n\"foreign_keys\": [],
\n\"partitions\": [],
\n\"collation_id\": 8
\n}<\/code>
\nIt is possible now to create roles<\/strong>, something we were waiting for years, that means you can define a set of privileges as a role and assign it to a user
\nmysqld9-(root@localhost) [Newfeatures]>create role tstrole;
\nmysqld9-(root@localhost) [Newfeatures]>grant all on employees.* to tstrole;
\nmysqld9-(root@localhost) [Newfeatures]>select host, user from mysql.user;
\n+---------------+-------------+
\n| host | user |
\n+---------------+-------------+
\n| % | tstrole |<\/code>
\nAs you can see, roles are stored in the mysql.user table meaning that you can assign a user to another user
\nmysqld9-(root@localhost) [Newfeatures]>show grants for tstrole;
\n+----------------------------------------------------------+
\n| Grants for tstrole@% |
\n+----------------------------------------------------------+
\n| GRANT USAGE ON *.* TO `tstrole`@`%` |
\n| GRANT ALL PRIVILEGES ON `employees`.* TO `tstrole`@`%` |
\n+----------------------------------------------------------+
\nmysqld9-(root@localhost) [Newfeatures]>grant tstrole to 'sme'@'localhost';
\nmysqld9-(root@localhost) [Newfeatures]>show grants for 'sme'@'localhost' using tstrole;
\n+----------------------------------------------------------------------------------+
\n| Grants for sme@localhost |
\n+----------------------------------------------------------------------------------+
\n| GRANT USAGE ON *.* TO `sme`@`localhost` |
\n| GRANT ALL PRIVILEGES ON `employees`.* TO `sme`@`localhost` |
\n| GRANT `tstrole`@`%` TO `sme`@`localhost` |
\n+----------------------------------------------------------------------------------+<\/code>
\nNow we connect as user “sme”
\nmysql -usme -p
\nmysql> use mysql
\nERROR 1044 (42000): Access denied for user 'sme'@'localhost' to database 'mysql'<\/code>
\nIt seems to work, as user “sme” has only access to the employees schema<\/p>\n
\nData dictionary tables are invisible,\u00a0 they do not appear in the output of SHOW TABLES and cannot be accessed directly
\nbut in most cases there are corresponding INFORMATION_SCHEMA tables that can be queried instead.<\/p>\n
\nYou can toggle\u00a0 now the visibility of an index as VISIBLE or INVISIBLE
\nWe know that because of unused indexes, performance of modifications (insert, updates) are reduced
\nAs they are also for the optimizer because they are taken in account for the plan selection
\nSo first check for unused indexes
\nmysqld9-(root@localhost) [information_schema]>SELECT * FROM sys.schema_unused_indexes;
\n+------------------+-------------------+---------------+
\n| object_schema | object_name | index_name |
\n+------------------+---- --------------+---------------+
\n| employees | departments | dept_name |
\n| employees | dept_manager | dept_no |
\n+------------------+-------------------+---------------+<\/code>
\nthen
\nmysqld9-(root@localhost) [employees]>alter table departments alter index dept_name INVISIBLE;
\n<\/code>
\nCheck in the data dictionary
\nmysqld9-(root@localhost) [employees]>SELECT * FROM information_schema.statistics WHERE is_visible='NO'G
\n*************************** 1. row ***************************
\nTABLE_CATALOG: def
\nTABLE_SCHEMA: employees
\nTABLE_NAME: departments
\nNON_UNIQUE: 0
\nINDEX_SCHEMA: employees
\nINDEX_NAME: dept_name
\nSEQ_IN_INDEX: 1
\nCOLUMN_NAME: dept_name
\nCOLLATION: A
\nCARDINALITY: NULL
\nSUB_PART: NULL
\nPACKED: NULL
\nNULLABLE:
\nINDEX_TYPE: BTREE
\nCOMMENT:
\nINDEX_COMMENT:
\nIS_VISIBLE: NO<\/strong><\/code>
\nYou can make now persistant<\/strong> global variables
\nselect @@max_connections;
\n+--------------------------+
\n| @@max_connections |
\n+--------------------------+
\n| 151 |
\n+--------------------------+
\nSET GLOBAL max_connections=200;<\/code>
\nIf you restart your Instance, such setting is lost. So now with
\nSET PERSIST max_connections=200;<\/code>
\nThis setting now will remain after an instance restart
\nselect @@max_connections;
\n+--------------------------+
\n| @@max_connections |
\n+--------------------------+
\n| 200 |
\n+--------------------------+<\/code>
\nLast but not least, be carefull when using MySQL 8.0.0.0 in a multi instance environment,
\nin my case 9 instances with different versions
\nI found a bug in mysqld_multi when you want to restart your instance 8.0.0.0
\nFirst I corrected the still not fixed Bug #77946 (https:\/\/bugs.mysql.com\/bug.php?id=77946)
\nin order to be able to stop my instance properly with mysqld_multi
\nI stopped it and then tried several times to restart it
\nNo way<\/strong>
\n2016-09-20T23:42:41.466540Z 1 [ERROR] InnoDB: Operating system error number 2 in a file operation.
\n2016-09-20T23:42:41.466562Z 1 [ERROR] InnoDB: The error means the system cannot find the path specified.
\n2016-09-20T23:42:41.466568Z 1 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
\n2016-09-20T23:42:41.466574Z 1 [ERROR] InnoDB: File .\/ibdata1: 'open' returned OS error 71. Cannot continue operation
\n2016-09-20T23:42:41.466582Z 1 [ERROR] InnoDB: Cannot continue operation.<\/code><\/p>\n
\nI reported this bug\u00a0 to MySQL (https:\/\/bugs.mysql.com\/83081)<\/p>\n","protected":false},"excerpt":{"rendered":"