Privileges For TEMPORARY TABLES
I have been developing an application on Windows with MySQL 3.23,
making use of temporary tables.Now when I try to port the application to a Unix box runningalso MySQL 3.23, I keep running into "access denied" errors with
the queries which employ temporary tables. This only happens
with CREATE TEMPORARY TABLE type queries, all other queries work
fine.I have given my user all privileges (CREATE, ALTER, INDEX, DELETE)
to the database yet still it can not create temporary tables.
Is there a special command for allowing temporary tables on MySQL 3.x ?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Privileges On Temporary Tables
at the momente I'm having the following problem: I have a DB user that should not be able to access all the tables in the database. Now I also want to allow him to create temporary tables which already works fine. The problem is that I can't SELECT from the table without granting the select privilege to my user on the whole database. What's the best way to circumvent this problem?
View Replies !
View Related
Temporary Tables
since a couple of days I am struggling with temporary tables. there is not much info on this subject in manual.I have the following situation: two 'perl' routines sub 'a{}' and sub 'b{}'. Sub 'a' creates temporary table and is inserting data into it. Sub 'b' was supposed to get data from temporary table created in sub 'a{}' but all I get is error message that table does not exists.is it possible to dump table between routines not cutting off session with db? There can be found in manual that temporary tables exists till session with database will not be terminated. What can be a possible reasons of my problems with access to temporary table?I am neither perl guru nor mysql. I am using BugTrack framework to add some customizations and can't get over this temporary tables.
View Replies !
View Related
Temporary Tables With AJAX
I have been busting my head with a problem, that goes like this: 1. with a first ajax call to the server(apache 2.0 + php), I create a temporary table like this: create temporary table peter select * from some_table limit 0,0; That creates an empty temporary table with the same structure as some_table. I use mysql 4.023. I use php call mysql_connect to connect to the mysql database. 2. I create a second ajax call to the server using the same mysql_connect call with the third parameter new_link set to false and make this query: select count(*) as 'count' from peter; This time I receive an error that this table does not exist. Any ideas on where a problem could lie?
View Replies !
View Related
Temporary Tables In MySQL (using Php)
I have the following problem: I have a switch with case 0, 1 and default. in case 0 I create a temporary table where I insert data then. When I get to the case 1 in my switch, I want to make a select(*) on this data in the tmp table, afterwards I delete all data in the table and insert the select from before... But when I do this I get always the tmp table does not exists. But when I do a select on it it works.. Any knowledge about, why it doesn't work? To descibe the thing a little better: ....
View Replies !
View Related
Temporary Tables Not Disapearing
I have a 5.0.22 server. I've been useing it for +- a week now and I see more and more temp(I think) tables being created. The names of these tables are something like A000000c05280lucy and B0000b0d03924shaldine. Is this temp tables and if they are why are they not deleting? I also see that my indexes in my tables are now table, my old tables are still the same. eq I have a index in one of my tables called WKey. I now have a new table called WKey and under rows it says "Index" and fields it gives "3".
View Replies !
View Related
Indexing Temporary Tables
In my application, I create a temporary table like so... CREATE TEMPORARY TABLE hits SELECT ... This is nice because I don't have to explicitly declare the column names or types. However, I would like to index at least one of the columns to speed things up a little. I have only been able to find two ways to do so (1) explicitly provide column definitions and indexes before the select (this worked, at least when I specified a single column primary key; and (2) to issue an ALTER TABLE statement after the creation and population of the table. I was curious what the implications of (2) are: I read in the documentation that for *non-unique* keys its actually faster to disable the keys before insertion. However, in my case the main key of interest will be a unique key (I may add other non-unique keys later). In the case of a unique key, will adding the indexes later via an ALTER TABLE statement be faster, slower, or approximately the same as adding it to the table as a part of the CREATE TEMPORARY TABLE statement? Am I right in assuming that for non-unique keys it is actually faster?
View Replies !
View Related
Alternative To Temporary Tables
I have a large query with two subquery within it that access the same table bu t have diff where clause. So i created a table first that runs this query once and has the result. Then use this table in the two subqueries to get the result. In this way i need to run the query just once and the performance is greatly improved. I drop the table immediately after the large query is run. But my problem is this table is visible across all db connections. So if someone double clicks on the grpah that triggers these queries, this table will be visible and that might create problems since the two clicks will trigger the create table again. I tried using temp tables since they are local to one connetcion, but they cannot be used more than once in a query. SO I could not use them.
View Replies !
View Related
Connections, Temporary Tables And PHP
An application is built using 2 or more scripts (on different files) that connect to mysql using the mysql_pconnect function. If I create a temporary table in the first script and then navigate to the second (for example, by submiting a form), would the temporary table persist between the first and second script? Can I access the temporary table from the second script or it would be dropped?
View Replies !
View Related
List Temporary Tables
Maybe this question was already answered, but I didn't found it vis "Search".. :) To show all tables except temporary I can use "SHOW TABLES"... But how do I show all tables or at least temporary tables? I can't believe that there's no such function!
View Replies !
View Related
Join Optimization & Temporary Tables
--B_3141046513_16095154 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable I=B9m wondering what (in general) causes a join to use temporary tables and filesorts... More specifically, what can I tune to avoid it? I can give more info about a specific situation if anyone=B9s interested. All the docs say is that you are likely to get a =B3Using temporary=B2 if you sort on a different column set than the =B3group by=B2, but I=B9m not doing that...something else must be triggering it.
View Replies !
View Related
PhpMyAdmin Export With Temporary Tables
I have a multi-statement sql script that creates two temporary tables and then runs a query on them. The query runs fine.However, I want to use phpMyAdmin to export the data to .csv format.The export gui only runs the select statement, not the create temporary table statements,so when it tries to select from the temporary tables it does not find them. What do I have to do to get this functionality to work (besides using persistant tables).
View Replies !
View Related
Setting Table Privileges (tables_priv) On 80 Tables?
I have been working with MySQL permissions as part of a MythTV project. I want to restrict access to some of the tables in the database. I created a user with: CREATE USER 'unpriv'@'192.168.1.102' IDENTIFIED BY 'easy2guess'; Then added privileges for this user: GRANT ALL ON mythconverg.* TO 'unpriv'@'192.168.1.102' IDENTIFIED BY 'easy2guess'; But since the table_priv privileges will not be read if the db privileges are already yes I had to revoke some of them. REVOKE DROP, ALTER, DELETE, UPDATE, INSERT ON mythconverg.* FROM 'unpriv'@'192.168.1.102'; So now I want to grant ALTER, DELETE, UPDATE, INSERT privileges on about 75 of the 80 tables. What is the easiest way to achieve this?
View Replies !
View Related
Using Temporary
This first query is like so... select m.*, mi.age from members m, members_addtl_info mi where m.nick like '%anynickname%' AND m.nick = mi.nick order by nick desc, account_login_last desc limit 0, 21 +-------+--------+---------------+---------+---------+---------+--------+--- ------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+---------+---------+---------+--------+--- ------------------------------+ | mi | ALL | PRIMARY | NULL | NULL | NULL | 740053 | Using temporary; Using filesort | | m | eq_ref | PRIMARY | PRIMARY | 15 | mi.nick | 1 | where used | +-------+--------+---------------+---------+---------+---------+--------+--- ------------------------------+ 2nd query seems alot fasteer..... select m.* from members m where m.nick like '%bigsh523%' order by nick desc, account_login_last desc limit 0, 21 +-------+------+---------------+------+---------+------+--------+----------- -----------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+------+---------------+------+---------+------+--------+----------- -----------------+ | m | ALL | NULL | NULL | NULL | NULL | 736939 | where used; Using filesort | +-------+------+---------------+------+---------+------+--------+-----------
View Replies !
View Related
Using Temporary Table
I have added a search function to a mysql database that creates alot of results. My plan was to use a temporary table to store the results and then display (using php and html) them 10 per page. This works great on page one, but then when I move on to the next page I get an error because the temporary table no longer exists. I pagiate by first getting a count of the resultset and then passing on a variable for the limit.
View Replies !
View Related
Create Temporary Table
When I try and run the following query create temporary table russ1 as select rcust.* from rcust; and I receive the error message: Error Code : 1044 Access denied for user: 'russ@192.168.1.79' to database 'customer' (0 ms taken) I can run the query from the command line of the server and the query works correctly. I have run the query GRANT ALL PRIVILEGES ON customer.* TO russ@192.168.1.79 In addition, I flushed the privileges, and I stopped and restarted the database.
View Replies !
View Related
Grant Select Temporary
I'm trying to use a grant command on a redhat 9 v:3.23.56-1.9 :- " grant create temporary tables on iptables.* to iptables_user@localhost identified by 'xxx' " And I get this syntax error, to which I cannot find the answer. ERROR 1064: You have an error in your SQL syntax near 'temporary tables on iptables.* to iptables_user@localhost identified by 'xxx' at line 1
View Replies !
View Related
Temporary Table Is Full
During executing SELECT query, MySQL creates disk temporary table. After the tabe exceeds 4GB I get table is full error. How can I change settings to remove 4 GB bareer from disk tmp tables? It is easy to set in phisical MyISAM table by increase MAX_ROWS.
View Replies !
View Related
Temporary Table Inserts?
I was wondering, we all know that temporary tables are system bottleneck, I was wondering if the slowness of inserts for temporary table is related to the number of fields that the temporary table have? What I mean is, would a temporary table with 2 fields run INSERT statements faster than a temporary table with 5 fields?
View Replies !
View Related
Temporary Table Error
I was surprised to get this error when playing with temporary tables: mysql> use test; Database changed mysql> create temporary table t (i int); ERROR 1049 (42000): Unknown database 'test' I am using WinXP with MySQL version14.7 Distrib 4.1.21 - mysql-max Does anyone know why I can't create a temp table? This happens with any database I select.
View Replies !
View Related
Errcode 17 In Temporary Directory
I run a web server (on IIS) with a big site on it. It has php5, mysql 5 and odbc 3.51 It has 640MB of ram. at random times I get this error (not always the same file, but similar): [MySQL][ODBC 3.51 Driver][mysqld-5.0.27-community-log]Can't create/write to file 'C:EasyPHP mp#sql_3fc0_0.MYD' (Errcode: 17) If I go to that directory and delete that file, the problem disappears. But until I do this the pages display a mysql/odbc error and nothin works. So if it happens at night or when I'm out, my website will remain down for a lot. Really annoying problem!! I googled the errcode 17 and found out that it can be caused by: - directory permission problem > this is not my case, I've given correct permissions - an antivirus performing a scan in the temp directory > this is not my case So what is this?! Code:
View Replies !
View Related
Temporary Lock Record
I would like to know if i can lock a record while someone access it. Example: Table: myTable Field1 Field2 name1 1 name2 0 name3 0 ... ... can i issue a satement to lock the record with the name2 ? and another statement to unlock it later in my code ? so that while name2 is locked, other people can only access name1, name3,...
View Replies !
View Related
Temporary Table Performance
Since I work with MySQL 4.0... I can't use sub queries. So I have to create a temporary table to perform a complex request. The request has to be called periodically (let's say every second). The data in the temporary table must be refreshed each time I perform the request. My question is : What solution has le lower cost in terms of performance : droping, recreating the table and inserting the new data OR deleting all the records and inserting the new ones? The table contains less than 500 records.
View Replies !
View Related
Temporary Table Can Act Like Session Variables
i m looking to use a temporary table that acts more like a SESSION variable does in php. i use other temp tables in the site im working on and they go away after the sql connection is closed at the end of the page. i was wondering if theres a way to create a table that is there for a certain time period, or the best would be, like i said earlier, to act almost exactly like a session variable.
View Replies !
View Related
Error Inserting Into Temporary Table
I create a temporary table by using following statement: CREATE TEMPORARY TABLE ttable1( id tinyint( 4 ) , q decimal( 5, 0 ) ) TYPE = HEAP tryng insert a record by statement: INSERT INTO ttable1 VALUES ('1','1') return the following: #1146 - Table 'rail.ttable1' doesn't exist How can I insert in my temp table?
View Replies !
View Related
Nested Query On A Temporary Table
Yeterday I started playing with temporary tables but ran into a problem. Let me describe the situation: As a programmer of a family tree program I want to know if there are any isolated families in a family tree. The program has a table called families and in this table looks as follows:
View Replies !
View Related
Temporary Table Accessable Only In Instance Of Procedure
If I have a procedure that will be run simultaneously by several people which requires the use of a temporary table is there a way for the table to be accessible only in the instance in which the procedure is being run so that multiple tables can have the same name but not have collisions? I would normally think to name the table with a random string but apparently SQL rules do not allow dynamically generated table names. The other thing I would try is to have a permanent memory table with the structure I need, with an additional column that would be populated with a random number that indicates which batch is being processed, with the contents being deleted right before the procedure ends. I don't know what kind of impact that would have on the server, and am hesitant to try as my test environment has nowhere near the capacity of the production environment, in just about every way possible.
View Replies !
View Related
Temporary Table Are Dropped After Close Connection?
I think I know the answer to this question, but just to make sure. Temporary tables are dropped as soon as the connection is closed. The connection refers to the mysql connection as oppossed to a SESSION? So if I wanted to preserve data for a user within their session, but delete it as soon as their session ends, temporary tables probably would work for me?
View Replies !
View Related
Create Temporary Table With Read Permissions Only
In one database I made a query like this create temporary table tempT type=heap select * from orig_table1; insert into tempT select * from table2; SELECT column1, column2, column3, sum(column4) as sumOfColumn4, etc, WHERE date = 20070306 and name = "jan" GROUP BY Column4 order by name desc; drop table tempT; But on another server I only have read permissions. Is there a trick to do such a thing when you only have read permissions. Can I add the output in a variable and then merge the second select in the variable and if necessary empty the variable?
View Replies !
View Related
Own Privileges
Is there a way for a user to get knowledge about his own privileges? For example can a user know if he/she has rights to INSERT INTO some = table? I'm using PHP4.
View Replies !
View Related
No Privileges
I have installed PHP and MYSQL as a testing server on my local machine using xampp. I then copied over the SQL data folders from my old machine. When I go into PHPMYADMIN the only database I can see is the informationschema and under create new database it states that I have no privelidges.
View Replies !
View Related
Privileges
My .net application connects to MySQL database. A developer created this MySQL database and few tables. Now when I try to create another table for my application it is giving me this error "ERROR 1142 (42000): CREATE command denied to user 'username'@'localhost' for table 'tablename'." Even i tried to grant all the privileges to my user name using the below command "GRANT ALL ON dbname.* TO 'username'@'localhost'; but still iam getting the following error "ERROR 1044 (42000): Access denied for user 'username'@'%' to database 'dbname'" Even i tried to create a new database but still i am getting an error.
View Replies !
View Related
Database Privileges
I was wondering if there is a way to prevent useres from creating databases. The scenario is for a web hosting environment where I would like to let users only use their pre-defined database and not be able to create any additional database (e.g. via phpmyadmin).
View Replies !
View Related
Setting Up Privileges
if I can restrict a user from a single table. I want him to be able to create,alter,delete all the tables but one, which I want him not to delete it, not to update it not to create it.Just to use it. Can I do it? As long as I have read there is only the way to give credentials for all the objects of the database.
View Replies !
View Related
Changing Privileges
I need help changing host privileges in mySQL server. I am trying to access mySQL server from a remote computer that doesn't have access rights. The online doc sucks; I can't get anything useful out of it.
View Replies !
View Related
GRANT ALL PRIVILEGES ON Db.* .....
Having read, as best I could, the docs at http://www.mysql.com/documentation ..., on host (Linux from scratch, kernel 2.4.20) "mysql.here.com" I enter: mysql> GRANT ALL PRIVILEGES ON db.* TO root@'max.here.com'; Query OK, 0 rows affected (0.00 sec) but ... [root@max.here.com]# mysql -h mysql.here.com -u root -pmypass ERROR 1045: Access denied for user: 'root@max.here.com' (Using password: YES) "netstat -lt" on mysql.here.com shows the socket is listening okay. tcp 0 0 *:mysql *:* LISTEN.
View Replies !
View Related
A User Dba Privileges
I need to give a special user admin rights to the databases he creates. As there is no dedicated MySQL database server for his needs yet, we created a special user on a multi-purpose MySQL server; this special user has the rights to do everything only on databases he creates. To distinguish his databases among others, we require his databases' names to be prefixed by "PNCC_". Here's how we did the trick: Once logged in as DBA: mysql> GRANT ALL ON PNCC_.* TO fred@my.fqdn.com IDENTIFIED BY 'some_password' WITH GRANT OPTION; (The query ran OK) mysql> UPDATE db SET Db='PNCC_%' WHERE db='PNCC_'; (The query ran OK) (We had to do two different commands, as GRANT does not seem to accept wildcards). From now on, after having flushed privileges, user fred@my.fqdn.com can create databases with names prefixed by "PNCC_", create tables within these databases, and populates them. But this user would like to have the possibility to create users with special privileges on the databases/tables he previously created. The question is: how can I grant fred to create users with rights ONLY on PNCC_<some name> databases, and nothing else (ie. no access to other databases, nor on mysql.*)? Do I need to insert a special row within mysql.user? If so, which one?
View Replies !
View Related
|
TRACKER
