Hi!
On Apr 30, Basil Hussain wrote:
> Hi,
>
> Anyway, while we're on the subject, I seem to be having some trouble with my
> indexes on MERGE tables. I posted a message a while ago, but no-one seems to
> have noticed it...
>
> Basically, if I create my MERGE table with indexes on the same columns as
> the underlying tables, I then get strange results from some queries. Below I
> quote part of my original message that illustrates the problem.
>
> > mysql> select count(*) from eventlog_36;
> > +----------+
> > | count(*) |
> > +----------+
> > | 389959 |
> > +----------+
> > 1 row in set (0.00 sec)
> >
> > mysql> select count(*) from eventlog_all where bannerid = 36;
> > +----------+
> > | count(*) |
> > +----------+
> > | 1 |
> > +----------+
> > 1 row in set (0.01 sec)
> >
> > (The table 'eventlog_36' is one of the tables mapped to 'eventlog_all' which
> > holds only records with a 'bannerid' column value of 36.)
> >
> > They don't match, so it's clearly not correct! It seems as though it's not
> > looking at the indexes of the mapped tables correctly, although I have no idea
> > why!
>
> Also, I get the following error when I try to alter the mapping of the MERGE
> table:
>
> > mysql> ALTER TABLE eventlog_all UNION=(eventlog_11, <..lots of tables...>,
> > eventlog_88);
> > ERROR 1031: Table handler for '#sql-1f1_ea6d9' doesn't have this option
>
> My MERGE table seems to be completely screwed! Could you explain what's
> going on here? Is it because I'm running only version 3.23.32 of MySQL? Do I
> need to upgrade? Any assistance would be appreciated, as no-one else seems
> to be able to help.
>
> Regards,
>
> ------------------------------------------------
> Basil Hussain ([EMAIL PROTECTED])
>
Ok, the first bug (incorrect COUNT, etc. for MERGE and individual tables)
was fixed some time ago. Though, it is possible that you found another bug,
the probability is low.
As for the second - ALTER TABLE ... UNION = () is absolutely legal syntax
and MySQL does support it (if you have source distribution, look at
mysql-test/t/merge.test). So, let's upgrade now, and then we'll see.
Regards,
Sergei
--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
