Hi, Update: I just did a grep:
grep 375707 Query c01*.log of the log file and did a grep of VBS/LoveLet-G on the out put file and got 123 entries, what I expected. But, the dump shows 111,009 in the 'n' field. This is starting to look like a bug in mysql 3.23.49? Any ideas? Thanks, Cheers, Douglas mysql Ver 11.16 Distrib 3.23.49, for dec-osf5.1 (alphaev6) -----Original Message----- From: Douglas B. Jones [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 10:22 AM To: 'Paul DuBois'; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Douglas B Jones Subject: RE: automatically incrementing an int value Hi, I just tried the below: create table virus ( n int auto_increment not null, name char(128) not null, primary key(n), unique(name(100)) ); with a data file that has 122,111 sql commands like: replace into virus values(NULL,"VBS/LoveLet-E"); replace into virus values(NULL,"VBS/LoveLet-E"); replace into virus values(NULL,"VBS/LoveLet-E"); replace into virus values(NULL,"VBS/LoveLet-G"); replace into virus values(NULL,"WM97/Myna-C"); replace into virus values(NULL,"VBS/LoveLet-G"); replace into virus values(NULL,"WM97/Myna-C"); replace into virus values(NULL,"VBS/LoveLet-G"); replace into virus values(NULL,"VBS/LoveLet-G"); replace into virus values(NULL,"W32/Sircam-A"); Now when I do a: grep VBS/LoveLet-G sqlfile | wc I get: 123 492 6027 123 entries for VBS/LoveLet-G in the file. When I do a mysqldump of the data file and just grep for VBS: mysqldump virus|grep VBS INSERT INTO virus VALUES (3,'VBS/LoveLet-E'); INSERT INTO virus VALUES (111009,'VBS/LoveLet-G'); INSERT INTO virus VALUES (55841,'VBS/Stages-A'); INSERT INTO virus VALUES (121521,'VBS/LoveLet-AS'); INSERT INTO virus VALUES (1208,'VBS/SST-A'); INSERT INTO virus VALUES (85602,'VBS/VBSWG-X'); INSERT INTO virus VALUES (1215,'VBS/VBSWG-Z'); INSERT INTO virus VALUES (5846,'VBS/LoveLet-CL'); INSERT INTO virus VALUES (5996,'VBS/VBSWG-Fam'); INSERT INTO virus VALUES (83835,'VBS/Haptime-Fam'); INSERT INTO virus VALUES (55356,'VBS/LoveLet-F'); INSERT INTO virus VALUES (55546,'VBS/FreeLinks'); INSERT INTO virus VALUES (91207,'VBS/Kakworm'); INSERT INTO virus VALUES (117623,'VBS/Redlof-A'); As you can see, the numbers (n field) are way to high? Is this a bug in mysql or n the sql? Even if I say unique(name) instead of unique(name(100)), I get the same results. Please note that I have tried destroying the table as well as the db, still get the same results. Any ideas? Thanks, Cheers, Douglas -----Original Message----- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Monday, March 10, 2003 11:14 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Douglas B Jones Subject: RE: automatically incrementing an int value At 11:09 -0500 3/10/03, Douglas B. Jones wrote: >Hi, > > >Great! This works, I did not use the 'null' and that is >where I had a problem. One other questions: is this atomic? >If I have several processes trying to do this at one time, >will each one correctly update the table (assuming they have >the same 'name' value. Yes. > Also, in the part: unique (name(100)), >what does the 100 do here? Is that saying the first 100 chars >are considered unique? Exactly. > >Thanks, >Cheers, >Douglas > >> -----Original Message----- >> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >> Sent: Monday, March 10, 2003 10:36 AM >> To: Douglas B Jones >> Cc: [EMAIL PROTECTED] >> Subject: Re: automatically incrementing an int value >> >> >> >> As I read the manual, the REPLACE command will do what you want. >> >> Make the name field UNIQUE, and the number field AUTO_INCREMENT NOT NULL. >> Replace dos a delete-if-present, insert. The insert generates a new ID. >> >> See test below, and note two rows affected by second replace. >> >> mysql> create table test (a int auto_increment not null, name tinytext not >> null, primary key (a), unique (name(100))) ; >> Query OK, 0 rows affected (0.02 sec) >> >> mysql> replace into test values (null, "hello") ; >> Query OK, 1 row affected (0.01 sec) >> >> mysql> select * from test ; >> +---+-------+ >> | a | name | >> +---+-------+ >> | 1 | hello | >> +---+-------+ >> 1 row in set (0.01 sec) >> >> mysql> replace into test values (null, "hello") ; >> Query OK, 2 rows affected (0.02 sec) >> >> mysql> select * from test ; >> +---+-------+ >> | a | name | >> +---+-------+ >> | 2 | hello | >> +---+-------+ > > 1 row in set (0.00 sec) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
