Re: mysql binlogs and their expiry times

On Wed, Oct 8, 2008 at 3:10 AM, Zbigniew Szalbot <[EMAIL PROTECTED]> wrote: > Hi there, > > I hope someone can help. Due to they way my HD has been sliced I had > to move mysql database to /usr/local/mysql. All works fine. Last week > I added this entry: > > #expire bin logs > expire_logs_days = 7

RE: mysql binlogs and their expiry times

You shouldn't have to flush the logs manually. I have been flushing binary logs using this expire_logs_days for some time with no other intervention. One thing to keep in mind is that if you have limited disk space, and an usual spike in updates your binary logs can still grow and consume all ava

Re: Finding gaps

Jerry, Here is a workaround for 4.1.22: SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id < b.id GROUP BY a.id HAVING `Missing From` < MIN(b.id); +--+--+ | Missing From | To | +--+--+ |3 |3 | |

DB Crash: Wrong bytesec: 0- 0- 0 at 0;

Hi. I had a very big trouble with my MySQL databases. After four years of usage of the MySQL all DBs crashed. Each table is marked as crashed and myisamchk trucates the table, mostly to zero rows. Some of them are not totaly dropped: # myisamchk -o klik_cas.MYI - recovering (with keycache) MyISAM

RE: Finding gaps

