subject:"logs"

RE: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

2013-06-25 Thread Rick James

If a crash occurs in the middle of an ALTER, the files may not get cleaned up.

 -Original Message-
 From: Reindl Harald [mailto:h.rei...@thelounge.net]
 Sent: Thursday, June 20, 2013 12:57 PM
 To: mysql@lists.mysql.com
 Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and
 appeared after MySQL crash?
 
 i know what happened but how get rid of these two bullshit files after
 *three years* not touched and used by mysqld
 
 Am 20.06.2013 21:28, schrieb Rick James:
  #sql files are temp tables that vanish when the ALTER (or whatever)
 finishes.  If you find one sitting around, it sounds like a crash happened
 in the middle of the ALTER.
 
  -Original Message-
  From: Reindl Harald [mailto:h.rei...@thelounge.net]
  Sent: Wednesday, June 19, 2013 12:19 PM
  To: mysql@lists.mysql.com
  Subject: Re: How can I drop a table that is named “logs/#sql-ib203”
  and appeared after MySQL crash?
 
 
 
  Am 19.06.2013 21:00, schrieb Franck Dernoncourt:
  `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`,
  but when trying to `ALTER` the table that was being changed during
  the crash MySQL complains about the existence of the table
  `logs/#sql-
  ib203`:
 
  ERROR 1050: Table 'logs/#sql-ib203' already exists
 
  SQL Statement:
 
  ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
  `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`
 
  DROP TABLE `logs/#sql-ib203`; does not work, neither do some name
  variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively
  Error
  Code: 1051.
  Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown
  table
 
  I use innodb_file_per_table. There was a mysql_datadir/logs/#sql-
  ib203.ibd
  file (or maybe .frm, sorry I forgot) that I deleted.
 
  Any idea how to get rid of this ghostly table `logs/#sql-ib203`?
 
  interesting question, i have identical crap since 2009 also after a
  crash und these blind table has the same structure as a used
  existing one
 
  if i delete the #-files mysql whines every startup while they are
  never used and it is ridiculous that there are references in the
  table space to this useless crap and no mysql version from 5.1.8 to
  5.5.32 is fixing this
 
  -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm
  -rw-rw 1 mysql mysql  64K 2011-07-24 11:49 #sql2-704-271.ibd


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

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

2013-06-25 Thread Reindl Harald

that is what we all know

but how to get rid of them?
but *why* they are not cleaned up?

* the global tablespace knows about them
* nothing is using them really
* so why can mysqld not cleanup this mess?

if you delete them all works fine but each start
the error-log is cluttered

Am 25.06.2013 17:46, schrieb Rick James:
 If a crash occurs in the middle of an ALTER, the files may not get cleaned up.
 
 -Original Message-
 From: Reindl Harald [mailto:h.rei...@thelounge.net]
 Sent: Thursday, June 20, 2013 12:57 PM
 To: mysql@lists.mysql.com
 Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and
 appeared after MySQL crash?

 i know what happened but how get rid of these two bullshit files after
 *three years* not touched and used by mysqld

 Am 20.06.2013 21:28, schrieb Rick James:
 #sql files are temp tables that vanish when the ALTER (or whatever)
 finishes.  If you find one sitting around, it sounds like a crash happened
 in the middle of the ALTER.

 -Original Message-
 From: Reindl Harald [mailto:h.rei...@thelounge.net]
 Sent: Wednesday, June 19, 2013 12:19 PM
 To: mysql@lists.mysql.com
 Subject: Re: How can I drop a table that is named “logs/#sql-ib203”
 and appeared after MySQL crash?



 Am 19.06.2013 21:00, schrieb Franck Dernoncourt:
 `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`,
 but when trying to `ALTER` the table that was being changed during
 the crash MySQL complains about the existence of the table
 `logs/#sql-
 ib203`:

 ERROR 1050: Table 'logs/#sql-ib203' already exists

 SQL Statement:

 ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
 `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`

 DROP TABLE `logs/#sql-ib203`; does not work, neither do some name
 variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively
 Error
 Code: 1051.
 Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown
 table

 I use innodb_file_per_table. There was a mysql_datadir/logs/#sql-
 ib203.ibd
 file (or maybe .frm, sorry I forgot) that I deleted.

 Any idea how to get rid of this ghostly table `logs/#sql-ib203`?

 interesting question, i have identical crap since 2009 also after a
 crash und these blind table has the same structure as a used
 existing one

 if i delete the #-files mysql whines every startup while they are
 never used and it is ridiculous that there are references in the
 table space to this useless crap and no mysql version from 5.1.8 to
 5.5.32 is fixing this

 -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm
 -rw-rw 1 mysql mysql  64K 2011-07-24 11:49 #sql2-704-271.ibd
 

-- 

Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / CISO / Software-Development
m: +43 (676) 40 221 40, p: +43 (1) 595 3999 33
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm



signature.asc
Description: OpenPGP digital signature

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

2013-06-21 Thread Jesper Wisborg Krogh


Hi Frank,

On 20/06/2013 05:00, Franck Dernoncourt wrote:

Hi all,

A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space
shortage while deleting some attributes in a table in the `logs` database
and adding an index.

`USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but
when trying to `ALTER` the table that was being changed during the crash
MySQL complains about the existence of the table `logs/#sql-ib203`:


It's a bit of a workaround, but you should be able to get rid of the 
file using the steps below. I'm using an example where I killed mysqld 
while it was dropping the to_date column from the salaries table in the 
employees sample database:


   mysql SHOW CREATE TABLE salaries\G
   *** 1. row ***
   Table: salaries
   Create Table: CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `emp_no` (`emp_no`),
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES
   `employees` (`emp_no`) ON DELETE CASCADE
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1
   1 row in set (0.00 sec)

   mysql ALTER TABLE salaries DROP COLUMN to_date;
   ERROR 2013 (HY000): Lost connection to MySQL server during query

   mysql$ ls -1 employees/#*
   employees/#sql-36ab_2.frm
   employees/#sql-ib30.ibd

1. Create a temporary table with the same structure as the salaries
   table would have looked after the ALTER that failed:

   mysql CREATE TABLE tmp LIKE salaries; ALTER TABLE tmp DROP COLUMN
   to_date;

2. Shutdown MySQL.
3. Copy the .frm file from the tmp table to have the same name as the
   #sql-*.ibd file:

   mysql$ cp employees/tmp.frm employees/#sql-ib30.frm

4. Start MySQL again.
5. Drop the #sql-ib30.frm table:

   mysql DROP TABLE `#mysql50##sql-ib30`;
   Query OK, 0 rows affected (0.01 sec)

6. Do the same for the #sql*.frm file (it'll get removed even though
   you get an error):

   mysql DROP TABLE `#mysql50##sql-36ab_2`;
   ERROR 1051 (42S02): Unknown table 'employees.#mysql50##sql-36ab_2'

I know it's not very elegant, but should work. The #mysql50# prefix 
tells MySQL to not encode the table name when mapping to the file system 
(https://dev.mysql.com/doc/refman/5.6/en/identifier-mapping.html).


Best regards,
Jesper Krogh
MySQL Support

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

2013-06-20 Thread Denis Jedig


Franck,

Am 19.06.2013 21:00, schrieb Franck Dernoncourt:


A table `logs/#sql-ib203` appeared after a MySQL crash


the #sql-ibtableID tables are temporarily created during an 
ALTER TABLE operation for recovery purposes. Apparently these 
temporary tables might stay in certain circumstances even after 
recovery is completed.


If you already tried enclosing the table name in backticks (DROP 
TABLE `#sql-ib203`) and using the DROP TEMPORARY TABLE syntax 
without success, copying the table along with all its data and 
dropping the original table afterwards or running `mysqldump 
database tablename  dump.sql  mysql  dump.sql` for a 
backup/restore operation at least will help the problem of being 
unable to run ALTER TABLE commands for the affected main table.


Regards,

Denis

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

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

2013-06-20 Thread Carsten Pedersen

This may be a naive question, but I'm not sure I can see you've covered 
this: Have you tried USE logs before DROP TABLE `#sql-ib203` (without 
the logs/ bit)?


/ Carsten

On 19-06-2013 21:00, Franck Dernoncourt wrote:

Hi all,

A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space
shortage while deleting some attributes in a table in the `logs` database
and adding an index.

`USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but
when trying to `ALTER` the table that was being changed during the crash
MySQL complains about the existence of the table `logs/#sql-ib203`:


ERROR 1050: Table 'logs/#sql-ib203' already exists

SQL Statement:

ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
`agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`


DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants
`/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051.
Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown table
'logs./#sql-ib203', Error Code: 1051. Unknown table 'logs.#sql-ib203' and
Error Code: 1051. Unknown table 'logs.sql-ib203'). Interestingly none of
these error messages display 'logs/#sql-ib203', which is the table name
MySQL complains it exists when I try to do ALTER.

I use innodb_file_per_table. There was a mysql_datadir/logs/#sql-ib203.ibd
file (or maybe .frm, sorry I forgot) that I deleted.

Any idea how to get rid of this ghostly table `logs/#sql-ib203`?

I use MySQL 5.6.12-winx64 and InnoDB.

Thanks,


Franck Dernoncourt
fran...@mit.edu
http://francky.me



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

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

2013-06-20 Thread Franck Dernoncourt

On Thu, Jun 20, 2013 at 7:13 AM, Denis Jedig d...@syneticon.net wrote:

 If you already tried enclosing the table name in backticks (DROP TABLE
 `#sql-ib203`) and using the DROP TEMPORARY TABLE syntax without success,


Yep, no success with:
DROP TEMPORARY TABLE `logs/#sql-ib203`;
DROP TEMPORARY TABLE `/#sql-ib203`;
DROP TEMPORARY TABLE `#sql-ib203`;
DROP TEMPORARY TABLE `sql-ib203`;
USE logs; DROP TEMPORARY TABLE `logs/#sql-ib203`;
USE logs; DROP TEMPORARY TABLE `/#sql-ib203`;
USE logs; DROP TEMPORARY TABLE `#sql-ib203`;
USE logs; DROP TEMPORARY TABLE `sql-ib203`;


  copying the table along with all its data and dropping the original table
 afterwards or running `mysqldump database tablename  dump.sql  mysql 
 dump.sql` for a backup/restore operation at least will help the problem of
 being unable to run ALTER TABLE commands for the affected main table.


I copied the data to a new table with a different name, but I wish there
were a more subtle way to solve the issue :)
I haven't dropped the original table yet, so I cannot confirm this will
solve the issue but hopefully it will.


On Thu, Jun 20, 2013 at 1:32 PM, Carsten Pedersen cars...@bitbybit.dkwrote:

 This may be a naive question, but I'm not sure I can see you've covered
 this: Have you tried USE logs before DROP TABLE `#sql-ib203` (without the
 logs/ bit)?


Thanks, I tried the following, none of them worked:

DROP TABLE `logs/#sql-ib203`;
DROP TABLE `/#sql-ib203`;
DROP TABLE `#sql-ib203`;
DROP TABLE `sql-ib203`;
USE logs; DROP TABLE `logs/#sql-ib203`;
USE logs; DROP TABLE `/#sql-ib203`;
USE logs; DROP TABLE `#sql-ib203`;
USE logs; DROP TABLE `sql-ib203`;

Also, I can create tables with that name:
USE logs; CREATE TABLE `logs/#sql-ib203` (id int);
USE logs; CREATE TABLE `/#sql-ib203` (id int);
USE logs; CREATE TABLE `#sql-ib203` (id int);
USE logs; CREATE TABLE `sql-ib203` (id int);

It does not conflict with any existing tables.

Here is an example where I CREATE and DROP `logs/#sql-ib203`:
step 1 14:47:48 USE logs 0 row(s) affected 0.000 sec
step 2  14:47:48 CREATE TABLE `logs/#sql-ib203` (id int) 0 row(s)
affected 0.047
sec
step 3 14:47:53 CREATE TABLE `logs/#sql-ib203` (id int) Error Code: 1050.
Table 'logs/#sql-ib203' already exists 0.000 sec
step 4 14:48:01 DROP TABLE `logs/#sql-ib203` 0 row(s) affected 0.047 sec
step 5 14:48:05 DROP TABLE `logs/#sql-ib203` Error Code: 1051. Unknown
table 'logs.logs/#sql-ib203' 0.000 sec
step 6 14:48:30 ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` Error
Code: 1050. Table 'logs/#sql-ib203' already exists 0.016 sec

DROP TABLE `logs/#sql-ib203` at step 4 works, which would allow me to do
step 2 CREATE TABLE `logs/#sql-ib203` (id int) again, but step 6 ALTER
TABLE still complains about the existence of 'logs/#sql-ib203'.

One last remark: the main file `ibdata1` contains references to
`logs/#sql-ib203`, which is not surprising given the error message I have
when trying to ALTER the original table. Is there any way to clean the file
`ibdata1` so that it only contains references to tables having an actual
data file? I use InnoDB with innodb_file_per_table

Thanks for your help,


Franck Dernoncourt
fran...@mit.edu
http://francky.me

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

2013-06-20 Thread Franck Dernoncourt

On Thu, Jun 20, 2013 at 3:28 PM, Rick James rja...@yahoo-inc.com wrote:

 #sql files are temp tables that vanish when the ALTER (or whatever)
 finishes.  If you find one sitting around, it sounds like a crash happened
 in the middle of the ALTER.


Yes the crash happened during an ALTER: the table `logs/#sql-ib203`
appeared after a MySQL crash due to disk space shortage while executing the
following query:

ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN `agent` ,
DROP COLUMN `ip` , DROP COLUMN `event_source`
, ADD INDEX `username_event_type_idx` (`username` ASC, `event_type` ASC) ;

Shouldn't the recovery take care of cleaning the temporary tables created
during the query running at the time of the crash? Or at least, if not,
shouldn't the DROP be working on this temporary table?

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

2013-06-20 Thread Reindl Harald

i know what happened but how get rid of these two bullshit files
after *three years* not touched and used by mysqld

Am 20.06.2013 21:28, schrieb Rick James:
 #sql files are temp tables that vanish when the ALTER (or whatever) finishes. 
  If you find one sitting around, it sounds like a crash happened in the 
 middle of the ALTER.
 
 -Original Message-
 From: Reindl Harald [mailto:h.rei...@thelounge.net]
 Sent: Wednesday, June 19, 2013 12:19 PM
 To: mysql@lists.mysql.com
 Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and
 appeared after MySQL crash?



 Am 19.06.2013 21:00, schrieb Franck Dernoncourt:
 `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`,
 but when trying to `ALTER` the table that was being changed during the
 crash MySQL complains about the existence of the table `logs/#sql-
 ib203`:

 ERROR 1050: Table 'logs/#sql-ib203' already exists

 SQL Statement:

 ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
 `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`

 DROP TABLE `logs/#sql-ib203`; does not work, neither do some name
 variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error
 Code: 1051.
 Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown table

 I use innodb_file_per_table. There was a mysql_datadir/logs/#sql-
 ib203.ibd
 file (or maybe .frm, sorry I forgot) that I deleted.

 Any idea how to get rid of this ghostly table `logs/#sql-ib203`?

 interesting question, i have identical crap since 2009 also after a crash
 und these blind table has the same structure as a used existing one

 if i delete the #-files mysql whines every startup while they are never
 used and it is ridiculous that there are references in the table space to
 this useless crap and no mysql version from 5.1.8 to 5.5.32 is fixing this

 -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm
 -rw-rw 1 mysql mysql  64K 2011-07-24 11:49 #sql2-704-271.ibd



signature.asc
Description: OpenPGP digital signature

RE: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

2013-06-20 Thread Rick James

#sql files are temp tables that vanish when the ALTER (or whatever) finishes.  
If you find one sitting around, it sounds like a crash happened in the middle 
of the ALTER.

 -Original Message-
 From: Reindl Harald [mailto:h.rei...@thelounge.net]
 Sent: Wednesday, June 19, 2013 12:19 PM
 To: mysql@lists.mysql.com
 Subject: Re: How can I drop a table that is named “logs/#sql-ib203” and
 appeared after MySQL crash?

 Am 19.06.2013 21:00, schrieb Franck Dernoncourt:
  `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`,
  but when trying to `ALTER` the table that was being changed during the
  crash MySQL complains about the existence of the table `logs/#sql-
 ib203`:

  ERROR 1050: Table 'logs/#sql-ib203' already exists

  SQL Statement:

  ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
  `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`

  DROP TABLE `logs/#sql-ib203`; does not work, neither do some name
  variants `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error
 Code: 1051.
  Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown table

  I use innodb_file_per_table. There was a mysql_datadir/logs/#sql-
 ib203.ibd
  file (or maybe .frm, sorry I forgot) that I deleted.

  Any idea how to get rid of this ghostly table `logs/#sql-ib203`?

 interesting question, i have identical crap since 2009 also after a crash
 und these blind table has the same structure as a used existing one

 if i delete the #-files mysql whines every startup while they are never
 used and it is ridiculous that there are references in the table space to
 this useless crap and no mysql version from 5.1.8 to 5.5.32 is fixing this

 -rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm
 -rw-rw 1 mysql mysql  64K 2011-07-24 11:49 #sql2-704-271.ibd

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

How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

2013-06-19 Thread Franck Dernoncourt

Hi all,

A table `logs/#sql-ib203` appeared after a MySQL crash due to disk space
shortage while deleting some attributes in a table in the `logs` database
and adding an index.

`USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but
when trying to `ALTER` the table that was being changed during the crash
MySQL complains about the existence of the table `logs/#sql-ib203`:

 ERROR 1050: Table 'logs/#sql-ib203' already exists

 SQL Statement:

 ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
 `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`

DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants
`/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051.
Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown table
'logs./#sql-ib203', Error Code: 1051. Unknown table 'logs.#sql-ib203' and
Error Code: 1051. Unknown table 'logs.sql-ib203'). Interestingly none of
these error messages display 'logs/#sql-ib203', which is the table name
MySQL complains it exists when I try to do ALTER.

I use innodb_file_per_table. There was a mysql_datadir/logs/#sql-ib203.ibd
file (or maybe .frm, sorry I forgot) that I deleted.

Any idea how to get rid of this ghostly table `logs/#sql-ib203`?

I use MySQL 5.6.12-winx64 and InnoDB.

Thanks,


Franck Dernoncourt
fran...@mit.edu
http://francky.me

Re: How can I drop a table that is named “logs/#sql-ib203” and appeared after MySQL crash?

2013-06-19 Thread Reindl Harald



