Docstoc

2009-09-12

Document Sample
2009-09-12 Powered By Docstoc
					2009-09-12 00:00:01 pinholecamera apologies
2009-09-12 00:00:48 pinholecamera 92 degrees out at the moment
2009-09-12 00:00:57 pinholecamera a little balmy
2009-09-12 00:01:46 zmen01 FurnaceBoy: any way to fix that for me so i
can wirte to there?
2009-09-12 00:02:43 FurnaceBoy zmen01: hm you could try    sudo chgrp
mysql /home/user
2009-09-12 00:03:06 FurnaceBoy zmen01: and     sudo chmod 770 /home/user
2009-09-12 00:03:31 zmen01 yep thanks
2009-09-12 00:03:42 FurnaceBoy zmen01: ugh, better:     sudo chmod g+rx
/home/user
2009-09-12 00:03:50 FurnaceBoy zmen01: ugh, better:     sudo chmod g+rwx
/home/user
2009-09-12 00:04:02 FurnaceBoy FurnaceBoy is tired.
2009-09-12 00:04:36 zmen01 yeaa finally
2009-09-12 00:04:42 zmen01 now lets try to write some SP
2009-09-12 00:04:45 zmen01 is that hard?
2009-09-12 00:05:00 FurnaceBoy with reference to the manual, not really.
2009-09-12 00:05:05 gtowey procedure sql
2009-09-12 00:05:29 gtowey zmen01 ^^ there a tutorial
2009-09-12 00:05:34 zmen01 txx
2009-09-12 00:05:37 Evet can i assign a field of a table to a field of
another table?
2009-09-12 00:05:57 FurnaceBoy Evet: sure, if you can relate them with a
join
2009-09-12 00:05:58 gtowey Evet: you can update with a join
2009-09-12 00:06:17 Evet FurnaceBoy, gtowey. is it have to be in same
order?
2009-09-12 00:06:29 FurnaceBoy Evet: you need a way to relate them. such
as id.
2009-09-12 00:06:37 FurnaceBoy Evet: relate rows*
2009-09-12 00:07:09 Evet FurnaceBoy, thanks. im looking for it
2009-09-12 00:07:48 FurnaceBoy Evet: simple ex.    UPDATE t2 JOIN t1
USING(id) SET t2.col1 = t1.col2;
2009-09-12 00:09:12 Evet FurnaceBoy, it looks there is nothing about
join, in phpmyadmin. so i have to use ssh
2009-09-12 00:09:57 FurnaceBoy Evet: we don't recommend phpmyadmin
2009-09-12 00:10:11 FurnaceBoy Evet: or support it. CLI ftw
2009-09-12 00:10:44 Evet FurnaceBoy, im using phpmyadmin as tutorial,
atm. it shows sql syntax etc.
2009-09-12 00:10:55 Evet im not so good to work with console
2009-09-12 00:11:10 FurnaceBoy Evet: which is why you should start doing
so immediately
2009-09-12 00:11:39 Evet hrm
2009-09-12 00:11:41 Evet you're right
2009-09-12 00:13:44 Evet FurnaceBoy, can't i use join with myisam?
2009-09-12 00:14:13 thumbs Evet: you can.
2009-09-12 00:15:02 FurnaceBoy Evet: what thumbs said, with bells on.
2009-09-12 00:22:15 thumbs the simplest questions are the best.
2009-09-12 00:22:19 thumbs Can I do that? Yes.
2009-09-12 00:24:22 zmen01 if i have 35 id's that need to be updated in a
db, how should i use the update statement?
2009-09-12 00:27:30 aboyz say i got a table and inside that table i got a
column named: price = $4.99. how do i update all the price to $ 2.99?
2009-09-12 00:27:57 LORBRITO Hi, i kknow the sintax for insert data of
one table in another, is like this insert into table1 (columns)
values(select * from table2)
2009-09-12 00:28:14 LORBRITO but the thing is that both thable has the
same numbers of columns
2009-09-12 00:28:33 LORBRITO i have to put all the columns or maybe is
another sintax?
2009-09-12 00:29:46 fantom LORBRITO: Leave out the VALUES() keyword
2009-09-12 00:30:05 thumbs no.
2009-09-12 00:30:15 LORBRITO i tried and does not work
2009-09-12 00:30:16 thumbs INSERT INTO tbl SELECT * FROM tbl2
2009-09-12 00:30:51 FurnaceBoy zmen01: not enough information.
2009-09-12 00:31:08 LORBRITO Yes thanks
2009-09-12 00:31:10 FurnaceBoy zmen01: a good start is to write a full
statement in English of what yuo want to happen
2009-09-12 00:31:11 thumbs fantom: that was an erronous response.
2009-09-12 00:31:11 LORBRITO FurnaceBoy
2009-09-12 00:31:26 fantom I was going to say, nobody ever agrees with
me...
2009-09-12 00:31:34 FurnaceBoy fantom: you're right. nobody ever does.
2009-09-12 00:31:36 Evet FurnaceBoy, i join tables by a field. but new
field filled up with "0"s
2009-09-12 00:31:43 zmen01 FurnaceBoy: select * from table where id =
'111' and '222' and 229' etc
2009-09-12 00:31:50 LORBRITO well, thanks thumbs
2009-09-12 00:31:55 LORBRITO that works fin
2009-09-12 00:31:59 LORBRITO fine
2009-09-12 00:32:03 thumbs LORBRITO: yeah.
2009-09-12 00:32:04 zmen01 FurnaceBoy: a lot values , like 229, 224,
3838, 2828 etc
2009-09-12 00:32:04 FurnaceBoy zmen01: not good enough. write down the
whole task in english first.
2009-09-12 00:32:17 FurnaceBoy thumbs++
2009-09-12 00:32:35 zmen01 FurnaceBoy: ok, i like to update a lot fields
to = 'YES' where id = a lot different id's
2009-09-12 00:32:41 FurnaceBoy zmen01: sorry that was meant for Evet
2009-09-12 00:32:45 fantom zmen01: WHERE id IN ('111','222','229')
2009-09-12 00:32:48 thumbs zmen01: WHERE id IN (<list>)
2009-09-12 00:32:59 thumbs fantom: how dare you type faster than me!
2009-09-12 00:33:02 FurnaceBoy zmen01: fantom is right!
2009-09-12 00:33:33 Evet FurnaceBoy, UPDATE `table1` JOIN `table2`
USING(`id`) SET `table1`.`dept_id` = `table2`.`dept_id`; the command i
typed
2009-09-12 00:33:46 FurnaceBoy Evet: ok should work, eh?
2009-09-12 00:33:55 Evet now dept_id field in table 2
2009-09-12 00:34:02 Evet has 45000 zeros
2009-09-12 00:34:10 thumbs Evet: it looks syntaxly correct.
2009-09-12 00:34:16 zmen01 thanks guys
2009-09-12 00:34:18 thumbs Evet: run a SELECT before you run UPDATE
2009-09-12 00:34:20 FurnaceBoy Evet: rows in t1 will have their dept_id
set to the dept_id of the corresponding row by id in t2
2009-09-12 00:34:29 FurnaceBoy thumbs: pishposh
2009-09-12 00:34:40 thumbs Evet: if you run SELECT first, you'll see
what's gonna happen.
2009-09-12 00:34:48 thumbs FurnaceBoy: I favour caution.
2009-09-12 00:34:55 FurnaceBoy thumbs: i know. i'm just teasing.
2009-09-12 00:35:04 FurnaceBoy thumbs: i did some really tedious updates
today. got one wrong.
2009-09-12 00:35:15 thumbs FurnaceBoy: boo.
2009-09-12 00:35:23 FurnaceBoy thumbs: gosh i hate manually cleaning up
after application bugs.
2009-09-12 00:35:31 FurnaceBoy thumbs: especially when i made the bug
too.
2009-09-12 00:35:36 thumbs FurnaceBoy: once I had DELETE FROM cust_table,
uncommented, in MSSQL
2009-09-12 00:35:51 thumbs and I hit F5 in that window, by mistake.
2009-09-12 00:36:05 thumbs *boom*
2009-09-12 00:36:09 FurnaceBoy thumbs: I do 3 things when updating. dump
first. do it in a txn. and SELECT up the ying yang.
2009-09-12 00:36:23 FurnaceBoy thumbs: this time i had to fall back to
dump. but that's the first time on this system
2009-09-12 00:36:33 FurnaceBoy thumbs: nasty bloody query as they all are
2009-09-12 00:36:38 thumbs yeah
2009-09-12 00:36:50 thumbs I run all my updates on my slaves, actually.
2009-09-12 00:37:03 thumbs so I only destroy the copy.
2009-09-12 00:37:12 FurnaceBoy thumbs: i can't afford slaves. the dishes
pile up, I tell you.
2009-09-12 00:37:25 thumbs I have six slaves in the house!
2009-09-12 00:37:27 fronix Im trying to show how many comments users have
done but this code string groups the users but i want to show only 1 user
so i can echo out it for 1 user string: http://pastebin.org/17314
2009-09-12 00:37:39 FurnaceBoy thumbs: no wonder you work so hard to pay
for it all
2009-09-12 00:37:55 thumbs yeah, and they want 19$/hour too
2009-09-12 00:40:46 Evet well, i have filled the base table with 0s too
2009-09-12 00:41:25 Evet but, the query does work fine, thanks :)
2009-09-12 00:44:33 FurnaceBoy Evet: yw. happy to corrupt your data any
time
2009-09-12 00:45:35 FurnaceBoy thumbs: see doing it on the slave might
not have helped here. I probably would have run the wrong query on the
master before i realised the smegup.
2009-09-12 00:45:47 FurnaceBoy thumbs: just as i committed before i
realised the smegup.
2009-09-12 00:45:52 FurnaceBoy thumbs: dumps ftw ;-
2009-09-12 00:46:05 FurnaceBoy thumbs: it was a subtle error
2009-09-12 00:47:38 fronix no one?
2009-09-12 00:48:46 djahandarie thumbs, I'd sign up for that
2009-09-12 00:48:52 djahandarie More than my current job pays. -_-
2009-09-12 00:49:13 Evet do i need to log out to import a dump file?
2009-09-12 00:49:45 Evet it seems console doesnt distinguish the file
path
2009-09-12 00:50:26 CorticalStack djahandarie: mysql> source backup.sql
2009-09-12 00:50:59 CorticalStack Evet: mysql> source backup.sql
2009-09-12 00:51:02 fronix can anyone see what im typing?
2009-09-12 00:51:05 CorticalStack djahandarie: sorry
2009-09-12 00:51:26 fronix HELLO ?
2009-09-12 00:51:56 Evet CorticalStack, thanks for it
2009-09-12 00:52:41 CorticalStack yw
2009-09-12 00:53:31 fronix wtf
2009-09-12 00:54:00 fronix Im trying to show how many comments users have
done but this code string groups the users but i want to show only 1 user
so i can echo out it for 1 user string: http://pastebin.org/17314
2009-09-12 00:54:02 CorticalStack anybody know why we were gaslighting
fronix?
2009-09-12 00:54:09 FurnaceBoy CorticalStack: I wasn't.
2009-09-12 00:54:16 FurnaceBoy CorticalStack: I just opened his link.
2009-09-12 00:54:22 FurnaceBoy CorticalStack: I'm not sure he's paying
enough
2009-09-12 00:54:41 fronix lol i just need some simple help
2009-09-12 00:54:46 CorticalStack FurnaceBoy: you teach him about left
join
2009-09-12 00:54:47 FurnaceBoy fronix: remove the GROUP BY and add a
WHERE condition
2009-09-12 00:55:14 fronix wont work
2009-09-12 00:55:16 fronix Mixing of GROUP columns
(MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no
GROUP BY clause
2009-09-12 00:55:34 FurnaceBoy fronix: that is quite true. so don't mix
them.
2009-09-12 00:55:52 fronix where is the mix?
2009-09-12 00:56:40 FurnaceBoy remove t.medlem_id as you already know
it's a constant.
2009-09-12 00:57:05 FurnaceBoy I assume your where is WHERE t.medlem_id =
X
2009-09-12 00:57:57 djahandarie CorticalStack... lol @ gaslighting
2009-09-12 00:58:00 FurnaceBoy fronix: correct?
2009-09-12 00:58:17 FurnaceBoy djahandarie: i think thumbs operates some
kind of sweatshop from his apartment
2009-09-12 00:58:18 fronix no
2009-09-12 00:58:23 FurnaceBoy fronix: ok, so explain again
2009-09-12 00:58:44 FurnaceBoy fronix: use the actual column names in
your explanation
2009-09-12 00:59:16 FurnaceBoy fronix: I'm the night shift. if you want
real help, ask for chadmaynard or seekwill
2009-09-12 00:59:36 djahandarie FurnaceBoy, I bet he passes around cards
with queries scawrled on them and forces innocent children to compute
them by hand
2009-09-12 00:59:45 FurnaceBoy djahandarie: I heard that too!
2009-09-12 00:59:50 djahandarie THE HORROR
2009-09-12 01:00:59 fronix Ok i have one column that is the id of the
actual post thats the t.id and then i have the medlem_id thats the id of
the user that posted so i want to take all the medlem_id from the X
person and count all the posts he has done
2009-09-12 01:01:33 CorticalStack FurnaceBoy: wow, pastebin just did
popup ads and browser maximization and used up all my system resources.
2009-09-12 01:01:40 iratik Alright mysql gods .. I can't take it anymore
.. This makes no sense... no sense at all.. Why am i getting a
duplicate entry for a number that isn't even in the source table ! ?
http://pastie.org/614232
2009-09-12 01:02:00 FurnaceBoy fronix: SELECT COUNT(*) FROM
g_forum_inlagg WHERE medlem_id = X
2009-09-12 01:02:14 FurnaceBoy CorticalStack: same here
2009-09-12 01:02:19 CorticalStack FurnaceBoy: I'm probably going to have
to reboot. I've never seen pastebin.org do that before
2009-09-12 01:02:31 FurnaceBoy CorticalStack: I think we might need to
deprecate those shits
2009-09-12 01:02:38 CorticalStack FurnaceBoy: I had to KILL ff from a
terminal
2009-09-12 01:02:47 FurnaceBoy CorticalStack: didn't kill Opera 10
2009-09-12 01:03:11 erkules moin someone used to big tables? I mean a
500.000.000 rows -> 1063GB table? Wonder if that would ever "fit" on a
dl380G5 *and* perform.
2009-09-12 01:03:22 CorticalStack FurnaceBoy: that really sucks. I'm not
going back there
2009-09-12 01:03:24 FurnaceBoy erkules: of course it would. now define
'perform'.
2009-09-12 01:03:40 fronix FurnaceBoy: what is the X for?
2009-09-12 01:03:43 erkules hehe good! 2700 Queries/s
2009-09-12 01:03:48 FurnaceBoy fronix: the id you want the count for.
2009-09-12 01:03:53 erkules FurnaceBoy: ^^
2009-09-12 01:03:56 FurnaceBoy erkules: what kind of queries.
2009-09-12 01:04:01 FurnaceBoy erkules: ACID?
2009-09-12 01:04:04 FurnaceBoy erkules: r/w?
2009-09-12 01:04:22 fronix Ok i need to do a php code there then
2009-09-12 01:04:34 FurnaceBoy erkules: that rate is trivial to achieve
in MyISAM
2009-09-12 01:04:49 FurnaceBoy erkules: other factors permitting...
2009-09-12 01:05:11 FurnaceBoy fronix: yeah, that's the parameter
2009-09-12 01:05:33 erkules InnoDB ./. no trx ./. 30% Update/Insert only
index-lookups
2009-09-12 01:05:38 erkules FurnaceBoy: ^^
2009-09-12 01:05:44 iratik Oh No!@
2009-09-12 01:05:44 FurnaceBoy erkules: so benchmark it
2009-09-12 01:05:54 FurnaceBoy erkules: but 2700 durable updates per
second is not so easy
2009-09-12 01:05:55 KristjanS SQL QUERY FAILURE: categoryid_path FROM
xcart_categories WHERE categoryid='60'
2009-09-12 01:05:57 KristjanS hmm?
2009-09-12 01:06:00 fronix hmm i tryed to do http://pastebin.org/17324
but its still giving me that dont mix COUNT() things
2009-09-12 01:06:27 FurnaceBoy fronix: did you try the query I gave you?
2009-09-12 01:06:34 fronix yes
2009-09-12 01:07:05 FurnaceBoy fronix: that is not the query I gave you.
2009-09-12 01:08:07 iratik this is why i suck
2009-09-12 01:08:16 Maleko why is this query not working
2009-09-12 01:08:19 Maleko UPDATE wp_posts SET post_text =
REPLACE(post_text, 'domain.com', 'domain.net')
2009-09-12 01:08:29 FurnaceBoy Maleko: you tell us
2009-09-12 01:08:39 fronix no but the query you gave me doesnt give any
result at all because the query doesnt know what member its taking
2009-09-12 01:08:48 iratik mid_leads had a 20 character long primary key
field.. next_leads had a 10 character long primary key field.. using
telephone as primary key in both. The extensions werre chopped off when
they entered the next_leads table creating a duplicate situation
2009-09-12 01:08:54 FurnaceBoy fronix: the form of that query is correct,
though, wouldn't you say?
2009-09-12 01:09:07 Evet source file.sql; gives an output for each row :(
2009-09-12 01:09:20 Armen52 hey folks, i tried to change mysql on my os x
server to use innoDB by default but for some reason the service wont
start up now, i get these errors on startup:
http://pastebin.com/d5bb22574 here is the contents of my my.cnf file:
http://pastebin.com/dd2f9551    i appreciate any help in getting this
working again, thanks
2009-09-12 01:09:42 Evet why people want to use innodb?
2009-09-12 01:09:52 Armen52 Evet: required for Confluence
2009-09-12 01:09:58 FurnaceBoy Evet: it is a very good idea, is why
2009-09-12 01:10:40 FurnaceBoy !tell Evet about innodb
2009-09-12 01:10:45 Maleko i think i got the syntax correct but yet it
always fail to replace the string
2009-09-12 01:10:57 FurnaceBoy Maleko: do a SELECT with the same
expression
2009-09-12 01:12:03 Evet innodb as default? hmm..
2009-09-12 01:12:21 Armen52 FurnaceBoy: any chance you know whats wrong
by looking at that startup log?
2009-09-12 01:12:26 FurnaceBoy Evet: it's a good default. people need to
start thinking of myisam as the unusual choice.
2009-09-12 01:12:36 FurnaceBoy Armen52: let's see
2009-09-12 01:13:08 FurnaceBoy Armen52: do you have anything stored in
InnoDB at the moment?
2009-09-12 01:13:49 FurnaceBoy Armen52: how much RAM Is on your machine
(also)
2009-09-12 01:14:05 CorticalStack Armen52: "InnoDB: Error: auto-extending
data file /var/mysql/ibdata1 is of a different size InnoDB: 640 pages
(rounded down to MB) than specified in the .cnf file"
2009-09-12 01:14:23 Armen52 FurnaceBoy: no, what happened is i set things
up for JIRA (another atlassian product) which didnt say anything about
requiring innoDB, then i got to confluence and they did require it. i
tried to follow the guide here
(http://confluence.atlassian.com/display/DOC/Database+Setup+For+MySQL)
but the stupid things only written for the windows wizard so i found some
sample innoDB my.cnf files online and used those to set mine up. but
things are failing now as
2009-09-12 01:14:23 Armen52 you can see.
2009-09-12 01:14:29 Armen52 12GB of ram on here
2009-09-12 01:14:30 FurnaceBoy CorticalStack: yes it's clear. I was going
to suggest he zaps data and logs
2009-09-12 01:14:48 CorticalStack FurnaceBoy: I believe that is called
for
2009-09-12 01:14:52 FurnaceBoy CorticalStack: assuming he's got nothing
in it
2009-09-12 01:15:09 CorticalStack FurnaceBoy: lot of zeros. ;^)
2009-09-12 01:15:25 Armen52 only DB data in there was in there before i
tried to change to innoDB
2009-09-12 01:15:39 FurnaceBoy Armen52: but you aren't 100% sure it's all
MyISAM
2009-09-12 01:15:52 CorticalStack FurnaceBoy: perhaps move it instead of
delete it
2009-09-12 01:16:13 FurnaceBoy CorticalStack: yers, or... we roll back
conf to match his ibdata size?
2009-09-12 01:16:20 Armen52 FurnaceBoy: how would i be sure?
2009-09-12 01:16:25 FurnaceBoy CorticalStack: i tihnk gtowey walked
someone through this , yesterday
2009-09-12 01:16:32 Armen52 there is a jiradb file within /var/mysql
2009-09-12 01:16:43 Armen52 doesnt that mean its a MyISAM?
2009-09-12 01:16:44 CorticalStack FurnaceBoy: I think he said skip the
size declaration in my.cnf
2009-09-12 01:16:47 FurnaceBoy Armen52: yeah that's a schema directory.
does it have MYD and MYI in it?
2009-09-12 01:17:01 FurnaceBoy CorticalStack: hmm...
2009-09-12 01:17:20 CorticalStack FurnaceBoy: skip the size, remove the
log, fire it up
2009-09-12 01:17:33 Armen52 FurnaceBoy: i'm not able to cd into jiradb?
2009-09-12 01:17:50 Armen52 even though it should be a folder and i tried
it with sudo, doesnt work
2009-09-12 01:17:52 FurnaceBoy Armen52: as an ordinary user no. in fact
you shouldn't be allowed in the data dir :)
2009-09-12 01:18:09 Armen52 yes but sudo should work no?
2009-09-12 01:18:09 FurnaceBoy Armen52: maybe it's not a directory. pb an
ls -l ?
2009-09-12 01:18:53 Armen52 http://pastebin.com/d745a9302
2009-09-12 01:19:30 FurnaceBoy Armen52: I think CorticalStack's on the
right track. Those look exactly like data and log file default sizes.
2009-09-12 01:19:53 Armen52 FurnaceBoy: ok, sorry but can you recap what
i should do next?
2009-09-12 01:20:02 FurnaceBoy Armen52: you can try commenting out
innodb_data_file_path and innodb_log_buffer_size
2009-09-12 01:20:13 FurnaceBoy then, if it starts, you can see if you
have anything stored in innodb
2009-09-12 01:20:46 Armen52 log buffer size or log file size?
2009-09-12 01:20:47 Naktibalda wsod
2009-09-12 01:20:55 FurnaceBoy Armen52: what i pasted
2009-09-12 01:21:04 FurnaceBoy Armen52: oh
2009-09-12 01:21:14 FurnaceBoy Armen52: no, you're right:
innodb_log_file_size]
2009-09-12 01:21:27 Armen52 FurnaceBoy: k
2009-09-12 01:21:39 FurnaceBoy Armen52: that would be another startup
gotcha, as the new config you inserted doesn't match existing file.
2009-09-12 01:21:55 FurnaceBoy Armen52: so with those commented out it
may just start up normally
2009-09-12 01:22:11 Armen52 FurnaceBoy: now what? do i remove those three
files or just try to save and startup?
2009-09-12 01:22:19 FurnaceBoy Armen52: leave all files alone for now
2009-09-12 01:22:29 FurnaceBoy Armen52: just edit conf and see if it
starts ok
2009-09-12 01:22:58 Armen52 FurnaceBoy: i think it started? check this
out: http://pastebin.com/d25dca75b
2009-09-12 01:23:12 FurnaceBoy Armen52: i don't need to. can you connect
to it? :)
2009-09-12 01:23:24 FurnaceBoy Armen52: yes looks good
2009-09-12 01:23:37 Armen52 to any db? or an innodb one? i havent set
things up for confluence quite yet but i can test if the jira connections
still work
2009-09-12 01:23:40 FurnaceBoy Armen52: now do a SHOW TABLE STATUS and
see if any are innodb
2009-09-12 01:23:48 Armen52 ok let me start up jira and see
2009-09-12 01:23:51 FurnaceBoy no need
2009-09-12 01:23:55 FurnaceBoy just do this in mysql as root user
2009-09-12 01:24:06 CorticalStack Armen52: or 'show variables like
'have_innodb';'
2009-09-12 01:24:19 FurnaceBoy CorticalStack: i want to see if he has
anything stored in innodb as well
2009-09-12 01:24:24 CorticalStack ok
2009-09-12 01:24:48 FurnaceBoy CorticalStack: what would it say in SHOW
TABLE STATUS if innodb *were* disabled?
2009-09-12 01:25:01 FurnaceBoy FurnaceBoy oughta know
2009-09-12 01:25:08 FurnaceBoy FurnaceBoy doesn't
2009-09-12 01:25:09 Armen52 FurnaceBoy: which db should i use?
2009-09-12 01:25:20 FurnaceBoy Armen52: try CorticalStack's command as
well
2009-09-12 01:25:30 FurnaceBoy Armen52: that will confirm innodb is A-ok
2009-09-12 01:25:57 Armen52 FurnaceBoy: CorticalStack's command came back
with a table, have_innodb=YES
2009-09-12 01:26:06 CorticalStack FurnaceBoy: I should set up some fail
cases with innodb on my dev box and see what fixes what in what order
2009-09-12 01:26:29 CorticalStack FurnaceBoy: diddle the my.cnf settings,
comment them out, etc
2009-09-12 01:26:29 FurnaceBoy Armen52: ok. it would be good to know
whether your existing schemata have anything stored in innodb.
2009-09-12 01:26:38 FurnaceBoy Armen52: so you'd need to SHOW TABLE
STATUS on them all, or query INFORMATION_SCHEMA
2009-09-12 01:26:40 FurnaceBoy is
2009-09-12 01:29:07 CorticalStack FurnaceBoy: that pastebin problem
earlier? I think we were directed to pastebin.org, not pastebin.com.
2009-09-12 01:29:14 FurnaceBoy CorticalStack: ouch
2009-09-12 01:29:20 FurnaceBoy pb
2009-09-12 01:29:28 FurnaceBoy ^^ safe i think :)
2009-09-12 01:29:38 CorticalStack I can't scroll back and check the url,c
an you?
2009-09-12 01:29:46 Armen52 FurnaceBoy: here's what i came up with:
http://pastebin.com/d7c2a931a
2009-09-12 01:30:21 Armen52 FurnaceBoy: dont see innoDB in there anywhere
2009-09-12 01:31:05 FurnaceBoy Armen52: right
2009-09-12 01:31:18 Armen52 FurnaceBoy: i think things are working well
again but any chance you could explain what i did wrong so i can avoid it
in the future?
2009-09-12 01:31:24 CorticalStack Armen52: so you tried to make innodb
default and then restarted mysqld? no alter table?
2009-09-12 01:31:34 FurnaceBoy Armen52: so, stop srvr, move the ibdata*
and iblog* files out of the data dir, uncomment those lines, and start
srvr.
2009-09-12 01:31:38 Armen52 CorticalStack: no i havent run any alter
table commands
2009-09-12 01:31:49 FurnaceBoy Armen52: it would work fine from NOW, but
I believe in having conf reflect explicitly the disk config
2009-09-12 01:32:07 CorticalStack Armen52: mysqld just wrote those files
when the error happened
2009-09-12 01:32:30 FurnaceBoy Armen52: what you 'did wrong' was to add
non-default InnoDB config. Previously the ibdata/iblog files were created
with default sizes. InnoDB won't start if they mismatch my.cnf
2009-09-12 01:32:48 FurnaceBoy CorticalStack: he added innodb conf to
my.cnf, i think.
2009-09-12 01:32:53 FurnaceBoy Armen52: my theory ok?
2009-09-12 01:33:08 Armen52 FurnaceBoy: i see, so if i wanted to use my
new values i'd need to remove the existing files, assuming of course they
contained no data and it was safe to remove them
2009-09-12 01:33:13 FurnaceBoy Armen52: yes.
2009-09-12 01:33:18 FurnaceBoy Armen52: otherwise you could dump/load.
2009-09-12 01:33:26 Armen52 ok thanks, let me try uncommenting and see if
things come up again
2009-09-12 01:33:38 FurnaceBoy Armen52: they won't... do all steps I
wrote
2009-09-12 01:33:59 FurnaceBoy stop/move/uncomment/start
2009-09-12 01:33:59 Armen52 ah right, sorry forgot that
2009-09-12 01:35:52 CorticalStack Armen52: when things have settled down,
look to running the tuning script.
2009-09-12 01:36:01 CorticalStack !tell Armen52 about tuning primer
2009-09-12 01:38:02 Armen52 FurnaceBoy: here
2009-09-12 01:38:11 Armen52 FurnaceBoy: heres what i got, i think its
good, http://pastebin.com/d1f32f39c
2009-09-12 01:38:11 FurnaceBoy Armen52: ys
2009-09-12 01:38:30 FurnaceBoy Armen52: perfect
2009-09-12 01:38:36 blcknight For some reason, Enterprise monitor is
effing up on me. I have 2 VM's in enterprise monitor, and whenever I
start the agent on one, it finds it, then I start the second, and it sort
of takes over for the first one in EM
2009-09-12 01:38:50 blcknight they both have unique uuid's and there's no
ip conflict or anything
2009-09-12 01:39:06 blcknight any ideas?
2009-09-12 01:39:12 Armen52 FurnaceBoy: excellent, thanks for your help!
CorticalStack too, thanks guys
2009-09-12 01:39:45 FurnaceBoy Armen52: yw
2009-09-12 01:39:48 CorticalStack yw
2009-09-12 01:43:09 erkules FurnaceBoy: Sorry for delay. benchmark is
hard, coze Im thinking if its not uselsee. Ive got a server doing 5000/s
(peek) but thats MyISAM with batch-skripts. The innodb-table will have a
584B PK and an 84B (+548B) Index. Thats hard to maintain and in fact Im
willing to tell the developer to redesingn it:)
2009-09-12 01:43:43 CorticalStack FurnaceBoy: you need an hour's podcast
for a bike ride this weekend check out the latest StackOverflow. Some
interesting ideas on Craigslist, journalism, democracy, etc.
2009-09-12 01:44:06 Techtronic hello , i have problems with mysql
Version: '5.0.75-0ubuntu10.2' i cant start it , error is
/usr/bin/mysql_upgrade: relocation error: /usr/bin/mysql_upgrade: symbol
dynstr_append_os_quoted, version libmysqlclient_15 not defined in file
libmysqlclient.so.15 with link time reference ................. Please
help
2009-09-12 01:45:52 FurnaceBoy erkules: I agree
2009-09-12 01:46:19 FurnaceBoy erkules: I think you should attempt a
benchmark no matter what the design is, though.
2009-09-12 01:46:27 FurnaceBoy erkules: set up a prototype system
2009-09-12 01:46:34 FurnaceBoy erkules: then you can compare designs
2009-09-12 01:46:42 FurnaceBoy erkules: it can be that Mk I is OK.
2009-09-12 01:46:53 FurnaceBoy erkules: but you need to know what to
improve and by how much
2009-09-12 01:49:22 erkules FurnaceBoy: Thx Im gonna do it. Btw isnt it -
compared to MyISAM - "useless" to build a covering index for the data you
want to fetch while using Innodb?
2009-09-12 01:49:30 Armen52 FurnaceBoy: any idea why my db keeps dropping
connections? i see things like this: 090911 17:32:24 [Warning] Aborted
connection 1 to db: 'mysql' user: 'root' host: 'localhost' (Got an error
reading communication packets)
2009-09-12 01:49:38 FurnaceBoy Armen52: firewall?
2009-09-12 01:50:01 Armen52 im sitting at the server, mysql is on
localhost
2009-09-12 01:50:04 FurnaceBoy erkules: you should review the manual
sections on innodb
2009-09-12 01:50:16 erkules FurnaceBoy: understood
2009-09-12 01:50:21 FurnaceBoy Armen52: what does the mysqld.err say?
anything?
2009-09-12 01:50:41 FurnaceBoy erkules: a long PK is bad if you have
secondary indexes
2009-09-12 01:50:50 Armen52 FurnaceBoy: wheres that file? in /var/mysql?
2009-09-12 01:50:54 Techtronic help
2009-09-12 01:50:55 FurnaceBoy Armen52: /var/log/mysql ?
2009-09-12 01:52:19 Armen52 FurnaceBoy: no such file there, no such path
defined in my.cnf
2009-09-12 01:52:37 FurnaceBoy Armen52: gotta be somewhere
2009-09-12 01:52:42 FurnaceBoy Armen52: /var/log
2009-09-12 01:52:42 erkules FurnaceBoy: Thats why my secondary Index has
size 84B (+548B) last PK, do 632B in total :-/. RTFM - again - now :-)
2009-09-12 01:52:43 FurnaceBoy ?
2009-09-12 01:53:14 FurnaceBoy erkules: actually it's PK size that
matters here
2009-09-12 01:53:22 FurnaceBoy erkules: but this is covered in the manual
2009-09-12 01:53:25 Armen52 FurnaceBoy: yeah thats where im looking,
there wasnt a mysql file or folder in /var/log
2009-09-12 01:54:24 erkules FurnaceBoy: yes my PK has 548B and thats used
again for the secondary index. damn I should quit for a while typing now.
sorry
2009-09-12 01:54:42 Techtronic :(
2009-09-12 01:54:47 FurnaceBoy erkules: :)
2009-09-12 01:54:56 FurnaceBoy erkules: good luck... seems like a design
rethink could be good
2009-09-12 01:55:02 FurnaceBoy erkules: saves disk space too
2009-09-12 01:55:34 fruitwerks I'm trying to do a group by, but I only
get the result of one join.. let me paste
2009-09-12 01:55:36 erkules FurnaceBoy: Thats what I wanted to hear, so I
feel strong to tell the developer :-D
2009-09-12 01:56:57 fruitwerks ok how can I make this work?
http://pastebin.ca/1562694
2009-09-12 01:57:06 FurnaceBoy erkules: :)
2009-09-12 01:57:22 FurnaceBoy erkules: set up a bit of a load test
anyway so you can put numbers to your gut feelings
2009-09-12 01:57:36 fruitwerks this isn't really what I want to do
though, because I want to process some of the data
2009-09-12 01:57:45 fruitwerks nm lol
2009-09-12 02:00:22 Armen52 FurnaceBoy: things seem to be working for
now, ill check in again if there are other issues. thanks again. gnite.
2009-09-12 02:03:54 erkules FurnaceBoy: ,o>
2009-09-12 02:05:06 thumbs FurnaceBoy: me has wine
2009-09-12 02:05:09 Techtronic Does anyone know what the problem?
2009-09-12 02:05:11 FurnaceBoy thumbs++
2009-09-12 02:05:50 FurnaceBoy Techtronic: reinstall
2009-09-12 02:05:52 thumbs pascual toso - el argentino
2009-09-12 02:05:59 FurnaceBoy thumbs: nice
2009-09-12 02:06:34 thumbs FurnaceBoy: it's rather light, but has a smoky
aftertaste
2009-09-12 02:06:38 Techtronic FurnaceBoy distro or mysql ? ;)
2009-09-12 02:06:55 FurnaceBoy Techtronic: mysql
2009-09-12 02:06:56 Techtronic or install postgresql
2009-09-12 02:06:58 thumbs FurnaceBoy: it's the first time I taste it.
It's surprisingly good.
2009-09-12 02:07:54 FurnaceBoy thumbs: :)
2009-09-12 02:08:15 thumbs I almost opted for a merlot. I'm glad I didn't
2009-09-12 02:08:32 FurnaceBoy thumbs: I buy em in 2s and 3s. spread the
risk
2009-09-12 02:08:54 thumbs FurnaceBoy: good for couples, bad for single
guys like me
2009-09-12 02:09:06 FurnaceBoy thumbs: she doesn't drink it
2009-09-12 02:09:12 thumbs oh, right.
2009-09-12 02:09:53 thumbs I almost forgot she didn't have a glass on the
lst meal
2009-09-12 02:10:06 FurnaceBoy thumbs: she's a weirdo
2009-09-12 02:10:12 thumbs heh?
2009-09-12 02:10:19 FurnaceBoy thumbs: doesn't like dry wines
2009-09-12 02:10:25 thumbs well, yes.
2009-09-12 02:10:35 thumbs dry red wines are delightful.
2009-09-12 02:11:16 behappy I have post.MYI with size 2.0Gb is there any
thing can I sestting up in my.cnf to faster import this data ??
2009-09-12 02:11:30 FurnaceBoy behappy: you mean query it?
2009-09-12 02:12:12 behappy FurnaceBoy, yes by command line mysql -uroot
-p db < db.sql
2009-09-12 02:12:20 FurnaceBoy behappy: um.
2009-09-12 02:12:25 FurnaceBoy behappy: you said a MYI
2009-09-12 02:12:47 FurnaceBoy behappy: so you're really importing a sql
dump
2009-09-12 02:13:00 FurnaceBoy behappy: there are a number of suggestions
in the manual to speed this up
2009-09-12 02:13:05 FurnaceBoy bulk load
2009-09-12 02:13:39 thumbs FurnaceBoy: yum. The second glass gets much
better
2009-09-12 02:14:24 freelanceweb WHen I am in a JOIN can't I call AS ?
2009-09-12 02:14:42 thumbs freelanceweb: you can use aliases.
2009-09-12 02:14:45 FurnaceBoy freelanceweb: hehe
2009-09-12 02:14:54 thumbs freelanceweb: what problem are you trying to
solve, exactly?
2009-09-12 02:14:56 FurnaceBoy freelanceweb: there's no 'in a join'.,
think of it as an expression
2009-09-12 02:15:16 blcknight If I'm doing a query, and I get 1 Warning
in the results, how do I see what it was?
2009-09-12 02:15:37 freelanceweb JOIN t2.clm ON (conditions) AS a name
2009-09-12 02:15:43 behappy FurnaceBoy, databses had size 5 Gb but I have
post.MYD 2.0 Gb and post.MYI also 2.0 Gb I have controlled whats happen I
get that post.MYI take long time and create post.TMD
2009-09-12 02:15:56 thumbs blcknight: show warnings;
2009-09-12 02:16:43 behappy FurnaceBoy, my.cnf
http://pastebin.com/d79839dd6
2009-09-12 02:17:29 fruitwerks I'm trying to use group with a date, it
works, but I have a few left outer joins and when I group, I only get the
first join
2009-09-12 02:17:38 blcknight thumbs: Thanks
2009-09-12 02:17:42 thumbs FurnaceBoy: TO sounds better and better. I
need a change of mind.
2009-09-12 02:18:26 fruitwerks argh this is tough one
2009-09-12 02:18:47 thumbs fruitwerks: put the LEFT JOINs at the end.
2009-09-12 02:19:15 fruitwerks http://pastebin.ca/1562694 after the group
by?
2009-09-12 02:19:30 thumbs fruitwerks: no, last in your FROM clause
2009-09-12 02:19:44 fruitwerks yeah I did.. look at the paste
2009-09-12 02:20:30 freelanceweb I Have a column that I want to bring in
2 different ways. JOIN size.name ON ( t1.clm2 = size.clm1 AND t2.clm3 !=
1) AS type1
2009-09-12 02:20:40 FurnaceBoy behappy: why do you need it to happen
quicker? can't you just load it
2009-09-12 02:20:51 thumbs fruitwerks: where's your pastebin?
2009-09-12 02:21:04 fruitwerks few lines up
2009-09-12 02:21:06 FurnaceBoy freelanceweb: have you differentiated join
conditions and row selection conditions?
2009-09-12 02:21:17 FurnaceBoy freelanceweb: that can help clarify things
2009-09-12 02:21:21 Naktibalda freelanceweb,   JOIN size.name AS type1 ON
( t1.clm2 = size.clm1 AND t2.clm3 != 1)
2009-09-12 02:21:29 Naktibalda is size a database or a table?
2009-09-12 02:21:47 freelanceweb Size is a table
2009-09-12 02:21:52 Naktibalda OMG, you are joining a column
2009-09-12 02:22:10 Naktibalda JOIN size ON(t1.clm2 = size.clm1 AND
t2.clm3 != 1)
2009-09-12 02:22:21 scgtrp is there a way to insert a bunch of identical
rows into a table at once?
2009-09-12 02:22:26 freelanceweb SRy.. I knew I was going about it
wrong..
2009-09-12 02:22:31 fruitwerks haha
2009-09-12 02:22:31 Naktibalda second condition isn't related to this
join
2009-09-12 02:22:49 thumbs fruitwerks: your last LEFT JOIN is bogus
2009-09-12 02:22:58 Naktibalda !tell fruitwerks about tutorial
2009-09-12 02:23:10 Naktibalda !tell freelanceweb about tutorial
2009-09-12 02:23:15 fruitwerks what is wrong with it thumbs?
2009-09-12 02:24:06 FurnaceBoy FurnaceBoy hears furious typing from the
neighbouring province
2009-09-12 02:24:11 Techtronic hmm Naktibalda tu lietuvis ?
2009-09-12 02:24:12 thumbs fruitwerks: the presence of a row from
feedings is dependant on the left join, which means it might be NULL
2009-09-12 02:24:20 Naktibalda Techtronic, ne
2009-09-12 02:25:00 thumbs fruitwerks: as such the result from a join
with the foodinv table might produce a cartesian product between feedings
and foodinv
2009-09-12 02:25:27 fruitwerks hmm.. that has never happened so far
2009-09-12 02:25:37 thumbs fruitwerks: it *might* happen.
2009-09-12 02:26:02 Techtronic Naktibalda gal tu gali padeti su mysql ?
2009-09-12 02:26:13 thumbs fruitwerks: the intersecting column has a non-
deterministic behaviour
2009-09-12 02:26:31 FurnaceBoy fruitwerks--
2009-09-12 02:26:32 thumbs fruitwerks: you should not rely on that
relationship.
2009-09-12 02:27:09 Techtronic '5.0.75-0ubuntu10.2' i cant start it ,
error is /usr/bin/mysql_upgrade: relocation error:
/usr/bin/mysql_upgrade: symbol dynstr_append_os_quoted, version
libmysqlclient_15 not defined in file libmysqlclient.so.15 with link time
reference
2009-09-12 02:27:21 FurnaceBoy Techtronic: did you reinstall?
2009-09-12 02:27:36 thumbs fruitwerks: typically, either the column is
out of place in the whole result set, or the normalization was botched
2009-09-12 02:27:49 thumbs fruitwerks: s/column/table/
2009-09-12 02:28:16 fruitwerks not sure why, but it does work everytime,
I think I need to do this in php anyway
2009-09-12 02:28:35 FurnaceBoy fruitwerks: things work accidentally but
are still broken
2009-09-12 02:28:58 fruitwerks I have about 2000 records in and no
glitches :/
2009-09-12 02:29:03 FurnaceBoy FurnaceBoy hopes fruitwerks isn't doing
avionics or x-ray control
2009-09-12 02:29:19 fruitwerks ... reptile husbandry
2009-09-12 02:29:25 thumbs fruitwerks: state the purpose of the feedings
table
2009-09-12 02:29:30 FurnaceBoy fruitwerks: well who wants sick lizards!!
2009-09-12 02:29:38 thumbs fruitwerks: did you intend to build a linking
table?
2009-09-12 02:30:01 fruitwerks haha, the feedings table contains the
ev_id and meal code, and a PK
2009-09-12 02:30:24 fruitwerks meal code returns a human readable from
foodinv
2009-09-12 02:30:42 Techtronic ech , ok good night all
2009-09-12 02:30:51 fruitwerks cya
2009-09-12 02:31:00 thumbs fruitwerks: so the last join should really be
INNER JOIN
2009-09-12 02:31:15 fruitwerks ok let me see if that breaks anything
2009-09-12 02:31:29 thumbs fruitwerks: you can't slap a simple INNER JOIN
there
2009-09-12 02:32:01 fruitwerks indeed that is uber fail
2009-09-12 02:32:12 thumbs fruitwerks: see, every meal code MUST
correspond to a food inventory entry
2009-09-12 02:32:22 fruitwerks it does
2009-09-12 02:32:27 thumbs fruitwerks: that is the basis of a INNER JOIN.
2009-09-12 02:33:00 fruitwerks if it wasn't in foodinv, they couldn't
have eaten it lol
2009-09-12 02:33:33 thumbs fruitwerks: the problem with the whole query
is that you're connecting two distinct entities, without a strict
relation.
2009-09-12 02:34:36 fruitwerks if I inner join foodinv, all I get are the
feedings in the result
2009-09-12 02:34:38 thumbs fruitwerks: one being the feedings<->inventory
to animals.
2009-09-12 02:35:00 thumbs fruitwerks: well, there must be a solution to
this.
2009-09-12 02:35:30 FurnaceBoy FurnaceBoy waits breathlessly
2009-09-12 02:36:16 thumbs got it.
2009-09-12 02:36:23 fruitwerks this is really hard to explain lol
2009-09-12 02:36:57 FurnaceBoy fruitwerks: which is why it's hard to
explain to the computer, too
2009-09-12 02:37:26 mikegriffin CorticalStack: i thought you might like
todays xkcd
2009-09-12 02:37:41 thumbs LEFT JOIN (feedings INNER JOIN foodinv ON
feedings.perm_id=foodinv.perm_id) AS fd ON master_ev.ev_id=fd.ev_id
2009-09-12 02:37:49 thumbs tada.
2009-09-12 02:38:04 fruitwerks ahh thats the lingo I get lost in lol
2009-09-12 02:38:09 thumbs that should resolve the ambiguity.
2009-09-12 02:38:17 fruitwerks make that my last join?>
2009-09-12 02:38:22 thumbs yes.
2009-09-12 02:38:48 fruitwerks empty result
2009-09-12 02:38:49 thumbs that replaces the last two joins, actually.
2009-09-12 02:38:53 thumbs ummm
2009-09-12 02:39:01 fruitwerks oh let me try
2009-09-12 02:39:32 thumbs this is called a derived table.
2009-09-12 02:39:53 thumbs left joining a derived table will have no
incidence on the whole resultset.
2009-09-12 02:41:55 fruitwerks mysql complains after the AS
2009-09-12 02:42:38 thumbs what error do you get?
2009-09-12 02:42:51 Naktibalda join in parenthesis? o_O
2009-09-12 02:42:56 fruitwerks thanks for the help, I think I'm going to
stick with what I have and try something in php
2009-09-12 02:43:14 fruitwerks said syntax error after the AS
2009-09-12 02:43:16 FurnaceBoy thumbs: doesn't a derived table require a
SELECT ?
2009-09-12 02:43:24 thumbs FurnaceBoy: right, it does.
2009-09-12 02:43:25 fruitwerks heh
2009-09-12 02:43:28 thumbs FurnaceBoy: you're right.
2009-09-12 02:44:16 thumbs Naktibalda: I blame the red wine.
2009-09-12 02:44:33 Naktibalda :)
2009-09-12 02:44:55 fruitwerks :D
2009-09-12 02:45:11 thumbs fruitwerks: that would resolve the syntax
error, I reckon?
2009-09-12 02:45:33 freelanceweb So I know I wasnt clear before.. maybe
this will help... http://paste2.org/p/422399
2009-09-12 02:47:52 freelanceweb so my result would have alias = sm and
btm_alias = sm_btm in one row. CASE maybe?
2009-09-12 02:47:58 thumbs oh bloody hell. No wonder I use british
expressions, I'm watching british TV.
2009-09-12 02:48:32 FurnaceBoy thumbs: you're in an awkward place then!
2009-09-12 02:48:48 thumbs I concur.
2009-09-12 02:49:30 FurnaceBoy thumbs: British PM apologised posthumously
to Alan Turing (for the persecution that led to the latter's suicide)
2009-09-12 02:49:40 FurnaceBoy thumbs: so they're not all bad, old chap.
2009-09-12 02:49:43 thumbs oh, I read.
2009-09-12 02:52:50 thumbs It was mostly atrocious to think that he meant
the initial comments.
2009-09-12 02:54:29 thumbs It's sad to see that most public apologies are
made out of necessity rather than honesty
2009-09-12 02:54:48 FurnaceBoy thumbs: yes. apparently the PM was
*required* to say something by some petitioning rule.
2009-09-12 02:55:08 thumbs exactly.
2009-09-12 02:55:27 FurnaceBoy thumbs: but it does at least give some
awareness to the very-much-ongoing systemic problem.
2009-09-12 02:55:48 thumbs not many folks will perceive that, however.
2009-09-12 02:56:00 FurnaceBoy thumbs: tragic story :|
2009-09-12 02:56:31 FurnaceBoy thumbs: if his death was meaningless and
unnecessary, at least he had a meaningful and important life.
2009-09-12 02:57:16 thumbs meaning is best viewed by external sources at
times. One is not always aware of self-commitments.
2009-09-12 02:57:23 thumbs errr achievements.
2009-09-12 02:58:18 FurnaceBoy thumbs: wine ?
2009-09-12 02:58:22 thumbs you'll always find appreciation in the most
unlikely party.
2009-09-12 02:58:27 FurnaceBoy FurnaceBoy just finished a glass
2009-09-12 02:58:29 thumbs ... right.
2009-09-12 02:58:35 thumbs let's have another.
2009-09-12 02:58:43 FurnaceBoy thumbs: there's always irc. no good deed
goes unpunished.
2009-09-12 02:59:15 thumbs the audience draws thin at this hour.
2009-09-12 02:59:44 FurnaceBoy thumbs: right, we need something to liven
up the campfire
2009-09-12 02:59:55 FurnaceBoy thumbs: ghost stories!
2009-09-12 03:00:07 thumbs oh, a good old-fashion debacle will do.
2009-09-12 03:00:26 FurnaceBoy and a good kickbanning.
2009-09-12 03:02:15 thumbs thumbs pours a fourth helping of wine
2009-09-12 03:02:48 freelanceweb ANy Ideas on my post?
2009-09-12 03:03:50 FurnaceBoy freelanceweb: looks like you need a self
join
2009-09-12 03:03:57 FurnaceBoy freelanceweb: and possibly a schema
lobotomy
2009-09-12 03:04:12 freelanceweb Lol
2009-09-12 03:04:50 thumbs I wonder if fruitwerks's query worked.
2009-09-12 03:04:56 thumbs I was looking forward to the result
2009-09-12 03:05:34 FurnaceBoy freelanceweb: this schema is bad.
2009-09-12 03:05:41 FurnaceBoy freelanceweb: what the h*** is the primary
key here?
2009-09-12 03:06:17 FurnaceBoy freelanceweb: it looks like this should be
2 tables.
2009-09-12 03:06:46 freelanceweb Well this is a table that has been added
to allow me to put an alias to a size .. now I am apending it to add a
veriation for a bottom item
2009-09-12 03:06:50 FurnaceBoy freelanceweb: if you can identify the
correct PK please do so
2009-09-12 03:07:04 freelanceweb PK?
2009-09-12 03:07:25 FurnaceBoy freelanceweb: what set of columns uniquely
identifies the entity/row
2009-09-12 03:07:35 FurnaceBoy answer this as if the 'alias' idea does
not exist
2009-09-12 03:09:39 FurnaceBoy freelanceweb: ok, so i think 'alias' is a
sufficient unique key, is it?
2009-09-12 03:10:18 FurnaceBoy freelanceweb: there's still something
fishy here.
2009-09-12 03:10:32 FurnaceBoy freelanceweb: are you trying to relate a
row with its 'btm' row?
2009-09-12 03:11:05 FurnaceBoy freelanceweb: on the face of it, you can't
do that properly, because the row's key is itself being modified to
indicate btm-ness.
2009-09-12 03:11:06 freelanceweb well.. You know what I am going to go
with the labotomy and just place the btmAlias alias in that colum instead
of this nightmare...
2009-09-12 03:11:27 FurnaceBoy freelanceweb: well let's start @ the
beginning. is 'alias' a column which uniquely identifies a row?
2009-09-12 03:11:54 freelanceweb ok well the uniqu would actualy be
btmAlias or not
2009-09-12 03:11:58 FurnaceBoy no
2009-09-12 03:12:16 FurnaceBoy what are the entities here?
2009-09-12 03:12:17 thumbs wait, the green light is at the bottom in
London?
2009-09-12 03:12:23 FurnaceBoy thumbs: and F1
2009-09-12 03:12:28 thumbs sorry, this seemed odd.
2009-09-12 03:13:02 FurnaceBoy freelanceweb: if i plucked one row from
this table. what is it, and how do i uniquely identify it?
2009-09-12 03:13:14 freelanceweb From the top. I select a size by brand
and look if it has an alias.
2009-09-12 03:13:30 FurnaceBoy freelanceweb: see, this indicates a
problem
2009-09-12 03:13:31 thumbs is the alias unique?
2009-09-12 03:13:42 FurnaceBoy freelanceweb: and is the alias optional?
2009-09-12 03:13:46 thumbs if you don't say yes here, then you have a
problem.
2009-09-12 03:13:53 FurnaceBoy freelanceweb: without 'alias' you have NO
possibilities for a primary key.
2009-09-12 03:14:00 FurnaceBoy freelanceweb: which is wrong
2009-09-12 03:14:07 freelanceweb I see that..
2009-09-12 03:14:19 FurnaceBoy so. what's the entity. and how would you
uniquely identify it. SKU?
2009-09-12 03:14:57 freelanceweb yes.. or product.. with sizes
2009-09-12 03:15:06 FurnaceBoy so (brand,size) is unique? unlikely.
2009-09-12 03:15:18 FurnaceBoy (Nike,10) .. (Nike,10) ,. oops!
2009-09-12 03:15:47 FurnaceBoy you need either a unique value (e.g. SKU)
or a unique tuple (brand,size,model,style...)
2009-09-12 03:16:19 FurnaceBoy also, size_id and size_name seems
redundant.
2009-09-12 03:16:38 freelanceweb whay not just brand,product, size
2009-09-12 03:16:43 FurnaceBoy is it unique>
2009-09-12 03:17:04 freelanceweb brand product size would be unique
2009-09-12 03:17:15 FurnaceBoy ok then that can be your pk
2009-09-12 03:17:19 thumbs I fail to believe that.
2009-09-12 03:17:20 FurnaceBoy but...
2009-09-12 03:17:23 thumbs but go ahead.
2009-09-12 03:17:59 FurnaceBoy if you want to continue with a single
table, then the pk would have to change to (brand,product,size,btm_flag)
2009-09-12 03:18:37 FurnaceBoy and you can see this is not going well
2009-09-12 03:19:14 freelanceweb my problem is if there is no btmAlias
and there is a Alias we defaust there if neither then we go to size name
2009-09-12 03:19:22 thumbs that's f.u.g.l.y.
2009-09-12 03:19:36 FurnaceBoy freelanceweb: well that's a simple query,
but it's important to get the schema right
2009-09-12 03:19:37 freelanceweb I agree.. I am changing th e schema..
2009-09-12 03:20:09 FurnaceBoy freelanceweb: so some of your
(brand,product,size) have alias and some of them have btm_alias
2009-09-12 03:20:31 freelanceweb yes
2009-09-12 03:20:42 FurnaceBoy if that's true and complete then i would
make 3 tables. you can reconsider your pk later.
2009-09-12 03:20:53 freelanceweb some would just get alias but others
would get btmAlias
2009-09-12 03:21:18 FurnaceBoy product(key, ...) and product_alias(key,
alias) and product_alias_btm(key, alias)
2009-09-12 03:21:37 FurnaceBoy that's what I would do, given what you
just told me.
2009-09-12 03:21:51 freelanceweb I can actualy use the btmAlias for the
actual alias there instead of it being a flag
2009-09-12 03:22:00 FurnaceBoy freelanceweb: ^^ please read first.
2009-09-12 03:22:16 FurnaceBoy freelanceweb: both alias and alias_btm are
optional, right?
2009-09-12 03:22:18 freelanceweb so I always pull all and let the code
check whis is populated
2009-09-12 03:22:22 freelanceweb right
2009-09-12 03:22:27 FurnaceBoy freelanceweb: you're typing more than
you're reading.
2009-09-12 03:22:41 FurnaceBoy FurnaceBoy | product(key, ...) and
product_alias(key, alias) and product_alias_btm(key, alias)
2009-09-12 03:23:22 FlaPer87 hey guys, is it possible to change Time to
String in a sql?
2009-09-12 03:23:29 thumbs freelanceweb: LISTEN TO FurnaceBoy!
2009-09-12 03:23:30 FurnaceBoy FlaPer87: it is returned as a string.
2009-09-12 03:23:40 freelanceweb ok THat makes sense.. another table it
is..
2009-09-12 03:23:46 FurnaceBoy freelanceweb: another two tables.
2009-09-12 03:24:08 FurnaceBoy freelanceweb: total 3. not four. not two.
not five. Brother Maynard! Bring The Holy Hand Grenade.
2009-09-12 03:24:16 FlaPer87 FurnaceBoy: and can I format it from
00:00:00 to 00:00 ?
2009-09-12 03:24:22 FurnaceBoy FlaPer87: yes.
2009-09-12 03:24:29 FlaPer87 FurnaceBoy: how?
2009-09-12 03:24:32 FurnaceBoy FlaPer87: LEFT(timecol,5)
2009-09-12 03:24:39 FlaPer87 FurnaceBoy: thaks
2009-09-12 03:25:00 freelanceweb FurnaceBoy : ty.. I wil make that
change..
2009-09-12 03:25:05 FurnaceBoy freelanceweb: yw
2009-09-12 03:25:25 freelanceweb FurnaceBoy : sry for the confusion..
2009-09-12 03:27:25 thumbs FurnaceBoy: you know, you have to kick me
once, in your life.
2009-09-12 03:27:30 FurnaceBoy freelanceweb: then the qry is SELECT
IFNULL(COALESCE(a.alias,b.alias),product.size_name) FROM product LEFT
JOIN product_alias a ON a.key=product.key LEFT JOIN product_btm_alias b
ON b.key=product.key
2009-09-12 03:27:39 FurnaceBoy freelanceweb: i've ignored the
complication of a composite pk
2009-09-12 03:27:59 FurnaceBoy thumbs: no thrill
2009-09-12 03:28:06 FurnaceBoy thumbs: I'm not a kicky person
2009-09-12 03:29:36 FurnaceBoy thumbs: power corrupts
2009-09-12 03:29:44 thumbs lies.
2009-09-12 03:30:05 thumbs I only kick when being openly insulted.
2009-09-12 03:30:24 FurnaceBoy thumbs: power corrupts, where nick <>
'thumbs'
2009-09-12 03:30:55 thumbs hey, now I look like a selfish bastard.
2009-09-12 03:31:23 FurnaceBoy QED!
2009-09-12 03:31:51 freelanceweb FurnaceBoy : That was much easier.. now
I can just JOIN...
2009-09-12 03:31:59 FurnaceBoy freelanceweb: ^^
2009-09-12 03:32:27 thumbs FurnaceBoy: 1 mysql: 0
2009-09-12 03:32:42 FurnaceBoy thumbs: you did the harder one. reptile
husbandry
2009-09-12 03:32:52 FurnaceBoy thumbs: his stuff is always agonising
2009-09-12 03:32:58 freelanceweb got it
2009-09-12 03:33:07 FurnaceBoy no offence fruitwerks
2009-09-12 03:33:08 thumbs FurnaceBoy: he has no solution yet.
2009-09-12 03:33:16 FurnaceBoy thumbs: death by schema
2009-09-12 03:33:22 thumbs FurnaceBoy: or perhaps he has, but he has not
replied.
2009-09-12 03:33:24 FurnaceBoy thumbs: it's like the hug of a boa
constrictor.
2009-09-12 03:33:27 FurnaceBoy ...that's been working out.
2009-09-12 03:34:19 thumbs hey, are you hugging my throat or is it me...?
2009-09-12 03:34:58 FurnaceBoy thumbs: the slaves are revolting
2009-09-12 03:35:38 thumbs they're on stike.
2009-09-12 03:35:57 thumbs I just got notice that they disliked their
meager salary
2009-09-12 03:37:37 thumbs dammit, my keyboard is refusing to type some
words for me
2009-09-12 03:38:11 FurnaceBoy thumbs: either it's drunk, or you are
2009-09-12 03:38:18 thumbs no, tipsy.
2009-09-12 03:39:16 thumbs so I'm contemplating a new phone, so I can
tweet from it.
2009-09-12 03:40:14 thumbs FurnaceBoy: unless you're tweeting from your
work PC?
2009-09-12 03:41:08 FurnaceBoy thumbs: yeah
2009-09-12 03:41:11 FurnaceBoy thumbs: shocking habit
2009-09-12 03:41:36 djahandarie thumbs, iphone!
2009-09-12 03:41:46 thumbs FurnaceBoy: you seem to have good things to
tweet about, tho.
2009-09-12 03:42:21 djahandarie I tried twitter once. Used it for about
two days.
2009-09-12 03:42:24 djahandarie Then I forgot about it.
2009-09-12 03:42:28 djahandarie Same with blogging...
2009-09-12 03:43:17 thumbs well, you can tweet a lot if your job
assignments are related to your interests.
2009-09-12 03:44:15 FurnaceBoy thumbs: my politics clash with some of my
co-workers'.
2009-09-12 03:44:24 FurnaceBoy thumbs: I self censor. some of the time.
:)
2009-09-12 03:44:49 FurnaceBoy thumbs: one rule of twitter, you never
know who's listening
2009-09-12 03:44:50 thumbs my coworkers are idiots.
2009-09-12 03:45:04 FurnaceBoy thumbs: being employed has advantages tho
2009-09-12 03:45:13 thumbs yeah, sure.
2009-09-12 03:45:53 thumbs my coworker started a linkin account and ...
found me.
2009-09-12 03:46:00 thumbs FurnaceBoy: I was dumbfounded.
2009-09-12 03:46:13 FurnaceBoy speaking of which
2009-09-12 03:46:30 thumbs oh, you have one of those too?
2009-09-12 03:46:49 FurnaceBoy yeah! let;s RELATE!
2009-09-12 03:47:16 FurnaceBoy oh wait, we are.
2009-09-12 03:47:42 FurnaceBoy "Recommend .... thumb's work on irc..."
2009-09-12 03:48:02 djahandarie I have a LinkedIn!
2009-09-12 03:48:10 thumbs yeah, we already cconnected.
2009-09-12 03:48:43 djahandarie djahandarie wonders why FurnaceBoy is
still opped
2009-09-12 03:49:12 FurnaceBoy say that to my face and find out!
2009-09-12 03:50:14 djahandarie djahandarie says to FurnaceBoy's face
"Why are you still opped?!"
2009-09-12 03:50:16 FurnaceBoy thumbs: LinkedIn is good at dredging up
one's past, memorable or regrettable as it may be. apparently Facepalm
does the same for intimate relationships. I wouldn't know
2009-09-12 03:50:27 FurnaceBoy djahandarie: oh. you called my bluff.
2009-09-12 03:50:35 djahandarie Facepalm? You mean Assbook?
2009-09-12 03:50:36 djahandarie Wait
2009-09-12 03:50:37 djahandarie What
2009-09-12 03:50:39 FurnaceBoy djahandarie: ya
2009-09-12 03:50:41 djahandarie http://www.linkedin.com/in/djahandarie
2009-09-12 03:50:44 thumbs FurnaceBoy: that depends on how much you
expose to it.
2009-09-12 03:52:00 djahandarie I don't really have much info on there...
enough to find me and kill me though I guess. D:
2009-09-12 03:52:21 thumbs FurnaceBoy: you know what company I work for,
I suppose.
2009-09-12 03:52:22 djahandarie Although my name alone is probably enough
for that considering how unique it is. :P
2009-09-12 03:52:35 FurnaceBoy thumbs: I could probably figure it out
from your LI profile
2009-09-12 03:52:56 FurnaceBoy djahandarie: that would take motive and
motivation
2009-09-12 03:53:53 djahandarie FurnaceBoy, or insanity!
2009-09-12 03:54:22 FurnaceBoy djahandarie: or a bus falling from a
parking lot
2009-09-12 03:55:01 thumbs so the bottle has one glass left.
2009-09-12 03:55:29 FurnaceBoy thumbs: that is impressive.
2009-09-12 03:55:35 thumbs I have to savour it.
2009-09-12 03:55:57 thumbs it was merely 750 mil, tho.
2009-09-12 03:55:58 djahandarie FurnaceBoy, you totally lost me on that
2009-09-12 03:56:15 FurnaceBoy djahandarie: me too
2009-09-12 03:56:26 FurnaceBoy thumbs: that's a good evening's bout.
unless you're cortex
2009-09-12 03:56:30 FurnaceBoy CorticalStack*
2009-09-12 03:56:40 FurnaceBoy nick complete FAIL since he's already
passed out drunk
2009-09-12 03:58:26 Evet innodb's default settings are ok?
2009-09-12 03:58:28 djahandarie djahandarie wonders if all DBAs drink
heavily while on IRC
2009-09-12 03:58:52 djahandarie Evet, OK? They work.
2009-09-12 03:58:54 thumbs djahandarie: what do you think?
2009-09-12 03:59:11 djahandarie Evet, they can be optimized based on your
server/application needs.
2009-09-12 03:59:22 thumbs !m Evet tuning primer
2009-09-12 03:59:27 thumbs ooooh
2009-09-12 03:59:31 djahandarie lol
2009-09-12 03:59:32 thumbs tuning primer
2009-09-12 03:59:52 mikegriffin anyone use the "MySQL Enterprise Monitor"
2009-09-12 04:00:02 FurnaceBoy mikegriffin: a few years ago i played with
it.
2009-09-12 04:00:04 Evet ö
2009-09-12 04:00:35 djahandarie mikegriffin, no, but I saw the big ad for
it on mysql.com today. :P
2009-09-12 04:00:35 FurnaceBoy thumbs: so i'm looking at the slow query
log. #1 is SET NAMES '...' @ 15 seconds.
2009-09-12 04:00:37 mikegriffin can you get it without full support?
2009-09-12 04:00:46 thumbs FurnaceBoy: huh?
2009-09-12 04:00:47 FurnaceBoy mikegriffin: at the time we had... Silver?
2009-09-12 04:00:57 thumbs FurnaceBoy: side effect.
2009-09-12 04:01:00 FurnaceBoy thumbs: yes
2009-09-12 04:01:38 djahandarie Lol @ SET NAMES on the top of the query
log
2009-09-12 04:02:00 thumbs most likely waiting on locks being released
2009-09-12 04:02:55 mikegriffin oh wow, not only do you have to have the
support contract to get the monitor, you have to use the silver edition @
$2k
2009-09-12 04:03:07 djahandarie I had SELECT 823/14 or something up there
at one point
2009-09-12 04:03:16 mikegriffin i would pay $200 for just the monitor per
year
2009-09-12 04:03:24 djahandarie Only because the server had died at that
point but was still taking queries and logging or something
2009-09-12 04:03:28 djahandarie It was weird
2009-09-12 04:04:12 mikegriffin so what should i recommend to someone
looking for easy to read monitoring? cacti?
2009-09-12 04:04:24 thumbs sure.
2009-09-12 04:04:29 mikegriffin i would just use 'show' :)
2009-09-12 04:04:49 djahandarie mikegriffin, percona made a mysql thing
for cacti which is pretty nice
2009-09-12 04:04:55 djahandarie Also supports apache/nginx/memcached
2009-09-12 04:04:59 mikegriffin thumbs djahandarie thanks
2009-09-12 04:05:01 djahandarie I think it was percona at least
2009-09-12 04:05:02 djahandarie :P
2009-09-12 04:05:20 djahandarie mikegriffin,
http://code.google.com/p/mysql-cacti-templates/
2009-09-12 04:05:21 thumbs oh, I like percona.
2009-09-12 04:05:33 thumbs the staff @percona is great
2009-09-12 04:05:42 djahandarie Beware though, cacti can have serious
performance problems
2009-09-12 04:05:51 djahandarie Someone I know had to modify their kernel
for it to work properly
2009-09-12 04:06:35 djahandarie thumbs, yeah, it'd be cool to work there.
I'm not as good as those other guys though. :(
2009-09-12 04:06:45 djahandarie I've only been doing MySQL for like a
year though. T_T
2009-09-12 04:07:03 thumbs djahandarie: I'm nowhere near close to either.
2009-09-12 04:07:10 thumbs djahandarie: perhaps in a few years.
2009-09-12 04:07:47 djahandarie Hell knows what I'll be doing in a few
years lol... last year I barely knew anything about programming
2009-09-12 04:08:07 djahandarie Besides a little PHP, SQL, and JavaScript
2009-09-12 04:08:43 djahandarie Now I know Haskell, Erlang, Scheme, and a
bunch of Computer Science shtuff
2009-09-12 04:09:09 djahandarie I also sucked with Linux a year ago
2009-09-12 04:09:21 thumbs OK, give yourself a few years.
2009-09-12 04:09:21 djahandarie I like to think I suck less now. 8-)
2009-09-12 04:09:46 djahandarie djahandarie takes note
2009-09-12 04:10:06 FurnaceBoy djahandarie: you probably do.
2009-09-12 04:10:13 FurnaceBoy djahandarie: my C sucked less after the
first 5-10 years
2009-09-12 04:10:57 djahandarie FurnaceBoy, I do suck less or I do suck?
:P
2009-09-12 04:11:12 mikegriffin less than what?
2009-09-12 04:11:16 FurnaceBoy djahandarie: i meant "you probably do suck
less"
2009-09-12 04:11:23 FurnaceBoy djahandarie | I like to think I suck less
now. 8-)
2009-09-12 04:11:30 FurnaceBoy we all should suck less tomorrow
2009-09-12 04:11:43 mikegriffin suck smarter not harder
2009-09-12 04:11:47 FurnaceBoy :)
2009-09-12 04:11:53 FurnaceBoy that's what I always tell your mom
2009-09-12 04:12:05 thumbs interesting twist of words.
2009-09-12 04:12:13 thumbs mikegriffin: you remind me of chad.
2009-09-12 04:12:25 FurnaceBoy thumbs: lucky chadmaynard is not here to
defend himself
2009-09-12 04:12:37 djahandarie Ahhh... so THAT'S his name
2009-09-12 04:12:42 FurnaceBoy thumbs: he does not love this channel any
more.
2009-09-12 04:12:45 djahandarie I couldn't tell where his first name
ended in that nick
2009-09-12 04:12:58 thumbs FurnaceBoy: that's not true.
2009-09-12 04:13:04 mikegriffin djahandarie: his last name is not nard
2009-09-12 04:13:31 mikegriffin there was a nick in here that took me
ages to figure out was a name backwards at some point
2009-09-12 04:14:16 djahandarie Mine is probably pretty confusing
2009-09-12 04:14:28 djahandarie To be honest it probably just looks like
a bunch of random letters smashed together
2009-09-12 04:15:34 thumbs djahandarie: let me guess, ... you're a DJ.
2009-09-12 04:15:45 djahandarie thumbs, no. lol
2009-09-12 04:15:50 djahandarie Darius Jahandarie
2009-09-12 04:15:52 djahandarie :P
2009-09-12 04:16:04 FurnaceBoy FurnaceBoy presses the big red DROPPED DOX
button
2009-09-12 04:16:20 FurnaceBoy oh, my real name is showing. I only wear a
kilt to irc
2009-09-12 04:16:27 mikegriffin /nick djfavicon
2009-09-12 04:16:36 mikegriffin that is my alternate ego
2009-09-12 04:16:51 FurnaceBoy mikegriffin: do you have an alter twitter
as well?
2009-09-12 04:17:01 mikegriffin hold on, nasa needs me
2009-09-12 04:17:12 mikegriffin mikegriffin puts on robe and wizard hat
2009-09-12 04:17:45 FurnaceBoy mikegriffin: I'm disappointed:
http://twitter.com/djfavicon
2009-09-12 04:18:57 mikegriffin you are so insistent i may someday become
a twitter
2009-09-12 04:19:03 mikegriffin or however you conjugate that
2009-09-12 04:19:11 FurnaceBoy mikegriffin: twit? tweeter? twat?
2009-09-12 04:19:33 FurnaceBoy mikegriffin: oh, I see. you fooled me. the
twitter you told me once was misinformation
2009-09-12 04:19:45 mikegriffin indeed
2009-09-12 04:19:47 FurnaceBoy mikegriffin: here i was, thinking his
tweets were yours
2009-09-12 04:20:02 mikegriffin i actually said, "no, that is not mine. i
dont have one"
2009-09-12 04:20:07 mikegriffin but you were drunk
2009-09-12 04:20:40 FurnaceBoy mikegriffin: probably
2009-09-12 04:20:52 djahandarie djahandarie doesn't drink regularly
2009-09-12 04:20:58 djahandarie Considering it would be very illegal for
me. :P
2009-09-12 04:21:04 FurnaceBoy mikegriffin: "and you are ugly. but
tomorrow, madam, I will be sober."
2009-09-12 04:21:30 mikegriffin :)
2009-09-12 04:22:10 mikegriffin mikegriffin wonders about the difference
between illegal and very illegal
2009-09-12 04:23:04 thumbs FurnaceBoy: your red wine suggestion was most
adequate.
2009-09-12 04:23:06 djahandarie mikegriffin, it's kind of like the
difference between legal and VERY legal
2009-09-12 04:23:16 FurnaceBoy thumbs: knew you'd go for it
2009-09-12 04:23:35 FurnaceBoy mikegriffin: also note the "it's not so
illegal that i can't do it occasionally."
2009-09-12 04:23:43 mikegriffin so i have some reorging to do. i might
not be on net for a couple of days.
2009-09-12 04:23:49 mikegriffin FurnaceBoy: promiss to miss me a little?
2009-09-12 04:24:02 thumbs miss you? Who?
2009-09-12 04:24:06 FurnaceBoy mikegriffin: i'll realise how much i
missed you when you pop back
2009-09-12 04:24:19 mikegriffin mikegriffin tips hat
2009-09-12 04:24:43 FurnaceBoy i think he's going into Witness Protection
Program
2009-09-12 04:24:57 thumbs oh. Pay holiday.
2009-09-12 04:25:00 FurnaceBoy right mikegriffin?
2009-09-12 04:25:02 thumbs paid, too.
2009-09-12 04:25:12 mikegriffin mikegriffin forgot this was in screen on
another host
2009-09-12 04:25:13 FurnaceBoy thumbs: yeah but i wouldn't relish the
cosmetic surgery
2009-09-12 04:25:26 mikegriffin i dont have to part after all
2009-09-12 04:25:33 mikegriffin no sweet sorrow
2009-09-12 04:25:39 djahandarie -_-
2009-09-12 04:25:45 djahandarie How do you exactly forget that anyways?
:P
2009-09-12 04:25:52 djahandarie sudo shutdown -h now
2009-09-12 04:25:53 djahandarie OSHT
2009-09-12 04:25:57 FurnaceBoy mikegriffin: we'll suffer a little when
you go to bed
2009-09-12 04:26:00 mikegriffin this window has been open for
weeks/months or something
2009-09-12 04:26:07 thumbs years?
2009-09-12 04:26:15 djahandarie MILLENIA
2009-09-12 04:26:17 mikegriffin screen is quite awesome
2009-09-12 04:26:22 mikegriffin but only for irssi
2009-09-12 04:26:28 mikegriffin it sucks for trying to get work done
2009-09-12 04:26:38 djahandarie Works for any curses program lol
2009-09-12 04:26:38 mikegriffin mikegriffin rants about copy/paste again
2009-09-12 04:26:41 thumbs I beg to differ
2009-09-12 04:26:42 djahandarie I run mutt and rtorrent in it
2009-09-12 04:27:07 thumbs irssi will help you gain knowledge.
2009-09-12 04:27:21 djahandarie You just need to remember to avoid the
distracting channels
2009-09-12 04:27:21 djahandarie :P
2009-09-12 04:27:54 thumbs well, it depends on what your field of work is
2009-09-12 04:27:58 FurnaceBoy djahandarie: this is #1
2009-09-12 04:28:21 mikegriffin i find this channel to be quite
distracting sometimes, when there are no other regulars around
2009-09-12 04:28:23 djahandarie "I'm an expert in ##defocus"
2009-09-12 04:28:25 thumbs if you work in the sql world, you can capture
gems from this channel.
2009-09-12 04:28:41 FurnaceBoy thumbs: this is true.
2009-09-12 04:28:47 mikegriffin it is hard to watch people give others
terrible advice
2009-09-12 04:28:53 djahandarie "Oh, where do you work?!"
2009-09-12 04:28:56 FurnaceBoy mikegriffin: ask [ray
2009-09-12 04:28:59 djahandarie "My mom's basement!"
2009-09-12 04:29:15 thumbs mikegriffin: sure, next time it happens, ping
me.
2009-09-12 04:29:15 djahandarie thumbs, that's the only reason I came to
idle in here in the first place
2009-09-12 04:29:39 djahandarie I can also complain about my job in here
2009-09-12 04:29:40 djahandarie :P
2009-09-12 04:29:43 thumbs mikegriffin: I'll k/b whoever gives erronous
advice.
2009-09-12 04:29:49 mikegriffin :)
2009-09-12 04:29:55 djahandarie "Just hit it with a hammer"
2009-09-12 04:30:09 maelstrom what can I use in a query to represent
exactly 48 hours before the time of the query ?
2009-09-12 04:30:11 thumbs no hammers allowed.
2009-09-12 04:30:21 medfly I suggest rebooting your server.
2009-09-12 04:30:24 djahandarie maelstrom, just hit it with a hammer
2009-09-12 04:30:35 thumbs now() - internal 48 hour
2009-09-12 04:30:42 FurnaceBoy thumbs: lol
2009-09-12 04:30:44 thumbs interval
2009-09-12 04:30:46 FurnaceBoy thumbs: your keyboard
2009-09-12 04:30:55 thumbs FurnaceBoy: I'm..... tipsy...
2009-09-12 04:31:16 mikegriffin i want to submit a patch too allow
plurals
2009-09-12 04:31:24 thumbs FurnaceBoy: I had an entire bottle of wine.
2009-09-12 04:31:26 djahandarie How the hell do you remember all these
wrench triggers
2009-09-12 04:31:29 FurnaceBoy thumbs: i'm impressed
2009-09-12 04:31:36 FurnaceBoy thumbs: now we need a schema design
question
2009-09-12 04:31:41 thumbs sure!
2009-09-12 04:31:45 thumbs shoot!
2009-09-12 04:31:47 FurnaceBoy djahandarie: he didn't. he was just
correcting himself.
2009-09-12 04:31:56 djahandarie FurnaceBoy, oh lmfao
2009-09-12 04:32:31 thumbs I have inherent memory from all the possible
triggers
2009-09-12 04:32:50 thumbs they are consequently subconscious
2009-09-12 04:33:09 FurnaceBoy !wench learn wine is Red wine is the best
way to start designing a schema. Drink 1 bottle then find a sharpie.
2009-09-12 04:33:21 maelstrom thanks thumbs
2009-09-12 04:33:24 thumbs winner.
2009-09-12 04:33:38 thumbs maelstrom: oh, sure.
2009-09-12 04:34:34 mikegriffin we need more error triggers
2009-09-12 04:34:38 mikegriffin error
2009-09-12 04:34:45 thumbs hey, this was the highlight of my week
2009-09-12 04:34:52 mikegriffin error
2009-09-12 04:34:55 mikegriffin error
2009-09-12 04:47:17 FurnaceBoy thumbs: heh
2009-09-12 04:47:24 FurnaceBoy thumbs: on this note I am going to bed.
boa noite
2009-09-12 04:47:43 thumbs boa noite!
2009-09-12 04:48:03 FurnaceBoy :)
2009-09-12 04:48:22 thumbs buenas noches!
2009-09-12 04:48:38 FurnaceBoy si
2009-09-12 04:48:43 FurnaceBoy FurnaceBoy disappears
2009-09-12 04:53:37 frontrow1 so if i want to convert my tables to
innodb from myisam, all i have to do is alter table..engine=..? but will
that mess up any of my scripts which access the data?
2009-09-12 04:55:07 frontrow1 so if i want to convert my tables to
innodb from myisam, all i have to do is alter table..engine=..? but will
that mess up any of my scripts which access the data?
2009-09-12 04:55:47 frontrow1 myisam is hurtin performance so bad..table
locks are killing
2009-09-12 04:55:58 mikegriffin frontrow1: there are a few things that
innodb does not support like fulltext indexes
2009-09-12 04:56:11 mikegriffin frontrow1: your app code does not change
when the engine does
2009-09-12 04:56:17 frontrow1 that shudnt be a problem inmy case
2009-09-12 04:56:34 frontrow1 well i read about fopreign keys and
such..ive worked with foreign keys once before in oracle
2009-09-12 04:56:57 mikegriffin you should bump up your
myisam_sort_buffer_size and innodb_buffer_pool_size prior to the alter if
possible
2009-09-12 04:57:05 mikegriffin frontrow1: they are optional
2009-09-12 04:58:02 frontrow1 mikegriffin: i ahve a table over 10 mil
records, and its been going extremely slow with any kind of query..shud i
generally see an improvement on this by switching to innodb?
2009-09-12 04:58:18 frontrow1 indexes at this point are getting too big
to be a major facotr
2009-09-12 04:58:33 mikegriffin idfma
2009-09-12 04:58:45 mikegriffin inno will help with locking issues
2009-09-12 04:59:31 frontrow1 the_wench/mikegriffin: what info shud i
provide? i would def like to make a wise choice on this seeing as how it
could take several hours for the switch
2009-09-12 05:07:51 Bookman frontrow1: you have 10 million records?
10,000,000?
2009-09-12 05:09:25 frontrow1 Bookman: 10mil +
2009-09-12 05:09:33 frontrow1 i dont have an updated count, but there are
way more
2009-09-12 05:10:05 Bookman Wow, that is a big dataset. How many fields
in each record?
2009-09-12 05:10:29 Bookman what is the key?
2009-09-12 05:11:05 frontrow1 Bookman: primary key is made up of a bigint
and a varchar
2009-09-12 05:11:11 frontrow1 15 fields
2009-09-12 05:11:46 Bookman What is the size of the db?
2009-09-12 05:12:20 frontrow1 ~500mb gzipped
2009-09-12 05:12:32 frontrow1 and growing
2009-09-12 05:12:41 Bookman Wow....researching here...
2009-09-12 05:13:05 frontrow1 im prety sure i would benefit from innodb,
i just dont want to waste time doing it, if im wrong
2009-09-12 05:13:20 frontrow1 i have zero experience with different db
engines
2009-09-12 05:13:23 Bookman Experiment locally. No big loss
2009-09-12 05:13:30 Bookman I do that all the tiem
2009-09-12 05:13:34 Bookman *time
2009-09-12 05:13:59 Bookman Do you really need a varchar as a primary
key?
2009-09-12 05:14:05 mikegriffin (srsly)
2009-09-12 05:14:09 mikegriffin mikegriffin wonders off
2009-09-12 05:14:17 mikegriffin wander? whatver
2009-09-12 05:14:19 frontrow1 Bookman: its an email address
2009-09-12 05:14:34 djahandarie frontrow1, what sort of system is this?
OLTP? Archives? Or what
2009-09-12 05:14:46 frontrow1 djahandarie: OLTP
2009-09-12 05:15:17 djahandarie Okay. Is it read-only ? read-mostly?
2009-09-12 05:15:49 frontrow1 no, lots and lots of inserts per day..lots
and lots of selects
2009-09-12 05:16:11 djahandarie That made the situation so much clearer
2009-09-12 05:17:10 frontrow1 djahandarie: great..what do you think
2009-09-12 05:17:44 djahandarie I think a lot
2009-09-12 05:19:19 djahandarie frontrow1, well, since you're not being
very clear with me, I can't exactly provide a lot of help. Is this one
table which you are altering "a lot"?
2009-09-12 05:19:20 frontrow1 djahandarie: so whats the verdict..stick
with myisam, or switch toi innodb
2009-09-12 05:19:47 djahandarie frontrow1, MyISAM locks the whole table
so if it's just one table which you are querying frequently switching to
innodb would help
2009-09-12 05:19:54 djahandarie Although you'd want to cut down on the
primary key size if you did that
2009-09-12 05:20:09 frontrow1 djahandarie: well there is a temp table,
where data first gets inserted...it gets scrubbed then passed to a good
table if its good, and a bad table if its bad data
2009-09-12 05:20:29 djahandarie And I imagine you only read frequently
from the good table?
2009-09-12 05:20:36 frontrow1 yes
2009-09-12 05:21:06 djahandarie Do you have any sort of numbers as to how
many queries you are getting/type of queries?
2009-09-12 05:22:18 frontrow1 i would say around 200 inserts per day on
the good table(bulk inserts that are selecting up to 45000 rows from the
temp table)
2009-09-12 05:22:45 frontrow1 around 300 selects(for large datasets)
2009-09-12 05:23:15 frontrow1 morning is heavy traffic, so each insert on
the good table carries the full 45000
2009-09-12 05:23:24 djahandarie That doesn't sound like a particularly
high-concurrency situation. I'd say stick with MyISAM
2009-09-12 05:23:34 djahandarie Look into replication if you want to
increase performance
2009-09-12 05:23:39 frontrow1 im getting terribly slow speeds on selects
2009-09-12 05:24:01 djahandarie frontrow1, well, are your tables properly
indexed?
2009-09-12 05:24:01 frontrow1 and table locking brings it to a halt when
several selects are going on at once
2009-09-12 05:24:19 djahandarie frontrow1, how would 300 selects over 24
hours be running at the same time?
2009-09-12 05:24:35 frontrow1 because they are part of different scripts
that analyse the data
2009-09-12 05:24:45 djahandarie So it runs 300 selects at once?
2009-09-12 05:24:50 frontrow1 no
2009-09-12 05:25:08 frontrow1 every 10 minutes there is a bulk insett
into the good table
2009-09-12 05:25:21 frontrow1 and every half hour there is a select
2009-09-12 05:25:26 frontrow1 but different scripts will overlap
2009-09-12 05:25:47 frontrow1 so script A selects 150,000 records,
meanwhile script B is trying to select 300,000 records
2009-09-12 05:26:06 frontrow1 and they are all time sensitive selects
2009-09-12 05:26:13 djahandarie frontrow1, are your tables properly
indexed?
2009-09-12 05:26:21 djahandarie Tablescanning a table that big would be
deadly
2009-09-12 05:26:48 frontrow1 what do you mean by properly?
2009-09-12 05:26:54 frontrow1 hold on ill pastebin the structure
2009-09-12 05:27:03 djahandarie frontrow1, don't, I'm not that interested
2009-09-12 05:27:17 frontrow1 ok
2009-09-12 05:27:22 djahandarie frontrow1, do the EXPLAINs show that you
are not filesorting?
2009-09-12 05:29:23 frontrow1 i should check that
2009-09-12 05:29:49 djahandarie Yes, you should, before considering
anything else.
2009-09-12 05:30:05 frontrow1 and what should i look for exactly
2009-09-12 05:30:10 frontrow1 to see that i am not filesorting?
2009-09-12 05:30:17 djahandarie
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html
2009-09-12 05:30:18 frontrow1 and that indexes are being taken advantage
of
2009-09-12 05:31:06 djahandarie If you want something more detailed, go
get the book High Performance MySQL
2009-09-12 05:31:17 frontrow1 ok thanks for the help
2009-09-12 05:31:22 djahandarie And I'm going to bed, so good night
2009-09-12 05:31:24 djahandarie No prob
2009-09-12 05:31:29 djahandarie Bye
2009-09-12 05:34:02 frontrow1 hmm, maybe someone mightknow..if a myisam
table is locked due to an update or select..do concurrent inserts which
are in queue occupy the web client? i.e. does a webusers page not load
since the query is in queue?
2009-09-12 06:12:12 rud Hi, what would be the method of choice to install
mysql5x server and client on macosx ? knowing i don't use any
port/package system (yet) .. ?
2009-09-12 07:07:30 manlymatt83 Can anyone recommend a good mysql naming
scheme? One main thing I'm wondering is - do people put the table name
in their column names?
2009-09-12 07:07:40 frontrow1 no
2009-09-12 07:08:04 frontrow1 column names can be accessed by using
tableName.columnName
2009-09-12 07:08:19 frontrow1 but naming your columns like that would be
very annoying and unneccessary
2009-09-12 07:08:29 manlymatt83 understood, and agreed - normally how i
do things, but I'm working on a new project where I want to do things
"right". I always get stuck when I'm naming reference tables.
2009-09-12 07:08:38 frontrow1 its all a matter of preference
2009-09-12 07:09:13 manlymatt83 create table user (....);     and then:
create table gender. Would I call the gender.id column gender_id inside
the user table?
2009-09-12 07:11:36 frontrow1 well in that sense you could, i usually do
that for ids to keep mysql from referencing them table.id..but i wudnt
name every single column like that
2009-09-12 07:13:05 manlymatt83 But then what happens when gender also
has a reference table for it?
2009-09-12 07:13:07 manlymatt83 what do you call that?
2009-09-12 07:13:11 manlymatt83 gender_<table>
2009-09-12 07:13:17 manlymatt83 or just table name and make it
standalone?
2009-09-12 07:15:20 frontrow1 i would do standalone
2009-09-12 07:15:29 frontrow1 the thing is there isnt a "right" naming
convention
2009-09-12 07:15:30 frontrow1 its what works
2009-09-12 07:18:24 manlymatt83 gotcha
2009-09-12 07:31:39 manlymatt83 thanks
2009-09-12 07:33:13 gtowey sql naming
2009-09-12 07:33:24 gtowey manlymatt83 ^^
2009-09-12 08:31:53 silv3r_m00n hi
2009-09-12 08:33:51 silv3r_m00n how can I get the year part of a time
field in the format 09   ?
2009-09-12 08:51:13 ekkis hei everyone, I'm trying to restore a database
dumped from an older version of mysql and it comes with some lines like -
------------------- which seem to break... what's up with that?
2009-09-12 08:52:00 Tonisius line breaks are normal in most scripts
2009-09-12 08:52:06 Tonisius it's good to break things down into multiple
lines
2009-09-12 08:52:24 silv3r_m00n how can I get the year part of a time
field in the format 09   ? YEAR(time) gives full year number
2009-09-12 08:52:56 Tonisius silv3r_m00n: tried to subtract the string?
2009-09-12 08:53:23 silv3r_m00n means - 2000 ?
2009-09-12 08:53:29 Tonisius ...
2009-09-12 08:53:31 Tonisius that's a number
2009-09-12 08:53:37 Tonisius sure why not, what ever gets you the result
you want.
2009-09-12 08:53:40 ekkis Tonisius: I guess the must have changed that
since 3.23
2009-09-12 08:53:42 ekkis grr
2009-09-12 08:56:03 Tonisius ekkis: could depend on your mysqldump
2009-09-12 08:56:22 ekkis how do you mean?
2009-09-12 08:57:16 silv3r_m00n can DATE_FORMAT be used with a timestamp
field ?
2009-09-12 08:58:07 Tonisius silv3r_m00n: once you convert it to a date
data type
2009-09-12 08:58:16 silv3r_m00n how ?
2009-09-12 08:58:20 Tonisius with date?
2009-09-12 08:58:26 Tonisius date(timestamp); ?
2009-09-12 08:58:38 Tonisius silv3r_m00n: why not google all this?
2009-09-12 08:58:48 silv3r_m00n ok
2009-09-12 09:10:58 coldfire22x is it possible to determine the size of
the indexes in a myisam table?
2009-09-12 09:11:21 Tonisius coldfire22x: in what way? size in bytes?
memory space? eh?
2009-09-12 09:11:33 Tonisius you can just get the row count, and multiply
by the type and length of th efield
2009-09-12 09:11:53 coldfire22x Tonisius: size in bytes would be nice ...
trying to compare the size of various packed indexes
2009-09-12 09:15:44 Tonisius not quite sure how to do it
2009-09-12 09:15:46 Tonisius good question though
2009-09-12 09:15:52 Tonisius I know it can be done, I just can't recall
how
2009-09-12 09:16:19 GNeRaL how to completely remove mysql, i want to
fresh installation with no old data?
2009-09-12 09:16:28 Tonisius GNeRaL: rm -rf /
2009-09-12 09:16:47 GNeRaL is it impossibly?
2009-09-12 09:17:13 Tonisius it's not impossible, you can install 30
versions of mysql, and it uses different data
2009-09-12 09:17:20 Tonisius you have to tell it what config to load, and
where to store the data
2009-09-12 09:18:50 GNeRaL i using centos 5.3 32b release, i installed it
with yum install mysql* with default settings.
2009-09-12 09:19:02 Tonisius GNeRaL: ask in centos channel
2009-09-12 09:52:09 salle GNeRaL: If you want to remove the datadir just
go and rm -rf it
2009-09-12 09:52:53 salle GNeRaL: Install/deinstall of MySQL doesn't
touch the existing datadir if you have one
2009-09-12 09:53:42 salle coldfire22x: SHOW TABLE STATUS LIKE
"your_table"; and read
2009-09-12 09:54:11 salle coldfire22x: Or just go to the databse
directory and ls -l your_table.MYI
2009-09-12 09:54:38 salle coldfire22x: You can not get the size of
individual index though
2009-09-12 10:07:08 coldfire22x salle: the individual index is what i was
looking for ... knew about the rest
2009-09-12 10:07:25 coldfire22x i guess i'll just have to dup some tables
and compare their respective indexes
2009-09-12 10:10:28 salle coldfire22x: What are you comparing?
2009-09-12 10:17:53 dwarder what is the best way to count how many
entries are on each month if each entry have got date field?
2009-09-12 10:18:16 _3^3 unfortunately, best way doesn't exist
2009-09-12 10:18:22 _3^3 you can do GROUP BY MONTH(date)
2009-09-12 10:18:30 _3^3 YEAR(date),MONTH(date) that is
2009-09-12 10:18:54 _3^3 (best way would be if mysql would recognize
serial nature of grouping and wouldn't do sorting ;-)
2009-09-12 10:25:09 salle _3^3: Serial nature of grouping? "wouldn't do
sorting"?
2009-09-12 10:25:26 salle _3^3: You can always suppress the implicit
sorting if you wish
2009-09-12 10:25:34 _3^3 salle: well, you can't, if you do GROUP BY
2009-09-12 10:26:10 _3^3 if mysql would recognize that it can still do
index scan for GROUP BY year,month ...
2009-09-12 10:26:13 _3^3 actually, it can't
2009-09-12 10:26:18 _3^3 I just realized;-)
2009-09-12 10:26:40 _3^3 it could do smaller filesorts for each grouping!
2009-09-12 10:26:45 _3^3 I wonder what is faster
2009-09-12 10:53:39 silv3r_m00n smallint(6) means an integer which has 6
digits ?
2009-09-12 11:16:31 bigfoot- silv3r_m00n:
http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html
2009-09-12 11:22:35 salle _3^3: You can completely avoid sorting with
GROUP BY x ORDER BY NULL
2009-09-12 11:22:58 salle !m silv3r_m00n zerofill
2009-09-12 11:23:09 salle !tell silv3r_m00n about zerofill
2009-09-12 11:27:54 dwarder _3^3: select * from table group by
YEAR(date),MONTH(date); Shows 1 entry per month, how can see how many
entries was in each month?
2009-09-12 11:28:17 dwarder what to count?
2009-09-12 11:31:21 adaptr dwarder: add a COUNT(*)
2009-09-12 11:33:13 dwarder adaptr: thanks
2009-09-12 11:36:31 salle dwarder: SELECT YEAR(date),MONTH(date),
COUNT(*) FROM tbl GROUP BY YEAR(date),MONTH(date);
2009-09-12 11:39:29 adaptr mysql won't stop you from selecting non-
aggregates, but you still shouldn't
2009-09-12 11:41:22 salle adaptr: ... unless you know what exactly are
you doing which is rare in this case
2009-09-12 11:42:14 adaptr well, ANSI strictly forbids it, and I cannot
think offhand of any good reason why you would want to return an
indeterminate value for any non-aggregate column
2009-09-12 11:45:08 salle adaptr: subqueries is one reason
2009-09-12 11:45:27 salle adaptr: getting COUNT(*) only
2009-09-12 11:46:12 adaptr salle: a subquery acts like an aggregate in
this respect, since it does not return indeterminate values, and COUNT
is, of course, an aggregate
2009-09-12 11:46:20 dwarder salle: can i see month name instead of 1 2
... somehow?
2009-09-12 11:46:28 adaptr dwarder: JOIN it with month names
2009-09-12 11:46:34 salle adaptr: MySQL approach always used to be "if
you are certain that want to shoot yourself in your left foot we won't
stop you"
2009-09-12 11:46:35 dwarder adaptr: thanks
2009-09-12 11:46:45 adaptr dwarder: or fiddle with the date and time
functions to extract names
2009-09-12 11:46:47 salle dwarder: Read more about date/time functions
2009-09-12 11:46:57 salle !man datetime
2009-09-12 11:47:04 adaptr !man date and
2009-09-12 11:47:24 salle dwarder:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
2009-09-12 11:48:01 salle dwarder: MONTHNAME() or DATE_FORMAT() can do
the job
2009-09-12 11:54:05 dwarder DATE_FORMAT(data, '%M') did the job
2009-09-12 11:54:30 dwarder thank you
2009-09-12 11:54:38 dwarder Tonisius: botsnack
2009-09-12 11:54:45 adaptr heh
2009-09-12 11:54:47 dwarder ohh
2009-09-12 11:54:49 adaptr tabfail
2009-09-12 11:54:51 dwarder sorry
2009-09-12 11:54:59 adaptr archivist: botsnack
2009-09-12 11:56:09 dwarder the_wench: botsnack
2009-09-12 11:56:21 dwarder !man date and
2009-09-12 11:56:33 dwarder !botsnack
2009-09-12 11:56:40 dwarder ..
2009-09-12 11:58:36 Tonisius !bootysnack
2009-09-12 11:58:43 Tonisius =D
2009-09-12 12:02:00 dwarder do anyone visualize data somehow, with php
and flash xml?
2009-09-12 12:02:22 adaptr I stare at it real hard
2009-09-12 12:02:31 dwarder :)
2009-09-12 12:02:33 adaptr and it suddenly becomes so clear
2009-09-12 12:04:07 dwarder something like http://flare.prefuse.org/
2009-09-12 12:30:46 YiamiYo hi
2009-09-12 12:31:15 YiamiYo is there any way to import a csv file at my
mysql database?
2009-09-12 12:31:54 _3^3 LOAD DATA
2009-09-12 12:32:25 YiamiYo 1 sec...
2009-09-12 12:33:03 YiamiYo here: http://pastebin.org/17376
2009-09-12 12:33:07 YiamiYo the csv is like this
2009-09-12 12:33:33 YiamiYo there is a field that is enclosed by "" and
contains comma..
2009-09-12 12:33:47 YiamiYo but all other fields aren't enclosed by ""
2009-09-12 12:34:10 mene hey. Can anyone help me find out, what's wrong
with this: http://paste.bradleygill.com/index.php?paste_id=18863 ORDER
BY seems not to work :(
2009-09-12 12:41:49 YiamiYo anyone?
2009-09-12 12:48:10 devil2005 SELECT * FROM `devil2005saccounts` WHERE
`account` LIKE '%Netherland%' AND `account` LIKE '%France%' ORDER BY `id`
ASC LIMIT 30 , should return all the account from france and netherlands
but doesnt
2009-09-12 12:48:15 devil2005 the syntax is crrect
2009-09-12 12:48:22 devil2005 and the data exists
2009-09-12 12:48:34 devil2005 but doesnt return with that query
2009-09-12 12:50:13 pezia show an examle row where it sould return
values.
2009-09-12 12:51:08 devil2005 SELECT * FROM `devil2005saccounts` WHERE
`account` LIKE '%Netherlands%' ORDER BY `id` ASC LIMIT 30
2009-09-12 12:51:15 devil2005 returns netherland accounts
2009-09-12 12:51:31 devil2005 and soe does that if i change netherlands
to France
2009-09-12 12:51:41 devil2005 but
2009-09-12 12:51:41 devil2005 SELECT * FROM `devil2005saccounts` WHERE
`account` LIKE '%Netherland%' AND `account` LIKE '%France%' ORDER BY `id`
ASC LIMIT 30
2009-09-12 12:51:45 devil2005 returns nothing
2009-09-12 12:53:01 YiamiYo use OR instead of AND
2009-09-12 12:53:54 devil2005 ahhh , that worked
2009-09-12 12:53:57 YiamiYo :)
2009-09-12 12:53:57 devil2005 thank you
2009-09-12 12:54:18 pezia but that is not france and netherlands :)
2009-09-12 12:54:26 YiamiYo xd
2009-09-12 12:54:54 devil2005 yea, this returned both frence and dutch
accounts
2009-09-12 12:54:58 YiamiYo can anyone help me now?:)
2009-09-12 12:56:29 devil2005 you said you used " , are you using
backticks for your tables?
2009-09-12 12:57:00 devil2005 althugh you can use " , if data contained
has " included it screws around with the syntax
2009-09-12 12:57:39 devil2005 e.g select * from `table` where `user` =
"crazy_guy"
2009-09-12 12:57:45 devil2005 instead of
2009-09-12 12:58:02 devil2005 select * from 'table' where 'user' =
"crazy_guy"
2009-09-12 12:59:05 YiamiYo are you talkin to me?
2009-09-12 12:59:09 YiamiYo talking*
2009-09-12 12:59:15 devil2005 yea
2009-09-12 12:59:30 YiamiYo then you didn't see the problem...xd
2009-09-12 12:59:56 YiamiYo i have a csv that is like this:
http://pastebin.org/17376
2009-09-12 13:00:19 YiamiYo and i want to load it into my mysql database
2009-09-12 13:00:53 devil2005 do you have PHPMyAdmin?
2009-09-12 13:01:05 YiamiYo the problem is that some fields are enclosed
by "" and contain comma (,)
2009-09-12 13:01:20 YiamiYo but all the other fields aren't enclosed by
""
2009-09-12 13:01:44 YiamiYo devil2005, no
2009-09-12 13:01:48 pezia so this is some excel export. i like that :)
2009-09-12 13:02:08 YiamiYo excel exports like this?lol
2009-09-12 13:02:21 pezia like this or worse.
2009-09-12 13:02:31 pezia doubles the ""s and so on..
2009-09-12 13:02:32 devil2005 download phpmyadmin and run it on your
server
2009-09-12 13:02:33 devil2005
http://img38.imageshack.us/img38/9438/clipboard1252756048093.png
2009-09-12 13:02:38 devil2005 is what it will look like
2009-09-12 13:03:24 YiamiYo i cannot do it elsewhere?
2009-09-12 13:03:42 YiamiYo i prefer to avoid phpmyadmin, navicat etc...
2009-09-12 13:04:54 pezia maybe you should try to preprocess the data if
your mysql client can't handle this
2009-09-12 13:04:56 Naktibalda !m YiamiYo load data
2009-09-12 13:05:44 YiamiYo pezia, to enclose everything with ""?
2009-09-12 13:06:12 pezia YiamiYo: or generate SQL insertions based on
the data. it is your choice
2009-09-12 13:06:44 devil2005 you could, but i wouldnt be able to help
you, if i were you i would install it temporarrly atleaft for the import.
after you import it, it will tell you (phpmyadmin) what command it issued
so you wont have to use phpmyadmin again
2009-09-12 13:07:00 YiamiYo if i install excel and import them there can
i export them all enclosed by ""?
2009-09-12 13:07:17 pezia exels exporting is limited
2009-09-12 13:07:33 YiamiYo M$ sucks...
2009-09-12 13:07:40 pezia _very_ limited. :)
2009-09-12 13:08:04 YiamiYo what about openoffice?
2009-09-12 13:08:28 YiamiYo can i import them there and export them with
""?
2009-09-12 13:09:20 devil2005 you can use phpmyadmin to import the csv
and export as csv, give it a try, then later you can look at the command
it issued and you wont need phpmyadmin to import/export your csv file
again
2009-09-12 13:12:58 YiamiYo kk....where to download?
2009-09-12 13:14:11 devil2005
http://www.phpmyadmin.net/home_page/downloads.php
2009-09-12 13:14:26 YiamiYo wow...
2009-09-12 13:14:30 YiamiYo you are good :)
2009-09-12 13:14:46 YiamiYo i bet that others would just tell me to
google...xd
2009-09-12 13:15:15 YiamiYo i just lost 20$ because of google...:/
2009-09-12 13:16:23 devil2005 you just lost $20 , how?
2009-09-12 13:18:16 YiamiYo someone asked from me to make him a script to
forward all labeled emails from his gmail account to another email
account but google doesn't let you click anywhere...:/
2009-09-12 13:19:35 SilentPenguin hey, is there anyway i can query mysql
with a string "my foo bar" and it will return a result matching "foo bar"
above a match of "bar"? but without containing foo ting bar, ie the
string remains unbroken
2009-09-12 13:19:36 YiamiYo i was tryig to do it for over 12 hours...but
nothing...:/
2009-09-12 13:19:52 SilentPenguin so the result has to be contained
within the query string
2009-09-12 13:19:55 devil2005 glad to help you out
2009-09-12 13:20:35 SilentPenguin kinda like %something% but in reverse
2009-09-12 13:21:53 devil2005 silent pengin: SELECT * FROM
`devil2005saccounts` WHERE `account` LIKE '%bar%' ORDER BY `id` ASC LIMIT
30
2009-09-12 13:22:49 Ceeram if i have a db field which holds a value
between 1 and 999 how would i get the not used numbers ?
2009-09-12 13:22:54 Ceeram is that possible with just 1 sql query?
2009-09-12 13:24:02 devil2005 what do you mean , how would you get the
"not used numbers"
2009-09-12 13:24:14 SilentPenguin devil2005, not quite what i meant
2009-09-12 13:24:28 Ceeram numbers that are not found in any row
2009-09-12 13:24:54 SilentPenguin i ment the LIKE contains a string, and
i want anything that is within that string
2009-09-12 13:25:22 archivist !tell Ceeram about a not in b
2009-09-12 13:25:26 Ceeram so lets say numbers 55 and 99 are not found in
a single row, how would i get the results to hold the numbers 55 and 99
2009-09-12 13:25:46 Ceeram i have no b table to join
2009-09-12 13:25:47 YiamiYo SilentPenguin, something like
'something%somethingelse'?
2009-09-12 13:25:57 SilentPenguin say i had an entry Lemons, and i
provided an entry query where LIKE '%my lemons are green%" i would want
that "lemons" entry
2009-09-12 13:26:14 SilentPenguin like an index, only the string must be
complete
2009-09-12 13:26:52 SilentPenguin since an index wouldn't care about word
order, i care about the word order
2009-09-12 13:27:15 Ceeram the field can have any value between 1 and 999
and every number can me used more then once, just wnat to know what
numbers arent used
2009-09-12 13:27:32 adaptr SilentPenguin: use a FTI
2009-09-12 13:27:45 SilentPenguin SilentPenguin looks it up, thanks
2009-09-12 13:28:22 Ceeram the number does not refer to an id, and is no
foreign key, its just a number
2009-09-12 13:28:31 SilentPenguin ah, full text index, how i missed that
is beyond me :P
2009-09-12 13:28:47 archivist Ceeram, define unused!
2009-09-12 13:28:58 Ceeram not found in any row
2009-09-12 13:29:08 archivist that is not unused
2009-09-12 13:29:20 archivist you are looking for gaps
2009-09-12 13:29:24 adaptr Ceeram: you want to return values that are not
present in the database ?
2009-09-12 13:29:28 adaptr weird
2009-09-12 13:29:30 Ceeram right
2009-09-12 13:29:42 adaptr well, here's a stunner: the database won't
tell you
2009-09-12 13:29:51 adaptr because they're NOT IN THE DATABASE
2009-09-12 13:29:54 devil2005 lol
2009-09-12 13:30:04 Ceeram i know they are not there
2009-09-12 13:30:24 YiamiYo if he makes another table with all 1->999 and
compare them??xd
2009-09-12 13:30:30 devil2005 i think in order for that to work you would
need to send 999 queris to the database
2009-09-12 13:30:33 devil2005 to check
2009-09-12 13:30:37 devil2005 i would hate to be your admin
2009-09-12 13:30:37 adaptr Ceeram: I require you to immediately pay me
$5000
2009-09-12 13:30:50 adaptr because I DID NOT EARN IT
2009-09-12 13:31:18 adaptr anyway, yes
2009-09-12 13:31:34 archivist common queries
2009-09-12 13:31:35 adaptr do an a not in b with a table that contains 1
thru 999
2009-09-12 13:31:45 archivist see above link Ceeram
2009-09-12 13:31:51 adaptr or just ... keep track of the numbers used
2009-09-12 13:31:58 adaptr in a table containing 1 thru 999
2009-09-12 13:32:00 Ceeram yes ofcourse could create another table, but
since db can do calculations and some logic i hoped it would be possible
something like SELECT field BETWEEN(1,999) WHERE field NOT BETWEEN(1,999)
2009-09-12 13:32:30 archivist !tell Ceeram about common queries
2009-09-12 13:32:36 adaptr a database is capable of some calculations and
IMMENSE amounts of logic, but what you're asking is not "logic"
2009-09-12 13:32:38 Ceeram im there now
2009-09-12 13:33:23 adaptr and if you're complaining about creating a
shitpoor small table that instantly solves your "problem", go elsewhere
2009-09-12 13:33:48 Ceeram im not complaining i just hoped there was an
alternative option
2009-09-12 13:33:59 adaptr because ?
2009-09-12 13:34:13 Ceeram just because
2009-09-12 13:34:34 Ceeram ok thx for the help i got my answer: it's not
possible thats all i needed to know
2009-09-12 13:34:38 SilentPenguin adaptr, thanks, looks perfect :)
2009-09-12 13:40:23 Ceeram
http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#71
2009-09-12 13:46:14 salle Ceeram: What's the problem with creating small
temporary table with all numbers you need and join against it?
2009-09-12 13:46:31 adaptr salle: "because"
2009-09-12 13:46:42 salle adaptr: "Because the sky is blue" ")
2009-09-12 13:47:18 Ceeram SELECT t1.extern +1 AS Missing FROM groups AS
t1 LEFT JOIN groups AS t2 ON t1.extern +1 = t2.extern WHERE t2.extern IS
NULL ORDER BY t1.extern
2009-09-12 13:47:27 Ceeram thank you
2009-09-12 13:49:05 salle the_wench: hi asl pls pic ?
2009-09-12 13:49:29 YiamiYo devil2005, i can see only SQL as format of
imported file into my phpMyAdmin...
2009-09-12 13:50:13 devil2005 there is a redio button toward to bottom
saying csv
2009-09-12 13:50:18 devil2005 radio*
2009-09-12 13:50:30 YiamiYo i can see only the radio button for SQL...:/
2009-09-12 13:50:58 adaptr can you take this to the proper channel,
please ?
2009-09-12 13:51:28 devil2005 there shouldnt , try #phpmyadmin , sorry to
pass the book
2009-09-12 13:51:43 YiamiYo adaptr, i'm trying to import a csv into a
mysql table...
2009-09-12 13:52:14 adaptr YiamiYo: don't care, this is not #phpmyadmin
2009-09-12 13:52:15 salle YiamiYo: LOAD DATE INFLIE
2009-09-12 13:52:39 salle YiamiYo: if you ask for MySQL solution. For
phpmyadmin take a look at the /topic
2009-09-12 13:53:03 YiamiYo salle, the problem is that some fields ar
eenclosed by "" and contain comma (,) but all the others aren't enclosed
by ""///
2009-09-12 13:53:33 salle YiamiYo: Fix csv file then
2009-09-12 13:53:40 YiamiYo how?
2009-09-12 13:53:53 salle YiamiYo: sed can do it for example
2009-09-12 13:54:15 YiamiYo sed?
2009-09-12 13:54:50 salle YiamiYo: or you favorite scripting language or
your favorite text editor none of them MySQL related
2009-09-12 13:56:45 lopz gm ;)
2009-09-12 14:33:34 pianolender Greetings to everybody!
2009-09-12 14:33:42 pianolender Does anyone ever try to connect to a
mysql server on the remote host and then get the following
2009-09-12 14:33:46 pianolender ERROR 2003 (HY000): Can't connect to
MySQL server on '10.0.94.17' (111)
2009-09-12 14:33:49 pianolender error?
2009-09-12 14:35:53 pianolender it means, as I think, that there's some
configuration option that prevents the server from accepting remote
connections, so the question is where can I find it?
2009-09-12 14:36:06 archivist remote
2009-09-12 14:36:28 archivist !perror 111
2009-09-12 15:07:25 magowiz Hi , is there a way should I set the max key
length option in mysql ?
2009-09-12 15:18:32 pianolender archivist: this "grant all priveliges"
looks rather a strange SQL query, inducing some thoughts about sci-fi
movies, but it worked! thank you
2009-09-12 15:30:02 SilentPenguin fulltext indexing wasn't what i want,
unless there is something in the docs i missed
2009-09-12 15:31:30 SilentPenguin i care about the word structure, ie a
string inside the query "my long string of words" would match a table
entry "string of words" but not "long words"
2009-09-12 15:43:01 salle SilentPenguin: You are doing something wrong
then
2009-09-12 15:43:29 SilentPenguin howso?
2009-09-12 15:44:53 SilentPenguin i have an entry in my table for
testing, which reads "over twenty" and i want to get that entry when a
query string contains "something over twenty grams" for example
2009-09-12 15:45:36 SilentPenguin fulltext doesnt seem to care about the
word order, and will return a result when the query contains just "over"
or "twenty"
2009-09-12 15:48:44 FurnaceBoy SilentPenguin: LIKE '%over twenty%'
2009-09-12 15:49:12 SilentPenguin no, not that way round, i want it the
other way round
2009-09-12 15:49:29 FurnaceBoy SilentPenguin: aha
2009-09-12 15:49:48 FurnaceBoy SilentPenguin: $param LIKE
CONCAT('%',col,'%')
2009-09-12 15:50:21 FurnaceBoy SilentPenguin: no claim to efficiency. if
FTS isn't your cup of hemlock, try Sphinx, Lucene
2009-09-12 15:50:57 FurnaceBoy SilentPenguin: or roll your own. searching
is only efficient when the data structure reflects your needs
2009-09-12 15:51:20 SilentPenguin well yeah, its for a bot, and the
"search engine" style searches on text are starting to piss everyone off
:P
2009-09-12 15:51:34 FurnaceBoy SilentPenguin: strictly speaking, the
above expression solves your problem.
2009-09-12 15:52:20 SilentPenguin especially when someone says something
entirely different and it decides to say something irrelevant where
someone has used a common word like "people" because they dont know what
they are doing :P
2009-09-12 15:52:32 FurnaceBoy SilentPenguin: right, at a minimum you
need scoring
2009-09-12 15:52:49 FurnaceBoy SilentPenguin: but LIKE will give you the
subphrase match
2009-09-12 15:53:42 SilentPenguin yeah, i couldn't find a consistent way
to make sure the scoring worked effectively, since its aimed at large
bodies of text rather than short strings
2009-09-12 15:53:55 FurnaceBoy SilentPenguin: i mean scoring in general.
i don't know about fts
2009-09-12 15:54:02 SilentPenguin ah
2009-09-12 15:56:11 SilentPenguin awesome, yeah, concat works fine
thanks, its a fix for the moment, i can always find a more efficient way
if the bot starts to get too laggy
2009-09-12 15:56:20 FurnaceBoy yw
2009-09-12 15:56:43 FurnaceBoy SilentPenguin: some lag is actually good
for a bot. instant responses are too non-human-like :)
2009-09-12 15:57:10 SilentPenguin :D
2009-09-12 15:58:00 SilentPenguin its a clone of bucket from XKCD, i
couldn't find the line where he uses a similar if not identical
expression, plus the fact i want to learn, not copy :)
2009-09-12 15:59:12 SilentPenguin i forget sometimes how flexable mysql
can be, because it shoehorns you into the state of thinking it isn't
flexable hehe
2009-09-12 16:02:46 CorticalStack mysql doesn't draw conclusions, people
do. ;^)
2009-09-12 16:10:42 adaptr mysql doesn't draw ERDs either, but people
really should
2009-09-12 16:46:06 mezod hi i want to take the account which has the
oldest date and has_host i did: http://pastebin.com/m2ea6b1df , it says
im using bad a group function, what im doing wrong?
2009-09-12 16:52:25 rand0mbits hey, say i have 2 fields as a primary key
in one table. if i want to bring an FK to another table, is it better to
bring these two, or to maybe create an autoincrement in table 1 and bring
that over?
2009-09-12 16:53:28 FurnaceBoy depends on whethr you need an artificial
key in child table anyway. usually the former
2009-09-12 16:55:08 rand0mbits does it create a lot more overhead that
i'm bringing two fields over, or it doesn't matter because it's a FK?
2009-09-12 16:55:33 FurnaceBoy rand0mbits: you probably won't be able to
measure it. don't micro-optimise
2009-09-12 16:56:08 rand0mbits alright. thanks :)
2009-09-12 16:56:08 FurnaceBoy it's certainly not a reason to add an
artificial key unless you have measurements proving it's a necessary and
sufficient win
2009-09-12 16:56:23 FurnaceBoy rand0mbits: which implies that you have a
performance problem to solve
2009-09-12 16:57:32 FurnaceBoy rand0mbits: also, redundant keys can be an
integrity issue
2009-09-12 17:17:18 n8w hey,how do i list only minimul value of a sum?
2009-09-12 17:18:24 n8w ive got a sum(3 values) n i need to show only the
smallest one....
2009-09-12 17:18:29 n8w anyone?
2009-09-12 17:24:13 FurnaceBoy n8w: can you paste your query
2009-09-12 17:24:13 brsoft hello. Im storing inputs in text fields such
as &#50937; which is a korean symbol
2009-09-12 17:24:26 brsoft should I store the symbol as it is or that
info?
2009-09-12 17:24:37 FurnaceBoy brsoft: that's html
2009-09-12 17:24:39 brsoft whats better for optimizing?
2009-09-12 17:24:43 FurnaceBoy brsoft: neither
2009-09-12 17:24:54 FurnaceBoy brsoft: if you are storing html, store
html, otherwise store text
2009-09-12 17:25:13 FurnaceBoy brsoft: you can store unicode
2009-09-12 17:25:28 brsoft better than the korean symbol?
2009-09-12 17:25:38 FurnaceBoy brsoft: what you pasted is html
2009-09-12 17:25:49 FurnaceBoy brsoft: it's up to you
2009-09-12 17:26:06 FurnaceBoy brsoft: obviously unicode is more compact
than a pile of html entities, but maybe you WANT html
2009-09-12 17:26:07 brsoft well I can store unicode. but is better to
store unicode or the symbol in terms of mysql obtimizing?
2009-09-12 17:26:17 FurnaceBoy brsoft: question makes no sense
2009-09-12 17:26:27 FurnaceBoy brsoft: unicode will be a tiny bit
smaller.
2009-09-12 17:26:36 FurnaceBoy brsoft: what's important is that you store
what you need to store.
2009-09-12 17:26:55 brsoft FurnaceBoy: phpBB guys store the symbols and
since I put myself as noobest than them I make these questions
2009-09-12 17:27:15 FurnaceBoy brsoft: exactly!
2009-09-12 17:27:21 FurnaceBoy brsoft: they do that because they want
html
2009-09-12 17:27:25 FurnaceBoy brsoft: do you?
2009-09-12 17:27:39 brsoft no I mean they store the chinese symbols as
they are
2009-09-12 17:27:41 FurnaceBoy brsoft: this is not an 'optimisation'
question.
2009-09-12 17:27:46 brsoft ok sorry
2009-09-12 17:27:55 FurnaceBoy it's a "what do yu want to store' question
2009-09-12 17:28:17 FurnaceBoy by as they are, do you mean as HTML
entities? this would make some sense for their application.
2009-09-12 17:28:19 brsoft but a text field full of all that html or
unicode crap isnt bad for optimizing?
2009-09-12 17:28:31 n8w FurnaceBoy, pm
2009-09-12 17:28:37 FurnaceBoy brsoft: not really. the more important q
is what should be stored
2009-09-12 17:29:11 FurnaceBoy brsoft: if you store something in an
inappropriate representations,conversions could kill you quicker than the
1% saving in space.
2009-09-12 17:29:26 FurnaceBoy brsoft: so: what are \you doing with the
text?
2009-09-12 17:30:30 brsoft i have two different scripts. one is for
translations from users who want to translate from an interface ive
deleoped. I get into the db the utf8 and it shows as utf8.
2009-09-12 17:30:35 brsoft the other one stores html
2009-09-12 17:31:09 FurnaceBoy brsoft: so sounds like storing html would
make some sense.
2009-09-12 17:31:11 brsoft the field of first is blob no text
2009-09-12 17:31:27 brsoft ok really thanks. I was going to pay for this
lol
2009-09-12 17:31:30 FurnaceBoy brsoft: oh i see, both scripts use the
same table.
2009-09-12 17:31:36 brsoft no different
2009-09-12 17:31:46 FurnaceBoy brsoft: oh
2009-09-12 17:31:47 brsoft im not that retard
2009-09-12 17:32:10 FurnaceBoy brsoft: it sounds like you could make a
similar choice to phpbb. though, you could also store unicode and send
the page as unicode.
2009-09-12 17:32:24 FurnaceBoy brsoft: i'm guessing for some reason phpbb
doesn't want to limit themselves to unicode page charsets
2009-09-12 17:32:30 brsoft I just thought the best was to save the
chinese symbols as phpBB guys were doing
2009-09-12 17:32:36 FurnaceBoy brsoft: so they use entities
2009-09-12 17:33:03 FurnaceBoy brsoft: you probably have a freer choice.
could go either way. store entities and spit out entities, or render your
page as unicode and use unicode from the table.
2009-09-12 17:33:25 FurnaceBoy brsoft: i'd take the lazier way :)
2009-09-12 17:33:29 brsoft they have a bunch of code to do this. have
been 2 days checking it. they have form input normalization functions nfc
and recompose and some other that are big waste of time thingies. time i
dont have
2009-09-12 17:33:38 FurnaceBoy brsoft: right. so use the simplest way
2009-09-12 17:34:04 brsoft ok thanks ^^
2009-09-12 17:34:48 FurnaceBoy brsoft: yw
2009-09-12 17:41:00 magowiz I'm trying to export a db using phpmyadmin ,
this db is in latin1, but when I import on another mysql with bigdump I
get : MySQL: Can't create table './Sql62686_5/tiki_articles.frm' (errno:
140)Stopped on error , apparently there's none syntax error
2009-09-12 17:45:00 Naktibalda !perror 140
2009-09-12 17:45:39 Naktibalda maybe you are importing dump to older
version of mysql?
2009-09-12 17:55:33 CodeNinja power outtage for the lose :(
2009-09-12 17:56:28 elops Hi guys, i'm trying to start mysql-server:
/etc/init.d/mysql start, but get error: http://dpaste.com/92691/
(/var/log/mysql/mysqld.err)
2009-09-12 17:56:47 CodeNinja was in the middle of inserting 16 million
rows into a db using a php script.... and the power went out.... at 2
million rows
2009-09-12 17:57:02 FurnaceBoy elops: looks like it is already running.
2009-09-12 17:57:06 FurnaceBoy elops: ps ax|grep mysql
2009-09-12 17:57:34 FurnaceBoy elops: actually sorry, no.
2009-09-12 17:57:50 FurnaceBoy elops: permission denied - interesting.
selinux? apparmor?
2009-09-12 17:58:17 elops gentoo
2009-09-12 17:58:44 FurnaceBoy FurnaceBoy slaps the_wench
2009-09-12 17:59:42 FurnaceBoy elops: check perms on /var/run/mysqld
directory
2009-09-12 18:00:24 CodeNinja CodeNinja is a sad panda
2009-09-12 18:00:27 FurnaceBoy CodeNinja: lucky it was a script! you can
re-run
2009-09-12 18:00:58 CodeNinja FurnaceBoy: except that the script wasn't
meant to run in a partially filled table
2009-09-12 18:01:05 CodeNinja it starts at 0
2009-09-12 18:01:08 CodeNinja implicitly
2009-09-12 18:01:26 FurnaceBoy CodeNinja: perfect, truncate it and re-
run.
2009-09-12 18:01:32 CodeNinja ?
2009-09-12 18:01:35 CodeNinja truncate the table?
2009-09-12 18:01:36 CodeNinja :(
2009-09-12 18:01:44 CodeNinja 2 million rows and 4 days down the hole
2009-09-12 18:01:50 CodeNinja CodeNinja sighs
2009-09-12 18:03:57 FurnaceBoy elops: ?
2009-09-12 18:07:19 mene Has anyone an idea, why this ORDER BY doesn't
work? Query: http://paste.bradleygill.com/index.php?paste_id=18863
2009-09-12 18:07:47 FurnaceBoy mene define 'doesn't work'
2009-09-12 18:08:17 FurnaceBoy FurnaceBoy wonders if that is the ugliest
pastebin evah
2009-09-12 18:09:18 CodeNinja :(
2009-09-12 18:09:34 CodeNinja 4 days to make the data... 2 minutes to
destroy it
2009-09-12 18:10:03 FurnaceBoy CodeNinja: without more information
there's not much we can do to help
2009-09-12 18:10:03 adaptr mene: aliases are not available after the
WHERE clause
2009-09-12 18:10:05 ven0m Question, does the order of tables i am JOINING
matter?
2009-09-12 18:10:05 mene FurnaceBoy: Seems random
2009-09-12 18:10:18 mene adaptr: got an Idea how to fix this?
2009-09-12 18:10:25 adaptr it is not fix-able
2009-09-12 18:10:33 adaptr change your query to avoid it
2009-09-12 18:10:39 ven0m will x JOIN y JOIN z will give me the same
result as y JOIN z JOIN x ?
2009-09-12 18:10:43 FurnaceBoy tias
2009-09-12 18:10:55 mene adaptr: yeah, but where to put the ORDER BY
correctly? :D
2009-09-12 18:10:59 adaptr ven0m: yes, INNER JOINs are commutable
2009-09-12 18:11:09 adaptr mene: that is not what I said, at all
2009-09-12 18:11:15 CodeNinja FurnaceBoy: there wasn't a whole lot to be
done.... it was the problem of the power going out.... had the power not
gone out it would still be running at around row 2.5 million... instead,
its barely passed 3000 now
2009-09-12 18:11:19 CodeNinja :(
2009-09-12 18:11:30 FurnaceBoy CodeNinja: why is it so slow?
2009-09-12 18:11:52 mene adaptr: hmmm... so what changes are necessary?
2009-09-12 18:12:34 adaptr mene: I have to feed it to yuo on a spoon ?
you can't use derived tables after the WHERE clause, I don't know how you
are going to "solve" it
2009-09-12 18:12:59 FurnaceBoy adaptr: his query doesn't? are you looking
at the same thing?
2009-09-12 18:13:01 mene adaptr: would it work to put ORDER BY in line 23
BEFORE the where clause?
2009-09-12 18:13:12 adaptr FurnaceBoy: alias, in this case
2009-09-12 18:13:12 CodeNinja FurnaceBoy: it queries another table to get
some information ahead of time, for each new row it inserts it queries
another table, its going about 10,000 rows per minute
2009-09-12 18:13:41 FurnaceBoy CodeNinja: maybe it's just crappy code
2009-09-12 18:14:13 CodeNinja It wasn't meant to be elegant code for this
2009-09-12 18:14:30 FurnaceBoy CodeNinja: as in 'inefficient'
2009-09-12 18:14:33 CodeNinja especially since this is a one time thing
and here after will be done in a smaller setup
2009-09-12 18:14:39 adaptr mene: you are seriously not listening to what
I say. there is nothing wrong with the syntax order - you are using an
alias in an ORDER BY clause. you can't
2009-09-12 18:14:45 FurnaceBoy CodeNinja: well now, it's a 2-time thing.
that often happens :)
2009-09-12 18:15:01 CodeNinja FurnaceBoy: power outtages for the lose :P
2009-09-12 18:15:04 FurnaceBoy adaptr: you can't?
2009-09-12 18:15:27 adaptr adaptr shudders at PHP "scripts" inserting 16M
rows in a specific order
2009-09-12 18:15:31 adaptr FurnaceBoy: you cannot
2009-09-12 18:15:40 FurnaceBoy FurnaceBoy reviews pastebin again
2009-09-12 18:15:48 CodeNinja FurnaceBoy: once this is done (with the 16
million rows), updates will be made to the database in chunks of a
thousand or so, which will be much much faster, and will also be done via
import, not insert and php
2009-09-12 18:16:10 FurnaceBoy adaptr: i don't see how he can't order by
a table alias `a` as per this query. also, his error isn't a parse error,
iirc
2009-09-12 18:16:31 CodeNinja adaptr: I couldn't come up with any other
way to do this, considering the size and this data is being generated
from scratch
2009-09-12 18:16:32 FurnaceBoy adaptr: ORDER BY a.objekt_groesse should
be fine, should it not?
2009-09-12 18:16:44 FurnaceBoy CodeNinja: 'from scratch'? RNG?
2009-09-12 18:17:12 CodeNinja FurnaceBoy: the data is being generated by
the php in a for loop
2009-09-12 18:17:19 adaptr WHERE 1 ?!?
2009-09-12 18:17:39 FurnaceBoy adaptr: it's joomla, i guess all bets are
off.
2009-09-12 18:17:40 adaptr CodeNinja: pure generation ? then WHY put it
in a database ?
2009-09-12 18:18:08 mene adaptr: I just replaced the "ORDER BY xy" -
the remaining stuff is standard joomla!-code....
2009-09-12 18:18:23 FurnaceBoy mene: WHERE 1 is a sign of code
generation, and redundant.
2009-09-12 18:18:36 mene adaptr: but replacing one ORDER bY by another
shouldn't mess it up, right?
2009-09-12 18:18:54 adaptr mene: what is the error
2009-09-12 18:19:11 mene adaptr: The Data isn't ordered ....
2009-09-12 18:19:20 adaptr how do you know
2009-09-12 18:19:22 mene looks like random order
2009-09-12 18:19:23 FurnaceBoy CodeNinja: what's the bottleneck, the
insert, or the calculation?
2009-09-12 18:19:45 CodeNinja calculation
2009-09-12 18:19:47 FurnaceBoy CodeNinja: if the latter, maybe php isn't
the best choice
2009-09-12 18:19:56 mene well, actually I don't know it it's ordered at
all - but it's definitly NOT ordered by object_groesse .... ;)
2009-09-12 18:20:01 adaptr my point exactly
2009-09-12 18:20:01 FurnaceBoy CodeNinja: profile it. could be something
simple
2009-09-12 18:20:05 CodeNinja like I said, this part is more or less a
one time thing
2009-09-12 18:20:18 adaptr that's what you think now
2009-09-12 18:20:21 CodeNinja after this, it will be done with an update
in blocks of 1000
2009-09-12 18:20:31 adaptr with more randomly-generated data ?
2009-09-12 18:20:38 CodeNinja no
2009-09-12 18:20:38 adaptr adaptr guffaws
2009-09-12 18:20:40 CodeNinja its not random
2009-09-12 18:20:40 CodeNinja :P
2009-09-12 18:20:51 mene adaptr: yepp, that's what I think. I got some
stuff that's displayed in the wrong order....
2009-09-12 18:20:56 FurnaceBoy CodeNinja: hehe, we're already up to 2
times. because the 1 time was really slow. ;-) you have to worry when
your program's runtime gives you a real exposure to random powerfail :-D
2009-09-12 18:20:58 CodeNinja but the original table was 1) too general,
and 2) broken to hell
2009-09-12 18:21:16 adaptr mene: so investigate and eliminate - throw
that crap away and reduce the problem to core
2009-09-12 18:21:50 CodeNinja FurnaceBoy: right.... its going a lot
faster than it was the first time... it was taking almost 2 hours to do a
major section where now it takes about 3-5 minutes
2009-09-12 18:22:06 CodeNinja so its running better now that I set renice
to -4 instead of +5
2009-09-12 18:24:08 CodeNinja and since this is the only thing my server
is really doing right now... I could bump it to realtime if I didn't feel
like giving anyone a real connection
2009-09-12 18:25:46 adaptr the fail here is inserting it into a table.
just let the script ouput into a file as it likes, then go off to drink
or something
2009-09-12 18:25:49 mene adaptr: could the "LIMIT 0, 10" mess the
ordering of objects above line 10 ?
2009-09-12 18:26:05 adaptr mene: LIMIT does what it advertises
2009-09-12 18:26:22 mene adaptr: oh, that was helpful. ;)
2009-09-12 18:26:41 CodeNinja adaptr: theres an option for that
2009-09-12 18:26:55 CodeNinja but I'd rather it be already in a table so
I can export it in a proper fashion
2009-09-12 18:27:33 FurnaceBoy|afk CodeNinja: isn't that an extra step?
2009-09-12 18:27:40 adaptr mene: you're JOINing several tables. depending
on the result, and given the fact that you don't GROUP anything, I would
expect the ORDER predicate to actually occur up to 10 times, filling your
entire result set with identical values
2009-09-12 18:27:40 FurnaceBoy|afk CodeNinja: export in what format? sql?
hehe
2009-09-12 18:28:02 CodeNinja FurnaceBoy|afk: I'd rather it be..... the
data breaks csv format
2009-09-12 18:28:19 CodeNinja unless I put it in some non csv, though
separated like csv format
2009-09-12 18:28:32 FurnaceBoy|afk CodeNinja: but the insert statemetns
are sql. if you store the inserts instead of executing them.. there's
your export.
2009-09-12 18:28:46 FurnaceBoy|afk CodeNinja: in other news, multiple-row
insert, etc, etc.
2009-09-12 18:29:59 CodeNinja FurnaceBoy|afk: hm (the idea of the storing
inserts), better than using a csv style. multiple row insert would not
work here because the data that goes after column1 and 2 are usually WAY
different on each line, but good suggestion none the less :)
2009-09-12 18:30:24 FurnaceBoy|afk CodeNinja: the suggestion is motivated
by you saying that 'in a table' is not your end result, but rather an
export
2009-09-12 18:30:45 FurnaceBoy|afk CodeNinja: oh i see, eahc insert is a
different set of columns
2009-09-12 18:31:17 CodeNinja no, its the same columns, but the data
could go from - to SOMETHING each other row
2009-09-12 18:31:18 FurnaceBoy|afk CodeNinja: if you want an export
presumably you know what form that should best take
2009-09-12 18:31:38 FurnaceBoy|afk CodeNinja: so if it's the same set of
columns, why can't you use multiple row insert? (which is much faster)
2009-09-12 18:32:33 FurnaceBoy|afk CodeNinja: when i say the same set of
columns, i mean specifying the same set of columns. but you seem to be
saying not
2009-09-12 18:33:44 CodeNinja well the data is going to be used on
multiple projects in multiple places, but the data needs to be in a
starting table to manage the changes of the other projects
2009-09-12 18:33:44 CodeNinja so this is the master copy essentially
2009-09-12 18:33:45 CodeNinja all changes will derive from this
2009-09-12 18:33:51 CodeNinja a sample row:
2009-09-12 18:34:06 FurnaceBoy|afk CodeNinja: in any case, if the store
is the bottleneck, then this eliminates the store.
2009-09-12 18:34:48 CodeNinja ?
2009-09-12 18:35:03 FurnaceBoy|afk CodeNinja: you said above that the
INSERTs were the bottleneck. doing it this way eliminates that entirely.
2009-09-12 18:35:23 FurnaceBoy|afk CodeNinja: maybe that 'll shave a day
or two off your runtime. pipe through bzip2 for more win.
2009-09-12 18:36:10 adaptr FurnaceBoy|afk: didn't he say that the
calculations were the bottleneck ?
2009-09-12 18:36:16 CodeNinja yes
2009-09-12 18:36:17 CodeNinja :P
2009-09-12 18:36:18 FurnaceBoy|afk adaptr: hm probably :)
2009-09-12 18:36:24 FurnaceBoy|afk my bad
2009-09-12 18:36:30 CodeNinja <FurnaceBoy> CodeNinja: what's the
bottleneck, the insert, or the calculation?
2009-09-12 18:36:30 CodeNinja <CodeNinja> calculation
2009-09-12 18:36:34 FurnaceBoy|afk oops
2009-09-12 18:36:34 CodeNinja :P
2009-09-12 18:36:55 adaptr FurnaceBoy|afk: it anything thakes 4 FUCKING
DAYS to insert a measly 2M rows, it's not the INSERTs that are the
problem...
2009-09-12 18:37:03 FurnaceBoy|afk adaptr: quite so :)
2009-09-12 18:37:08 CodeNinja adaptr: its going a lot faster than that
now
2009-09-12 18:37:10 adaptr I can insert 4 BILLION rows a day
2009-09-12 18:37:36 CodeNinja 200,000 in 10 minutes or so
2009-09-12 18:37:39 adaptr CodeNinja: why does it have to be restarted
from scratch ?
2009-09-12 18:37:41 CodeNinja 20 minutes maybe
2009-09-12 18:38:01 CodeNinja its hard to explain
2009-09-12 18:38:09 FurnaceBoy|afk chadmaynard: hi
2009-09-12 18:38:26 chadmaynard I don't talk to afks
2009-09-12 18:38:30 adaptr there's only one valid reason to start that
torture from zero again, and that is a direct series progression in the
data
2009-09-12 18:43:18 coldfire22x salle: from last night's discussion, i
was weighing the difference between packed indexes and various sizes
2009-09-12 18:44:43 CodeNinja adaptr: thats the most simplest way to put
it... good explanation, it would've taken me 20 minutes to tell you that
2009-09-12 18:45:50 salle coldfire22x: What packed indexes?
2009-09-12 18:46:09 salle coldfire22x: Compressed with myisampack ?
2009-09-12 18:46:27 CodeNinja adaptr: the end result of this data is
about 20GB (I'm guessing) is there a way to have mysql compress it down
2009-09-12 19:13:09 Evet can i assign a field of a table to a field of
another table? "without joining physically"
2009-09-12 19:14:21 preilly can you use a variable in a select statement
e.g., SET @test = 'url'; SELECT * FROM @test;
2009-09-12 19:15:51 rosco_y how can I test the number of rows affected by
a DELETE command?
2009-09-12 19:16:58 rosco_y I wish I could do: "IF ((DELETE FROM T WHERE
T.ID = nnn)>0)" etc.
2009-09-12 19:18:31 gencha rosco_y: the first line from the mysql docs on
DELETE: "For the single-table syntax, the DELETE statement deletes rows
from tbl_name and returns a count of the number of deleted rows."
2009-09-12 19:18:46 gencha second line: "This count can be obtained by
calling the ROW_COUNT() function (see Section 11.10.3, “Information
Functions”)."
2009-09-12 19:19:17 rosco_y gencha: so do you think "SET myCount = DELETE
FROM TABLE;" might work?
2009-09-12 19:19:31 rosco_y or maybe even my syntax above
2009-09-12 19:19:57 rosco_y Thanks gencha
2009-09-12 19:20:04 rosco_y I'll work with that
2009-09-12 19:20:08 gencha my pleasure ;D
2009-09-12 19:21:25 coldfire22x salle: yeah ... i need to have some
indexes on varchars (some rather lengthy). wanted to see what the index
sizes were with different lengths of packed indexes to make sure the
indexes are selective enough and fit in memory
2009-09-12 19:24:18 preilly does this not work: Â INSERT INTO @table_name
2009-09-12 19:26:59 preilly can anybody help me please
2009-09-12 19:29:24 adaptr preilly: try asking a sensible question
2009-09-12 19:29:55 preilly adaptr: I just wanted to know if there is a
way to use a mysql user variable in an insert statement
2009-09-12 19:30:05 preilly adaptr: is that not a sensible question?
2009-09-12 19:30:12 adaptr if you had asked that, yes
2009-09-12 19:30:40 preilly adaptr: ha ha -- okay, well that is what I
was asking....
2009-09-12 19:31:00 adaptr whut ?
2009-09-12 19:31:18 preilly can it be done?
2009-09-12 19:32:07 Naktibalda preilly, yes. create a string using
CONCAT, PREPARE it and EXECUTE
2009-09-12 19:32:49 preilly Naktibalda: thanks!
2009-09-12 19:32:53 Naktibalda SET @query = CONCAT('INSERT INTO
',@table,' VALUES(...)';
2009-09-12 19:33:10 Naktibalda !m preilly prepare
2009-09-12 19:34:53 preilly Naktibalda: thanks!
2009-09-12 19:50:13 dankles Hello - I'm on 5.0.45, and I am getting a lot
of random "error 124 from storage engine" on SELECTs from one of my
MyISAM tables. I can't figure out what this means!
2009-09-12 19:50:49 Naktibalda !perror 124
2009-09-12 19:51:02 DASPRiD perror?
2009-09-12 19:51:20 DASPRiD DASPRiD just knows terror
2009-09-12 19:51:32 dankles Error #1030 in SQL query: Got error 124 from
storage engine
2009-09-12 19:53:18 dankles Query is basic SELECT (lots of fields) FROM
onetable WHERE x=1 AND y=2 AND (w=1 OR u=2)
2009-09-12 19:53:39 dankles and it seems to happen randomly, usually when
there's a lot of concurrent use on that table
2009-09-12 19:57:14 dankles no ideas ?
2009-09-12 19:59:32 m3nt0r is it possible to use a selected field value
as unit specifier (DAY/MONTH) within INTERVAL ?
2009-09-12 20:01:23 m3nt0r something like "INTERVAL t.after
UPPER(t.unit)"
2009-09-12 20:04:29 adaptr m3nt0r: in a prepared statement, why not
2009-09-12 20:11:17 salle coldfire22x: myisampack compresses indexes on
disk not in memort
2009-09-12 20:11:19 salle memory
2009-09-12 20:12:54 awnstudio salle, can you do me a favor please?
2009-09-12 20:13:25 awnstudio salle, please go to this site and let me
know if the myspace music player shows up
http://www.myspace.com/jenaemichell
2009-09-12 20:33:30 salle awnstudio: Why should I?
2009-09-12 20:34:21 salle m3nt0r: it's valid syntax
2009-09-12 20:36:30 preilly Is there any way around this error: ERROR
1336 (0A000): Dynamic SQL is not allowed in stored function or trigger
with the following trigger: http://pastebin.com/m2cf9ec02
2009-09-12 20:36:50 salle m3nt0r: If you want x + INTERVAL y <unit expr>
no this is not supported
2009-09-12 20:37:22 salle preilly: The error message says it all
2009-09-12 20:37:46 preilly salle: hmm
2009-09-12 20:38:16 preilly so you can't use PREPARE in a TRIGGER?
2009-09-12 20:38:44 preilly so, can I create a stored procedure and call
that from the trigger?
2009-09-12 20:39:21 salle preilly: I doubt it will work, but it worth
trying
2009-09-12 20:39:22 m3nt0r salle: okay. i actually just found a
workaround using "select if( strcmp(`unit`, 'days'), date_add(`created`,
interval .. month), date_add(`created`, interval .. day) )"
2009-09-12 20:40:16 salle m3nt0r: You can use CASE to modify the interval
2009-09-12 20:41:24 m3nt0r hmm. checking..
2009-09-12 20:43:01 salle m3nt0r: CASE unit WHEN 'days' THEN ...
2009-09-12 20:43:46 salle m3nt0r: By the way what is the point of useing
strcmp() there?
2009-09-12 20:44:30 m3nt0r compares and returns bool?
2009-09-12 20:44:46 salle m3nt0r: It doesn't
2009-09-12 20:44:56 salle m3nt0r:
http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html
2009-09-12 20:45:10 salle "STRCMP() returns 0 if the strings are the
same, -1 if the first argument is smaller than the second according to
the current sort order, and 1 otherwise."
2009-09-12 20:45:20 m3nt0r ic
2009-09-12 20:45:34 salle m3nt0r: Why not plain simple units = 'days' ?
2009-09-12 20:45:56 m3nt0r i agree
2009-09-12 20:57:08 kjkoster5489 Ehm. Interesting problem. ERROR 1022
(23000) at line 82: Can't write; duplicate key in table
2009-09-12 20:57:16 kjkoster5489 I am trying to restore a MySQL dump.
2009-09-12 20:57:28 kjkoster5489 Should primary key not be unique?
2009-09-12 20:59:13 Xgc kjkoster5489: Yes, but you may have triggers or
something similar that are conflicting as you insert rows.
2009-09-12 20:59:28 kjkoster5489 Nope. Clean table.
2009-09-12 20:59:34 Xgc kjkoster5489: Make sure your tables are empty
before importing.
2009-09-12 20:59:52 kjkoster5489 Dropped the database.
2009-09-12 21:00:03 Xgc kjkoster5489: Duplicate key could be any unique
key, not just the primary key.
2009-09-12 21:00:49 Xgc kjkoster5489: If you can't figure it out, provide
the dump or some smaller testcast case that produces the behavior.
2009-09-12 21:01:01 Xgc testcase
2009-09-12 21:01:05 kjkoster5489 http://paste.ubuntu.com/269956/
2009-09-12 21:01:57 kjkoster5489 Providing the dump is difficult because
1) it contains customer IP addresses and 2) it is about 3/4 million
records.
2009-09-12 21:02:39 Xgc kjkoster5489: Right. So you're probably no your
own unless you can provide a testcase without private information.
2009-09-12 21:03:15 Xgc kjkoster5489: It should be easy to identify by
the specific row causing the error.
2009-09-12 21:03:24 kjkoster5489 My test is to mysqldump on machine a and
drop/import on machine b.
2009-09-12 21:03:24 kjkoster5489 How do I find the duplicate key on
machine a?
2009-09-12 21:03:24 kjkoster5489 SELECT COUNT(id), id FROM Event GROUP BY
id;
2009-09-12 21:03:37 kjkoster5489 I just don't understand how that table
can even exist on machine a.
2009-09-12 21:04:07 Naktibalda dupes
2009-09-12 21:04:20 kjkoster5489 Ahh.
2009-09-12 21:04:52 Xgc kjkoster5489: Maybe there's something special
about the archive engine. I've never used it. The primary key (id)
can't have duplicates, by definition. So you won't find any with that
SQL.
2009-09-12 21:05:32 kjkoster5489 Right, it yields the empty set.
2009-09-12 21:05:44 kjkoster5489 Yay.
2009-09-12 21:05:51 kjkoster5489 Well, thanks for the help, guys.
2009-09-12 21:06:09 kjkoster5489 Looks like I have to rebuild that table
from scratch.
2009-09-12 21:07:15 kjkoster5489 Hmm. Can I make mysqldump not use multi-
insert, but one insert per record?
2009-09-12 21:07:16 Xgc kjkoster5489: You haven't found the issue yet.
Any action you take probably won't be helpful.
2009-09-12 21:07:27 Xgc kjkoster5489: Yes.
2009-09-12 21:07:38 salle kjkoster5489: Of course you can
2009-09-12 21:07:44 salle kjkoster5489: Just read the options
2009-09-12 21:07:49 salle kjkoster5489: mysqldump --help
2009-09-12 21:08:17 Xgc Don't forget the -v
2009-09-12 21:10:10 kjkoster5489 --skip-opt, got it.
2009-09-12 21:10:17 kjkoster5489 I'm off for some re-dumping.
2009-09-12 21:10:29 kjkoster5489 Thanks again, I'll let you know what I
found.
2009-09-12 21:15:13 salle kjkoster5489: How did yu manage to get
duplicate key while restoring from dump?
2009-09-12 21:15:45 Cluber Is there a way to select a say 20 rows from a
query e.g. select customer_id from sales where name='dave' and then show
the details of all the id's in ONE query. theres 2 tables.
customer(customer_id,name,address) and sales(sale_id, customerid,
product)
2009-09-12 21:15:59 kjkoster5489 salle: excellent question.
2009-09-12 21:19:52 awnstudio Cluber, use limit
2009-09-12 21:20:15 kjkoster5489 salle: Xgc: found the problem.
2009-09-12 21:20:23 Cluber awnstudio: in what way
2009-09-12 21:20:42 salle Cluber: add LIMIT 20; at the end and keep it in
mind without ORDER BY it's "some 20 rows"
2009-09-12 21:20:43 awnstudio select customer_id from sales where
name='dave' and then show the details of all the id's in ONE query.
theres 2 tables. customer(customer_id,name,address) and sales(sale_id,
customerid, product) limit 0,20
2009-09-12 21:20:59 awnstudio sorry
2009-09-12 21:21:02 awnstudio select customer_id from sales where
name='dave' and then show the details of all the id's in ONE query.
theres 2 tables. customer(customer_id,name,address) and sales(sale_id,
customerid, product) limit 20
2009-09-12 21:21:14 CorticalStack awnstudio: you are referring to a JOIN
query.
2009-09-12 21:21:15 kjkoster5489 salle: Xgc:
http://paste.ubuntu.com/269964/
2009-09-12 21:21:26 kjkoster5489 Try that SQL in your test database.
2009-09-12 21:21:33 kjkoster5489 Fails for me.
2009-09-12 21:21:35 CorticalStack join
2009-09-12 21:21:45 CorticalStack m! awnstudio join
2009-09-12 21:21:50 CorticalStack bot
2009-09-12 21:21:59 CorticalStack joins
2009-09-12 21:22:01 kjkoster5489 If I remove the AUTO_INCREMENT, it does
insert.
2009-09-12 21:22:05 CorticalStack awnstudio: ^^
2009-09-12 21:22:14 awnstudio what?
2009-09-12 21:22:49 awnstudio i didn't ask anyon for join information if
thats what this is.
2009-09-12 21:22:56 CorticalStack awnstudio: the answer to your question
involves a JOIN query
2009-09-12 21:23:23 awnstudio CorticalStack, i didnt ask a question!
2009-09-12 21:23:32 CorticalStack awnstudio: oops, my error sorry
2009-09-12 21:23:43 awnstudio np
2009-09-12 21:23:45 Cluber lol
2009-09-12 21:23:55 CorticalStack !tell Cluber about joins
2009-09-12 21:27:09 _bugz_ what is the latest version of MySQL.net
connector for production?
2009-09-12 21:29:09 kjkoster5489 salle: Xgc:
http://bugs.mysql.com/bug.php?id=37182
2009-09-12 21:50:14 Cluber SELECT EventID FROM Matches WHERE MatchType <>
'' GROUP BY EventID ORDER BY EventID DESC as c inner join Events as s on
c.EventID=s.EventID
2009-09-12 21:50:18 Cluber what is wrong with this?
2009-09-12 21:51:09 salle doesn't work
2009-09-12 21:51:40 salle Cluber: My crystal ball says you get syntax
error
2009-09-12 21:51:57 Cluber salle: Your crystal ball is correct
2009-09-12 21:51:59 salle Cluber: at 'inner join'
2009-09-12 21:52:07 Cluber yes
2009-09-12 21:52:08 salle Cluber: Why didn't you say it?
2009-09-12 21:52:50 salle Cluber: You either don't understand joins or
simply forgot ()
2009-09-12 21:53:01 Cluber syntax to use near 'as c inner join Events as
s on c.EventID=s.EventID' at line 1
2009-09-12 21:53:50 salle Cluber: You either don't understand joins or
you are thinking subqueries, but forgot ()
2009-09-12 21:54:29 salle Cluber: What are you trying to achieve?
2009-09-12 21:54:29 Cluber hmm
2009-09-12 21:55:03 Cluber salle: i am trying to select some eventid's
from the events table
2009-09-12 21:55:10 Cluber sorry
2009-09-12 21:55:21 Cluber i am trying to select some eventid's from the
matches table
2009-09-12 21:55:37 Cluber and get the event data from the events table
2009-09-12 21:56:56 salle Cluber: Start with: SELECT ... FROM x JOIN y ON
(<join condition>)
2009-09-12 21:57:14 salle Cluber: Then refine it
2009-09-12 21:58:52 Cluber ok
2009-09-12 22:00:23 Cluber im struggling with this
2009-09-12 22:00:32 salle Cluber: The syntax error you get is because you
can't have join clauses after ORDER BY clause, but that's minor problem.
The main one is it doesn't make sense at all
2009-09-12 22:00:52 Cluber do you understand what im trying to do
2009-09-12 22:00:56 salle Cluber: Struggling with what? What columns do
you need from each table?
2009-09-12 22:01:10 Cluber i need all columns from the Events table
2009-09-12 22:01:18 Cluber select * from events
2009-09-12 22:01:25 Cluber ffs lol
2009-09-12 22:01:31 Cluber select * from matches
2009-09-12 22:01:45 Cluber but there is a condition on that
2009-09-12 22:01:52 iveqy i've a question: SELECT * FROM users WHERE name
LIKE '%bob%'; The problem is that % is greedy. So the question returns
bobster instead of just bob. How do I make the question to shoose the
shortest hit?
2009-09-12 22:02:09 foobar2k hey guys, I'm having trouble with mysql
replication. does mysql replication propogate schema changes to slaves
too?
2009-09-12 22:02:31 salle Cluber: SELECT <columns you need> FROM t1 JOIN
t2 ON (t1.x = t2.y); :)
2009-09-12 22:03:39 Cluber salle: What about my condition?
2009-09-12 22:03:54 Cluber SELECT EventID FROM Matches WHERE MatchType <>
''
2009-09-12 22:04:02 salle Cluber: just add it ...
2009-09-12 22:04:03 Cluber where do i put the MatchType <> ''
2009-09-12 22:04:13 salle Cluber: In WHERE clause indeed
2009-09-12 22:04:34 salle Cluber: SELECT <columns you need> FROM t1 JOIN
t2 ON (t1.x = t2.y) WHERE t1.col != 'foo';
2009-09-12 22:04:49 Cluber ahh i see
2009-09-12 22:05:06 salle Cluber: Get some book about SQL basics or try
some online course like http://sqlcourse.com
2009-09-12 22:06:03 Cluber SELECT * FROM Matches JOIN Events ON
(Matches.EventID = Events.EventID) WHERE MatchType <> ''
2009-09-12 22:06:27 Cluber but this selects the Matches... I want to
select the Events
2009-09-12 22:06:41 salle Cluber: Look carefully at the result
2009-09-12 22:07:04 Cluber ahh
2009-09-12 22:07:34 Cluber very cool
2009-09-12 22:07:37 salle Cluber: It selects matching rows from both
tables. You really need some first steps in SQL :)
2009-09-12 22:07:49 Cluber join is my new best friend
2009-09-12 22:08:48 ciss hi, can a table's update_time manually be set to
an earlier date?
2009-09-12 22:09:13 salle ciss: What is "table's update_time"?
2009-09-12 22:09:39 ciss salle, i'm referring to
information_schema.TABLES.UPDATE_TIME
2009-09-12 22:11:27 CorticalStack !tell ciss about information_schema
2009-09-12 22:11:42 CorticalStack ciss: you cannot directly manipulate it
2009-09-12 22:11:44 salle ciss: touch can do it
2009-09-12 22:11:54 Xgc iveqy: Use ORDER BY LENGTH(colname) LIMIT 1 -or-
see the groupwise max articles if you need to do this over more than one
group at once.
2009-09-12 22:12:12 salle ciss: but you better shutdown mysqld first or
at least FLUSH WITH READ LOCK
2009-09-12 22:13:19 CorticalStack ciss: you trying to cover up a screwup
or a hack?
2009-09-12 22:13:24 CorticalStack ;^)
2009-09-12 22:13:41 salle CorticalStack: looks like :)
2009-09-12 22:14:13 iveqy Xgc: thanks!
2009-09-12 22:14:16 iveqy it worked
2009-09-12 22:15:38 CorticalStack ciss: don't forget your .mysql_history
file!
2009-09-12 22:15:47 salle hehe
2009-09-12 22:15:58 salle CorticalStack: what about binlogs?
2009-09-12 22:16:07 ciss salle, then i'd better try finding another
solution. i wrote a script for db synchronization via php (remote host is
a shared host without shell access). i'm currently tracking the last sync
time through the file names of gzipped backups of the remote tables
2009-09-12 22:16:26 salle ciss: You better use another solution
2009-09-12 22:16:44 salle ciss: What about sync table with single
TIMESTAMP column?
2009-09-12 22:16:56 ciss the one drawback is that i cannot track local
changes since the last update without storing additional information
2009-09-12 22:17:16 salle ciss: insert row with each sync and you know
last sync plus all the history
2009-09-12 22:19:01 CorticalStack CorticalStack is disappointed, thought
something juicy was going on.
2009-09-12 22:20:30 ciss CorticalStack, sorry to let you down :)
2009-09-12 22:20:43 CorticalStack lol
2009-09-12 22:20:48 salle ciss: information_schema.TABLES.UPDATE_TIME is
not reliable. It only works with MyISAM using file utime. If you restore
from backup these times change
2009-09-12 22:21:28 ciss salle, good point. didn't know that
2009-09-12 22:21:52 ciss and here was thinking i'd be almost done ...
2009-09-12 22:22:22 ciss +i
2009-09-12 22:22:57 salle ciss: CREATE TABLE rsync(ts TIMESTAMP PRIMARY
KEY); :)
2009-09-12 22:28:15 ciss salle, perhaps i should just activate live
checksums ...
2009-09-12 22:28:38 salle ciss: What are live checksums?
2009-09-12 22:29:54 CorticalStack Beer O'Clock!
2009-09-12 22:30:35 ciss salle, "alter table option checksum 1;" makes
mysql create a short checksum for each table. available in
information_schema.TABLES.CHECKSUM
2009-09-12 22:31:25 salle ciss: How does the checksum tell you when the
table was last updates?
2009-09-12 22:31:55 ciss salle, it doesn't, but it tells me if there are
differences between the local and the remote version
2009-09-12 22:32:40 ciss salle, and if so, i can compare both tables and
apply the differences locally
2009-09-12 22:34:01 salle ciss: heh
2009-09-12 22:34:20 salle ciss: Of course, but that's miles away from
your initial question
2009-09-12 22:35:54 ciss salle: the core idea is to prevent a complete
reimport of the remote database by only importing changes
2009-09-12 22:35:59 C00re select (firstname + lastname) as name, ssn from
customers; how can change this so i get a string instead of an integer 0
in 'name' ?
2009-09-12 22:36:20 ciss C00re, CONCAT()
2009-09-12 22:36:23 salle CONCAT()
2009-09-12 22:36:26 C00re doesnt work
2009-09-12 22:36:46 C00re oh, i tried contat()
2009-09-12 22:36:51 C00re will try :)
2009-09-12 22:36:59 C00re stupid google :P
2009-09-12 22:37:46 C00re ah great
2009-09-12 22:37:48 C00re thanx
2009-09-12 22:39:43 ciss anyway, salle, thanks for your remarks and
suggestions
2009-09-12 22:40:08 ciss bye
2009-09-12 23:07:15 das7002 I have a database with one row of hex numbers
and I wanted to sort by that row in hex order how do I do that?
2009-09-12 23:07:55 salle das7002: if numbers are stored in hex format
what's the problem?
2009-09-12 23:08:11 salle das7002: Not that it makes sense to store hex
as strings but ...
2009-09-12 23:08:19 das7002 MySql is sorting them alpanumerically
2009-09-12 23:08:51 salle das7002: Store them as INTs and use SELECT
HEX() when necessary
2009-09-12 23:09:24 salle das7002: Not only you will get better sort
order, but you will use much less space
2009-09-12 23:10:09 das7002 Is there anyway to convert the hex values to
ints so I can change that row to int (I know how to alter the table to
change to int)
2009-09-12 23:11:15 das7002 oh, I feel stupid unhex should do that
2009-09-12 23:11:25 salle das7002: HEX() and UNHEX()
2009-09-12 23:13:46 b0red is 5.0 the last stable?
2009-09-12 23:15:06 salle b0red: 5.1
2009-09-12 23:32:40 CorticalStack CorticalStack knew FurnaceBoy when he
was A Funny Kook
2009-09-12 23:34:05 b0red how to change mysqld root passwd
2009-09-12 23:34:57 CorticalStack b0red: do you know the current one?
2009-09-12 23:35:29 b0red mysqladmin -u root password passhere
2009-09-12 23:35:32 b0red yup, it's empty
2009-09-12 23:35:35 b0red changed.
2009-09-12 23:36:16 Cluber Truncated incorrect datetime value:
'12/11/1993'
2009-09-12 23:36:22 Cluber select YEAR(Date) as "Year" from Events
2009-09-12 23:36:37 Cluber im trying to get just he year from that date
2009-09-12 23:36:42 CorticalStack b0red: if you can log in as root;
http://dev.mysql.com/doc/refman/5.0/en/set-password.html
2009-09-12 23:36:59 CorticalStack Cluber: that's not a date, it's a
string
2009-09-12 23:37:14 Cluber CorticalStack: darn
2009-09-12 23:37:20 Cluber can i just change my data type
2009-09-12 23:37:21 CorticalStack Cluber: try to use the correct data
types and formats
2009-09-12 23:37:21 Cluber to date
2009-09-12 23:37:40 Cluber or will that screw things up?
2009-09-12 23:38:03 maxb Cluber: There is no 1993rd day in the 11 month
of the year 12 AD
2009-09-12 23:38:19 CorticalStack Cluber: to change string data to date,
create a new column with the proper data type (date of datetime) and use
str_to_date() to populate it with an update query
2009-09-12 23:38:36 CorticalStack date or datetime
2009-09-12 23:38:52 CorticalStack !m Cluber date time fu
2009-09-12 23:39:16 thumbs !m date and time f
2009-09-12 23:39:20 Cluber maxb: ahh i hate the american way of writing
dats
2009-09-12 23:39:22 Cluber *dates
2009-09-12 23:39:27 thumbs !noo date and time f
2009-09-12 23:39:27 Cluber YYYY-MM-DD
2009-09-12 23:39:34 CorticalStack Cluber:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
2009-09-12 23:39:50 salle Cluber: Are you storing dates as '12/11/1993' ?
What type is your column?
2009-09-12 23:40:07 salle Cluber: as a side note it's bad idea to use
reserved words as column names
2009-09-12 23:40:40 Cluber http://www.tizag.com/sqlTutorial/sqldate.php
2009-09-12 23:40:42 Cluber they used it :P
2009-09-12 23:40:51 CorticalStack Cluber: that is not an 'American way'
of writing dates
2009-09-12 23:41:08 salle Cluber: I have no idea who are "they" and don't
really care :)
2009-09-12 23:41:20 Cluber there normally a good site lol
2009-09-12 23:41:25 Cluber SELECT MONTH('8/14/04') as "Month";
2009-09-12 23:41:29 Cluber there using this?
2009-09-12 23:41:31 thumbs Cluber: most folks who publish tutorials
online are idiots.
2009-09-12 23:41:47 Cluber thumbs: lol. This is a big site...
2009-09-12 23:41:54 thumbs Cluber: I don't care.
2009-09-12 23:41:57 Cluber So they are wrong?
2009-09-12 23:42:06 CorticalStack completely
2009-09-12 23:42:20 Cluber the internet can be a misleading place
2009-09-12 23:42:38 salle Cluber: May I suggest you trust the vendor
manual first?
2009-09-12 23:42:45 CorticalStack Cluber: you need to take some
responsibility about learning about data types from the manual
2009-09-12 23:42:53 salle Cluber: http://dev.mysql.com/doc/ in this case
2009-09-12 23:43:46 salle Cluber: By the way this tutorial doesn't say it
is MySQL oriented
2009-09-12 23:44:07 salle although it *is* MySQL
2009-09-12 23:44:31 Cluber hehe
2009-09-12 23:44:44 Cluber so im basically need to change my data type
2009-09-12 23:44:51 Cluber and adjust the values
2009-09-12 23:45:13 Cluber from dd/mm/yyyy to YYYY-MM-DD
2009-09-12 23:45:19 salle Cluber: what is your data type at the moment?
2009-09-12 23:45:38 Cluber salle: String
2009-09-12 23:45:46 Cluber do not ask why i chose that
2009-09-12 23:45:47 Cluber sorry
2009-09-12 23:45:50 Cluber its VARCHAR
2009-09-12 23:46:05 salle Cluber: heh
2009-09-12 23:46:18 salle Cluber: Which tutorial told you to use VARCHAR
for dates????
2009-09-12 23:46:29 CorticalStack Cluber: this is not an uncommon error
for a beginner
2009-09-12 23:46:31 salle Cluber: Even CHAR is bad idea, but VARCHAR is
plain stupid
2009-09-12 23:46:41 salle Cluber: STR_TO_DATE will help you
2009-09-12 23:46:48 salle Cluber:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-
functions.html#function_str-to-date
2009-09-12 23:46:53 xQuasar how would i do a select query where timestamp
is 2 weeks before now?
2009-09-12 23:46:54 Cluber ok lets have a look at this beast
2009-09-12 23:47:02 salle Cluber: Better add new column though so you
don't accidentaly lose your data
2009-09-12 23:47:25 Cluber xQuasar: i would convery 2 weeks to seconds
2009-09-12 23:47:29 xQuasar SELECT * FROM `table` WHERE
`timestamp_column` < (CURRENT_TIMESTAMP - '00:14:00 00:00:00) doesn't
seem to work
2009-09-12 23:47:37 salle xQuasar: .. WHERE ts < NOW() - INTERVAL 2 WEEK;
2009-09-12 23:47:44 xQuasar ok ty!
2009-09-12 23:48:05 Cluber 1209600 secs
2009-09-12 23:48:07 CorticalStack Cluber: you'll end up adding a column
of date data type using alter table. thn doing something like 'update t1
set date_col = str_to_date(varchar_col, <def>);
2009-09-12 23:48:10 Cluber off the top of my head
2009-09-12 23:48:15 salle xQuasar: or <= depending on what you need
2009-09-12 23:48:39 salle Cluber: Better learn more about MySQL date/time
functions :)
2009-09-12 23:49:23 CorticalStack Cluber: when you start using the proper
date and time data types, you can take advantage of the numerous built in
mysql functions. till then, maybe not offer incorrect advice.
2009-09-12 23:49:32 salle Cluber: How do you code .. WHERE ts < NOW() -
INTERVAL 9 MONTH; and take care of all new years, leap years etc?
2009-09-12 23:50:29 Cluber i see
2009-09-12 23:50:31 salle Cluber: It is possible to update the values in
place and then alter the column type, but better do it as CorticalStack
explained
2009-09-12 23:50:42 Cluber lol
2009-09-12 23:50:51 FurnaceBoy wasn't that funny
2009-09-12 23:50:51 Cluber yeah i dont want to lose this data
2009-09-12 23:50:55 Cluber theres over 1000 rows
2009-09-12 23:50:57 FurnaceBoy omg
2009-09-12 23:51:19 salle 1000 rows is nanoworld in databases universe :)
2009-09-12 23:51:24 FurnaceBoy hey Cluber did you get my sql solution to
your winning streak problem?
2009-09-12 23:51:34 piklu hello everybody
2009-09-12 23:51:39 FurnaceBoy pik ohai
2009-09-12 23:52:12 piklu i have webmin installed on a server. and i have
added mysql users through it, but they are not able to connect....make a
guess
2009-09-12 23:52:26 salle piklu: Is it some quiz?
2009-09-12 23:52:34 Cluber FurnaceBoy: No, i just used a php loop in the
end
2009-09-12 23:52:53 piklu No....make a guess meant help me out....LOL
2009-09-12 23:52:53 Cluber well remmebered
2009-09-12 23:53:28 Cluber salle: its a teraworld if your painsteakingly
inputting data
2009-09-12 23:53:30 Cluber row by row
2009-09-12 23:53:35 salle piklu: In what language "make a guess" means
"help me"?
2009-09-12 23:53:47 piklu salle....its clear here now
2009-09-12 23:53:52 piklu " HELP ME "
2009-09-12 23:53:56 FurnaceBoy Cluber: i memo'd the solution to you.
https://slashdot.org/~toby/journal/233855
2009-09-12 23:54:01 piklu so am asking help !
2009-09-12 23:54:16 salle piklu: Take a look at the /topic and ask MySQL
question if you have one
2009-09-12 23:54:48 Cluber ahh wow
2009-09-12 23:55:01 CorticalStack piklu: or at least provide sufficient
data to make it possible to answer. local or remote connections, error
messages, etc
2009-09-12 23:55:11 Cluber FurnaceBoy: very nice
2009-09-12 23:55:46 piklu LOCAL CONNECTIONS , SSH ---> Access Denied
Errors, created users with webmin.
2009-09-12 23:56:08 CorticalStack piklu: that is an incomplete error
message
2009-09-12 23:56:20 CorticalStack piklu: full and exact, it matters
2009-09-12 23:57:00 salle piklu: webmin is offtopic in #mysql
2009-09-12 23:57:02 piklu Access Denied for user@ ' localhost ' with
password yes
2009-09-12 23:58:00 Cluber update Events set Date_New = str_to_date(Date,
format);
2009-09-12 23:58:04 Cluber whats my format options
2009-09-12 23:58:06 piklu CorticalStack : the thing is it doesnt lets
connect when even i have provided it all permissions
2009-09-12 23:58:16 FurnaceBoy date functions
2009-09-12 23:58:19 thumbs piklu: so grant access properly, or provide
the correct password.
2009-09-12 23:58:20 CorticalStack piklu: so the server is reached, it
repsonds, and says 'give me the correct password instead'
2009-09-12 23:58:28 thumbs piklu: also, don't use webmin to create users.
2009-09-12 23:58:41 salle Cluber: Asking others to read the manual for
you is not very polite to say the least
2009-09-12 23:58:47 piklu thumbs : I also feel sick about it.
2009-09-12 23:58:49 salle Cluber:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-
functions.html#function_str-to-date
2009-09-12 23:58:51 FurnaceBoy salle: it's 90% of what we get
2009-09-12 23:58:55 Cluber update Events set Date_New = str_to_date(Date,
'%d/%m/%Y');
2009-09-12 23:58:57 Cluber like this
2009-09-12 23:58:57 FurnaceBoy !wench learn date functions is
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
2009-09-12 23:59:01 FurnaceBoy Cluber: ^^
2009-09-12 23:59:14 piklu CorticalStack : it keeps denying them
2009-09-12 23:59:25 Cluber how crap is that
2009-09-12 23:59:29 Cluber the functions aren't even linked
2009-09-12 23:59:43 FurnaceBoy Cluber: yes they are.
2009-09-12 23:59:44 Cluber you have to scroll through the entire page
2009-09-12 23:59:49 FurnaceBoy Cluber: no you don't.
2009-09-12 23:59:52 CorticalStack lol
2009-09-12 23:59:55 Cluber there not linked!
2009-09-12 23:59:55 thumbs piklu: so grant access properly, or provide
the correct password.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:4
posted:10/23/2012
language:Unknown
pages:53