How is this possible? Deployment is on a slightly older version than I
have on my development machine, but since I am not doing anything exotic
it really shouldn't matter:

Deployment:
========================
Server version: 5.0.51a-24+lenny5
MySQL client version: 5.0.51a

Development:
========================
Server version: 5.1.41-3ubuntu12.10
MySQL client version: 5.1.41

Fortunately, it seems that the queries and views running in the
deployment environment are returning the correct data, but on my
development machine it is not. It isn't a PHP or charset problem either
because I can see the data discrepancies when I run the SELECT
statements in the terminal. I know the data is the same because I
started over with a clean database (no tables, routines or views) and
recreated everything from a dump I made from the development machine.

The only real difference I noticed is in some indices on one relatively
substantial table (abt. 5000 rows) which drives the query in question.
Here is the table structure:

CREATE TABLE `kursliste` (
`kurs_nr` int(11) NOT NULL,
`abteilung` varchar(50) collate utf8_unicode_ci default NULL,
`lp_kuerzel` varchar(50) collate utf8_unicode_ci default NULL,
`kurs_name` varchar(50) collate utf8_unicode_ci default NULL,
`zimmer` varchar(50) collate utf8_unicode_ci default NULL,
`dow` int(11) NOT NULL,
`lektion` int(11) NOT NULL,
`semester` int(11) NOT NULL default '3' COMMENT '1 = nur 1. Semester;
2 = nur 2. Semester; 3 = ganzjährig',
KEY `idx_kursnr_kursliste` (`kurs_nr`),
KEY `idx_abt_kursliste` (`abteilung`),
KEY `idx_kursname_kursliste` (`kurs_name`),
KEY `idx_dow_zeit` (`dow`,`lektion`),
KEY `idx_kuerzel` (`lp_kuerzel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

In the earlier MySQL version, the indices look like this:

-----------------------------------------------------
Keyname Type Field Cardinality
-----------------------------------------------------
idx_kursnr_kursliste INDEX kurs_nr 1590
idx_abt_kursliste INDEX abteilung 125
idx_kursname_kursliste INDEX kurs_name 265
idx_dow_zeit INDEX dow 119
--- lektion
idx_kuerzel INDEX lp_kuerzel 367
-----------------------------------------------------

And on my development machine:

---------------------------------------------------------------------
Keyname Type Unique Packed Field Cardinality
---------------------------------------------------------------------
idx_kursnr_kursliste BTREE No No kurs_nr 796
idx_abt_kursliste BTREE No No abteilung 129
idx_kursname_kursliste BTREE No No kurs_name 207
idx_dow_zeit BTREE No No dow 6
--- lektion 119
idx_kuerzel BTREE No No lp_kuerzel 298
---------------------------------------------------------------------

Notice the difference in cardinality. Is it possible that the different
index data can cause the queries to actually miss some data?

If necessary, I can post the SQL of the views, but it is quite large.
Has anyone else encountered this problem when upgrading MySQL from 5.0
to 5.1?

Robert Hairgrove <nobody (AT) hogwash (DOT) com> wrote:

"slightly older" LOL

Cardinalities are estimates for InnoDB tables. See
http://dev.mysql.com/doc/refman/5.1/...trictions.html

Cardinalities only affect the optimizer decision. Unless you
speculate on side effects (i.e. LIMIT without ORDER BY) then all
execution plans should yield the same result.

So far we have no information to say anything.

The data in the base table(s) and the view definition is really the
same? How did you check that?

What are the differences you are seeing? Different number of rows?
Different row content? Big difference or small? EXPLAIN results?

And since views are involved: what about the query that defines the
view? Same result? And same as SELECT * from the view?

Have you read the manual? The "Upgrade from 5.0 to 5.1" section?
Noticed the "incompatible change"s?


XL

On 8/8/2011 1:11 AM, Robert Hairgrove wrote:
Index data is taken directly from the database. The fact you have
different cardinality indicates you have different data in the two
databases.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 08/08/2011 11:16 AM, Axel Schwenke wrote:
I dropped the complete database on my local development machine and
installed it again from an SQL dump generated in phpMyAdmin from the
school's server.

Different number of rows (92 vs. 96 for one test case). EXPLAIN results
are almost exactly the same.

Same results.

I read that, but there is nothing there that would apply here, AFAICT.

The query for the view I was executing has three UNION select
statements; the first two selects are SELECT DISTINCT, the third only a
regular SELECT statement.

As it turns out, the results are distinct anyway (at least for my few
test cases), but then using DISTINCT shouldn't make any difference in
the results returned, should it? However, the same query with the same
underlying data running on MySQL 5.0 returns 96 rows, and running on
MySQL 5.1 only 92 rows.

When I remove the DISTINCT keyword, both queries return the same
(correct) data: 96 rows. Now I have to test a lot of things to make sure
that I don't actually need DISTINCT. Perhaps SELECT DISTINCT behaves
differently when used together with unions?

I'm ready to supply CREATE TABLE statements as well as the SQL for the
view, but hoping that there is a simple answer to all of this since I am
just a programmer and not a MySQL "guru"...

Robert Hairgrove <nobody (AT) hogwash (DOT) com> wrote:
"almost the same"? Details?

I guess (and you please check!) that 5.0 returns some duplicate rows
where 5.1 returns strictly distinct rows.

The reason why DISTINCT is not needed for the SELECT, is that UNION
works as UNION DISTINCT by default (as opposed to UNION ALL).

The difference is, that with SELECT DISTINCT each sub result that
goes into the UNION, is distinct in itself. The optimizer could
use that extra information to chose a different execution plan.
But I doubt that. Probably all sub results are merged in a temporary
table and sorted (to remove the duplicates).

Anyway.

If (SELECT ...) UNION (SELECT ...) returns a different result than
(SELECT DISTINCT ...) UNION (SELECT DISTINCT ...) then this is a bug.
Please find a minimal example showing the effect and report a bug at
bugs.mysql.com


XL