MySQL Community Server 5.7.9 (GA) has been released (part 1/2)

[Bjorn Munch]

Dear MySQL users,

MySQL Server 5.7.9 (GA) is a new version of the world's
most popular open source database. This is the first official release
of MySQL 5.7.

[Due to size limitations on forum/email, this announcement had to be
split into two parts. This is part 1.]

The new features in this release are now deemed to be of Release quality. 

Note that 5.7.9 includes all features in MySQL 5.6 and previous 5.7 
Development Milestone Releases. An overview of what's new in MySQL 5.7 
is available online at 

http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

For information on installing MySQL 5.7.9 on new servers, please see
the MySQL installation documentation at

  http://dev.mysql.com/doc/refman/5.7/en/installing.html

MySQL Server 5.7.9 is available in source and binary form for a number of
platforms from the "Development Releases" selection of our download
pages at

  http://dev.mysql.com/downloads/mysql/

MySQL Server 5.7.9 is also available from our repository for Linux
platforms, go here for details:

  http://dev.mysql.com/downloads/repo/

Windows packages are available via the Installer for Windows or .ZIP
(no-install) packages for more advanced needs. The point and click
configuration wizards and all MySQL products are available in the
unified Installer for Windows:

  http://dev.mysql.com/downloads/installer/

5.7.9 also comes with a web installer as an alternative to the full
installer.

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.

We welcome and appreciate your feedback, bug reports, bug fixes,
patches, etc.:

  http://bugs.mysql.com/report.php

The following section lists the changes in MySQL 5.7.9 since the 5.7.8
Release Candidate.

  http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-9.html

Enjoy!