Am 19.06.2013 21:00, schrieb Franck Dernoncourt:
 `USE logs; SHOW TABLES;` does not list the table `logs/#sql-ib203`, but
 when trying to `ALTER` the table that was being changed during the crash
 MySQL complains about the existence of the table `logs/#sql-ib203`:
 
 ERROR 1050: Table 'logs/#sql-ib203' already exists

 SQL Statement:

 ALTER TABLE `logs`.`srv_logs` DROP COLUMN `filenum` , DROP COLUMN
 `agent` , DROP COLUMN `ip` , DROP COLUMN `event_source`
 
 DROP TABLE `logs/#sql-ib203`; does not work, neither do some name variants
 `/#sql-ib203`, `#sql-ib203`, `sql-ib203`. (respectively Error Code: 1051.
 Unknown table 'logs.logs/#sql-ib203',  Error Code: 1051. Unknown table

 I use innodb_file_per_table. There was a mysql_datadir/logs/#sql-ib203.ibd
 file (or maybe .frm, sorry I forgot) that I deleted.
 
 Any idea how to get rid of this ghostly table `logs/#sql-ib203`?

interesting question, i have identical crap since 2009 also
after a crash und these blind table has the same structure
as a used existing one

if i delete the #-files mysql whines every startup while
they are never used and it is ridiculous that there are
references in the table space to this useless crap and
no mysql version from 5.1.8 to 5.5.32 is fixing this

-rw-rw 1 mysql mysql 8,4K 2011-07-24 11:49 #sql2-704-271.frm
-rw-rw 1 mysql mysql  64K 2011-07-24 11:49 #sql2-704-271.ibd




signature.asc
Description: OpenPGP digital signature

RE: Are There Slow Queries that Don't Show in the Slow Query Logs?

2013-05-31 Thread Robinson, Eric

 -Original Message-
 From: Vikas Shukla [mailto:myfriendvi...@gmail.com] 
 Sent: Thursday, May 30, 2013 7:19 PM
 To: Robinson, Eric; mysql@lists.mysql.com
 Subject: RE: Are There Slow Queries that Don't Show in the 
 Slow Query Logs?

 Hi,

 No, it does not represents the time from request to response 
 not does it includes the time that is spent in waiting for 
 the locks to be released.
 The slow query log consists of SQL statements that took more 
 than long_query_time seconds to EXECUTE. The default value of 
 long_query_time is 10.
 The time to acquire the initial locks is not counted as 
 execution time.
 mysqld writes a statement to the slow query log after it has 
 been executed and after all locks have been released, so log 
 order might differ from execution order.

 Lets take an example, if a query is received at 10:00 hrs and 
 it waits till 10:05 hrs , it starts getting executed at 
 10:05:00 and completed at 10:05:24 (HH:MM:SS). So, here it 
 took 24 seconds to execute. So only
 24 seconds is counted.
 So if long_query_time is equal to 10, which is by default, 
 this would be logged in slow query log as it takes more than 
 10 seconds to execute.

 Sent from my Windows Phone From: Robinson, Eric
 Sent: 31-05-2013 03:48
 To: mysql@lists.mysql.com
 Subject: Are There Slow Queries that Don't Show in the Slow 
 Query Logs?
 As everyone knows, with MyISAM, queries and inserts can lock 
 tables and force other queries to wait in a queue. When that 
 happens, does the time shown in the slow query logs represent 
 the whole time from when the server received the request to 
 when the response was sent to the client? Or is the time a 
 query spends waiting for a table lock to be released omitted 
 from what is recorded in the slow query logs?

 --
 Eric Robinson

Very good answer, Vikas. Thank you for the clarification!

--Eric

Disclaimer - May 31, 2013 
This email and any files transmitted with it are confidential and intended 
solely for Vikas Shukla,mysql@lists.mysql.com. If you are not the named 
addressee you should not disseminate, distribute, copy or alter this email. Any 
views or opinions presented in this email are solely those of the author and 
might not represent those of Physicians' Managed Care or Physician Select 
Management. Warning: Although Physicians' Managed Care or Physician Select 
Management has taken reasonable precautions to ensure no viruses are present in 
this email, the company cannot accept responsibility for any loss or damage 
arising from the use of this email or attachments. 
This disclaimer was added by Policy Patrol: http://www.policypatrol.com/

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

RE: Are There Slow Queries that Don't Show in the Slow Query Logs?

2013-05-30 Thread Vikas Shukla

Hi,

No, it does not represents the time from request to response not does
it includes the time that is spent in waiting for the locks to be
released.
The slow query log consists of SQL statements that took more than
long_query_time seconds to EXECUTE. The default value of
long_query_time is 10.
The time to acquire the initial locks is not counted as execution time.
mysqld writes a statement to the slow query log after it has been
executed and after all locks have been released, so log order might
differ from execution order.

Lets take an example, if a query is received at 10:00 hrs and it waits
till 10:05 hrs , it starts getting executed at 10:05:00 and completed
at 10:05:24 (HH:MM:SS). So, here it took 24 seconds to execute. So only
24 seconds is counted.
So if long_query_time is equal to 10, which is by default, this would
be logged in slow query log as it takes more than 10 seconds to execute.

Sent from my Windows Phone From: Robinson, Eric
Sent: 31-05-2013 03:48
To: mysql@lists.mysql.com
Subject: Are There Slow Queries that Don't Show in the Slow Query Logs?
As everyone knows, with MyISAM, queries and inserts can lock tables
and force other queries to wait in a queue. When that happens, does
the time shown in the slow query logs represent the whole time from
when the server received the request to when the response was sent to
the client? Or is the time a query spends waiting for a table lock to
be released omitted from what is recorded in the slow query logs?

--
Eric Robinson






Disclaimer - May 30, 2013
This email and any files transmitted with it are confidential and
intended solely for 'mysql@lists.mysql.com'. If you are not the named
addressee you should not disseminate, distribute, copy or alter this
email. Any views or opinions presented in this email are solely those
of the author and might not represent those of Physicians' Managed
Care or Physician Select Management. Warning: Although Physicians'
Managed Care or Physician Select Management has taken reasonable
precautions to ensure no viruses are present in this email, the
company cannot accept responsibility for any loss or damage arising
from the use of this email or attachments.
This disclaimer was added by Policy Patrol: http://www.policypatrol.com/

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

Help with purging old logs for each customer ID

2012-10-25 Thread Daevid Vincent

I have a customer log table that is starting to rapidly fill up (we have
hundreds of thousands of users, but many are transient, and use the service
for a few months, or use the free trial and quit, etc.)

CREATE TABLE `customers_log` (
  `customer_log_id` bigint(20) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned default '0',
  `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
  `type`
enum('View','Action','Admin','Search','Login','Logout','Access','General','A
PI'),
  `source` enum('web','mobile','system'),
  `body` text,
  PRIMARY KEY  (`customer_log_id`),
  KEY `created_on` (`created_on`),
  KEY `customers_id` (`customer_id`)
) ENGINE=InnoDB

What I'd like to do now is make a 'rolling log' in that I want to DELETE any
entries older than 90 days for EACH `customer_id`.

I'm not sure how to do that in a query? I'd rather not iterate over each
customer_id if I can help it.




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

RE: Help with purging old logs for each customer ID

2012-10-25 Thread Rick James

Off hand, I would iterate over the PRIMARY KEY, looking at a thousand rows at a 
time, DELETEing any that need to be purged.  I would use a Perl or PHP loop, or 
write a stored procedure.  More discussion of huge deletes (which this 
_could_ be):
http://mysql.rjweb.org/doc.php/deletebig
(PARTITIONing does not apply in your case, as I understand it.)

I like the loop:

SELECT @z := customer_log_id FROM customers_log LIMIT 1000, 1;
DELETE FROM customers_log
WHERE customer_log_id = @left_off AND customer_log_id  @z
  AND created_on  NOW() - INTERVAL 90 DAY;
sleep a few seconds (to be a nice guy)

Plus code to take care of iterating and terminating.

That loop could be done continually.

It seems that customer_id is irrelevant??  

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Thursday, October 25, 2012 11:46 AM
 To: mysql@lists.mysql.com
 Subject: Help with purging old logs for each customer ID
 
 I have a customer log table that is starting to rapidly fill up (we
 have hundreds of thousands of users, but many are transient, and use
 the service for a few months, or use the free trial and quit, etc.)
 
 CREATE TABLE `customers_log` (
   `customer_log_id` bigint(20) unsigned NOT NULL auto_increment,
   `customer_id` int(10) unsigned default '0',
   `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update
 CURRENT_TIMESTAMP,
   `type`
 enum('View','Action','Admin','Search','Login','Logout','Access','Genera
 l','A
 PI'),
   `source` enum('web','mobile','system'),
   `body` text,
   PRIMARY KEY  (`customer_log_id`),
   KEY `created_on` (`created_on`),
   KEY `customers_id` (`customer_id`)
 ) ENGINE=InnoDB
 
 What I'd like to do now is make a 'rolling log' in that I want to
 DELETE any entries older than 90 days for EACH `customer_id`.
 
 I'm not sure how to do that in a query? I'd rather not iterate over
 each customer_id if I can help it.
 
 
 
 
 --
 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: Help with purging old logs for each customer ID

2012-10-25 Thread Daevid Vincent

Well, the customer_id is relevant in that I want the last 90 days relative
to each customer.

customer_id = 123 might have logs from jan - mar
customer_id = 444 might have logs from feb - may

So it's a rolling log of THEIR last 90 days from their last log (most
recent) back 90 days from there.

Does that make more sense?

I guess I was trying to avoid looping over every customer ID and computing
if I could help it. I thought by using a GROUP BY or something it could
group all the logs for a given customer and then trim them that way.

But maybe brute force is the way to go?

 -Original Message-
 From: Rick James [mailto:rja...@yahoo-inc.com]
 Sent: Thursday, October 25, 2012 1:09 PM
 To: Daevid Vincent; mysql@lists.mysql.com
 Subject: RE: Help with purging old logs for each customer ID
 
 Off hand, I would iterate over the PRIMARY KEY, looking at a thousand rows
 at a time, DELETEing any that need to be purged.  I would use a Perl or
PHP
 loop, or write a stored procedure.  More discussion of huge deletes
(which
 this _could_ be):
 http://mysql.rjweb.org/doc.php/deletebig
 (PARTITIONing does not apply in your case, as I understand it.)
 
 I like the loop:
 
 SELECT @z := customer_log_id FROM customers_log LIMIT 1000, 1;
 DELETE FROM customers_log
 WHERE customer_log_id = @left_off AND customer_log_id  @z
   AND created_on  NOW() - INTERVAL 90 DAY;
 sleep a few seconds (to be a nice guy)
 
 Plus code to take care of iterating and terminating.
 
 That loop could be done continually.
 
 It seems that customer_id is irrelevant??
 
  -Original Message-
  From: Daevid Vincent [mailto:dae...@daevid.com]
  Sent: Thursday, October 25, 2012 11:46 AM
  To: mysql@lists.mysql.com
  Subject: Help with purging old logs for each customer ID
 
  I have a customer log table that is starting to rapidly fill up (we
  have hundreds of thousands of users, but many are transient, and use
  the service for a few months, or use the free trial and quit, etc.)
 
  CREATE TABLE `customers_log` (
`customer_log_id` bigint(20) unsigned NOT NULL auto_increment,
`customer_id` int(10) unsigned default '0',
`created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update
  CURRENT_TIMESTAMP,
`type`
  enum('View','Action','Admin','Search','Login','Logout','Access','Genera
  l','A
  PI'),
`source` enum('web','mobile','system'),
`body` text,
PRIMARY KEY  (`customer_log_id`),
KEY `created_on` (`created_on`),
KEY `customers_id` (`customer_id`)
  ) ENGINE=InnoDB
 
  What I'd like to do now is make a 'rolling log' in that I want to
  DELETE any entries older than 90 days for EACH `customer_id`.
 
  I'm not sure how to do that in a query? I'd rather not iterate over
  each customer_id if I can help it.
 
 
 
 
  --
  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


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

RE: Help with purging old logs for each customer ID

2012-10-25 Thread Rick James

If the 90 days is back from MAX(created_on) for a given customer...
   INDEX(customer_id, created_on)
will probably be needed.  And that should replace KEY `customers_id` 
(`customer_id`).

Maybe...

DELETE FROM customers_log AS a
WHERE a.customer_log_id = @left_off AND a.customer_log_id  @z
  AND a.created_on  
 ( SELECT MAX(created_on) FROM customers_log
 WHERE customer_id = a.customer_id
 )
  - INTERVAL 90 DAY );
(Since this has the subquery, I would do only 100 at a time, not 1000)

Or...
CREATE TEMPORARY TABLE tmp
SELECT customer_id, MAX(created_on) - INTERVAL 90 DAY  AS cutoff
FROM customers_log
GROUP BY customer_id;
DELETE FROM customers_log AS a
   JOIN tmp  ON a.customer_id = tmp.customer_id
WHERE a.customer_log_id = @left_off AND a.customer_log_id  @z
  AND a.created_on  tmp.cutoff;

If you have millions of rows, a delete without some kind of loop is asking for 
trouble.

Or...
Turning things around to base it on customers...
Loop through customer_ids (yeah, you did not want to do this)

SELECT @id := 0;
StartLoop:
SELECT @id := customer_id WHERE customer_id  @id ORDER BY customer_id 
LIMIT 1;
if @id is NULL, exit
DELETE FROM customers_log AS a
WHERE a.customer_id = @id
  AND a.created_on  
 ( SELECT MAX(created_on) FROM customers_log
 WHERE customer_id = @id
 )
  - INTERVAL 90 DAY ); 
EndLoop.

Since there is no rush for the purging, there is little need to optimize it 
other than to keep it from interfering with other queries.  To that end, the 
compound index I propose is important.

 -Original Message-
 From: Daevid Vincent [mailto:dae...@daevid.com]
 Sent: Thursday, October 25, 2012 1:33 PM
 To: Rick James; mysql@lists.mysql.com
 Subject: RE: Help with purging old logs for each customer ID
 
 Well, the customer_id is relevant in that I want the last 90 days
 relative to each customer.
 
 customer_id = 123 might have logs from jan - mar customer_id = 444
 might have logs from feb - may
 
 So it's a rolling log of THEIR last 90 days from their last log (most
 recent) back 90 days from there.
 
 Does that make more sense?
 
 I guess I was trying to avoid looping over every customer ID and
 computing if I could help it. I thought by using a GROUP BY or
 something it could group all the logs for a given customer and then
 trim them that way.
 
 But maybe brute force is the way to go?
 
  -Original Message-
  From: Rick James [mailto:rja...@yahoo-inc.com]
  Sent: Thursday, October 25, 2012 1:09 PM
  To: Daevid Vincent; mysql@lists.mysql.com
  Subject: RE: Help with purging old logs for each customer ID
 
  Off hand, I would iterate over the PRIMARY KEY, looking at a thousand
  rows at a time, DELETEing any that need to be purged.  I would use a
  Perl or
 PHP
  loop, or write a stored procedure.  More discussion of huge deletes
 (which
  this _could_ be):
  http://mysql.rjweb.org/doc.php/deletebig
  (PARTITIONing does not apply in your case, as I understand it.)
 
  I like the loop:
 
  SELECT @z := customer_log_id FROM customers_log LIMIT 1000, 1;
  DELETE FROM customers_log
  WHERE customer_log_id = @left_off AND customer_log_id  @z
AND created_on  NOW() - INTERVAL 90 DAY;
  sleep a few seconds (to be a nice guy)
 
  Plus code to take care of iterating and terminating.
 
  That loop could be done continually.
 
  It seems that customer_id is irrelevant??
 
   -Original Message-
   From: Daevid Vincent [mailto:dae...@daevid.com]
   Sent: Thursday, October 25, 2012 11:46 AM
   To: mysql@lists.mysql.com
   Subject: Help with purging old logs for each customer ID
  
   I have a customer log table that is starting to rapidly fill up (we
   have hundreds of thousands of users, but many are transient, and
 use
   the service for a few months, or use the free trial and quit, etc.)
  
   CREATE TABLE `customers_log` (
 `customer_log_id` bigint(20) unsigned NOT NULL auto_increment,
 `customer_id` int(10) unsigned default '0',
 `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on
   update CURRENT_TIMESTAMP,
 `type`
  
 enum('View','Action','Admin','Search','Login','Logout','Access','Gen
   era
   l','A
   PI'),
 `source` enum('web','mobile','system'),
 `body` text,
 PRIMARY KEY  (`customer_log_id`),
 KEY `created_on` (`created_on`),
 KEY `customers_id` (`customer_id`)
   ) ENGINE=InnoDB
  
   What I'd like to do now is make a 'rolling log' in that I want to
   DELETE any entries older than 90 days for EACH `customer_id`.
  
   I'm not sure how to do that in a query? I'd rather not iterate over
   each customer_id if I can help it.
  
  
  
  
   --
   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

RE: Help with purging old logs for each customer ID

2012-10-25 Thread Daevid Vincent

Thank you Rick! You're a super freak! ;-p

This gets me in the vicinity of where I'm trying to go and I learned a few
new tricks with the StartLoop: stuff too! Neat!

d.

 -Original Message-
 From: Rick James [mailto:rja...@yahoo-inc.com]
 Sent: Thursday, October 25, 2012 1:54 PM
 To: Daevid Vincent; mysql@lists.mysql.com
 Subject: RE: Help with purging old logs for each customer ID
 
 If the 90 days is back from MAX(created_on) for a given customer...
INDEX(customer_id, created_on)
 will probably be needed.  And that should replace KEY `customers_id`
 (`customer_id`).
 
 Maybe...
 
 DELETE FROM customers_log AS a
 WHERE a.customer_log_id = @left_off AND a.customer_log_id  @z
   AND a.created_on 
  ( SELECT MAX(created_on) FROM customers_log
  WHERE customer_id = a.customer_id
  )
   - INTERVAL 90 DAY );
 (Since this has the subquery, I would do only 100 at a time, not 1000)
 
 Or...
 CREATE TEMPORARY TABLE tmp
 SELECT customer_id, MAX(created_on) - INTERVAL 90 DAY  AS cutoff
 FROM customers_log
 GROUP BY customer_id;
 DELETE FROM customers_log AS a
JOIN tmp  ON a.customer_id = tmp.customer_id
 WHERE a.customer_log_id = @left_off AND a.customer_log_id  @z
   AND a.created_on  tmp.cutoff;
 
 If you have millions of rows, a delete without some kind of loop is asking
 for trouble.
 
 Or...
 Turning things around to base it on customers...
 Loop through customer_ids (yeah, you did not want to do this)
 
 SELECT @id := 0;
 StartLoop:
 SELECT @id := customer_id WHERE customer_id  @id ORDER BY customer_id
 LIMIT 1;
 if @id is NULL, exit
 DELETE FROM customers_log AS a
 WHERE a.customer_id = @id
   AND a.created_on 
  ( SELECT MAX(created_on) FROM customers_log
  WHERE customer_id = @id
  )
   - INTERVAL 90 DAY );
 EndLoop.
 
 Since there is no rush for the purging, there is little need to optimize
it
 other than to keep it from interfering with other queries.  To that end,
the
 compound index I propose is important.
 
  -Original Message-
  From: Daevid Vincent [mailto:dae...@daevid.com]
  Sent: Thursday, October 25, 2012 1:33 PM
  To: Rick James; mysql@lists.mysql.com
  Subject: RE: Help with purging old logs for each customer ID
 
  Well, the customer_id is relevant in that I want the last 90 days
  relative to each customer.
 
  customer_id = 123 might have logs from jan - mar customer_id = 444
  might have logs from feb - may
 
  So it's a rolling log of THEIR last 90 days from their last log (most
  recent) back 90 days from there.
 
  Does that make more sense?
 
  I guess I was trying to avoid looping over every customer ID and
  computing if I could help it. I thought by using a GROUP BY or
  something it could group all the logs for a given customer and then
  trim them that way.
 
  But maybe brute force is the way to go?
 
   -Original Message-
   From: Rick James [mailto:rja...@yahoo-inc.com]
   Sent: Thursday, October 25, 2012 1:09 PM
   To: Daevid Vincent; mysql@lists.mysql.com
   Subject: RE: Help with purging old logs for each customer ID
  
   Off hand, I would iterate over the PRIMARY KEY, looking at a thousand
   rows at a time, DELETEing any that need to be purged.  I would use a
   Perl or
  PHP
   loop, or write a stored procedure.  More discussion of huge deletes
  (which
   this _could_ be):
   http://mysql.rjweb.org/doc.php/deletebig
   (PARTITIONing does not apply in your case, as I understand it.)
  
   I like the loop:
  
   SELECT @z := customer_log_id FROM customers_log LIMIT 1000, 1;
   DELETE FROM customers_log
   WHERE customer_log_id = @left_off AND customer_log_id  @z
 AND created_on  NOW() - INTERVAL 90 DAY;
   sleep a few seconds (to be a nice guy)
  
   Plus code to take care of iterating and terminating.
  
   That loop could be done continually.
  
   It seems that customer_id is irrelevant??
  
-Original Message-
From: Daevid Vincent [mailto:dae...@daevid.com]
Sent: Thursday, October 25, 2012 11:46 AM
To: mysql@lists.mysql.com
Subject: Help with purging old logs for each customer ID
   
I have a customer log table that is starting to rapidly fill up (we
have hundreds of thousands of users, but many are transient, and
  use
the service for a few months, or use the free trial and quit, etc.)
   
