… we try to insert another value on the second session: transaction2 > START TRANSACTION; transaction2 > INSERT INTO t …size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle … with the replication you should change the binary log format to “row”. Depending on the statement, …
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
… MySQL manual actually says this, but not in the clearest way. It just says If you are using the binary log, concurrent inserts are converted to normal inserts for CREATE … SELECT or INSERT … SELECT statements. If you use…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 3 Comments
…_data AFTER INSERT on t FOR EACH ROW INSERT INTO t_copy VALUE(NEW.i); mysql> insert into t VALUES(1),(2),(3); Binary Log: #111213…: mysql> SELECT YEAR(CURDATE()) INTO @this_year; mysql> insert into t VALUES(@this_year) Binary Log: #111213 23:58:11 server id 1 end_log_pos…. MySQL uses the INSERT_ID variable to store that value. Binary Log: #111214 0:03:04 server id 1 end_log_pos 811 Intvar SET INSERT…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
… appropriate name. The Binary Log is the MySQL log which records changes to tables in the database. FlexCDC reads the binary log to determine what rows… to `test`.`demo` will automatically be captured. Insert data in one transaction (two rows): mysql> insert into test.demo values (NULL,2),(NULL…
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
…call is logged to the binary log. Let’s create a stored procedure to hold the transaction, which this time will insert data … what’s in the binary log: master > show binlog events in ‘mysql-bin.000011′\G *************************** 1. row *************************** Log_name: mysql-bin.000011 Pos: …
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
… innodb_locks_unsafe_for_binlog option with caution. Note disabling binary logs is not enough to trigger relaxed locks. You have to…_for_binlog=1 as well. This is done so enabling binary log does not cause unexpected changes in locking behavior and performance… 224576 MySQL thread id 1794751, query id 6994931 localhost root Sending data insert into test select * from sample ——– As you can see INSERT…
Post: Infinite Replication Loop
October 10, 2011 By 2 Comments
… write a blog post about these infinite loop of binary log statements in MySQL Replication. To explain what they are, how to identify… time those looping statement were first inserted. Another way to see it is by monitoring your MySQL server with cacti, you will… in the binary-log of mysql 4 and the master position is increased… Step 6: the slave position on the active master (mysql 1…
Post: Why audit logging with triggers in MySQL is bad for replication
September 29, 2008 By 10 Comments
… you to quickly parse the binary log files, extracting the relevant statements. mysqlbinlog bin_log.000001 | mysqlsla -lt binary – Check out http://hackmysql.com…, with the inserts being assigned a different Foo_History_Id. This is because the statements are written to the binary log in commit…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… MySQL…inserts (multiple value inserts). Really helps if you have really bulk inserts 100-1000+ values per single insert statement. concurrent_insert – Enables concurrent insert…binary logs from master. It does not affect Innodb and other storage engines transaction logs, slow query log or general log…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…refeshed. The incremental method is somewhat conceptually similar to using mysql binary logs for point-in-time recovery after a restoring a backup…flexviews.create(‘demo’, ‘dashboard_customer_sales’, ‘INCREMENTAL’); SET @mvid := LAST_INSERT_ID(); CALL flexviews.add_expr(@mvid,’GROUP’,'customer_id’,'…
