<salle_> aestetix: Bad guess <salle_> aestetix: InnoDB does not lock tables <aestetix> ah, ok <aestetix> I’m just seeing that in munin <_raymond_> salle: well… it does in some cases. But not likely that aestetix is seeing. <salle_> _raymond_: I know <_raymond_> aestetix: Perhaps you should better understand what exactly it is munin is reporting. 
<fayaz> salle_: that’s the problem, both transaction may read and then update the same row <aestetix> I was just looking at the “table locks” graph <_raymond_> aestetix: And what does that mean to munin? <salle_> fayaz: There is no problem with that. Isolation as defined in ACID handles it <_raymond_> fayaz: You haven’t answered my question. <salle_> _raymond_: He can’t 
<aestetix> _raymond_: well, it’s just displaying whatever number it’s getting <fayaz> _raymond_: i could simply use SimpleDB or any similar datastore <aestetix> Unless I misunderstand your questino <_raymond_> aestetix: “whever number it’s getting” Don’t you think that maybe you should know what, *exactly* that number is, and perhaps even what it means? <_raymond_> fayaz: fail <salle_> aestetix: Great. Now you have two options: * understand what exactly it displays there; * learn how to use SHOW GLOBAL STATUS yourself without need to rely on another monitoring tool <aestetix> ah, ok <_raymond_> fayaz: Do you understand how SimpleDB locking works? <aestetix> thanks <fayaz> _raymond_: i guess so. but i’ll be using eventually consistent reads. <_raymond_> … <_raymond_> fayaz: Do you have any idea what it is you are saying? Or are you just string together various marketing phrases? <fayaz> _raymond_: i hope not <salle_> fayaz: Prove it <_raymond_> fayaz: well, near as I can tell… you lack fundimential understanding but sound like the people I see manning the booths at trade shows. <hemanth> mysql dump says dump completed, but not able to see the tables 
<hemanth> what am i missing? <_raymond_> hemanth: eh? <hemanth> _raymond_: fixed, dumb me…was using mysqldump < rather than mysql < grrr <_raymond_> hemanth: Oh, yea. Not gonna work so well. <hemanth> heh heh, me migrating drupal to wp <_raymond_> sounds like fun… not. <fayaz> guys, i just need to have some way so that a very long transaction could read the data that was updated by a smaller transaction (that started later). <mi6_x3m> hello <mi6_x3m> how do I solve duplicated IDs on join? <_raymond_> mi6_x3m: eh? <mi6_x3m> _raymond_: nothing, I am an idiot <_raymond_> seems to be a common theme today. <mi6_x3m> what else happened <_raymond_> mi6_x3m: someone was trying to import data using mysqldump <_raymond_> mi6_x3m: and before that we had someone speaking in tongue ( lots of marketing speak and hype ) <mi6_x3m> Xgc: <mi6_x3m> do you keep logs from yesterday? <mi6_x3m> can you paste me the final query we discussed <_raymond_> mi6_x3m: a bit early for him to be awake <mi6_x3m> or does anyone keeps logs <Vacuity> 02:51 mi6_x3m: SELECT … FROM (Users AS u CROSS JOIN UserRoles AS r) LEFT JOIN AssumedRoles AS ar ON ar.role_id = r.role_id AND ar.user_id = u.user_id; <Vacuity> that timestamp is CET, GMT+1 <mi6_x3m> Vacuity: yes, thank you that was it <ruslan_osmanov> hi. Let’s assume we’ve a field like `a` int unsigned not null default 0 and a row with a = 2; update table set a = a – 10 causes an overflow, and `a` becomes a large number – 65535 or so. <ruslan_osmanov> is there a mean to protect against such kind of occasions ? <ruslan_osmanov> well, i know i can update table set a = if (a >= 10, a – 10, 0); But is there something more beautiful? <ruslan_osmanov> and I wonder, why the “unsigned” field property doesn’t work as wanted here? <dokma> is there any obvious simple way that I just cannot see to sort records by pid (parent id in the same table) field?? <dokma> pid btw forms an uninterrupted linked liste that connects all records <dokma> so it forms an implied sorting order <_raymond_> ruslan_osmanov: know thy data. <_raymond_> ruslan_osmanov: you can UPDATE t SET a = a – 10 WHERE a > 10; <Vacuity> SET a=max(0,a-10) <Vacuity> greatest() <Vacuity> omg… <_raymond_> dokma: what? <dokma> _raymond_: you’ll have to clarify that a bit <ruslan_osmanov> _raymond_, it won’t reset the value to zero(while it is needed). I think the best is greatest(0, a – 10) as Vacuity suggested. Vacuity, thank you. I thought of SET sql_mode=’NO_UNSIGNED_SUBTRACTION’. B<_raymond_> dokma: what you have asked makes no sense. <dokma> _raymond_: it makes perfect sense, you’re misunderstanding some part <lpe> any replication ninja avaialable? <ruslan_osmanov> dokma, try to join table with itself: select a.* from tbl1 as a join tbl1 as b on a.pid = b.id … Something link this <_raymond_> !t lpe ask <lpe> “Last_Error: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupt<ubiquity_bot> lpe: Don’t ask to ask, Don’t state: “I have a question”, Don’t ask: “Is anyone around?” or “Can anyone help?”. Just Ask The Question. Also, please read: http://workaround.org/getting-help-on-irc <lpe> that’s my question <_raymond_> sounds like the master crashed. <lpe> and I think I’ve tried every single solution on the internet so far <lpe> the master is fine <dokma> ruslan_osmanov: hmmm… I can’t see immediately how I could follow that up to get a sorted result set <lpe> I’ve tried changing position to everything I can think of, it doesn’t resolve the issue <ruslan_osmanov> dokma, likely you need * left * join <lpe> followed this http://www.softwareprojects.com/resources/programming/t-how-to-recover-from-mysql-replication-event-too-sma-1859.html and http://www.am3n.net/2009/02/09/replication-stop-relay-log-file-corrupt/ <lpe> no difference <dokma> lpe: why don’t you do a full reset? <lpe> ‘couse it’s >300GB data to transfer on a 100Mbit connection <lpe> it takes absolute ages <VectorX> gtowey i was able to adjust the indexes on that query to finnaly use indexes, saved about ~1 sec <dokma> lpe: it probably does not <dokma> you can figure out which tables remained absolutely the same <lpe> should’ve done innodb_file_per_table with xtrabackup from the beginning, but I wasn’t around at that time <dokma> just md5 all rows together and compare all tables <lpe> well, it will still be a lot of data <lpe> how would you do that, btw? <lpe> shell example <dokma> you would combine CONCAT or the other concat that can concatenate rows to concatenate all data in a table and then call md5 on it <lpe> concat on >1b rows would take quite some time <lpe> as well it beeing a few hundred tables <dokma> let it run while trying to figure out a proper solution <dokma> I hope your server is not updating atm? <dokma> master that is <lpe> it is <dokma> then this approach is pointless <lpe> why’s that? <lpe> won’t it sync the new data through the logs if specify a position back in time? <lpe> (which I’ve already written down, obviously) <dokma> well by the time you figure out which tables are still the same that could obviously change <lpe> ah, yes <lpe> the md5 approach indeed <lpe> I would stop it beforehand, obviously <dokma> no, my approach was to figure out how many tables are still the same <dokma> so if the amount of data to transfer is small enough you could just scp it over <lpe> how about if I flush the logs on the slave, stop the mysqld on the master, scp the logs then start from my formerly written down log_pos? <dokma> I’ve never checked the binary log my self but is it really binary or does it contain sql queries as well? <lpe> it’s binary, but there’s a tool to read it <lpe> mysqlbinlog <dokma> does it give you anything usefull on what has become corrupted? <lpe> no, nothing <lpe> ‘couse the master ain’t corrupted I believe <dokma> so the slave is corrupted? <lpe> probably <lpe> or something alike <dokma> do you know where does the slave hold the record of how far it has updated it self? <lpe> no <lpe> perhaps in back log <dokma> because if it is not in the binary log then you can just copy it and restart slave <dokma> it should just try to continue updating… <lpe> on the master I have a position after we put read lock on it and shut the daemon down <lpe> then we scp’d over all the 300G’s <lpe> then used that position <lpe> and we got these errors <lpe> so if I can just try to delete the logs on the slave <lpe> scp over new logs (after shutting down the mysqld again) <dokma> what? why didn’t you reset the slave after the copying? was master running while scping? <lpe> and use the same position <lpe> no, master was off <lpe> and yes, we reset the slave <dokma> well if you copied the data and reset the slave what is the problem then? I don’t understand <lpe> that aforementioned error is the problem <lpe> ^ <dokma> did you manage to determine which relay log is corrupted? <_raymond_> and how did configure the slave? <lpe> no <lpe> _raymond_: what you wanna know? <dokma> I think you’ll just have to do a proper restart of the replication: stop both, reset both, copy data, start master, start slave <_raymond_> the excact CHANGE MASTER command you issued. <lpe> dokma: that will take another 12 hours or so <lpe> _raymond_: change master to masterlogfile=foo, masterlogpos=bar; basically <lpe> _raymond_: rest in my.cnf <lpe> (user/pass etc) <_raymond_> You’re useless <lpe> thanks <dokma> lpe: so at this moment you have the running master that logs a slave with the copied data that does not replicate and you do not know if the log is corrupt on the master or a slave <lpe> I can’t find any signs of corruption on the master atleast <dokma> and you tried to reset a slave <lpe> with RESET SLAVE; yes <lpe> not more then that <dokma> lpe: do you still have the copied 300GB of data on the slave untouched somewhere? <lpe> yep <dokma> the you can try again to stop the slave, reset it, copy data into slaves data folder and restart it as suggested here: http://homepage.mac.com/kelleherk/iblog/C711669388/E226281480/index.html <dokma> this procedure deletes slaves data folder with the binary logs so you’ll end up being sure that slaves log is not corrupted at least <lpe> this is exactly how we just did it <lpe> and the copy takes a bit over an hour as well <dokma> so you deleted everything in slaves data folder? <lpe> yeah, it was completely empty <lpe> except for the mysql db <lpe> but yeah, that guide is exactly what we’ve done the last few hours <dokma> then it’s the masters relay log that is corrupted <dokma> because slaves relay log is in that folder <lpe> I just noticed something <lpe> the ibdata01 file <lpe> is >250G <dokma> and since you deleted it and copied masters realy in there it must be that the masters relay log is corrupt <lpe> and has filled up the disk, 0 bytes available on that partition <lpe> that sounds like something that can fuck up quite a bit <lpe> (on the master, this is) <dokma> that’s quite “normal”, you’d have to dump and recreate all innodb files to reduce that <dokma> 0 bytes available on the master or slave? <lpe> master <lpe> but solely on the partition that holds the ibdata01 file <dokma> well then your master is not working <lpe> not the mysqld <lpe> it is, ‘couse it’s on another partition <dokma> the server is running but it cannot do anything with any innodb table <lpe> probably <lpe> I don’t know what consequences this could have <dokma> I assume that on the master your data folder is on the same partition as your ibdata1 file? <dokma> lpe: ? <lpe> dokma: no <dokma> lpe: doesn’t your master have a ton of errors in it’s logs about not having space for ibdata1? <lpe> dokma: I suspect the moron who worked here before saw that the file was getting a bit large, and instead of fixing it he moved it to another partition with more space and just changed the pointer to that partitio<dokma> lpe: try googling for ‘shrinking ibdata1′ <dokma> lpe: I don’t think it’s too hard <lpe> 111212 19:38:16 InnoDB: Error: Write to file ./ibdata1 failed at offset 65 125829120. <lpe> it is, it’s impossible actually <lpe> http://bugs.mysql.com/bug.php?id=1341 <lpe> and doing a mysqldump > and mysql < on this data is impossible <lpe> would need xtrabackup or something <dokma> I see <dokma> you’re f****d <lpe> but tbfh, I think this is a lost cause, and we just need to set up a new server and migrate one site at a time <lpe> yeah, f****ck is a good explanation <lpe> well, thanks for your help dokma, and _raymond_ I hope you die a fiery death * lpe is off <dokma> _raymond_: weren’t you the one who called him useless? why don’t you kick yourself out as well? <_raymond_> dokma: because he couldn’t follow direction. <_raymond_> dokma: his problem is easily enough solved, but his arrogoance has caused him to flonder and be useless. <dokma> hmmm…. I didn’t see the entire conversation so I cannot judge atm <_raymond_> dokma: actually, you did. <dokma> hmmm…. I don’t think I’ve seen any useful info from you, correct me if I’m wrong
Feb 132012
Pages: 1 2