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

I'm just wondering if other people know of any obscure reasons that
cause problems when creating merge tables.

Many Thanks
Regards
Neil.

On Tue, 25 Sep 2007 13:10:43 -0700, "N. Sloane"
<neil@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)

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


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.

On Thu, 27 Sep 2007 07:23:14 -0700, "N. Sloane"
<neil@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)

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 and suggestion.
Rgds
Neil.

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 and suggestion.
>Rgds
>Neil.

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

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

Hi Neil,


On Fri, 28 Sep 2007 13:23:33 -0700, "N. Sloane"
<neil@invidion.co.uk> wrote:

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

Looks good then.

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

It shouldn't make any difference.

>Baffled...

I give up
Perhaps Axel Schwenke (Support Engineer, MySQL AB) can shed some
light on this. Hm, haven't seen him here for a while.

>Rgds
>Neil.

Please keep us posted of the cause and the solution, if you are
able to find one.
--
( Kees
)
c[_] Like a lot of husbands throughout history, Mr. Webster would sit
down and try to talk to his wife. As soon as he'd say something
though, she'd fire back with, "And just what the hell is THAT
supposed to mean?" Thus, Webster's Dictionary was born. (#6)

>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

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

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

Does the problem occur if the union joins
only two tables, one of them being `120`, i.e.:
UNION=(`1`,`120`)? If that fails with the same
error, then it looks like a corrupt table.

What happens if you reverse
the order of tables `120` and `121`, i.e.:
UNION=(`1`, `2`, `3`, ..., `119`, `121`, `120`, `122`, ... `5355`)
If that moves the syntax error to table 122, then it
looks more like a bug in MySQL.

Just a thought. HTH, Charles

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

Errcode 24 on my system is "too many open files". Assuming it means
the same on yours, you're hitting an OS limit. Try to either get
MySQL not to cache so many open file descriptors (open tables), or
raise the system limit.

On 30 Sep, 09:11, gordonb.iy...@burditt.org (Gordon Burditt) wrote:
> >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 theMergeTable, 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)
>
> Errcode 24 on my system is "too many open files". Assuming it means
> the same on yours, you're hitting an OS limit. Try to either getMySQLnot to cache so many open file descriptors (open tables), or
> raise the system limit.

OK,

I've just looked at this again, and tried to create a merge table out
of only five tables. Doesn't seem to matter whether it is 5 or 5000
underlying tables. It still gives the same error:

mysql> drop table shares;Query OK, 0 rows affected (0.03 sec)

mysql> 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) UNSIGNED NOT NULL auto_increment,
INDEX(id)) ENGINE=MRG_MyISAM UNION=(`1`, `2`, `3`, `4`, `5`)
INSERT_METHOD LAST;
Query OK, 0 rows affected (0.10 sec)

mysql> select * from shares where symbol = "^ftse";ERROR 1168 (HY000):
Unable to open underlying table which is differently defined or of non-
MyISAM type or doesn't exist
mysql>


Regarding the point:
>> Try to either get MySQL not to cache so many open file descriptors (open tables), or raise the system limit.

I'm no expert, so I a little unsure what to tweak.

I upped some of the settings (as follows) but this dosen't seem to
make much difference:

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 256M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8


As my machine only has 512MB RAM (possibly time to buy some more) I
can't set these too high.

Anything obvious here that I should tweak.

Many thanks again.

Rgds
Neil.