Temporary Tables with Triggers Problem
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
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
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
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
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
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
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
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
Temporary tables created on disk: 99%
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. I've confirmed this via the show global status like 'created%' command. This is in spite of having the following in my my.cnf: tmp_table_size = 2G max_heap_table_size = 2G I have confirmed these values are really being used via the show variables like command. So there's no typo in my my.cnf. Mysqltuner says Temporary table size is already large - reduce result set size but that is not a practical suggestion in that none of the programs using the databases on the server were written by me. I have databases for spamassassin bayesian rules, horde3 imp (webmail) and moodle on the system. I am not about to try to modify their code. I'm running mysql from debian stable (5.0.1). I've double and triple checked the documentation and it sure looks as if setting tmp_table_size and max_heap_table_size to be very large should eliminate this problem. But it doesn't seem to work. I just have to be missing something. But what? My complete my.cnf is below. PS: I have another server that is serving only drupal and it too is creating a high percentage of temp tables on disk (75%). I figure the same setting is missing and/or wrong on both servers. # # The MySQL database server configuration file. # # You can copy this to one of: # - /etc/mysql/my.cnf to set global options, # - ~/.my.cnf to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain # chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /srv/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 # # * Fine Tuning # # Commonly used optimization parameters # tmp_table_size = 2G max_heap_table_size = 2G key_buffer = 2G max_allowed_packet = 16M thread_stack = 128K thread_cache_size = 256 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP max_connections= 256 table_cache= 1024 thread_concurrency = 16 open_files_limit = 8192 # # * Query Cache Configuration # query_cache_limit = 16M query_cache_size= 512M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. #log = /var/log/mysql/mysql.log # # Error logging goes to syslog. This is a Debian improvement :) # # Here you can see queries with especially long duration # To see slow queries, use 'show processlist' log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * BerkeleyDB # # Using BerkeleyDB is now discouraged as its support will cease in 5.1.12. skip-bdb # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # You might want to disable InnoDB to shrink the mysqld process by circa 100MB. #skip-innodb innodb_buffer_pool_size = 2G # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI tinyca. # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M # # * NDB Cluster # # See /usr/share/doc/mysql
Re: Temporary tables created on disk: 99%
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?
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?
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?
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
temporary tables on disk?
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]
Re: temporary tables on disk?
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?
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
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]
temporary tables
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]
Re: temporary tables
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
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, 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
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
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]
Views can't refer to temporary tables
Greetings. Continuing on from my previous question, I've discovered that I *can't* create views which refer to temporary tables. Is there any plan to drop this requirement? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on views, stored procedures temporary tables
Hi all. I have a long and complicated chain of queries in MS Access that I want to port to using stored procedures views. What I'd like to do is something like: 1) stored procedures grabs original data set, and dumps into temporary tables 2) I open a view, which is in turn based on other views, which eventually points back to the temporary tables created in 1) Can I do that? ie if I create the views now, will they still work later, considering the temporary tables will be dropped and re-created later on? Do I have to drop the views as well, and re-create them after the stored procedure that creates and populates the temporary tables? Thanks :) Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary table ERROR 1109 (42S02) where are temporary tables kept?
I ran the following commands: USE snort; CREATE TEMPORARY TABLE sidtemp SELECT cid FROM event WHERE timestamp '2006-05-01'; ... SELECT count(*) from sidtemp; count(*) 7501376 DELETE FROM data WHERE data.cid = sidtemp.cid; ERROR 1109 (42S02): Unkown table 'sidtemp' in where clause SHOW tables; Does not include sidtemp in the list of tables in the snort database nor would I expect it to. Question: What database is the table sidtemp in? r/Raymond
Re: Temporary table ERROR 1109 (42S02) where are temporary tables kept?
Jacob, Raymond A Jr wrote: I ran the following commands: USE snort; CREATE TEMPORARY TABLE sidtemp SELECT cid FROM event WHERE timestamp '2006-05-01'; ... SELECT count(*) from sidtemp; count(*) 7501376 DELETE FROM data WHERE data.cid = sidtemp.cid; ERROR 1109 (42S02): Unkown table 'sidtemp' in where clause SHOW tables; Does not include sidtemp in the list of tables in the snort database nor would I expect it to. Question: What database is the table sidtemp in? r/Raymond You have the wrong syntax. You can't mention a table in the WHERE clause that wasn't in the FROM clause. Try DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid; or DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid; See the manual for details http://dev.mysql.com/doc/refman/4.1/en/delete.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Temporary table ERROR 1109 (42S02) where are temporary tables kept?
Thank you, I was definitely on the wrong track on this one. I annotated your commands to make sure that I understood what they were doing. Are my comments correct? --- You have the wrong syntax. You can't mention a table in the WHERE clause that wasn't in the FROM clause. Try DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid; /* deletes all records in data with cid equal cid in sidtemp but leaves sidtemp unchanged */ or DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid; /* deletes all records in data and sidtemp where cids are equal */ -- /* Will the USING clause work also? */ or /* looking that link below: */ DELETE data FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid or DELETE data FROM data USING data, sidtemp JOIN sidtemp ON data.cid = sidtemp.cid Is the above correct also? Thank you, raymond -- See the manual for details http://dev.mysql.com/doc/refman/4.1/en/delete.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary table ERROR 1109 (42S02) where are temporary tables kept?
Jacob, Raymond A Jr wrote: Thank you, I was definitely on the wrong track on this one. I annotated your commands to make sure that I understood what they were doing. Are my comments correct? --- You have the wrong syntax. You can't mention a table in the WHERE clause that wasn't in the FROM clause. Try DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid; /* deletes all records in data with cid equal cid in sidtemp but leaves sidtemp unchanged */ Correct. or DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid; /* deletes all records in data and sidtemp where cids are equal */ No, this only deletes from data. These 2 are supposed to be equivalent. Using this version of the syntax deletes rows from the tables named *before* the FROM. Tables used to determine the matching rows come after the FROM. The first version I gave uses an explicit JOIN, the second uses the implicit, comma join. I prefer explicit joins, but I included the implicit join because it seemed to be what you were trying. -- /* Will the USING clause work also? */ It should. or /* looking that link below: */ DELETE data FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid or DELETE data FROM data USING data, sidtemp JOIN sidtemp ON data.cid = sidtemp.cid Is the above correct also? Almost. In the USING form, the tables which should lose rows go after FROM, while the tables used to make the selection go after USING. Hence, the query would be DELETE FROM data USING data JOIN sidtemp ON data.cid = sidtemp.cid; or DELETE FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid; In general, the implicit join syntax (comma between tables with join condition(s) in the WHERE clause) seems easy to use, but frequently leads to trouble. The explicit join syntax (table JOIN table ON condition) is much clearer, which should help avoid mistakes. You should probably read the manual page describing JOIN syntax http://dev.mysql.com/doc/refman/4.1/en/join.html. Thank you, raymond Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Temporary table ERROR 1109 (42S02) where are temporary tables kept?
Thanks again, raymond -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 05, 2006 14:54 To: Jacob, Raymond A Jr Cc: mysql@lists.mysql.com Subject: Re: Temporary table ERROR 1109 (42S02) where are temporary tables kept? Jacob, Raymond A Jr wrote: Thank you, I was definitely on the wrong track on this one. I annotated your commands to make sure that I understood what they were doing. Are my comments correct? --- You have the wrong syntax. You can't mention a table in the WHERE clause that wasn't in the FROM clause. Try DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid; /* deletes all records in data with cid equal cid in sidtemp but leaves sidtemp unchanged */ Correct. or DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid; /* deletes all records in data and sidtemp where cids are equal */ No, this only deletes from data. These 2 are supposed to be equivalent. Using this version of the syntax deletes rows from the tables named *before* the FROM. Tables used to determine the matching rows come after the FROM. The first version I gave uses an explicit JOIN, the second uses the implicit, comma join. I prefer explicit joins, but I included the implicit join because it seemed to be what you were trying. -- /* Will the USING clause work also? */ It should. or /* looking that link below: */ DELETE data FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid or DELETE data FROM data USING data, sidtemp JOIN sidtemp ON data.cid = sidtemp.cid Is the above correct also? Almost. In the USING form, the tables which should lose rows go after FROM, while the tables used to make the selection go after USING. Hence, the query would be DELETE FROM data USING data JOIN sidtemp ON data.cid = sidtemp.cid; or DELETE FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid; In general, the implicit join syntax (comma between tables with join condition(s) in the WHERE clause) seems easy to use, but frequently leads to trouble. The explicit join syntax (table JOIN table ON condition) is much clearer, which should help avoid mistakes. You should probably read the manual page describing JOIN syntax http://dev.mysql.com/doc/refman/4.1/en/join.html. Thank you, raymond Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Support for temporary tables inside stored procedures?
Does MySQL have support for temporary tables inside stored procedures? _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Support for temporary tables inside stored procedures?
J A [EMAIL PROTECTED] wrote on 03/01/2006 11:08:10 AM: Does MySQL have support for temporary tables inside stored procedures? _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ Yes, you can create and destroy temporary tables from within stored procedures. Remember that all temporary tables and user variables are connection-specific. You have to maintain the connection between SPROC executions to have the results of one SPROC be available to the next. Drop a connection and your user variables and temporary tables created by that connection go away (garbage collected by the server). The obverse situation is also true. Using a pooled connection may result in object carry-over from one process to another (because returning the connection to the pool does may not actually drop the connection). You should always destroy any temporary object when you are through using it and always initialize your variables before usage. That way you don't accidentally hand old data off to following threads and you don't inherit previous threads state values (unless you really want them). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: SELECT/JOIN performance on temporary tables depends on timing of index creation
Gleb: Aha. OK, we tried using OPTIMIZE instead of ANALYZE, and that does indeed update the Cardinality of the index, the way you would expect. Thanks very much for figuring this out! I really appreciate the help. Thanks again! Allan Gleb Paharenko [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hello. I've checked this and found that ANALYZE table really doesn't work, but OPTIMIZE table made its work. In case it won't help you send to list complete definitions of you tables and queries. Allan Miller wrote: Hi Gleb, Thanks for the quick response. Unfortunately, ANALYZE TABLE does not appear to affect the Cardinality field of a temporary table (it is still NULL, even with rows added). Only by creating the index itself after the insert seems to make the query faster. I also tried FORCE INDEX and it did not make things any faster. Do you have any idea why creating the index AFTER putting the data in the table affects the performance so dramatically? Allan -- 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: SELECT/JOIN performance on temporary tables depends on timing of index creation
Hello. I've checked this and found that ANALYZE table really doesn't work, but OPTIMIZE table made its work. In case it won't help you send to list complete definitions of you tables and queries. Allan Miller wrote: Hi Gleb, Thanks for the quick response. Unfortunately, ANALYZE TABLE does not appear to affect the Cardinality field of a temporary table (it is still NULL, even with rows added). Only by creating the index itself after the insert seems to make the query faster. I also tried FORCE INDEX and it did not make things any faster. Do you have any idea why creating the index AFTER putting the data in the table affects the performance so dramatically? Allan -- 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]
SELECT/JOIN performance on temporary tables depends on timing of index creation
In MySQL 4.1.11, if you create two temporary tables with non-unique indices as part of the CREATE TEMPORARY TABLE statement, then insert several hundred (or thousand) rows, a subsequent SELECT using a JOIN between the two tables is substantially slower than if you create the indices after inserting the rows into the temporary tables. When I run a SHOW INDEX for these tables, the only difference is the Cardinality field, where it is NULL if the index was created prior to any inserts and is a number if the index was created after the inserts. Why is it that adding an index before or after the addition of data makes a difference on SELECT performance? Allan Miller -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT/JOIN performance on temporary tables depends on timing of index creation
Hello. Why is it that adding an index before or after the addition of data makes a difference on SELECT performance? Check whether the problem still exists if you run ANALYZE table after inserts in case you have already created indexes. FORCE_INDEX in the SELECT clause should help as well. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html Allan Miller wrote: In MySQL 4.1.11, if you create two temporary tables with non-unique indices as part of the CREATE TEMPORARY TABLE statement, then insert several hundred (or thousand) rows, a subsequent SELECT using a JOIN between the two tables is substantially slower than if you create the indices after inserting the rows into the temporary tables. When I run a SHOW INDEX for these tables, the only difference is the Cardinality field, where it is NULL if the index was created prior to any inserts and is a number if the index was created after the inserts. Why is it that adding an index before or after the addition of data makes a difference on SELECT performance? Allan Miller -- 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: SELECT/JOIN performance on temporary tables depends on timing of index creation
Hi Gleb, Thanks for the quick response. Unfortunately, ANALYZE TABLE does not appear to affect the Cardinality field of a temporary table (it is still NULL, even with rows added). Only by creating the index itself after the insert seems to make the query faster. I also tried FORCE INDEX and it did not make things any faster. Do you have any idea why creating the index AFTER putting the data in the table affects the performance so dramatically? Allan Gleb Paharenko [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hello. Why is it that adding an index before or after the addition of data makes a difference on SELECT performance? Check whether the problem still exists if you run ANALYZE table after inserts in case you have already created indexes. FORCE_INDEX in the SELECT clause should help as well. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/how-to-avoid-table-scan.html Allan Miller wrote: In MySQL 4.1.11, if you create two temporary tables with non-unique indices as part of the CREATE TEMPORARY TABLE statement, then insert several hundred (or thousand) rows, a subsequent SELECT using a JOIN between the two tables is substantially slower than if you create the indices after inserting the rows into the temporary tables. When I run a SHOW INDEX for these tables, the only difference is the Cardinality field, where it is NULL if the index was created prior to any inserts and is a number if the index was created after the inserts. Why is it that adding an index before or after the addition of data makes a difference on SELECT performance? Allan Miller -- 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]
temporary tables
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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary tables
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
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]
persistent temporary tables
i have come to realise that temporary tables are only persistent for 30 secs, is there a way to extend that ? My applications is attempting to store data before and entry is made and then insert the data from the temporary table after the entry is inserted then drop the temp table. This should work in theory but even with peristent php connections the tables are lost. Any ideas ? I have had to create heap tables with random named tables for each user, then drop them as i would with temp tables, is this a good idea ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: persistent temporary tables
At 08:30 AM 1/27/2005, electroteque wrote:
i have come to realise that temporary tables are only persistent for 30
secs, is there a way to extend that ? My applications is attempting to
store data before and entry is made and then insert the data from the
temporary table after the entry is inserted then drop the temp table. This
should work in theory but even with peristent php connections the tables
are lost. Any ideas ? I have had to create heap tables with random named
tables for each user, then drop them as i would with temp tables, is this
a good idea ?
Double check all of your code. I had a similar problem in the past only to
find that at least one call to the database in my script was NOT
persistent, so it would drop the temporary database. After making ALL
connections in my script persistent, I didn't have the problem any more.
Also:
1. You cannot refer to a TEMPORARY table more than once in the same query
AND in different SELECT queries combined by UNION operator.
2.
You cannot refer to a TEMPORARY table more than once in the same query. For
example, the following does not work:
mysql SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'
quotes from http://dev.mysql.com/doc/mysql/en/temporary-table-problems.html
There are probably other things to check too. Maybe someone more
experienced will reply here. To test the 30 second problem, try
this. Create a simple script that creates a temporary table, populates it
with a couple of things, starts a counter that will count for more than 30
seconds, then read from the table and output the results. Don't have the
script doing anything else. This is just to see if your system really does
kill the table before 30 seconds. Test it with a counter less than 30
seconds also to make sure that it works for you.
I have two functions that I use to connect to my databases. Not sure where
I got them. Probably off of this list or the php list which is what I
normally program in. Yes, the functions are php code:
function get_mysql_query($query){
GLOBAL $hostName,$userName,$password,$databaseName;
$connection = @mysql_connect($hostName,$userName,$password);
while($connection == FALSE):
$connection = @mysql_connect($hostName,$userName,$password);
endwhile;
$db = @mysql_select_db($databaseName, $connection);
$result = @mysql($databaseName,$query);
$connection_close = @mysql_close($connection);
return $result;
}
function get_mysql_query_stay_open($query){
GLOBAL $hostName,$userName,$password,$databaseName;
$connection = @mysql_connect($hostName,$userName,$password);
while($connection == FALSE):
$connection = @mysql_connect($hostName,$userName,$password);
endwhile;
$db = @mysql_select_db($databaseName, $connection);
$result = @mysql($databaseName,$query);
return $result;
}
Normally when I use the second one in a script, I do NOT use the first one
without watching carefully what I am doing. If it is a small script, then
I will just use the get_mysql_query_stay_open query if I need to leave the
connection open and not even worry about closing the connection. I only
worry about it on larger scripts where it might eat up to many resources if
I leave it open when it is not needed to be left open.
Hope this might help in some small way.
Steve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
joins on temporary tables, and grant statement limitations
MySQL Manual A.7.3 TEMPORARY TABLE You cannot refer to a TEMPORARY table more than once in the same query. Anything in the works to fix this pretty major limitation? Also, any chance of being able to specify wildcards in table names in GRANT statements any time soon? E.g. GRANT ALL ON test.'temp\%' TO user Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary tables rights
Time ago I submit a post about temp tables and rights privileges and it's in my head still. There is an user privilege to create temporary table (create_tmp_table_priv) but when the owner of the table need drop this table can't do (if have drop priv can, but it's a bomb time). Someone have an idea how to deal with this? Alejandro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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). 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? All innodb tables, MySQL 4.1.7 for both master and slaves. Thanks. Mike -- 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
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]
temporary tables and replication
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]
Re: temporary tables and replication
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: replication of temporary tables
Tom Cunningham [EMAIL PROTECTED] wrote: In short: what is the standard MySQL handling of temporary tables, *especially* when you've got a slave filter on to only replicate certain tables? These options apply to the temporary tables too. My replication stopped over the weekend, because a query was executed which created temporary tables, then used that table to update one of the non-temporary tables which I was replicating. Do I need to add all possible temporary tables to my slave-side replication filter? (i.e., replicate-do-table=temp1, temp2, ...) (even when I did so, it didn't seem to work, but I'll have another go). For replicate-do-table option you should also specify database name. Look at replicate-wild-do-table option where you can use wildcard characters in the database and table names. For example: replicate-wild-do-table=db%.temp% -- 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]
replication of temporary tables
Hi, In short: what is the standard MySQL handling of temporary tables, *especially* when you've got a slave filter on to only replicate certain tables? My replication stopped over the weekend, because a query was executed which created temporary tables, then used that table to update one of the non-temporary tables which I was replicating. Do I need to add all possible temporary tables to my slave-side replication filter? (i.e., replicate-do-table=temp1, temp2, ...) (even when I did so, it didn't seem to work, but I'll have another go). Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Re: Temporary tables in mySQL
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]
temporary tables
Which of these three joins are using temporary table? # 1 Inner Join # 2 Left Join #3 Outer Join Thanks
Re: temporary tables
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]
Temporary tables rights
I'll be fighting with rights over tmp tables time ago, basically I want give full temporary tables management but notforotherall tables, including select right. The only way to do that work was insert in tables_priv for each user a "user/tmp_table_name" record granting full privileges. There is a problem with this because 'TMP%' as table_name doesn't work in tables_priv, you must insert many rows as many different temporary tables names you think use. The question is: There is a way to improve tmp rights management? I think this is a weak side of mysql. Thnks! Alejandro _ IncrediMail - El Email ha evolucionado finalmente - Haga clic aquí
Re: Temporary tables rights
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
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]
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, DELETEON db1.table1 TO user1; GRANT SELECT,INSERT, UPDATE, DELETEON 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: SHOWing temporary tables
H?ctor Villafuerte D. [EMAIL PROTECTED] wrote: Hi all, How can I see the temporary tables in a database? You can't. Is there something like SHOW TEMPORARY 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]
SHOWing temporary tables
Hi all, How can I see the temporary tables in a database? Is there something like SHOW TEMPORARY TABLES? Thanks, Hector -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: temporary tables
[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]
temporary tables
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. Thanks mhp
GRANT CREATE TEMPORARY TABLES
Ok. Someone else was talking about this earlier in the list, but I didn't see the answer that I am looking for. I have just setup a new RedHat 9.0 server with MySQL 3.23.58. My program that worked on an older version of MySQL doesn't work on this one. The problem is that the temporary tables are not being created. How can I turn on the GRANT function so that each of the users that have databases can create temporary tables in their own databases ONLY. Can this be done with a GLOBAL type of grant? If so, what would be the Grant statement to turn this on? Or will I have to define this each time a user us setup? If so, what would be the Grant statement to turn this on this way? The docs did show a little about this, but not this specifically (that I found) and since I am new to this type of grant, I don't want to screw things up. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GRANT CREATE TEMPORARY TABLES
Thank You. I found out that I had to run mysql_fix_privilege_tables program. Thanks Steve At 08:07 AM 10/28/2003, you wrote: Hi there, I guess you are going to have to grant this at the database level, unfortunately for you. These privileges are stored in the mysql.db and mysql.host tables, or can be manipulated using something like the following, which is a little easier to try out: GRANT CREATE TEMPORARY TABLES ON my_db.* TO 'user' IDENTIFIED BY 'pass'; I'm doing this and it works fine :) I guess if you have multiple databases and users you could make a INSERT INTO ... SELECT statement to manipulate the mysql tables directly and then do a flush priveledges. Hope that helps, Mike -Original Message- From: Steve Buehler [mailto:[EMAIL PROTECTED] Sent: 28 October 2003 13:51 To: mysql Subject: GRANT CREATE TEMPORARY TABLES Ok. Someone else was talking about this earlier in the list, but I didn't see the answer that I am looking for. I have just setup a new RedHat 9.0 server with MySQL 3.23.58. My program that worked on an older version of MySQL doesn't work on this one. The problem is that the temporary tables are not being created. How can I turn on the GRANT function so that each of the users that have databases can create temporary tables in their own databases ONLY. Can this be done with a GLOBAL type of grant? If so, what would be the Grant statement to turn this on? Or will I have to define this each time a user us setup? If so, what would be the Grant statement to turn this on this way? The docs did show a little about this, but not this specifically (that I found) and since I am new to this type of grant, I don't want to screw things up. Thanks Steve -- 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: Detect temporary tables
Jeff McKeon [EMAIL PROTECTED] wrote: I know I can issue show tables to give me a list of tables from the current database, how can I do the same thing with temporary tables? That is, is there a command to list the current temporary tables? You can't. -- 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: Detect temporary tables
I know I can issue show tables to give me a list of tables from the current database, how can I do the same thing with temporary tables? That is, is there a command to list the current temporary tables? You can't. RATS!!! Jeff -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Thursday, October 23, 2003 4:38 AM To: [EMAIL PROTECTED] Subject: Re: Detect temporary tables Jeff McKeon [EMAIL PROTECTED] wrote: I know I can issue show tables to give me a list of tables from the current database, how can I do the same thing with temporary tables? That is, is there a command to list the current temporary tables? You can't. -- 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Detect temporary tables
I know I can issue show tables to give me a list of tables from the current database, how can I do the same thing with temporary tables? That is, is there a command to list the current temporary tables? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary tables
Due to complex query needs and the lack of subqueries in the current production version of MySQL i have to use temporary tables, but there is almost no information about the behavior of them in the manual.pdf documentation file of mySQL database. Does any one know where to find more information about temporary tables?. The questions i would like to answer are: 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? Regards, FB
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
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]
Temporary tables
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? Or does it mean that I can't use more than 1 temporary table in the query at all? Thanks in advance, Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary tables
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]
Temporary Tables
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 am running MySQL 4 on RedHat 9 with the latest MyODBC. The create syntax works as advertised: create temporary table X select * from Y where Z = 'abc'; Incidentally, MyODBC works fine with disk-based tables. I just set up an ODBC DSN and can access all the tables via Excel, MS Word, MS Access, etc. except the temporary tables. TIA. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary Tables
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 am running MySQL 4 on RedHat 9 with the latest MyODBC. The create syntax works as advertised: create temporary table X select * from Y where Z = 'abc'; Incidentally, MyODBC works fine with disk-based tables. I just set up an ODBC DSN and can access all the tables via Excel, MS Word, MS Access, etc. except the temporary tables. TIA. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Temporary Tables
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]
Alpha 4.1 - Temporary Tables Question
I'm running as normal user (not root or database owner). This user has create temporary table priv. show tables partial: GRANT CREATE TEMPORARY TABLES ON `foobar0`.* TO 'foo'@'localhost' - (foobar0 and foo are masks for the database name and user, respectively) I created two tmp tables with intermdiate results. I then try to join the 2 tmp tables to a third tmp table and get the following error: ERROR 1142: select command denied to user: '[EMAIL PROTECTED]' for table 'foobar' (foo and foobar are masks for the user and table, respectively). How can I grant a select on a tmp table to the user that created it? I shouldn't have to, should I? I must be missing something obvious. Did I screw up the GRANT CREATE TEMPORARY TABLES statement? Thanks for your time. Pete -- This email may contain CONFIDENTIAL or PRIVILEGED information and is a private communication between the intended addressee and PharMetrics, Inc. If you have received this email in error, reading, copying, using, or disclosing its contents to others is prohibited. Please notify us of the delivery error by replying to this message, and then delete it from your system. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alpha 4.1-Temporary Tables-Alter/Update
Good Morning, I'm running Alpha 4.1 on Linux. I'm new to MySql but familliar with other databases. I set up a user with Create Temporary Tables permissions. That user can create temp tables but can not alter or update the table. That user is getting Update command denied to user... for the temp table. They also get this error when an alter table is executed. Doesn't the user have ownership of the temp table they create? If they do then why can't they alter/update it? Am I missing something? Pete -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alpha 4.1-Temporary Tables-Alter/Update
Peter Gorelczenko [EMAIL PROTECTED] wrote: Good Morning, I'm running Alpha 4.1 on Linux. I'm new to MySql but familliar with other databases. I set up a user with Create Temporary Tables permissions. That user can create temp tables but can not alter or update the table. That user is getting Update command denied to user... for the temp table. They also get this error when an alter table is executed. Doesn't the user have ownership of the temp table they create? If they do then why can't they alter/update it? Am I missing something? CREATE TEMPORARY TABLES privilege allows only create temporary tables. -- 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: Alpha 4.1-Temporary Tables-Alter/Update
So, CREATE TEMPORARY TABLES only allows the initial create and insert into a temporary table. Any other privileges like alter, update, delete, and possibly drop are reserved for real tables and database grants. Don't get me wrong, I'm glad we have the create temp table grant/option. We can accomplish what we are trying to do by creating a series of temp tables and join them together. They are relatively small tables. It just would have been slick via the update and Alter syntax. Thanks for the time. Pete On Tuesday 22 July 2003 10:56, Egor Egorov wrote: Peter Gorelczenko [EMAIL PROTECTED] wrote: Good Morning, I'm running Alpha 4.1 on Linux. I'm new to MySql but familliar with other databases. I set up a user with Create Temporary Tables permissions. That user can create temp tables but can not alter or update the table. That user is getting Update command denied to user... for the temp table. They also get this error when an alter table is executed. Doesn't the user have ownership of the temp table they create? If they do then why can't they alter/update it? Am I missing something? CREATE TEMPORARY TABLES privilege allows only create temporary tables. -- This email may contain CONFIDENTIAL or PRIVILEGED information and is a private communication between the intended addressee and PharMetrics, Inc. If you have received this email in error, reading, copying, using, or disclosing its contents to others is prohibited. Please notify us of the delivery error by replying to this message, and then delete it from your system. Thank you. -- 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 [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
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
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
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]
Temporary tables - MySQL 4.1 alpha
Hi MySQL Fans ;-), 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 b) I understood that only HEAP tables are stored in the memory. I was assuming (until now) that HEAP tables are (the only one type of) temporary tables. 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 ? If, so what would be the difference between a temporary table in general and a HEAP table ? -- --- 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]
View all temporary tables in a database
HI, I create temporary tables using the query CREATE TEMPORARY TABLE .. How do i list all the temporary tables in the database SHOW TABLES STATUS does not show the temporary tables. Thanks Anis - 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
Temporary tables/ view in MySQL
Hi. Are temporary tabels or views supported in MySQL? Some info required please Thanks Anis - 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
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
How to view all the temporary tables ?
Hi, I am able to create temporary tables in MYSQL,using CREATE TEMPORARY TABLE I am using JDBC for this. Just like show tables will give all the table names in that databases, is there any query to view all current Temporary table names? Rgds Anis - 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
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
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
Temporary Tables
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? 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
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
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
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
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
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
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
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
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
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
