Re: Assistance with trigger

[Machiel Richards]

Please ignore my request as I have managed to figure this out.

Thank you.

had to rewrite the insert to use Old.Column1... etc... and that works quite 
well.



From: Machiel Richards 
Sent: Friday, 26 April 2019 10:48 AM
To: mysql@lists.mysql.com
Subject: Assistance with trigger

Hi All

   I am hoping this email finds all well.

I would like to request some assistance with a MySQL trigger please.

We need to implement a trigger that will in short terms make a backup of a 
row before it gets deleted.

so we have tableA and TableB (backup table).

I added a before delete trigger to insert into TABLEB select from TABLEA... 
However upon delete it inserts the whole table instead of only the single row 
being deleted.

Here is my insert and select statement :


INSERT INTO tableB (Column1,backup_date,updated_by_user,Column4) SELECT 
Column1,NOW(),`username`,Column2 from tableA;



How can I rewrite this section to only select and insert the rows that are 
actually being deleted.

Your help would be greatly appreciated as I have not been able to find the 
answer on google as yet.

Regards

Re: How do I determine if versions of phpMyAdmin before 4.8.5 is SQL Injectable using sqlmap?

[shawn l.green]

Hello,

On 4/17/2019 10:29 AM, Turritopsis Dohrnii Teo En Ming wrote:

Subject/Topic: How do I determine if versions of phpMyAdmin before 4.8.5 is SQL 
Injectable using sqlmap?

Good evening from Singapore,

Our customer (company name is Confidential/not disclosed) reported that their 
MySQL database has been found missing or was deleted a few times.


While it is bad form to explain how to break into anyone's software 
(including our own), there are places you can look to get a better idea 
about what might have happened:


1 - the database may have been removed by a DROP DATABASE command.

General Query Log - this will show you which session issued the command 
and the command itself.


Audit log (only for commercial releases) - same thing

Binary Log - Should have a record of the command executing. But, 
depending on which account was used or if Binary Log filtering is in 
place, it may not. This presumes that the Binary Log is even enabled on 
this system.  Many people mistakenly believe it is only for Replication 
when its other primary use is for point-in-time recovery. If your 
customer has a recent backup and all of the Binary Log files created 
since that backup, they could return the system to the point it was at 
just before that database went missing, skip that DROP command, then 
continue rolling forward the changes to the other tables to return to a 
"current" state of their data.


2 - The database was "dropped" by either changing privileges to the 
folder or by removing it from disk or some other file-level or 
system-level operation. Either of those would cause errors to start 
appearing in the MySQL Error Log because a resource that mysqld thinks 
should exist is no longer available.   While the Error Log can't tell 
you which operation made those files "no longer available" it will have 
a fingerprint that such an action happened outside of mysqld.



Have you determined which method was used to make that database/schema 
disappear?


A normal DROP command (which could happen through an SQL injection 
attack) would not leave messages in the Error Log about "unable to 
access ..." or something similar. The server (mysqld) would know that 
the database was gone (because it removed it) and it wouldn't be trying 
to find it or the tables within it for your clients to use it.





... snip ...
No matter how many commands I try, sqlmap always report that phpMyAdmin 4.8.4 
is *NOT* SQL injectable. Perhaps I was using the wrong sqlmap commands all the 
time? The following is one of the many sqlmap commands I have used.

$ python sqlmap.py -u "https://www.EXAMPLE.com/phymyadmin/index.php?id=1; --level=1 
--dbms=mysql --sql-query="drop database"



Privately asking phpMyAdmin may be a better source of information about 
how to hack their system to do things it was not intended to do. This 
list is not about phpMyAdmin and it is very public.  They may also have 
a way of showing you some kind of trace or log that serves as a 
fingerprint for that happening.



--
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: Replication and user privileges

[Jim]

On 2/26/2019 1:57 PM, Jim wrote:

On 2/26/2019 9:44 AM, shawn l.green wrote:

Hello Jim,

On 2/25/2019 7:29 PM, Jim wrote:

On 2/25/2019 5:46 PM, shawn l.green wrote:

Hello Jim,

On 2/25/2019 5:04 PM, Jim wrote:
I have a question about mysql replication. I believe I understand 
most

of it, but have a question about user privileges.

I understand on the master, the replication user must have the
Repl_slave_priv privilege as described here:
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave 





My question is about what replication-related users and privileges 
must

exist on the slave.

So, for example, if an insert on the master that is to be 
replicated is

performed by user 'abc' with proper insert permissions on the master,
does that same 'abc' user with same insert permissions need to 
exist on

the slave as well?

In other words, what user is performing the replication operation 
on the

slave? I don't see any indication of users referenced in the bin logs
that I have examined on the master. Are user and privileges regarding
replicated queries irrelevant on the slave and that is handled all
internally via the replication thread with no regard to user 
privileges?


Thank you.
Jim



Your final supposition is correct. All privileges were checked and
verified on the master when the original command was executed. The
Replication system on the slave is going to repeat that change as well
as possible given the state of its copy of the data without regards to
"who originally performed this change" on the upstream master.

We do not store credentials in the Binary Log because they are not
important to either of the purposes of the Binary Log

* point-in-time recovery
or
* Replication (which is very much like an automated, continuous
point-in-time recovery)

===

That replication account you mentioned, on the master, is required to
give a slave (and you could have several) enough rights to read the
Binary Log and not much else. This allows you to create an account
that can login from a remote location with the "least privileges"
necessary to do its job. This minimizes your data's exposure should
that account become compromised.

Many other accounts could also have the REPL_SLAVE_PRIV privilege and
any of those could be used by a slave to do the same job. However
losing control over one of those more privileged accounts could pose a
higher risk to your data.




Thanks, Shawn. Your response confirms what I had assumed was happening.

So bottom line... what I plan to do is strip the various
insert/update/delete privileges from appropriate db users on my slaves.
I had placed them there originally because I thought they would be
needed for the replicated queries, but not true based on your response.

I only want the various mysql users used by my code to have select 
privs
on the slaves so that if somehow a slave was mistakenly written to 
via a
bug in my code, that write would fail and I would receive the error. 
The
slaves should only be used for selects and should never experience a 
write.


That would make sense based on our discussion, correct?

Thanks again.
Jim



As masters and slaves can exchange "positions" or "roles" (it depends 
on how you like to mentally visualize the relationship) within a 
replication graph in a failover situation, adding time to 
re-establish actual permissions using GRANT commands to reset user 
accounts to their old privileges may not be time you want to spend.


A cleaner, simpler solution is to set the --super-read-only flag in 
the server:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only 



That way, you get the behavior you want (no writes to a read-only 
slave) without forcing differences to the content of your privileges 
tables within different nodes of your Replication setup.  Each node 
will remain a transactionally consistent copy of all the others 
(within the temporal limits of replication being an asynchronous 
process).


Yours,



Thanks, Shawn.

super-read-only looks perfect for what I want. I can keep my slaves 
with all the potential users needed to take over as master without 
risking unwanted writes.


Given how you read:
"If the |read_only| 
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only> 
system variable is enabled, the server permits client updates only 
from users who have the |SUPER| 
<https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super> 
privilege. If the |super_read_only| 
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only> 
system variable is also enabled, the server prohibits client updates 
even from users who have |SUPER| 
<https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super>."
One somewhat gets the impression that in order to enable 
super_read_only, one 

Re: Replication and user privileges

[Jim]

On 2/26/2019 9:44 AM, shawn l.green wrote:

Hello Jim,

On 2/25/2019 7:29 PM, Jim wrote:

On 2/25/2019 5:46 PM, shawn l.green wrote:

Hello Jim,

On 2/25/2019 5:04 PM, Jim wrote:

I have a question about mysql replication. I believe I understand most
of it, but have a question about user privileges.

I understand on the master, the replication user must have the
Repl_slave_priv privilege as described here:
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave 





My question is about what replication-related users and privileges 
must

exist on the slave.

So, for example, if an insert on the master that is to be 
replicated is

performed by user 'abc' with proper insert permissions on the master,
does that same 'abc' user with same insert permissions need to 
exist on

the slave as well?

In other words, what user is performing the replication operation 
on the

slave? I don't see any indication of users referenced in the bin logs
that I have examined on the master. Are user and privileges regarding
replicated queries irrelevant on the slave and that is handled all
internally via the replication thread with no regard to user 
privileges?


Thank you.
Jim



Your final supposition is correct. All privileges were checked and
verified on the master when the original command was executed. The
Replication system on the slave is going to repeat that change as well
as possible given the state of its copy of the data without regards to
"who originally performed this change" on the upstream master.

We do not store credentials in the Binary Log because they are not
important to either of the purposes of the Binary Log

* point-in-time recovery
or
* Replication (which is very much like an automated, continuous
point-in-time recovery)

===

That replication account you mentioned, on the master, is required to
give a slave (and you could have several) enough rights to read the
Binary Log and not much else. This allows you to create an account
that can login from a remote location with the "least privileges"
necessary to do its job. This minimizes your data's exposure should
that account become compromised.

Many other accounts could also have the REPL_SLAVE_PRIV privilege and
any of those could be used by a slave to do the same job. However
losing control over one of those more privileged accounts could pose a
higher risk to your data.




Thanks, Shawn. Your response confirms what I had assumed was happening.

So bottom line... what I plan to do is strip the various
insert/update/delete privileges from appropriate db users on my slaves.
I had placed them there originally because I thought they would be
needed for the replicated queries, but not true based on your response.

I only want the various mysql users used by my code to have select privs
on the slaves so that if somehow a slave was mistakenly written to via a
bug in my code, that write would fail and I would receive the error. The
slaves should only be used for selects and should never experience a 
write.


That would make sense based on our discussion, correct?

Thanks again.
Jim



As masters and slaves can exchange "positions" or "roles" (it depends 
on how you like to mentally visualize the relationship) within a 
replication graph in a failover situation, adding time to re-establish 
actual permissions using GRANT commands to reset user accounts to 
their old privileges may not be time you want to spend.


A cleaner, simpler solution is to set the --super-read-only flag in 
the server:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only 



That way, you get the behavior you want (no writes to a read-only 
slave) without forcing differences to the content of your privileges 
tables within different nodes of your Replication setup.  Each node 
will remain a transactionally consistent copy of all the others 
(within the temporal limits of replication being an asynchronous 
process).


Yours,



Thanks, Shawn.

super-read-only looks perfect for what I want. I can keep my slaves with 
all the potential users needed to take over as master without risking 
unwanted writes.


Given how you read:
"If the |read_only| 
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only> 
system variable is enabled, the server permits client updates only from 
users who have the |SUPER| 
<https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super> 
privilege. If the |super_read_only| 
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only> 
system variable is also enabled, the server prohibits client updates 
even from users who have |SUPER| 
<https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super>."
One somewhat gets the impression that in order to enable 
super_read_only, one must also enable read_only.


However, base

Re: Replication and user privileges

[shawn l.green]

Hello Jim,

On 2/25/2019 7:29 PM, Jim wrote:

On 2/25/2019 5:46 PM, shawn l.green wrote:

Hello Jim,

On 2/25/2019 5:04 PM, Jim wrote:

I have a question about mysql replication. I believe I understand most
of it, but have a question about user privileges.

I understand on the master, the replication user must have the
Repl_slave_priv privilege as described here:
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave



My question is about what replication-related users and privileges must
exist on the slave.

So, for example, if an insert on the master that is to be replicated is
performed by user 'abc' with proper insert permissions on the master,
does that same 'abc' user with same insert permissions need to exist on
the slave as well?

In other words, what user is performing the replication operation on the
slave? I don't see any indication of users referenced in the bin logs
that I have examined on the master. Are user and privileges regarding
replicated queries irrelevant on the slave and that is handled all
internally via the replication thread with no regard to user privileges?

Thank you.
Jim



Your final supposition is correct. All privileges were checked and
verified on the master when the original command was executed. The
Replication system on the slave is going to repeat that change as well
as possible given the state of its copy of the data without regards to
"who originally performed this change" on the upstream master.

We do not store credentials in the Binary Log because they are not
important to either of the purposes of the Binary Log

* point-in-time recovery
or
* Replication (which is very much like an automated, continuous
point-in-time recovery)

===

That replication account you mentioned, on the master, is required to
give a slave (and you could have several) enough rights to read the
Binary Log and not much else. This allows you to create an account
that can login from a remote location with the "least privileges"
necessary to do its job. This minimizes your data's exposure should
that account become compromised.

Many other accounts could also have the REPL_SLAVE_PRIV privilege and
any of those could be used by a slave to do the same job. However
losing control over one of those more privileged accounts could pose a
higher risk to your data.




Thanks, Shawn. Your response confirms what I had assumed was happening.

So bottom line... what I plan to do is strip the various
insert/update/delete privileges from appropriate db users on my slaves.
I had placed them there originally because I thought they would be
needed for the replicated queries, but not true based on your response.

I only want the various mysql users used by my code to have select privs
on the slaves so that if somehow a slave was mistakenly written to via a
bug in my code, that write would fail and I would receive the error. The
slaves should only be used for selects and should never experience a write.

That would make sense based on our discussion, correct?

Thanks again.
Jim



As masters and slaves can exchange "positions" or "roles" (it depends on 
how you like to mentally visualize the relationship) within a 
replication graph in a failover situation, adding time to re-establish 
actual permissions using GRANT commands to reset user accounts to their 
old privileges may not be time you want to spend.


A cleaner, simpler solution is to set the --super-read-only flag in the 
server:

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

That way, you get the behavior you want (no writes to a read-only slave) 
without forcing differences to the content of your privileges tables 
within different nodes of your Replication setup.  Each node will remain 
a transactionally consistent copy of all the others (within the temporal 
limits of replication being an asynchronous process).


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

Re: Replication and user privileges

[Jim]

On 2/25/2019 5:46 PM, shawn l.green wrote:

Hello Jim,

On 2/25/2019 5:04 PM, Jim wrote:

I have a question about mysql replication. I believe I understand most
of it, but have a question about user privileges.

I understand on the master, the replication user must have the
Repl_slave_priv privilege as described here:
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave 




My question is about what replication-related users and privileges must
exist on the slave.

So, for example, if an insert on the master that is to be replicated is
performed by user 'abc' with proper insert permissions on the master,
does that same 'abc' user with same insert permissions need to exist on
the slave as well?

In other words, what user is performing the replication operation on the
slave? I don't see any indication of users referenced in the bin logs
that I have examined on the master. Are user and privileges regarding
replicated queries irrelevant on the slave and that is handled all
internally via the replication thread with no regard to user privileges?

Thank you.
Jim



Your final supposition is correct. All privileges were checked and 
verified on the master when the original command was executed. The 
Replication system on the slave is going to repeat that change as well 
as possible given the state of its copy of the data without regards to 
"who originally performed this change" on the upstream master.


We do not store credentials in the Binary Log because they are not 
important to either of the purposes of the Binary Log


* point-in-time recovery
or
* Replication (which is very much like an automated, continuous 
point-in-time recovery)


===

That replication account you mentioned, on the master, is required to 
give a slave (and you could have several) enough rights to read the 
Binary Log and not much else. This allows you to create an account 
that can login from a remote location with the "least privileges" 
necessary to do its job. This minimizes your data's exposure should 
that account become compromised.


Many other accounts could also have the REPL_SLAVE_PRIV privilege and 
any of those could be used by a slave to do the same job. However 
losing control over one of those more privileged accounts could pose a 
higher risk to your data.





Thanks, Shawn. Your response confirms what I had assumed was happening.

So bottom line... what I plan to do is strip the various 
insert/update/delete privileges from appropriate db users on my slaves.
I had placed them there originally because I thought they would be 
needed for the replicated queries, but not true based on your response.


I only want the various mysql users used by my code to have select privs 
on the slaves so that if somehow a slave was mistakenly written to via a 
bug in my code, that write would fail and I would receive the error. The 
slaves should only be used for selects and should never experience a write.


That would make sense based on our discussion, correct?

Thanks again.
Jim

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

Re: Replication and user privileges

[shawn l.green]

Hello Jim,

On 2/25/2019 5:04 PM, Jim wrote:

I have a question about mysql replication. I believe I understand most
of it, but have a question about user privileges.

I understand on the master, the replication user must have the
Repl_slave_priv privilege as described here:
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave


My question is about what replication-related users and privileges must
exist on the slave.

So, for example, if an insert on the master that is to be replicated is
performed by user 'abc' with proper insert permissions on the master,
does that same 'abc' user with same insert permissions need to exist on
the slave as well?

In other words, what user is performing the replication operation on the
slave? I don't see any indication of users referenced in the bin logs
that I have examined on the master. Are user and privileges regarding
replicated queries irrelevant on the slave and that is handled all
internally via the replication thread with no regard to user privileges?

Thank you.
Jim



Your final supposition is correct. All privileges were checked and 
verified on the master when the original command was executed. The 
Replication system on the slave is going to repeat that change as well 
as possible given the state of its copy of the data without regards to 
"who originally performed this change" on the upstream master.


We do not store credentials in the Binary Log because they are not 
important to either of the purposes of the Binary Log


* point-in-time recovery
or
* Replication (which is very much like an automated, continuous 
point-in-time recovery)


===

That replication account you mentioned, on the master, is required to 
give a slave (and you could have several) enough rights to read the 
Binary Log and not much else. This allows you to create an account that 
can login from a remote location with the "least privileges" necessary 
to do its job. This minimizes your data's exposure should that account 
become compromised.


Many other accounts could also have the REPL_SLAVE_PRIV privilege and 
any of those could be used by a slave to do the same job. However losing 
control over one of those more privileged accounts could pose a higher 
risk to your data.



--
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 V 8.0.12 and mysqdump

[Walter Harms]

> Halaasz Saandor  hat am 9. Februar 2019 um 10:01 geschrieben:
> 
> 
> 2019/02/08 10:32 ... Walter Harms:
> > Hello list,
> > i run into an unexpected problem with mysqldump:
> > 
> > mysqldump --version
> > mysqldump  Ver 8.0.12 for Linux on x86_64 (MySQL Community Server - GPL)
> > 
> > 
> > when i try it results in:
> > mysqldump: Error: 'Lost connection to MySQL server during query' when trying
> > to
> > dump tablespaces
> > mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'ndbinfo\_version'': MySQL
> > server has gone away (2006)
> 
> I regulary hav this problem with the command-line client (mysql.exe) and 
> when I asked R H gave this answer (and with the command-line client it 
> is much less imporant):
> 
>  Forwarded Message 


I found a solution with this (to set for mysqld in my.cnf):
wait_timeout = 31536000

It sets the time out very high and mysqldump can now complet the query.

personaly i would say this is not a propper solution as it does not solve
the problem of an sql statement taking 15min to complet.

re,
 wh

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

Re: mysql V 8.0.12 and mysqdump

[Walter Harms]

