MySQL Bugs: #33824: Manipulations on a view and on the underlying table cause duplicate primary key

Bug #33824	Manipulations on a view and on the underlying table cause duplicate primary key
Submitted:	11 Jan 2008 17:56	Modified:	11 Feb 2008 21:00
Reporter:	Philip Stoev	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S1 (Critical)
Version:	6.0.4-p3	OS:	Any
Assigned to:	Hakan Küçükyılmaz	CPU Architecture:	Any
Tags:	compiler_suspected

Description:
The rpl_sys test fails on the latest 6.0.4 binary provided by the build team with the master and the slave differing after the test is over.

The difference is that the table on the master has duplicate records for 31 primary key values (14431 records total) in table test.viewer_tbl2. The duplicates are visible with mysqldump and when doing a SELECT COUNT(*) GROUP BY BY PRIMARY_KEY. Selecting on the primary key value that has duplicates produces just a single row. The duplicates appear identical in all their columns.

The slave table is OK. This test passed correctly with the previous build team binary, 6.0.4-p2.

How to repeat:
mysql> select a, count(*) as C from viewer_tbl2 group by a having C > 1;
+-------+---+
| a     | C |
+-------+---+
|    60 | 2 |
|   187 | 2 |
|   202 | 2 |
|   209 | 2 |
|   472 | 2 |
|   580 | 2 |
|   796 | 2 |
|   811 | 2 |
|   818 | 2 |
|  1378 | 2 |
|  1505 | 2 |
|  1520 | 2 |
|  1527 | 2 |
|  2236 | 2 |
|  2276 | 2 |
|  2945 | 2 |
|  4100 | 2 |
|  4784 | 2 |
|  4909 | 2 |
|  5393 | 2 |
|  5472 | 2 |
|  6102 | 2 |
|  6181 | 2 |
|  6711 | 2 |
|  6789 | 2 |
|  8699 | 2 |
|  9208 | 2 |
| 10142 | 2 |
| 15426 | 2 |
| 23635 | 2 |
| 28644 | 2 |
+-------+---+
31 rows in set (19.08 sec)

Philip, If a mysql-test-rul.pl test could be developed that results in duplics in a primary field, then it would most certainly help to isolate the problem.

I need to replicate this with an "official" build from the build team.

Test case for bug 33824

Attachment: bug33824.zip (application/x-zip-compressed, text), 116 bytes.

Please find attached a test case for this bug. To run, please do the following:

1. Place the .txt files in mysql-test and the .test files in mysql-test/t

2. Run perl ./mysql-test-run.pl --stress --stress-init-file=bug33824_init.txt --stress-test-file=bug33824_run.txt --stress-test-duration=600 --stress-threads=50

3. 5 minutes into the test, issue the following query in a separate client:

select a, count(*) as C from test.viewer_tbl2 group by a having C > 1;

you will get a non-empty result set, even though "a" is a primary key column.

Please examine viewer_sp() in bug33824_init.test to see what tables are involved. Let me know if anything else is needed from me.

I am changing the title of this bug to reflect the fact that replication is not involved.

Jim, There is a reproducible testcase now.  Can you look into this today?  Is this a beta issue?

Correct files for bug #33824

Attachment: bug33824.zip (application/x-zip-compressed, text), 3.73 KiB.

I compiled and tested latest mysql-6.0-falcon-team tree on rhas3-x86 and I was not able to reproduce the duplicate key error anymore. I did the following:

- Clone a fresh mysql-6.0-falcon-team on lu0009
  - BUILD/compile-dist
  - make dist
- Copied resulting mysql-6.0.5-alpha.tar.gz to rhas3-x86
- On rhas3-x86
  - Unpacked the archive
  - CC="ccache gcc" CXX="ccache gcc" ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --libexecdir=/usr/local/mysql/bin --with-comment="MySQL Community Server (GPL)" --with-server-suffix="" --enable-thread-safe-client --enable-local-infile --enable-assembler --with-pic --with-fast-mutexes --with-client-ldflags="-static" --with-mysqld-ldflags="-static" --with-zlib-dir=bundled --with-big-tables --with-ssl --with-readline --with-embedded-server --with-archive-storage-engine --with-blackhole-storage-engine --with-ndbcluster --with-csv-storage-engine --with-example-storage-engine --with-falcon --with-federated-storage-engine --with-partition --with-innodb --with-extra-charsets=all
  - make -j2

- Tested the resulting binary on my laptop where the previous binary was failing. I could not reproduce a duplicate key error anymore with the new binary.

This bug is not reproducible with the 6.0.4 binary compiled with GCC: (GNU) 4.1.1 20070105 (Red Hat 4.1.1-52)

This bug is not reproducible with the latest binary provided by the build team, built with GCC: (GNU) 4.0.1 20050727 (Red Hat 4.0.1-5)