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

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)