> Halaasz Saandor  hat am 9. Februar 2019 um 10:01 geschrieben:
> 
> 
> 2019/02/08 10:32 ... Walter Harms:
> > Hello list,
> > i run into an unexpected problem with mysqldump:
> > 
> > mysqldump --version
> > mysqldump  Ver 8.0.12 for Linux on x86_64 (MySQL Community Server - GPL)
> > 
> > 
> > when i try it results in:
> > mysqldump: Error: 'Lost connection to MySQL server during query' when trying
> > to
> > dump tablespaces
> > mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'ndbinfo\_version'': MySQL
> > server has gone away (2006)
> 
> I regulary hav this problem with the command-line client (mysql.exe) and 
> when I asked R H gave this answer (and with the command-line client it 
> is much less imporant):
> 
>  Forwarded Message 
> Subject: Re: ERROR 2013 (HY000): Lost connection to MySQL server during 
> query
> Date: Mon, 06 Jan 2014 17:07:45 +0100
> From: Reindl Harald 
> 
> 
> Am 06.01.2014 15:36, schrieb h...@tbbs.net:
>  > Now that I installed 5.6.14 on our Vista machine, when using "mysql" 
> I often see that error-message, which under 5.5.8 I never saw. What is 
> going on?
> 
> what about look in the servers logfiles
> most likely "max_allowed_packet" laughable low
> 

I do not thing so,
it is onvoius that the sql statement i postet is rediciusly slow, causing
mysqldump to terminate the connection. What anoys me most is the fact that
the result is empty. So i could remove it from the code, but i have no idea
about the consequences.

NTL i will try max_allowed_packet and see what will happen.

re,
 wh

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

Re: mysql V 8.0.12 and mysqdump

[Halaasz Saandor]

2019/02/08 10:32 ... Walter Harms:

Hello list,
i run into an unexpected problem with mysqldump:

mysqldump --version
mysqldump  Ver 8.0.12 for Linux on x86_64 (MySQL Community Server - GPL)


when i try it results in:
mysqldump: Error: 'Lost connection to MySQL server during query' when trying to
dump tablespaces
mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'ndbinfo\_version'': MySQL
server has gone away (2006)


I regulary hav this problem with the command-line client (mysql.exe) and 
when I asked R H gave this answer (and with the command-line client it 
is much less imporant):


 Forwarded Message 
Subject: Re: ERROR 2013 (HY000): Lost connection to MySQL server during 
query

Date: Mon, 06 Jan 2014 17:07:45 +0100
From: Reindl Harald 


Am 06.01.2014 15:36, schrieb h...@tbbs.net:
> Now that I installed 5.6.14 on our Vista machine, when using "mysql" 
I often see that error-message, which under 5.5.8 I never saw. What is 
going on?


what about look in the servers logfiles
most likely "max_allowed_packet" laughable low

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

Re: undiscribe

[Reindl Harald]

WTF

look at the "List-Unsubscribe:" header in every list message and say
thanks to fools using DMARC enabled domains on mailing-lists resulting
in list footers are removd everywhere

no mailing list unsubsribe on planet earth works that way spam every
subscriber, you got a welcome message as you subscribed with
instructions too, keep such informations for later usage

Am 02.12.18 um 15:43 schrieb 高强:
> |
> undiscribe

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

Re: [ANN] Mroonga 8.09 - Fast fulltext search for all languages on MySQL

[Horimoto Yasuhiro]

Hi,

Sorry, There was wrong release information in Mroonga 8.09.
The MySQL 8 is not supported.  That is still being handled.

On 2018/11/29 14:12, Horimoto Yasuhiro wrote:
> Hi,
> 
> Mroonga 8.09 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/2018/11/29/mroonga-8.09.html
> 
> Changes:
>http://mroonga.org/docs/news.html#release-8.09
> 
> Here are some topics in this release.
> 
>   * Supported Ubuntu 18.10 (Cosmic Cuttlefish).
>   * Supported MariaDB 10.3.10.
>   * Supported MariaDB 10.2.19
>   * Supported MariaDB 10.1.37
>   * Supported Percona Server 5.7.23-25.
>   * Supported MariaDB 10.3.11.
>   * Supported MySQL 5.6.42.
>   * Supported MySQL 5.7.24.
>   * Supported MySQL 8.
> 
> Regards,
> 

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

Re: Estimate mysqldump size

[Olivier]

Ronan McGlue  writes:

> Hi Olivier,
>
> On 28/11/2018 8:00 pm, Olivier wrote:
>> Hello,
>>
>> Is there a way that gives an estimate of the size of a mysqldump such a
>> way that it would always be larger than the real size?
>>
>> So far, I have found:
>>
>> mysql -s -u root -e "SELECT SUM(data_length) Data_BB FROM
>>information_schema.tables WHERE table_schema NOT IN
>>('information_schema','performance_schema','mysql');
>>
>> but the result may be smaller than the real size.
>
> In the above example, you also need to account for index_length, eg
>
> mysql>  select round(SUM(data_length+index_length)/POWER(1024,2),1) 
> Total_MB,round(SUM(data_length)/POWER(1024,2),1) 
> data_MB,round(SUM(index_length)/POWER(1024,2),1) index_MB  FROM 
> information_schema.tables where TABLE_SCHEMA not in ( 
> "information_schema", "performance_schema", "mysql") ;
> +--+-+--+
> | Total_MB | data_MB | index_MB |
> +--+-+--+
> |   4546.0 |  4093.7 |    452.2 |
> +--+-+--+
> 1 row in set (0.00 sec)

Thanks.

> However, this doesn't 100% map to OS file size ( if using innodb file 
> per table ) and will likely never be 100% accurate to what the OS 
> reports, due to fragmentation etc.
>
>>
>> I am writting a program that takes the result of mysqldump and pipe it
>> in a tar file.
>
> A typical global mysqldump ( ie taken with -A ) will be a single file.  
> Why are you then wanting to pipe this to a tar archive?

The tar file will be part of Amanda backup. On a full backup, it should
have the mysqldump and on incremental backups it should have the binary
logs.

Having everything in a tar file makes it very consistent and easy to
deal with in case of catastrophic failure (like everything is lost
except the tape, the backup can still be extracted by hand on a live
CD/single user system as it is all tar).

Amanda will also take care of the compression.

> Its also common for mysqldump to be compressed via a pipe due to the 
> nature of the output file created ( eg text files compress *very* well ) 
> , to then be sent across the network , eg via ssh
>
> mysqldump -u.. -p -A | gzip > schema.sql.gz
>
>
> Aside from your stated goal of piping to tar, if we can step back a 
> level briefly - what are you trying to achieve here?

A plugin for Amanda. I think a commercial solution exist, I don't need
anything very fancy, so I am trying to come up with my own solution.

Best regards,

Olivier

>
>> Tar file format has the size in the header, before the
>> data and if the size of the dump is bigger than the size declared in the
>> header, tar does not like that (if the size of the dump is smaller than
>> the actual size, it can be padded with spaces).
>>
>> So, the estimate must be larger than the actual dump, how to acheive
>> that?
>
> It wont be anything other than an estimate , however it should still be 
> reasonably close if you arent doing a *lot* of dml on it.
>
> You could artificially inflate the expected size by ,eg multiplying by 
> 1.1x or 1.2x , however there will always be an edge case table which 
> will be greater still..
>
>
> Regards
>
> Ronan McGlue
>
> MySQL Support
>
>
>
>>
>> Thanks in advance,
>>
>> Olivier
>>
>>
>

-- 

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

Re: Estimate mysqldump size

[Olivier]

Ronan McGlue  writes:

> Hi Olivier,
>
> On 28/11/2018 8:00 pm, Olivier wrote:
>> Hello,
>>
>> Is there a way that gives an estimate of the size of a mysqldump such a
>> way that it would always be larger than the real size?
>>
>> So far, I have found:
>>
>> mysql -s -u root -e "SELECT SUM(data_length) Data_BB FROM
>>information_schema.tables WHERE table_schema NOT IN
>>('information_schema','performance_schema','mysql');
>>
>> but the result may be smaller than the real size.
>
> In the above example, you also need to account for index_length, eg

But I thought I had read that indexes are not saved by a myslqdump, but
recreated on a restore?

Thanks in advance,

Olivier

>
> mysql>  select round(SUM(data_length+index_length)/POWER(1024,2),1) 
> Total_MB,round(SUM(data_length)/POWER(1024,2),1) 
> data_MB,round(SUM(index_length)/POWER(1024,2),1) index_MB  FROM 
> information_schema.tables where TABLE_SCHEMA not in ( 
> "information_schema", "performance_schema", "mysql") ;
> +--+-+--+
> | Total_MB | data_MB | index_MB |
> +--+-+--+
> |   4546.0 |  4093.7 |    452.2 |
> +--+-+--+
> 1 row in set (0.00 sec)
>
> However, this doesn't 100% map to OS file size ( if using innodb file 
> per table ) and will likely never be 100% accurate to what the OS 
> reports, due to fragmentation etc.
>
>>
>> I am writting a program that takes the result of mysqldump and pipe it
>> in a tar file.
>
> A typical global mysqldump ( ie taken with -A ) will be a single file.  
> Why are you then wanting to pipe this to a tar archive?
>
> Its also common for mysqldump to be compressed via a pipe due to the 
> nature of the output file created ( eg text files compress *very* well ) 
> , to then be sent across the network , eg via ssh
>
> mysqldump -u.. -p -A | gzip > schema.sql.gz
>
>
> Aside from your stated goal of piping to tar, if we can step back a 
> level briefly - what are you trying to achieve here?
>
>> Tar file format has the size in the header, before the
>> data and if the size of the dump is bigger than the size declared in the
>> header, tar does not like that (if the size of the dump is smaller than
>> the actual size, it can be padded with spaces).
>>
>> So, the estimate must be larger than the actual dump, how to acheive
>> that?
>
> It wont be anything other than an estimate , however it should still be 
> reasonably close if you arent doing a *lot* of dml on it.
>
> You could artificially inflate the expected size by ,eg multiplying by 
> 1.1x or 1.2x , however there will always be an edge case table which 
> will be greater still..
>
>
> Regards
>
> Ronan McGlue
>
> MySQL Support
>
>
>
>>
>> Thanks in advance,
>>
>> Olivier
>>
>>
>

-- 

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

Re: Estimate mysqldump size

[Reindl Harald]

Am 28.11.18 um 10:00 schrieb Olivier:
> Is there a way that gives an estimate of the size of a mysqldump such a
> way that it would always be larger than the real size?
keep in mind that a dump has tons of sql statements not existing that
way in the data

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

Re: Estimate mysqldump size

[Ronan McGlue]

Hi Olivier,

On 28/11/2018 8:00 pm, Olivier wrote:

Hello,

Is there a way that gives an estimate of the size of a mysqldump such a
way that it would always be larger than the real size?

So far, I have found:

mysql -s -u root -e "SELECT SUM(data_length) Data_BB FROM
   information_schema.tables WHERE table_schema NOT IN
   ('information_schema','performance_schema','mysql');

but the result may be smaller than the real size.


In the above example, you also need to account for index_length, eg

mysql>  select round(SUM(data_length+index_length)/POWER(1024,2),1) 
Total_MB,round(SUM(data_length)/POWER(1024,2),1) 
data_MB,round(SUM(index_length)/POWER(1024,2),1) index_MB  FROM 
information_schema.tables where TABLE_SCHEMA not in ( 
"information_schema", "performance_schema", "mysql") ;

+--+-+--+
| Total_MB | data_MB | index_MB |
+--+-+--+
|   4546.0 |  4093.7 |    452.2 |
+--+-+--+
1 row in set (0.00 sec)

However, this doesn't 100% map to OS file size ( if using innodb file 
per table ) and will likely never be 100% accurate to what the OS 
reports, due to fragmentation etc.




I am writting a program that takes the result of mysqldump and pipe it
in a tar file.


A typical global mysqldump ( ie taken with -A ) will be a single file.  
Why are you then wanting to pipe this to a tar archive?


Its also common for mysqldump to be compressed via a pipe due to the 
nature of the output file created ( eg text files compress *very* well ) 
, to then be sent across the network , eg via ssh


mysqldump -u.. -p -A | gzip > schema.sql.gz


Aside from your stated goal of piping to tar, if we can step back a 
level briefly - what are you trying to achieve here?



Tar file format has the size in the header, before the
data and if the size of the dump is bigger than the size declared in the
header, tar does not like that (if the size of the dump is smaller than
the actual size, it can be padded with spaces).

So, the estimate must be larger than the actual dump, how to acheive
that?


It wont be anything other than an estimate , however it should still be 
reasonably close if you arent doing a *lot* of dml on it.


You could artificially inflate the expected size by ,eg multiplying by 
1.1x or 1.2x , however there will always be an edge case table which 
will be greater still..



Regards

Ronan McGlue

MySQL Support





Thanks in advance,

Olivier




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

Re: Connections from mysql8.0 to mysql5.1 - bad handshake

[shawn l.green]

Hi Jim,

On 10/31/2018 7:12 PM, Halaasz Saandor wrote:

2018/10/31 15:15 ... Jim:

Given the following bug report, what I am trying to do does not sound
hopeful:
https://bugs.mysql.com/bug.php?id=90994


...


Any thoughts or do I need to accept that what I'm attempting just
isn't going to work?


 From the same bug report, id=90994:

[24 Oct 20:17] Brad Jackson

Version 8.0.13 was released on 10/22 and the change list says:

"Support for MySQL 5.5 by MySQL Workbench 8.0 was removed. If you still
need to use MySQL Workbench on a MySQL 5.5 server, you can use MySQL
Workbench 6.3"

I guess we're all stuck on the old version until we upgrade our servers.



Halaasz is on the right track.

As a client (which is what a replication slave really is), MySQL 8.0 
doesn't know how to login to a 5.1 server. Those old handshakes have 
been retired.


We only maintain connection compatibility with the "previous version" 
which, relative to 8.0, is 5.7 . Older versions may work, but it isn't 
guaranteed.


You could, potentially, set up replication chain like this
5.1 -> (is a master of) 5.5 -> 5.6 -> 5.7 -> 8.0

