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: 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: 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: MySQL Cluster Manager 1.4.3 has been released

[daniel so]

Hi, Sree,

I'm taking care of the release now. Docs should be up soon.

Daniel


On 10/07/2017 6:53 AM, Sreedhar S wrote:


Dear MySQL Users,

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

MySQL Cluster Manager is an optional component of the MySQL Cluster 
Carrier

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

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

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

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


Changes in MySQL Cluster Manager 1.4.3 (2017-07-10)

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

   Functionality Added or Changed

 * Agent: CPU usage during idle time for the mcmd agents has
   been significantly reduced. (Bug #26227736)

 * Agent: A new error code, Error 7030, has been created for
   failed ndb_mgmd commands and mysqld queries. (Bug
   #26160968)

 * Agent: Added support for the --skip-networking option for
   mysqld nodes, allowing mysqld nodes of a managed cluster
   to communicate with client applications using named pipes
   or shared memory on Windows platforms, and socket files
   on Unix-like platforms.
   Notice that, however, communication between mcmd agents
   and mcm clients using named pipes, shared memory, or
   socket files remain unsupported. (Bug #25992390, Bug
   #25974499)

 * Client: The start cluster --initial command now
   reinitializes the SQL nodes (if their data directories
   are empty) as well as the data nodes of an NDB Cluster. A
   new option, --skip-init, has been introduced, for
   specifying a comma-separated list of the SQL nodes for
   which reinitialization is to be skipped. (Bug #25856285,
   Bug #85713)

 * Client: Checksum verification has been added for all
   cluster reconfiguration plans created by the mcmd agents.
   Checksums for plans created locally are shared among all
   agents, and when the checksums do not match, the
   reconfiguration is aborted. This prevents agents from
   executing different plans. (Bug #23225839)

 * Files have been removed from the MySQL Cluster Manager +
   NDB Cluster bundled package, in order to reduce the
   package size significantly. (Bug #25916635)

   Bugs Fixed

 * Agent: When the list nextnodeid command was run against a
   cluster with the maximum number of nodes allowed, the
   mcmd agent quit unexpectedly. With this fix, the
   situation is properly handled. (Bug #26286531)

 * Agent: For a cluster with NoOfReplicas=1, trying to stop
   a data node with the stop process command would cause the
   agent to quit unexpectedly. (Bug #26259780)

 * Agent: When a data node was killed by an arbitrator in a
   situation of network partitioning, an mcmd failed to
   handle the exit report from the node and quit
   unexpectedly. It was due to a mishandling of the
   nodegroup information, which this fix corrects. (Bug
   #26192412)

 * Agent: A cluster could not be started if a relative path
   had been used for the --manager-directory option to set
   the location of the agent repository. (Bug #26172299)

 * Agent: When executing a user command, the mcmd agent
   could hang if the expected reply from another agent never
   arrived. This fix improves the timeout handling to avoid
   such hangs. (Bug #26168339)

 * Agent: While running the import config command, the mcmd
   agents that were present during the earlier dryrun for
   the import would become silent and then unavailable. This
   was due to some hostname resolution issues, which has
   been addressed by this fix. (Bug #26089906)

 * Agent: A collect log command sometimes failed at the
   middle with an ERROR 1003 Internal error: No clients
   connected. It was because the mcmd agent reset the copy
   completion marker prematurely; the behavior has been
   stopped by this fix. (Bug #26086958)

 * Agent: When the mcmd agents' clocks ran out of sync due
   to time drifts on virtual machines running Windows
   operations systems and then the clocks ran in sync again,
   communications among the agents failed. This fix prevents
   the problem by making the agents use a monotonic timer
   

Re: MySQL server has gone away

[Reindl Harald]

Am 03.04.2017 um 21:22 schrieb Mahmood N:

well, who did set it that low?


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


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


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


how to act on a 4xx temporary error?

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



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

Am 03.04.2017 um 20:41 schrieb Mahmood N:

Good news!

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


well, who did set it that low?

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

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



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

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

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


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

Re: MySQL server has gone away

[Mahmood N]

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

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

 

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

well, who did set it that low?

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

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

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

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



   

Re: MySQL server has gone away

[Reindl Harald]

Am 03.04.2017 um 20:41 schrieb Mahmood N:

Good news!

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


well, who did set it that low?

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

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


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

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


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

Re: MySQL server has gone away

[Mahmood N]

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

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

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


   

   

Re: MySQL server has gone away

[Mahmood N]

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


   

Re: MySQL server has gone away

[Reindl Harald]

Am 03.04.2017 um 19:45 schrieb Mahmood N:

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


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

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

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


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

Re: MySQL server has gone away

[Mahmood N]

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


   

Re: MySQL server has gone away

[Mahmood N]

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


   

Re: MySQL server has gone away

[Reindl Harald]

Am 03.04.2017 um 19:26 schrieb Mahmood N:

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


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


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



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


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

;mysqlnd.net_read_timeout = 31536000

Note that is comment


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


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

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

Re: MySQL server has gone away

[Mahmood N]

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

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

Note that is comment.

 Regards,
Mahmood 


   

Re: MySQL server has gone away

[Reindl Harald]

Am 03.04.2017 um 18:11 schrieb Mahmood N:

The my.conf file contains

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


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



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


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


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

Re: MySQL server has gone away

[Mahmood N]

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


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


   

Re: MySQL server has gone away

[Reindl Harald]

Am 03.04.2017 um 17:52 schrieb Mahmood N:

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


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

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


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

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


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

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

Re: MySQL server has gone away

[Mahmood N]

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



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



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

   

Re: MySQL server has gone away

[shawn l.green]

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

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

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

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

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

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

mysql> show full processlist
 ->
 ->



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



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



You need to consider a few possibilities,

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


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


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


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


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

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


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

Re: MySQL server has gone away

[Mahmood N]

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

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

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

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

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

mysql> show full processlist
    ->
    ->



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



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



   

Re: MySQL server has gone away

[Johnny Withers]

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

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



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

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




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

Re: MySQL server has gone away

[Mahmood N]

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

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

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

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

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


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

 Regards,
Mahmood

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

   

Re: MySQL server has gone away

[Ken D'Ambrosio]

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

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

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

Re: Mysql 5.6, Centos 7 and errno: 24 - Too many open files - Again

[Reindl Harald]

Am 01.12.2016 um 08:26 schrieb Götz Reinicke - IT Koordinator:

it happend again and I can't fix it, may be someone has a working
solution and information on that:


surely -> man systemd

LimitNOFILE=infinity
LimitMEMLOCK=infinity
OOMScoreAdjust=-1000



I installed the most recent mysql 5.6 community on a server and do get a
lot of "errno: 24 - Too many open files".

There are suggestions to increase the open_files_limit, change/add that
to /etc/security/limits.conf and modify the systemd script by hand


/etc/security/limits.conf don't matter
/etc/systemd/system/servicename.service.d/ is your friend

https://www.freedesktop.org/software/systemd/man/systemd-system.conf.html

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

Re: MySQL Platform Migration

[Johan De Meersman]

Right... if even a fraction of that is true (I have no reason to doubt you, but 
I can also not verify any of it, obviously), that is imo certainly something to 
take up with a list moderator (Shawn ?). That kind of behaviour is unacceptable.

- Original Message -
> From: "Reindl Harald" <h.rei...@thelounge.net>
> To: "MySql" <mysql@lists.mysql.com>
> Sent: Tuesday, 13 September, 2016 13:39:59
> Subject: Re: MySQL Platform Migration

> Am 13.09.2016 um 12:13 schrieb Johan De Meersman:
>> - Original Message -
>>> From: "Ryan Coleman" <ryan.cole...@cwis.biz>
>>> Subject: Re: MySQL Platform Migration
>>>
>>> Because they want to be belittled by european jackasses online.
>>
>> The thing is, while he has a bit of a rough edge, his technical advice is 
>> always
>> solid. What has your contribution been, outside of insulting him, and for 
>> some
>> reason, an entire continent?
> 
> this guy is just mentally ill and the next steps are:
> 
> * starting off-list mails (already happened)
> * can't stand the repsone
> * add a list-moderator to CC
> * setup something in his MUA which send all read messages back
>   to me (includig old ones from other lists he reads again)
> * can't stand the response to that action
> * forwards that responses to my boss
> * after having enough of the game and reject his
>   envelope asking for list-removal by pretending
>   that my server sends bounces to all his list mails while
>   in fact it only rejects private email
> 
> https://mail-archives.apache.org/mod_mbox/spamassassin-users/201608.mbox/%3cbb0c1584-f56a-4c1e-8b58-0d4b7d9a8...@cwis.biz%3E
> 
> 
> the reason for that personal hate (statet on a different list) is that
> repsone from last year: https://marc.info/?l=mysql=144526386203911=4
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

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

Re: MySQL Platform Migration

[Reindl Harald]

Am 13.09.2016 um 13:47 schrieb Ryan Coleman:

Right...

I don't like seeing people being shredded by a high strung personality because 
they simply don't understand things at your level, Reiny.


the message where you responded with "jackasses" was actually nice and 
you are hardly at the position call me "Reiny"



Be nice to people and I won't say anything. Be an ass and I will.


mind your own dirt - you are not in the position to play internet police

especially when *you* are the guy with wordings like "jackasses" and 
"Your proven track record of asshole first" and i assure you the next 
time you start the same game as 08/2015 with 70 mails within 3 days 
which deserved a special folder in my archive you will regret it deeply



On Sep 13, 2016, at 06:39, Reindl Harald <h.rei...@thelounge.net> wrote:


Am 13.09.2016 um 12:13 schrieb Johan De Meersman:
- Original Message -

From: "Ryan Coleman" <ryan.cole...@cwis.biz>
Subject: Re: MySQL Platform Migration

Because they want to be belittled by european jackasses online.


The thing is, while he has a bit of a rough edge, his technical advice is 
always solid. What has your contribution been, outside of insulting him, and 
for some reason, an entire continent?


this guy is just mentally ill and the next steps are:

* starting off-list mails (already happened)
* can't stand the repsone
* add a list-moderator to CC
* setup something in his MUA which send all read messages back
 to me (includig old ones from other lists he reads again)
* can't stand the response to that action
* forwards that responses to my boss
* after having enough of the game and reject his
 envelope asking for list-removal by pretending
 that my server sends bounces to all his list mails while
 in fact it only rejects private email

https://mail-archives.apache.org/mod_mbox/spamassassin-users/201608.mbox/%3cbb0c1584-f56a-4c1e-8b58-0d4b7d9a8...@cwis.biz%3E


the reason for that personal hate (statet on a different list) is that repsone from 
last year: https://marc.info/?l=mysql=144526386203911=4


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

Re: MySQL Platform Migration

[Ryan Coleman]

Right...

I don't like seeing people being shredded by a high strung personality because 
they simply don't understand things at your level, Reiny. 

Be nice to people and I won't say anything. Be an ass and I will. 

> On Sep 13, 2016, at 06:39, Reindl Harald <h.rei...@thelounge.net> wrote:
> 
> 
> 
>> Am 13.09.2016 um 12:13 schrieb Johan De Meersman:
>> - Original Message -
>>> From: "Ryan Coleman" <ryan.cole...@cwis.biz>
>>> Subject: Re: MySQL Platform Migration
>>> 
>>> Because they want to be belittled by european jackasses online.
>> 
>> The thing is, while he has a bit of a rough edge, his technical advice is 
>> always solid. What has your contribution been, outside of insulting him, and 
>> for some reason, an entire continent?
> 
> this guy is just mentally ill and the next steps are:
> 
> * starting off-list mails (already happened)
> * can't stand the repsone
> * add a list-moderator to CC
> * setup something in his MUA which send all read messages back
>  to me (includig old ones from other lists he reads again)
> * can't stand the response to that action
> * forwards that responses to my boss
> * after having enough of the game and reject his
>  envelope asking for list-removal by pretending
>  that my server sends bounces to all his list mails while
>  in fact it only rejects private email
> 
> https://mail-archives.apache.org/mod_mbox/spamassassin-users/201608.mbox/%3cbb0c1584-f56a-4c1e-8b58-0d4b7d9a8...@cwis.biz%3E
> 
> 
> the reason for that personal hate (statet on a different list) is that 
> repsone from last year: https://marc.info/?l=mysql=144526386203911=4
> 
> -- 
> 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: MySQL Platform Migration

[Reindl Harald]

Am 13.09.2016 um 12:13 schrieb Johan De Meersman:

- Original Message -

From: "Ryan Coleman" <ryan.cole...@cwis.biz>
Subject: Re: MySQL Platform Migration

Because they want to be belittled by european jackasses online.


The thing is, while he has a bit of a rough edge, his technical advice is 
always solid. What has your contribution been, outside of insulting him, and 
for some reason, an entire continent?


this guy is just mentally ill and the next steps are:

* starting off-list mails (already happened)
* can't stand the repsone
* add a list-moderator to CC
* setup something in his MUA which send all read messages back
  to me (includig old ones from other lists he reads again)
* can't stand the response to that action
* forwards that responses to my boss
* after having enough of the game and reject his
  envelope asking for list-removal by pretending
  that my server sends bounces to all his list mails while
  in fact it only rejects private email

https://mail-archives.apache.org/mod_mbox/spamassassin-users/201608.mbox/%3cbb0c1584-f56a-4c1e-8b58-0d4b7d9a8...@cwis.biz%3E


the reason for that personal hate (statet on a different list) is that 
repsone from last year: https://marc.info/?l=mysql=144526386203911=4


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

Re: MySQL Platform Migration

[Johan De Meersman]

- Original Message -
> From: "Reindl Harald" <h.rei...@thelounge.net>
> Subject: Re: MySQL Platform Migration
> 
> we discussed this multiple times here

True; but new people on the list may not have seen that.


> when you *copy* the datadir on the target machine nobody eats your data
> on the old one away and hence you can try out how it behaves on the new
> machine without any risk

Again, true; but I can well imagine scenarios with relatively huge datadirs and 
very stringent uptime requirements, so you only get the one window to take the 
system down and cold-copy the datafiles. And, yes, that's what slaves are for, 
but if there is no existing slave you run into the same problem again.

Circumstances vary, and asking doesn't hurt on most mailing lists :-p


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

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

Re: MySQL Platform Migration

[Johan De Meersman]

- Original Message -
> From: "Matthias Schmidt" <b...@admilon.net>
> Subject: Re: MySQL Platform Migration
> 
> I had to make certain adjustements to the config file thou.

Interesting - what changes did you have to make? I would expect most things to 
just keep working as they are, unless you were using something like the 
Federated engine that got disabled by default quite a while ago.


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

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

Re: MySQL Platform Migration

[Reindl Harald]

Am 13.09.2016 um 12:09 schrieb Johan De Meersman:

- Original Message -

From: "Reindl Harald" <h.rei...@thelounge.net>
Subject: Re: MySQL Platform Migration

why shouldn't it when the identical software is running?
it's just a bunch of files used by mysql


Little/big endianness, for one, although I seem to recall, and your later mail 
confirms, that that is not an issue for MySQL datafiles - and Mac has gone 
Intel years ago anyway.


we discussed this multiple times here


There's no issue in this case; but it's not always as simple as 'same software, 
same binary files'.


that's why you try things out - takes not longer than write 3 mails and 
since you don't touch the original data no risk involved



One thing that is worth looking at, however, is users with the IP/hostname of 
the old host in them - those may need to be changed if the related software is 
also moved.

Also, some Linux distributions do specific setup - Debian, for one, sets up a 
debian-sys-maint user that gets used for clean startup/shutdown/upgrade tasks. 
Both copying over the datafiles and importing a mysqldump from the old db will 
destroy that user entry, so it needs to be recreated manually afterwards. Other 
distributions may or may not have their own quirks.


as said copy it to the new machine and you are done - why do people
these days not just try out things, look if it works and when it don't
asking questions?


Eh. Sometimes it's comforting to get someone else's opinion before messing with 
production systems you're not an expert on


really?

when you *copy* the datadir on the target machine nobody eats your data 
on the old one away and hence you can try out how it behaves on the new 
machine without any risk


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

Re: MySQL Platform Migration

[Johan De Meersman]

- Original Message -
> From: "Ryan Coleman" <ryan.cole...@cwis.biz>
> Subject: Re: MySQL Platform Migration
> 
> Because they want to be belittled by european jackasses online.

The thing is, while he has a bit of a rough edge, his technical advice is 
always solid. What has your contribution been, outside of insulting him, and 
for some reason, an entire continent?


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

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

Re: MySQL Platform Migration

[Johan De Meersman]

- Original Message -
> From: "Reindl Harald" <h.rei...@thelounge.net>
> Subject: Re: MySQL Platform Migration
> 
> why shouldn't it when the identical software is running?
> it's just a bunch of files used by mysql

Little/big endianness, for one, although I seem to recall, and your later mail 
confirms, that that is not an issue for MySQL datafiles - and Mac has gone 
Intel years ago anyway.

Some software, but again, not MySQL, also writes datafiles differently 
depending on the architecture they're on. Had shenanigans some years ago simply 
moving (non-mysql) datafiles between 32- and 64-bit platforms.

There's no issue in this case; but it's not always as simple as 'same software, 
same binary files'.


One thing that is worth looking at, however, is users with the IP/hostname of 
the old host in them - those may need to be changed if the related software is 
also moved.

Also, some Linux distributions do specific setup - Debian, for one, sets up a 
debian-sys-maint user that gets used for clean startup/shutdown/upgrade tasks. 
Both copying over the datafiles and importing a mysqldump from the old db will 
destroy that user entry, so it needs to be recreated manually afterwards. Other 
distributions may or may not have their own quirks.


> as said copy it to the new machine and you are done - why do people
> these days not just try out things, look if it works and when it don't
> asking questions?

Eh. Sometimes it's comforting to get someone else's opinion before messing with 
production systems you're not an expert on.



-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

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

Re: MySQL Platform Migration

[Matthias Schmidt]

Hello,

I’m quite new to this list and I’m surprised about the vocabulary used on this 
list by certain people, who besides that do not add any productive idea to the 
problem asked by the OT.

Besides that I upgraded just from MacOS10.6 -> 10.11 and copying just the data 
folder did the job.
As I was using also a newer version of mysql I ran mysql_upgrade -u root -p 
after starting mysql and all is now running fine.
I had to make certain adjustements to the config file thou.
imho a change from Darwin to Linux (which one) isn’t that much different.

cheers
Matthias


> Am 12.09.2016 um 22:06 schrieb Ryan Coleman :
> 
> Dear sir,
> 
> You do not realize that there aren’t always sys admins on these lists. Your 
> proven track record of asshole first, kid gloves later drives people away.
> 
> Your fight to change mailing lists is one which only you seem to share.
> 
> Goodnight.
> 
>> On Sep 12, 2016, at 7:27 AM, Reindl Harald  wrote:
>> 
>> first: get rid of "reply-all"o n mailing-lists, you break others 
>> "reply-list" button with the second copy which arrives usually faster the 
>> the list one
>> 
>> Am 12.09.2016 um 13:35 schrieb Harrie Robins:
>>> It is bad practice to do so in my eyes (and yes that is an *opinion*).
>>> When you advice people, instruct them to take the proven route, as
>>> described in the mysql documentation
>> 
>> proven route?
>> 
>> proven is that you can *test* how it works out by
>> * hot rsync
>> * shut down the old server
>> * cold rsync
>> * start the old server
>> which is scriptable to minimize downtime
>> 
>> after that (while the old machine is still in production) you figure out 
>> what needs to get adopted in the configuration
>> 
>> then you test your software, prove that everything works fine
>> 
>> in the meantime you can test as often as you want the hot-cold-rsync to 
>> refresh the mysql databases from production - and *now you have proven* that 
>> everything works
>> 
>> since you have proven that successful you can write a final script which 
>> does the sync (over ssh with certificates) a last time, restart the old 
>> servers network servioce with a prepared network address and on the new 
>> server take over the old ip adress
>> 
>> and trust me: that way you minimize downtime, have a proven *by yourself* 
>> way to go which works, is tested and when correct done nobody notices that 
>> the machine and undrlying operating system changed
>> 
>> after doing that dozens of times for thousands of mysql databases i know 
>> what i am doing and call bad practice ways which take depending on database 
>> sizes hours and beware god something goes wrong with your dump
>> 
>>> Second, mastering mysqldump should be golden standard for any DBA.
>>> Telling someone that asks for guidance to simply copy around some files
>>> is bad practice as you do not know the level of expertise involved.
>> 
>> the golden standard for any sysadmin is to know where his datafiles and 
>> configuration files are stored and how they work together - independent of 
>> the software type
>> 
>>> Regards,
>>> Harrie
>>> 
>>> On 12 September 2016 at 11:03, Reindl Harald >> > wrote:
>>> 
>>> 
>>> 
>>>   Am 12.09.2016 um 10:48 schrieb Harrie Robins:
>>> 
>>>   Ok let's drop this. Simply copying files to migrate a server is
>>>   not the
>>>   approach to take (in my humble opinion)
>>> 
>>> 
>>>   And why?
>>> 
>>>   When you start with "Get the same release-version of mysql running
>>>   on the target platform" and dump/load what's the point in playing
>>>   around with dump-files when you hava binary datafiles which can be
>>>   used on the destination
>>> 
>>>   and no - i am not talking about theory
>>> 
>>>   i migrated a hosting-infrastructure with dozens of servers from OSX
>>>   PPC to OSX Intel and later to Linux x86_64 with just rsync the
>>>   mysql-datadir
>> 
>> --
>> 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
> 



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: MySQL Platform Migration

[Reindl Harald]

Am 12.09.2016 um 15:06 schrieb Ryan Coleman:

Dear sir,

You do not realize that there aren’t always sys admins on these lists


how does that change the fact that it just works and it did work from 
day one as described long before i became a sysadmin and that it now 
works over 15 years from mysql3 to mariadb 10.1.x


and it's pretty logical that it works just because it's a *binary 
identical copy* where mysqld has no chance at all to know that it lived 
on a different machine first



Your proven track record of asshole first, kid gloves later drives people away.


MIND YOUR OWN BUSINESS


Your fight to change mailing lists is one which only you seem to share.


what fight?
what is the point in producing mail copies?


Goodnight.


to say it with your words: go away!


On Sep 12, 2016, at 7:27 AM, Reindl Harald  wrote:

first: get rid of "reply-all"o n mailing-lists, you break others "reply-list" 
button with the second copy which arrives usually faster the the list one

Am 12.09.2016 um 13:35 schrieb Harrie Robins:

It is bad practice to do so in my eyes (and yes that is an *opinion*).
When you advice people, instruct them to take the proven route, as
described in the mysql documentation


proven route?

proven is that you can *test* how it works out by
* hot rsync
* shut down the old server
* cold rsync
* start the old server
which is scriptable to minimize downtime

after that (while the old machine is still in production) you figure out what 
needs to get adopted in the configuration

then you test your software, prove that everything works fine

in the meantime you can test as often as you want the hot-cold-rsync to refresh 
the mysql databases from production - and *now you have proven* that everything 
works

since you have proven that successful you can write a final script which does 
the sync (over ssh with certificates) a last time, restart the old servers 
network servioce with a prepared network address and on the new server take 
over the old ip adress

and trust me: that way you minimize downtime, have a proven *by yourself* way 
to go which works, is tested and when correct done nobody notices that the 
machine and undrlying operating system changed

after doing that dozens of times for thousands of mysql databases i know what i 
am doing and call bad practice ways which take depending on database sizes 
hours and beware god something goes wrong with your dump


Second, mastering mysqldump should be golden standard for any DBA.
Telling someone that asks for guidance to simply copy around some files
is bad practice as you do not know the level of expertise involved.


the golden standard for any sysadmin is to know where his datafiles and 
configuration files are stored and how they work together - independent of the 
software type


Regards,
Harrie

On 12 September 2016 at 11:03, Reindl Harald > wrote:



   Am 12.09.2016 um 10:48 schrieb Harrie Robins:

   Ok let's drop this. Simply copying files to migrate a server is
   not the
   approach to take (in my humble opinion)


   And why?

   When you start with "Get the same release-version of mysql running
   on the target platform" and dump/load what's the point in playing
   around with dump-files when you hava binary datafiles which can be
   used on the destination

   and no - i am not talking about theory

   i migrated a hosting-infrastructure with dozens of servers from OSX
   PPC to OSX Intel and later to Linux x86_64 with just rsync the
   mysql-datadir


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

Re: MySQL Platform Migration

[Ryan Coleman]

Dear sir,

You do not realize that there aren’t always sys admins on these lists. Your 
proven track record of asshole first, kid gloves later drives people away.

Your fight to change mailing lists is one which only you seem to share.

Goodnight. 

> On Sep 12, 2016, at 7:27 AM, Reindl Harald  wrote:
> 
> first: get rid of "reply-all"o n mailing-lists, you break others "reply-list" 
> button with the second copy which arrives usually faster the the list one
> 
> Am 12.09.2016 um 13:35 schrieb Harrie Robins:
>> It is bad practice to do so in my eyes (and yes that is an *opinion*).
>> When you advice people, instruct them to take the proven route, as
>> described in the mysql documentation
> 
> proven route?
> 
> proven is that you can *test* how it works out by
> * hot rsync
> * shut down the old server
> * cold rsync
> * start the old server
> which is scriptable to minimize downtime
> 
> after that (while the old machine is still in production) you figure out what 
> needs to get adopted in the configuration
> 
> then you test your software, prove that everything works fine
> 
> in the meantime you can test as often as you want the hot-cold-rsync to 
> refresh the mysql databases from production - and *now you have proven* that 
> everything works
> 
> since you have proven that successful you can write a final script which does 
> the sync (over ssh with certificates) a last time, restart the old servers 
> network servioce with a prepared network address and on the new server take 
> over the old ip adress
> 
> and trust me: that way you minimize downtime, have a proven *by yourself* way 
> to go which works, is tested and when correct done nobody notices that the 
> machine and undrlying operating system changed
> 
> after doing that dozens of times for thousands of mysql databases i know what 
> i am doing and call bad practice ways which take depending on database sizes 
> hours and beware god something goes wrong with your dump
> 
>> Second, mastering mysqldump should be golden standard for any DBA.
>> Telling someone that asks for guidance to simply copy around some files
>> is bad practice as you do not know the level of expertise involved.
> 
> the golden standard for any sysadmin is to know where his datafiles and 
> configuration files are stored and how they work together - independent of 
> the software type
> 
>> Regards,
>> Harrie
>> 
>> On 12 September 2016 at 11:03, Reindl Harald > > wrote:
>> 
>> 
>> 
>>Am 12.09.2016 um 10:48 schrieb Harrie Robins:
>> 
>>Ok let's drop this. Simply copying files to migrate a server is
>>not the
>>approach to take (in my humble opinion)
>> 
>> 
>>And why?
>> 
>>When you start with "Get the same release-version of mysql running
>>on the target platform" and dump/load what's the point in playing
>>around with dump-files when you hava binary datafiles which can be
>>used on the destination
>> 
>>and no - i am not talking about theory
>> 
>>i migrated a hosting-infrastructure with dozens of servers from OSX
>>PPC to OSX Intel and later to Linux x86_64 with just rsync the
>>mysql-datadir
> 
> -- 
> 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: MySQL Platform Migration

[Reindl Harald]

first: get rid of "reply-all"o n mailing-lists, you break others 
"reply-list" button with the second copy which arrives usually faster 
the the list one


Am 12.09.2016 um 13:35 schrieb Harrie Robins:

It is bad practice to do so in my eyes (and yes that is an *opinion*).
When you advice people, instruct them to take the proven route, as
described in the mysql documentation


proven route?

proven is that you can *test* how it works out by
 * hot rsync
 * shut down the old server
 * cold rsync
 * start the old server
which is scriptable to minimize downtime

after that (while the old machine is still in production) you figure out 
what needs to get adopted in the configuration


then you test your software, prove that everything works fine

in the meantime you can test as often as you want the hot-cold-rsync to 
refresh the mysql databases from production - and *now you have proven* 
that everything works


since you have proven that successful you can write a final script which 
does the sync (over ssh with certificates) a last time, restart the old 
servers network servioce with a prepared network address and on the new 
server take over the old ip adress


and trust me: that way you minimize downtime, have a proven *by 
yourself* way to go which works, is tested and when correct done nobody 
notices that the machine and undrlying operating system changed


after doing that dozens of times for thousands of mysql databases i know 
what i am doing and call bad practice ways which take depending on 
database sizes hours and beware god something goes wrong with your dump



Second, mastering mysqldump should be golden standard for any DBA.
Telling someone that asks for guidance to simply copy around some files
is bad practice as you do not know the level of expertise involved.


the golden standard for any sysadmin is to know where his datafiles and 
configuration files are stored and how they work together - independent 
of the software type



Regards,
Harrie

On 12 September 2016 at 11:03, Reindl Harald > wrote:



Am 12.09.2016 um 10:48 schrieb Harrie Robins:

Ok let's drop this. Simply copying files to migrate a server is
not the
approach to take (in my humble opinion)


And why?

When you start with "Get the same release-version of mysql running
on the target platform" and dump/load what's the point in playing
around with dump-files when you hava binary datafiles which can be
used on the destination

and no - i am not talking about theory

i migrated a hosting-infrastructure with dozens of servers from OSX
PPC to OSX Intel and later to Linux x86_64 with just rsync the
mysql-datadir


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

Re: MySQL Platform Migration

[Harrie Robins]

Hi,
.
It is bad practice to do so in my eyes (and yes that is an *opinion*). When
you advice people, instruct them to take the proven route, as described in
the mysql documentation.
Second, mastering mysqldump should be golden standard for any DBA. Telling
someone that asks for guidance to simply copy around some files is bad
practice as you do not know the level of expertise involved.

Regards,

Harrie

On 12 September 2016 at 11:03, Reindl Harald  wrote:

>
>
> Am 12.09.2016 um 10:48 schrieb Harrie Robins:
>
>> Ok let's drop this. Simply copying files to migrate a server is not the
>> approach to take (in my humble opinion)
>>
>
> And why?
>
> When you start with "Get the same release-version of mysql running on the
> target platform" and dump/load what's the point in playing around with
> dump-files when you hava binary datafiles which can be used on the
> destination
>
> and no - i am not talking about theory
>
> i migrated a hosting-infrastructure with dozens of servers from OSX PPC to
> OSX Intel and later to Linux x86_64 with just rsync the mysql-datadir
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: MySQL Platform Migration

[Reindl Harald]

Am 12.09.2016 um 10:48 schrieb Harrie Robins:

Ok let's drop this. Simply copying files to migrate a server is not the
approach to take (in my humble opinion)


And why?

When you start with "Get the same release-version of mysql running on 
the target platform" and dump/load what's the point in playing around 
with dump-files when you hava binary datafiles which can be used on the 
destination


and no - i am not talking about theory

i migrated a hosting-infrastructure with dozens of servers from OSX PPC 
to OSX Intel and later to Linux x86_64 with just rsync the mysql-datadir


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

Re: MySQL Platform Migration

[Harrie Robins]

Ok let's drop this. Simply copying files to migrate a server is not the
approach to take (in my humble opinion).

First asses the situation, a platform migration? Get the same
release-version of mysql running on the target platform. Dump your mysql
database the with the excellent tool mysqldump which is shipped with mysql:
mysqldump -u root -p  --all-databases > target.sql

Now import the databases: mysql -u root -p --execute="source
database_dump.sql" --force"

This will overwrite all databases including the mysql-table which contains
users. Make sure to run mysql_upgrade next to fix any inconsistancy's.
Restart mysql or flush privs > done.

Regards,

Harrie

On 11 September 2016 at 21:53, Martin Mueller <
martinmuel...@northwestern.edu> wrote:

> Not very helpful rhetoric on either side of the fence. Every time an
> unnecessary comment is made, some time cost is imposed on the folks who
> make the mistake of opening the email thinking there was some useful
> advice.
>
> Civilization and good manners consist very largely of bitten tongues. And
> biting your tongue might be a good thing for others
>
> MM
> Martin Mueller
>
> Professor emeritus of English and Classics
> Northwestern University
>
>
>
>
> On 9/11/16 12:27 PM, "Ryan Coleman" <ryan.cole...@cwis.biz> wrote:
>
> >Because they want to be belittled by european jackasses online.
> >
> >On Sep 10, 2016, at 11:56 PM, Reindl Harald <h.rei...@thelounge.net>
> >wrote:
> >>
> >>
> >> Am 11.09.2016 um 06:36 schrieb Suresh Rajagopal:
> >>> Is the mysql datafile compatible with different operation system ? I
> >>>have not done this in the past.
> >>
> >> why shouldn't it when the identical software is running?
> >> it's just a bunch of files used by mysql
> >>
> >> as said copy it to the new machine and you are done - why do people
> >>these days not just try out things, look if it works and when it don't
> >>asking questions?
> >>
> >>> - Original Message -
> >>> From: Reindl Harald <h.rei...@thelounge.net>
> >>> To: mysql@lists.mysql.com
> >>> Sent: Saturday, September 10, 2016 7:19 PM
> >>> Subject: Re: MySQL Platform Migration
> >>>
> >>> Am 11.09.2016 um 00:26 schrieb Suresh Rajagopal:
> >>>> Is there any documentation for migrating mysql 5.0 database from
> >>>>darwin to Linux
> >>>
> >>> what do you need to migrate?
> >>>
> >>> shutdown mysqld, copy the datadir to the new machine, start mysqld with
> >>> adopted configuration on the new machine - done
> >>>
> >>> P.S:
> >>> don't cross-post on mysql and mariadb list
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives:
> >>https://urldefense.proofpoint.com/v2/url?u=http-
> 3A__lists.mysql.com_mysql
> >>=CwIFAg=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=
> rG8zxOdssqSzDRz
> >>4x1GLlmLOW60xyVXydxwnJZpkxbk=cQyy1LyUdO1_o4_3K62-
> IxYpnXPNhcErgHZ2NXXmnT
> >>c=tiL5vs-xwiEX93V1KAJ7sw4QOg-ZD1qBrflS_f0lMow=
> >> To unsubscribe:
> >>https://urldefense.proofpoint.com/v2/url?u=http-
> 3A__lists.mysql.com_mysql
> >>=CwIFAg=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=
> rG8zxOdssqSzDRz
> >>4x1GLlmLOW60xyVXydxwnJZpkxbk=cQyy1LyUdO1_o4_3K62-
> IxYpnXPNhcErgHZ2NXXmnT
> >>c=tiL5vs-xwiEX93V1KAJ7sw4QOg-ZD1qBrflS_f0lMow=
> >>
> >
> >
> >--
> >MySQL General Mailing List
> >For list archives:
> >https://urldefense.proofpoint.com/v2/url?u=http-
> 3A__lists.mysql.com_mysql&
> >d=CwIFAg=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=
> rG8zxOdssqSzDRz4x
> >1GLlmLOW60xyVXydxwnJZpkxbk=cQyy1LyUdO1_o4_3K62-
> IxYpnXPNhcErgHZ2NXXmnTc
> >=tiL5vs-xwiEX93V1KAJ7sw4QOg-ZD1qBrflS_f0lMow=
> >To unsubscribe:
> >https://urldefense.proofpoint.com/v2/url?u=http-
> 3A__lists.mysql.com_mysql&
> >d=CwIFAg=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=
> rG8zxOdssqSzDRz4x
> >1GLlmLOW60xyVXydxwnJZpkxbk=cQyy1LyUdO1_o4_3K62-
> IxYpnXPNhcErgHZ2NXXmnTc
> >=tiL5vs-xwiEX93V1KAJ7sw4QOg-ZD1qBrflS_f0lMow=
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: MySQL Platform Migration

[Reindl Harald]

Am 11.09.2016 um 20:27 schrieb Ryan Coleman:

Because they want to be belittled by european jackasses online.


if you insist in not get any mail from me just shut up when nobody asked 
you - mind your own business



On Sep 10, 2016, at 11:56 PM, Reindl Harald  wrote:



Am 11.09.2016 um 06:36 schrieb Suresh Rajagopal:

Is the mysql datafile compatible with different operation system ? I have not 
done this in the past.


why shouldn't it when the identical software is running?
it's just a bunch of files used by mysql

as said copy it to the new machine and you are done - why do people these days 
not just try out things, look if it works and when it don't asking questions?


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

Re: MySQL Platform Migration

[Martin Mueller]

Not very helpful rhetoric on either side of the fence. Every time an
unnecessary comment is made, some time cost is imposed on the folks who
make the mistake of opening the email thinking there was some useful
advice. 

Civilization and good manners consist very largely of bitten tongues. And
biting your tongue might be a good thing for others

MM
Martin Mueller

Professor emeritus of English and Classics
Northwestern University




On 9/11/16 12:27 PM, "Ryan Coleman" <ryan.cole...@cwis.biz> wrote:

>Because they want to be belittled by european jackasses online.
>
>On Sep 10, 2016, at 11:56 PM, Reindl Harald <h.rei...@thelounge.net>
>wrote:
>> 
>> 
>> Am 11.09.2016 um 06:36 schrieb Suresh Rajagopal:
>>> Is the mysql datafile compatible with different operation system ? I
>>>have not done this in the past.
>> 
>> why shouldn't it when the identical software is running?
>> it's just a bunch of files used by mysql
>> 
>> as said copy it to the new machine and you are done - why do people
>>these days not just try out things, look if it works and when it don't
>>asking questions?
>> 
>>> - Original Message -
>>> From: Reindl Harald <h.rei...@thelounge.net>
>>> To: mysql@lists.mysql.com
>>> Sent: Saturday, September 10, 2016 7:19 PM
>>> Subject: Re: MySQL Platform Migration
>>> 
>>> Am 11.09.2016 um 00:26 schrieb Suresh Rajagopal:
>>>> Is there any documentation for migrating mysql 5.0 database from
>>>>darwin to Linux
>>> 
>>> what do you need to migrate?
>>> 
>>> shutdown mysqld, copy the datadir to the new machine, start mysqld with
>>> adopted configuration on the new machine - done
>>> 
>>> P.S:
>>> don't cross-post on mysql and mariadb list
>> 
>> -- 
>> MySQL General Mailing List
>> For list archives:
>>https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.mysql.com_mysql
>>=CwIFAg=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=rG8zxOdssqSzDRz
>>4x1GLlmLOW60xyVXydxwnJZpkxbk=cQyy1LyUdO1_o4_3K62-IxYpnXPNhcErgHZ2NXXmnT
>>c=tiL5vs-xwiEX93V1KAJ7sw4QOg-ZD1qBrflS_f0lMow=
>> To unsubscribe: 
>>https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.mysql.com_mysql
>>=CwIFAg=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=rG8zxOdssqSzDRz
>>4x1GLlmLOW60xyVXydxwnJZpkxbk=cQyy1LyUdO1_o4_3K62-IxYpnXPNhcErgHZ2NXXmnT
>>c=tiL5vs-xwiEX93V1KAJ7sw4QOg-ZD1qBrflS_f0lMow=
>> 
>
>
>--
>MySQL General Mailing List
>For list archives:
>https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.mysql.com_mysql;
>d=CwIFAg=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=rG8zxOdssqSzDRz4x
>1GLlmLOW60xyVXydxwnJZpkxbk=cQyy1LyUdO1_o4_3K62-IxYpnXPNhcErgHZ2NXXmnTc
>=tiL5vs-xwiEX93V1KAJ7sw4QOg-ZD1qBrflS_f0lMow=
>To unsubscribe:   
>https://urldefense.proofpoint.com/v2/url?u=http-3A__lists.mysql.com_mysql;
>d=CwIFAg=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=rG8zxOdssqSzDRz4x
>1GLlmLOW60xyVXydxwnJZpkxbk=cQyy1LyUdO1_o4_3K62-IxYpnXPNhcErgHZ2NXXmnTc
>=tiL5vs-xwiEX93V1KAJ7sw4QOg-ZD1qBrflS_f0lMow=
>


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

Re: MySQL Platform Migration

[Ryan Coleman]

Because they want to be belittled by european jackasses online.

On Sep 10, 2016, at 11:56 PM, Reindl Harald <h.rei...@thelounge.net> wrote:
> 
> 
> Am 11.09.2016 um 06:36 schrieb Suresh Rajagopal:
>> Is the mysql datafile compatible with different operation system ? I have 
>> not done this in the past.
> 
> why shouldn't it when the identical software is running?
> it's just a bunch of files used by mysql
> 
> as said copy it to the new machine and you are done - why do people these 
> days not just try out things, look if it works and when it don't asking 
> questions?
> 
>> - Original Message -
>> From: Reindl Harald <h.rei...@thelounge.net>
>> To: mysql@lists.mysql.com
>> Sent: Saturday, September 10, 2016 7:19 PM
>> Subject: Re: MySQL Platform Migration
>> 
>> Am 11.09.2016 um 00:26 schrieb Suresh Rajagopal:
>>> Is there any documentation for migrating mysql 5.0 database from darwin to 
>>> Linux
>> 
>> what do you need to migrate?
>> 
>> shutdown mysqld, copy the datadir to the new machine, start mysqld with
>> adopted configuration on the new machine - done
>> 
>> P.S:
>> don't cross-post on mysql and mariadb list
> 
> -- 
> 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: MySQL Platform Migration

[Reindl Harald]

Am 11.09.2016 um 06:36 schrieb Suresh Rajagopal:

Is the mysql datafile compatible with different operation system ? I have not 
done this in the past.


why shouldn't it when the identical software is running?
it's just a bunch of files used by mysql

as said copy it to the new machine and you are done - why do people 
these days not just try out things, look if it works and when it don't 
asking questions?



- Original Message -
From: Reindl Harald <h.rei...@thelounge.net>
To: mysql@lists.mysql.com
Sent: Saturday, September 10, 2016 7:19 PM
Subject: Re: MySQL Platform Migration

Am 11.09.2016 um 00:26 schrieb Suresh Rajagopal:

Is there any documentation for migrating mysql 5.0 database from darwin to Linux


what do you need to migrate?

shutdown mysqld, copy the datadir to the new machine, start mysqld with
adopted configuration on the new machine - done

P.S:
don't cross-post on mysql and mariadb list


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

Re: MySQL Platform Migration

[Suresh Rajagopal]

Hi Reindl,

Is the mysql datafile compatible with different operation system ? I have not 
done this in the past.

Regarding cross-post, mysql list was bouncing due to html email format. Didn't 
realize until today. That's why posted in mariadb.

Thanks
Suresh



- Original Message -
From: Reindl Harald <h.rei...@thelounge.net>
To: mysql@lists.mysql.com
Sent: Saturday, September 10, 2016 7:19 PM
Subject: Re: MySQL Platform Migration






Am 11.09.2016 um 00:26 schrieb Suresh Rajagopal:
> Is there any documentation for migrating mysql 5.0 database from darwin to 
> Linux

what do you need to migrate?

shutdown mysqld, copy the datadir to the new machine, stat mysqld with 
adopted configuration ion the new machine - done

P.S:
don't cross-post on mysql and mariadb list

-- 
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: MySQL Platform Migration

[Reindl Harald]

Am 11.09.2016 um 00:26 schrieb Suresh Rajagopal:

Is there any documentation for migrating mysql 5.0 database from darwin to Linux


what do you need to migrate?

shutdown mysqld, copy the datadir to the new machine, stat mysqld with 
adopted configuration ion the new machine - done


P.S:
don't cross-post on mysql and mariadb list

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

Re: MySql Swapping issues

[Jesper Wisborg Krogh]

Hi Machiel,

On 8/09/2016 05:24, Machiel Richards wrote:

...

Biggest issue at the moment is mysql taking all physical memory until
nothing left, the starts swapping as well until that is completely used up.


Since you are using MySQL 5.7, you can consider enabling memory 
instrumentation in the Performance Schema:


UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' 
WHERE NAME LIKE 'memory/%';


That may help to locate where the memory is used.

To monitor the memory usage use either the sys.memory_% views 
(https://dev.mysql.com/doc/refman/5.7/en/sys-schema-views.html) or the 
Performance Schema summary tables 
(https://dev.mysql.com/doc/refman/5.7/en/memory-summary-tables.html).


Note that there is a little extra overhead instrumenting the memory.

Best regards,
Jesper Krogh
MySQL Support


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

Re: MySql Swapping issues

[Johan De Meersman]

- Original Message -
> From: "Machiel Richards" <machiel.richa...@gmail.com>
> Subject: Re: MySql Swapping issues
> 
> When I monitor the MySQL processlists, there is constantly about 30-35 
> processes
> and half of them never change simply showing as :
> 
> Connect | 124678 | Waiting for an event from Coordinator

15-ish? That feels like a lot, but then I'm not particularly familiar with 
parallel replication. Might be normal for your configuration.


> The other half of the processes are queries and inserts and I can see those
> being processed and going off the list fairly regularly.

So those disconnect normally, which /should/ clean up any session memory.

I'm pretty much out of obvious things to point at. Random toughts:
 - typo in some dynamically-assigned buffer sizing (not a lot of those, though)
 - I have in the past seen memory use increase at times when the logs couldn't 
get flushed to disk; but I assume something like that would become apparent at 
restart time anyway
 - A memory leak of that size seems improbable, but it's always worth checking 
the bug reports for your server version or test other versions

Maybe someone else has some more ideas, or has seen similar behaviour?

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

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

Re: MySql Swapping issues

[Machiel Richards]

In terms of the amount of processes, that is the max amount ever seen. The
servers are monitored 24/7 so it is constantly under hawk eye from both
staff and monitoring systems.

The other memory values I was referring to are things like the open-table
cache, and other buffers as you assumed yes. They use innodb engine for
their databases.

In terms of setting up the cronjobs using ps, this has been done using
several methods and it always come down to only one process using all
memory and that is mysql server.

Even cpu usage is fairly high sometimes, however this was found to be when
they run large updates or reports.

Biggest issue at the moment is mysql taking all physical memory until
nothing left, the starts swapping as well until that is completely used up.

On 07 Sep 2016 15:47, "Johan De Meersman"  wrote:


- Original Message -
> From: "Machiel Richards" 
> Subject: MySql Swapping issues
>
>I had a look at the settings and the innodb buffer pool on one server
> is set to about 70% of the physical memory and the others to about 30% of
> physical memory.

Not unreasonable, especially given the memory sizes you give.


>All other buffer and memory relevant parameters are set to fairly low
> values as they were recently decreased with no difference in the symptoms.

Good, although 'fairly low' is vague :-) For most purposes, there's no need
to tune the specialised buffers at all. I assume you mean myisam key cache
and the like.


> In terms of server and queries, The smallest server have 64Gb of
> physical memory and the biggest server have 128Gb of physical memory and
> the biggest server database size is about 600Gb odd.

That's a large difference,but not necessarily a problem, as long as your
active dataset fits in the bufferpool.


> I had a look at the processes running and there are at best 38
> processes running including the replication processes.

Is that what you see whenever you look, or is it from a trending tool like
Munin or Cacti?

The former can be very deceiving, especially with connect-select-quit
applications like PHP sites. I strongly recommend setting up proper
trending if you don't have it, so you can see what's going on when you're
not looking, too - and compare to past activity.

Personally I use Munin; the standard plugins in there are a good base, but
there's a very good one at https://github.com/kjellm/munin-mysql. I have my
own fork of that, too, which contains a number of extra graphs that depend
on considerable modifications of the main module.

Cacti is pretty much just as good (and iirc the kjellm plugin is actually
based on a cacti plugin); I just prefer the way munin is managed.


>I do not see any other hardware related issues and swappiness settings
> have been configured to 1.

For DB-only servers (and really, production servers in general) I generally
opt to not have any swap at all. Once you start using it, it's a slow death
struggle anyway; better off to just have it die immediately and fix the
configuration.


>Any ideas , links, advice, etc... will be appreciated.

Memory creep is often hard to diagnose; set up a simple cronjob that runs
PS; sorts by memory use and outputs the top lines to a log every half hour
or so. You can then do some sed/awk/gnuplot magic on that to see what
process keeps growing.

If it turns out that it actually *is* the mysql server, that may be a
memory leak, but just as probably it could be a maintenance schedule
somewhere that suddenly bursts a couple of dozen connections, exhausting
server memory. Pretty hard to tell you more without telemetry :-)


--
Unhappiness is discouraged and will be corrected with kitten pictures.

Re: MySql Swapping issues

[Johan De Meersman]

- Original Message -
> From: "Machiel Richards" 
> Subject: MySql Swapping issues
> 
>I had a look at the settings and the innodb buffer pool on one server
> is set to about 70% of the physical memory and the others to about 30% of
> physical memory.

Not unreasonable, especially given the memory sizes you give.


>All other buffer and memory relevant parameters are set to fairly low
> values as they were recently decreased with no difference in the symptoms.

Good, although 'fairly low' is vague :-) For most purposes, there's no need to 
tune the specialised buffers at all. I assume you mean myisam key cache and the 
like.


> In terms of server and queries, The smallest server have 64Gb of
> physical memory and the biggest server have 128Gb of physical memory and
> the biggest server database size is about 600Gb odd.

That's a large difference,but not necessarily a problem, as long as your active 
dataset fits in the bufferpool.


> I had a look at the processes running and there are at best 38
> processes running including the replication processes.

Is that what you see whenever you look, or is it from a trending tool like 
Munin or Cacti?

The former can be very deceiving, especially with connect-select-quit 
applications like PHP sites. I strongly recommend setting up proper trending if 
you don't have it, so you can see what's going on when you're not looking, too 
- and compare to past activity.

Personally I use Munin; the standard plugins in there are a good base, but 
there's a very good one at https://github.com/kjellm/munin-mysql. I have my own 
fork of that, too, which contains a number of extra graphs that depend on 
considerable modifications of the main module.

Cacti is pretty much just as good (and iirc the kjellm plugin is actually based 
on a cacti plugin); I just prefer the way munin is managed.


>I do not see any other hardware related issues and swappiness settings
> have been configured to 1.

For DB-only servers (and really, production servers in general) I generally opt 
to not have any swap at all. Once you start using it, it's a slow death 
struggle anyway; better off to just have it die immediately and fix the 
configuration.


>Any ideas , links, advice, etc... will be appreciated.

Memory creep is often hard to diagnose; set up a simple cronjob that runs PS; 
sorts by memory use and outputs the top lines to a log every half hour or so. 
You can then do some sed/awk/gnuplot magic on that to see what process keeps 
growing.

If it turns out that it actually *is* the mysql server, that may be a memory 
leak, but just as probably it could be a maintenance schedule somewhere that 
suddenly bursts a couple of dozen connections, exhausting server memory. Pretty 
hard to tell you more without telemetry :-)


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

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

Re: mysql query for current date accounting returns NULL

[shawn l.green]

On 3/26/2016 4:36 PM, shawn l.green wrote:



On 3/25/2016 6:39 AM, JAHANZAIB SYED wrote:

I have Freeradius 2.x with MySQL 5.5 in Ubuntu.

I want to query user quota for current date. I am using following code

SELECT (SUM(acctinputoctets)+SUM(acctoutputoctets)) AS Total FROM
radacct where (acctstarttime between  DATE_FORMAT(NOW(),'%Y-%m-%d')
AND NOW() AND acctstoptime  between  DATE_FORMAT(NOW() ,'%Y-%m-%d')
AND NOW()) AND radacct.username='%{User-Name}'

It works fine if there is acctstoptime value in table. but if user
have not disconnected yet (and have no previous session for today) it
returns NULL.

So how can i can get the value even if user acttstoptime is null?




Try this...(using an earlier suggestion to the thread)

SELECT
  SUM(acctinputoctets + acctoutputoctets) AS Total
FROM radacct
WHERE
   radacct.username='%{User-Name}'
   AND acctstarttime BETWEEN CURDATE() AND NOW()
   AND (
 acctstoptime  <= NOW()
 OR acctstoptime IS NULL
   )

But in reality, can you have an acctstarttime that is >= NOW()? If not,
then you can also simplify that term to just



oops! one too many AND's

   AND AND acctstarttime >= CURDATE()


I meant to write
AND acctstarttime >= CURDATE()



and lose the BETWEEN comparison.



--
Shawn

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

Re: mysql query for current date accounting returns NULL

[shawn l.green]

On 3/25/2016 6:39 AM, JAHANZAIB SYED wrote:

I have Freeradius 2.x with MySQL 5.5 in Ubuntu.

I want to query user quota for current date. I am using following code

SELECT (SUM(acctinputoctets)+SUM(acctoutputoctets)) AS Total FROM radacct where 
(acctstarttime between  DATE_FORMAT(NOW(),'%Y-%m-%d') AND NOW() AND 
acctstoptime  between  DATE_FORMAT(NOW() ,'%Y-%m-%d') AND NOW()) AND 
radacct.username='%{User-Name}'

It works fine if there is acctstoptime value in table. but if user have not 
disconnected yet (and have no previous session for today) it returns NULL.

So how can i can get the value even if user acttstoptime is null?




Try this...(using an earlier suggestion to the thread)

SELECT
 SUM(acctinputoctets + acctoutputoctets) AS Total
FROM radacct
WHERE
  radacct.username='%{User-Name}'
  AND acctstarttime BETWEEN CURDATE() AND NOW()
  AND (
acctstoptime  <= NOW()
OR acctstoptime IS NULL
  )

But in reality, can you have an acctstarttime that is >= NOW()? If not, 
then you can also simplify that term to just


  AND AND acctstarttime >= CURDATE()

and lose the BETWEEN comparison.

--
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 query for current date accounting returns NULL

[Hal.sz S.ndor]

2016/03/25 06:39 ... JAHANZAIB SYED:

I want to query user quota for current date. I am using following code

SELECT SUM(acctinputoctets)+SUM(acctoutputoctets) AS Total FROM radacct where 
(acctstarttime between  DATE_FORMAT(NOW(),'%Y-%m-%d') AND NOW() AND 
acctstoptime between DATE_FORMAT(NOW() ,'%Y-%m-%d') AND NOW()) AND 
username='%{User-Name}'

It works fine if there is acctstoptime value in table. but if user have not 
disconnected yet (and have no previous session for today) it returns NULL.


That expression has problems. Not only it works only when both 
acctstarttime and acctstoptime are good, but only if they are on the 
same day, today.


> So how can i can get the value even if user acctstoptime is null?
Really, it is best to omit the test on "acctstoptime".

I don't like the form of the test, either. If "acctstarttime" is of 
DATETIME (or TIMESTAMP) type I like this better:

acctstarttime BETWEEN CURDATE() AND NOW()
otherwise
CAST(acctstarttime AS DATETIME) BETWEEN CURDATE() AND NOW()

You are also not GROUPing BY anything, which, strictly speakind, with 
SUM is bad SQL, but, of course, it works because only one value of 
"username" is sought.


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

Re: Mysql devel

[Reindl Harald]

Am 23.02.2016 um 13:12 schrieb Larry Martell:

On Tue, Feb 23, 2016 at 7:07 AM, Larry Martell  wrote:

On Tue, Feb 23, 2016 at 6:16 AM, Reindl Harald  wrote:


Am 23.02.2016 um 04:47 schrieb Larry Martell:


I am trying to install the mysql devel libs (so I can build MySQLdb).
This is on RHEL 6. It's failing as shown below. Can anyone help with
this?


you are mixing remi and other repos


I did this:

# yum shell --enablerepo=remi

remove php-mysql
install php-mysqlnd
run


Followed by this:

yum install mysql-devel
/usr/local/bin/pip2.7 install MySQL-python

Now the MySQL server is down and will not come up. It fails with this:

Fatal error: mysql.user table is damaged. Please run mysql_upgrade

It was running 5.1 before and now it's running 5.7.

But how can I run mysql_upgrade if I can't start the server?


Actually it was running 5.5 before:

Server version: 5.5.44 MySQL Community Server (GPL) by Remi


all your problems are coming from careless mix repos and allow updates 
without considering the result and read how to deal with 3rd party repos 
from the very beginning


downgrade and re-consider how you are doing upgrades
5.5 to 5.7 and skip 5.6 entirely is a bad idea
make sure that you don't mix mysql-packages from different repos

and RTFM https://wiki.centos.org/de/AdditionalResources/Repositories/
Les RPM de Remi repository - (See http://rpms.famillecollet.com/) Remi 
Collet maintains latest version of MySQL and PHP (backports of fedora 
RPM). Suggest caution if using this repo due to replacement of core packages


Third Party Repositories
WARNING: These repositories are not provided nor supported by CentOS. 
The CentOS project has no control over these sites. Many have their own 
mailing lists, IRC channels, issue trackers, etc. for support issues 
with their packages.


NOTE: If you are considering using a 3rd Party Repository, then you 
should seriously consider how to prevent unintended 'updates' from these 
side archives from over-writing some core part of CentOS. One approach 
is to only enable these archives from time to time, and generally leave 
them disabled. See: man yum


Another approach is to use the exclude= and includepkgs= options on a 
per sub-archive basis, in the matching .conf file found in 
/etc/yum.repos.d/ See: man yum.conf


The yum Priorities plug-in can prevent a 3rd party repository from 
replacing base packages, or prevent base/updates from replacing a 3rd 
party package.




signature.asc
Description: OpenPGP digital signature

Re: Mysql devel

[Larry Martell]

On Tue, Feb 23, 2016 at 7:07 AM, Larry Martell  wrote:
> On Tue, Feb 23, 2016 at 6:16 AM, Reindl Harald  wrote:
>>
>>
>> Am 23.02.2016 um 04:47 schrieb Larry Martell:
>>>
>>> I am trying to install the mysql devel libs (so I can build MySQLdb).
>>> This is on RHEL 6. It's failing as shown below. Can anyone help with
>>> this?
>>
>>
>> you are mixing remi and other repos
>
> I did this:
>
> # yum shell --enablerepo=remi
>> remove php-mysql
>> install php-mysqlnd
>> run
>
> Followed by this:
>
> yum install mysql-devel
> /usr/local/bin/pip2.7 install MySQL-python
>
> Now the MySQL server is down and will not come up. It fails with this:
>
> Fatal error: mysql.user table is damaged. Please run mysql_upgrade
>
> It was running 5.1 before and now it's running 5.7.
>
> But how can I run mysql_upgrade if I can't start the server?

Actually it was running 5.5 before:

Server version: 5.5.44 MySQL Community Server (GPL) by Remi

>
>>
>>> Package mysql-community-devel.x86_64 0:5.7.11-1.el6 will be installed
>>> for package: mysql-server-5.5.44-1.el6.remi.x86_64
>>
>>
>>

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

Re: Mysql devel

[Larry Martell]

On Tue, Feb 23, 2016 at 6:16 AM, Reindl Harald  wrote:
>
>
> Am 23.02.2016 um 04:47 schrieb Larry Martell:
>>
>> I am trying to install the mysql devel libs (so I can build MySQLdb).
>> This is on RHEL 6. It's failing as shown below. Can anyone help with
>> this?
>
>
> you are mixing remi and other repos

I did this:

# yum shell --enablerepo=remi
> remove php-mysql
> install php-mysqlnd
> run

Followed by this:

yum install mysql-devel
/usr/local/bin/pip2.7 install MySQL-python

Now the MySQL server is down and will not come up. It fails with this:

Fatal error: mysql.user table is damaged. Please run mysql_upgrade

It was running 5.1 before and now it's running 5.7.

But how can I run mysql_upgrade if I can't start the server?

>
>> Package mysql-community-devel.x86_64 0:5.7.11-1.el6 will be installed
>> for package: mysql-server-5.5.44-1.el6.remi.x86_64
>
>
>

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

Re: Mysql devel

[Reindl Harald]

Am 23.02.2016 um 04:47 schrieb Larry Martell:

I am trying to install the mysql devel libs (so I can build MySQLdb).
This is on RHEL 6. It's failing as shown below. Can anyone help with
this?


you are mixing remi and other repos


Package mysql-community-devel.x86_64 0:5.7.11-1.el6 will be installed
for package: mysql-server-5.5.44-1.el6.remi.x86_64





signature.asc
Description: OpenPGP digital signature

Re: mysql\innodb_table_stats.ibd. Cannot open tablespace

[Steven Siebert]

The error is stating that your innodb log sequence is higher that that of
the actual data files...any chance your data partition is full after your
restore?

On Fri, Jan 22, 2016 at 3:23 PM, Neil Tompkins  wrote:

> Hi,
>
> Hoping someone can help me identify why I keep having to restore my
> database.  You can see below, that my machine shut down normally, yet when
> I restarted the machine back up, I'm getting the error
> 'mysql\innodb_table_stats.ibd. Cannot open tablespace' and am having to
> delete the current database and restore my backup.
>
> Here is my Error log file.
>
> 2016-01-22 18:06:28 2540 [Note] MySQL: Normal shutdown
>
> 2016-01-22 18:06:28 2540 [Note] Giving 0 client threads a chance to die
> gracefully
> 2016-01-22 18:06:28 2540 [Note] Event Scheduler: Purging the queue. 0
> events
> 2016-01-22 18:06:28 2540 [Note] Shutting down slave threads
> 2016-01-22 18:06:28 2540 [Note] Forcefully disconnecting 0 remaining
> clients
> 2016-01-22 18:06:28 2540 [Note] Binlog end
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'partition'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_SYS_TABLESPACES'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_SYS_FOREIGN_COLS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_SYS_TABLESTATS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_FT_INDEX_TABLE'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_FT_INDEX_CACHE'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_FT_BEING_DELETED'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_FT_DELETED'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_FT_DEFAULT_STOPWORD'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_METRICS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_BUFFER_POOL_STATS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_BUFFER_PAGE_LRU'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_CMP_PER_INDEX_RESET'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMPMEM'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMP_RESET'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMP'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_LOCKS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_TRX'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'InnoDB'
> 2016-01-22 18:06:31 2540 [Note] InnoDB: FTS optimize thread exiting.
> 2016-01-22 18:06:31 2540 [Note] InnoDB: Starting shutdown...
> 2016-01-22 19:43:48 2556 [Note] Plugin 'FEDERATED' is disabled.
> 2016-01-22 19:43:48 2556 [Warning] option 'innodb-autoextend-increment':
> unsigned value 67108864 adjusted to 1000
> 2016-01-22 19:43:48 a0c InnoDB: Warning: Using
> innodb_additional_mem_pool_size is DEPRECATED. This option may be removed
> in future releases, together with the option innodb_use_sys_malloc and with
> the InnoDB's internal memory allocator.
> 2016-01-22 19:43:48 2556 [Note] InnoDB: The InnoDB memory heap is disabled
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Mutexes and rw_locks use Windows
> interlocked functions
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Compressed tables use zlib 1.2.3
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Not using CPU crc32 instructions
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Initializing buffer pool, size =
> 1.0G
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Completed initialization of buffer
> pool
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Highest supported file format is
> Barracuda.
> 2016-01-22 19:43:48 2556 [Note] InnoDB: The log sequence numbers 1600607
> and 1600607 in ibdata files do not match the log sequence number 154136116
> in the ib_logfiles!
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Database was not shutdown normally!
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Starting crash recovery.
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Reading tablespace information from
> the .ibd files...
> 2016-01-22 19:43:48 2556 [ERROR] 

Re: mysql\innodb_table_stats.ibd. Cannot open tablespace

[Lisa Smith]

Hi Neil,

When you say you delete the current database, do you mean the database
files only? Are you doing a complete restore?

On Fri, Jan 22, 2016 at 1:23 PM, Neil Tompkins  wrote:

> Hi,
>
> Hoping someone can help me identify why I keep having to restore my
> database.  You can see below, that my machine shut down normally, yet when
> I restarted the machine back up, I'm getting the error
> 'mysql\innodb_table_stats.ibd. Cannot open tablespace' and am having to
> delete the current database and restore my backup.
>
> Here is my Error log file.
>
> 2016-01-22 18:06:28 2540 [Note] MySQL: Normal shutdown
>
> 2016-01-22 18:06:28 2540 [Note] Giving 0 client threads a chance to die
> gracefully
> 2016-01-22 18:06:28 2540 [Note] Event Scheduler: Purging the queue. 0
> events
> 2016-01-22 18:06:28 2540 [Note] Shutting down slave threads
> 2016-01-22 18:06:28 2540 [Note] Forcefully disconnecting 0 remaining
> clients
> 2016-01-22 18:06:28 2540 [Note] Binlog end
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'partition'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_SYS_TABLESPACES'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_SYS_FOREIGN_COLS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_SYS_TABLESTATS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_FT_INDEX_TABLE'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_FT_INDEX_CACHE'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_FT_BEING_DELETED'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_FT_DELETED'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_FT_DEFAULT_STOPWORD'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_METRICS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_BUFFER_POOL_STATS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_BUFFER_PAGE_LRU'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
> 'INNODB_CMP_PER_INDEX_RESET'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMPMEM'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMP_RESET'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMP'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_LOCKS'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_TRX'
> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'InnoDB'
> 2016-01-22 18:06:31 2540 [Note] InnoDB: FTS optimize thread exiting.
> 2016-01-22 18:06:31 2540 [Note] InnoDB: Starting shutdown...
> 2016-01-22 19:43:48 2556 [Note] Plugin 'FEDERATED' is disabled.
> 2016-01-22 19:43:48 2556 [Warning] option 'innodb-autoextend-increment':
> unsigned value 67108864 adjusted to 1000
> 2016-01-22 19:43:48 a0c InnoDB: Warning: Using
> innodb_additional_mem_pool_size is DEPRECATED. This option may be removed
> in future releases, together with the option innodb_use_sys_malloc and with
> the InnoDB's internal memory allocator.
> 2016-01-22 19:43:48 2556 [Note] InnoDB: The InnoDB memory heap is disabled
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Mutexes and rw_locks use Windows
> interlocked functions
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Compressed tables use zlib 1.2.3
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Not using CPU crc32 instructions
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Initializing buffer pool, size =
> 1.0G
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Completed initialization of buffer
> pool
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Highest supported file format is
> Barracuda.
> 2016-01-22 19:43:48 2556 [Note] InnoDB: The log sequence numbers 1600607
> and 1600607 in ibdata files do not match the log sequence number 154136116
> in the ib_logfiles!
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Database was not shutdown normally!
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Starting crash recovery.
> 2016-01-22 19:43:48 2556 [Note] InnoDB: Reading tablespace information from
> the .ibd files...
> 2016-01-22 19:43:48 2556 [ERROR] InnoDB: Attempted to open a 

Re: mysql\innodb_table_stats.ibd. Cannot open tablespace

[Neil Tompkins]

My disk drive has about 3 gb free space.  The sequence of events is

1. Create database
2. Restore the data
3. Use the database, SELECT data etc
4. Shut down PC
5. When I restart PC I get this error

Re: mysql\innodb_table_stats.ibd. Cannot open tablespace

[Neil Tompkins]

Sorry to clarify;  I have my database schema, and when the database crashes
at the moment, I delete that crashed database, create the new database
using the schema, and then restore the data using a import routine I have
written in C Sharp, to import data from a CSV file to MySQL tables.

The database works fine, but seems to crash when I shut the PC down.


On Fri, Jan 22, 2016 at 8:47 PM, Lisa Smith  wrote:

> Hi Neil,
>
> When you say you delete the current database, do you mean the database
> files only? Are you doing a complete restore?
>
> On Fri, Jan 22, 2016 at 1:23 PM, Neil Tompkins <
> neil.tompk...@googlemail.com> wrote:
>
>> Hi,
>>
>> Hoping someone can help me identify why I keep having to restore my
>> database.  You can see below, that my machine shut down normally, yet when
>> I restarted the machine back up, I'm getting the error
>> 'mysql\innodb_table_stats.ibd. Cannot open tablespace' and am having to
>> delete the current database and restore my backup.
>>
>> Here is my Error log file.
>>
>> 2016-01-22 18:06:28 2540 [Note] MySQL: Normal shutdown
>>
>> 2016-01-22 18:06:28 2540 [Note] Giving 0 client threads a chance to die
>> gracefully
>> 2016-01-22 18:06:28 2540 [Note] Event Scheduler: Purging the queue. 0
>> events
>> 2016-01-22 18:06:28 2540 [Note] Shutting down slave threads
>> 2016-01-22 18:06:28 2540 [Note] Forcefully disconnecting 0 remaining
>> clients
>> 2016-01-22 18:06:28 2540 [Note] Binlog end
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'partition'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_SYS_DATAFILES'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_SYS_TABLESPACES'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_SYS_FOREIGN_COLS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_SYS_TABLESTATS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_FT_INDEX_TABLE'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_FT_INDEX_CACHE'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_FT_BEING_DELETED'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_FT_DELETED'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_FT_DEFAULT_STOPWORD'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_METRICS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_BUFFER_POOL_STATS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_BUFFER_PAGE_LRU'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_CMP_PER_INDEX_RESET'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_CMP_PER_INDEX'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMPMEM'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMP_RESET'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMP'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_LOCKS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_TRX'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'InnoDB'
>> 2016-01-22 18:06:31 2540 [Note] InnoDB: FTS optimize thread exiting.
>> 2016-01-22 18:06:31 2540 [Note] InnoDB: Starting shutdown...
>> 2016-01-22 19:43:48 2556 [Note] Plugin 'FEDERATED' is disabled.
>> 2016-01-22 19:43:48 2556 [Warning] option 'innodb-autoextend-increment':
>> unsigned value 67108864 adjusted to 1000
>> 2016-01-22 19:43:48 a0c InnoDB: Warning: Using
>> innodb_additional_mem_pool_size is DEPRECATED. This option may be removed
>> in future releases, together with the option innodb_use_sys_malloc and
>> with
>> the InnoDB's internal memory allocator.
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: The InnoDB memory heap is disabled
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: Mutexes and rw_locks use Windows
>> interlocked functions
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: Compressed tables use zlib 1.2.3
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: Not using CPU crc32 instructions
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: Initializing buffer pool, size =
>> 1.0G
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: Completed initialization of buffer
>> pool
>> 2016-01-22 19:43:48 2556 [Note] 

Re: mysql\innodb_table_stats.ibd. Cannot open tablespace

[Neil Tompkins]

One point to add, that might be of interest is that the primary key and
foreign key for two of related tables is a VARCHAR(255), and my MySQL
version is '5.6.12-log'

On Fri, Jan 22, 2016 at 8:47 PM, Lisa Smith  wrote:

> Hi Neil,
>
> When you say you delete the current database, do you mean the database
> files only? Are you doing a complete restore?
>
> On Fri, Jan 22, 2016 at 1:23 PM, Neil Tompkins <
> neil.tompk...@googlemail.com> wrote:
>
>> Hi,
>>
>> Hoping someone can help me identify why I keep having to restore my
>> database.  You can see below, that my machine shut down normally, yet when
>> I restarted the machine back up, I'm getting the error
>> 'mysql\innodb_table_stats.ibd. Cannot open tablespace' and am having to
>> delete the current database and restore my backup.
>>
>> Here is my Error log file.
>>
>> 2016-01-22 18:06:28 2540 [Note] MySQL: Normal shutdown
>>
>> 2016-01-22 18:06:28 2540 [Note] Giving 0 client threads a chance to die
>> gracefully
>> 2016-01-22 18:06:28 2540 [Note] Event Scheduler: Purging the queue. 0
>> events
>> 2016-01-22 18:06:28 2540 [Note] Shutting down slave threads
>> 2016-01-22 18:06:28 2540 [Note] Forcefully disconnecting 0 remaining
>> clients
>> 2016-01-22 18:06:28 2540 [Note] Binlog end
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'partition'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_SYS_DATAFILES'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_SYS_TABLESPACES'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_SYS_FOREIGN_COLS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_SYS_TABLESTATS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_FT_INDEX_TABLE'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_FT_INDEX_CACHE'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_FT_BEING_DELETED'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_FT_DELETED'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_FT_DEFAULT_STOPWORD'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_METRICS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_BUFFER_POOL_STATS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_BUFFER_PAGE_LRU'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_CMP_PER_INDEX_RESET'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin
>> 'INNODB_CMP_PER_INDEX'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMPMEM'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMP_RESET'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_CMP'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_LOCKS'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'INNODB_TRX'
>> 2016-01-22 18:06:31 2540 [Note] Shutting down plugin 'InnoDB'
>> 2016-01-22 18:06:31 2540 [Note] InnoDB: FTS optimize thread exiting.
>> 2016-01-22 18:06:31 2540 [Note] InnoDB: Starting shutdown...
>> 2016-01-22 19:43:48 2556 [Note] Plugin 'FEDERATED' is disabled.
>> 2016-01-22 19:43:48 2556 [Warning] option 'innodb-autoextend-increment':
>> unsigned value 67108864 adjusted to 1000
>> 2016-01-22 19:43:48 a0c InnoDB: Warning: Using
>> innodb_additional_mem_pool_size is DEPRECATED. This option may be removed
>> in future releases, together with the option innodb_use_sys_malloc and
>> with
>> the InnoDB's internal memory allocator.
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: The InnoDB memory heap is disabled
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: Mutexes and rw_locks use Windows
>> interlocked functions
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: Compressed tables use zlib 1.2.3
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: Not using CPU crc32 instructions
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: Initializing buffer pool, size =
>> 1.0G
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: Completed initialization of buffer
>> pool
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: Highest supported file format is
>> Barracuda.
>> 2016-01-22 19:43:48 2556 [Note] InnoDB: The log sequence numbers 1600607
>> and 1600607 in ibdata files do not match the log sequence 

Re: mysql\innodb_table_stats.ibd. Cannot open tablespace

[shawn l.green]

Hello Neil,

On 1/22/2016 3:23 PM, Neil Tompkins wrote:

2016-01-22 19:43:48 2556 [ERROR] InnoDB: Attempted to open a previously
opened tablespace. Previous tablespace mysql/innodb_table_stats uses space
ID: 1 at filepath: .\mysql\innodb_table_stats.ibd. Cannot open tablespace
sportstrader/event which uses space ID: 1 at filepath:
.\sportstrader\event.ibd
InnoDB: Error: could not open single-table tablespace file
.\sportstrader\event.ibd


This is where you need to focus. Something has modified the tablespace 
ID in the event.ibd file (the table `sportstrader`.`events`) so that it 
has the same ID as one of the system tables (the one used to store 
persistent stats for the InnoDB storage engine).


Each tablespace must have its own unique ID value.

This could have been anything from a bad sector on disk, a stray write 
by some other program, an intentional file-system-level change by some 
nefarious person, a bad memory cell that held the ID value of the 
`event` table's first page that caused a wrong value to be written to 
disk when the table closed, a bad backup/restore cycle ... the list can 
go on practically forever.  The problem is, the ID value in the .ibd 
file now conflicts with that of another tablespace file.


The resolution is to delete that table then restore it from a known good 
copy (preferably one from a backup).  If necessary, use the content of 
the Binary log to recover changes made to the table since that backup 
was made.


If you don't have a good copy (or any Binary log content) then try this:

1) Make a copy of event.ibd then remove it from that folder
2) Restart mysqld, it will complain about the missing file into the 
Error log. This is expected.

3) Use the techniques here to DROP that table.
http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
4) Create an empty copy of that table (correct schema, no data)
5) Use ALTER TABLE...DISCARD TABLESPACE then ALTER TABLE...IMPORT 
TABLESPACE to replace the empty .ibd file you have now with the one you 
backed up earlier.
(demonstrated at the bottom of that link I just provided for "orphaned" 
tablespaces)


Then, when you can, schedule a full hardware check to look for 
potentially failing memory or disk media. That's the most common cause 
for a fault like this.


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 dump global read lock

[Lisa Smith]

Artem,

You have --add-locks listed which will run LOCK TABLES prior to each table
dump. --single-transaction is for InnoDB tables and ensures that they are
copied in a consistent state.

I hope this helps!

On Thu, Dec 17, 2015 at 4:24 PM, Artem Kuchin  wrote:

> Hello!
>
> Hereis my mysqldump command line
>
> mysqldump -u root --events --complete-insert --skip-opt
> --single-transaction --add-drop-table --add-locks --create-options
> --disable-keys --
> extended-insert --quick --set-charset --routines --triggers --hex-blob
> DB_NAME
>
> But i see tons of Waiting for global read lock
> in show processlist for many tables in many different databases for all
> modification queries and locks
>
> Why?  As i understood --skip-opt --single-transaction must disable global
> read lock
>
>
> mysql version
> Server version: 5.6.27-log Source distribution
>
> Artem
>
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: mysql instance disk quota

[Ken Peng]

How to set OS disk quota?


On 2015/9/6  17:52, Reindl Harald wrote:

set OS disk quota for them


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

Re: mysql instance disk quota

[Reindl Harald]

Am 06.09.2015 um 11:08 schrieb Ken Peng:

We have some instances running in a hardware server, each instance has
different port.

For quota limits, we can adjust my.cnf to control each instance's memory
usage, also can use cgroups to set CPU quota.

But what's the general solution to setup the disk quota? For example, I
want each instance should use the storage no more than 50GB.


you can't

what do you expect how a database server should do if the disk quota is 
reached? what about the global table space?




signature.asc
Description: OpenPGP digital signature

Re: mysql instance disk quota

[Ken Peng]

Hi,

If disk quota is reached, an error can be threw out. we can accept this 
policy. Thanks.



On 2015/9/6 17:28, Reindl Harald wrote:


Am 06.09.2015 um 11:08 schrieb Ken Peng:

We have some instances running in a hardware server, each instance has
different port.

For quota limits, we can adjust my.cnf to control each instance's memory
usage, also can use cgroups to set CPU quota.

But what's the general solution to setup the disk quota? For example, I
want each instance should use the storage no more than 50GB.


you can't

what do you expect how a database server should do if the disk quota is
reached? what about the global table space?



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

Re: mysql instance disk quota

[Reindl Harald]

Am 06.09.2015 um 11:37 schrieb Ken Peng:

If disk quota is reached, an error can be threw out. we can accept this
policy. Thanks.


and damage will happen - jesus christ the worst thing for a database is 
"disk full", if you don't care just start the mysql instances as 
different users and set OS disk quota for them, but be prepared for data 
loss sooner or later



On 2015/9/6 17:28, Reindl Harald wrote:


Am 06.09.2015 um 11:08 schrieb Ken Peng:

We have some instances running in a hardware server, each instance has
different port.

For quota limits, we can adjust my.cnf to control each instance's memory
usage, also can use cgroups to set CPU quota.

But what's the general solution to setup the disk quota? For example, I
want each instance should use the storage no more than 50GB.


you can't

what do you expect how a database server should do if the disk quota is
reached? what about the global table space?





signature.asc
Description: OpenPGP digital signature

Re: mysql instance disk quota

[Reindl Harald]

Am 06.09.2015 um 12:01 schrieb Ken Peng:

How to set OS disk quota?


that's hardly a mysql question
http://lmgtfy.com/?q=linux+disk+quota


On 2015/9/6  17:52, Reindl Harald wrote:

set OS disk quota for them




signature.asc
Description: OpenPGP digital signature

Re: Mysql-Proxy Return prior to Execution

[Stefan Certic]

Hi Martin,

Application benchmarking purposes - i don't want any latency for updates to
complete. Still, data is needed. But i don't want application nor server to
handle the logic wether sql went fine or not.

The idea is: Fire an update / insert, return ok to application, execute
query - while application can execute another one immediately after OK.

Best Regards,

On Sun, Aug 30, 2015 at 2:52 AM, Martin Gainty mgai...@hotmail.com wrote:




  Date: Sun, 30 Aug 2015 02:39:24 +0200
  Subject: Mysql-Proxy Return prior to Execution
  From: ste...@cs-networks.net
  To: mysql@lists.mysql.com
 
  Hi Everyone,
 MGStefan-
 
  Does anyone by chance have a working example of mysql-proxy lua script
 that
  can:
 
  - First return OK to a client
  - Then actually execute the query on server, ignoring the results.

 MGcan you please elaborate ..why execute query if no results returned to
 client?
 
  Thanks in advance!
 
  --
  Stefan Certic
  *Chief Technology Officer*
 
 
 
  ste...@cs-networks.net
  --
  CS Network Solutions Limited
 
  84 High Street, Southall, Middlesex, UB1 3DB. United Kingdom
 
  London Switchboard: +442071933539
  Belgrade Operations: +38112448755
 
  Web: www.cs-networks.net
  http://www.facebook.com/smsanywhere https://twitter.com/cs_networks
  http://www.linkedin.com/company/cs-networks
  http://www.youtube.com/csnetworks
  --
 
  This message (including any attachments) is confidential and may be
  privileged. If you have received it by mistake please notify the sender
 by
  return e-mail and delete this message from your system. Any unauthorized
  use or dissemination of this message in whole or in part is strictly
  prohibited. Please note that e-mails are susceptible to change. CS
 Networks
  shall not be liable for the improper or incomplete transmission of the
  information contained in this communication nor for any delay in its
  receipt or damage to your system. CS Networks does not guarantee that the
  integrity of this communication has been maintained nor that this
  communication is free of viruses, interceptions or interference.




-- 
Stefan Certic
*Chief Technology Officer*



ste...@cs-networks.net
--
CS Network Solutions Limited

84 High Street, Southall, Middlesex, UB1 3DB. United Kingdom

London Switchboard: +442071933539
Belgrade Operations: +38112448755

Web: www.cs-networks.net
http://www.facebook.com/smsanywhere  https://twitter.com/cs_networks
http://www.linkedin.com/company/cs-networks
http://www.youtube.com/csnetworks
--

This message (including any attachments) is confidential and may be
privileged. If you have received it by mistake please notify the sender by
return e-mail and delete this message from your system. Any unauthorized
use or dissemination of this message in whole or in part is strictly
prohibited. Please note that e-mails are susceptible to change. CS Networks
shall not be liable for the improper or incomplete transmission of the
information contained in this communication nor for any delay in its
receipt or damage to your system.  CS Networks does not guarantee that the
integrity of this communication has been maintained nor that this
communication is free of viruses, interceptions or interference.

Re: Mysql-Proxy Return prior to Execution

[Stefan Certic]

Managed to solve, just in case anyone else needs the same:

function read_query( packet )

   if string.byte(packet) == proxy.COM_QUERY then

 condition.

 if (condition) then

 print(replaced with  .. query )

 proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )

 proxy.response.type = proxy.MYSQLD_PACKET_OK

 return proxy.PROXY_SEND_RESULT, proxy.PROXY_SEND_QUERY

 end

end

end

On Sun, Aug 30, 2015 at 3:00 AM, Stefan Certic ste...@cs-networks.net
wrote:

 Hi Martin,

 Application benchmarking purposes - i don't want any latency for updates
 to complete. Still, data is needed. But i don't want application nor server
 to handle the logic wether sql went fine or not.

 The idea is: Fire an update / insert, return ok to application, execute
 query - while application can execute another one immediately after OK.

 Best Regards,

 On Sun, Aug 30, 2015 at 2:52 AM, Martin Gainty mgai...@hotmail.com
 wrote:




  Date: Sun, 30 Aug 2015 02:39:24 +0200
  Subject: Mysql-Proxy Return prior to Execution
  From: ste...@cs-networks.net
  To: mysql@lists.mysql.com
 
  Hi Everyone,
 MGStefan-
 
  Does anyone by chance have a working example of mysql-proxy lua script
 that
  can:
 
  - First return OK to a client
  - Then actually execute the query on server, ignoring the results.

 MGcan you please elaborate ..why execute query if no results returned to
 client?
 
  Thanks in advance!
 
  --
  Stefan Certic
  *Chief Technology Officer*
 
 
 
  ste...@cs-networks.net
  --
  CS Network Solutions Limited
 
  84 High Street, Southall, Middlesex, UB1 3DB. United Kingdom
 
  London Switchboard: +442071933539
  Belgrade Operations: +38112448755
 
  Web: www.cs-networks.net
  http://www.facebook.com/smsanywhere https://twitter.com/cs_networks
  http://www.linkedin.com/company/cs-networks
  http://www.youtube.com/csnetworks
  --
 
  This message (including any attachments) is confidential and may be
  privileged. If you have received it by mistake please notify the sender
 by
  return e-mail and delete this message from your system. Any unauthorized
  use or dissemination of this message in whole or in part is strictly
  prohibited. Please note that e-mails are susceptible to change. CS
 Networks
  shall not be liable for the improper or incomplete transmission of the
  information contained in this communication nor for any delay in its
  receipt or damage to your system. CS Networks does not guarantee that
 the
  integrity of this communication has been maintained nor that this
  communication is free of viruses, interceptions or interference.




 --
 Stefan Certic
 *Chief Technology Officer*



 ste...@cs-networks.net
 --
 CS Network Solutions Limited

 84 High Street, Southall, Middlesex, UB1 3DB. United Kingdom

 London Switchboard: +442071933539
 Belgrade Operations: +38112448755

 Web: www.cs-networks.net
 http://www.facebook.com/smsanywhere  https://twitter.com/cs_networks
 http://www.linkedin.com/company/cs-networks
 http://www.youtube.com/csnetworks
 --

 This message (including any attachments) is confidential and may be
 privileged. If you have received it by mistake please notify the sender by
 return e-mail and delete this message from your system. Any unauthorized
 use or dissemination of this message in whole or in part is strictly
 prohibited. Please note that e-mails are susceptible to change. CS Networks
 shall not be liable for the improper or incomplete transmission of the
 information contained in this communication nor for any delay in its
 receipt or damage to your system.  CS Networks does not guarantee that the
 integrity of this communication has been maintained nor that this
 communication is free of viruses, interceptions or interference.




-- 
Stefan Certic
*Chief Technology Officer*



ste...@cs-networks.net
--
CS Network Solutions Limited

84 High Street, Southall, Middlesex, UB1 3DB. United Kingdom

London Switchboard: +442071933539
Belgrade Operations: +38112448755

Web: www.cs-networks.net
http://www.facebook.com/smsanywhere  https://twitter.com/cs_networks
http://www.linkedin.com/company/cs-networks
http://www.youtube.com/csnetworks
--

This message (including any attachments) is confidential and may be
privileged. If you have received it by mistake please notify the sender by
return e-mail and delete this message from your system. Any unauthorized
use or dissemination of this message in whole or in part is strictly
prohibited. Please note that e-mails are susceptible to change. CS Networks
shall not be liable for the improper or incomplete transmission of the
information contained in this communication nor for any delay in its
receipt or damage to your system.  CS Networks does not guarantee that the
integrity of this communication has been maintained nor that this
communication 

Re: mysql Digest 12 Aug 2015 13:08:20 -0000 Issue 5317

[Bob Eby]

converting from MyISAM to innodb would certainly pose problems, I
guess the main question would be is MyISAM functionality a strict
sub-set of innodb?

I'm not sure, but maybe someone else here knows better.

Good luck,
Robert

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

Re: mysql Digest 12 Aug 2015 13:08:20 -0000 Issue 5317

[Reindl Harald]

Am 12.08.2015 um 16:49 schrieb Bob Eby:

converting from MyISAM to innodb would certainly pose problems, I
guess the main question would be is MyISAM functionality a strict
sub-set of innodb?

I'm not sure, but maybe someone else here knows better


no it is for sure not





signature.asc
Description: OpenPGP digital signature

Re: mysql Digest 12 Aug 2015 13:08:20 -0000 Issue 5317

[hsv]

On 2015/08/12 10:49, Bob Eby wrote:

converting from MyISAM to innodb would certainly pose problems, I
guess the main question would be is MyISAM functionality a strict
sub-set of innodb?

I'm not sure, but maybe someone else here knows better.
No, as already said: for one thing, MyISAM allows more incremented 
integers in a primary key, more than one. Better find out what functions 
matter to you.


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

Re: MySQL 5.5 Slow performance to insert

[Johan De Meersman]

- Original Message -
 From: Camilo Vieira camilo.vie...@gmail.com
 Subject: Re: MySQL 5.5 Slow performance to insert

 $ ./mysqltuner.pl --user root --pass abril@123

Thank you for that password :-) I don't particularly like MySQLtuner myself, it 
makes assumptions about your workload that are, imo, false more often than not. 
Anyway...

 [!!] Total fragmented tables: 284

*shrug* Small issue on modern storage, pretty much no issue on solid state.

 [!!] Key buffer used: 18.2% (3M used / 16M cache)

*shrug* You could shrink that if you have no MyISAM at all, but it's a 
neglectable amount of memory.

 [!!] Query cache is disabled

Which is pretty much irrelevant for an insert workload :-)

 [!!] Table cache hit rate: 1% (400 open / 30K opened)

That might be worth growing. Exact numbers are hard to point at, use munin or 
similar to figure out the growth rate of opened_tables.

 [!!] InnoDB  buffer pool / data size: 4.0G/52.4G

If possible, size your buffer pool to at least your active dataset. This will 
have a considerable impact on your initial insert speed.

 [!!] InnoDB buffer pool instances: 1

From 5.6 (I think) this defaults to 8. The benefit is less contention for 
mutexes and the like. Upgrading to 5.6 is worthwile in most cases anyway.

 [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)

Hmm. That's peculiar for an insert workload... did you run that after (or 
during) the workload?


Your original mail also doesn't seem to specify the workload beyond 'inserts'. 
Details?

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

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

Re: MySQL 5.5 Slow performance to insert

[Camilo Vieira]

Hi Luis,

I have got the output below:

$ ./mysqltuner.pl --user root --pass abril@123
[OK] Logged in using credentials passed on the command line
   MySQLTuner 1.5.0 - Major Hayden ma...@mhtx.net
   Bug reports, feature requests, and downloads at http://mysqltuner.com/
   Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.35-0ubuntu0.12.04.2
[OK] Operating on 64-bit architecture

 Storage Engine Statistics
---
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 9K (Tables: 9)
[--] Data in InnoDB tables: 52G (Tables: 3468)
[--] Data in MEMORY tables: 30M (Tables: 44)
[!!] Total fragmented tables: 284

 Performance Metrics
-
[--] Up for: 2d 17h 35m 17s (1M q [5.796 qps], 23K conn, TX: 690M, RX: 1B)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Total buffers: 4.1G global + 2.7M per thread (1000 max threads)
[OK] Maximum reached memory usage: 4.1G (28.09% of installed RAM)
[OK] Maximum possible memory usage: 6.7G (45.63% of installed RAM)
[OK] Slow queries: 0% (11/1M)
[OK] Highest usage of available connections: 2% (22/1000)
[OK] Aborted connections: 0.01%  (3/23713)
[!!] Key buffer used: 18.2% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/109.0K
[OK] Read Key buffer hit rate: 100.0% (4K cached / 0 reads)
[OK] Write Key buffer hit rate: 100.0% (1K cached / 0 writes)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 14K sorts)
[OK] Temporary tables created on disk: 5% (3K on disk / 76K total)
[OK] Thread cache hit rate: 99% (24 created / 23K connections)
[!!] Table cache hit rate: 1% (400 open / 30K opened)
[OK] Open file limit used: 0% (2/65K)
[OK] Table locks acquired immediately: 100% (289K immediate / 289K locks)

 InnoDB Metrics
-
[--] InnoDB is enabled.
[!!] InnoDB  buffer pool / data size: 4.0G/52.4G
[!!] InnoDB buffer pool instances: 1
[OK] InnoDB Used buffer: 100.00% (262144 used/ 262144 total)
[OK] InnoDB Read buffer efficiency: 99.99% (1899718337 hits/ 1899815994
total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 13143 writes)

 Replication Metrics
-
[--] No replication slave(s) for this server.
[--] This is a standalone server..

 Recommendations
-
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Remove Anonymous User account - there is 2 Anonymous account.
Set up a Password for user with the following SQL statement ( SET
PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Set up a Secure Password for user@host ( SET PASSWORD FOR
'user'@'SpecificDNSorIp'
= PASSWORD('secure_password'); )
Restrict Host for user@% to user@SpecificDNSorIp
Enable the slow query log to troubleshoot bad queries
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64:
http://bit.ly/1mi7c4C
Variables to adjust:
query_cache_size (= 8M)
table_open_cache ( 400)
innodb_buffer_pool_size (= 52G) if possible.
innodb_buffer_pool_instances(=4)


2015-07-24 18:28 GMT-03:00 Luis Daniel Lucio Quiroz 
luis.daniel.lu...@gmail.com:

 How big is your innodb buffer in compare all innodb?

 Try running the mysqltuner script, it will give you useful information
 On Jul 24, 2015 4:40 PM, Camilo Vieira camilo.vie...@gmail.com wrote:

 Hi,

 My MySQL server is performing very slow inserts. Does somebody could help
 me to understand what's happening?

 mysql show engine innodb status \G
 *** 1. row ***
   Type: InnoDB
   Name:
 Status:
 =
 150724 17:40:28 INNODB MONITOR OUTPUT
 =
 Per second averages calculated from the last 1 seconds
 -
 BACKGROUND THREAD
 -
 srv_master_thread loops: 173967 1_second, 173966 sleeps, 17386 10_second,
 191 background, 191 flush
 srv_master_thread log flush and writes: 174022
 --
 SEMAPHORES
 --
 OS WAIT ARRAY INFO: reservation count 40941, signal count 55139
 Mutex spin waits 101247, rounds 886301, OS waits 24221
 RW-shared spins 29684, rounds 413636, OS waits 11014
 RW-excl spins 18765, rounds 257448, OS waits 5190
 Spin rounds per wait: 8.75 mutex, 13.93 RW-shared, 13.72 RW-excl
 
 LATEST FOREIGN KEY ERROR
 
 150722 18:23:08 Transaction:
 TRANSACTION 31AC5E3, ACTIVE 8 sec inserting
 mysql tables in use 3, locked 3
 826 lock struct(s), heap size 96696, 31241 row lock(s), undo log entries
 9932
 MySQL thread id 932, 

Re: MySQL 5.5 Slow performance to insert

[shawn l.green]

On 7/24/2015 4:35 PM, Camilo Vieira wrote:

Hi,

My MySQL server is performing very slow inserts. Does somebody could help
me to understand what's happening?
... snip ...
---TRANSACTION 31D6D74, ACTIVE 27107 sec
mysql tables in use 8, locked 8
7470 lock struct(s), heap size 801208, 849088 row lock(s)
MySQL thread id 15361, OS thread handle 0x7fea5e5c2700, query id 2690080
10.180.17.252 root Copying to tmp table
insert into CONFERENCIA_ENCALHE
(data, preco_capa_informado, qtde, qtde_informada,
chamada_encalhe_cota_id, controle_conferencia_encalhe_cota_id,
movimento_estoque_id, movimento_estoque_cota_id,
produto_edicao_id,juramentada)
(select distinct data_recolhimento, pe.preco_previsto, mec.qtde, mec.qtde,
cec.id, ccec.id, me.id, mec.id, mec.produto_edicao_id,0
from
movimento_estoque_cota_memoria mec,
movimento_estoque_memoria me,
chamada_encalhe ce,
chamada_encalhe_cota cec,
controle_conferencia_encalhe cce,
controle_conferencia_encalhe_cota ccec,
produto_edicao pe
where
mec.tipo_movimento_id =

...snip...


--
BUFFER POOL AND MEMORY
--
Total memory allocated 2197815296; in additional pool allocated 0
Dictionary memory allocated 28294038
Buffer pool size   131071
Free buffers   0
Database pages 123957
Old database pages 45737



Here are the two things I noticed:

1) You are using a subquery in this INSERT command instead of a naked 
SELECT. How long does it take that query to execute in isolation 
(outside of an INSERT command) ?


The correct syntax would be to skip the parentheses around the SELECT 
portion of the command. Instead of this,


insert into CONFERENCIA_ENCALHE(...) (select distinct data_recolhimento, 
pe.preco_previsto, mec.qtde, ...


, do this,

insert into CONFERENCIA_ENCALHE(...) SELECT distinct data_recolhimento, 
pe.preco_previsto, mec.qtde, ...



2) You have allocated very little memory to your InnoDB Buffer Pool. 
131071 pages = 2GB.  Depending on how much data you are attempting to 
first SELECT from seven tables then INSERT into the other, you may be 
forcing the system to do a lot of disk-level intermediate storage which 
is much slower than buffering that same information in memory. However, 
that may be all the RAM you can spare for MySQL. If that is so, then 
there is little you can do about this particular part of the problem.


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 5.5 Slow performance to insert

[yoku ts.]

Hi,

Your INSEERquery status is Copying to tmp table,
this means fetching rows which has to be inserted is slow.

You should tune SELECT statement in your insert query.
Adding indexes and/or simplifying query and/or .. so on.


```
---TRANSACTION 31D6D74, ACTIVE 27107 sec
mysql tables in use 8, locked 8
7470 lock struct(s), heap size 801208, 849088 row lock(s)
MySQL thread id 15361, OS thread handle 0x7fea5e5c2700, query id 2690080
10.180.17.252 root Copying to tmp table
insert into CONFERENCIA_ENCALHE
(data, preco_capa_informado, qtde, qtde_informada,
chamada_encalhe_cota_id, controle_conferencia_encalhe_cota_id,
movimento_estoque_id, movimento_estoque_cota_id,
produto_edicao_id,juramentada)
(select distinct data_recolhimento, pe.preco_previsto, mec.qtde, mec.qtde,
cec.id, ccec.id, me.id, mec.id, mec.produto_edicao_id,0
from
movimento_estoque_cota_memoria mec,
movimento_estoque_memoria me,
chamada_encalhe ce,
chamada_encalhe_cota cec,
controle_conferencia_encalhe cce,
controle_conferencia_encalhe_cota ccec,
produto_edicao pe
where
mec.tipo_movimento_id =
```


yoku0825,

2015-07-25 5:35 GMT+09:00 Camilo Vieira camilo.vie...@gmail.com:
 Hi,

 My MySQL server is performing very slow inserts. Does somebody could help
 me to understand what's happening?

 mysql show engine innodb status \G
 *** 1. row ***
   Type: InnoDB
   Name:
 Status:
 =
 150724 17:40:28 INNODB MONITOR OUTPUT
 =
 Per second averages calculated from the last 1 seconds
 -
 BACKGROUND THREAD
 -
 srv_master_thread loops: 173967 1_second, 173966 sleeps, 17386 10_second,
 191 background, 191 flush
 srv_master_thread log flush and writes: 174022
 --
 SEMAPHORES
 --
 OS WAIT ARRAY INFO: reservation count 40941, signal count 55139
 Mutex spin waits 101247, rounds 886301, OS waits 24221
 RW-shared spins 29684, rounds 413636, OS waits 11014
 RW-excl spins 18765, rounds 257448, OS waits 5190
 Spin rounds per wait: 8.75 mutex, 13.93 RW-shared, 13.72 RW-excl
 
 LATEST FOREIGN KEY ERROR
 
 150722 18:23:08 Transaction:
 TRANSACTION 31AC5E3, ACTIVE 8 sec inserting
 mysql tables in use 3, locked 3
 826 lock struct(s), heap size 96696, 31241 row lock(s), undo log entries
 9932
 MySQL thread id 932, OS thread handle 0x7feaf0042700, query id 42396
 10.180.17.252 root
 insert into movimento_estoque_cota (
 ID,APROVADO_AUTOMATICAMENTE,DATA_APROVACAO,STATUS,DATA,DATA_CRIACAO,TIPO_MOVIMENTO_ID,USUARIO_ID,
 QTDE,PRODUTO_EDICAO_ID,COTA_ID,ESTOQUE_PROD_COTA_ID,ORIGEM,APROVADOR_ID,LANCAMENTO_ID,status_estoque_financeiro,
 PRECO_COM_DESCONTO,PRECO_VENDA,VALOR_DESCONTO,FORMA_COMERCIALIZACAO)
 (select
 ID,true,date(sysdate()),'APROVADO',DATA,date(sysdate()),TIPO_MOVIMENTO_ID,1,
 QTDE,PRODUTO_EDICAO_ID,COTA_ID,
 ESTOQUE_PROD_COTA_ID,
 'CARGA_INICIAL',1,LANCAMENTO_ID,
 IF(STATUS_ESTOQUE_FINANCEIRO=1,'FINANCEIRO_PROCESSADO','FINANCEIRO_NAO_PROCESSADO'),
 PRECO_COM_DESCONTO,PRECO_
 Foreign key constraint fails for table
 `teste_historico`.`movimento_estoque_cota`:
 ,
   CONSTRAINT `FK459444C362506D6B` FOREIGN KEY (`ESTOQUE_PROD_COTA_ID`)
 REFERENCES `estoque_produto_cota` (`ID`)
 Trying to add in child table, in index `FK459444C362506D6B` tuple:
 DATA TUPLE: 2 fields;
  0: len 8; hex 80054211; asc   B ;;
  1: len 8; hex 8009743f; asc   t?;;

 But in parent table `teste_historico`.`estoque_produto_cota`, in index
 `PRIMARY`,
 the closest match we can find is record:
 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
  0: len 8; hex 80053f0d; asc   ? ;;
  1: len 6; hex 031aba25; asc  %;;
  2: len 7; hex 85561a1a14; ascV   ;;
  3: len 9; hex 80; asc  ;;
  4: len 9; hex 80003c; asc ;;
  5: len 8; hex 8044; ascD;;
  6: len 8; hex 8001007d; asc};;

 
 TRANSACTIONS
 
 Trx id counter 31DDD14
 Purge done for trx's n:o  31D8A49 undo n:o  0
 History list length 425
 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 0, not started
 MySQL thread id 18036, OS thread handle 0x7fea5e591700, query id 2854409
 localhost root
 show engine innodb status
 ---TRANSACTION 31DDD13, not started
 MySQL thread id 16024, OS thread handle 0x7fea5e4cd700, query id 2854407
 10.129.28.111 root
 ---TRANSACTION 31DC01D, not started
 MySQL thread id 15932, OS thread handle 0x7fea5e52f700, query id 2812134
 10.129.28.111 root
 ---TRANSACTION 31D83B4, not started
 MySQL thread id 15931, OS thread handle 0x7fea5d714700, query id 2721807
 10.129.28.111 root
 ---TRANSACTION 31D886C, not started
 MySQL thread id 15930, OS thread handle 0x7fea5d776700, query id 2730189
 10.129.28.111 root
 ---TRANSACTION 31D8396, not started
 MySQL thread id 15924, OS thread handle 0x7feaf0073700, query id 2721613
 10.129.28.111 root
 ---TRANSACTION 

Re: MySQL Variable size usage in shell prompt

[wagnerbianchi.com]

Not sure about the size of your dump, but, have you tried to set the new
value on the server and client side? you can increase max_allowed_packet up
to 1G. Let us know after you tried that, and maybe other guys have another
solution to share...


--
*Wagner Bianchi, +55.31.8654.9510*
Oracle ACE Director
https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541,
MySQL Certified Professional
Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P.
Email: m...@wagnerbianchi.com
Skype: wbianchijr

2015-06-16 10:28 GMT-03:00 Manivannan S manivanna...@spanservices.com:

 Hi,

 When we are trying to restore the dump file, we got an error like  Got a
 packet bigger than max_allowed_packet. Then we increased
 max_allowed_packet variable size and passed along with MySQL restore
 command.

 mysql -max_allowed_packet=128M -uusername -p  /path/file.sql

 After increasing the variable size also we got same error. Later we
 converted the value from 128M to 134217728 Bytes, then restore done
 successfully.

 In case my.cnf, when we pass the value for MySQL variables its accepting
 either in bytes, K, M or G.

 Why it doesn't consider128M in MySQL prompt while restoring the dump file?

 Regards
 Manivannan S

 DISCLAIMER: This email message and all attachments are confidential and
 may contain information that is Privileged, Confidential or exempt from
 disclosure under applicable law. If you are not the intended recipient, you
 are notified that any dissemination, distribution or copying of this email
 is strictly prohibited.  If you have received this email in error, please
 notify us immediately by return email to mailad...@spanservices.com and
 destroy the original message.  Opinions, conclusions and other information
 in this message that do not relate to the official of SPAN, shall be
 understood to be nether given nor endorsed by SPAN.

Re: MySQL 5.7 Innodb performans issue

[Jørn Dahl-Stamnes]

Found the cause.

sync_binlog was set to 1. I suspect that the default value is 5.5 was 0 and 
that is has changed to 1 
sometime after that.

Setting it to 0 boosted the performance back to normal (4x speed) and the HD 
LED indicated much 
lower stress on the hard disk.

Found this after I found out what caused it:

https://www.percona.com/blog/2009/01/21/beware-ext3-and-sync-binlog-do-not-play-well-together/

I suspect that this also apply to ext4, or?

-- 
Jørn Dahl-Stamnes
homepage: http://photo.dahl-stamnes.net/

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

Re: MySQL 5.7 Innodb performans issue

[Morgan Tocker]

Hi Jørn,

 Found this after I found out what caused it:
 
 https://www.percona.com/blog/2009/01/21/beware-ext3-and-sync-binlog-do-not-play-well-together/
 
 I suspect that this also apply to ext4, or?

I would go more specific and say that sync_binlog=1 does not play well with 
single-threaded workloads.

MySQL 5.6 and above can group commit to the binary log (reducing a lot of IO if 
transactions arrive at the same time).  I have an explanation on how it works 
here:
http://www.tocker.ca/2014/12/30/an-easy-way-to-describe-mysqls-binary-log-group-commit.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: MySQL 5.7 Innodb performans issue

[wagnerbianchi.com]

Can you share the SHOW ENGINE INNODB STATUS\G ?


--
*Wagner Bianchi, +55.31.8654.9510*
Oracle ACE Director
https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541,
MySQL Certified Professional
Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P.
Email: m...@wagnerbianchi.com
Skype: wbianchijr

2015-05-20 15:07 GMT-03:00 Jørn Dahl-Stamnes sq...@dahl-stamnes.net:

 Hello

 (again I must say).

 Over a year ago I experienced a severe drop in the MySQL Innodb
 performance after ugrading to MySQL
 5.6. I did not found any solution to that so I downgraded back to 5.5.33
 and lived with in until
 recently.

 After a system disk crash I replaced the system disk with an identical
 disk, upgraded the test
 server to Fedora Core 21 and installed MySQL 5.7.7 by downloading RPM
 files.

 Now I'm facing the same problem with poor Innodb performance.

 How have I messured the performance? I got a data collector system that
 get new data from mails
 (stored as files). The system read the files, store data in the database
 and then prerform a lot of
 calulcations on the data.

 The performance is messured by how many jobs the system can process when
 putting months of mails
 into a queue at once. While I was using 5.5.53 it processed about 2000
 jobs per minute with a peak
 up to 2500.

 Each time I test the system I use an initial database and the same mail
 files, so the input is
 always the same.

 With 5.7.7 the performance has dropped by a factor 4 - about 500-600 jobs
 per minute.

 I have played with the settings in my.cnf file but nothing seem to have
 any influence on the
 performance. So I'm using more or less the same settings for both version.

 Another thing I have noticed is that when running the test with MySQL
 5.5.33, the hard disk LED was
 mostly dark. But now, it is flashing like h**l. So it seems like the
 problem has to do with the disk
 activity.

 I would be very glad to get some feedback on this that would bring my
 performance back to normal
 :)

 About the server:
 System disk (where the binlogs are stored) is a regular magnetic disk.

 All Innodb files are stored on a SSD disk mounted with defaults,
 nouser_xattr,noatime,data=writeback,barrier=0

 The system got 32 Gb memory and have an AMD 8-core CPU (AMD FX-8120).



 This is my.cnf I'm currently using:

 # The MySQL server
 [mysqld]
 port= 3306
 socket  = /tmp/mysql.sock

 explicit_defaults_for_timestamp = TRUE

 # Logging
 slow_query_log_file = /var/log/mysql-slow.log
 slow_query_log = 1
 long_query_time = 10
 log_queries_not_using_indexes = OFF

 skip-external-locking
 key_buffer_size = 384M
 max_allowed_packet = 32M
 table_open_cache = 512
 sort_buffer_size = 2M
 read_buffer_size = 2M
 read_rnd_buffer_size = 8M
 myisam_sort_buffer_size = 64M
 thread_cache_size = 8
 max_connections = 50

 join_buffer_size = 64M

 # Replication Master Server (default)
 # binary logging is required for replication
 log-bin=/var/mysql/mysql-bin
 server-id   = 1
 binlog_format=mixed

 # Innodb settings.
 innodb_open_files   = 2048
 open_files_limit= 8096
 innodb_data_home_dir= /data/mysql/data
 innodb_data_file_path   =
 ibdata1:20G;ibdata2:20G;ibdata3:20G;ibdata4:20G:autoextend
 innodb_file_per_table   = 0
 innodb_autoextend_increment = 256
 innodb_log_group_home_dir   = /data/mysql/data
 innodb_buffer_pool_size = 25G
 innodb_log_file_size= 300M
 innodb_log_files_in_group   = 2
 innodb_log_buffer_size  = 128M

 innodb_flush_log_at_trx_commit  = 1
 innodb_support_xa   = 0
 innodb_flush_method = O_DIRECT
 innodb_lock_wait_timeout= 50
 innodb_thread_concurrency   = 16

 innodb_fast_shutdown= 0

 [mysql]
 no-auto-rehash

 --
 Jørn Dahl-Stamnes
 homepage: http://photo.dahl-stamnes.net/

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

Re: MySQL 5.7 Innodb performans issue

[Jørn Dahl-Stamnes]

On Wednesday, May 20, 2015, wagnerbianchi.com wrote:
 Can you share the SHOW ENGINE INNODB STATUS\G ?

Sure, here it is. 

*** 1. row ***
  Type: InnoDB
  Name: 
Status: 
=
2015-05-20 20:29:56 0x7f9a4c189700 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 10 seconds
-
BACKGROUND THREAD
-
srv_master_thread loops: 204 srv_active, 0 srv_shutdown, 2320 srv_idle
srv_master_thread log flush and writes: 2524
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 5507
OS WAIT ARRAY INFO: signal count 5827
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 1901, rounds 3750, OS waits 1849
RW-excl spins 108, rounds 1784098, OS waits 8
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 1.97 RW-shared, 16519.43 RW-excl, 0.00 RW-sx

TRANSACTIONS

Trx id counter 1826118
Purge done for trx's n:o  1826115 undo n:o  0 state: running but idle
History list length 1793
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421804350357096, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421804350355272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1826117, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 12, OS thread handle 140329332676352, query id 123121 localhost 
127.0.0.1 dahls 
query end
UPDATE washperiod SET washperiodstatus_id=1 WHERE washperiod_id=127687474

FILE I/O

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
104317 OS file reads, 6881 OS file writes, 6297 OS fsyncs
164.08 reads/s, 16384 avg bytes/read, 21.90 writes/s, 21.90 fsyncs/s
-
INSERT BUFFER AND ADAPTIVE HASH INDEX
-
Ibuf: size 1, free list len 0, seg size 2, 38 merges
merged operations:
 insert 130, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 53118839, node heap has 400 buffer(s)
783.22 hash searches/s, 854.11 non-hash searches/s
---
LOG
---
Log sequence number 137891623643
Log flushed up to   137891623643
Pages flushed up to 137884471017
Last checkpoint at  137884471017
0 pending log flushes, 0 pending chkp writes
4586 log i/o's done, 21.90 log i/o's/second
--
BUFFER POOL AND MEMORY
--
Total large memory allocated 27485798400
Dictionary memory allocated 1342452
Buffer pool size   1638225
Free buffers   1533395
Database pages 104430
Old database pages 38704
Modified db pages  2007
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 104305, created 125, written 1448
164.08 reads/s, 0.30 creates/s, 0.00 writes/s
Buffer pool hit rate 992 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 50.39/s, evicted without access 0.00/s, Random read ahead 
0.00/s
LRU len: 104430, unzip_LRU len: 0
I/O sum[0]:cur[8], unzip sum[0]:cur[0]
--
INDIVIDUAL BUFFER POOL INFO
--
---BUFFER POOL 0
Buffer pool size   204775
Free buffers   188541
Database pages 16183
Old database pages 5993
Modified db pages  587
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 16136, created 47, written 257
18.00 reads/s, 0.20 creates/s, 0.00 writes/s
Buffer pool hit rate 996 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 7.50/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 16183, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   204775
Free buffers   192460
Database pages 12269
Old database pages 4548
Modified db pages  179
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 12269, created 0, written 153

Re: MySQL 5.7 Innodb performans issue

[Morgan Tocker]

Hi Jørn,

 - The data collector system processing jobs, is it multi threaded?
 
 Sorry, forgot about that. No, it is not multi threaded. It is a PHP bases 
 system using several 
 script running sequently in an infinite loop. Each script taking care of part 
 of the job of 
 processing the data.

Newer MySQL releases are often more scalable (= works better for multi-threaded 
applications).

On a single threaded basis though, as functionality is added it is difficult to 
always keep the same performance numbers.  When you mentioned that there was a 
regression in 5.6 as well, it might be interesting to compare to the most 
recent 5.6.  There were some single-threaded regression fixes after the GA 
release.

 - Do you have a sample schema + set of queries we could look at?
 (We pay close attention to regressions.)
 
 This will be BIG, since it it so many different queries and tables.
 Not sure what you mean by We pay close attention to regressions”.

Bugs that introduce a loss of performance have a higher priority factor applied 
to them.  We are very interested to hear clear cases where an operation takes 
more time in MySQL 5.7 versus earlier releases.

 In terms of your configuration:
 
 I would usually recommend assuming the default values for some of the
 settings you’ve specified (table_open_cache, sort_buffer_size,
 thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..).  
 A 25G buffer pool on a 32G server with some of your other buffers being
 quite large is something you may need to look into too.
 
 I know, but sofare no swapping is taking place. The test server is used for 
 testing this system 
 only. And in 99% of the time, only one client is using the SQL server.

This makes some sense based on your workload being single threaded as well.

It looks from show engine innodb status that your server is just starting up, 
and caches are empty, so versus a 5.5 server that has been running for a while 
it will likely be slower.

What you may be able to do to track a specific set of statements that take 
longer in 5.7, is convert a mysqlbinlog to sql (mysqlbinlog mybinlogfile  
output.sql) and replay it on a 5.5 and 5.7 server.  If you can pair it down a 
little from there, then the output.sql file can be uploaded to bugs.mysql.com 
and evaluated.

Thanks!

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

Re: MySQL 5.7 Innodb performans issue

[Jørn Dahl-Stamnes]

On Wednesday, May 20, 2015, Morgan Tocker wrote:
 Hi Jørn,
 
 Wagner’s point about SHOW ENGINE INNODB STATUS is a good one.  A couple of
 other questions about your workload:
 
 - The data collector system processing jobs, is it multi threaded?

Sorry, forgot about that. No, it is not multi threaded. It is a PHP bases 
system using several 
script running sequently in an infinite loop. Each script taking care of part 
of the job of 
processing the data.

 - Do you have a sample schema + set of queries we could look at?
  (We pay close attention to regressions.)

This will be BIG, since it it so many different queries and tables.
Not sure what you mean by We pay close attention to regressions.

 In terms of your configuration:
 
 I would usually recommend assuming the default values for some of the
 settings you’ve specified (table_open_cache, sort_buffer_size,
 thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..).  
 A 25G buffer pool on a 32G server with some of your other buffers being
 quite large is something you may need to look into too.

I know, but sofare no swapping is taking place. The test server is used for 
testing this system 
only. And in 99% of the time, only one client is using the SQL server.

-- 
Jørn Dahl-Stamnes
homepage: http://photo.dahl-stamnes.net/

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

Re: MySQL 5.7 Innodb performans issue

[wagnerbianchi.com]

I'd like to add to the Morgan's note that if you want to restrict the
number of transactions inside InnoDB kernel to 16, you need at least
configure the tickets...

= http://www.pythian.com/blog/once-again-about-innodb-concurrency-tickets/

BTW, leave it as its default, IMHO, innodb_thread_concurrency=0, is
better...


--
*Wagner Bianchi, +55.31.8654.9510*
Oracle ACE Director
https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541,
MySQL Certified Professional
Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P.
Email: m...@wagnerbianchi.com
Skype: wbianchijr

2015-05-20 15:25 GMT-03:00 Morgan Tocker morgan.toc...@oracle.com:

 Hi Jørn,

 Wagner’s point about SHOW ENGINE INNODB STATUS is a good one.  A couple of
 other questions about your workload:

 - The data collector system processing jobs, is it multi threaded?

 - Do you have a sample schema + set of queries we could look at?
  (We pay close attention to regressions.)

 In terms of your configuration:

 I would usually recommend assuming the default values for some of the
 settings you’ve specified (table_open_cache, sort_buffer_size,
 thread_cache_size, innodb_log_buffer_size, innodb_thread_concurrency..).
  A 25G buffer pool on a 32G server with some of your other buffers being
 quite large is something you may need to look into too.

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

Re: MySQL 5.7 Innodb performans issue

[wagnerbianchi.com]

Additionally, take a look on this http://wagnerbianchi.com/blog/?p=982 as
you're running mysql on SSD


--
*Wagner Bianchi, +55.31.8654.9510*
Oracle ACE Director
https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541,
MySQL Certified Professional
Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P.
Email: m...@wagnerbianchi.com
Skype: wbianchijr

2015-05-20 15:15 GMT-03:00 wagnerbianchi.com m...@wagnerbianchi.com:

 Can you share the SHOW ENGINE INNODB STATUS\G ?


 --
 *Wagner Bianchi, +55.31.8654.9510 %2B55.31.8654.9510*
 Oracle ACE Director
 https://apex.oracle.com/pls/otn/f?p=19297:4:105567988301604::NO:4:P4_ID:4541,
 MySQL Certified Professional
 Percona MySQL Forum http://www.percona.com/forums/ Community V.I.P.
 Email: m...@wagnerbianchi.com
 Skype: wbianchijr

 2015-05-20 15:07 GMT-03:00 Jørn Dahl-Stamnes sq...@dahl-stamnes.net:

 Hello

 (again I must say).

 Over a year ago I experienced a severe drop in the MySQL Innodb
 performance after ugrading to MySQL
 5.6. I did not found any solution to that so I downgraded back to 5.5.33
 and lived with in until
 recently.

 After a system disk crash I replaced the system disk with an identical
 disk, upgraded the test
 server to Fedora Core 21 and installed MySQL 5.7.7 by downloading RPM
 files.

 Now I'm facing the same problem with poor Innodb performance.

 How have I messured the performance? I got a data collector system that
 get new data from mails
 (stored as files). The system read the files, store data in the database
 and then prerform a lot of
 calulcations on the data.

 The performance is messured by how many jobs the system can process when
 putting months of mails
 into a queue at once. While I was using 5.5.53 it processed about 2000
 jobs per minute with a peak
 up to 2500.

 Each time I test the system I use an initial database and the same mail
 files, so the input is
 always the same.

 With 5.7.7 the performance has dropped by a factor 4 - about 500-600 jobs
 per minute.

 I have played with the settings in my.cnf file but nothing seem to have
 any influence on the
 performance. So I'm using more or less the same settings for both version.

 Another thing I have noticed is that when running the test with MySQL
 5.5.33, the hard disk LED was
 mostly dark. But now, it is flashing like h**l. So it seems like the
 problem has to do with the disk
 activity.

 I would be very glad to get some feedback on this that would bring my
 performance back to normal
 :)

 About the server:
 System disk (where the binlogs are stored) is a regular magnetic disk.

 All Innodb files are stored on a SSD disk mounted with defaults,
 nouser_xattr,noatime,data=writeback,barrier=0

 The system got 32 Gb memory and have an AMD 8-core CPU (AMD FX-8120).



 This is my.cnf I'm currently using:

 # The MySQL server
 [mysqld]
 port= 3306
 socket  = /tmp/mysql.sock

 explicit_defaults_for_timestamp = TRUE

 # Logging
 slow_query_log_file = /var/log/mysql-slow.log
 slow_query_log = 1
 long_query_time = 10
 log_queries_not_using_indexes = OFF

 skip-external-locking
 key_buffer_size = 384M
 max_allowed_packet = 32M
 table_open_cache = 512
 sort_buffer_size = 2M
 read_buffer_size = 2M
 read_rnd_buffer_size = 8M
 myisam_sort_buffer_size = 64M
 thread_cache_size = 8
 max_connections = 50

 join_buffer_size = 64M

 # Replication Master Server (default)
 # binary logging is required for replication
 log-bin=/var/mysql/mysql-bin
 server-id   = 1
 binlog_format=mixed

 # Innodb settings.
 innodb_open_files   = 2048
 open_files_limit= 8096
 innodb_data_home_dir= /data/mysql/data
 innodb_data_file_path   =
 ibdata1:20G;ibdata2:20G;ibdata3:20G;ibdata4:20G:autoextend
 innodb_file_per_table   = 0
 innodb_autoextend_increment = 256
 innodb_log_group_home_dir   = /data/mysql/data
 innodb_buffer_pool_size = 25G
 innodb_log_file_size= 300M
 innodb_log_files_in_group   = 2
 innodb_log_buffer_size  = 128M

 innodb_flush_log_at_trx_commit  = 1
 innodb_support_xa   = 0
 innodb_flush_method = O_DIRECT
 innodb_lock_wait_timeout= 50
 innodb_thread_concurrency   = 16

 innodb_fast_shutdown= 0

 [mysql]
 no-auto-rehash

 --
 Jørn Dahl-Stamnes
 homepage: http://photo.dahl-stamnes.net/

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

Re: MySQL 5.7 Innodb performans issue

[Morgan Tocker]

Hi Jørn,

Wagner’s point about SHOW ENGINE INNODB STATUS is a good one.  A couple of 
other questions about your workload:

- The data collector system processing jobs, is it multi threaded?

- Do you have a sample schema + set of queries we could look at?
 (We pay close attention to regressions.)

In terms of your configuration:

I would usually recommend assuming the default values for some of the settings 
you’ve specified (table_open_cache, sort_buffer_size, thread_cache_size, 
innodb_log_buffer_size, innodb_thread_concurrency..).   A 25G buffer pool on a 
32G server with some of your other buffers being quite large is something you 
may need to look into too.

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

Re: MySQL 5.7 Innodb performans issue

[Jørn Dahl-Stamnes]

On Wednesday, May 20, 2015, wagnerbianchi.com wrote:
 I'd like to add to the Morgan's note that if you want to restrict the
 number of transactions inside InnoDB kernel to 16, you need at least
 configure the tickets...
 
 = http://www.pythian.com/blog/once-again-about-innodb-concurrency-tickets/
 
 BTW, leave it as its default, IMHO, innodb_thread_concurrency=0, is
 better...

Changed it to 0 and restarted MySQL. Now the test will be running all night 
long...
(a full test took 3+ weeks with 5.5 and I expect 12-14 weeks with the speed of 
5.7).

But as I wrote before, it seems like 5.7 causing a much higher disk load than 
5.5 based on how the 
HD LED is flashing. It could be off for a long time (a second or even more) 
with 5.5 while now it 
is hardly off at all.

On Wednesday, May 20, 2015, Morgan Tocker morgan.toc...@oracle.com wrote:
It looks from show engine innodb status that your server is just starting up, 
and caches are empty, 
so versus a 5.5 server that has been running for a while it will likely be 
slower.

It this is the case I have never experienced this behavior. The PHP scripts 
write output about 
everything that is happening. And the speed of the output tells me how fast 
things goes. With 5.5 
the speed was much higher right from the start (after a reboot and starting up 
the test).

-- 
Jørn Dahl-Stamnes
homepage: http://photo.dahl-stamnes.net/

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

Re: MySQL - SSL - with TLS1.2 cipher AES256-SHA256 / DHE-RSA-AES256-SHA256

[Reindl Harald]

Am 17.03.2015 um 13:21 schrieb Bhushan Rane:

I have compiled MySQL with openssl, I am able to connect to MySQL over
SSL with TLS1.0 ciphers. But when I tried to connect with TLS1.2 ciphers
connection fails with error


MySQL / MariaDB don't support anything better than DHE-RSA-AES128-SHA 
(AES256 is supported but not more secure than AES128)


* no ECHDE
* no AES-GCM
* no SHA256

no idea how they manage that because openssl has support
___

ssl-cipher=DHE-RSA-AES256-SHA256
ssl-cipher=AES256-SHA256

are not supported and don't make much sense anyways
___

the currently best ciphersuite would be the following because AES-GCM is 
hardware optimized on recent machines (Intel AES) and GCM is *always* 
better than a stupid CBC cipher


ECDHE-RSA-AES128-GCM-SHA256




signature.asc
Description: OpenPGP digital signature

Re: MySQL - SSL - with TLS1.2 cipher AES256-SHA256 / DHE-RSA-AES256-SHA256

[Reindl Harald]

Am 17.03.2015 um 14:25 schrieb Bhushan Rane:

Thanks for instant reply.

As suggested I tried with ECDHE-RSA-AES128-GCM-SHA256 and
ECDHE-RSA-AES128-SHA but no luck.
Created new certs for EC - configured my.cnf with new certs


you *did not* really read my reply

 MySQL / MariaDB don't support anything better than DHE-RSA-AES128-SHA
 (AES256 is supported but not more secure than AES128)

 * no ECHDE
 * no AES-GCM
 * no SHA256



Below are commands used for cert creation :-

/opt/scr-openssl/ssl/bin/openssl ecparam -out ecc_ecdsa_root.key -name
secp384r1 -genkey
echo -e US\nObt\nIntelSelfSA\nSSG\nTesters\nCAServer\n\n\n |
/opt/scr-openssl/ssl/bin/openssl req -new -x509 -days 365 -key
ecc_ecdsa_root.key -out ecc_ecdsa_root.pem

/opt/scr-openssl/ssl/bin/openssl ecparam -out ecc_ecdsa_ca.key -name
secp384r1 -genkey
echo -e US\nObt\nIntelSelfSA\nSSG\nTesters\nMySQLServer\n\n\n |
/opt/scr-openssl/ssl/bin/openssl req -new -key ecc_ecdsa_ca.key -out
ecc_ecdsa_ca.csr
/opt/scr-openssl/ssl/bin/openssl x509 -req -extensions v3_ca -extfile
x509_1.ext -days 365 -in ecc_ecdsa_ca.csr -CA ecc_ecdsa_root.pem -CAkey
ecc_ecdsa_root.key -out ecc_ecdsa_ca.pem -CAcreateserial

/opt/scr-openssl/ssl/bin/openssl ecparam -out ecc_ecdsa_client.key -name
secp384r1 -genkey
echo -e US\nObt\nIntelSelfSA\nSSG\nTesters\nMySQLClient\n\n\n |
/opt/scr-openssl/ssl/bin/openssl req -new -key ecc_ecdsa_client.key -out
ecc_ecdsa_client.csr
/opt/scr-openssl/ssl/bin/openssl x509 -req -extensions v3_ca -extfile
x509_2.ext -days 365 -in ecc_ecdsa_client.csr -CA ecc_ecdsa_ca.pem
-CAkey ecc_ecdsa_ca.key -out ecc_ecdsa_client.pem -CAcreateserial


mysql -uroot -p --ssl-cipher=ECDHE-RSA-AES128-GCM-SHA256 -T -v

Enter password:
ERROR 2026 (HY000): SSL connection error:
error:0001:lib(0):func(0):reason(1)

User time 0.00, System time 0.00
Maximum resident set size 2728, Integral resident set size 0
Non-physical pagefaults 793, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 3, Involuntary context switches 3

mysql -uroot -p --ssl-cipher=ECDHE-RSA-AES128-SHA -T -v

Enter password:
ERROR 2026 (HY000): SSL connection error:
error:0001:lib(0):func(0):reason(1)

User time 0.00, System time 0.00
Maximum resident set size 2748, Integral resident set size 0
Non-physical pagefaults 798, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 4, Involuntary context switches 5


Also, check openssl - used openssl supports EC ciphers listed above.

Am 17.03.2015 um 13:21 schrieb Bhushan Rane:

I have compiled MySQL with openssl, I am able to connect to MySQL over
SSL with TLS1.0 ciphers. But when I tried to connect with TLS1.2 ciphers
connection fails with error


MySQL / MariaDB don't support anything better than DHE-RSA-AES128-SHA
(AES256 is supported but not more secure than AES128)

* no ECHDE
* no AES-GCM
* no SHA256

no idea how they manage that because openssl has support
___

ssl-cipher=DHE-RSA-AES256-SHA256
ssl-cipher=AES256-SHA256

are not supported and don't make much sense anyways
___

the currently best ciphersuite would be the following because AES-GCM is
hardware optimized on recent machines (Intel AES) and GCM is *always*
better than a stupid CBC cipher

ECDHE-RSA-AES128-GCM-SHA256




signature.asc
Description: OpenPGP digital signature

Re: Mysql Syslog client.

[Stephan Gomes Higuti]

yoku0825,

Thanks for answering, I've just find out what happened.
I'm using OpenSUSE 13.2, and by default it uses systemdlogger and not
syslog, so, my super complex solution for this case was replacing
systemdlogger for syslog!   :-D
Worked like a charm.

Regards,

Stephan Higuti


On 12 March 2015 at 23:48, yoku ts. yoku0...@gmail.com wrote:

 yoku0825




Att,

Stephan Gomes Higuti

Re: Mysql Syslog client.

[yoku ts.]

Hi Stephan,

I've already tried syslog but it works fine in my environment.

$ /usr/mysql/5.7.6/bin/mysql --syslog -h 192.168.198.214 -u yoku
mysql show databases;
Mar 13 11:27:57 dev-personal-04 MysqlClient: SYSTEM_USER:'ttanaka',
MYSQL_USER:'yoku', CONNECTION_ID:9, DB_SERVER:'192.168.198.214',
DB:'--', QUERY:'show databases;'

$ /usr/mysql/5.7.6/bin/mysql -h sys-db02 -P 3307 -u yoku --syslog
mysql status
Mar 13 11:30:01 dev-personal-04 MysqlClient: SYSTEM_USER:'ttanaka',
MYSQL_USER:'yoku', CONNECTION_ID:17818, DB_SERVER:'sys-db02', DB:'--',
QUERY:'status'

mysql show databases;
Mar 13 11:30:03 dev-personal-04 MysqlClient: SYSTEM_USER:'ttanaka',
MYSQL_USER:'yoku', CONNECTION_ID:17818, DB_SERVER:'sys-db02', DB:'--',
QUERY:'show databases;'

$ /usr/mysql/5.7.6/bin/mysql -S /usr/mysql/5.7.6/data/mysql.sock -u
root --syslog
mysql select current_user();
Mar 13 11:30:24 dev-personal-04 MysqlClient: SYSTEM_USER:'ttanaka',
MYSQL_USER:'root', CONNECTION_ID:10, DB_SERVER:'--', DB:'--',
QUERY:'select current_user();'


mysql command line client's --syslog sends log to local(client
machine)'s syslogd.


yoku0825,

2015-03-13 3:59 GMT+09:00 Stephan Gomes Higuti higuti@gmail.com:
 Hello guys.

 Im using MySQL 5.7.6 for testing some new features, including syslog
 support for client.
 Its working great and Im getting the following lines when using localhost:

 Mar 12 12:36:34 server1 MysqlClient[17854]: SYSTEM_USER:'user1',
 MYSQL_USER:'userdb', CONNECTION_ID:5, DB_SERVER:'--', DB:'mysql',
 QUERY:'use mysql'
 Mar 12 12:36:36 server1 MysqlClient[17854]: SYSTEM_USER:'user1',
 MYSQL_USER:'userdb', CONNECTION_ID:5, DB_SERVER:'--', DB:'mysql',
 QUERY:'show tables;'
 Mar 12 12:36:55 server1 MysqlClient[17856]: SYSTEM_USER:'user1',
 MYSQL_USER:'userdb', CONNECTION_ID:6, DB_SERVER:'--', DB:'mysql',
 QUERY:'use mysql '

 However, if I try to access MySQL in any other server, for example mysql
 -uuser -ppasswd -hserver2, it doesn't log in syslog.
 My client section config is:

 [client]
 #password   = [your_password]
 port= 3306
 socket  = /var/lib/mysql/mysql.sock
 syslog

 Have any of you tried this?

 Thank you in advance.

 Stephan

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

Re: mysql float data type

[Jan Steinman]

 From: Lucio Chiappetti lu...@lambrate.inaf.it
 
 never used DECIMAL nor intend to

Why would you blow off an important feature of any system?

DECIMAL performs infinite precision math, and should be used in ALL 
situations where you don't want rounding errors. It should ALWAYS be your first 
choice for quantities of money, for example.

 If a taxpayer thinks he can cheat safely, he probably will. -- Diogenes
 Jan Steinman, EcoReality Co-op 


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

Re: mysql float data type

[Lucio Chiappetti]

On Tue, 16 Dec 2014, Hartmut Holzgraefe wrote:

On 16.12.2014 15:16, xiangdongzou wrote:


 Can anyone tell me why 531808.11 has been changed to 531808.12 ?


typical decimal-binary-decimal conversion/rounding error.


never used DECIMAL nor intend to, but the issue is typical of precision 
issues among float (32-bit) and double (64-bit) in ANY programming 
language. Google for IEEE floating point


Some (most) users are unaware that a 32-bit real (REAL*4 for oldtimer 
Fortran users like myself) have about 7 digits of precision, while 64-bit 
(doubles, double precision, REAL*8) get to about 16.


So if a quantity needs high precision (typically this occurs for angular 
quantities where arcseconds are important), use double.


--

Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html

Do not like Firefox =29 ?  Get Pale Moon !  http://www.palemoon.org

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

Re: mysql float data type

[Patrick Sherrill]

We always store as strings to avoid rounding issues and then convert for calcs 
to whatever precision we need. 
Pat...

Sent from my iPhone

 On Dec 17, 2014, at 6:24 AM, Lucio Chiappetti lu...@lambrate.inaf.it wrote:
 
 On Tue, 16 Dec 2014, Hartmut Holzgraefe wrote:
 On 16.12.2014 15:16, xiangdongzou wrote:
 
 Can anyone tell me why 531808.11 has been changed to 531808.12 ?
 
 typical decimal-binary-decimal conversion/rounding error.
 
 never used DECIMAL nor intend to, but the issue is typical of precision 
 issues among float (32-bit) and double (64-bit) in ANY programming language. 
 Google for IEEE floating point
 
 Some (most) users are unaware that a 32-bit real (REAL*4 for oldtimer Fortran 
 users like myself) have about 7 digits of precision, while 64-bit (doubles, 
 double precision, REAL*8) get to about 16.
 
 So if a quantity needs high precision (typically this occurs for angular 
 quantities where arcseconds are important), use double.
 
 -- 
 
 Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy)
 For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html
 
 Do not like Firefox =29 ?  Get Pale Moon !  http://www.palemoon.org
 
 -- 
 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: mysql float data type

[Hartmut Holzgraefe]

On 17 December 2014 14:21:40 CET, Patrick Sherrill patr...@michael-clarke.com 
wrote:
We always store as strings to avoid rounding issues and then convert
for calcs to whatever precision we need. 
Pat...

So you'll still be affected by rounding errors during conversion and 
calculation,
two problems you'd avoid when using DECIMAL instead ...
-- 
Hartmut Holzgraefe, Principal Support Engineer (EMEA)
MariaDB Corporation | http://www.mariadb.com/

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