On Sep 28, 12:30 am, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
> On Thu, 27 Sep 2007 14:25:39 -0700, "N. Sloane"
>
>
>
> <n...@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)

OK,

I checked, and table `120` exists; I can run SELECTs on it, and it
shows no obvious signs of corruption.

I checked the data directory, and the files are there. See below:

DTHP:/usr/local/mysql/data/securities root# ls 120\.*
120.MYD 120.MYI 120.frm

I can't see anything in table 120 that is any different from the other
tables.

I'm running the database on a OSX10.4 machine, but I don't really
think this will make any difference.

Baffled...

Rgds
Neil.