| Bug #55595 | Lots of data differences between MyISAM and InnoDB where there shouldn't be | ||
|---|---|---|---|
| Submitted: | 28 Jul 2010 3:30 | Modified: | 14 Mar 2014 19:04 |
| Reporter: | Roel Van de Paar | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Storage Engines | Severity: | S1 (Critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[28 Jul 2010 3:31]
Roel Van de Paar
Full log. Will analyze what the main differences are soon.
Attachment: 71.zip (application/force-download, text), 283.39 KiB.
[28 Jul 2010 4:00]
Roel Van de Paar
WHERE clause occurrence count.txt
Attachment: WHERE clause occurrence count.txt (text/plain), 3.51 KiB.
[28 Jul 2010 4:01]
Roel Van de Paar
Based on an occurrence count of the WHERE clauses, the main issue areas seem to be: 1. Where there are 'NOT NULL' definition, without a key 2. Where there are 'NOT NULL' definition, with a key 3. Where there is a Primary Key
[28 Jul 2010 10:07]
Roel Van de Paar
OK, small mistake here: --threads=20 "--threads=N - number of threads to run. Using more than one thread makes the test non-deterministic. If 100% determinism is important (such as when running the same test against two servers) use either only SELECT queries or set --threads to 1. The default is 10 threads;" (http://forge.mysql.com/wiki/RandomQueryGenerator) Testing now with --threads=1
[28 Jul 2010 10:11]
Roel Van de Paar
OK, there are still differences, but I wonder if they are related to bug #55541. Need to do some more testing.
[1 Nov 2010 8:32]
Roel Van de Paar
Did some more testing and found at least one bug. Logged separately as bug #57880. May still be other issues here.
Description: Running a testcase with statements like these: INSERT INTO _table (_field) VALUES (_digit); UPDATE _table SET _field = digit WHERE condition; DELETE FROM _table WHERE condition; condition being either "_field = value", "_field > value" or "_field < value" Produces many differences between MyISAM and InnoDB, where there shouldn't be. There is no reason why results should differ (no FK's, no LIMIT clauses, no other complexities). How to repeat: ============= Main run file kill -9 `pidof mysqld` cd /randgen/randgen rm -R /tmp/71/ rm -R /tmp/72/ rm ./71.log rm ./72.log perl ./runall.pl \ --basedir=/mysql/mysql-5.1.49-linux-i686-glibc23/ \ --vardir1=/tmp/71/ \ --vardir2=/tmp/72/ \ --mysqld1=--log-output=none \ --mysqld1=--log_error=/randgen/new_testing/7/71.log \ --mysqld1=--default-storage-engine=InnoDB \ --mysqld2=--log-output=none \ --mysqld2=--log_error=/randgen/new_testing/7/72.log \ --mysqld2=--default-storage-engine=MyISAM \ --gendata=/randgen/new_testing/7/7.zz \ --grammar=/randgen/new_testing/7/7.yy \ --queries=50000000 \ --threads=20 \ --duration=5000 \ --Validators=ResultsetComparator,MarkErrorLog,AbortOnSyntaxError cd /randgen/new_testing/7/ ============= 7.yy query: update | insert | delete ; update: UPDATE _table SET _field = digit WHERE condition; delete: DELETE FROM _table WHERE condition; insert: INSERT INTO _table ( _field ) VALUES ( _digit ) ; condition: _field = value | _field > value | _field < value ; value: _digit | _char(255) | _english | _datetime | NULL ; ============= 7.zz $tables = { rows => [0, 1, 10, 20, 25, 50, 75, 100], pk => [ undef, 'int auto_increment' ] }; $fields = { types => [ 'bit', 'int', 'char', 'char(64)', 'text', 'enum', 'set', 'blob', 'float', 'decimal', 'varchar(10)', 'varchar(1024)', 'date', 'datetime'], indexes => [undef, 'key' ], null => [undef, 'not null'], default => [undef, 'default null'], sign => [undef, 'unsigned'], charsets => ['utf8', 'latin1'] }; $data = { numbers => [ 'digit', 'null', '10', undef ], strings => [ 'letter', 'english', 'string(1024)', 'quid', 'null' ], blobs => [ 'english', 'data' ], temporals => ['date', 'year', 'null', undef ] }; =============