Re: Temporary Tables with Triggers Problem

[Ananda Kumar]

can you please share the code of the trigger. Any kind of error your getting


On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins neil.tompk...@googlemail.com
 wrote:

 Hi,

 I've a trigger that writes some data to a temporary table; and at the end
 of the trigger writes all the temporary table data in one insert to our
 normal Innodb table.

 However, for some reason the trigger isn't copying the table from the
 temporary table to the Innodb table.  If I write in the trigger the inserts
 to the Innodb table, it works fine.

 Any ideas why.  I'm running MySQL 5.6.

 Thanks
 Neil

Re: Temporary Tables with Triggers Problem

[Neil Tompkins]

This is my Trigger which doesn't seem to work; but doesn't cause a error

DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
varchar(36),UserId bigint(20),ActionType
enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
varchar(255),FieldName varchar(36),OldValue text,NewValue text);
IF NEW.RoomsToSell  OLD.RoomsToSell THEN
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
END IF;

IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
INSERT INTO AuditTrail
SELECT tempHotelRateAvailability.* FROM tempHotelRateAvailability;

END IF;

DROP TEMPORARY TABLE tempHotelRateAvailability;

However if I use this call in the Trigger and change a value in the table
it works fine;

INSERT INTO AuditTrail
(AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
LoggedOn)
 VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
NOW());


On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar anan...@gmail.com wrote:

 can you please share the code of the trigger. Any kind of error your
 getting


 On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've a trigger that writes some data to a temporary table; and at the end
 of the trigger writes all the temporary table data in one insert to our
 normal Innodb table.

 However, for some reason the trigger isn't copying the table from the
 temporary table to the Innodb table.  If I write in the trigger the
 inserts
 to the Innodb table, it works fine.

 Any ideas why.  I'm running MySQL 5.6.

 Thanks
 Neil

Re: Temporary Tables with Triggers Problem

[Ananda Kumar]

did u check if data is getting inserted into tempHotelRateAvailability


On Wed, May 29, 2013 at 7:21 PM, Neil Tompkins neil.tompk...@googlemail.com
 wrote:

 This is my Trigger which doesn't seem to work; but doesn't cause a error

 DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

 CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
 varchar(36),UserId bigint(20),ActionType
 enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
 varchar(255),FieldName varchar(36),OldValue text,NewValue text);
 IF NEW.RoomsToSell  OLD.RoomsToSell THEN
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
 CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
 END IF;

 IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
 INSERT INTO AuditTrail
 SELECT tempHotelRateAvailability.* FROM tempHotelRateAvailability;

 END IF;

 DROP TEMPORARY TABLE tempHotelRateAvailability;

 However if I use this call in the Trigger and change a value in the table
 it works fine;

 INSERT INTO AuditTrail
 (AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
 LoggedOn)
  VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
 NOW());


 On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar anan...@gmail.com wrote:

 can you please share the code of the trigger. Any kind of error your
 getting


 On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've a trigger that writes some data to a temporary table; and at the end
 of the trigger writes all the temporary table data in one insert to our
 normal Innodb table.

 However, for some reason the trigger isn't copying the table from the
 temporary table to the Innodb table.  If I write in the trigger the
 inserts
 to the Innodb table, it works fine.

 Any ideas why.  I'm running MySQL 5.6.

 Thanks
 Neil

Re: Temporary Tables with Triggers Problem

[Neil Tompkins]

I took the following lines of code slightly modified and it returned some
data using a normal Query Editor

CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
varchar(36),UserId bigint(20),ActionType varchar(36),TableName
varchar(36),RowKey varchar(255),FieldName varchar(36),OldValue
varchar(255),NewValue varchar(255), LoggedOn TIMESTAMP);
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2, NOW());
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',4,4, NOW());
SELECT * FROM tempHotelRateAvailability;


On Wed, May 29, 2013 at 2:57 PM, Ananda Kumar anan...@gmail.com wrote:

 did u check if data is getting inserted into tempHotelRateAvailability


 On Wed, May 29, 2013 at 7:21 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 This is my Trigger which doesn't seem to work; but doesn't cause a error

  DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

 CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
 varchar(36),UserId bigint(20),ActionType
 enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
 varchar(255),FieldName varchar(36),OldValue text,NewValue text);
 IF NEW.RoomsToSell  OLD.RoomsToSell THEN
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
 CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
 END IF;

 IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
 INSERT INTO AuditTrail
 SELECT tempHotelRateAvailability.* FROM
 tempHotelRateAvailability;
 END IF;

 DROP TEMPORARY TABLE tempHotelRateAvailability;

 However if I use this call in the Trigger and change a value in the table
 it works fine;

 INSERT INTO AuditTrail
 (AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
 LoggedOn)
  VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
 NOW());


 On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar anan...@gmail.com wrote:

 can you please share the code of the trigger. Any kind of error your
 getting


 On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've a trigger that writes some data to a temporary table; and at the
 end
 of the trigger writes all the temporary table data in one insert to our
 normal Innodb table.

 However, for some reason the trigger isn't copying the table from the
 temporary table to the Innodb table.  If I write in the trigger the
 inserts
 to the Innodb table, it works fine.

 Any ideas why.  I'm running MySQL 5.6.

 Thanks
 Neil

Re: Temporary Tables with Triggers Problem

[Ananda Kumar]

But, does it work inside the trigger. If not, then based on the logic,
there will not be any data, and data goes not get inserted from temp table
to innodb table


On Wed, May 29, 2013 at 7:29 PM, Neil Tompkins neil.tompk...@googlemail.com
 wrote:

 I took the following lines of code slightly modified and it returned some
 data using a normal Query Editor

 CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
 varchar(36),UserId bigint(20),ActionType varchar(36),TableName
 varchar(36),RowKey varchar(255),FieldName varchar(36),OldValue
 varchar(255),NewValue varchar(255), LoggedOn TIMESTAMP);
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2, NOW());
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',4,4, NOW());
 SELECT * FROM tempHotelRateAvailability;


 On Wed, May 29, 2013 at 2:57 PM, Ananda Kumar anan...@gmail.com wrote:

 did u check if data is getting inserted into tempHotelRateAvailability


 On Wed, May 29, 2013 at 7:21 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 This is my Trigger which doesn't seem to work; but doesn't cause a error

  DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

 CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
 varchar(36),UserId bigint(20),ActionType
 enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
 varchar(255),FieldName varchar(36),OldValue text,NewValue text);
 IF NEW.RoomsToSell  OLD.RoomsToSell THEN
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
 CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
 END IF;

 IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
 INSERT INTO AuditTrail
 SELECT tempHotelRateAvailability.* FROM
 tempHotelRateAvailability;
 END IF;

 DROP TEMPORARY TABLE tempHotelRateAvailability;

 However if I use this call in the Trigger and change a value in the
 table it works fine;

 INSERT INTO AuditTrail
 (AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
 LoggedOn)
  VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
 NOW());


 On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar anan...@gmail.com wrote:

 can you please share the code of the trigger. Any kind of error your
 getting


 On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've a trigger that writes some data to a temporary table; and at the
 end
 of the trigger writes all the temporary table data in one insert to our
 normal Innodb table.

 However, for some reason the trigger isn't copying the table from the
 temporary table to the Innodb table.  If I write in the trigger the
 inserts
 to the Innodb table, it works fine.

 Any ideas why.  I'm running MySQL 5.6.

 Thanks
 Neil

Re: Temporary Tables with Triggers Problem

[Neil Tompkins]

OK, the data is going into the temp table.  But when I run the
command INSERT INTO AuditTrail SELECT tempHotelRateAvailability.* FROM
tempHotelRateAvailability;   from the TRIGGER it does not copy the data.

However if I run this query INSERT INTO AuditTrail SELECT
tempHotelRateAvailability.* FROM tempHotelRateAvailability;   from the
MySQL query editor (not the trigger), it copies the data find.

Any ideas ?


