SEO

#1 (**permalink**) 02-28-2008, 07:34 AM

I tried to create a temporary memory table using the following:

CREATE TEMPORARY TABLE mytable_tmp LIKE mytable ENGINE=MEMORY;

However, I encountered a problem in that MySQL didn't like this
statement with the clause "ENGINE=MEMORY". If I remove this clause,
all is fine. However, I wanted the temporary table to be a memory
table, though, for the sake of speed. I suspect I may have to process
some of the data before moving it to its final destination.

The statement above clearly shows what I want to do at this stage. Can
I do it this way, or do I have to extract the SQL statement that would
create the table and alter it to use the memory engine?

Thanks,

Ted

#2 (**permalink**) 02-28-2008, 07:34 AM

Ted wrote:
> I tried to create a temporary memory table using the following:
>
> CREATE TEMPORARY TABLE mytable_tmp LIKE mytable ENGINE=MEMORY;

http://dev.mysql.com/doc/refman/4.1/...ge-engine.html says:
"Before MySQL 4.1, MEMORY tables are called HEAP tables."

So if you are using MySQL 4.0 or earlier, try ENGINE=HEAP.
If you are using MySQL earlier than 4.0, try TYPE=HEAP.

Regards,
Bill K.

#3 (**permalink**) 02-28-2008, 07:34 AM

OOPS!

I should havr said I am using MySQL 5.0.16

Thanks.

Ted

#4 (**permalink**) 02-28-2008, 07:34 AM

Ted wrote:
> I should havr said I am using MySQL 5.0.16
>
> CREATE TEMPORARY TABLE mytable_tmp LIKE mytable ENGINE=MEMORY;

Aha -- http://dev.mysql.com/doc/refman/5.0/...ate-table.html says:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(] LIKE old_tbl_name [)];

This suggests that using table_options (of which ENGINE=MEMORY is an
example) is not permitted when you're using LIKE old_tbl_name.

So perhaps it would work to use "show create table mytable", get a full
CREATE TABLE statement, and then edit that to specify the MEMORY engine
option.

Regards,
Bill K.

#5 (**permalink**) 02-28-2008, 07:34 AM

Thanks Bill,

OK, as expected, that works after a fashion.

Something annoying I am seeing, though, is that I have several columns
which are of type char, intended to hold 'y' or 'n'. Had I had control
over the format, these would be boolean. However, I must work with
what I get. The problem is that either MySQL or MySQL Query Browser is
converting char into tinyint. I wouldn't care about this except that
when the data is to be loaded into the table, I get an error message
basically saying that 'y' and 'n' are not valid numbers. Every time I
view the table definition, I see the fields stored as tinyint. I
change them to char, and all is ok until the next time I look, and then
I see them converted back to tinyint.

Thanks,

Ted

#6 (**permalink**) 02-28-2008, 07:34 AM

Ted wrote:
> view the table definition, I see the fields stored as tinyint. I
> change them to char, and all is ok until the next time I look, and then
> I see them converted back to tinyint.

Is this only happening with your ENGINE=MEMORY table? I haven't worked
with such tables, perhaps they don't take kindly to being altered. What
about dropping the table and recreating it with the column defined as CHAR?

Regards,
Bill K.

#7 (**permalink**) 02-28-2008, 07:34 AM

No, this is happening with all of my InnoDB tables, even those created
de novo.

Cheers,

Ted

#8 (**permalink**) 02-28-2008, 07:34 AM

Ted wrote:
> Thanks Bill,
>
> OK, as expected, that works after a fashion.
>
> Something annoying I am seeing, though, is that I have several columns
> which are of type char, intended to hold 'y' or 'n'. Had I had control
> over the format, these would be boolean. However, I must work with
> what I get. The problem is that either MySQL or MySQL Query Browser is
> converting char into tinyint. I wouldn't care about this except that
> when the data is to be loaded into the table, I get an error message
> basically saying that 'y' and 'n' are not valid numbers. Every time I
> view the table definition, I see the fields stored as tinyint. I
> change them to char, and all is ok until the next time I look, and then
> I see them converted back to tinyint.
>
> Thanks,
>
> Ted
>

Tinyint is a synonym for CHAR. This is by design.

What does your insert statement look like?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Trouble creating temporary memory table