RE: GRANT issues
hi Steve, after you login with the new account , you can use the command show grants; to check which permission had been already granted to this account as below. mysql show grants; +-+ | Grants for r...@localhost | +-+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | +-+ 1 row in set (0.00 sec) best regards liuyann From: smarq...@marquez-design.com Subject: GRANT issues Date: Tue, 5 Oct 2010 09:50:54 -0500 To: mysql@lists.mysql.com Greetings, I am attempting to set up permissions on DB with the following code from the terminal on Mac OS 10.6 logged into mysql with a user that has access to the mysql database GRANT SELECT ON pet_calendar.* TO username@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; I can get the user and password set up fine, but it does not grant any privileges. It is probably an easy fix that I am just missing, but I would appreciate your help. Thanks, -- Steve Marquez Marquez Design e-mail: smarq...@marquez-design.com web: http://www.marquez-design.com phone: 479-648-0325 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: from excel to the mySQL
hi HaidarPesebe, one way to do this is : 1. save the excel to xxx.csv 2. then use the mysql SQL statement LOAD DATA INFILE for example : LOAD DATA INFILE 'c:\\test\\xxx.csv' INTO TABLE tbl_name FIELDS TERMINATED BY ',' best regards liuyann From: haidarpes...@gmail.com To: mysql@lists.mysql.com Subject: from excel to the mySQL Date: Tue, 3 Aug 2010 16:23:22 +0700 Dear All, I'm needs a way to upload data from excel to the mySQL database. Dear all, I need help is how to upload data from excel columns and load into mysql database using php? Thanks HaidarPesebe
RE: Getting the sum() for a column from a joined table
hi , Octavian you can try this SQL. = select agents.id, agents.name, (select count(*) from clients where agent=agents.id), (select sum(value) from sales where agent=agents.id) from agents where agent.id=100 = best regards ACMAIN To: mysql@lists.mysql.com From: orasn...@gmail.com Subject: Getting the sum() for a column from a joined table Date: Sun, 3 Jan 2010 01:35:49 +0200 Hi, I have 3 tables, `agents`, `clients` and `sales` and I want to select a single agent from the `agents` table, and 2 more columns that contain the number of clients for the selected user (from the `clients` table) and the sum of the sales for the selected user (from the `sales` table). Is it possible to do this selection in a single query? I have tried using: select agents.id, agents.name, count(clients.name), sum(sales.value) from agents left join clients on agents.id=clients.agent, left join sales on agents.id=sales.agent where agent.id=100 group by clients.agent, sales.agent; But it doesn't give good results. The sum of sales is bigger than it should be... kind of multiplied with the number of clients that match, like if there were no group by columns specified. I have tried to group by more other columns like clients.id and sales.id or agents.id, but with no good results. Thank you for your help. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=liuy...@live.com _ Keep your friends updated―even when you’re not signed in. http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_5:092010
RE: error creating table
hi, I run your code , but worked propertly. I suggested to check the table USERS , ROLES, is the column userid / roleid same data type (INT(10) UNSIGNED ) with your userroles table ? == mysql create table users (userid INT(10) UNSIGNED primary key); Query OK, 0 rows affected (0.06 sec) mysql create table roles (roleid INT(10) UNSIGNED primary key); Query OK, 0 rows affected (0.06 sec) mysql CREATE TABLE `userroles` ( - `roleid` INT(10) UNSIGNED NOT NULL DEFAULT '0', - `userid` INT(10) UNSIGNED NOT NULL DEFAULT '0', - PRIMARY KEY (`roleid`, `userid`), - INDEX `FK1_user` (`userid`), - CONSTRAINT `fk_userid` FOREIGN KEY (`userid`) REFERENCES `users` - (`userid`) ON UPDATE CASCADE ON DELETE CASCADE, - CONSTRAINT `fk_roleid` FOREIGN KEY (`roleid`) REFERENCES `roles` - (`roleid`) ON UPDATE CASCADE ON DELETE CASCADE - ) - COLLATE=utf8_general_ci - ENGINE=InnoDB - ROW_FORMAT=COMPACT - AVG_ROW_LENGTH=0; Query OK, 0 rows affected (0.08 sec) mysql == best regards liuyann Date: Sun, 4 Oct 2009 23:47:54 +0530 Subject: error creating table From: saf...@gmail.com To: mysql@lists.mysql.com Hi, I'm trying to create a table with 2 columns both are primary key (combined) and both are foreign key as well. I'm getting error cannot create table. Here is the sql CREATE TABLE `userroles` ( `roleid` INT(10) UNSIGNED NOT NULL DEFAULT '0', `userid` INT(10) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`roleid`, `userid`), INDEX `FK1_user` (`userid`), CONSTRAINT `fk_userid` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT `fk_roleid` FOREIGN KEY (`roleid`) REFERENCES `roles` (`roleid`) ON UPDATE CASCADE ON DELETE CASCADE ) COLLATE=utf8_general_ci ENGINE=InnoDB ROW_FORMAT=COMPACT AVG_ROW_LENGTH=0 -- Sharique uddin Ahmed Farooqui (C++/C# Developer, IT Consultant) http://safknw.blogspot.com/ Peace is the Ultimate thing we want. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=liuy...@live.com _ Windows Live: Keep your friends up to date with what you do online. http://www.microsoft.com/middleeast/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:en-xm:SI_SB_1:092010
RE: login issue..pls help
hi Faizal, in this case, usualy we choose the MyISAM engine, as it will more fast than the innodb. here is the comparision for hte MyISAM vs InnoDB for you reference. 13.1. Comparing Transaction and Nontransaction Engines http://dev.mysql.com/doc/refman/5.1/en/storage-engine-compare-transactions.html best regards liuyann From: sac.fai...@gmail.com Date: Thu, 1 Oct 2009 15:06:54 +0530 Subject: Re: login issue..pls help To: aim.prab...@gmail.com CC: mysql@lists.mysql.com user will create more number of tickets per day. so INSERT, UPDATE, SELECT will use highly. in document says' Myisam will not support transaction but it has self backup and restoration method. but in InnoDB good for transaction but no self managed backup and restoration.. now how can i choose my engine. ? my usage is only storage and retrieve data.. so which one is normally preferred by all developers thanks kumar Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 2:59 PM, prabhat kumar aim.prab...@gmail.com wrote: innodb. Since, If there are many modifications of the data, it's said that InnoDB works faster because it uses row locking instead of table locking, like MyISAM. However, if there are mainly SELECT statements, a MyISAM table might be faster. On Thu, Oct 1, 2009 at 2:33 PM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: 1 doubt the front end application is lab ticketing system. so user will create many ticket. iam expecting per day 200k ticket user will create. so which storage engine is best to use. innodb or myisam..? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 2:10 PM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: thanks kumar. Storage in datadir contain only one location.is it possible to have multiple location to store datafiles.if yes, how? what is the different between storage engine and tablespace in mysql? account creation *create user 'test'@'%' identified by 'test'; grant usage on test.* to 'test'@'%' identified by 'test'; grant select,insert,update,delete,create,drop on test.* to 'test'@'%'; * I used 'USAGE' clause this time but even after that i can able to see other schema and i can able to 'select' tables from other schema. how i can restrict this. only default schema should be accessible to the user thanks in advance.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 1:13 PM, prabhat kumar aim.prab...@gmail.comwrote: A. For transaction *innoDB* is best. B. *ALTER TABLE table_name ENGINE = InnoDB; * but* *if you tons of tables so better export all tables in file and replace all occurs of *myisam* with *InnoDB *the import it. and enable InnoDB engine. http://www.linux.com/archive/articles/46370 C. 1. Stop MySQL. 2. Copy current mysql dir to new drive. 3. Modify the current location with new location in /etc/my.cnf file , datadir variable. 4. Start MySQL. note: *datadir=/path/to/datadir/mysq*l http://docdb.fnal.gov/doc/my.cnf.html On Thu, Oct 1, 2009 at 12:53 PM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: hi Kumar thanks for your info.. another doubt.. which storage engine is best for transaction.. in my server. the default engine is myisam. now i changed one of my schema to innoDB. is that correct. now i want to create another schema in different location. how to create that? because current mountpoint space is very less, in this situation how can move existing schemas to new location? thanks in advance.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 12:28 PM, prabhat kumar aim.prab...@gmail.comwrote: a. INFORMATION_SCHEMA is the information database ie metadata, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them. Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. http://dev.mysql.com/doc/refman/5.0/en/information-schema.html b. Its depend on your requirement like your user will perform only DML query or also want DDL operations. http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html On Thu, Oct 1, 2009 at 12:08 PM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: thanks kumar.. now its working i have another doubt i created an user with limited privilege on particular schema. schema name: test *mysql create user 'test'@'%' identified by 'test'; mysql grant
RE: Unable To Remove User
hi Carloswil, could you do below check: 1. show processlists; // is there any session logined by this user account? 2. select CURRENT_USER() ; // ensure you are logining with r...@localhost, not the r...@% best regards liuyann Date: Wed, 30 Sep 2009 09:37:28 -0400 Subject: Re: Unable To Remove User From: carlosw...@gmail.com To: mysql@lists.mysql.com On Wed, Sep 30, 2009 at 9:12 AM, Martin Gainty mgai...@hotmail.com wrote: Grant he is trying to login as that user then drop the user he logged in as No, no and no. I clearly showed my commands including my login to MySQL as root. I also cleanly noted I can't even login as the user I am trying to delete. I will try Claudio's suggestion and post back my results. I find it odd that the output of users has a carriage return in it which malformed the table output. It's not normally like that so you're possibly right and that could be the issue! Either way I hope Claudio's instructions resolve it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=liuy...@live.com _ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx
