#mysql
17 October 2007
Total 30 pages. You are browsing page 4/30.
First :: Prev :: [...] [2] [3] [4] [5] [6] [...] :: Next :: Last
02:50 <****> in a trigger i do something for each row in the table.. but i want to something for each row in another table too, inside the first "for each row"-loop. is this possible?
02:51 <****> Simetrical, id 1. Strange.
02:51 <****> kimseong: thanks dude, that is common sense, i should have thought of that, hahaha
02:52 <****> corrideat, . . .
02:52 <****> corrideat: Simetrical has given you the answer right from the start
02:52 <****> kimseong, he says it doesn't work.
02:52 <****> Thank you very much.
02:52 <****> And gives him the first row.
02:52 <****> So I will try to find the error in
02:53 <****> ops, that was for smellis :)
02:53 <****> anyone ever heard of cheatnaion.org?
02:53 <****> the db.
02:53 <****> chadmaynard, no, but I've heard of cheatnation.org.
02:53 <****> corrideat: try select max(id) from table;
02:53 <****> Possibly it's a nation of cheaters? If so, I will personally petition President Bush to invade it. Clearly we don't want a nation of cheaters around.
02:53 <****> Simetrical: ah that's it.
02:53 <****> chadmaynard: how many points did you get?
02:55 <****> what is the best type for an enabled/disabled field in a record? char?
02:56 <****> rgr: bit
02:57 <****> kimseong: how stupid do I feel. I never scrolled down in the phpadmin type pick :(
02:57 <****> Simetrical, So, when I wrote the SQL query, I'd a WHEN that was only satisfied by the first item.
02:58 <****> I'm feeling idiotic. Sorry for the inconvenience.
02:58 <****> corrideat, which is why I told you to run the exact query I typed, and not add WHERE clauses.
03:00 <****> Simetrical, I just copied the code I had upside and removed the other fields, but not the WHERE clause.
03:01 <****> i have a question...
03:01 <****> ask
03:01 <****> !ask
03:01 <****> lol
03:01 <****> SELECT * FROM `ticket`
03:01 <****> LEFT JOIN (`message`, `message_content`, `requester`)
03:01 <****> ON (ticket.id = message.ticket_id AND message.id = message_content.message_id AND ticket.id = requester.ticket_id)
03:01 <****>:(
03:01 <****> WHERE ticket.mask = 'JBL-26681-367' ;
03:01 <****> erm
03:01 <****> whut...supposed to be on one line o.O
03:01 <****> sec
03:01 <****> Is that syntax legal?
03:01 <****> yes
03:01 <****> SELECT * FROM `ticket` LEFT JOIN (`message`, `message_content`, `requester`) ON (ticket.id = message.ticket_id AND message.id = message_content.message_id AND ticket.id = requester.ticket_id) WHERE ticket.mask = 'JBL-26681-367' ;
03:02 <****> it returns 49 rows though
03:02 <****> the most rows in any of those tables is 7
03:03 <****> What are the extras, then?
03:03 <****> cartesian product?
03:03 <****> yea it's cartesian
03:03 <****> There you go, then.
03:03 <****> so shouldn't it return the most amount of rows for any given table?
03:04 <****> so the rows can be # message * # message_content * # rquester
03:04 <****> multiply of the tables, not the max of 1 table :)
03:04 <****> oh
03:04 <****> it joins all rows from each table
03:04 <****> so
03:05 <****> tbl1.rowa gets joined to both tbl2.rowa and tbl2.rowb?
03:05 <****> Yes.
03:06 <****> Unless your join condition doesn't allow that.
03:07 <****> how can i query for the max number of characters in a particular table field? I want to make sure i have not allocated excessive character lengths to my fields
03:07 <****> spg2007, why do you care? You're using VARCHAR anyway, right?
03:08 <****> okay, so what do i need to change my join to? i only want to join the message table to the message_content table when message.id = message_content.message_id
03:08 <****> are there not performance and space gains to be realized if i minimize the size of fiels?
03:09 <****> spg2007, VARCHAR(255) uses the same space as VARCHAR(5), for the same length of strings stored. (VARCHAR(256) uses more, though.)
03:09 <****> in some cases i am using TEXT
03:09 <****> anyone?
03:09 <****> spg2007, the answer, in any case, is SELECT MAX(LENGTH(column)) FROM table; which is a table scan and will take a while.
03:10 <****> spg2007: procedure analyse, search for this in the manual
03:10 <****> it works for all column types
03:13 * Simetrical likes how calls to the session table have magically turned into type=system queries on his test box
03:13 <****> thats exactly what i need... thx!
03:16 <****> ahhh
03:16 <****> i dont even need a join i guess
03:18 <****> gah
03:18 <****> it only returns 2 rows
03:18 <****> SELECT * FROM `ticket`,`message`, `message_content`, `requester` WHERE ticket.mask = 'JBL-26681-367' AND message.ticket_id = ticket.id AND message_content.message_id = message.id AND requester.ticket_id = ticket.id;
03:24 <****> oh
03:24 <****> nevermind
03:24 <****> i think it is working. lmao
03:25 <****> what's so funny?
03:25 <****> thumbs: PEBKAC has stuck again. this time, my query was working and i was expecting it to do something else...dunno why
03:25 <****> it does exactly what i want
03:30 <****> anyone know of a script like automysqlbackup that allows you to specify tables? I hate re-inventing the wheel if it already exists.
03:30 <****> mysqldump
03:31 <****> thanks mr obvious
03:32 <****> mysqldump + rsnapshot
03:59 <****> at which point is the table_cache too large? assuming fd's are not an issue
03:59 <****> are there any concerns there performance wise?
04:02 <****> Simetrical|away: rsnapshot looks interesting
04:03 <****> hello all.
04:03 <****> I have a question.
04:04 <****> does mysql do locks on its database files?
04:04 <****> in other words, can another application access the database files at the same time as Mysql/
04:04 <****> ?
04:08 <****> kevev: do you mean other processes access the raw data file on the filesystem level?
04:09 <****> mikegriffin: yes.
04:10 <****> kevev: there is an option to lock the files, of course it needs filesystem enforcement too
04:10 <****> kevev: external-locking
04:10 <****> ok.
04:10 <****> kevev: and skip-external-locking
04:11 <****> but in a generic implementation an external application can access the files?
04:11 <****> the fs can enforce the lock, so need a fs that has such support
04:12 <****> I am testing replication. I know mysql does this. But I am going to test a file replication system instead. I want to see if there will be issues.
04:12 <****> I will use ext3.
04:12 <****> I do not want the files locked.
Total 30 pages. You are browsing page 4/30.
First :: Prev :: [...] [2] [3] [4] [5] [6] [...] :: Next :: Last