On Thu, 27 Sep 2007 14:25:39 -0700, "N. Sloane"
<neil@invidion.co.uk> wrote:

>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)

Ok, this is something you could check.
Every database has its own directory in the MySQL data
directory. Every MyISAM table consists of a few files, the
filename equals the table name, then there is at least a
description file, a data file (.MYD) and one or more index
files.
Perhaps you skipped the creation of TABLE `120` by accident?


>This is starting to get a bit obscure...
>
>Thanks for all your help and suggestion.
>Rgds
>Neil.

Good again,
--
( Kees
)
c[_] Paranoia is nothing to be afraid of!! (#103)