Re: Restoring a db with RI enforced
Lou Olsten [EMAIL PROTECTED] wrote: If I'm using mysqldump to dump a database with referential integrity in place, does MySQL build the information in such a way that the referenced tables are loaded first to avoid invalid inserts into a table with a foreign key in place? I'm trying to find an option for mysqldump, and the closest one I see is --disable-keys, but I'm not sure if that's what I need or not. You can turn off foreign key checks with SET FOREIGN_KEY_CHECKS = 0 command. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
Hi, Sorry. My english is not so good. :( I try to explain. I have table1 : ID value -- 1 100 1 101 1 102 1 200 2 100 2 300--- 2 310 | 3 100 | | and table2: | | value | --- | 300 - The result of the query should be from IDs of table1 (In this case 1,3) . The ID 2 is not allowed, because the table2 is the exception table wich is containing the value 300. Michael Stassen [EMAIL PROTECTED] 2004-04-28 06:13 PM To: Zoltan Gyurasits/GYO/COMP/[EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: SQL SELECT HELP Classification: I'm afraid I don't understand. From your first message, it appears you want a list of rows from table1 whose ids do not appear in table2. The query Egor sent you does just that. Did you try it? If, as you say here, that isn't what you want, could you please describe what you do want? Michael [EMAIL PROTECTED] wrote: Thanks your help, but I would like to do the following: If I have is ONE same ID between the two tables, than the result it must be empty. Egor Egorov [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: snip I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( If I've got you right you need LEFT JOIN instead of INNER JOIN. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Win2k Server open to connections from all IPs
Victor Pendleton wrote: If you are going to do this, may I suggest starting MySQL with a non standard port number? Yeah, right. And a hacker will not perform a portscan? And he will not recognize a response from that port (e.g. Host 'hostname' is not allowed to connect to this MySQL server)??? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
I hope it should work: Select table1.ID from table1 left join table2 on table1.value=table2.value where table2.value is null OR if you want distinct IDs Select distinct table1.ID from table1 left join table2 on table1.value=table2.value where table2.value is null though I didn't test it, if it does (or doesn't) let me know Regards Nitin - Original Message - From: [EMAIL PROTECTED] To: Michael Stassen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, April 29, 2004 1:15 PM Subject: Re: SQL SELECT HELP Hi, Sorry. My english is not so good. :( I try to explain. I have table1 : ID value -- 1 100 1 101 1 102 1 200 2 100 2 300--- 2 310 | 3 100 | | and table2: | | value | --- | 300 - The result of the query should be from IDs of table1 (In this case 1,3) . The ID 2 is not allowed, because the table2 is the exception table wich is containing the value 300. Michael Stassen [EMAIL PROTECTED] 2004-04-28 06:13 PM To: Zoltan Gyurasits/GYO/COMP/[EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: SQL SELECT HELP Classification: I'm afraid I don't understand. From your first message, it appears you want a list of rows from table1 whose ids do not appear in table2. The query Egor sent you does just that. Did you try it? If, as you say here, that isn't what you want, could you please describe what you do want? Michael [EMAIL PROTECTED] wrote: Thanks your help, but I would like to do the following: If I have is ONE same ID between the two tables, than the result it must be empty. Egor Egorov [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: snip I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( If I've got you right you need LEFT JOIN instead of INNER JOIN. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL SELECT HELP
Hi, Thanx the answer! I have tried it, but didn't work correctly. The result was 1,2,3 :(( Nitin [EMAIL PROTECTED] 2004-04-29 09:54 AM To: Zoltan Gyurasits/GYO/COMP/[EMAIL PROTECTED] Michael Stassen [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: SQL SELECT HELP Classification: I hope it should work: Select table1.ID from table1 left join table2 on table1.value=table2.value where table2.value is null OR if you want distinct IDs Select distinct table1.ID from table1 left join table2 on table1.value=table2.value where table2.value is null though I didn't test it, if it does (or doesn't) let me know Regards Nitin - Original Message - From: [EMAIL PROTECTED] To: Michael Stassen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, April 29, 2004 1:15 PM Subject: Re: SQL SELECT HELP Hi, Sorry. My english is not so good. :( I try to explain. I have table1 : ID value -- 1 100 1 101 1 102 1 200 2 100 2 300--- 2 310 | 3 100 | | and table2: | | value | --- | 300 - The result of the query should be from IDs of table1 (In this case 1,3) . The ID 2 is not allowed, because the table2 is the exception table wich is containing the value 300. Michael Stassen [EMAIL PROTECTED] 2004-04-28 06:13 PM To: Zoltan Gyurasits/GYO/COMP/[EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: SQL SELECT HELP Classification: I'm afraid I don't understand. From your first message, it appears you want a list of rows from table1 whose ids do not appear in table2. The query Egor sent you does just that. Did you try it? If, as you say here, that isn't what you want, could you please describe what you do want? Michael [EMAIL PROTECTED] wrote: Thanks your help, but I would like to do the following: If I have is ONE same ID between the two tables, than the result it must be empty. Egor Egorov [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: snip I have a query problem. I want to make a query SELECT* FROM table1 INNER JOIN table2 ON table1.id NOT IN table2.id But I can't use the NOT IN expression here. What can i do? I have the MySQL version 4.x I can't use subquery :( If I've got you right you need LEFT JOIN instead of INNER JOIN. SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error dropping databases [in InnoDb]
Hi, I have found this too. I think it is as a result of a change introduced in 4.0.18. From http://www.innodb.com/ibman.php#InnoDB.history : MySQL/InnoDB-4.0.18, February 13, 2004 - Do not allow dropping a table referenced by a FOREIGN KEY constraint, unless the user does SET FOREIGN_KEY_CHECKS=0. The error message here is somewhat misleading 'Cannot delete or update a parent row...', and must be changed in a future version 4.1.x. It's a good change, but I'm guessing that InnoDb is handling a drop database first as if all tables are being dropped in some arbirtary order and hence not respecting foreign keys. One solution is to disable foreign key checks before the drop, and then re-enable them afterwards, or alternatively explicitly drop all tables in the correct order before dropping the database. I wonder if there are any plans to fix this in future versions on InnoDb? Thanks, Mike -Original Message- From: Tom O'Neil [mailto:[EMAIL PROTECTED] Sent: 25 March 2004 22:07 To: [EMAIL PROTECTED] Subject: Error dropping databases I am trying to drop several InnoDB and am unable to do so. I login to the command line client and enter drop database [db_name]; and then get this error: ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails Why am I unable to drop these databases? Tom = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Help with a basic question I can't seem to find an answer.
You will need root privilege in order to install and start MySQL. After the installation, you may have to edit some confs to get MySQL to start automatically at every boot. Thereafter, you can access the database server be creating a user account for Mysql admin (root), plus unlimited number of user accounts. See MySQL Docs for more about the installation: http://dev.mysql.com/doc/mysql/en/Installing.html HTH Babs || -Original Message- || From: Sreekanth Nagisetty [mailto:[EMAIL PROTECTED] || Sent: Wednesday, April 28, 2004 10:32 PM || To: [EMAIL PROTECTED] || Subject: Help with a basic question I can't seem to find an answer. || || Here is a basic question I am struggling to find an answer. I want to run || mysql under solaris. Security is not a great concern to me and only one || application accesses the database locally. I don't have and can't get the || root access to the machine. Also, I can't create mysql user and groups on || this machine. Can someone tell me if it is possible for me to install and || run mysql completely as a normal user on the system? If it is, can you || please give me a rough idea on how to proceed with the installation? I || searched the mysql mailing lists and rest of the internest pretty || extensively ( I think!!) and stil couldn't find a straight answer. Thank you || very much for your time and help. || || _ || Stop worrying about overloading your inbox - get MSN Hotmail Extra Storage! || http://join.msn.com/?pgmarket=en-uspage=hotmail/es2ST=1/go/onm00200362 ave/direct/01/ || || || -- || MySQL General Mailing List || For list archives: http://lists.mysql.com/mysql || To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with a basic question I can't seem to find an answer.
No you don't. Mysql will run perfectly happily as a standard user. If you're installing from an rpm you will however need root privilege unless the rpm is configured as relocatable - and the standard mysql ones aren't. What you need to do is get yourself the source tarball (.gz) and not the rpm. Install it locally, and build it. You'll need to set --prefix=local path to which you have access and if port 3306 is blocked you'll need to set --tcp-port=port Run the build and install as standard from this point which will install mysql into you're local directory. Configure you're data dir and run the install_db script. I suggest you copy one of the /share/dbsize.cnf files To a my.cnf in the data dir and edit it appropriately and use --defaults-file=data dir/my.cnf as a parameter to the install_db script. Then set your path so it picks up install_dir/bin and run nohup mysqld_safe to start the mysqld as you. The rest of the admin/user configuration is standard. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: B. Fongo [mailto:[EMAIL PROTECTED] Sent: 29 April 2004 11:45 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Mysql. Com Subject: FW: Help with a basic question I can't seem to find an answer. You will need root privilege in order to install and start MySQL. After the installation, you may have to edit some confs to get MySQL to start automatically at every boot. Thereafter, you can access the database server be creating a user account for Mysql admin (root), plus unlimited number of user accounts. See MySQL Docs for more about the installation: http://dev.mysql.com/doc/mysql/en/Installing.html HTH Babs || -Original Message- || From: Sreekanth Nagisetty [mailto:[EMAIL PROTECTED] || Sent: Wednesday, April 28, 2004 10:32 PM || To: [EMAIL PROTECTED] || Subject: Help with a basic question I can't seem to find an answer. || || Here is a basic question I am struggling to find an answer. I want to run || mysql under solaris. Security is not a great concern to me and only one || application accesses the database locally. I don't have and can't get the || root access to the machine. Also, I can't create mysql user and groups on || this machine. Can someone tell me if it is possible for me to install and || run mysql completely as a normal user on the system? If it is, can you || please give me a rough idea on how to proceed with the installation? I || searched the mysql mailing lists and rest of the internest pretty || extensively ( I think!!) and stil couldn't find a straight answer. Thank you || very much for your time and help. || || _ || Stop worrying about overloading your inbox - get MSN Hotmail Extra Storage! || http://join.msn.com/?pgmarket=en-uspage=hotmail/es2ST=1/go/onm00200362 ave/direct/01/ || || || -- || MySQL General Mailing List || For list archives: http://lists.mysql.com/mysql || To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump
The oracle and MySQL create syntax is not completely compabitable. You will have to identify and correct the errors. For example text data type in MySQL may need to be either VARCHAR(N) or CLOB. ... -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 4/28/04 11:37 PM Subject: mysqldump hi, i have a small table with one field that i created to test.i took a backup of database using this stmt: mysqldump --user= --password= db db.sql now i am trying to run this sql file in oracle sqlplus and i am not able to . it doesnt create the table again and i get syntax errors. How can i run this in Oracle??? liz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[client] var not working
Per a response from Victoria (thanks, BTW!) I see that I can reload my InnoDB RI's data by turning off SET FOREIGN_KEY_CHECKS = 0 during the restore. I then tried to go to the machine where the dumps were going to be restored and set this variable in the [client] section of my.cnf. But when I try to connect after that, I get an: ERROR: unknown variable 'foreign_key_checks=0'. I then tried using: set variable=foreign_key_checks=0 ...but got the same result. Is there a list of variables that I *can* use in the [client] section, or am I just doing something wrong. Thanks, Lou Olsten
Re: SELECT DISTINCT returns an incorrect result with special characters
I've solved the problem starting the server using the option --character-set=dos. The side effect is that searches become accent-sensitive. Best wishes. --- Ricardo [EMAIL PROTECTED] escreveu: I have a problem with SELECT DISTINCT if the target field contains special characters. Example: select MyField from MyTable +--+ | MyField | +--+ | fá | | Fá | | fa | | Fa | | fâ | | Fâ | | fã | | Fã | +--+ select distinct MyField from MyTable +--+ | MyField | +--+ | fá | +--+ MyField is varchar type and MyTable is InnoDB. Is there any way to change this behaviour in MySQL? I use version 4.0.16. Thanks. __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [client] var not working
hi Lou, I believe this is a recent feature so make sure you on on 4.0.18 if possible, I know is was added before 4.0.14. I am not sure this works on 3.23. Ken - Original Message - From: Lou Olsten [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, April 29, 2004 10:38 AM Subject: [client] var not working Per a response from Victoria (thanks, BTW!) I see that I can reload my InnoDB RI's data by turning off SET FOREIGN_KEY_CHECKS = 0 during the restore. I then tried to go to the machine where the dumps were going to be restored and set this variable in the [client] section of my.cnf. But when I try to connect after that, I get an: ERROR: unknown variable 'foreign_key_checks=0'. I then tried using: set variable=foreign_key_checks=0 ...but got the same result. Is there a list of variables that I *can* use in the [client] section, or am I just doing something wrong. Thanks, Lou Olsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [client] var not working
Place the SET FOREIGN_KEY_CHECKS=0 in the beginning of the file that contains the backup or run this command from the command line before you import the file. -Original Message- From: Lou Olsten To: [EMAIL PROTECTED] Sent: 4/29/04 9:38 AM Subject: [client] var not working Per a response from Victoria (thanks, BTW!) I see that I can reload my InnoDB RI's data by turning off SET FOREIGN_KEY_CHECKS = 0 during the restore. I then tried to go to the machine where the dumps were going to be restored and set this variable in the [client] section of my.cnf. But when I try to connect after that, I get an: ERROR: unknown variable 'foreign_key_checks=0'. I then tried using: set variable=foreign_key_checks=0 ...but got the same result. Is there a list of variables that I *can* use in the [client] section, or am I just doing something wrong. Thanks, Lou Olsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [client] var not working
Lou Olsten [EMAIL PROTECTED] wrote: Per a response from Victoria (thanks, BTW!) I see that I can reload my InnoDB RI's data by turning off SET FOREIGN_KEY_CHECKS = 0 during the restore. I then tried to go to the machine where the dumps were going to be restored and set this variable in the [client] section of my.cnf. But when I try to connect after that, I get an: ERROR: unknown variable 'foreign_key_checks=0'. I then tried using: set variable=foreign_key_checks=0 ...but got the same result. Is there a list of variables that I *can* use in the [client] section, or am I just doing something wrong. You should add the following statement to the beginning of the dump file: SET FOREIGN_KEY_CHECKS = 0; or load dump file like: mysql SET FOREIGN_KEY_CHECKS = 0; mysql SOURCE dump_file.sql; mysql SET FOREIGN_KEY_CHECKS = 1; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [client] var not working
At 10:38 -0400 4/29/04, Lou Olsten wrote: Per a response from Victoria (thanks, BTW!) I see that I can reload my InnoDB RI's data by turning off SET FOREIGN_KEY_CHECKS = 0 during the restore. I then tried to go to the machine where the dumps were going to be restored and set this variable in the [client] section of my.cnf. But when I try to connect after that, I get an: ERROR: unknown variable 'foreign_key_checks=0'. I then tried using: set variable=foreign_key_checks=0 ...but got the same result. Is there a list of variables that I *can* use in the [client] section, or am I just doing something wrong. There is an example that shows how to use the variable on this page: http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html I got to this page by going to: http://dev.mysql.com/doc/mysql/en/index.html Then I typed foreign_key_checks into the search box and clicked the Go button. The first page in the list of hits is the one shown above. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to determine how fragmented a innodb table is ?
Is there a simple way to determine how fragmented a Innodb table is ? Thanks, Marc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT returns an incorrect result with special char acters
At 23:28 +0200 4/28/04, Jochem van Dieten wrote: Ricardo wrote: Which collation are you using in MS SQL Server? And in MySQL? MS SQL Server 8 Collation = Latin1_General_CI_AS MySQL 4.0.16 character_set = latin1 The charset determines which characters can occur. The collation determines the sort order. What is the collation in MySQL? http://dev.mysql.com/doc/mysql/en/Charset-server.html That's for 4.1 and up. There isn't such a concept of collation in 4.0.x. Ricardo, you might try SELECT DISTINCT BINARY col_name rather than SELECT DISTINCT col_name. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Command for getting back an auto_increment field?
Hi, i got a table wich its pk is an auto_increment field. I have 10 elements in this table, wich makes the pk_id field = 10. I inserted incorrectly anoter row in this table (the 11th) and imediately deleted it. Although, i'd like that the next time i insert a row in this table, it's index be still 11 (not 12 as mysql would do by default). Is there any fast command i can make to correct the table situation? Thanks, ltcmelo __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urgent: how to increase the database size
At 23:32 +0530 4/28/04, [EMAIL PROTECTED] wrote: Hi, I am trying to insert the records in the database. After the insertion of 3millions records, it fails to insert the records further. Can someone help me in this problem. How do I go and increase the capacity of the database. Moreover if someone can provide me the maximum size of the database which is possible. Databases don't have a maximum size. You'll probably get a better answer if you provide information more specific than it fails. For example, do you get an error message? If so, what is it? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urgent: how to increase the database size
Hi, Unless he is having some weird nightmare that doesn't involve reality, then I am pretty sure he is talking about this in the my.cnf: innodb_data_file_path = ibdata1:400M:autoextend A while back I remember that the autoextend was not in the sample my.cnf files. I think this was around 4.0.1 or something like that. So in that case, if you don't have autoextend, you could have a db with a max size. Or maybe he is just running out of disk space :) Thanks, Eric At 08:29 AM 4/29/2004, Paul DuBois wrote: At 23:32 +0530 4/28/04, [EMAIL PROTECTED] wrote: Hi, I am trying to insert the records in the database. After the insertion of 3millions records, it fails to insert the records further. Can someone help me in this problem. How do I go and increase the capacity of the database. Moreover if someone can provide me the maximum size of the database which is possible. Databases don't have a maximum size. You'll probably get a better answer if you provide information more specific than it fails. For example, do you get an error message? If so, what is it? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Command for getting back an auto_increment field?
At 12:27 -0300 4/29/04, Leandro Melo wrote: Hi, i got a table wich its pk is an auto_increment field. I have 10 elements in this table, wich makes the pk_id field = 10. I inserted incorrectly anoter row in this table (the 11th) and imediately deleted it. Although, i'd like that the next time i insert a row in this table, it's index be still 11 (not 12 as mysql would do by default). Is there any fast command i can make to correct the table situation? If it's a MyISAM table, yes, although you might ask yourself whether there's really any need to do so. (Answer: nearly always not.) This statement will cause the next AUTO_INCREMENT value generated to be one more than the current maximum value in the column: ALTER TABLE tbl_name AUTO_INCREMENT = 1; -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Command for getting back an auto_increment field?
On Thu, 29 Apr 2004 12:27:46 -0300 (ART) Leandro Melo [EMAIL PROTECTED] wrote: Hi, i got a table wich its pk is an auto_increment field. I have 10 elements in this table, wich makes the pk_id field = 10. I inserted incorrectly anoter row in this table (the 11th) and imediately deleted it. Although, i'd like that the next time i insert a row in this table, it's index be still 11 (not 12 as mysql would do by default). Is there any fast command i can make to correct the table situation? ALTER TABLE your_table AUTO_INCREMENT = 11; Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird problem with displaying and retrieving varchar
Hello, I have a table with 3 columns: mysql desc srv_ref_cities; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | state_id | char(2) | YES | | NULL| | | COUNTYCODE | char(3) | | | | | | CITY | varchar(40) | | | | | ++-+--+-+-+---+ And the problem is when i display a query (two of the columns don't show): mysql select * from srv_ref_cities where state_id='08'; +--+++ | state_id | COUNTYCODE | CITY | +--+++ || Acres Green || Aguilar if I have just state_id, countycode it displays just fine. I am thinking that maybe the city is unicode but i can't figure this one out. Has anyone else run into this problem or does anyone have any ideas to help me debug this? I have tried versions 4.0.15 and 5 but the same thing happens in each version. thanks, will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [client] var not working
Thanks, Paul. You appear frustrated that I didn't look in the docs first. I had been to that page (and thoroughly read it) after Victoria's reply. Prior to the reply, I tried searching but wasn't sure what to search for, so didn't find what I was looking for. The real gist of my question was: Is there a list of variables that I *can* use in the [client] section, or am I just doing something wrong. I'm trying to figure out why I couldn't just put it in the [client] section because I'm trying to understand the entire product on a deeper level. I've thoroughly read http://dev.mysql.com/doc/mysql/en/Option_files.html and it appears that this request (knowing all the config file options) has been made by others, so I felt no need to post it there in addition to the others. So back to my question... is looking at the command line options for a program a good way to figure out what can go into the option file or does that not always hold true? SHOW VARIABLES? I noticed that SET FOREIGN_KEY_CHECKS doesn't appear in the SHOW VARIABLES list. The only place I found it was by querying @@session.FOREIGN_KEY_CHECKS. Thanks, Lou - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Lou Olsten [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, April 29, 2004 11:21 AM Subject: Re: [client] var not working At 10:38 -0400 4/29/04, Lou Olsten wrote: Per a response from Victoria (thanks, BTW!) I see that I can reload my InnoDB RI's data by turning off SET FOREIGN_KEY_CHECKS = 0 during the restore. I then tried to go to the machine where the dumps were going to be restored and set this variable in the [client] section of my.cnf. But when I try to connect after that, I get an: ERROR: unknown variable 'foreign_key_checks=0'. I then tried using: set variable=foreign_key_checks=0 ...but got the same result. Is there a list of variables that I *can* use in the [client] section, or am I just doing something wrong. There is an example that shows how to use the variable on this page: http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html I got to this page by going to: http://dev.mysql.com/doc/mysql/en/index.html Then I typed foreign_key_checks into the search box and clicked the Go button. The first page in the list of hits is the one shown above. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Oracle Listener Like Functionality
We are designing a new solution and I have not been able to find functionality anywhere in the MySQL documentation or literature. With an Oracle database you can bring up the database, recover what is needed, all while leaving the listener down. The listener in Oracle just allows clients to connect if it is up. With the listener down no external clients can connect to the DB. Is there similar functionality in MySQL? Right now my work around is to bring up servers by default on a different port than 3306 so that clients aren't connecting to a slave or a master in a 'Not Ready' state. Once the database is recovered then I'm reconfiguring the port. Has anyone else done something similar to this? I'm new to MySQL so any better suggestions would be appreciated. Sam Peterson
Re: Weird problem with displaying and retrieving varchar
Will Richardson wrote: Hello, I have a table with 3 columns: mysql desc srv_ref_cities; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | state_id | char(2) | YES | | NULL| | | COUNTYCODE | char(3) | | | | | | CITY | varchar(40) | | | | | ++-+--+-+-+---+ And the problem is when i display a query (two of the columns don't show): mysql select * from srv_ref_cities where state_id='08'; +--+++ | state_id | COUNTYCODE | CITY | +--+++ || Acres Green || Aguilar Looks to me like your city contains a carriage return followed by some spaces. This is overwriting the state_id and COUNTYCODE on your screen. The data is there. You just can't see it. if I have just state_id, countycode it displays just fine. I am thinking that maybe the city is unicode but i can't figure this one out. Has anyone else run into this problem or does anyone have any ideas to help me debug this? I have tried versions 4.0.15 and 5 but the same thing happens in each version. thanks, will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urgent: how to increase the database size
If you are getting table full error,may be your table size is exceeding the maximum default size allowed. check the table size and default size. You can change the maximum size of the table using Alter table command. Somthing like following,but it is better so back up the database before doing this,otherwise if the new size(MAX_ROWS*AVG_ROW_LENGHT) is smaller ,your data will be lost. examle: ALTER TABLE tbl_name MAX_ROWS=10 AVG_ROW_LENGTH=nnn; ALso check the table status Show table status tablename; kam... --- [EMAIL PROTECTED] wrote: Hi, I am trying to insert the records in the database. After the insertion of 3millions records, it fails to insert the records further. Can someone help me in this problem. How do I go and increase the capacity of the database. Moreover if someone can provide me the maximum size of the database which is possible. Regards, Anup Mahansaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] = Don't worry about the world coming to an end today. It's already tomorrow in Australia. ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°¤º° Do You Yahoo ! ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°¤º° __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [client] var not working
At 12:22 -0400 4/29/04, Lou Olsten wrote: Thanks, Paul. You appear frustrated that I didn't look in the docs first. Um, no. I gave instructions for finding the answer to a question that you were asking. I don't think that translates into any frustration about your use of the manual. I didn't know if you looked there or not. I had been to that page (and thoroughly read it) after Victoria's reply. Prior to the reply, I tried searching but wasn't sure what to search for, so didn't find what I was looking for. The real gist of my question was: Is there a list of variables that I *can* use in the [client] section, or am I just doing something wrong. The only options that can go in the [client] section are those options that are understood by *all* client programs that read option files. Or at least all client programs that you happen to use that read option files. So if myprog1 --help lists an option and myprog2 --help doesn't list an option, you shouldn't put it in [client]. I'm trying to figure out why I couldn't just put it in the [client] section because I'm trying to understand the entire product on a deeper level. I've thoroughly read http://dev.mysql.com/doc/mysql/en/Option_files.html and it appears that this request (knowing all the config file options) has been made by others, so I felt no need to post it there in addition to the others. Run the program with the --help option to find out what options you can list in an option file. If an option is specific to a given program, put it in the option file section named for that specific program. E.g., an option that mysqldump supports but other programs do not should go in the [mysqldump] section. I've noticed those comments in the online manual as well. Personally, I don't really see the point of trying to list every single possible option on that page. The information for particular programs is given in the sections that describe those programs, and makes more sense in that context. So back to my question... is looking at the command line options for a program a good way to figure out what can go into the option file or does that not always hold true? SHOW VARIABLES? I noticed that SET FOREIGN_KEY_CHECKS doesn't appear in the SHOW VARIABLES list. The only place I found it was by querying @@session.FOREIGN_KEY_CHECKS. Well, one point here is that FOREIGN_KEY_CHECKS isn't a client option. But the fact that it doesn't show up in SHOW VARIABLES is, I agree, a problem. There is some talk going on behind the scenes about this, but it isn't solved yet. This variable and other such are listed here: http://dev.mysql.com/doc/mysql/en/SET_OPTION.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order by price?
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2))
as price FROM table ORDER BY price
A note first. The column establishments is an int(10), as you see I am
taking this number through a formula, and I want the result of this formula
to be rounded to two decimal places, and to stick a $ sign in front of it
so I can easily display it on a table.
However here is the problem, in ASC order I get these results:
$110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110 is at
very bottom)
Any help in solving this would be greatly appreciated.
Thanks,
Yoed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by price?
I think when adding the $ the number gets convert to a string. And then
the query orders it by alpha numberic.
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2))
as price FROM table ORDER BY price
A note first. The column establishments is an int(10), as you see I am
taking this number through a formula, and I want the result of this
formula
to be rounded to two decimal places, and to stick a $ sign in front of
it
so I can easily display it on a table.
However here is the problem, in ASC order I get these results:
$110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110 is
at
very bottom)
Any help in solving this would be greatly appreciated.
Thanks,
Yoed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by price?
Yoed Anis wrote:
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2))
as price FROM table ORDER BY price
Hint: you've made this a string comparison using CONCAT, i.e.,
alphabetical, not numerical, sorting rules apply.
How about
SELECT ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2) as price FROM table ORDER BY price;
Then using a printf/sprintf type formatting statement on the application/presentation layer to show to the user?
What is the application language y
A note first. The column establishments is an int(10), as you see I am
taking this number through a formula, and I want the result of this formula
to be rounded to two decimal places, and to stick a $ sign in front of it
so I can easily display it on a table.
However here is the problem, in ASC order I get these results:
$110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110 is at
very bottom)
Any help in solving this would be greatly appreciated.
Thanks,
Yoed
HTH,
Robert Taylor
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by price?
At 12:14 -0500 4/29/04, Yoed Anis wrote:
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2))
as price FROM table ORDER BY price
A note first. The column establishments is an int(10), as you see I am
taking this number through a formula, and I want the result of this formula
to be rounded to two decimal places, and to stick a $ sign in front of it
so I can easily display it on a table.
However here is the problem, in ASC order I get these results:
$110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110 is at
very bottom)
Sounds like you want:
ORDER BY ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Order by price?
Both of you are right Daniel, and Robert.
I've written a PHP class that tabelizes the query directly and I haven't
built in formatting options. Doing so would make the object more complicated
then I would like it to be, so I wanted to see if there is a mysql solution
to this.
I think I'll end up cheating out of this solution by simply ordering by
establishments instead of price, price will always be higher for the higher
# of establishments (I don't have any # of establishments higher than the
maximum of the quadratic equatiosn). Maybe not elegant, but it'll work :-)
Thanks guys,
Best,
Yoed
-Original Message-
From: Robert J Taylor [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 29, 2004 12:23 PM
To: Yoed Anis; [EMAIL PROTECTED]
Subject: Re: Order by price?
Yoed Anis wrote:
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC
),2))
as price FROM table ORDER BY price
Hint: you've made this a string comparison using CONCAT, i.e.,
alphabetical, not numerical, sorting rules apply.
How about
SELECT ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2) as
price FROM table ORDER BY price;
Then using a printf/sprintf type formatting statement on the
application/presentation layer to show to the user?
What is the application language y
A note first. The column establishments is an int(10), as you see I
am taking this number through a formula, and I want the result of this
formula to be rounded to two decimal places, and to stick a $ sign in
front of it so I can easily display it on a table.
However here is the problem, in ASC order I get these results: $110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110 is
at
very bottom)
Any help in solving this would be greatly appreciated.
Thanks,
Yoed
HTH,
Robert Taylor
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Command for getting back an auto_increment field?
If it`s a InnoDB table :-? my case --- Paul DuBois [EMAIL PROTECTED] escreveu: At 12:27 -0300 4/29/04, Leandro Melo wrote: Hi, i got a table wich its pk is an auto_increment field. I have 10 elements in this table, wich makes the pk_id field = 10. I inserted incorrectly anoter row in this table (the 11th) and imediately deleted it. Although, i'd like that the next time i insert a row in this table, it's index be still 11 (not 12 as mysql would do by default). Is there any fast command i can make to correct the table situation? If it's a MyISAM table, yes, although you might ask yourself whether there's really any need to do so. (Answer: nearly always not.) This statement will cause the next AUTO_INCREMENT value generated to be one more than the current maximum value in the column: ALTER TABLE tbl_name AUTO_INCREMENT = 1; -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com = beginner __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by price?
Paul DuBois wrote: *snip* Sounds like you want: ORDER BY ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2) Perfect. (I'm saying, duh over here to myself!) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Order by price?
Paul,
I tried doing that in one trial run, and I wasn't getting the $200 figure so
I gave up on that.
It seems like doing an order by that statement wouldn't have given me the
sort I wanted.
Don't know why that is though - in theory you should be right, I can try it
a bit more if your curious.
Best,
Yoed
-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 29, 2004 12:28 PM
To: Yoed Anis; [EMAIL PROTECTED]
Subject: Re: Order by price?
At 12:14 -0500 4/29/04, Yoed Anis wrote:
I'm having trouble with this query:
SELECT
CONCAT('$',ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC
),2))
as price FROM table ORDER BY price
A note first. The column establishments is an int(10), as you see I
am taking this number through a formula, and I want the result of this
formula to be rounded to two decimal places, and to stick a $ sign in
front of it so I can easily display it on a table.
However here is the problem, in ASC order I get these results: $110.46
$173.86
$208.02
$22.62
$22.62
And in DESC order I get these results:
$90.44
$79.94
$50.10
$48.33
(with the max result 208 for instance at the 3rd from very bottom, 110
is at very bottom)
Sounds like you want:
ORDER BY ROUND(($varA*(establishments^2)+$varB*(establishments)+$varC),2)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Oracle Listener Like Functionality
In the last episode (Apr 29), Sam Peterson said: We are designing a new solution and I have not been able to find functionality anywhere in the MySQL documentation or literature. With an Oracle database you can bring up the database, recover what is needed, all while leaving the listener down. The listener in Oracle just allows clients to connect if it is up. With the listener down no external clients can connect to the DB. Is there similar functionality in MySQL? Start it up with --skip-networking, and do your maintenance via the Unix socket. Another option is to use your OSes firewall software to block port 3306 (this method doesn't require you to bounce mysql to en/disable the port). -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connect string for ASP
Dear All I am using ASP. Can anyone give me the connection code to connect to a MySQL database without using ODBC DSN? Thanks Michael Johnson Director BPEnet EMEA Sun iForce Premier Solution, Sales, Change Management Development Partner Part of the BPEnet Group Limited 13 Austin Friars London EC2N 2JX Tel:. +44 (0)870 922 0247 / (0)207 670 1690 Fax: +44 (0)207 670 1717 E-mail: [EMAIL PROTECTED] Web: www.bpenet.net BPEnet Offices in ~ Sussex, London Dublin
Why are compiled queries 50% slower than parsed queries
OK I've just been completing some benchmarking comparing compiled queries (new in 4.1.1 and above) with standard parsed queries. For both INSERT queries insert into table( col1, col2,col3) values(?,?,?) and SELECT queries select col1, col2,col3 where col1 between ? and ? the compile query is 50% slower Parsed :- Insert total average mean mean max min run records time timetimerecordstime time 0248517.610451 0.000306 0.000239 1008 105588.00 231.00 Select run 0 average query rows timetime 0 61 0.075193 0.007519 17 0.049296 0.004930 2 41 0.092106 0.009211 39 0.048571 0.004857 4 16 0.048425 0.004843 5 62 0.092040 0.009204 66 0.066477 0.006648 71 0.280004 0.028000 88 0.047900 0.004790 94 0.113344 0.011334 100 0.214862 0.021486 110 0.140650 0.014065 121 0.251094 0.025109 130 0.123815 0.012382 140 0.133870 0.013387 156 0.306981 0.030698 160 0.074054 0.007405 170 0.095875 0.009588 181 0.126500 0.012650 198 0.109567 0.010957 Compiled :- Insert total average mean mean max min run records time timetimerecordstime time 024851 18.224807 0.000733 0.000506 1117 303256.00 438.00 Select run 0 average query rows timetime 00 0.086140 0.086140 10 0.062718 0.062718 20 0.109377 0.109377 30 0.062499 0.062499 40 0.062543 0.062543 50 0.109723 0.109723 60 0.085447 0.085447 70 0.132177 0.132177 80 0.062524 0.062524 90 0.133001 0.133001 100 0.526721 0.526721 110 0.525792 0.525792 120 0.524818 0.524818 130 0.133818 0.133818 140 0.525871 0.525871 150 0.527045 0.527045 160 0.085946 0.085946 170 0.108599 0.108599 180 0.526602 0.526602 190 0.119850 0.119850 Anyone care to shed some light on this? Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Connect string for ASP
ConnectStr = Driver={MySQL ODBC 3.51 Driver};server=;DB=;UID=;PWD=
-Original Message-
From: michael johnson
To: [EMAIL PROTECTED] mysql. com
Cc: Joy Johnson
Sent: 4/29/04 1:33 PM
Subject: Connect string for ASP
Dear All
I am using ASP. Can anyone give me the connection code to connect to a
MySQL
database without using ODBC DSN?
Thanks
Michael Johnson
Director
BPEnet EMEA
Sun iForce Premier Solution, Sales, Change Management Development
Partner
Part of the BPEnet Group Limited
13 Austin Friars
London
EC2N 2JX
Tel:. +44 (0)870 922 0247 / (0)207 670 1690
Fax: +44 (0)207 670 1717
E-mail: [EMAIL PROTECTED]
Web: www.bpenet.net
BPEnet Offices in ~ Sussex, London Dublin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Connect string for ASP
You will need to have MyODBC installed. You do not need to create a DSN.
-Original Message-
From: Joy Johnson
To: 'Victor Pendleton'; 'michael johnson '; '[EMAIL PROTECTED] mysql. com '
Sent: 4/29/04 1:50 PM
Subject: RE: Connect string for ASP
I presume I do not need myodbc installed anywhere to do this.
Thanks for the prompt response
BPEnet Sales Support Team
BPEnet EMEA
Sun iForce Premier Solution, Sales, Change Management Development
Partner
Part of the BPE Group Limited
13 Austin Friars
London
EC2N 2JX
Tel: 0870 922 0247 / 0207 670 1690
Fax: 0207 670 1717
E-mail: [EMAIL PROTECTED]
Web: www.bpenet.net
BPEnet Offices in: Sussex, London Dublin
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: 29 April 2004 19:46
To: 'michael johnson '; '[EMAIL PROTECTED] mysql. com '
Cc: 'Joy Johnson '
Subject: RE: Connect string for ASP
ConnectStr = Driver={MySQL ODBC 3.51 Driver};server=;DB=;UID=;PWD=
-Original Message-
From: michael johnson
To: [EMAIL PROTECTED] mysql. com
Cc: Joy Johnson
Sent: 4/29/04 1:33 PM
Subject: Connect string for ASP
Dear All
I am using ASP. Can anyone give me the connection code to connect to a
MySQL database without using ODBC DSN?
Thanks
Michael Johnson
Director
BPEnet EMEA
Sun iForce Premier Solution, Sales, Change Management Development
Partner Part of the BPEnet Group Limited 13 Austin Friars London EC2N
2JX Tel:. +44 (0)870 922 0247 / (0)207 670 1690
Fax: +44 (0)207 670 1717
E-mail: [EMAIL PROTECTED]
Web: www.bpenet.net
BPEnet Offices in ~ Sussex, London Dublin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by price?
I want to count the passing and failing scores of a group of tests so I have a table with a row that describes each test Each test has a minimum passing score. Each test can be run an arbitrary number of times so I have a table of scores, which uses test id as a foreign key. what I would like to do is count the count of fails and passes. ideally in a single query. so the test table lookes like this -- t_id . . . . min_pass_score --- and the score table looks like this: --- score_id t_id score . . . --- I would like to select so the result set looks like this: t_id no_passes no_fails - 1 5 6 2 12 4 3192 34 . . . How can I do this? I have access to Mysql 4.0.18 although the production server still runs 3.23 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Connect string for ASP
The connection I am trying to make is from a website where the hosting
machine will not have Myodbc installed and the database I am trying to
connect to is on another Internet visible server where MySQL is installed
and myodbc is installed.
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: 29 April 2004 19:54
To: 'Joy Johnson '; Victor Pendleton; ''michael johnson ' ';
''[EMAIL PROTECTED] mysql. com ' '
Subject: RE: Connect string for ASP
You will need to have MyODBC installed. You do not need to create a DSN.
-Original Message-
From: Joy Johnson
To: 'Victor Pendleton'; 'michael johnson '; '[EMAIL PROTECTED] mysql. com '
Sent: 4/29/04 1:50 PM
Subject: RE: Connect string for ASP
I presume I do not need myodbc installed anywhere to do this.
Thanks for the prompt response
BPEnet Sales Support Team
BPEnet EMEA
Sun iForce Premier Solution, Sales, Change Management Development
Partner
Part of the BPE Group Limited
13 Austin Friars
London
EC2N 2JX
Tel: 0870 922 0247 / 0207 670 1690
Fax: 0207 670 1717
E-mail: [EMAIL PROTECTED]
Web: www.bpenet.net
BPEnet Offices in: Sussex, London Dublin
-Original Message-
From: Victor Pendleton [mailto:[EMAIL PROTECTED]
Sent: 29 April 2004 19:46
To: 'michael johnson '; '[EMAIL PROTECTED] mysql. com '
Cc: 'Joy Johnson '
Subject: RE: Connect string for ASP
ConnectStr = Driver={MySQL ODBC 3.51 Driver};server=;DB=;UID=;PWD=
-Original Message-
From: michael johnson
To: [EMAIL PROTECTED] mysql. com
Cc: Joy Johnson
Sent: 4/29/04 1:33 PM
Subject: Connect string for ASP
Dear All
I am using ASP. Can anyone give me the connection code to connect to a
MySQL database without using ODBC DSN?
Thanks
Michael Johnson
Director
BPEnet EMEA
Sun iForce Premier Solution, Sales, Change Management Development
Partner Part of the BPEnet Group Limited 13 Austin Friars London EC2N
2JX Tel:. +44 (0)870 922 0247 / (0)207 670 1690
Fax: +44 (0)207 670 1717
E-mail: [EMAIL PROTECTED]
Web: www.bpenet.net
BPEnet Offices in ~ Sussex, London Dublin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
transfer to a file
hi, i have a huge database and i want to transfer the data to a file. can i tranfer the result of a query to a file for eg. if i say select * from tablename; it selects all the tables. but the mysql window is small to see all the records when the database has many records.can i send these records and save it in a file? liz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transfer to a file
On Thu, 29 Apr 2004 16:05:56 -0400 (EDT) [EMAIL PROTECTED] wrote: hi, i have a huge database and i want to transfer the data to a file. can i tranfer the result of a query to a file for eg. if i say select * from tablename; it selects all the tables. but the mysql window is small to see all the records when the database has many records.can i send these records and save it in a file? http://dev.mysql.com/doc/mysql/en/SELECT.html Search for INTO OUTFILE SELECT * INTO OUTFILE '/home/me/data.txt' FROM mytable; (Note, you may have to deal with OS permission issues and you need the FILE MySQL privilege) Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [client] var not working
Paul, please see below... - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Lou Olsten [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, April 29, 2004 1:00 PM Subject: Re: [client] var not working At 12:22 -0400 4/29/04, Lou Olsten wrote: Thanks, Paul. You appear frustrated that I didn't look in the docs first. Um, no. Long day. Sorry for the intimation. ;-) The only options that can go in the [client] section are those options that are understood by *all* client programs that read option files. Or at least all client programs that you happen to use that read option files. So if myprog1 --help lists an option and myprog2 --help doesn't list an option, you shouldn't put it in [client]. Great. Thanks. Run the program with the --help option to find out what options you can list in an option file. If an option is specific to a given program, put it in the option file section named for that specific program. E.g., an option that mysqldump supports but other programs do not should go in the [mysqldump] section. Got it. Thanks. I've noticed those comments in the online manual as well. Personally, I don't really see the point of trying to list every single possible option on that page. The information for particular programs is given in the sections that describe those programs, and makes more sense in that context. Agreed. And especially being that I can use the --help option as stated above. That way it stays consistent across versions, etc. Well, one point here is that FOREIGN_KEY_CHECKS isn't a client option. But the fact that it doesn't show up in SHOW VARIABLES is, I agree, a problem. There is some talk going on behind the scenes about this, but it isn't solved yet. Fair enough! This variable and other such are listed here: http://dev.mysql.com/doc/mysql/en/SET_OPTION.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating Users and Passwords
I thought I had a handle on this, but now I'm all screwed up.
MySQL 4.1.1a-alpha-max-debug-log
Windows 2000 Server
I'm trying to create a user roby with a password of 'foo' with access to everything.
Here's what happens:
- Sign in as root on the local host.
- GRANT ALL PRIVILEGES ON *.* TO roby@'%' IDENTIFIED BY 'foo' WITH GRANT OPTION;
- On same machine, try to login with: mysql -u roby -pfoo -h localhost
- Receive: ERROR 1045 (28000): Access denied for user: 'roby'@'localhost' (Using
password: YES)
I'm wondering why that doesn't work, but here's where I get REALLY confused. I can
then sign in with NO PASSWORD and get into the system:
- mysql -u roby
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 44 to server version: 4.1.1a-alpha-max-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql
Moreoever... there is no entry for roby in the mysql.db database, which I thought was
supposed to happen when I granted everything.
I then issued:
mysql SET PASSWORD FOR roby = PASSWORD('foo');
Query OK, 0 rows affected (0.00 sec)
mysql flush privileges;
Query OK, 0 rows affected (0.10 sec)
But still received:
C:\mysql\binmysql -u roby -pfoo
ERROR 1045 (28000): Access denied for user: 'roby'@'localhost' (Using password: YES)
Any help is appreciated!
Lou
Re: Creating Users and Passwords
In the last episode (Apr 29), Lou Olsten said: I thought I had a handle on this, but now I'm all screwed up. MySQL 4.1.1a-alpha-max-debug-log Windows 2000 Server I'm trying to create a user roby with a password of 'foo' with access to everything. Here's what happens: - Sign in as root on the local host. - GRANT ALL PRIVILEGES ON *.* TO roby@'%' IDENTIFIED BY 'foo' WITH GRANT OPTION; - On same machine, try to login with: mysql -u roby -pfoo -h localhost - Receive: ERROR 1045 (28000): Access denied for user: 'roby'@'localhost' (Using password: YES) Remember that localhost is a special keyword that refers to the unix-domain socket, and will not be matched with a wildcard '%' hostname. Use -h 127.0.0.1 or -h publicip if you are on the same machine as the server and want to test remote privs. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating Users and Passwords
Thanks. Tried it with no luck. Moreover, I get the same results as below when I try to connect with the mysql client from a different machine. Really bizarre behavior. I have a 4.1 alpha running at home on my XP box. I will do some testing there tonight to see if it's something I'm doing unique to that box. Lou - Original Message - From: Dan Nelson [EMAIL PROTECTED] To: Lou Olsten [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, April 29, 2004 5:21 PM Subject: Re: Creating Users and Passwords In the last episode (Apr 29), Lou Olsten said: I thought I had a handle on this, but now I'm all screwed up. MySQL 4.1.1a-alpha-max-debug-log Windows 2000 Server I'm trying to create a user roby with a password of 'foo' with access to everything. Here's what happens: - Sign in as root on the local host. - GRANT ALL PRIVILEGES ON *.* TO roby@'%' IDENTIFIED BY 'foo' WITH GRANT OPTION; - On same machine, try to login with: mysql -u roby -pfoo -h localhost - Receive: ERROR 1045 (28000): Access denied for user: 'roby'@'localhost' (Using password: YES) Remember that localhost is a special keyword that refers to the unix-domain socket, and will not be matched with a wildcard '%' hostname. Use -h 127.0.0.1 or -h publicip if you are on the same machine as the server and want to test remote privs. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting distinct counts
I am using MySQL 4.0.18 and trying to generate a list of the number of row in a table with the same last name, by last name. Is there any way to do this without creating another table? Here is what I am trying: mysql select count(distinct last_name) as LnameCnt, last_name from table_ABC group by last_name; ...such that I could get this: LnameCnt last_name -- 10 Connor 255Jones 2 Puttinger Any ideas? TIA, tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating Users and Passwords
What is the version of your mysql client? If it is pre 4.1 then it is likely being bitten by the password length change from 4.0.xx to 4.1 from 16bytes to 41bytes. You'll need to read the note on passwords in the 4.1 docs. Hope this helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating Users and Passwords
hmmmif you are on the same machine, does it work if you do not specify the '-h localhost'? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting distinct counts
just drop the 'distinct' - should give you what you want mysql select count(last_name) as LnameCnt, last_name from table_ABC group by last_name; hth steve [EMAIL PROTECTED] wrote: I am using MySQL 4.0.18 and trying to generate a list of the number of row in a table with the same last name, by last name. Is there any way to do this without creating another table? Here is what I am trying: mysql select count(distinct last_name) as LnameCnt, last_name from table_ABC group by last_name; ...such that I could get this: LnameCnt last_name -- 10 Connor 255Jones 2 Puttinger Any ideas? TIA, tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JDBC ResultSet exception
Dear All
I work on a small application with database support. For development I use
the MySQL database which is just doing fine. During testing I experienced a,
in my opinion, strange bug.
I have the following source code:
Connection conn = p_GetConnectionFromPool();
try {
// Try to lock the database connection to have exclusive access for
table locks
synchronized (conn) {
Statement stmt = null;
ResultSet rSet = null;
try {
// Create statement from connection
stmt = conn.createStatement();
// First, read the configured timeout time
rSet = stmt.executeQuery(select v from config where
k='time');
rSet.next();
String ttl = rSet.getString(v);
rSet.close();
rSet = null;
rSet = stmt.executeQuery(show databases);
while (rSet.next()) {
// Do something in here
}
rSet.close();
} catch (SQLException Ex) {
// Notify about exception
} finally {
// Do some cleanup work
if (rSet != null) {
try { rSet.close(); } catch (SQLException
Ignore) {}
rSet = null;
}
if (stmt != null) {
try { stmt.close(); } catch (SQLException
Ignore) {}
stmt = null;
}
}
}
} finally {
// Place the connection back in the connection pool
if (conn != null) { p_Connections.push(conn); }
}
Now the problem I encounter:
The first query (select v from config where k='time') is executed without
any problems. I can read the returned value and save in 'ttl'. Then, I close
this result set and leave it to the garbage collector. Next, I want to use
the same variable to take the result set of another query (show databases)
which is executed as well. But then, when I execute while (rSet.next()) I
get the following exception:
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:4579)
at com.mysql.jdbc.ResultSet.next(ResultSet.java:2423)
at
net.sos.web.services.mapping.CMySQLMappingService$1.run(CMySQLMappingService
.java:323)
at java.lang.Thread.run(Unknown Source)
while (rSet.next()) is on line 323 in an anonymous class implementation of
java.lang.Runnable, but I think this is not the reason for the exception.
I use Connection/J in version 3.1.1alpha and MySQL 5.0.0alpha (I have to
because of some features unique to MySQL 5). The JDK I use is 1.4.2_02
I hope someone can help me since I have really no clue why this is
happening. Temporarily I don't close the result set but this would result in
a lot of memory consumption in my system.
Thanks
Bjoern
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why are compiled queries 50% slower than parsed queries
Kevin, I've been using 4.1.1 since it was released and I've never heard of compiled queries. Are you talking about prepared statements? Donny -Original Message- From: Kevin Cowley [mailto:[EMAIL PROTECTED] Sent: Thursday, April 29, 2004 1:35 PM To: [EMAIL PROTECTED] Mysql. Com Subject: Why are compiled queries 50% slower than parsed queries OK I've just been completing some benchmarking comparing compiled queries (new in 4.1.1 and above) with standard parsed queries. For both INSERT queries insert into table( col1, col2,col3) values(?,?,?) and SELECT queries select col1, col2,col3 where col1 between ? and ? the compile query is 50% slower Parsed :- Insert total average mean mean max min run records time timetimerecordstime time 0248517.610451 0.000306 0.000239 1008 105588.00 231.00 Select run 0 average query rows timetime 0 61 0.075193 0.007519 17 0.049296 0.004930 2 41 0.092106 0.009211 39 0.048571 0.004857 4 16 0.048425 0.004843 5 62 0.092040 0.009204 66 0.066477 0.006648 71 0.280004 0.028000 88 0.047900 0.004790 94 0.113344 0.011334 100 0.214862 0.021486 110 0.140650 0.014065 121 0.251094 0.025109 130 0.123815 0.012382 140 0.133870 0.013387 156 0.306981 0.030698 160 0.074054 0.007405 170 0.095875 0.009588 181 0.126500 0.012650 198 0.109567 0.010957 Compiled :- Insert total average mean mean max min run records time timetimerecordstime time 024851 18.224807 0.000733 0.000506 1117 303256.00 438.00 Select run 0 average query rows timetime 00 0.086140 0.086140 10 0.062718 0.062718 20 0.109377 0.109377 30 0.062499 0.062499 40 0.062543 0.062543 50 0.109723 0.109723 60 0.085447 0.085447 70 0.132177 0.132177 80 0.062524 0.062524 90 0.133001 0.133001 100 0.526721 0.526721 110 0.525792 0.525792 120 0.524818 0.524818 130 0.133818 0.133818 140 0.525871 0.525871 150 0.527045 0.527045 160 0.085946 0.085946 170 0.108599 0.108599 180 0.526602 0.526602 190 0.119850 0.119850 Anyone care to shed some light on this? Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
libmysql setting it's own signal handlers?
I have a program that is using (via a front end library) libmysql. If I set up a signal handler before I initialize libmysql, my signal handler is not called when that signal is sent to the process. If I move the line of code that sets the signal handler to *after* the line that initializes libmysql, my signal handler works fine. What does libmysql do to the signal handlers when it initializes? I don't have the source for it in front of my, or I would probably go digging my self. Running up against a deadline as it is. :) Thanks! j- k- -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JDBC ResultSet exception
On Thu, 29 Apr 2004 23:59:05 +0200 Bjoern Wuest [EMAIL PROTECTED] wrote: Dear All I work on a small application with database support. For development I use the MySQL database which is just doing fine. During testing I experienced a, in my opinion, strange bug. I have the following source code: [snip] You might have better luck with the MySQL/Java mailing list: http://lists.mysql.com/list.php?list=javasub=1 I think it's a fairly high traffic list with more Java gurus than this list. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting distinct counts (solved)
...blind as a bat, it was right in front of me in basic SQL blush. Thank you kindly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating Users and Passwords
At 16:21 -0500 4/29/04, Dan Nelson wrote: In the last episode (Apr 29), Lou Olsten said: I thought I had a handle on this, but now I'm all screwed up. MySQL 4.1.1a-alpha-max-debug-log Windows 2000 Server I'm trying to create a user roby with a password of 'foo' with access to everything. Here's what happens: - Sign in as root on the local host. - GRANT ALL PRIVILEGES ON *.* TO roby@'%' IDENTIFIED BY 'foo' WITH GRANT OPTION; - On same machine, try to login with: mysql -u roby -pfoo -h localhost - Receive: ERROR 1045 (28000): Access denied for user: 'roby'@'localhost' (Using password: YES) Remember that localhost is a special keyword that refers to the unix-domain socket, and will not be matched with a wildcard '%' hostname. Oh? More likely the symptoms are due to not having deleted the anonymous-user accounts. One of those accounts has a hostname of 'localhost', which is more specific than '%', and hostname matching happens before username matching. http://dev.mysql.com/doc/mysql/en/Connection_access.html Use -h 127.0.0.1 or -h publicip if you are on the same machine as the server and want to test remote privs. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating Users and Passwords
Lou Olsten wrote:
I thought I had a handle on this, but now I'm all screwed up.
MySQL 4.1.1a-alpha-max-debug-log
Windows 2000 Server
I'm trying to create a user roby with a password of 'foo' with access to everything. Here's what happens:
- Sign in as root on the local host.
- GRANT ALL PRIVILEGES ON *.* TO roby@'%' IDENTIFIED BY 'foo' WITH GRANT OPTION;
That's correct.
- On same machine, try to login with: mysql -u roby -pfoo -h localhost
- Receive: ERROR 1045 (28000): Access denied for user: 'roby'@'localhost'
(Using password: YES)
First, note that localhost is the default, so -h localhost is unnecessary,
though it shouldn't hurt.
You need to read http://dev.mysql.com/doc/mysql/en/Connection_access.html.
The gist is that [EMAIL PROTECTED] matching is done host first, then user. If
more than one host matches, the most specific wins. So, when conecting as
roby from localhost, the anonymous user ''@localhost is a better match than
[EMAIL PROTECTED], because the host part is more specific. This bites a lot of people.
Most, I think, solve this by deleting the anonymous users.
mysql -u root -p mysql
mysql DELETE FROM user WHERE User='';
mysql FLUSH PRIVILEGES;
Another possibility is that you have a [EMAIL PROTECTED] user, who would also
trump [EMAIL PROTECTED] when connecting from localhost. You could check with
SELECT User, Host FROM user WHERE User='roby';
Then either drop that user or make his privileges match.
I'm wondering why that doesn't work, but here's where I get REALLY
confused. I can then sign in with NO PASSWORD and get into the system:
By default, the anonymous user has no password. You can check. Once you're
in, enter
SELECT CURRENT_USER();
to see who mysql believes you are.
- mysql -u roby
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 44 to server version: 4.1.1a-alpha-max-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql
Moreoever... there is no entry for roby in the mysql.db database, which I
thought was supposed to happen when I granted everything.
No. The db table holds db-specific privileges. Global privileges (*.*) go
in the user table.
I then issued:
mysql SET PASSWORD FOR roby = PASSWORD('foo');
Query OK, 0 rows affected (0.00 sec)
mysql flush privileges;
Query OK, 0 rows affected (0.10 sec)
You didn't specify a host, so this defaults to setting the password for
[EMAIL PROTECTED], which won't help if you're actually connecting as [EMAIL PROTECTED] or
''@localhost.
But still received:
C:\mysql\binmysql -u roby -pfoo
ERROR 1045 (28000): Access denied for user: 'roby'@'localhost' (Using password: YES)
Any help is appreciated!
Lou
Finally, are you really sure you want someone to be able to connect as
(effective) root from *anywhere* on the net? Personally, I restrict that
level of access to localhost only. If you really need to be able to
administer mysql remotely, I'd strongly recommend you make the host part as
specific as you can. Maybe [EMAIL PROTECTED] or [EMAIL PROTECTED]
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating Users and Passwords
Dan Nelson wrote: In the last episode (Apr 29), Lou Olsten said: I thought I had a handle on this, but now I'm all screwed up. MySQL 4.1.1a-alpha-max-debug-log Windows 2000 Server I'm trying to create a user roby with a password of 'foo' with access to everything. Here's what happens: - Sign in as root on the local host. - GRANT ALL PRIVILEGES ON *.* TO roby@'%' IDENTIFIED BY 'foo' WITH GRANT OPTION; - On same machine, try to login with: mysql -u roby -pfoo -h localhost - Receive: ERROR 1045 (28000): Access denied for user: 'roby'@'localhost' (Using password: YES) Remember that localhost is a special keyword that refers to the unix-domain socket, and will not be matched with a wildcard '%' hostname. Use -h 127.0.0.1 or -h publicip if you are on the same machine as the server and want to test remote privs. This is incorrect. You are right that localhost is the unix socket, but % will match localhost if there is not a more specific alternative. The problem is that [EMAIL PROTECTED] matching is done host first, and the default mysql install creates an anonymous user ''@localhost. So, if you have entries for ''@localhost and [EMAIL PROTECTED], it's ''@localhost which matches when joe connects from localhost. If [EMAIL PROTECTED] is the only possible match, however (no entries for ''@localhost or [EMAIL PROTECTED]), then it will match. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating Users and Passwords
A HUGE thank you to everyone who helped me with this. Everything is working
as expected now. That connection stuff (along with the blank user entry)
got me!!!
Finally, are you really sure you want someone to be able to connect as
(effective) root from *anywhere* on the net? Personally, I restrict that
level of access to localhost only. If you really need to be able to
administer mysql remotely, I'd strongly recommend you make the host part
as
specific as you can. Maybe [EMAIL PROTECTED] or [EMAIL PROTECTED]
Thanks for the tip and when we move to production, we definitely will have
tighter security. Right now I'm just laying the groundwork and educating
everyone (scary thought) so we can move to production with as little pain as
possible.
Thanks again.
Lou
- Original Message -
From: Michael Stassen [EMAIL PROTECTED]
To: Lou Olsten [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, April 29, 2004 9:34 PM
Subject: Re: Creating Users and Passwords
Lou Olsten wrote:
I thought I had a handle on this, but now I'm all screwed up.
MySQL 4.1.1a-alpha-max-debug-log
Windows 2000 Server
I'm trying to create a user roby with a password of 'foo' with access to
everything. Here's what happens:
- Sign in as root on the local host.
- GRANT ALL PRIVILEGES ON *.* TO roby@'%' IDENTIFIED BY 'foo' WITH GRANT
OPTION;
That's correct.
- On same machine, try to login with: mysql -u roby -pfoo -h localhost
- Receive: ERROR 1045 (28000): Access denied for user:
'roby'@'localhost'
(Using password: YES)
First, note that localhost is the default, so -h localhost is
unnecessary,
though it shouldn't hurt.
You need to read
http://dev.mysql.com/doc/mysql/en/Connection_access.html.
The gist is that [EMAIL PROTECTED] matching is done host first, then user. If
more than one host matches, the most specific wins. So, when conecting as
roby from localhost, the anonymous user ''@localhost is a better match
than
[EMAIL PROTECTED], because the host part is more specific. This bites a lot of
people.
Most, I think, solve this by deleting the anonymous users.
mysql -u root -p mysql
mysql DELETE FROM user WHERE User='';
mysql FLUSH PRIVILEGES;
Another possibility is that you have a [EMAIL PROTECTED] user, who would also
trump [EMAIL PROTECTED] when connecting from localhost. You could check with
SELECT User, Host FROM user WHERE User='roby';
Then either drop that user or make his privileges match.
I'm wondering why that doesn't work, but here's where I get REALLY
confused. I can then sign in with NO PASSWORD and get into the system:
By default, the anonymous user has no password. You can check. Once
you're
in, enter
SELECT CURRENT_USER();
to see who mysql believes you are.
- mysql -u roby
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 44 to server version:
4.1.1a-alpha-max-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql
Moreoever... there is no entry for roby in the mysql.db database, which
I
thought was supposed to happen when I granted everything.
No. The db table holds db-specific privileges. Global privileges (*.*)
go
in the user table.
I then issued:
mysql SET PASSWORD FOR roby = PASSWORD('foo');
Query OK, 0 rows affected (0.00 sec)
mysql flush privileges;
Query OK, 0 rows affected (0.10 sec)
You didn't specify a host, so this defaults to setting the password for
[EMAIL PROTECTED], which won't help if you're actually connecting as [EMAIL
PROTECTED]
or
''@localhost.
But still received:
C:\mysql\binmysql -u roby -pfoo
ERROR 1045 (28000): Access denied for user: 'roby'@'localhost' (Using
password: YES)
Any help is appreciated!
Lou
Finally, are you really sure you want someone to be able to connect as
(effective) root from *anywhere* on the net? Personally, I restrict that
level of access to localhost only. If you really need to be able to
administer mysql remotely, I'd strongly recommend you make the host part
as
specific as you can. Maybe [EMAIL PROTECTED] or [EMAIL PROTECTED]
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: libmysql setting it's own signal handlers?
Joshua J. Kugler wrote: I have a program that is using (via a front end library) libmysql. If I set up a signal handler before I initialize libmysql, my signal handler is not called when that signal is sent to the process. If I move the line of code that sets the signal handler to *after* the line that initializes libmysql, my signal handler works fine. What does libmysql do to the signal handlers when it initializes? I don't have the source for it in front of my, or I would probably go digging my self. Running up against a deadline as it is. :) mysql client library traps SIGPIPE to deal with some weird threading issues. The problem is that is some cases, a threaded program might get a spurious SIGPIPE, and then the program crashes if it is not handled. What you are doing should be just fine - all that happens inside is that SIGPIPE is ignored. The only problem is if you really want to handle SIGPIPE while in the middle of a mysql call. In that case, recomple the client without --enable-thread-safe-client or hack the source. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Build mysql 4.0.18 on Aix 5.2
erri wrote: I'm trying to compile 4.0.18 version on Aix 5.2 ML2 (IBM 7044-270 with Two cpu Power3-II), with gcc-2.95 with this parameters(note in mysql.com): CC=gcc -pipe -mcpu=powerpc -Wa,-many \ CXX=gcc -pipe -mcpu=powerpc -Wa,-many \ CXXFLAGS=-felide-constructors -fno-exceptions -fno-rtti \ /configure --prefix=/usr/local/mysql --with-low-memory But i get: gcc -pipe -mcpu=powerpc -Wa,-many -DHAVE_CONFIG_H -I. -I. -I.. -I./../includ e -I../include -I..-O3 -DDBUG_OFF -c `test -f 'my_print_defaults.c' || echo './'`my_print_defaults.c my_print_defaults.c:52: incompatible types in initialization my_print_defaults.c:52: initializer element is not constant my_print_defaults.c:52: (near initialization for `my_long_options[6].str_values') gmake[1]: *** [my_print_defaults.o] Error 1 gmake[1]: Leaving directory `/software/mysql-4.0.18/extra' gmake: *** [all-recursive] Error 1 make: 1254-004 El código de error del último mandato es 2. Any idea ? At first glance, looks like a broken compiler. Very stranges that gcc would break like this. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why are compiled queries 50% slower than parsed queries
Kevin Cowley wrote: OK I've just been completing some benchmarking comparing compiled queries (new in 4.1.1 and above) with standard parsed queries. For both INSERT queries insert into table( col1, col2,col3) values(?,?,?) and SELECT queries select col1, col2,col3 where col1 between ? and ? the compile query is 50% slower Parsed :- Insert total average mean mean max min run records time timetimerecordstime time 0248517.610451 0.000306 0.000239 1008 105588.00 231.00 Select run 0 average query rows timetime 0 61 0.075193 0.007519 17 0.049296 0.004930 2 41 0.092106 0.009211 39 0.048571 0.004857 4 16 0.048425 0.004843 5 62 0.092040 0.009204 66 0.066477 0.006648 71 0.280004 0.028000 88 0.047900 0.004790 94 0.113344 0.011334 100 0.214862 0.021486 110 0.140650 0.014065 121 0.251094 0.025109 130 0.123815 0.012382 140 0.133870 0.013387 156 0.306981 0.030698 160 0.074054 0.007405 170 0.095875 0.009588 181 0.126500 0.012650 198 0.109567 0.010957 Compiled :- Insert total average mean mean max min run records time timetimerecordstime time 024851 18.224807 0.000733 0.000506 1117 303256.00 438.00 Select run 0 average query rows timetime 00 0.086140 0.086140 10 0.062718 0.062718 20 0.109377 0.109377 30 0.062499 0.062499 40 0.062543 0.062543 50 0.109723 0.109723 60 0.085447 0.085447 70 0.132177 0.132177 80 0.062524 0.062524 90 0.133001 0.133001 100 0.526721 0.526721 110 0.525792 0.525792 120 0.524818 0.524818 130 0.133818 0.133818 140 0.525871 0.525871 150 0.527045 0.527045 160 0.085946 0.085946 170 0.108599 0.108599 180 0.526602 0.526602 190 0.119850 0.119850 Anyone care to shed some light on this? Kevin: First, check your code to make sure that you actually prepare once, and then run many times, rather than prepare each time. If that is indeed the case, send your code to MySQL developers and have them check out the performance bug. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