But then you would need to worry if your table definitions (we have 
deprecated and removed some data types since 5.1 was the "current 
version") are even legal in 8.0.  Other language features, like command 
syntaxes, also evolve over time.


There's a lot of deprecation history you will need to worry about. 
Something that is legal to replicate from 5.1 -> 5.5 may no longer be 
legal between 5.5 and 5.6 because 5.5 may have been the last version for 
which that feature was supported.  You may be better off with a 
lift-and-shift upgrade to try to establish a copy of your non-system 
tables and other objects (like stored procedures or views) in an empty 
initialized 8.0 server.  Then you can use a set of 8.0 CREATE USER and 
GRANT commands (you can't use naked GRANT commands to create accounts 
any longer. That feature was deprecated and removed in earlier versions) 
to populate your 8.0 server with user accounts.


Once you reach that stage, you are ready to start testing copies of your 
applications against 8.0 to see what else will need to be updated (such 
as the library you use to connect to MySQL).  Moving from 5.1 to 8.0 is 
a big shift, you potentially have a lot of work ahead of you.



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

Re: Connections from mysql8.0 to mysql5.1 - bad handshake

[Halaasz Saandor]

2018/10/31 15:15 ... Jim:
Given the following bug report, what I am trying to do does not sound 
hopeful:

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


...


Any thoughts or do I need to accept that what I'm attempting just isn't 
going to work?


From the same bug report, id=90994:

[24 Oct 20:17] Brad Jackson

Version 8.0.13 was released on 10/22 and the change list says:

"Support for MySQL 5.5 by MySQL Workbench 8.0 was removed. If you still 
need to use MySQL Workbench on a MySQL 5.5 server, you can use MySQL 
Workbench 6.3"


I guess we're all stuck on the old version until we upgrade our servers.

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

Re: High cpu usage

[shawn l.green]

Hello Machiel,

(I am guessing you can only process top-posts?)

When you stop only the IO thread, you may leave the last event recorded 
to the Relay Log incomplete.


When it gets to that part of the Relay Log, the SQL thread may only be 
part-way through a transaction. It will keep that transaction alive 
waiting for the IO thread to finish downloading the rest of the event 
from the master's copy of the binary log. That partially-complete 
transaction is most likely blocking the ability of your other commands 
to operate more efficiently for several reasons:

 * MVCC
 * InnoDB history length
 * Incomplete transactions to secondary indexes forcing those commands 
to scan the table instead of using the index (related to MVCC)



We made the SQL thread wait so that intermittent networks (a real thing 
years ago) would not "break" replication. We would wait for connectivity 
to resume so that replication could continue.


A safer plan is to stop both threads at the same time. Just use the 
basic STOP SLAVE command instead of the more specific STOP SLAVE IO_THREAD.


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.




On 10/26/2018 2:09 AM, Machiel Richards wrote:

Hi Shawn


Thank you for the response.


 In order to pause the slave , the stop the sql_io_thread, and to unpause 
they simply start the thread.


  I have run "show engine innodb status" yes and the threads show 90% as 
sleeping and then a few selects , all from the same table as it does a lot of 
authentications for dial outs.


 I will have a look at the results from SELECT * FROM 
information_schema.INNODB_TRX; during the day as we get this issue regularly 
and will provide feedback.



Regards





(earlier thread below... )




From: shawn l.green 
Sent: Thursday, 25 October 2018 9:54:10 PM
To: mysql@lists.mysql.com
Subject: Re: High cpu usage

Hello Machiel,

On 10/25/2018 6:09 AM, Machiel Richards wrote:

Good day all


 Hoping this mail finds you well.


 I am hoping someone can perhaps give us some guidance here as we now seem 
to be stuck on a problem and have not been able to find a solution after more 
than a month.


 We are running an opensips server on Centos 6.5 , using mysql 5.7.13 which 
was installed via Tarball file.


 The server is setup as a slave and master and receives updates from 
opensips config nodes as well as registrations from workers.


 Replication is paused during the day and forward replication (master) is 
disabled at the moment.


 However , we are getting an issue every day on mysql side in terms of 
mysql pushing up server load.



  During the day the server is running fine with a load avg not going above 
1.5 during peak times.


  However in the evening , replication is unpaused, and completes 
processing and catchup within about 15 minutes and is paused again about 30 
minutes after the unpause.



Give or take 45 minutes to an hour after the replication is paused 
again, mysql starts to cause high cpu usage with no apparent processes running 
as can be seen on full processlist (maybe one or two selects which completes 
fairly quickly)


 The higher load, causes queries to slow down however and opensips to 
start timing out on db connections, causing clients to resubmit.


   The resubmits , then obviously causes even more load spiking the mysql 
load to increase as well as the server load and eventually opensips kills 
itself.



  I have looked at the disks, iowaits, memory usage, all is fine.


  We do not see any strange queries or stick queries, no deadlocks, etc... 
only the increase in selects after mysql starts to push up the cpu load.



  We have added all indexes we can find, but even this has made no 
difference at all.


   Currently we are at a loss so I am hoping someone else can assist in 
explaining how else we can find out why mysql is eating up the cpu ...



 The same behaviour can also be seen the moment any new feature is added to 
the server that requires mysql processing to be done, so this does not seem to 
be specifically related to replication, however it does seem like the current 
load from replication causes mysql to act up.


the server is currently running on SSD (recently replaced) , and 8Gb of 
memory with 1 x quadcore CPU.



  should any more info be required, please feel free to ask.




When you say pause replication, what command are you executing on the
slave?

Which end of the system is experiencing the high CPU usage: the master
or the slave?

Have you checked these resources to see what the InnoDB main or
background threads are doing when your CPU starts to spike? (you could
be in a massive rollback)

SHOW ENG

Re: High cpu usage

[Machiel Richards]

Hi Shawn


   Thank you for the response.


In order to pause the slave , the stop the sql_io_thread, and to unpause 
they simply start the thread.


 I have run "show engine innodb status" yes and the threads show 90% as 
sleeping and then a few selects , all from the same table as it does a lot of 
authentications for dial outs.


I will have a look at the results from SELECT * FROM 
information_schema.INNODB_TRX; during the day as we get this issue regularly 
and will provide feedback.



Regards



From: shawn l.green 
Sent: Thursday, 25 October 2018 9:54:10 PM
To: mysql@lists.mysql.com
Subject: Re: High cpu usage

Hello Machiel,

On 10/25/2018 6:09 AM, Machiel Richards wrote:
> Good day all
>
>
> Hoping this mail finds you well.
>
>
> I am hoping someone can perhaps give us some guidance here as we now seem 
> to be stuck on a problem and have not been able to find a solution after more 
> than a month.
>
>
> We are running an opensips server on Centos 6.5 , using mysql 5.7.13 
> which was installed via Tarball file.
>
>
> The server is setup as a slave and master and receives updates from 
> opensips config nodes as well as registrations from workers.
>
>
> Replication is paused during the day and forward replication (master) is 
> disabled at the moment.
>
>
> However , we are getting an issue every day on mysql side in terms of 
> mysql pushing up server load.
>
>
>
>  During the day the server is running fine with a load avg not going 
> above 1.5 during peak times.
>
>
>  However in the evening , replication is unpaused, and completes 
> processing and catchup within about 15 minutes and is paused again about 30 
> minutes after the unpause.
>
>
>
>Give or take 45 minutes to an hour after the replication is paused 
> again, mysql starts to cause high cpu usage with no apparent processes 
> running as can be seen on full processlist (maybe one or two selects which 
> completes fairly quickly)
>
>
> The higher load, causes queries to slow down however and opensips to 
> start timing out on db connections, causing clients to resubmit.
>
>
>   The resubmits , then obviously causes even more load spiking the mysql 
> load to increase as well as the server load and eventually opensips kills 
> itself.
>
>
>
>  I have looked at the disks, iowaits, memory usage, all is fine.
>
>
>  We do not see any strange queries or stick queries, no deadlocks, etc... 
> only the increase in selects after mysql starts to push up the cpu load.
>
>
>
>  We have added all indexes we can find, but even this has made no 
> difference at all.
>
>
>   Currently we are at a loss so I am hoping someone else can assist in 
> explaining how else we can find out why mysql is eating up the cpu ...
>
>
>
> The same behaviour can also be seen the moment any new feature is added 
> to the server that requires mysql processing to be done, so this does not 
> seem to be specifically related to replication, however it does seem like the 
> current load from replication causes mysql to act up.
>
>
>the server is currently running on SSD (recently replaced) , and 8Gb of 
> memory with 1 x quadcore CPU.
>
>
>
>  should any more info be required, please feel free to ask.
>
>

When you say pause replication, what command are you executing on the
slave?

Which end of the system is experiencing the high CPU usage: the master
or the slave?

Have you checked these resources to see what the InnoDB main or
background threads are doing when your CPU starts to spike? (you could
be in a massive rollback)

SHOW ENGINE INNODB STATUS
SELECT * FROM information_schema.INNODB_TRX;

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc.

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: High cpu usage

[shawn l.green]

Hello Machiel,

On 10/25/2018 6:09 AM, Machiel Richards wrote:

Good day all


Hoping this mail finds you well.


I am hoping someone can perhaps give us some guidance here as we now seem 
to be stuck on a problem and have not been able to find a solution after more 
than a month.


We are running an opensips server on Centos 6.5 , using mysql 5.7.13 which 
was installed via Tarball file.


The server is setup as a slave and master and receives updates from 
opensips config nodes as well as registrations from workers.


Replication is paused during the day and forward replication (master) is 
disabled at the moment.


However , we are getting an issue every day on mysql side in terms of mysql 
pushing up server load.



 During the day the server is running fine with a load avg not going above 
1.5 during peak times.


 However in the evening , replication is unpaused, and completes processing 
and catchup within about 15 minutes and is paused again about 30 minutes after 
the unpause.



   Give or take 45 minutes to an hour after the replication is paused 
again, mysql starts to cause high cpu usage with no apparent processes running 
as can be seen on full processlist (maybe one or two selects which completes 
fairly quickly)


The higher load, causes queries to slow down however and opensips to 
start timing out on db connections, causing clients to resubmit.


  The resubmits , then obviously causes even more load spiking the mysql 
load to increase as well as the server load and eventually opensips kills 
itself.



 I have looked at the disks, iowaits, memory usage, all is fine.


 We do not see any strange queries or stick queries, no deadlocks, etc... 
only the increase in selects after mysql starts to push up the cpu load.



 We have added all indexes we can find, but even this has made no 
difference at all.


  Currently we are at a loss so I am hoping someone else can assist in 
explaining how else we can find out why mysql is eating up the cpu ...



The same behaviour can also be seen the moment any new feature is added to 
the server that requires mysql processing to be done, so this does not seem to 
be specifically related to replication, however it does seem like the current 
load from replication causes mysql to act up.


   the server is currently running on SSD (recently replaced) , and 8Gb of 
memory with 1 x quadcore CPU.



 should any more info be required, please feel free to ask.




When you say pause replication, what command are you executing on the 
slave?


Which end of the system is experiencing the high CPU usage: the master 
or the slave?


Have you checked these resources to see what the InnoDB main or 
background threads are doing when your CPU starts to spike? (you could 
be in a massive rollback)


SHOW ENGINE INNODB STATUS
SELECT * FROM information_schema.INNODB_TRX;

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc.

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: info on open tables

[Halaasz Saandor]

2018/09/28 07:48 ... Machiel Richards:

   During the times when it experiences issues, I found that there is always about 
4-5 processes on mysql that gets stuck on "closing table" state and once the 
software (opensips) is restarted due to a sigfault, then these queries are killed as well.


   Based on information found we added grafana graphs and did notice that 
the table_open_cache is fully used during the time of the sigfaults and the 
queries getting stuck so we tested by increasing the table_open_cache.


  This did however not resolve the issue as we simply saw table_open_cache 
using the full value of 3000 and once again the same behaviour.



  When running "Show open tables" though, we find that the amount of open 
tables is only 283 and a count of all tables returned that there is only 381 tables on 
the server.


 However when running "Show global status like 'open_tables' we get a value 
of 2900.


 So my questions are as follows :


  1. Why is the table_open_cache used up if there are not even that many 
tables.

  2. Why does these values differ so much? or do they provide different 
info.



These occur to me:

"If you have no privileges for a table, it does not show up in the 
output from SHOW OPEN TABLES."


If an table is kept open, but there is no action, does it stay in the cache?

One shows really only the tables, the other shows a table with its user.

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

Re: what is the rationale for not allowing LOCK TABLES in a stored procedure

[shawn l.green]

Hello Mogens,

On 8/18/2018 2:32 PM, Mogens Melander wrote:

Guys,

I think I remember this from way back.

You could ask for a lock, and get an OK if it is safe.

Something like, if there is pending transactions, on your target tables,
you would get a NO.

But then again. I could be wrong, and Shawn is the authority on this.



Your request for a lock would have waited until all existing readers or 
writers (depending on the type of lock you asked for) had finished using 
the tables you wanted to lock. By extension, that means that any 
transactions active against the tables you wanted to lock would have 
also needed to have committed or rolled back before your request would 
have been granted. Any new actions against the table would have been 
queued up behind your LOCK request. This has confused more than one DBA 
as they didn't realize that the LOCK was going to be such a tight 
bottleneck.


These kinds of whole table locks live above the blocking/locking 
coordination of the individual storage engines or the transaction 
control code.  They are managed in the "server layer" of our code.


This separation of scope is one reason why blending transactional and 
non-transactional tables in the same data management process is 
generally frowned on. Either be all-transactional (InnoDB) or not. The 
behavior will be easier to predict allowing your developers to use 
either the transaction control commands (BEGIN/COMMIT/ROLLBACK/... ) or 
the LOCK commands with confidence.



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.







=== original thread ===


On 2018-08-18 23:59, shawn l.green wrote:

Hello Jeff,

On 8/13/2018 12:05 PM, j...@lxvi.net wrote:

Hello, I have read through several pages of the reference manual, and
I've seen several instances where it is stated that LOCK TABLES (and
UNLOCK TABLES) is not allowed in a stored procedure, but so far, I
haven't found an explanation as to *why* that is. Could someone please
enlighten me?

Thanks


Normally, the list is more responsive than this. This is a pretty easy
question and someone usually handles those before I need to step in as
a backstop.

The key why you cannot execute a LOCK TABLE command within a stored
program is here:
https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
###
LOCK TABLES is not transaction-safe and implicitly commits any active
transaction before attempting to lock the tables.
###

Stored programs execute under the scope of the transaction in which
they are started. That determines which sets of rows are "visible" to
the routine and sets boundaries on what may be committed or rolled
back should the need arise.

(a simple example)
* your session: START TRANSACTION
* your session: ...other data activity ...
* your session (INSERT ... )
  * causes an INSERT trigger to fire
* which calls a stored procedure

If that stored procedure or that trigger called a LOCK TABLE command,
it would forcibly COMMIT the existing transaction you had been working
within until that moment.  Your half-completed work would have become
fully committed even if a later step had needed you to issue a
ROLLBACK command.

Note, even if you are not in a multi-statement transaction that any
stored programs called by or executed within the scope of your user
command are part of that little mini (auto-committed) transaction.

Does that help?

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN






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

Re: what is the rationale for not allowing LOCK TABLES in a stored procedure

[Mogens Melander]

Guys,

I think I remember this from way back.

You could ask for a lock, and get an OK if it is safe.

Something like, if there is pending transactions, on your target tables,
you would get a NO.

But then again. I could be wrong, and Shawn is the authority on this.

On 2018-08-18 23:59, shawn l.green wrote:

Hello Jeff,

On 8/13/2018 12:05 PM, j...@lxvi.net wrote:

Hello, I have read through several pages of the reference manual, and
I've seen several instances where it is stated that LOCK TABLES (and
UNLOCK TABLES) is not allowed in a stored procedure, but so far, I
haven't found an explanation as to *why* that is. Could someone please
enlighten me?

Thanks


Normally, the list is more responsive than this. This is a pretty easy
question and someone usually handles those before I need to step in as
a backstop.

The key why you cannot execute a LOCK TABLE command within a stored
program is here:
https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
###
LOCK TABLES is not transaction-safe and implicitly commits any active
transaction before attempting to lock the tables.
###

Stored programs execute under the scope of the transaction in which
they are started. That determines which sets of rows are "visible" to
the routine and sets boundaries on what may be committed or rolled
back should the need arise.

(a simple example)
* your session: START TRANSACTION
* your session: ...other data activity ...
* your session (INSERT ... )
  * causes an INSERT trigger to fire
* which calls a stored procedure

If that stored procedure or that trigger called a LOCK TABLE command,
it would forcibly COMMIT the existing transaction you had been working
within until that moment.  Your half-completed work would have become
fully committed even if a later step had needed you to issue a
ROLLBACK command.

Note, even if you are not in a multi-statement transaction that any
stored programs called by or executed within the scope of your user
command are part of that little mini (auto-committed) transaction.

Does that help?

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN


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

Re: how to select the record with one sql statement?

[shawn l.green]

Hello sea,

On 8/13/2018 7:01 PM, sea wrote:

helle,
I have a table, like this:
  pigId  dayweigt
   pig1  2018-1-121
   pig2  2018-1-131
   pig3  2018-1-141
   pig1  2018-1-222
   pig2  2018-1-231
   pig3  2018-1-240
   pig1  2018-1-323  pig2  2018-1-330
   pig3  2018-1-341
   .

only the pig1'weight increase continuously for 3 days.   Giving the input: 
num_of_day(weight increasing continuously for num_of_day);   expecting the 
output: certain_day, pigId;from certain_day,  pigId'weight increasing 
continuously for num_of_day.  How to select the records in one sql 
statement?
 thanks



I've thought about this a bit (since your question appeared on the list) 
and I break down the tasks you need to perform in my head like this. 
(Others on the list may have different ways to approach the same problem)


task 1 - For each bucket, a pigId value, assemble an ordered list (not a 
set) of each weight sorted by time.  (not hard)


task 2 - Within each ordered list, compare the values of every 
consecutive pair.  (several ways to do this)


task 3 - Iterate over those "consecutive value differences" generated in 
task 2 looking for the longest sequence of positive non-zero values for 
each pigId.  (this is not really a set-oriented process so normal SELECT 
or GROUP BY command patterns will not handle it with any efficency)


I'm afraid that attempting all of that sequencing and iteration using 
just a single set-based SQL command is not going to be practical. Using 
one or more cursors within a stored procedure is your best bet for this 
type of sequential trend analysis.


I could easily imagine the first step as a INSERT...SELECT...ORDER BY... 
command going to a new table with an autoincrement column on it (to 
provide a global sequence number across all of your individual pigId 
values) . The second step could do a self join to that table where the 
ON clause could look like

  a.pigId = b.pigID AND a.seq-1 = b.seq

But at that point, counting the length of sequences (and remembering 
when each trend became positive) needs a loop. That's where even 
complicated set-wise SQL fails you and you need to shift into using the 
SQL of stored programs.



Regards,
--
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: what is the rationale for not allowing LOCK TABLES in a stored procedure

[shawn l.green]

Hello Jeff,

On 8/13/2018 12:05 PM, j...@lxvi.net wrote:

Hello, I have read through several pages of the reference manual, and
I've seen several instances where it is stated that LOCK TABLES (and
UNLOCK TABLES) is not allowed in a stored procedure, but so far, I
haven't found an explanation as to *why* that is. Could someone please
enlighten me?

Thanks


Normally, the list is more responsive than this. This is a pretty easy 
question and someone usually handles those before I need to step in as a 
backstop.


The key why you cannot execute a LOCK TABLE command within a stored 
program is here:

https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html
###
LOCK TABLES is not transaction-safe and implicitly commits any active 
transaction before attempting to lock the tables.

###

Stored programs execute under the scope of the transaction in which they 
are started. That determines which sets of rows are "visible" to the 
routine and sets boundaries on what may be committed or rolled back 
should the need arise.


(a simple example)
* your session: START TRANSACTION
* your session: ...other data activity ...
* your session (INSERT ... )
  * causes an INSERT trigger to fire
* which calls a stored procedure

If that stored procedure or that trigger called a LOCK TABLE command, it 
would forcibly COMMIT the existing transaction you had been working 
within until that moment.  Your half-completed work would have become 
fully committed even if a later step had needed you to issue a ROLLBACK 
command.


Note, even if you are not in a multi-statement transaction that any 
stored programs called by or executed within the scope of your user 
command are part of that little mini (auto-committed) transaction.


Does that help?

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

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

Re: ANN: Upscene releases Database Workbench 5.4.4

[webmaster]

Sent from my Samsung Galaxy smartphone.
 Original message From: "Martijn Tonies (Upscene Productions)" 
 Date: 09/07/2018  13:17  (GMT+00:00) To: 
firebird-to...@yahoogroups.com, mysql@lists.mysql.com Subject: ANN: Upscene 
releases Database Workbench 5.4.4 
Upscene Productions is proud to announce the availability of the next version 
of the popular multi-DBMS development tool:

" Database Workbench 5.4.4 “

This release includes support for MariaDB 10.1 and 10.2, MySQL 5.7 and Azure 
and the latest version of PostgreSQL. Previous releases included a custom 
report writer, a renewed stored routine debugger with full support for Firebird 
3 Stored Functions and Packages and several other features.

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

Here's the full list of changes
http://www.upscene.com/go/?go=tracker=5.4.x=12

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: MySQL optimization for testing purpose

[Sebastien FLAESCH]

On 04/03/2018 06:15 PM, Sebastien FLAESCH wrote:

On 04/03/2018 05:59 PM, Sebastien FLAESCH wrote:

Hi all,

Looking for some configuration tips to speed execution of our non-regression 
tests with MySQL.

We do pure functional testing, data persistence is not critical.

Our tests are not using a lot of data, but we create and drop a lot of test 
tables.

We have concurrency tests (processes locking rows), so this feature matters.

Are there some InnoDB params to enable or disable to speed up our tests?

Some tests behave a bit strange, sometimes one of our test takes 1 min 30 secs, 
sometimes
it takes only 15 seconds...


In fact this tests creates and drops ~150 times the same table, having a single 
column
using different types each time.

I can clearly see that sometimes table creations are fast, but from time to 
time it
slows down to about a second to create a table.



How can this happen?

Any log I could enable and provide here?

Thanks in advance!
Seb






FYI, have tried following settings, but it did not help:

innodb_stats_auto_recalc=0
innodb_file_per_table=0
innodb_stats_persistent=0

I have recreated my database after restarting the server.

Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Seb

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

Re: MySQL optimization for testing purpose

[Sebastien FLAESCH]

On 04/03/2018 05:59 PM, Sebastien FLAESCH wrote:

Hi all,

Looking for some configuration tips to speed execution of our non-regression 
tests with MySQL.

We do pure functional testing, data persistence is not critical.

Our tests are not using a lot of data, but we create and drop a lot of test 
tables.

We have concurrency tests (processes locking rows), so this feature matters.

Are there some InnoDB params to enable or disable to speed up our tests?

Some tests behave a bit strange, sometimes one of our test takes 1 min 30 secs, 
sometimes
it takes only 15 seconds...


In fact this tests creates and drops ~150 times the same table, having a single 
column
using different types each time.

I can clearly see that sometimes table creations are fast, but from time to 
time it
slows down to about a second to create a table.



How can this happen?

Any log I could enable and provide here?

Thanks in advance!
Seb




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

Re: How to get the MySQL Command-Line Tool to display Unicode properly?

[Johan De Meersman]

Probably the dumbest suggestion yet, but have you tried "set names utf8" in the 
client?

On 20 March 2018 20:50:08 CET, Roger House  wrote:
>
>
>On 03/15/2018 02:06 PM, Roger House wrote:
>>
>>
>> On 03/15/2018 11:30 AM, shawn l.green wrote:
>>> Hi Roger,
>>>
>>> (please note, this is a bottom-post forum)
>>>
>>> On 3/13/2018 7:54 PM, Roger House wrote:
>>> >
>>> > On 03/13/2018 03:11 PM, Reindl Harald wrote:
>>> >>
>>> >>
>>> >> Am 13.03.2018 um 22:59 schrieb Roger House:
>>> >>> In all respects except one, the treatment of Unicode works just 
>>> fine.
>>> >>> I can write Unicode to database tables, read it, display it,
>etc.,
>>> >>> with no problems. The exception is mysql, the MySQL Command-Line
>>> >>> Tool. When I execute a SELECT statement to see rows in a table
>>> >>> containing the Venus and Mars Unicode characters, here is what I
>see
>>> >>> on the screen:
>>> >>>
>>> >>> || Venus | ♀ | | Mars | ♂ | |
>>> >>>
>>> >>> What I should see in the right column are the standard glyphs
>for
>>> >>> Venus and Mars.
>>> >>>
>>> >>> Any ideas about how to get the MySQL Command-Line Tool to
>display
>>> >>> Unicode properly?
>>> >> what operating system
>>> >> what terminal
>>> >>
>>> >> all recent Linux systems have UTF8 as default
>>> >>
>>> >
>>> >
 I am running Ubuntu MATE 16.04.  I have the problem also on Windows
