On Sep 27, 8:57 pm, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
> On Thu, 27 Sep 2007 07:23:14 -0700, "N. Sloane"
>
>
>
> <n...@invidion.co.uk> wrote:
> >On 26 Sep, 14:26, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
> >> On Tue, 25 Sep 2007 13:10:43 -0700, "N. Sloane"
>
> >> <n...@invidion.co.uk> wrote:
> >> >Hi,
>
> >> >I've been trying to construct a MERGE table in MySQL from about 5000
> >> >other tables, each of which have a few thousand entries. However, when
> >> >I create and try and access it, I keep getting the dreaded error:
>
> >> >ERROR 1064 (42000): You have an error in your SQL syntax; check the
> >> >manual that corresponds to your MySQL server version for the right
> >> >syntax to use near ....
>
> >> >I've read the docs, and am using the same table type (MyISAM), and all
> >> >the table columns of each of the 5000 tables is exactly the same. Each
> >> >table has a primary key called 'id', and when I try and create the
> >> >merge table, I use
>
> >> >INDEX(id)
>
> >> >to specify the index, not making it a primary key.
>
> >> >I'm sure my MySQL syntax is correct;
>
> >> I'm sure it's not. When you show your syntax I might change my
> >> mind.
>
> >> >I'm wondering if there are any
> >> >additional procedures I need to go through before this. For instance,
> >> >do I need to re-order or ammend the indexes of the other tables before
> >> >I merge them?
>
> >> Not that I'm aware of. Still, that has nothing to do with syntax
> >> errors.
>
> >> >I'm just wondering if other people know of any obscure reasons that
> >> >cause problems when creating merge tables.
> >> >Many Thanks
> >> >Regards
> >> >Neil.
>
> >> Regards,
> >> --
> >> ( Kees
> >> )
> >> c[_] A chicken is an egg's way of producing more eggs. (#436)- Hide quoted text -
>
> >> - Show quoted text -
>
> >Syntax below:
>
> >Query to create tables (numbered from 1 - 5355)
>
> >CREATE TABLE `1` ( `date` date NOT NULL, `symbol` char(20) default
> >NULL, `open` decimal(10,2) default NULL, `close` decimal(10,2) default
> >NULL, `adjClose` decimal(10,2) NOT NULL, `high` decimal(10,2) default
> >NULL, `low` decimal(10,2) default NULL, `volume` decimal(10,2) default
> >NULL, `id` int(11) NOT NULL auto_increment, PRIMARY KEY (`id`))
>
> >Query to create Merge Table:
>
> >CREATE TABLE `shares` ( `date` date NOT NULL, `symbol` char(20)
> >default NULL, `open` decimal(10,2) default NULL, `close` decimal(10,2)
> >default NULL, `adjClose` decimal(10,2) NOT NULL, `high` decimal(10,2)
> >default NULL, `low` decimal(10,2) default NULL, `volume` decimal(10,2)
> >default NULL, `id` int(11) NOT NULL auto_increment, INDEX(id))
> >ENGINE=MRG_MyISAM UNION=(`1`, `2`, `3`, ......, `5355`) INSERT
> >METHOD=LAST
>
> Ok, now it's much more clear what you're trying to do.
>
> I think
> INSERT METHOD=LAST
> should be
> INSERT_METHOD=LAST
>
> That's all, but I only tested with two tables. With 5355 tables,
> you might hit some limit on statement size or similar.
>
> BTW, It wouldn't hurt to explicitly define ENGINE=MyISAM for the
> base tables, just in case the server defaults to some other
> engine.
>
> >You are proably right in saying that the above syntax is incorrect in
> >some way that I just can't see.
>
> >I noticed that although I do not explicitly declare the main tables as
> >MyISAM tables, they are created as MyISAM tables, so assumed that that
> >would not cause a problem.
>
> >However, in the merge code, should I change INDEX(id) to KEY(id) ?
>
> >Many Thanks
> >Neil.
>
> Good luck and regards,
> --
> ( Kees
> )
> c[_] The reasonable man adapts himself to the world; the
> unreasonable one persists in trying to adapt the world
> to himself. Therefore all progress depends on the
> unreasonable man. (George Bernard Shaw) (#467)

Hmmm... Still can't get this right.

I used a PHP script to run an alter command accross all the tables to
ensure that they are of type My_ISAM.

i.e. alter table `1` engine=MyISAM

Altering INSERT METHOD=LAST to INSERT_METHOD=LAST also makes no
difference.

When I issue the statement to create the Merge Table, it executes
without any errors. They only occur when I try and access the table.

mysql> describe `shares`;
ERROR 29 (HY000): File './securities/120.MYD' not found (Errcode: 24)

This is starting to get a bit obscure...

Thanks for all your help and suggestion.
Rgds
Neil.