| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12
Comments:
<0> right?
<1> malmen: Yes. You don't need the `` backtics.
<2> Xgc: I don't think that's quite what I'm looking for. I'm pulling some 3,000 entries from 3 different bug trackers. In my huge amalgamation of subqueries, I label each system and get back a large number of results for displaying with PHP. What I want is a count on number of results form each tracker without having to do 3 more queries to find it.
<0> done
<0> tks :)
<3> gold star to coreyo for using "amalgamatino"
<3> er, amalgamation
<1> coreyo: If you have 3 queries and you don't want to change that, you're stuck. If you're pulling data from 3 different sources inone query, you can tag that data with a derived column and count based on that. But you still need a separate counting query, for obvious reasons due to aggregation issues.
<2> litheum: *coreyo* bows
<1> coreyo: Of course, the entire mess could be combined into one query, but that's probably not what you want.
<2> Xgc: It's a single query with a bazillion different subqueries. It all ends up joined or unioned into a single table by the end, though. So I guess it is, in fact, a single query
<4> coreyo: did you get my suggested select statement... I lost connectivity
<1> coreyo: If you can add appropriate derived columns to identify the like data, you can count them.. but you won't want to do that (count) in the initial query.
<5> : :O
<1> coreyo: If you want to show an example query, I can probably show you what I mean by adjusting your example.
<2> cornell: no, I didn't see anything
<2> Xgc: k, here's what I've got. After a buttload of joins, unions, and subqueries, the ultimate result is a huge table of results that comes back. One of the fields identifies which tracker it came from (say "foo", "bar", or "fubar").
<2> so out of my final table, I want to know how many rows have a "tracker_name" field that = "foo", how many = "bar", and how many = "fubar"
<1> coreyo: Right. You want to avoid 3 separate queries to count the trackers and would like one counting query, correct?
<2> Xgc: absolutely :)
<6> case sounds like a good plan
<2> Xgc: then when I display the report that I'm generating, I can display how many total results there were, and how many came from each tracker
<2> which is really all that I want to do
<1> SELECT count(if(tracker_name='foo',1,null)) foo_cnt, count(if(tracker_name='bar',1,null)) bar_cnt, ... FROM (your gigantic query that returns tracker_name) v1
<1> coreyo: Like that.
<7> I'm having a hard time deciding where to take backups in a master/slave pair
<2> Xgc: hold for 10 minutes while I try to fit it into my query :)
<1> coreyo: The alternative form would be SELECT tracker_name, count(*) tracker_cnt FROM (your buge query with tracker_name) v1 GROUP BY tracker_name;
<7> The master has the advantage of having binlogs enabled *anyway*, but that means that I have to watch out for both backups and slaves when purging them
<7> The slave has the advantage of getting rid of the "two things use these" problem, but having binlog backups from the slave means complication if I ever need to promote it to master.
<1> coreyo: The only difference is the first returns N columns and the second returns N rows.
<7> Any thoughts?
<1> coreyo: The first form doesn't scale well and is less dynamic. The second form scales to as many trackers as necessary.
<8> how would i go about changing the mysql config to listen on my network, rather than just localhost?
<2> Xgc: so in the second form, in order to get count for a particular tracker, I would need to find a row from that tracker and look in it's count column?
<2> abre: my.cnf change listen to *
<8> cheers
<1> coreyo: Eahch row in the result is for a particular tracker (by name).
<4> Ok coreyo... what I posted, or tried, was Select system, count(*) from (...) group by system
<1> coreyo: Right. It's a list of pairs of (tracker_name, count)
<1> coreyo: Which looks similar to cornell's last comment.
<2> Xgc: maybe I misread that second form that you gave me. Let me play with it some and get back to you :).
<2> yeah....
<2> do I need to group it by tracker if I'm going to do this?
<1> coreyo: Just as I showed. You said you have tracker_name in the result. That's what you group by.
<9> hi all
<9> how can i setup my tables to allow for data that has the value of "nan" ?
<4> coreyo: xgc and I are saying the same thing: [13:26]<1> coreyo: The alternative form would be SELECT tracker_name, count(*)
<4> tracker_cnt FROM (your buge query with tracker_name) v1 GROUP BY
<4> tracker_name;
<1> coreyo: You can group by anything you wish. The name of the field and number of grouping fields is not important.
<8> coreyo, there is no listen directive
<9> i currently have the columns setup as ints or decimals but every now and then i get a data value that is nan. when my program tries to insert a nan into the table mysql gives the error that it can't
<9> how can i allow mysql to accept nan values?
<10> nan is the same as NULL
<10> its nothing...
<10> not empty, just nothing
<9> ahh.. so i just need to remove not null from my table declerations and all should be well?
<10> right, but i dont know what you would get..
<10> that makes sense to me
<2> abre: I can't remember the exact directive
<11> hi. do you know where i could find a script to synchonize 2 mysql databases ?
<9> indolent or if i did if data == NULL { data = 0; } in my C program that way mysql will always get an actual value?
<11> php script
<9> indolent would that work?
<2> there's one that sets address and one that sets port. YOu want the one that currently says 127.0.0.1, or localhost. I'd have to go delving to find it
<10> NULL in that sense would be a string null... -
<8> coreyo, it was bind-address
<8> coreyo, thanks anyway
<2> Xgc: Cornell: I think I've got it, I just need to play with it. Thanks.
<2> abre: that's it :)
<10> in MYSQL it would would work in an auto inc.. if its a varchar or text you will get the string NULL
<1> BucWheat: The engine probably isn't going to like invalid data, unless you can find a function to convert it to null before the engine tries to insert/update it.
<2> abre: I was confusing the name of the variable with the apache equivalent :)
<10> i'm not sure.. i woudl jsut test it
<11> hi. do you know where i could find a php script to synchonize 2 mysql databases ?
<10> kristalino, look into replication and or synchronization with mysql nativly.. no script
<9> Xgc, indolent the columns in question are the ones that are declared as int and decimals
<9> i'm about to test
<11> indolent, i can't do that on my webhoster :-\
<10> kristanlin: then you will have some problems i think, unles syou setup a 3rd database that you will write to, and using a script sync the changes to the slaves
<10> but i can promise you , you will have erros and missyncronization eventually
<10> i did somethin light like that i setup a fake master and used it as a wrtie que
<10> then on times increments i moved data to the read table -
<11> indolent, yes. Do you still have the script that does that ?
<10> er.. its PHP3 and ald version of mysql.. im sure i have it in a backup somewhere
<10> now i use master/slave
<11> indolent, if you find it that could save me life :)
<10> i have to look at home -- late
<10> it was pretty simple, - just read from table where updateTime (timestamp) was between 5 minutes ago
<9> Xgc does nan equal null ?
<10> then insert into the new table
<1> BucWheat: No. Nothing does.
<10> i wouldnt do that for anythign more than simpe stuff though.. nothing critical
<1> BucWheat: What did you find?
<12> 45 0 * * * /var/mysql/bin/mysqldump --all-databases --create-options --force --p***word=changeme > /www/websightdesigns.com/mysql.bak/mysql.sql
<9> i'm about to go test now
<12> can anyone tell me why that cron job wouldn't be working?
<12> i'm trying to export all my databases for backup purposes
<9> Xgc is there a function in mysql that will convert nan to say 0 or some other value?
<12> but i'm getting zero filesize when the cron does it. tons of errors, but it does work if i do it by command line myself though
<1> BucWheat: Show me what you mean by nan and what API are you using?
<2> Xgc: Cornell: okay, here's the catch. I need all of the data from the table as well as the count
<1> coreyo: You will have aggregation issues. Don't do that unless you want to just tack on the results of the counts as separate columns. It's probably not a good idea.
<2> Xgc: Cornell: using this, I could get the count in 1 query, then the actual data in another query. That might not be a bad solution.
<13> How do I enable auto increment in a field in an already created table?
<1> coreyo: Right.
<2> but I'm still doing the same query twice in a row
<12> anyone have any ideas?
<4> coreyo: are you doing the query in a program?
<1> coreyo: No. You're doing two separate things.
<1> coreyo: It's *NOT* the same query.
<2> hehe... well 2 of the same subqueries (which is a good 95% of the cpu time)
<4> Yes, Xgc, but he's thinking he's retrieving the data twice
<9> Xgc well when my C program sends mysql data mysql returns an error saying it can't insert the value "nan" into the column field
<1> cornell: He really isn't.
<1> cornell: I can understand his feelings.
<4> He's not re-retrieving the data?
<1> cornell: Not literally.
<2> Xgc: Cornell: so you're saying that it will cache the query between two different queries?
<10> is it possible to do a seelct and an update simultaneously?
<2> Xgc: er... cache the subquery between two different queries?
<10> like select where AND update set lastchecked = NOW()
<4> I don't know, coreyo, perhaps Xgc is thinking that, and he's more likely right than I am
<1> cornell: It's a separate query over the same data. We have this same problem any time aggregation is involved. People have a hard time understanding it.
<12> 45 0 * * * /var/mysql/bin/mysqldump --all-databases --create-options --force --p***word=changeme > /www/websightdesigns.com/mysql.bak/mysql.sql ...does this line in my crontab look ok to everyone?
<1> coreyo: If caching is to occur, the engine will do it. If you want to generate a materialized view, feel free. MySQL doesn't support that yet.
<2> Xgc: what I'm saying is that we have a query, and we have the same query with a "count" wrapped around it. that's what I'm talking about when I say using the same query. Set me straight if I'm taking the wrong approach, I ws just hoping to optimize
<1> coreyo: Your choice is to generate a temp (or not so temp) table to hold the intermediate data.
<1> coreyo: Trust me. I know why you're asking.
<2> Xgc: k, I can trust you :)
<1> coreyo: It's the same issue we all face all the time.
<2> Xgc: I have not yet used temporary tables. My only concern with something like that would be race conditions.
<2> Xgc: but I don't know how temporary tables work at all, I've not had occasion to use them yet
<1> coreyo: You can always *lock* in some way. Not nice, but you could.
<2> Xgc: after all of this, it might just be better to do 2 queries :)
<2> Xgc: but it's still better than the 4 that I was hoping to avoid. The count query is also quite a bit faster than the data query when you factor in IO time from the MySQL server too, so it's not so bad
<2> Xgc: Cornell: k, I've got a good solution now, thanks guys
<4> Anyway... my permissions problem... I'm not understanding this... I've looked at my file system and I see a directory in /var/lib/mysql which corresponds to my database. The owner and user is mysql, and mysql has rw authority. So... a mysql defined user, accessing that database is doing so under mysql's authority and hence should be able to access it, even though that user's not defined on to the system, right?
<2> cornell: not defined in the operating system, or the Mysql grant tables?
<4> not defined on to the system... i.e. the operationg system.
<12> is everyone just busy or does noone really know? i'd have thought it to be a pretty simple question really...
<4> I'd created a user in mysql and not in the operating system. I granted him insert, update, select authority on the table in question. And my selects failed. When I attempt the select logged into mysql with a userid that is also known to the operating system, it works.
<10> ezrafree whos crontab?
<10> rooit?
<10> root?
<10> or your ssh account's
<4> DynamicWebProject\apras\Webcontent\WEB-INF\lib\web.xml
<4> <?xml version="1.0" encoding="UTF-8"?>
Return to
#mysql
or
Go to some related
logs:
AC_PYTHON_DEVEL freebsd
max_allowed_packet upload ruby on rails linux
#suse
#debian
ubuntu disc image mounter
edubuntu alsa-oss deb
#gentoo
xhephyr
No rule to make target libtorrent
reiserfs3 defrag
|
|