#mysql
10 November 2007
Total 22 pages. You are browsing page 1/22.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Sat Nov 10 00:00:09 2007
--- Day changed Sat Nov 10 2007
00:00 <****> mysql is smart enough to compare dates directly
00:02 <****> adaptr: I want to insert a bunch of rows at once, same values for everything except the user_id
00:02 <****> how can I extract the data from a table, change the type of the field and re-import it
00:03 <****> so I want to select user_id from users, and insert the same row for all those user_id values.
00:03 <****> JZ1: mysqldump, alter table, run the dumped sql. but why not just alter the type of the filed in place?
00:04 <****> dkr because last time it delete the content of them
00:04 <****> just reset the values to 0
00:04 <****> I guess it depends on the tyep change. my alters are usually between similar types like smallint to int
00:04 <****> mine is from varchar to int
00:05 <****> ah, and an ascii "0" is not the same as the integer 0
00:05 <****> is not the same when it was 245 and became an integer 0
00:07 <****> Nice.. my INSERT .. SELECT with 'values' worked perfectly.. INSERT INTO threads_marking (thread_id, user_id, last_post_date, last_read_date) SELECT '23',id,'1194649817','0' FROM users :)
00:12 <****> Anyone help me with this query? SELECT DISTINCT users.* FROM users LEFT JOIN wanted_jobs ON users.`id` = wanted_jobs.`user_id` I'm trying to select only users that show up in the wanted_jobs table, but this is selecting all users.
00:12 <****> RobotDeathSquad: what is left join
00:13 <****> jbalint: Sorry, don't understand your question.
00:13 <****> RobotDeathSquad: what is "LEFT JOIN"
00:14 <****> A type of join? http://dev.mysql.com/doc/refman/5.0/en/join.html
00:14 <****> RobotDeathSquad: what does it do?
00:16 <****> Well, it's basically say to substitute wanted_jobs.`user_id` for the users.`id` column
00:17 <****> RobotDeathSquad: i'm not sure what you mean
00:17 <****> SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) is equivalent to: SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
00:18 <****> Does that make sense.
00:18 <****> not in your case
00:19 <****> http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php
00:20 <****> RobotDeathSquad: whats the difference between JOIN and LEFT JOIN
00:20 <****> join implies an inner join, left implies an outer left join.
00:20 <****> right
00:20 <****> RobotDeathSquad: and whats the difference between inner and outer join?
00:21 <****> inner is where both values are represented in both tables
00:21 <****> outer represents all valuesets where the left is existant. likewise with right, except in reverse.
00:21 <****> RobotDeathSquad: does this make sense?
00:22 <****> Yes, and it's all stuff I know.
00:22 <****> RobotDeathSquad: why are you using LEFT JOIN in your query?
00:23 <****> because i want all the values in the left table
00:23 <****> that are also in the right.
00:23 <****> gah
00:23 <****> whatever
00:24 <****> ??
00:24 <****> why would you use a left join for that
00:24 <****> makes perfect sense to me?
00:24 <****> pnof: it does?
00:25 <****>right, even if right is nonexistant, yes?
00:25 <****> no, "because i want all the values in the left table" "that are also in the right."
00:26 <****> ah, in that case he'd want an inner.
00:27 <****> Ah, I guess I misunderstood.
00:27 <****> I thought it was only if right existed.
00:27 * pnof focused on the query and misread the description.
00:29 <****> hi when restoring a binary backup do i have to restart the mysql server or is there away to so without restart, the restored tables are backends for a merge table, thx
00:32 <****> lwilke flush tables;
00:32 <****> ebergen: cool thx!
00:57 <****> is there a way to run a trigger so that it only runs on a master and replicates its action to the slaves?
00:59 <****> Hi.. I have a question.. Is there a way to tell MySQL to not use RAM at all for a certain table/database, and thus do all UPDATEs and SELECTs reading from or writing to the hard disk directly?
01:00 <****> Koper`: I don't think so
01:01 <****> :(
01:01 <****> Koper`, do you have a rational reason for wanting this?
01:02 <****> Well saving RAM would be a reason :)
01:10 <****> CaptTofu: why would you want to do that
01:12 <****> Koper`: that would make your queries incredibly slow
01:13 <****> Koper`: if you don't want mysql to take as much memory you will need to tweak the various settings that control the cache and buffer settings in the config file
01:13 <****> telling it to not cache a certain table won't help... it will just use that cache space for another table
01:14 <****> you can just turn off the query cache and set the other buffers/caches to 0
01:15 <****> or.... you could buy more ram :)
01:15 <****> but I don't see the point in operating a database at that point. even my internet tablet with 128M of ram with mysql on it uses ram
01:15 <****> yeah ram is cheap, even ECC buffered ram
01:20 <****> Hi. I have a log of users e-mail addresses and the datetime they were submitted. How do I get the e-mail only if the submission date is 14 days old or less?
01:21 <****> CURDATE
01:21 <****> !man date and time functions
01:21 <****> (Date and Time Functions) : http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
01:21 <****> SELECT something FROM tbl_name
01:21 <****> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
01:22 <****> Ok, thanks all. Lumberg I'll try that specifically. SQL I've been looking through that page most of today. :)
01:23 <****> I pasted that directly from the page link SQL gave you, btw SQL is a bot
01:23 <****> lol, thx.
01:23 <****> easy to miss I guess
01:23 <****> I thought I was close with TIMEDIFF but I couldn't get a correct syntax.
01:23 <****> I just had to do that query yesterday so I knew which one you're talking about
01:24 <****> ah, nice
01:24 <****> feel free to replace the 30 with your date-range
01:24 <****> such as 14
01:24 <****> right.
01:27 <****> Thanks again, already working.
01:28 <****> Personally, I'd just store dates as unixtime, and subtract 1209600 for 14 days old, heh.
01:31 <****> depends if you want to have the app constantly converting unix time to get a date to display for a webpage
01:31 <****> I prefer datetime for that, but when dealing with filename dates I like unixtime
01:32 <****> Yeah, it all depends. I like the convenience of having unixtime so you know h:m:s if you want them at some future point you didn't anticipate originally.
01:33 <****> (It's easy to trim unixtime down to just a date for displaying.. hard to invent h:m:s data from thin air)
01:34 <****> this is true
01:36 <****> Lumberg: I'm aware of that. There will be virtually no SELECTs at all and about 1 INSERT every 1-2 seconds that can take as much time as it wants..
01:37 <****> ToeBee: it will just use that cache space for another table <-- that's good, I guess
01:38 <****> interesting use of mysql
01:38 <****> Koper`: you said you wanted mysql to use less memory
01:39 <****> here's a part php, part mysql question.. if I do a query like this from mysql, it works perfectly.. if I do it from php, it doesn't work (presumably because of a problem with interpreting quotes?): INSERT .. SELECT with 'values' worked perfectly.. INSERT INTO threads_marking (thread_id, user_id, last_post_date, last_read_date) SELECT '23',id,'1194649817','0' FROM users :)
01:40 <****> So I have.. SELECT '23',id,'1194649817','0' FROM users.. but when I use it in php, it considers the quoted values as column names
01:43 <****> Anybody know what would cause this error when I try to build myself without the client (./configure --without-client)? "make[2]: *** No rule to make target `password.c', needed by `password.lo'. Stop."
01:46 <****> ToeBee: no, i'm saying that I wanted that particular table to not use memory
01:47 <****> Because i don't care how slow INSERTs and SELECTs are
01:47 <****> (for that particular table)
01:47 <****> you need to escape those ''
Total 22 pages. You are browsing page 1/22.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last