>7
 and on Mac OS Version 10.11.6.  I do not think that the problem has
>to
 do with the operating system nor the terminal.  Everything about
>the
 Unicode text works fine in all tools such as editors, the cat
>command,
 etc.  It is only when I am running mysql and I issue a SELECT 
 command to
 see what is in a row.  Then the UTF-8 is not rendered properly. I
 believe the problem is with mysql.

 Roger

>>>
>>> If I presume that your terminal has a code page that is 
>>> utf8-compatible (you say that cat command renders the multibyte 
>>> characters just fine) then it could be your client-side mysql 
>>> settings that are rendering those multibyte characters into 
>>> individual glyphs based on their individual byte values.
>>>
>>> The next time you are in mysql and have a chance to look at some
>utf8 
>>> data, please collect and share these two reports:
>>>
>>> status
>>> SHOW GLOBAL VARIABLES like '%haracter%';
>>>
>>> (you can obfuscate any sensitive details like server names or
>addresses)
>>>
>>> Yours,
>> Here is the requested info:
>>
>> mysql> status
>> --
>> mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine 
>> wrapper
>>
>> Connection id:        5
>> Current database:    ephemeris
>> Current user:        root@localhost
>> SSL:            Not in use
>> Current pager:        stdout
>> Using outfile:        ''
>> Using delimiter:    ;
>> Server version:        5.7.21-0ubuntu0.16.04.1 (Ubuntu)
>> Protocol version:    10
>> Connection:        Localhost via UNIX socket
>> Server characterset:    utf8mb4
>> Db characterset:    latin1
>> Client characterset:    utf8mb4
>> Conn.  characterset:    utf8mb4
>> UNIX socket:        /var/run/mysqld/mysqld.sock
>> Uptime:            6 hours 17 min 8 sec
>>
>> Threads: 1  Questions: 28  Slow queries: 0  Opens: 122 Flush tables: 
>> 1  Open tables: 41  Queries per second avg: 0.001
>> --
>>
>> mysql> show global variables like '%haracter%';
>> +--++
>> | Variable_name    | Value  |
>> +--++
>> | character_set_client | utf8mb4    |
>> | character_set_connection | utf8mb4    |
>> | character_set_database   | utf8mb4    |
>> | character_set_filesystem | binary |
>> | character_set_results    | utf8mb4    |
>> | character_set_server | utf8mb4    |
>> | character_set_system | utf8   |
>> | character_sets_dir   | /usr/share/mysql/charsets/ |
>> +--++
>> 8 rows in set (0.01 sec)
>>
>>
>Update
>
>I noticed that the status command shows
>
>     Db characterset:    latin1
>
>whereas all the other charactersets are utf8mb4.  So I looked around to
>see
>how to change the Db characterset, and came up with this:
>
>     ALTER DATABASE ephemeris CHARACTER SET utf8mb4 COLLATE 
>utf8mb4_general_ci;
>
>After which, status shows
>
>     Db characterset:    utf8mb4
>
>So the database characterset has now been changed.  But
>
>     select * from planet_desc;
>
>still shows
>
>     Venus | ♀
>     Mars  | ♂
>
>I shut down the mysql service and restarted it, but this did not change
>anything.
>
>I'm beginning to think that the command line tool mysql does not pay
>any
>attention to character sets when the SELECT command displays on the
>terminal.
>
>Roger

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

Re: How to get the MySQL Command-Line Tool to display Unicode properly?

[Roger House]

On 03/15/2018 02:06 PM, Roger House wrote:



On 03/15/2018 11:30 AM, shawn l.green wrote:

Hi Roger,

(please note, this is a bottom-post forum)

On 3/13/2018 7:54 PM, Roger House wrote:
>
> On 03/13/2018 03:11 PM, Reindl Harald wrote:
>>
>>
>> Am 13.03.2018 um 22:59 schrieb Roger House:
>>> In all respects except one, the treatment of Unicode works just 
fine.

>>> I can write Unicode to database tables, read it, display it, etc.,
>>> with no problems. The exception is mysql, the MySQL Command-Line
>>> Tool. When I execute a SELECT statement to see rows in a table
>>> containing the Venus and Mars Unicode characters, here is what I see
>>> on the screen:
>>>
>>> || Venus | ♀ | | Mars | ♂ | |
>>>
>>> What I should see in the right column are the standard glyphs for
>>> Venus and Mars.
>>>
>>> Any ideas about how to get the MySQL Command-Line Tool to display
>>> Unicode properly?
>> what operating system
>> what terminal
>>
>> all recent Linux systems have UTF8 as default
>>
>
>

I am running Ubuntu MATE 16.04.  I have the problem also on Windows 7
and on Mac OS Version 10.11.6.  I do not think that the problem has to
do with the operating system nor the terminal.  Everything about the
Unicode text works fine in all tools such as editors, the cat command,
etc.  It is only when I am running mysql and I issue a SELECT 
command to

see what is in a row.  Then the UTF-8 is not rendered properly. I
believe the problem is with mysql.

Roger



If I presume that your terminal has a code page that is 
utf8-compatible (you say that cat command renders the multibyte 
characters just fine) then it could be your client-side mysql 
settings that are rendering those multibyte characters into 
individual glyphs based on their individual byte values.


The next time you are in mysql and have a chance to look at some utf8 
data, please collect and share these two reports:


status
SHOW GLOBAL VARIABLES like '%haracter%';

(you can obfuscate any sensitive details like server names or addresses)

Yours,

Here is the requested info:

mysql> status
--
mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine 
wrapper


Connection id:        5
Current database:    ephemeris
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    utf8mb4
Db characterset:    latin1
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:            6 hours 17 min 8 sec

Threads: 1  Questions: 28  Slow queries: 0  Opens: 122 Flush tables: 
1  Open tables: 41  Queries per second avg: 0.001

--

mysql> show global variables like '%haracter%';
+--++
| Variable_name    | Value  |
+--++
| character_set_client | utf8mb4    |
| character_set_connection | utf8mb4    |
| character_set_database   | utf8mb4    |
| character_set_filesystem | binary |
| character_set_results    | utf8mb4    |
| character_set_server | utf8mb4    |
| character_set_system | utf8   |
| character_sets_dir   | /usr/share/mysql/charsets/ |
+--++
8 rows in set (0.01 sec)



Update

I noticed that the status command shows

    Db characterset:    latin1

whereas all the other charactersets are utf8mb4.  So I looked around to see
how to change the Db characterset, and came up with this:

    ALTER DATABASE ephemeris CHARACTER SET utf8mb4 COLLATE 
utf8mb4_general_ci;


After which, status shows

    Db characterset:    utf8mb4

So the database characterset has now been changed.  But

    select * from planet_desc;

still shows

    Venus | ♀
    Mars  | ♂

I shut down the mysql service and restarted it, but this did not change
anything.

I'm beginning to think that the command line tool mysql does not pay any
attention to character sets when the SELECT command displays on the
terminal.

Roger


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

Re: How to get the MySQL Command-Line Tool to display Unicode properly?

[Roger House]

On 03/15/2018 11:30 AM, shawn l.green wrote:

Hi Roger,

(please note, this is a bottom-post forum)

On 3/13/2018 7:54 PM, Roger House wrote:
>
> On 03/13/2018 03:11 PM, Reindl Harald wrote:
>>
>>
>> Am 13.03.2018 um 22:59 schrieb Roger House:
>>> In all respects except one, the treatment of Unicode works just fine.
>>> I can write Unicode to database tables, read it, display it, etc.,
>>> with no problems. The exception is mysql, the MySQL Command-Line
>>> Tool. When I execute a SELECT statement to see rows in a table
>>> containing the Venus and Mars Unicode characters, here is what I see
>>> on the screen:
>>>
>>> || Venus | ♀ | | Mars | ♂ | |
>>>
>>> What I should see in the right column are the standard glyphs for
>>> Venus and Mars.
>>>
>>> Any ideas about how to get the MySQL Command-Line Tool to display
>>> Unicode properly?
>> what operating system
>> what terminal
>>
>> all recent Linux systems have UTF8 as default
>>
>
>

I am running Ubuntu MATE 16.04.  I have the problem also on Windows 7
and on Mac OS Version 10.11.6.  I do not think that the problem has to
do with the operating system nor the terminal.  Everything about the
Unicode text works fine in all tools such as editors, the cat command,
etc.  It is only when I am running mysql and I issue a SELECT command to
see what is in a row.  Then the UTF-8 is not rendered properly. I
believe the problem is with mysql.

Roger



If I presume that your terminal has a code page that is 
utf8-compatible (you say that cat command renders the multibyte 
characters just fine) then it could be your client-side mysql settings 
that are rendering those multibyte characters into individual glyphs 
based on their individual byte values.


The next time you are in mysql and have a chance to look at some utf8 
data, please collect and share these two reports:


status
SHOW GLOBAL VARIABLES like '%haracter%';

(you can obfuscate any sensitive details like server names or addresses)

Yours,

Here is the requested info:

mysql> status
--
mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper

Connection id:        5
Current database:    ephemeris
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        5.7.21-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    utf8mb4
Db characterset:    latin1
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:            6 hours 17 min 8 sec

Threads: 1  Questions: 28  Slow queries: 0  Opens: 122 Flush tables: 1  
Open tables: 41  Queries per second avg: 0.001

--

mysql> show global variables like '%haracter%';
+--++
| Variable_name    | Value  |
+--++
| character_set_client | utf8mb4    |
| character_set_connection | utf8mb4    |
| character_set_database   | utf8mb4    |
| character_set_filesystem | binary |
| character_set_results    | utf8mb4    |
| character_set_server | utf8mb4    |
| character_set_system | utf8   |
| character_sets_dir   | /usr/share/mysql/charsets/ |
+--++
8 rows in set (0.01 sec)


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

Re: How to get the MySQL Command-Line Tool to display Unicode properly?

[shawn l.green]

Hi Roger,

(please note, this is a bottom-post forum)

On 3/13/2018 7:54 PM, Roger House wrote:
>
> On 03/13/2018 03:11 PM, Reindl Harald wrote:
>>
>>
>> Am 13.03.2018 um 22:59 schrieb Roger House:
>>> In all respects except one, the treatment of Unicode works just fine.
>>> I can write Unicode to database tables, read it, display it, etc.,
>>> with no problems. The exception is mysql, the MySQL Command-Line
>>> Tool. When I execute a SELECT statement to see rows in a table
>>> containing the Venus and Mars Unicode characters, here is what I see
>>> on the screen:
>>>
>>> || Venus | ♀ | | Mars | ♂ | |
>>>
>>> What I should see in the right column are the standard glyphs for
>>> Venus and Mars.
>>>
>>> Any ideas about how to get the MySQL Command-Line Tool to display
>>> Unicode properly?
>> what operating system
>> what terminal
>>
>> all recent Linux systems have UTF8 as default
>>
>
>

I am running Ubuntu MATE 16.04.  I have the problem also on Windows 7
and on Mac OS Version 10.11.6.  I do not think that the problem has to
do with the operating system nor the terminal.  Everything about the
Unicode text works fine in all tools such as editors, the cat command,
etc.  It is only when I am running mysql and I issue a SELECT command to
see what is in a row.  Then the UTF-8 is not rendered properly.  I
believe the problem is with mysql.

Roger



If I presume that your terminal has a code page that is utf8-compatible 
(you say that cat command renders the multibyte characters just fine) 
then it could be your client-side mysql settings that are rendering 
those multibyte characters into individual glyphs based on their 
individual byte values.


The next time you are in mysql and have a chance to look at some utf8 
data, please collect and share these two reports:


status
SHOW GLOBAL VARIABLES like '%haracter%';

(you can obfuscate any sensitive details like server names or addresses)

Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services

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: How to get the MySQL Command-Line Tool to display Unicode properly?

[Roger House]

I am running Ubuntu MATE 16.04.  I have the problem also on Windows 7 
and on Mac OS Version 10.11.6.  I do not think that the problem has to 
do with the operating system nor the terminal.  Everything about the 
Unicode text works fine in all tools such as editors, the cat command, 
etc.  It is only when I am running mysql and I issue a SELECT command to 
see what is in a row.  Then the UTF-8 is not rendered properly.  I 
believe the problem is with mysql.


Roger


On 03/13/2018 03:11 PM, Reindl Harald wrote:



Am 13.03.2018 um 22:59 schrieb Roger House:
In all respects except one, the treatment of Unicode works just fine. 
I can write Unicode to database tables, read it, display it, etc., 
with no problems. The exception is mysql, the MySQL Command-Line 
Tool. When I execute a SELECT statement to see rows in a table 
containing the Venus and Mars Unicode characters, here is what I see 
on the screen:


|| Venus | ♀ | | Mars | ♂ | |

What I should see in the right column are the standard glyphs for 
Venus and Mars.


Any ideas about how to get the MySQL Command-Line Tool to display 
Unicode properly?

what operating system
what terminal

all recent Linux systems have UTF8 as default




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

Re: How to get the MySQL Command-Line Tool to display Unicode properly?

[Reindl Harald]

Am 13.03.2018 um 22:59 schrieb Roger House:
In all respects except one, the treatment of Unicode works just fine. I 
can write Unicode to database tables, read it, display it, etc., with no 
problems. The exception is mysql, the MySQL Command-Line Tool. When I 
execute a SELECT statement to see rows in a table containing the Venus 
and Mars Unicode characters, here is what I see on the screen:


|| Venus | ♀ | | Mars | ♂ | |

What I should see in the right column are the standard glyphs for Venus 
and Mars.


Any ideas about how to get the MySQL Command-Line Tool to display 
Unicode properly?

what operating system
what terminal

all recent Linux systems have UTF8 as default

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

Re: mysql_upgrade then mysqld. I feel very ID-10-T PEBKAC today

[Bob Eby]

Subject: Re: mysql_upgrade then mysqld. I feel very ID-10-T PEBKAC today
> 2018-02-23T14:02:33.962240Z 0 [ERROR] [MY-010735] Can't open the
mysql.plugin table. Please run mysql_upgrade to create it.

> How do I run mysql_upgrade if the mysqld server won't start?

Please don't bother to answer, I did figure it out, I'm sorry, for a bad
question and worse followup.  Apparently I'm a spammer as well.

Apparently the error was caused by something else besides plugins in
my configuration.  After carefully running --initialize from scratch
and modifying my.ini I was able to get mysqld.exe up and running just
fine on Windows 64 for MySQL 8.0 RC.  After that mysql_upgrade worked
a treat.  I did have a couple issues since it was my first time *NOT*
using --initialize_insecure in this configuration but it all works if
you do it right.  As per usual.

Sorry for the noise here,

Robert Eby

Re: Optimize fails due to duplicate rows error but no duplicates found

[shawn l.green]

(please do not top post - see my answer below)

On 2/13/2018 4:00 PM, Machiel Richards wrote:

ok, so we have managed to get an id out of the errors etc... however
when we look in the table that id does not even exist at all.


no idea what is going on here though.



*From:* shawn l.green <shawn.l.gr...@oracle.com>
*Sent:* 13 February 2018 09:51:33 PM
*To:* mysql@lists.mysql.com
*Subject:* Re: Optimize fails due to duplicate rows error but no
duplicates found
Hello Machiel,

On 2/13/2018 3:02 AM, Machiel Richards wrote:

Good day guys,


 I am hoping this mail finds you well.


I am at a bit of a loss here...


 We are trying to run optimize against a table in order to reclaim disk 
space from archived data which has been removed.


 However, after running for over an hour , the optimize fails stating there 
is a duplicate entry in the table.



  We have now spent 2 days using various methods but we are unable to find 
any duplicates in the primary key and also nothing on the unique key fields.


Any idea on why optimize would still be failing ?



Regards



Is it possible that the duplicate keys were the result of
re-partitioning your data where one of the "older" copies was in the
wrong partition as part of an upgrade from an earlier version?

See the entry in
https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-31.html that
start with...

Incompatible Change; Partitioning: Changes in the KEY partitioning hashing 
functions used with numeric, date and time, ENUM, and SET columns in MySQL 5.5 
makes tables using partitioning or subpartitioning by KEY on any of the 
affected column types and created  on a MySQL 5.5 or later server incompatible 
with a MySQL 5.1 server.

This is because the partition IDs as calculated by a MySQL 5.5 or later
server almost certainly differ from those calculated by a MySQL 5.1
server for the same table definition and data as a result of the changes
in these functions.

A normal indexed lookup against a partitioned table will use
(particularly for a PK value) "partition pruning" .  To see all of your
PK values regardless of which partition they are in, you need to scan
the table and avoid all indexes.

example:
# for a numeric PK column
CREATE TABLE myPK_list SELECT pk from sourcetable WHERE pk+0 > 0;

Then you can check the list in the generated table to find any duplicate
values.

Then you can modify a SELECT command to search each partition or
subpartition individually until you find the rows that are in the wrong
spots.
https://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.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



Then another thing to consider is that someone (during the lifetime of 
this table) changed the character set of your table (possibly changing 
it from using a case-sensitive collation to a case-insensitive 
collation) without actually converting the data on the table to use the 
new character set.


Is the key being duplicated numeric or character-based?

If numeric, is the value being reported as the duplicate at the high end 
of the permitted range of values for that column?


Regards,

--
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_upgrade then mysqld. I feel very ID-10-T PEBKAC today

[Reindl Harald]

Am 23.02.2018 um 15:15 schrieb Bob Eby:

2018-02-23T14:02:33.962240Z 0 [ERROR] [MY-010735] Can't open the
mysql.plugin table. Please run mysql_upgrade to create it.

How do I run mysql_upgrade if the mysqld server won't start?


post the *full* errorlog and watch for invaluid config stuff

most likely start mysqld without grant-tables (CAUTION: every user with 
and without password works for that time from everywhere!) would break 
the loop so that "mysql_upgrade" suceeds and after that *mmedidiatly* 
start the sevrer again with permission system enabled


https://stackoverflow.com/questions/1708409/how-to-start-mysql-with-skip-grant-tables

https://ma.ttias.be/mysql-table-mysql-plugin-doesnt-exist-after-mysql-upgrade/




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

Re: Optimize fails due to duplicate rows error but no duplicates found

[Machiel Richards]

ok, so we have managed to get an id out of the errors etc... however when we 
look in the table that id does not even exist at all.


no idea what is going on here though.



