search

zxs / tungsten-replicator

Automatically exported from code.google.com/p/tungsten-replicator
0 stars 0 forks source link

The mysqlsessions filter will fail if the thread_id is -1 #821

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago 
What steps will reproduce the problem?

The default thread_id was changed to -1 at some point. In unknown cases, the 
MySQLExtractor will not set this to 0. We should update the mysqlsessions 
filter so that it will change a thread_id of -1 to 0 when when running 'SET 
@@session.pseudo_thread_id'

What is the expected output?

...

What do you see instead?

...

What is the possible cause?

...

What is the proposed solution?

...

Additional information

...

Use labels and text to provide additional information.

Original issue reported on code.google.com by jeff.m...@continuent.com on 6 Feb 2014 at 2:02

GoogleCodeExporter commented 9 years ago 
Please provide a stack trace how does it fail with -1, in case the fix 
introduces unexpected regressions in other scenarios.

Original comment by linas.vi...@continuent.com on 6 Feb 2014 at 2:17

GoogleCodeExporter commented 9 years ago 
This error comes up when you have binlog_format=ROW and 
sql_mode=STRICT_ALL_TABLES.

Original comment by jeff.m...@continuent.com on 6 Feb 2014 at 2:28

GoogleCodeExporter commented 9 years ago 
This has been fixed in 2.0.1 and in trunk.

Original comment by jeff.m...@continuent.com on 6 Feb 2014 at 3:24

GoogleCodeExporter commented 9 years ago 
Stephane to add a test for this.

Original comment by jeff.m...@continuent.com on 6 Feb 2014 at 4:57

GoogleCodeExporter commented 9 years ago 
Hi,

I am facing this same issue in a simple Master Slave replication with Master on 
MIXED format binary logging. This issue can be replicated with Sakila DB data 
installed on Master, please see attached.

I am using Tungsten Replicator 2.2.0 build 292

Original comment by ankit.th...@gmail.com on 28 Feb 2014 at 10:58

Attachments:

GoogleCodeExporter commented 9 years ago 
The fix is only in trunk. You will need to install the nightly build.

Original comment by jeff.m...@continuent.com on 28 Feb 2014 at 5:28

GoogleCodeExporter commented 9 years ago 
Hi,

Can you please direct me to the link which has the fix ?

Regards,
Ankit

Original comment by ankit.th...@gmail.com on 28 Feb 2014 at 5:46

GoogleCodeExporter commented 9 years ago 
Nightly builds are available at 
http://s3.amazonaws.com/files.continuent.com/builds/nightly/tungsten-2.2-snapsho
ts/index.html.

Original comment by jeff.m...@continuent.com on 28 Feb 2014 at 5:48

GoogleCodeExporter commented 9 years ago 
Hi,

I tried the nightly build: Tungsten Replicator 2.2.1 build 180, strange enough 
I got following error in a simple Master Slave Replication with replicating 
Sakila DB 

