Re: problem open a innodb table

[Yogesh Kore]

Dont you have mysql dump file for those table? It is best way to restore
InnoDB tables.

2011/1/7 Elim PDT 

>  I messed up the ibdata1,ib_logfile1,0 files and encounter the problems of
> openning innodb tables even after I copied the back-up files of few months
> ago. I don't have many innodb tables and so this not causing too much
> trouble.
>
> I don't know what to do for restoring those tables, and not even know if
> that is possible or not. Please help.
>
> Attached is a table of primes that contains 2+ million primes yet it is so
> small that I can attached here.
>
> Thanks
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?unsub=yogeshk...@gmail.com
>

problem open a innodb table

[Elim PDT]

I messed up the ibdata1,ib_logfile1,0 files and encounter the problems of 
openning innodb tables even after I copied the back-up files of few months ago. 
I don't have many innodb tables and so this not causing too much trouble.

I don't know what to do for restoring those tables, and not even know if that 
is possible or not. Please help.

Attached is a table of primes that contains 2+ million primes yet it is so 
small that I can attached here.

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: cleaning up general_log table?

[Jacek Becla]

Hi Ronaldo,

After some digging I did find a way!

USE mysql;
CREATE TABLE gn2 LIKE general_log;
RENAME TABLE general_log TO oldLogs, gn2 TO general_log;

the rename is atomic, so it is quick, there is no
need to stop the server. After that, oldLogs is
very easy to mess up with

thanks,
Jacek





On 01/06/2011 07:53 PM, Rolando Edwards wrote:

Since that table is a MyISAM table, you must do the following:

01. In mysql, CREATE DATABASE IF NOT EXISTS mystuff;
02. service mysql stop
03. cd /var/lib/mysql/mysql
04. mv general_log.* ../mystuff
05. comment out general log from my.cnf
06. service mysql start
07. In mysql, DELETE FROM mystuff.general_log WHERE event_time<'2011-01-01';
08. service mysql stop
09. cd /var/lib/mysql/mstuff
10. mv general_log.* ../mysql
11. enable general log in my.cnf
12. service mysql start
13. In mysql, DROP DATABASE mystuff;

Does anyone know a shorter way ???

Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM&  Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: Jacek Becla [mailto:be...@slac.stanford.edu]
Sent: Thursday, January 06, 2011 5:57 PM
To: mysql@lists.mysql.com
Subject: cleaning up general_log table?

Hello,