From: shawn l.green <shawn.l.gr...@oracle.com>
Sent: 13 February 2018 09:51:33 PM
To: mysql@lists.mysql.com
Subject: Re: Optimize fails due to duplicate rows error but no duplicates found

Hello Machiel,

On 2/13/2018 3:02 AM, Machiel Richards wrote:
> Good day guys,
>
>
>  I am hoping this mail finds you well.
>
>
> I am at a bit of a loss here...
>
>
>  We are trying to run optimize against a table in order to reclaim disk 
> space from archived data which has been removed.
>
>
>  However, after running for over an hour , the optimize fails stating 
> there is a duplicate entry in the table.
>
>
>
>   We have now spent 2 days using various methods but we are unable to 
> find any duplicates in the primary key and also nothing on the unique key 
> fields.
>
>
> Any idea on why optimize would still be failing ?
>
>
>
> Regards
>

Is it possible that the duplicate keys were the result of
re-partitioning your data where one of the "older" copies was in the
wrong partition as part of an upgrade from an earlier version?

See the entry in
https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-31.html that
start with...
> Incompatible Change; Partitioning: Changes in the KEY partitioning hashing 
> functions used with numeric, date and time, ENUM, and SET columns in MySQL 
> 5.5 makes tables using partitioning or subpartitioning by KEY on any of the 
> affected column types and created on a MySQL 5.5 or later server incompatible 
> with a MySQL 5.1 server. This is because the partition IDs as calculated by a 
> MySQL 5.5 or later server almost certainly differ from those calculated by a 
> MySQL 5.1 server for the same table definition and data as a result of the 
> changes in these functions.

A normal indexed lookup against a partitioned table will use
(particularly for a PK value) "partition pruning" .  To see all of your
PK values regardless of which partition they are in, you need to scan
the table and avoid all indexes.

example:
# for a numeric PK column
CREATE TABLE myPK_list SELECT pk from sourcetable WHERE pk+0 > 0;

Then you can check the list in the generated table to find any duplicate
values.

Then you can modify a SELECT command to search each partition or
subpartition individually until you find the rows that are in the wrong
spots.
https://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.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

Re: Optimize fails due to duplicate rows error but no duplicates found

[Machiel Richards]

Thank you for the response.


  There are no partitioning involved at all...


  I will try the suggestion though and see if we get anything...


Regards



From: shawn l.green <shawn.l.gr...@oracle.com>
Sent: 13 February 2018 09:51:33 PM
To: mysql@lists.mysql.com
Subject: Re: Optimize fails due to duplicate rows error but no duplicates found

Hello Machiel,

On 2/13/2018 3:02 AM, Machiel Richards wrote:
> Good day guys,
>
>
>  I am hoping this mail finds you well.
>
>
> I am at a bit of a loss here...
>
>
>  We are trying to run optimize against a table in order to reclaim disk 
> space from archived data which has been removed.
>
>
>  However, after running for over an hour , the optimize fails stating 
> there is a duplicate entry in the table.
>
>
>
>   We have now spent 2 days using various methods but we are unable to 
> find any duplicates in the primary key and also nothing on the unique key 
> fields.
>
>
> Any idea on why optimize would still be failing ?
>
>
>
> Regards
>

Is it possible that the duplicate keys were the result of
re-partitioning your data where one of the "older" copies was in the
wrong partition as part of an upgrade from an earlier version?

See the entry in
https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-31.html that
start with...
> Incompatible Change; Partitioning: Changes in the KEY partitioning hashing 
> functions used with numeric, date and time, ENUM, and SET columns in MySQL 
> 5.5 makes tables using partitioning or subpartitioning by KEY on any of the 
> affected column types and created on a MySQL 5.5 or later server incompatible 
> with a MySQL 5.1 server. This is because the partition IDs as calculated by a 
> MySQL 5.5 or later server almost certainly differ from those calculated by a 
> MySQL 5.1 server for the same table definition and data as a result of the 
> changes in these functions.

A normal indexed lookup against a partitioned table will use
(particularly for a PK value) "partition pruning" .  To see all of your
PK values regardless of which partition they are in, you need to scan
the table and avoid all indexes.

example:
# for a numeric PK column
CREATE TABLE myPK_list SELECT pk from sourcetable WHERE pk+0 > 0;

Then you can check the list in the generated table to find any duplicate
values.

Then you can modify a SELECT command to search each partition or
subpartition individually until you find the rows that are in the wrong
spots.
https://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.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

Re: Optimize fails due to duplicate rows error but no duplicates found

[shawn l.green]

Hello Machiel,

On 2/13/2018 3:02 AM, Machiel Richards wrote:

Good day guys,


 I am hoping this mail finds you well.


I am at a bit of a loss here...


 We are trying to run optimize against a table in order to reclaim disk 
space from archived data which has been removed.


 However, after running for over an hour , the optimize fails stating there 
is a duplicate entry in the table.



  We have now spent 2 days using various methods but we are unable to find 
any duplicates in the primary key and also nothing on the unique key fields.


Any idea on why optimize would still be failing ?



Regards



Is it possible that the duplicate keys were the result of 
re-partitioning your data where one of the "older" copies was in the 
wrong partition as part of an upgrade from an earlier version?


See the entry in 
https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-31.html that 
start with...

Incompatible Change; Partitioning: Changes in the KEY partitioning hashing 
functions used with numeric, date and time, ENUM, and SET columns in MySQL 5.5 
makes tables using partitioning or subpartitioning by KEY on any of the 
affected column types and created on a MySQL 5.5 or later server incompatible 
with a MySQL 5.1 server. This is because the partition IDs as calculated by a 
MySQL 5.5 or later server almost certainly differ from those calculated by a 
MySQL 5.1 server for the same table definition and data as a result of the 
changes in these functions.


A normal indexed lookup against a partitioned table will use 
(particularly for a PK value) "partition pruning" .  To see all of your 
PK values regardless of which partition they are in, you need to scan 
the table and avoid all indexes.


example:
# for a numeric PK column
CREATE TABLE myPK_list SELECT pk from sourcetable WHERE pk+0 > 0;

Then you can check the list in the generated table to find any duplicate 
values.


Then you can modify a SELECT command to search each partition or 
subpartition individually until you find the rows that are in the wrong 
spots.

https://dev.mysql.com/doc/refman/5.7/en/partitioning-selection.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

Re: Examples of savepoints and transactions

[shawn l.green]

Hello Lars,

On 1/24/2018 8:50 AM, Johan De Meersman wrote:

What you're looking for is simple backup and restore :-)

Savepoints are, simply put, markers within a transaction; allowing you to 
rollback only part of a transaction instead of the whole thing. A commit will 
inevitably commit the ENTIRE transactions, and thus remove the savepoints.

A typical workflow for the kind of thing you're trying to do is to have your (automated) 
testing framework restore last night's backup after the test run. You could also make a 
backup before the test run and restore that afterwards; have an automated nightly db copy 
from prod to dev; or in very specific cases you could simply have your test system revert 
the data by issuing the "reverse" queries - although that one is rarely an 
option in real life.

Another alternative would be to take a filesystem (or virtual machine) 
snapshot, and revert to that after the tests. Filesystem snapshots will require 
your database to be stopped and started, though.

/Johan


- Original Message -

From: "Lars Nielsen" <l...@lfweb.dk>
To: "MySql" <mysql@lists.mysql.com>
Sent: Tuesday, 23 January, 2018 23:19:29
Subject: Re: Examples of savepoints and transactions



Den 22-01-2018 kl. 22:01 skrev shawn l.green:

Hello Lars,

On 1/21/2018 3:37 PM, Lars Nielsen wrote:

Hi,
I have a system that uses begin and commit transactions. It works
like a dream! ;)
Now I want to test it by creating test data. This how ever cannot be
rolled back. I think the solution for rolling back test data is to
use savepoints and rollback. I think it is hard to find examples of
this scenario. Are there some good guides or tutorials out there
somewhere? Any suggestions are welcome.

Best regards
Lars Nielsen



Can you mock up an example (a simple text walkthrough) of how you
think a savepoint should work with what you are calling "test data" ?
I think that the term "test data" is too general to make much sense to
most of us in the context you described.


Yours,

Hello Shawn,
Thanks for your interest. Here is an example of my idea.

I have a php site working through PDO connections. I insert some data
through php like this :

|START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x,
y, z); COMMIT; |||

||Now I want to do automated tests that create "dummy" data that i want
to remove after the test has finished:
like this :

|SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM
table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT
INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL
OPERATIONS ROLLBACK TO autotest1; |||

||All done. I have tested the application and have cleaned up the dummy
test-data.

The issue is that when I call the first commit then the savepoint is
deleted.

Is this possible at all?

Regards Lars

||






Is table2 what you want to return to its earlier state?

Other techniques to do what Johan suggested include:
* Make a copy of your "base" data for each test run. That way you don't 
change your starting point. When that test run is over, drop the copy. 
This way your "data to be tested" exists (or ceases to exist) outside 
the boundaries of the transactions you are creating to test/change that 
data but the original state of that data persists somewhere else.


* Use a non-transactional storage engine for table3 (being 
non-transactional means that the changes you store there will not be 
affected by a ROLLBACK or COMMIT. They become "permanent" the moment you 
do them).


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

Re: Examples of savepoints and transactions

[Lars Nielsen]

Kind Regards / Med venlig hilsen
Lars Nielsen
> - Original Message -
>> From: "Lars Nielsen" <l...@lfweb.dk>
>> To: "MySql" <mysql@lists.mysql.com>
>> Sent: Tuesday, 23 January, 2018 23:19:29
>> Subject: Re: Examples of savepoints and transactions
> 
>>> Den 22-01-2018 kl. 22:01 skrev shawn l.green:
>>> Hello Lars,
>>> 
>>>> On 1/21/2018 3:37 PM, Lars Nielsen wrote:
>>>> Hi,
>>>> I have a system that uses begin and commit transactions. It works
>>>> like a dream! ;)
>>>> Now I want to test it by creating test data. This how ever cannot be
>>>> rolled back. I think the solution for rolling back test data is to
>>>> use savepoints and rollback. I think it is hard to find examples of
>>>> this scenario. Are there some good guides or tutorials out there
>>>> somewhere? Any suggestions are welcome.
>>>> 
>>>> Best regards
>>>> Lars Nielsen
>>>> 
>>> 
>>> Can you mock up an example (a simple text walkthrough) of how you
>>> think a savepoint should work with what you are calling "test data" ?
>>> I think that the term "test data" is too general to make much sense to
>>> most of us in the context you described.
>>> 
>>> 
>>> Yours,
>> Hello Shawn,
>> Thanks for your interest. Here is an example of my idea.
>> 
>> I have a php site working through PDO connections. I insert some data
>> through php like this :
>> 
>> |START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
>> UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x,
>> y, z); COMMIT; |||
>> 
>> ||Now I want to do automated tests that create "dummy" data that i want
>> to remove after the test has finished:
>> like this :
>> 
>> |SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM
>> table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT
>> INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL
>> OPERATIONS ROLLBACK TO autotest1; |||
>> 
>> ||All done. I have tested the application and have cleaned up the dummy
>> test-data.
>> 
>> The issue is that when I call the first commit then the savepoint is
>> deleted.
>> 
>> Is this possible at all?
>> 
>> Regards Lars
>> 
>> ||
>> 
> 
> -- 
> The bay-trees in our country are all wither'd
> And meteors fright the fixed stars of heaven;
> The pale-faced moon looks bloody on the earth
> And lean-look'd prophets whisper fearful change.
> These signs forerun the death or fall of kings.
>  -- Wm. Shakespeare, "Richard II"

> Den 24. jan. 2018 kl. 14.50 skrev Johan De Meersman <vegiv...@tuxera.be>:
> 
> What you're looking for is simple backup and restore :-)
> 
> Savepoints are, simply put, markers within a transaction; allowing you to 
> rollback only part of a transaction instead of the whole thing. A commit will 
> inevitably commit the ENTIRE transactions, and thus remove the savepoints.
> 
> A typical workflow for the kind of thing you're trying to do is to have your 
> (automated) testing framework restore last night's backup after the test run. 
> You could also make a backup before the test run and restore that afterwards; 
> have an automated nightly db copy from prod to dev; or in very specific cases 
> you could simply have your test system revert the data by issuing the 
> "reverse" queries - although that one is rarely an option in real life.
> 
> Another alternative would be to take a filesystem (or virtual machine) 
> snapshot, and revert to that after the tests. Filesystem snapshots will 
> require your database to be stopped and started, though.
> 
> /Johan
> 

Thanks Johan,
I understood the savepoints could be around transactions and not within! 
I know how to restore from a backup. I just wanted to avoid loading 500+GB 
after each test-run. :)
Thanks for your help everyone.
/Lars

Re: Examples of savepoints and transactions

[Johan De Meersman]

What you're looking for is simple backup and restore :-)

Savepoints are, simply put, markers within a transaction; allowing you to 
rollback only part of a transaction instead of the whole thing. A commit will 
inevitably commit the ENTIRE transactions, and thus remove the savepoints.

A typical workflow for the kind of thing you're trying to do is to have your 
(automated) testing framework restore last night's backup after the test run. 
You could also make a backup before the test run and restore that afterwards; 
have an automated nightly db copy from prod to dev; or in very specific cases 
you could simply have your test system revert the data by issuing the "reverse" 
queries - although that one is rarely an option in real life.

Another alternative would be to take a filesystem (or virtual machine) 
snapshot, and revert to that after the tests. Filesystem snapshots will require 
your database to be stopped and started, though.

/Johan


- Original Message -
> From: "Lars Nielsen" <l...@lfweb.dk>
> To: "MySql" <mysql@lists.mysql.com>
> Sent: Tuesday, 23 January, 2018 23:19:29
> Subject: Re: Examples of savepoints and transactions

> Den 22-01-2018 kl. 22:01 skrev shawn l.green:
>> Hello Lars,
>>
>> On 1/21/2018 3:37 PM, Lars Nielsen wrote:
>>> Hi,
>>> I have a system that uses begin and commit transactions. It works
>>> like a dream! ;)
>>> Now I want to test it by creating test data. This how ever cannot be
>>> rolled back. I think the solution for rolling back test data is to
>>> use savepoints and rollback. I think it is hard to find examples of
>>> this scenario. Are there some good guides or tutorials out there
>>> somewhere? Any suggestions are welcome.
>>>
>>> Best regards
>>> Lars Nielsen
>>>
>>
>> Can you mock up an example (a simple text walkthrough) of how you
>> think a savepoint should work with what you are calling "test data" ?
>> I think that the term "test data" is too general to make much sense to
>> most of us in the context you described.
>>
>>
>> Yours,
> Hello Shawn,
> Thanks for your interest. Here is an example of my idea.
> 
> I have a php site working through PDO connections. I insert some data
> through php like this :
> 
>|START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
> UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x,
> y, z); COMMIT; |||
> 
>||Now I want to do automated tests that create "dummy" data that i want
> to remove after the test has finished:
> like this :
> 
>|SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM
> table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT
> INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL
> OPERATIONS ROLLBACK TO autotest1; |||
> 
>||All done. I have tested the application and have cleaned up the dummy
> test-data.
> 
> The issue is that when I call the first commit then the savepoint is
> deleted.
> 
> Is this possible at all?
> 
> Regards Lars
> 
>||
> 

-- 
The bay-trees in our country are all wither'd
And meteors fright the fixed stars of heaven;
The pale-faced moon looks bloody on the earth
And lean-look'd prophets whisper fearful change.
These signs forerun the death or fall of kings.
  -- Wm. Shakespeare, "Richard II"

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

Re: Examples of savepoints and transactions

[Lars Nielsen]

Den 22-01-2018 kl. 22:01 skrev shawn l.green:

Hello Lars,

On 1/21/2018 3:37 PM, Lars Nielsen wrote:

Hi,
I have a system that uses begin and commit transactions. It works 
like a dream! ;)
Now I want to test it by creating test data. This how ever cannot be 
rolled back. I think the solution for rolling back test data is to 
use savepoints and rollback. I think it is hard to find examples of 
this scenario. Are there some good guides or tutorials out there 
somewhere? Any suggestions are welcome.


Best regards
Lars Nielsen



Can you mock up an example (a simple text walkthrough) of how you 
think a savepoint should work with what you are calling "test data" ?  
I think that the term "test data" is too general to make much sense to 
most of us in the context you described.



Yours,

Hello Shawn,
Thanks for your interest. Here is an example of my idea.

I have a php site working through PDO connections. I insert some data 
through php like this :


|START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; 
UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, 
y, z); COMMIT; |||


||Now I want to do automated tests that create "dummy" data that i want 
to remove after the test has finished:

like this :

|SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM 
table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT 
INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL 
OPERATIONS ROLLBACK TO autotest1; |||


||All done. I have tested the application and have cleaned up the dummy 
test-data.


The issue is that when I call the first commit then the savepoint is 
deleted.


Is this possible at all?

Regards Lars

||

Re: Examples of savepoints and transactions

[shawn l.green]

Hello Lars,

On 1/21/2018 3:37 PM, Lars Nielsen wrote:

Hi,
I have a system that uses begin and commit transactions. It works like a dream! 
;)
Now I want to test it by creating test data. This how ever cannot be rolled 
back. I think the solution for rolling back test data is to use savepoints and 
rollback. I think it is hard to find examples of this scenario. Are there some 
good guides or tutorials out there somewhere? Any suggestions are welcome.

Best regards
Lars Nielsen



Can you mock up an example (a simple text walkthrough) of how you think 
a savepoint should work with what you are calling "test data" ?  I think 
that the term "test data" is too general to make much sense to most of 
us in the context you described.



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

Re: Can't get my query to return wanted data

[Chris Roy-Smith]

On Fri, 19 Jan 2018 11:25:42 -0500, shawn l.green wrote:

> Hello Chris,
> 
> On 1/19/2018 12:50 AM, Chris Roy-Smith wrote:
>> Hi I am running mysql 5.7.20 in ubuntu linux 17.10
>>
>> I have 2 tables, member and status with contents like
>>
>> member:
>> ident,   given,  surname 1   fredjones 2 johnhoward 
3   henry   wales 4
>> jennybrown
>>
>> status:
>> identyear 1  2017 2  2017 3  2017 4  2017 1  2018 3  2018
>>
>> I want my query to return the name and ident from the member table for
>> all members that has not got an entry in status with year=2018
>>
>> I have been working on the following query to achieve this, but it only
>> returns data when there is no `year` entries for a selected year.
>>
>> select details.ident, given, surname from details left join status on
>> details.ident = status.ident where NOT EXISTS (select year from status
>> where (status.year = 2018) and (details.ident = status.ident) )
>>
>>
>> Thank you for looking at this.
>> regards, Chris Roy-Smith
>>
>>
>>
> try this...
> 
> SELECT
>d.ident, d.given, d.surname
> FROM details d LEFT JOIN (
>SELECT DISTINCT ident FROM status WHERE year=2018
> ) s
>ON s.ident = d.ident
> WHERE
>s.ident is NULL;
> 
> How it works
> #
> Start by building a list of unique `ident` values that match the
> condition you do NOT want to find. (you will see why in a moment)
> 
> LEFT JOIN that list to your list of members (with your list on the right
> side of the LEFT JOIN).  Where that join's ON condition is satisfied, a
> value for the column s.ident will exist. Where it isn't satisfied, there
> will be a NULL value in s.ident.
> 
> Finally, filter the combination of the s and d tables (I'm referring to
> their aliases) to find all the rows where s.ident was not given a value
> because it did not satisfy the ON condition of your outer join.
> #
> 
> 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.