On Wed, May 29, 2013 at 3:02 PM, Ananda Kumar anan...@gmail.com wrote:

 But, does it work inside the trigger. If not, then based on the logic,
 there will not be any data, and data goes not get inserted from temp table
 to innodb table


 On Wed, May 29, 2013 at 7:29 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 I took the following lines of code slightly modified and it returned some
 data using a normal Query Editor

 CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
 varchar(36),UserId bigint(20),ActionType varchar(36),TableName
 varchar(36),RowKey varchar(255),FieldName varchar(36),OldValue
 varchar(255),NewValue varchar(255), LoggedOn TIMESTAMP);
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2, NOW());
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',4,4, NOW());
 SELECT * FROM tempHotelRateAvailability;


 On Wed, May 29, 2013 at 2:57 PM, Ananda Kumar anan...@gmail.com wrote:

 did u check if data is getting inserted into tempHotelRateAvailability


 On Wed, May 29, 2013 at 7:21 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 This is my Trigger which doesn't seem to work; but doesn't cause a error

  DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

 CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
 varchar(36),UserId bigint(20),ActionType
 enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
 varchar(255),FieldName varchar(36),OldValue text,NewValue text);
 IF NEW.RoomsToSell  OLD.RoomsToSell THEN
 INSERT INTO tempHotelRateAvailability VALUES
 (UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
 CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
 END IF;

 IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
 INSERT INTO AuditTrail
 SELECT tempHotelRateAvailability.* FROM
 tempHotelRateAvailability;
 END IF;

 DROP TEMPORARY TABLE tempHotelRateAvailability;

 However if I use this call in the Trigger and change a value in the
 table it works fine;

 INSERT INTO AuditTrail
 (AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
 LoggedOn)
  VALUES (UUID(),1,'UPDATE','HotelRateAvailability',
 1,'RoomsToSell',1,2, NOW());


 On Wed, May 29, 2013 at 2:49 PM, Ananda Kumar anan...@gmail.comwrote:

 can you please share the code of the trigger. Any kind of error your
 getting


 On Wed, May 29, 2013 at 6:49 PM, Neil Tompkins 
 neil.tompk...@googlemail.com wrote:

 Hi,

 I've a trigger that writes some data to a temporary table; and at the
 end
 of the trigger writes all the temporary table data in one insert to
 our
 normal Innodb table.

 However, for some reason the trigger isn't copying the table from the
 temporary table to the Innodb table.  If I write in the trigger the
 inserts
 to the Innodb table, it works fine.

 Any ideas why.  I'm running MySQL 5.6.

 Thanks
 Neil

Re: Temporary Tables with Triggers Problem

[hsv]

 2013/05/29 14:51 +0100, Neil Tompkins 
This is my Trigger which doesn't seem to work; but doesn't cause a error

DROP TEMPORARY TABLE IF EXISTS tempHotelRateAvailability;

CREATE TEMPORARY TABLE tempHotelRateAvailability(AuditTrailId
varchar(36),UserId bigint(20),ActionType
enum('INSERT','UPDATE','DELETE'),TableName varchar(36),RowKey
varchar(255),FieldName varchar(36),OldValue text,NewValue text);
IF NEW.RoomsToSell  OLD.RoomsToSell THEN
INSERT INTO tempHotelRateAvailability VALUES
(UUID(),NEW.LastChangedBy,'UPDATE','HotelRateAvailability',
CONCAT(OLD.RoomID,'|',OLD.Day),'RoomsToSell',OLD.RoomsToSell,NEW.RoomsToSell);
END IF;

IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
INSERT INTO AuditTrail
SELECT tempHotelRateAvailability.* FROM tempHotelRateAvailability;

END IF;

DROP TEMPORARY TABLE tempHotelRateAvailability;

However if I use this call in the Trigger and change a value in the table
it works fine;

INSERT INTO AuditTrail
(AuditTrailId,UserId,ActionType,TableName,RowKey,FieldName,OldValue,NewValue,
LoggedOn)
 VALUES (UUID(),1,'UPDATE','HotelRateAvailability', 1,'RoomsToSell',1,2,
NOW()); 


You have left out the opening line, but it looks like AFTER UPDATE; is the 
table AuditTrail, or another?

How did this pass the parser,
IF SELECT COUNT(*) FROM tempHotelRateAvailability  0 THEN
? If Workbench corrected it, there is no knowing what the code really is. This 
is correct,
IF (SELECT COUNT(*) FROM tempHotelRateAvailability)  0 THEN
but it is just as well to write
IF EXISTS(SELECT * FROM tempHotelRateAvailability) THEN
.

Why bother with the temporary table? It never has more rows; it is just as well 
to insert straight into AuditTrail if NEW.RoomsToSell  OLD.RoomsToSell.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Temporary tables created on disk: 99%

[Rob Wultsch]

On Tue, Jun 1, 2010 at 8:40 AM, John G. Heim jh...@math.wisc.edu wrote:
 On my db server, mysql has 2 gigabytes for temporary tables and yet its
 creating 99% of temporary tables on disk.

 According to mysqltuner, 99% of temporary tables are created on disk.

Probably blobs:
Instances of BLOB or TEXT columns in the result of a query that is
processed using a temporary table causes the server to use a table on
disk rather than in memory because the MEMORY storage engine does not
support those data types (see Section 7.5.10, “How MySQL Uses Internal
Temporary Tables”). Use of disk incurs a performance penalty, so
include BLOB or TEXT columns in the query result only if they are
really needed. For example, avoid using SELECT *, which selects all
columns. 

From http://dev.mysql.com/doc/refman/5.0/en/blob.html


If you can get us a query, explain, and table/index info we may be
able to help you more.

Also, you might want to consider creating a ram disk so that filesorts
that must be done on disk might be done inexpensively.

-- 
Rob Wultsch
wult...@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: temporary tables on disk?

[Chris Scheller]

Baron Schwartz wrote on Thu, Sep 20, 2007 at 07:46:44PM -0400:
 Michael explained it well, but just to say it a different way, the 
 temporary table is created as an intermediate step in the table, and is 
 implicit, not explicit.  So it's not sent to the slave -- the query is 
 sent to the slave, and if the query optimizer makes the same decisions 
 on the slave that it did on the master, the slave will also create a 
 temporary table during query processing.

i should have been more clear. what i meant is that the create temporary
table query is sent to the slave, and the table is created on the
slave. this much i know from slaves dying then not restarting due to
the missing temporary table. my current problem is my slaves are
creating myisam temporary tables on disk and alot of them. which
eventualy results in mysqld no longer being able to open any files
with

ERROR: 1  Can't create/write to file '/sql/tmp/#sql17fb_2f_0.frm'
(Errcode: 24)
070919 11:06:41  Slave: Error 'Can't create/write to file
'/sql/tmp/#sql17fb_2f_0.frm' (Errcode: 24)' on query 'CREATE TEMPORARY
TABLE aggregate (

along with messages like

070919 22:04:03  Error in accept: Too many open files

what brought this all on was a switch to new hardware(completely new
server) for the master.  kept the same version of mysql on the new
hardware, slaves didn't change at all, other then resetting up
replication from scratch with the data snapshot from the current
master after hardware swap. this didn't happen on the slaves prior to
the hardware swap.

i don't understand why the temp tables are being written to disk now,
when they weren't in the past. 


 
 Michael Dykman wrote:
 Temporary tables only exist for the length of time that the connection
 that created them remains connected and are only visible to that
 connection.  There is no reason to replicate these to a slave at all,
 as no client connecting to that slave would ever be able to see them.
 
  - michael dykman
 
 
 On 9/20/07, Chris Scheller [EMAIL PROTECTED] wrote:
 according to
 http://dev.mysql.com/doc/refman/4.1/en/internal-temporary-tables.html
 temporary tables can sometimes be written to disk as myisam. in
 replication are these myisam temp tables sent to the slaves as myisam
 tables or in memory tables?
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 
 
 
 

-- 
++---+---+
| Chris Scheller | http://www.pobox.com/~schelcj | JID: [EMAIL PROTECTED]  |
++---+---+
| Ubuntu   -- an African word, meaning Slackware is too hard for me  |
| Mandriva -- a French word, meaning Slackware is too hard for me|
| Fedora   -- an American word, meaning Slackware is too hard for me |
|-- seen on alt.os.linux.slackware   |
++
  There was a boy called Eustace Clarence Scrubb, and he almost deserved
it.
-- C. S. Lewis, The Chronicles of Narnia


pgpeqUn2WliIi.pgp
Description: PGP signature

Re: temporary tables on disk?

[Michael Dykman]

There is a setting in your my.cnf which specifies the threshold at
which temporary tables will be put to disk instead of being held in
RAM.  This has to be a dynamic decision as the system has to consider
available RAM and the size of any given temporary table..  under
normal circumstances, the my.cnf setting will be respected, but it is
NOT a guarantee.

Have you read this?
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_tmp_table_size

 - michael dykman

On 9/21/07, Chris Scheller [EMAIL PROTECTED] wrote:
 Michael Dykman wrote on Fri, Sep 21, 2007 at 12:19:23PM -0400:
  Your issue might be related to the host OS, namely a per-process limit
  on file handles.  What OS are you using?

 that is definitely part of the problem. since so many temporary tables
 are being created, mysqld just shits itself after awhile. my main
 problem, as i see it is that these temp tables are being written to
 disk in the first place. prior to my server switch this wasn't the
 case. in any case, the box is slackware-11.0 and this is
 mysql-4.0.17(yes i know it is old, and i did try to migrate to 5 with
 not fun results. updating this code base to support mysql-5.0.x, just
 isn't an option right now.)


 --
 ++---+---+
 | Chris Scheller | http://www.pobox.com/~schelcj | JID: [EMAIL PROTECTED]  |
 ++---+---+
 | Ubuntu   -- an African word, meaning Slackware is too hard for me  |
 | Mandriva -- a French word, meaning Slackware is too hard for me|
 | Fedora   -- an American word, meaning Slackware is too hard for me |
 |-- seen on alt.os.linux.slackware   |
 ++
   I was playing poker the other night ... with Tarot cards. I got a full
 house and four people died.
 -- Steven Wright




-- 
 - michael dykman
 - [EMAIL PROTECTED]

 - All models are wrong.  Some models are useful.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: temporary tables on disk?

[Chris Scheller]

Michael Dykman wrote on Fri, Sep 21, 2007 at 01:37:57PM -0400:
 There is a setting in your my.cnf which specifies the threshold at
 which temporary tables will be put to disk instead of being held in
 RAM.  This has to be a dynamic decision as the system has to consider
 available RAM and the size of any given temporary table..  under
 normal circumstances, the my.cnf setting will be respected, but it is
 NOT a guarantee.
 
 Have you read this?
 http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_tmp_table_size

yes, several times. both max_heap_table_size and tmp_table_size have
remained at the defaults. even still, these temp tables are not that
big and _should_not_ be bumping into that limit. and they never have
hit that limit in the four to five years this code has been in
production. at the moment i have everything running and stable with
all slaves replicating cleanly. 

-- 
++---+---+
| Chris Scheller | http://www.pobox.com/~schelcj | JID: [EMAIL PROTECTED]  |
++---+---+
| Ubuntu   -- an African word, meaning Slackware is too hard for me  |
| Mandriva -- a French word, meaning Slackware is too hard for me|
| Fedora   -- an American word, meaning Slackware is too hard for me |
|-- seen on alt.os.linux.slackware   |
++
  Lunatic Asylum, n.:
The place where optimism most flourishes.


pgpbbyOEiGP5U.pgp
Description: PGP signature

Re: temporary tables on disk?

[Michael Dykman]

Temporary tables only exist for the length of time that the connection
that created them remains connected and are only visible to that
connection.  There is no reason to replicate these to a slave at all,
as no client connecting to that slave would ever be able to see them.

 - michael dykman


On 9/20/07, Chris Scheller [EMAIL PROTECTED] wrote:
 according to
 http://dev.mysql.com/doc/refman/4.1/en/internal-temporary-tables.html
 temporary tables can sometimes be written to disk as myisam. in
 replication are these myisam temp tables sent to the slaves as myisam
 tables or in memory tables?


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
 - michael dykman
 - [EMAIL PROTECTED]

 - All models are wrong.  Some models are useful.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: temporary tables on disk?

[Baron Schwartz]

Michael explained it well, but just to say it a different way, the 
temporary table is created as an intermediate step in the table, and is 
implicit, not explicit.  So it's not sent to the slave -- the query is 
sent to the slave, and if the query optimizer makes the same decisions 
on the slave that it did on the master, the slave will also create a 
temporary table during query processing.


Michael Dykman wrote:

Temporary tables only exist for the length of time that the connection
that created them remains connected and are only visible to that
connection.  There is no reason to replicate these to a slave at all,
as no client connecting to that slave would ever be able to see them.

 - michael dykman


On 9/20/07, Chris Scheller [EMAIL PROTECTED] wrote:

according to
http://dev.mysql.com/doc/refman/4.1/en/internal-temporary-tables.html
temporary tables can sometimes be written to disk as myisam. in
replication are these myisam temp tables sent to the slaves as myisam
tables or in memory tables?


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

Re: temporary tables

[Curtis Maurand]

You were right.  Its a global privilege not a table one.  I granted it at
a global level.  it can't be granted at the databae level

Dan Buettner wrote:
 Curtis, you might need to make sure 'admin'@'localhost' has 'FILE'
 privileges in the proper database, for load data infile.  Note that
 'admin'@'%' is not the same as 'admin'@'localhost'

 See http://dev.mysql.com/doc/refman/5.0/en/grant.html


 Also here is a comment from the same page:
 QUOTE
 Posted by Cristian Gafton on July 17 2006 6:28pm  [Delete] [Edit]
 Please note that the current MySQL priviledge model does not support
 creating a read-only user for an application that needs to work with
 temporary tables. In other words, the 'SELECT,CREATE TEMPORARY TABLES'
 privileges are not sufficient to let an application work with
 temporary tables against a read-only set of tables. MySQL also
 requires INSERT, DELETE and UPDATE grants to allow temporary tables to
 be used. Since the temporary tables are not 'defined' at the time of
 the grant, one would have no choice but to grant INSERT,DELETE,UPDATE
 on db.*, which negates the possibility of a read-only user.

 It would be nice if MySQL would have a more generic 'TEMPORARY TABLES'
 permission that would allow one to create, insert, delete from and
 drop temporary tables without having to give up insert/update/delete
 privileges on the real tables

 /QUOTE

 Dan



 On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote:

 here is what I get.  I'm logged in as the user admin (really a regular
 user.)

 mysql create temporary table customer_tmp as select * from customer
 limit 0;
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 mysql load data infile '/home/bluetarp/ezauth/customers.txt' into table
 customer_tmp;
 ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using
 password: YES)




 Dan Buettner wrote:
  Or possibly that the mysql user on the box does not have access to the
  data file in question.  Can you post the error messages you get?
 
  Dan
 
 
  On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote:
  This may sound like a stupid question, but I have to ask.  I've been
  running a script that goes like the following.
 
 
 
  use ecommerce;
  create temporary table customer_tmp as select * from customer limit
 0;
  load data infile '/home/bluetarp/ezauth/customers.txt' into table
   \customer_tmp;
 
  at which point the script fails for permssion reasons.  It seems that
  this
  user can create a temporary table, but not load data into it?
 
  What did I miss on permissions to allow this to work?
 
  Thanks
  Curtis
 
 
  --
  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]
 







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: temporary tables

