Professional Web Applications Themes

Trouble creating temporary memory table - MySQL

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

  1. #1

    Default Trouble creating temporary memory table

    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

    Ted Guest

  2. #2

    Default Re: Trouble creating temporary memory table

    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.
    Bill Karwin Guest

  3. #3

    Default Re: Trouble creating temporary memory table

    OOPS!

    I should havr said I am using MySQL 5.0.16

    Thanks.

    Ted

    Ted Guest

  4. #4

    Default Re: Trouble creating temporary memory table

    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.
    Bill Karwin Guest

  5. #5

    Default Re: Trouble creating temporary memory table

    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

    Ted Guest

  6. #6

    Default Re: Trouble creating temporary memory table

    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.
    Bill Karwin Guest

  7. #7

    Default Re: Trouble creating temporary memory table

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

    Cheers,

    Ted

    Ted Guest

  8. #8

    Default Re: Trouble creating temporary memory table

    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]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  9. #9

    Default Re: Trouble creating temporary memory table

    I had a similar issue, it turns out I just needed to add the default cht 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 CHT=latin1;
    Ben Walters is offline Junior Member
    Join Date
    Jan 2011
    Posts
    1

Similar Threads

  1. Temporary Table Creation Problem in CF7 not CF6.1
    By Ortho in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 24th, 04:20 PM
  2. Get result from temporary Oracle Table
    By Franck in forum ASP.NET Web Services
    Replies: 0
    Last Post: April 28th, 07:48 AM
  3. #26387 [NEW]: TABLE TEMPORARY DOES NOT WORK
    By davefazio at annulet dot com in forum PHP Development
    Replies: 0
    Last Post: November 24th, 08:09 PM
  4. Temporary Table Query
    By Jonathan Derbyshire in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 4th, 07:44 PM
  5. Replies: 3
    Last Post: December 6th, 02:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139