Hi Shawn,
That works a treat! Thank you for a solution with an explanation, which I 
appreciate, because it helps me learn.
Regards, Chris Roy-Smith


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

Re: Can't get my query to return wanted data

[shawn l.green]

Hello Chris,

On 1/19/2018 12:50 AM, Chris Roy-Smith wrote:

Hi
I am running mysql 5.7.20 in ubuntu linux 17.10

I have 2 tables, member and status with contents like

member:
ident,  given,  surname
1   fredjones
2   johnhoward
3   henry   wales
4   jenny   brown

status:
ident   year
1   2017
2   2017
3   2017
4   2017
1   2018
3   2018

I want my query to return the name and ident from the member table for all
members that has not got an entry in status with year=2018

I have been working on the following query to achieve this, but it only
returns data when there is no `year` entries for a selected year.

select details.ident, given, surname from details left join status on
details.ident = status.ident where NOT EXISTS (select year from status
where (status.year = 2018) and (details.ident = status.ident) )


Thank you for looking at this.
regards, Chris Roy-Smith




try this...

SELECT
  d.ident, d.given, d.surname
FROM details d
LEFT JOIN (
  SELECT DISTINCT ident
  FROM status
  WHERE year=2018
) s
  ON s.ident = d.ident
WHERE
  s.ident is NULL;

How it works
#
Start by building a list of unique `ident` values that match the 
condition you do NOT want to find. (you will see why in a moment)


LEFT JOIN that list to your list of members (with your list on the right 
side of the LEFT JOIN).  Where that join's ON condition is satisfied, a 
value for the column s.ident will exist. Where it isn't satisfied, there 
will be a NULL value in s.ident.


Finally, filter the combination of the s and d tables (I'm referring to 
their aliases) to find all the rows where s.ident was not given a value 
because it did not satisfy the ON condition of your outer join.

#

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

Re: monitoring mysql performance

[Reindl Harald]

Am 02.11.2017 um 20:09 schrieb Miguel González:

  I have a VPS Web server (Apache 2.4/PHP 7.x + Varnish 4.1) running with
MySQL 5.6. I have 20 Gb of RAM. I serve Wordpress sites mainly all using
innodb.

  So I´m thinking of ways of improving MySQL performance and of course
for that, you need to measure.

  Currently I´m checking, there are no slow queries and read and writes
perform well. I have enabled slow queries logging but the truth is that
most queries logged are not slow, they are just slow when the web server
load is too high, so when you try to check those queries at other time,
there isn´t anything wrong with them


so what evidence do you have that it's the database server at all?

mysqltuner would be a good start for config hints in that case

but mostly the webserver load is high because wordpress, joomla and all 
that crap is terrible inefficient even with PHP7 and opcache enabled and 
so you need caching in the application - not query results, whole 
content parts like navigation and so on - parts of the page which don#t 
change every time and are shared between different pages


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

Re: How to get the MySQL Command-Line Tool to display Unicode properly

[Hal.sz S.ndor]

2017/10/18 18:32 ... Roger House:

I get the same behavior with the MySQL Command Line Tool when I run it on
Windows, Mac OS X, and Ubuntu, so I'm pretty sure the problem has to do 
with mysql itself.


What do you know about the displays to which the client is writing?
In the case of "cmd" under Windows, see what others had to do for other 
programs:

https://stackoverflow.com/questions/388490/unicode-characters-in-windows-command-line-how

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

Re: Last established connection timestamp by a specific user

[Singer Wang]

Unfortunately not with the standard configuration.

You're best bet going forward would be to look at
MySQL Enterprise Audit -
https://www.mysql.com/products/enterprise/audit.html


On Tue, Oct 10, 2017 at 3:54 PM, Gone, Sajan  wrote:

> Hi,
>
>We have a MySQL instance which is currently running on version
> `5.7.11-enterprise-commercial-advanced-log`.  On this instance I am
> trying to figure out the most recent timestamp at which a specific user has
> established a connection to this instance (or) performed any DML operations
> which might have changed the status of the database.
>
> Is there any way I can get such information from the 
> information_schema/performance_schema
> tables (or) from any of the mysql logs?
>
> Thank You,
> Sajan Gone
> Database Administrator.
>
> 
>
> Notice: This communication may contain privileged and/or confidential
> information. If you are not the intended recipient, please notify the
> sender by email, and immediately delete the message and any attachments
> without copying or disclosing them. LB may, for any reason, intercept,
> access, use, and disclose any information that is communicated by or
> through, or which is stored on, its networks, applications, services, and
> devices.
>

Re: Question about contributing a patch

[shawn l.green]

On 10/9/2017 3:27 AM, Xiaoyu Wang wrote:

Hello,I reported a bug, at https://bugs.mysql.com/bug.php?id=87637, as well as 
a patch. And Bogdan, the bug hunter, told me this patch would show up on the 
dev contribution report. So, could anyone please tell me how to contact dev 
team, or how can I know the progress about integrating the patch. By the way, I 
signed Oracle Contributor Agreement.
Any reply would be a great help.
Thanks, sincerely
Xiaoyu



Hello Xiaoyu,

Your interaction with the developers will happen through your bug report 
just as it did with our bug report handling team. If they need any 
details or if they need to engage with you again that is where they will 
contact you.


As to the integration of your fix into our code... that gets more 
complicated. There may be edge cases or use cases that need us to modify 
your code to handle. Sometimes these are found as the developer applies 
your patch to our code, sometimes with post-build unit testing, 
sometimes only after full integration testing.


And when that work may start depends on when a developer is scheduled to 
work on the specific bug you designed the patch for. So it could be a 
while.


Thank you very much for helping MySQL to become a better product!

Regards,
--
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 Community Server 8.0.3-rc has been released (part 1/2)

[Sebastien FLAESCH]

Can you please stop using C++ comments in header files designed for C 
programming?

/opt3/dbs/mys/8.0/include/mysql.h:36:1: error: C++ style comments are not 
allowed in ISO C90 [-Werror]
 // Small extra definitions to avoid pulling in my_inttypes.h in client code.
 ^
/opt3/dbs/mys/8.0/include/mysql.h:36:1: error: (this will be reported only once 
per input file) [-Werror]
In file included from /opt3/dbs/mys/8.0/include/mysql.h:68:0,
 from mys.c:42:
/opt3/dbs/mys/8.0/include/binary_log_types.h:68:8: error: C++ style comments 
are not allowed in ISO C90 [-Werror]
 #endif // __cplusplus
^
/opt3/dbs/mys/8.0/include/binary_log_types.h:68:8: error: (this will be 
reported only once per input file) [-Werror]
In file included from /opt3/dbs/mys/8.0/include/mysql.h:69:0,
 from mys.c:42:
/opt3/dbs/mys/8.0/include/mem_root_fwd.h:30:9: error: C++ style comments are 
not allowed in ISO C90 [-Werror]
 #endif  // MEM_ROOT_FWD_INCLUDED
 ^
/opt3/dbs/mys/8.0/include/mem_root_fwd.h:30:9: error: (this will be reported 
only once per input file) [-Werror]
In file included from /opt3/dbs/mys/8.0/include/mysql.h:83:0,
 from mys.c:42:
/opt3/dbs/mys/8.0/include/mysql_time.h:29:1: error: C++ style comments are not 
allowed in ISO C90 [-Werror]
 // Do not not pull in the server header "my_inttypes.h" from client code.
 ^

...

Seb


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

Re: Omit another where clause from original query

[Hal.sz S.ndor]

2017/09/19 17:19 ... Don Wieland:

Of these found rows, I want to omit those rows where there are rows found after 
the END TimeStamp based on ?below ?where clause:

  WHERE 1 AND apt.appt_status_id IN (16) AND apt.user_id IN (3) AND apt.time_start 
 > ‘1504238399'

We are trying to find Former Clients who have not been seen after the date 
range - Lapsed Client Report


What are you getting that you want not to see? Certainly if you simply 
left off the upper half of the 'BETWEEN' you would get all those that 
you now get and all that have been left out by being dated over 
‘1504238399'.


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

Re: innodb_read_only issues

[Josh Paetzel]

On Thu, Sep 7, 2017, at 02:04 PM, shawn l.green wrote:
> Hello Josh,
> 
> 
> What appears to be missing on that page is a discussion what to do with 
> the "temporary tablespace" used to hold intrinsic (internal) temporary 
> tables created by different types of queries you could execute.
> 
> https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html
> 
> https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html
> 
> If you point the setting --innodb-temp-data-file-path to a location that 
> is writeable (and accessible to the user that your mysqld is running 
> as), does that get you past this problem?
> 
> https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_temp_data_file_path
> 
> Think of it as allocating "scratch space" for this mysqld to "think" 
> while it processes your queries against the data.
> 
> 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
> 


Shawn,

That was exactly the problem.  Thanks for the reply.

https://bugs.mysql.com/?id=87697

-- 

Thanks,

Josh Paetzel

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

Re: innodb_read_only issues

[shawn l.green]

Hello Josh,

On 9/6/2017 11:01 PM, Josh Paetzel wrote:

I've followed the instructions at
https://dev.mysql.com/doc/refman/5.7/en/innodb-read-only-instance.html

Which starts with:

14.6.2 Configuring InnoDB for Read-Only Operation

You can now query InnoDB tables where the MySQL data directory is on
read-only media, by enabling the --innodb-read-only configuration option
at server startup.

Exactly what I want to do.

However the server bails out trying to create files.

2017-09-07T02:12:33.688368Z 0 [Note] InnoDB: Started in read only mode
2017-09-07T02:12:33.688405Z 0 [Note] InnoDB: Mutexes and rw_locks use
GCC atomic builtins
2017-09-07T02:12:33.688410Z 0 [Note] InnoDB: Uses event mutexes
2017-09-07T02:12:33.688415Z 0 [Note] InnoDB: GCC builtin
__atomic_thread_fence() is used for memory barrier
2017-09-07T02:12:33.688419Z 0 [Note] InnoDB: Compressed tables use zlib
1.2.11
2017-09-07T02:12:33.688695Z 0 [Note] InnoDB: Number of pools: 1
2017-09-07T02:12:33.688803Z 0 [Note] InnoDB: Using CPU crc32
instructions
2017-09-07T02:12:33.688810Z 0 [Note] InnoDB: Disabling background log
and ibuf IO write threads.
2017-09-07T02:12:33.690040Z 0 [Note] InnoDB: Initializing buffer pool,
total size = 1G, instances = 8, chunk size = 128M
2017-09-07T02:12:33.809821Z 0 [Note] InnoDB: Completed initialization of
buffer pool
2017-09-07T02:12:33.836689Z 0 [Note] InnoDB: Highest supported file
format is Barracuda.
2017-09-07T02:12:33.852709Z 0 [Note] InnoDB: Creating shared tablespace
for temporary tables
2017-09-07T02:12:33.852757Z 0 [ERROR] InnoDB: Operating system error
number 13 in a file operation.
2017-09-07T02:12:33.852764Z 0 [ERROR] InnoDB: The error means mysqld
does not have the access rights to the directory.
2017-09-07T02:12:33.852769Z 0 [ERROR] InnoDB: Operating system error
number 13 in a file operation.
2017-09-07T02:12:33.852774Z 0 [ERROR] InnoDB: The error means mysqld
does not have the access rights to the directory.
2017-09-07T02:12:33.852779Z 0 [ERROR] InnoDB: Cannot open datafile
'/var/db/mysql/ibtmp1'
2017-09-07T02:12:33.852784Z 0 [ERROR] InnoDB: Unable to create the
shared innodb_temporary
2017-09-07T02:12:33.852789Z 0 [ERROR] InnoDB: Plugin initialization
aborted with error Cannot open a file
2017-09-07T02:12:34.067298Z 0 [ERROR] Plugin 'InnoDB' init function
returned error.
2017-09-07T02:12:34.067315Z 0 [ERROR] Plugin 'InnoDB' registration as a
STORAGE ENGINE failed.
2017-09-07T02:12:34.067322Z 0 [ERROR] Failed to initialize plugins.
2017-09-07T02:12:34.067327Z 0 [ERROR] Aborting

For what it's worth /var/db/mysql is chmod 550, chown mysql:mysql, so
the mysql user has read access to the directory but can't create files
in it.

I've examined the source and found:

 /* Open temp-tablespace and keep it open until shutdown. */

 err = srv_open_tmp_tablespace(create_new_db, _tmp_space);

 if (err != DB_SUCCESS) {
 return(srv_init_abort(err));
 }

in storage/innobase/srv/srv0start.cc which is not wrapped with a if
(!srv_read_only_mode)

Is this a bug I am hitting or am I holding it wrong?



What appears to be missing on that page is a discussion what to do with 
the "temporary tablespace" used to hold intrinsic (internal) temporary 
tables created by different types of queries you could execute.


https://dev.mysql.com/doc/refman/5.7/en/innodb-temporary-tablespace.html

https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html

If you point the setting --innodb-temp-data-file-path to a location that 
is writeable (and accessible to the user that your mysqld is running 
as), does that get you past this problem?


https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_temp_data_file_path

Think of it as allocating "scratch space" for this mysqld to "think" 
while it processes your queries against the data.


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

Re: MySQL Cluster Manager 1.4.3 has been released

