Re: Show ROUTINE body, not PROCEDURE

[Dotan Cohen]

On Wed, Mar 27, 2013 at 5:46 PM,   wrote:
> 2013/03/27 08:01 +0200, Dotan Cohen 
> Actually, it is the user that I am logged in as that created the
> function. That is why I find it hard to believe that one needs root /
> admin access to see its definition.
> 
> And that user set DEFINER other than itself, and that worked???
> That takes SUPER.
>

The guy who wrote the routine is no longer available, but he never had
access to any other account. The guy with root did _not_ add this
routine for him, that is for certain! Therefore I conclude that the
current MySQL user did create this routine and I'm missing something
to understand how to see its definition.


-- 
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com

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

Re: Show ROUTINE body, not PROCEDURE

[Peter Brawley]

On 2013-03-28 3:33 PM, Dotan Cohen wrote:

On Wed, Mar 27, 2013 at 5:46 PM,   wrote:

2013/03/27 08:01 +0200, Dotan Cohen 

Actually, it is the user that I am logged in as that created the
function. That is why I find it hard to believe that one needs root /
admin access to see its definition.

And that user set DEFINER other than itself, and that worked???
That takes SUPER.


The guy who wrote the routine is no longer available, but he never had
access to any other account. The guy with root did _not_ add this
routine for him, that is for certain! Therefore I conclude that the
current MySQL user did create this routine and I'm missing something
to understand how to see its definition.


Can someone run the server with --skip-grant-tables to retrieve this code?

PB








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

Re: Defragmentation of MySQL tables, how many times have I to do it?

[Reindl Harald]

Am 28.03.2013 19:43, schrieb Rick James:
> I have never traced a performance issue in InnoDB to the need for OPTIMIZE.

but with files_per_table you can get back GIGABTYES of disk-space
depending on your backups this may be relevant

> The Query Cache is irrelevant to this discussion

it is NOT because look at the server-stats after a optimize table
hint: cached results for any of the tables are thrown away



signature.asc
Description: OpenPGP digital signature

RE: Defragmentation of MySQL tables, how many times have I to do it?

[Rick James]

How often to OPTIMIZE?

The Short answer:  Never.

The Long answer:  A _few_ tables _may_ need OPTIMIZE _sometimes_.

One test:  Is the "free" space (according to SHOW TABLE STATUS or equivalent 
information_schema stuff) is > 10%, then OPTIMIZE.  Maybe.  However...  That 
math works OK for MyISAM, but is unreliable for InnoDB because (1) things are 
freed in big chunks, thereby showing large free space, and (2) hiding small 
chunks that don't yet showing in the free space.  That is, the number may be 
too high or too low.

BTrees are inherently 'stable'.  InnoDB merges adjacent BTree blocks.  These 
comments lead to the conclusion that there is rarely anything to gain by 
OPTIMIZEing an InnoDB table or MyISAM indexes.

MyISAM data, after lots of DELETEs/UPDATEs/INSERTs of _variable_ length rows 
('Dynamic') can cause fragmentation of individual rows.  Normally a row is 
contiguous in the .MYD file; but it could be in multiple pieces if there were 
small free spots when it was inserted.  So..., if there is a lot of churn, it 
may be useful to OPTIMIZE.  However, I would suggest only once a month.  This 
is perhaps the only case I have found for OPTIMIZEing MyISAM for performance.

With PARTITIONing, do not attempt to OPTIMIZE a single PARTITION; it will 
reOPTIMIZE the entire table (at least in the InnoDB case).  Instead, use ALTER 
TABLE..REORGANIZE.. on one partition into itself.

I have never traced a performance issue in InnoDB to the need for OPTIMIZE.

The Query Cache is irrelevant to this discussion.



