unique URL's as ID in mysql table

2010-06-04 Thread Norman Khine 
hello, i have a mysql database that stores URL's in a table now i
would like to change the schema so that the URL's are unique so my
question is: is it appropriate to use URL's as a unique IDs if not
what are the alternatives?

any advise much appreciated

norman

-- 
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
% .join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )

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

Re: Geting current user pasword.

2010-06-04 Thread Johan De Meersman 
On Thu, Jun 3, 2010 at 8:44 PM, Michael Dykman mdyk...@gmail.com wrote:

 address, there are privacy regulations which prohibit the practice.


I fully agree with you, but as a matter of pedantry I would like to point
out that the privacy regulations you speak of are not applicable outside of
your part of the world :-)

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Missing database file names

2010-06-04 Thread Jesse F. Hughes 
Is it possible to build a .MYI file from scratch?  

I have found a file that I believe is recorded.MYD.  I have the .frm
file as well, but I don't think that recorded.MYI survived the
file system event.

-- 
No feeling sympathy for mathematicians who start marching with signs
like 'Will work for food' in the future...  I will not show mercy
going forward.  I was trained as a soldier in the United States Army
after all... We play to win. --James Harris, feel his wrath!

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

Re: Missing database file names

2010-06-04 Thread Jaime Crespo Rincón 
2010/6/4 Jesse F. Hughes je...@phiwumbda.org:
 Is it possible to build a .MYI file from scratch?

 I have found a file that I believe is recorded.MYD.  I have the .frm
 file as well, but I don't think that recorded.MYI survived the
 file system event.

Yes, the mysql utility myisamchk and the REPAIR command should be
able to regenerate an MYI file from a .MYD and .frm files.
After all, MDI file only contains the disk version of the indexes.
Beware of possible issues if using different server version, though.

-- 
Jaime Crespo
MySQL  Java Instructor
Warp Networks
http://warp.es

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

Best way to purge old records from a huge table?

2010-06-04 Thread Brian Dunning 
Hey all -

I have a table with 12,000,000 records spread over about 6 years. I'm trying to 
delete all but the last 2 years, but no matter how small of a group I try to 
delete at a time, it keeps hanging up the server and I eventually have to 
restart MySQL. The table looks like this:

  `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
CURRENT_TIMESTAMP,
  `lat` double NOT NULL default '0',
  `lon` double NOT NULL default '0',
  `referer` int(12) NOT NULL default '0',
  PRIMARY KEY  (`referer`,`lat`,`lon`),
  KEY `creation` (`creation`,`referer`)

And the query I've been trying looks like this:

delete from tablename where `creation`  '2006-04-01 00:00:00'

...trying to do the oldest 1 month of records at a time. So am I just trying a 
really inefficient query? Is there a better way to do this?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Best way to purge old records from a huge table?

2010-06-04 Thread Ananda Kumar 
dont use a single delete statment.
Use a stored proc, loop through and delete record by record and commit for
every 10k. In this way, your mysql will not hang and if you replication
setup, slave also will not lag behind.

regards
anandkl

On Fri, Jun 4, 2010 at 8:40 PM, Brian Dunning br...@briandunning.comwrote:

 Hey all -

 I have a table with 12,000,000 records spread over about 6 years. I'm
 trying to delete all but the last 2 years, but no matter how small of a
 group I try to delete at a time, it keeps hanging up the server and I
 eventually have to restart MySQL. The table looks like this:

  `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update
 CURRENT_TIMESTAMP,
  `lat` double NOT NULL default '0',
  `lon` double NOT NULL default '0',
  `referer` int(12) NOT NULL default '0',
  PRIMARY KEY  (`referer`,`lat`,`lon`),
  KEY `creation` (`creation`,`referer`)

 And the query I've been trying looks like this:

 delete from tablename where `creation`  '2006-04-01 00:00:00'

 ...trying to do the oldest 1 month of records at a time. So am I just
 trying a really inefficient query? Is there a better way to do this?
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com

Re: Best way to purge old records from a huge table?

2010-06-04 Thread Krishna Chandra Prajapati 
Hi Brian,

I would suggest you to use mk-archiver (Maatkit Tools) for this activity.

http://www.percona.com/files/presentations/Make_Life_Easier_Maatkit_v2.pdf

Regards,
Krishna

On Fri, Jun 4, 2010 at 8:40 PM, Brian Dunning br...@briandunning.comwrote:

 Hey all -

 I have a table with 12,000,000 records spread over about 6 years. I'm
 trying to delete all but the last 2 years, but no matter how small of a
 group I try to delete at a time, it keeps hanging up the server and I
 eventually have to restart MySQL. The table looks like this:

  `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update
 CURRENT_TIMESTAMP,
  `lat` double NOT NULL default '0',
  `lon` double NOT NULL default '0',
  `referer` int(12) NOT NULL default '0',
  PRIMARY KEY  (`referer`,`lat`,`lon`),
  KEY `creation` (`creation`,`referer`)

 And the query I've been trying looks like this:

 delete from tablename where `creation`  '2006-04-01 00:00:00'

 ...trying to do the oldest 1 month of records at a time. So am I just
 trying a really inefficient query? Is there a better way to do this?
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=prajapat...@gmail.com

Public history of database size, throughput?

2010-06-04 Thread Mike Spreitzer 
Are there any publicly available data on how the size of some (or better 
yet, many) particular real database(s) changed over time (for a longish 
period of time)?  How about data on how the throughput (in any interesting 
terms) varied over time?

Thanks,
Mike Spreitzer

RE: Best way to purge old records from a huge table?

2010-06-04 Thread Martin Gainty 

Hi Brian-

 

i think the best way to ensure your dates are using -MM-DD format is for 
your dml to reference dates with DATE_FORMAT('-MM-DD','%Y-%m-%d') e.g.

 

mysql select DEIT_EVENT_SEQUENCE_ID,DEIT_EVENT_STATUS_CODE,DEIT_EVENT_DATE 
from DEIT;
+++-+
| DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE |
+++-+
|1 | 1  | 2006-09-04
  |
|2 | 2  | 2006-09-05
  |
|3 | 3  | 2006-09-06
  |
+++-+
3 rows in set (0.00 sec)

 

mysql delete from DEIT where 
DEIT_EVENT_DATEDATE_FORMAT('2006-09-05','%Y-%m-%d');
Query OK, 1 row affected (0.02 sec)

 

--the record is deleted so lets select to make sure

mysql select DEIT_EVENT_SEQUENCE_ID,DEIT_EVENT_STATUS_CODE,DEIT_EVENT_DATE 
from DEIT;
+++-+
| DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE |
+++-+
|  2 | 2  | 2006-09-05  |
|  3 | 3  | 2006-09-06  |
+++-+
2 rows in set (0.00 sec)


hth

Martin Gainty 
__ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.



 

 From: br...@briandunning.com
 Subject: Best way to purge old records from a huge table?
 Date: Fri, 4 Jun 2010 08:10:07 -0700
 To: mysql@lists.mysql.com
 
 Hey all -
 
 I have a table with 12,000,000 records spread over about 6 years. I'm trying 
 to delete all but the last 2 years, but no matter how small of a group I try 
 to delete at a time, it keeps hanging up the server and I eventually have to 
 restart MySQL. The table looks like this:
 
 `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
 CURRENT_TIMESTAMP,
 `lat` double NOT NULL default '0',
 `lon` double NOT NULL default '0',
 `referer` int(12) NOT NULL default '0',
 PRIMARY KEY (`referer`,`lat`,`lon`),
 KEY `creation` (`creation`,`referer`)
 
 And the query I've been trying looks like this:
 
 delete from tablename where `creation`  '2006-04-01 00:00:00'
 
 ...trying to do the oldest 1 month of records at a time. So am I just trying 
 a really inefficient query? Is there a better way to do this?
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
 
  
_
Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox.
http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_1

Re: Best way to purge old records from a huge table?

2010-06-04 Thread Johan De Meersman 
I can't help but wonder how this is in any way relevant to the
original question.

