Re: login issue..pls help
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 select,insert,update,delete,create,drop on test.* to 'test'@ '%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar aim.prab...@gmail.comwrote: Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: Hi iam getting the following error while login into mysql server. andd141# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Can someone please help me understand why I am not able to login from root? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: login issue..pls help
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.com wrote: 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 select,insert,update,delete,create,drop on test.* to 'test'@ '%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar aim.prab...@gmail.comwrote: Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: Hi iam getting the following error while login into mysql server. andd141# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Can someone please help me understand why I am not able to login from root? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Adding Fields To Table
Hello Carlos, I am no doubt very new to MySQL and have been studying the on line manual however I find it a bit complex and don't understand what it's suggesting I do in this case. I created a database called 'staff' and then in that database I created one simple table: mysql show tables; +-+ | Tables_in_staff | +-+ | it | +-+ 1 row in set (0.00 sec) mysql describe it; +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | name | varchar(40) | YES | | NULL| | | username | varchar(20) | YES | | NULL| | | email| varchar(20) | YES | | NULL| | | office | char(3) | YES | | NULL| | +--+-+--+-+-+---+ 4 rows in set (0.00 sec) My question is if I would like to add addition fields like 'title' 'phone number', how can I modify the table fields entry to add more fields? Thanks for any help! The manual has a section on how to create and modify your metadata, I suggest you take a look, it explains all. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.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: login issue..pls help
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.com wrote: 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 select,insert,update,delete,create,drop on test.* to 'test'@ '%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar aim.prab...@gmail.comwrote: Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: Hi iam getting the following error while login into mysql server. andd141# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Can someone please help me understand why I am not able to login from root? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Adding Fields To Table
You can use : *ALTER TABLE it ADD COLUMN title varchar(25), phone number integer (6);* it will add new column after last. but you can also specify the particular column after you want to add. like. *ALTER TABLE it ADD COLUMN title varchar(25), phone number integer (6) AFTER pusername; * check more options. http://dev.mysql.com/doc/refman/5.1/en/alter-table.html On Thu, Oct 1, 2009 at 12:32 PM, Martijn Tonies m.ton...@upscene.comwrote: Hello Carlos, I am no doubt very new to MySQL and have been studying the on line manual however I find it a bit complex and don't understand what it's suggesting I do in this case. I created a database called 'staff' and then in that database I created one simple table: mysql show tables; +-+ | Tables_in_staff | +-+ | it | +-+ 1 row in set (0.00 sec) mysql describe it; +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | name | varchar(40) | YES | | NULL| | | username | varchar(20) | YES | | NULL| | | email| varchar(20) | YES | | NULL| | | office | char(3) | YES | | NULL| | +--+-+--+-+-+---+ 4 rows in set (0.00 sec) My question is if I would like to add addition fields like 'title' 'phone number', how can I modify the table fields entry to add more fields? Thanks for any help! The manual has a section on how to create and modify your metadata, I suggest you take a look, it explains all. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: login issue..pls help
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.com wrote: 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 select,insert,update,delete,create,drop on test.* to 'test'@ '%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar aim.prab...@gmail.comwrote: Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: Hi iam getting the following error while login into mysql server. andd141# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Can someone please help me understand why I am not able to login from root? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: login issue..pls help
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.com wrote: 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.com wrote: 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 select,insert,update,delete,create,drop on test.* to 'test'@'%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar aim.prab...@gmail.comwrote: Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: Hi iam getting the following error while login into mysql server. andd141# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Can someone please help me understand why I am not able to login from root? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Adding Fields To Table
Hi! prabhat kumar wrote: You can use : *ALTER TABLE it ADD COLUMN title varchar(25), phone number integer (6);* it will add new column after last. but you can also specify the particular column after you want to add. like. *ALTER TABLE it ADD COLUMN title varchar(25), phone number integer (6) AFTER pusername; * The syntax is correct, but one data type is wrong: A phone number is no numeric type, it is a string of (mostly) digits. Those who think there is no difference, they are wrong: 1) In a phone number, you may need a leading zero. No numeric type supports that, unless you try some formatting which would then affect all values and not just selected ones. 2) Just ask yourself whether it makes sense to compute the average phone number (yes, the term is misleading). Similar reasoning applies to other digit strings, like article number, ISBN (books), SSN (social security number), passport number, etc. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: login issue..pls help
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 select,insert,update,delete,create,drop on test.* to 'test'@'%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar aim.prab...@gmail.comwrote: Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: Hi iam getting the following error while login into mysql server. andd141# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Can someone please help me understand why I am not able to login from root? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile
Re: login issue..pls help
a. at Linux level you can make soft link to database dir to particular database ie schema. I don't is it possible at mysql level? b. There is no relation between storage engine and table space. Yes, Innodb uses tablespace you can read more on both. c. http://mysql-tips.blogspot.com/2005/04/setup-new-users-in-mysql.html 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 select,insert,update,delete,create,drop on test.* to 'test'@'%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar aim.prab...@gmail.comwrote: Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: Hi iam getting the following error while login into mysql server. andd141# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Can someone please help me understand why I am not able to login from root? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat
Re: login issue..pls help
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 select,insert,update,delete,create,drop on test.* to 'test'@'%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar aim.prab...@gmail.com wrote: Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L sac.fai...@gmail.comwrote: Hi iam getting the following error while login into mysql server. andd141# mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Can someone please help me understand why I am not able to login from root? Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn:
Re: Adding Fields To Table
Thanks Joerg. You are very correct. On Thu, Oct 1, 2009 at 2:40 PM, Joerg Bruehe joerg.bru...@sun.com wrote: Hi! prabhat kumar wrote: You can use : *ALTER TABLE it ADD COLUMN title varchar(25), phone number integer (6);* it will add new column after last. but you can also specify the particular column after you want to add. like. *ALTER TABLE it ADD COLUMN title varchar(25), phone number integer (6) AFTER pusername; * The syntax is correct, but one data type is wrong: A phone number is no numeric type, it is a string of (mostly) digits. Those who think there is no difference, they are wrong: 1) In a phone number, you may need a leading zero. No numeric type supports that, unless you try some formatting which would then affect all values and not just selected ones. 2) Just ask yourself whether it makes sense to compute the average phone number (yes, the term is misleading). Similar reasoning applies to other digit strings, like article number, ISBN (books), SSN (social security number), passport number, etc. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: login issue..pls help
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 select,insert,update,delete,create,drop on test.* to 'test'@'%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar aim.prab...@gmail.com wrote: Use : mysql -u root -p (it will prompt for password) and if its not a local host also add -h ipaddress On Thu, Oct
Re: login issue..pls help
hi kumar what is the purpose of 'Grant usage ' command Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Oct 1, 2009 at 3:06 PM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: 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.comwrote: 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.comwrote: 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.com wrote: 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 select,insert,update,delete,create,drop on test.* to 'test'@'%';* after that i login with 'test' user using mysql query browser. but i can able to see other schemas like 'information_Schema' and 'test_schema' created by other. how can i avoid this. i want to see only my default schema.. 2. for created new mysql database or schema what are the privileges are required.. please help.. Best Regards Faizal S GSM : 9840118673 Blog:
[OT] Relocation lookup
Hi all, Given the Social and Political reality in my country (Madagascar), I am obliged to look for a relocation. This is my public profile: http://www.linkedin.com/in/mihaminarakotomandimby Would you be aware of a position I could fit in? Thank you. -- Architecte Informatique chez Blueline/Gulfsat: Administration Systeme, Recherche Developpement +261 34 29 155 34 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
upgrading from 4.1 to 5.4
Hello I have to upgrade many mysql databases from mysql 4 and 4.1 to 5.4 with a large size 2.7 TB All tables use the MyISAM engine. I have to make that update on live system with minimal down time possible. The official method takes too much time. But I’ve read that we can use ‘*dump and reload’* to upgrade directly to 5.1, will it work with 5.4? Also the tables contain many charset? Will they be altered? It's first time that I do that. Are there any better solution and any precaution to take? thanks for your help. monem
Call for articles for Fall Issue of Open Source Database Magazine
Hello everyone, I wanted to take a minute and extend an invitation to anyone who has interest in writing about MySQL or other open source database servers. July brought the first issue of Open Source Database Magazine and it was a resounding success. Our download count has skyrocketed. I was blown away when I checked my stats after the first two days of release and there were almost three times as many downloads as I had ever had of any of the previous issues of MySQL Magazine. Thanks to those who participated by contributing articles; I couldn't have done it without you. The feedback I have received is that the change in direction has been embraced by the open source database community. I am making multiple changes in the magazine but the one that will directly affect authors is that I am increasing the payment for articles. All the details about the coming changes are on an OSDB Magazine blog post here: http://www.osdbzine.net/wordpress/?p=3 I need to hear from you by October the 5th with any proposals and the article needs to be completed and turned in by October the 30th. Looking forward to hearing from you. Keith Murphy -- Editor Open Source Database Magazine http://www.osdbzine.net edi...@osdbzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: upgrading from 4.1 to 5.4
Using mysqldump and loading directly into 5.4 *might* work, but you should never do any of this on your production system without testing. Get another box, start with 4.1 and do the upgrade on a test server -- even test your queries as there a few incompatible changes between 4 and 5. One you know the exact steps you need to take, and how much time it takes, then you can plan the upgrade accordingly on your live system. Regards, Gavin Towey -Original Message- From: monem mysql [mailto:monem.my...@gmail.com] Sent: Thursday, October 01, 2009 9:31 AM To: mysql@lists.mysql.com Subject: upgrading from 4.1 to 5.4 Hello I have to upgrade many mysql databases from mysql 4 and 4.1 to 5.4 with a large size 2.7 TB All tables use the MyISAM engine. I have to make that update on live system with minimal down time possible. The official method takes too much time. But I've read that we can use '*dump and reload'* to upgrade directly to 5.1, will it work with 5.4? Also the tables contain many charset? Will they be altered? It's first time that I do that. Are there any better solution and any precaution to take? thanks for your help. monem The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Nested Joins
Hi;
I'm new to join statements. Here's my python syntax:
cursor.execute('select * from %s left join products on
%s.Item=products.Item (left join categories on
products.Category=categories.ID);' % (client, client))
I believe it's clear how I want to nest, but I don't know how to repair my
syntax.
TIA,
V
RE: Nested Joins
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
2. Give the exact error message
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
5. Explain what you're trying to accomplish.
Regards,
Gavin Towey
-Original Message-
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: Thursday, October 01, 2009 1:34 PM
To: mysql@lists.mysql.com
Subject: Nested Joins
Hi;
I'm new to join statements. Here's my python syntax:
cursor.execute('select * from %s left join products on
%s.Item=products.Item (left join categories on
products.Category=categories.ID);' % (client, client))
I believe it's clear how I want to nest, but I don't know how to repair my
syntax.
TIA,
V
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the original
message.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey gto...@ffn.com wrote:
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
ProgrammingError: (1064, 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 ') left join categories on products.Category=categories.ID)' at
line 1), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on
%s.Item=products.Item left join categories on
products.Category=categories.ID;' % (client, client))
The client in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in
products, where all the information about the products is to be found,
EXCEPT the name of the category. For that, we have to go to the categories
table.
Hope that makes it clear.
TIA,
V
RE: Nested Joins
Victor,
Thank you for the information, that was helpful.
At least part of the problem is the variables you are replacing in that string,
which we can't see.
The statement should be something like:
select * from ben_franklin_planners c join products p on c.Item=p.Item join
categories cat on p.Category=cat.ID
Make your code produce the above, and you should be fine. I suspect you don't
need LEFT JOIN there, an inner join will suffice.
For more info on joins:
http://hashmysql.org/index.php?title=Introduction_to_Joins
For more indepth info:
http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf
http://dev.mysql.com/doc/refman/5.1/en/join.html
Regards,
Gavin Towey
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: Thursday, October 01, 2009 2:25 PM
To: Gavin Towey; mysql@lists.mysql.com
Subject: Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey
gto...@ffn.commailto:gto...@ffn.com wrote:
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
ProgrammingError: (1064, 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 ') left join categories on products.Category=categories.ID)' at line
1), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on %s.Item=products.Item
left join categories on products.Category=categories.ID;' % (client, client))
The client in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in products,
where all the information about the products is to be found, EXCEPT the name of
the category. For that, we have to go to the categories table.
Hope that makes it clear.
TIA,
V
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the original
message.
Re: Nested Joins
Well, your syntax is *exactly* what I had (with a few cosmetic changes).
I've been over the MySQL manual on joins with no luck. I'll read over your
resources tonight. Any other ideas would be appreciated.
Thanks,
V
On Thu, Oct 1, 2009 at 4:49 PM, Gavin Towey gto...@ffn.com wrote:
Victor,
Thank you for the information, that was helpful.
At least part of the problem is the variables you are replacing in that
string, which we can’t see.
The statement should be something like:
select * from ben_franklin_planners c join products p on c.Item=p.Item join
categories cat on p.Category=cat.ID
Make your code produce the above, and you should be fine. I suspect you
don’t need LEFT JOIN there, an inner join will suffice.
For more info on joins:
http://hashmysql.org/index.php?title=Introduction_to_Joins
For more indepth info:
http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf
http://dev.mysql.com/doc/refman/5.1/en/join.html
Regards,
Gavin Towey
*From:* Victor Subervi [mailto:victorsube...@gmail.com]
*Sent:* Thursday, October 01, 2009 2:25 PM
*To:* Gavin Towey; mysql@lists.mysql.com
*Subject:* Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey gto...@ffn.com wrote:
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
ProgrammingError: (1064, 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 ') left join categories on products.Category=categories.ID)' at
line 1), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on
%s.Item=products.Item left join categories on
products.Category=categories.ID;' % (client, client))
The client in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in
products, where all the information about the products is to be found,
EXCEPT the name of the category. For that, we have to go to the categories
table.
Hope that makes it clear.
TIA,
V
--
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the
original message.
RE: Nested Joins
Victor,
Just noticed, the join condition from client to productions should be changed:
select * from ben_franklin_planners c join products p on c.Item=p.ID join
categories cat on p.Category=cat.ID
If you're still getting syntax errors you need to check your variables. Try
assigning the query you're building to a string, then printing it out so you
know *exactly* what you're sending to mysql.
Regards,
Gavin Towey
From: Victor Subervi [mailto:victorsube...@gmail.com]
Sent: Thursday, October 01, 2009 3:04 PM
To: Gavin Towey; mysql@lists.mysql.com
Subject: Re: Nested Joins
Well, your syntax is *exactly* what I had (with a few cosmetic changes). I've
been over the MySQL manual on joins with no luck. I'll read over your resources
tonight. Any other ideas would be appreciated.
Thanks,
V
On Thu, Oct 1, 2009 at 4:49 PM, Gavin Towey
gto...@ffn.commailto:gto...@ffn.com wrote:
Victor,
Thank you for the information, that was helpful.
At least part of the problem is the variables you are replacing in that string,
which we can't see.
The statement should be something like:
select * from ben_franklin_planners c join products p on c.Item=p.Item join
categories cat on p.Category=cat.ID
Make your code produce the above, and you should be fine. I suspect you don't
need LEFT JOIN there, an inner join will suffice.
For more info on joins:
http://hashmysql.org/index.php?title=Introduction_to_Joins
For more indepth info:
http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.pdf
http://dev.mysql.com/doc/refman/5.1/en/join.html
Regards,
Gavin Towey
From: Victor Subervi
[mailto:victorsube...@gmail.commailto:victorsube...@gmail.com]
Sent: Thursday, October 01, 2009 2:25 PM
To: Gavin Towey; mysql@lists.mysql.commailto:mysql@lists.mysql.com
Subject: Re: Nested Joins
On Thu, Oct 1, 2009 at 4:03 PM, Gavin Towey
gto...@ffn.commailto:gto...@ffn.com wrote:
Joins aren't nested like that, unless you use a subquery. I think you just
need to remove the parens around the second join.
I tried that and no go :(
For better help:
1. show the real SQL -- echo the statement. Most people here don't like
looking at app code because your variables could contain anything.
ProgrammingError: (1064, 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 ') left join categories on products.Category=categories.ID)' at line
1), referer: http://13gems.com/global_solutions/spreadsheet_edit.py
2. Give the exact error message
Isn't that the same thing?
3. If there's no error, explain what you expect and what you're getting
4. Include table schema
DESCRIBE `ben_franklin_planners`
ID int(4) unsigned NULL
Item int(4) unsigned NULL
Discount int(2) unsigned NULL
DESCRIBE categories
ID int(3) primary key not NULL auto_increment
Category varchar(20) unique NULL
describe products
ID int(4) primary key not NULL
Category int(3) NULL
Item varchar(20) UNIQUE NULL
Description varchar(255) NULL
UOM varchar(20) NULL
Price float(7,2) NULL
5. Explain what you're trying to accomplish.
cursor.execute('select * from %s left join products on %s.Item=products.Item
left join categories on products.Category=categories.ID;' % (client, client))
The client in this case is ben_franklin_planners
ben_franklin_planners has an item # that is the same as the item # in products,
where all the information about the products is to be found, EXCEPT the name of
the category. For that, we have to go to the categories table.
Hope that makes it clear.
TIA,
V
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the original
message.
The information contained in this transmission may contain privileged and
confidential information. It is intended only for the use of the person(s)
named above. If you are not the intended recipient, you are hereby notified
that any review, dissemination, distribution or duplication of this
communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the original
message.