[daniel so]

   for their communication. (Bug #26084090)

 * Agent: The dropping or recreating of a node group that
   took place when adding data nodes could sometimes fail
   with an assertion error ("Polled nodegroup info is
   inconsistent"). This fix relaxes the assertion, which
   allows the node group reconfiguration to be completed.
   (Bug #26051753)
   References: See also: Bug #20104357.

 * Agent: During an execution of a set command, if no mysqld
   node is available for querying cluster information, an
   mcmd agent timed out while waiting for the "prepared"
   message from another agent, even after the message was
   already sent. This was due to the fact that the two
   agents had inconsistent plans of execution for the set
   command. This fix prevents the inconsistency. (Bug
   #26021616)
   References: This issue is a regression of: Bug #14230789,
   Bug #23148061.

 * Agent: A backup cluster --waitcompleted timed out
   sometimes right before the backup was completed when
   there were a lot of tables to be backed up. This was
   because the logical backup for the tables' metadata was
   taking too long in that case. With this fix, the mcmd
   agent is now sent progress reports of the logical backup,
   and the backup does not time out unless no more progress
   reports are received. (Bug #26000482)

 * Agent: When a set command involved a restart of data
   nodes of a cluster but one of the data nodes had been
   stopped, the set command failed with a timeout. With this
   fix, the set command is carried out successfully with a
   rolling restart for the data nodes. (Bug #25869325)

 * Agent: If a mysqld node was configured with the
   --skip-name-resolve option, attempts for mcmd to connect
   to the mysqld node would fail with the error message Host
   '127.0.0.1' is not allowed to connect to this MySQL
   server. This was because the MySQL account used by mcmd
   had 127.0.0.1 as its host name, which is not allowed when
   the --skip-name-resolve option is used with the mysqld
   node. This fix corrects the account host name to
   localhost. (Bug #25831764, Bug #85620)

 * Agent: When a host and its mcmd agent were restarted,
   mcmd might fail to restart a management or mysqld node on
   the host, and the show status command continuously
   returned the status of the node to be unknown. (Bug
   #25822822)

 * Agent: When an mcmd agent was in the process of shutting
   down, a user command issued then might cause the agent to
   quit unexpectedly. With this fix, an error message "Agent
   is shutting down" is returned, and the agent continues
   with its shutdown. (Bug #25055338)

 * Agent: When a set command involved a restart of data
   nodes of a cluster but one of the data nodes was in the
   failed state, mcmd restarted the data node, and then
   restarted it once more as part of a rolling restart,
   which was unnecessary. This fix eliminates the second
   restart. (Bug #23586651)

 * Client: After an agent was started and a few commands had
   been executed from the mcm client, the show settings
   command started returning the wrong value for the
   --log-level option. (Bug #26189795)

 * Client: Trying to set the mysqld node option
   --validate_password resulted in an error complaining that
   the parameter did not exist, even if the Password
   Validation Plugin
   (http://dev.mysql.com/doc/refman/5.6/en/validate-password 
<http://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html>
-plugin.html 
<http://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html>) 
had already been installed on the mysqld

   node. It was due to some errors with the plug-in
   activation for mysqld nodes, which have now been
   corrected. (Bug #25797125)

On behalf of the Oracle MySQL RE Team

-Sreedhar S



--
Daniel So
MySQL Documentation Team
Oracle Canada ULC / Markham, ON
MySQL: www.mysql.com
Oracle: www.oracle.com

Fwd: Re: Something strange here...

[shawn l.green]

Hello List,

So sorry about the bad click. I meant to "Reply to list" but instead 
just replied to the original poster.


This is the exact same advice that hsv@  just provided. If I had paid 
attention I could have saved him the duplication of efforts. My 
apologies to him and everyone else.


Humbly embarrassed,
Shawn


 Forwarded Message 
Subject: Re: Something strange here...
Date: Wed, 14 Jun 2017 14:04:02 -0400
From: shawn l.green <shawn.l.gr...@oracle.com>
Organization: Oracle Corporation
To: Chris Knipe <sav...@savage.za.org>

Hello Chris,

On 6/13/2017 5:42 PM, Chris Knipe wrote:

Hi all,

Can someone explain to me why this is happening please:

mysql> SELECT * FROM CustomerDetails WHERE Username=’blah’\G
*** 1. row ***
  EntryID: F83D4109-DCA8-426F-98B1-00B4AF117ACB
 Username: blah
AccountVolume: 0
1 row in set (0.00 sec)

mysql> UPDATE CustomerDetails SET 
AccountVolume=GREATEST(CAST(AccountVolume-2865 AS SIGNED), CAST(0 AS SIGNED)) 
WHERE Username='blah';
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in 
'(`test`.`CustomerDetails`.`AccountVolume` - 2865)'

Using GREATEST, shouldn’t it be irrelevant whether AccountVolume-INT is signed 
or unsigned?

How would I go about doing this?  I have played quite a bit with CAST here, but 
I am not having much luck.

Thnx,
Chris.



If I read between the lines, I think you created AccountVolume as a
"BIGINT UNSIGNED" column. Right?

When you subtract something from a 0 BIGINT UNSIGNED column, you are
attempting to make a negative BIGINT UNSIGNED value (which is illegal)

Have you tried casting the column to SIGNED before the subtraction.
Instead of this...
CAST(AccountVolume-2865 AS SIGNED)

Try this
(CAST(AccountVolume AS SIGNED) - 2865)


That should get through the first part of the problem. But you still
need to re-cast the result of the GREATEST function back to an UNSIGNED
value so that it matches the type of the left side of the assignment
operator


(showing any earlier attempts to fix the problem when engaging outside
resources for help can save guessing time)


Another way to avoid this problem is to use something like an IF()
function to avoid going out of range

AccountVolume = IF(AccountVolume > 2865, AccountVolume-2865, 0)



--
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: Something strange here...

[Hal.sz S.ndor]

2017/06/13 17:42 ... Chris Knipe:

Can someone explain to me why this is happening please:

mysql> SELECT * FROM CustomerDetails WHERE Username=’blah’\G
*** 1. row ***
  EntryID: F83D4109-DCA8-426F-98B1-00B4AF117ACB
 Username: blah
AccountVolume: 0
1 row in set (0.00 sec)

mysql> UPDATE CustomerDetails SET 
AccountVolume=GREATEST(CAST(AccountVolume-2865 AS SIGNED), CAST(0 AS SIGNED)) 
WHERE Username='blah';
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in 
'(`test`.`CustomerDetails`.`AccountVolume` - 2865)'

Using GREATEST, shouldn’t it be irrelevant whether AccountVolume-INT is 
signed or unsigned?


The expression you need is something like this,
... = IF(AccountVolume < 2865, 0, AccountVolume-2865)...
, because the complaint arises from the subtraction which turns 
negative, which, for UNSIGNED integers, is out of range. The GREATEST is 
apply'd after the subtraction.


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

Re: mysqld_multi

[Reindl Harald]

Am 01.06.2017 um 00:01 schrieb Matthew Black:

I DON'T RUN SYSTEMD, so that's not an option. At all. Why is that so hard to 
grasp?


then just clone the sysvinit script as i have done years ago before 
syetemd on dozens of machines without ever touch mysqld_multi - why is 
that so hard to grasp?



Where do I enter the command "create table database" when mysqld isn't running? 
It isn't possible to launch mysqld when there's no database directory or initialized 
database.


*that* was missing from the very begin but still RTFM
https://dev.mysql.com/doc/refman/5.7/en/mysql-install-db.html

in my google search field is nothing more than "mysql init database"

and guess what - when you have *somewhere* a running instance you can 
just shut it down, rsync the "mysql" folder from the datadir to the new 
instance and just fire it up - that's how i clone and init mysqld 
instances since 15 years, i explained that already



You fail to grasp my problem and your answers are completely unhelpful


you fail to describe your problem properly


they don't behave anything different if you have a single server


Really? With mysqld_multi, each mysqld daemon listens on a separate port. Each 
database instance gets its own environment that database administrators control 
WITHOUT INTERFERING with other database instances. In single-server 
environment, the server listens only on port 3306 and all databases run on that 
one port; it is not possible to shutdown individual databases, only ALL 
databases.


tell me something new - but there is no difference how you connect to a 
databaseserver - just host/port or host/socket - so what


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

RE: mysqld_multi

[Matthew Black]

I DON'T RUN SYSTEMD, so that's not an option. At all. Why is that so hard to 
grasp?

Where do I enter the command "create table database" when mysqld isn't running? 
It isn't possible to launch mysqld when there's no database directory or 
initialized database.

You fail to grasp my problem and your answers are completely unhelpful. I did 
not ask for "help" commands or links to the 5200-page reference manual, as 
those did not supply the options required to solve MY PROBLEM. This is a 
community forum where members are free to post questions. Several people 
pointed me toward the right direction but their answers were incomplete. It was 
ultimately Oracle support that answered my question, for which I am most 
appreciative.


> they don't behave anything different if you have a single server

Really? With mysqld_multi, each mysqld daemon listens on a separate port. Each 
database instance gets its own environment that database administrators control 
WITHOUT INTERFERING with other database instances. In single-server 
environment, the server listens only on port 3306 and all databases run on that 
one port; it is not possible to shutdown individual databases, only ALL 
databases.

matthew



-Original Message-
From: Reindl Harald [mailto:h.rei...@thelounge.net] 
Sent: Wednesday, May 31, 2017 9:28 AM
To: mysql@lists.mysql.com
Subject: Re: mysqld_multi



Am 31.05.2017 um 17:48 schrieb Matthew Black:
> # mysql -uroot -p --socket=/MySQLdb/cba/mysql.sock
> mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
> mysql> quit
> 
> I was simply seeking those three commands, but the reference manual did not 
> provide a real-world example.

they don't behave anything different if you have a single server, 
mysqld_multi or just a dozen mysqld instances started directly with 
systemd and "Can anyone provide a simple example of how to edit 
/etc/my.cnf file and command line steps necessary for creating a new 
database running on, for example, port 3311" still is nosense besides a 
basic command like "create table database" where your problem was 
obviosuly connect to the instance at all

in my first response you got:
[harry@srv-rhsoft:~]$ mysql --help | grep port

well, find the socket option is similar

[harry@rh:~]$ mysql --help | grep socket
   -S, --socket=name   The socket file to use for connection.

-- 
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: mysqld_multi

[Reindl Harald]

Am 31.05.2017 um 17:48 schrieb Matthew Black:

# mysql -uroot -p --socket=/MySQLdb/cba/mysql.sock
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
mysql> quit

I was simply seeking those three commands, but the reference manual did not 
provide a real-world example.


they don't behave anything different if you have a single server, 
mysqld_multi or just a dozen mysqld instances started directly with 
systemd and "Can anyone provide a simple example of how to edit 
/etc/my.cnf file and command line steps necessary for creating a new 
database running on, for example, port 3311" still is nosense besides a 
basic command like "create table database" where your problem was 
obviosuly connect to the instance at all


in my first response you got:
[harry@srv-rhsoft:~]$ mysql --help | grep port

well, find the socket option is similar

[harry@rh:~]$ mysql --help | grep socket
  -S, --socket=name   The socket file to use for connection.

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

RE: mysqld_multi

[Matthew Black]

Thank you all for the tips. I've read the manual multiple times and it is NOT 
clear what options are necessary in a multi environment. The missing element 
was provided by Oracle support: I need to use --defaults-file=my.cnf as in:

# mysqld --defaults-file=cba.cnf --initialize --user=cba

CBA.CNF contents:
[mysqld]# No instance number, NOT documented!
socket = /MySQLdb/cba/mysql.sock
port   = 3317
pid-file   = /MySQLdb/cba/mysqld.pid
datadir= /MySQLdb/cba
lc-messages-dir = /usr/share/mysql/English
log-error  = /MySQLdb/cba/mysql.error
user   = cba


# mysql -uroot -p --socket=/MySQLdb/cba/mysql.sock
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
mysql> quit


I was simply seeking those three commands, but the reference manual did not 
provide a real-world example.

matthew



-Original Message-
From: shawn l.green [mailto:shawn.l.gr...@oracle.com] 
Sent: Saturday, May 20, 2017 10:57 AM
To: mysql@lists.mysql.com
Subject: Re: mysqld_multi

Hello Matthew,

On 5/19/2017 12:19 PM, Matthew Black wrote:
> I just installed MySQL Enterprise Edition 5.7 on RHEL 6.8 to replace an aging 
> 5.1 system running on RHEL 5. We run mysqld_multi with multiple instances, 
> each database on its own TCP Port 33xx. I'm having trouble creating a 
> database on the new server in a multi environment.
>
>
>
> Can anyone provide a simple example of how to edit /etc/my.cnf file and 
> command line steps necessary for creating a new database running on, for 
> example, port 3311?
>
>
>
> Thanks in advance.
>
>
>
> matthew
>

How to use mysql_multi is covered in the Manual. This includes a sample 
my.cnf file demonstrating how to define your separate instances.
https://dev.mysql.com/doc/refman/5.7/en/mysqld-multi.html

However, before you setup an instance to be managed by mysqld_multi, you 
will need to instantiate a set of datafiles for that 5.7 instance of the 
mysqld daemon to manage. You do that following the directions here (by 
hand) the first time.
https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization.html

This means you need to setup at least two folders (one for --datadir and 
one for --tmpdir) for each separate instance you want to create and 
assign ownership and privileges to those folders appropriate to the user 
your mysqld daemon will be executing as when it runs. There are other 
things you must also keep unique between instances when they share a 
common host machine. Those are described here:
https://dev.mysql.com/doc/refman/5.7/en/multiple-servers.html

An example of setting up the folders and assigning privileges to them is 
located in the instructions to installing a set of mysqld binaries using 
a .zip or .tar archive.  Please note, you do not need a separate mysqld 
installation for each instance you want to create. Several daemons (each 
operating on their own port, socket, folders, data files,... ) can be 
started using just one set of binary files.
https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

So... the general process would look like this (presuming you have 
already installed mysqld and setup at least one instance)
==

1) Decide where you want a second (or later) instance to store its 
files. Choose port numbers and unix socket names for this new instance 
that are unique from any other instances that will be running on this host.

2) Setup any new folders you need to create (including assigning privileges)

3) Document those names and any other settings you want this additional 
instance to use in a configuration file specific for this instance

4) Use that special configuration file to bootstrap (initialize) the 
data files used to manage that instance (the --initialize instructions 
were linked to earlier in this reply)

5) Once you have this instance setup the way you want. Shut it down.

6) Copy the elements that are unique to this instance into an 
appropriately-named section of your common configuration file (the one 
that mysqld_multi will read)

7) Test that you can start/stop this new instance using mysqld_multi

As you can tell, it takes a bit of planning and effort to establish a 
non-default setup of hosting multiple MySQL instances on the same host 
machine. There is no simple one-line command to tell mysqld_multi to 
create a new instance as there are things it cannot do (like create 
folders in your file system).

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


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

Re: Which choice of mysql??

[Peter Brawley]

On 5/24/2017 12:21, Papa wrote:
I am learning Java and I'd like to use MySQL to create, delete and 
update databases using Java. I have done this with C++ SQLite, but 
now, as I said, I want to port my code to Java [not easy for a Java 
nubby]. However, the MySQL installer 
[mysql-installer-web-community-5.7.18.1] has several choices of which 
I am not familiar. I don't use MS-Office at all, I use OpenOffice, so, 
when at the "Choosing a Setup Type" window, 'Developer Default' 
requires some MS products I do not have. 


You mean MySQL for Visual Studio and MySQL for Excel? I think you can 
uncheck them.


I tried the other options, but I think they also require MS Office 
and/or VS.


What other options require Office or VS?


What would you recommend me in this case?


You'll want Connector/J (under "Connectors"?).

PB



TIA





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

Re: mysqld_multi

[shawn l.green]

Hello Matthew,

On 5/19/2017 12:19 PM, Matthew Black wrote:

I just installed MySQL Enterprise Edition 5.7 on RHEL 6.8 to replace an aging 
5.1 system running on RHEL 5. We run mysqld_multi with multiple instances, each 
database on its own TCP Port 33xx. I'm having trouble creating a database on 
the new server in a multi environment.



Can anyone provide a simple example of how to edit /etc/my.cnf file and command 
line steps necessary for creating a new database running on, for example, port 
3311?



Thanks in advance.



matthew



How to use mysql_multi is covered in the Manual. This includes a sample 
my.cnf file demonstrating how to define your separate instances.

https://dev.mysql.com/doc/refman/5.7/en/mysqld-multi.html

However, before you setup an instance to be managed by mysqld_multi, you 
will need to instantiate a set of datafiles for that 5.7 instance of the 
mysqld daemon to manage. You do that following the directions here (by 
hand) the first time.

https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization.html

This means you need to setup at least two folders (one for --datadir and 
one for --tmpdir) for each separate instance you want to create and 
assign ownership and privileges to those folders appropriate to the user 
your mysqld daemon will be executing as when it runs. There are other 
things you must also keep unique between instances when they share a 
common host machine. Those are described here:

https://dev.mysql.com/doc/refman/5.7/en/multiple-servers.html

An example of setting up the folders and assigning privileges to them is 
located in the instructions to installing a set of mysqld binaries using 
a .zip or .tar archive.  Please note, you do not need a separate mysqld 
installation for each instance you want to create. Several daemons (each 
operating on their own port, socket, folders, data files,... ) can be 
started using just one set of binary files.

https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

So... the general process would look like this (presuming you have 
already installed mysqld and setup at least one instance)

==

1) Decide where you want a second (or later) instance to store its 
files. Choose port numbers and unix socket names for this new instance 
that are unique from any other instances that will be running on this host.


2) Setup any new folders you need to create (including assigning privileges)

3) Document those names and any other settings you want this additional 
instance to use in a configuration file specific for this instance


4) Use that special configuration file to bootstrap (initialize) the 
data files used to manage that instance (the --initialize instructions 
were linked to earlier in this reply)


5) Once you have this instance setup the way you want. Shut it down.

6) Copy the elements that are unique to this instance into an 
appropriately-named section of your common configuration file (the one 
that mysqld_multi will read)


7) Test that you can start/stop this new instance using mysqld_multi

As you can tell, it takes a bit of planning and effort to establish a 
non-default setup of hosting multiple MySQL instances on the same host 
machine. There is no simple one-line command to tell mysqld_multi to 
create a new instance as there are things it cannot do (like create 
folders in your file system).


Regards,
--
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: mysqld_multi

[Reindl Harald]

can't you just stop reply-all so that responses are going back to the 
list


Am 20.05.2017 um 12:01 schrieb Johan De Meersman:

He means a database, not a schema. Instance would have been clearer as 
terminology goes, admittedly, but in a MySQL context the two are identical.


database and instance are identical?

> I just installed MySQL Enterprise Edition 5.7 on RHEL 6.8 to
> replace an aging 5.1 system running on RHEL 5. We run
> mysqld_multi with multiple instances, each database on its
> own TCP Port 33xx. I'm having trouble creating a database
> on the new server in a multi environment.

i still have no idea what is the problem

* connect to the instance with "mysql -h 127.0.0.1 -P 3307 -u root -p"
* create database dbname


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

Re: mysqld_multi

[Johan De Meersman]

He means a database, not a schema. Instance would have been clearer as 
terminology goes, admittedly, but in a MySQL context the two are identical.
-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

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

Re: mysqld_multi

[Reindl Harald]

Am 19.05.2017 um 18:42 schrieb Matthew Black:

Sorry, but that is not the least bit helpful. We are not ready for RHEL 7, 
which is VERY different than prior versions. I don't really need the victim 
blaming for using an earlier RHEL release that is still fully supported and in 
widespread use.


your problem - we are using Fedora in production since 2008 and systemd 
was introduced that i even can't remember the sysvinit mess...



As for /etc/my.cnf, that's where one defines the port, database location, log 
file, port number, and user, so they don't have to be placed on the command 
line.


and how do you imagine hat to work for *multiple instances*


Your response does not explain the command line steps necessary for creating a 
new database.


becasue creating a database is absolutely basic stuff at all and if you 
mean the mysql database with users itself - well, i hvanet created one 
from scratch since 2003 because they are portable between 
windows/linux/osx and i just rsync the folder or a baisc install and 
that's it



-Original Message-
From: Reindl Harald [mailto:h.rei...@thelounge.net]
Sent: Friday, May 19, 2017 9:27 AM
To: mysql@lists.mysql.com
Subject: Re: mysqld_multi



Am 19.05.2017 um 18:19 schrieb Matthew Black:

I just installed MySQL Enterprise Edition 5.7 on RHEL 6.8 to replace an aging 
5.1 system running on RHEL 5. We run mysqld_multi with multiple instances, each 
database on its own TCP Port 33xx. I'm having trouble creating a database on 
the new server in a multi environment.

Can anyone provide a simple example of how to edit /etc/my.cnf file and command 
line steps necessary for creating a new database running on, for example, port 
3311?


what has this to do with the my.cnf?
just connect to the correct instance and that's it

and probably get rid of RHEL6 because with systemd you don't need all
the crap around to start multiple instances, just a few lines in the
unit-file pointing to the correct config and mysqld_safe is also no needed

