Hi Peter,
On Mon, Mar 26, 2001 at 04:30:43PM +0100, Peter Skipworth wrote:
> Eeps...I'm running 3.23.35..I gather you'd suggest down-grading to .33 ?
>
Or wait for 3.23.36. I would expect that they release it soon because
the MySQL team advised not to use the previous two versions. I just
hope that .36 is okay. But I'll stick with my patched .33 for a while.
It works fine for me.
> 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...
>
Not sure, but I believe somewhere in 3.23.xx. It was in 3.23.32 and it
was not in 3.22.27.
Regards,
Fred.
> 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.
> > > >
> > > >
> > >
> >
> >
>
--
Fred van Engen XO Communications B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
---------------------------------------------------------------------
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