[Dan Buettner]

Or possibly that the mysql user on the box does not have access to the
data file in question.  Can you post the error messages you get?

Dan


On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote:

This may sound like a stupid question, but I have to ask.  I've been
running a script that goes like the following.



use ecommerce;
create temporary table customer_tmp as select * from customer limit 0;
load data infile '/home/bluetarp/ezauth/customers.txt' into table
 \customer_tmp;

at which point the script fails for permssion reasons.  It seems that this
user can create a temporary table, but not load data into it?

What did I miss on permissions to allow this to work?

Thanks
Curtis


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

Re: temporary tables

[Curtis Maurand]

here is what I get.  I'm logged in as the user admin (really a regular user.)

mysql create temporary table customer_tmp as select * from customer limit 0;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql load data infile '/home/bluetarp/ezauth/customers.txt' into table
customer_tmp;
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using
password: YES)




Dan Buettner wrote:
 Or possibly that the mysql user on the box does not have access to the
 data file in question.  Can you post the error messages you get?

 Dan


 On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote:
 This may sound like a stupid question, but I have to ask.  I've been
 running a script that goes like the following.



 use ecommerce;
 create temporary table customer_tmp as select * from customer limit 0;
 load data infile '/home/bluetarp/ezauth/customers.txt' into table
  \customer_tmp;

 at which point the script fails for permssion reasons.  It seems that
 this
 user can create a temporary table, but not load data into it?

 What did I miss on permissions to allow this to work?

 Thanks
 Curtis


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




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: temporary tables

[Dan Buettner]

Curtis, you might need to make sure 'admin'@'localhost' has 'FILE'
privileges in the proper database, for load data infile.  Note that
'admin'@'%' is not the same as 'admin'@'localhost'

See http://dev.mysql.com/doc/refman/5.0/en/grant.html


Also here is a comment from the same page:
QUOTE
Posted by Cristian Gafton on July 17 2006 6:28pm[Delete] [Edit]
Please note that the current MySQL priviledge model does not support
creating a read-only user for an application that needs to work with
temporary tables. In other words, the 'SELECT,CREATE TEMPORARY TABLES'
privileges are not sufficient to let an application work with
temporary tables against a read-only set of tables. MySQL also
requires INSERT, DELETE and UPDATE grants to allow temporary tables to
be used. Since the temporary tables are not 'defined' at the time of
the grant, one would have no choice but to grant INSERT,DELETE,UPDATE
on db.*, which negates the possibility of a read-only user.

It would be nice if MySQL would have a more generic 'TEMPORARY TABLES'
permission that would allow one to create, insert, delete from and
drop temporary tables without having to give up insert/update/delete
privileges on the real tables

/QUOTE

Dan



On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote:


here is what I get.  I'm logged in as the user admin (really a regular user.)

mysql create temporary table customer_tmp as select * from customer limit 0;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql load data infile '/home/bluetarp/ezauth/customers.txt' into table
customer_tmp;
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using
password: YES)




Dan Buettner wrote:
 Or possibly that the mysql user on the box does not have access to the
 data file in question.  Can you post the error messages you get?

 Dan


 On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote:
 This may sound like a stupid question, but I have to ask.  I've been
 running a script that goes like the following.



 use ecommerce;
 create temporary table customer_tmp as select * from customer limit 0;
 load data infile '/home/bluetarp/ezauth/customers.txt' into table
  \customer_tmp;

 at which point the script fails for permssion reasons.  It seems that
 this
 user can create a temporary table, but not load data into it?

 What did I miss on permissions to allow this to work?

 Thanks
 Curtis


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






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: temporary tables

[Curtis Maurand]

When I created the user, I specified:

grant all on ecommerce.* to admin@'localhost' identified by 'password';

Doesn't that cover the file privilege?  I noticed the navicat commercial
product doesn't list that privilege specifically.  the mysql-administrator
just locks up when I go to manage users.  i've been working it from the
command line.

Curtis

Dan Buettner wrote:
 Curtis, you might need to make sure 'admin'@'localhost' has 'FILE'
 privileges in the proper database, for load data infile.  Note that
 'admin'@'%' is not the same as 'admin'@'localhost'

 See http://dev.mysql.com/doc/refman/5.0/en/grant.html


 Also here is a comment from the same page:
 QUOTE
 Posted by Cristian Gafton on July 17 2006 6:28pm  [Delete] [Edit]
 Please note that the current MySQL priviledge model does not support
 creating a read-only user for an application that needs to work with
 temporary tables. In other words, the 'SELECT,CREATE TEMPORARY TABLES'
 privileges are not sufficient to let an application work with
 temporary tables against a read-only set of tables. MySQL also
 requires INSERT, DELETE and UPDATE grants to allow temporary tables to
 be used. Since the temporary tables are not 'defined' at the time of
 the grant, one would have no choice but to grant INSERT,DELETE,UPDATE
 on db.*, which negates the possibility of a read-only user.

 It would be nice if MySQL would have a more generic 'TEMPORARY TABLES'
 permission that would allow one to create, insert, delete from and
 drop temporary tables without having to give up insert/update/delete
 privileges on the real tables

 /QUOTE

 Dan



 On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote:

 here is what I get.  I'm logged in as the user admin (really a regular
 user.)

 mysql create temporary table customer_tmp as select * from customer
 limit 0;
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 mysql load data infile '/home/bluetarp/ezauth/customers.txt' into table
 customer_tmp;
 ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using
 password: YES)




 Dan Buettner wrote:
  Or possibly that the mysql user on the box does not have access to the
  data file in question.  Can you post the error messages you get?
 
  Dan
 
 
  On 8/15/06, Curtis Maurand [EMAIL PROTECTED] wrote:
  This may sound like a stupid question, but I have to ask.  I've been
  running a script that goes like the following.
 
 
 
  use ecommerce;
  create temporary table customer_tmp as select * from customer limit
 0;
  load data infile '/home/bluetarp/ezauth/customers.txt' into table
   \customer_tmp;
 
  at which point the script fails for permssion reasons.  It seems that
  this
  user can create a temporary table, but not load data into it?
 
  What did I miss on permissions to allow this to work?
 
  Thanks
  Curtis
 
 
  --
  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]
 







-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: temporary tables

[Frederic Wenzel]

On 8/15/06, Dan Buettner [EMAIL PROTECTED] wrote:

It would be nice if MySQL would have a more generic 'TEMPORARY TABLES'
permission that would allow one to create, insert, delete from and
drop temporary tables without having to give up insert/update/delete
privileges on the real tables


Can't you blacklist the real tables in turn? I.e. assign create,
insert, update rights to the new user and then assign table specific
rights to him as well, revoking these rights for the individual real
tables?

That's quite a bit more work and has to be maintained everytime you
add a new real table (so it's no real replacement for your suggestion
above) but it seems to be a quite secure workaround.

Fred

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: temporary tables

[Alec . Cawley]

Ted Toporkov [EMAIL PROTECTED] wrote on 15/03/2005 11:04:09:

 List,
 
 I'm trying to create tables that will store data temporarily, if a php 
 page generates data to fill the table, then any number of queries could 
 be run on the table, and then be automatically be deleted if it's not 
 queries for let's say an hour or something like that.
 
 Can this be accomplished with temporary tables, or should i just create 
 static tables and then use a cron job to delete unused ones?
 
 What is the best way to approach this?

Temporary tables are private to a single Connection, and would therefore 
not be an appropriate solution to this problem. If you have to do it, the 
cron job appears youe best bet. However, I query the requirement. ISTM 
that you are basically saying that you do not trust MySQL's cachein 
ability, both to cache recently used table blocks and to cache the result 
of recent queries. I would take the first approximation of trusting MySQL 
and only attempting solutions such as that which you propose when you know 
for certain that the system will not handle them without. Have you fully 
characterised the behaviour of the system without this kludge in place?

Alec





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: temporary tables

[Gleb Paharenko]

Hello.



A TEMPORARY table is visible only to the current connection,

and is dropped automatically when the connection is closed. 

So, I think, they won't solve your task.





Ted Toporkov [EMAIL PROTECTED] wrote:

 List,

 

 I'm trying to create tables that will store data temporarily, if a php 

 page generates data to fill the table, then any number of queries could 

 be run on the table, and then be automatically be deleted if it's not 

 queries for let's say an hour or something like that.

 

 Can this be accomplished with temporary tables, or should i just create 

 static tables and then use a cron job to delete unused ones?

 

 What is the best way to approach this?

 

 Ted Toporkov

 [EMAIL PROTECTED]

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: temporary tables, replication, and innodb tables

[Heikki Tuuri]

Mike,
- Original Message - 
From: Mike Debnam [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Sunday, November 21, 2004 9:25 PM
Subject: temporary tables, replication, and innodb tables


I'm working on a new series of queries that share a temporary table.
I've tried using both create temporary table  select and create
temp table then insert into t ... select from y. Both seem to
create a lock that (if the select portion runs long enough) causes a
deadlock with the replication thread on that box (it's a slave).
do you have binlogging switched on in the slave? If yes, then CREATE ... 
SELECT ... will indeed take locks on the rows it scans in the SELECT. If 
not, then the SELECT is a consistent, non-locking read. Can you switch off 
binlogging?

Another solution is to use SELECT ... INTO OUTFILE. That is always processed 
as a consistent read.

When the select portion runs more than innodb_lock_wait_timeout
seconds the slave replication thread dies with the errors:
041119 16:54:06 [ERROR] Slave: Error 'Lock wait timeout exceeded; try
restarting transaction' on query. ., Error_code: 1205
041119 16:54:06 [ERROR] Error running query, slave SQL thread aborted.
Fix the problem, and restart the slave SQL thread with SLAVE START.
We stopped at log 'db-bin.81' position 65976472
Am I missing something here or is the only way to avoid potential
problems with the slave replication thread is to increase
innodb_lock_wait_timeout to a large enough value that it will be
longer than any potential select for the temporary table?
That is a solution.
This locking problem is yet another case where the upcoming MySQL row-level 
binlogging will help. Then we do not need locks on the SELECT table ever, 
because execution does not need to be serializable.

All innodb tables, MySQL 4.1.7 for both master and slaves.
Thanks.
Mike
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL technical support from https://order.mysql.com/ 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: temporary tables and replication

[Eric Bergen]

I believe some versions of 4.0 have problem with auto dropping
temporary tables in replication. The easy fix is to drop temporary
tables when you are finished with them instead of relying on mysql to
do it for you.

-- 
Eric Bergen
[EMAIL PROTECTED]


On Wed, 1 Sep 2004 08:16:43 -0600, Alfredo Cole [EMAIL PROTECTED] wrote:
 Hi:
 
 I'm using MySQL 4.0, and have setup replication with one server and one slave,
 both running LM 9.1. My application creates a temporary table on the master,
 which is supposed to be deleted when the connection is closed. When that
 happens, replication stops with the following message:
 
 ERROR: 1051  Unknown table 'tmpclisal'
 040831 20:16:38  Slave: error 'Unknown table 'tmpclisal'' on query
 'DROP /*!40005 TEMPORARY */ TABLE truepos.tmpclisal', error_code=1051
 040831 20:16:38  Error running query, slave SQL thread aborted. Fix the
 problem, and restart the slave SQL thread with SLAVE START. We stopped at
 log 'central-bin.001' position 12475966
 
 Is there a problem in using temporary tables and replication? I have not found
 any clues in the manual. If there is a problem, perhaps someone can point to
 a url where I can find a solution?
 
 Thank you.
 
 --
 Alfredo J. Cole
 Grupo ACyC
 www.acyc.com - www.clshonduras.com - SolCom
 
 --
 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]

Re: Temporary tables in mySQL

[Rhino]

It's dead easy; see the example below. By the way, the term you want is
'join', not 'merge'.

In the example, Sample is the name of the database. 'emp' and 'dept' are two
tables that can be joined on a common value; the common value is called
'workdept' in the 'emp' table and 'deptno' in the 'dept' table.

Here are the table layouts:

+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| deptno   | char(3) |  | PRI | |   |
| deptname | varchar(36) |  | | |   |
| mgrno| varchar(6)  | YES  | | NULL|   |
+--+-+--+-+-+---+

+--+--+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+--+--+-+-+---+
| empno| char(6)  |  | PRI | |   |
| firstnme | char(12) |  | | |   |
| midinit  | char(1)  | YES  | | NULL|   |
| lastname | char(15) |  | | |   |
| workdept | char(3)  |  | MUL | |   |
| salary   | decimal(9,2) |  | | 0.00|   |
+--+--+--+-+-+---+

Here is the sample script, written in bash, that demonstrates what you want
to do:

use Sample;

select Create temporary table containing join result as Comment;
drop table if exists join_temp;
create temporary table if not exists join_temp
select *
from dept as d inner join emp as e
on e.workdept = d.deptno;

select Display temporary table as Comment;
select * from join_temp;

Rhino

- Original Message - 
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, March 15, 2004 3:31 PM
Subject: Temporary tables in mySQL


 Hi,
  Is it possible to create a temporary table in mySQL using columns
from tables in two databases? Basically I want to split a table between two
databases and if some logic is satisfied I want to merge(the right word?)
the
 two tables into one in one of these databases. What's the best way to
achieve this?
  I am using mySQL 4.0.15. Any   information/help/suggestions or
pointers would be much appreciated.

 Thanks

 -Ravi


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

Re: temporary tables

[Paul DuBois]

At 10:33 -0500 1/24/04, Mike Mapsnac wrote:
Which of these three joins are using temporary table?

# 1 Inner Join
# 2 Left Join
#3  Outer Join


I don't understand what you're asking here.  Can you be more specific
about what you want to know?
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Temporary tables rights

[adburne]

Matt, thanks for your reply,this weekend I was thinking the way to do this work, because I use many times the same temporary table name for differentscriptswith differenttable structure.

All my temp tables begin with 'TMP', I thought grant something like 'TMP%' on tables_priv.table_name but looking in mysql's manual this doesn't work.

But there is a tricky,if you know thenames of your temp tables you can make an insert on tables_priv with the table name:

INSERT INTO tables_priv (host, db, user, table_name, grantor, table_priv, column_priv)VALUES ('host.localdomain','db1','user1','TMP_Liq', USER(), 'Select,Insert,Update,Delete,Alter,Drop,Index','')

and this work!

Alejandro

---Mensaje original---


De: Matt W
Fecha: sábado 13 de diciembre de 2003 22:36:54
A: adburne; [EMAIL PROTECTED]
Asunto: Re: Temporary tables rights

Hi Alejandro,

Yeah, this issue has come up before. It's not possible to GRANT DROP on
temp tables without GRANTing DROP on the whole database.

The temp tables will be dropped when the client disconnects you know,
right? And if you want to empty the table or reuse it, you should be
able to TRUNCATE it, I think.

And actually, if you can TRUNCATE the other tables (if the DELETE
privilege allows it), isn't that just as bad as DROPping them? :-)


Matt


- Original Message -
From: adburne
To: [EMAIL PROTECTED]
Sent: Friday, December 12, 2003 11:31 AM
Subject: Temporary tables rights


Hi, I'm granting users to use temporary tables as:

GRANT CREATE TEMPORARY TABLES ON db1.* TO user1;

and having grants on many other tables as:

GRANT SELECT, INSERT, UPDATE, DELETE ON db1.table1 TO user1;
GRANT SELECT, INSERT, UPDATE, DELETE ON db1.table2 TO user1;
.

but how make this work

CREATE TEMPORARY TABLE tmp1 AS SELECT * FROM table1;
ok

SELECT * FROM tmp1;
Error: select command denied to user: [EMAIL PROTECTED] for table tmp1

also:
DROP TABLE tmp1;
Error: drop command denied to user: [EMAIL PROTECTED] for table tmp1

I don't want grant select and "drop" global privilege over db1 but I
want use temporary tables, there is a way to do this work?

Alejandro
.







_ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí

Re: Temporary tables rights

[Matt W]

Hi Alejandro,

Yeah, this issue has come up before. It's not possible to GRANT DROP on
temp tables without GRANTing DROP on the whole database.

The temp tables will be dropped when the client disconnects you know,
right? And if you want to empty the table or reuse it, you should be
able to TRUNCATE it, I think.

And actually, if you can TRUNCATE the other tables (if the DELETE
privilege allows it), isn't that just as bad as DROPping them? :-)


Matt


- Original Message -
From: adburne
To: [EMAIL PROTECTED]
Sent: Friday, December 12, 2003 11:31 AM
Subject: Temporary tables rights


Hi, I'm granting users to use temporary tables as:

GRANT CREATE TEMPORARY TABLES ON db1.* TO user1;

and having grants on many other tables as:

GRANT SELECT, INSERT, UPDATE, DELETE ON db1.table1 TO user1;
GRANT SELECT, INSERT, UPDATE, DELETE ON db1.table2 TO user1;


but how make this work

CREATE TEMPORARY TABLE tmp1 AS SELECT * FROM table1;
ok

SELECT * FROM tmp1;
Error: select command denied to user: [EMAIL PROTECTED] for table tmp1

also:
DROP TABLE tmp1;
Error: drop command denied to user: [EMAIL PROTECTED] for table tmp1

I don't want grant select and drop global privilege over db1 but I
want use temporary tables, there is a way to do this work?

Alejandro


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: temporary tables

[Victoria Reznichenko]

[EMAIL PROTECTED] wrote:
 
 I am having trouble creating temp tables.  An error message keeps coming up 
 saying that @localhost does not exist.  (or something like that) can anyone 
 help.

Could you show us exactly error message? Does user have CREATE TEMPORARY TABLES 
privilege?


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Temporary tables

[Antony Dovgal]

On Thu, 25 Sep 2003 02:26:28 -0600
[EMAIL PROTECTED] wrote:

 1) What happens if two (or more) users create a temporary table with the 
 same name at the same time?
 2) If i don't drop the tables, when are the tables droped by the server?
 3) what are the memory and or performance issues of temporary tables?
 4) Can i use session based temporary tables?