CREATE TABLE `customers_log` (
  `customer_log_id` bigint(20) unsigned NOT NULL auto_increment,
  `customer_id` int(10) unsigned default '0',
  `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
  `type`
   
  enum('View','Action','Admin','Search','Login','Logout','Access','Gen
era
l','A
PI'),
  `source` enum('web','mobile','system'),
  `body` text,
  PRIMARY KEY  (`customer_log_id`),
  KEY `created_on` (`created_on

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-16 Thread Shawn Green


On 10/15/2012 7:15 PM, spameden wrote:

Thanks a lot for all your comments!

I did disable Query cache before testing with

set query_cache_type=OFF

for the current session.

I will report this to the MySQL bugs site later.




First. What are all of your logging settings?

SHOW GLOBAL VARIABLES LIKE '%log%';

Next. When you physically look in the slow query log, how long does it 
say that it took this command to execute?


And last, before you can ask MySQL to fix a bug, you must first ensure 
it's a MySQL bug. Please try to reproduce your results using official 
binaries, not those constructed by a third party.  If the problem exists 
in our packages, do tell us about it. If the problem is not reproducible 
using official MySQL products, then please report it to the appropriate 
channel for the product you are using.


MySQL Bugs -
http://bugs.mysql.com/

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



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

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-16 Thread spameden

Will do.

mysql  SHOW GLOBAL VARIABLES LIKE '%log%';
+-+-+
| Variable_name   | Value
|
+-+-+
| back_log| 50
|
| binlog_cache_size   | 32768
|
| binlog_direct_non_transactional_updates | OFF
|
| binlog_format   | MIXED
|
| expire_logs_days| 5
|
| general_log | OFF
|
| general_log_file| /var/run/mysqld/mysqld.log
|
| innodb_flush_log_at_trx_commit  | 2
|
| innodb_flush_log_at_trx_commit_session  | 3
|
| innodb_locks_unsafe_for_binlog  | OFF
|
| innodb_log_block_size   | 512
|
| innodb_log_buffer_size  | 8388608
|
| innodb_log_file_size| 2145386496
|
| innodb_log_files_in_group   | 2
|
| innodb_log_group_home_dir   | ./
|
| innodb_mirrored_log_groups  | 1
|
| innodb_overwrite_relay_log_info | OFF
|
| log | OFF
|
| log_bin | ON
|
| log_bin_trust_function_creators | ON
|
| log_bin_trust_routine_creators  | ON
|
| log_error   | /var/log/mysql-error.log
|
| log_output  | FILE
|
| log_queries_not_using_indexes   | ON
|
| log_slave_updates   | OFF
|
| log_slow_admin_statements   | OFF
|
| log_slow_filter |
|
| log_slow_queries| ON
|
| log_slow_rate_limit | 1
|
| log_slow_slave_statements   | OFF
|
| log_slow_sp_statements  | ON
|
| log_slow_timestamp_every| OFF
|
| log_slow_verbosity  | microtime
|
| log_warnings| 1
|
| max_binlog_cache_size   | 18446744073709547520
|
| max_binlog_size | 104857600
|
| max_relay_log_size  | 0
|
| relay_log   | /var/log/mysql/mysqld-relay-bin
|
| relay_log_index |
|
| relay_log_info_file | relay-log.info
|
| relay_log_purge | ON
|
| relay_log_space_limit   | 0
|
| slow_query_log  | ON
|
| slow_query_log_file | /var/log/mysql/mysql-slow.log
|
| slow_query_log_microseconds_timestamp   | OFF
|
| sql_log_bin | ON
|
| sql_log_off | OFF
|
| sql_log_update  | ON
|
| suppress_log_warning_1592   | OFF
|
| sync_binlog | 0
|
| use_global_log_slow_control | none
|
+-+-+
51 rows in set (0.01 sec)

Here is full output, but writing happens ONLY if
log_queries_not_using_indexes turned ON.

Query takes:
# Query_time: 0.291280  Lock_time: 0.50  Rows_sent: 0  Rows_examined:
133876  Rows_affected: 0  Rows_read: 1
# Bytes_sent: 1775  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: F229398
SET timestamp=1350389078;
SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id,
service, account, id, sms_type, mclass, mwi, coding, compress, validity,
deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo,
meta_data, task_id, msgid FROM send_sms_test FORCE INDEX (priority_time)
WHERE time = UNIX_TIMESTAMP(NOW()) ORDER by priority LIMIT 0,50;


2012/10/16 Shawn Green shawn.l.gr...@oracle.com

 On 10/15/2012 7:15 PM, spameden wrote:

 Thanks a lot for all your comments!

 I did disable Query cache before testing with

 set query_cache_type=OFF

 for the current session.

 I will report this to the MySQL bugs site later.



 First. What are all of your logging settings?

 SHOW GLOBAL VARIABLES LIKE '%log%';

 Next. When you physically look in the slow query log, how long does it say
 that it took this command to execute?

 And last, before you can ask MySQL to fix a bug, you must first ensure
 it's a MySQL bug. Please try to reproduce your results using official
 binaries, not those constructed by a third party.  If the problem exists in
 our packages, do tell us about it. If the problem is not reproducible using
 official MySQL products, then please report it to the appropriate channel
 for the product you are using.

 MySQL Bugs -
 http://bugs.mysql.com/

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



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

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-16 Thread Michael Dykman

your now() statement is getting executed for every row on the select.  try
ptting the phrase up front
as in:
set @ut= unix_timestamp(now())
and then use that in your statement.

On 2012-10-16 8:42 AM, spameden spame...@gmail.com wrote:

Will do.

mysql  SHOW GLOBAL VARIABLES LIKE '%log%';
+-+-+
| Variable_name   | Value
|
+-+-+
| back_log| 50
|
| binlog_cache_size   | 32768
|
| binlog_direct_non_transactional_updates | OFF
|
| binlog_format   | MIXED
|
| expire_logs_days| 5
|
| general_log | OFF
|
| general_log_file| /var/run/mysqld/mysqld.log
|
| innodb_flush_log_at_trx_commit  | 2
|
| innodb_flush_log_at_trx_commit_session  | 3
|
| innodb_locks_unsafe_for_binlog  | OFF
|
| innodb_log_block_size   | 512
|
| innodb_log_buffer_size  | 8388608
|
| innodb_log_file_size| 2145386496
|
| innodb_log_files_in_group   | 2
|
| innodb_log_group_home_dir   | ./
|
| innodb_mirrored_log_groups  | 1
|
| innodb_overwrite_relay_log_info | OFF
|
| log | OFF
|
| log_bin | ON
|
| log_bin_trust_function_creators | ON
|
| log_bin_trust_routine_creators  | ON
|
| log_error   | /var/log/mysql-error.log
|
| log_output  | FILE
|
| log_queries_not_using_indexes   | ON
|
| log_slave_updates   | OFF
|
| log_slow_admin_statements   | OFF
|
| log_slow_filter |
|
| log_slow_queries| ON
|
| log_slow_rate_limit | 1
|
| log_slow_slave_statements   | OFF
|
| log_slow_sp_statements  | ON
|
| log_slow_timestamp_every| OFF
|
| log_slow_verbosity  | microtime
|
| log_warnings| 1
|
| max_binlog_cache_size   | 18446744073709547520
|
| max_binlog_size | 104857600
|
| max_relay_log_size  | 0
|
| relay_log   | /var/log/mysql/mysqld-relay-bin
|
| relay_log_index |
|
| relay_log_info_file | relay-log.info
|
| relay_log_purge | ON
|
| relay_log_space_limit   | 0
|
| slow_query_log  | ON
|
| slow_query_log_file | /var/log/mysql/mysql-slow.log
|
| slow_query_log_microseconds_timestamp   | OFF
|
| sql_log_bin | ON
|
| sql_log_off | OFF
|
| sql_log_update  | ON
|
| suppress_log_warning_1592   | OFF
|
| sync_binlog | 0
|
| use_global_log_slow_control | none
|
+-+-+
51 rows in set (0.01 sec)

Here is full output, but writing happens ONLY if
log_queries_not_using_indexes turned ON.

Query takes:
# Query_time: 0.291280  Lock_time: 0.50  Rows_sent: 0  Rows_examined:
133876  Rows_affected: 0  Rows_read: 1
# Bytes_sent: 1775  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: F229398
SET timestamp=1350389078;
SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id,
service, account, id, sms_type, mclass, mwi, coding, compress, validity,
deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo,
meta_data, task_id, msgid FROM send_sms_test FORCE INDEX (priority_time)
WHERE time = UNIX_TIMESTAMP(NOW()) ORDER by priority LIMIT 0,50;



2012/10/16 Shawn Green shawn.l.gr...@oracle.com

 On 10/15/2012 7:15 PM, spameden wrote:

 T...

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-16 Thread spameden

Interesting thought, but I get the same result.

# Query_time: 0.001769  Lock_time: 0.001236 Rows_sent: 0  Rows_examined: 0
use kannel;
SET timestamp=1350413592;
select * from send_sms FORCE INDEX (priority_time) where time=@ut order by
priority limit 0,11;

the MySQL i'm using is 5.5.28 from dotdeb.org, pretty sure it's close to
the original except packaging scripts.

I will check this on the release from MySQL site and report back.

Thanks to all.

2012/10/16 Michael Dykman mdyk...@gmail.com

 your now() statement is getting executed for every row on the select.  try
 ptting the phrase up front
 as in:
 set @ut= unix_timestamp(now())
 and then use that in your statement.

 On 2012-10-16 8:42 AM, spameden spame...@gmail.com wrote:

 Will do.

 mysql  SHOW GLOBAL VARIABLES LIKE '%log%';

 +-+-+
 | Variable_name   | Value
 |

 +-+-+
 | back_log| 50
 |
 | binlog_cache_size   | 32768
 |
 | binlog_direct_non_transactional_updates | OFF
 |
 | binlog_format   | MIXED
 |
 | expire_logs_days| 5
 |
 | general_log | OFF
 |
 | general_log_file| /var/run/mysqld/mysqld.log
 |
 | innodb_flush_log_at_trx_commit  | 2
 |
 | innodb_flush_log_at_trx_commit_session  | 3
 |
 | innodb_locks_unsafe_for_binlog  | OFF
 |
 | innodb_log_block_size   | 512
 |
 | innodb_log_buffer_size  | 8388608
 |
 | innodb_log_file_size| 2145386496
 |
 | innodb_log_files_in_group   | 2
 |
 | innodb_log_group_home_dir   | ./
 |
 | innodb_mirrored_log_groups  | 1
 |
 | innodb_overwrite_relay_log_info | OFF
 |
 | log | OFF
 |
 | log_bin | ON
 |
 | log_bin_trust_function_creators | ON
 |
 | log_bin_trust_routine_creators  | ON
 |
 | log_error   | /var/log/mysql-error.log
 |
 | log_output  | FILE
 |
 | log_queries_not_using_indexes   | ON
 |
 | log_slave_updates   | OFF
 |
 | log_slow_admin_statements   | OFF
 |
 | log_slow_filter |
 |
 | log_slow_queries| ON
 |
 | log_slow_rate_limit | 1
 |
 | log_slow_slave_statements   | OFF
 |
 | log_slow_sp_statements  | ON
 |
 | log_slow_timestamp_every| OFF
 |
 | log_slow_verbosity  | microtime
 |
 | log_warnings| 1
 |
 | max_binlog_cache_size   | 18446744073709547520
 |
 | max_binlog_size | 104857600
 |
 | max_relay_log_size  | 0
 |
 | relay_log   | /var/log/mysql/mysqld-relay-bin
 |
 | relay_log_index |
 |
 | relay_log_info_file | relay-log.info
 |
 | relay_log_purge | ON
 |
 | relay_log_space_limit   | 0
 |
 | slow_query_log  | ON
 |
 | slow_query_log_file | /var/log/mysql/mysql-slow.log
 |
 | slow_query_log_microseconds_timestamp   | OFF
 |
 | sql_log_bin | ON
 |
 | sql_log_off | OFF
 |
 | sql_log_update  | ON
 |
 | suppress_log_warning_1592   | OFF
 |
 | sync_binlog | 0
 |
 | use_global_log_slow_control | none
 |

 +-+-+
 51 rows in set (0.01 sec)

 Here is full output, but writing happens ONLY if
 log_queries_not_using_indexes turned ON.

 Query takes:
 # Query_time: 0.291280  Lock_time: 0.50  Rows_sent: 0  Rows_examined:
 133876  Rows_affected: 0  Rows_read: 1
 # Bytes_sent: 1775  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
 # InnoDB_trx_id: F229398
 SET timestamp=1350389078;
 SELECT sql_id, momt, sender, receiver, udhdata, msgdata, time, smsc_id,
 service, account, id, sms_type, mclass, mwi, coding, compress, validity,
 deferred, dlr_mask, dlr_url, pid, alt_dcs, rpi, charset, boxc_id, binfo,
 meta_data, task_id, msgid FROM send_sms_test FORCE INDEX (priority_time)
 WHERE time = UNIX_TIMESTAMP(NOW()) ORDER by priority LIMIT 0,50;



 2012/10/16 Shawn Green shawn.l.gr...@oracle.com

  On 10/15/2012 7:15 PM, spameden wrote:
 
  T...

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-16 Thread hsv

 2012/10/16 12:57 -0400, Michael Dykman 
your now() statement is getting executed for every row on the select.  try
ptting the phrase up front
as in:
set @ut= unix_timestamp(now())
and then use that in your statement.

Quote:

Functions that return the current date or time each are evaluated only once per 
query at the start of query execution. This means that multiple references to a 
function such as 
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_nowNOW()
 within a single query always produce the same result. (For our purposes, a 
single query also includes a call to a stored program (stored routine, trigger, 
or event) and all subprograms called by that program.) This principle also 
applies to 
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_curdateCURDATE(),
 
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_curtimeCURTIME(),
 
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_utc-dateUTC_DATE(),
 
file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_utc-timeUTC_TIME(),
 file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/f!
 unctio
ns.html#function_utc-timestampUTC_TIMESTAMP(), and to any of their synonyms. 



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

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-16 Thread spameden

That's exactly what I thought when reading Michael's email, but tried
anyways, thanks for clarification :)

2012/10/16 h...@tbbs.net

  2012/10/16 12:57 -0400, Michael Dykman 
 your now() statement is getting executed for every row on the select.  try
 ptting the phrase up front
 as in:
 set @ut= unix_timestamp(now())
 and then use that in your statement.
 
 Quote:

 Functions that return the current date or time each are evaluated only
 once per query at the start of query execution. This means that multiple
 references to a function such as
 file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_nowNOW()
 within a single query always produce the same result. (For our purposes, a
 single query also includes a call to a stored program (stored routine,
 trigger, or event) and all subprograms called by that program.) This
 principle also applies to
 file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_curdateCURDATE(),
 file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_curtimeCURTIME(),
 file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_utc-dateUTC_DATE(),
 file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_utc-timeUTC_TIME(),
 file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/f!
  unctio
 ns.html#function_utc-timestampUTC_TIMESTAMP(), and to any of their
 synonyms.



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

mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread spameden

Hi, list.

Sorry for the long subject, but I'm really interested in solving this and
need a help:

I've got a table:

mysql show create table send_sms_test;
+---+---+
| Table | Create
Table
|
+---+---+
| send_sms_test | CREATE TABLE `send_sms_test` (
  `sql_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `momt` enum('MO','MT') DEFAULT NULL,
  `sender` varchar(20) DEFAULT NULL,
  `receiver` varchar(20) DEFAULT NULL,
  `udhdata` blob,
  `msgdata` text,
  `time` bigint(20) NOT NULL,
  `smsc_id` varchar(255) DEFAULT 'main',
  `service` varchar(255) DEFAULT NULL,
  `account` varchar(255) DEFAULT NULL,
  `id` bigint(20) DEFAULT NULL,
  `sms_type` tinyint(1) DEFAULT '2',
  `mclass` bigint(20) DEFAULT NULL,
  `mwi` bigint(20) DEFAULT NULL,
  `coding` bigint(20) DEFAULT NULL,
  `compress` bigint(20) DEFAULT NULL,
  `validity` bigint(20) DEFAULT NULL,
  `deferred` bigint(20) DEFAULT NULL,
  `dlr_mask` bigint(20) DEFAULT NULL,
  `dlr_url` varchar(255) DEFAULT NULL,
  `pid` bigint(20) DEFAULT NULL,
  `alt_dcs` bigint(20) DEFAULT NULL,
  `rpi` bigint(20) DEFAULT NULL,
  `charset` varchar(255) DEFAULT NULL,
  `boxc_id` varchar(255) DEFAULT NULL,
  `binfo` varchar(255) DEFAULT NULL,
  `meta_data` text,
  `task_id` bigint(20) DEFAULT NULL,
  `msgid` bigint(20) DEFAULT NULL,
  `priority` int(3) unsigned NOT NULL DEFAULT '500',
  PRIMARY KEY (`sql_id`),
  KEY `task_id` (`task_id`),
  KEY `receiver` (`receiver`),
  KEY `msgid` (`msgid`),
  KEY `priority_time` (`priority`,`time`)
) ENGINE=InnoDB AUTO_INCREMENT=7806318 DEFAULT CHARSET=utf8

Slow-queries turned on with an option:
| log_queries_not_using_indexes | ON|

mysqld --version
mysqld  Ver 5.1.65-rel14.0 for debian-linux-gnu on x86_64 ((Percona Server
(GPL), 14.0, Revision 475))

If I check with EXPLAIN MySQL says it would use the index:
mysql *desc select * from send_sms_test where time=UNIX_TIMESTAMP(NOW())
order by priority limit 0,11;*
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type  | possible_keys | key
| key_len | ref  | rows | Extra   |
++-+---+---+---+---+-+--+--+-+
|  1

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread spameden

Hi, I've just checked on MySQL-5.5.28

it acts absolutely same.

I need to use (priority,time) KEY instead of (time, priority) because query
results in better performance.

With first key used there is no need to sort at all, whilst if using latter:
mysql *desc select * from send_sms_test FORCE INDEX (time_priority) where
time=UNIX_TIMESTAMP(NOW()) order by priority limit 0,13;*
++-+---+---+---+---+-+--+---+-+
| id | select_type | table | type  | possible_keys | key
| key_len | ref  | rows  | Extra   |
++-+---+---+---+---+-+--+---+-+
|  1 | SIMPLE  | send_sms_test | range | time_priority | time_priority
| 8   | NULL | 73920 | Using where; *Using filesort* |
++-+---+---+---+---+-+--+---+-+
1 row in set (0.00 sec)

It uses filesort and results in a worser performance...

Any suggestions ? Should I submit a bug?

2012/10/16 spameden spame...@gmail.com

 Hi, list.

 Sorry for the long subject, but I'm really interested in solving this and
 need a help:

 I've got a table:

 mysql show create table send_sms_test;

 +---+---+
 | Table | Create
 Table
 |

 +---+---+
 | send_sms_test | CREATE TABLE `send_sms_test` (
   `sql_id` bigint(20) NOT NULL AUTO_INCREMENT,
   `momt` enum('MO','MT') DEFAULT NULL,
   `sender` varchar(20) DEFAULT NULL,
   `receiver` varchar(20) DEFAULT NULL,
   `udhdata` blob,
   `msgdata` text,
   `time` bigint(20) NOT NULL,
   `smsc_id` varchar(255) DEFAULT 'main',
   `service` varchar(255) DEFAULT NULL,
   `account` varchar(255) DEFAULT NULL,
   `id` bigint(20) DEFAULT NULL,
   `sms_type` tinyint(1) DEFAULT '2',
   `mclass` bigint(20) DEFAULT NULL,
   `mwi` bigint(20) DEFAULT NULL,
   `coding` bigint(20) DEFAULT NULL,
   `compress` bigint(20) DEFAULT NULL,
   `validity` bigint(20) DEFAULT NULL,
   `deferred` bigint(20) DEFAULT NULL,
   `dlr_mask` bigint(20) DEFAULT NULL,
   `dlr_url` varchar(255) DEFAULT NULL,
   `pid` bigint(20) DEFAULT NULL,
   `alt_dcs`

RE: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread Rick James

* Rows = 11 / 22 -- don't take the numbers too seriously; they are crude 
approximations based on estimated cardinality.

* The 11 comes from the LIMIT -- therefore useless in judging the efficiency.  
(The 22 may be 2*11; I don't know.)

* Run the EXPLAINs without LIMIT -- that will avoid the bogus 11/22.

* If the CREATE INDEX took only 0.67 sec, I surmise that you have very few rows 
in the table??  So this discussion is not necessarily valid in general cases.

* What percentage of time values meet the WHERE?  This has a big impact on the 
choice of explain plan and performance.

* Set long_query_time = 0; to get it in the slowlog even if it is fast.  Then 
look at the various extra values (such as filesort, on disk, temp table used, 
etc).

* Do this (with each index):
SHOW SESSION STATUS LIKE 'Handler_read%';
SELECT ... FORCE INDEX(...) ...;
SHOW SESSION STATUS LIKE 'Handler_read%';
Then take the diffs of the handler counts.  This will give you a pretty 
detailed idea of what is going on; better than the SlowLog.

* INT(3) is not a 3-digit integer, it is a full 32-bit integer (4 bytes).  
Perhaps you should have SMALLINT UNSIGNED (2 bytes).

* BIGINT takes 8 bytes -- usually over-sized.


 -Original Message-
 From: spameden [mailto:spame...@gmail.com]
 Sent: Monday, October 15, 2012 1:42 PM
 To: mysql@lists.mysql.com
 Subject: mysql logs query with indexes used to the slow-log and not
 logging if there is index in reverse order
 
 Hi, list.
 
 Sorry for the long subject, but I'm really interested in solving this
 and need a help:
 
 I've got a table:
 
 mysql show create table send_sms_test;
 +---+--
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---+
 | Table | Create
 Table
 |
 +---+--
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---
 ---+
 | send_sms_test | CREATE TABLE `send_sms_test` (
   `sql_id` bigint(20) NOT NULL AUTO_INCREMENT,
   `momt` enum('MO','MT') DEFAULT NULL,
   `sender` varchar(20) DEFAULT NULL,
   `receiver` varchar(20) DEFAULT NULL,
   `udhdata` blob,
   `msgdata` text,
   `time` bigint(20) NOT NULL,
   `smsc_id` varchar(255) DEFAULT 'main',
   `service` varchar(255) DEFAULT NULL,
   `account` varchar(255) DEFAULT NULL,
   `id` bigint(20) DEFAULT NULL,
   `sms_type` tinyint(1) DEFAULT '2',
   `mclass

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread spameden

|
| Handler_read_next | 576090 |
| Handler_read_prev | 0  |
| Handler_read_rnd  | 126|
| Handler_read_rnd_next | 223|
+---++
6 rows in set (0.00 sec)

mysql select * from send_sms_test FORCE INDEX (time_priority) where
time=UNIX_TIMESTAMP(NOW()) order by priority LIMIT 0,100;
100 rows in set (0.09 sec)

mysql SHOW SESSION STATUS LIKE 'Handler_read%';
+---++
| Variable_name | Value  |
+---++
| Handler_read_first| 18 |
| Handler_read_key  | 244|
| Handler_read_next | 719969 |
| Handler_read_prev | 0  |
| Handler_read_rnd  | 226|
| Handler_read_rnd_next | 223|
+---++
6 rows in set (0.00 sec)

I don't understand much in Handler thing, could you please explain more,
based on the results I've posted ? In which case it works better and how it
uses the index?

About BIGINT(20) and INT(3) I will look further into this later, I
understand it might be oversized, but my main question is about index why
it's using it so weird.

Many thanks for your quick answer!
2012/10/16 Rick James rja...@yahoo-inc.com

 * Rows = 11 / 22 -- don't take the numbers too seriously; they are crude
 approximations based on estimated cardinality.

 * The 11 comes from the LIMIT -- therefore useless in judging the
 efficiency.  (The 22 may be 2*11; I don't know.)

 * Run the EXPLAINs without LIMIT -- that will avoid the bogus 11/22.

 * If the CREATE INDEX took only 0.67 sec, I surmise that you have very few
 rows in the table??  So this discussion is not necessarily valid in general
 cases.

 * What percentage of time values meet the WHERE?  This has a big impact on
 the choice of explain plan and performance.

 * Set long_query_time = 0; to get it in the slowlog even if it is fast.
  Then look at the various extra values (such as filesort, on disk, temp
 table used, etc).

 * Do this (with each index):
 SHOW SESSION STATUS LIKE 'Handler_read%';
 SELECT ... FORCE INDEX(...) ...;
 SHOW SESSION STATUS LIKE 'Handler_read%';
 Then take the diffs of the handler counts.  This will give you a pretty
 detailed idea of what is going on; better than the SlowLog.

 * INT(3) is not a 3-digit integer, it is a full 32-bit integer (4 bytes).
  Perhaps you should have SMALLINT UNSIGNED (2 bytes).

 * BIGINT takes 8 bytes -- usually over-sized.


  -Original Message-
  From: spameden [mailto:spame...@gmail.com]
  Sent: Monday, October 15, 2012 1:42 PM
  To: mysql@lists.mysql.com
  Subject: mysql logs query with indexes used to the slow-log and not
  logging if there is index in reverse order
 
  Hi, list.
 
  Sorry for the long subject, but I'm really interested in solving this
  and need a help:
 
  I've got a table:
 
  mysql show create table send_sms_test;
  +---+--
  ---
  ---
  ---
  ---
  ---
  ---
  ---
  ---
  ---
  ---
  ---
  ---
  ---
  ---
  ---
  ---
  ---
  ---
  ---+
  | Table | Create
  Table

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread spameden

Sorry, forgot to say:

mysql show variables like 'long_query_time%';
+-+---+
| Variable_name   | Value |
+-+---+
| long_query_time | 10.00 |
+-+---+
1 row in set (0.00 sec)

It's getting in the log only due:

mysql show variables like '%indexes%';
+---+---+
| Variable_name | Value |
+---+---+
| log_queries_not_using_indexes | ON|
+---+---+
1 row in set (0.00 sec)

If I turn it off - it's all fine

My initial question was why MySQL logs it in the slow log if the query uses
an INDEX?

And why it's not logging if I create an INDEX (time, priority) (but in the
query there is FORCE INDEX (priority,time) specified, so MySQL shouldn't
use newly created INDEX (time, priority) at all).

2012/10/16 spameden spame...@gmail.com

 Sorry, my previous e-mail was a test on MySQL-5.5.28 on an empty table.

 Here is the MySQL-5.1 Percona testing table:

 mysql select count(*) from send_sms_test;
 +--+
 | count(*) |
 +--+
 |   143879 |
 +--+
 1 row in set (0.03 sec)

 Without LIMIT:
 mysql desc select * from send_sms_test FORCE INDEX (time_priority) where
 time=UNIX_TIMESTAMP(NOW()) order by priority;

 ++-+---+---+---+---+-+--+---+-+

 | id | select_type | table | type  | possible_keys | key
 | key_len | ref  | rows  | Extra   |

 ++-+---+---+---+---+-+--+---+-+
 |  1 | SIMPLE  | send_sms_test | range | time_priority | time_priority
 | 8   | NULL | 73920 | Using where; Using filesort |

 ++-+---+---+---+---+-+--+---+-+
 1 row in set (0.00 sec)

 mysql desc select * from send_sms_test FORCE INDEX (priority_time) where
 time=UNIX_TIMESTAMP(NOW()) order by priority;

 ++-+---+---+---+---+-+--++-+

 | id | select_type | table | type  | possible_keys | key
 | key_len | ref  | rows   | Extra   |

 ++-+---+---+---+---+-+--++-+
 |  1 | SIMPLE  | send_sms_test | index | NULL  | priority_time
 | 12  | NULL | 147840 | Using where |

 ++-+---+---+---+---+-+--++-+

 1 row in set (0.00 sec)

 But I actually need to use LIMIT, because client uses this to limit the
 number of records returned to process.

 mysql select * from send_sms_test FORCE INDEX (priority_time) where
 time=UNIX_TIMESTAMP(NOW()) order by priority LIMIT 0,100;
 100 rows in set (0.00 sec)

 mysql show profile;
 ++--+
 | Status | Duration |
 ++--+
 | starting   | 0.53 |
 | Opening tables | 0.09 |
 | System lock| 0.05 |
 | Table lock | 0.04 |
 | init   | 0.37 |
 | optimizing | 0.05 |
 | statistics | 0.07 |
 | preparing  | 0.05 |
 | executing  | 0.01 |
 | Sorting result | 0.03 |
 | Sending data   | 0.000856 |
 | end| 0.03 |
 | query end  | 0.01 |
 | freeing items  | 0.15 |
 | logging slow query | 0.01 |
 | logging slow query | 0.47 |
 | cleaning up| 0.02 |
 ++--+
 17 rows in set (0.00 sec)

 mysql select * from send_sms_test FORCE INDEX (time_priority) where
 time=UNIX_TIMESTAMP(NOW()) order by priority LIMIT 0,100;
 100 rows in set (0.08 sec)
 mysql show profile;
 ++--+
 | Status | Duration |
 ++--+
 | starting   | 0.48 |
 | Opening tables | 0.09 |
 | System lock| 0.02 |
 | Table lock | 0.04 |
 | init   | 0.47 |
 | optimizing | 0.06 |
 | statistics | 0.43 |
 | preparing  | 0.18 |
 | executing  | 0.01 |
 | Sorting result | 0.076725 |
 | Sending data   | 0.001406 |
 | end| 0.03 |
 | query end  | 0.01 |
 | freeing items  | 0.12 |
 | logging slow query | 0.01 |
 | cleaning up| 0.02 |
 ++--+
 16 rows in set (0.00 sec)

 As you can see latter query takes more time, because it's using filesort
 as well.

 Now, handler:
 mysql SHOW SESSION STATUS LIKE 'Handler_read%';select * from
 send_sms_test FORCE INDEX (priority_time) where time=UNIX_TIMESTAMP(NOW())
 order by priority LIMIT 0,100;SHOW SESSION STATUS LIKE

RE: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread Rick James

I don't fully understand Handler numbers, either.  But note the vast difference 
in Handler_read_next, as if the second test had to read (sequentially scan) a 
lot more stuff (in the index or the data).

Summary:
   INDEX(time, priority) -- slower; bigger Handler numbers; shorter key_len; 
filesort
   INDEX(priority, time) -- faster; smaller; seems to use both keys of the 
index (key_len=12); avoids filesort (because INDEX(priority, ...) agrees with 
ORDER BY priority).

The Optimizer has (at some level) two choices:

* Start with the WHERE

* Start with the ORDER BY
Since the ORDER BY matches one of the indexes, it can avoid the sort and stop 
with the LIMIT.  However, if most of the rows failed the WHERE clause, this 
could be the wrong choice.
That is, it is hard for the optimizer to get a query like this right every 
time.

To see what I mean, flip the inequality in WHERE time = ... around;  I think 
the results will be disappointing.

If you had more than a million rows, I would bring up PARTITIONing as a assist 
to this 2-dimensional type of problem.

From: spameden [mailto:spame...@gmail.com]
Sent: Monday, October 15, 2012 3:23 PM
To: Rick James
Cc: mysql@lists.mysql.com
Subject: Re: mysql logs query with indexes used to the slow-log and not logging 
if there is index in reverse order

Sorry, my previous e-mail was a test on MySQL-5.5.28 on an empty table.

Here is the MySQL-5.1 Percona testing table:

mysql select count(*) from send_sms_test;
+--+
| count(*) |
+--+
|   143879 |
+--+
1 row in set (0.03 sec)

Without LIMIT:
mysql desc select * from send_sms_test FORCE INDEX (time_priority) where 
time=UNIX_TIMESTAMP(NOW()) order by priority;
++-+---+---+---+---+-+--+---+-+
| id | select_type | table | type  | possible_keys | key   | 
key_len | ref  | rows  | Extra   |
++-+---+---+---+---+-+--+---+-+
|  1 | SIMPLE  | send_sms_test | range | time_priority | time_priority | 8  
 | NULL | 73920 | Using where; Using filesort |
++-+---+---+---+---+-+--+---+-+
1 row in set (0.00 sec)

mysql desc select * from send_sms_test FORCE INDEX (priority_time) where 
time=UNIX_TIMESTAMP(NOW()) order by priority;
++-+---+---+---+---+-+--++-+
| id | select_type | table | type  | possible_keys | key   | 
key_len | ref  | rows   | Extra   |
++-+---+---+---+---+-+--++-+
|  1 | SIMPLE  | send_sms_test | index | NULL  | priority_time | 12 
 | NULL | 147840 | Using where |
++-+---+---+---+---+-+--++-+
1 row in set (0.00 sec)

But I actually need to use LIMIT, because client uses this to limit the number 
of records returned to process.

mysql select * from send_sms_test FORCE INDEX (priority_time) where 
time=UNIX_TIMESTAMP(NOW()) order by priority LIMIT 0,100;
100 rows in set (0.00 sec)

mysql show profile;
++--+
| Status | Duration |
++--+
| starting   | 0.53 |
| Opening tables | 0.09 |
| System lock| 0.05 |
| Table lock | 0.04 |
| init   | 0.37 |
| optimizing | 0.05 |
| statistics | 0.07 |
| preparing  | 0.05 |
| executing  | 0.01 |
| Sorting result | 0.03 |
| Sending data   | 0.000856 |
| end| 0.03 |
| query end  | 0.01 |
| freeing items  | 0.15 |
| logging slow query | 0.01 |
| logging slow query | 0.47 |
| cleaning up| 0.02 |
++--+
17 rows in set (0.00 sec)

mysql select * from send_sms_test FORCE INDEX (time_priority) where 
time=UNIX_TIMESTAMP(NOW()) order by priority LIMIT 0,100;
100 rows in set (0.08 sec)
mysql show profile;
++--+
| Status | Duration |
++--+
| starting   | 0.48 |
| Opening tables | 0.09 |
| System lock| 0.02 |
| Table lock | 0.04 |
| init   | 0.47 |
| optimizing | 0.06 |
| statistics | 0.43 |
| preparing  | 0.18 |
| executing  | 0.01 |
| Sorting result | 0.076725 |
| Sending data   | 0.001406 |
| end| 0.03 |
| query end  | 0.01 |
| freeing items  | 0.12 |
| logging slow query | 0.01 |
| cleaning up| 0.02

RE: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread Rick James

Ø  My initial question was why MySQL logs it in the slow log if the query uses 
an INDEX?

That _may_ be worth a bug report.

A _possible_ answer...  EXPLAIN presents what the optimizer is in the mood for 
at that moment.  It does not necessarily reflect what it was in the mood for 
when it ran the query.

When timing things, run them twice (and be sure not to hit the Query cache).  
The first time freshens the cache (buffer_pool, etc); the second time gives you 
a 'reproducible' time.  I believe (without proof) that the cache contents can 
affect the optimizer's choice.

From: spameden [mailto:spame...@gmail.com]
Sent: Monday, October 15, 2012 3:29 PM
To: Rick James
Cc: mysql@lists.mysql.com
Subject: Re: mysql logs query with indexes used to the slow-log and not logging 
if there is index in reverse order

Sorry, forgot to say:

mysql show variables like 'long_query_time%';
+-+---+
| Variable_name   | Value |
+-+---+
| long_query_time | 10.00 |
+-+---+
1 row in set (0.00 sec)

It's getting in the log only due:

mysql show variables like '%indexes%';
+---+---+
| Variable_name | Value |
+---+---+
| log_queries_not_using_indexes | ON|
+---+---+
1 row in set (0.00 sec)

If I turn it off - it's all fine

My initial question was why MySQL logs it in the slow log if the query uses an 
INDEX?

And why it's not logging if I create an INDEX (time, priority) (but in the 
query there is FORCE INDEX (priority,time) specified, so MySQL shouldn't use 
newly created INDEX (time, priority) at all).
2012/10/16 spameden spame...@gmail.commailto:spame...@gmail.com
Sorry, my previous e-mail was a test on MySQL-5.5.28 on an empty table.

Here is the MySQL-5.1 Percona testing table:

mysql select count(*) from send_sms_test;
+--+
| count(*) |
+--+
|   143879 |
+--+
1 row in set (0.03 sec)

Without LIMIT:
mysql desc select * from send_sms_test FORCE INDEX (time_priority) where 
time=UNIX_TIMESTAMP(NOW()) order by priority;
++-+---+---+---+---+-+--+---+-+

| id | select_type | table | type  | possible_keys | key   | 
key_len | ref  | rows  | Extra   |
++-+---+---+---+---+-+--+---+-+
|  1 | SIMPLE  | send_sms_test | range | time_priority | time_priority | 8  
 | NULL | 73920 | Using where; Using filesort |
++-+---+---+---+---+-+--+---+-+
1 row in set (0.00 sec)
mysql desc select * from send_sms_test FORCE INDEX (priority_time) where 
time=UNIX_TIMESTAMP(NOW()) order by priority;
++-+---+---+---+---+-+--++-+

| id | select_type | table | type  | possible_keys | key   | 
key_len | ref  | rows   | Extra   |
++-+---+---+---+---+-+--++-+
|  1 | SIMPLE  | send_sms_test | index | NULL  | priority_time | 12 
 | NULL | 147840 | Using where |
++-+---+---+---+---+-+--++-+

1 row in set (0.00 sec)
But I actually need to use LIMIT, because client uses this to limit the number 
of records returned to process.

mysql select * from send_sms_test FORCE INDEX (priority_time) where 
time=UNIX_TIMESTAMP(NOW()) order by priority LIMIT 0,100;
100 rows in set (0.00 sec)

mysql show profile;
++--+
| Status | Duration |
++--+
| starting   | 0.53 |
| Opening tables | 0.09 |
| System lock| 0.05 |
| Table lock | 0.04 |
| init   | 0.37 |
| optimizing | 0.05 |
| statistics | 0.07 |
| preparing  | 0.05 |
| executing  | 0.01 |
| Sorting result | 0.03 |
| Sending data   | 0.000856 |
| end| 0.03 |
| query end  | 0.01 |
| freeing items  | 0.15 |
| logging slow query | 0.01 |
| logging slow query | 0.47 |
| cleaning up| 0.02 |
++--+
17 rows in set (0.00 sec)

mysql select * from send_sms_test FORCE INDEX (time_priority) where 
time=UNIX_TIMESTAMP(NOW()) order by priority LIMIT 0,100;
100 rows in set (0.08 sec)
mysql show profile;
++--+
| Status | Duration |
++--+
| starting   | 0.48 |
| Opening tables | 0.09 |
| System lock| 0.02 |
| Table lock

Re: mysql logs query with indexes used to the slow-log and not logging if there is index in reverse order

2012-10-15 Thread spameden

Thanks a lot for all your comments!

I did disable Query cache before testing with

set query_cache_type=OFF

for the current session.

I will report this to the MySQL bugs site later.