pendingExceptionMessage: java.sql.SQLIntegrityConstraintViolationException: 
Duplicate entry '1' for key 'PRIMARY'
                         Failing statement : INSERT INTO `sakila`.`film` ( `film_id` , `title` , `description` , `release_year` , `language_id` , `original_language_id` , `rental_duration` , `rental_rate` , `length` , `replacement_cost` , `rating` , `special_features` , `last_update` )  VALUES (  ?  ,  UNHEX( ? )  ,  UNHEX( ? )  ,  ?  ,  ?  ,  ?  ,  ?  ,  ?  ,  ?  ,  ?  ,  ?  ,  ?  ,  ?  )
                         Arguments:
                          - ROW# = 0
                           - COL(index=1 name=film_id type=4 [INTEGER] length=2 unsigned=true blob=false desc=SMALLINT(5) UNSIGNED) = 1
                           - COL(index=2 name=title type=12 [VARCHAR] length=255 unsigned=false blob=false desc=VARCHAR(255)) = ACADEMY DINOSAUR
                           - COL(index=3 name=description type=2004 [BLOB] length=65535 unsigned=false blob=false desc=TEXT) = A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies
                           - COL(index=4 name=release_year type=4 [INTEGER] length=0 unsigned=false blob=false desc=YEAR(4)) = 2006
                           - COL(index=5 name=language_id type=4 [INTEGER] length=1 unsigned=true blob=false desc=TINYINT(3) UNSIGNED) = 1
                           - COL(index=6 name=original_language_id type=0 [NULL] length=0 unsigned=true blob=false desc=TINYINT(3) UNSIGNED) = NULL
                           - COL(index=7 name=rental_duration type=4 [INTEGER] length=1 unsigned=true blob=false desc=TINYINT(3) UNSIGNED) = 6
                           - COL(index=8 name=rental_rate type=3 [DECIMAL] length=0 unsigned=false blob=false desc=DECIMAL(4,2)) = 0.99
                           - COL(index=9 name=length type=4 [INTEGER] length=2 unsigned=true blob=false desc=SMALLINT(5) UNSIGNED) = 86
                           - COL(index=10 name=replacement_cost type=3 [DECIMAL] length=0 unsigned=false blob=false desc=DECIMAL(5,2)) = 20.99
                           - COL(index=11 name=rating type=1111 [OTHER] length=5 unsigned=false blob=false desc=ENUM('G','PG','PG-13','R','NC-17')) = 2
                           - COL(index=12 name=special_features type=4 [INTEGER] length=54 unsigned=false blob=false desc=SET('TRAILERS','COMMENTARIES','DELETED SCENES','BEHIND THE SCENES')) = 12
                           - COL(index=13 name=last_update type=93 [TIMESTAMP] length=0 unsigned=false blob=false desc=TIMESTAMP) = 2006-02-15 05:03:42.0
                          - ROW# = 1
                           - COL(index=1 name=film_id type=4 [INTEGER] length=2 unsigned=true blob=false desc=SMALLINT(5) UNSIGNED) = 2
                           - COL(index=2 name=title type=12 [VARCHAR] length=255 unsigned=false blob=false desc=VARCHAR(255)) = ACE GOLDFINGER
                           - COL(index=3 name=description type=2004 [BLOB] length=65535 unsigned=false blob=false desc=TEXT) = A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China
                           - COL(index=4 name=release_year type=4 [INTEGER] length=0 unsigned=false blob=false desc=YEAR(4)) = 2006
                           - COL(index=5 name=language_id type=4 [INTEGER] length=1 unsigned=true blob=false desc=TINYINT(3) UNSIGNED) = 1
                           - COL(index=6 name=original_language_id type=0 [NULL] length=0 unsigned=true blob=false desc=TINYINT(3) UNSIGNED) = NULL
                           - COL(index=7 name=rental_duration type=4 [INTEGER] length=1 unsigned=true blob=false desc=TINYINT(3) UNSIGNED) = 3
                           - COL(index=8 name=rental_rate type=3 [DECIMAL] length=0 unsigned=false blob=false desc=DECIMAL(4,2)) = 4.99
                           - COL(index=9 name=length type=4 [INTEGER] length=2 unsigned=true blob=false desc=SMALLINT(5) UNSIGNED) = 48
                           - COL(index=10 name=replacement_cost type=3 [DECIMAL] length=0 unsigned=false blob=false desc=DECIMAL(5,2)) = 12.99
                           - COL(index=11 name=rating type=1111 [OTHER] length=5 unsigned=false blob=false desc=ENUM('G','PG','PG-13','R','NC-17')) = 1
                           - COL(index=12 name=special_features type=4 [INTEGER] length=54 unsigned=false blob=false desc=SET('TRAILERS','COMMENTARIES','DELETED SCENES','BEHIND THE SCENES')) = 5
                           - COL(index=13 name=last_update type=93 [TIMESTAMP] length=0 unsigned=false blob=false desc=TIMESTAMP) = 2006-02-15 05:03:42.0
                          - ROW# = 2
                           - COL(index=1 name=film_id type=4 [INTEGER] length=2 unsigned=true blob=false desc=SMALLINT(5) UNSIGNED) = 3
                           - COL(index=2 name=title type=12 [VARCHAR] length=255 unsigned=false blob=false desc=VARCHAR(255)) = ADAPTATION HOLES
                           - COL(index=3 name=description type=2004 [BLOB] length=65535 unsigned=false blob=false desc=TEXT) = A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory
                           - COL(index=4 name=release_year type=4 [INTEGER] length=0 unsigned=false blob=false desc=YEAR(4)) = 2006
                           - COL(index=5 name=language_id type=4 [INTEGER] length=1 unsigned=true blob=false desc=TINYINT(3) UNSIGNED) = 1
                           - COL(index=6 name=original_language_id type=0 [NULL] length=0 unsigned=true blob=false desc=TINYINT(3) UNSIGNED) = NULL
                           - COL(index=7 name=rental_duration type=4 [INTEGER] length=1 unsigned=true blob=false desc=TINYINT(3) UNSIGNED) = 7
                           - COL(index=8 name=rental_rate type=3 [DECIMAL] length=0 unsigned=false blob=false desc=DECIMAL(4,2)) = 2.99
                           - COL(index=9 name=length type=4 [INTEGER] length=2 unsigned=true blob=false desc=SMALLINT(5) UNSIGNED) = 50
                           - COL(index=10 name=replacement_cost type=3 [DECIMAL] length=0 unsigned=false blob=false desc=DECIMAL(5,2)) = 18.99
                           - COL(index=11 name=rating type

It reports a duplicate primary which is surely not the case as I am using 
standard sakila db data insert script (attached with my initial comment).

Original comment by ankit.th...@gmail.com on 2 Mar 2014 at 11:55

GoogleCodeExporter commented 9 years ago 
https://docs.continuent.com/tungsten-replicator-2.2/operations-transactions.html

Original comment by jeff.m...@continuent.com on 2 Mar 2014 at 9:15

GoogleCodeExporter commented 9 years ago 
Hi Jeff,

I have rechecked and there is nothing wrong with the data on either Master or 
Slave, the data is created from scratch on Master and there was no DB with name 
sakila on Slave initially, I am listing the detailed steps to reproduce the 
issue:

What steps will reproduce the problem?

On a Master-Slave Topology, run following on Master DB:

drop database sakila;
source sakila-schema.sql
source sakila-data.sql

What is the expected output?

There should be no java.sql.SQLIntegrityConstraintViolationException execption 
as I am dropping any existing sakila DB and after that using standard 
Sakila schema creation and data scripts.

This is further verified by following SQL results on Master DB

        mysql> select count(*) from sakila.film;
        +----------+
        | count(*) |
        +----------+
        |     1000 |
        +----------+
        1 row in set (0.00 sec)

        mysql> select film_id,count(*) from sakila.film group by film_id having count(*) > 1;
        Empty set (0.00 sec)

What do you see instead?

pendingExceptionMessage: java.sql.SQLIntegrityConstraintViolationException: 
Duplicate entry '1' for key 'PRIMARY'
                         Failing statement : INSERT INTO `sakila`.`film` ( `film_id` , `title` , `description` , `release_year` , `language_id` , `original_language_id` , `rental_duration` , `rental_rate` , `length` , `replacement_cost` , `rating` , `special_features` , `last_update` )  VALUES (  ?  ,  UNHEX( ? )  ,  UNHEX( ? )  ,  ?  ,  ?  ,  ?  ,  ?  ,  ?  ,  ?  ,  ?  ,  ?  ,  ?  ,  ?  )

Additional information

I am using Tungsten Replicator 2.2.1 build 180

Attachments

Sakila Schema creation and data populate scripts
SEQNO causing the issue
TREPCTL output on Slave

Original comment by ankit.th...@gmail.com on 3 Mar 2014 at 5:52

Attachments:

GoogleCodeExporter commented 9 years ago 
Attaching Sakila DB creation scripts used for the above test

Original comment by ankit.th...@gmail.com on 3 Mar 2014 at 5:54

Attachments:

GoogleCodeExporter commented 9 years ago 
This is getting of course for this particular ticket. This issue is resolved. 
If you are having other issues with the replicator you should reset your 
servers and try from scratch. Any further questions should be posted to the 
discussion list.

Original comment by jeff.m...@continuent.com on 3 Mar 2014 at 2:54

GoogleCodeExporter commented 9 years ago 
Please provide a way of reproducing this issue for testing

Original comment by g.maxia on 11 Apr 2014 at 8:48

GoogleCodeExporter commented 9 years ago 
See comment #2.

https://code.google.com/p/tungsten-replicator/issues/detail?id=821#c2

Original comment by jeff.m...@continuent.com on 11 Apr 2014 at 12:11

GoogleCodeExporter commented 9 years ago 
How to reproduce, in 2.2.0
1) set binlog format to ROW, and SQL_MODE to STRICT_ALL_TABLES
2) install a master/slave topology
3) the replicator will fail at the first event (heartbeat), saying that 
@@pseudo_thread_id is -1

In build 2.2.1-261 the bug is fixed. Added test in standard cookbook tests

Original comment by g.maxia on 12 Apr 2014 at 3:06

GoogleCodeExporter commented 9 years ago 
A note has been added to the 2.2.1 release notes: 

 The mysqlsessionsupport filter would cause replication to fail when the default thread_id was set to -1, for example when STRICT_ALL_TABLES SQL mode had been enabled. The replicator has been updated to interpret -1 as 0 to prevent this error.

Original comment by mc.br...@continuent.com on 14 Apr 2014 at 1:46