| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Comments:
<0> and giving away their "free" Oracle database
<0> and free beer
<1> Bleh, beer :P
<0> so we were all taking the piss saying "well at least the BEER is REALLY free"
<1> haha
<0> sat in the back of some install fest with MySQL shirts on
<0> was a good laugh at least :)
<2> Leithal: what about "SET local sql_log_off=1; run your query... "
<0> TodoInTX: Again that effects the statement that *fires the trigger, not the statements the trigger fires*
<0> you usually want the actual statement against the table (the triggers the trigger) to be replicated
<3> Leithal, ok... i've got 2 tables.. to test this... master.main .. master.testing ... I put a trigger on master.main to put the stuff in master.testing... and the data doesn't replicate from master.testing.. is there a way to get the trigger begin/end block to replicate the inserts into the master.testing ?
<0> create the trigger on the slave
<0> exactly the opposite of *not* doing it ;)
<0> !man procedure replication faq
<4> Nothing found.
<0> damn you
<3> i've already read that
<3> i read the whole 2 chapters twice over heh
<3> just getting it all tested out
<0> maybe you skipped a section again eh ;)
<0> http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-replication-faq.html
<3> *cry*
<0> *
<0> Do triggers work with replication?
<0> Triggers and replication in MySQL 5.0 work the same as in most other database engines: Actions carried out through triggers on a master are not replicated to a slave server. Instead, triggers that exist on tables that reside on a MySQL master server need to be created on the corresponding tables on any MySQL slave servers so that the triggers activate on the slaves as well as the master.
<0> *
<0> How are actions carried out through triggers on a master replicated to a slave?
<0> First, the triggers that exist on a master must be re-created on the slave server. Once this is done, the replication flow works as any other standard DML statement that participates in replication. For example, consider a table EMP that has an AFTER insert trigger, which exists on a master MySQL server. The same EMP table and AFTER insert trigger exist on the slave server as well. The replication flow would be:
<3> !learn procedure replication faq is http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-replication-faq.html
<0> 1.
<0> An INSERT statement is made to EMP.
<0> 2.
<0> The AFTER trigger on EMP activates.
<0> 3.
<0> The INSERT statement is written to the binary log.
<0> 4.
<0> The replication slave picks up the INSERT statement to EMP and executes it.
<0> 5.
<0> The AFTER trigger on EMP that exists on the slave activates.
<0> !define procedure replication faq as http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-replication-faq.html
<4> Defined.
<3> !define leithal is a god
<4> You aren't allowed to do that.
<3> !man leithal
<4> Nothing found.
<5> !tell use about Leithal
<4> But I don't know a thing about that.
<5> what! forgotten
<0> s/use/us
<5> !tell us about Leithal
<4> But I don't know a thing about that.
<3> !tell us about leithal
<4> But I don't know a thing about that.
<0> s/leithal/leith
<0> heh
<3> !tell us about leith
<4> xlx asked me to tell you this: Leith wants to be Mr.Anonymous but talks way too much for that. He's even helpful!
<0> I don't talk nearly enough any more :(
<5> any idea when the manual docbook sources will be available to us anons
<0> !define Leith as Leith's always locked away in a dark room by Therion, if you want his attention offer him Oreos
<4> Defined.
<5> fresh out of oreos
<0> archivist: Has been discussed - they are going to put them online some time
<5> Ive been playing with php's docs recently, its easier to make bot and search engine indexes
<3> Leithal, a trigger that causes a deadlock... any example code that you know of, laying around?
<3> err, that handles a deadlock situation
<0> "handles a deadlock situation"?
<0> well you can do things such as insert / select / insert in a round robin fashion etc.
<0> no code hanging around off the top of my head
<6> how do you do to only select from a field if it contains digits?
<0> RLIKE '[[::digit:]]'
<0> without typos
<0> !man regular expressions
<4> (MySQL Regular Expressions) : http://dev.mysql.com/doc/mysql/en/Regexp.html
<6> thanks
<3> Leithal, ok maybe you can help me with this.. i asked this a few days ago but nobody had a solution
<0> well if you can hang around for me sure
<0> I just started work and have a backlog to clear
<3> i've got 1100 dedicated to innodb buffer pool.. tables running off sdb ... 400M dedicated to key_buffer .. and with a table of 65M rows (fixed) .. a delete of 400,000 rows takes forever. Now, if I disable the innodb, it takes mere minutes... Yes the innodb stuff is active, but where the myisam is on a different drive sda , it's utilization is near 100% .. so I dunno why it takes so damn long
<3> yes indexes are created.. it's a delete from table using smalltable , table where smalltable.id = table.id;
<3> only 1 primary index on the table.. the index size is 660MB on disk
<3> machine doesn't swap
<7> when I make cross-products of tables, is it "ok" to eliminate the duplicates with "GROUP BY"?
<3> ndee, it's ok to do anything that doesn't hinder performance i'd say
<0> tried using a transaction and commiting periodically?
<3> the table is a myisam table...
<0> oh I see
<7> xlx: ok
<3> the 2 necessary .. always updated/inserted tables are innodb.. the less frequently used table is myisam but it takes forever to update/delete from the table
<0> xlx: strange that innodb has an effect on it
<3> seperated on 2 disks
<0> there would be a bunch of stuff I'd want to analyze on that
<0> but if it's MyISAM, first thing you want to do is increase the key buffer as large as you can
<0> *sometimes* it's far faster to insert....select to a new table and drop the old table
<3> already is ... key on disk (MYI is 650M) .. key buffer is 400 M
<0> that probably would not be the case for you with 65million rows though
<0> for *that* table
<0> is that the only table?
<0> what is the key buffer hit rate?
<3> yeah
<0> what are the rest of the status variables like?
<0> etc. etc.
<0> What you want to find out is the influence that InnoDB is having
<3> i probably haven't used it since i rebooted but :
<3> | Key_blocks_used | 272158 |
<3> | Key_read_requests | 2372596184 |
<3> | Key_reads | 20635795 |
<3> | Key_write_requests | 325218479 |
<3> | Key_writes | 95378298 |
<3> i should flush status and redo it
<3> i periodically create tables and stuff so those key* status variables are probably out of wack
<3> whack
<0> not bad then - there must be some other influence - I would not expect performance to differ that greatly if you are running something that should have no influence from InnoDB
<3> those stats are probably from my select into outfile, load data into... to get around the m***ive update/delete
<0> That's probably the best way to be doing it right now as well ;)
<0> But I'm still interested in why innodb apparently effects it
<6> i have a enum column with four values, how do i alter it to drop one value?
<3> Leithal, i was thinking about maybe creating a trigger to do it immediately and not have to do it over time... do you think it'd still be better to recreate the table twice a day or something than to run a trigger?
<0> archivist: There is a request in to make an svn tree for th docbook stuff
<5> ok thanks
<0> xlx: which ever performs the best with minimal impact eh ;)
<0> that would most probably be a trigger, in a 24x7 shop
<0> if you have enough maintenance time - then the table modification
<8> Leithal, i'm xlx just on a different computer... Do you happen to know if you can get a trigger to not insert the row?
<0> based on what?
<8> master.blah <== inserts .. on the slave ... I want to have a trigger that executes before the insert and does a bunch of sql and then does not insert into the table
<0> put a different trigger on the slave
<0> do the other bunch of stuff
<0> invalidate the insert
<0> i.e a BEFORE INSERT that does something like setting a NOT NULL column to NULL
<8> that's the only way.. invalidate the insert? (seems crazy)
<8> you can't do .. leave ?
<0> SET NEW.col = NULL
<8> yep i got what you said but it seems dirty
<0> hmm not sure if you can leave or not in a trigger
<8> hackery, where a "leave;" would work nicely
<8> i guess i'll try it
<0> you can tell me ;)
<0> problem is - even if you could leave - I'm not sure if that would effect the triggering statement
<9> hashmysql.org:4180 | Wiki: hashmysql.org | Doc: http://
<0> it would only effect the trigger
<9> why is it the PHP $_GET array has always a value in it even if the user used a $_post ?
<8> Leithal, why can't you do : create trigger trig_bi before insert on go_queried for each row insert ignore into go_trig select new.*; |
<8> why can't you do .. select new.* ?
<8> i know it's bad, i'm just wondering
<0> Why?
<0> why would you?
<0> you just want to fill in all the new values?
<0> that would be a nightmare
Return to
#mysql
or
Go to some related
logs:
#linux
jpilot avantgo fedora
failed to start the x server debian
#suse
pattern mathching perl script
#linux
Khereth
ljbuesch
linux tee error
#mysql
|
|