Posted
almost 4 years
ago
by
Surabhi Bhat
Dear MySQL Users,
MySQL Cluster is the distributed, shared-nothing variant of MySQL. This
storage engine provides:
- In-Memory storage - Real-time performance
- Transparent Auto-Sharding - Read & write scalability
-
... [More]
Active-Active/Multi-Master geographic replication
- 99.999% High Availability with no single point of failure and
on-line maintenance
- NoSQL and SQL APIs (including C++, Java, http, Memcached and
JavaScript/Node.js)
MySQL Cluster 7.4 makes significant advances in performance; operational
efficiency (such as enhanced reporting and faster restarts and upgrades)
and conflict detection and resolution for active-active replication
between MySQL Clusters.
MySQL Cluster 7.4.29 has been released and can be downloaded from
http://www.mysql.com/downloads/cluster/
where you will also find Quick Start guides to help you get your first
MySQL Cluster database up and running.
The release notes are available from
http://dev.mysql.com/doc/relnotes/mysql-cluster/7.4/en/index.html
MySQL Cluster enables users to meet the database challenges of next
generation web, cloud, and communications services with uncompromising
scalability, uptime and agility.
More details can be found at
http://www.mysql.com/products/cluster/
Enjoy !
==============================================================================
Changes in MySQL NDB Cluster 7.4.29 (5.6.49-ndb-7.4.29) (2020-07-14, General Availability)
MySQL NDB Cluster 7.4.29 is a new release of MySQL NDB Cluster 7.4,
based on MySQL Server 5.6 and including features in version 7.4 of
the NDB storage engine, as well as fixing recently discovered bugs in
previous NDB Cluster releases.
Obtaining MySQL NDB Cluster 7.4. MySQL NDB Cluster 7.4 source code
and binaries can be obtained from
https://dev.mysql.com/downloads/cluster/.
For an overview of changes made in MySQL NDB Cluster 7.4, see What is
New in NDB Cluster 7.4
( https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-what-is-new-7-4.html ).
This release also incorporates all bug fixes and changes made in
previous NDB Cluster releases, as well as all bug fixes and feature
changes which were added in mainline MySQL 5.6 through MySQL 5.6.49
(see Changes in MySQL 5.6.49 (2020-07-13, General Availability)
( https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-49.html )).
Bugs Fixed
* During a node restart, the SUMA block of the node that is
starting must get a copy of the subscriptions (events with
subscribers) and subscribers (NdbEventOperation instances which
are executing) from a node already running. Before the copy is
complete, nodes which are still starting ignore any user-level
SUB_START or SUB_STOP requests; after the copy is done, they can
participate in such requests. While the copy operation is in
progress, user-level SUB_START and SUB_STOP requests are blocked
using a DICT lock. An issue was found whereby a starting node
could participate in SUB_START and SUB_STOP requests after the
lock was requested, but before it is granted, which resulted in
unsuccessful SUB_START and SUB_STOP requests. This fix ensures
that the nodes cannot participate in these requests until after
the DICT lock has actually been granted. (Bug #31302657)
* An excessive number of entries were written to the backup
log when performing an online backup, using ndbmtd on the data
nodes. This happened because row changes occurring to fragments
managed by LDM instance 1 were always recorded in the log, even
when they were for primary fragments other than primary
fragments. This wasted resources when restoring from the backup,
and could cause other problems when doing so, such as when using
staging tables for schema transforms while executing ndb_restore.
(Bug #31034270)
* The Dojo toolkit included with NDB Cluster and used by
the Auto-Installer was upgraded to version 1.15.3. (Bug
#31029110)
* A packed version 1 configuration file returned by
ndb_mgmd could contain duplicate entries following an upgrade to
NDB 8.0, which made the file incompatible with clients using
version 1. This occurs due to the fact that the code for handling
backwards compatibility assumed that the entries in each section
were already sorted when merging it with the default section. To
fix this, we now make sure that this sort is performed prior to
merging. (Bug #31020183)
* When executing any of the SHUTDOWN, ALL STOP, or ALL
RESTART management commands, it is possible for different nodes
to attempt to stop on different global checkpoint index (CGI)
boundaries. If they succeed in doing so, then a subsequent system
restart is slower than normal because any nodes having an earlier
stop GCI must undergo takeover as part of the process. When nodes
failing on the first GCI boundary cause surviving nodes to be
nonviable, surviving nodes suffer an arbitration failure; this
has the positive effect of causing such nodes to halt at the
correct GCI, but can give rise to spurious errors or similar. To
avoid such issues, extra synchronization is now performed during
a planned shutdown to reduce the likelihood that different data
nodes attempt to shut down at different GCIs as well as the use
of unnecessary node takeovers during system restarts. (Bug
#31008713)
On Behalf of MySQL Release Engineering Team,
Surabhi Bhat
[Less]
|
Posted
almost 4 years
ago
by
Sreedhar Sreedhargadda
Dear MySQL Users,
MySQL Cluster is the distributed, shared-nothing variant of MySQL.
This storage engine provides:
- In-Memory storage - Real-time performance (with optional
checkpointing to disk)
- Transparent Auto-Sharding - Read &
... [More]
write scalability
- Active-Active/Multi-Master geographic replication
- 99.999% High Availability with no single point of failure
and on-line maintenance
- NoSQL and SQL APIs (including C++, Java, http, Memcached
and JavaScript/Node.js)
MySQL Cluster 7.5.19 has been released and can be downloaded from
http://www.mysql.com/downloads/cluster/
where you will also find Quick Start guides to help you get your
first MySQL Cluster database up and running.
The release notes are available from
http://dev.mysql.com/doc/relnotes/mysql-cluster/7.5/en/index.html
MySQL Cluster enables users to meet the database challenges of next
generation web, cloud, and communications services with uncompromising
scalability, uptime and agility.
More details can be found at
http://www.mysql.com/products/cluster/
Enjoy !
==============================================================================
Changes in MySQL NDB Cluster 7.5.19 (5.7.31-ndb-7.5.19) (2020-07-14,
General Availability)
MySQL NDB Cluster 7.5.19 is a new release of MySQL NDB
Cluster 7.5, based on MySQL Server 5.7 and including features
in version 7.5 of the NDB
(https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html)
storage engine, as well as fixing recently discovered bugs in
previous NDB Cluster releases.
Obtaining MySQL NDB Cluster 7.5. MySQL NDB Cluster 7.5
source code and binaries can be obtained from
https://dev.mysql.com/downloads/cluster/.
For an overview of changes made in MySQL NDB Cluster 7.5, see
What is New in NDB Cluster 7.5
(https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-what-is-new-7-5.html).
This release also incorporates all bug fixes and changes made
in previous NDB Cluster releases, as well as all bug fixes
and feature changes which were added in mainline MySQL 5.7
through MySQL 5.7.31 (see Changes in MySQL 5.7.31 (2020-07-13,
General Availability)
(https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-31.html)).
Bugs Fixed
* During a node restart, the SUMA
(https://dev.mysql.com/doc/ndb-internals/en/ndb-internals-kernel-blocks-suma.html)
block of the node that is starting must get a copy
of the subscriptions (events with subscribers) and
subscribers (NdbEventOperation
(https://dev.mysql.com/doc/ndbapi/en/ndb-ndbeventoperation.html)
instances which are executing) from a node already
running. Before the copy is complete, nodes which
are still starting ignore any user-level SUB_START or
SUB_STOP requests; after the copy is done, they can
participate in such requests. While the copy operation is
in progress, user-level SUB_START and SUB_STOP requests
are blocked using a DICT
(https://dev.mysql.com/doc/ndb-internals/en/ndb-internals-kernel-blocks-dbdict.html)
lock. An issue was found whereby a starting node could
participate in SUB_START and SUB_STOP requests after the
lock was requested, but before it is granted, which
resulted in unsuccessful SUB_START and SUB_STOP requests.
This fix ensures that the nodes cannot participate in
these requests until after the DICT
(https://dev.mysql.com/doc/ndb-internals/en/ndb-internals-kernel-blocks-dbdict.html)
lock has actually been granted. (Bug #31302657)
* Statistics generated by NDB for use in tracking internal
objects allocated and deciding when to release them were
not calculated correctly, with the result that the
threshold for resource usage was 50% higher than
intended. This fix corrects the issue, and should allow
for reduced memory usage. (Bug #31127237)
* An excessive number of entries were written to the backup
log when performing an online backup, using ndbmtd on the
data nodes. This happened because row changes occurring
to fragments managed by LDM instance 1 were always
recorded in the log, even when they were for primary
fragments other than primary fragments. This wasted
resources when restoring from the backup, and could cause
other problems when doing so, such as when using staging
tables for schema transforms while executing ndb_restore.
(Bug #31034270)
* The Dojo toolkit included with NDB Cluster and used by
the Auto-Installer was upgraded to version 1.15.3. (Bug
#31029110)
* A packed version 1 configuration file returned by
ndb_mgmd could contain duplicate entries following an
upgrade to NDB 8.0, which made the file incompatible with
clients using version 1. This occurs due to the fact that
the code for handling backwards compatibility assumed
that the entries in each section were already sorted when
merging it with the default section. To fix this, we now
make sure that this sort is performed prior to merging.
(Bug #31020183)
* When executing any of the SHUTDOWN
(https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-mgm-client-commands.html#ndbclient-shutdown),
ALL STOP
(https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-mgm-client-commands.html#ndbclient-stop),
or ALL RESTART
(https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-mgm-client-commands.html#ndbclient-restart)
management commands, it is possible for different nodes
to attempt to stop on different global checkpoint index
(CGI) boundaries. If they succeed in doing so, then a
subsequent system restart is slower than normal because
any nodes having an earlier stop GCI must undergo
takeover as part of the process. When nodes failing on
the first GCI boundary cause surviving nodes to be
nonviable, surviving nodes suffer an arbitration failure;
this has the positive effect of causing such nodes to
halt at the correct GCI, but can give rise to spurious
errors or similar.
To avoid such issues, extra synchronization is now
performed during a planned shutdown to reduce the
likelihood that different data nodes attempt to shut down
at different GCIs as well as the use of unnecessary node
takeovers during system restarts. (Bug #31008713)
* The master node in a backup shut down unexpectedly on
receiving duplicate replies to a DEFINE_BACKUP_REQ
signal. These occurred when a data node other than the
master errored out during the backup, and the backup
master handled the situation by sending itself a
DEFINE_BACKUP_REF signal on behalf of the missing node,
which resulted in two replies being received from the
same node (a CONF signal from the problem node prior to
shutting down and the REF signal from the master on
behalf of this node), even though the master expected
only one reply per node. This scenario was also
encountered for START_BACKUP_REQ and STOP_BACKUP_REQ
signals.
This is fixed in such cases by allowing duplicate replies
when the error is the result of an unplanned node
shutdown. (Bug #30589827)
* A BLOB value is stored by NDB in multiple parts; when
reading such a value, one read operation is executed per
part. If a part is not found, the read fails with a row
not found error, which indicates a corrupted BLOB, since
a BLOB should never have any missing parts. A problem can
arise because this error is reported as the overall
result of the read operation, which means that mysqld
sees no error and reports zero rows returned.
This issue is fixed by adding a check specifically for
the case in which a blob part is not found. Now, when this
occurs, overwriting the row not found error with
corrupted blob, which causes the originating SELECT
(https://dev.mysql.com/doc/refman/5.7/en/select.html)
statement to fail as expected. Users of the NDB API
should be aware that, despite this change, the
NdbBlob::getValue()
(https://dev.mysql.com/doc/ndbapi/en/ndb-ndbblob-getvalue.html)
method continues to report the error as row not found
in such cases. (Bug #28590428)
* Incorrect handling of operations on replicas during node
restarts could result in a forced shutdown, or in content
diverging between replicas, when primary keys with
nonbinary (case-sensitive) equality conditions were used.
(Bug #98526, Bug #30884622)
On Behalf of Oracle/MySQL Release Engineering Team,
Sreedhar S
[Less]
|
Posted
almost 4 years
ago
by
Piotr Obrzut
Dear MySQL Users,
MySQL Cluster is the distributed, shared-nothing variant of MySQL.
This storage engine provides:
- In-Memory storage - Real-time performance (with optional
checkpointing to disk)
- Transparent Auto-Sharding - Read
... [More]
& write scalability
- Active-Active/Multi-Master geographic replication
- 99.999% High Availability with no single point of failure
and on-line maintenance
- NoSQL and SQL APIs (including C++, Java, http, Memcached
and JavaScript/Node.js)
MySQL Cluster 7.6.15 has been released and can be downloaded from
https://www.mysql.com/downloads/cluster/
where you will also find Quick Start guides to help you get your
first MySQL Cluster database up and running.
MySQL Cluster 7.6 is also available from our repository for Linux
platforms, go here for details:
https://dev.mysql.com/downloads/
The release notes are available from
https://dev.mysql.com/doc/relnotes/mysql-cluster/7.6/en/
MySQL Cluster enables users to meet the database challenges of next
generation web, cloud, and communications services with uncompromising
scalability, uptime and agility.
More details can be found at
https://www.mysql.com/products/cluster/
Enjoy !
==============================================================================
Changes in MySQL NDB Cluster 7.6.15 (5.7.31-ndb-7.6.15) (2020-07-14, General Availability)
MySQL NDB Cluster 7.6.15 is a new release of NDB 7.6, based
on MySQL Server 5.7 and including features in version 7.6 of
the NDB storage engine, as well as fixing recently discovered
bugs in previous NDB Cluster releases.
Obtaining NDB Cluster 7.6. NDB Cluster 7.6 source code and
binaries can be obtained from
https://dev.mysql.com/downloads/cluster/.
For an overview of changes made in NDB Cluster 7.6, see What
is New in NDB Cluster 7.6
(https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-what-is-new-7-6.html).
This release also incorporates all bug fixes and changes made
in previous NDB Cluster releases, as well as all bug fixes
and feature changes which were added in mainline MySQL 5.7
through MySQL 5.7.31 (see Changes in MySQL 5.7.31 (2020-07-13, General Availability)
(https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-31.html)).
Bugs Fixed
* NDB Disk Data: ndbmtd sometimes terminated unexpectedly
when it could not complete a lookup for a log file group
during a restore operation. (Bug #31284086)
* NDB Disk Data: An uninitialized variable led to issues
when performing Disk Data DDL operations following a
restart of the cluster. (Bug #30592528)
* ndb_restore --remap-column did not handle columns
containing NULL values correctly. Now any offset
specified by the mapping function used with this option
is not applied to NULL, so that NULL is preserved as
expected. (Bug #31966676)
* MaxDiskWriteSpeedOwnRestart was not honored as an upper
bound for local checkpoint writes during a node restart.
(Bug #31337487)
References: See also: Bug #29943227.
* During a node restart, the SUMA block of the node that is
starting must get a copy of the subscriptions (events
with subscribers) and subscribers (NdbEventOperation
instances which are executing) from a node already
running. Before the copy is complete, nodes which are
still starting ignore any user-level SUB_START or
SUB_STOP requests; after the copy is done, they can
participate in such requests. While the copy operation is
in progress, user-level SUB_START and SUB_STOP requests
are blocked using a DICT lock.
An issue was found whereby a starting node could
participate in SUB_START and SUB_STOP requests after the
lock was requested, but before it is granted, which
resulted in unsuccessful SUB_START and SUB_STOP requests.
This fix ensures that the nodes cannot participate in
these requests until after the DICT lock has actually
been granted. (Bug #31302657)
* DUMP 1001 (DumpPageMemoryOnFail) now prints out
information about the internal state of the data node
page memory manager when allocation of pages fails due to
resource constraints. (Bug #31231286)
* Statistics generated by NDB for use in tracking internal
objects allocated and deciding when to release them were
not calculated correctly, with the result that the
threshold for resource usage was 50% higher than
intended. This fix corrects the issue, and should allow
for reduced memory usage. (Bug #31127237)
* An excessive number of entries were written to the backup
log when performing an online backup, using ndbmtd on the
data nodes. This happened because row changes occurring
to fragments managed by LDM instance 1 were always
recorded in the log, even when they were for primary
fragments other than primary fragments. This wasted
resources when restoring from the backup, and could cause
other problems when doing so, such as when using staging
tables for schema transforms while executing ndb_restore.
(Bug #31034270)
* The Dojo toolkit included with NDB Cluster and used by
the Auto-Installer was upgraded to version 1.15.3. (Bug
#31029110)
* A packed version 1 configuration file returned by
ndb_mgmd could contain duplicate entries following an
upgrade to NDB 8.0, which made the file incompatible with
clients using version 1. This occurs due to the fact that
the code for handling backwards compatibility assumed
that the entries in each section were already sorted when
merging it with the default section. To fix this, we now
make sure that this sort is performed prior to merging.
(Bug #31020183)
* When executing any of the SHUTDOWN, ALL STOP, or ALL
RESTART management commands, it is possible for different
nodes to attempt to stop on different global checkpoint
index (CGI) boundaries. If they succeed in doing so, then
a subsequent system restart is slower than normal because
any nodes having an earlier stop GCI must undergo
takeover as part of the process. When nodes failing on
the first GCI boundary cause surviving nodes to be
nonviable, surviving nodes suffer an arbitration failure;
this has the positive effect of causing such nodes to
halt at the correct GCI, but can give rise to spurious
errors or similar.
To avoid such issues, extra synchronization is now
performed during a planned shutdown to reduce the
likelihood that different data nodes attempt to shut down
at different GCIs as well as the use of unnecessary node
takeovers during system restarts. (Bug #31008713)
* When responding to a SCANTABREQ, an API node can provide
a distribution key if it knows that the scan should work
on only one fragment, in which case the distribution key
should be the fragment ID, but in some cases a hash of
the partition key was used instead, leading to failures
in DBTC. (Bug #30774226)
* Several memory leaks found in ndb_import have been
removed. (Bug #30756434, Bug #30727956)
* The master node in a backup shut down unexpectedly on
receiving duplicate replies to a DEFINE_BACKUP_REQ
signal. These occurred when a data node other than the
master errored out during the backup, and the backup
master handled the situation by sending itself a
DEFINE_BACKUP_REF signal on behalf of the missing node,
which resulted in two replies being received from the
same node (a CONF signal from the problem node prior to
shutting down and the REF signal from the master on
behalf of this node), even though the master expected
only one reply per node. This scenario was also
encountered for START_BACKUP_REQ and STOP_BACKUP_REQ
signals.
This is fixed in such cases by allowing duplicate replies
when the error is the result of an unplanned node
shutdown. (Bug #30589827)
* When updating NDB_TABLE comment options using ALTER
TABLE, other options which has been set to non-default
values when the table was created but which were not
specified in the ALTER TABLE statement could be reset to
their defaults.
See Setting NDB_TABLE Options
(https://dev.mysql.com/doc/refman/5.7/en/create-table-ndb-table-comment-options.html),
for more information. (Bug #30428829)
* When, during a restart, a data node received a
GCP_SAVEREQ signal prior to beginning start phase 9, and
thus needed to perform a global checkpoint index write to
a local data manager's local checkpoint control file, it
did not record information from the DIH block originating
with the node that sent the signal as part of the data
written. This meant that, later in start phase 9, when
attempting to send a GCP_SAVECONF signal in response to
the GCP_SAVEREQ, this information was not available,
which meant the response could not be sent, resulting in
an unplanned shutdown of the data node. (Bug #30187949)
* Setting EnableRedoControl to false did not fully disable
MaxDiskWriteSpeed, MaxDiskWriteSpeedOtherNodeRestart, and
MaxDiskWriteSpeedOwnRestart as expected. (Bug #29943227)
References: See also: Bug #31337487.
* Removed a memory leak found in the ndb_import utility.
(Bug #29820879)
* A BLOB value is stored by NDB in multiple parts; when
reading such a value, one read operation is executed per
part. If a part is not found, the read fails with a row
not found error, which indicates a corrupted BLOB, since
a BLOB should never have any missing parts. A problem can
arise because this error is reported as the overall
result of the read operation, which means that mysqld
sees no error and reports zero rows returned.
This issue is fixed by adding a check specifically for
the case in wich a blob part is not found. Now, when this
occurs, overwriting the row not found error with
corrupted blob, which causes the originating SELECT
statement to fail as expected. Users of the NDB API
should be aware that, despite this change, the
NdbBlob::getValue() method continues to report the error
as row not found in such cases. (Bug #28590428)
* Incorrect handling of operations on replicas during node
restarts could result in a forced shutdown, or in content
diverging between replicas, when primary keys with
nonbinary (case-sensitive) equality conditions were used.
(Bug #98526, Bug #30884622)
On Behalf of MySQL Release Engineering Team
Piotr Obrzut [Less]
|
Posted
almost 4 years
ago
by
Justin Swanhart
MySQL 8.0.21 is out, and the WARP storage engine has been updated to support the changes made in the new version.
There are no significant functionality changes to the storage engine for MySQL 8.0.21, only the minor changes required to compile in
... [More]
the new version.
I will be posting benchmarks of WARP vs InnoDB and Column Store using the Star Schema Benchmark, DBT3, and some other comparison tests.
I will also be releasing a Docker image to make testing WARP easy. As always, binaries are available upon request. Keep in mind that WARP is a pluggable storage engine, and you can request just the plugin, or the whole MySQL 8 release, for version 8.0.19, 8.0.20, or 8.0.21.
[Less]
|
Posted
almost 4 years
ago
by
Frederic Descamps
MySQL 8.0.21 has been released today, wooohooo \o/
As usual, this release contains contributions and let me thanks all the contributors on behalf of the MySQL Team.
MySQL 8.0.21 includes contributions from Edgars Irmejs, Daniël van Eeden
... [More]
, Jeremy Cole, Wenfeng Shih, Billy O’Neal, Lou Shuai, Tsubasa Tanaka and Facebook.
Thank you all for your great contributions. MySQL is an Open Source project, GPL, and we accept contributions !
Here is the list of the contributions above:
Filesort chosen where index should’ve been, using LIMIT, contribution by Edgars Irmejs
Use mysql_config for –with-mysql-capi= when using –static, contribution by Daniël van Eeden
Better error in comp_err with blank space only lines, contribution by Facebook, and some others…
Dangerous optimization reconsidering_access_paths_for_index_ordering, contribution by Jeremy Cole
Implement context manager, contribution by Wenfeng Shih
Include missing headers breaking VS2019 version 16.6 nightly, contribution by Billy O’Neal
mysql-connector-cpp cmake files not work well when as a third party, contribution by Lou Shuai
a security contribution from Tsubasa Tanaka
If you have patches and you also would like to be part of the MySQL Contributors, you can do so from MySQL’s GitHub repository (requires signing the Oracle Contributor Agreement).
Thank you again for the contributions and see you for the next release !
[Less]
|
Posted
almost 4 years
ago
by
Dave Stokes
MySQL 8.0.21 was released today and one of the many new features is the JSON_VALUE() function. The main motivation is to ease index creation values from JSON data but there is more to it.JSON_VALUE() finds a specified scalar JSON value in JSON data
... [More]
and returns it as a SQL value.Examples
I will use the mysql_x example database data for examples. So let us start with getting the life expectancy data.SELECT JSON_EXTRACT(doc, "$.demographics.LifeExpectancy") AS raw FROM countryinfo LIMIT 4;+--------------------+| raw |+--------------------+| 78.4000015258789 || 45.900001525878906 || 38.29999923706055 || 76.0999984741211 |+--------------------+That is great information if not exactly human eye friendly.We can use JSON_VALUE() to make it a little easier for humansSELECT JSON_VALUE(doc, "$.demographics.LifeExpectancy" RETURNING DECIMAL(6,2)) AS trimmed FROM countryinfo LIMIT 4;+---------+| trimmed |+---------+| 78.40 || 45.90 || 38.30 || 76.10 |+---------+And it can be very useful in a WHERE clause. In this example there is no RETURNING clause.SELECT doc->"$.Name" FROM countryinfo WHERE JSON_VALUE(doc, "$.demographics.LifeExpectancy" RETURNING DECIMAL(6,3)) > 80.1;+---------------+| doc->"$.Name" |+---------------+| "Andorra" || "Japan" || "Macao" || "San Marino" |+---------------+The optional RETURN clause will cast your data as FLOAT, DOUBLE, DECIMAL, SIGNED,UNSIGNED, DATE, TIME, DATETIME, CHAR, or JSON.There are even on empty and on error clauses.SELECT JSON_VALUE('{firstname:"John"}', '$.lastname' DEFAULT 'No last name found' ON ERROR) AS "Last Name";+--------------------+| Last Name |+--------------------+| No last name found |+--------------------+And of course it can be used to help define indexes.CREATE TABLE xdemo (j JSON, x INTEGER, key((json_value(j, '$.id'))) );All opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him
[Less]
|
Posted
almost 4 years
ago
by
Dave Stokes
Recently the MySQL 8.0 Developer Certification became available joining the MySQL 8.0 Developer Certifications. I have recently seen posts on various social media sites asking what is on the exam, is there an exam guide, and what do you 'really
... [More]
need to know' to pass the exam. Both tests are very tough. Neither is going to be passed by rote memorization as many of the questions require analysis of system output before trying to adjust for the desired result. There are no true or false questions and no essays. What you will get is a series of questions written by subject matter experts that range from testing basic conceptual knowledge to very tough assessments of how to fix problems. BTW the exam software lets you mark questions for later review and it does help to bypass the tougher questions for later review at the end of the test. Are the question tricky? No, not really. They really require in depth knowledge of MySQL 8.0. If you have ever interviewed someone for a DBA job you know there are certain points of knowledge (pain points) that a candidate MUST have to be trusted with your precious data. Imagine going to your physician and having to identify which body part is a knee and which part is an elbow. I personally would trust anyone with either the 8.0 DBA or 8.0 Developer certification to know their stuff. Certification guide? Nope, sorry. Back in the 5.0 days there was a team effort to produce a comprehensive exam guide. But 5.0 was a long time ago and there was an effort back in the 5.6 days for a resurrected guide that did not come to fruition. And having written a technical book, I can assure you that any such work is like writing in sand on a busy holiday vacation spot beach as the content is constantly changes and the community is adding or dropping tools continually. I highly recommend Oracle's classes like MySQL For Developers and from regular reviews of the course materials on my part tell me that the curriculum team does a fantastic job conveying the complex material way in an easy to consume package of literature and exercises. I still recommend practicing the exercises for several weeks after the class before taking the exam. Exam crams? Do not waste you money. The 'dumps' I have examined in the past are either poor third-hands recitations of poor interpretations of what was on a test or bad copies from the manual. How to Have a Good Chance to Pass the Exam
Review the exam topics below, using it as a check list. Go to the MySQL Manual enter and read the sections for that area. Make sure you understand any examples and please do try them on your own scratch server. Please write down notes in a notebook if you think having a consolidated review tool is handy for you. Also check the MySQL Server Team blog and Planet.mysql.com for blogs from other Oracle MySQL Teams. And if you have questions please ask away on forums.mysql.com and mysqlcommunity.slack,com. I monitor the Certification board on the forums if you have questionsThe Exam Topics
DBA Certification
Architecture
Configure client connections to the server
Understand how MySQL stores data
Understand how InnoDB stores data and logs
Configure buffers and caches
Understand and use the Data Dictionary
Security
Create user accounts and roles
Use authentication plug-ins
Control user and role permissions
Recognize common security risks
Secure MySQL server connections
Provide password and login security
Secure the MySQL host environment
Prevent SQL injection attacks
Encrypt MySQL data
Configure MySQL Enterprise Firewall
Query Optimization
Examine how MySQL optimizes queries
Analyze queries with MySQL Enterprise Monitor
Create indexes to improve server performance
Monitor and understand index statistics
High Availability Techniques
Explain how replication provides high availability and scalability
Configure replication
Explain the role of the binary log in replication
Configure multisource replication
Explain the role of replication threads
Monitor and troubleshoot replication
Describe MySQL InnoDB cluster and Group Replication
Configure a MySQL InnoDB cluster
Perform an InnoDB cluster recovery
Developer Certification
Connectors and APIs
Choose between connectors for a given application
Demonstrate connector use, management and configuration
Retrieve data from the database by using a connector
Handle special values
Secure credentials and connections
MySQL Schema Objects and Data
Design, create, and alter views
Store and process string data
Store and process numeric data
Store and process temporal data
Store and process spatial data
Query Optimization
Use indexes for optimization
Analyze queries for optimization
Rewrite queries for optimization
JSON and Document Store
Create and store JSON documents
Process data in JSON documents
Explain application development with NoSQL and XDevAPI
Create and access a document store
Use MySQL Shell to access document stores
Data-driven Applications
Use prepared statements
Set SQL Modes to change MySQL behavior
Handle and interpret errors and warnings
Display query results
Aggregate and summarize data
Advanced Report Generation
Transactions
Control transactions in SQL
Control transactions in applications
Resolve consistency problems with isolation levels
Understand locking mechanisms within MySQL
MySQL Stored Programs
Create and execute stored routines
Explain the programming constructs used in stored routines
Create and execute triggers
Schedule database operations
Server Installation and Configuration
Install and use the MySQL server and client programs
Identify the files and folders created during installation
Start and stop MySQL
Upgrade MySQL
Configure MySQL by using options and option files
Configure MySQL variables
Launch multiple MySQL servers on the same host
Monitoring and Maintenance
Configure and view MySQL log files
Monitor MySQL processes and status
Configure MySQL Enterprise Audit
Use MySQL Enterprise Monitor to view activity in MySQL
Monitor database growth and explain capacity planning
Troubleshoot problems with locked resources
Backups and Recovery
Distinguish between the different types of backup
Implement a backup strategy
Backup and restore data with MySQL Enterprise Backup
Use mysqldump and mysqlpump to perform logical backups
Explain when and how to use raw file backups
Back up the binary log
All opinions expressed in this blog are those of Dave Stokes who is actually amazed to find anyone else agreeing with him
[Less]
|
Posted
almost 4 years
ago
by
Surabhi Bhat
Dear MySQL users,
MySQL Connector/J 8.0.21 is the latest General Availability release of
the MySQL Connector/J 8.0 series. It is suitable for use with MySQL
Server versions 8.0, 5.7, and 5.6. It supports the Java Database
Connectivity (JDBC)
... [More]
4.2 API, and implements the X DevAPI.
In the documentation for MySQL 8.0.21, we have started
changing the term "master" to "source", the term "slave" to
"replica", the term "whitelist" to "allowlist", and the term
"blacklist" to "blocklist". There are currently no changes to
the product's syntax, so these terms are still present in the
documentation where the current code requires their use. See
the blog post MySQL Terminology Updates
(https://mysqlhighavailability.com/mysql-terminology-updates/)
for more information.
This release includes the following new features and changes, also
described in more detail on
https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-21.html
As always, we recommend that you check the "CHANGES" file in the
download archive to be aware of changes in behavior that might affect
your application.
To download MySQL Connector/J 8.0.21 GA, see the "General Availability
(GA) Releases" tab at http://dev.mysql.com/downloads/connector/j/
Enjoy!
Changes in MySQL Connector/J 8.0.21 (2020-07-13, General Availability)
* Functionality Added or Changed
* Bugs Fixed
Functionality Added or Changed
* X DevAPI: The JSON Schema Validation Functions
( https://dev.mysql.com/doc/refman/8.0/en/json-validation-functions.html )
on MySQL servers are now supported by
Connector/J; see Schema Validation for details.
* The required versions of the 3rd-party libraries needed
for running or compiling Connector/J have been changed;
new requirements for additional libraries have also been
added. See Installing Connector/J from a Binary
Distribution
( https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-binary-installation.html )
and Installing from Source
( https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-installing-source.html )
for details.
Bugs Fixed
* When trying to set a parameter for a PreparedStatement
using the method
PreparedStatement.setObject(parameterIndex, "false",
Types.BOOLEAN), the value was set to true instead of
false. (Bug #30911870, Bug #98237)
On Behalf of MySQL Release Engineering Team,
Surabhi Bhat
[Less]
|
Posted
almost 4 years
ago
by
Nawaz Nazeer ahamed
Dear MySQL users,
The MySQL developer tools team announces 8.0.21 as our General Availability
(GA) for MySQL Workbench 8.0.
For discussion, join the MySQL Workbench Forums:
http://forums.mysql.com/index.php?152
The release is now available in
... [More]
source and binary form for a number of
platforms from our download pages at:
http://dev.mysql.com/downloads/tools/workbench/
Enjoy!
Changes in MySQL Workbench 8.0.21 (2020-07-13, General
Availability)
Functionality Added or Changed
* A new check box was added to the dialog for resetting an
expired password. When selected it indicates that the server
version in use precedes MySQL 5.7. (Bug #30799058)
* MySQL Workbench now supports the following MySQL 8.0.19
and 8.0.20 server language features:
+ DROP CHECK, ALTER CONSTRAINT, and DROP CONSTRAINT in
ALTER TABLE statements.
+ Locking clause for query expressions.
+ Table values constructor.
+ Short table select syntax.
+ New requirements for the CHANGE MASTER replication
statement (row format, primary key check).
+ Failed login attempts and password lock time in
ALTER USER statements.
+ Format support for EXPLAIN ANALYZE statements.
+ New and changed keywords:
o ARRAY
o FAILED_LOGIN_ATTEMPTS
o MASTER_COMPRESSION_ALGORITHM
o MASTER_TLS_CIPHERSUITES
o MASTER_ZSTD_COMPRESSION_LEVEL
o MEMBER
o OFF
o PASSWORD_LOCK_TIME
o PRIVILEGE_CHECKS_USER
o RANDOM
o REQUIRE_ROW_FORMAT
o REQUIRE_TABLE_PRIMARY_KEY_CHECK
o STREAM
o TIMESTAMP
o TIME
Bugs Fixed
* MySQL Workbench Migration Wizard encountered type -154
(SQL_SS_TIME2) while copying Microsoft SQL Server tables, and it
returned an error on the unhandled type. This fix adds the
following new SQL data types to the wizard: SQL_SS_TIME2
(supported by MySQL) and SQL_SS_TIMESTAMPOFFSET (not supported).
(Bug #30884140)
* The TableRead, TableInsert, TableUpdate, and TableDelete
events for each table touched by a query did not include complete
information in the Audit Inspector. (Bug #30807539)
* Valid TABLE and VALUES statements were identified as
having syntax errors in the SQL editor. (Bug #30775372,
Bug #98266, Bug #30775008, Bug #98263)
* MySQL accounts created during the prerequisite setup
using the Create MEB Account button were not granted sufficient
privilege to schedule an online backup or to execute a full
backup on Windows. (Bug #30774786)
* The Table Data Import Wizard returned an error when used
to import a large JSON file, rather than loading the file.
(Bug #30733445)
* Text selected in the SQL editor was blocked by the
highlight color. (Bug #30369329, Bug #97070)
* A change made to the CREATE statement to rename an
existing stored procedure did not revise the corresponding name
in the generated DROP PROCEDURE statement when the change was
applied using the editor. This fix modifies the generated
statement to no longer drop the original stored procedure after
it is renamed. (Bug #30103689, Bug #96289)
* On Windows, statements that do not generate a result set,
such as INSERT or UPDATE statements, caused MySQL Workbench to
close unexpectedly after the EXPLAIN operation was executed and
then the Result Grid output type was selected. (Bug #27576669,
Bug #89745)
* Although the installation and file path were valid, MySQL
Workbench did not find the ogr2ogr and ogrinfo files when loading
spatial data. (Bug #26447395, Bug #87074)
* A column created with NULL ON UPDATE CURRENT_TIMESTAMP(6)
prevented the MySQL Table Editor from showing table information,
such as indexes, views, foreign keys, and so on. (Bug #26388250,
Bug #86906)
* Memory use on a Windows host was excessive when a large
result set was exported to a file, causing MySQL Workbench to
become unresponsive. (Bug #26139027, Bug #86430)
* Synchronizing a database that included a table with JSON
fields to the data model produced new, separate tables for the
JSON data, instead of updating the original table. In this
instance, the target MySQL version set in the modeling
preference, 5.6.30 by default, does not support the operation.
This fix sets the default version to MySQL 8.0, but the value can
be adjusted by selecting Preferences, Modeling, and then MySQL
from the Edit menu. (Bug #25788105, Bug #85648)
* The zoom-out button did not alter the zoom level of
spatial data when viewed from a Linux host, unless the zoom level
was reset to the outermost level first. (Bug #25723313,
Bug #85444)
* MySQL Workbench became unresponsive for several minutes
after a server startup attempt was made on macOS hosts.
(Bug #25077227, Bug #83769)
* No errors or warnings were reported when scheduling a
backup with MySQL Enterprise Backup, although the backup did not
execute as scheduled. (Bug #23562328, Bug #81788)
* A Sybase database migration with the ODBC (FreeTDS)
connection method produced an exception when the connection was
tested. (Bug #23345282, Bug #81546)
* MySQL Workbench server administration could not acquire
management access to Oracle Solaris. For a list of supported
platforms, visit
https://www.mysql.com/support/supportedplatforms/workbench.html.
(Bug #22453133, Bug #72204)
* On Linux, edits made to values in the result grid were
lost when using the pointer device to move within the grid,
rather than pressing the Enter or Tab key to change the focus.
(Bug #20742151, Bug #76387)
* Forward engineering a valid MySQL Workbench model was
unsuccessful when the operation was performed using a MySQL 5.5
server instance. (Bug #20637664)
* On macOS, a query containing SELECT @@hostname returned
the column header, but the query did not show any visible output
unless the first row below the column header was selected.
(Bug #20540937)
* After reverting changes made to triggers in a schema that
had been saved and synchronized, MySQL Workbench was not able to
locate any differences when the reverted schema was
re-synchronized with the model. (Bug #20222031)
* A connection saved with MySQL Workbench Migration Wizard
was no longer visible in the wizard after MySQL Workbench was
upgraded to a later version. An attempt to save the connection
again, using the same name, returned an error indicating that the
original connection was preserved. (Bug #18454574)
* No connection could be made to the PostgreSQL database
system when it was selected as the source in MySQL Workbench
Migration Wizard on macOS. (Bug #17299989)
On Behalf of Oracle/MySQL Release Engineering Team,
Nawaz Nazeer Ahamed
[Less]
|
Posted
almost 4 years
ago
by
Tvarita Jain
Dear MySQL users,
MySQL Server 5.6.49, a new version of the popular Open Source Database
Management System, has been released. MySQL 5.6.49 is recommended for
use on production systems.
For an overview of what's new in MySQL 5.6, please see
... [More]
http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html
Starting with 5.6.11, Microsoft Windows packages for MySQL 5.6 are
available both as a "full" installer and as a "web" installer. The
full installer is significantly larger and comes bundled with the
latest software releases available. This bundle makes it easy to
download and configure a full server and development suite.
The web installer doesn't come bundled with any actual products and
instead relies on download-on-demand to fetch only the products you
choose to install. This makes the initial download much smaller but
increases install time as the individual products will need to be
downloaded.
For information on installing MySQL 5.6.49 on new servers or upgrading
to MySQL 5.6.49 from previous MySQL releases, please see
http://dev.mysql.com/doc/refman/5.6/en/installing.html
MySQL Server 5.6.49, is available in source and binary form for a number
of platforms from our download pages at
http://dev.mysql.com/downloads/mysql
We welcome and appreciate your feedback, bug reports, bug fixes,
patches, etc:
http://bugs.mysql.com/report.php
The following link lists the changes in the MySQL 5.6 since the release
of MySQL 5.6.48. It may also be viewed online at
http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-49.html
==============================================================================
Changes in MySQL 5.6.49 (2020-07-13, General Availability)
* Configuration Notes
* Security Notes
* Bugs Fixed
Configuration Notes
* tcmalloc is no longer a permitted value for the
mysqld_safe --malloc-lib option. (Bug #31372027)
Security Notes
* The linked OpenSSL library for MySQL Server has been
updated to version 1.1.1g. Issues fixed in the new OpenSSL
version are described at https://www.openssl.org/news/cl111.txt
and https://www.openssl.org/news/vulnerabilities.html. (Bug
#31296697)
Bugs Fixed
* Partitioning: A query against a partitioned table, which
used an ORDER BY, returned unordered results under the following
conditions:
+ The table had a composite index with a prefix on one of the
columns.
+ The query's WHERE clause contained an equality condition on
the prefixed column.
+ The column with the prefix was the leftmost column in the
index.
+ The column used in the ORDER BY was the rightmost column in
the index.
+ The index was used for handling the ORDER BY. Our thanks to
Quanan Han for the contribution. (Bug #84070, Bug #25207522)
* Replication: When a replication source server shuts down
and restarts, its MEMORY tables become empty. To replicate this
effect to replicas, the first time that the source uses a given
MEMORY table after startup, it notifies replicas that the table
must be emptied by writing a DELETE statement for that table to
the binary log. Previously, the generated DELETE statement was
written to the binary log statement cache for the current
session, which could result in it being logged together with
other statements under the same GTID, or logged without BEGIN and
COMMIT statements. Also, in some situations, the generated DELETE
statement could consume the GTID intended for the transaction
that triggered it. The generated DELETE statement is now logged
with accompanying BEGIN and COMMIT statements, and the resulting
transaction is flushed to the binary log immediately after it is
written to the statement cache, so that it always receives its
own GTID and is kept separate from other transactions. (Bug
#30527929, Bug #25681518, Bug #77729)
* Privileges on the INFORMATION_SCHEMA.FILES table were
being checked incorrectly. (Bug #30350829)
* mysql_store_result() could fail to detect invalid data
packets. (Bug #29921423)
* The server sometimes mistakenly removed a subquery with a
GROUP BY when optimizing a query, even in some cases when this
subquery was used by an outer select. This could occur when the
subquery also used an aggregate function. (Bug #28240054)
On behalf of the Release Engineering Team,
Tvarita Jain
[Less]
|