http://www.mysql.com/doc/en/CREATE_TABLE.html

The temporary table is visible only to the current connection, and will be deleted 
automatically when the connection is closed. This means that two different connections 
can both use the same temporary table name without conflicting with each other or with 
an existing table of the same name. (The existing table is hidden until the temporary 
table is deleted.) From MySQL 4.0.2 on, you must have the CREATE TEMPORARY TABLES 
privilege to be able to create temporary tables. 

---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Temporary tables

[fbeltran]

Thanks!



Antony Dovgal [EMAIL PROTECTED] 
25/09/2003 02:33 a.m.

To
[EMAIL PROTECTED]
cc

Subject
Re: Temporary tables






On Thu, 25 Sep 2003 02:26:28 -0600
[EMAIL PROTECTED] wrote:

 1) What happens if two (or more) users create a temporary table with the 

 same name at the same time?
 2) If i don't drop the tables, when are the tables droped by the server?
 3) what are the memory and or performance issues of temporary tables?
 4) Can i use session based temporary tables?

http://www.mysql.com/doc/en/CREATE_TABLE.html

The temporary table is visible only to the current connection, and will be 
deleted automatically when the connection is closed. This means that two 
different connections can both use the same temporary table name without 
conflicting with each other or with an existing table of the same name. 
(The existing table is hidden until the temporary table is deleted.) From 
MySQL 4.0.2 on, you must have the CREATE TEMPORARY TABLES privilege to be 
able to create temporary tables. 

---
WBR,
Antony Dovgal aka tony2001
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

Re: Temporary tables

[Egor Egorov]

Mikhail Entaltsev [EMAIL PROTECTED] wrote:
 Hi,
 
 I've found the phrase in MySQL documentation
 http://www.mysql.com/doc/en/Temporary_table_problems.html
 
 You can't use temporary tables more than once in the same query. For
 example, the following doesn't work.
 mysql SELECT * FROM temporary_table, temporary_table AS t2;
 
 Does it mean that I can't use THE SAME temporary table twice in THE SAME
 query?

Yes.

 Or does it mean that I can't use more than 1 temporary table in the query at
 all?

No.




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Temporary Tables

[Alec . Cawley]

You said --

I am trying to establish temporary tables that are accessible via MyODBC.
I can create the table with data in it and query it from the MySQL command
line, but it never shows up through MyODBC.  Is there something magical
about temporary (memory-based) tables that prevents this?



I think you are misundersanding temorary tables. Temporary tables are not
necessarily in ram: unless you specify otherwise, they will be created on
disk. However, temporary tables are (a) only accessible via the connection
which created them and (b) deleted automatically when that connection is
closed. This means that you do not have to think og unique table names for
short-life scratch tables.

What I think you want is a Heap table, created by specifying TYPE=HEAP in
your create command - see http://www.mysql.com/doc/en/HEAP.html . Heap
tables have certain restrictions, and disappear when mysql is stopped, but
are visible to all users.

  Alec



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Temporary tables - MySQL 4.1 alpha

[Victoria Reznichenko]

Nils Valentin [EMAIL PROTECTED] wrote:
 I have some questions about temporary tables. I would appreciate any replies:
 
 I created a temporary table f.e. like this:
 
 mysql create temporary table temp SELECT * FROM relations;
 Query OK, 4 rows affected (2.35 sec)
 Records: 4  Duplicates: 0  Warnings: 0
 
 When I do
 
 mysql show create table temp;
 
 Then it will give me this:
 
 | temp  | CREATE TEMPORARY TABLE `temp` (
  `member_id` int(16) NOT NULL default '0',
  `company_id` int(16) NOT NULL default '0',
  `membership_id` int(16) NOT NULL default '0'
 ) TYPE=MyISAM CHARSET=latin1 |
 
 
 Now what confuses me is that 
 
 a) it says here TYPE=MyISAM

Because MyISAM is default table type

 b) I understood that only HEAP tables are stored in the memory.

Yes.

 I was assuming (until now) that HEAP tables are (the only one type of)  
 temporary tables.

Temporary tables may have type HEAP.

 I tried 
 
 CREATE TEMPORARY TABLE temp TYPE= InnoDB SELECT * FROM relations; 
 or
 CREATE TEMPORARY TABLE temp TYPE= HEAP SELECT * FROM relations;
 
 and they are all created as  in-memory tables no files are created.
 Can I use any table format (ISAM,MYISAM,BDB,InnoDB,HEAP) for temporary tables 

You can use MyISAM, ISAM, MERGE, HEAP, InnoDB.
Temporary table files are created in the temporary directory:
http://www.mysql.com/doc/en/Temporary_files.html

 If, so what would be the difference between a temporary table in general and a 
 HEAP table ?

Temporary tables are the per-connection tables.
HEAP tables are stored in memory tables. 


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Temporary tables - MySQL 4.1 alpha

[Nils Valentin]

WOW  Victoria, 

thats a good explanation. That makes many things clearer now.
Thank you very much.

However, when not having the  set the tmpdir variable were are the temporary 
tables stored ? (I searched the whole harddisc) I assume in this case that 
they must be stored in the memory, is that right ?

Best regards

Nils Valentin
Tokyo/Japan

2003 6 25  19:17Victoria Reznichenko :
 Nils Valentin [EMAIL PROTECTED] wrote:
  I have some questions about temporary tables. I would appreciate any
  replies:
 
  I created a temporary table f.e. like this:
 
  mysql create temporary table temp SELECT * FROM relations;
  Query OK, 4 rows affected (2.35 sec)
  Records: 4  Duplicates: 0  Warnings: 0
 
  When I do
 
  mysql show create table temp;
 
  Then it will give me this:
  | temp  | CREATE TEMPORARY TABLE `temp` (
 
   `member_id` int(16) NOT NULL default '0',
   `company_id` int(16) NOT NULL default '0',
   `membership_id` int(16) NOT NULL default '0'
  ) TYPE=MyISAM CHARSET=latin1 |
 
 
  Now what confuses me is that
 
  a) it says here TYPE=MyISAM

 Because MyISAM is default table type

  b) I understood that only HEAP tables are stored in the memory.

 Yes.

  I was assuming (until now) that HEAP tables are (the only one type of)
  temporary tables.

 Temporary tables may have type HEAP.

  I tried
 
  CREATE TEMPORARY TABLE temp TYPE= InnoDB SELECT * FROM relations;
  or
  CREATE TEMPORARY TABLE temp TYPE= HEAP SELECT * FROM relations;
 
  and they are all created as  in-memory tables no files are created.
  Can I use any table format (ISAM,MYISAM,BDB,InnoDB,HEAP) for temporary
  tables

 You can use MyISAM, ISAM, MERGE, HEAP, InnoDB.
 Temporary table files are created in the temporary directory:
   http://www.mysql.com/doc/en/Temporary_files.html

  If, so what would be the difference between a temporary table in general
  and a HEAP table ?

 Temporary tables are the per-connection tables.
 HEAP tables are stored in memory tables.


 --
 For technical support contracts, goto https://order.mysql.com/?ref=ensita
 This email is sponsored by Ensita.net http://www.ensita.net/
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
 /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
___/   www.mysql.com

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Temporary tables - MySQL 4.1 alpha

[Victoria Reznichenko]

Nils Valentin [EMAIL PROTECTED] wrote:
 WOW  Victoria, 
 
 thats a good explanation. That makes many things clearer now.
 Thank you very much.
 
 However, when not having the  set the tmpdir variable were are the temporary 
 tables stored ? (I searched the whole harddisc) I assume in this case that 
 they must be stored in the memory, is that right ?

Nope. Temporary tables are stored in memory only if table type is HEAP. All other 
temporary tables are stored on disk. By default temporary dir is /tmp or /usr/tmp. You 
can check it with
SHOW VARIABLES LIKE 'tmpdir';

Name of temporary table files looks like #sql2884_b_0.frm.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Temporary tables - MySQL 4.1 alpha

[Nils Valentin]

Hi Vitcoria,

Thank you for the response. No more questions about this, all systems clear 
now ;-).