2012/10/16 Rick James rja...@yahoo-inc.com

 **Ø  **My initial question was why MySQL logs it in the slow log if the
 query uses an INDEX?

 

 That _may_ be worth a bug report.

 ** **

 A _possible_ answer...  EXPLAIN presents what the optimizer is in the mood
 for at that moment.  It does not necessarily reflect what it was in the
 mood for when it ran the query.

 ** **

 When timing things, run them twice (and be sure not to hit the Query
 cache).  The first time freshens the cache (buffer_pool, etc); the second
 time gives you a 'reproducible' time.  I believe (without proof) that the
 cache contents can affect the optimizer's choice.

 ** **

 *From:* spameden [mailto:spame...@gmail.com]
 *Sent:* Monday, October 15, 2012 3:29 PM

 *To:* Rick James
 *Cc:* mysql@lists.mysql.com
 *Subject:* Re: mysql logs query with indexes used to the slow-log and not
 logging if there is index in reverse order

 ** **

 Sorry, forgot to say:

 mysql show variables like 'long_query_time%';
 +-+---+
 | Variable_name   | Value |
 +-+---+
 | long_query_time | 10.00 |
 +-+---+
 1 row in set (0.00 sec)

 It's getting in the log only due:

 mysql show variables like '%indexes%';
 +---+---+
 | Variable_name | Value |
 +---+---+
 | log_queries_not_using_indexes | ON|
 +---+---+
 1 row in set (0.00 sec)

 If I turn it off - it's all fine

 My initial question was why MySQL logs it in the slow log if the query
 uses an INDEX?

 And why it's not logging if I create an INDEX (time, priority) (but in the
 query there is FORCE INDEX (priority,time) specified, so MySQL shouldn't
 use newly created INDEX (time, priority) at all).

 2012/10/16 spameden spame...@gmail.com

 Sorry, my previous e-mail was a test on MySQL-5.5.28 on an empty table.

 Here is the MySQL-5.1 Percona testing table:

 mysql select count(*) from send_sms_test;
 +--+
 | count(*) |
 +--+
 |   143879 |
 +--+
 1 row in set (0.03 sec)

 Without LIMIT:
 mysql desc select * from send_sms_test FORCE INDEX (time_priority) where
 time=UNIX_TIMESTAMP(NOW()) order by priority;

 ++-+---+---+---+---+-+--+---+-+
 


 | id | select_type | table | type  | possible_keys | key
 | key_len | ref  | rows  | Extra   |


 ++-+---+---+---+---+-+--+---+-+
 |  1 | SIMPLE  | send_sms_test | range | time_priority | time_priority
 | 8   | NULL | 73920 | Using where; Using filesort |

 ++-+---+---+---+---+-+--+---+-+
 

 1 row in set (0.00 sec)

 mysql desc select * from send_sms_test FORCE INDEX (priority_time) where
 time=UNIX_TIMESTAMP(NOW()) order by priority;

 ++-+---+---+---+---+-+--++-+
 


 | id | select_type | table | type  | possible_keys | key
 | key_len | ref  | rows   | Extra   |


 ++-+---+---+---+---+-+--++-+
 |  1 | SIMPLE  | send_sms_test | index | NULL  | priority_time
 | 12  | NULL | 147840 | Using where |

 ++-+---+---+---+---+-+--++-+
 


 1 row in set (0.00 sec)

 But I actually need to use LIMIT, because client uses this to limit the
 number of records returned to process.

 mysql select * from send_sms_test FORCE INDEX (priority_time) where
 time=UNIX_TIMESTAMP(NOW()) order by priority LIMIT 0,100;
 100 rows in set (0.00 sec)

 mysql show profile;
 ++--+
 | Status | Duration |
 ++--+
 | starting   | 0.53 |
 | Opening tables | 0.09 |
 | System lock| 0.05 |
 | Table lock | 0.04 |
 | init   | 0.37 |
 | optimizing | 0.05 |
 | statistics | 0.07 |
 | preparing  | 0.05 |
 | executing  | 0.01 |
 | Sorting result | 0.03 |
 | Sending data   | 0.000856 |
 | end| 0.03 |
 | query end  | 0.01 |
 | freeing items  | 0.15 |
 | logging slow query | 0.01 |
 | logging slow query | 0.47 |
 | cleaning up| 0.02 |
 ++--+
 17 rows in set (0.00

show master status; show binary logs

2012-04-18 Thread Hal�sz S�ndor

In the command show binary logs one may indifferently write binary or 
master, and it is so for some other commands associated with this 
function--but for the command show master status there is no such variant. 
Why? Is it considered obsolescent?


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

Re: show master status; show binary logs

2012-04-18 Thread Claudio Nanni

Hi Halasz,

This happens quite often.
It can be because more meaningful commands, or more compliant to the
standard are found,
or commands more consistent with the rest of the syntax.
for instance look at slow query log parameter change
herehttp://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
Usually not to create problems alias are introduced for quite long time and
then only at a certain point (very late) made obsolete.
You may read the Release Notes of the release you are using, and/or the
previous ones, to know what changes have been done.
In any case it shouldn't represent any problem.

Cheers

Claudio

2012/4/17 Halász Sándor h...@tbbs.net

 In the command show binary logs one may indifferently write binary or
 master, and it is so for some other commands associated with this
 function--but for the command show master status there is no such
 variant. Why? Is it considered obsolescent?


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




-- 
Claudio

Re: Logs not working

2010-09-05 Thread monloi perez

Thansk,

Will try to turn on log_warnings.

-Mon





From: Nitin Mehta ntn...@yahoo.com
To: monloi perez mlp_fol...@yahoo.com; Ananda Kumar anan...@gmail.com
Cc: mysql mailing list mysql@lists.mysql.com
Sent: Sun, September 5, 2010 12:03:45 PM
Subject: Re: Logs not working

I believe that will not be logged unless you have enabled log_warnings. Too 
many 

connections would normally mean that the number is going beyond the configured 
limit and denying new sessions is an expected behavior and should not be an 
error really.

Regards,
Nitin





From: monloi perez mlp_fol...@yahoo.com
To: Ananda Kumar anan...@gmail.com
Cc: mysql mailing list mysql@lists.mysql.com
Sent: Sat, September 4, 2010 11:37:42 AM
Subject: Re: Logs not working

on the db server? meaning  the mysqld log right? THere is really no data for 
teh 


current error. The last error they said was too much connections and shouldnt 
that be logged in mysqld.log?

Thanks,
Mon





From: Ananda Kumar anan...@gmail.com
To: monloi perez mlp_fol...@yahoo.com
Cc: mysql mailing list mysql@lists.mysql.com
Sent: Thu, September 2, 2010 6:14:26 PM
Subject: Re: Logs not working


Did u check the logs on the db server, to see what the issue was.

regards
anandkl


On Thu, Sep 2, 2010 at 6:25 AM, monloi perez mlp_fol...@yahoo.com wrote:

All,

I'm not sure if this is the right mailing list since the specific mailing lists
doesn't seem to meet my concern.

For some reason mysql client stops logging to mysqd.log. We had an issue on Aug
20. But when I checked the log, the latest was on Aug 2.
Any idea on how to resolve this or what caused it?

Thanks,
Mon

Re: Logs not working

2010-09-04 Thread monloi perez

on the db server? meaning  the mysqld log right? THere is really no data for 
teh 
current error. The last error they said was too much connections and shouldnt 
that be logged in mysqld.log?

Thanks,
Mon





From: Ananda Kumar anan...@gmail.com
To: monloi perez mlp_fol...@yahoo.com
Cc: mysql mailing list mysql@lists.mysql.com
Sent: Thu, September 2, 2010 6:14:26 PM
Subject: Re: Logs not working


Did u check the logs on the db server, to see what the issue was.
 
regards
anandkl


On Thu, Sep 2, 2010 at 6:25 AM, monloi perez mlp_fol...@yahoo.com wrote:

All,

I'm not sure if this is the right mailing list since the specific mailing lists
doesn't seem to meet my concern.

For some reason mysql client stops logging to mysqd.log. We had an issue on Aug
20. But when I checked the log, the latest was on Aug 2.
Any idea on how to resolve this or what caused it?

Thanks,
Mon

Re: Logs not working

2010-09-04 Thread Nitin Mehta

I believe that will not be logged unless you have enabled log_warnings. Too 
many 
connections would normally mean that the number is going beyond the configured 
limit and denying new sessions is an expected behavior and should not be an 
error really.

Regards,
Nitin





From: monloi perez mlp_fol...@yahoo.com
To: Ananda Kumar anan...@gmail.com
Cc: mysql mailing list mysql@lists.mysql.com
Sent: Sat, September 4, 2010 11:37:42 AM
Subject: Re: Logs not working

on the db server? meaning  the mysqld log right? THere is really no data for 
teh 

current error. The last error they said was too much connections and shouldnt 
that be logged in mysqld.log?

Thanks,
Mon





From: Ananda Kumar anan...@gmail.com
To: monloi perez mlp_fol...@yahoo.com
Cc: mysql mailing list mysql@lists.mysql.com
Sent: Thu, September 2, 2010 6:14:26 PM
Subject: Re: Logs not working


Did u check the logs on the db server, to see what the issue was.

regards
anandkl


On Thu, Sep 2, 2010 at 6:25 AM, monloi perez mlp_fol...@yahoo.com wrote:

All,

I'm not sure if this is the right mailing list since the specific mailing lists
doesn't seem to meet my concern.

For some reason mysql client stops logging to mysqd.log. We had an issue on Aug
20. But when I checked the log, the latest was on Aug 2.
Any idea on how to resolve this or what caused it?

Thanks,
Mon

Re: Logs not working

2010-09-02 Thread Ananda Kumar

Did u check the logs on the db server, to see what the issue was.

regards
anandkl

On Thu, Sep 2, 2010 at 6:25 AM, monloi perez mlp_fol...@yahoo.com wrote:

 All,

 I'm not sure if this is the right mailing list since the specific mailing
 lists
 doesn't seem to meet my concern.

 For some reason mysql client stops logging to mysqd.log. We had an issue on
 Aug
 20. But when I checked the log, the latest was on Aug 2.
 Any idea on how to resolve this or what caused it?

 Thanks,
 Mon

Logs not working

2010-09-01 Thread monloi perez

All,

I'm not sure if this is the right mailing list since the specific mailing lists 
doesn't seem to meet my concern.

For some reason mysql client stops logging to mysqd.log. We had an issue on Aug 
20. But when I checked the log, the latest was on Aug 2.
Any idea on how to resolve this or what caused it?

Thanks,
Mon

Help with flush logs

2010-08-10 Thread Nitin Mehta

Hi,

This is probably very simple for someone who has encountered the problem before 
but I'm struggling to find how I can contain the MySQL error log from being 
flushed at FLUSH LOGS command. This command is executed as part of the 
database backup every night and simply moves the old error log to -old file. 
This way, I can only have maximum 2 days of error logs while I need to keep the 
logs for at least 60 days (for auditing as well as debugging purposes). I 
understand that I can probably achieve this using log rotate or similar things 
but just wondering if it is possible to achieve this within MySQL conf itself.


Thanks in advance!

Regards,
Nitin

FLUSH LOCAL LOGS

2010-06-09 Thread Darvin Denmian

Hello !

Is there some diference between : FLUSH LOCAL LOGS and FLUSH LOGS ?

Thanks

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: FLUSH LOCAL LOGS

2010-06-09 Thread AZZOPARDI Konrad

Ok now 

-Original Message-
From: Darvin Denmian [mailto:darvin.denm...@gmail.com] 
Sent: Wednesday 09 June 2010 19:00
To: mysql@lists.mysql.com
Subject: FLUSH LOCAL LOGS

Hello !

Is there some diference between : FLUSH LOCAL LOGS and FLUSH LOGS ?

Thanks

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=azzopa...@eib.org

Les informations contenues dans ce message et/ou ses annexes sont 
reservees a l'attention et a l'utilisation de leur destinataire et peuvent etre 
confidentielles. Si vous n'etes pas destinataire de ce message, vous etes 
informes que vous l'avez recu par erreur et que toute utilisation en est 
interdite. Dans ce cas, vous etes pries de le detruire et d'en informer la 
Banque Europeenne d'Investissement.

The information in this message and/or attachments is intended solely for 
the attention and use of the named addressee and may be confidential. If 
you are not the intended recipient, you are hereby notified that you have 
received this transmittal in error and that any use of it is prohibited. In 
such a case please delete this message and kindly notify the European 
Investment Bank accordingly.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: FLUSH LOCAL LOGS

2010-06-09 Thread Paul DuBois


On Jun 9, 2010, at 11:59 AM, Darvin Denmian wrote:

 Hello !
 
 Is there some diference between : FLUSH LOCAL LOGS and FLUSH LOGS ?


Yes. http://dev.mysql.com/doc/refman/5.1/en/flush.html says:


By default, FLUSH statements are written to the binary log so that they will be 
replicated to replication slaves. Logging can be suppressed with the optional 
NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

Note
FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not 
written to the binary log in any case because they would cause problems if 
replicated to a slave.


-- 
Paul DuBois
Oracle Corporation / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: FLUSH LOCAL LOGS

2010-06-09 Thread Darvin Denmian

Thanks Paul

you opened my eyes !!!

On Wed, Jun 9, 2010 at 2:27 PM, Paul DuBois paul.dub...@oracle.com wrote:

 On Jun 9, 2010, at 11:59 AM, Darvin Denmian wrote:

 Hello !

 Is there some diference between : FLUSH LOCAL LOGS and FLUSH LOGS ?


 Yes. http://dev.mysql.com/doc/refman/5.1/en/flush.html says:

 
 By default, FLUSH statements are written to the binary log so that they will 
 be replicated to replication slaves. Logging can be suppressed with the 
 optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

 Note
 FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are 
 not written to the binary log in any case because they would cause problems 
 if replicated to a slave.
 

 --
 Paul DuBois
 Oracle Corporation / MySQL Documentation Team
 Madison, Wisconsin, USA
 www.mysql.com



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

SSL Logs in MySQL

2009-07-12 Thread Sharath Chalasani

Hello there,
I am getting connected to MySQL through SSL.

I want to see the logs of SSL getting trigerred when someone access MySQL
database from any application or URL.
Where do I need to modify or edit the configuration, in order to see the SSL
logs?
I can only see the query logs as of now

Thanks a lot in advance

Sharath

Re: Recover data without logs

2009-06-01 Thread Weitao Liu

Still thanks,I will remember how to avoide this if the feature!

Re: Recover data without logs

2009-06-01 Thread ewen fortune

Weitao,

On Sun, May 31, 2009 at 10:13 AM, Weitao Liu liuwt...@gmail.com wrote:
 I had deleted some important data from my mysql server,who can tell how can
 I recover my data,I do not open the bin log,is there some other method?

If you are using InnoDB you may be able to recovery the data from the
pages if you did something like DELETE * FROM...

http://code.google.com/p/innodb-tools/

Cheers,

Ewen


 thanks a lot !


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Recover data without logs

2009-05-31 Thread Weitao Liu

I had deleted some important data from my mysql server,who can tell how can
I recover my data,I do not open the bin log,is there some other method?

thanks a lot !

AW: Recover data without logs

2009-05-31 Thread Benedikt Schackenberg

O think not, did you make dumps of your database? If not you have to use
bin-log

-Ursprüngliche Nachricht-
Von: Weitao Liu [mailto:liuwt...@gmail.com] 
Gesendet: Sonntag, 31. Mai 2009 10:13
An: mysql@lists.mysql.com
Betreff: Recover data without logs

I had deleted some important data from my mysql server,who can tell how can
I recover my data,I do not open the bin log,is there some other method?

thanks a lot !


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: the way to get more info in logs

2009-04-18 Thread Claudio Nanni

seems like there is a problem of consistency of innodb data and recovery
does not go well.
try to take a look at innodb status:

show engine innodb status

http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
http://dev.mysql.com/doc/refman/5.0/en/innodb-monitors.html

Cheers

Claudio



2009/4/17 Mihail Vetchinov m.vetchi...@rambler-co.ru

 Anybody can tell me about way which will force the mysqld write more
 information into logs (.err file)?

 My mysqld restarted every 20-30 minutes without reasonable info in log file
 and I dont understand why it occurs.

 I see only string mysqld restarted and nothing more! :(

 Example of log:

 090417 16:34:14  InnoDB: Started; log sequence number 0 73771
 090417 16:34:14 [Note] /usr/local/libexec/mysqld: ready for connections.
 Version: '5.0.77-log'  socket: '/tmp/mysql.sock'  port: 3306  FreeBSD port:
 mysql-server-5.0.77_1
 090417 16:34:14 [Note] Slave SQL thread initialized, starting replication
 in log 'mysql-bin.000576' at position 530085643, relay log
 './beta23-relay-bin.000174' position: 93504
 090417 16:34:14 [Note] Slave I/O thread: connected to master '
 bet...@beta27.rambler.ru:3306',  replication started in log
 'mysql-bin.000576' at position 530085643
 090417 16:39:38  mysqld restarted
 InnoDB: The log sequence number in ibdata files does not match
 InnoDB: the log sequence number in the ib_logfiles!
 090417 16:39:39  InnoDB: Database was not shut down normally!
 InnoDB: Starting crash recovery.
 InnoDB: Reading tablespace information from the .ibd files...
 InnoDB: Restoring possible half-written data pages from the doublewrite
 InnoDB: buffer...
 090417 16:39:39  InnoDB: Started; log sequence number 0 73781


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com

the way to get more info in logs

2009-04-17 Thread Mihail Vetchinov

Anybody can tell me about way which will force the mysqld write more 
information into logs (.err file)?


My mysqld restarted every 20-30 minutes without reasonable info in log 
file and I dont understand why it occurs.


I see only string mysqld restarted and nothing more! :(

Example of log:

090417 16:34:14  InnoDB: Started; log sequence number 0 73771
090417 16:34:14 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.77-log'  socket: '/tmp/mysql.sock'  port: 3306  FreeBSD 
port: mysql-server-5.0.77_1
090417 16:34:14 [Note] Slave SQL thread initialized, starting 
replication in log 'mysql-bin.000576' at position 530085643, relay log 
'./beta23-relay-bin.000174' position: 93504
090417 16:34:14 [Note] Slave I/O thread: connected to master 
'bet...@beta27.rambler.ru:3306',  replication started in log 
'mysql-bin.000576' at position 530085643

090417 16:39:38  mysqld restarted
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090417 16:39:39  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
090417 16:39:39  InnoDB: Started; log sequence number 0 73781


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Quit in MySQL logs

2008-06-22 Thread Malka Cymbalista

We are running MySQL 5.0.45 on a Linux machine.  I have enabled the general 
query log and I notice that for some of the connections, the last command is 
Quit but for some of the connections, the Quit command does not appear.  What 
do I have to do to ensure that the Quit command gets printed in the log.
Thanks for any help.
-- 

Malka Cymbalista
Webmaster, Weizmann Institute of Science
[EMAIL PROTECTED]
08-934-3036



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

Re: Usefulness of mysql logs when using innodb?

2008-05-20 Thread Moon's Father

You should keep it on in my opinion.

On Tue, May 13, 2008 at 6:04 PM, Nico Sabbi [EMAIL PROTECTED] wrote:

 Hi,
 I guess that when I'm using only Innodb and no replication I can
 safely disable mysql's (bin-) log files (that grow to no end) because
 Innodb has its own log files. Is it correct?
 Thanks,
Nico

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




-- 
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

Re: purge binary logs on master

2008-05-19 Thread Todd Lyons

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Sun, May 18, 2008 at 01:02:32PM +0530, Ananda Kumar wrote:

I want to purge bin logs which are older than 4 days. I want to keep 4 days
of bin logs so that in the event of any data issue, i can restore it from
bin logs. So, i tired to use

PURGE MASTER LOGS BEFORE '2008-05-07 00:00:00';
But it did not work. Can you please help me on this

I was going to say drop the time stamp and just use the date, but it
works the same for me using '2008-05-XX 00:00:00' as it does using
'2008-05-XX'.  Just confirming that your command does seem correct,
dunno why it's not working for you.
- -- 
Regards...  Todd
when you shoot yourself in the foot, just because you are so neurally
broken that the signal takes years to register in your brain, it does
not mean that your foot does not have a hole in it.  --Randy Bush
Linux kernel 2.6.22-14-generic   6 users,  load average: 0.89, 0.61, 0.40
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIMad/Y2VBGxIDMLwRAirdAJ9+76Ew8AB5Y6FurP4zF8l9Q60p5gCcDtKi
8qQbhM/cExUYVsoFhgS4Vm8=
=THsx
-END PGP SIGNATURE-

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

Re: purge binary logs on master

2008-05-19 Thread Moon's Father

You can add this condition in the configuration file if you didn't find
other way to solve this.
expire_logs_per_day = 4

On Tue, May 20, 2008 at 12:14 AM, Todd Lyons [EMAIL PROTECTED] wrote:

 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1

 On Sun, May 18, 2008 at 01:02:32PM +0530, Ananda Kumar wrote:

 I want to purge bin logs which are older than 4 days. I want to keep 4
 days
 of bin logs so that in the event of any data issue, i can restore it from
 bin logs. So, i tired to use
 
 PURGE MASTER LOGS BEFORE '2008-05-07 00:00:00';
 But it did not work. Can you please help me on this

 I was going to say drop the time stamp and just use the date, but it
 works the same for me using '2008-05-XX 00:00:00' as it does using
 '2008-05-XX'.  Just confirming that your command does seem correct,
 dunno why it's not working for you.
 - --
 Regards...  Todd
 when you shoot yourself in the foot, just because you are so neurally
 broken that the signal takes years to register in your brain, it does
 not mean that your foot does not have a hole in it.  --Randy Bush
 Linux kernel 2.6.22-14-generic   6 users,  load average: 0.89, 0.61, 0.40
 -BEGIN PGP SIGNATURE-
 Version: GnuPG v1.4.6 (GNU/Linux)

 iD8DBQFIMad/Y2VBGxIDMLwRAirdAJ9+76Ew8AB5Y6FurP4zF8l9Q60p5gCcDtKi
 8qQbhM/cExUYVsoFhgS4Vm8=
 =THsx
 -END PGP SIGNATURE-

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




-- 
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

Re: purge binary logs on master

2008-05-18 Thread Ananda Kumar

Hi Juan,
I want to purge bin logs which are older than 4 days. I want to keep 4 days
of bin logs so that in the event of any data issue, i can restore it from
bin logs. So, i tired to use

PURGE MASTER LOGS BEFORE '2008-05-07 00:00:00';

But it did not work. Can you please help me on this

regards
anandkl



On 5/17/08, Juan Eduardo Moreno [EMAIL PROTECTED] wrote:

 Ananda,

 Use, PURGE MASTER LOGS TO 'name of the last binary that you want leave in
 your disk';

 Regards

 On Sat, May 17, 2008 at 8:56 AM, Ananda Kumar [EMAIL PROTECTED] wrote:

 Hi All,
 I using this command to purge binary logs on master, but i dont see the
 binary logs getting deleted. These binary logs are listed in index file.
 I am using mysql version 5.0.41 community version. Can you please let me
 know if any thing else need to be done for this to work.

 log-bin is enables.

 PURGE LOGS BEFORE '2008-05-07 00:00:00';

 regards
 anandkl

purge binary logs on master

2008-05-17 Thread Ananda Kumar

Hi All,
I using this command to purge binary logs on master, but i dont see the
binary logs getting deleted. These binary logs are listed in index file.
I am using mysql version 5.0.41 community version. Can you please let me
know if any thing else need to be done for this to work.

log-bin is enables.

PURGE MASTER LOGS BEFORE '2008-05-07 00:00:00';

regards
anandkl

Re: purge binary logs on master

2008-05-17 Thread Juan Eduardo Moreno

Ananda,

Use, PURGE MASTER LOGS TO 'name of the last binary that you want leave in
your disk';

Regards

On Sat, May 17, 2008 at 8:56 AM, Ananda Kumar [EMAIL PROTECTED] wrote:

 Hi All,
 I using this command to purge binary logs on master, but i dont see the
 binary logs getting deleted. These binary logs are listed in index file.
 I am using mysql version 5.0.41 community version. Can you please let me
 know if any thing else need to be done for this to work.

 log-bin is enables.

 PURGE LOGS BEFORE '2008-05-07 00:00:00';

 regards
 anandkl

Res: MySQL purge logs

2008-05-15 Thread Wagner Bianchi

mysql reset master;

 
Wagner Bianchi 
Diretor de Tecnologia - INFODBA CT
[EMAIL PROTECTED] - (31) 3272 - 0226 / 9114 - 7695
 



- Mensagem original 
De: Kaushal Shriyan [EMAIL PROTECTED]
Para: mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]
Enviadas: Domingo, 11 de Maio de 2008 13:39:02
Assunto: MySQL purge logs

Hi

I am referring to
http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html

whats the exact syntax to purge this MySQL Binary Logs

-rw-rw 1 mysql  701 1.1G May  5 07:39 host1-bin.000681
-rw-rw 1 mysql  701 1.1G May  5 09:09 host1-bin.000682
-rw-rw 1 mysql  701 1.1G May  5 10:49 host1-bin.000683
-rw-rw 1 mysql  701 1.1G May  5 20:24 host1-bin.000684
-rw-rw 1 mysql  701 1.1G May  5 21:47 host1-bin.000685
-rw-rw 1 mysql  701 1.1G May  5 23:31 host1-bin.000686
-rw-rw 1 mysql  701 1.1G May  6 01:40 host1-bin.000687
-rw-rw 1 mysql  701 1.1G May  6 04:26 host1-bin.000688
-rw-rw 1 mysql  701 1.1G May  6 07:00 host1-bin.000689
-rw-rw 1 mysql  701 1.1G May  6 08:58 host1-bin.000690
-rw-rw 1 mysql  701 1.1G May  6 17:54 host1-bin.000691
-rw-rw 1 mysql  701 1.1G May  6 21:01 host1-bin.000692
-rw-rw 1 mysql  701 1.1G May  6 22:46 host1-bin.000693
-rw-rw 1 mysql  701 1.1G May  7 00:56 host1-bin.000694
-rw-rw 1 mysql  701 1.1G May  7 02:52 host1-bin.000695
-rw-rw 1 mysql  701 1.1G May  7 05:44 host1-bin.000696
-rw-rw 1 mysql  701 1.1G May  7 07:28 host1-bin.000697
-rw-rw 1 mysql  701 1.1G May  7 09:09 host1-bin.000698
-rw-rw 1 mysql  701 1.1G May  7 18:40 host1-bin.000699
-rw-rw 1 mysql  701 1.1G May  7 21:00 host1-bin.000700
-rw-rw 1 mysql  701 1.1G May  7 22:35 host1-bin.000701
-rw-rw 1 mysql  701 1.1G May  8 00:40 host1-bin.000702
-rw-rw 1 mysql  701 1.1G May  8 03:20 host1-bin.000703
-rw-rw 1 mysql  701 1.1G May  8 05:53 host1-bin.000704
-rw-rw 1 mysql  701 1.1G May  8 07:59 host1-bin.000705
-rw-rw 1 mysql  701 1.1G May  8 09:24 host1-bin.000706
-rw-rw 1 mysql  701 1.1G May  8 18:36 host1-bin.000707
-rw-rw 1 mysql  701 1.1G May  8 21:21 host1-bin.000708
-rw-rw 1 mysql  701 1.1G May  8 22:57 host1-bin.000709
-rw-rw 1 mysql  701 1.1G May  9 01:25 host1-bin.000710
-rw-rw 1 mysql  701 1.1G May  9 03:41 host1-bin.000711
-rw-rw 1 mysql  701 1.1G May  9 06:05 host1-bin.000712
-rw-rw 1 mysql  701 1.1G May  9 07:50 host1-bin.000713
-rw-rw 1 mysql  701 1.1G May  9 09:29 host1-bin.000714
-rw-rw 1 mysql  701 1.1G May  9 19:20 host1-bin.000715
-rw-rw 1 mysql  701 1.1G May  9 21:46 host1-bin.000716

Thanks and Regards

Kaushal



  Abra sua conta no Yahoo! Mail, o único sem limite de espaço para 
armazenamento!
http://br.mail.yahoo.com/

Usefulness of mysql logs when using innodb?

2008-05-13 Thread Nico Sabbi

Hi,
I guess that when I'm using only Innodb and no replication I can
safely disable mysql's (bin-) log files (that grow to no end) because
Innodb has its own log files. Is it correct?
Thanks,
Nico

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

RE: MySQL purge logs

2008-05-12 Thread Rick James

RESET is not a good idea -- PURGE to some point is better.

 

 -Original Message-
 From: Dennis Yu [mailto:[EMAIL PROTECTED] 
 Sent: Sunday, May 11, 2008 6:10 PM
 To: Kaushal Shriyan
 Cc: mysql@lists.mysql.com; [EMAIL PROTECTED]
 Subject: Re: MySQL purge logs
 
 login MySQL with root and use:
 RESET MASTER
 that's all you need to do.
 
 
 Kaushal Shriyan wrote:
  Hi
 
  I am referring to
  http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html
 
  whats the exact syntax to purge this MySQL Binary Logs
 
  -rw-rw 1 mysql  701 1.1G May  5 07:39 host1-bin.000681
  -rw-rw 1 mysql  701 1.1G May  5 09:09 host1-bin.000682
  -rw-rw 1 mysql  701 1.1G May  5 10:49 host1-bin.000683
  -rw-rw 1 mysql  701 1.1G May  5 20:24 host1-bin.000684
  -rw-rw 1 mysql  701 1.1G May  5 21:47 host1-bin.000685
  -rw-rw 1 mysql  701 1.1G May  5 23:31 host1-bin.000686
  -rw-rw 1 mysql  701 1.1G May  6 01:40 host1-bin.000687
  -rw-rw 1 mysql  701 1.1G May  6 04:26 host1-bin.000688
  -rw-rw 1 mysql  701 1.1G May  6 07:00 host1-bin.000689
  -rw-rw 1 mysql  701 1.1G May  6 08:58 host1-bin.000690
  -rw-rw 1 mysql  701 1.1G May  6 17:54 host1-bin.000691
  -rw-rw 1 mysql  701 1.1G May  6 21:01 host1-bin.000692
  -rw-rw 1 mysql  701 1.1G May  6 22:46 host1-bin.000693
  -rw-rw 1 mysql  701 1.1G May  7 00:56 host1-bin.000694
  -rw-rw 1 mysql  701 1.1G May  7 02:52 host1-bin.000695
  -rw-rw 1 mysql  701 1.1G May  7 05:44 host1-bin.000696
  -rw-rw 1 mysql  701 1.1G May  7 07:28 host1-bin.000697
  -rw-rw 1 mysql  701 1.1G May  7 09:09 host1-bin.000698
  -rw-rw 1 mysql  701 1.1G May  7 18:40 host1-bin.000699
  -rw-rw 1 mysql  701 1.1G May  7 21:00 host1-bin.000700
  -rw-rw 1 mysql  701 1.1G May  7 22:35 host1-bin.000701
  -rw-rw 1 mysql  701 1.1G May  8 00:40 host1-bin.000702
  -rw-rw 1 mysql  701 1.1G May  8 03:20 host1-bin.000703
  -rw-rw 1 mysql  701 1.1G May  8 05:53 host1-bin.000704
  -rw-rw 1 mysql  701 1.1G May  8 07:59 host1-bin.000705
  -rw-rw 1 mysql  701 1.1G May  8 09:24 host1-bin.000706
  -rw-rw 1 mysql  701 1.1G May  8 18:36 host1-bin.000707
  -rw-rw 1 mysql  701 1.1G May  8 21:21 host1-bin.000708
  -rw-rw 1 mysql  701 1.1G May  8 22:57 host1-bin.000709
  -rw-rw 1 mysql  701 1.1G May  9 01:25 host1-bin.000710
  -rw-rw 1 mysql  701 1.1G May  9 03:41 host1-bin.000711
  -rw-rw 1 mysql  701 1.1G May  9 06:05 host1-bin.000712
  -rw-rw 1 mysql  701 1.1G May  9 07:50 host1-bin.000713
  -rw-rw 1 mysql  701 1.1G May  9 09:29 host1-bin.000714
  -rw-rw 1 mysql  701 1.1G May  9 19:20 host1-bin.000715
  -rw-rw 1 mysql  701 1.1G May  9 21:46 host1-bin.000716
 
  Thanks and Regards
 
  Kaushal
 

 
 
 -- 
 MySQL Replication Mailing List
 For list archives: http://lists.mysql.com/replication
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
 
 

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

Re: MySQL purge logs

2008-05-12 Thread Augusto Bott

You could also do:
SET GLOBAL expire_log_days=n;

BTW - this same hint is on the the very same manual page you mentioned
at the beginning of this thread.

-- 
Augusto Bott


On Mon, May 12, 2008 at 11:11 AM, Rick James [EMAIL PROTECTED] wrote:
 RESET is not a good idea -- PURGE to some point is better.





   -Original Message-
   From: Dennis Yu [mailto:[EMAIL PROTECTED]
   Sent: Sunday, May 11, 2008 6:10 PM
   To: Kaushal Shriyan
   Cc: mysql@lists.mysql.com; [EMAIL PROTECTED]
   Subject: Re: MySQL purge logs
  
   login MySQL with root and use:
   RESET MASTER
   that's all you need to do.
  
  
   Kaushal Shriyan wrote:
Hi
   
I am referring to
http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html
   
whats the exact syntax to purge this MySQL Binary Logs
   
-rw-rw 1 mysql  701 1.1G May  5 07:39 host1-bin.000681
-rw-rw 1 mysql  701 1.1G May  5 09:09 host1-bin.000682
-rw-rw 1 mysql  701 1.1G May  5 10:49 host1-bin.000683
-rw-rw 1 mysql  701 1.1G May  5 20:24 host1-bin.000684
-rw-rw 1 mysql  701 1.1G May  5 21:47 host1-bin.000685
-rw-rw 1 mysql  701 1.1G May  5 23:31 host1-bin.000686
-rw-rw 1 mysql  701 1.1G May  6 01:40 host1-bin.000687
-rw-rw 1 mysql  701 1.1G May  6 04:26 host1-bin.000688
-rw-rw 1 mysql  701 1.1G May  6 07:00 host1-bin.000689
-rw-rw 1 mysql  701 1.1G May  6 08:58 host1-bin.000690
-rw-rw 1 mysql  701 1.1G May  6 17:54 host1-bin.000691
-rw-rw 1 mysql  701 1.1G May  6 21:01 host1-bin.000692
-rw-rw 1 mysql  701 1.1G May  6 22:46 host1-bin.000693
-rw-rw 1 mysql  701 1.1G May  7 00:56 host1-bin.000694
-rw-rw 1 mysql  701 1.1G May  7 02:52 host1-bin.000695
-rw-rw 1 mysql  701 1.1G May  7 05:44 host1-bin.000696
-rw-rw 1 mysql  701 1.1G May  7 07:28 host1-bin.000697
-rw-rw 1 mysql  701 1.1G May  7 09:09 host1-bin.000698
-rw-rw 1 mysql  701 1.1G May  7 18:40 host1-bin.000699
-rw-rw 1 mysql  701 1.1G May  7 21:00 host1-bin.000700
-rw-rw 1 mysql  701 1.1G May  7 22:35 host1-bin.000701
-rw-rw 1 mysql  701 1.1G May  8 00:40 host1-bin.000702
-rw-rw 1 mysql  701 1.1G May  8 03:20 host1-bin.000703
-rw-rw 1 mysql  701 1.1G May  8 05:53 host1-bin.000704
-rw-rw 1 mysql  701 1.1G May  8 07:59 host1-bin.000705
-rw-rw 1 mysql  701 1.1G May  8 09:24 host1-bin.000706
-rw-rw 1 mysql  701 1.1G May  8 18:36 host1-bin.000707
-rw-rw 1 mysql  701 1.1G May  8 21:21 host1-bin.000708
-rw-rw 1 mysql  701 1.1G May  8 22:57 host1-bin.000709
-rw-rw 1 mysql  701 1.1G May  9 01:25 host1-bin.000710
-rw-rw 1 mysql  701 1.1G May  9 03:41 host1-bin.000711
-rw-rw 1 mysql  701 1.1G May  9 06:05 host1-bin.000712
-rw-rw 1 mysql  701 1.1G May  9 07:50 host1-bin.000713
-rw-rw 1 mysql  701 1.1G May  9 09:29 host1-bin.000714
-rw-rw 1 mysql  701 1.1G May  9 19:20 host1-bin.000715
-rw-rw 1 mysql  701 1.1G May  9 21:46 host1-bin.000716
   
Thanks and Regards
   
Kaushal
   
   
  
  
   --
   MySQL Replication Mailing List
   For list archives: http://lists.mysql.com/replication
   To unsubscribe:
   http://lists.mysql.com/[EMAIL PROTECTED]


 
  

  --
  MySQL Replication Mailing List
  For list archives: http://lists.mysql.com/replication
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



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

Re: MySQL purge logs

2008-05-12 Thread D Hill


On Mon, 12 May 2008 at 11:58 -0400, [EMAIL PROTECTED] confabulated:


You could also do:
SET GLOBAL expire_log_days=n;


You forgot the 's' in log:

  SET GLOBAL expire_logs_days=n;


BTW - this same hint is on the the very same manual page you mentioned
at the beginning of this thread.

--
Augusto Bott


On Mon, May 12, 2008 at 11:11 AM, Rick James [EMAIL PROTECTED] wrote:

RESET is not a good idea -- PURGE to some point is better.





 -Original Message-
 From: Dennis Yu [mailto:[EMAIL PROTECTED]
 Sent: Sunday, May 11, 2008 6:10 PM
 To: Kaushal Shriyan
 Cc: mysql@lists.mysql.com; [EMAIL PROTECTED]
 Subject: Re: MySQL purge logs

 login MySQL with root and use:
 RESET MASTER
 that's all you need to do.


 Kaushal Shriyan wrote:
 Hi

 I am referring to
 http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html

 whats the exact syntax to purge this MySQL Binary Logs

 -rw-rw 1 mysql  701 1.1G May  5 07:39 host1-bin.000681
 -rw-rw 1 mysql  701 1.1G May  5 09:09 host1-bin.000682
 -rw-rw 1 mysql  701 1.1G May  5 10:49 host1-bin.000683
 -rw-rw 1 mysql  701 1.1G May  5 20:24 host1-bin.000684
 -rw-rw 1 mysql  701 1.1G May  5 21:47 host1-bin.000685
 -rw-rw 1 mysql  701 1.1G May  5 23:31 host1-bin.000686
 -rw-rw 1 mysql  701 1.1G May  6 01:40 host1-bin.000687
 -rw-rw 1 mysql  701 1.1G May  6 04:26 host1-bin.000688
 -rw-rw 1 mysql  701 1.1G May  6 07:00 host1-bin.000689
 -rw-rw 1 mysql  701 1.1G May  6 08:58 host1-bin.000690
 -rw-rw 1 mysql  701 1.1G May  6 17:54 host1-bin.000691
 -rw-rw 1 mysql  701 1.1G May  6 21:01 host1-bin.000692
 -rw-rw 1 mysql  701 1.1G May  6 22:46 host1-bin.000693
 -rw-rw 1 mysql  701 1.1G May  7 00:56 host1-bin.000694
 -rw-rw 1 mysql  701 1.1G May  7 02:52 host1-bin.000695
 -rw-rw 1 mysql  701 1.1G May  7 05:44 host1-bin.000696
 -rw-rw 1 mysql  701 1.1G May  7 07:28 host1-bin.000697
 -rw-rw 1 mysql  701 1.1G May  7 09:09 host1-bin.000698
 -rw-rw 1 mysql  701 1.1G May  7 18:40 host1-bin.000699
 -rw-rw 1 mysql  701 1.1G May  7 21:00 host1-bin.000700
 -rw-rw 1 mysql  701 1.1G May  7 22:35 host1-bin.000701
 -rw-rw 1 mysql  701 1.1G May  8 00:40 host1-bin.000702
 -rw-rw 1 mysql  701 1.1G May  8 03:20 host1-bin.000703
 -rw-rw 1 mysql  701 1.1G May  8 05:53 host1-bin.000704
 -rw-rw 1 mysql  701 1.1G May  8 07:59 host1-bin.000705
 -rw-rw 1 mysql  701 1.1G May  8 09:24 host1-bin.000706
 -rw-rw 1 mysql  701 1.1G May  8 18:36 host1-bin.000707
 -rw-rw 1 mysql  701 1.1G May  8 21:21 host1-bin.000708
 -rw-rw 1 mysql  701 1.1G May  8 22:57 host1-bin.000709
 -rw-rw 1 mysql  701 1.1G May  9 01:25 host1-bin.000710
 -rw-rw 1 mysql  701 1.1G May  9 03:41 host1-bin.000711
 -rw-rw 1 mysql  701 1.1G May  9 06:05 host1-bin.000712
 -rw-rw 1 mysql  701 1.1G May  9 07:50 host1-bin.000713
 -rw-rw 1 mysql  701 1.1G May  9 09:29 host1-bin.000714
 -rw-rw 1 mysql  701 1.1G May  9 19:20 host1-bin.000715
 -rw-rw 1 mysql  701 1.1G May  9 21:46 host1-bin.000716

 Thanks and Regards

 Kaushal




 --
 MySQL Replication Mailing List
 For list archives: http://lists.mysql.com/replication
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]







 --
 MySQL Replication Mailing List
 For list archives: http://lists.mysql.com/replication
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




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




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

Re: MySQL purge logs

2008-05-12 Thread Paul DuBois



On May 11, 2008, at 11:39 AM, Kaushal Shriyan wrote:


Hi

I am referring to
http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html

whats the exact syntax to purge this MySQL Binary Logs


It's as indicated on the page that you reference. For example,
to purge all logs before .000698, use

PURGE MASTER LOGS TO 'host1-bin.000698';

To purge all logs older than May 7, use

PURGE MASTER LOGS BEFORE '2008-05-07 00:00:00';



-rw-rw 1 mysql  701 1.1G May  5 07:39 host1-bin.000681
-rw-rw 1 mysql  701 1.1G May  5 09:09 host1-bin.000682
-rw-rw 1 mysql  701 1.1G May  5 10:49 host1-bin.000683
-rw-rw 1 mysql  701 1.1G May  5 20:24 host1-bin.000684
-rw-rw 1 mysql  701 1.1G May  5 21:47 host1-bin.000685
-rw-rw 1 mysql  701 1.1G May  5 23:31 host1-bin.000686
-rw-rw 1 mysql  701 1.1G May  6 01:40 host1-bin.000687
-rw-rw 1 mysql  701 1.1G May  6 04:26 host1-bin.000688
-rw-rw 1 mysql  701 1.1G May  6 07:00 host1-bin.000689
-rw-rw 1 mysql  701 1.1G May  6 08:58 host1-bin.000690
-rw-rw 1 mysql  701 1.1G May  6 17:54 host1-bin.000691
-rw-rw 1 mysql  701 1.1G May  6 21:01 host1-bin.000692
-rw-rw 1 mysql  701 1.1G May  6 22:46 host1-bin.000693
-rw-rw 1 mysql  701 1.1G May  7 00:56 host1-bin.000694
-rw-rw 1 mysql  701 1.1G May  7 02:52 host1-bin.000695
-rw-rw 1 mysql  701 1.1G May  7 05:44 host1-bin.000696
-rw-rw 1 mysql  701 1.1G May  7 07:28 host1-bin.000697
-rw-rw 1 mysql  701 1.1G May  7 09:09 host1-bin.000698
-rw-rw 1 mysql  701 1.1G May  7 18:40 host1-bin.000699
-rw-rw 1 mysql  701 1.1G May  7 21:00 host1-bin.000700
-rw-rw 1 mysql  701 1.1G May  7 22:35 host1-bin.000701
-rw-rw 1 mysql  701 1.1G May  8 00:40 host1-bin.000702
-rw-rw 1 mysql  701 1.1G May  8 03:20 host1-bin.000703
-rw-rw 1 mysql  701 1.1G May  8 05:53 host1-bin.000704
-rw-rw 1 mysql  701 1.1G May  8 07:59 host1-bin.000705
-rw-rw 1 mysql  701 1.1G May  8 09:24 host1-bin.000706
-rw-rw 1 mysql  701 1.1G May  8 18:36 host1-bin.000707
-rw-rw 1 mysql  701 1.1G May  8 21:21 host1-bin.000708
-rw-rw 1 mysql  701 1.1G May  8 22:57 host1-bin.000709
-rw-rw 1 mysql  701 1.1G May  9 01:25 host1-bin.000710
-rw-rw 1 mysql  701 1.1G May  9 03:41 host1-bin.000711
-rw-rw 1 mysql  701 1.1G May  9 06:05 host1-bin.000712
-rw-rw 1 mysql  701 1.1G May  9 07:50 host1-bin.000713
-rw-rw 1 mysql  701 1.1G May  9 09:29 host1-bin.000714
-rw-rw 1 mysql  701 1.1G May  9 19:20 host1-bin.000715
-rw-rw 1 mysql  701 1.1G May  9 21:46 host1-bin.000716

Thanks and Regards

Kaushal


--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com


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

MySQL purge logs

2008-05-11 Thread Kaushal Shriyan

Hi

I am referring to
http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html

whats the exact syntax to purge this MySQL Binary Logs

-rw-rw 1 mysql  701 1.1G May  5 07:39 host1-bin.000681
-rw-rw 1 mysql  701 1.1G May  5 09:09 host1-bin.000682
-rw-rw 1 mysql  701 1.1G May  5 10:49 host1-bin.000683
-rw-rw 1 mysql  701 1.1G May  5 20:24 host1-bin.000684
-rw-rw 1 mysql  701 1.1G May  5 21:47 host1-bin.000685
-rw-rw 1 mysql  701 1.1G May  5 23:31 host1-bin.000686
-rw-rw 1 mysql  701 1.1G May  6 01:40 host1-bin.000687
-rw-rw 1 mysql  701 1.1G May  6 04:26 host1-bin.000688
-rw-rw 1 mysql  701 1.1G May  6 07:00 host1-bin.000689
-rw-rw 1 mysql  701 1.1G May  6 08:58 host1-bin.000690
-rw-rw 1 mysql  701 1.1G May  6 17:54 host1-bin.000691
-rw-rw 1 mysql  701 1.1G May  6 21:01 host1-bin.000692
-rw-rw 1 mysql  701 1.1G May  6 22:46 host1-bin.000693
-rw-rw 1 mysql  701 1.1G May  7 00:56 host1-bin.000694
-rw-rw 1 mysql  701 1.1G May  7 02:52 host1-bin.000695
-rw-rw 1 mysql  701 1.1G May  7 05:44 host1-bin.000696
-rw-rw 1 mysql  701 1.1G May  7 07:28 host1-bin.000697
-rw-rw 1 mysql  701 1.1G May  7 09:09 host1-bin.000698
-rw-rw 1 mysql  701 1.1G May  7 18:40 host1-bin.000699
-rw-rw 1 mysql  701 1.1G May  7 21:00 host1-bin.000700
-rw-rw 1 mysql  701 1.1G May  7 22:35 host1-bin.000701
-rw-rw 1 mysql  701 1.1G May  8 00:40 host1-bin.000702
-rw-rw 1 mysql  701 1.1G May  8 03:20 host1-bin.000703
-rw-rw 1 mysql  701 1.1G May  8 05:53 host1-bin.000704
-rw-rw 1 mysql  701 1.1G May  8 07:59 host1-bin.000705
-rw-rw 1 mysql  701 1.1G May  8 09:24 host1-bin.000706
-rw-rw 1 mysql  701 1.1G May  8 18:36 host1-bin.000707
-rw-rw 1 mysql  701 1.1G May  8 21:21 host1-bin.000708
-rw-rw 1 mysql  701 1.1G May  8 22:57 host1-bin.000709
-rw-rw 1 mysql  701 1.1G May  9 01:25 host1-bin.000710
-rw-rw 1 mysql  701 1.1G May  9 03:41 host1-bin.000711
-rw-rw 1 mysql  701 1.1G May  9 06:05 host1-bin.000712
-rw-rw 1 mysql  701 1.1G May  9 07:50 host1-bin.000713
-rw-rw 1 mysql  701 1.1G May  9 09:29 host1-bin.000714
-rw-rw 1 mysql  701 1.1G May  9 19:20 host1-bin.000715
-rw-rw 1 mysql  701 1.1G May  9 21:46 host1-bin.000716

Thanks and Regards

Kaushal

Re: MySQL purge logs

2008-05-11 Thread Dennis Yu


login MySQL with root and use:
RESET MASTER
that's all you need to do.


Kaushal Shriyan wrote:

Hi

I am referring to
http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html

whats the exact syntax to purge this MySQL Binary Logs

-rw-rw 1 mysql  701 1.1G May  5 07:39 host1-bin.000681
-rw-rw 1 mysql  701 1.1G May  5 09:09 host1-bin.000682
-rw-rw 1 mysql  701 1.1G May  5 10:49 host1-bin.000683
-rw-rw 1 mysql  701 1.1G May  5 20:24 host1-bin.000684
-rw-rw 1 mysql  701 1.1G May  5 21:47 host1-bin.000685
-rw-rw 1 mysql  701 1.1G May  5 23:31 host1-bin.000686
-rw-rw 1 mysql  701 1.1G May  6 01:40 host1-bin.000687
-rw-rw 1 mysql  701 1.1G May  6 04:26 host1-bin.000688
-rw-rw 1 mysql  701 1.1G May  6 07:00 host1-bin.000689
-rw-rw 1 mysql  701 1.1G May  6 08:58 host1-bin.000690
-rw-rw 1 mysql  701 1.1G May  6 17:54 host1-bin.000691
-rw-rw 1 mysql  701 1.1G May  6 21:01 host1-bin.000692
-rw-rw 1 mysql  701 1.1G May  6 22:46 host1-bin.000693
-rw-rw 1 mysql  701 1.1G May  7 00:56 host1-bin.000694
-rw-rw 1 mysql  701 1.1G May  7 02:52 host1-bin.000695
-rw-rw 1 mysql  701 1.1G May  7 05:44 host1-bin.000696
-rw-rw 1 mysql  701 1.1G May  7 07:28 host1-bin.000697
-rw-rw 1 mysql  701 1.1G May  7 09:09 host1-bin.000698
-rw-rw 1 mysql  701 1.1G May  7 18:40 host1-bin.000699
-rw-rw 1 mysql  701 1.1G May  7 21:00 host1-bin.000700
-rw-rw 1 mysql  701 1.1G May  7 22:35 host1-bin.000701
-rw-rw 1 mysql  701 1.1G May  8 00:40 host1-bin.000702
-rw-rw 1 mysql  701 1.1G May  8 03:20 host1-bin.000703
-rw-rw 1 mysql  701 1.1G May  8 05:53 host1-bin.000704
-rw-rw 1 mysql  701 1.1G May  8 07:59 host1-bin.000705
-rw-rw 1 mysql  701 1.1G May  8 09:24 host1-bin.000706
-rw-rw 1 mysql  701 1.1G May  8 18:36 host1-bin.000707
-rw-rw 1 mysql  701 1.1G May  8 21:21 host1-bin.000708
-rw-rw 1 mysql  701 1.1G May  8 22:57 host1-bin.000709
-rw-rw 1 mysql  701 1.1G May  9 01:25 host1-bin.000710
-rw-rw 1 mysql  701 1.1G May  9 03:41 host1-bin.000711
-rw-rw 1 mysql  701 1.1G May  9 06:05 host1-bin.000712
-rw-rw 1 mysql  701 1.1G May  9 07:50 host1-bin.000713
-rw-rw 1 mysql  701 1.1G May  9 09:29 host1-bin.000714
-rw-rw 1 mysql  701 1.1G May  9 19:20 host1-bin.000715
-rw-rw 1 mysql  701 1.1G May  9 21:46 host1-bin.000716

Thanks and Regards

Kaushal

  



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

Re: Storing Apache logs in MySQL.

2007-11-11 Thread Filip Krejci



This however is not an option at all when you run Apache in prefork 
mode. Because the sum of your MaxClients will be equal to the 
connections to your MySQL Server.


You can use connection pooling eg SQLrelay




--
Filip Krejci [EMAIL PROTECTED]

Linux - und Spass dabei!

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

Storing Apache logs in MySQL.

2007-11-09 Thread Shaun T. Erickson

I pull Apache (combined format) logs, daily, from a number of mirrors,
back to a central server for processing. I'd like to somehow load them
in MySQL for analysis.

1) Does anyone know of a script that can parse the logs and load them?
(I presume I can get the DB schema from it.)

2) Can MySQL handle a little more than one million records a day
(currently)? If so, what kind of disk space needs will it have?

3) Am I out of my mind? Anything I'm not considering, that I should?

-- 
-ste

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

Re: Storing Apache logs in MySQL.

2007-11-09 Thread Ian

On 9 Nov 2007 at 10:44, Shaun T. Erickson wrote:

 I pull Apache (combined format) logs, daily, from a number of mirrors,
 back to a central server for processing. I'd like to somehow load them
 in MySQL for analysis.
 
 1) Does anyone know of a script that can parse the logs and load them?
 (I presume I can get the DB schema from it.)
 
 2) Can MySQL handle a little more than one million records a day
 (currently)? If so, what kind of disk space needs will it have?
 
 3) Am I out of my mind? Anything I'm not considering, that I should?

