<chadmaynard> nerds with your games <Formfeed> mate, you’re on a mysql channel <Formfeed> lol <Formfeed> what’s your excuse <ebergen> chadmaynard is more normal <chadmaynard> I’ve just never been one for games <ebergen> I don’t know a lot of mysql people that also game <ebergen> I know a few that have played through sc2 single player <ebergen> but none that like the multiplayer <thumbs> ebergen: I like call of duty, too! <ebergen> I have mw3 but I haven’t played it yet <danieliser> trying to design a relationship table that allows me to store several relationships. wav gonna do something like, ID, relationship_type, relA, relB. is this the right way to do it? <danieliser> also a sub-type, this way i can do type = staff->client, sub-type = preferedstaff, staffID, clientID or type = client->client, sub-type = father, clientA, clientB <gtowey> just have one type <gtowey> type and subtype are really the same thing <gtowey> then you can organize your types as a tree <gtowey> either that, or make a many to many relationship with types, so you can add any number of them <gtowey> the general principle is that you design for only three cases: 0, 1 or N <gtowey> otherwise you get stuck in the land of “well we have two, but why not a third? or a fourth?” … it never ends <gtowey> danieliser: ^^ <thumbs> gtowey: I design for 42 <Ratty> Hi, I ran into this problem: #1005 – Can’t create table ‘table.owner’ (errno: 150) … I see no issues with my SQL syntax. Here it is: http://pastebin.com/HaFjeUtH <gtowey> !tell ratty about 150 <ubiquity_bot> ratty: MySQL error code 150: Foreign key constraint is incorrectly formed –> To see the error: SHOW ENGINE INNODB STATUSG and look for the Latest Foreign Key Error –> More information and FK require<Ratty> I did that already <Ratty> I did see that already* <gtowey> Ratty: show us <Ratty> My SQL clearly shows that there is no constraint issues. <gtowey> Ratty: and show the statement causing the error… <gtowey> clearly <Ratty> gtowey: Is this what you want? http://pastebin.com/uMQvLFqr <Ratty> I think I included everything. <glaksmono> hi anyone is around? <ShockValue> hey all.. i have a mysql database on one hosted server, and would like to move it to another.. easiest way to do this including database, logins, etc? I have SSH and FTP access to both <GoMySQL> ShockValue, mysqldump -uuser -ppass -A > dump.sql <ShockValue> roger that! was googling and found similar suggestions <QbY> Is it possible to create a default on a column that is a cast()? I have a character column and want the default to be a UUID() but casted as CHAR <angelazou> quick question, i have an equi join query <angelazou> and I want to find the maximum value of a column of values <angelazou> I did something like me.item_id = max(me.item_id) <angelazou> but mysql said it’s wrong <gtowey> Ratty: so you didn’t show the error from the innodb status <gtowey> Ratty: right now I’m guessing that the entrances table hasn’t been created yet. <gtowey> !tell Ratty about fk import <ubiquity_bot> Ratty: Mysqldump handles foreign keys badly. If you get a foreign key error when importing, add SET foreign_key_checks =0; to the top of your dump file. <VectorX> hi, anyone got an idea how i can speed up this query http://pastebin.com/jpU2jqWx its taking about 1.5 seconds with about 36k records in the main table(first FROM) <VectorX> rows: 35089 Extra: Using temporary; Using filesort <gtowey> VectorX: so without a WHERE clause, there’s no way to reduce the number of rows <gtowey> VectorX: are you sure you need LEFT JOIN ? <gtowey> VectorX: it looks like you don’t <VectorX> i need to return all the rows in that table, so this is normal then ?, which LEFT are you talking about <VectorX> the first one possibly can change the but last 3 are needed <VectorX> i cant seem to shake it off 1.5secs and thats on a ssd <Defusal> hi everyone <Defusal> i just wrote a bunch of SQL that does what i need, but with a bunch of subqueries. can anyone take a look at it and tell me if it can be made more efficient without too much effort? <Defusal> http://pastie.org/3320120 <VectorX> gtowey you still there <VectorX> was waiting for a reply from you <gtowey> getting rid of the temptable and filesort would help, but there’s already an index on that id. I think it’s ignoring it because of the amount of data it has to read from other tables <gtowey> so I don’t think there’s much you can do for this query <gtowey> it just reads lots of data <nb-ben> Defusal: you’re trying to calculate ladder rankings? <Defusal> nb-ben, indeed <Defusal> with a decoupled normalized schema <Defusal> it works, i was using ruby code before, but that obviously wont scale <nb-ben> you should calculate a player’s ranking after every match <Defusal> i finally rewrote it with SQL now, but im no expert <nb-ben> and then just select ordered by the ranking <nb-ben> that would be the efficient way <Defusal> nb-ben, i can look at caching in the future, i’d just like to get the query as efficient as possible first <nb-ben> Defusal: it will never be efficient <VectorX> gtowey but i think its adequately indexed, unless i have done something wrong, if it just used them it should be faster, its just not making any sense to me <Defusal> nb-ben, so none of those subqueries can be replaced with a join? <gtowey> VectorX: “Using temporary; Using filesort” means it’s not using an index for the GROUP BY or ORDER BY parts <VectorX> exactly <nb-ben> Defusal: the better idea would probably be to select all the player information and calculate the ladder outside of mysql <Defusal> nb-ben, how could that be more efficient <gtowey> VectorX: so there’s some details of why mysql won’t use an index for that case that I don’t fully understand. it sort of looks like it should, but there’s something about the query that prevents it <Defusal> nb-ben <nb-ben> Defusal: shorter query and it will have to calculate all of them anyway <Defusal> the reason its not cached is so it can be filtered by date <VectorX> gtowey there has to be a way to make it use the index coz its in the primary table and they are all indexes appropriately <Defusal> im pretty sure SQL is more efficient nb-ben <Defusal> even with all those subqueries <nb-ben> Defusal: choose intervals at which you want to snapshot the ladder and cron snapshots of the ladder <gtowey> VectorX: if you remove all columns in the FROM clause except for a.id, does it use the index then? <Defusal> nb-ben, the user can enter *any* time range <Defusal> using natural language parsing <nb-ben> Defusal: do you agree that what you have right now is not the way to do it? <Defusal> nb-ben, not the most efficient, but the best i can come up with <nb-ben> I am trying to help you come up with something better <Defusal> i was hoping that an expert here would tell me i can replace some of my subqueries with a join <VectorX> gtowey if i take out all the joins, and leave just the a table, the group and order uses the index <Defusal> as i know that would be more efficient <Defusal> nb-ben, do you understand that due to the constaints, there is no way to cache it <gtowey> VectorX: that’s not quite what I asked <Defusal> even with all those subqueries, MySQL will be faster than Ruby <Defusal> and will scale better <VectorX> gtowey ok ill play with it a bit, i just wanted to see if someone immediately spotted a problem <gtowey> VectorX: no, I’ve seen more cases like your query and I have yet to find the time to sit down and figure out why that happens <Defusal> nb-ben, in future, i will cache the most popular queried data, such as “all time” <VectorX> 
<Defusal> but i need the most efficient way to query it, since there will be custom periods requested <nb-ben> Defusal: I am having trouble actually understanding what you did in there, I could just guess you are saving raw data in your database and you are calculating what should be presented on the fly <nb-ben> which is probably the outcome of poor design <Defusal> nb-ben, as i said, the schema must be decoupled as it is <Defusal> calculating on the fly is the only way to calculate the statistics over a custom time period <nb-ben> Defusal: how about this <Defusal> i could give you my ruby ActiveRecord models which show how my tables are associated with each other, but that’ll only help if you know ruby nb-ben <nb-ben> make a table that holds a row of player’s wins, losses, and win percent after every match <nb-ben> each row will have a timestamp column <nb-ben> and that way when people ask you for something <nb-ben> you will be able to pull it without calculating anything <Defusal> another table 
<Defusal> i already have so many <nb-ben> how many tables do you have? <nb-ben> you only need one <nb-ben> for this, at least <Defusal> well obviously <Defusal> but its another one <nb-ben> get rid of whatever you did so far <nb-ben> lol <Defusal> i cant do that! <Defusal> this is not only about wins and losses <Defusal> a lot more stats are stored for future use <Defusal> http://pastie.org/3315513 <nb-ben> just save a snapshot of the player’s stats after every match <Defusal> these are the models directly related to this <nb-ben> and you eliminate the calculation stuff from your queries <Defusal> the schema cannot change, it is the best possible implementation <Defusal> yeah well like i say <Defusal> that will probably work <Defusal> but its another table <Defusal> more things to update <Defusal> :/ <nb-ben> well, add another table if you need to <nb-ben> it is only updated after matches <nb-ben> apart from that it’s read-only <Defusal> well <Defusal> stats are updated during a match <Defusal> at the end of every round, or during a round even <nb-ben> this table’s stats are updated after the match <Defusal> they are updated in realtime <nb-ben> because it’s for ladder ranking <Defusal> *only* the wins/losses <Defusal> everything else is realtime <Defusal> and players check stats during matches <nb-ben> well, Defusal <nb-ben> you better make another table <nb-ben> and have your database looking a little less pretty <Defusal> heh <nb-ben> than making your application dysfunctional <Defusal> i guess a join is out of the question then <nb-ben> it might optimize something that fails at best case scenario <Defusal> ill only have time to add another table and everything required in the future <Defusal> i have 100000 other more important things to do <Defusal> ill just have to get to it before scaling becomes an issue <nb-ben> Defusal: this wil lstart failing once you have over 50 players in your database depending on your server <Defusal> at least the SQL is more efficient than doing it manually outside the db, for now <Defusal> how so? <Defusal> there are 287 steam_players at the moment <nb-ben> well I suppose if nobody actually accesses this page it’s fine <Defusal> 86 steam_users <Defusal> i cleared this database less than 2 days ago <nb-ben> Defusal: make a script that does a page request per second (without waiting for last one to get over) <Defusal> its barely more than a day old <nb-ben> see what happens to your server <Defusal> its not a website <Defusal> but yea <nb-ben> oic <nb-ben> I just assumed it is, my bad <Defusal> its an totally async platform <Defusal> a* <nb-ben> sorry, I can’t help you make your query better <nb-ben> though I highly recommend you implement design changes instead of thinking about this query <Defusal> thanks <Defusal> i will do that at some point in the future <Defusal> but yeah, this projects todo list is getting out of hand very quickly <Defusal> nevermind all the todo comments in my codebase :/ <nb-ben> take it slow and do it right <nb-ben> spending another day to save a few days later is better <Defusal> as long as the stats are being recorded for now, and basic stats work, thats the most important part <nb-ben> Defusal: what are you making? <Defusal> i’ve had to implement a udp server and the remote logging protocol with a ton of regex to parse the realtime logging <nb-ben> a gather bot? <Defusal> have you heard of a Pickup Bot <nb-ben> I made a lot of IRC bots that do with game servers back in the day <nb-ben> mostly for soldat and cs1.6 <Defusal> my bot interfaces with the servers, chooses a free server, sets the correct map and password, received remote logging from all the servers in realtime, and even handles ingame commands <Defusal> its for CSS <nb-ben> I see <Defusal> it also syncs with irc services to autologin to registered accounts, which are linked to steam accounts via an ingame command <Defusal> etc <nb-ben> is this an opensource project <Defusal> its quite a complex beast all in all <nb-ben> or is someone paying you for it <Defusal> not at this point in time <Defusal> i will open source it eventually <nb-ben> personal project? <Defusal> thats a little complicated <nb-ben> a project for a community for which you do free work and take a lot of stress <Defusal> it was a personal project, now its more of a “quid pro quo” kind of thing <nb-ben> heh <nb-ben> so you are not very passionate about it <Defusal> i develop my own CSS server mods for my own servers, so by extending my older pickup bot project for another server, i am getting a sponsorship for my servers <Defusal> but i intend on using it for my own pickup servers in the future, though not standard CSS <Defusal> right now, its already taken too long <Defusal> and i need to finish the most important things, and fix a couple annoying issues at soon as possible <nb-ben> I used to do things like this <nb-ben> it’s bad <Defusal> i have worked on more community projects than i can count over the years <nb-ben> how old are you Defusal? <Defusal> its built on a bot library and highly modular framework i have developed over a couple years <nb-ben> I think you should drop that commitment <Defusal> the library is open source, and i intend on releasing my framework open source in the future, once its been refactored more <nb-ben> lol <Defusal> lol? <Defusal> i have way more than just this commitment 
<Defusal> i am 22 next month <nb-ben> you obviously just want it to “work” and care about meeting expectations than the projects itself, while you are being underpaid <Defusal> but i have to start studying full time very soon <Defusal> so i wont have time for this <Defusal> nah thats not quite true <Defusal> i do enjoy working on such projects <Defusal> i like giving back to communities that i enjoy being a part of <nb-ben> but you keep the poor database design because you don’t want to put that effort into making it right <Defusal> and well, theres no other solutions remotely close to the projects i work on <Defusal> so if i don’t do it, no one will <Defusal> no <Defusal> i told you i will add a caching table layer at some point <Defusal> before scaling becomes an issue <Defusal> and this is just one of my pickup bots, for a single gmae <Defusal> game* <nb-ben> alright lol <Defusal> 3 instance of my bot is being used for 3 different games at the moment <nb-ben> i don’t really have much advice for you beyond what you’ve already dismissed <Defusal> worst part is fixing issues in one, only to break something for another game <Defusal> yeah, thanks for the advice though <nb-ben> design it smart and write it stupid <nb-ben> that’s what you want <Defusal> i will definitely implement the table at some point <Defusal> for now i wont have time to implement an interface for most of the other stats anyway <Defusal> so just as long as they’re being logged, im happy <angelazou> hi, i’m having a bit of problem with phpmyadmin import <angelazou> the import actually come from an export from another database on the same server * _raymond_ point angelazou to the /topic <angelazou> um, not the right place? <_raymond_> correct, #phpmyadmin <cjhmdm> hello, I’m setting up a load balanced web server configuration, but instead of replicating the mysql db (master-master), I’m considering using a single db for both web servers. Will I need to do anything speci<_raymond_> cjhmdm: Most have several web front ends being serviced by a single MySQL server. <cjhmdm> _raymond_ so there’s no auto increment type changes I need to make specifically for this type of set up? <_raymond_> cjhmdm: for a single mysql server? No. <cjhmdm> _raymond_ thanks <siemion> anyone available to answer a quick question about global variables and existing connections? <siemion> there can’t be 580 lurkers in here… can there? <_raymond_> siemion: No affect <siemion> hi raymond, so if I update global variables, the new value will have no effect on existing connections? <salle_> . <_raymond_> really it depends upon which one, but by and large, correct. <siemion> in order to get the new values I need to close the current connection and re-initiate a new one? <_raymond_> siemion: Unless you are in a java, or similar pooling environment, most connections to MySQL are very short lived. <siemion> the one I am interested in is the interactive_timeout value.. the one with a usual default of 28800 seconds <_raymond_> reconnect <siemion> ahh, ok, cool… thanks! I probably shouldn’t write code that keeps connections open so long anyway 
<_raymond_> siemion: depends upon application needs <_raymond_> siemion: There have been customer who complained to MySQL about the connection be limited to only one year. <siemion> wow.. <_raymond_> Vacuity: ping <Vacuity> pong <_raymond_> Vacuity: /msg ? <Vacuity> sure thing <_raymond_> ops: please feel free to remove that ban in a few hours when hopefully their connection stablizes <Vacuity> _raymond_: I like the $##fixyourconnection postfix to the ban, that thumbs brought to my attention <_raymond_> Vacuity: What is that? <Vacuity> The ban will auto-redirect the user to ##fixyourconnection <Vacuity> so he won’t show up here anymore, but knows what happened <_raymond_> Vacuity: Ok, what’ the syntax of that ban? <Vacuity> in your case: *!*chronosx@112.202.138.*$##fixyourconnection <_raymond_> Vacuity: I’m told “There is no such channel.” <_raymond_> Vacuity: Yet I’m able to /join it. <Vacuity> ah, darn. it has to exist for the redirect. :/ <_raymond_> Oh, it exists <Vacuity> try again <_raymond_> This is what I’m issuing: /ban chronosx$##fixyourconnection *!*chronosx@112.202.138.*$##fixyourconnection <_raymond_> ah, I see the issue. * _raymond_ sighs <fayaz> hi <fayaz> i have an innodb table in my schema, which holds some commonly shared data between users. so several threads would be updating them concurrently. currently, i’m thinking of moving the particular table out of my<salle_> fayaz: Obviously it the simplest solution is to not move it <salle_> fayaz: No change = simple. Any change = complex <fayaz> salle_: i need to let the threads update the tables, without any conflicts… <_raymond_> fayaz: And what datastore provides that? <salle_> fayaz: Perfect task for InnoDB <fayaz> i mean by ignoring the conflicts <salle_> fayaz: You can completely ignore conflicts only in case you don’t update any data <aestetix> hey, I just converted a database over to innodb, and I”m seeing massive table locking issues
Feb 132012