Thank you so much.

Nils Valentin
Tokyo/Japan

2003 6 25  21:18Victoria Reznichenko :
 Nils Valentin [EMAIL PROTECTED] wrote:
  WOW  Victoria,
 
  thats a good explanation. That makes many things clearer now.
  Thank you very much.
 
  However, when not having the  set the tmpdir variable were are the
  temporary tables stored ? (I searched the whole harddisc) I assume in
  this case that they must be stored in the memory, is that right ?

 Nope. Temporary tables are stored in memory only if table type is HEAP. All
 other temporary tables are stored on disk. By default temporary dir is /tmp
 or /usr/tmp. You can check it with SHOW VARIABLES LIKE 'tmpdir';

 Name of temporary table files looks like #sql2884_b_0.frm.


 --
 For technical support contracts, goto https://order.mysql.com/?ref=ensita
 This email is sponsored by Ensita.net http://www.ensita.net/
__  ___ ___   __
   /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
  / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
 /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
___/   www.mysql.com

-- 
---
Valentin Nils
Internet Technology

 E-Mail: [EMAIL PROTECTED]
 URL: http://www.knowd.co.jp
 Personal URL: http://www.knowd.co.jp/staff/nils


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Temporary tables/ view in MySQL

[Dyego Souza do Carmo]

Respondendo,
quinta-feira, 13 de março de 2003, 05:05:24, Mensagem Original:

ASKA Hi.
ASKA Are temporary tabels or views supported in MySQL?
ASKA Some info required  please 

ASKA Thanks
ASKA Anis


ASKA -
ASKA Before posting, please check:
ASKAhttp://www.mysql.com/manual.php   (the manual)
ASKAhttp://lists.mysql.com/   (the list archive)

ASKA To request this thread, e-mail [EMAIL PROTECTED]
ASKA To unsubscribe, e-mail [EMAIL PROTECTED]
ASKA Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Temporary tables is supported :
MySQL Manual | 6.5.3 CREATE TABLE Syntax
http://www.mysql.com/doc/en/CREATE_TABLE.html

The views is not supported yet !
It is planned to implement views in MySQL Server around version 5.0







-
  ++  Dyego Souza do Carmo   ++   Dep. Desenvolvimento   
-
 E S C R I B A   I N F O R M A T I C A
-
The only stupid question is the unasked one (somewhere in Linux's HowTo)
Linux registred user : #230601
--ICQ   : 221602060
$ look into my eyes Phone : +55 041 296-2311  r.112
look: cannot open my eyes Fax   : +55 041 296-6640
-
   Reply: [EMAIL PROTECTED]



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: Temporary tables/ view in MySQL

[Victoria Reznichenko]

On Thursday 13 March 2003 10:05, Ahmed S K Anis wrote:

 Are temporary tabels

Yes. Take a look at:
http://www.mysql.com/doc/en/CREATE_TABLE.html

 or views supported in MySQL?

Nope.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary Tables

[Mamatha Balasubramanian]

Thanks!

Mamatha






From: Paul DuBois [EMAIL PROTECTED]
To: Mamatha Balasubramanian 
[EMAIL PROTECTED],[EMAIL PROTECTED]
Subject: Re: Temporary Tables
Date: Tue, 4 Mar 2003 17:31:36 -0600

At 23:19 + 3/4/03, Mamatha Balasubramanian wrote:
Thank you once again.

I have a web-interface that does search on a given text and I would have a 
script that creates a temporary table. So according to you, in my script, 
I just to need
create a temporary table and not have to worry about another client using 
the same web interface (and thereby using the same program). Can you 
please elaborate a little more on this?
Sure.

You are incorrect. :-)

That is, you're making an assumption that cannot necessarily be made.
If you can guarantee that the web script will establish a new connection,
and the connection will terminate when the script ends, you can indeed
do what you describe above.
But you *cannot* do that if you're running your script in an environment
that uses persistent connections that may be used by successive instances
of the script.  PHP persistent connections fall into this class, for
example.  Several requests might be served by the same instance of the
web server process, and you don't know that one request won't be getting
the connection used by a previous request.  In that case, the connection
won't have closed, and the TEMPORARY table won't have disappeared.
You can guard against this by issuing this query before creating the
TEMPORARY table:
DROP TABLE IF EXISTS tbl_name


Thanks,
Mamatha




From: Paul DuBois [EMAIL PROTECTED]
To: Mamatha Balasubramanian 
[EMAIL PROTECTED],[EMAIL PROTECTED]
Subject: Re: Temporary Tables
Date: Tue, 4 Mar 2003 17:06:30 -0600

At 23:00 + 3/4/03, Mamatha Balasubramanian wrote:
Hi,
I would like to know how MySQL handles multiple temporary tables?
1. Can multiple temporary tables be created at the same time?
2. If so, how does MySQL differentiate them - do we need to explicitly 
give them different names inorder to identify them or does MySQL provide 
a timestamp (or use some other means) to identify the tables?
You can create multiple temporary tables, but they must have different
names.
A TEMPORARY table can have the same name as a non-TEMPORARY table.
The non-TEMPORARY table is hidden to the client that creates the
TEMPORARY table as long as the TEMPORARY table exists.
A second TEMPORARY table with the same name cannot be created.
This is on a connection-specific basis.  Two clients each can create
a TEMPORARY table with the same name.  Only the table created by a given
client is visible to that client.
I use MySQL 4.0.7 on Red Hat.

Thanks,
Mamatha
_
The new MSN 8: advanced junk mail protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail

-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary Tables

[Paul DuBois]

At 23:00 + 3/4/03, Mamatha Balasubramanian wrote:
Hi,
I would like to know how MySQL handles multiple temporary tables?
1. Can multiple temporary tables be created at the same time?
2. If so, how does MySQL differentiate them - do we need to 
explicitly give them different names inorder to identify them or 
does MySQL provide a timestamp (or use some other means) to identify 
the tables?
You can create multiple temporary tables, but they must have different
names.
A TEMPORARY table can have the same name as a non-TEMPORARY table.
The non-TEMPORARY table is hidden to the client that creates the
TEMPORARY table as long as the TEMPORARY table exists.
A second TEMPORARY table with the same name cannot be created.
This is on a connection-specific basis.  Two clients each can create
a TEMPORARY table with the same name.  Only the table created by a given
client is visible to that client.
I use MySQL 4.0.7 on Red Hat.

Thanks,
Mamatha


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary Tables

[Daniel Kasak]

Mamatha Balasubramanian wrote:

Hi,
I would like to know how MySQL handles multiple temporary tables?
1. Can multiple temporary tables be created at the same time? 
Yes

2. If so, how does MySQL differentiate them - do we need to explicitly 
give them different names inorder to identify them or does MySQL 
provide a timestamp (or use some other means) to identify the tables?
I believe MySQL uses the client's connection ID (which is unique) to 
identify the temporary table. You don't have to use different names, no. 
Cool, eh?

--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com
-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary Tables

[Mamatha Balasubramanian]

Thank you once again.

I have a web-interface that does search on a given text and I would have a 
script that creates a temporary table. So according to you, in my script, I 
just to need
create a temporary table and not have to worry about another client using 
the same web interface (and thereby using the same program). Can you please 
elaborate a little more on this?

Thanks,
Mamatha





From: Paul DuBois [EMAIL PROTECTED]
To: Mamatha Balasubramanian 
[EMAIL PROTECTED],[EMAIL PROTECTED]
Subject: Re: Temporary Tables
Date: Tue, 4 Mar 2003 17:06:30 -0600

At 23:00 + 3/4/03, Mamatha Balasubramanian wrote:
Hi,
I would like to know how MySQL handles multiple temporary tables?
1. Can multiple temporary tables be created at the same time?
2. If so, how does MySQL differentiate them - do we need to explicitly 
give them different names inorder to identify them or does MySQL provide a 
timestamp (or use some other means) to identify the tables?
You can create multiple temporary tables, but they must have different
names.
A TEMPORARY table can have the same name as a non-TEMPORARY table.
The non-TEMPORARY table is hidden to the client that creates the
TEMPORARY table as long as the TEMPORARY table exists.
A second TEMPORARY table with the same name cannot be created.
This is on a connection-specific basis.  Two clients each can create
a TEMPORARY table with the same name.  Only the table created by a given
client is visible to that client.
I use MySQL 4.0.7 on Red Hat.

Thanks,
Mamatha


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail 
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
_
Help STOP SPAM with the new MSN 8 and get 2 months FREE*  
http://join.msn.com/?page=features/junkmail

-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary Tables

[Paul DuBois]

At 23:19 + 3/4/03, Mamatha Balasubramanian wrote:
Thank you once again.

I have a web-interface that does search on a given text and I would 
have a script that creates a temporary table. So according to you, 
in my script, I just to need
create a temporary table and not have to worry about another client 
using the same web interface (and thereby using the same program). 
Can you please elaborate a little more on this?
Sure.

You are incorrect. :-)

That is, you're making an assumption that cannot necessarily be made.
If you can guarantee that the web script will establish a new connection,
and the connection will terminate when the script ends, you can indeed
do what you describe above.
But you *cannot* do that if you're running your script in an environment
that uses persistent connections that may be used by successive instances
of the script.  PHP persistent connections fall into this class, for
example.  Several requests might be served by the same instance of the
web server process, and you don't know that one request won't be getting
the connection used by a previous request.  In that case, the connection
won't have closed, and the TEMPORARY table won't have disappeared.
You can guard against this by issuing this query before creating the
TEMPORARY table:
DROP TABLE IF EXISTS tbl_name


Thanks,
Mamatha




From: Paul DuBois [EMAIL PROTECTED]
To: Mamatha Balasubramanian 
[EMAIL PROTECTED],[EMAIL PROTECTED]
Subject: Re: Temporary Tables
Date: Tue, 4 Mar 2003 17:06:30 -0600

