Eeps...I'm running 3.23.35..I gather you'd suggest down-grading to .33 ?
I realise how the MERGE tables work...I INSERT into the sub-tables and
select from both the MERGED table and the sub-tables. By the way, do you
happen to know when support for "select distinct(count...))" was added ? I
know it didn't used to work, but I thought I'd try it again this morning
and lo and behold, it was supported...
Cheers,
P
On Mon, 26 Mar 2001, Fred
van Engen wrote:
> Hi Peter,
>
> On Mon, Mar 26, 2001 at 03:04:29PM +0100, Peter Skipworth wrote:
> > Ouch!! Watch this!! I queried the 'sub' table (Feb's data), tried querying
> > the MERGE table (which errored, with mysqld restarting itself in some
> > fashion), queried the SAME sub table, and most of my data had
> > disappeared! I've run 'check table...' and it doesnt show any errors...
> >
>
> What version are you running? I've never lost any data on a server
> running MySQL version 3.23.32 and 3.23.33. Neither has it ever crashed
> on a query. I use (simple but rather large) MERGE tables on a
> production server with not too complex queries. I do INSERTs on the
> base tables, UPDATEs and SELECTs on the MERGE tables and a weelky
> ALTER TABLE UNION on the MERGE table. All tested without major problems.
> I patched the sources because of a concurrent insert bug and there is a
> problem with query optimization that forces me to specify an index with
> USE INDEX (...). I can't imagine that my patch would have anything to
> do with your server crashing.
>
> Make sure you read the section in the manual about MERGE tables. You
> can do SELECTs and UPDATEs on the MERGEd table. You can do INSERTs on
> any of the base tables. You should NEVER use ALTER TABLE on any of the
> base tables.
>
> Also, stay away from 3.23.34 and 3.23.35. There are some serious bug
> reports for these. I'm not sure if these could cause your problems
> though.
>
>
> > mysql> select count(distinct cookie) as c, dayofmonth(event_time) as d
> > from tbl_webevents_022001 where event_time >= '2001-02-01 00:00:00' and
> > event_time < '2001-03-01 00:00:00' group by d;
> >
> > | 1004 | 1 |
> > | 1194 | 2 |
> > | 1189 | 3 |
> > | 559 | 4 |
> > | 1571 | 5 |
> > | 1319 | 6 |
> > | 1359 | 7 |
> > | 1462 | 8 |
> > | 1321 | 9 |
> > | 791 | 10 |
> > | 799 | 11 |
> > | 1592 | 12 |
> > | 1406 | 13 |
> > | 1689 | 14 |
> > | 1613 | 15 |
> > | 1604 | 16 |
> > | 1104 | 17 |
> > | 1089 | 18 |
> > | 1767 | 19 |
> > | 1828 | 20 |
> > | 1593 | 21 |
> > | 1665 | 22 |
> > | 1327 | 23 |
> > | 879 | 24 |
> > | 967 | 25 |
> > | 1570 | 26 |
> > | 1624 | 27 |
> > | 1575 | 28 |
> > +------+------+
> >
> > mysql> select count(distinct cookie) as c, dayofmonth(event_time) as d
> > from tbl_webevents where event_time >= '2001-02-01 00:00:00' and
> > event_time < '2001-03-01 00:00:00' group by d;
> > ERROR 2013: Lost connection to MySQL server during query
> >
> > mysql> select count(distinct cookie) as c, dayofmonth(event_time) as d
> > from tbl_webevents_022001 where event_time >= '2001-02-01 00:00:00' and
> > event_time < '2001-03-01 00:00:00' group by d;
> > ERROR 2006: MySQL server has gone away
> > No connection. Trying to reconnect...
> > +------+------+
> > | c | d |
> > +------+------+
> > | 1247 | 1 |
> > | 1080 | 2 |
> > | 500 | 3 |
> > | 559 | 4 |
> > | 1571 | 5 |
> > | 608 | 6 |
> > +------+------+
> >
> > Where did my data go?! bwahaha..I'm so glad I'm doing all of this on a dev
> > server!
> >
>
> Did you ALTER base tables?
>
> Regards,
>
> Fred.
>
> > On Mon, 26 Mar 2001, Fred
> > van Engen wrote:
> >
> > > Hi Peter,
> > >
> > > On Mon, Mar 26, 2001 at 12:20:14PM +0100, Peter Skipworth wrote:
> > > > I've got a table which is basically a log of traffic on one of my websites
> > > > - I have a seperate table per month, all of which are MERGED for queries
> > > > which need access to more than a month's worth of data.
> > > >
> > > > select count(distinct cookie) as c,dayofmonth(event_time) as d from
> > > > tbl_webevents where event_time >= '2001-02-01 00:00:00' and
> > > > event_time < '2001-03-01 00:00:00' group by d;
> > > >
> > >
> > > Could you post an EXPLAIN SELECT ... for this?
> > >
> > >
> > > > This query currently takes in excess of 20 seconds to return a result
> > > > (tbl_webevents is the merge table, consisting of 6 x 250,000 row table).
> > > >
> > >
> > > How long does the query take on the unmerged table for februari?
> > > Just for comparison of course. The optimizer doesn't work as well
> > > on MERGE tables as it does on regular tables. You might need to
> > > explicitly tell it to use a certain index with SELECT ... FROM
> > > tbl_webevents USE INDEX (...) WHERE ...
> > >
> > > Could you post an EXPLAIN SELECT ... for this single table as well?
> > >
> > >
> > > Regards,
> > >
> > > Fred.
> > >
> > >
> >
>
>
---------------------------------------------------------------------
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