On Fri, Jun 4, 2010 at 6:12 PM, Martin Gainty mgai...@hotmail.com wrote:

 Hi Brian-



 i think the best way to ensure your dates are using -MM-DD format is for 
 your dml to reference dates with DATE_FORMAT('-MM-DD','%Y-%m-%d') e.g.



 mysql select DEIT_EVENT_SEQUENCE_ID,DEIT_EVENT_STATUS_CODE,DEIT_EVENT_DATE 
 from DEIT;
 +++-+
 | DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE |
 +++-+
 |                                    1 | 1                      | 2006-09-04  
     |
 |                                    2 | 2                      | 2006-09-05  
     |
 |                                    3 | 3                      | 2006-09-06  
     |
 +++-+
 3 rows in set (0.00 sec)



 mysql delete from DEIT where 
 DEIT_EVENT_DATEDATE_FORMAT('2006-09-05','%Y-%m-%d');
 Query OK, 1 row affected (0.02 sec)



 --the record is deleted so lets select to make sure

 mysql select DEIT_EVENT_SEQUENCE_ID,DEIT_EVENT_STATUS_CODE,DEIT_EVENT_DATE 
 from DEIT;
 +++-+
 | DEIT_EVENT_SEQUENCE_ID | DEIT_EVENT_STATUS_CODE | DEIT_EVENT_DATE |
 +++-+
 |                      2 | 2                      | 2006-09-05      |
 |                      3 | 3                      | 2006-09-06      |
 +++-+
 2 rows in set (0.00 sec)


 hth

 Martin Gainty
 __
 Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

 Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
 sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
 oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich 
 dem Austausch von Informationen und entfaltet keine rechtliche 
 Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen 
 wir keine Haftung fuer den Inhalt uebernehmen.

 Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
 destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
 l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci 
 est interdite. Ce message sert à l'information seulement et n'aura pas 
 n'importe quel effet légalement obligatoire. Étant donné que les email 
 peuvent facilement être sujets à la manipulation, nous ne pouvons accepter 
 aucune responsabilité pour le contenu fourni.





 From: br...@briandunning.com
 Subject: Best way to purge old records from a huge table?
 Date: Fri, 4 Jun 2010 08:10:07 -0700
 To: mysql@lists.mysql.com

 Hey all -

 I have a table with 12,000,000 records spread over about 6 years. I'm trying 
 to delete all but the last 2 years, but no matter how small of a group I try 
 to delete at a time, it keeps hanging up the server and I eventually have to 
 restart MySQL. The table looks like this:

 `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
 CURRENT_TIMESTAMP,
 `lat` double NOT NULL default '0',
 `lon` double NOT NULL default '0',
 `referer` int(12) NOT NULL default '0',
 PRIMARY KEY (`referer`,`lat`,`lon`),
 KEY `creation` (`creation`,`referer`)

 And the query I've been trying looks like this:

 delete from tablename where `creation`  '2006-04-01 00:00:00'

 ...trying to do the oldest 1 month of records at a time. So am I just trying 
 a really inefficient query? Is there a better way to do this?
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com


 _
 Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox.
 http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_1



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

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

Re: Best way to purge old records from a huge table?

2010-06-04 Thread Shawn Green 

Brian Dunning wrote:

Hey all -

I have a table with 12,000,000 records spread over about 6 years. I'm trying to 
delete all but the last 2 years, but no matter how small of a group I try to 
delete at a time, it keeps hanging up the server and I eventually have to 
restart MySQL. The table looks like this:

  `creation` timestamp NOT NULL default CURRENT_TIMESTAMP on update 
CURRENT_TIMESTAMP,
  `lat` double NOT NULL default '0',
  `lon` double NOT NULL default '0',
  `referer` int(12) NOT NULL default '0',
  PRIMARY KEY  (`referer`,`lat`,`lon`),
  KEY `creation` (`creation`,`referer`)

And the query I've been trying looks like this:

delete from tablename where `creation`  '2006-04-01 00:00:00'

...trying to do the oldest 1 month of records at a time. So am I just trying a 
really inefficient query? Is there a better way to do this?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=sh...@mysql.com



My idea is to create a new table with just the data you want to keep and 
drop the old one. Every batch you delete must update the indexes on the 
existing table. Creating a new,smaller, batch of data with a fresh set 
of indexes  should be much faster than incrementally deflating the 
existing huge set of data.


Once the new table is created, use a RENAME TABLE to swap both table 
names to put the new table into the old one's place and to give the old 
table a name you can work with later.


--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

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

Re: Missing database file names

2010-06-04 Thread Jesse F. Hughes 
(I emailed this to Martin and Jaime rather than to the discussion
group, so I'm re-sending it.)

Martin Gainty mgai...@hotmail.com writes:

 Jesse ..please keep us apprised on your progress..we would like to
 know how fubar the db can be before it becomes 'unrecoverable'

Well, I'm not having much luck so far.

I have a file that I'm certain is recorded.frm.  I also have a file
that I'm almost certain is recorded.MYD.  I stuck an arbitrary .MYI
file in as recorded.MYI, in the hopes that it would be overwritten
with good data.

Unfortunately, here's the result:

r...@pw:/var/lib/mysql/mythconverg# myisamchk -o recorded.MYI
Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted 
to 4294963200
Warning: option 'read_buffer_size': unsigned value 18446744073709551615 
adjusted to 4294967295
Warning: option 'write_buffer_size': unsigned value 18446744073709551615 
adjusted to 4294967295
Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 
adjusted to 4294967295
- recovering (with keycache) MyISAM-table 'recorded.MYI'
Data records: 0
Key 1 - Found wrong stored record at 0
Found link that points at 19054226382932 (outside data file) at 36708
Found link that points at 13556668244051 (outside data file) at 44344
Found link that points at 13673551504478720 (outside data file) at 59948
Found link that points at 5996055336546156 (outside data file) at 59956
Found link that points at 17916077434656 (outside data file) at 64864
Found block with too small length at 69120; Skipped

and so on.

The result is an empty database, of course.

Now, someone mentioned that I need to be sure I have the same versions
of MySQL.  I thought that I had the same versions, since I thought
that I had the same versions of Ubuntu on my working and non-working
machines.  Unfortunately, it looks like the non-working machine had
5.0.67-0ubuntu6, while the working machine has
5.1.30really5.0.75-0ubuntu10.3 (referring here to Ubuntu package
versions, but I think the mysql versions are 5.0.67 and 5.0.75,
resp.).

Perhaps that's the problem?  Or perhaps I'm wrong about the .MYD file
and it isn't really recorded.MYD?

-- 
So how do you go on?  [...] How will you keep moving for the next few
weeks or months until you are known for what you are, the story
becomes huge all over the world, and you have reporters at your
schools asking you, why? -- Another JSH mystery

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

Re: Missing database file names

2010-06-04 Thread Jesse F. Hughes 
Jesse F. Hughes je...@phiwumbda.org writes:

 r...@pw:/var/lib/mysql/mythconverg# myisamchk -o recorded.MYI
 Warning: option 'key_buffer_size': unsigned value 18446744073709551615 
 adjusted to 4294963200
 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 
 adjusted to 4294967295
 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 
 adjusted to 4294967295
 Warning: option 'sort_buffer_size': unsigned value
18446744073709551615 adjusted to 4294967295

Could these problems be that the dead machine was 64 bit and the
working machine 32 bit?  

I'm rebuilding the dead machine now.  When it's got mysql loaded on
it, I'll try to run myisamchk on a 64-bit machine.

-- 
Jesse F. Hughes
Well, if I can get [my proof of FLT accepted], then I hopefully get a
book deal down the road, and maybe I get to go on 'Oprah'.
  James Harris, on the rewards of mathematical endeavours.

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

Prevent user from changing passwords?

2010-06-04 Thread Anthony R. J. Ball 

  I have been looking high and low but am having trouble finding good
  info. I need to try to lock down mysql with strong passwords
  password expiry yadda yadda. I've looked at securich and am not sure
  I want to use it.

  All I really want is to prevent users from be able to change their
  password directly. If I can do that, then strong passwords, password
  expiry etc are all very trivial using some sort of script/webpage
  whatever to change passwords. Is there a way to prevent users from
  being able to change their password other than modifying the code to
  break set password?

-- 
 www.suave.net - Anthony Ball - a...@suave.net
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Excuse me.  Haven't we met somewhere before? 


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