At 23:00 + 3/4/03, Mamatha Balasubramanian wrote:
Hi,
I would like to know how MySQL handles multiple temporary tables?
1. Can multiple temporary tables be created at the same time?
2. If so, how does MySQL differentiate them - do we need to 
explicitly give them different names inorder to identify them or 
does MySQL provide a timestamp (or use some other means) to 
identify the tables?
You can create multiple temporary tables, but they must have different
names.
A TEMPORARY table can have the same name as a non-TEMPORARY table.
The non-TEMPORARY table is hidden to the client that creates the
TEMPORARY table as long as the TEMPORARY table exists.
A second TEMPORARY table with the same name cannot be created.
This is on a connection-specific basis.  Two clients each can create
a TEMPORARY table with the same name.  Only the table created by a given
client is visible to that client.
I use MySQL 4.0.7 on Red Hat.

Thanks,
Mamatha


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary Tables

[William R. Mussatto]

 At 23:19 + 3/4/03, Mamatha Balasubramanian wrote:
Thank you once again.

I have a web-interface that does search on a given text and I would
 have a script that creates a temporary table. So according to you,  in
 my script, I just to need
create a temporary table and not have to worry about another client
 using the same web interface (and thereby using the same program).  Can
 you please elaborate a little more on this?

 Sure.

 You are incorrect. :-)

 That is, you're making an assumption that cannot necessarily be made. If
 you can guarantee that the web script will establish a new connection,
 and the connection will terminate when the script ends, you can indeed
 do what you describe above.

 But you *cannot* do that if you're running your script in an environment
 that uses persistent connections that may be used by successive
 instances of the script.  PHP persistent connections fall into this
 class, for example.  Several requests might be served by the same
 instance of the web server process, and you don't know that one request
 won't be getting the connection used by a previous request.  In that
 case, the connection won't have closed, and the TEMPORARY table won't
 have disappeared.

 You can guard against this by issuing this query before creating the
 TEMPORARY table:

 DROP TABLE IF EXISTS tbl_name



Thanks,
Mamatha
Probably even better to have the Drop Table at the end of the script that
used it.



From: Paul DuBois [EMAIL PROTECTED]
To: Mamatha Balasubramanian
[EMAIL PROTECTED],[EMAIL PROTECTED]
Subject: Re: Temporary Tables
Date: Tue, 4 Mar 2003 17:06:30 -0600

At 23:00 + 3/4/03, Mamatha Balasubramanian wrote:
Hi,
I would like to know how MySQL handles multiple temporary tables?

1. Can multiple temporary tables be created at the same time?
2. If so, how does MySQL differentiate them - do we need to
explicitly give them different names inorder to identify them or
 does MySQL provide a timestamp (or use some other means) to
identify the tables?

You can create multiple temporary tables, but they must have different
 names.

A TEMPORARY table can have the same name as a non-TEMPORARY table. The
 non-TEMPORARY table is hidden to the client that creates the TEMPORARY
 table as long as the TEMPORARY table exists.
A second TEMPORARY table with the same name cannot be created.

This is on a connection-specific basis.  Two clients each can create a
 TEMPORARY table with the same name.  Only the table created by a given
 client is visible to that client.


I use MySQL 4.0.7 on Red Hat.

Thanks,
Mamatha




William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary Tables

[KH Chiu]

Paul is right. I would like to add a small remark, it should better to drop 
the temp. tables before closing your script. This can free up resources. I 
had a painful experience that I had created hash temp. tables without 
dropping them. This lead to memory leak.

Regards,


--
Yours,
KH Chiu
CA Computer Consultants Ltd.
Tel: 3104 2070 Fax: 3010 0896
Email: [EMAIL PROTECTED]
Website: www.caconsultant.com

 At 23:19 + 3/4/03, Mamatha Balasubramanian wrote:
 Thank you once again.
 
 I have a web-interface that does search on a given text and I would 
 have a script that creates a temporary table. So according to you, 
 in my script, I just to need
 create a temporary table and not have to worry about another client 
 using the same web interface (and thereby using the same program). 
 Can you please elaborate a little more on this?
 
 Sure.
 
 You are incorrect. :-)
 
 That is, you're making an assumption that cannot necessarily be made.
 If you can guarantee that the web script will establish a new 
 connection, and the connection will terminate when the script ends,
  you can indeed do what you describe above.
 
 But you *cannot* do that if you're running your script in an environment
 that uses persistent connections that may be used by successive instances
 of the script.  PHP persistent connections fall into this class, for
 example.  Several requests might be served by the same instance of 
 the web server process, and you don't know that one request won't be 
 getting the connection used by a previous request.  In that case,
  the connection won't have closed, and the TEMPORARY table won't 
 have disappeared.
 
 You can guard against this by issuing this query before creating the
 TEMPORARY table:
 
 DROP TABLE IF EXISTS tbl_name
 
 
 Thanks,
 Mamatha
 
 
 
 
 
 From: Paul DuBois [EMAIL PROTECTED]
 To: Mamatha Balasubramanian 
 [EMAIL PROTECTED],[EMAIL PROTECTED]
 Subject: Re: Temporary Tables
 Date: Tue, 4 Mar 2003 17:06:30 -0600
 
 At 23:00 + 3/4/03, Mamatha Balasubramanian wrote:
 Hi,
 I would like to know how MySQL handles multiple temporary tables?
 
 1. Can multiple temporary tables be created at the same time?
 2. If so, how does MySQL differentiate them - do we need to 
 explicitly give them different names inorder to identify them or 
 does MySQL provide a timestamp (or use some other means) to 
 identify the tables?
 
 You can create multiple temporary tables, but they must have different
 names.
 
 A TEMPORARY table can have the same name as a non-TEMPORARY table.
 The non-TEMPORARY table is hidden to the client that creates the
 TEMPORARY table as long as the TEMPORARY table exists.
 A second TEMPORARY table with the same name cannot be created.
 
 This is on a connection-specific basis.  Two clients each can create
 a TEMPORARY table with the same name.  Only the table created by a given
 client is visible to that client.
 
 
 I use MySQL 4.0.7 on Red Hat.
 
 Thanks,
 Mamatha
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail mysql-unsubscribe-
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary Tables

[Paul DuBois]

At 15:56 -0800 3/4/03, William R. Mussatto wrote:
  At 23:19 + 3/4/03, Mamatha Balasubramanian wrote:
Thank you once again.

I have a web-interface that does search on a given text and I would
 have a script that creates a temporary table. So according to you,  in
 my script, I just to need
create a temporary table and not have to worry about another client
 using the same web interface (and thereby using the same program).  Can
 you please elaborate a little more on this?
 Sure.

 You are incorrect. :-)

 That is, you're making an assumption that cannot necessarily be made. If
 you can guarantee that the web script will establish a new connection,
 and the connection will terminate when the script ends, you can indeed
 do what you describe above.
 But you *cannot* do that if you're running your script in an environment
 that uses persistent connections that may be used by successive
 instances of the script.  PHP persistent connections fall into this
 class, for example.  Several requests might be served by the same
 instance of the web server process, and you don't know that one request
 won't be getting the connection used by a previous request.  In that
 case, the connection won't have closed, and the TEMPORARY table won't
 have disappeared.
 You can guard against this by issuing this query before creating the
 TEMPORARY table:
 DROP TABLE IF EXISTS tbl_name


Thanks,
Mamatha
Probably even better to have the Drop Table at the end of the script that
used it.
Not necessarily. If you create the table and then an error occurs,
you may not reach the point that drops the table.  Then the next
instance of the script, which expects the table not to exist when
it creates it, will fail.
-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary Tables

[Daniel Kasak]

Paul DuBois wrote:

That is, you're making an assumption that cannot necessarily be made.
If you can guarantee that the web script will establish a new connection,
and the connection will terminate when the script ends, you can indeed
do what you describe above.
But you *cannot* do that if you're running your script in an environment
that uses persistent connections that may be used by successive instances
of the script.  PHP persistent connections fall into this class, for
example.  Several requests might be served by the same instance of the
web server process, and you don't know that one request won't be getting
the connection used by a previous request.  In that case, the connection
won't have closed, and the TEMPORARY table won't have disappeared.
You can guard against this by issuing this query before creating the
TEMPORARY table:
DROP TABLE IF EXISTS tbl_name
In this case I would actually use unique table names.
If you have a web server that is set up to use persistant database 
connections and there is a chance that 2 clients may run the same script 
in the same connection, then dropping the tmp table isn't going to solve 
all of your problems. You can't have the 2nd instance of the script 
screwing with the 1st. If you have some form of unique identifier, then 
append it to the table name. Otherwise consider creating your own (and 
testing for it's existance before using it) and appending it to the 
table name.

--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com
-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary Tables

[Paul DuBois]

At 12:08 +1100 3/5/03, Daniel Kasak wrote:
Paul DuBois wrote:

That is, you're making an assumption that cannot necessarily be made.
If you can guarantee that the web script will establish a new connection,
and the connection will terminate when the script ends, you can indeed
do what you describe above.
But you *cannot* do that if you're running your script in an environment
that uses persistent connections that may be used by successive instances
of the script.  PHP persistent connections fall into this class, for
example.  Several requests might be served by the same instance of the
web server process, and you don't know that one request won't be getting
the connection used by a previous request.  In that case, the connection
won't have closed, and the TEMPORARY table won't have disappeared.
You can guard against this by issuing this query before creating the
TEMPORARY table:
DROP TABLE IF EXISTS tbl_name
In this case I would actually use unique table names.
If you have a web server that is set up to use persistant database 
connections and there is a chance that 2 clients may run the same 
script in the same connection, then dropping the tmp table isn't 
going to solve all of your problems. You can't have the 2nd instance 
of the script screwing with the 1st. If you have some form of unique 
identifier, then append it to the table name. Otherwise consider 
creating your own (and testing for it's existance before using it) 
and appending it to the table name.
Dropping the table before attempting to use it eliminates any need to
come up with a unique name.
Two clients *cannot* run the same script at the same time, *unless* they
are being executed by different web server processes.  In that case, they'll
be using different connections, and a temporary table created by one
connection cannot be seen by the other connection.
--
Daniel Kasak
IT Developer
* NUS Consulting Group*
Level 18, 168 Walker Street
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: www.nusconsulting.com


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary tables

[John Coder]

On Wed, 2002-09-25 at 10:10, Keith C. Ivey wrote:
 On 25 Sep 2002, at 0:57, John Coder wrote:
 
  So it's probably the fact that I didn't make the connection persistent
  that screwed me up sometimes I feel so stupid.
 
 I don't think that will help.  Read Paul's message again.  Even if 
 you make the connection persistent, you can't be sure you'll get the 
 same connection for the other page.
 
 In a Web application, I don't think temporary tables are useful if 
 you need them to be shared between pages.  One solution I've used is 
 to create an ordinary table with a unique name and then drop it when 
 you're finished with it.
 
 -- 
 Keith C. Ivey [EMAIL PROTECTED]
 Tobacco Documents Online
 http://tobaccodocuments.org

That's what I resorted to doing and yes I cahnged it to mysql_pconnect
and it didn't make a diff.

John


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary tables

[Keith C. Ivey]

On 25 Sep 2002, at 0:57, John Coder wrote:

 So it's probably the fact that I didn't make the connection
 persistent that screwed me up sometimes I feel so stupid.

I don't think that will help.  Read Paul's message again.  Even if 
you make the connection persistent, you can't be sure you'll get the 
same connection for the other page.

In a Web application, I don't think temporary tables are useful if  
you need them to be shared between pages.  One solution I've used is 
to create an ordinary table with a unique name and then drop it when 
you're finished with it.

[Filter fodder: SQL]

-- 
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary tables

[Paul DuBois]

At 10:12 -0400 9/25/02, Keith C. Ivey wrote:
On 25 Sep 2002, at 0:57, John Coder wrote:

  So it's probably the fact that I didn't make the connection
  persistent that screwed me up sometimes I feel so stupid.

I don't think that will help.  Read Paul's message again.  Even if
you make the connection persistent, you can't be sure you'll get the
same connection for the other page.

Just to confirm: Keith's correct, that's what I was saying.


In a Web application, I don't think temporary tables are useful if 
you need them to be shared between pages.  One solution I've used is
to create an ordinary table with a unique name and then drop it when
you're finished with it.

[Filter fodder: SQL]

--
Keith C. Ivey [EMAIL PROTECTED]
Tobacco Documents Online
http://tobaccodocuments.org


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary tables

[Paul DuBois]

hi all
I',m wondering if temporary tables stay around longer then the page you
use to create them if you create them with a php page. my thoughts were
they do but mine doesn't for some reason. I've made a temporary table to
query on a page that draws a pie chart out of the data in the temporary
table but when I try to access it from that page there's nothing there.

Tables created with CREATE TEMPORARY TABLE disappear when the current
connection closes.  They also are visible only within the connection that
created them.

So:

- If you create the table from one page using a non-persistent connection,
   the connection will close when the script ends and the table will
   disappear and be unavailable to other pages.
- If you create the table from one page using a persistent connection, the
   connection will not *necessarily* close when the script ends.  However,
   there is no guarantee that a following page will get that same connection,
   in which case the table will not be visible even if it happens to still
   exist.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary tables

[John Coder]

So it's probably the fact that I didn't make the connection persistent
that screwed me up 
sometimes I feel so stupid. hehehe

John
On Wed, 2002-09-25 at 00:34, Paul DuBois wrote:
 hi all
 I',m wondering if temporary tables stay around longer then the page you
 use to create them if you create them with a php page. my thoughts were
 they do but mine doesn't for some reason. I've made a temporary table to
 query on a page that draws a pie chart out of the data in the temporary
 table but when I try to access it from that page there's nothing there.
 
 Tables created with CREATE TEMPORARY TABLE disappear when the current
 connection closes.  They also are visible only within the connection that
 created them.
 
 So:
 
 - If you create the table from one page using a non-persistent connection,
the connection will close when the script ends and the table will
disappear and be unavailable to other pages.
 - If you create the table from one page using a persistent connection, the
connection will not *necessarily* close when the script ends.  However,
there is no guarantee that a following page will get that same connection,
in which case the table will not be visible even if it happens to still
exist.
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary tables in binary logs

[Paul DuBois]

At 16:33 -0400 8/14/02, Michael McClennen wrote:
I just noticed that creations and deletions of temporary tables are
recorded in the MySQL binary log.  My question is: during replication,
are these transmitted and replayed on the slave servers?  If so, could
someone explain to me why?  This would seem to be useless, since the
temporary tables are accessible only to the thread that created them.

What if you update a non-temporary table based on the contents of
a temporary table?


Also if so, is there a way to turn this off?

   -- Michael McClennen


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary Tables

[John Fishworld]

 You have written the following:

I understand the principles but WHEN should they be used or considered ??

Any help appreciated

 tia
 John

 sql,query




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary Tables

[DL Neil]

John,

  You have written the following:

 I understand the principles but WHEN should they be used or considered ??

 Any help appreciated

Something of an assumption here that we're all able to recollect an earlier post? Hope 
I'm on the right
wavelength.

I use temporary tables to get over the (current) lacks in MySQL's features:

- sub-selects that cannot be restated as JOINs, eg recursive use of complex GROUP 
functions

- as a substitute for VIEWs (which overlaps the above).

Also for speed/efficiencies:

- where a number of different queries will be performed on (essentially) the same 
subset of a table/join of
tables, and I want to either save repeated hits (hammering) against the db, or where 
I want/need to
force/guarantee (in as much as I can) that the data will be kept in core/RAM and thus 
available at a higher
speed.

Will be interested to hear/learn from, what others have come up with!
=dn



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: TEMPORARY TABLES

[John Lodge]

If you are using php, the function you want is mysql_data_seek($result,0) to
reset the result set.

John Lodge

-Original Message-
From: Walter D. Funk [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 07, 2002 2:04 PM
To: [EMAIL PROTECTED]
Subject: TEMPORARY TABLES


Ive just another question,
I couldnt get the info from the manual, I suppose thats because
I dont know other basics on Mysql ..  :-(

Ive been using the temporary table, and it works well, but, I can only
display the results set just once in my script, is that normal?
Ive been trying to paginate the result but when I try to show the second
page it seems to be empty.

thanks in advance


  if there are too many simultaneous connections which create several
  temporary tables is it posible to
  knock down the server?
 
  thanks in advance

 No, server will not be knocked down by many tables opened, provided
 your OS has sufficient number of file descriptors to handle.





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary tables, BDB tables, and Innobase questions

[Heikki Tuuri]

Hi Stephen,

1.  Can temporary tables be of type BDB?  Now before the question is asked
as to why I need transactional support on a temporary table, let me first
say that I really don't need it; this is just something we stumbled across
during development.  
If we create a temporary table of type BDB and attempt to rollback a
transaction, we get an error during rollback and the data is not rolled
back.  However, doing the exact same transaction on a BDB table that is not
specified as being temporary work as expected, ie, a rollback undoes
theupdates.

The idea of a temporary table is that it is a fast temporary storage
for rows. The user does not need recovery for it. Then one can save writing
the log records, and insertions are faster. Also rollback may be unavailable
for a temporary table, for a similar performance reason.

3.  I've read several posts regarding the Innobase tables.  Where can I
download the source from to start trying to use Innobase rather than BDB
tables? 

Innobase is projected to be released in MySQL-3.23.34. Monty will be back
from South America on Thursday or Friday, and the work to build the
release will continue.

By the way, Innobase now has a web page at www.innobase.fi (innobase.com
and innobase.net were already reserved).

Stephen L. Faustino
Senior Software Engineer
SecureLogix Corporation
Direct/Vmail (210)402-9669x949
mailto:[EMAIL PROTECTED]

Regards,

Heikki Tuuri
Innobase Oy
Helsinki, Finland


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Temporary tables, BDB tables, and Innobase questions

[Jeremy D. Zawodny]

On Mon, Feb 26, 2001 at 05:52:09PM -0600, Stephen Faustino wrote:
 
 3. I've read several posts regarding the Innobase tables.  Where can
 I download the source from to start trying to use Innobase rather
 than BDB tables?

They'll be in 3.23.24 when it is released.

Or you can pull them from the public bitkeeper repository.
Instructions were posted on this list a while ago. Should be in the
archives.

Jeremy
-- 
Jeremy D. Zawodny, [EMAIL PROTECTED]
Technical Yahoo - Yahoo Finance
Desk: (408) 328-7878Fax: (408) 530-5454
Cell: (408) 439-9951

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Temporary tables

[James R. Butzen]

How about

CREATE TEMPORARY TABLE tempbills SELECT billdesc, bpamount FROM bill_pay

Thanks,

James
NEBIS Tech.
http://www.nebis.com

-Original Message-
From: Matt Bailey [mailto:[EMAIL PROTECTED]]
Sent: Saturday, January 27, 2001 4:49 PM
To: [EMAIL PROTECTED]
Subject: Temporary tables


I'm using version 3.23.25, and am having trouble setting up a temporary
table. I have the following permanent table already set up:

bill_pay
(pbid int (8) default '0' not null auto_increment,
billdesc varchar(20),
bpamount int(8),
pbdate date,
budgetid int(8),
primary key (pbid), unique id (pbid))

I have tried to set up a temporary table:

CREATE TEMPORARY TABLE tempbills (
billdesc varchar(20),
bpamount int(8)
)

SELECT billdesc, bpamount FROM bill_pay

When I do this I get an error message saying "Duplicate column name
"billdesc"". And if I change the column names on the temp table it still
won't work - the table doesn't get created.

If someone could help me out I'd be grateful!

Matt

_
Get your FREE download of MSN Explorer at http://explorer.msn.com


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php