Hi,

An easier method may be to install mod_log_mysql on each of the Apache servers 
and 
log directly to the central MySQL installation.

Regards

Ian
-- 


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

Re: Storing Apache logs in MySQL.

2007-11-09 Thread Samuel Vogel




Hi,

An easier method may be to install mod_log_mysql on each of the Apache 
servers and log directly to the central MySQL installation.


Regards

Ian
  
This however is not an option at all when you run Apache in prefork 
mode. Because the sum of your MaxClients will be equal to the 
connections to your MySQL Server.

This rendered the module useless for me.

I do write my logs a MySQL statements to a file, that gets rotated every 
day and I have another script, that reads the rotated log file and 
writes to the database.


Regards,
Samy


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

MySQL crashing on flush-logs

2007-10-03 Thread Ofer Inbar

We have MySQL 5.0.27 running on about 10 different RedHat EL4 boxes,
all from the same RPMs.  Every night we run mysqladmin flush-logs from
crontab (as well as some other things) on most of these servers.

One on server, mysqld is dying with signal 11 every single night right
during the mysqladmin flush-logs command.  None of the others ever do that.
This is repeatable.  It happens every night.

We're investigating possible causes, but in the meantime I'm also
curious if anyone else on this list has run into something similar
and has some suggestions.


Here's the backtrace portion of the error log from the most recent crash:

| Attempting backtrace. You can use the following information to find out
| where mysqld died. If you see no messages after this, something went
| terribly wrong...
| Cannot determine thread, fp=0x45394f78, backtrace may not be correct.
| Stack range sanity check OK, backtrace follows:
| 0x5f737400746f6f72
| New value of fp=0x1874230 failed sanity check, terminating stack trace!
| Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and 
follow instructions on how to resolve the stack
|  trace. Resolved
| stack trace is much more helpful in diagnosing the problem, so please do 
| resolve it
| Trying to get some variables.
| Some pointers may be invalid and cause the dump to abort...
| thd-query at (nil)  is invalid pointer
| thd-thread_id=12310

  -- Cos

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

Re: flush logs vs. mysqladmin

2007-06-20 Thread Paul DuBois


At 12:12 PM -0400 6/12/07, Ofer Inbar wrote:

We run a mysqladmin flush-logs from cron every night.  This causes our
server to start a new binary log.  However, the slow query log does
not get flushed - our server continues updating the same slow query
log file.

If I run mysql and then issue a flush logs command, it flushes the
binary logs and the slow query log as well.

 - Redhat ES 4
 - MySQL 5.0.24 from redhat-supplied RPM
 - Both mysqladmin from cron, and my mysql flush logs, use -u root

Why does mysqladmin not flush the slow query log?


It does, but the slow query log (just like the general query log)
isn't created as a numbered sequence of files. You'll need to rotate
it yourself.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

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

flush logs vs. mysqladmin

2007-06-12 Thread Ofer Inbar

We run a mysqladmin flush-logs from cron every night.  This causes our
server to start a new binary log.  However, the slow query log does
not get flushed - our server continues updating the same slow query
log file.

If I run mysql and then issue a flush logs command, it flushes the
binary logs and the slow query log as well.

 - Redhat ES 4
 - MySQL 5.0.24 from redhat-supplied RPM
 - Both mysqladmin from cron, and my mysql flush logs, use -u root

Why does mysqladmin not flush the slow query log?
  -- Cos

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

Redo logs take to much disk space

2007-05-15 Thread chiel

Hello,

I got a problem with the redo log from mysql. 
my /var/log/mysql fills up with mysql-bin.0# files and a few moments ago it 
was about 10Gb in size (after 2 weeks in production), so I decide to delete al 
the files.
What can I do to stop this kind of behavior? and is its safe to delete all the 
files with a cronjob? and witch files are recommend to delete if so?

chiel

Re: Redo logs take to much disk space

2007-05-15 Thread asv

 What can I do to stop this kind of behavior? and is
 its safe to delete all the files with a cronjob? and witch files are
 recommend to delete if so?
just commet log-bin option of [mysqld] section in your my.cnf file 
(/etc/my.cnf) 
you can also remove these files manually -- it will not affect server

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

Re: Redo logs take to much disk space

2007-05-15 Thread chiel


What can I do to stop this kind of behavior? and is
its safe to delete all the files with a cronjob? and witch files are
recommend to delete if so?

just commet log-bin option of [mysqld] section in your my.cnf file
(/etc/my.cnf)
you can also remove these files manually -- it will not affect server

--



Thanks, it works. I also had to comment: expire_logs_days and 
max_binlog_size.
One question about this, is it safe to turn of log_bin? Or can you tweak it 
somehow so that it won't' take some much disk space? 



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

Re: Redo logs take to much disk space

2007-05-15 Thread asv

 One question about this, is it safe to turn of log_bin? 
i think, you can. the log is necessary for data replication and sometimes for 
data recovery.
you can read about it here:
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
 Or can you tweak it somehow so that it won't' take some much disk space?
you can bzip old logs if you need them but don't want them to take so much 
space :)



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

Re: Redo logs take to much disk space

2007-05-15 Thread Alex Arul Lurthu


the ask lies in expire_logs_days. If you set this to optimal number of days,
logs older than the configured days will get purged.

~Alex


On 5/15/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:


 One question about this, is it safe to turn of log_bin?
i think, you can. the log is necessary for data replication and sometimes
for
data recovery.
you can read about it here:
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
 Or can you tweak it somehow so that it won't' take some much disk space?
you can bzip old logs if you need them but don't want them to take so much
space :)



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

Bin logs...

2007-05-03 Thread Ashley M. Kirchner



   Probably a silly question, but if I have my logs set to binary, how 
can I read them and check on a query that's failing?  I don't 
necessarily want to switch to text logs because we'll want to do 
replication soon here, so I want to keep it as a binary log.  My problem 
is I have a query that's failing and I can't figure it, I can't read the 
log file.


   Suggestions anyone?

--
W | It's not a bug - it's an undocumented feature.
 +
 Ashley M. Kirchner mailto:[EMAIL PROTECTED]   .   303.442.6410 x130
 IT Director / SysAdmin / Websmith . 800.441.3873 x130
 Photo Craft Imaging   . 3550 Arapahoe Ave. #6
 http://www.pcraft.com . .  ..   Boulder, CO 80303, U.S.A. 




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

Re: Bin logs...

2007-05-03 Thread Gerald L. Clark


Ashley M. Kirchner wrote:


   Probably a silly question, but if I have my logs set to binary, how 
can I read them and check on a query that's failing?  I don't 
necessarily want to switch to text logs because we'll want to do 
replication soon here, so I want to keep it as a binary log.  My problem 
is I have a query that's failing and I can't figure it, I can't read the 
log file.


   Suggestions anyone?


Use the mysqlbinlog command.

--
Gerald L. Clark
Supplier Systems Corporation

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

a script to archive binary logs

2007-05-02 Thread Ofer Inbar

For disaster recovery, it's good to have copies of your database dumps
that you can easily  conveniently access, that are outside the data
center where the database lives.  Since we do a weekly full dump and
use binary logs for incrementals, I also wanted copies of our binary
logs in the same place.

However, binary logs for an active server can be very big.  It'd be
nice to gzip them them for faster transfer, lower bandwidth charges,
less disk space used on the backup host, etc.  And they compress well:
in my experience, usually to about 1/10th of original size.

Unfortunately, if you gzip the destination, you can't easily use rsync
to make the backups, since it won't correctly identify which files
need to be copied or deleted.  So I wrote this script, which syncs one
directory to another, gzip'ing the resulting files - but only files
whose name matches a regex you set at the beginning.  It knows that
each file in the source dir corresponds to a file with the same name
with .gz appended in the destination dir, and correctly figures out
which ones to copy over and which ones to delete.

I posted the generic version at: http://thwip.sysadmin.org/dirsyncgz

Here it is, with variables set for typical mysql binary log use:

--
#!/usr/bin/perl
#
# $Id: dirsyncgz,v 1.1 2007/05/03 04:15:35 cos Exp $
#
# syncs files w/names matching a regex from srcdir to destdir, and gzips
#
# only files whose modification time is more recent than the
# corresponding gzip'ed file will be copied, and if a file has been
# deleted from the srcdir, the corresponding gzip'ed file will be
# deleted from the destdir

my $srcdir = /var/lib/mysql;
my $destdir = /backup/mysqllogs;
my $basename = ^binlog.\d+$;

opendir SRCDIR, $srcdir or die $0: can't open directory $srcdir: $!\n;

foreach $file
  ( sort grep { /$basename/  -f $srcdir/$_ } readdir(SRCDIR) )

{ next unless ((stat($srcdir/$file))[9]  (stat($destdir/$file.gz))[9]);
  print Copying $srcdir/$file to $destdir\n;
  
  system(cp -p $srcdir/$file $destdir) == 0
or warn $0: cp -p $srcdir/$file $destdir failed: $?\n
and next;
  system(gzip -f $destdir/$file) == 0
or warn $0: gzip -f $destdir/$file failed: $?\n;
}

# now delete from the backup dir any logs deleted from the srcdir

opendir DESTDIR, $destdir or die $0: can't open directory $destdir: $!\n;

foreach $savedfile
  ( sort grep { /$basename/  -f $destdir/$_ } readdir(DESTDIR) )
{ $savedfile =~ s/.gz$//;
  next if -f $srcdir/$savedfile;

  print Deleting $savedfile from $destdir\n;
  unlink $destdir/${savedfile}.gz
or unlink $destdir/$savedfile
or warn $0: error deleting $savedfile: $!\n;
}
--

