IRC Logs

Network: freenode Channel: #mysql

	Enter your search terms Submit search form
	Web www.irclog.org

[22:44]<zj2__>is there any efficient way of backing up a (possibly 1gb+) database via a web interface without either loading the entire contents into RAM and/or killing the server's performance?
[22:44]<drgynfn_>How about this one?
[22:44]<drgynfn_>http://pastebin.ca/110580
[22:45]<xjzfnv>rob__: There's a PHP script that does staggered dumps.
[22:45]<rrsjggysgg>21435 rows in set (2.73 sec)
[22:45]<rrsjggysgg>It likes that JOIN :)
[22:45]<dgcd>i have a little question about a db structure : i have to store many values that are just true or false and it should be scaleable! anyone has a good idea about that ?
[22:46]<xjzfnv>mnky: How many true/false?
[22:46]<dgcd>there are now about 20 but it should be scaleable so you never know ;)
[22:46]<drgynfn_>And if you take that same query and add a CREATE VIEW INVOICES_TOT AS to the top of it?
[22:46]<vnr2jw999>what happens if i updated a mysql3 server to mysql4 will all the data/tables go crazy?
[22:47]<xjzfnv>mnky: You could use bitwise functions...ie, first question if true adds 1 to the number, second 2, third 4, fourth 8, fifth 16....
[22:47]<rrsjggysgg>What about it.
[22:47]<mrvvknffd>is there a way to fetch a random row?
[22:47]<drgynfn_>is a select * from invoices_tot significantly slower?
[22:47]<hzzzysjgf>Xorlev: that is pretty unscalable to a changing number of questions
[22:48]<dgcd>jup i also thought about that but what if i want to find anything with a special option?
[22:48]<rrsjggysgg>Right now, SELECT * FROM INVOICES_TOT gives 22467 rows in set (7.37 sec)
[22:48]<xjzfnv>HarrisonF: If it got too big I suppose.
[22:48]<rrsjggysgg>let me do that one
[22:49]<hzzzysjgf>mnky: you want three tables, questions, answers, tests (or users depending on what you are storing data about)
[22:49]<rrsjggysgg>21435 rows in set (2.74 sec)
[22:49]<drgynfn_>Okay.. well then we have proved that there are no issues with the view you created or any of its parts. :)
[22:49]<rrsjggysgg>That's good.
[22:50]<drgynfn_>So we just have to find a way to get rid of all the derived queries in that nasty one.
[22:50]<rrsjggysgg>I've tried forcing indexes, but it doesnt seem to want to work
[22:50]<rrsjggysgg>And i dont think you can force indexes on derived queries
[22:51]<tjmjigtx>does anyone know if there are any functional differences as far as php5.1 is concerned between compiling against libmysqlclient.so.14 and libmysqlclient.so.15 ?
[22:51]<drgynfn_>jasoneisen: What is the English explanation of what that second query is trying to do?
[22:51]<dgcd>thats a good idea with the bitwise functions but what if a option is removed or a new option was created? how do i handle that?
[22:52]<rrsjggysgg>find the next acct_id with a lower total inv_31_60 value of all of its invoices
[22:53]<xjzfnv>mnky: A new option would be pretty easy...if they answered true then the next power of 2 would be added, but remove isn't as easy...probably not the best solution.
[22:54]<dgcd>i may need a function to calculate all option-lines so i also need to store what option is on what bit
[22:55]<xjzfnv>You could serialize an array.
[22:55]<drgynfn_>jasoneisen: Bah! I just can't visualize the data well enough. Do you think you could give me four or five rows of dummy data that I could load up and play with?
[22:55]<xjzfnv>But then you can't search by it.
[22:55]<dgcd>Xorlev: no its a pretty nice and strogare friendly idea but its not good to handle
[22:55]<drgynfn_>mnky: Have you considered a wide to long transformation?
[22:56]<crgguggu>what is a primary key made of more than one field called again?
[22:56]<crgguggu>composite?
[22:56]<crgguggu>what's the technical name for it...
[22:56]<tjmjigtx>ok... I'll take that as a no.
[22:56]<dgcd>DanielE_: sry i never heard of that - whats that about ?
[22:56]<rrsjggysgg>ACCOUNTS has COLLECTOR_ID, ACCT_ID, ACCT_DUPLICATE, and ACCT_COMMENT_DATE
[22:57]<rrsjggysgg>INVOICES has COLLECTOR_ID, ACCT_ID, INV_ID, INV_31_60, INV_61_90, INV_91
[22:57]<xjzfnv>I'm curious about wide-to-long as well...I've never heard of it.
[22:58]<drgynfn_>mnky: rather than storing the data that might change as columns in the table, you create a secondary table that contains each of those data members as a row. The long part of that is very true however. If you had two products with 20 attributes, there would be two rows in your products table and 40 rows in your product_data table.
[22:58]<drgynfn_>20 rows with a FK of product 1 and 20 rows with a FK of product 2.
[22:58]<xjzfnv>Ahh, I thought of that.
[22:59]<xjzfnv>That'd work...
[22:59]<dgcd>let me read it again ;)
[23:00]<drgynfn_>jasoneisen: I guess, what I'd like to get would be five rows of dummy data where you could say if you pass in this collector_ID and this account_id, then this row of data would be the right one to return.
[23:00]<drgynfn_>jasoneisen: That way I could play with some join alternatives and have a good chance of knowing if I "got it right"
[23:01]<rrsjggysgg>I don't have any dummy data... :(
[23:01]<dgcd>DanielE_: so i create a new column for every option i wanna store but just in a new table?
[23:02]<drgynfn_>mnky: No, all options go in the same _data table. the _data table has a PK auto int, FK parent_id, FK option_id, value tinyint
[23:03]<dgcd>ah now i got it
[23:03]<dgcd>nice idea
[23:03]<xra>mnky: It's like a properties table [property_id, property_name, property_value, and optionally a product_id here or in a relationship table]
[23:04]<drgynfn_>jasoneisen: If you could just take the schema of the invoices_tot view, create five rows of dummy data and then post a dump of that mock table so I can load it up and play with it, I'll be happy to try to help you further.
[23:04]<rrsjggysgg>DanielE_: Working on it...
[23:05]<dgcd>do you have a kinda example about that Xgc or DanielE_ ?
[23:06]<drgynfn_>mnky: So the advantages of long is the ability to add new types of data without changing the schema. The downside is your data table can get very big very quickly and you need to optimize the indices on it to be able to query properly.
[23:06]<dgcd>yeah i favor that bitwise storing but i don't have much time so i thing i use that option for now
[23:08]<dgcd>ok many thanks for your help
[23:09]<drgynfn_>mnky, I just exported some data from my db, let me pastebin it
[23:10]<dgcd>oh yeah that would be great
[23:11]<drgynfn_>http://hashmysql.org/paste/viewentry.php?id=2271
[23:11]<drgynfn_>Hrm.. oops, I didn't query for the same product ids, but the transformation of wide to long should still make sense.
[23:12]<drgynfn_>I didn't include the product_datum_names table that maps the id to the name like 'price' or 'brand'
[23:12]<dgcd>jup makes sense
[23:12]<dgcd>thx again ;)
[23:13]<rrsjggysgg>DanielE_: http://pastebin.ca/110619
[23:14]<drgynfn_>jasoneisen: Ouch. This will work, but I was just looking for the a sample of the aggregate data from the view. :) A lot more work than I wanted you to do. :)
[23:15]<rrsjggysgg>I can't give samples, its sensitive data
[23:15]<zgmzgw2zdsjg>have you considered getting it counselling ?
[23:15]<zgmzgw2zdsjg>could reduce its sensitivity
[23:16]<drgynfn_>jasoneisen: Oh yeah, I know. I didn't mean sample as in the real stuff, I just meant I didn't need the invoices and accounts tables, only the joined structure created by the invoice_tot view.
[23:16]<xra>Self-esteem problems.
[23:16]<drgynfn_>Anyway, I'm looking at this now.
[23:16]<zgmzgw2zdsjg>could be Xgc :P
[23:18]<drgynfn_>jasoneisen: lol.. the data you gave me doesn't have any values in 31_60 or 61_90. :) I'm throwing some random in there.
[23:18]<rrsjggysgg>Ah, duh
[23:19]<drgynfn_>So what I should do is make MM01 have a 31_60 sum less than MM02? Then MM01 should be what is returned from your query?
[23:19]<rrsjggysgg>yep
[23:19]<rrsjggysgg>the sum of all the invoices
[23:20]<rrsjggysgg>I love typing random numbers into pastebin to see what people are sticking in there
[23:21]<rrsjggysgg>http://pastebin.ca/110579 this one is interesting
[23:21]<drgynfn_>spider spam. >:(
[23:22]<kgfjzrg>this is bugging me now, MySQL Error: Invalid SQL: SELECT cUsername FROM Staff WHERE cUsername='workffs'
[23:23]<mrg__v>Why would one want to flush all cached hosts regularly?
[23:23]<mrg__v>per mysqladmin
[23:24]<rrsjggysgg>Someone should write a script that archives all pastebins, makes it searchable, so you can see if someone has already made a script you need to write
[23:24]<sgjdns>jasoneisen: http://www.google.com/search?q=hobbies
[23:24]<rrsjggysgg>?
[23:25]<sgjdns>Just giving you a gentle ribbing about how you spend your free time.
[23:25]<zj2__>is it a bad idea to change any permissions in /var/lib/mysql? i am trying to back up a database using mysqlhotcopy, however the permissions set up currently mean that no user but 'mysql' can access it
[23:25]<sgjdns>Keloran: Is that the whole query?
[23:25]<rrsjggysgg>snoyes: you're the one idling in an irc

Back to #mysql
Or go to some related logs:
#php
#debian
#netbsd
#gentoo
#gentoo