Trouble creating temporary memory table

[Ted]

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

[Bill Karwin]

Ted wrote:

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

[url]http://dev.mysql.com/doc/refman/4.1/en/memory-storage-engine.html[/url] 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.

[Ted]

OOPS!

I should havr said I am using MySQL 5.0.16

Thanks.

Ted

[Bill Karwin]

Ted wrote:

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

Aha -- [url]http://dev.mysql.com/doc/refman/5.0/en/create-table.html[/url] 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.

[Ted]

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

[Bill Karwin]

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.

[Ted]

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

Cheers,

Ted

[Jerry Stuckle]

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.
[email]jstucklex@attglobal.net[/email]
==================

[Ben Walters]

I had a similar issue, it turns out I just needed to add the default charset at the end of the statement and include backticks around the table name and column name to get it to work properly.

It is probably of note that I was entering this information through the query window of Sequel Pro for Mac OS X and not the mysql console.

My statement started as :
CREATE TEMPORARY TABLE IF NOT EXISTS tmplog (msg varchar(512)) ENGINE = MEMORY;

and it finally worked when I changed it to:
CREATE TEMPORARY TABLE IF NOT EXISTS `tmplog` (`msg` varchar(512)) ENGINE=Memory DEFAULT CHARSET=latin1;