Re: Can not add foreign key constraint

[Peter Brawley]

On 4/24/2017 15:28, Peter Brawley wrote:

On 4/24/2017 13:59, David Mehler wrote:

Hello,

root@localhost [mail]> show engine innodb_status;
ERROR 1286 (42000): Unknown storage engine 'innodb_status'


Well it's very unlikely InnoDB made that up, it's probably in one of 
your Create texts.


Ah, an error cascade, as Shawn Green noticed, there's a typo in my 
suggested command, should be ...


show engine innodb status;

PB

-



PB

-




This is on a Mysql 5.7 setup.

Thanks.
Dave.



On 4/24/17, Peter Brawley  wrote:

On 4/24/2017 12:28, David Mehler wrote:

Hello,

Here's the create table sand error message.

root@localhost [(none)]> use mail;
Database changed
root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` (
-> `user` varchar(40) NOT NULL,
-> `remote_ip` varchar(18) NOT NULL,
-> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`user`),
-> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE
CASCADE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1215 (HY000): Cannot add foreign key constraint


For the table it's referencing here it is:

CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `user` varchar(40) NOT NULL,
 `password` varchar(32) NOT NULL,
 `quota` bigint(20) NOT NULL DEFAULT 256,
  `quota_messages` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user` (`user`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE
CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   Hope this helps.

Adding in a dummy Create Table for the missing referenced
`virtual_domains`, we have ...

drop table if exists lastauth, virtual_users, virtual_domains;

CREATE TABLE virtual_domains (

id int PRIMARY KEY

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_users` (

`id` int(11) NOT NULL auto_increment,

`domain_id` int(11) NOT NULL,

`user` varchar(40) NOT NULL,

`password` varchar(32) NOT NULL,

`quota` bigint(20) NOT NULL DEFAULT 256,

`quota_messages` int(11) NOT NULL DEFAULT 0,

PRIMARY KEY (`id`),

UNIQUE KEY (`user`),

FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE 
CASCADE


) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `lastauth` (

`user` varchar(40) NOT NULL,

`remote_ip` varchar(18) NOT NULL,

`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,

PRIMARY KEY (`user`),

FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


which executes without error. To find out what's going awry in your
setup, right after the error occurs execute ...

show engine innodb_status;


and search the result for LATEST FOREIGN KEY ERROR.

PB


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








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

Re: Can not add foreign key constraint

[shawn l.green]

On 4/24/2017 2:10 PM, Peter Brawley wrote:

On 4/24/2017 12:28, David Mehler wrote:

...snip


Adding in a dummy Create Table for the missing referenced
`virtual_domains`, we have ...

drop table if exists lastauth, virtual_users, virtual_domains;

CREATE TABLE virtual_domains (

   id int PRIMARY KEY

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_users` (

   `id` int(11) NOT NULL auto_increment,

   `domain_id` int(11) NOT NULL,

   `user` varchar(40) NOT NULL,

   `password` varchar(32) NOT NULL,

   `quota` bigint(20) NOT NULL DEFAULT 256,

   `quota_messages` int(11) NOT NULL DEFAULT 0,

   PRIMARY KEY (`id`),

   UNIQUE KEY (`user`),

   FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `lastauth` (

   `user` varchar(40) NOT NULL,

   `remote_ip` varchar(18) NOT NULL,

   `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,

   PRIMARY KEY (`user`),

   FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


which executes without error. To find out what's going awry in your
setup, right after the error occurs execute ...

show engine innodb_status;


and search the result for LATEST FOREIGN KEY ERROR.

PB



Typo warning:   "innodb status" is two words (no underscore)

https://dev.mysql.com/doc/refman/5.6/en/show-engine.html

--
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: Can not add foreign key constraint

[David Mehler]

Hello,

Here's the engines I have:

root@localhost [(none)]> show engines;
++-++--+--++
| Engine | Support | Comment
 | Transactions | XA   | Savepoints |
++-++--+--++
| InnoDB | DEFAULT | Supports transactions, row-level
locking, and foreign keys | YES  | YES  | YES|
| MRG_MYISAM | YES | Collection of identical MyISAM tables
 | NO   | NO   | NO |
| MEMORY | YES | Hash based, stored in memory, useful
for temporary tables  | NO   | NO   | NO |
| BLACKHOLE  | YES | /dev/null storage engine (anything
you write to it disappears) | NO   | NO   | NO |
| MyISAM | YES | MyISAM storage engine
 | NO   | NO   | NO |
| CSV| YES | CSV storage engine
 | NO   | NO   | NO |
| ARCHIVE| YES | Archive storage engine
 | NO   | NO   | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema
 | NO   | NO   | NO |
| FEDERATED  | NO  | Federated MySQL storage engine
 | NULL | NULL | NULL   |
++-++--+--++
9 rows in set (0.00 sec)


Not sure why I'm getting the error 1286.

Thanks.
Dave.


On 4/24/17, Peter Brawley  wrote:
> On 4/24/2017 13:59, David Mehler wrote:
>> Hello,
>>
>> root@localhost [mail]> show engine innodb_status;
>> ERROR 1286 (42000): Unknown storage engine 'innodb_status'
>
> Well it's very unlikely InnoDB made that up, it's probably in one of
> your Create texts.
>
> PB
>
> -
>
>>
>>
>> This is on a Mysql 5.7 setup.
>>
>> Thanks.
>> Dave.
>>
>>
>>
>> On 4/24/17, Peter Brawley  wrote:
>>> On 4/24/2017 12:28, David Mehler wrote:
 Hello,

 Here's the create table sand error message.

 root@localhost [(none)]> use mail;
 Database changed
 root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` (
 -> `user` varchar(40) NOT NULL,
 -> `remote_ip` varchar(18) NOT NULL,
 -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
 UPDATE CURRENT_TIMESTAMP,
 -> PRIMARY KEY (`user`),
 -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE
 CASCADE
 -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 ERROR 1215 (HY000): Cannot add foreign key constraint


 For the table it's referencing here it is:

 CREATE TABLE `virtual_users` (
   `id` int(11) NOT NULL auto_increment,
   `domain_id` int(11) NOT NULL,
   `user` varchar(40) NOT NULL,
  `password` varchar(32) NOT NULL,
  `quota` bigint(20) NOT NULL DEFAULT 256,
   `quota_messages` int(11) NOT NULL DEFAULT 0,
   PRIMARY KEY (`id`),
   UNIQUE KEY `user` (`user`),
   FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE
 CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Hope this helps.
>>> Adding in a dummy Create Table for the missing referenced
>>> `virtual_domains`, we have ...
>>>
>>> drop table if exists lastauth, virtual_users, virtual_domains;
>>>
>>> CREATE TABLE virtual_domains (
>>>
>>> id int PRIMARY KEY
>>>
>>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>>>
>>> CREATE TABLE `virtual_users` (
>>>
>>> `id` int(11) NOT NULL auto_increment,
>>>
>>> `domain_id` int(11) NOT NULL,
>>>
>>> `user` varchar(40) NOT NULL,
>>>
>>> `password` varchar(32) NOT NULL,
>>>
>>> `quota` bigint(20) NOT NULL DEFAULT 256,
>>>
>>> `quota_messages` int(11) NOT NULL DEFAULT 0,
>>>
>>> PRIMARY KEY (`id`),
>>>
>>> UNIQUE KEY (`user`),
>>>
>>> FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE
>>> CASCADE
>>>
>>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>>>
>>> CREATE TABLE IF NOT EXISTS `lastauth` (
>>>
>>> `user` varchar(40) NOT NULL,
>>>
>>> `remote_ip` varchar(18) NOT NULL,
>>>
>>> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
>>> CURRENT_TIMESTAMP,
>>>
>>> PRIMARY KEY (`user`),
>>>
>>> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE
>>>
>>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>>>
>>>
>>> which executes without error. To find out what's going awry in your
>>> setup, right after the error occurs execute ...
>>>
>>> show engine innodb_status;
>>>
>>>
>>> and search 

Re: Can not add foreign key constraint

[Peter Brawley]

On 4/24/2017 13:59, David Mehler wrote:

Hello,

root@localhost [mail]> show engine innodb_status;
ERROR 1286 (42000): Unknown storage engine 'innodb_status'


Well it's very unlikely InnoDB made that up, it's probably in one of 
your Create texts.


PB

-




This is on a Mysql 5.7 setup.

Thanks.
Dave.



On 4/24/17, Peter Brawley  wrote:

On 4/24/2017 12:28, David Mehler wrote:

Hello,

Here's the create table sand error message.

root@localhost [(none)]> use mail;
Database changed
root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` (
-> `user` varchar(40) NOT NULL,
-> `remote_ip` varchar(18) NOT NULL,
-> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`user`),
-> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE
CASCADE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1215 (HY000): Cannot add foreign key constraint


For the table it's referencing here it is:

CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `user` varchar(40) NOT NULL,
 `password` varchar(32) NOT NULL,
 `quota` bigint(20) NOT NULL DEFAULT 256,
  `quota_messages` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user` (`user`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE
CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

   Hope this helps.

Adding in a dummy Create Table for the missing referenced
`virtual_domains`, we have ...

drop table if exists lastauth, virtual_users, virtual_domains;

CREATE TABLE virtual_domains (

id int PRIMARY KEY

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_users` (

`id` int(11) NOT NULL auto_increment,

`domain_id` int(11) NOT NULL,

`user` varchar(40) NOT NULL,

`password` varchar(32) NOT NULL,

`quota` bigint(20) NOT NULL DEFAULT 256,

`quota_messages` int(11) NOT NULL DEFAULT 0,

PRIMARY KEY (`id`),

UNIQUE KEY (`user`),

FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `lastauth` (

`user` varchar(40) NOT NULL,

`remote_ip` varchar(18) NOT NULL,

`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,

PRIMARY KEY (`user`),

FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


which executes without error. To find out what's going awry in your
setup, right after the error occurs execute ...

show engine innodb_status;


and search the result for LATEST FOREIGN KEY ERROR.

PB


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





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

Re: Can not add foreign key constraint

[David Mehler]

Hello,

root@localhost [mail]> show engine innodb_status;
ERROR 1286 (42000): Unknown storage engine 'innodb_status'


This is on a Mysql 5.7 setup.

Thanks.
Dave.



On 4/24/17, Peter Brawley  wrote:
> On 4/24/2017 12:28, David Mehler wrote:
>> Hello,
>>
>>Here's the create table sand error message.
>>
>>root@localhost [(none)]> use mail;
>>Database changed
>>root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` (
>>-> `user` varchar(40) NOT NULL,
>>-> `remote_ip` varchar(18) NOT NULL,
>>-> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
>>UPDATE CURRENT_TIMESTAMP,
>>-> PRIMARY KEY (`user`),
>>-> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE
>> CASCADE
>>-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>>ERROR 1215 (HY000): Cannot add foreign key constraint
>>
>>
>>For the table it's referencing here it is:
>>
>>CREATE TABLE `virtual_users` (
>>  `id` int(11) NOT NULL auto_increment,
>>  `domain_id` int(11) NOT NULL,
>>  `user` varchar(40) NOT NULL,
>> `password` varchar(32) NOT NULL,
>> `quota` bigint(20) NOT NULL DEFAULT 256,
>>  `quota_messages` int(11) NOT NULL DEFAULT 0,
>>  PRIMARY KEY (`id`),
>>  UNIQUE KEY `user` (`user`),
>>  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE
>> CASCADE
>>) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>>
>>   Hope this helps.
>
> Adding in a dummy Create Table for the missing referenced
> `virtual_domains`, we have ...
>
> drop table if exists lastauth, virtual_users, virtual_domains;
>
> CREATE TABLE virtual_domains (
>
>id int PRIMARY KEY
>
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> CREATE TABLE `virtual_users` (
>
>`id` int(11) NOT NULL auto_increment,
>
>`domain_id` int(11) NOT NULL,
>
>`user` varchar(40) NOT NULL,
>
>`password` varchar(32) NOT NULL,
>
>`quota` bigint(20) NOT NULL DEFAULT 256,
>
>`quota_messages` int(11) NOT NULL DEFAULT 0,
>
>PRIMARY KEY (`id`),
>
>UNIQUE KEY (`user`),
>
>FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
>
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
> CREATE TABLE IF NOT EXISTS `lastauth` (
>
>`user` varchar(40) NOT NULL,
>
>`remote_ip` varchar(18) NOT NULL,
>
>`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
> CURRENT_TIMESTAMP,
>
>PRIMARY KEY (`user`),
>
>FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE
>
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
>
> which executes without error. To find out what's going awry in your
> setup, right after the error occurs execute ...
>
> show engine innodb_status;
>
>
> and search the result for LATEST FOREIGN KEY ERROR.
>
> PB
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

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

Re: Can not add foreign key constraint

[Peter Brawley]

On 4/24/2017 12:28, David Mehler wrote:

Hello,

   Here's the create table sand error message.

   root@localhost [(none)]> use mail;
   Database changed
   root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` (
   -> `user` varchar(40) NOT NULL,
   -> `remote_ip` varchar(18) NOT NULL,
   -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
   UPDATE CURRENT_TIMESTAMP,
   -> PRIMARY KEY (`user`),
   -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE
   -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   ERROR 1215 (HY000): Cannot add foreign key constraint


   For the table it's referencing here it is:

   CREATE TABLE `virtual_users` (
 `id` int(11) NOT NULL auto_increment,
 `domain_id` int(11) NOT NULL,
 `user` varchar(40) NOT NULL,
`password` varchar(32) NOT NULL,
`quota` bigint(20) NOT NULL DEFAULT 256,
 `quota_messages` int(11) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`),
 UNIQUE KEY `user` (`user`),
 FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  Hope this helps.


Adding in a dummy Create Table for the missing referenced 
`virtual_domains`, we have ...


drop table if exists lastauth, virtual_users, virtual_domains;

CREATE TABLE virtual_domains (

  id int PRIMARY KEY

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_users` (

  `id` int(11) NOT NULL auto_increment,

  `domain_id` int(11) NOT NULL,

  `user` varchar(40) NOT NULL,

  `password` varchar(32) NOT NULL,

  `quota` bigint(20) NOT NULL DEFAULT 256,

  `quota_messages` int(11) NOT NULL DEFAULT 0,

  PRIMARY KEY (`id`),

  UNIQUE KEY (`user`),

  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `lastauth` (

  `user` varchar(40) NOT NULL,

  `remote_ip` varchar(18) NOT NULL,

  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP,

  PRIMARY KEY (`user`),

  FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


which executes without error. To find out what's going awry in your 
setup, right after the error occurs execute ...


show engine innodb_status;


and search the result for LATEST FOREIGN KEY ERROR.

PB


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

Re: Can not add foreign key constraint

[David Mehler]

Hello,

  Here's the create table sand error message.

  root@localhost [(none)]> use mail;
  Database changed
  root@localhost [mail]> CREATE TABLE IF NOT EXISTS `lastauth` (
  -> `user` varchar(40) NOT NULL,
  -> `remote_ip` varchar(18) NOT NULL,
  -> `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
  UPDATE CURRENT_TIMESTAMP,
  -> PRIMARY KEY (`user`),
  -> FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE
  -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  ERROR 1215 (HY000): Cannot add foreign key constraint


  For the table it's referencing here it is:

  CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`user` varchar(40) NOT NULL,
   `password` varchar(32) NOT NULL,
   `quota` bigint(20) NOT NULL DEFAULT 256,
`quota_messages` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `user` (`user`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 Hope this helps.

  Thanks.
  Dave.

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

Re: Can not add foreign key constraints

[Peter Brawley]

On 4/24/2017 9:18, David Mehler wrote:

Hello,

  I'm trying to add a table to an existing database. I'm wanting it to get
  one of it's fields from an already existing table. I've done this
  before in this database. This works:

  CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`user` varchar(40) NOT NULL,
`password` varchar(128) NOT NULL,
`quota` bigint(20) NOT NULL DEFAULT 256,
`quota_messages` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `user` (`user`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  This does not:

  CREATE TABLE IF NOT EXISTS `lastauth` (
  `user` varchar(40) NOT NULL,
  `remote_ip` varchar(18) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
  CURRENT_TIMESTAMP,
  PRIMARY KEY (`user`),
  FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  Can anyone spot the situation?


Let's see the CREATE TABLE statement for the referenced table, and the 
error message.


PB

-



  Thanks.
  Dave.




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

Re: Can not add foreign key constraints

[Reindl Harald]

Am 24.04.2017 um 16:18 schrieb David Mehler:

  I'm trying to add a table to an existing database. I'm wanting it to get
  one of it's fields from an already existing table. I've done this
  before in this database. This works:

  CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`user` varchar(40) NOT NULL,
`password` varchar(128) NOT NULL,
`quota` bigint(20) NOT NULL DEFAULT 256,
`quota_messages` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `user` (`user`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  This does not:

  CREATE TABLE IF NOT EXISTS `lastauth` (
  `user` varchar(40) NOT NULL,
  `remote_ip` varchar(18) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
  CURRENT_TIMESTAMP,
  PRIMARY KEY (`user`),
  FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  Can anyone spot the situation?


wouldn't it be cool if you post the errors you get from the start?

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

Can not add foreign key constraints

[David Mehler]

Hello,

 I'm trying to add a table to an existing database. I'm wanting it to get
 one of it's fields from an already existing table. I've done this
 before in this database. This works:

 CREATE TABLE `virtual_users` (
   `id` int(11) NOT NULL auto_increment,
   `domain_id` int(11) NOT NULL,
   `user` varchar(40) NOT NULL,
   `password` varchar(128) NOT NULL,
   `quota` bigint(20) NOT NULL DEFAULT 256,
   `quota_messages` int(11) NOT NULL DEFAULT 0,
   PRIMARY KEY (`id`),
   UNIQUE KEY `user` (`user`),
   FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 This does not:

 CREATE TABLE IF NOT EXISTS `lastauth` (
 `user` varchar(40) NOT NULL,
 `remote_ip` varchar(18) NOT NULL,
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
 CURRENT_TIMESTAMP,
 PRIMARY KEY (`user`),
 FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 Can anyone spot the situation?

 Thanks.
 Dave.

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

behavior and documents conflict for SELECT LAST_INSERT_ID()

[Chenxi Li]

Dear friends,

1. https://bugs.mysql.com/bug.php?id=78934

2.
https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id

The document (2) says that LAST_INSERT_ID() will not be changed if no rows
are inserted  successfully. But (1) says that it's undefined when no rows
are inserted successfully, which is in real case.

What is the problem? the document or the code?

Best Regards,
Chenxi Li

Upscene releases Database Workbench 5.3

[Martijn Tonies (Upscene Productions)]

Upscene Productions is proud to announce the availability of
the next version of the popular multi-DBMS development tool:

" Database Workbench 5.3 "

This release includes a custom report writer, increased support for PostgreSQL, 
a renewed stored routine debugger with full support for Firebird 3 Stored 
Functions and Packges and adds several other features.

Database Workbench 5 comes in multiple editions with different pricing models, 
there's always a version that suits you!



"Version 5 included many new features", says Martijn Tonies, founder of Upscene 
Productions. 
"It added code editor features, has diagramming improvements, multiple 
editions, is fully HiDPI aware and offers secure connections to PostgreSQL, 
MySQL and MariaDB. The most recent version adds a custom report writer and 
increased support for PostgreSQL, as requested by many of our customers."


For more information, see What's new in Database Workbench 5?
( http://www.upscene.com/database_workbench/whatsnew )


Database Workbench supports MySQL, MariaDB, Firebird, Oracle, MS SQL Server,
SQL Anywhere, NexusDB, PostgreSQL and InterBase, comes in multiple editions and 
is licensed based on
selectable modules.

It includes tools for database design, database maintenance, testing, data 
transfer,
data import & export, database migration, database compare and numerous other 
tools.


About Database Workbench
Database Workbench is a database developer tool, over 12 years in the making and
is being used by thousands of developers across the globe who have come to rely 
on it
every day. From database design, implementation, to testing and debugging, it 
will aid you 
in your daily database work.

About Upscene Productions
Based in The Netherlands, Europe, this small but dedicated company has been 
providing
database developers with useful tools for over 14 years. Slowly expanding the 
product portfolio
and gaining recognition amongst InterBase and Firebird database developers, 
they now offer
tools for a whole range of database systems, including Oracle and Microsoft SQL 
Server.

Re: DATETIME vs CHAR for "timestamp"

[shawn l.green]

On 4/14/2017 3:11 PM, SSC_perl wrote:

I have creation date/time fields in my script that are formatted as 
|MM|DD|hh|mm|ss.  Short of changing the script, should I set the field type 
in MySQL to DATETIME, or would it be better in terms of speed and efficiency to 
set it as char(19)?  Or would it not make a difference?

Thanks,
Frank




That all depends. Do you...

a) want mysqld to treat that column as an actual temporal value

or

b) want mysqld to see it as an opaque string of random alphanumeric 
characters


As you appear to have referred to this as a "creation date/time" 
tracking field it appears you want this to be treated like a temporal 
value so that you can easily do things like


SELECT ... WHERE create_date > NOW() - interval 7 days ;

If it's a temporal column, you can use functions like those in the next 
URL against it. If it's a string-type column, you can't unless you first 
convert your string into a temporal data type.


https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html


My suggestion is to use a native temporal data type (I recommend 
DATETIME) and that you review this section on how to format temporal 
literals (so that you can pass them easily from your application into 
MySQL)

https://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html

Using the correct data type is important to performance. You want to 
avoid forcing the server to perform too many implicit type conversions. 
Those usually nullify any performance improvements an index on those 
columns might provide:

https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

And the native DATETIME data type only needs 8 bytes to store its data 
while your CHAR(16) may need up to 64 bytes of storage.

https://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html


Yours,
--
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

DATETIME vs CHAR for "timestamp"

[SSC_perl]

I have creation date/time fields in my script that are formatted as 
|MM|DD|hh|mm|ss.  Short of changing the script, should I set the field type 
in MySQL to DATETIME, or would it be better in terms of speed and efficiency to 
set it as char(19)?  Or would it not make a difference?

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

Re: Automated SQL checker?

[SSC_perl]

> On Apr 12, 2017, at 3:38 PM, Ronan McGlue  wrote:
> 
> Enable the slow log on the DB.

Thanks Ronan.  That sounds like it would be beneficial.  I take it 
you’re referring to a setting on the server, though.  If that’s the case, I 
don’t think I can do that as I’m on shared hosting (unless I’m misunderstanding 
you).  When I get some free time (hah!) maybe I’ll set up MySQL on my laptop.  
Unfortunately, installing DBI on OS X can be a nightmare, so I don’t know 
if/when that will happen.

Thanks again,
Frank
https://www.surfshopcart.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Automated SQL checker?

[Ronan McGlue]

Hi Frank,

Enable the slow log on the DB. Any queries which are above the threshold 
variable ( |long_query_time| 
 
) will be logged .


Default is 10s, so I assume you'd want to drop this down to 1-2 ( or 
less ) for a shopcart type response time?


You can then use mysqldumpslow command to aggregate this file after a 
period of  time to identify queries which are greater than long_query_time.


This is typically the first step in analyzing inefficient queries ( or 
more accurately, queries which are candidates for improvement ) in a 
running DB. eg


*|mysqldumpslow -s c -t 5 |*

https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

Regards


Ronan McGlue

MySQL Support



On 13/04/2017 01:06, SSC_perl wrote:

On Apr 11, 2017, at 1:26 PM, Michael Munger  wrote:

Use MySQL workbench.

Thanks, Michael.  I played with it some already and it looks like it 
will give me a lot to work with.

Will it also let me know if field types are wrong for the given 
information type, or is that asking for too much?

Frank

https://www.surfshopcart.com


--
Regards

Ronan McGlue
MySQL Support
Oracle Australia

MySQL Shell 1.0.9 GA has been released

[Hery Ramilison]

Dear MySQL Users,

This is the first GA version of MySQL Shell (a component of the MySQL
Server). The MySQL Shell is provided under Oracle's dual-license.

MySQL Shell is an interactive JavaScript, Python and SQL console
interface, supporting development and administration for the MySQL
Server. It provides scriptable API that supports the creation and
management of MySQL InnoDB clusters, as well as a modern fluent CRUD
API for the MySQL Document Store.

The AdminAPI provides an integrated solution for high availability and
scalability using InnoDB based MySQL databases, without requiring
advanced MySQL expertise.

The Document Store DevAPI enables developers and DBAs to create
"schema-less" JSON document collections and perform Create, Update,
Read, Delete (CRUD) operations on those collections with little to
no knowledge of the SQL language.

Additionally, MySQL Shell provides partial compatibility with the
mysql command line client by supporting many of the same command line
options. As with the mysql command line, MySQL Shell can also be used
to connect to and send SQL commands to pre-5.7.12 versions of the
MySQL server using the standard MySQL network protocol.

For more information about how to configure and work with an InnoDB cluster
see https://dev.mysql.com/doc/refman/en/mysql-innodb-cluster-userguide.html

For more information about how to use MySQL Shell and the MySQL Document
Store support see https://dev.mysql.com/doc/refman/en/document-store.html

For full documentation on MySQL Server, MySQL Shell and related topics,
see http://dev.mysql.com/doc/refman/en/

If you want to write applications that use the the CRUD based X DevAPI
use the latest MySQL Connectors for your language of choice. You can
also find documentation about Connectors in the MySQL documentation
site.

For more information about how to download MySQL Shell, see
http://dev.mysql.com/downloads/shell/

We welcome and appreciate your feedback and bug reports, see
http://bugs.mysql.com/

Enjoy!


Changes in MySQL Shell 1.0.9 (2017-04-12)

   Functionality Added or Changed

 * In SQL mode, support was added for \G to display the SQL
   result vertically.
   The --vertical (or -E) command line option was added to
   display all result sets vertically. This sets the
   shell.options.outputFormat variable to "vertical". (Bug
   #24848230)

   Bugs Fixed

 * The MYSQLPROVISION environment variable is no longer
   supported or required by MySQL Shell. (Bug #25733261)

 * mysqlprovision copyright text was missing the current
   year. (Bug #25725965)

 * Access to schema objects using property syntax
   (db.collection) was not automatically available. (Bug
   #25721628)

 * The mysqlprovision binary was not built by default when
   compiling from source. (Bug #25699824)

 * Executing print with the --execute option could cause
   MySQL Shell to stop unexpectedly. (Bug #25684798)

 * SQL import operations would fail if multiple-line
   comments were present in the SQL being imported. (Bug
   #25637606)

 * When using dba.configureLocalInstance(), error messages
   generated due to a configuration file update failure
   would not report the cause of failure but a misleading
   error message of its core code. (Bug #25593140)

 * On Windows, error handling was improved to better notify
   the user when the system's Python does not support SSL.
   (Bug #25534469)

 * dba.checkInstanceConfiguration() was not documented in
   the dba global variable help. (Bug #25487579)

 * A dissolved cluster object was not being correctly set as
   invalid, meaning it was possible to access its methods
   and properties. The fix ensures that dissolved cluster
   objects are correctly detected and a new error similar to
   Can't call function 'function_name' is generated when
   executed on a dissolved cluster. (Bug #25141048)

 * Global DB object properties could change their semantics
   at runtime. For example, the property giving the schema
   name could later be displayed as a table object. This was
   related to how cached objects were given precedence. Now,
   on object retrieval, the cache is only accessed if the
   requested member is not a fixed member of the class. (Bug
   #24964342)

 * The addInstance() method did not function on remote MySQL
   instances. (Bug #24832550)

 * MySQL Shell was not detecting changes made manually to
   the cluster topology to use Group Replication
   multi-primary mode
   (group_replication_single_primary_mode=OFF). MySQL Shell
   was not showing the read-write abilities of the
   instances. (Bug #24756046)

 * On Unix, MySQL Shell was showing the password used to run
   the command in the operating system process list.

On behalf of the MySQL Shell and MySQL Release Team,
Kent Boortz & Hery Ramilison

--
MySQL 

MySQL Router 2.1.3 GA has been released

[Balasubramanian Kandasamy]

Dear MySQL users,

MySQL Router 2.1.3 GA is the first GA release for MySQL Router 2.1.

The MySQL Router is a new building block for high availability
solutions based on MySQL InnoDB clusters.

By taking advantage of the new Group Replication technology,
and combined with the MySQL Shell, InnoDB clusters provide an
integrated solution for high availability and scalability for
InnoDB based MySQL databases, that does not require advanced MySQL
expertise.

The deployment of applications with high availability requirements
is greatly simplified by MySQL Router. MySQL client connections are
transparently routed to online members of a InnoDB cluster, with
MySQL server outages and cluster reconfigurations being automatically
handled by the Router.

MySQL Router 2.1 also introduces the following additions:

* Automatic setup/bootstrap with a MySQL InnoDB cluster
* Support for the new X protocol
* Support for deployments inside self-contained directories
* Easy and secure password management using a built-in keyring

You can download MySQL Router fromhttp://dev.mysql.com/downloads/router.
Package binaries are available for several platforms and also as a
source code download.

If you are a commercial customer, MySQL Router is available via the
MySQL Oracle Support (MOS) website. Packages will be available on
eDelivery at the next monthly refresh.

Documentation for MySQL Router can be found at
http://dev.mysql.com/doc/mysql-router/en

Enjoy!

Changes in MySQL Router 2.1.3 (2017-04-12, General Availability)

   A known limitation of this release:
   Note

   The --bootstrap command line option does not accept IPv6 addresses.

 * Functionality Added or Changed

 * Bugs Fixed

   Functionality Added or Changed

 * Added functionality to also scan for Router configuration
   files with the .ini extension, to provide backward compatibility
   with previous Router installations.  Router looks in the initial
   directory for the .conf version, then checks for a .ini version,
   and then repeats the process in the second directory that's
   typically the user's home directory. (Bug #25688333)

   Bugs Fixed

 * On Linux, systemd based distributions defaulted to
   mysqlrouter.ini instead of mysqlrouter.conf. (Bug #25688333)

 * Bootstrapping as a super user (uid==0) without the --user
   option would generate files and directories that were owned by
   root an not accessible by others. Now, super users are required
   to pass in the --user option. Also while not recommended, the
   super user name can be passed in to force using the super user,
   such as --user=root.  (Bug #25682736)

 * Router would compile against yaSSL but not OpenSSL. (Bug
   #25672823, Bug #85068)

 * On Solaris, MySQL Router would not start as it could not
   find the required logger.so. (Bug #25638708)

 * On FreeBSD, a unit-test would always fail with "Bad
   Suki". (Bug #25549490)

On behalf of Oracle MySQL Release Team
Balasubramanian Kandasamy

Re: Automated SQL checker?

[SSC_perl]

> On Apr 11, 2017, at 1:26 PM, Michael Munger  
> wrote:
> 
> Use MySQL workbench.

Thanks, Michael.  I played with it some already and it looks like it 
will give me a lot to work with.

Will it also let me know if field types are wrong for the given 
information type, or is that asking for too much?

Frank

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

RE: Automated SQL checker?

[Michael Munger]

Use MySQL workbench.

Add query to the editor, execute, check results.

Then, use the "Execution plan" feature to see how things are executing and look 
for bad things (Cartesian products, stupid loops, etc...)

See also: Query stats.


Michael Munger, dCAP, MCPS, MCNPS, MBSS
High Powered Help, Inc.
Microsoft Certified Professional
Microsoft Certified Small Business Specialist
Digium Certified Asterisk Professional
mich...@highpoweredhelp.com


-Original Message-
From: SSC_perl [mailto:p...@surfshopcart.com] 
Sent: Tuesday, April 11, 2017 4:04 PM
To: mysql mailing list 
Subject: Automated SQL checker?

Is there a way, perhaps with a script or a service, that one can check 
MySQL code to see about making it more efficient?  I maintain an open source 
shopping cart written in Perl and it’s been awhile since the SQL has been 
worked on, so I want to see if it could use some updating.

Thanks,
Frank

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

Automated SQL checker?

[SSC_perl]

Is there a way, perhaps with a script or a service, that one can check 
MySQL code to see about making it more efficient?  I maintain an open source 
shopping cart written in Perl and it’s been awhile since the SQL has been 
worked on, so I want to see if it could use some updating.

Thanks,
Frank

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

MySQL Cluster 7.5.6 has been released

[Lars Tangvald]

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 & 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.6, 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.

MySQL Cluster 7.5 is also available from our repository for Linux
platforms, go here for details:

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

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.6 (5.7.18-ndb-7.5.6) (2017-04-10,
General Availability)

   MySQL NDB Cluster 7.5.6 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
   (http://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
   http://dev.mysql.com/downloads/cluster/.
   Repo packages for apt and yum can now be found at
   https://dev.mysql.com/downloads/repo/

   For an overview of changes made in MySQL NDB Cluster 7.5, see
   What is New in MySQL NDB Cluster 7.5
(http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-what-is-new.html).

   This release also incorporates all bugfixes and changes made
   in previous NDB Cluster releases, as well as all bugfixes and
   feature changes which were added in mainline MySQL 5.7
   through MySQL 5.7.18 (see Changes in MySQL 5.7.18 (2017-04-10)
(http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-18.html)).

 Platform-Specific Notes

 * Ubuntu 14.04 and Ubuntu 16.04 are now supported.

 * The minimum required version of Solaris is now Solaris 11
   update 3, due to a dependency on system runtime
   libraries.

 * On Solaris, MySQL is now built with Developer Studio 12.5
   instead of gcc. The binaries require the Developer Studio
   C/C++ runtime libraries to be installed. See here for how
   to install only the libraries:
   https://docs.oracle.com/cd/E60778_01/html/E60743/gozsu.html

   Functionality Added or Changed

 * Packaging: Yum repo packages are added for EL5, EL6, EL7,
   and SLES12.
   Apt repo packages are added for Debian 7, Debian 8,
   Ubuntu 14.04, and Ubuntu 16.04

   Bugs Fixed

 * Partitioning: The output of EXPLAIN PARTITIONS
   (http://dev.mysql.com/doc/refman/5.7/en/explain.html)
   displayed incorrect values in the partitions column when
   run on an explicitly partitioned NDB
   (http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html)
   table having a large number of partitions.
   This was due to the fact that, when processing an EXPLAIN
   statement, mysqld calculates the partition ID for a hash
   value as (hash_value % number_of_partitions), which is
   correct only when the table is partitioned by HASH, since
   other partitioning types use different methods of mapping
   hash values to partition IDs. This fix replaces the
   partition ID calculation performed by mysqld with an
   internal NDB function which calculates the partition ID
   correctly, based on the table's partitioning type. (Bug
   #21068548)
   References: See also: Bug #25501895, Bug #14672885.

 * CPU usage of the data node's main thread by the DBDIH
   master block as the end of a local checkpoint could
   approach 100% in certain cases where the database had a
   very large number of fragment replicas. This is fixed by
   reducing the frequency and range of fragment queue
   checking during an LCP. (Bug #25443080)

 * The ndb_print_backup_file utility failed when attempting
   to read from a backup file when the backup included a
   table having more than 500 columns. (Bug #25302901)
   References: See also: Bug #25182956.

 * Multiple data node failures during a partial restart of
   the cluster could cause API nodes to fail. This was due
   to expansion of an internal object ID map by one thread,
   thus changing its location in memory, while another
   thread was still accessing the old location, 

MySQL Cluster 7.2.28 has been released

[Daniel Horecki]

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 & 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 and Memcached)

MySQL Cluster 7.2.28, 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.2/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.2.28 (5.5.55-ndb-7.2.28) (2017-04-10, 
General Availability)


MySQL NDB Cluster 7.2.28 is a new release of NDB Cluster,
incorporating new features in the NDB storage engine, and
fixing recently discovered bugs in previous MySQL NDB Cluster
7.2 development releases.

Obtaining MySQL NDB Cluster 7.2.  MySQL NDB Cluster 7.2
source code and binaries can be obtained from
http://dev.mysql.com/downloads/cluster/.

This release also incorporates all bugfixes and changes made
in previous NDB Cluster releases, as well as all bugfixes and
feature changes which were added in mainline MySQL 5.5
through MySQL 5.5.55 (see Changes in MySQL 5.5.55 (2017-04-10, 
General availability)

(http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-55.html)).

Bugs Fixed

  * NDB Disk Data: Stale data from NDB Disk Data tables that
had been dropped could potentially be included in backups
due to the fact that disk scans were enabled for these.
To prevent this possibility, disk scans are now
disabled---as are other types of scans---when taking a
backup. (Bug #84422, Bug #25353234)

  * NDB Disk Data: In some cases, setting dynamic in-memory
columns of an NDB Disk Data table to NULL was not handled
correctly. (Bug #79253, Bug #22195588)


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

MySQL Community Server 8.0.1-dmr has been released (part 3/3)

[Bjorn Munch]

[This is part 3 of the announcement]

 * JSON: When a JSON value consisted of a large sub-document
   wrapped in many levels of JSON arrays, objects, or both,
   serialization of the JSON value sometimes required an
   excessive amount time to complete. (Bug #23031146)

 * JSON: When a NULL value existed in a JSON column, the
   result from a query using GROUP_CONCAT() together with
   the ORDER BY clause was not always correct. (Bug
   #22992666)

 * JSON: The internal rapid_json_handler used its own data
   structures to represent a partially-built DOM; these had
   to be converted into a Json_dom graph before returning
   the result. Now this handler builds the graph directly,
   which reduces the amount of work required to build it,
   and thus to parse a JSON document. (Bug #22900110)

 * JSON: The internal function Item_func_case::val_json()
   did not always set the null value flag as expected when a
   CASE expression evaluated to NULL, leading to an
   assertion in debug builds of the server. (Bug #22887227)

 * JSON: The SUM() function truncated decimal values
   extracted from JSON documents, producing an integer
   result. (Bug #84935, Bug #25530204)

 * JSON: A JSON document that contained a double value
   slightly greater than the maximum value that can be
   represented by a double silently replaced it with zero
   instead of rejecting the value and raising an error. Such
   values are now handled correctly in MySQL JSON documents.
   The underlying issue was traced to a problem with
   RapidJSON, which has been reported to that library's
   developers as Issue #849
   (https://github.com/miloyip/rapidjson/issues/849). (Bug
   #84891, Bug #25518504)

 * JSON: The JSON_SEARCH() and JSON_CONTAINS_PATH()
   functions did not work when the one_or_all argument was
   specified using UTF-16 encoding. For both of these
   functions, this argument is now converted to utfmb4 if
   need be before its value is checked. (Bug #84880, Bug
   #22516960)

 * JSON: The JSON_UNQUOTE() function did not work with
   strings that used UTF-16 encoding. Now these strings are
   converted to utfmb4 internally before being processed.
   (Bug #84878, Bug #25516881)

 * JSON: Updating the same JSON column in a single statement
   could cause incorrect values to be written into the
   table. This occurred when the second update overwrote the
   column value with a subset of itself. An example of such
   a statement is shown here:
UPDATE t SET col = JSON_ARRAY(value), col = col->'$[0]';

   (Bug #84694, Bug #25461627)

 * JSON: The functions JSON_QUOTE() and JSON_UNQUOTE() did
   not work correctly with multibyte character sets such as
   utf8mb4. (Bug #84680, Bug #25455065)
   References: See also: Bug #77234, Bug #21193273.

 * JSON: The error message for Error 3152
   ER_JSON_USED_AS_KEY has been changed from JSON column
   '%s' cannot be used in key specification to the less
   confusing and more accurate JSON column '%s' supports
   indexing only via generated columns on a specified JSON
   path. (Bug #81364, Bug #23274244)

 * JSON: Internal tests for MySQL JSON functionality ran out
   of stack space on some platforms when run against a
   debug-enabled server. Because timely checks were not made
   for stack usage, the server did not detect this
   situation, leading to a server exit.
   The fix for this issue is twofold:

  + Stack overrun checks are now made before attempting
to serialize a nested array or object, so that the
operation fails gracefully when processing deeply
nested JSON documents, rather than causing an exit.

  + Serialization of JSON documents has been reorganized
so that it requires less use of the stack when
compiled without optimization.
   (Bug #81083, Bug #23106330)

 * MySQL did not compile with GCC 7. (Bug #25643811)

 * The (undocumented) WINDOWS_RUNTIME_MD CMake option has
   been removed. (Bug #25611359)

 * If --skip-innodb or one of its variants was used, a
   spurious warning about avoid_temporal_upgrade was
   generated. (Bug #25573578)

 * mysqld_safe failed to restart the server if a
   PID_FILE.shutdown file was present. (Bug #25572504)
   References: This issue is a regression of: Bug #11751149.

 * For Debian/Ubuntu packages, user-defined collation files
   could be overwritten during MySQL upgrades. Charset files
   are now marked as conffiles so that user customizations
   generate a prompt during upgrades whether to overwrite
   them. (Bug #25525628, Bug #84761)

 * For CREATE TABLE statements that specified the table name
   with a database qualifier and included a DATA 

MySQL Community Server 8.0.1-dmr has been released (part 2/3)

[Bjorn Munch]

[ This is part 2 of the announcement ]

   Functionality Added or Changed

 * InnoDB: By default, InnoDB reads uncommitted data when
   calculating statistics. In the case of an uncommitted
   transaction that deletes rows from a table, InnoDB
   excludes records that are delete-marked when calculating
   row estimates and index statistics, which can lead to
   non-optimal execution plans for other transactions that
   are operating on the table concurrently using a
   transaction isolation level other than READ UNCOMMITTED.
   To avoid this scenario, a new configuration option,
   innodb_stats_include_delete_marked, can be enabled to
   ensure that InnoDB includes delete-marked records when
   calculating persistent optimizer statistics. (Bug
   #2990)

 * InnoDB: Geometry parsing and bounding box computational
   code for r-trees was moved from InnoDB to the server.

 * InnoDB: InnoDB now supports NOWAIT and SKIP LOCKED
   options with SELECT ... FOR SHARE and SELECT ... FOR
   UPDATE locking read statements. NOWAIT causes the
   statement to return immediately if a requested row is
   locked by another transaction. SKIP LOCKED removes locked
   rows from the result set. See Locking Read Concurrency
   with NOWAIT and SKIP LOCKED
(http://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html#innodb-locking-reads-nowait-skip-locked).
   SELECT ... FOR SHARE replaces SELECT ... LOCK IN SHARE
   MODE, but LOCK IN SHARE MODE remains available for
   backward compatibility. The statements are equivalent.
   However, FOR SHARE supports NOWAIT, SKIP LOCKED, and OF
   tbl_name options. See SELECT Syntax
   (http://dev.mysql.com/doc/refman/8.0/en/select.html).
   OF tbl_name applies locking queries to named tables.
   Note
   OF is now a reserved word and cannot be used as an
   identifier without identifier quoting.

 * InnoDB: The InnoDB tablespace encryption feature now
   supports encryption of redo log and undo log data,
   controlled by the innodb_redo_log_encrypt and
   innodb_undo_log_encrypt configuration options. See Redo
   Log Data Encryption
(http://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-encryption.html#innodb-tablespace-encryption-redo-log),
   and Undo Log Data Encryption
(http://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-encryption.html#innodb-tablespace-encryption-undo-log).

 * InnoDB: InnoDB internal temporary tables that are stored
   on disk now support multiple cursor positions, permitting
   single writer and multiple reader access within the same
   thread. The purpose of this enhancement is to provide
   support for recursive and non-recursive common table
   expressions (CTEs).
   Additionally, consistent-read access to InnoDB on-disk
   internal temporary tables is replaced by a dirty-read
   scheme, and row counts for InnoDB on-disk internal
   temporary tables now use row count statistics instead of
   slower table scans.

 * InnoDB: InnoDB now compresses large objects into a
   sequence of smaller zlib streams for tables that use
   ROW_FORMAT=COMPRESSED. Previously, large object data was
   compressed into a single zlib stream.

 * Replication: View change events from a Group Replication
   group can now be replicated to an external multithreaded
   slave (MTS) of type DATABASE. (Bug #25170698)

 * Replication: When a negative or fractional timeout
   parameter was supplied to
   WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(), the server behaved
   in unexpected ways. With this fix:

  + A fractional timeout value is read as-is, with no
round-off.

  + A negative timeout value is rejected with an error
if the server is on a strict SQL mode; if the server
is not on a strict SQL mode, the value makes the
function return NULL immediately without any waiting
and then issue a warning.
   (Bug #24976304, Bug #83537)

 * Replication: Added the binlog_expire_logs_seconds system
   variable, which sets an interval in seconds for purging
   of the binary log. The effects of this variable and
   expire_logs_days are cumulative, making it possible to
   set a period such as 1.5 days. To completely disable
   automatic binary log purging, set both variables equal to
   0, which is the default value for both of them. (Bug
   #71697, Bug #18260088)

 * Replication: Performance schema tables have been added to
   monitor replication lags and queues. The
   replication_connection_status table has updated
   information on the last transaction queued in the relay
   log, as well as the transaction currently being queued in
   the relay log. The
   replication_applier_status_by_coordinator table has
   updated 

MySQL Community Server 5.7.18 has been released

[Hery Ramilison]

Dear MySQL users,

MySQL Server 5.7.18, a new version of the popular Open Source
Database Management System, has been released. MySQL 5.7.18 is
recommended for use on production systems.

For an overview of what's new in MySQL 5.7, please see

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

For information on installing MySQL 5.7.18 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.18 is available in source and binary form for a number of
platforms from our download pages at

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

MySQL Server 5.7.18 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.18 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 since
the release of MySQL 5.7.17. It may also be viewed online at

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

Enjoy!

Changes in MySQL 5.7.18 (2017-04-10)
   Compilation Notes

 * Windows builds now use the default runtime libraries
   (builds use the /MD flag). (Bug #25611609)

 * CMake support was added for compiling with Developer
   Studio 12.6. (Bug #25384295)

   Configuration Notes

 * MySQL failed to compile if -DENABLE_DEBUG_SYNC=OFF AND
   -DWITH_DEBUG=ON were both given. The ENABLE_DEBUG_SYNC
   option has been removed and enabling WITH_DEBUG enables
   Debug Sync. (Bug #18374703)

 * The --temp-pool server option is deprecated and will be
   removed in MySQL 8.0.

   DTrace Support

 * Support for DTrace is deprecated and is removed in MySQL
   8.0.

   Packaging Notes

 * Changes in RPM package structure require a larger set of
   packages to be removed to install MySQL Server cleanly.
   (Bug #25603087)

 * To avoid potential race conditions, Debian packages now
   use the GNU install utility rather than a combination of
   mkdir, touch, and chown. (Bug #25258829)

 * The my-default.cnf.sh file (used to produce a default
   my-default.cnf or my-default.ini file) is no longer
   included in source distributions and my-default.cnf and
   my-default.ini are no longer included in or installed by
   distribution packages. (Bug #22525354)

 * Reminder: MySQL 5.7 requires the Microsoft Visual C++
   2013 Redistributable Package to run on Windows platforms.
   Users should make sure the package has been installed on
   the system before starting the server. The package is
   available at the Microsoft Download Center
   (http://www.microsoft.com/en-us/download/default.aspx).

   Parser Notes

 * PROCEDURE ANALYSE() syntax is now deprecated and is
   removed in MySQL 8.0.

 * The use of \N as a synonym for NULL in SQL statements is
   deprecated and is removed in MySQL 8.0. Use NULL instead.
   This change does not affect text file import or export
   operations performed with LOAD DATA INFILE or SELECT ...
   INTO OUTFILE, for which NULL continues to be represented
   by \N. See LOAD DATA INFILE Syntax
   (http://dev.mysql.com/doc/refman/5.7/en/load-data.html).

   Security Notes

 * The linked OpenSSL library for the MySQL Commercial
   Server has been updated to version 1.0.2k. For a
   description of issues fixed in this version, see
   http://www.openssl.org/news/vulnerabilities.html.
   This change does not affect the Oracle-produced MySQL
   Community build of MySQL Server, which uses the yaSSL
   library instead. (Bug #25768671)

 * The keyring_okv plugin no longer supports RSA or DSA key
   types. (Bug #25540639)

 * The keyring_okv keyring plugin now can use the SafeNet
   KeySecure Appliance as the KMIP back end for keyring
   storage. For instructions, see Using the keyring_okv
   Oracle Key Vault Plugin
   (http://dev.mysql.com/doc/refman/5.7/en/keyring-okv-plugin.html).

   Thread Pool Notes

 * To improve thread_pool plugin performance, connection
   authentication and initialization have been moved from
   the acceptor thread to the thread pool worker threads
   that handle client connections. This 

MySQL Community Server 5.5.55 has been released

[Gipson Pulla]

Dear MySQL users,

MySQL Server 5.5.55 is a new version of the 5.5 production release
of the world's most popular open source database. MySQL 5.5.55 is
recommended for use on production systems.

MySQL 5.5 includes several high-impact enhancements to improve the
performance and scalability of the MySQL Database, taking advantage of
the latest multi-CPU and multi-core hardware and operating systems. In
addition, with release 5.5, InnoDB is now the default storage engine for
the MySQL Database, delivering ACID transactions, referential integrity
and crash recovery by default.

MySQL 5.5 also provides a number of additional enhancements including:

  - Significantly improved performance on Windows, with various
Windows specific features and improvements
  - Higher availability, with new semi-synchronous replication and
Replication Heartbeat
  - Improved usability, with Improved index and table partitioning,
SIGNAL/RESIGNAL support and enhanced diagnostics, including a new
Performance Schema monitoring capability.

For a more complete look at what's new in MySQL 5.5, please see the
following resources:

MySQL 5.5 is GA, Interview with Tomas Ulin:

  http://dev.mysql.com/tech-resources/interviews/thomas-ulin-mysql-55.html

Documentation:

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

If you are running a MySQL production level system, we would like to
direct your attention to MySQL Enterprise Edition, which includes the
most comprehensive set of MySQL production, backup, monitoring,
modeling, development, and administration tools so businesses can
achieve the highest levels of MySQL performance, security and uptime.

  http://mysql.com/products/enterprise/

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

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

For upgrading from previous MySQL releases, please see the important
upgrade considerations at:

  http://dev.mysql.com/doc/refman/5.5/en/upgrading.html

MySQL Database 5.5.55 is available in source and binary form for a
number of platforms from our download pages at:

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

The following section lists the changes in the MySQL source code since
the previous released version of MySQL 5.5. It may also be viewed
online at:

  http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-55.html

Enjoy!

Changes in MySQL 5.5.55 (2017-04-10, General availability)


 * Compilation Notes

 * Configuration Notes

 * Packaging Notes

 * Security Notes

 * Thread Pool Notes

 * Functionality Added or Changed

 * Bugs Fixed

   Compilation Notes

 * Windows builds now use the default runtime libraries
   (builds use the /MD flag). (Bug #25611609)

   Configuration Notes

 * MySQL failed to compile if -DENABLE_DEBUG_SYNC=OFF AND
   -DWITH_DEBUG=ON were both given. The ENABLE_DEBUG_SYNC
   option has been removed and enabling WITH_DEBUG enables
   Debug Sync. (Bug #18374703)

   Packaging Notes

 * Reminder: MySQL 5.5 requires the Microsoft Visual C++
   2008 Redistributable Package to run on Windows platforms.
   Users should make sure the package has been installed on
   the system before starting the server. The package is
   available at the Microsoft Download Center
   (http://www.microsoft.com/en-us/download/default.aspx).

   Security Notes

 * The mysql_options() C API function now supports a
   MYSQL_OPT_SSL_MODE option. The only permitted option
   value is SSL_MODE_REQUIRED, to require a secure
   connection to the server. It causes mysql_real_connect()
   to fail if an encrypted connection cannot be obtained,
   without falling back to an unencrypted connection. Thus,
   mysql_real_connect() returns an error if the server does
   not support SSL or the client is not configured to use
   SSL. The client/server exchange terminates immediately
   after the initial server packet has been received if the
   server indicates that it does not support SSL.
   To require an encrypted connection in MySQL 5.5, the
   standard MySQL client programs call mysql_options() to
   set MYSQL_OPT_SSL_MODE if the --ssl-mode=REQUIRED
   command-line option was specified. Third-party
   applications that must be able to require encrypted
   connections can use the same technique. For details, see
   mysql_ssl_set()
 (http://dev.mysql.com/doc/refman/5.5/en/mysql-ssl-set.html).
   The minor C API version number was not incremented for
   this change. Application programs compiled for MySQL 5.5
   that require MYSQL_OPT_SSL_MODE may fail to operate
   properly if the dynamic loader provides an older client
   library without MYSQL_OPT_SSL_MODE. Such applications
   must be written to handle this possibility by checking
   whether the mysql_options() call succeeds or fails. (Bug

MySQL for Visual Studio 1.2.7 has been released

[Hery Ramilison]

Dear MySQL users,

The MySQL Windows Experience Team is proud to announce the release
of MySQL for Visual Studio 1.2.7.

This is a maintenance release for 1.2.x. It can be used for
production environments.

MySQL for Visual Studio is a product that includes all of the
Visual Studio integration functionality to create and manage
MySQL databases when developing .NET applications.

MySQL for Visual Studio is installed using the MySQL Installer
for Windows which comes in 2 versions:
   * Full (386.6 MB) which includes a complete set of MySQL products
 with their binaries included in the downloaded bundle.
   * Web (1.7 MB - a network install) which will just pull MySQL
 for Visual Studio over the web and install it when run.

You can download MySQL Installer from our official Downloads page at
http://dev.mysql.com/downloads/installer/.

MySQL for Visual Studio can also be downloaded by using the product
standalone installer found at
http://dev.mysql.com/downloads/windows/visualstudio/.

Changes in MySQL for Visual Studio 1.2.7 (2017-04-10)

   Functionality Added or Changed

 * Removed Support for Microsoft Visual Studio 2010.

 * Added support for Microsoft Visual Studio 2017.

   Bugs Fixed

 * The MySQL Data Export Tool exported data from the wrong
   schema when a second schema was selected and then
   deselected. In addition, all selected schemas were
   deselected when a single schema was deselected. (Bug
   #25713981, Bug #2366)

 * Script files did not accept most keyboard input after the
   file was saved for the first time. (Bug #25713638, Bug
   #24751945)

What Is New In MySQL for Visual Studio 1.2
---
- New MySQL Project Items for creating data views in Windows Forms and
  ASP.NET MVC web applications.

- A new option in web configuration tool for the ASP.NET Personalization
  Provider (this feature requires MySQL Connector/NET 6.9 or newer).

- A new option in web configuration tool for the ASP.NET Site Map
  Provider   (this feature requires MySQL Connector/NET 6.9 or newer).

- A new option for the MySQLSimpleMembership provider in the web
  configuration tool. (This feature requires MySQL Connector/NET or newer).


MySQL Windows Forms Project Item
---
This Project Item is available on the Add New Item dialog in Visual Studio
when adding a new item to an existing project.

The dialog presented to create the MySQL Windows Forms Project Item
automates the generation of a Windows Form, representing a view for
MySQL data available through an existing Entity Framework's model
entity containing a MySQL table or view.

Different view types are available to present the data:
   * Single-column: A form that contains one control by each existing
 column in the table with navigation controls and that allows CRUD
 operations.All controls can include validations for numeric and
 DateTime data types.

   * Grid: A form with a data grid view that contains navigation controls.

   * Master-detail: A form with a single control layout for the Parent 
table

 and a data grid view to navigate through child table's data.

Supported with C# or Visual Basic language. This feature requires
Connector/NET 6.7.5, 6.8.3 or 6.9.x.

For more details on the features included check the documentation at:
https://dev.mysql.com/doc/connector-net/en/visual-studio-project-items-forms.html


MySQL ASP.NET MVC Project Item
---
This Project Item is available on the Add New Item dialog in Visual Studio
when adding a new item to an existing project.

The dialog presented to create the MySQL ASP.NET MVC Item automates the
generation of a controller and its corresponding view, representing a view
for MySQL data available through an existing Entity Framework's model entity
containing a MySQL table or view. The MVC versions supported by this wizard
are 3 when using Visual Studio 2010 or 2012, and 4 when using Visual Studio
2013 or greater.

The generation of the MVC items is done by creating an Entity Framework data
model either with Entity Framework version 5 or 6 depending on the user's
selection.

Supported with C# or Visual Basic language. This feature requires
Connector/NET 6.7.5, 6.8.3 or 6.9.x.

For more details on the features included check the documentation at:
https://dev.mysql.com/doc/connector-net/en/visual-studio-project-items-mvc.html


New option in web configuration tool for the ASP.NET Personalization 
Provider

---
Personalization provider allows to store personalization state-state 
data regarding
the content and layout of Web Parts pages-generated by the Web Parts 
personalization
service using MySQL as a 

MySQL Community Server 5.6.36 has been released

[Prashant Tekriwal]

Dear MySQL users,

MySQL Server 5.6.36, a new version of the popular Open Source
Database Management System, has been released. MySQL 5.6.36 is
recommended for use on production systems.

For an overview of what's new in MySQL 5.6, please see

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.36 on new servers or upgrading
to MySQL 5.6.36 from previous MySQL releases, please see

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

MySQL Server is available in source and binary form for a number of
platforms from our download pages at

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

Not all mirror sites may be up to date at this point in time, so if you
can't find this version on some mirror, please try again later or choose
another download site.

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

https://wikis.oracle.com/display/mysql/Contributing

The following section lists the changes in the MySQL 5.6 since
the release of MySQL 5.6.35. It may also be viewed
online at

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-36.html

Enjoy!


== 


Changes in MySQL 5.6.36 (2017-04-10, General Availability)


 * Compilation Notes

 * Configuration Notes

 * Packaging Notes

 * Security Notes

 * Thread Pool Notes

 * Functionality Added or Changed

 * Bugs Fixed

   Compilation Notes

 * Windows builds now use the default runtime libraries
   (builds use the /MD flag). (Bug #25611609)

 * CMake support was added for compiling with Developer
   Studio 12.6. (Bug #25384295)

   Configuration Notes

 * MySQL failed to compile if -DENABLE_DEBUG_SYNC=OFF AND
   -DWITH_DEBUG=ON were both given. The ENABLE_DEBUG_SYNC
   option has been removed and enabling WITH_DEBUG enables
   Debug Sync. (Bug #18374703)

   Packaging Notes

 * Changes in RPM package structure require a larger set of
   packages to be removed to install MySQL Server cleanly.
   (Bug #25603087)

 * To avoid potential race conditions, Debian packages now
   use the GNU install utility rather than a combination of
   mkdir, touch, and chown. (Bug #25258829)

 * CMake-generated packaging for Debian/Ubuntu packages was
   refactored for improved maintainability. The change
   includes updated logic for correctly replacing native
   distribution packaging in Debian and Ubuntu. (Bug
   #25126961, Bug #25251872, Bug #84198)

 * Reminder: MySQL 5.6 requires the Microsoft Visual C++
   2010 Redistributable Package to run on Windows platforms.
   Users should make sure the package has been installed on
   the system before starting the server. The package is
   available at the Microsoft Download Center
   (http://www.microsoft.com/en-us/download/default.aspx).

   Security Notes

 * The linked OpenSSL library for the MySQL Commercial
   Server has been updated to version 1.0.2k. For a
   description of issues fixed in this version, see
http://www.openssl.org/news/vulnerabilities.html.
   This change does not affect the Oracle-produced MySQL
   Community build of MySQL Server, which uses the yaSSL
   library instead. (Bug #25768671)

 * The mysql_options() C API function now supports a
   MYSQL_OPT_SSL_MODE option. The only permitted option
   value is SSL_MODE_REQUIRED, to require a secure
   connection to the server. It causes mysql_real_connect()
   to fail if an encrypted connection cannot be obtained,
   without falling back to an unencrypted connection. Thus,
   mysql_real_connect() returns an error if the server does
   not support SSL or the client is not configured to use
   SSL. The client/server exchange terminates immediately
   after the initial server packet has been received if the
   server indicates that it does not support SSL.
   To require an encrypted connection in MySQL 5.6, the
   standard MySQL client programs call mysql_options() to
   set MYSQL_OPT_SSL_MODE if the --ssl-mode=REQUIRED
   command-line option was specified. Third-party
   applications that must be able to require encrypted
   connections can use the same 

ANN: DAC for MySQL 3.1.0 is out!

[Aleksander Andreev]

MicroOLAP Direct Access Components for MySQL and Delphi/C++ Builder (also
known as MySQLDAC) is a Borland Delphi/C++Builder component set for direct
connect to MySQL database server. DAC for MySQL allows you to create
Delphi/C++Builder applications without BDE, ODBC, ADO and without
libmysql.dll.

What’s new in v3.1.0:
This release includes RAD Studio 10.2 Tokyo support and several bug fixes
and improvements.

Full changelog:

[!] RAD Studio 10.2 Tokyo support has been introduced
[*] Additional check added for TMySQLDatabase global list existence in
finalization
[-] "An empty default value of fields when using TMySQLQuery with the
RequestLive property" bug was fixed

You're welcome to download the DAC for MySQL v3.1.0 right now at:
http://microolap.com/products/connectivity/mysqldac/download/ ,
or login to your private area on our site at http://microolap.com/my/downlo
ads/

Please don't hesitate to ask any questions or report bugs with our Support
Ticketing system available at http://www.microolap.com/support/

-- 
Aleksandr Andreev
Developer
MicroOLAP Technologies LTD
aleksandr.andr...@gf.microolap.com
http://microolap.com

Re: MySQL server has gone away

[Reindl Harald]

Am 03.04.2017 um 21:22 schrieb Mahmood N:

well, who did set it that low?


ِDon't know. Maybe the previous admin hadn't used mysql for sending emails!!


on a proper server you have a local smtpd like postfix listening on 
127.0.0.1 and hence you can send thousands of messages within seconds 
from a web-application and your local relay queues messages and try to 
deliver them by default up to 5 days


when you use a remote smtpd directly froma php application your are 
doing all wrong - what when the smtpd is not reachable or got restarted 
in the middle of sending?


how to act on a 4xx temporary error?

anything which takes more then 30 seconds needs to be fixed proper and 
then the timeout would not have been a probkem at all



On Monday, April 3, 2017 11:37 PM, Reindl Harald
 wrote:

Am 03.04.2017 um 20:41 schrieb Mahmood N:

Good news!

I changed wait_timeout=30 to wait_timeout=600 and now the error
disappears... I don't know if long_query_time=1 has effect.


well, who did set it that low?

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout

http://orbisius.com/howto/web-development/change-mysqls-wait_timeout-interactive_timeout-variables/



On Monday, April 3, 2017 10:32 PM, Mahmood N > wrote:

I tested with both 5 and 1 and see the log files are empty.

I am really confused about that error and it is taking more than 2 weeks
about that!


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

Re: MySQL server has gone away

[Mahmood N]

>well, who did set it that low?
ِDon't know. Maybe the previous admin hadn't used mysql for sending emails!!
Anyway, thanks.
 Regards,
Mahmood 

On Monday, April 3, 2017 11:37 PM, Reindl Harald  
wrote:
 

 

Am 03.04.2017 um 20:41 schrieb Mahmood N:
> Good news!
>
> I changed wait_timeout=30 to wait_timeout=600 and now the error
> disappears... I don't know if long_query_time=1 has effect.

well, who did set it that low?

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout

http://orbisius.com/howto/web-development/change-mysqls-wait_timeout-interactive_timeout-variables/

> On Monday, April 3, 2017 10:32 PM, Mahmood N  wrote:
> I tested with both 5 and 1 and see the log files are empty.
>
> I am really confused about that error and it is taking more than 2 weeks
> about that!

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



   

Re: MySQL server has gone away

[Reindl Harald]

Am 03.04.2017 um 20:41 schrieb Mahmood N:

Good news!

I changed wait_timeout=30 to wait_timeout=600 and now the error
disappears... I don't know if long_query_time=1 has effect.


well, who did set it that low?

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout

http://orbisius.com/howto/web-development/change-mysqls-wait_timeout-interactive_timeout-variables/


On Monday, April 3, 2017 10:32 PM, Mahmood N  wrote:
I tested with both 5 and 1 and see the log files are empty.

I am really confused about that error and it is taking more than 2 weeks
about that!


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

Re: MySQL server has gone away

[Mahmood N]

Good news!
I changed wait_timeout=30 to wait_timeout=600 and now the error disappears... I 
don't know if long_query_time=1 has effect. Regards,
Mahmood 

On Monday, April 3, 2017 10:32 PM, Mahmood N  wrote:
 

 Dear reindl,
I tested with both 5 and 1 and see the log files are empty.
I am really confused about that error and it is taking more than 2 weeks about 
that!
 Regards,
Mahmood 


   

   

Re: MySQL server has gone away

[Mahmood N]

Dear reindl,
I tested with both 5 and 1 and see the log files are empty.
I am really confused about that error and it is taking more than 2 weeks about 
that!
 Regards,
Mahmood 


   

Re: MySQL server has gone away

[Reindl Harald]

Am 03.04.2017 um 19:45 schrieb Mahmood N:

So I set long_query_time=5 and restarted the service. Test the email
page again. Still the logs are empty


WTF - you had it set to 5 seconds 2 hours ago
i am out here...

Am 03.04.2017 um 18:11 schrieb Mahmood N:
> The my.conf file contains
>
> log_error=/var/log/mysql/error.log
> slow_query_log=1
> slow_query_log_file=/var/log/mysql/error_slow.log
> max_connections=200
> max_user_connections=30
> wait_timeout=30
> interactive_timeout=50
> long_query_time=5
> character-set-client-handshake = FALSE
> thread_concurrency = 8
> query_cache_size = 16M
> thread_cache_size = 8
> max_allowed_packet = 8M

"long_query_time=5" is low when you talk about 5 seconds as i remember 
in previuos posts - given that a reasonable server should be able to 
handle hunredts to thousands of requests per second anything above 1 
second is a alert sign


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

Re: MySQL server has gone away

[Mahmood N]

So I set long_query_time=5 and restarted the service. Test the email page 
again. Still the logs are empty.
 Regards,
Mahmood 


   

Re: MySQL server has gone away

[Mahmood N]

>since when is phpinfo() - the *real* active configuration be it changed 
>by some config snippet, vhost-configuration or even ini_set() - the same 
>than a random file in /etc?
Sorry I totally didn't understand that sentence... Regards,
Mahmood 


   

Re: MySQL server has gone away

[Reindl Harald]

Am 03.04.2017 um 19:26 schrieb Mahmood N:

given that a reasonable server should be able to
handle hunredts to thousands of requests per second anything above 1
second is a alert sign


Excuse me, do you mean higher values are better? I didn't understand. I
said in my posts that when I submit the email test, the refresh time for
that page is about 5 minutes.


if you think a moment you realize that lower values are better when 5 
seconds don't log any query and you have obvious slow queries



look also in phpinfo() for mysqlnd and mysql params containing "timeout"
and/or "max"


in /etc/php/7.0/apache2/php.ini I see

;mysqlnd.net_read_timeout = 31536000

Note that is comment


since when is phpinfo() - the *real* active configuration be it changed 
by some config snippet, vhost-configuration or even ini_set() - the same 
than a random file in /etc?


http://php.net/manual/en/function.phpinfo.php

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

Re: MySQL server has gone away

[Mahmood N]

>given that a reasonable server should be able to 
>handle hunredts to thousands of requests per second anything above 1 
>second is a alert sign
Excuse me, do you mean higher values are better? I didn't understand. I said in 
my posts that when I submit the email test, the refresh time for that page is 
about 5 minutes. 

>look also in phpinfo() for mysqlnd and mysql params containing "timeout" 
>and/or "max"
in /etc/php/7.0/apache2/php.ini I see
;mysqlnd.net_read_timeout = 31536000

Note that is comment.

 Regards,
Mahmood 


   

Re: MySQL server has gone away

[Reindl Harald]

Am 03.04.2017 um 18:11 schrieb Mahmood N:

The my.conf file contains

log_error=/var/log/mysql/error.log
slow_query_log=1
slow_query_log_file=/var/log/mysql/error_slow.log
max_connections=200
max_user_connections=30
wait_timeout=30
interactive_timeout=50
long_query_time=5
character-set-client-handshake = FALSE
thread_concurrency = 8
query_cache_size = 16M
thread_cache_size = 8
max_allowed_packet = 8M


"long_query_time=5" is low when you talk about 5 seconds as i remember 
in previuos posts - given that a reasonable server should be able to 
handle hunredts to thousands of requests per second anything above 1 
second is a alert sign



I restarted the mysql server (/etc/init.d/mysql restart on ubuntu) and
tested the email page one again. I again see that error message on the
browser, however, the log files are empty.


look also in phpinfo() for mysqlnd and mysql params containing "timeout" 
and/or "max"


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

Re: MySQL server has gone away

[Mahmood N]

The my.conf file contains
log_error=/var/log/mysql/error.log
slow_query_log=1
slow_query_log_file=/var/log/mysql/error_slow.log
max_connections=200
max_user_connections=30
wait_timeout=30
interactive_timeout=50
long_query_time=5
character-set-client-handshake = FALSE
thread_concurrency = 8
query_cache_size = 16M
thread_cache_size = 8
max_allowed_packet = 8M


I restarted the mysql server (/etc/init.d/mysql restart on ubuntu) and tested 
the email page one again. I again see that error message on the browser, 
however, the log files are empty.
 Regards,
Mahmood 


   

Re: MySQL server has gone away

[Reindl Harald]

Am 03.04.2017 um 17:52 schrieb Mahmood N:

Dear all,
Currently max_allowed_packet is set to 8M. That test email is simply a test 
email containing some basic information in the message body to assure that the 
email system works.
Thing that can help me is to put mysql in the debug mode in one terminal and at 
the same time, submit a test email from the browser. Then I can see what is 
going there in sql. This process is similar to sshd debug mode where I can turn 
on the ssh service in the debug mode and see what is what. Is there is any 
other option for debugging, please let me know.


The MySQL Error Log may  contain details explaining why mysqld was unable to 
stay running

If you are referring to /var/log/musql/error.log then I have to say it is empty!


Perhaps you could connect your mysql client, load the screen, then run show 
full processlist every second or so to see what queries are going on.

As I said, I know few things about mysql. Please let me know the steps to do 
that


https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

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

Re: MySQL server has gone away

[Mahmood N]

Dear all,
Currently max_allowed_packet is set to 8M. That test email is simply a test 
email containing some basic information in the message body to assure that the 
email system works.
Thing that can help me is to put mysql in the debug mode in one terminal and at 
the same time, submit a test email from the browser. Then I can see what is 
going there in sql. This process is similar to sshd debug mode where I can turn 
on the ssh service in the debug mode and see what is what. Is there is any 
other option for debugging, please let me know.



>The MySQL Error Log may  contain details explaining why mysqld was unable to 
>stay running
If you are referring to /var/log/musql/error.log then I have to say it is empty!



>Perhaps you could connect your mysql client, load the screen, then run show 
>full processlist every second or so to see what queries are going on.
As I said, I know few things about mysql. Please let me know the steps to do 
that
 Regards,
Mahmood 
  Show original message 

   

Re: MySQL server has gone away

[shawn l.green]

On 4/3/2017 8:15 AM, Mahmood N wrote:

When I click on the submit button in Moodle and it is waiting for refresh, I 
execute the mysql command but the output is not meaningful

mahmood@ce:/var/www/html/courses$ mysql -u moodle -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30912
Server version: 5.5.54-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show full processlist
 ->
 ->



Meanwhile using Webmin, I execute the same command for that user and see
  Output from SQL command show full processlist ..
| Id | User | Host | db | Command | Time | State | Info |
| 30912 | moodle | localhost |
  | Sleep | 42 |



I am not expert with MySQL, however as the Moodle admin I am trying to fix the 
problems.
Regards,
Mahmood



You need to consider a few possibilities,

a) Moodle didn't want to wait long enough for the query to complete (a 
Moodle Timeout) so it said "the server is not responding..."


b) Moodle sent MySQL a command that was "too large".  To protect itself 
from abuse, all MySQL instances have a configurable limit about how 
"large" a command can be. If the command is larger than this limit, the 
server rejects it and closes the connection.  (this could explain why 
the query you just attempted from Moodle is not visible in the list of 
executing commands)


c) Something is unstable in your MySQL instance. The MySQL Error Log may 
contain details explaining why mysqld was unable to stay running.  The 
angel process mysqld_safe would try to restart the server automatically 
which could explain why Moodle was only unresponsive for a short while.


Additional resources:
https://dev.mysql.com/doc/refman/5.6/en/problems.html   (in particular, 
review B.5.2)


--
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: MySQL server has gone away

[Mahmood N]

When I click on the submit button in Moodle and it is waiting for refresh, I 
execute the mysql command but the output is not meaningful

mahmood@ce:/var/www/html/courses$ mysql -u moodle -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30912
Server version: 5.5.54-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show full processlist
    ->
    ->



Meanwhile using Webmin, I execute the same command for that user and see
 Output from SQL command show full processlist ..
| Id | User | Host | db | Command | Time | State | Info |
| 30912 | moodle | localhost | 
 | Sleep | 42 |



I am not expert with MySQL, however as the Moodle admin I am trying to fix the 
problems.
Regards,
Mahmood



   

Re: MySQL server has gone away

[Johnny Withers]

I'd suspect the underlying query is poorly designed for the amount of data
you have stored. If you have access to the mysql server you could connect
to it using any mysql client and run 'show full processlist' to see the
query as that page is trying to load. The query is probably in the 'sending
data' state and gets killed at the limit of one of the timeout variables.
This causes the application to throw that error you have.

Copy that query out, prefix it with explain and see if there are any tables
in the query where an index could be added to optimize the database for the
query.



On Mon, Apr 3, 2017 at 6:09 AM, Ken D'Ambrosio  wrote:

> Basically, it says that MySQL is not responding to queries. So it likely
> has died, or perhaps is mis-configured.
>
> On April 3, 2017 7:07:25 AM EDT, Mahmood N  wrote:
> >Hi,I am using Moodle which itself uses SQL for the database. Problem is
> >that, when I run the email plugin and execute the command, the refresh
> >time of the page becomes high (in the order of 3-5 minutes) and at the
> >end, I see this message
> >Debug info: MySQL server has gone away
> >SELECT id, sid, state, userid, lastip, timecreated, timemodified FROM
> >mdl_sessions WHERE sid = ?
> >[array (
> > 0 => 'jqfbgd5b0q6e2l81bb5gb87mn3',
> >)]
> >Error code: dmlreadexceptionStack trace:
> >  - line 479 of /lib/dml/moodle_database.php: dml_read_exception thrown
> >- line 1175 of /lib/dml/mysqli_native_moodle_database.php: call to
> >moodle_database->query_end()
> >- line 1551 of /lib/dml/moodle_database.php: call to
> >mysqli_native_moodle_database->get_records_sql()
> >- line 1523 of /lib/dml/moodle_database.php: call to
> >moodle_database->get_record_sql()
> >- line 1502 of /lib/dml/moodle_database.php: call to
> >moodle_database->get_record_select()
> >- line 286 of /lib/classes/session/manager.php: call to
> >moodle_database->get_record()
> >- line 82 of /lib/classes/session/manager.php: call to
> >core\session\manager::initialise_user_session()
> >   - line 785 of /lib/setup.php: call to core\session\manager::start()
> >   - line 27 of /config.php: call to require_once()
> >   - line 30 of /index.php: call to require_once()
> >
> >
> >Although it looks like a bug in Moodle, but the guys said it is a MySQL
> >issue. I am confused about that. If you have any idea please let me
> >know. What does this error say exactly?
> >
> > Regards,
> >Mahmood
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.




-- 
-
Johnny Withers
601.209.4985
joh...@pixelated.net

Re: MySQL server has gone away

[Mahmood N]

Thanks for the quick reply. So, how can I get further information? 

Thing is that, after 5 minutes, when I refresh the page every thing is normal.
 Regards,
Mahmood 

On Monday, April 3, 2017 3:39 PM, Ken D'Ambrosio  wrote:
 

 Basically, it says that MySQL is not responding to queries. So it likely has 
died, or perhaps is mis-configured.

On April 3, 2017 7:07:25 AM EDT, Mahmood N  wrote:
Hi,I am using Moodle which itself uses SQL for the database. Problem is that, 
when I run the email plugin and execute the command, the refresh time of the 
page becomes high (in the order of 3-5 minutes) and at the end, I see this 
message
Debug info: MySQL server has gone away
SELECT id, sid, state, userid, lastip, timecreated, timemodified FROM 
mdl_sessions WHERE sid = ?
[array (
 0 => 'jqfbgd5b0q6e2l81bb5gb87mn3',
)]
Error code: dmlreadexceptionStack trace:
   - line 479 of /lib/dml/moodle_database.php: dml_read_exception thrown
   - line 1175 of /lib/dml/mysqli_native_moodle_database.php: call to 
moodle_database->query_end()
   - line 1551 of /lib/dml/moodle_database.php: call to 
mysqli_native_moodle_database->get_records_sql()
   - line 1523 of /lib/dml/moodle_database.php: call to 
moodle_database->get_record_sql()
   - line 1502 of /lib/dml/moodle_database.php: call to 
moodle_database->get_record_select()
   - line 286 of /lib/classes/session/manager.php: call to 
moodle_database->get_record()
   - line 82 of /lib/classes/session/manager.php: call to 
core\session\manager::initialise_user_session()
   - line 785 of /lib/setup.php: call to core\session\manager::start()
   - line 27 of /config.php: call to require_once()
   - line 30 of /index.php: call to require_once()


Although it looks like a bug in Moodle, but the guys said it is a MySQL issue. 
I am confused about that. If you have any idea please let me know. What does 
this error say exactly?

 Regards,
Mahmood

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

   

Re: MySQL server has gone away

[Ken D'Ambrosio]

Basically, it says that MySQL is not responding to queries. So it likely has 
died, or perhaps is mis-configured.

On April 3, 2017 7:07:25 AM EDT, Mahmood N  wrote:
>Hi,I am using Moodle which itself uses SQL for the database. Problem is
>that, when I run the email plugin and execute the command, the refresh
>time of the page becomes high (in the order of 3-5 minutes) and at the
>end, I see this message
>Debug info: MySQL server has gone away
>SELECT id, sid, state, userid, lastip, timecreated, timemodified FROM
>mdl_sessions WHERE sid = ?
>[array (
> 0 => 'jqfbgd5b0q6e2l81bb5gb87mn3',
>)]
>Error code: dmlreadexceptionStack trace:
>  - line 479 of /lib/dml/moodle_database.php: dml_read_exception thrown
>- line 1175 of /lib/dml/mysqli_native_moodle_database.php: call to
>moodle_database->query_end()
>- line 1551 of /lib/dml/moodle_database.php: call to
>mysqli_native_moodle_database->get_records_sql()
>- line 1523 of /lib/dml/moodle_database.php: call to
>moodle_database->get_record_sql()
>- line 1502 of /lib/dml/moodle_database.php: call to
>moodle_database->get_record_select()
>- line 286 of /lib/classes/session/manager.php: call to
>moodle_database->get_record()
>- line 82 of /lib/classes/session/manager.php: call to
>core\session\manager::initialise_user_session()
>   - line 785 of /lib/setup.php: call to core\session\manager::start()
>   - line 27 of /config.php: call to require_once()
>   - line 30 of /index.php: call to require_once()
>
>
>Although it looks like a bug in Moodle, but the guys said it is a MySQL
>issue. I am confused about that. If you have any idea please let me
>know. What does this error say exactly?
>
> Regards,
>Mahmood

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

MySQL server has gone away

[Mahmood N]

Hi,I am using Moodle which itself uses SQL for the database. Problem is that, 
when I run the email plugin and execute the command, the refresh time of the 
page becomes high (in the order of 3-5 minutes) and at the end, I see this 
message
Debug info: MySQL server has gone away
SELECT id, sid, state, userid, lastip, timecreated, timemodified FROM 
mdl_sessions WHERE sid = ?
[array (
 0 => 'jqfbgd5b0q6e2l81bb5gb87mn3',
)]
Error code: dmlreadexceptionStack trace:
   - line 479 of /lib/dml/moodle_database.php: dml_read_exception thrown
   - line 1175 of /lib/dml/mysqli_native_moodle_database.php: call to 
moodle_database->query_end()
   - line 1551 of /lib/dml/moodle_database.php: call to 
mysqli_native_moodle_database->get_records_sql()
   - line 1523 of /lib/dml/moodle_database.php: call to 
moodle_database->get_record_sql()
   - line 1502 of /lib/dml/moodle_database.php: call to 
moodle_database->get_record_select()
   - line 286 of /lib/classes/session/manager.php: call to 
moodle_database->get_record()
   - line 82 of /lib/classes/session/manager.php: call to 
core\session\manager::initialise_user_session()
   - line 785 of /lib/setup.php: call to core\session\manager::start()
   - line 27 of /config.php: call to require_once()
   - line 30 of /index.php: call to require_once()


Although it looks like a bug in Moodle, but the guys said it is a MySQL issue. 
I am confused about that. If you have any idea please let me know. What does 
this error say exactly?

 Regards,
Mahmood

[ANN] Mroonga 7.01 - Fast fulltext search for all languages on MySQL

[Kentaro Hayashi]

Hi,

Mroonga 7.01 has been released!

Mroonga is a MySQL storage engine that supports fast fulltext search
and geolocation search.  It is CJK ready. It uses Groonga as a storage
and fulltext search engine.

Document:
   http://mroonga.org/docs/

How to install: Install Guide
   http://mroonga.org/docs/install.html

How to upgrade: Upgrade Guide
   http://mroonga.org/docs/upgrade.html

Blog:
   http://mroonga.org/en/blog/2017/03/29/mroonga-7.01.html

Changes:
   http://mroonga.org/docs/news.html#release-7.01

There are some topics in this release.

  * Dropped CentOS 5 support because of EOL
  * [Storage mode] Supported fast ORDER LIMIT with ENUM.
  * Supported COMPRESS_ZSTD column compression flag.
  * Visual Studio 2015 or later is required to build from source.

Thanks.
-- 
Kentaro Hayashi 


pgpDYoZLrtZ7r.pgp
Description: PGP signature

Re: seeing errors

[william drescher]

On 3/25/2017 10:03 AM, Hal.sz S.ndor wrote:

2017/03/24 15:19 ... william drescher:

While loading a database using the mysql command 'source'  I see
occasional warnings flash by on the screen.

when it finished I used the command "show warnings," but only one
warning showed (the last one).

How can I see the warnings earlier in the load ?


There is an option to the client program mysql,
"--show-warnings", that not summaries but full messages are
shown. Furthermore, if, as by default, warnings, too, go to the
error log, you will find everything interesting there.


Thank you


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

Re: seeing errors

[Hal.sz S.ndor]

2017/03/24 15:19 ... william drescher:

While loading a database using the mysql command 'source'  I see
occasional warnings flash by on the screen.

when it finished I used the command "show warnings," but only one
warning showed (the last one).

How can I see the warnings earlier in the load ?


There is an option to the client program mysql, "--show-warnings", that 
not summaries but full messages are shown. Furthermore, if, as by 
default, warnings, too, go to the error log, you will find everything 
interesting there.


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

seeing errors

[william drescher]

While loading a database using the mysql command 'source'  I see 
occasional warnings flash by on the screen.


when it finished I used the command "show warnings," but only one 
warning showed (the last one).


How can I see the warnings earlier in the load ?

-bill


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

MySQL Connector/C++ 2.0.4 m2 has been released

[Daniel Horecki]

Dear MySQL users,

MySQL Connector/C++ 2.0.4 is the next development milestone of the MySQL
Connector/C++ 2.0 series. Connector/C++ 2.0 can be used to access MySQL
implementing Document Store or in a traditional way, using SQL queries. 
It allows writing both C++ applications using X DevAPI or plain C 
applications using XAPI.


To learn more about how to write applications using X DevAPI, see X
DevAPI User Guide (http://dev.mysql.com/doc/x-devapi-userguide/en/) and 
X DevAPI reference at

https://dev.mysql.com/doc/dev/connector-cpp/devapi_ref.html. For more
information about using plain C XAPI see XAPI reference at
http://dev.mysql.com/doc/dev/connector-cpp/xapi_ref.html. For generic
information on using Connector/C++ 2.0, see
http://dev.mysql.com/doc/dev/connector-cpp/.

Note

Connector/C++ 2.0 requires MySQL Server version 5.7.12 or higher
with X Plugin enabled. For general documentation about how to get
started using MySQL as a document store, see Using MySQL as a Document
Store (http://dev.mysql.com/doc/refman/5.7/en/document-store.html).

To download MySQL Connector/C++ 2.0.4, see the "Development Releases"
tab at http://dev.mysql.com/downloads/connector/cpp/

Changes in MySQL Connector/C++ 2.0.4 (2017-03-16, Development
Milestone)

   Functionality Added or Changed

 * Support was added for secure sessions over TLS
   connections. A secure session can be requested either via
   the ssl-enable and ssl-ca options of a connection string,
   or using explicit session creation options. For X DevAPI
   session settings, see

http://dev.mysql.com/doc/dev/connector-cpp/classmysqlx_1_1_session_settings.html.
   For XAPI session settings, see
   http://dev.mysql.com/doc/dev/connector-cpp/group__xapi.html
   (check the documentation for enum mysqlx_opt_type_t).

 * The format of document ID values generated when adding
   documents to a collation has changed. It is still a
   string of 32 hexadecimal digits based on UUID, but the
   order of digits was changed to match the requirement of a
   stable ID prefix.

 * The X DevAPI Schema object now supports methods for view
   manipulation: createView(), alterView(), and dropView().
   XAPI now contains functions that implement similar
   functionality: mysqlx_view_create(),
   mysqlx_view_replace(), mysqlx_view_modify(), and
   (implemented previously) mysqlx_view_drop().
   As with other XAPI operations, there are functions that
   create a statement handle without executing it:
   mysqlx_view_create_new(), mysqlx_view_replace_new(), and
   mysqlx_view_modify_new().
   These XAPI functions modify view DDL statements before
   execution: mysqlx_set_view_algorithm(),
   mysqlx_set_view_security(),
   mysqlx_set_view_check_option(),
   mysqlx_set_view_definer(), and mysqlx_set_view_columns().

 * Connector/C++ now supports IPv6 target hosts in
   connection strings and when creating sessions using other
   methods.

   Bugs Fixed

 * When rList is an empty list, table.insert().rows(rList)
   caused a segmentation fault. (Bug #25515964)

On Behalf of the MySQL/ORACLE RE Team


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

MySQL Connector/Net 7.0.7 m6 has been released

[Prashant Tekriwal]

Dear MySQL users,


MySQL Connector/Net 7.0.7 is the fourth development release that expands
cross-platform support to Linux and OS X when using Microsoft's .NET Core
framework. Now, .NET developers can use the X DevAPI with .NET Core and
Entity Framework Core (EF Core) 1.0 to create server applications that run
on Windows, Linux and OS X. We are very excited about this change and
really look forward to your feedback on it!

MySQL Connector/Net 7.0.7 is also the sixth development release of MySQL
Connector/Net to add support for the new X DevAPI.  The X DevAPI enables
application developers to write code that combines the strengths of the
relational and document models using a modern, NoSQL-like syntax that
does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see
http://dev.mysql.com/doc/x-devapi-userguide/en/index.html. For more
information about how the X DevAPI is implemented in Connector/Net, see
http://dev.mysql.com/doc/dev/connector-net.

Please note that the X DevAPI requires at least MySQL Server version
5.7.12 or higher with the X Plugin enabled. For general documentation
about how to get started using MySQL as a document store, see
http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

To download MySQL Connector/Net 7.0.7 M6, see the "Development
Releases" tab at http://dev.mysql.com/downloads/connector/net/

Changes in MySQL Connector/Net 7.0.7 (Not yet released,
Development)

   Functionality Added or Changed

 * X DevAPI: Added support for Internet Protocol version 6
   (IPv6) addresses. Host names can now resolve as IPv4 or
   IPv6 addresses.

 * X DevAPI: Connection string syntax is now identical to
   the URI scheme, which provides a cross-product syntax for
   defining the connection data to establish a session.

 * X DevAPI: Added new methods to the Schema class to
   create, alter, and drop views. Usage notes include:

  + Views created with the
Schema.CreateView().DefinedAs() method chain are
supported for use with the table Select method, but
are not supported with the collection Find method.

  + A collection view created in the database by some
mechanism other than the CreateView method is not
defined as a view by Table.IsView.

  + Query objects assigned to a view with the CreateView
method are static, even when the underlying query
changes.

Nuget packages are available at:

https://www.nuget.org/packages/MySql.Data/7.0.7-m6
https://www.nuget.org/packages/MySql.Web/7.0.7-m6
https://www.nuget.org/packages/MySql.Data.Entity/7.0.7-m6
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/7.0.7-m6

Enjoy and thanks for the support!

On behalf of the MySQL Release Engineering Team
Prashant Tekriwal

MySQL Connector/Java 6.0.6-m5 has been released

[Gipson Pulla]

Dear MySQL users,

MySQL Connector/J 6.0.6 m5 Development Release is a developer milestone
release for the 6.0.x series.
This release includes the following new features and changes, also described
in more detail on
https://dev.mysql.com/doc/relnotes/connector-j/6.0/en/news-6-0-6.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 6.0.6 m5, see the "Development
Releases" tab at http://dev.mysql.com/downloads/connector/j/

Enjoy!

Changes in MySQL Connector/J 6.0.6 (2017-03-10, Milestone 5)

   Version 6.0.6 Milestone is the fifth development release of
   the 6.0 branch of MySQL Connector/J, providing an insight
   into upcoming features. It is suitable for use with MySQL
   Server versions 5.5, 5.6, and 5.7. It supports the Java
   Database Connectivity (JDBC) 4.2 API.

 * Functionality Added or Changed

 * Bugs Fixed

   Functionality Added or Changed

 * X DevAPI: The getPluginVersion() method has been removed
   from Connector/J, as it is no longer supported by the X
   Protocol. (Bug #25056803)

 * IPv6 host addresses are now supported for connections
   using the X Protocol.

 * X DevAPI: The following new connection options have been
   added for SSL/TLS configuration:

  + xdevapi.ssl-enable

  + xdevapi.ssl-truststore

  + xdevapi.ssl-verify-server-certificate
   See Configuration Properties
(http://dev.mysql.com/doc/connector-j/6.0/en/connector-j-reference-configuration-properties.html)
   for details.

 * Connector/J now supports the new character set collations
   implemented in MySQL Server 8.0. See Unicode Character
   Sets
(http://dev.mysql.com/doc/refman/5.7/en/charset-unicode-sets.html) 
   for details.

 * X DevAPI: DDL for views are now supported by the new
   methods createView(), dropView(), and alterView().
   However, the functions do not support partitioning in an
   InnoDB cluster or sharding.

 * X DevAPI: The Connector/J X Dev API has been reorganized:

  + All interfaces for public usage have been moved to
the com.mysql.cj.api.xdevapi package, and their
implementation classes to com.mysql.cj.xdevapi.

  + All Connector/J internal interfaces have been moved
to the com.mysql.cj.api.x.core and
com.mysql.cj.api.x.io packages, and their
implementation classes to com.mysql.cj.x.core and
com.mysql.cj.x.io.

  + Protobuf generated classes have been moved to the
com.mysql.cj.x.protobuf package.

  + The MysqlxSessionFactory has been renamed
XSessionFactory.

   Bugs Fixed

 * When Connector/J was reading a TIMESTAMP value into an
   instance of a JSR-310 data type, the reading was wrong
   when the time did not exist in the local time zone of the
   JVM due to a time change for Daylight Saving Time. It was
   because Connector/J created first a java.sql.Timestamp
   instance (which used the JVM's time zone) for the value
   and then converted it to, for example,
   java.time.LocalDateTime. With this fix, a JSR-310 object
   is created directly to avoid the conversion. (Bug
   #24658016, Bug #82964)

 * Query executions using prepared statements failed with
   the error "Unknown ProtocolEntity class null" when the
   connection property useCursorFetch was set to true. (Bug
   #24527173)

 * When server-side prepared statements were used, updates
   to result sets failed in errors. (Bug #24525461)

 * The getString() method returned wrong millisecond values
   for the TIMESTAMP data type. (Bug #24512766, Bug #82707)

 * Updates to a document object of the Dbdoc type failed
   when the document contained an array. (Bug #24471057)

 * The method isNumberSigned() returned true for columns of
   type VARCHAR, which was wrong as it should return false
   for any non-numeric columns, according to the JDBC
   Specification. The behavior has now been corrected. (Bug
   #24350526)

 * An IllegalArgumentException was thrown when lenient was
   false for a Calendar object and the hours, minutes, and
   seconds explicitly set via the Calendar constructor did
   not match the values resulted from the timezone
   conversion of the Calendar object's date value. (Bug
   #23702040, Bug #82005)

 * X DevAPI: The getLastDocumentIds() method only reported
   document IDs specified by users, but not those generated
   by Connector/J. With this fix, all IDs are now reported.
   (Bug #23519211)

 * A connection failed with IllegalStateException:
   TrustManagerFactoryImpl is not initialized after
   Connector/J set javax.net.ssl.trustStore. (Bug #23510894)

 * A NullPointerException was thrown 

udf shared library import

[Tim Holme]

I was not able to load a function in udf_example.so.  The response to
CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';

is

Error Code: 1126. Can't open shared library 'udf_example.so' (errno: 0
/usr/lib64/mysql/plugin/udf_example.so: cannot open shared object file: No
such file or directory)

I did follow instructions
1. compile udf_example.so with
gcc -fPIC -I/usr/include/mysql/ -shared -o udf_example.so udf_example.c
2. verify my plugin directory:
show variables like 'plugin%' : /usr/lib64/mysql/plugin/
3. place udf_example.so in /usr/lib64/mysql/plugin/
4. verify I have a table mysql.func and a column 'type': SELECT * FROM
mysql.func;

Any hints?  Thank you.

MySQL Connector/NodeJS 1.0.6 M5 has been released

[Lars Tangvald]

Dear MySQL users,

MySQL Connector/Node.js is a new Node.js driver for use with the X
DevAPI. This release, v1.0.6 M5, is the fourth development release of the
MySQL Connector/Node.js 1.0 series.

The X DevAPI enables application developers to write code that combines
the strengths of the relational and document models using a modern,
NoSQL-like syntax that does not assume previous experience writing
traditional SQL.

MySQL Connector/Node.js can be downloaded through npm (see
https://www.npmjs.com/package/@mysql/xdevapi for details) or from
https://dev.mysql.com/downloads/connector/nodejs/.

To learn more about how to write applications using the X DevAPI, see
http://dev.mysql.com/doc/x-devapi-userguide/en/. For more information
about how the X DevAPI is implemented in MySQL Connector/Node.js, and
its usage, see http://dev.mysql.com/doc/dev/connector-nodejs/.

Please note that the X DevAPI requires at least MySQL Server version
5.7.12 or higher with the X Plugin enabled. For general documentation
about how to get started using MySQL as a document store, see
http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

Changes in MySQL Connector/Node.js 1.0.6 (2017-03-07, Milestone 5)

   Functionality Added or Changed

 * Added support for validating the server certificate with
   a given CA and/or CRL.

 * Added support for creating TLS sessions with a URI or
   connection string.

 * Added support for creating IPv6 sessions with a URI or
   connection string.

 * Added support for single array or multiple argument
   function calls on the public API.

   Bugs Fixed

 * Fixed issues with collection.bind(). (Bug #23236379)

 * Fixed parsing issues on URI and connection string
   corner-cases.

 * Updated behavior of collection.add([]) to avoid confusing
   exceptions.

Enjoy and thanks for the support!

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

MySQL Cluster Manager 1.4.2 has been released

[Hery Ramilison]

Dear MySQL Users,

MySQL Cluster Manager 1.4.2 has been released and can be downloaded
from the My Oracle Support (MOS) website. It will also be available
on Oracle Software Delivery Cloud at http://edelivery.oracle.com with
the next monthly update

MySQL Cluster Manager is an optional component of the MySQL Cluster Carrier
Grade Edition, providing a command-line interface that automates common
management tasks, including the following online operations:
 - Configuring and starting MySQL Cluster
 - Upgrades
 - Adding and removing cluster nodes
 - Adding and removing site hosts
 - Configuration changes
 - Backup and restore

MySQL Cluster Manager is a commercial extension to the MySQL family of 
products.

More details can be found at http://www.mysql.com/products/cluster/mcm/

A brief summary of changes in MySQL Cluster Manager version 1.4.2 is 
listed below:


Changes in MySQL Cluster Manager 1.4.2 (2017-03-07)

   This section documents all changes and bug fixes that have
   been applied in MySQL Cluster Manager 1.4.2 since the release
   of MySQL Cluster Manager version 1.4.1.

   Functionality Added or Changed

 * Agent: To allow easy detection of an incomplete agent
   backup, an empty file named INCOMPLETE is created in the
   folder in which the backup is created when the backup
   agents command begins, and is deleted after the backup is
   finished. The continuous existence of the file after the
   backup process is over indicates that the backup is
   incomplete. (Bug #25126866)

 * Agent: MySQL Cluster Manager can now recover
   automatically a failed mysqld node, as long as the data
   directory of the node is empty when recovery is
   attempted; if that is not the case, after cleaning up the
   data directory manually, users can now manually run the
   start process --initial to rebuild the mysqld node's data
   directory. (Bug #18415446)

 * Agent: A new command, update process, imports a process
   back into the control of mcmd after mcmd has lost track
   of the process' status due to different reasons (for
   example, it has been restarted manually outside of MySQL
   Cluster Manager). For more details, see the description
   of the command.

 * Agent: The show status command now reports progress when
   the new --progress or --progressbar options is used.

   Bugs Fixed

 * Agent: When a custom FileSystemPath value was used for a
   data node, the list backups and restore cluster commands
   failed, as the backup directory could not be found. (Bug
   #25549903)

 * Agent: In some situations, a certain mcmd agent took too
   long to process event messages that a synchronization
   timeout occurred among the agents. This was because the
   agent went into a mutex contention for file access, which
   this fix removes. (Bug #25462861)

 * Agent: The collect logs command reported success even if
   file transfers were incomplete. This fix adds checks for
   file transfer completion and reports any errors. (Bug
   #25436057)

 * Agent: An ndbmtd node sometimes (for example, at a
   rolling restart of the cluster) sent out a large amount
   of event messages, and it might take too long for an mcmd
   agent to process them that the agent lagged behind on its
   readiness for the next command, resulting in a
   synchronization timeout among the mcmd agents. This fix
   drastically reduced the amount of event messages sent by
   an ndbmtd node, thus reducing the chance of a
   synchronization timeout under the situation. (Bug
   #25358050)

 * Agent: A management node failure might trigger mcmd to
   quit unexpectedly on Windows platforms. (Bug #25336594)

 * Agent: Multiple errors thrown by the backup agents,
   rotate logs, and change log-level commands could
   potentially overwrite each other, causing a lost of error
   information. (Bug #25134452)

 * Agent: The collect logs command hung when TCP connections
   could not be established between the agent that initiated
   the command and the other agents. This fix makes the
   command timeout after the situation persists for more
   than 30s. Also, a new mcmd option, --copy-port, has been
   added, by which users can specify the TCP port number to
   be used for log copying. (Bug #25064313)

 * Agent: The .mcm file created by the import config
   --dryrun command sometimes have certain configuration
   settings missing from it. (Bug #24962848)

 * Agent: A restore cluster command would fail if MySQL
   Cluster Manager did not have write access to the
   BackupDataDir of each data node. The unnecessary
   requirement has now been removed. (Bug #24763936)

 * Agent: If a stop cluster or a stop process command had
   failed, a restart on some of the processes might fail
   with 

MySQL Router 2.1.2 RC has been released

[Balasubramanian Kandasamy]

Dear MySQL users,

MySQL Router 2.1.2 RC is a release candidate for MySQL Router 2.1.

The MySQL Router is a new building block for high availability
solutions based on MySQL InnoDB clusters.

By taking advantage of the new Group Replication technology,
and combined with the MySQL Shell, InnoDB clusters provide an
integrated solution for high availability and scalability for
InnoDB based MySQL databases, that does not require advanced MySQL
expertise.

The deployment of applications with high availability requirements
is greatly simplified by MySQL Router. MySQL client connections are
transparently routed to online members of a InnoDB cluster, with
MySQL server outages and cluster reconfigurations being automatically
handled by the Router.

MySQL Router 2.1 also introduces the following additions:

* Automatic setup/bootstrap with a MySQL InnoDB cluster
* Support for the new X protocol
* Support for deployments inside self-contained directories
* Easy and secure password management using a built-in keyring

You can download MySQL Router from http://dev.mysql.com/downloads/router.
Package binaries are available for several platforms and also as a
source code download.

Documentation for MySQL Router can be found at
http://dev.mysql.com/doc/mysql-router/en

Enjoy!

Changes in MySQL Router 2.1.2 (2017-03-06, Release Candidate)


 * Functionality Added or Changed

 * Bugs Fixed

   Functionality Added or Changed

 * Windows: downloads now require Visual C++ Redistributable
   for Visual Studio 2015, when before the 2013 version was
   required.

 * MySQL Fabric support was removed.

 * mysqlrouter --help output was improved to include the
   current default folder locations for the system, and also
   usage examples.

 * X Protocol support was added.
   The new protocol configuration option was added to
   support the X Protocol. Setting protocol to x enables the
   X Protocol for connections, otherwise the default classic
   protocol is used.

 * New SSL command line options: --ssl-mode, --ssl-ca,
   --ssl-capath, --ssl-cipher, --ssl-crl, --ssl-crlpath, and
   --tls-version.

 * Keyring key management was added to securely manage
   passwords.
   With this, the new master_key_path and keyring_path
   configuration options were added.

 * Bootstrapping support was added.
   New bootstrapping command line options: --bootstrap,
   --conf-base-port, --conf-bind-address,
   --conf-use-sockets, --conf-skip-tcp, --directory,
   --force, and --name

   Bugs Fixed

 * While bootstrapping router, the process to discover local
   interfaces had a memory leak. (Bug #25456674)

 * Fixed "use of uninitialized bytes" issues as discovered
   by valgrind. (Bug #25455825)

 * After bootstrapping router with the --conf-use-sockets
   and --directory options, the socket path configuration
   value defined in the generated configuration file was
   invalid. This was because it used the socketsdir value in
   the socket path, but the directory was not created as
   part of the bootstrap process or when router was started.
   (Bug #25391460)

 * On Windows, immediately starting a bootstrapped Router
   installation would fail to load the generated
   configuration file.
   In addition, the generated text for missing configuration
   files was improved to also include the paths that were
   checked. (Bug #25343904)


On behalf of Oracle MySQL Release Team
Balasubramanian Kandasamy

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

MySQL Router 2.1.2 RC has been released

[Balasubramanian Kandasamy]

Dear MySQL users,

MySQL Router 2.1.2 RC is a release candidate for MySQL Router 2.1.

The MySQL Router is a new building block for high availability
solutions based on MySQL InnoDB clusters.

By taking advantage of the new Group Replication technology,
and combined with the MySQL Shell, InnoDB clusters provide an
integrated solution for high availability and scalability for
InnoDB based MySQL databases, that does not require advanced MySQL
expertise.

The deployment of applications with high availability requirements
is greatly simplified by MySQL Router. MySQL client connections are
transparently routed to online members of a InnoDB cluster, with
MySQL server outages and cluster reconfigurations being automatically
handled by the Router.

MySQL Router 2.1 also introduces the following additions:

* Automatic setup/bootstrap with a MySQL InnoDB cluster
* Support for the new X protocol
* Support for deployments inside self-contained directories
* Easy and secure password management using a built-in keyring

You can download MySQL Router from http://dev.mysql.com/downloads/router.
Package binaries are available for several platforms and also as a
source code download.

Documentation for MySQL Router can be found at
http://dev.mysql.com/doc/mysql-router/en

Enjoy!

Changes in MySQL Router 2.1.2 (2017-03-06, Release Candidate)


 * Functionality Added or Changed

 * Bugs Fixed

   Functionality Added or Changed

 * Windows: downloads now require Visual C++ Redistributable
   for Visual Studio 2015, when before the 2013 version was
   required.

 * MySQL Fabric support was removed.

 * mysqlrouter --help output was improved to include the
   current default folder locations for the system, and also
   usage examples.

 * X Protocol support was added.
   The new protocol configuration option was added to
   support the X Protocol. Setting protocol to x enables the
   X Protocol for connections, otherwise the default classic
   protocol is used.

 * New SSL command line options: --ssl-mode, --ssl-ca,
   --ssl-capath, --ssl-cipher, --ssl-crl, --ssl-crlpath, and
   --tls-version.

 * Keyring key management was added to securely manage
   passwords.
   With this, the new master_key_path and keyring_path
   configuration options were added.

 * Bootstrapping support was added.
   New bootstrapping command line options: --bootstrap,
   --conf-base-port, --conf-bind-address,
   --conf-use-sockets, --conf-skip-tcp, --directory,
   --force, and --name

   Bugs Fixed

 * While bootstrapping router, the process to discover local
   interfaces had a memory leak. (Bug #25456674)

 * Fixed "use of uninitialized bytes" issues as discovered
   by valgrind. (Bug #25455825)

 * After bootstrapping router with the --conf-use-sockets
   and --directory options, the socket path configuration
   value defined in the generated configuration file was
   invalid. This was because it used the socketsdir value in
   the socket path, but the directory was not created as
   part of the bootstrap process or when router was started.
   (Bug #25391460)

 * On Windows, immediately starting a bootstrapped Router
   installation would fail to load the generated
   configuration file.
   In addition, the generated text for missing configuration
   files was improved to also include the paths that were
   checked. (Bug #25343904)


On behalf of Oracle MySQL Release Team
Balasubramanian Kandasamy

MySQL Shell 1.0.8 RC has been released

[Kent Boortz]

Dear MySQL users,

MySQL Shell 1.0.8 RC is the first release candidate of the MySQL
for MySQL Shell 1.0 series.

The MySQL Shell is an interactive JavaScript, Python and SQL
command-line interface, supporting development and administration for
the MySQL Server. The MySQL Shell includes the X DevAPI that enables
developers to use a new Create Read Update Delete (CRUD) API. MySQL
Shell is a component of the MySQL Server.

This release introduces support for the creation and management of
MySQL InnoDB clusters from MySQL servers with the AdminAPI.
By taking advantage of the new Group Replication technology,
InnoDB clusters provide an integrated solution for high availability
and scalability for InnoDB based MySQL databases, without requiring
advanced MySQL expertise.

And with MySQL Router 2.1, applications can take advantage of a
highly available and scalable MySQL InnoDB cluster with minimal or
no changes in their code.

This is a Developer Release, so we are looking forward to getting
your feedback on the MySQL Shell as well as the new API and all the
other new features.

The MySQL Shell provides

 * MySQL High Availability and Scaling API: AdminAPI

 * Both Interactive and Batch operations

 * JavaScript, Python, and SQL language modes

 * Document and Relational Models

 * CRUD Document and Relational API: DevAPI

 * Output results in Traditional Table, JSON, and Tab Separated formats

 * And much more

To download the MySQL Shell

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

To learn more about MySQL Shell, a component of the MySQL Server, see

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

To learn more about using MySQL as a document store, see

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

For MySQL Shell Tutorials

  JavaScript - 
http://dev.mysql.com/doc/refman/5.7/en/mysqlx-shell-tutorial-javascript.html
  Python - 
http://dev.mysql.com/doc/refman/5.7/en/mysqlx-shell-tutorial-python.html

User documentation for the X DevAPI

  http://dev.mysql.com/doc/x-devapi-userguide/en/index.html

For more information about how the X DevAPI is implemented in MySQL Shell,
and its usage, see http://dev.mysql.com/doc/dev/mysqlsh-devapi/

User documentation for InnoDB cluster which can be administered using
the AdminAPI included with MySQL Shell:

  https://dev.mysql.com/doc/mysql-innodb-cluster/en/

==

Changes in MySQL Shell 1.0.8 (2017-03-06)

 * Functionality Added or Changed

 * Bugs Fixed

   Functionality Added or Changed

 * The URI is now a positional argument when starting MySQL
   Shell at the command line. (Bug #25077429)

 * In the MySQL Shell Python X DevAPI implementation the
   mysqlx and mysql modules have been moved into the new
   mysqlsh module. This changes the way you import the
   modules, now you should issue:

 mysql-py> from mysqlsh import mysql
 mysql-py> from mysqlsh import mysqlx

   (Bug #25030138)

 * XSessions have been removed, which means the --x command
   option is deprecated. The default session is now a
   NodeSession. (Bug #24958348, Bug #83553)

 * The stored sessions functionality has been removed.
   (Bug #24949016, Bug #83530)

 * MySQL Shell version 1.0.8 includes the newly released
   AdminAPI available in JavaScript and Python which enables
   you to set up and manage InnoDB clusters. It provides a
   modern fluent API which wraps the complexity associated
   with configuring, provisioning and managing an InnoDB
   cluster, without sacrificing power, flexibility or
   security.

   Bugs Fixed

 * When using Python mode, assigning a key element that was
   identified as a type could cause an unexpected halt. The
   fix ensures that keys which are strings are correctly
   identified as such as are interpreted as keys.
   (Bug #25191539)

 * Attempting to create a connection with the wrong user or
   password on Linux resulted in an Error Unknown option
   trace_protocol message being displayed. The fix ensures
   that the correct ERROR: 1045 (28000): Access denied for
   user error is displayed. (Bug #25071433, Bug #83759)

 * Statements that were not executed, for example due to a
   syntax error, were not being added to the command
   history. This has been improved so that any statement is
   added to the command history and can be accessed using
   the up and down cursor keys. Additionally the internal
   MySQL Shell are now added to the command history.
   (Bug #24967864)
   References: See also: Bug #24669771.

 * When MySQL Shell had an open connection and was in SQL
   mode, pressing Control-C caused an unexpected halt.
   (Bug #24812731)
   References: See also: Bug #24663772, Bug #23065126.

 * The output of the \help command has been updated to
 

MySQL Enterprise Backup 4.1.0 has been released

[Karen Langford]

Dear MySQL users,

MySQL Enterprise Backup v4.1.0, a new version of the online MySQL backup
tool, is now available for download from the My Oracle Support (MOS) website
as our latest GA release. This release will be available on eDelivery (OSDC)
after the next upload cycle. MySQL Enterprise Backup is a commercial
extension to the MySQL family of products.

MySQL Enterprise Backup 4.1.0 supports only the MySQL Server 5.7.9 and
above.  For any earlier versions of the MySQL server, please use MySQL
Enterprise Backup 3.12 instead.

A brief summary of the changes in MySQL Enterprise Backup (MEB)
version 4.1.0 is given below.

Changes in MySQL Enterprise Backup 4.1.0 (2017-03-03)

   Functionality Added or Changed

 * MySQL Enterprise Backup now supports the --ssl-mode
   option, which enables you to specify the security state
   of the connection to the server. It replaces the client
   side --ssl and --ssl-verify-server-cert options, which
   are now deprecated. See the description of the --ssl-mode
   option in MySQL 5.7 Reference Manual
   (http://dev.mysql.com/doc/refman/5.7/en/) for details.
   (Bug #23508228)

 * A new option, --skip-final-rescan, makes mysqlbackup skip
   the final rescan for InnoDB tables that are modified by
   DDL operations after the database has been locked near
   the end of a backup operation. This potentially shortens
   the duration for the lock and reduces the backup's impact
   on the server's normal operation. See the description for
   --skip-final-rescan for details. (Bug #21094221)

 * The output by mysqlbackup, which goes to the stderr
   stream and the message log, has now been improved to
   include the timestamps and thread IDs for all steps taken
   by mysqlbackup, in order to provide more information for
   debugging purposes. (Bug #20142619)

 * During the final stage of a backup when MySQL Enterprise
   Backup tried to temporarily put the database into a
   read-only state using the FLUSH TABLES WITH READ LOCK
   statement in order to copy non-InnoDB files, if a long
   query was running on the server at the same time, the
   FLUSH TABLES WITH READ LOCK statement could be taking too
   long to finish, holding up further queries and eventually
   bringing down the server.
   A new mysqlbackup option --lock-wait-timeout can now be
   used to specify the timeout in seconds for the FLUSH
   TABLES WITH READ LOCK statement. If the timeout is
   exceeded, the statement is failed and the lock on the
   tables is released, so that queries held up by the lock
   can then be executed. mysqlbackup then retries the
   statement and continues with the backup. Default value
   for --lock-wait-timeout is 60 [seconds]. (Bug #14339483)

 * A full set of exit codes have now been implemented for
   MySQL Enterprise Backup. Also, a new mysqlbackup command,
   print-message, returns an exit message for any given exit
   code supplied with the new option --error-code. See Exit
   codes of MySQL Enterprise Backup
   (http://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/ 

meb-exitcodes.html 
) 
for details.


 * To increase the performance for hot backups, mysqlbackup
   now shortens the final phase of the backups by resizing
   the MyISAM key cache before it locks the database with a
   FLUSH TABLES WITH READ LOCK statement. The resize
   triggers a flush of the MyISAM key cache, which reduces
   the time it takes to run the FLUSH TABLES WITH READ LOCK
   statement. The MyISAM key cache size is changed back to
   its original value afterward.

 * Apply-log operations can now be performed with multiple
   worker threads in parallel, which can improve performance
   for the operations. The number of threads to be used can
   be specified with the --process-thread option.

 * The copying of redo log files into backups has been made
   faster, shortening the overall backup time in some cases
   and making it less likely that a backup fails because a
   redo log file has been overwritten before it is copied.

 * MySQL Enterprise Backup now supports optimistic
   incremental backup, in which mysqlbackup scans only those
   InnoDB data files that have been modified since the last
   backup for changed pages and then saves them into the
   incremental backup. It potentially makes incremental
   backups faster, and is performed by specifying
   --incremental=optimistic. See Full-scan versus Optimistic
   Incremental Backup
   (http://dev.mysql.com/doc/mysql-enterprise-backup/4.1/ 

Optimize table partitions

[Machiel Richards]

Good day everyone

 I hope this mail is finding everyone well.

 I am asking for some insight again today as I can not seem to find
the correct info on the net.

 Some of our database tables are quite large with millions of records.

 However due to disk space issues, we have done a huge amount of
archiving by moving data to archiving servers and deleting from main
server.

 We would like to however run optimize table in order to reclaim some
of the disk space , but we are facing the following issues :

   - Running optimize on the full table not possible due to lack of
disk space.
- We then tried to optimize per partition using "Alter Table
 optimize partition ;"

  Optimizing per partition however seems to be taking extremely long
as we are only able to optimize 35 partitions (of the 1024
partitions in the table) during the course of 4 hours.



 I would like to know whether there is a more efficient way to do this?

 These are live servers that we can not unfortunately take offline
(i.e. lock table as per some suggestions found).


I would really appreciate any feedback in this matter.


Regards


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

Re: multiple domain names mapped to a single IP

[Reindl Harald]

Am 02.03.2017 um 16:41 schrieb Bruce Ferrell:

There are two ways to approach this:

1.) Turn off name resolution in MySQL and only do the ACL by IP. This is
probably best as name resolution can slow the database and cause
outright app failure if DNS fails for any reason.

2.) Make absolutely certain the names resolve correctly in DNS... Then
see point 1 above.


he is talking about *server* address not the client

that you always should use "skip-name-resolve" and never ever set 
permissions based on reverse-DNS because a) when DNS lags everything 
lags and b) it is easy for many people which control the PTR of their 
zone let it answer what ever yxou wanted to see for grant access


but again: pointing with several hostnames to the same IP has nothing to 
do with reverse-lookup at all (nowhere)


on the other hand it makes no sense at all in context of a 
database-server because it has no concept of vhosts at all



On 3/2/17 7:01 AM, Kaushal Shriyan wrote:

Is there any pros and cons to multiple domain names mapped to a single IP
work in MySQL client server setup like in case of httpd webserver
there is
a concept of VHost having multiple domain names mapped to a single IP?

For example :-

int-mysqldbserver1.example.com :- 192.168.0.11
int-mysqldbserver2.example.com :- 192.168.0.11

Will there be a issue when i point full qualified domain name in the
application which uses mysql client program since both domain names are
pointing to the same IP?

Any help will be highly appreciable.


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

Re: multiple domain names mapped to a single IP

[Bruce Ferrell]

Kaushal

There are two ways to approach this:

1.) Turn off name resolution in MySQL and only do the ACL by IP. This is 
probably best as name resolution can slow the database and cause 
outright app failure if DNS fails for any reason.


2.) Make absolutely certain the names resolve correctly in DNS... Then 
see point 1 above.


On 3/2/17 7:01 AM, Kaushal Shriyan wrote:

Hi,

Is there any pros and cons to multiple domain names mapped to a single IP
work in MySQL client server setup like in case of httpd webserver there is
a concept of VHost having multiple domain names mapped to a single IP?

For example :-

int-mysqldbserver1.example.com :- 192.168.0.11
int-mysqldbserver2.example.com :- 192.168.0.11

Will there be a issue when i point full qualified domain name in the
application which uses mysql client program since both domain names are
pointing to the same IP?

Any help will be highly appreciable.

Regards,

Kaushal




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

Re: multiple domain names mapped to a single IP

[Reindl Harald]

Am 02.03.2017 um 16:01 schrieb Kaushal Shriyan:

Is there any pros and cons to multiple domain names mapped to a single IP
work in MySQL client server setup like in case of httpd webserver there is
a concept of VHost having multiple domain names mapped to a single IP?

For example :-

int-mysqldbserver1.example.com :- 192.168.0.11
int-mysqldbserver2.example.com :- 192.168.0.11

Will there be a issue when i point full qualified domain name in the
application which uses mysql client program since both domain names are
pointing to the same IP?

Any help will be highly appreciable


as long there are no tls certificates are verified which in that case 
should contain SAN for both names a client don't care anyways for names, 
it just connects to a IP address relsoved over whatever method 
(/etc/hosts, DNS, NSS)


how would a client which resolves "int-mysqldbserver1.example.com" even 
know that "int-mysqldbserver2.example.com" exists - it can't which 
answers the primary question


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

multiple domain names mapped to a single IP

[Kaushal Shriyan]

Hi,

Is there any pros and cons to multiple domain names mapped to a single IP
work in MySQL client server setup like in case of httpd webserver there is
a concept of VHost having multiple domain names mapped to a single IP?

For example :-

int-mysqldbserver1.example.com :- 192.168.0.11
int-mysqldbserver2.example.com :- 192.168.0.11

Will there be a issue when i point full qualified domain name in the
application which uses mysql client program since both domain names are
pointing to the same IP?

Any help will be highly appreciable.

Regards,

Kaushal

Re: Changing a field's data in every record

[Hal.sz S.ndor]

2017/02/18 ... debt:

Is there a formula to change the format of the data in a single field in 
every record of a table?  She has a "timestamp” in a text field formatted as 
2017|02|16|04|58|42 and she wants to convert it to a more human readable format like 
 2017-02-16 @ 04:58:42


It now occurs to me, long after a working answer has been found, that 
because MySQL is none too picky about the strings that it turns into 
timestamps, that adding a DATETIME field, TS say, and thus updating is 
enough programming:


UPDATE tbl SET TS=timestampFormatted_CharacterStringfield;

MySQL takes any punctuation for a separator, and cares only about the 
numerals between the separators.


There is also the function STR_TO_DATE which takes a string to convert 
and a format string.


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

MySQL Connector/J 5.1.41 has been released

[Gipson Pulla]

Dear MySQL Users,

MySQL Connector/J 5.1.41, a maintenance release of the production 5.1
branch has been released. Connector/J is the Type-IV pure-Java JDBC
driver for MySQL.

Version 5.1.41 is suitable for use with many MySQL server versions,
including 4.1, 5.0, 5.1, 5.4 and 5.5.

MySQL Connector Java is available in source and binary form from the
Connector/J download pages at
http://dev.mysql.com/downloads/connector/j/5.1.html
and mirror sites as well as Maven-2 repositories.

MySQL Connector Java (Commercial) is already available for download on the
My Oracle Support (MOS) website. This release will be available on eDelivery
(OSDC) in next month's upload cycle.

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.

MySQL Connector/J 5.1.41 includes the following general bug fixes and
improvements, also available in more detail on
http://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1-41.html

Changes in MySQL Connector/J 5.1.41 (2017-02-28)

   Version 5.1.41 is a maintenance release of the production 5.1
   branch. It is suitable for use with MySQL server versions
   5.5, 5.6, and 5.7. It supports the Java Database Connectivity
   (JDBC) 4.2 API.

   Bugs Fixed

 * Connections failed with MySQLSyntaxErrorException:
   Unknown character set when
   connectionCollation=ISO-8859-13. This was due to a wrong
   logic in Connector/J's internal charset mapping, which
   has now been fixed. (Bug #25504578)

 * When loading classes through some external class loaders,
   com.mysql.jdbc.Util threw an NoClassDefFoundError. This
   was caused by Class.getPackage() returning null when some
   external class loaders were used. This fix replaces those
   calls of Class.getPackage() with calls of the new method
   Class.getName(), which return package names that are
   extracted from the fully-qualified class names. (Bug
   #25048543, Bug #83052)

 * In the manifest for the Connector/J JAR file, the
   Import-Package directive specified version numbers for
   the javax.net.ssl package. The specification was
   unnecessary, and it caused the configuration of an SSL
   connection to a MySQL server to fail in an OSGi
   environment. The version requirement has now been
   removed. (Bug #24942672, Bug #82826)

 * In a Fabric setup, when multiple threads required to have
   hashes computed, an ArrayIndexOutOfBoundsException might
   be thrown from inside HashShardMapping. This fix prevents
   the issue by having
   HashShardMapping.getShardIndexForKey() synchronized. (Bug
   #24289730, Bug #82203)

 * When the configuration property cacheResultSetMetadata
   was set to true, a ping query using a PreparedStatement
   failed with a NullPointerException. This fix moves the
   ping query to an earlier stage of the statement
   execution, which prevents the exception. (Bug #23535001,
   Bug #81706)

 * The setFabricShardTable() method failed to parse
   qualified table names (in the format of
   database_name.table_name), which causes SQLExceptions to
   be thrown. (Bug #23264511, Bug #81108)

 * A race condition occurred when a call of
   Connection.setNetworkTimeout() was followed closely by a
   call of Connection.close(), and a NullPointerException
   might result if the connection was closed before the
   executor supplied to setNetworkTimeout() was able to set
   the timeout, as the executor would run into a null
   mysqlConnection object. This fix removed the race
   condition. (Bug #21181249, Bug #75615)

 * With the connection properties
   cacheServerConfiguration=true and
   elideSetAutoCommits=true, any new connection to the
   server obtained after the first connection was
   established had the variable autoCommit equaled false,
   even if the value of the variable was true on the server.
   That was because the value of autoCommit was not properly
   initialized when a new connection was established, and
   this fix corrects that.
   Also, since release 5.1.41, the functionality of the
   property elideSetAutoCommits has been disabled due to
   Bug# 66884. Any value given for the property is now
   ignored by Connector/J. (Bug #17756825, Bug #70785)

 * When using Tomcat and a web application that utilized
   Connector/J was down, Tomcat was unable to stop the
   AbandonedConnectionCleanupThread started internally by
   Connector/J, leading to multiple instances of the thread
   when the web application was restarted; or, Tomcat was
   able to stop the thread but unable to restart it on
   reload of the web application. Different combinations of
   Tomcat's default settings, usage of Tomcat's
   ServletContextListener feature, and locations of the

Re: Changing a field's data in every record

[Reindl Harald]

Am 20.02.2017 um 10:35 schrieb Lucio Chiappetti:

On Sat, 18 Feb 2017, debt wrote:


How does one "grab" the existing data and then change it? Can this
be done solely in MySQL


I am not sure to understand your question ... you usually manipulate
data inside mysql ... but here it seems to me you are not talking of
changing the data VALUE (UPDATE table SET column=new value WHERE ...)
but of changing the DATA TYPE (ALTER TABLE table CHANGE or ALTER TABLE
table MODIFY depending on whether ypou change the column name too) or
even of (just) changing the FORMAT in which a value is displayed ...


but what he needs is a "select field from bla; foreach($data as 
$row){str_replace('x', 'y'); update table" for every record and he would 
like to do it in pure sql



Is there a formula to change the format of the data in a single
field in every record of a table?  She has a "timestamp??? in a
text field formatted as 2017|02|16|04|58|42 and she wants to
convert it to a more human readable format like 2017-02-16 @
04:58:42


curious ... the default format for a time stamp is very similar to the
latter (except for you funny at-sign) :

mysql> select time from north33 limit 1;
+-+
| time|
+-+
| 2013-01-22 12:47:47 |
+-+


and what has this to do with a TEXTFILED CONTAINING STRINGS LIKE 
"2017|02|16|04|58|42" - you need to understand what you quote first 
before you can comment it...



and only if I cast it to an integer I get the other form

mysql> select time+0 from north33 limit 1;
++
| time+0 |
++
| 20130122124747 |
++


completly unreleated to the problem


Anyhow I advise you to read the mysql manual and in particular the
function chapter and the "time and date function" subchapter. E.g. here

https://dev.mysql.com/doc/refman/5.7/en/functions.html


problem is that you did not understand the problem of the OP at all and 
pointing blindly to the manual is useless


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

Re: Changing a field's data in every record

[Lucio Chiappetti]

On Sat, 18 Feb 2017, debt wrote:


How does one "grab" the existing data and then change it? Can this
be done solely in MySQL


I am not sure to understand your question ... you usually manipulate data 
inside mysql ... but here it seems to me you are not talking of changing 
the data VALUE (UPDATE table SET column=new value WHERE ...) but of 
changing the DATA TYPE (ALTER TABLE table CHANGE or ALTER TABLE table 
MODIFY depending on whether ypou change the column name too) or even of 
(just) changing the FORMAT in which a value is displayed ...



Is there a formula to change the format of the data in a single
field in every record of a table?  She has a "timestamp??? in a
text field formatted as 2017|02|16|04|58|42 and she wants to
convert it to a more human readable format like 2017-02-16 @
04:58:42


curious ... the default format for a time stamp is very similar to the 
latter (except for you funny at-sign) :


mysql> select time from north33 limit 1;
+-+
| time|
+-+
| 2013-01-22 12:47:47 |
+-+

and only if I cast it to an integer I get the other form

mysql> select time+0 from north33 limit 1;
++
| time+0 |
++
| 20130122124747 |
++

Anyhow I advise you to read the mysql manual and in particular the 
function chapter and the "time and date function" subchapter. E.g. here


https://dev.mysql.com/doc/refman/5.7/en/functions.html

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

Re: Changing a field's data in every record

[debt]

> On Feb 19, 2017, at 3:50 AM, Brad Barnett  wrote:
> 
> SUBSTRING_INDEX

Thanks a million, Brad!  That works perfectly.  SUBSTRING_INDEX didn’t 
come up in my searches so this is the first I’ve heard of it.

Also, as you could see, it wasn’t a true timestamp.  It is only used 
for viewing, not for searching or sorting, so a text field worked perfectly 
fine for that purpose.  However, after reading everyone’s replies, I convinced 
her to change that field to DATETIME and to lose the ‘@‘.  Now they have the 
best of both worlds - more readable data and a true DATETIME field that will be 
more useful should they ever need it to be.

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

Re: Changing a field's data in every record

[Brad Barnett]

Erm.

I've seen some weird responses to this.  Yes, you can do this.

First -- get the data into a usable format.  Then, put it into a usable
format (eg, timestamp for datetime field).

Read up on how mysql interprets date/time data on fields.  And, create a
new timestamp or date field.

Then, do something like this:

update table set timestamp_field=concat(
SUBSTRING_INDEX(bah,'|',1),"/",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-5),'|',1),"/",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-4),'|',1)," ",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-3),'|',1),":",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-2),'|',1),":",
SUBSTRING_INDEX(SUBSTRING_INDEX(bah,'|',-1),'|',1));

All the data will then be in that timestamp field or datatime column.  A
datetime column is very readable.

As others have mentioned (nicely, and not so nicely), you can easily
format the output of a timestamp or datetime as wanted.






 On Sat, 18 Feb 2017 13:13:38 -0800
debt  wrote:

>   I’ve been asked to post a question here for a friend.
> 
>   Is there a formula to change the format of the data in a single
> field in every record of a table?  She has a "timestamp” in a text
> field formatted as 2017|02|16|04|58|42 and she wants to convert it to a
> more human readable format like  2017-02-16 @ 04:58:42
> 
>   How does one "grab" the existing data and then change it?  Can
> this be done solely in MySQL, or will she have to grab the data and
> then manipulate it in PHP or something?
> 
> Thanks,
> Marc
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
> 

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

Re: Changing a field's data in every record

[Reindl Harald]

Am 19.02.2017 um 11:11 schrieb Peter Brawley:

On 2/18/2017 15:13, debt wrote:

I’ve been asked to post a question here for a friend.

Is there a formula to change the format of the data in a single
field in every record of a table?  She has a "timestamp” in a text
field formatted as 2017|02|16|04|58|42 and she wants to convert it to
a more human readable format like  2017-02-16 @ 04:58:42

How does one "grab" the existing data and then change it?


If it's a timestamp, it's saved as 2017-02-16 04:58:42, not as you
showed it, and there's no need to change it, indeed she couldn't.
Instead, in a query that retrieves the timestamp, use the mysql
date_format() function to format the timestamp as desired.


you missed the quotes around timestamp as well as "in a text field 
formatted" and so THERE IS a need to change it - please consider to read 
before answer


as i read the post i hoped someone will say it nicer in between: 
*nobody* right in his mind saves timestamps in a "human readable format" 
but as a unix timestamp or date with the correct field type


unix timestamps have the advantage that they can be also in PHP simple 
compared with <>, < and > because it#s just the seconds since 1970-01-0-1


since this is nothing but random text you just need to select the data, 
split them, chnage the filed type, write them back and replace the 
direct output with strftime()


http://php.net/manual/de/function.explode.php
http://php.net/manual/de/function.mktime.php
http://php.net/manual/de/function.strftime.php


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

Re: Changing a field's data in every record

[Peter Brawley]

On 2/18/2017 15:13, debt wrote:

I’ve been asked to post a question here for a friend.

Is there a formula to change the format of the data in a single field in 
every record of a table?  She has a "timestamp” in a text field formatted as 
2017|02|16|04|58|42 and she wants to convert it to a more human readable format like 
 2017-02-16 @ 04:58:42

How does one "grab" the existing data and then change it?


If it's a timestamp, it's saved as 2017-02-16 04:58:42, not as you 
showed it, and there's no need to change it, indeed she couldn't. 
Instead, in a query that retrieves the timestamp, use the mysql 
date_format() function to format the timestamp as desired.


PB


   Can this be done solely in MySQL, or will she have to grab the data and then 
manipulate it in PHP or something?

Thanks,
Marc



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

Changing a field's data in every record

[debt]

I’ve been asked to post a question here for a friend.

Is there a formula to change the format of the data in a single field 
in every record of a table?  She has a "timestamp” in a text field formatted as 
2017|02|16|04|58|42 and she wants to convert it to a more human readable format 
like  2017-02-16 @ 04:58:42

How does one "grab" the existing data and then change it?  Can this be 
done solely in MySQL, or will she have to grab the data and then manipulate it 
in PHP or something?

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

[ANN] Mroonga 7.00 - Fast fulltext search for all languages on MySQL

[Kentaro Hayashi]

Hi,

Mroonga 7.00 has been released!  Even though major version upgrade, it
keeps compatibility of Mroonga database.

Mroonga is a MySQL storage engine that supports fast fulltext search
and geolocation search.  It is CJK ready. It uses Groonga as a storage
and fulltext search engine.

Document:
   http://mroonga.org/docs/

How to install: Install Guide
   http://mroonga.org/docs/install.html

How to upgrade: Upgrade Guide
   http://mroonga.org/docs/upgrade.html

Blog:
   http://mroonga.org/en/blog/2017/02/09/mroonga-7.00.html

Changes:
   http://mroonga.org/docs/news.html#release-7.00

There are two topics in this release.

  * Supported FOREIGN KEY constrain on UPDATE and DELETE parent
row. In the previous versions, only FOREIGN KEY constrain on
INSERT is supported.

  * [Storage mode] Supported updating row even though its table has
primary key with ROW binlog format. In the previous version, it
causes update statement error.
-- 
Kentaro Hayashi 


pgpHKkASWJG_I.pgp
Description: PGP signature

MySQL Workbench 6.3.9 GA has been released

[Daniel Horecki]

Dear MySQL users,


The MySQL developer tools team announces 6.3.9 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.9 GA now, for Windows, Mac OS X 10.10+,
Oracle Linux 7, Fedora 24 and 25, Ubuntu 16.04 and 16.10
or sources, from:

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

Enjoy!


Changes in MySQL Workbench 6.3.9 (2017-02-07, General
Availability)

   Functionality Added or Changed

 * Support was added for many MySQL 8.0 features. Note that
   configuration-file support for MySQL 8.0 is not available
   and a warning banner is displayed during attempted use.
   (Bug #24908593, Bug #83422)

 * With MySQL 5.7, several new instruments were added to the
   performance section including the following for Memory
   Usage: Total Memory, Top Memory by Event, Top Memory by
   User, Top Memory by Host, Top Memory by Thread, User
   Resource Usage, and the following for User Resource
   Usage: Overview, I/O Statistics, and Statement
   Statistics. (Bug #21491775)

 * With MySQL 5.7, GTID_MODE can now be set online. (Bug
   #20712931)

 * Workbench is now aware of the
   innodb_optimize_point_storage option that was added in
   MySQL Server 5.7.5, and later removed in MySQL Server
   5.7.6. Note that MySQL Server 5.7.9 was the first 5.7 GA
   release. (Bug #20661496)

 * Mroonga storage engine support is now available for
   tables. (Bug #20058280, Bug #74367)

 * A new DDL tab was added to show the complete create
   statement of a table or view. Click the table inspector
   (info) button next to the table or view in the Schemas
   Navigator to display the new information. (Bug #19889537,
   Bug #74554)

 * Dropped support for DBDesigner 4.

 * Dropped Fabric support.

 * Dropped support for MySQL 5.1. Minimum version is now
   MySQL 5.5.

 * Changed to C++11.

 * Changed to GTK 3 on Linux.

 * OS X: Version 10.7 (Lion) and 10.8 (Mountain Lion)
   support was dropped. Now supporting 10.9 (Mavericks),
   10.10 (Yosemite), 10.11 (El Capitan), and 10.12 (Sierra).

 * Windows: Zip packages and 32-bit binaries are no longer
   published. The .NET Framework version 4.5 is now
   required.

 * Linux: Fedora 23 support dropped and Fedora 25 support
   added. Oracle Linux 6 support was dropped.

 * The option to specify an alternative application data
   directory, instead of the default location, was added.

 * The JSON editor was improved with better parsing and
   error checking.

 * A new Log Level preference (under Others) was added to
   alter the log verbosity level.
   As before, this can still be set by passing in the
   log-level
   (http://dev.mysql.com/doc/workbench/en/wb-command-line-op
   tions.html) command-line argument into Workbench at
   runtime, and doing so overrides the Log Level setting.

 * The bundled sakila_full.mwb model now uses a dedicated
   5.7 version to allow for 5.7 specific features,
   regardless of the version setting in the preferences.

 * Full MySQL 5.7 language support was added, which affects
   grammar, syntax highlighting, preferences, behavior, and
   more.

 * The Home screen was modified: the connections, models,
   and starters were split into individual pages.

   Bugs Fixed

 * With multiple tabs open in the SQL Editor, using the
   context-sensitive menu to close all other tabs caused
   MySQL Workbench to exit unexpectedly. (Bug #25466086, Bug
   #84643)

 * Objects sorted in the schema inspector and then selected
   for an operation, such as Drop Table, generated a
   statement for the wrong object. (Bug #25464011, Bug
   #84697)

 * MySQL Workbench supports the option to require a CA
   certificate or identity validation. (Bug #25408639)

 * If a full backup added warning messages to the backup log
   file, then running an incremental backup later failed.
   (Bug #25315796)

 * No JSON tab appeared for the Open Value in Editor command
   if the field contained an empty array. (Bug #25222455,
   Bug #84106)

 * Schema names were not escaped properly in resulting DROP
   TRIGGER statements when a trigger was created or edited.
   (Bug #25036458, Bug #83655)

 * In some cases, executing a query caused MySQL Workbench
   to become unresponsive when the host was macOS Sierra.
   (Bug #25036263, Bug #83658)

 * Having a single quotation mark (') in the text field of a
   table caused the MySQL Schema Transfer wizard to fail.
   (Bug #24978403, Bug #83616)

 * Simple and complex queries executed from SQL Editor
   

MySQL Utilities 1.6.5 GA has been released

[Hery Ramilison]

Dear MySQL users,

MySQL MySQL Utilities 1.6.5 is a GA version of 1.6 release series. It can be
used for production environments.

MySQL Utilities version 1.6.5 GA is compatible with MySQL Server
versions 5.5 and greater. Python v2.6 and v2.7 are supported.
It is available for download from:

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

A brief summary of changes is listed below. Please check the CHANGES.txt
file inside the distribution for a more complete list of changes.

MySQL Utilities provides a collection of command-line utilities that are
used for maintaining and administering MySQL servers, including:
• Admin Utilities (Clone, Copy, Compare, Diff, Export, Import,
User Management)
• Replication Utilities (Setup, Configuration, Verification)
• General Utilities (Disk Usage, Redundant Indexes, Manage Meta &
Audit Data)
• And more

Changes in MySQL Utilities 1.6.5 (2017-01-25)

   Bugs Fixed

 * Corrected a problem when using a --exec-before script
   that failed causing the mysqlfailover utility to fail
   silently. (Bug #25030151)

 * Added corrections to the message text of some utilities
   to eliminate typographical errors. (Bug #24742356, Bug
   #83173)

 * The mysqldbcopy utility failed to copy views that refer
   other views in the list of databases to be copied. (Bug
   #24657793)

 * Corrected an issue when using anonymous hosts. (Bug
   #24624716, Bug #82636)

 * The mysqlfrm utility failed to parse some .frm files
   properly. This fix ensures that the utility can parse
   tables with many columns in the field list. (Bug
   #24613794, Bug #82907)

 * The mysqldbcopy utility failed to copy tables with a
   primary key set to zero and with AUTO_INCREMENT applied.
   This fix enables the copy and export of auto-increment
   columns with an initial value of zero. For export, the
   user is warned to turn on the NO_AUTO_VALUE_ON_ZERO mode
   for the import and suggested SQL statements to enable the
   mode are provided. For copy, the mode is turned on
   automatically and turned off after the copy. (Bug
   #23598948, Bug #81866)

 * Permits IPv4 address parsing to include values of 0-255
   rather than 0-254. Thanks to Erol Guven for the patch.
   (Bug #23578714, Bug #81834)

 * Tables with BLOB data in fields marked NOT NULL
   restricted the operation of the mysqldbcopy utility. This
   fix enables the utility to copy these tables by
   temporarily altering the columns on the destination to
   remove the NOT NULL option and then to restore it after
   the copy. (Bug #23335753, Bug #81553)

 * Corrected an issue with reading strings from
   configuration files when run with Python 2.6. (Bug
   #22738248)

 * Corrected the error message stating the
   --discover-slaves-login option is not used with the
   failover command for the mysqlrpladmin utility. (Bug
   #22616847, Bug #80082)

 * Corrected the problem when socket files are used in
   replication utilities. Previously, the port defaulted to
   3306 instead of the actual port for the servers. The code
   now retrieves the port from the connected servers so that
   connections of slaves are directed to the correct port.
   (Bug #22543116)

 * Added context to errors resulting from server startup for
   the mysqlserverclone utility. Messages recommend using
   the -vvv option to find the actual error from the server
   startup sequence. (Bug #22457829)

 * The mysqlfrm utility failed when the --verbosity option
   was used. This fix replaces the --verbosity option with
   --verbose, the correct option name, and uses it
   consistently in error messages and code. (Bug #22254445,
   Bug #79382)

 * The mysqlserverinfo utility required connection
   parameters to show all MySQL servers running on the local
   host. This fix removes that restriction when the
   --show-servers option is specified. (Bug #22215626, Bug
   #79135)

 * Corrected a problem in the mysqldbimport utility where
   specifying the --skip option with a valid type failed to
   skip the intended object or objects during the operation.
   (Bug #22155217, Bug #79128)

 * The mysqldbcopy utility failed to copy grants for
   anonymous users. (Bug #22093518, Bug #78940)

 * Improved the --quiet option to silence all informational
   messages for the mysqldbcompare utility. (Bug #22093453,
   Bug #78938)

 * Corrected a problem using a single database name in the
   mysqldiff utility when comparing the same database on two
   servers. (Bug #21764003)

 * The DEB packages did not create the required
   /usr/share/pyshared/mysql directory. (Bug #21626041, Bug
   #78054)

 * Added error handling to ensure only one slave is
   specified for the --slave option for the 

RE: io thread very slow copying binlogs

[Michael Dykman]

If you are confident that it's not the network, that pretty much leaves RAM or 
disk as the source of your problem. Assuming that you are seeing no performance 
issues on the master (I expect you would have mentioned), let's focus on the 
slave.

The behaviour you describe sounds to me like you have entered swap space: 
everythingthing is working fine, just in incredibly slow motion.
What does vmstat report?  Try someithng like 
  $ vmstat 2 60
and see if any swap traffic can be observed?

How about cat /proc//status ? What does VmSwap suggest?

 - michael dykman

-Original Message-
From: Brad Barnett [mailto:mysql-general-l...@l8r.net] 
Sent: Friday, January 20, 2017 10:45 AM
To: mysql@lists.mysql.com
Subject: Re: io thread very slow copying binlogs



Hey Morgan,

Thanks for the tip.  Might come in handy.

But, I'm positive it's not a disconnect / reconnect thing.  Or, at least not 
one affected by that timeout.

I can do a watch ls -lh in the binlog dir, and see the relay log increasing in 
size by a M every 4 or 5 seconds or so.  About 200kbyte/sec / 1.6Mbit/sec right 
now.

It seems very steady too.  As in, if I look at bytes, they're constantly 
increasing.. just, slow..

On Fri, 20 Jan 2017 10:19:57 -0500
"Morgan Tocker"  wrote:

> Hi Brad,
> 
> > MySQL community edition 5.6.29, running Linux.
> > 
> > Binlogs never seem to get caught up on slaves.
> > 
> > I've done all I can, to validate that this isn't network or disk 
> > related.
> > 
> > Disk tests (using iostat and other methods) show lots of bandwidth 
> > left on the slave and master.
> > 
> > Network tests, such as:
> > 
> > - using scp to copy binlogs directly
> > - using different NICs to copy binlogs
> > - using mysqlbinlog to snag logs (the most 'real' way I can think to
> >   simulate the replication thread copying binlogs from the master)
> > 
> > All seem to show that network speed is blazingly fast.
> > 
> > Yet, MySQL is barely getting 4mbit/sec across the network, and onto 
> > the
> disk.
> > And that's on a good day.
> > 
> > Any immediate suggestions here?  This seems very weird, and SQL 
> > thread is constantly running out of stuff to process.
> 
> Networking is not my strong-suit, but I have a suggestion:
> 
> Try lowering slave-net-timeout
> http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#
> option
> _mysqld_slave-net-timeout
> 
> I remember that we lowered the default in MySQL 5.7 (from 1hr to 60
> seconds) so that the connection between master/slave would be 
> considered broken faster.  If you have the throughput on a graph it 
> might better explain if it is a constant 4mbit/sec or more broken.
> 
> 
> - Morgan

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


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

Re: io thread very slow copying binlogs

[Brad Barnett]

Hey Morgan,

Thanks for the tip.  Might come in handy.

But, I'm positive it's not a disconnect / reconnect thing.  Or, at least
not one affected by that timeout.

I can do a watch ls -lh in the binlog dir, and see the relay log
increasing in size by a M every 4 or 5 seconds or so.  About
200kbyte/sec / 1.6Mbit/sec right now.

It seems very steady too.  As in, if I look at bytes, they're constantly
increasing.. just, slow..

On Fri, 20 Jan 2017 10:19:57 -0500
"Morgan Tocker"  wrote:

> Hi Brad,
> 
> > MySQL community edition 5.6.29, running Linux.
> > 
> > Binlogs never seem to get caught up on slaves.
> > 
> > I've done all I can, to validate that this isn't network or disk
> > related.
> > 
> > Disk tests (using iostat and other methods) show lots of bandwidth
> > left on the slave and master.
> > 
> > Network tests, such as:
> > 
> > - using scp to copy binlogs directly
> > - using different NICs to copy binlogs
> > - using mysqlbinlog to snag logs (the most 'real' way I can think to
> >   simulate the replication thread copying binlogs from the master)
> > 
> > All seem to show that network speed is blazingly fast.
> > 
> > Yet, MySQL is barely getting 4mbit/sec across the network, and onto
> > the
> disk.
> > And that's on a good day.
> > 
> > Any immediate suggestions here?  This seems very weird, and SQL
> > thread is constantly running out of stuff to process.
> 
> Networking is not my strong-suit, but I have a suggestion:
> 
> Try lowering slave-net-timeout
> http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option
> _mysqld_slave-net-timeout
> 
> I remember that we lowered the default in MySQL 5.7 (from 1hr to 60
> seconds) so that the connection between master/slave would be
> considered broken faster.  If you have the throughput on a graph it
> might better explain if it is a constant 4mbit/sec or more broken.
> 
> 
> - Morgan

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

Re: io thread very slow copying binlogs

[Brad Barnett]

I should add that I've turned the SQL thread off, it makes no difference
from what I can see...

On Fri, 20 Jan 2017 10:31:38 -0500
Brad Barnett  wrote:

> 
> 
> 
> On Fri, 20 Jan 2017 10:30:09 -0500
> Brad Barnett  wrote:
> 
> > 
> > 
> > Hey Morgan,
> > 
> > Thanks for the tip.  Might come in handy.
> > 
> > But, I'm positive it's not a disconnect / reconnect thing.  Or, at
> > least not one affected by that timeout.
> > 
> > I can do a watch ls -lh in the binlog dir, and see the relay log
> > increasing in size by a M every 4 or 5 seconds or so.  About
> > 200kbyte/sec / 1.6Mbit/sec right now.
> > 
> > It seems very steady too.  As in, if I look at bytes, they're
> > constantly increasing.. just, slow..
> > 
> > On Fri, 20 Jan 2017 10:19:57 -0500
> > "Morgan Tocker"  wrote:
> > 
> > > Hi Brad,
> > > 
> > > > MySQL community edition 5.6.29, running Linux.
> > > > 
> > > > Binlogs never seem to get caught up on slaves.
> > > > 
> > > > I've done all I can, to validate that this isn't network or disk
> > > > related.
> > > > 
> > > > Disk tests (using iostat and other methods) show lots of bandwidth
> > > > left on the slave and master.
> > > > 
> > > > Network tests, such as:
> > > > 
> > > > - using scp to copy binlogs directly
> > > > - using different NICs to copy binlogs
> > > > - using mysqlbinlog to snag logs (the most 'real' way I can think
> > > > to simulate the replication thread copying binlogs from the
> > > > master)
> > > > 
> > > > All seem to show that network speed is blazingly fast.
> > > > 
> > > > Yet, MySQL is barely getting 4mbit/sec across the network, and
> > > > onto the
> > > disk.
> > > > And that's on a good day.
> > > > 
> > > > Any immediate suggestions here?  This seems very weird, and SQL
> > > > thread is constantly running out of stuff to process.
> > > 
> > > Networking is not my strong-suit, but I have a suggestion:
> > > 
> > > Try lowering slave-net-timeout
> > > http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option
> > > _mysqld_slave-net-timeout
> > > 
> > > I remember that we lowered the default in MySQL 5.7 (from 1hr to 60
> > > seconds) so that the connection between master/slave would be
> > > considered broken faster.  If you have the throughput on a graph it
> > > might better explain if it is a constant 4mbit/sec or more broken.
> > > 
> > > 
> > > - Morgan

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

RE: io thread very slow copying binlogs

[Morgan Tocker]

Hi Brad,

> MySQL community edition 5.6.29, running Linux.
> 
> Binlogs never seem to get caught up on slaves.
> 
> I've done all I can, to validate that this isn't network or disk related.
> 
> Disk tests (using iostat and other methods) show lots of bandwidth left on
> the slave and master.
> 
> Network tests, such as:
> 
> - using scp to copy binlogs directly
> - using different NICs to copy binlogs
> - using mysqlbinlog to snag logs (the most 'real' way I can think to
>   simulate the replication thread copying binlogs from the master)
> 
> All seem to show that network speed is blazingly fast.
> 
> Yet, MySQL is barely getting 4mbit/sec across the network, and onto the
disk.
> And that's on a good day.
> 
> Any immediate suggestions here?  This seems very weird, and SQL thread is
> constantly running out of stuff to process.

Networking is not my strong-suit, but I have a suggestion:

Try lowering slave-net-timeout
http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option
_mysqld_slave-net-timeout

I remember that we lowered the default in MySQL 5.7 (from 1hr to 60 seconds)
so that the connection between master/slave would be considered broken
faster.  If you have the throughput on a graph it might better explain if it
is a constant 4mbit/sec or more broken.


- Morgan


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

io thread very slow copying binlogs

[Brad Barnett]

Hey all,

I have a weird issue.

MySQL community edition 5.6.29, running Linux.

Binlogs never seem to get caught up on slaves.  

I've done all I can, to validate that this isn't network or disk related.

Disk tests (using iostat and other methods) show lots of bandwidth left
on the slave and master.

Network tests, such as:

- using scp to copy binlogs directly
- using different NICs to copy binlogs
- using mysqlbinlog to snag logs (the most 'real' way I can think to
  simulate the replication thread copying binlogs from the master)

All seem to show that network speed is blazingly fast.

Yet, MySQL is barely getting 4mbit/sec across the network, and
onto the disk.  And that's on a good day.

Any immediate suggestions here?  This seems very weird, and SQL thread is
constantly running out of stuff to process.

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

MySQL Cluster 7.5.5 has been released

[Hery Ramilison]

Dear MySQL Users,

MySQL Cluster 7.5.5 (GA) is a GA release for MySQL Cluster 7.5.

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 & 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.5, 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.5 (5.7.17-ndb-7.5.5) (2017-01-17)

   MySQL Cluster NDB 7.5.5 is a new release of MySQL Cluster NDB
   7.5, based on MySQL Server 5.7 and including features in
   version 7.5 of the NDB
   (http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster.html)
   storage engine, as well as fixing recently discovered bugs in
   previous MySQL Cluster releases.

   Obtaining MySQL Cluster NDB 7.5.  MySQL Cluster NDB 7.5
   source code and binaries can be obtained from
   http://dev.mysql.com/downloads/cluster/.

   For an overview of changes made in MySQL Cluster NDB 7.5, see
   What is New in MySQL NDB Cluster 7.5
   (http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-what-is-new.html).

   This release also incorporates all bugfixes and changes made
   in previous MySQL Cluster releases, as well as all bugfixes
   and feature changes which were added in mainline MySQL 5.7
   through MySQL 5.7.17 (see Changes in MySQL 5.7.17
   (2016-12-12) , General Availability)
   (http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-17.html)).

   Bugs Fixed

 * Packaging: The RPM installer for the MySQL Cluster
   auto-installer package had a dependency on python2-crypt
   instead of python-crypt. (Bug #24924607)

 * Microsoft Windows: Installation failed when the
   Auto-Installer (ndb_setup.py) was run on a Windows host
   that used Swedish as the system language. This was due to
   system messages being issued using the cp1252 character
   set; when these messages contained characters that did
   not map directly to 7-bit ASCII (such as the ä character
   in Tjänsten ... startar), conversion to UTF-8---as
   expected by the Auto-Installer web client---failed.
   This fix has been tested only with Swedish as the system
   language, but should work for Windows systems set to
   other European languages that use the cp1252 character
   set. (Bug #83870, Bug #25111830)

 * No traces were written when ndbmtd received a signal in
   any thread other than the main thread, due to the fact
   that all signals were blocked for other threads. This
   issue is fixed by the removal of SIGBUS, SIGFPE, SIGILL,
   and SIGSEGV signals from the list of signals being
   blocked. (Bug #25103068)

 * The rand() function was used to produce a unique table ID
   and table version needed to identify a schema operation
   distributed between multiple SQL nodes, relying on the
   assumption that rand() would never produce the same
   numbers on two different instances of mysqld. It was
   later determined that this is not the case, and that in
   fact it is very likely for the same random numbers to be
   produced on all SQL nodes.
   This fix removes the usage of rand() for producing a
   unique table ID or version, and instead uses a sequence
   in combination with the node ID of the coordinator. This
   guarantees uniqueness until the counter for the sequence
   wraps, which should be sufficient for this purpose.
   The effects of this duplication could be observed as
   timeouts in the log (for example NDB create db: waiting
   max 119 sec for distributing) when restarting multiple
   mysqld processes simultaneously or nearly so, or when
   issuing the same CREATE DATABASE
   (http://dev.mysql.com/doc/refman/5.7/en/create-database.html)
   or DROP DATABASE
   (http://dev.mysql.com/doc/refman/5.7/en/drop-database.html)
   statement on multiple SQL nodes. (Bug #24926009)

 * The ndb_show_tables utility did not display type
   information for hash maps or fully replicated triggers.
   (Bug #24383742)

 * Long message buffer exhaustion when firing immediate
   triggers could result in row ID leaks; this 

MySQL Cluster 7.2.27 has been released

[Gipson Pulla]

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 & 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 and Memcached)

MySQL Cluster 7.2.27, 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.2/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/

Changes in MySQL NDB Cluster 7.2.27 (5.5.54-ndb-7.2.27) (2017-01-17)

   MySQL NDB Cluster 7.2.27 is a new release of NDB Cluster,
   incorporating new features in the NDB storage engine, and
   fixing recently discovered bugs in previous MySQL NDB Cluster
   7.2 development releases.

   Obtaining MySQL NDB Cluster 7.2.  MySQL NDB Cluster 7.2
   source code and binaries can be obtained from
   http://dev.mysql.com/downloads/cluster/.

   This release also incorporates all bugfixes and changes made
   in previous NDB Cluster releases, as well as all bugfixes and
   feature changes which were added in mainline MySQL 5.5
   through MySQL 5.5.54 (see Changes in MySQL 5.5.54
   (2016-12-12, General availability)
(http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-54.html)).

   Bugs Fixed

 * A number of potential buffer overflow issues were found
   and fixed in the NDB codebase. (Bug #25260091)
   References: See also: Bug #23152979.

 * ndb_restore did not restore tables having more than 341
   columns correctly. This was due to the fact that the
   buffer used to hold table metadata read from .ctl files
   was of insufficient size, so that only part of the table
   descriptor could be read from it in such cases. This
   issue is fixed by increasing the size of the buffer used
   by ndb_restore for file reads. (Bug #25182956)


On Behalf of MySQL Release Engineering Team,
Gipson Pulla

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

Re: kill query and prepared statements

[Sebastien FLAESCH]

Sorry for the spam, and thanks for you suggestion Johan!

http://bugs.mysql.com/bug.php?id=84470

Seb

On 01/11/2017 11:21 AM, Johan De Meersman wrote:

Seb,

You should log a bug at http://bugs.mysql.com - this is not a developer list.

/Johan

- Original Message -

From: "Sebastien FLAESCH" 
To: "MySql" 
Sent: Tuesday, 10 January, 2017 14:55:42
Subject: kill query and prepared statements



Hi all,

I have reported this problem before, but I raise it again, since I still get
this problem with 5.7.17

See attached code:

I want to interrupt a long running statement with CTRL-C by starting a new
connect to make a KILL QUERY.

I am using the same technique as the mysql client code.

The difference here is that my code is using PREPARED STATEMENTS with
mysql_stmt_prepare() etc.

Problem: After interrupting the first query with CTRL-C, the call to
mysql_stmt_close() hangs...

Maybe I am missing some new connection or statement option...?!?

IMPORTANT: This problem appeared in a 5.7.x, same code is working fine with
5.6(.16) 

Please can someone from MySQL C API team try to reproduce and confirm?

Thanks!
Seb


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





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

Re: kill query and prepared statements

[Johan De Meersman]

Seb,

You should log a bug at http://bugs.mysql.com - this is not a developer list.

/Johan

- Original Message -
> From: "Sebastien FLAESCH" 
> To: "MySql" 
> Sent: Tuesday, 10 January, 2017 14:55:42
> Subject: kill query and prepared statements

> Hi all,
> 
> I have reported this problem before, but I raise it again, since I still get
> this problem with 5.7.17
> 
> See attached code:
> 
> I want to interrupt a long running statement with CTRL-C by starting a new
> connect to make a KILL QUERY.
> 
> I am using the same technique as the mysql client code.
> 
> The difference here is that my code is using PREPARED STATEMENTS with
> mysql_stmt_prepare() etc.
> 
> Problem: After interrupting the first query with CTRL-C, the call to
> mysql_stmt_close() hangs...
> 
> Maybe I am missing some new connection or statement option...?!?
> 
> IMPORTANT: This problem appeared in a 5.7.x, same code is working fine with
> 5.6(.16) 
> 
> Please can someone from MySQL C API team try to reproduce and confirm?
> 
> Thanks!
> Seb
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

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

Re: kill query and prepared statements

[Sebastien FLAESCH]

Just to be clear:

This is a hint, to find the real problem, this is NOT A WORKAROUND for us!

Seb

On 01/10/2017 03:53 PM, Sebastien FLAESCH wrote:

Investigating different API calls, to see if it makes a difference...

Seems that the problem is related to the read-only cursor type option!

If you comment out:

unsigned long ct = (unsigned long) CURSOR_TYPE_READ_ONLY;
s = mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (const void *) );

Then it works ...

Seb


On 01/10/2017 02:55 PM, Sebastien FLAESCH wrote:

Hi all,

I have reported this problem before, but I raise it again, since I still get 
this problem with 5.7.17

See attached code:

I want to interrupt a long running statement with CTRL-C by starting a new 
connect to make a KILL QUERY.

I am using the same technique as the mysql client code.

The difference here is that my code is using PREPARED STATEMENTS with 
mysql_stmt_prepare() etc.

Problem: After interrupting the first query with CTRL-C, the call to 
mysql_stmt_close() hangs...

Maybe I am missing some new connection or statement option...?!?

IMPORTANT: This problem appeared in a 5.7.x, same code is working fine with 
5.6(.16) 

Please can someone from MySQL C API team try to reproduce and confirm?

Thanks!
Seb









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

Re: kill query and prepared statements

[Sebastien FLAESCH]

Investigating different API calls, to see if it makes a difference...

Seems that the problem is related to the read-only cursor type option!

If you comment out:

unsigned long ct = (unsigned long) CURSOR_TYPE_READ_ONLY;
s = mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (const void *) );

Then it works ...

Seb


On 01/10/2017 02:55 PM, Sebastien FLAESCH wrote:

Hi all,

I have reported this problem before, but I raise it again, since I still get 
this problem with 5.7.17

See attached code:

I want to interrupt a long running statement with CTRL-C by starting a new 
connect to make a KILL QUERY.

I am using the same technique as the mysql client code.

The difference here is that my code is using PREPARED STATEMENTS with 
mysql_stmt_prepare() etc.

Problem: After interrupting the first query with CTRL-C, the call to 
mysql_stmt_close() hangs...

Maybe I am missing some new connection or statement option...?!?

IMPORTANT: This problem appeared in a 5.7.x, same code is working fine with 
5.6(.16) 

Please can someone from MySQL C API team try to reproduce and confirm?

Thanks!
Seb






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

kill query and prepared statements

[Sebastien FLAESCH]

Hi all,

I have reported this problem before, but I raise it again, since I still get 
this problem with 5.7.17

See attached code:

I want to interrupt a long running statement with CTRL-C by starting a new 
connect to make a KILL QUERY.

I am using the same technique as the mysql client code.

The difference here is that my code is using PREPARED STATEMENTS with 
mysql_stmt_prepare() etc.

Problem: After interrupting the first query with CTRL-C, the call to 
mysql_stmt_close() hangs...

Maybe I am missing some new connection or statement option...?!?

IMPORTANT: This problem appeared in a 5.7.x, same code is working fine with 
5.6(.16) 

Please can someone from MySQL C API team try to reproduce and confirm?

Thanks!
Seb
#include 
#include 
#include 
#include 

#include 
#include 


static char * c_host = "orion";
static char * c_user = "mysuser";
static char * c_auth = "fourjs";
static intc_port = 3308;
static char * c_sock = NULL;
static char * c_dbnm = "test1";

static int executing_query;
static unsigned long thread_id;

static void kill_query(void)
{
char cmd[50];
MYSQL *h;
h = mysql_init(NULL);
if (!mysql_real_connect(h, c_host, c_user, c_auth,
c_dbnm, c_port, c_sock,
CLIENT_FOUND_ROWS)) {
fprintf(stderr, "kill_query: Could not connect (err=%d)\n", mysql_errno(h));
return;
}
sprintf(cmd, "KILL QUERY %ld", thread_id);
if (mysql_query(h, cmd) != 0) {
fprintf(stderr, "Could not execute %s.", cmd);
}
mysql_close(h);
}

static void handle_ctrlc_signal(int sig)
{
fprintf(stdout, "SIGINT caught! executing_query = %d\n", executing_query);
if (executing_query) {
executing_query = 0;
kill_query();
}
return;
}


int main(int argc, char ** argv)
{
MYSQL * conn;
MYSQL_STMT * stmt;
int i, s;
unsigned long ct = (unsigned long) CURSOR_TYPE_READ_ONLY;
const char * sqlstmt = "select benchmark(10, md5('when will it end?'))";

signal(SIGINT, handle_ctrlc_signal);

conn = mysql_init(NULL);

if (!mysql_real_connect(conn, c_host, c_user, c_auth,
c_dbnm, c_port, c_sock,
CLIENT_FOUND_ROWS)) {
fprintf(stderr, "Could not connect (err=%d)\n", mysql_errno(conn));
return -1;
}

thread_id = mysql_thread_id(conn);
fprintf(stdout, "MySQL thread ID: %ld\n", thread_id);

for (i=0; i<3; i++) {

fprintf(stdout, "\nRound %d:\n", i+1);

fprintf(stdout, "Allocating statement handle...");
stmt = mysql_stmt_init(conn);
if (stmt==NULL) {
fprintf(stderr, "Could not create statement handle (err=%d)\n", mysql_errno(conn));
return -1;
}
fprintf(stdout, "   handle = %p\n", (void*) stmt);

fprintf(stdout, "Preparing statement %p ...\n", (void*) stmt);
s = mysql_stmt_prepare(stmt, sqlstmt, (unsigned long) strlen(sqlstmt));
if (s!=0) {
fprintf(stderr, "Could not prepare statement (err=%d)\n", mysql_errno(conn));
return -1;
}

fprintf(stdout, "Setting cursor type to read only for %p ...\n", (void*) stmt);
s = mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, (const void *) );
if (s!=0) {
fprintf(stderr, "Could not set cursor type (err=%d)\n", mysql_errno(conn));
return -1;
}

fprintf(stdout, "Executing statement %p ...\n", (void*) stmt);
executing_query = 1;
s = mysql_stmt_execute(stmt);
if (s!=0) {
if (mysql_errno(conn) == 1317) {
fprintf(stdout, "Statement interrupted by user...\n");
} else {
fprintf(stderr, "Could not execute the query (err=%d)\n", mysql_errno(conn));
return -1;
}
}

fprintf(stdout, "Closing/freeing statement handle %p ...\n", (void*) stmt);
s = mysql_stmt_close(stmt);
if (s!=0) {
fprintf(stderr, "Could not close statement handle (err=%d)\n", mysql_errno(conn));
return -1;
}

fprintf(stdout, "Round done.\n");
}

return 0;
}

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

mysql Archiving engine

[Machiel Richards]

Good day all


Compliments of the season to everyone and I hope that this mail finds
all well.

 I am trying to find out a bit more regarding the archiving storage
engine.

 We currently have an archiving process that the dev team wrote for
archiving of data.

  However, management has requested that we move the archived data on
the archive server to a database using tables create with the
Archive storage engine.

  This also counts for all new data.


  After reading up on it, the dev team have a few concerns and thus I
would like to find out from people who may have had experience with
the storage engine.


 From what we can see it stores unindexed data, thus reading from
these tables will be slow as it uses full table scans.

  The current archiving process however copies data over to the
archive server, then it does integrity checks between the archive
server and live data to confirm that the data did in fact copy
correctly before it removes the data from the live server.

  The concern is that moving to the archive storage engine will slow
down the process as all selects for the integrity checks will become
slower due to no indexes being available, etc...


Can anyone please advise on this ?
Any info will be greatly appreciated.

Regards


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

MySQL Connector/C++ 1.1.8 has been released

[Daniel Horecki]

Dear MySQL Users,

A new GA (general availability) version of MySQL Connector/C++ has
been made available: MySQL Connector/C++ 1.1.8 GA. The MySQL
Connector/C++ provides a C++ API for connecting client applications to
the MySQL Server 5.5 or newer.

You can download the production release at:

http://dev.mysql.com/downloads/connector/cpp/1.1.html

MySQL Connector C++ (Commercial) will be available for download on the
My Oracle Support (MOS) website. This release will be available on eDelivery
(OSDC) in next month's upload cycle.

The MySQL driver for C++ offers an easy to use API derived from JDBC
4.0. MySQL Workbench has used it successfully for years.

We have improved the driver since the last GA release. Please see the
documentation and the CHANGES file in the source distribution for a
detailed description of bugs that have been fixed. Bug descriptions are
also listed below.

Enjoy!

==

Changes in MySQL Connector/C++ 1.1.8 (2016-12-16, General
Availability)


Security Notes

  * OpenSSL is ending support for version 1.0.1 in December
2016; see
https://www.openssl.org/policies/releasestrat.html.
Consequently, Connector/C++ Commercial builds now use
version 1.0.2 rather than version 1.0.1, and the linked
OpenSSL library for the Connector/C++ Commercial has been
updated from version 1.0.1 to version 1.0.2j. For a
description of issues fixed in this version, see
https://www.openssl.org/news/vulnerabilities.html.
This change does not affect Oracle-produced MySQL
Community builds of Connector/C++, which use the yaSSL
library instead.

Functionality Added or Changed

  * Connector/C++ now supports a OPT_TLS_VERSION connection
option for specifying the protocols permitted for
encrypted connections. The option value is string
containing a comma-separated list of one or more protocol
names. Example:
connection_properties["OPT_TLS_VERSION"] = sql::SQLString("TLSv1.1,TLS
v1.2");

The permitted values depend on the SSL library used to
compile MySQL: TLSv1, TLSv1.1, TLSv1.2 if OpenSSL was
used; TLSv1 and TLSv1.1 if yaSSL was used. The default is
to permit all available protocols.
For more information about connection protocols in MySQL,
see Secure Connection Protocols and Ciphers
(http://dev.mysql.com/doc/refman/5.7/en/secure-connection
-protocols-ciphers.html). (Bug #23496967)

  * Connector/C++ now supports a OPT_SSL_MODE connection
option for specifying the security state of the
connection to the server. Permitted option values are
SSL_MODE_PREFERRED (the default), SSL_MODE_DISABLED,
SSL_MODE_REQUIRED, SSL_MODE_VERIFY_CA, and
SSL_MODE_VERIFY_IDENTITY. These values correspond to the
values of the --ssl-mode option supported by MySQL client
programs; see Command Options for Secure Connections
(http://dev.mysql.com/doc/refman/5.7/en/secure-connection
-options.html). For example, this setting specifies that
the connection should be unencrypted:
connection_properties["OPT_SSL_MODE"] = sql::SSL_MODE_DISABLED;

The OPT_SSL_MODE option comprises the capabilities of the
sslEnforce and sslVerify connection options.
Consequently, both of those options are now deprecated.
(Bug #23496952)

  * Connector/C++ now supports OPT_MAX_ALLOWED_PACKET and
OPT_NET_BUFFER_LENGTH connection options. Each option
takes a numeric value. They correspond to the
MYSQL_OPT_MAX_ALLOWED_PACKET and
MYSQL_OPT_NET_BUFFER_LENGTH options for the
mysql_options() C API function.

  * Issues compiling Connector/C++ under Visual Studio 2015
were corrected.

Bugs Fixed

  * A segmentation fault could occur for attempts to insert a
large string using a prepared statement. (Bug #23212333,
Bug #81213)

  * The certification verification checks that are enabled by
the verifySSL connection option were not performed
properly. (Bug #22931974)

  * Connector/C++ failed to compile against a version of the
MySQL C API older than 5.7. (Bug #22838573, Bug #80539,
Bug #25201287)

On Behalf of the MySQL/ORACLE RE Team
Daniel Horecki


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

[ANN] QxOrm 1.4.3 and QxEntityEditor 1.2.1 released : the Qt ORM supports CMake and provides a new QxEntityEditor documentation

[QxOrm contact]

Hello,

QxOrm library 1.4.3 and QxEntityEditor application 1.2.1 just released :
http://www.qxorm.com/
The Qt ORM library now supports CMake and provides a new QxEntityEditor
documentation : http://www.qxorm.com/qxorm_en/manual_qxee.html

QxOrm library 1.4.3 changes log :

 - Support CMake : new CMakeLists.txt file added to build QxOrm library
with CMake
 - Improve SQL error messages when qx::dao functions return a database error
 - New parameter in singleton class qx::QxSqlDatabase to log SQL bound
values (setTraceSqlBoundValues) : by default, bound values are logged when
an error occurred
 - New syntax to select columns to not fetch : -{ col_1, col_2, etc... }
 - New function qx::dao::call_query_without_prepare() to execute specific
SQL queries without prepared statement
 - Improve QxModelView module : all QxOrm models (based on qx::IxModel
interface) can be serialized to JSON format (including all relationships
levels) : this is now another way to work with relationships and QML
(thanks to JSON.parse() and JSON.stringify() javascript functions) without
using nested models concept (so without using QxEntityEditor model/view
generated classes)
 - Improve qxBlogModelView sample project and QxOrm manual to show how to
access to relationships data in QML (nested models or JSON)
 - Fix a memory leak in qx::QxSqlRelation class
 - Reduce output binary size (~20%) and compilation times (~20%) to build
persistent classes based on QxOrm library
 - Support unity build concept to reduce compilation times to build QxOrm
library and C++ persistent classes generated by QxEntityEditor application
: for more details, see _QX_UNITY_BUILD compilation option in QxOrm.pri or
QxOrm.cmake configuration file
 - Improve QxConvert module : possibility to store in database complex
QVariant properties which contain QVariantMap, QVariantHash or QVariantList
types (JSON format)
 - Fix an issue with some databases when a foreign key is also a part of
the primary key
 - Fix an issue with QSharedPointer and boost::serialization when a same
raw pointer is shared by several QSharedPointer during deserialization
process

QxEntityEditor application 1.2.1 changes log :

 - Support CMake : each C++ project generated by QxEntityEditor (persistent
classes, services, model/view) provide a CMakeLists.txt file to build with
CMake
 - New online QxEntityEditor manual (user guide) available at :
http://www.qxorm.com/qxorm_en/manual_qxee.html
 - Improve Javascript engine to customize export generated files : add a
parameter named 'output_location' to know where generated files are located
+ add functions to get a list of all entities/enums of a project (see
./samples/custom_script.js file for more details)
 - Improve database import process (SQLite, MySQL/MariaDB, PostgreSQL,
Oracle and MSSQLServer) : fix an issue importing composite foreign keys +
foreign keys embedded in primary key ==> so it is now possible to import
more complex database schema
 - Improve DDL database schema export process : fix an issue with 1-1
relationship + manage composite keys
 - New menu "Naming convention" to provide a fast way to rename all
entities/properties/enums (without breaking mapping to database) : support
snake_case, camelCase and PascalCase (or upper camel case), this new menu
can be useful after an import from database process for example
 - Each screen of QxEntityEditor provides now a fast access to the online
manual (user guide) in its associated topic (new button "Documentation" +
shortcut pressing F1 key everywhere)
 - New buttons undo/redo in the main QxEntityEditor toolbar to undo or redo
actions done on entities, enumerations, comments, layout (undo/redo feature
can be disabled to improve performance on large projects)
 - Support _QX_UNITY_BUILD compilation option to reduce compilation times
of generated C++ projects (recommended with CMake which doesn't support
natively precompiled headers)
 - Fix an issue importing a relationship where target entity doesn't have a
primary key
 - Fix an issue with C++ services export plugin and JSON serialization :
"Unable to create nude pointer for input parameter"
 - Fix an issue with abstract entities and C++ model/view export plugin and
C++ services export plugin
 - Possibility to put hexadecimal values to define an enumeration
 - New Javascript sample file in directory ./samples/ named q_property.js
to show how to add automatically Q_PROPERTY definition for each property
generated by QxEntityEditor

You can download latest version of QxOrm library and QxEntityEditor
application on QxOrm website : http://www.qxorm.com/

Regards,


Lionel Marty - QxOrm library

MySQL Community Server 5.7.17 has been released

[Hery Ramilison]

Dear MySQL users,

MySQL Server 5.7.17, a new version of the popular Open Source
Database Management System, has been released. MySQL 5.7.17 is
recommended for use on production systems.

For an overview of what's new in MySQL 5.7, please see

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

For information on installing MySQL 5.7.17 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.17 is available in source and binary form for a number of
platforms from our download pages at

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

MySQL Server 5.7.17 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.17 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 since
the release of MySQL 5.7.16. It may also be viewed online at

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

Enjoy!

Changes in MySQL 5.7.17 (2016-12-12)

   Compilation Notes

 * For GCC versions higher than 4.4,
   -fno-expensive-optimizations was replaced with
   -ffp-contract=off, which has the effect of enabling more
   optimizations. Thanks to Alexey Kopytov for the patch.
   (Bug #24571672, Bug #82760)

   MySQL Enterprise Notes

 * Enterprise Encryption for MySQL Enterprise Edition now
   enables server administrators to impose limits on maximum
   key length by setting environment variables. These can be
   used to prevent clients from using excessive CPU
   resources by passing very long key lengths to
   key-generation operations. For more information, see
   Enterprise Encryption Usage and Examples

(http://dev.mysql.com/doc/refman/5.7/en/enterprise-encryption-usage.html). 
(Bug #19687742)


   Packaging Notes

 * RPM packages now are built with -DWITH_NUMA=ON for
   platforms with NUMA support: OEL higher than EL5, Fedora,
   SLES, Docker. (Bug #24689078)

   Security Notes

 * Incompatible Change: These changes were made to
   mysqld_safe:

  + Unsafe use of rm and chown in mysqld_safe could
result in privilege escalation. chown now can be
used only when the target directory is /var/log. An
incompatible change is that if the directory for the
Unix socket file is missing, it is no longer
created; instead, an error occurs. Due to these
changes, /bin/bash is required to run mysqld_safe on
Solaris. /bin/sh is still used on other Unix/Linux
platforms.

  + The --ledir option now is accepted only on the
command line, not in option files.

  + mysqld_safe ignores the current working directory.
   Other related changes:

  + Initialization scripts that invoke mysqld_safe pass
--basedir explicitly.

  + Initialization scripts create the error log file
only if the base directory is /var/log or /var/lib.

  + Unused systemd files for SLES were removed.
   (Bug #24483092, Bug #25088048)
   References: See also: Bug #24464380, Bug #24388753.

 * MySQL Server now includes a plugin library that enables
   administrators to introduce an increasing delay in server
   response to clients after a certain number of consecutive
   failed connection attempts. This capability provides a
   deterrent that slows down brute force attacks that
   attempt to access MySQL user accounts. For more
   information, see The Connection-Control Plugin

(http://dev.mysql.com/doc/refman/5.7/en/connection-control-plugin.html).

 * OpenSSL is ending support for version 1.0.1 in December
   2016; see
   https://www.openssl.org/policies/releasestrat.html.
   Consequently, MySQL Commercial Server builds now use
   version 1.0.2 rather than version 1.0.1, and the linked
   OpenSSL library for the MySQL Commercial Server has been
   updated from version 1.0.1 to version 1.0.2j. For a
   description of issues fixed in this version, see
   https://www.openssl.org/news/vulnerabilities.html.
   This change does not affect the Oracle-produced MySQL
  

MySQL Community Server 5.5.54 has been released

[Gipson Pulla]

Dear MySQL users,

MySQL Server 5.5.54 is a new version of the 5.5 production release
of the world's most popular open source database. MySQL 5.5.54 is
recommended for use on production systems.

MySQL 5.5 includes several high-impact enhancements to improve the
performance and scalability of the MySQL Database, taking advantage of
the latest multi-CPU and multi-core hardware and operating systems. In
addition, with release 5.5, InnoDB is now the default storage engine for
the MySQL Database, delivering ACID transactions, referential integrity
and crash recovery by default.

MySQL 5.5 also provides a number of additional enhancements including:

  - Significantly improved performance on Windows, with various
Windows specific features and improvements
  - Higher availability, with new semi-synchronous replication and
Replication Heartbeat
  - Improved usability, with Improved index and table partitioning,
SIGNAL/RESIGNAL support and enhanced diagnostics, including a new
Performance Schema monitoring capability.

For a more complete look at what's new in MySQL 5.5, please see the
following resources:

MySQL 5.5 is GA, Interview with Tomas Ulin:

  http://dev.mysql.com/tech-resources/interviews/thomas-ulin-mysql-55.html

Documentation:

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

If you are running a MySQL production level system, we would like to
direct your attention to MySQL Enterprise Edition, which includes the
most comprehensive set of MySQL production, backup, monitoring,
modeling, development, and administration tools so businesses can
achieve the highest levels of MySQL performance, security and uptime.

  http://mysql.com/products/enterprise/

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

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

For upgrading from previous MySQL releases, please see the important
upgrade considerations at:

  http://dev.mysql.com/doc/refman/5.5/en/upgrading.html

MySQL Database 5.5.54 is available in source and binary form for a
number of platforms from our download pages at:

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

The following section lists the changes in the MySQL source code since
the previous released version of MySQL 5.5. It may also be viewed
online at:

  http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-54.html

Enjoy!

==
Changes in MySQL 5.5.54 (2016-12-12, General availability)


 * Security Notes

 * Bugs Fixed

   Security Notes

 * Incompatible Change: These changes were made to
   mysqld_safe:

  + Unsafe use of rm and chown in mysqld_safe could
result in privilege escalation. chown now can be
used only when the target directory is /var/log. An
incompatible change is that if the directory for the
Unix socket file is missing, it is no longer
created; instead, an error occurs. Due to these
changes, /bin/bash is required to run mysqld_safe on
Solaris. /bin/sh is still used on other Unix/Linux
platforms.

  + The --ledir option now is accepted only on the
command line, not in option files.

  + mysqld_safe ignores the current working directory.
   Other related changes:

  + Initialization scripts that invoke mysqld_safe pass
--basedir explicitly.

  + Initialization scripts create the error log file
only if the base directory is /var/log or /var/lib.

  + Unused systemd files for SLES were removed.
   (Bug #24483092, Bug #25088048)
   References: See also: Bug #24464380, Bug #24388753.

   Bugs Fixed

 * Incompatible Change: A change made in MySQL 5.7.8 for
   handling of multibyte character sets by LOAD DATA was
   reverted due to the replication incompatibility
   (Bug #24487120, Bug #82641)
   References: See also: Bug #23080148.

 * InnoDB: The GCC mach_parse_compressed function should
   load one to five bytes depending on the value of the
   first byte. Due to a GCC bug, GCC 5 and 6 emit code to
   load four bytes before the first byte value is checked
   (GCC Bug #77673). A workaround prevents this behavior.
   Thanks to Laurynas Biveinis for the patch.
   (Bug #24707869, Bug #83073)

 * Some Linux startup scripts did not process the datadir
   setting correctly. (Bug #25159791)

 * CREATE TABLE with a DATA DIRECTORY clause could be used
   to gain extra privileges. (Bug #25092566)

 * OEL RPM packages now better detect which platforms have
   multilib support (for which 32-bit and 64-bit libraries
   can be installed). Thanks to Alexey Kopytov for the
   patch. (Bug #24925181, Bug #83457)

 * If mysqladmin shutdown encountered an error determining
   the server process ID 

MySQL Connector/NET 6.10.0 alpha has been released

[Prashant Tekriwal]

Dear MySQL users,

MySQL Connector/Net 6.10.0 alpha is the first release to provide Entity
Framework Core 1.1 support and to enable compression in the .NET Core
version of the driver for enhanced cross-platform application
development.

To download MySQL Connector/Net 6.10.0 alpha, see the "Development
Releases" tab at http://dev.mysql.com/downloads/connector/net/

Changes in MySQL Connector/Net 6.10.0 (2016-12-09, Alpha)

   Known limitation of this release: DbContext within an
   application can emit an unhandled exception. Due to this
   limitation, MySQL Connector/Net 6.9.9 is the preferred
   version to use with EF6.

   Functionality Added or Changed

 * Support for compression was extended to the .NET Core
   version of the driver.

 * Added support for Entity Framework (EF) Core 1.1.


Nuget packages are available at:

https://www.nuget.org/packages/MySql.Data/6.10.0-alpha
https://www.nuget.org/packages/MySql.Web/6.10.0-alpha
https://www.nuget.org/packages/MySql.Data.Entity/6.10.0-alpha
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/6.10.0-alpha

Enjoy and thanks for the support!

On behalf of the MySQL Release Team
Prashant Tekriwal


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

Re: audit trails

[Jesper Wisborg Krogh]

Hi,

On 8/12/2016 18:39, mach...@seworx.co.za wrote:

...

 So to recap what has been done for now :

  - Triggers to insert a record in audit table to show 
the table, type of query(insert/update) and who made the relevant change.
  - Trigger to prevent deletes from tables which will 
feedback an error to state that deletes are not allowed.


 What I need to still resolve:

  -- Trigger for deletes should still log an entry 
into the audit table to notify which user attempted to do a delete.

  -- More permanent solutions to be implemented.


If your trigger generates an error, the only way to get it to log an 
entry into a table is to ensure that table is not using a transactional 
storage engine. Otherwise both the attempted delete and the audit insert 
will be rolled back. Using a non-transactional storage engine of course 
has its own problems, but depending on the requirements of the logging, 
it may be good enough.


An alternative as already mentioned is to use an audit log plugin. In 
MySQL 5.7.13 and later, there are extensive filtering options available 
to avoid logging everything, e.g. it's possible to limit the audit 
logging to specific actions and/or tables. See also 
https://dev.mysql.com/doc/refman/5.7/en/audit-log-filtering.html - 
Disclaimer: I work for MySQL so will of course be happy to see you 
choose our audit log plugin.


If you intend deletes not to be possible, I will also recommend you to 
remove the DELETE and DROP privileges to the table for your users.


Best regards,
Jesper Krogh
MySQL Support


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