You can sync the logs to a remotely mounted filesystem and/or use its
destination directory as a source directory for your rsync.

  --  Cos (Ofer Inbar)  --  [EMAIL PROTECTED]
  It's been said that if a sysadmin does his job perfectly, he's the
  fellow that people wonder what he does and why the company needs him,
  until he goes on vacation.  -- comp.unix.admin FAQ

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

Re: Bin logs and mysql 4

2007-04-30 Thread Scott Tanner

On Fri, 2007-04-27 at 09:19 -0500, [EMAIL PROTECTED] wrote:
 So if one is doing a full mysqldump every night, all bin-logs can be 
 deleted after this? 

  On the slave - Yes. In fact I would highly recommend it before
starting the slave processes again. This will reset the bin log's
'position' back to 0, giving you a clear starting point that will
correspond to the backup.  For the master server (if your backing up
from the slave) you'll only want to purge the bin logs back to your
slowest slave.


 If bin-logging is disabled, will master/slave syncing still occur?
 
No, if you disable the bin-logs, replication wont be happening.


Regards,
Scott


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

Re: Bin logs and mysql 4

2007-04-27 Thread dpgirago

So if one is doing a full mysqldump every night, all bin-logs can be 
deleted after this? 

If bin-logging is disabled, will master/slave syncing still occur?

David

 Issuing a 'reset master' will purge all of the logs as well. I wouldn't
 just rm them, as they are being tracked in the index file. 

 If you aren't running a slave, then these files are only good for data
 recovery purposes. Say a DBA goes crazy and deletes all of the databases
 mid-day (too much stress). You could restore the previous nights backup
 and run these bin logs up to the point of the delete command - a little
 bit of editing would be needed to do this, but you get the idea.

 For this to work smoothly, you need to reset the logs after every
 backup. If your using mysqldump, just add the --delete-master-logs
 option. 

  If you want to turn the logs off, remove log-bin and log-bin-index
 from the conf file.

 
 Regards,
 Scott Tanner


On Thu, 2007-04-26 at 09:47 -0700, Scott Haneda wrote:
   In the short term, see the manual page for PURGE MASTER LOGS.  In 
the
   long term, write a cron job.
   
   innotop (http://sourceforge.net/projects/innotop) also has a new
   feature, unreleased because I just wrote it a few hours ago, which 
will
   help you figure out which binlogs can be purged s keystroke 
:-)--delete-master-logs
  
  I don't quite get this, if SHOW SLAVE STATUS shows empty result set, 
and I
  am just running one server, not a master + slave setup at all, its 
really
  rather simple.
  
  So, how would I ever know what logs I can safely delete or purge?
  
  Do I really need to use mysql to purge them or can I just `rm` them?
  
  I guess I could push this to cron?
  PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);
  
  My question is, what are these logs really good for, I assume 
restoration,
  and from what I read, but how do I know how far back I should keep?
  
  thanks
  -- 
  -
  Scott HanedaTel: 415.898.2602
  http://www.newgeo.com Novato, CA U.S.A.

Bin logs and mysql 4

2007-04-26 Thread Scott Haneda

Running mysql 4, just poked into data and see I have gigs and gigs of
hostname-bin.xxx log files.

How does one maintain these, can someone point me to relevant data on what
to do about drive space being lost to these?

thanks
-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.



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

Re: Bin logs and mysql 4

2007-04-26 Thread Baron Schwartz


Hi,

Scott Haneda wrote:

Running mysql 4, just poked into data and see I have gigs and gigs of
hostname-bin.xxx log files.

How does one maintain these, can someone point me to relevant data on what
to do about drive space being lost to these?

thanks


See attached message I just sent to another user a bit ago :-)

Baron
---BeginMessage---

Hello,

Brown, Charles wrote:

Hello All. My bin.log directory is getting full with bin.log files. We
are running out of space. What can I do in the short term? Is there a
command that I can issue that will get rid of old bin log files not
needed?


In the short term, see the manual page for PURGE MASTER LOGS.  In the 
long term, write a cron job.


innotop (http://sourceforge.net/projects/innotop) also has a new 
feature, unreleased because I just wrote it a few hours ago, which will 
help you figure out which binlogs can be purged safely with a single 
keystroke :-)


Baron


---End Message---

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

Re: Bin logs and mysql 4

2007-04-26 Thread Scott Haneda

 In the short term, see the manual page for PURGE MASTER LOGS.  In the
 long term, write a cron job.
 
 innotop (http://sourceforge.net/projects/innotop) also has a new
 feature, unreleased because I just wrote it a few hours ago, which will
 help you figure out which binlogs can be purged safely with a single
 keystroke :-)

I don't quite get this, if SHOW SLAVE STATUS shows empty result set, and I
am just running one server, not a master + slave setup at all, its really
rather simple.

So, how would I ever know what logs I can safely delete or purge?

Do I really need to use mysql to purge them or can I just `rm` them?

I guess I could push this to cron?
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);

My question is, what are these logs really good for, I assume restoration,
and from what I read, but how do I know how far back I should keep?

thanks
-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.



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

Re: Bin logs and mysql 4

2007-04-26 Thread Baron Schwartz


Hi Scott,

Scott Haneda wrote:

In the short term, see the manual page for PURGE MASTER LOGS.  In the
long term, write a cron job.

innotop (http://sourceforge.net/projects/innotop) also has a new
feature, unreleased because I just wrote it a few hours ago, which will
help you figure out which binlogs can be purged safely with a single
keystroke :-)


I don't quite get this, if SHOW SLAVE STATUS shows empty result set, and I
am just running one server, not a master + slave setup at all, its really
rather simple.

So, how would I ever know what logs I can safely delete or purge?

Do I really need to use mysql to purge them or can I just `rm` them?

I guess I could push this to cron?
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);

My question is, what are these logs really good for, I assume restoration,
and from what I read, but how do I know how far back I should keep?

thanks


Yes -- sorry for being so  general.  You can use the binlogs for a) 
replication b) replaying changes since your last backup so you get 
point-in-time recovery.  If you have no replication slaves, just delete 
everything older than your latest backup.  You  can just use 'rm'.  If 
you use PURGE MASTER LOGS BEFORE, it's a bit easier than cron because 
you can do it across all platforms easily.  On UNIX of course, you'd use 
something like


find /var/lib/mysql/data -name *.bin -mtime +7 -exec rm {} \

(My find syntax is guaranteed to be wrong there... don't run that as I 
typed it).


But if you do it via SQL, you don't have to mess with this.

Baron

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

Re: Bin logs and mysql 4

2007-04-26 Thread Scott Haneda

 Yes -- sorry for being so  general.  You can use the binlogs for a)
 replication b) replaying changes since your last backup so you get
 point-in-time recovery.  If you have no replication slaves, just delete
 everything older than your latest backup.  You  can just use 'rm'.  If
 you use PURGE MASTER LOGS BEFORE, it's a bit easier than cron because
 you can do it across all platforms easily.  On UNIX of course, you'd use
 something like
 
 find /var/lib/mysql/data -name *.bin -mtime +7 -exec rm {} \
 
 (My find syntax is guaranteed to be wrong there... don't run that as I
 typed it).
 
 But if you do it via SQL, you don't have to mess with this.

Thanks!
So, I take it since I do not have a slave at all, I could safely just
disable this feature altogether?

If I do not need point in time recovery, and the once every 12 hour dump I
do across all databases is ok with me, I suppose I can just disable said
feature?  Heck, some of these boogers are a GB each :-)
-- 
-
Scott HanedaTel: 415.898.2602
http://www.newgeo.com Novato, CA U.S.A.



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

Re: Bin logs and mysql 4

2007-04-26 Thread Scott Tanner

 Issuing a 'reset master' will purge all of the logs as well. I wouldn't
just rm them, as they are being tracked in the index file.  

 If you aren't running a slave, then these files are only good for data
recovery purposes. Say a DBA goes crazy and deletes all of the databases
mid-day (too much stress). You could restore the previous nights backup
and run these bin logs up to the point of the delete command - a little
bit of editing would be needed to do this, but you get the idea.

 For this to work smoothly, you need to reset the logs after every
backup. If your using mysqldump, just add the --delete-master-logs
option. 

  If you want to turn the logs off, remove log-bin and log-bin-index
from the conf file.


Regards,
Scott Tanner



On Thu, 2007-04-26 at 09:47 -0700, Scott Haneda wrote:
  In the short term, see the manual page for PURGE MASTER LOGS.  In the
  long term, write a cron job.
  
  innotop (http://sourceforge.net/projects/innotop) also has a new
  feature, unreleased because I just wrote it a few hours ago, which will
  help you figure out which binlogs can be purged s keystroke 
  :-)--delete-master-logs
 
 I don't quite get this, if SHOW SLAVE STATUS shows empty result set, and I
 am just running one server, not a master + slave setup at all, its really
 rather simple.
 
 So, how would I ever know what logs I can safely delete or purge?
 
 Do I really need to use mysql to purge them or can I just `rm` them?
 
 I guess I could push this to cron?
 PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY);
 
 My question is, what are these logs really good for, I assume restoration,
 and from what I read, but how do I know how far back I should keep?
 
 thanks
 -- 
 -
 Scott HanedaTel: 415.898.2602
 http://www.newgeo.com Novato, CA U.S.A.
 
 
 


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

Re: Bin logs and mysql 4

2007-04-26 Thread Baron Schwartz


So, I take it since I do not have a slave at all, I could safely just
disable this feature altogether?

If I do not need point in time recovery, and the once every 12 hour dump I
do across all databases is ok with me, I suppose I can just disable said
feature?  Heck, some of these boogers are a GB each :-)

Sounds reasonable to me!

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

Re: Differnt logs for each databse

2006-12-09 Thread Mathieu Bruneau

Sachin Sharma a écrit :
 
 Hi,
 
 Is it possible to have logs for each database into differnt files ?
 Rather on single file with log=/var/log/mysqld.log.
It's not possible no... Maybe you should look at running different mysql
instance for each of them that need separate lock! (Prevent join between
the db but allow for tuning of param for each, and allow more memory to
be used than the 3-4G limit per process on 32bits architecture)
mysqld_multi allows easy management of those kind of setup!

-- 
Mathieu Bruneau
aka ROunofF

===
GPG keys available @ http://rounoff.darktech.org

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

Differnt logs for each databse

2006-12-08 Thread Sachin Sharma



Hi,

Is it possible to have logs for each database into differnt files ? 
Rather on single file with log=/var/log/mysqld.log.

--
Sachin Sharma
Sr. Engineer Systems (Linux)
Net4India Ltd.

D-25
Sector 3
Noida-201301
INDIA

Tel:   0120-5323500
Fax:  0120-5323520
URL: http://www.net4.in

This message may contain confidential and/or privileged information. If 
you are not the addressee or authorized to receive this for the 
addressee, you must not use, copy, disclose or take any action based on 
this message or any information herein. If you have received this 
message in error, please advise the sender immediately by reply e-mail 
and delete this message. Thank you for your cooperation.





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

Selecting Disk Layouts for Logs DB Files

2006-11-28 Thread Jason J. W. Williams


Hello,

I'm hitting a performance wall on my MySQL primarily I believe because
the bin log and the InnoDB logs are on the same volume group as
another MySQL server. In reality, I have four MySQL servers, two per
server (in Solaris Containers). All four are sharing the same volume
group to maximize the spindle count. Unfortunately, that's driving the
seek time crazy.

Optimally, I would give each MySQL server two volume groups (one for
its logs and another for its databases). That would ensure that the
logs and databases each had dedicated disks in the array.

Unfortunately, given my array configuration I'm limited to 6 volume
groups (unlimited LUNs inside those VGs), and would need 8 VGs to give
each of the 4 servers two VGs. The issue is complicated by the fact
that the second 2 MySQL servers are slaves of the first two. So the
data written to one server is identically written a few seconds later
to the slave.

So my question is, which would be the better trade-off:

1.) Put the logs for two master MySQL servers on one VG, and then the
databases for those master MySQL servers on a second VG.

or

2.) Put the logs for a master and slave MySQL server on the same VG,
and then put the databases for the slave and master on a second VG.

Or is there a better way of splitting the IO for different disks?

Any help is greatly appreciated.

Best Regards,
Jason

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

purging bin logs

2006-10-10 Thread George Law

Hi All,

I have a question on purging some old bin-logs.

whats the best way to do it?  

This is a fairly old version - 4.0.18-standard-log.  I have 128 1 GB
files out there, going back 8 months.  

I think the correct syntax is : 
 PURGE BINARY LOGS TO 'mysql-bin.010'; 

but from what the previous admin who I inherited this from says, this
locks up the whole database while its purging the logs.

Are there any low-impact solutions?

This is a fairly high traffic DB, so locking up the database really is
not an option.

Thanks!


George Law
glaw at ionosphere.net

 

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

RE: purging bin logs

2006-10-10 Thread Daevid Vincent

http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html

DÆVID  

 -Original Message-
 From: George Law [mailto:[EMAIL PROTECTED] 
 Sent: Tuesday, October 10, 2006 11:56 AM
 To: mysql@lists.mysql.com
 Subject: purging bin logs

 Hi All,

 I have a question on purging some old bin-logs.

 whats the best way to do it?  

 This is a fairly old version - 4.0.18-standard-log.  I have 128 1 GB
 files out there, going back 8 months.  

 I think the correct syntax is : 
  PURGE BINARY LOGS TO 'mysql-bin.010'; 

 but from what the previous admin who I inherited this from says, this
 locks up the whole database while its purging the logs.

 Are there any low-impact solutions?

 This is a fairly high traffic DB, so locking up the database really is
 not an option.

 Thanks!

 George Law
 glaw at ionosphere.net

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

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

MySQL socket and logs locations

2006-09-24 Thread Peter Bradley


Hi,

I've installed MySQL 5.0.24a-standard on my AMD 64, SuSE 10.0 box, using 
the binary distribution from the MySQL downloads site.  I've also 
installed the GUI tools from the same location, using the SuSE 10.x rpm 
packages.  The intention is to use MySQL with PHP5 and Apache.  Both 
these latter have been installed from the SuSE rpm files supplied on DVD 
with SuSE 10.0.  I have a couple of problems. 

Firstly, PHP seems to want the mysql.sock file to be in 
/var/lib/mysql/mysql.sock - at least this is the location given by 
phpinfo() - instead of /tmp/mysql.sock.  This location appears to be 
contained in an environment variable called MYSQL_SOCKET, but I can't 
find where it is set.  One solution would be to get MySQL to put the 
mysql.sock file in the place expected by PHP.  Is this possible?


At the moment, I have a /etc/my.cnf file with the following content:

[mysqld]
socket=/tmp/mysql.sock

[client]
socket=/tmp/mysql.sock

I added these entries in an attempt to get PHP to use the actual 
location that MySQL is using, but this doesn't appear to have had any 
effect (according to phpinfo()).


Secondly, MySQL appears to be trying to write logs to /var/log/mysql 
(according to MySQL Administrator).  This location does not exist.  I'm 
getting no error messages, but presumably this means I'm not getting any 
logs either.  Should I create this location and if so, what permissions 
and ownership should I give to the directory?


Thanks in advance


Peter


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

Re: MySQL socket and logs locations

2006-09-24 Thread Russbucket

Peter Bradley wrote:

 Hi,
 
 I've installed MySQL 5.0.24a-standard on my AMD 64, SuSE 10.0 box, using
 the binary distribution from the MySQL downloads site.  I've also
 installed the GUI tools from the same location, using the SuSE 10.x rpm
 packages.  The intention is to use MySQL with PHP5 and Apache.  Both
 these latter have been installed from the SuSE rpm files supplied on DVD
 with SuSE 10.0.  I have a couple of problems.
 
 Firstly, PHP seems to want the mysql.sock file to be in
 /var/lib/mysql/mysql.sock - at least this is the location given by
 phpinfo() - instead of /tmp/mysql.sock.  This location appears to be
 contained in an environment variable called MYSQL_SOCKET, but I can't
 find where it is set.  One solution would be to get MySQL to put the
 mysql.sock file in the place expected by PHP.  Is this possible?
 
 At the moment, I have a /etc/my.cnf file with the following content:
 
 [mysqld]
 socket=/tmp/mysql.sock
 
 [client]
 socket=/tmp/mysql.sock
 
 I added these entries in an attempt to get PHP to use the actual
 location that MySQL is using, but this doesn't appear to have had any
 effect (according to phpinfo()).
 
 Secondly, MySQL appears to be trying to write logs to /var/log/mysql
 (according to MySQL Administrator).  This location does not exist.  I'm
 getting no error messages, but presumably this means I'm not getting any
 logs either.  Should I create this location and if so, what permissions
 and ownership should I give to the directory?
 
 Thanks in advance
 
 
 Peter
I just check my install of mysql (version mysql-4.1.13-3.8 on SUSE 10.0) and
the socket is in /var/lib/mysql and the mysqld log is in /var/lib/mysql.
This is the mysql version that came with my 10.0 DVD. Did you use YAST to
install? I don't know if these locations changed with the later version of
mysql.

From phpinfo:
MYSQL_SOCKET   /var/lib/mysql/mysql.sock
MYSQLI_SOCKET   /var/lib/mysql/mysql.sock

mysql.default_socket  no value  no value

Are your sockets enabled:
sockets
Sockets Support   enabled

Sorry I cannot help anymore.
-- 
Russ

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

Re: MySQL socket and logs locations

2006-09-24 Thread Peter Bradley


Russbucket wrote:

I just check my install of mysql (version mysql-4.1.13-3.8 on SUSE 10.0) and
the socket is in /var/lib/mysql and the mysqld log is in /var/lib/mysql.
This is the mysql version that came with my 10.0 DVD. Did you use YAST to
install? I don't know if these locations changed with the later version of
mysql.
  
Thanks a lot for that, Russ.  The difference between our two setups is 
that you've done the SuSE 10.0 YaST install of mysql 4.1.  I needed to 
upgrade from that to MySQL 5.0, because I wanted to use stored 
procedures.  The result is that I had to do a binary install from files 
supplied by MySQL on their site.  It was either that or upgrade to SuSE 
10.1.


However, I think I've sorted it by altering my /etc/my.cnf file.  Time 
will tell :)

Sorry I cannot help anymore.
  

Thanks for trying.  It's appreciated.


Peter



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

Slow log logs non-slow statements

2006-08-15 Thread Dominik Klein


I have specified

log-slow-queries
long-query-time=10

in my.cnf and restarted my server. After that I see statements logged 
into the slow-log-file.


But it seems that mysql logs too much into that file.

When executing this statement:

mysql show variables like %tx%;
+---+-+
| Variable_name | Value   |
+---+-+
| tx_isolation  | REPEATABLE-READ |
+---+-+
1 row in set (0,00 sec)

it immediately shows up in the slow-log:

# Time: 060815 14:40:22
# [EMAIL PROTECTED]: root[root] @ localhost []
# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 1
show variables like %tx%;

This is also true vor simple select statements which give a result in 
(0,00 sec).


How can I make mysql log only those slow queries, that are really slow.

Thanks in advance
Dominik

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

Re: Slow log logs non-slow statements

2006-08-15 Thread Jay Pipes

It is likely you are also logging any queries not using an index (doing
full table scans).  Check the configuration variable: log_long_format

Cheers,
Jay

On Tue, 2006-08-15 at 14:45 +0200, Dominik Klein wrote:
 I have specified
 
 log-slow-queries
 long-query-time=10
 
 in my.cnf and restarted my server. After that I see statements logged 
 into the slow-log-file.
 
 But it seems that mysql logs too much into that file.
 
 When executing this statement:
 
 mysql show variables like %tx%;
 +---+-+
 | Variable_name | Value   |
 +---+-+
 | tx_isolation  | REPEATABLE-READ |
 +---+-+
 1 row in set (0,00 sec)
 
 it immediately shows up in the slow-log:
 
 # Time: 060815 14:40:22
 # [EMAIL PROTECTED]: root[root] @ localhost []
 # Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 1
 show variables like %tx%;
 
 This is also true vor simple select statements which give a result in 
 (0,00 sec).
 
 How can I make mysql log only those slow queries, that are really slow.
 
 Thanks in advance
 Dominik
 


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

1 2 3 4 >

1 - 100 of 311 matches

Mail list logo