List:Replication« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:August 9 2011 1:17pm
Subject:Re: Problem creating temp tables during replication
View as plain text  
Dear anonymous system dude,

You claim:
On 8/8/2011 17:13, Database System wrote:
> A temporary table doesn't create .frm file, even if you say engine=memory.
>
>....

I say you are incorrect. Here is my proof.
#########################################################

# I created a temporary table using 5.5.12.

localhost.test>show tables;
Empty set (0.00 sec)

localhost.test>create temporary table mytemp (a int) engine=MEMORY;
Query OK, 0 rows affected (0.00 sec)

localhost.test>show tables;
Empty set (0.00 sec)

# Then populated it to prove it was still there

localhost.test>INSERT mytemp (a) VALUES (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

localhost.test>select * from mytemp;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

# The data dir/test folder is empty (which explains why it didn't show 
up in the SHOW TABLES report).

C:\mysql_5.5.12\data\test>dir
  Volume in drive C is System
  Volume Serial Number is 325B-0199

  Directory of C:\mysql_5.5.12\data\test

05/17/2011  14:10    <DIR>          .
05/17/2011  14:10    <DIR>          ..
04/11/2011  15:19                 0 .empty
                1 File(s)              0 bytes

# But the .frm file still exists. The .frm is created in the --tmpdir 
location. If none is specified it uses the default for the OS.

localhost.test>show global variables like 'tmpdir';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| tmpdir        | C:\DOCUME~1\shawn\LOCALS~1\Temp |
+---------------+---------------------------------+
1 row in set (0.00 sec)


C:\mysql_5.5.12\data\test>set
...
TEMP=C:\DOCUME~1\shawn\LOCALS~1\Temp
TMP=C:\DOCUME~1\shawn\LOCALS~1\Temp
...


# If I look *there*, we can see the temporary table's .frm file.

C:\DOCUME~1\shawn\LOCALS~1\Temp>dir *.frm
  Volume in drive C is System
  Volume Serial Number is 325B-0199

  Directory of C:\DOCUME~1\shawn\LOCALS~1\Temp

08/09/2011  08:57             8,554 #sql1248_1_3.frm
                1 File(s)          8,554 bytes

##############################################

The lesson here is that unless MySQL converts all storage engines to use 
some non-existent internal data dictionary, then all tables will have a 
.frm file. You just need to know where to look in order to find it.

Perhaps you should do your research before you try to instruct the rest 
of us in topics you are unfamiliar with? (repeating this exercise on 
Linux is a problem left to the students)

Warmest regards,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Problem creating temp tables during replicationAlex Frangis5 Aug
  • Re: Problem creating temp tables during replicationDatabase System5 Aug
    • Re: Problem creating temp tables during replicationEric Bergen5 Aug
      • Re: Problem creating temp tables during replicationWagner Bianchi5 Aug
        • Re: Problem creating temp tables during replicationDatabase System5 Aug
          • Re: Problem creating temp tables during replicationRick James8 Aug
  • Re: Problem creating temp tables during replicationMySQL)8 Aug
    • Re: Problem creating temp tables during replicationDatabase System8 Aug
      • Re: Problem creating temp tables during replicationMySQL)9 Aug
        • Re: Problem creating temp tables during replicationDatabase System9 Aug
          • Re: Problem creating temp tables during replicationMySQL)9 Aug