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