==
Changes in MySQL 5.7.9 (2015-10-21)


 * Audit Log Plugin Notes

 * C API Notes

 * Compilation Notes

 * Packaging Notes

 * Performance Schema Notes

 * Plugin Notes

 * Security Notes

 * Spatial Data Support

 * sys Schema Notes

 * Functionality Added or Changed

 * Bugs Fixed

   Audit Log Plugin Notes

 * Some events cannot be terminated. Previously, if an audit
   plugin returned nonzero status for a nonterminable event,
   the server ignored the status and continued processing
   the event. However, if an audit plugin used the
   my_message() function to terminate a nonterminable event,
   a server exit occurred. Now the server correctly handles
   termination of nonterminable events using my_message().
   (Bug #21458066)

   C API Notes

 * The mysql_options() C API function has two new options,
   MYSQL_OPT_MAX_ALLOWED_PACKET and
   MYSQL_OPT_NET_BUFFER_LENGTH, that set the
   max_allowed_packet and net_buffer_length system
   variables, respectively. Each option name also now can be
   passed to the mysql_get_option() C API function to
   retrieve its value. For more information, see
   mysql_options()
   (http://dev.mysql.com/doc/refman/5.7/en/mysql-options.html),
   and mysql_get_option()
   (http://dev.mysql.com/doc/refman/5.7/en/mysql-get-option.html).
   (Bug #20821550)

 * Previously, it was necessary to call mysql_thread_end()
   for each mysql_thread_init() call to avoid a memory leak.
   C API internals have been reimplemented to reduce the
   amount of information allocated by mysql_thread_init()
   that must be freed by mysql_thread_end():

  + For release/production builds without debugging
support enabled, mysql_thread_end() need not be
called.

  + For debug builds, mysql_thread_init() allocates
debugging information for the DBUG package (see The
DBUG Package
(http://dev.mysql.com/doc/refman/5.7/en/dbug-package.html)).
mysql_thread_end() must be called for each
mysql_thread_init() call to avoid a memory leak.
   (Bug #20621281, Bug #21802367)

   Compilation Notes

 * MySQL distributions now contain a mysqlclient.pc file
   that provides information about MySQL configuration for
   use by the pkg-config command. This enables pkg-config to
   be used as an alternative to mysql_config for obtaining
   information such as compiler flags or link libraries
   required to compile MySQL applications. For more
   information, see Building C API Client Programs Using
   pkg-config
(http://dev.mysql.com/doc/refman/5.7/en/c-api-building-clients-pkg-config.html).
   A new INSTALL_PKGCONFIGDIR CMake option is available to
   specify the directory in which to install the
 

MySQL Community Server 5.7.9 (GA) has been released (part 2/2)

[Bjorn Munch]

[This is part 2 of the announcement]

   Bugs Fixed, cont.

 * Replication: The group replication applier channel does
   not support DATABASE as the slave_parallel_type; when
   group replication is started, this is checked for
   explicitly, and handled correctly. However, it remained
   possible to change this value indirectly at a later point
   in time by increasing the value of slave_parallel_workers
   while the slave SQL thread was stopped, which caused the
   applier to fail with an error. To fix this problem, the
   slave_parallel_type for the group_replication_applier is
   now checked to make sure that it is set to LOGICAL_CLOCK
   whenever the number of slave_parallel_workers is set
   greater then 0, and not merely when group replication is
   first started. (Bug #21798804)

 * Replication: As binlog_error_action=ABORT_SERVER is the
   default in MySQL 5.7.7 and later it is being used for
   more error situations. The behavior has been adjusted to
   generate a core dump to improve troubleshooting
   possibilities. (Bug #21486161, Bug #77738)

 * Replication: At runtime, some Gtid_set objects could be
   instrumented with a performance schema mutex key equal to
   0 (which is invalid), due to its use as the effective
   default value when the mutex key was not actually
   supplied. This allowed these objects to be created
   without a valid key, which led to further issues when
   using them. (Bug #21485997)

 * Replication: When running the server with gtid_mode=ON, a
   DELETE from a MEMORY table following a restart was not
   written to the binary log correctly. (Bug #21045848)

 * Replication: The locking behavior of replication
   administration statements has changed to make SHOW SLAVE
   STATUS more concurrent. This makes the NONBLOCKING clause
   redundant for SHOW SLAVE STATUS and it has been removed.
   (Bug #20593028)

 * Replication: ER_CANT_USE_AUTO_POSITION_WITH_GTID_MODE_OFF
   errors were not reported using the correct format. (Bug
   #20545943)

 * Replication: When the dump thread was killed while
   dumping an inactive binary log, some events in this log
   could be skipped and thus not replicated. (Bug #78337,
   Bug #21816399)
   References: See also Bug #74607, Bug #19975697.

 * Replication: XA transactions could cause an assert
   condition on XA COMMIT; this was happening because the
   internal transaction state was not reset between XA
   PREPARE and XA COMMIT or XA ROLLBACK, due to the fact
   that these operations constitute separate transactions
   under XA. In addition, XA ROLLBACK statements were not
   handled properly in some cases. (Bug #78264, Bug
   #21755890)

 * Replication: The interface between the Group Replication
   plugin and the Performance Schema engine made use of a
   type of memory allocation which was passed to the server,
   and was a potential source of problems when passing
   information between the plugin and performance_schema
   tables. The implementation for this interface has been
   reworked so as to avoid performing this type of memory
   allocation when sharing data. (Bug #78263, Bug #21755699)

 * Replication: The MTS submode set for each channel was
   ignored by the worker threads, which continued to read
   and use the global flag set for all slave channels. This
   could lead to errors when the coordinator was of one type
   and its workers of another. (Bug #77763, Bug #21464737)

 * Replication: Replication slaves could fail for having
   insufficient privileges when they had been granted only
   the REPLICATION SLAVE privilege. (Bug #77732, Bug
   #21455603)

 * Replication: The status variable Slave_open_temp_tables
   keeps track of the number of temporary tables that are
   opened by the replication slave. If multi-source
   replication is enabled, it is the total number of
   temporary tables for all channels. This fix addresses the
   following issues relating to this variable:

  + RESET SLAVE FOR CHANNEL channel forced the value of
Slave_open_temp_tables to 0; in the event that some
other replication channel had open temporary tables
which were later dropped, the value wrapped around
to a large negative value (1 - 2^32). This also
caused spurious or missed warnings when issuing a
STOP SLAVE or CHANGE MASTER TO statement.

  + The internal function that modifies
Slave_open_temp_tables in such cases relied on two
incorrect assumptions:

  1. That the variable is updated by only one thread
 when multi-threaded slaves are not enabled,
 which is not true in the case of multi-source

Re: Query Help...

[shawn l.green]

On 10/22/2015 11:48 AM, Don Wieland wrote:



On Oct 20, 2015, at 1:24 PM, shawn l.green  wrote:

Which release of MySQL are you using?


Version 5.5.45-cll


How many rows do you get if you remove the GROUP_CONCAT operator? We don't need 
to see the results. (sometimes it is a good idea to look at the raw, 
unprocessed results)

Is it possible that you are attempting to concat more values than allowed by 
--group-concat-max-len ?


When I did this I realized I was missing a GROUP BY clause

Her is the debugged working version. Thanks guys.

SELECT
ht.*,
CONCAT(o.first_name, " ", o.last_name) AS orphan,
GROUP_CONCAT(DISTINCT hti.rec_code ORDER BY hti.rec_code ASC SEPARATOR ", ") AS 
alloc
FROM hiv_transactions ht
LEFT JOIN tk_orphans o ON ht.orphan_id = o.orphan_id
LEFT JOIN hiv_trans_items hti ON ht.transaction_id = hti.hiv_transaction_id
WHERE ht.donor_id = 730 AND ht.tr_date BETWEEN "2015-01-01 00:00:00" AND "2015-12-31 
23:59:59"
GROUP BY ht.`transaction_id`
ORDER BY ht.tr_date DESC, ht.rec_code ASC;

Don Wieland
d...@pointmade.net
http://www.pointmade.net
https://www.facebook.com/pointmade.band




Thank you for sharing your solution.

Best wishes,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Query Summary Help...

[Michael Dykman]

I'm not at a terminal but have you tried grouping by p.pk_ProductID instead
of i.fk...? It is the actual value you are selecting as well as being on
the primary table in the query.

On Thu, Oct 22, 2015, 5:18 PM Don Wieland  wrote:

> Hi gang,
>
> I have a query:
>
> SELECT
> p.pk_ProductID,
> p.Description,
> i.Quantity
>
> FROM invoice_invoicelines_Product p
> JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND
> i.fk_InvoiceID IN (1,2,3)
>
> WHERE p.pk_ProductID IN (1,2,3);
>
> It produces a list like the following:
>
> 1,Banana,3
> 2,Orange,1
> 2,Orange,4
> 3,Melon,3
> 3,Melon,3
>
> I want to SUM the i.Quantity per ProductID, but I am unable to get the
> scope/syntax correct. I was expecting the following would work:
>
> SELECT
> p.pk_ProductID,
> p.Description,
> SUM(i.Quantity)
>
> FROM invoice_invoicelines_Product p
> JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND
> i.fk_InvoiceID IN (1,2,3)
>
> WHERE p.pk_ProductID IN (1,2,3)
> GROUP BY i.fk_ProductID;
>
> but it is not working.
>
>
> Little help please. Thanks!
>
>
> Don Wieland
> d...@pointmade.net
> http://www.pointmade.net
> https://www.facebook.com/pointmade.band
>
>
>
>
>

Re: Query Summary Help...

[Don Wieland]

> On Oct 22, 2015, at 2:41 PM, Michael Dykman  wrote:
> 
> I'm not at a terminal but have you tried grouping by p.pk_ProductID instead
> of i.fk...? It is the actual value you are selecting as well as being on
> the primary table in the query.

Yeah I tried that - actually the SUM I need is on the JOIN relationship - 
results should be:

1,Banana,3
2,Orange,5
3,Melon,6

Thanks!

Don Wieland
d...@pointmade.net
http://www.pointmade.net
https://www.facebook.com/pointmade.band

Query Summary Help...

[Don Wieland]

Hi gang,

I have a query:

SELECT 
p.pk_ProductID, 
p.Description, 
i.Quantity  

FROM invoice_invoicelines_Product p
JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND 
i.fk_InvoiceID IN (1,2,3)  

WHERE p.pk_ProductID IN (1,2,3);

It produces a list like the following:

1,Banana,3
2,Orange,1
2,Orange,4
3,Melon,3
3,Melon,3

I want to SUM the i.Quantity per ProductID, but I am unable to get the 
scope/syntax correct. I was expecting the following would work:

SELECT 
p.pk_ProductID, 
p.Description, 
SUM(i.Quantity)  

FROM invoice_invoicelines_Product p
JOIN invoice_InvoiceLines i ON p.pk_ProductID = i.fk_ProductID AND 
i.fk_InvoiceID IN (1,2,3)  

WHERE p.pk_ProductID IN (1,2,3)
GROUP BY i.fk_ProductID;

but it is not working.


Little help please. Thanks!


Don Wieland
d...@pointmade.net
http://www.pointmade.net
https://www.facebook.com/pointmade.band

Re: Query Summary Help...

[Michael Dykman]

One more guess:

Try explicitly aliasing the fields of interest and using those aliases
exclusively throughout the rest of the expression.

SELECT
p.pk_ProductID as pid,
p.Description as dsc,
SUM(i.Quantity) as totl

FROM invoice_invoicelines_Product p
JOIN invoice_InvoiceLines i ON pid = i.fk_ProductID

WHERE pid IN (1,2,3)
AND i.fk_InvoiceID IN (1,2,3)
GROUP BY pid;

Note that I moved the invoiceID clause out of the join condition into the
where filter. The ON clause should only contain expressions of relational
interest.

On Thu, Oct 22, 2015, 6:00 PM Don Wieland  wrote:

>
> > On Oct 22, 2015, at 2:41 PM, Michael Dykman  wrote:
> >
> > I'm not at a terminal but have you tried grouping by p.pk_ProductID
> instead
> > of i.fk...? It is the actual value you are selecting as well as being on
> > the primary table in the query.
>
> Yeah I tried that - actually the SUM I need is on the JOIN relationship -
> results should be:
>
> 1,Banana,3
> 2,Orange,5
> 3,Melon,6
>
> Thanks!
>
> Don Wieland
> d...@pointmade.net
> http://www.pointmade.net
> https://www.facebook.com/pointmade.band
>
>
>
>
>

MySQL Workbench 6.3.5 GA has been released

[Lars Tangvald]

Dear MySQL users,

The MySQL developer tools team announces 6.3.5 as our GA release for
MySQL Workbench 6.3.

For the full list of changes in this revision, visit
http://dev.mysql.com/doc/relnotes/workbench/en/changes-6-3.html

For discussion, join the MySQL Workbench Forums:
http://forums.mysql.com/index.php?152

Download MySQL Workbench 6.3.5 GA now, for Windows, Mac OS X 10.7+,
Oracle Linux 6 and 7, Fedora 21 and Fedora 22, Ubuntu 14.04 and
Ubuntu 15.04 or sources, from:

http://dev.mysql.com/downloads/tools/workbench/

Enjoy!

Changes in MySQL Workbench 6.3.5 (2015-10-21)

   Known limitations:

 * The new JSON editor is in an early state, and has a
   number of usability issues.

 * The table editor in a MySQL Workbench model does not
   allow the JSON data type. The table editor in the SQL
   Editor is not affected.

 * Model synchronization fails to function for tables with
   JSON columns.

 * The MySQL migration wizard will not function with MySQL
   Server 5.7 as the source database. However, MySQL Server
   5.7 can be the target database.

 * The bundled mysqldump tool fails to create dumps for
   MySQL Server 5.6 and earlier. To work around this
   limitation, set an explicit mysqldump binary in the
   Workbench preferences under Administration -> Data Export
   and Import -> Path to mysqldump Tool. Use one from MySQL
   Server 5.6.

   Functionality Added or Changed

 * A JSON viewer/editor was added in the field editor for
   result sets.

   Bugs Fixed

 * Executing Create EER Model from Database with Place
   imported objects on a diagram enabled would not always
   create the diagram. (Bug #22046248)

 * On OS X 10.11, a newly opened EER diagram was not always
   viewable. A workaround was to either scroll or resize the
   EER diagram window. (Bug #22016760, Bug #78829)

 * MySQL Workbench commercial edition now checks MySQL
   Enterprise Backup compatibility with MySQL 5.7 when using
   the Online Backup and Restore features. MySQL Enterprise
   Backup 4.0 and above is required with MySQL 5.7 and
   above. (Bug #21972677)

 * On Windows, connecting via RDP to Workbench with a model
   would cause MySQL Workbench to crash. (Bug #21951171)

 * On Windows with the firewall enabled, either editing a
   current user or creating a new user from the Users and
   Privileges tab would crash after clicking Apply. (Bug
   #21866290)

 * Under Server Status, the reported Disk Space in Data Dir
   was limited to "1.00 TB", even if more space was
   available. (Bug #21833382)

 * The SQL parser and grammar was updated; some queries
   would emit bogus errors, although they would execute with
   success. For example, queries with syntax such as "ALTER
   TABLE foo MODIFY COLUMN bar VARCHAR(10)" and "ALTER TABLE
   foo MODIFY bar INT NOT NULL". (Bug #21776051, Bug
   #20116935, Bug #77549, Bug #75048)

 * On Windows, creating blocked statements (such as
   BEGIN...END) with auto-complete activated would cause
   MySQL Workbench to emit an unhandled exception (Bug
   #21774642, Bug #77488)

 * On Linux, dragging a routine to a Routine Group did not
   function. (Bug #21770594, Bug #78299)

 * Opening Open Value in Viewer to view the contents of BLOB
   and string types would sometimes crash MySQL Workbench.
   (Bug #21746985, Bug #75516)

 * An SSH timeout option was added, under the Others
   preferences panel. (Bug #21690190)

 * After opening a model file, after choosing Preferences,
   Others, checking (enabling) Allow more than one instance
   of MySQL Workbench to run, clicking OK would crash MySQL
   Workbench. (Bug #21666322)

 * On Windows 10, MySQL Workbench failed with an error
   similar to "The type initializer for
   'HtmlRenderer.Utils.FontsUtils' threw an exception". The
   HtmlRenderer.dll DLL was updated with a fix. (Bug
   #21642135, Bug #75344)

 * Clicking Cancel when using the Table Import wizard would
   emit an error, although the import process continued.
   (Bug #21610117, Bug #78013)

 * On OS X, the Rescan for local MySQL instances operation
   was not discovering local MySQL server connections. (Bug
   #21605220, Bug #77989)

 * On Windows, and when connecting to MySQL Server 5.7.8 or
   higher, the Server Logs did not properly display the
   MySQL error logs, and it instead referred to a file named
   "stderr" that could not be found. This is because the
   error log is now written to the Windows Event Log. (Bug
   #21566823, Bug #77958)

 * Executing Control + W to close all tabs, and then
   executing it again (with all tabs already closed), would
   emit a Null Pointer Exception. (Bug #21543454, Bug
   #77906)

 * When creating new user privileges with 

Re: Query Help...

[Don Wieland]

> On Oct 20, 2015, at 1:24 PM, shawn l.green  wrote:
> 
> Which release of MySQL are you using?

Version 5.5.45-cll

> How many rows do you get if you remove the GROUP_CONCAT operator? We don't 
> need to see the results. (sometimes it is a good idea to look at the raw, 
> unprocessed results)
> 
> Is it possible that you are attempting to concat more values than allowed by 
> --group-concat-max-len ?

When I did this I realized I was missing a GROUP BY clause

Her is the debugged working version. Thanks guys. 

SELECT 
ht.*, 
CONCAT(o.first_name, " ", o.last_name) AS orphan, 
GROUP_CONCAT(DISTINCT hti.rec_code ORDER BY hti.rec_code ASC SEPARATOR ", ") AS 
alloc 
FROM hiv_transactions ht 
LEFT JOIN tk_orphans o ON ht.orphan_id = o.orphan_id 
LEFT JOIN hiv_trans_items hti ON ht.transaction_id = hti.hiv_transaction_id 
WHERE ht.donor_id = 730 AND ht.tr_date BETWEEN "2015-01-01 00:00:00" AND 
"2015-12-31 23:59:59" 
GROUP BY ht.`transaction_id`
ORDER BY ht.tr_date DESC, ht.rec_code ASC;

Don Wieland
d...@pointmade.net
http://www.pointmade.net
https://www.facebook.com/pointmade.band