Is there a way to purge parts of general log table
without interrupting the server? Ideally, we would
like to transfer older logs (say copy all entries
from 2010 into a separate location, and then run:

DELETE FROM mysql.general_log WHERE event_time<'2011-01-01'

However this yields an error:

ERROR 1556 (HY000): You can't use locks with log tables.

What is a recommended way to clean up mysql.general_log?

Thanks,
Jacek




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

RE: cleaning up general_log table?

[Rolando Edwards]

Since that table is a MyISAM table, you must do the following:

01. In mysql, CREATE DATABASE IF NOT EXISTS mystuff;
02. service mysql stop
03. cd /var/lib/mysql/mysql
04. mv general_log.* ../mystuff
05. comment out general log from my.cnf
06. service mysql start
07. In mysql, DELETE FROM mystuff.general_log WHERE event_time<'2011-01-01';
08. service mysql stop
09. cd /var/lib/mysql/mstuff
10. mv general_log.* ../mysql
11. enable general log in my.cnf
12. service mysql start
13. In mysql, DROP DATABASE mystuff;

Does anyone know a shorter way ???

Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: Jacek Becla [mailto:be...@slac.stanford.edu] 
Sent: Thursday, January 06, 2011 5:57 PM
To: mysql@lists.mysql.com
Subject: cleaning up general_log table?

Hello,

Is there a way to purge parts of general log table
without interrupting the server? Ideally, we would
like to transfer older logs (say copy all entries
from 2010 into a separate location, and then run:

DELETE FROM mysql.general_log WHERE event_time<'2011-01-01'

However this yields an error:

ERROR 1556 (HY000): You can't use locks with log tables.

What is a recommended way to clean up mysql.general_log?

Thanks,
Jacek

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net


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

cleaning up general_log table?

[Jacek Becla]

Hello,

Is there a way to purge parts of general log table
without interrupting the server? Ideally, we would
like to transfer older logs (say copy all entries
from 2010 into a separate location, and then run:

DELETE FROM mysql.general_log WHERE event_time<'2011-01-01'

However this yields an error:

ERROR 1556 (HY000): You can't use locks with log tables.

What is a recommended way to clean up mysql.general_log?

Thanks,
Jacek

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

Efficient Blobbing?

[Felix E. Klee]

I am dealing with blobs up to - I estimate - about 100 kilo bytes in
size.

Storage engine: InnoDB

Frontend: PHP

Some questions:

* I've read somewhere that it's not good to update blobs, because it
  leads to reallocation, thus fragmentation, thus bad performance. Is
  that true? Any reference on this?

* Initially the blobs get constructed by appending data chunks. Each
  chunk is up to 16 kilo bytes in size. Is it more efficient to use a
  separate chunk table instead, for example with fields as below?

parent_id, position, chunk

  Then, to get the entire blob, one would do something like:

SELECT GROUP_CONCAT(chunk ORDER BY position) FROM chunks WHERE
parent_id = 187

  The result would be used in a PHP script.

* Is there any difference between the types of blobs, aside from the
  size needed for meta data, which should be negligible.

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

Efficient Blobbing?

[Felix E. Klee]

I am dealing with blobs up to - I estimate - about 100 kilo bytes in
size.

Storage engine: InnoDB

Frontend: PHP

Some questions:

* I've read somewhere that it's not good to update blobs, because it
  leads to reallocation, thus fragmentation, thus bad performance. Is
  that true? Any reference on this?

* Initially the blobs get constructed by appending data chunks. Each
  chunk is up to 16 kilo bytes in size. Is it more efficient to use a
  separate chunk table instead, for example with fields as below?

parent_id, position, chunk

  Then, to get the entire blob, one would do something like:

SELECT GROUP_CONCAT(chunk ORDER BY position) FROM chunks WHERE
parent_id = 187

  The result would be used in a PHP script.

* Is there any difference between the types of blobs, aside from the
  size needed for meta data, which should be negligible.

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

Efficient Blobbing?

[Felix E. Klee]

I am dealing with blobs up to - I estimate - about 100 kilo bytes in
size.

Storage engine: InnoDB

Frontend: PHP

Some questions:

* I've read somewhere that it's not good to update blobs, because it
 leads to reallocation, thus fragmentation, thus bad performance. Is
 that true? Any reference on this?

* Initially the blobs get constructed by appending data chunks. Each
 chunk is up to 16 kilo bytes in size. Is it more efficient to use a
 separate chunk table instead, for example with fields as below?

   parent_id, position, chunk

 Then, to get the entire blob, one would do something like:

   SELECT GROUP_CONCAT(chunk ORDER BY position) FROM chunks WHERE
   parent_id = 187

 The result would be used in a PHP script.

* Is there any difference between the types of blobs, aside from the
 size needed for meta data, which should be negligible.

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

Re: MySQL raw files to .SQL

[Lydia Rowe]

Hmm, no I had not. Does this mean that there is no native way to do it? (that 
would be my preference.)

Sent from my iPad

On Jan 1, 2011, at 12:27 PM, Wagner Bianchi  wrote:

> Did a tried MaatKit? MaatKit let you control over the number of threads
> dedicated to extract a MySQL backup.
> 
> Best regards.
> --
> Wagner Bianchi
> 
> 
> 2010/12/31 Lydia Rowe 
> 
>> In order to get a database into A .SQL file, I usually import the raw
>> files, .MYI and such, into a database and then run mysqldump. Is there a
>> quicker, one-step solution?
>> 
>> Thanks!
>> 
>> Sent from my iPad
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.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: Stored procedure

[Machiel Richards]

HI All

 Thank you for the responses.

I have been going through the documentation the whole of today
thus far and it seems to be easy enough.

I am still however confused on how to achieve the following
though , and this might be due to a lack of experience or I might just
not be thinking straight...


- From what I can tell the scheduled event is
created and contains the "body" of what needs to be run at the
times,etc... specified.
- The command I need to run though will be
somthing like this:

  -->   call 
()
- The purpose of the procedure is to delete all
records from specific tables older than (<) the specified date.

The procedure is already working and if I run it manually
entering the date it works 100%.

However, I need to schedule an event to run each day @ 02h00 for
instance which will then call the procedure as per above.

My problem (which I had with the bash script as well) is to
get the full correct date () passed to the
"call procedure()" statement.

 Can anybody give me some ideas as I have tried so many options
and yet none of them has worked as yet.


Regards
Machiel

-Original Message-
From: petya 
To: Machiel Richards 
Cc: mysql@lists.mysql.com
Subject: Re: Stored procedure
Date: Wed, 05 Jan 2011 12:44:07 +0100


http://dev.mysql.com/doc/refman/5.1/en/events.html

On 01/05/2011 12:21 PM, Machiel Richards wrote:
> HI
>
> How do I use the mysql event scheduler?
>
> I have not used this as yet so not sure how to use it.
>
>
> Regards
> Machiel
>
> -Original Message-
> *From*: petya mailto:petya%20%3cpe...@petya.org.hu%3e>>
> *To*: Machiel Richards  >,
> mysql@lists.mysql.com 
> *Subject*: Re: Stored procedure
> *Date*: Wed, 05 Jan 2011 12:15:59 +0100
>
> Hi,
>
> Use the mysql event scheduler instead of cron, the bash script is quite
> pointless, and call your stored procedure with now() - interval 1 day
> parameter.
>
> Peter
>
> On 01/05/2011 11:00 AM, Machiel Richards wrote:
>>  Good day all
>>
>>   I am hoping that someone can assist me here.
>>
>>   As per a client requirement, I am writing a
>>  script/stored procedure combination in order to do the following:
>>
>>   - Script to be run within a cron once a day
>>  according to a set schedule.
>>   - script to connect to mysql and call a stored
>>  procedure
>>   - stored to procedure to do the following:
>>
>>   *   retrieve row id of the record
>>  that indicates the last record of a specified date (i.e 00:00 yesterday)
>>   [select max(id) into
>>  max_id from table1 where utc<   dt]
>>
>> * delete records from table2 where
>>  id<   max_id
>>  * delete records from table1
>>  where id<   max_id
>>
>>   After a struggle to get the script and stored
>>  procedure working I am now stuck at the following point.
>>
>>   the date that needs to be specified to the
>>  stored procedure must be in the following format:
>>
>>   2011-01-04 00:00
>>  (i.e. yesterday 00:00) meaning that everything before this date and time
>>  needs to be deleted.
>>
>>   However when trying to run the script with
>>  the date like this, then I get the following message:
>>
>>
>>   ERROR 1064 (42000) at line 1: You
>>  have an error in your SQL syntax; check the manual that corresponds to
>>  your MySQL server version for the right syntax to use near '00:00)' at
>>  line 1
>>
>>
>>   I initially had the script create the
>>  date in a different manner but then the 00:00 was seen as a seperate
>>  argument which didn't work. After some changes the date is now being
>>  read correctly from what I can tell but now I get the message above.
>>
>>
>>   Herewith my script and stored procedure definitions:
>>
>>
>>
>>  Script:
>>
>>  #!/bin/bash
>>
>>  DATE="`date --date="1 days ago"  +%Y-%m-%d` 00:00"
>>  echo"$DATE"
>>
>>  mysqldump -u root -p   --databases
>>>  /backups/DB_backup.dump
>>
>>  mysql -u root -p   -D   -e"call select_delete_id_2($DATE)"
>>
>>  exit
>>
>>
>>
>>
>>  Stored Proc:
>>
>>
>>  begin declare max_id int(11); select max(id) into max_id from table1
>>  where utc<   dt; delete from table2 where id<   max_id; delete from table1
>>  where id<   max_id; end
>>
>>
>>   Does anybody perhaps have any suggestions?
>>
>>  Regards
>>  Machiel
>>
>

Re: mysql 5.5 optimize changes?

[Geoff Galitz]

As per usual, I seem to have found the answer after asking for help...

It seems our team did not cleanly remove the old centos packaged versions, 
or perhaps there is a bug in the removal scripts.  As such, an incorrect 
mysqlcheck binary was running and that somehow caused the problems.






--
From: "Geoff Galitz" 
Sent: Thursday, January 06, 2011 10:46 AM
To: 
Subject: mysql 5.5 optimize changes?




Hello.

Our team has upgraded a few of our mysql servers from 5.0.77 on centos to 
5.5.8.  Since then mysqlcheck -Ao has been failing with permissions 
errors:


mysqlcheck: Got error: 1142: SELECT,INSERT command denied to user 
'root'@'localhost' for table 'XXX' when executing 'OPTIMIZE TABLE ...


The error is pretty self-explanatory, but my question is, is this a known 
issue?  Must we go about modifying permissions on the servers we wish to 
upgrade?  I tried to locate this in the changelogs but could not find 
anything.



Thanks.





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

mysql 5.5 optimize changes?

[Geoff Galitz]

Hello.

Our team has upgraded a few of our mysql servers from 5.0.77 on centos to 
5.5.8.  Since then mysqlcheck -Ao has been failing with permissions errors:

mysqlcheck: Got error: 1142: SELECT,INSERT command denied to user 
'root'@'localhost' for table 'XXX' when executing 'OPTIMIZE TABLE ... 

The error is pretty self-explanatory, but my question is, is this a known 
issue?  Must we go about modifying permissions on the servers we wish to 
upgrade?  I tried to locate this in the changelogs but could not find anything.


Thanks.