Well, 5.x accepted the query. It's been running for awhile, now, so I'll find out later if it did what I need. >-Original Message- >From: Peter Brawley [mailto:[EMAIL PROTECTED] >Sent: Wednesday, October 08, 2008 5:25 PM >To: Jerry Schwartz; mysql@lists.mysql.com >Subject: Re: Finding gaps

Re: Finding gaps

I must be missing something obvious; or does this not work in 4.1.22? Looks like a 4.1.22 bug. PB Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl

RE: Finding gaps

>-Original Message- >From: Gerald L. Clark [mailto:[EMAIL PROTECTED] >Sent: Wednesday, October 08, 2008 4:44 PM >To: Jerry Schwartz >Cc: mysql@lists.mysql.com >Subject: Re: Finding gaps > >Jerry Schwartz wrote: >> I'm finally getting back to this issue, and I've read the bits on >> artful

Re: Finding gaps

Jerry Schwartz wrote: I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id < b.id GROUP BY a.id HAVING a.id < MIN(b.id) - 1; SELECT a.id+1 AS `Missing_From`

RE: Finding gaps

I'm finally getting back to this issue, and I've read the bits on artfulsoftware. The example SELECT a.id+1 AS 'Missing From', MIN(b.id) - 1 AS 'To' FROM tbl AS a, tbl AS b WHERE a.id < b.id GROUP BY a.id HAVING a.id < MIN(b.id) - 1; Looks like exactly what I want. However, when I try it (pro

Re: Foreign Keys

On Wed, Oct 8, 2008 at 11:56 AM, Jim Lyons <[EMAIL PROTECTED]> wrote: > Indexes speed up joins. Foreign keys should be indexes themselves, so they > can also speed up joins. If the FK is not an index, it won't help. So, > index your FKs If you add a FOREIGN KEY constraint in MySQL 5+ it adds an

Re: Foreign Keys

I'll check the site mentioned, thank-you so much! http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html PB/ - / Ben A. Hilleli wrote: >So you are talking about parent-child relationships in a single table, or in the technical jargon >reflexive relatio

RE: Foreign Keys

>So you are talking about parent-child relationships in a single table, or in the technical jargon >reflexive relationships. See www.artfulsoftware.com for detailed examples of how this is done, >but here is the thumbnail sketch:

Re: Foreign Keys

So you are talking about parent-child relationships in a single table, or in the technical jargon reflexive relationships. See www.artfulsoftware.com for detailed examples of how this is done, but here is the thumbnail sketch: The table has to have a ParentID column (call it what you want) that po

Re: Foreign Keys

I know that this is the standard means of dealing with a many to many relationship, I'm just not sure it fits here. > USER: >emailID (PK) >userName >Password >Address >Etc > > FRIEND: >emailID (PK) > > USER_FRIEND >user_emailID

Master-master setup

I am running 5.0.24a on Slackware Linux. I would like to set up a master-master replication process so that I can use both servers as master as add/delete/update records on both servers from different application servers (Tomcat.) I suspect the inserts will be OK but don't understand how the e

Current state of CONNECT BY support in MySQL?

Many people seem to ask this question periodically but I wanted the most recent answer... What's the current state of having support for CONNECT BY in MySQL? I'm using MySQL 5.0.45 on CentOS 5.2 and noticed in the documentation that I can't write my own recursive functions: "Stored functi

Re: mysql binlogs and their expiry times

we do the same thing. works just fine. On Wed, Oct 8, 2008 at 10:55 AM, Tom Brown <[EMAIL PROTECTED]> wrote: > > According to the doc for this parameter, "Possible removals happen at >> startup and at binary log rotation." This implies that removal is not >> automatic - you have to either flus

Re: Foreign Keys

Indexes speed up joins. Foreign keys should be indexes themselves, so they can also speed up joins. If the FK is not an index, it won't help. So, index your FKs On Wed, Oct 8, 2008 at 10:43 AM, Ben A.H. <[EMAIL PROTECTED]> wrote: > Does using foreign keys simply enforce referential integrity O

Re: mysql binlogs and their expiry times

According to the doc for this parameter, "Possible removals happen at startup and at binary log rotation." This implies that removal is not automatic - you have to either flush logs or bounce the server. We use "Purge Master" periodically. i do this from cron mysql -e "PURGE MASTER LOGS

Re: Question of Relationship between tables

Usually, you'd have 3 tables: USER, FRIEND, and a third table named something like USER_FRIEND. They'd be set up like: USER: emailID (PK) userName Password Address Etc FRIEND: emailID (PK) USER_FRIEND user_emailID (PK) friend_emailID (PK)

Re: mysql binlogs and their expiry times

According to the doc for this parameter, "Possible removals happen at startup and at binary log rotation." This implies that removal is not automatic - you have to either flush logs or bounce the server. We use "Purge Master" periodically. On Wed, Oct 8, 2008 at 3:10 AM, Zbigniew Szalbot <[EMAIL

Foreign Keys

Does using foreign keys simply enforce referential integrity OR can it also speed up JOIN queries? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Question of Relationship between tables

Hello, I'm having conceptualizing the correct relationship for what seems a very simple scenario: Scenario: I have a standard "USERS" table... USERS have a list of "FRIENDS", these can be other members or also non members... Similar to facebook... My main issue is conceptualizing the relations

OpenSQL Camp -- a conference for open-source databases

OpenSQL Camp is a hybrid conference and hackfest on November 14-16, 2008 in Charlottesville, Virginia USA. It's of, by and for open-source databases. A lot of celebrities will be here, including the creator of SQLite, and of course Brian Aker and Monty Widenius and Peter Zaitsev etc. It's free, a

Re: mysql binlogs and their expiry times

2008/10/8 Rolando Edwards <[EMAIL PROTECTED]>: > At the Linux prompt, enter this : cat mysql-bin.index > > Make sure every binlog is in the mysql-bin.index. If the list is incorrect, > then the expire logs feature won't work. > > To test this out, try using PURGE MASTER LOGS TO ' mysql-bin.55'

RE: mysql binlogs and their expiry times

At the Linux prompt, enter this : cat mysql-bin.index Make sure every binlog is in the mysql-bin.index. If the list is incorrect, then the expire logs feature won't work. To test this out, try using PURGE MASTER LOGS TO ' mysql-bin.55'; If this doesn't work, this verifies that the contents

Trouble with large data in MySql

Hi, We have 4 tables in which we have approximately 40 Million records per month. We are having trouble getting results from MySql as it takes about 4-5 hours to complete for each query. We are using this primarily for reporting purposes. My table schema is as follows SMAS Table: Colum

Re: mysql binlogs and their expiry times

Check show variables like 'expire_log%'; It must show expire_logs_days = 7 On Wed, Oct 8, 2008 at 2:44 PM, Zbigniew Szalbot <[EMAIL PROTECTED]> wrote: > Hello, > > 2008/10/8 Uma Bhat <[EMAIL PROTECTED]>: > > To check the current my.cnf file which is being used by the server, use > > below > >

Re: mysql binlogs and their expiry times

Hello, 2008/10/8 Uma Bhat <[EMAIL PROTECTED]>: > To check the current my.cnf file which is being used by the server, use > below > > $ ps -ef | grep mysql > You should be able to see the my.cnf path used (usually specified in > --defaults-file="/path to my.cnf") Yes, it was referring to the cor

mysql binlogs and their expiry times

Hi there, I hope someone can help. Due to they way my HD has been sliced I had to move mysql database to /usr/local/mysql. All works fine. Last week I added this entry: #expire bin logs expire_logs_days = 7 to /usr/local/mysql/my.cnf I restarted the MySQL server and now I have been waiting for