Thanks that gives me options, yes table was already created what I wanted was for the table itself to know that when MySql reloads to go an get all from another table.
I was understanding this was just something I did when I created the table the first time as a Character of the table to know on load select * from Test2 This allows me to maintain stability and speed at the same time. While I write to 2 tables I always read from 1 and reading is done 95 times more often at least. I have set the My.cnf to 128M for memory tables as default but it appears I still stop at the 10M limit on memory tables anyway. Should I add something into the creation of the table to override the defaults locally with that table? Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, March 14, 2005 8:28 AM To: [EMAIL PROTECTED] Cc: Gleb Paharenko; mysql@lists.mysql.com Subject: Re: Auto loading a table [EMAIL PROTECTED] wrote: > [Donny Lairson] Quick bump I never got an answer > > I have a table fsearch_temp I use it as a memory table to keep things > light and fast but after a restart I want to repopulate some data > automatically. So I thought I just said load from TEST2 which would by a > myisam table containing the hardbackup I need. But obviously not the > right way of saying this. I must be reading the instructions wrong can > someone clarify this for me? <snip> Which instructions are you reading? I expect you get a syntax error, right? From the manual <http://dev.mysql.com/doc/mysql/en/create-table.html>, the correct syntax is CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] Gleb Paharenko wrote: > Hello. > >>) ENGINE = MEMORY LOAD FROM TEST2 DEFAULT CHARSET = utf8 AUTO_INCREMENT =0 > > You should use select statement, not LOAD. For example: > CREATE TABLE ..... SELECT * FROM TEST2; > > And table options like DEFAULT CHARSET you should put before select statement. > See: > http://dev.mysql.com/doc/mysql/en/create-table.html I think this is accurate but misleading. CREATE ... SELECT adds columns from the SELECT to the columns defined in the CREATE, so you cannot fix this simply by getting the last line right. You have to leave out the column definitions. On the other hand (from the manual page you cite), CREATE TABLE ... SELECT does not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible. If you want to have indexes in the created table, you should specify these before the SELECT statement... so you do need to keep the index definitions. Thus, assuming fsearch_temp's create_definition matches that of table TEST2, to create fsearch_temp as a copy of TEST2, you would CREATE TABLE fsearch_temp ( PRIMARY KEY (fsearchId), KEY fsearchIp (fsearchIp) ) ENGINE = MEMORY DEFAULT CHARACTER SET utf8 SELECT * FROM TEST2; but I don't think this is what you want, either. First, there is this caveat (from the manual): Some conversion of column types might occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can become CHAR columns. To avoid that, you need to first CREATE the table, then populate it with a copy of TEST2 in a separate INSERT ... SELECT statement. See the manual for details <http://dev.mysql.com/doc/mysql/en/insert-select.html>. In any case, MEMORY tables don't go away unless they are dropped. Only the rows disappear when mysql stops. If you've previously created this table and haven't dropped it, it should still exist as an empty table on startup. In that case, you only need to reload the rows. INSERT INTO fsearch_temp SELECT * FROM TEST2; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