[harry@srv-rhsoft:~]$ mysql --help | grep port
-P, --port=#Port number to use for connection or 0 for
default to, in
--progress-reports  Get progress reports for long running commands (like
(Defaults to on; use --skip-progress-reports to
disable.)


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

RE: mysqld_multi

[Matthew Black]

Sorry, but that is not the least bit helpful. We are not ready for RHEL 7, 
which is VERY different than prior versions. I don't really need the victim 
blaming for using an earlier RHEL release that is still fully supported and in 
widespread use.

As for /etc/my.cnf, that's where one defines the port, database location, log 
file, port number, and user, so they don't have to be placed on the command 
line.

Your response does not explain the command line steps necessary for creating a 
new database.
 
matthew


-Original Message-
From: Reindl Harald [mailto:h.rei...@thelounge.net] 
Sent: Friday, May 19, 2017 9:27 AM
To: mysql@lists.mysql.com
Subject: Re: mysqld_multi



Am 19.05.2017 um 18:19 schrieb Matthew Black:
> I just installed MySQL Enterprise Edition 5.7 on RHEL 6.8 to replace an aging 
> 5.1 system running on RHEL 5. We run mysqld_multi with multiple instances, 
> each database on its own TCP Port 33xx. I'm having trouble creating a 
> database on the new server in a multi environment.
> 
> Can anyone provide a simple example of how to edit /etc/my.cnf file and 
> command line steps necessary for creating a new database running on, for 
> example, port 3311?

what has this to do with the my.cnf?
just connect to the correct instance and that's it

and probably get rid of RHEL6 because with systemd you don't need all 
the crap around to start multiple instances, just a few lines in the 
unit-file pointing to the correct config and mysqld_safe is also no needed

[harry@srv-rhsoft:~]$ mysql --help | grep port
   -P, --port=#Port number to use for connection or 0 for 
default to, in
   --progress-reports  Get progress reports for long running commands (like
   (Defaults to on; use --skip-progress-reports to 
disable.)

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

Re: mysqld_multi

[Reindl Harald]

Am 19.05.2017 um 18:19 schrieb Matthew Black:

I just installed MySQL Enterprise Edition 5.7 on RHEL 6.8 to replace an aging 
5.1 system running on RHEL 5. We run mysqld_multi with multiple instances, each 
database on its own TCP Port 33xx. I'm having trouble creating a database on 
the new server in a multi environment.

Can anyone provide a simple example of how to edit /etc/my.cnf file and command 
line steps necessary for creating a new database running on, for example, port 
3311?


what has this to do with the my.cnf?
just connect to the correct instance and that's it

and probably get rid of RHEL6 because with systemd you don't need all 
the crap around to start multiple instances, just a few lines in the 
unit-file pointing to the correct config and mysqld_safe is also no needed


[harry@srv-rhsoft:~]$ mysql --help | grep port
  -P, --port=#Port number to use for connection or 0 for 
default to, in

  --progress-reports  Get progress reports for long running commands (like
  (Defaults to on; use --skip-progress-reports to 
disable.)


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

Re: tcmalloc mysql 5.7.14

[Machiel Richards]

Hi Shawn

   Thank you for the response.

I have in fact in the meantime installed jemmalloc libraries on a few
servers and the issue with the memory is definitely gone when using
jemmalloc library.

 I do still feel this is a bug though , as I am only able to produce
this issue when using mysql 5.7 on ubuntu using the system memory
libraries.

   Using other libraries like jemmalloc now seems to work 100% again, and
in previous versions of mysql on same OS , using system libraries also
works 100%.

 This issue I have is only present when using mysql 5.7.

  I have logged this as a bug, however simply got a reply that it is
not a bug.

  but all the other tests as well as the new jemmalloc was all tested
on the same machine and OS.

  Thank you for all the assistance though...


Regards

On Sat, May 13, 2017 9:41 pm, shawn l.green wrote:
> (yes, I recognize how late this reply is)
>
>
> On 5/8/2017 7:56 AM, Reindl Harald wrote:
>
>>
>>
>> Am 08.05.2017 um 13:51 schrieb Machiel Richards:
>>
>>> We are having an issue with memory allocations on mysql 5.7.14
>>> whereby mysql is not releasing the memory that is being allocated
>>> during mysqldump processes.
>>>
>>> This has been logged as a bug with mysql dev team however they do
>>> not see this as a bug and suggested we test using another malloc
>>> library such as tcmalloc.
>>>
>>> However from what I can see, this is no longer included in mysql 5.7
>>> and thus I am trying to find out if anyone can tell me how to enable /
>>>  install this and how to tell mysql to use this library.
>>
>> does the oracle stuff not support "jemalloc" like MariaDB
>>
>> MariaDB even suppports "-DWITH_JEMALLOC=system" because it's not the
>> job of random software ship and build random library sources in their
>> tarballs which usually don't get much attention in case of updates
>> (others than system packages)
>>
>>
>
> Yes, if your system has the jemalloc library on it, MySQL can use it.
> One way to activate it is with this option to mysqld_safe.
>
>
> https://dev.mysql.com/doc/refman/5.7/en/mysqld-safe.html#option_mysqld_sa
> fe_malloc-lib
>
> And, you can control whether the InnoDB engine uses it's own memory
> management routines or those provided by the operating system:
> https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-use_sys_malloc
> .html
>
>
>
>
> Just because we don't bundle a library with our software does not mean
> that our end users cannot download one for their operating system from
> other reputable sources (like the Google devs for tcmalloc, for example)
>
> --
> 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
>
>
>



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

Re: Ssd vs mix of ssd and spinning disk

[shawn l.green]

Hi Shain,

On 5/8/2017 1:53 PM, Shain Miley wrote:

Hello,
We have traditionally setup our mysql database servers with a mix of ssd and 
spinning disk drives.

We use the ssd drives (Raid-1) for the mysql tablespace data, and we use the 
spinning disks (15 sas in Raid-1) for the index data, etc.

I am wondering if going forward we should simply put all of the data on the ssd 
drives. Does anyone have any information on whether or not we would see any 
significant performance increase if we made this switch?

I have been thinking about using 4 ssd drives (Raid-10) going forward…and 
wondering if we should expect to see any improvement in the database 
performance.

Any thoughts?

Thanks in advance,

Shain



If you would benefit from shifting storage technologies depends on how 
limiting your current devices are to your overall throughput.


In most cases, workloads are either CPU-bound (normally due to poor 
choices in table design or query patterns) or DISK-bound (too many reads 
and writes, i/o requests,  for the device to keep up).


Occasionally systems become MEMORY-bound (normally due to poor 
configuration choices which push the system to using swap) or 
NETWORK-bound (the number of concurrent network round trips to complete 
a task is higher than the components can handle).



Of those 4 situations, which is contributing most to your total response 
latency?


For example, are you spending more time waiting for data to be buffered 
in from disk than you are computing and returning the results? If so, 
faster disks could help temporarily.


What may help more (and for a longer time) is to improve your storage 
and retrieval patterns (table and query designs) to require less 
frequent trips to disk (aka, better buffering) or to need smaller slices 
of each table (more selective indexes, querying for fewer columns, 
sharding tables, sharding data to different instances, partitioning 
data, ... ).



--
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: tcmalloc mysql 5.7.14

[shawn l.green]

(yes, I recognize how late this reply is)

On 5/8/2017 7:56 AM, Reindl Harald wrote:



Am 08.05.2017 um 13:51 schrieb Machiel Richards:

We are having an issue with memory allocations on mysql 5.7.14
whereby
mysql is not releasing the memory that is being allocated during
mysqldump processes.

 This has been logged as a bug with mysql dev team however they do
not
see this as a bug and suggested we test using another malloc library
such as tcmalloc.

However from what I can see, this is no longer included in mysql 5.7
and thus I am trying to find out if anyone can tell me how to enable /
install this and how to tell mysql to use this library.


does the oracle stuff not support "jemalloc" like MariaDB

MariaDB even suppports "-DWITH_JEMALLOC=system" because it's not the job
of random software ship and build random library sources in their
tarballs which usually don't get much attention in case of updates
(others than system packages)



Yes, if your system has the jemalloc library on it, MySQL can use it. 
One way to activate it is with this option to mysqld_safe.


https://dev.mysql.com/doc/refman/5.7/en/mysqld-safe.html#option_mysqld_safe_malloc-lib

And, you can control whether the InnoDB engine uses it's own memory 
management routines or those provided by the operating system:
https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-use_sys_malloc.html 




Just because we don't bundle a library with our software does not mean 
that our end users cannot download one for their operating system from 
other reputable sources (like the Google devs for tcmalloc, for example)


--
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: Ssd vs mix of ssd and spinning disk

[Reindl Harald]

Am 08.05.2017 um 19:53 schrieb Shain Miley:

Hello,
We have traditionally setup our mysql database servers with a mix of ssd and 
spinning disk drives.

We use the ssd drives (Raid-1) for the mysql tablespace data, and we use the 
spinning disks (15 sas in Raid-1) for the index data, etc.

I am wondering if going forward we should simply put all of the data on the ssd 
drives. Does anyone have any information on whether or not we would see any 
significant performance increase if we made this switch?

I have been thinking about using 4 ssd drives (Raid-10) going forward…and 
wondering if we should expect to see any improvement in the database 
performance.

Any thoughts?


http://www.tansi.org/hybrid/


CAUTION: only works for RAID1 - not for linux RAID10

if i only woul dhave knwen that 10 years ago i would have my setups with 
4 disks on two RAID1 with a RAID0 on top...


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

Re: tcmalloc mysql 5.7.14

[Machiel Richards]

I upgraded my mysql on my laptop to the MariaDB which is using the
jemmalloc as you stated and that seems to work well.

However on the servers it is running the Percona mysql 5.7.14 and there
are no specific malloc libraries configured.
>From what I understood , this then uses the normal OS malloc libraries ,
but this has proven to be problematic.


So even if I can setup jemmalloc as a test to see if it makes a
difference, that would help, however I am not sure how to enable / install
it on percona mysql 5.7.14 though so this is where I need some help.

   I would really appreciate any help here.


Regards


On Mon, May 8, 2017 1:56 pm, Reindl Harald wrote:
>

>
> Am 08.05.2017 um 13:51 schrieb Machiel Richards:
>
>> We are having an issue with memory allocations on mysql 5.7.14 whereby
>> mysql is not releasing the memory that is being allocated during
>> mysqldump processes.
>>
>> This has been logged as a bug with mysql dev team however they do not
>> see this as a bug and suggested we test using another malloc library such
>> as tcmalloc.
>>
>> However from what I can see, this is no longer included in mysql 5.7
>> and thus I am trying to find out if anyone can tell me how to enable /
>> install this and how to tell mysql to use this library.
>
> does the oracle stuff not support "jemalloc" like MariaDB
>
> MariaDB even suppports "-DWITH_JEMALLOC=system" because it's not the job
> of random software ship and build random library sources in their tarballs
> which usually don't get much attention in case of updates (others than
> system packages)
>
> --
> 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: tcmalloc mysql 5.7.14

[Reindl Harald]

Am 08.05.2017 um 13:51 schrieb Machiel Richards:

We are having an issue with memory allocations on mysql 5.7.14 whereby
mysql is not releasing the memory that is being allocated during
mysqldump processes.

 This has been logged as a bug with mysql dev team however they do not
see this as a bug and suggested we test using another malloc library
such as tcmalloc.

However from what I can see, this is no longer included in mysql 5.7
and thus I am trying to find out if anyone can tell me how to enable /
install this and how to tell mysql to use this library.


does the oracle stuff not support "jemalloc" like MariaDB

MariaDB even suppports "-DWITH_JEMALLOC=system" because it's not the job 
of random software ship and build random library sources in their 
tarballs which usually don't get much attention in case of updates 
(others than system packages)


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

My thanks to everyone who helped on this issue. The index did it.

Thanks.
Dave.


On 4/28/17, Johan De Meersman <vegiv...@tuxera.be> wrote:
> That is quite different, as I suspected :-)
>
> Referential keys require an index on the target table that begins with the
> referenced field, so you'll need to add one on user, as was specified in the
> create table you originally posted.
>
> On 28 April 2017 01:21:39 CEST, David Mehler <dave.meh...@gmail.com> wrote:
>>Hello,
>>
>> Here's the output of the command show create table virtual_users:
>>
>>  create table virtual_users;
>>+---+-+
>> | Table | Create Table
>>
>>
>>
>>
>>
>>
>>  |
>>+---+-+
>> | virtual_users | 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 '0',
>>   `quota_messages` int(11) NOT NULL DEFAULT '0',
>>   PRIMARY KEY (`id`),
>>   UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
>>   CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`)
>> REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
>> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
>>+---+-+
>> 1 row in set (0.00 sec)
>>
>>Thanks.
>>Dave.
>>
>>>
>>>
>>> On 4/26/17, Johan De Meersman <vegiv...@tuxera.be> wrote:
>>>>
>>>> I note that the innodb status says it couldn't find an index on the
>>>> referenced column. Did the create statements come from your create
>>>> scripts
>>>> or from a show create table statement? I'm suspicious about the
>>index on
>>>> virtual_users(user).
>>>>
>>>>
>>>> - Original Message -
>>>>> From: "David Mehler" <dave.meh...@gmail.com>
>>>>> To: "MySql" <mysql@lists.mysql.com>
>>>>> Sent: Tuesday, 25 April, 2017 23:07:19
>>>>> Subject: Re: Can not add foreign key constraint
>>>>
>>>>> Hello,
>>>>>
>>>>> Tried recreating the virtual_users table didn't solve anything.
>>Would
>>>>> it be possible for anyone to check out my box directly?
>>>>>
>>>>> Thanks.
>>>>> Dave.
>>>>
>>>> --
>>>> The bay-trees in our country are all wither'd
>>>> And meteors fright the fixed stars of heaven;
>>>> The pale-faced moon looks bloody on the earth
>>>> And lean-look'd prophets whisper fearful change.
>>>> These signs forerun the death or fall of kings.
>>>>   -- Wm. Shakespeare, "Richard II"
>>>>
>>>
>>> Thanks.
>>> Dave.
>>>
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:http://lists.mysql.com/mysql
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
>
> --
> 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

[Johan De Meersman]

That is quite different, as I suspected :-)

Referential keys require an index on the target table that begins with the 
referenced field, so you'll need to add one on user, as was specified in the 
create table you originally posted.

On 28 April 2017 01:21:39 CEST, David Mehler <dave.meh...@gmail.com> wrote:
>Hello,
>
> Here's the output of the command show create table virtual_users:
>
>  create table virtual_users;
>+---+-+
> | Table | Create Table
>
>
>
>
>
>
>  |
>+---+-+
> | virtual_users | 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 '0',
>   `quota_messages` int(11) NOT NULL DEFAULT '0',
>   PRIMARY KEY (`id`),
>   UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
>   CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`)
> REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
>+---+-+
> 1 row in set (0.00 sec)
>
>Thanks.
>Dave.
>
>>
>>
>> On 4/26/17, Johan De Meersman <vegiv...@tuxera.be> wrote:
>>>
>>> I note that the innodb status says it couldn't find an index on the
>>> referenced column. Did the create statements come from your create
>>> scripts
>>> or from a show create table statement? I'm suspicious about the
>index on
>>> virtual_users(user).
>>>
>>>
>>> - Original Message -
>>>> From: "David Mehler" <dave.meh...@gmail.com>
>>>> To: "MySql" <mysql@lists.mysql.com>
>>>> Sent: Tuesday, 25 April, 2017 23:07:19
>>>> Subject: Re: Can not add foreign key constraint
>>>
>>>> Hello,
>>>>
>>>> Tried recreating the virtual_users table didn't solve anything.
>Would
>>>> it be possible for anyone to check out my box directly?
>>>>
>>>> Thanks.
>>>> Dave.
>>>
>>> --
>>> The bay-trees in our country are all wither'd
>>> And meteors fright the fixed stars of heaven;
>>> The pale-faced moon looks bloody on the earth
>>> And lean-look'd prophets whisper fearful change.
>>> These signs forerun the death or fall of kings.
>>>   -- Wm. Shakespeare, "Richard II"
>>>
>>
>> Thanks.
>> Dave.
>>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/mysql

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

--
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 output of the command show create table virtual_users:

  create table virtual_users;
 
+---+-+
 | Table | Create Table






  |
 
+---+-+
 | virtual_users | 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 '0',
   `quota_messages` int(11) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
   UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`),
   CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`)
 REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
 
+---+-+
 1 row in set (0.00 sec)

Thanks.
Dave.

>
>
> On 4/26/17, Johan De Meersman <vegiv...@tuxera.be> wrote:
>>
>> I note that the innodb status says it couldn't find an index on the
>> referenced column. Did the create statements come from your create
>> scripts
>> or from a show create table statement? I'm suspicious about the index on
>> virtual_users(user).
>>
>>
>> - Original Message -
>>> From: "David Mehler" <dave.meh...@gmail.com>
>>> To: "MySql" <mysql@lists.mysql.com>
>>> Sent: Tuesday, 25 April, 2017 23:07:19
>>> Subject: Re: Can not add foreign key constraint
>>
>>> Hello,
>>>
>>> Tried recreating the virtual_users table didn't solve anything. Would
>>> it be possible for anyone to check out my box directly?
>>>
>>> Thanks.
>>> Dave.
>>
>> --
>> The bay-trees in our country are all wither'd
>> And meteors fright the fixed stars of heaven;
>> The pale-faced moon looks bloody on the earth
>> And lean-look'd prophets whisper fearful change.
>> These signs forerun the death or fall of kings.
>>   -- Wm. Shakespeare, "Richard II"
>>
>
> 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 constraint

[David Mehler]

Hello,

Tried recreating the virtual_users table didn't solve anything. Would
it be possible for anyone to check out my box directly?

Thanks.
Dave.


On 4/24/17, Peter Brawley  wrote:
> On 4/24/2017 20:47, David Mehler wrote:
>> Hello,
>>
>> Thanks. Here's the create statements for virtual_domains,
>> virtual_users, and the one that isn't working lastauth:
>>
>> CREATE TABLE `virtual_domains` (
>>`id` int(11) NOT NULL auto_increment,
>>`name` varchar(50) NOT NULL,
>>PRIMARY KEY (`id`)
>> ) 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(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;
>>
>> 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;
>
> Again, that works in 5.6 and 5.7, so if it doesn't work for you, there
> is something wrong in one of your files. Did you try recreating
> virtual_users?
>
> PB
>
> -
>
>>
>> Thanks.
>> Dave.
>>
>>
>> On 4/24/17, Peter Brawley  wrote:
>>> On 4/24/2017 18:16, David Mehler wrote:
 Hello,

 I'm running Mysql 5.7.18.

 My virtual_users are working fine, it's the new table that isn't. Or
 am I missing something?
>>> MySQL says it cannot find an index which the table's create statement
>>> declares. If that's the create statement that created the table, the
>>> table's corrupted.
>>>
>>> PB
>>>
>>> 
 Thanks.
 Dave.


 On 4/24/17, Peter Brawley  wrote:
> On 4/24/2017 17:41, David Mehler wrote:
>> Hello,
>>
>> Here's the output. I hope it helps.
>>
>> 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
>> root@localhost [mail]> show engine innodb status;
>> 

Re: Can not add foreign key constraint

[Peter Brawley]

On 4/24/2017 20:47, David Mehler wrote:

Hello,

Thanks. Here's the create statements for virtual_domains,
virtual_users, and the one that isn't working lastauth:

CREATE TABLE `virtual_domains` (
   `id` int(11) NOT NULL auto_increment,
   `name` varchar(50) NOT NULL,
   PRIMARY KEY (`id`)
) 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(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;

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;


Again, that works in 5.6 and 5.7, so if it doesn't work for you, there 
is something wrong in one of your files. Did you try recreating 
virtual_users?


PB

-



Thanks.
Dave.


On 4/24/17, Peter Brawley  wrote:

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

Hello,

I'm running Mysql 5.7.18.

My virtual_users are working fine, it's the new table that isn't. Or
am I missing something?

MySQL says it cannot find an index which the table's create statement
declares. If that's the create statement that created the table, the
table's corrupted.

PB



Thanks.
Dave.


On 4/24/17, Peter Brawley  wrote:

On 4/24/2017 17:41, David Mehler wrote:

Hello,

Here's the output. I hope it helps.

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
root@localhost [mail]> show engine innodb status;

Re: Can not add foreign key constraint

[David Mehler]

Hello,

Thanks. Here's the create statements for virtual_domains,
virtual_users, and the one that isn't working lastauth:

CREATE TABLE `virtual_domains` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) 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(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;

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;

Thanks.
Dave.


On 4/24/17, Peter Brawley  wrote:
> On 4/24/2017 18:16, David Mehler wrote:
>> Hello,
>>
>> I'm running Mysql 5.7.18.
>>
>> My virtual_users are working fine, it's the new table that isn't. Or
>> am I missing something?
>
> MySQL says it cannot find an index which the table's create statement
> declares. If that's the create statement that created the table, the
> table's corrupted.
>
> PB
>
> 
>>
>> Thanks.
>> Dave.
>>
>>
>> On 4/24/17, Peter Brawley  wrote:
>>> On 4/24/2017 17:41, David Mehler wrote:
 Hello,

 Here's the output. I hope it helps.

 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
 root@localhost [mail]> show engine innodb status;
 

Re: DATETIME vs CHAR for "timestamp"

[SSC_perl]

> On Apr 14, 2017, at 1:07 PM, shawn l.green  wrote:
> 
> That all depends. Do you...

Hi Shawn,

I thought I had replied to your response, but it looks like I didn’t.  
Thank you for your email.  It was a thorough response and the links were very 
helpful, as well.  I’ve settled on both DATE and DATETIME, depending on whether 
the time is needed or not, which means I’ll have to change some of my code, but 
that will only strengthen the script in the long run.

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

[David Mehler]

Hello,

I'm running Mysql 5.7.18.

My virtual_users are working fine, it's the new table that isn't. Or
am I missing something?

Thanks.
Dave.


On 4/24/17, Peter Brawley  wrote:
> On 4/24/2017 17:41, David Mehler wrote:
>> Hello,
>>
>> Here's the output. I hope it helps.
>>
>> 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
>> root@localhost [mail]> show engine innodb status;
>> 

Re: Can not add foreign key constraint

[David Mehler]

Hello,

Here's the output. I hope it helps.

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
root@localhost [mail]> show engine innodb status;

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

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

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

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

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