Re: Slaves show different results for query

On 05/19/2015 11:52 PM, Musall Maik wrote:
> Hi,
>
> I have a strange case where a SELECT for a primary key returns 0 rows on one slave, while it returns the correct 1 row on another slave and on the master. It does however return that row on all slaves when queried with LIKE and trailing or leading wildcard.
>
> psql version is 9.3.5, because that's what comes with Ubuntu 14.04 LTS
> Master runs Ubuntu 14.04 LTS
> Slave 1 runs also Ubuntu 14.04 LTS
> Slave 2 runs Mac OS X 10.7, pgsql installed via homebrew
>
> Both slaves are configured with streaming replication, and I've been using that setup for years, starting with psql 9.1, with no problems so far. Suspecting some weird problem, I already re-initialized slave 2 with a fresh backup and started replication from the beginning, so the database is fresh from a master copy, and is verified to be current.
>
> 2015-05-19 20:53:43.937 CEST LOG: entering standby mode
> 2015-05-19 20:53:43.974 CEST LOG: redo starts at 31/3F000028
> 2015-05-19 20:53:45.522 CEST LOG: consistent recovery state reached at 31/40CCE6E8
> 2015-05-19 20:53:45.523 CEST LOG: database system is ready to accept read only connections
> 2015-05-19 20:53:45.604 CEST LOG: started streaming WAL from primary at 31/41000000 on timeline 1
>
>
> So here's the query.
>
> SELECT * FROM MyTable WHERE email = 'foo(at)example(dot)com';
>
> This returns 1 row on master and slave 1, but 0 on slave 2, while this query:
>
> SELECT * FROM MyTable WHERE email LIKE 'foo(at)example(dot)com%';
>
> or this one
>
> SELECT * FROM MyTable WHERE email LIKE '%foo(at)example(dot)com';
>
> returns the correct 1 row on all three systems. Note that this works with the wildcard on either end, or also somewhere in the middle, doesn't matter. Note: "email" is the primary key on this table.
>
> This behaviour is the same with any address to be queried, and is also the same on a similar second table. This does NOT occur on any other table, which all have integer primary keys. There is also no problem when I select for other attributes on these tables.
>
> Does anyone have a hint?

What are the encodings on the various machines and in the databases?

>
> Thanks
> Maik
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com