BEGIN/COMMIT statements not written to the binary log : may it cause problems with replication ?
Hello there, I use MySQL (4.0.1) with InnoDB tables with binary logging on. I see that the BEGIN and COMMIT statements that wrap my queries are not written to the binary log. I perfectly understand this if I have one server. But assume I have a master server, and a slave server that replicates the master. Let's say I distribute reads. Let's say I issue the following queries on the master (I use BEGIN and COMMIT for consistency) : BEGIN; update accounts set sum=sum-100 where num=10; update accounts set sum=sum+100 where num=12; # I don't work in a bank !! COMMIT; then only update accounts set sum=sum-100 where num=10; update accounts set sum=sum+100 where num=12; is written to the master's binary log, and then propagates to the slave. Assume that, while it is propagating I issue a reading query on the slave : select sum from accounts where num in(10,12); Then, to my mind, this select might be treated AFTER the first update being processed by the slave, and BEFORE the second update being processed by the slave. Then I'll get not-consistent results. This would not happen if BEGIN and COMMIT had been written to the master's binary log. Does anyone here agree or am I misunderstanding anything ? If this problem really exists, is it to be fixed ? Thanks ! Guilhem BICHOT IPSN/DIR/SG/SI 01 46 54 92 31 mailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: transaction
Yes MySQL supports transaction. But you have to use certain table types : for example InnoDB or BDB (and not MyISAM). I use InnoDB ; read the following section of the manual : http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#I nnoDB it will tell you which options you may have to set in your my.cnf or my.ini file. After that, you will have BEGIN/COMMIT/ROLLBACK, automatic crash recovery, etc. Guilhem -Message d'origine- De : Felik Harmanto [mailto:[EMAIL PROTECTED]] Envoye : mardi 5 fevrier 2002 12:10 A : [EMAIL PROTECTED] Objet : transaction hi, is mysql support transaction? how to setup it? please help thx felik __ Do You Yahoo!? Send FREE Valentine eCards with Yahoo! Greetings! http://greetings.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Spam Filtered - Re: Making Unique Pairs ??
alter table your_table add unique index (path_id,word) ; -Message d'origine- De : Jon Shoberg [mailto:[EMAIL PROTECTED]] Envoyé : mardi 5 février 2002 16:40 À : [EMAIL PROTECTED] Objet : Spam Filtered - Re: Making Unique Pairs ?? HUH ? Oh well lets try again, please read below ... SQL = SELECT help FROM mysql.list WHERE spam NOT LIKE 'bounce%' Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: I have a table such as IDpath_idword ID is the primary key unqigned int path_id is a foreign key unsigned int word is a single word of text varchar (32) How can I set up the table such that all -pairs- of PATH_ID and WORD are unique? The table should look like the following 12jim 23tom 32joe 47jack but if I try an insert '2' and 'jim' again, the statement fails. Any thoughts? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