> -Original Message-
> From: Bheemsen Aitha [mailto:pgb...@motorola.com]
> Sent: Thursday, March 28, 2013 8:59 AM
> To: Reindl Harald
> Cc: mysql@lists.mysql.com
> Subject: Re: Defragmentation of MySQL tables, how many times have I to
> do it?
> 
> Hi Reindl,
> 
> I would like to implement your strategy of optimizing tables. Can you
> please share how are running these scripts? Where does the mysql-
> wrapper-class exist? And what parameters need to be passed?
> 
> Thanks
> Bheem Aitha
> MySQL and Oracle DBA
> On Mar 28, 2013 4:43 AM, "Reindl Harald" 
> wrote:
> 
> >
> >
> > Am 28.03.2013 12:28, schrieb Antonio Fernández Pérez:
> > > So, is it not necessary (not recommended) to defragment tables if I
> > > have
> > a
> > > lot of write operations (writing or deleting)?
> >
> > it is recommended but not permanently and not blindly
> >
> > i use a daily cronjob which runs optimize table on tables with >= 50
> > KB overhead based on this methods of a internal mysql-wrapper-class
> >
> >
> >  public function optimizeall($action, $returntables, $flush,
> > $min_overhead, $only_myisam=true)  {
> >   $output = '';
> >   $dblist = $this->showdatabases();
> >   foreach($dblist as $akt)
> >   {
> >if($akt != 'information_schema' && $akt != 'performance_schema')
> >{
> > if(function_exists('apache_reset_timeout'))
> > {
> >  apache_reset_timeout();
> > }
> > $output .= $this->optimizetables($akt, $action, $returntables,
> > array(), $min_overhead, $only_myisam);
> > if($flush)
> > {
> >  echo $output;
> >  @ob_end_flush();
> >  flush();
> >  $output = '';
> > }
> >}
> >   }
> >   return $output;
> >  }
> >
> >
> >  public function optimizetables($database, $action='optimize',
> > $returntables=0, array $tablelist=array(), $min_overhead=0,
> > $only_myisam=true)  {
> >   global $rh_php_sapi_name;
> >   $first  = false;
> >   $output = '';
> >   $sql= '';
> >   if(empty($database))
> >   {
> >$database = $this->parent->db;
> >   }
> >   if(empty($tablelist))
> >   {
> >$tablelist = $this->showtables($database);
> >   }
> >   if(!empty($tablelist))
> >   {
> >foreach($tablelist as $akt)
> >{
> > $ignore = false;
> > if($only_myisam)
> > {
> >  $this->parent->select_db($database);
> >  $type_result = $this->parent->query('SHOW TABLE STATUS LIKE \''
> .
> > $akt . '\'', 1, 0);
> >  $type_row = $this->parent->fetch_assoc($type_result);
> >  if(strtolower($type_row['Engine']) == 'innodb')
> >  {
> >   $ignore = true;
> >  }
> > }
> > if(!$ignore && ($min_overhead == 0 ||
> > $this->get_table_overhead($database, $akt) >= $min_overhead))
> > {
> >  if($first)
> >  {
> >   $sql .= ', ';
> >  }
> >  else
> >  {
> >   $sql = $action . ' table ';
> >  }
> >  $sql .= '`' . $database . '`.`' . $akt . '`';
> >  $first = true;
> >  if($returntables)
> >  {
> >   $output .= $database . '.' . $akt;
> >   if($rh_php_sapi_name != 'cli')
> >   {
> >$output .= '';
> >   }
> >   $output .= MY_LE;
> >  }
> > }
> >}
> >if($action != 'all')
> >{
> > if(!empty($sql))
> > {
> >  $result = $this->parent->query($sql);
> > }
> >}
> >else
> >{
> > if(!empty($sql))
> > {
> >  $zsp = $sql;
> >  $result = $this->parent->query(str_replace('all', 'check',
> $zsp),
> > 1, 0)

Re: Defragmentation of MySQL tables, how many times have I to do it?

[Reindl Harald]

optimizeall() is simply called from a cron script

the mysql-wrapper does not exist in public but you get the table
overhead with  "SHOW TABLE STATUS" as you can see below

Am 28.03.2013 16:59, schrieb Bheemsen Aitha:
> I would like to implement your strategy of optimizing tables. Can you please 
> share how are running these scripts?
> Where does the mysql-wrapper-class exist? And what parameters need to be 
> passed?
> 
> 
> On Mar 28, 2013 4:43 AM, "Reindl Harald"  > wrote:
> 
> 
> 
> Am 28.03.2013 12:28, schrieb Antonio Fernández Pérez:
> > So, is it not necessary (not recommended) to defragment tables if I 
> have a
> > lot of write operations (writing or deleting)?
> 
> it is recommended but not permanently and not blindly
> 
> i use a daily cronjob which runs optimize table on tables with >= 50 KB 
> overhead
> based on this methods of a internal mysql-wrapper-class
> 
> 
>  public function optimizeall($action, $returntables, $flush, 
> $min_overhead, $only_myisam=true)
>  {
>   $output = '';
>   $dblist = $this->showdatabases();
>   foreach($dblist as $akt)
>   {
>if($akt != 'information_schema' && $akt != 'performance_schema')
>{
> if(function_exists('apache_reset_timeout'))
> {
>  apache_reset_timeout();
> }
> $output .= $this->optimizetables($akt, $action, $returntables, 
> array(), $min_overhead, $only_myisam);
> if($flush)
> {
>  echo $output;
>  @ob_end_flush();
>  flush();
>  $output = '';
> }
>}
>   }
>   return $output;
>  }
> 
> 
>  public function optimizetables($database, $action='optimize', 
> $returntables=0, array $tablelist=array(),
> $min_overhead=0, $only_myisam=true)
>  {
>   global $rh_php_sapi_name;
>   $first  = false;
>   $output = '';
>   $sql= '';
>   if(empty($database))
>   {
>$database = $this->parent->db;
>   }
>   if(empty($tablelist))
>   {
>$tablelist = $this->showtables($database);
>   }
>   if(!empty($tablelist))
>   {
>foreach($tablelist as $akt)
>{
> $ignore = false;
> if($only_myisam)
> {
>  $this->parent->select_db($database);
>  $type_result = $this->parent->query('SHOW TABLE STATUS LIKE \'' . 
> $akt . '\'', 1, 0);
>  $type_row = $this->parent->fetch_assoc($type_result);
>  if(strtolower($type_row['Engine']) == 'innodb')
>  {
>   $ignore = true;
>  }
> }
> if(!$ignore && ($min_overhead == 0 || 
> $this->get_table_overhead($database, $akt) >= $min_overhead))
> {
>  if($first)
>  {
>   $sql .= ', ';
>  }
>  else
>  {
>   $sql = $action . ' table ';
>  }
>  $sql .= '`' . $database . '`.`' . $akt . '`';
>  $first = true;
>  if($returntables)
>  {
>   $output .= $database . '.' . $akt;
>   if($rh_php_sapi_name != 'cli')
>   {
>$output .= '';
>   }
>   $output .= MY_LE;
>  }
> }
>}
>if($action != 'all')
>{
> if(!empty($sql))
> {
>  $result = $this->parent->query($sql);
> }
>}
>else
>{
> if(!empty($sql))
> {
>  $zsp = $sql;
>  $result = $this->parent->query(str_replace('all', 'check', $zsp), 1, 
> 0);
>  $result = $this->parent->query(str_replace('all', 'repair', $zsp), 
> 1, 0);
>  $result = $this->parent->query(str_replace('all', 'optimize', $zsp), 
> 1, 0);
> }
>}
>   }
>   return $output;
>  }



signature.asc
Description: OpenPGP digital signature

Re: Defragmentation of MySQL tables, how many times have I to do it?

[Bheemsen Aitha]

Hi Reindl,

I would like to implement your strategy of optimizing tables. Can you
please share how are running these scripts? Where does the
mysql-wrapper-class exist? And what parameters need to be passed?

Thanks
Bheem Aitha
MySQL and Oracle DBA
On Mar 28, 2013 4:43 AM, "Reindl Harald"  wrote:

>
>
> Am 28.03.2013 12:28, schrieb Antonio Fernández Pérez:
> > So, is it not necessary (not recommended) to defragment tables if I have
> a
> > lot of write operations (writing or deleting)?
>
> it is recommended but not permanently and not blindly
>
> i use a daily cronjob which runs optimize table on tables with >= 50 KB
> overhead
> based on this methods of a internal mysql-wrapper-class
>
>
>  public function optimizeall($action, $returntables, $flush,
> $min_overhead, $only_myisam=true)
>  {
>   $output = '';
>   $dblist = $this->showdatabases();
>   foreach($dblist as $akt)
>   {
>if($akt != 'information_schema' && $akt != 'performance_schema')
>{
> if(function_exists('apache_reset_timeout'))
> {
>  apache_reset_timeout();
> }
> $output .= $this->optimizetables($akt, $action, $returntables,
> array(), $min_overhead, $only_myisam);
> if($flush)
> {
>  echo $output;
>  @ob_end_flush();
>  flush();
>  $output = '';
> }
>}
>   }
>   return $output;
>  }
>
>
>  public function optimizetables($database, $action='optimize',
> $returntables=0, array $tablelist=array(),
> $min_overhead=0, $only_myisam=true)
>  {
>   global $rh_php_sapi_name;
>   $first  = false;
>   $output = '';
>   $sql= '';
>   if(empty($database))
>   {
>$database = $this->parent->db;
>   }
>   if(empty($tablelist))
>   {
>$tablelist = $this->showtables($database);
>   }
>   if(!empty($tablelist))
>   {
>foreach($tablelist as $akt)
>{
> $ignore = false;
> if($only_myisam)
> {
>  $this->parent->select_db($database);
>  $type_result = $this->parent->query('SHOW TABLE STATUS LIKE \'' .
> $akt . '\'', 1, 0);
>  $type_row = $this->parent->fetch_assoc($type_result);
>  if(strtolower($type_row['Engine']) == 'innodb')
>  {
>   $ignore = true;
>  }
> }
> if(!$ignore && ($min_overhead == 0 ||
> $this->get_table_overhead($database, $akt) >= $min_overhead))
> {
>  if($first)
>  {
>   $sql .= ', ';
>  }
>  else
>  {
>   $sql = $action . ' table ';
>  }
>  $sql .= '`' . $database . '`.`' . $akt . '`';
>  $first = true;
>  if($returntables)
>  {
>   $output .= $database . '.' . $akt;
>   if($rh_php_sapi_name != 'cli')
>   {
>$output .= '';
>   }
>   $output .= MY_LE;
>  }
> }
>}
>if($action != 'all')
>{
> if(!empty($sql))
> {
>  $result = $this->parent->query($sql);
> }
>}
>else
>{
> if(!empty($sql))
> {
>  $zsp = $sql;
>  $result = $this->parent->query(str_replace('all', 'check', $zsp), 1,
> 0);
>  $result = $this->parent->query(str_replace('all', 'repair', $zsp), 1,
> 0);
>  $result = $this->parent->query(str_replace('all', 'optimize', $zsp),
> 1, 0);
> }
>}
>   }
>   return $output;
>  }
>
>

Re: Defragmentation of MySQL tables, how many times have I to do it?

[Manuel Arostegui]

Hello Antonio,

As Reindl said, it is recommended but it shouldnt be done just
everyday if your tables are quite big and you use query cache.

If you do have lot of writes and deletes (depending on what you
consider a lot), your table will indeed have lot of 'blank rows' which
eat disk space.

Optimizing tables if good for both: saving disk space and reading
faster from them.
However, remember that optimize locks your table so it can affect your
writes and create a big overhead in your server (IO).

If I were your I would benchmark how fast my operations are after
a...lets say monthly optimize and then decide.
I am assuming you are not optimizing because of disk space issues.

Manuel

2013/3/28, Antonio Fernández Pérez :
> Ok. Thank you very much.
>
> Best regards,
>
> Antonio.
>


-- 
Manuel Aróstegui
Systems Team
tuenti.com

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

Re: Defragmentation of MySQL tables, how many times have I to do it?

[Antonio Fernández Pérez]

Hello Manuel,

Exactly. My objective doing performance MySQL it isn't to free disk space
(it isn't the problem). I would like to get a good performance using cache
and setting good values to configuration variables.

I will defrag tables once per month.

Best regards,

Antonio.

Re: Defragmentation of MySQL tables, how many times have I to do it?

[Antonio Fernández Pérez]

Ok. Thank you very much.

Best regards,

Antonio.

Re: Defragmentation of MySQL tables, how many times have I to do it?

[Reindl Harald]

Am 28.03.2013 12:28, schrieb Antonio Fernández Pérez:
> So, is it not necessary (not recommended) to defragment tables if I have a
> lot of write operations (writing or deleting)?

it is recommended but not permanently and not blindly

i use a daily cronjob which runs optimize table on tables with >= 50 KB overhead
based on this methods of a internal mysql-wrapper-class


 public function optimizeall($action, $returntables, $flush, $min_overhead, 
$only_myisam=true)
 {
  $output = '';
  $dblist = $this->showdatabases();
  foreach($dblist as $akt)
  {
   if($akt != 'information_schema' && $akt != 'performance_schema')
   {
if(function_exists('apache_reset_timeout'))
{
 apache_reset_timeout();
}
$output .= $this->optimizetables($akt, $action, $returntables, array(), 
$min_overhead, $only_myisam);
if($flush)
{
 echo $output;
 @ob_end_flush();
 flush();
 $output = '';
}
   }
  }
  return $output;
 }


 public function optimizetables($database, $action='optimize', $returntables=0, 
array $tablelist=array(),
$min_overhead=0, $only_myisam=true)
 {
  global $rh_php_sapi_name;
  $first  = false;
  $output = '';
  $sql= '';
  if(empty($database))
  {
   $database = $this->parent->db;
  }
  if(empty($tablelist))
  {
   $tablelist = $this->showtables($database);
  }
  if(!empty($tablelist))
  {
   foreach($tablelist as $akt)
   {
$ignore = false;
if($only_myisam)
{
 $this->parent->select_db($database);
 $type_result = $this->parent->query('SHOW TABLE STATUS LIKE \'' . $akt . 
'\'', 1, 0);
 $type_row = $this->parent->fetch_assoc($type_result);
 if(strtolower($type_row['Engine']) == 'innodb')
 {
  $ignore = true;
 }
}
if(!$ignore && ($min_overhead == 0 || $this->get_table_overhead($database, 
$akt) >= $min_overhead))
{
 if($first)
 {
  $sql .= ', ';
 }
 else
 {
  $sql = $action . ' table ';
 }
 $sql .= '`' . $database . '`.`' . $akt . '`';
 $first = true;
 if($returntables)
 {
  $output .= $database . '.' . $akt;
  if($rh_php_sapi_name != 'cli')
  {
   $output .= '';
  }
  $output .= MY_LE;
 }
}
   }
   if($action != 'all')
   {
if(!empty($sql))
{
 $result = $this->parent->query($sql);
}
   }
   else
   {
if(!empty($sql))
{
 $zsp = $sql;
 $result = $this->parent->query(str_replace('all', 'check', $zsp), 1, 0);
 $result = $this->parent->query(str_replace('all', 'repair', $zsp), 1, 0);
 $result = $this->parent->query(str_replace('all', 'optimize', $zsp), 1, 0);
}
   }
  }
  return $output;
 }



signature.asc
Description: OpenPGP digital signature

Re: Defragmentation of MySQL tables, how many times have I to do it?

[Antonio Fernández Pérez]

Hi Reindl,

Thanks for your reply.

So, is it not necessary (not recommended) to defragment tables if I have a
lot of write operations (writing or deleting)?

Best regards,

Antonio.

Re: Defragmentation of MySQL tables, how many times have I to do it?

[Reindl Harald]

Am 28.03.2013 12:09, schrieb Antonio Fernández Pérez:
> Hi everybody,
> 
> Once I have done the defragmentation of MySQL tables, mysql-tunner.pl suggests
> me do it again. Is this correct? I think that the idea is that in the
> result of the script there are not fragmented tables ...
> 
> Any ideas?

depends on how large your tables and how much fragmented tey are
if you delete a record from a table it would be counted as
fragmented - so what, if you permenantly run "optimize table"
you produce more load as any frgamentation and additionally
you trash the whole time the query-cache



signature.asc
Description: OpenPGP digital signature

Defragmentation of MySQL tables, how many times have I to do it?

[Antonio Fernández Pérez]

Hi everybody,

Once I have done the defragmentation of MySQL tables, mysql-tunner.pl suggests
me do it again. Is this correct? I think that the idea is that in the
result of the script there are not fragmented tables ...

Any ideas?

Thank you very much.

Best regards,

Antonio.

Re: All client commands to syslog?

[yoku ts.]

Hello,

"--syslog" is option of "mysql" command line client and it works in my
environment.
Do you put [mysql] section in your my.cnf?

Regards,


yoku

2013/3/28 Rafał Radecki :
> Hi All.
>
> I have a production setup of four databases connected with
> replication. I would like to log every command that clients execute
> for auditing.
>
> I've read 
> http://www.percona.com/doc/percona-server/5.5/diagnostics/mysql_syslog.html?id=percona-server:features:mysql_syslog&redirect=1#client-variables
> but despite the fact that I use percona
> Percona-Server-server-55-5.5.28-rel29.3.388.rhel6.x86_64 it does not
> seem to work. I've added "syslog"/"syslog ON" to my my.cnf server
> configuration file but no info about executed commands in logs.
>
> I've also read 
> http://www.mysqlperformanceblog.com/2008/07/08/logging-your-mysql-command-line-client-sessions/.
>
> What is the best way to log all client commands?
>
> Best regards,
> Rafal.
>
> --
> 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: All client commands to syslog?

[Guy_Lichtman]

Hi, 

You can check out the McAfee MySQL Audit Plugin at: 
https://github.com/mcafee/mysql-audit 

It is an open source plugin providing audit capabilities for MySQL. It can be 
used to log executing commands. It can log to a file or over a unix domain 
socket to syslog-ng. 

Best,

Guy

-Original Message-
From: Rafał Radecki [mailto:radecki.ra...@gmail.com] 
Sent: Thursday, March 28, 2013 9:52 AM
To: mysql@lists.mysql.com
Subject: All client commands to syslog?

Hi All.

I have a production setup of four databases connected with replication. I would 
like to log every command that clients execute for auditing.

I've read 
http://www.percona.com/doc/percona-server/5.5/diagnostics/mysql_syslog.html?id=percona-server:features:mysql_syslog&redirect=1#client-variables
but despite the fact that I use percona
Percona-Server-server-55-5.5.28-rel29.3.388.rhel6.x86_64 it does not seem to 
work. I've added "syslog"/"syslog ON" to my my.cnf server configuration file 
but no info about executed commands in logs.

I've also read 
http://www.mysqlperformanceblog.com/2008/07/08/logging-your-mysql-command-line-client-sessions/.

What is the best way to log all client commands?

Best regards,
Rafal.

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

All client commands to syslog?

[Rafał Radecki]

Hi All.

I have a production setup of four databases connected with
replication. I would like to log every command that clients execute
for auditing.

I've read 
http://www.percona.com/doc/percona-server/5.5/diagnostics/mysql_syslog.html?id=percona-server:features:mysql_syslog&redirect=1#client-variables
but despite the fact that I use percona
Percona-Server-server-55-5.5.28-rel29.3.388.rhel6.x86_64 it does not
seem to work. I've added "syslog"/"syslog ON" to my my.cnf server
configuration file but no info about executed commands in logs.

I've also read 
http://www.mysqlperformanceblog.com/2008/07/08/logging-your-mysql-command-line-client-sessions/.

What is the best way to log all client commands?

Best regards,
Rafal.

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