#mysql
24 October 2007
Total 29 pages. You are browsing page 1/29.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Wed Oct 24 00:00:13 2007
--- Day changed Wed Oct 24 2007
00:00 <****> ALTER TABLE `wp_posts` ADD UNIQUE (`t_md5`);
00:00 <****> ERROR 1072 (42000): Key column 't_md5' doesn't exist in table
00:00 <****> setuid: It's a dupe cos the first one is blank, and the second one will be blank
00:00 <****> setuid: update table set `t_md5` = MD5(`post_content`); too
00:00 <****> ah i see :]
00:00 <****> what the hell... I can't add it, because it doesn't exist
00:00 * setuid scratches his head
00:00 <****> me is using the up arrow to say all the things he has already said
00:01 <****> well stop deleting stuff. We don't want to help you do the same thing 12 times
00:01 <****> I'm not deleting anything
00:01 <****> The db is in a pristine, Wordpress default state
00:01 <****> I'd like to add a unique col called t_md5 to it
00:01 <****> sigh.
00:01 <****> :-))
00:01 <****> I can't, because it either says it exists (it doesn't), or claims it doesn't exist, and can't add it.
00:02 <****> anyway, why do you want to have md5's unique ?
00:02 <****> alter table wp_posts add column t_md5 varchar(32) not null unique;
00:02 <****> alter table wp_posts add column t_md5 varchar(32) not null unique;
00:02 <****> ERROR 1062 (23000): Duplicate entry '' for key 2
00:02 <****> ah . nevermind .. i got it
00:02 <****> setuid: update table set `t_md5` = MD5(`post_content`); too
00:02 <****> update table set `t_md5` = MD5(`post_content`);
00:02 <****> ERROR 1064 (42000):
00:02 <****> setuid: that's because you have data in the table!
00:02 <****> duh!
00:02 <****> geez
00:02 <****> I can't drop the existing data
00:02 <****> setuid: Start from the beginning: You want to add the column first. chadmaynards statement WITHOUT the unique
00:02 <****> that _must_ stay there
00:03 <****> setuid: Then do the update
00:03 <****> I have 90 rows which must not be removed
00:03 * setuid smashes his head on the desk repeatedly
00:03 <****> setuid: Then do the alter ending with unique(t_md5)
00:03 <****> Fuck it, I'll just insert with all the dupes, and manually delete the ddamn things
00:03 <****> does mysql support views
00:03 <****> ?
00:04 <****> nevele: yup
00:04 <****> thanks!
00:04 * setuid grumbles about trainwrecks in mysql
00:05 <****> setuid: you are the only one who doesn't understand this here
00:05 <****> You would think a unique index would prevent dupes, but I guess not
00:05 <****> setuid: you usually build a schema and then insert data.
00:05 * Gushie bangs his head on the wall
00:06 <****> chadmaynard, The schema is already built, and I can't readily modify that... I'd rather not touch Wordpress core code.
00:06 <****> :-))))
00:06 <****> setuid: a unique index will, but you can't index am entire freaking text column
00:06 <****> setuid: well delete the rows YOU inserted in
00:06 <****> setuid: i think you understand it wrong way
00:06 <****> setuid: Is this a one off thing, or a regular import?
00:06 <****> you can put them back after you put the unique constraint it
00:07 <****> in**
00:07 <****> Gushie, One-off
00:07 <****> setuid: the unique means there is not possible to have two different rows with the same value in same field
00:07 <****> setuid: If it's a one off, I suggest you just do it manually it'll be easier for all concerned!
00:07 <****> longin, Which doesn't work with mysql, if you're using a unique index with auto_increment
00:08 <****> setuid: why ?
00:08 <****> why it shouldnt work
00:08 <****> longin, Because a unique id would be a unique row, hence, allow dupes
00:08 <****> 1,test,12345
00:08 <****> 2,test,12345
00:08 <****> id,title,string
00:09 <****> then set unique for some other column
00:09 <****> ie title
00:09 <****> for example, would allow dupes, even though test,12345 is duplicated
00:09 <****> I tried that, it doesn't allow it
00:09 <****> And Wordpress (blindly) reuses post_title for image uploads as well as body content
00:09 <****> because you already have dupes in there, isnt you
00:09 <****> setuid: be very quiet
00:09 <****> which column type shoul i use to store phone numbers?
00:09 * setuid shuts up
00:09 <****> ionico: depends on format i guess
00:09 <****> setuid: create table wp_post_back like wp_posts
00:10 <****> just numbers, without spaces or -
00:10 <****> ionico: ie if u want to have international numbers you would need to have +
00:10 <****> i would use int .. why not ?
00:10 <****> i wont
00:11 <****> just local numbers
00:11 <****> longin: cos you'd lose leading zeros, that's why...
00:11 <****> setuid: did you do it?
00:11 <****> chadmaynard, Yes
00:11 <****> Gushie: theres an option zerofill isnt it ?
00:11 <****> Copied the table to a new table
00:11 <****> oh, isee
00:11 <****> setuid: INSERT INTO wp_posts_back SELECT * FROM wp_posts;
00:12 <****> setuid: did you do it?
00:12 <****> we dont have numbers with leading zeros ...
00:12 <****> Doesn't like that, let me see why
00:12 <****> Invalid syntax of some sort
00:12 <****> setuid: what's the error?
00:12 <****> oh i said wp_post_back
00:12 <****> ah, wp_post_back, not wp_posts_back... (your typo, no worries)
00:13 <****> setuid: INSERT INTO wp_post_back SELECT * FROM wp_posts;
00:13 <****> yep
00:13 <****> Query OK, 410 rows affected (0.39 sec)
00:13 <****> ok. truncate table wp_posts;
00:13 <****> Yep
00:13 <****> setuid: alter table wp_posts add column t_md5 varchar(32) not null unique;
00:14 <****> Done;
Total 29 pages. You are browsing page 1/29.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last