Getting The Sum Of Amounts For Each User - How To Join?
I have a simple table for transactions that will store information like a bank account.
To find the balance for a given user I want to sum all the amounts and display the result
How can I do this for more than one user at a time?
CREATE TABLE `transactions` (
`id` int(5) NOT NULL auto_increment,
`user_id` int(10) NOT NULL default Ɔ',
`created` datetime default NULL,
`modified` datetime default NULL,
`description` text character set latin1 collate latin1_general_cs NOT NULL,
`amount` decimal(10,0) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
I'm hoping to get output like:
user_id | balance(sum of amounts)
------------------------------------------
1 | 70
2 | -70
3 | 10
4 | 5
View Complete Forum Thread with Replies
Related Forum Messages:
Mammoth Amounts Of Self Joins
I have a table that contains keywords (Varchars) each one mapped to a product. so the database schema is just an id column, product column and keyword column. I want to select the products that contain x,y,z keywords. Now if this query involves many keywords I end up with a massive amount of self joins on the keywords table, is there a better way to do this than self joins? What would work perhaps is multiple unions where I could discard non duplicated rows but you can't seem to do that. Any ideas... ?
View Replies !
Adding Amounts Of Time
I am looking for a way, as the title suggests, to add a length of time to a time-type field.I needed to type something, and this is what I logically typed (of course, incorrect): SET playtime=playtime + '$length' Let's say playtime = 00:10:30, and $length = 00:04:30. What do I need to change the above statement to, so that the new value of playtime = 00:15:00 ?
View Replies !
Import Large Amounts
My client has a large database in MS Access. I need to put a section of this (approximately 900 rows) into MySQL to be displayed on a website. The thing is, every month I will be resupplied with updated data, which will need to replace the data that is currently in the SQL database. I am operating on a Mac and they've given it to me as an EXCEL spreadsheet, which I am trying to import into MYSQL using phpMyADmin with no luck. I've tried exporting from EXCEL to various formats, such as CSV, but I can't seem to get it to import into the MySQL database Does anyone know what I can do? I need it to be relatively simple so that every month I can simply reimport the new data. Is there some trick to importing the data from EXCEL, or should I ask the client to supply the data from MS Access in a different format.
View Replies !
Handling Large Amounts Of Text
I am desiging a web store to hold and manage up to 150,000 to 200,000 items and have run into a problem which I believe can be fixed but I am not sure about the way to go about it.The problem is that every product has a rich text description that contains a lot of extra HTML code that goes into the database with the product description. I would estimate every product has an average of 10k of description and code. Now instead of putting all of this into the database would it be possible to write this to a .txt file and keep it in a products_desc folder? Would this keep the database at a respectible size and optmize everything?Does anyone have any good examples or tutorials on going about this with PHP?
View Replies !
Inserting Large Amounts Of Data Into Mysql
I have a whole bunch of numbers and state, county, city, etc. data that i'm trying to load into a database for a demographics website. The data is all in excel currently so I could easily copy and paste it to a text file or another medium if necessary but I'm wondering what the quickest and most efficient way to get it all entered into mysql is.
View Replies !
JOIN -> Limiting One Record Per User
I have a database table called 'users' and an associated table called 'profile_pics' that has records for uploaded pictures for any associated user that chooses to upload one. The thing is, one user can have many profile pictures, but on this particular page I am making, I want only the most recent profile picture to display for each user. So I want my select query to retrieve all profile pictures from all members who have a profile picture(s), but only display one profile picture for each member (eventhough they may have more than one). How can I go about doing that in my SELECT statement? Any ideas how I can work this out?
View Replies !
How To Save Accounts Based On Variable Amounts Of Time
I'm currently building a website where users can signup for various accounts. An account may last 30 days, 12 months or 24 months. If a user's account is about to expire, an email notification is send, via a CRON Job. I want to save the different account types in the database, but I'm not sure how to save the duration of an account. If it was all months, I could simply save ཈' or པ' as an INT, but it also has an account for 30 days, which is more flexible. Edit: It would also be nice if I could ORDER BY duration..
View Replies !
Error 1142 - Mes. The Following Occured: SELECT Command Denied To User Xx@hostx.com For Table 'user
When trying to do anything to do with "User Administration" in MySQL Administrator 5.1 connecting to a remote MySQL database, I get the following message when I click on the "User Administration" icon/text in top left frame. Verbatim quote apart from my connection details which I am anonymising): A MySQL error was encountered. The message is: Could not fetch user names. The following occured: SELECT command denied to user xxx@hostxxx.xxx.xxx.com for table 'user' (1142) No I have looked through search and found the following: http://lists.mysql.com/mysql/134369 Unfortunately that tells me that it has happened to someone else but that's about it. What is one supposed to do with the fix? I am wondering if this is why I cannot create ordrop any tables with this username at the moment, even though it is the Admin level username....
View Replies !
Root User Denied Access To Fetch User List.
I can still log onto MySQL administrator or the command line program but I somehow appear to have lost all priveliges to alter or view the user list from this account. Even under the command line interface I can login but it tells me access is denied when I try to alter "root" permissions on a database. I cannot seem to log onto any of the other accounts created but obviously if I could get into the user admin I could solve this.
View Replies !
Copy User Privileges To A Newly Created User.
I have just created a user with specific permissions to certain tables in different databases. Now I have to create serveral more users with the same permission. Is there a command in MySQL that allows me to create the new user by copying the privileges from a template user?
View Replies !
Join Vs. Inner Join Vs. Implied Join = Different Results ??
I SUM() only on the order table in all queries below. Here's a set of queries that I thought would/should yield the exact same results: QUERY 1: SELECT COUNT( o.orderID ) FROM order o WHERE DATE( o.orderDATE ) = ��-01-04' AND o.orderSTATUS = 300 yields 161 QUERY 2: SELECT COUNT( o.orderID ) FROM order o LEFT OUTER JOIN credit_card cc ON o.orderID = cc.orderID WHERE DATE( o.orderDATE ) = ��-01-04' AND o.orderSTATUS = 300 yields 175 QUERY 3: SELECT COUNT( o.orderID ) FROM order o, credit_card cc WHERE o.orderID = cc.orderID AND DATE( o.orderDATE ) = ��-01-04' AND o.orderSTATUS = 300 yields 157
View Replies !
LEFT JOIN? RIGHT JOIN? Multiple JOIN?
Simplifying this down to its basics, I'm using LEFT JOIN in a query but I'm not getting the results I want. The tables are: table services service_id service_name table services_provided service_id service_date (date field) cust_id service_quantity I need to select ALL services from the services table, and the number of services provided (by a specific customer, in a specific time frame) from the services_provided table, so that I can generate a list that shows services provided by that customer in the specified period of time The query: SELECT service_date, service_name, service_quantity FROM services LEFT JOIN services_provided ON services_provided.service_id = services.service_id WHERE cust_id = $cust_id AND MONTH(service_date) = 10 AND YEAR(service_date) = 2007 GROUP BY service_id ORDER BY service_id (Aside: The date to be selected varies - it may be the whole year, or may be a selection of months,such as 1, 2 or 3. This is determined dynamically in the script. The cust_id is determined by which customer is logged in.) I'm pretty sure that the left join as I have it should return all services, even if there's no corresponding entry in the services_provided table. But because of the WHERE clause, I don't get a complete list of all services -- if the customer doesn't have any entries for a particular service, that service doesn't come up in my results. Do I need to change how I'm joining the tables, or join them twice? I'm sure I could do this with a nested query, but I'm trying to avoid that.
View Replies !
Deleted Root User From 'user' Database!
I so stupidly deleted the root user from my mysql > user database. Now I can't login to mysql. I uninstalled the rpm and reinstalled it but it still won't let me setup a new root user. How can I delete the mysql data files so that I start again, fresh? Where are these files located? I can't find them anywhere after removing the RPM but they are obviously there because upon reinstalling the RPM I'm still in the same position--I try to do mysqladmin -u root password 'password' but it says "Access denied for user root@localhost"
View Replies !
Access Denied For User: 'user@172.22.0.1' (Using Password: YES)
I am currently running two servers one with Tomcat and the other with MySQL. They are both in different Windows domains and on different network segments. I have the following users on the MySQL server: user@localhost user@% I am getting a VERRRY SLOOOW connection between the two machines so I thought I'd add a user just for the Tomcat server. The IP address of the Tomcat server is 172.22.0.1 so naturally the user would be 'user@172.22.0.1' When I added this user I got the following error Access denied for user: 'user@172.22.0.1' (Using password: YES) Any ideas on what I can do to remedy the situation?
View Replies !
Deleted Root User From Mysql.user !!!
While trying to grant access to root user, i deleted all entries for root from mysql.user. I've tried to use root% (anonimous built-in administrator user) but to no good). Is there another admin user i could use to run mysql monitor and insert root records to mysql.user? I've read Could I use a copy of the mysql.user table? I'm using version 5.0.16-standard on Linux Fedora Core 3. Before ruinin the root user, i made a backup of a databasefrom the same server (not mysql database); i was wondering if that backup contains the mysql.user information somehow?
View Replies !
User@localhost And User@domain.com
I have been following a tutorial for a mail server which uses mysql, mainly postfix all through the tutorial i have been asked to put a user and password in the files for the database.. which i have done root and pass1 in the log files the error shows as warning access denied for user root@domain.com ( using password : yes ) when i log on from the shell, to mysql and purpously get the password wrong, I get access denied for user root@localhost ( using password : no ) i have checked the password is correct.. is the part after the @ the issue, and how can i tell mysql that root@domainname.com is a valid user???
View Replies !
Selecting User,password From User
When I do: select User,Password from user in MySQL, I get encrypted password. How can I see the real value? And, what is the proper (secure) way to access MySQL database from PHP or other scripting languages?
View Replies !
Connecting Three Tables With Left Join And Ordinary Join
I have 3 Mysql tables: Week (with columns day and hour) Activity (with columns day, hour, activityid and ac_text) Person (with columns name and activityid) I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement: Select …. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour I can then make a loop (I am usin asp.net) that writes the activities. My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid). I have a little extra question. When I make the join above and print the result (day, time and activity) there isn’t any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.
View Replies !
Straight_join, Join Order & Join Conditions
I have a query with 4 tables and plain 'JOIN's the explain gives the best join order, and it completes in 1.5 secs I add a single ORDER BY (a calculated column) and the join orders all shift and the query takes 85secs! So I read the docs and it suggests STRAIGHT_JOIN to force join order. now I was using: JOIN myTable ON xyx=abc but in the docs it seems the ON condition is not permisible here, though it does work. Am I infact doing an 'INNER JOIN'? certainly if I change to INNER JOIN there is no difference. However the only way I can force the join order is to use STRAIGHT_JOIN that does not accept an ON condition, so I have shifted the clauses to the WHERE and it works fine. Is there any syntax I can use to keep the ON conditions, I prefer this approach it makes the code clearer regarding intent. Code:
View Replies !
Cross Join + Full Outer Join ?
I desesperately need to cross join 2 rows in a table, like this table : table values : env type value --------------------- env1 a 1 env1 b 2 env2 b 3 env2 c 4 env3 c 5 -> to get another table crossed by env and type like this : table results : a b c ---------------------- env1 1 2 null env2 null 3 4 env3 null null 5 i tried requests using cross join and full outer join but no way, FULL OUTER JOIN is unknow by mysql, indeed i doubt this is the good solution...
View Replies !
User Problems... Only Root User "works".
i have been trying to figure this strange problem out for a week or so. here it is. i have a set of users with various permissions for different reasons. about 2 weeks ago, my hardware suffered a fairly nasty failure. i recovered everything. things seemed to work out. i didnt think to check my phpBB forums or my blog (or other mysql based stuff). never had any problems with the DB... so, maybe i am missing something simple here. i hope so. what my users can do: they can log in via command line, a gui i use on mac os x and it does not matter if it is local, or remote. they work just fine. what all but one users cannot do: none of my users can run phpbb or the blog stuff.. except for the root user. any other user returns the error message written in the code of which ever script i try to access telling me that "could not connect to DB" etc.. root works fine. now, i thought maybe it was a problem with the forums and other stuff. but i cant be. same thing happens with all things... even a game server i occasionally run that keeps account info in a DB. all these users are set up properly and have worked just fine in the past. nothing new has been installed, no conf changes..im at a loss...
View Replies !
Joining Data (inner Join / Self Join?)
I am relatively new to php/mysql and I am having a problem figuring out how to do a join. I have a database with a person's name and each person has an ID. I want to be able to add their relatives by typing only their ID. For example if person 1's descendant was person 37, I want to be able to enter that in the DB and then run a query on person 1's page so that when I have 37 entered as his descendant it will query the DB for his name and print his name but not the ID.
View Replies !
Using LEFT JOIN Instead Of A Equi-JOIN
I have a SQL statement in some code I'm trying to get my head around.. I havent used SQL that much so I assume this is a newbie question: Why would someone use LEFT JOIN if they can simply construct the statement with equi-JOIN? The first statement uses left joins and the 2nd is my reconstruction using equi-JOINs.. so far they produce the same results (however it could be I dont have the right kind of test data) So to summarize my questions: Why do it using LEFT JOINS which I personally find harder to read over the equi-JOIN, 2nd Do they acutally produce the same result everytime? 1st (LEFT JOIN) ------------------------------------------------------------- SELECT action.action, summary.gatekeepercl, branch.branch FROM summary LEFT JOIN action ON summary.action=action.id LEFT JOIN branch ON summary.branch=branch.id WHERE summary.gatekeepercl IN (506100,506101) 2nd (equi-JOIN) --------------------------------------------------------------- SELECT action.action, summary.gatekeepercl, branch.branch FROM summary, action, branch WHERE summary.action=action.id AND summary.branch=branch.id AND summary.gatekeepercl IN (506100,506101)
View Replies !
Upgrading: Replacing Commas With "INNER JOIN" In LEFT JOIN Queries
I've just upgraded from 4.1 to 5.0 and I'm very scared. So, no more comma-separated table names in queries with LEFT JOIN clauses are allowed? ( http://forums.devshed.com/mysql-help-4/having-unknown-column-in-on-clause-error-323495.html) Each of those commas has to become ' INNER JOIN '. I have almost 400 left join queries spread out over a couple hundred files. I'm sure it's for the best, but oh is this task going to hurt! It looks like even a regular expression search & replace solution won't be feasible. I'm sure I'm not the first person who had to do some mass replacing. Any suggestions? Am I dreaming thinking there might be a variable that can be set that will allow the "old" format? I'm sure I'm in store for other issues, but are any of them major syntax changes like this one?
View Replies !
Changing User From One User To Another
Currently i have 2 Users in My MySQL(root,user1)...Whenever i am clicking command prompt Client its directly taking me to root user. If i want to change user (to user1) from there how i need to proceed...(Like connect in Oracle what we need to be use in mysql.
View Replies !
JOIN Within LEFT JOIN
I am using MySQL 3.23.54. I have the following table structure. FORMS form_id (PK) form_name STAFF ASSIGNMENTS staff_assignment_id (PK) form_id (FK) staff_id (FK) STAFF staff_id (PK) first_name last_name For each record in FORMS there may be zero, one or multiple records in STAFF ASSIGNMENTS. I need to perform a left join from FORMS on STAFF_ASSIGNMENTS. When there is a record in STAFF ASSIGNMENTS, I need to perform a join with STAFF to retreive staff name. Here is my attempt at the query. SELECT forms.form_id, forms.form_name, staff.first_name, staff.last_name FROM forms LEFT JOIN staff_assignments ON forms.form_id = staff_assignments.form_id (JOIN staff on staff_assignements.staff_id = staff.staff_id) How do I need to write the query?
View Replies !
Inner Join Or Left Join?
What I am trying to do is this.... English Table: Number Text Roman 1 One I 2 Two II 3 Three III 4 Four IV Hindi Table: Number Text 2 Do 3 Teen 4 Char 5 Panch Expected Results where number is 2 Text Roman Two II Do II
View Replies !
Self Join Plus Left Join?
select distinct t2.personid, count(*) from messagexperson as t1, messagexperson as t2 where t1.messageid = t2.messageid and t1.personid = 2877 and t2.personid <> 2877 group by t2.personid like to get the actual names of the other people rather than the person id. Is there a way to combine another join with the self join to accomplish this? I tried inserting a left join in place of the t2 definitions, i.e. select distinct t2.name, count(*) from messagexperson as t1, (messagexperson left join person on messagexperson.personid = person.id) as t2 where t1.messageid = t2.messageid and t1.personid = 2877 and t2.personid <> 2877 group by t2.personid
View Replies !
To JOIN Or Not To JOIN... Or Am I Missing Something...?
Right, I was always under the impression that it was 'better' to use JOINs, partly because it is 'faster'. I'm now wondering if that is simply a myth. Take these two SQL statements: SELECT DISTINCT p.ProductID, p.Image, p.Price FROMproducts AS p RIGHT JOINcategory_links AS c_l ONc_l.ProductID= p.ProductID INNER JOINcategories AS c ONc.CategoryID= c_l.CategoryID RIGHT JOINbrands AS b ONp.BrandID= b.BrandID RIGHT JOINsize_links AS s_l ONs_l.ProductID= p.ProductID INNER JOINsizes AS s ONs.SizeID= s_l.SizeID RIGHT JOINcolour_links AS co_l ONco_l.ProductID= p.ProductID INNER JOINcolours AS co ONco.ColourID= co_l.ColourID SELECT DISTINCT p.ProductID, p.Image, p.Price FROMproducts AS p, category_links AS c_l, categories AS c, brands AS b, size_links AS s_l, sizes AS s, colour_links AS co_l, colours AS co WHEREc_l.ProductID= p.ProductID ANDc.CategoryID= c_l.CategoryID ANDp.BrandID= b.BrandID ANDs_l.ProductID= p.ProductID ANDs.SizeID= s_l.SizeID ANDco_l.ProductID= p.ProductID ANDco.ColourID= co_l.ColourID The first one uses JOINs and the second simply uses WHERE. As a matter of information, both have additional WHERE details added to refine the search. I'm using a fast PC, and there are only 14 products in the database (and not all that much data in the other tables). However, I was getting REALLY slow script execution, and I traced it to the SQL query. Running the first one takes an average of 7 seconds. Running the second query takes less than 1 second. It's almost instantaneous in fact. This kinda tells me NOT to use JOINs... and to stick with WHERE for this. But in that case, I am left confused as to where it is appropriate to use JOINs and where it isn't...? I did a couple of hours of Googling and didn't clear the matter up. All the articles I found pointed towards using JOINs. Obviously at the end of the day I'm going to use the faster method. Plus, after thinking about it, there's a lot more work being done with the JOINs, is there not...? I'd love to know what some other people think about this, and whether I'm just totally out on my JOIN usage or if other people are using them in the same situations. ::] krycek [::
View Replies !
Need Help With Join And Values In The Join
I set up a join using two tables (description and product). I have multiple products that use the same description. I also have different material types for the products. The SQL is "SELECT descripID, subCategory, ProdID, subCat, materialType FROM products, descriptions WHERE subCategory = subCat". from the description table: - descripID - subCategory from the product table: - ProdID - subCat - materialType What I want to do is get the descripID and materialType into a new table. There is going to be multiple descripIDs that match multiple materialTypes. I only want one materialType to be with a particular DescripID. for example: descripID: 01 materialType: 01 descripID: 01 materialType: 02 descripID: 02 materialType: 01 descripID: 03 materialType: 03 I hope this helps. Here is some of the output I currently have: DescripID: 0024 Desc Subcat: LINSEED OIL Prod Subcat: LINSEED OIL ProdID: 00024 Prod Material: 01 DescripID: 0024 Desc Subcat: LINSEED OIL Prod Subcat: LINSEED OIL ProdID: 00025 Prod Material: 01 DescripID: 0024 Desc Subcat: LINSEED OIL Prod Subcat: LINSEED OIL ProdID: 00026 Prod Material: 03 DescripID: 0024 Desc Subcat: LINSEED OIL Prod Subcat: LINSEED OIL ProdID: 00027 Prod Material: 02 DescripID: 0024 Desc Subcat: LINSEED OIL Prod Subcat: LINSEED OIL ProdID: 00028 Prod Material: 02
View Replies !
(select Where) Join OR (select Join) Join
which one is better, (select where) join OR (select join) join ?! I can join two table with select and where, also i can do the same with join keyword. The result is same but which one is better? I know that joining with join keyword is better for explicit code but what about performance?
View Replies !
User Name
I'm trying to connect to mySQL through Dreamweaver, and got as far as the mySQL Connection box, where I type in my connection name, server, user name, and password, then search for a database. I'm using a tutorial for an online class, but it's not very clear about the user name. How do I get one/do I already have one? Could it be one I set up with phpMyAdmin? Please keep in mind that I am as noob as they come, so I may need the techono-speak dumbed down a bit
View Replies !
User And Use DB
Using PHP and MySQL: I know I can use a connection string such as : $mysqli = mysqli_connect("SQLSvr01","User", "Pass", "dB", Port); Will I have a confined Query based on this connection or if my web site makes a query for a seperate dB at the same time from another user will they conflict. or let me refrase Is this connection isolated from all other requests, regardless of how many times the request is made, or is it dependant on the user or sever or database?
View Replies !
User: Any
I've noticed a user called Any in the MySQL privileges table. This user has 'useage' priv on host 'localhost' and 'build' ... should I delete this? Does it pose a security risk?Also, what's the host 'build' ?
View Replies !
Can't Create New User In 4.1.18
this is what I am doing: mySQL> GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON store. TO newusername@127.0.0.1 IDENTIFIED BY 'newpassword'; I have tried leaving out , LOCK TABLES ON also just ,LOCK TABLES it keeps saying: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TO newusername@127.0.0.1 IDENTIFIED BY 'newpassword'' at line 1 I am using 4.1.18
View Replies !
User.MYD Broken
While doing a couple of grant statements I suddenly got the message that user.MYD could not be opened. So I copied it back from yesterdays backup but no luck. I restored user.MYD, user.MYI and user.frm and after that the whole mysql directory in /var/lib/mysql but the server now refuses to start. What can I do?
View Replies !
User Question
I am new to MySQL and recently took over the MySQL from someone who also did not know what they were doing. I recently installed PHPMyAdmin to better manage the box but I noticed that the user table looked like this: User Host Password Global privileges Grant Action Any % No USAGE No Edit Any localhost Yes ALL PRIVILEGES Yes Edit root % Yes ALL PRIVILEGES Yes Edit root localhost Yes ALL PRIVILEGES Yes Edit My question is, why is there 2 any and 2 root users? Don't I only need the root@localhost and any@localhost? What would be the best way for me to find out if these users are needed?
View Replies !
Remove User But Still Can Log In
I use the command to create a user and grant all privileges to this user, then I delete this user from the user table but I can log in use this user. Why ? grant all privileges on *.* to john@"%" identified by 'john' I then delete the user 'john' from the user table in mysql database. But then I tried to log in using this user successfully.
View Replies !
Cannot Create User
I have a problem with mySql server: I can only run it as root. I use mySql 4.0.15 and phpMyAdmin 2.5.5. on Mac OSX 10.3.2. I run mySql server as root, log in via phpMyAdmin as root and I see there is no user mysql when I click 'Privileges'. When I try to make new user 'MySql' (with minimal privileges) and click 'Go' I get an error 'unknown command'. The corresponding mySql statement is: GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES ON * . * TO "mysql"@ "%"IDENTIFIED BY "*****"WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ; #1047 - Unknown command How can I log in as user root and not have Grant privileges??? More importantly, how can I create a user mysql with the normal set of privileges (update, create, alter etc)? I have been using the procedure described on the mysql.com website 'A.4.2 How to Reset a Forgotten Root Password' to reset the password for root.
View Replies !
Create User
I have a problem with mySql server: I can only run it as root. I use mySql 4.0.15 and phpMyAdmin 2.5.5. on Mac OSX 10.3.2. I run mySql server as root, log in via phpMyAdmin as root and I see there is no user mysql when I click 'Privileges'. When I try to make new user 'MySql' (with minimal privileges) and click 'Go' I get an error 'unknown command'. The corresponding mySql statement is: GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES ON * . * TO "mysql"@ "%"IDENTIFIED BY "*****" WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 ; #1047 - Unknown command How can I log in as user root and not have Grant privileges??? More importantly, how can I create a user mysql with the normal set of privileges (update, create, alter etc)? I have been using the procedure described on the mysql.com website 'A.4.2 How to Reset a Forgotten Root Password' to reset the password for root.
View Replies !
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 !
User Access
I have added a user, this user can attach to mysql i.e. mysql -Uusername -P database, but they can't access the database I want via use database; What permissions do I need to check?
View Replies !
User And Password
I just installed mysql for the 5th time... and I tried to follow the refrence manual on how to delete the default users that comes with it and add my own with a password so there is no annoynomis acess. But something happened and where it should of said mysql> It just had -> A arrow.. and I coldn't fix it, so can someone tell me how to delete all the users and make my own with a password. I can take it from there. I've been trying to set this up for a while cause i've been learning cold fusion, I just can't get this to work somehow.
View Replies !
|
TRACKER
