RE: sum() using group, and duplicates problems...
Don This is more help than I ever anticipated, and I certainly thank you very much. Let me digest / re-program this. If there's anything I can do you for, just let me know. Thanks lots! Regards, Kelly W. Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Don Read [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 8:44 PM To: Black, Kelly W [PCS] Cc: [EMAIL PROTECTED] Subject: RE: sum() using group, and duplicates problems... On 26-Mar-2003 Black, Kelly W [PCS] wrote: Thanks again Serge and Don, Don not to sound like an idiot but what would you recommend on adding a unique key. I understand the idea, but didn't want to corrupt tables trying to get it right. Serge, I knew DISTINCT operated on whole lines, I was counting on that but as you can see, I didn't know about the sum *before* the distinction... Thanks guys, I will try these and see if I can fix it. It's tough to say without the context. So I'll make some assumptions and guesses. Kelly, you get to figure out if they are valid for you. Let's look at the query: SELECT DISTINCT measurement, sum(val), pcfver, hour, release FROM pcf WHERE release = curdate()-1 group by measurement, hour; --- I'll assume 'release' is a DATE and 'hour' is a TINYINT UNSIGNED, meaning the hour of 'release' day when the entry was recorded. I'll further assume 'measurement' is a discrete measurement sensor. Example: a thermocouple at the bottom of an exhaust stack is a different measurement # than the TC at the top of the stack. And 'pcfvar' is some nominal or ideal value (but it could be some max/min acceptable). The 'val' field is, of course, your independent y-variable. --- We've already discussed/fixed the 'curdate()-1' error. And since yure going to constrain the table to UNIQUE entries, let's get rid of the DISTINCT function. So, you're left with the X-axis (non-independent) variables: 'measurement', 'release', 'hour', 'pcfvar'. The 'measurement' is a no-brainer: the temp at the bottom of the chimney has nothing to do with the top (in the database sense). 'release' ? another no-brainer. You're reporting by date. So far the index is : UNIQUE KEY mr (measurement, release); 'pcfvar' is an iffy. It might change by the date or hour. Example: aircraft taking off before 7am must be quieter than 90dB, but can hit 105 dB afterwards (yeah, I made up the numbers). UNIQUE KEY mrp (measurement, release, pcfvar); 'hour' gets a bit more tricky. If pinkware (humans) are recording the data, you've got to consider the case of entries at :01 and then :59 in the same hour ... And which is more valid ? INSERT IGNORE INTO ... or REPLACE INTO ... ? Or you could allow both readings and AVG() indstead of SUM() ... Depending on what you'll allow, a full constraint would be: UNIQUE KEY mrph (measurement, release, pcfvar, hour); --- Kelly. You owe me two virtual beers. Shiner Bock or Miller High-Life tall-boys will be fine. Invoice to follow. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: granting privileges using wildcards
Just run mysql in it's own little jail with --user=mysql # or some username you add to the tables... Regards, Kelly Black -Original Message- From: Dimitar Haralanov [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 4:18 PM To: [EMAIL PROTECTED] Subject: Re: granting privileges using wildcards On Wed, 26 Mar 2003 14:37:33 -0800 Jennifer Goodie wrote: To answer the original question, I have tried a lot of different ways, but the only solution I have found is granting on the entire database or specifing each table in the tables_priv table. I go with the second option because although it is more work I like only allowing as much access as needed. The wild cards just don't seem to work in this instance. Has anybody had any success with it? This solution works, of course, but has one unwanted pre-requisite. I have to be the root MySQL user (or a user that has privileges to modify that table) to modify table_priv. The code that I am working on will be running on a Apache server and I do not want to have the web server login to MySQL using a user with that high privileges. It is a security issue!! :) I guess, I will have to work arround this!! Thanx -- Mitko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: About database design
Richard Dice is a friend of mine, and has done a wonderful tutorial on Choosing the right database system that I found marvelous, at webmonkey: http://hotwired.lycos.com/webmonkey/backend/databases/tutorials/tutorial1.ht ml Regards, Kelly Black Sprint PCS Performance Engineering Irvine, Ca. 92612 949-623-5417 714-722-9727 (PCS) Linux was very clearly the answer, but what was the question again? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, March 28, 2003 2:40 PM To: [EMAIL PROTECTED] Subject: About database design Where can a newbie go to learn how design and build a database quick. Thanks David -- 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: granting privileges using wildcards
Usually there's a little more administrative work to limiting usage...but it's up to you. Regards, ~KB -Original Message- From: Jennifer Goodie [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 2:38 PM To: Black, Kelly W [PCS]; 'Dimitar Haralanov'; [EMAIL PROTECTED] Subject: RE: granting privileges using wildcards That would grant the user select on everything in every database no matter what host they are coming from. He wants to only grant on specific tables, and did not mention anything about allowing from all hosts. To answer the original question, I have tried a lot of different ways, but the only solution I have found is granting on the entire database or specifing each table in the tables_priv table. I go with the second option because although it is more work I like only allowing as much access as needed. The wild cards just don't seem to work in this instance. Has anybody had any success with it? -Original Message- From: Black, Kelly W [PCS] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 11:14 AM To: 'Dimitar Haralanov'; [EMAIL PROTECTED] Subject: RE: granting privileges using wildcards I think this might do what you want, but then you will be required to log in with the -p syntax... GRANT SELECT on *.* TO yourlogin@'%' IDENTIFIED BY somepassword; FLUSH PRIVILEGES; mysql -u youruserid -p -h hostname dbasename Regards, Kelly Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Dimitar Haralanov [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 11:03 AM To: [EMAIL PROTECTED] Subject: granting privileges using wildcards Hi, I have been trying to find information on the following question but have been unable to do so: Is it possible to grant options to a user using wildcards. For example: let's say that I have a database named 'db', and a user 'admin' who has full privileges. The user 'admin' will add and delete tables to 'db' but any table that is added/deleted will have the following name format 'table_[A-z0-9]'. What I would like to be able to do is grant select privileges to any and all of the tables 'table_*' to a user 'non_admin'. Is this possible and if it is, how can I do that? Thank you for your help! -- Mitko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql scripts
Also you can do mysql -u userid -p -h hostname dbname /path/to/your/script.sql Regards, Kelly Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Anderson Pereira Ataides [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 5:37 AM To: [EMAIL PROTECTED] Subject: Re: mysql scripts Dan, you can use: mysql -e YOUR SQL STATEMENT []s Anderson Pereira Ataides Em Qui 27 Mar 2003 08:03, Daniel Kiss escreveu: Hi all, I have a question about mysql client. How can I use it in such a way that it just executes a script on a specified database and then exists? Thanks, Dan -- 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: sum() using group, and duplicates problems...
Don Thanks. I will have to change them and try that. I had been bouncing back and forth between decimal and numeric but wasn't sure which was working better. I still think it might have to do with my sql query. Here's an example as you previously mentioned... SELECT DISTINCT measurement, sum(val), pcfver, hour, release FROM pcf WHERE release = curdate()-1 group by measurement, hour; The -sql runs fine, but there are hours in which I receive duplicated input data. I have filtered as much as I can, but was really needing help from the database driver to eliminate the rest. Thanks for your help. Regards, Kelly W. Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Don Read [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 8:30 PM To: Black, Kelly W [PCS] Cc: Mysql (E-mail) Subject: RE: sum() using group, and duplicates problems... On 25-Mar-2003 Black, Kelly W [PCS] wrote: On 25-Mar-2003 Black, Kelly W [PCS] wrote: Hello. Sorry for the long post. This has been a big problem for me, and I hope someone will help... I have a long standing problem with an MySQL query. Or perhaps a couple of sql query problems... Firstly I have been told I cannot use sum() on a value while using group by parm because it's not accurate. This is obvious as the result sets are often quite wrong... I'm not sure where you got that. I've never seen it. See attached screen shot. It's common knowledge that when comparing six or eight there's no problem. But once you move into the millions there's quite a margin for error. mysql select .3334455 * 94343473264278464 as test; +---+ | test | +---+ | 314478244215319756800.000 | +---+ 1 row in set (0.00 sec) Since you'll only get 15 significant digits w/ a 64bit float, you're in for considerable margins of error. MySQL is _NOT_ an arbitrary precision calculator. If you need that kind of precision, you should be working with DECIMAL(). Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sum() using group, and duplicates problems...
Thanks again Serge and Don, Don not to sound like an idiot but what would you recommend on adding a unique key. I understand the idea, but didn't want to corrupt tables trying to get it right. Serge, I knew DISTINCT operated on whole lines, I was counting on that but as you can see, I didn't know about the sum *before* the distinction... Thanks guys, I will try these and see if I can fix it. Regards, Kelly Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Serge Paquin [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 10:18 AM To: [EMAIL PROTECTED] Subject: Re: sum() using group, and duplicates problems... The DISTINCT is almost usless when done on a GROUP BY clause. Also DISTINCT operates on the entire row and not just on the field. ie It will give you DISTINCT measurement, sum(val), pcfver, hour, release. And not just DISTINCT measurement results. Serge. On Wed, 26 Mar 2003 12:00:24 -0600 (CST) Don Read [EMAIL PROTECTED] wrote: On 26-Mar-2003 Black, Kelly W [PCS] wrote: Don Thanks. I will have to change them and try that. I had been bouncing back and forth between decimal and numeric but wasn't sure which was working better. I still think it might have to do with my sql query. Here's an example as you previously mentioned... SELECT DISTINCT measurement, sum(val), pcfver, hour, release FROM pcf WHERE release = curdate()-1 group by measurement, hour; The -sql runs fine, but there are hours in which I receive duplicated input data. I have filtered as much as I can, but was really needing help from the database driver to eliminate the rest. Thanks for your help. Couple of problems I see in the query: 1. curdate()-1 don't do what you think it do: mysql select curdate(), curdate()-27; ++--+ | curdate() | curdate()-27 | ++--+ | 2003-03-26 | 20030299 | ++--+ 1 row in set (0.00 sec) --use DATE_SUB(curdate(), INTERVAL 1 DAY) instead. 2. If you have duplicates that is going to hammer your results. The DISTINCT function filters the result set _after_ they're selected (and SUM'ed). If you have duplicate entries, the sum(val) will total both rows in the group --probably not what you want. A Perl script could help clear up any duplicates. Then you could add a UNIQUE key to keep 'em out. my $0.02. Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: granting privileges using wildcards
I think this might do what you want, but then you will be required to log in with the -p syntax... GRANT SELECT on *.* TO yourlogin@'%' IDENTIFIED BY somepassword; FLUSH PRIVILEGES; mysql -u youruserid -p -h hostname dbasename Regards, Kelly Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Dimitar Haralanov [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 26, 2003 11:03 AM To: [EMAIL PROTECTED] Subject: granting privileges using wildcards Hi, I have been trying to find information on the following question but have been unable to do so: Is it possible to grant options to a user using wildcards. For example: let's say that I have a database named 'db', and a user 'admin' who has full privileges. The user 'admin' will add and delete tables to 'db' but any table that is added/deleted will have the following name format 'table_[A-z0-9]'. What I would like to be able to do is grant select privileges to any and all of the tables 'table_*' to a user 'non_admin'. Is this possible and if it is, how can I do that? Thank you for your help! -- Mitko -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sum() using group, and duplicates problems...
Hello. Sorry for the long post. This has been a big problem for me, and I hope someone will help... I have a long standing problem with an MySQL query. Or perhaps a couple of sql query problems... Firstly I have been told I cannot use sum() on a value while using group by parm because it's not accurate. This is obvious as the result sets are often quite wrong... However, while attempting to use MIN(), MAX(), SUM() etc.. it will error saying: ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause How can I organize data and get accurate sums? It seems the SQL crowd wants to have it's cake and eat it too on this one :) Secondly I have duplicate results. I know this to be the case for many reasons. I have manually taken the raw dump (input data file) and manually calculated all the results on paper, and when my counts are off, the are *always* to factors of the original number. For example if I get a result of 1024 for a certain count, and the output result is wrong, it's always some multiple of 1024 such as 2048. *However* SELECT DISTINCT doesn't SEE the duplicates, as so: mysql CREATE TABLE clean_pcf AS SELECT DISTINCT * FROM pcf where release = curdate()-1; Query OK, 2438 rows affected (0.11 sec) Records: 2438 Duplicates: 0 Warnings: 0 mysql Any ideas on how I can get rid of the bug wars??? Regards, Kelly Black Linux was very clearly the answer, but what was the question again? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Permission Setup
Try using GRANT ALL ON Security.* TO newbuddy@'%' INDENTIFIED BY 'password'; Regards, Kelly Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Terrance Win [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 12:57 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Permission Setup Dear Stefan, Sorry for incomplete information and also thank you for your help. Here is the recent condition .. --- mysqlstatus; - mysql Ver 12.18 Distrib 4.0.12, for Win95/Win98(i32) Connection id: 338 Current database: mysql Current user: [EMAIL PROTECTED] SSL:Not in use Server Version: 4.0.12-nt Protocol version: 10 Connection: localhost via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: 3306 Uptime: 5 hours 42 min 38 sec Threads: 1 Questions: 1796 Slow queries: 0 Opens: 21 Flush tables: 1 Open tables:0 Queries per second avg: 0.087 - mysqlGRANT ALL ON Security.* to [EMAIL PROTECTED] Identified by 'password'; Query OK, 0 rows affected(0,00 sec) mysqlexit Bye C:\mysql\binmysql -h network.srv -u newbuddy -p password ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Help me please. with Regards, Terry From: Stefan Hinz [EMAIL PROTECTED] Reply-To: Stefan Hinz [EMAIL PROTECTED] To: Terrance Win [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Permission Setup Date: Tue, 25 Mar 2003 21:00:00 +0100 Terry, I 've a problem with permission setup. In my newly install MySQL server; First, I log into as 'root' and create a database, lets say 'myDB'. Then I Grant a user name 'admin' with password to that database'myDB' in hostname 'network.srv'. Then I log out from root account. Let's suppose your GRANT statement was okay. Better if you would provide the statement you issued. I tried to log in as 'admin' using password to the same host. %mysql -u admin -network.srv -p password:** That should be mysql -u admin -h network.srv -p. Let's assume you specified that. In this point the server said Access Denied to user [EMAIL PROTECTED], blablabla. That blablabla might hold some information worth posting. Is there any mistake in my job? I'm using W2K server as OS. When asking a question like that, you should provide information like the following sessions info: mysql STATUS; -- mysql Ver 12.18 Distrib 4.0.10-gamma, for Win95/Win98 (i32) Connection id: 7 Current database: GuideProf Current user: [EMAIL PROTECTED] SSL:Not in use Server version: 4.0.10-gamma-max-nt-log Protocol version: 10 Connection: localhost via TCP/IP Client characterset:latin1_de Server characterset:latin1_de TCP port: 3306 Uptime: 1 day 1 hour 18 min 47 sec Threads: 1 Questions: 226 Slow queries: 10 Opens: 102 Flush tables: 1 Open table -- mysql GRANT ALL ON test.* TO 'someoneelse'@'localhost' IDENTIFIED BY 'youwontguess'; Query OK, 0 rows affected (0.19 sec) mysql EXIT Bye C:\mysql\binmysql -h localhost -u someoneelse -pyouwontguess Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 to server version: 4.0.10-gamma-max-nt-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql USE mysql; ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'mysql' mysql USE test; Database changed Look at the last couple of lines. They show that the privileges were granted as expected. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- 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: mysql install---help please
Try /usr/local/mysql/bin/mysql -u userid -p -h hostname password: mysql Regards, Kelly Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Jennifer Goodie [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 3:16 PM To: katherine bjork; mysql Subject: RE: mysql install---help please You said that you changed the password to the password you wanted, but in all the examples you are trying to access the server without a password. Try using the -p flag so it prompts you for your password, then type in whatever you set the password to be. -Original Message- From: katherine bjork [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 3:02 PM To: mysql Subject: mysql install---help please Tried for the second time to install mysql on my mac and again a problem during the install related to the password. I typed in /usr/local/mysql/bin/mysqladmin -u root password 'new-password' and changed 'new-password' to the password I wanted. Got the -- on the next line but nothing else so I assumed it worked. When I try to do anything such as check to see if mysql and php are happy I get Warning: mysql_connect() [function.mysql-connect]: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) in /Library/WebServer/Documents/mysql_up2.php on line 5 Warning: mysql_select_db() [function.mysql-select-db]: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) in /Library/WebServer/Documents/mysql_up2.php on line 9 Warning: mysql_select_db() [function.mysql-select-db]: A link to the server could not be established in /Library/WebServer/Documents/mysql_up2.php on line 9 Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) When I go back and try /usr/local/mysql/bin/mysqladmin -u root password 'new-password' again I get /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Also tried /usr/local/mysql/bin/mysqladmin -u root -h $hostname password 'new-password' but was denied. How the heck do I give myself access to mysql? Please don't point me to the mysql.org page...been there I do not understand the grants et al. Need plain english as in explicit instruction on what to do. BTW version is 4 on mac osx running apache 1.3 with php 4.3 Katherine -- 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: mysql install---help please
Sorry you would use the name of the system, or the ip like so... (in pseudo terms) mysql -u yourpersonaluserid -p -h thehostiuse.com or mysql -u yourpersonalloginid -p -h ipaddress (such as 203.12.34.56) sql query ~KB -Original Message- From: katherine bjork [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 4:13 PM To: Black, Kelly W [PCS] Subject: Re: mysql install---help please I typed in /usr/local/mysql/bin/mysql -u userid -p -h localhost password: prompted for password, gave only one I ever use response was ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) where userid was my login On Tuesday, March 25, 2003, at 03:50 PM, Black, Kelly W [PCS] wrote: Try /usr/local/mysql/bin/mysql -u userid -p -h hostname password: mysql Regards, Kelly Black Linux was very clearly the answer, but what was the question again? -Original Message- From: Jennifer Goodie [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 3:16 PM To: katherine bjork; mysql Subject: RE: mysql install---help please You said that you changed the password to the password you wanted, but in all the examples you are trying to access the server without a password. Try using the -p flag so it prompts you for your password, then type in whatever you set the password to be. -Original Message- From: katherine bjork [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 3:02 PM To: mysql Subject: mysql install---help please Tried for the second time to install mysql on my mac and again a problem during the install related to the password. I typed in /usr/local/mysql/bin/mysqladmin -u root password 'new-password' and changed 'new-password' to the password I wanted. Got the -- on the next line but nothing else so I assumed it worked. When I try to do anything such as check to see if mysql and php are happy I get Warning: mysql_connect() [function.mysql-connect]: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) in /Library/WebServer/Documents/mysql_up2.php on line 5 Warning: mysql_select_db() [function.mysql-select-db]: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) in /Library/WebServer/Documents/mysql_up2.php on line 9 Warning: mysql_select_db() [function.mysql-select-db]: A link to the server could not be established in /Library/WebServer/Documents/mysql_up2.php on line 9 Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) When I go back and try /usr/local/mysql/bin/mysqladmin -u root password 'new-password' again I get /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Also tried /usr/local/mysql/bin/mysqladmin -u root -h $hostname password 'new-password' but was denied. How the heck do I give myself access to mysql? Please don't point me to the mysql.org page...been there I do not understand the grants et al. Need plain english as in explicit instruction on what to do. BTW version is 4 on mac osx running apache 1.3 with php 4.3 Katherine -- 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: mysqladmin -u root -h myhost password 'new-password' fails
You guys probably need to start mysql with the --skip-grant option and fix the allow tables... See the mysql.com website or google.com for lost password mysql -Original Message- From: Chee-Wai Yeung [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 25, 2003 5:09 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: mysqladmin -u root -h myhost password 'new-password' fails Karl, yes, same problem here. Looking at the user table it seems the entry is there, but mysqladmin failed. I also attempted to set by hand the password using update table. The update succeeded, but then when I used mysql -u root -h hostname -p and typed in the password, mysql also denied access saying the access was denied. The problem does not exist in mysql 3.23.56. Any help appreciated. Thanks Chee Wai --- [EMAIL PROTECTED] wrote: Description: I installed the 4.0.12-max binary distribution for x86-linux (got the same behavior with -standard). This was a brand new installation. When I ran scripts/mysql_install_db, it tells me: PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! This is done with: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h tug.org password 'new-password' See the manual for more instructions. The first command, without the -h, worked fine, and indeed I must type password now when I say mysqladmin -u root or -u root -h localhost. However, the second command, with the -h tug.org, fails, with the error message mysqladmin: unable to change password; error: 'Can't find any matching row in the user table' More importantly, I can still run commands like mysqladmin -u root -h tug.org variables without being asked for a password. (By the way, I suggest adding this test to the other ones listed in the `General security' node.) When I inspected the user table as explained in the manual, there are lines for both localhost and tug.org, and only the localhost line has a password (which I've x'd it below): mysql select Host,User,Password from mysql.user WHERE User='root'; +---+--+--+ | Host | User | Password | +---+--+--+ | localhost | root | | | tug.org | root | | +---+--+--+ So I looked in the manual, but found no information on this error. I also tried searching the mailing lists at lists.mysql.com with no luck, although searching was so slow I couldn't be as thorough as I would liked to have been. I also searched for this on google[/groups] with no results, but found (not surprisingly) a number of other people reporting the exact same problem: http://groups.google.com/groups?q=mysqladmin+%22find+any+matching+row%22hl= enlr=ie=UTF-8oe=UTF-8selm=alig93%242t8e%241%40FreeBSD.csie.NCTU.edu.twr num=3 http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8th=5bca2770813d0 016rnum=6 http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8th=8a5b663b86b43 73brnum=4 I expect I could explicitly set the password for [EMAIL PROTECTED] with set password. But the point is that the mysql_install_db script is telling me to do something that doesn't work. Any help appreciated ... How-To-Repeat: ... follow INSTALL-BINARY instructions and: ... root# mysqladmin -u root -h tug.org password mynewpassword mysqladmin: unable to change password; error: 'Can't find any matching row in the user table' Submitter-Id:submitter ID Originator: Karl Berry Organization: MySQL support: none Synopsis:mysqladmin -u root -h myhost password 'new-password' fails Severity:non-critical Priority:medium Category:mysql Class: sw-bug Release: mysql-4.0.12-max (Official MySQL-max binary) Server: /usr/local/mysql/bin/mysqladmin Ver 8.40 Distrib 4.0.12, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version4.0.12-max-log Protocol version 10 ConnectionLocalhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 37 min 7 sec Threads: 1 Questions: 52 Slow queries: 0 Opens: 7 Flush tables: 1 Open tables: 1 Queries per second avg: 0.023 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux tug.org 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) Compilation info: CC='gcc' CFLAGS='-O2 -mpentiumpro -DBIG_TABLES' CXX='gcc' CXXFLAGS='-O2 -mpentiumpro
FW: confirm unsubscribe from mysql@lists.mysql.com
Cute. sql, query ~K Black -Original Message- From: [EMAIL PROTECTED] [mailto:mysql-help;lists.mysql.com] Sent: Thursday, November 14, 2002 6:38 AM To: Black, Kelly W [PCS] Subject: confirm unsubscribe from [EMAIL PROTECTED] Hi! This is the ezmlm program. I'm managing the [EMAIL PROTECTED] mailing list. To confirm that you would like [EMAIL PROTECTED] removed from the mysql mailing list, please send an empty reply to this address: [EMAIL PROTECTED] ysql.com Usually, this happens when you just hit the reply button. If this does not work, simply copy the address and paste it into the To: field of a new message. I haven't checked whether your address is currently on the mailing list. To see what address you used to subscribe, look at the messages you are receiving from the mailing list. Each message has your address hidden inside its return path; for example, [EMAIL PROTECTED] receives messages with return path: mysql-return-number[EMAIL PROTECTED] Some mail programs are broken and cannot handle long addresses. If you cannot reply to this request, instead send a message to [EMAIL PROTECTED] and put the entire address listed above into the Subject: line. --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, send a message to the address in the List-Unsubscribe header of messages from the list. If you don't know how to display headers and haven't changed E-mail addresses since subscribing, you'll be successful with an e-mail to: [EMAIL PROTECTED] Send mail to the following for info and FAQ for this list: [EMAIL PROTECTED] [EMAIL PROTECTED] Similar addresses exist for the digest list: [EMAIL PROTECTED] [EMAIL PROTECTED] To get messages 123 through 145 (a maximum of 100 per request), mail: [EMAIL PROTECTED] To get an index with subject and author for messages 123-456 , mail: [EMAIL PROTECTED] They are always returned as sets of 100, max 2000 per request, so you'll actually get 100-499. To receive all messages with the same subject as message 12345, send an empty message to: [EMAIL PROTECTED] The messages do not really need to be empty, but I will ignore their content. Only the ADDRESS you send to is important. You can start a subscription for an alternate address, for example [EMAIL PROTECTED], just add a hyphen and your address (with '=' instead of '@') after the command word: [EMAIL PROTECTED] To stop subscription for this address, mail: [EMAIL PROTECTED] In both cases, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete your subscription. If despite following these instructions, you do not get the desired results, please contact my owner at [EMAIL PROTECTED] Please be patient, my owner is a lot slower than I am ;-) --- Enclosed is a copy of the request I received. Return-Path: [EMAIL PROTECTED] Received: (qmail 6789 invoked from network); 14 Nov 2002 14:38:00 - Received: from esacom56-ext.estec.esa.int (HELO esacom56-int.estec.esa.int) (131.176.107.3) by www.mysql.com with SMTP; 14 Nov 2002 14:38:00 - Received: from esacom52.estec.esa.int. (esacom52.estec.esa.nl [131.176.7.7]) by esacom56-int.estec.esa.int (8.10.2/8.10.2/ESA-External-v2.0) with ESMTP id gAEEbcX22596 for [EMAIL PROTECTED]; Thu, 14 Nov 2002 15:37:38 +0100 (MET) Received: from estecmail4.estec.esa.int (estecmail4.estec.esa.nl [131.176.7.65]) by esacom52.estec.esa.int. (8.9.2/8.9.2/ESA-ESTEC-mail-gw-v1.6) with SMTP id OAA24083 for [EMAIL PROTECTED]; Thu, 14 Nov 2002 14:37:38 GMT Received: by estecmail4.estec.esa.int(Lotus SMTP MTA v4.6.7 (934.1 12-30-1999)) id 41256C71.0055AD1A ; Thu, 14 Nov 2002 16:35:48 +0100 X-Lotus-FromDomain: ESA From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Message-ID: [EMAIL PROTECTED] Date: Thu, 14 Nov 2002 15:37:29 +0100 Mime-Version: 1.0 Content-type: text/plain; charset=us-ascii Content-Disposition: inline Erik F. van der Wenden ESTEC/TOS-EMS Postbus 299 2200 AG Noordwijk, The Netherlands Keplerlaan 1 2201 AZ Noordwijk, The Netherlands T: (31) 71 5653594 M: (31) 6 51608116 E: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Installation Help
Right. My mistake. I think you get the general idea. ~K Black -Original Message- From: Paul DuBois [mailto:paul;snake.net] Sent: Tuesday, November 12, 2002 4:21 PM To: Black, Kelly W [PCS]; 'Phil Iovino'; 'Scott Pippin' Cc: [EMAIL PROTECTED] Subject: RE: Installation Help At 11:45 -0600 11/12/02, Black, Kelly W [PCS] wrote: Make sure you issued the correct GRANT statements at the sql, query. mysqluse mysql; Database Changed mysql GRANT * ON *.* TO '[EMAIL PROTECTED]' IDENTIFIED BY 'somepassword'; That's not quite right. - GRANT * is not legal, I suspect you mean GRANT ALL. - '[EMAIL PROTECTED]' is quoted incorrectly. That is equivalent to '[EMAIL PROTECTED]'@'%'. I suspect you mean 'userid'@'hostname.com'. (user and host parts should be quoted separately.) #note that will give FULL access...see the docs to restrict this. mysql FLUSH PRIVILEGES; 0 Rows Affected. ~Kelly W. Black - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: create table w/ data/index directory
It doesn't appear this part is valid sql, query DATA_DIRECTORY=/tmp; ~K Black -Original Message- From: Yuyi Guo [mailto:yuyi;fnal.gov] Sent: Wednesday, November 13, 2002 9:21 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: create table w/ data/index directory Hi, I am using Mysql version: 3.23.52 on Red Hat Linux. I tried to create a table as below and got error. Could some one point me what I did wrong? Or the DATA DIRECTORY defination does not really exist? Thanks, Yuyi Guo mysql create table testtable2 (col1 varchar(10) NOT NULL, col2 varchar(10), primary key (col1), index idx1 (col2)) DATA_DIRECTORY=/tmp; ERROR 1064: You have an error in your SQL syntax near 'DATA_DIRECTORY=/tmp' at line 1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Installation Help
Sorry I made a mistake as Paul pointed out. I meant GRANT ALL ON *.* TO '[EMAIL PROTECTED]' IDENTIFIED BY 'somepassword'; If you use the localhost substitution for the fully qualified domain name then be sure your /etc/hosts file has a line at the top like 127.0.0.1 localdomain.localhost localhost ~Kelly W. Black -Original Message- From: Phil Iovino [mailto:phil;nxtek.net] Sent: Wednesday, November 13, 2002 10:36 AM To: 'Paul DuBois'; Black, Kelly W [PCS]; 'Scott Pippin' Cc: [EMAIL PROTECTED] Subject: RE: Installation Help I wasn't sure if I was supposed to use 'root' or 'root@localhost' so I did it for both. I'm still getting this: [root@web01 phil]# /usr/local/mysql/bin/mysqladmin version /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)' Ugh. :) -Original Message- From: Paul DuBois [mailto:paul;snake.net] Sent: Tuesday, November 12, 2002 7:21 PM To: Black, Kelly W [PCS]; 'Phil Iovino'; 'Scott Pippin' Cc: [EMAIL PROTECTED] Subject: RE: Installation Help At 11:45 -0600 11/12/02, Black, Kelly W [PCS] wrote: Make sure you issued the correct GRANT statements at the sql, query. mysqluse mysql; Database Changed mysql GRANT * ON *.* TO '[EMAIL PROTECTED]' IDENTIFIED BY mysql 'somepassword'; That's not quite right. - GRANT * is not legal, I suspect you mean GRANT ALL. - '[EMAIL PROTECTED]' is quoted incorrectly. That is equivalent to '[EMAIL PROTECTED]'@'%'. I suspect you mean 'userid'@'hostname.com'. (user and host parts should be quoted separately.) #note that will give FULL access...see the docs to restrict this. mysql FLUSH PRIVILEGES; 0 Rows Affected. ~Kelly W. Black - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Using MySQL with Perl 5.8?
Did you remember to allow backwards compatibility in @INC for perl 5.6.1? ~K Black -Original Message- From: Octavian Rasnita [mailto:orasnita;home.ro] Sent: Wednesday, November 13, 2002 2:58 AM To: MySQL List Subject: Using MySQL with Perl 5.8? Hi all, I was able to install the module DBD::mysql for Perl 5.6.1 using ppm (under Windows 2000) but after installing Perl 5.8, ppm can't find this module anymore. I could find only the module DBD::mysqlPP for pure Perl. I don't understand what pure perl means but I've seen that this module accepts less functions than DBD::mysql. Do you know if DBD::mysql works with Perl 5.8 and how can I install it? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Upgrading MySQL from 3.23.41 to 3.23.53a
You might need to use rpm -e to uninstall previous version first. Check the documentation. ~Kelly W. Black -Original Message- From: Rick Root [mailto:rroot;wakeinternet.com] Sent: Wednesday, November 13, 2002 10:47 AM To: mysql Subject: Upgrading MySQL from 3.23.41 to 3.23.53a I would like to upgrade MySQL to the latest production release (3.23.53a) from my current 3.23.41 I have the following packages installed already: mysql-devel-3.23.41-1 mysqlclient9-3.23.22-6 mysql-3.23.41-1 mysql-server-3.23.41-1 What do I need to do in order to upgrade to the newest version? I tried using the RPM facility to install and upgrade the new packages I just downloaded but I get conflicts... ie... [root@frodo MySQL]# rpm --install MySQL-3.23.53a-1.i386.rpm error: failed dependencies: MySQL conflicts with mysql-3.23.41-1 MySQL-server conflicts with mysql-server-3.23.41-1 [root@frodo MySQL]# rpm -U MySQL-3.23.53a-1.i386.rpm error: failed dependencies: MySQL-server conflicts with mysql-server-3.23.41-1 libmysqlclient.so.10 is needed by mysql-server-3.23.41-1 libmysqlclient.so.10 is needed by perl-DBD-MySQL-1.2216-4 [root@frodo MySQL]# So what should I do? Do I need to actually remove the old MySQL first and then install the new one? - Rick Root - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql server hardware
If you ran into any problems it would probably be RAM. RAM is cheap so if you have trouble...just pop a couple of 512MB chips in it. ~KB -Original Message- From: Jeremy Zawodny [mailto:Jeremy;Zawodny.com] Sent: Wednesday, November 13, 2002 11:12 AM To: Devore, Jacob Cc: '[EMAIL PROTECTED]' Subject: Re: mysql server hardware On Tue, Nov 12, 2002 at 11:22:11PM -0800, Devore, Jacob wrote: Can any of you point me to documentation or have a quick summary of how beefy to make a mysql server. Basically we probably won't have more than 50 connections at one time and we will be pushing packets of 2Mb at times. I'm curious if I should go with tons of ram, faster cpu's, a gig nic, or is it even worth it to upgrade our server. I believe right now it's on a dual 400 p2 with 512Mb of ram and 100Mb connection. Do some benchmarking. I suspect you'll find that your current hardware is adaquate, assuming that MySQL and your operating system are well tuned. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 99 days, processed 2,102,130,626 queries (244/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How stable is MySQL 4.x vs 3.23.x?
I have been using the MySql 4.x MAX with absolutely NO problems. It's being used on heavily operated servers, and I upgraded to 4.x for it's incredible index speeds and it's VERY reliable to me. In fact, in our local benchmarks it's kicking the living crap out of Oracle on sql, query. :) ~Kelly W. Black -Original Message- From: Jaime Teng [mailto:jaime;qinet.net] Sent: Monday, November 11, 2002 7:11 PM To: [EMAIL PROTECTED] Subject: How stable is MySQL 4.x vs 3.23.x? Hi, How stable is the new MySQL 4.x? I've read some really nice and powerful stuffs with MySQL 4. x and surely like to use them. Is this still in the 'use at your own risk' stage? or is it stable enough for use already? regards, Jaime - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql_install_db hangs
Make sure some previous version of MySql isn't running. I have seen this on systems which had the OS installed version of MySql, and I had to issue shell /sbin/service mysql stop shell cd /usr/local/mysqlversion shell ./bin/mysqld_safe -user=theuserid Hope this helps... ~Kelly W. Black -Original Message- From: George Chelidze [mailto:wrath;geo.net.ge] Sent: Tuesday, November 12, 2002 1:39 AM To: [EMAIL PROTECTED] Subject: mysql_install_db hangs Hello, One of my custommers use mysql database and a few days ago he asked me to solve the strange problem with it. I don't know the history of server so I am unable to feed you with information like what has been changed before problem occured, but below is the describtion of problem itself and maybe someone can tell what can be the reason. facts: ps ax doesn't show any mysql process running netstat -lnp shows that tcp/3306 port is in listening state but application doesn't present ('-' instead). pid file still exsists but process with pid listed in pid file doesn't present. mysql is unable to start because socket is already in use... I have uninstalled mysql rpm-s and rebooted, after system booted up 3306 port became finally closed so I tried to install new rpm-s. First I tried to install MySQL-VERSION.i386.rpm and after some time I saw: Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables after that it hangs and there is no message until I terminate it from shell using CTRL+c. After termination: #ps ax | grep mysql 5736 pts/2S 0:00 sh /usr/bin/mysql_install_db -IN-RPM 5741 pts/2S 0:00 /usr/sbin/mysqld --bootstrap --skip-grant-tables --basedir=/ --datadir=/var/lib/mysql --skip-innodb --skip-gemini --skip-bdb #netstat -lnp | grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN - # I have tried to install mysql from source and installation finished successfully but problem still exsist when I run mysql_install_db Any ideas? -- George Chelidze - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Solaris 2.6
You don't have a compiler installed? Hmmm. This seems tough to believe. Try issuing 'which cc' ~K Black -Original Message- From: leaddog1 [mailto:jsobeck;lead-dog.net] Sent: Tuesday, November 12, 2002 6:44 AM To: [EMAIL PROTECTED] Subject: Solaris 2.6 Is there any version of MySQL with a binary distribution available that supports InnoDb for Sun OS 5.6? I cannot build one from source because I do not have a compiler installed (cc) on the box. Jim Sobeck [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: slow queries
Make sure when you are creating the database that U_Number | int(9) unsigned | | PRI | NULL is U_Number | numeric() | | PRI | NULL -Original Message- From: Petre Agenbag [mailto:internet;boesmanland.com] Sent: Tuesday, November 12, 2002 4:27 AM To: [EMAIL PROTECTED] Subject: slow queries mysql,query Hi List I have a couple of slow queries listed in my slow log, but I don't know what to do from here to make them fast. Here is an EXPLAIN table: mysql explain w3t_Users; ++--+--+-+-+ + | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+ + | U_Username | varchar(30) | | MUL | || | U_Password | varchar(32) | | | || | U_Email| varchar(50) | YES | | NULL || | U_Fakeemail| varchar(50) | YES | | NULL || | U_Name | varchar(100) | YES | | NULL || | U_Totalposts | int(9) unsigned | YES | | NULL || | U_Laston | int(11) unsigned | YES | | NULL || | U_Signature| text | YES | | NULL || | U_Homepage | varchar(150) | YES | | NULL || | U_Occupation | varchar(150) | YES | | NULL || | U_Hobbies | varchar(200) | YES | | NULL || | U_Location | varchar(200) | YES | | NULL || | U_Bio | text | YES | | NULL || | U_Status | varchar(15) | | MUL | User || | U_Sort | int(4) unsigned | YES | | NULL || | U_Display | varchar(10) | | | || | U_View | varchar(10) | | | || | U_PostsPer | int(11) unsigned | YES | | NULL || | U_Number | int(9) unsigned | | PRI | NULL| auto_increment | | U_EReplies | char(3) | | | || | U_Notify | char(3) | | | || | U_TextCols | char(3) | YES | | NULL || | U_TextRows | char(3) | YES | | NULL || | U_Extra1 | varchar(200) | YES | | NULL || | U_Extra2 | varchar(200) | YES | | NULL || | U_Extra3 | varchar(200) | YES | | NULL || | U_Extra4 | varchar(200) | YES | | NULL || | U_Extra5 | varchar(200) | YES | | NULL || | U_Post_Format | varchar(5) | | | || | U_Registered | int(11) unsigned | YES | | NULL || | U_Preview | varchar(5) | YES | | NULL || | U_Picture | varchar(150) | YES | | NULL || | U_PictureView | char(3) | YES | | NULL || | U_Visible | char(3) | YES | | yes || | U_PicturePosts | char(3) | YES | | NULL || | U_AcceptPriv | char(3) | YES | | yes || | U_RegEmail | varchar(50) | YES | | NULL || | U_RegIP| varchar(15) | YES | | NULL || | U_Groups | varchar(250) | YES | | -1- || | U_Language | varchar(20) | YES | | NULL || | U_Title| varchar(100) | YES | | NULL || | U_FlatPosts| char(2) | YES | | NULL || | U_TempPass | varchar(32) | YES | | NULL || | U_Color| varchar(15) | YES | | NULL || | U_TempRead | text | YES | | NULL || | U_StyleSheet | varchar(50) | YES | | NULL || | U_TimeOffset | varchar(10) | YES | | NULL || | U_Privates | int(4) unsigned | YES | | 0 || | U_FrontPage| varchar(20) | YES | | NULL || | U_ActiveThread | int(4) unsigned | YES | | NULL || | U_StartPage| char(2) | YES | | cp || | U_Favorites| varchar(250) | YES | | - || | U_ShowSigs | char(3) | YES | | NULL || | U_OnlineFormat | char(3) | YES | | NULL || | U_Rating |
RE: unable to start mysql
Don't re-install.. Fix the problem Try issuing shellcd /usr/local/mysqlversion shell./configure ~K Black -Original Message- From: mod_perl [mailto:shine_perl;spectrum.net.in] Sent: Monday, November 11, 2002 10:23 PM To: [EMAIL PROTECTED] Subject: unable to start mysql hi all, when i try to start mysql using safe_mysqld i get following message [mysql@inemrtest2 mysql]$ safe_mysqld [1] 3644 [mysql@inemrtest2 mysql]$ Starting mysqld daemon with databases from /opt/mysql/data 021112 11:59:15 mysqld ended if mysqld is crashed how can i reinstall thanks in advance shine - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Installation Help
Make sure you issued the correct GRANT statements at the sql, query. mysqluse mysql; Database Changed mysql GRANT * ON *.* TO '[EMAIL PROTECTED]' IDENTIFIED BY 'somepassword'; #note that will give FULL access...see the docs to restrict this. mysql FLUSH PRIVILEGES; 0 Rows Affected. ~Kelly W. Black -Original Message- From: Phil Iovino [mailto:phil;nxtek.net] Sent: Tuesday, November 12, 2002 8:14 AM To: 'Scott Pippin' Cc: [EMAIL PROTECTED] Subject: RE: Installation Help I believe root has all permissions. I'm using Webmin to verify it. I started it with -u root but still got the same error. Since I installed with the RPM didn't it give root permission? -Original Message- From: Scott Pippin [mailto:spippin;mtctrains.com] Sent: Tuesday, November 12, 2002 10:43 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Installation Help Scott Pippin [EMAIL PROTECTED] Phil Iovino [EMAIL PROTECTED] 11/12/02 08:13AM I'm trying to install MySQL under RedHat 8. I installed the MySQL 3.23.53a-1 and MySQL-client 3.23.53a-1 RPMs. Per the instructions at http://www.mysql.com/documentation/mysql/bychapter/manual_Installing.ht m l#Post-installation, I started the server with /usr/bin/safe_mysqld and it appears to have started. When I try to test it, I get this: [root@web01 bin]# mysqladmin version mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: NO)' I'm kinda stuck. Any ideas? Did you set up root as a user? If you did, try /usr/bin/safe_mysqld -u root If not set up a user. http://www.mysql.com/doc/en/Adding_users.html hope this helps. (sql, mysql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem compiling mysqlgui 1.7.5
See the file INSTALL_BINARY ~KB -Original Message- From: Pierre [mailto:pierre;epinetworx.com] Sent: Tuesday, November 12, 2002 1:45 AM To: [EMAIL PROTECTED] Subject: Problem compiling mysqlgui 1.7.5 Hi, I try to compile mysqlgui-src-1.7.5 on my GNU/Linux system with gcc-3.2 but with the make I got this : # gmake gmake: *** No rule to make target `edfile.o', needed by `mysqlgui'. Stop. It would be great if you could help me with this ! Best regards, Pierre. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: delayed inserts and messages in the host.err log
Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space, If it persists, use vmstat 1 ~Kelly W Black -Original Message- From: Jeremy Zawodny [mailto:Jeremy;Zawodny.com] Sent: Tuesday, November 12, 2002 2:31 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: delayed inserts and messages in the host.err log On Tue, Nov 12, 2002 at 02:18:44PM -0800, [EMAIL PROTECTED] wrote: This is just a should I be concerned question. I am getting a few messages in the mysql error log about delayed inserts and the database not being able to obtain a lock. The database is averaging about 250 queries per second. We are using delayed inserts for most every insert and the data is read about every 5 seconds or so. I am not getting that many errors considering the amount of inserts taking place (very heavy on the inserts), I think, I'm just not sure what this really means... is data being lost? I've looked at the data and it doesn't appear to be *losing* it. [snip] 021112 15:32:12 mysqld started /usr/local/mysql-3.23.51-sun-solaris2.8-sparc/bin/mysqld: ready for connections 021112 16:04:31 Delayed insert thread couldn't get requested lock for table app_nc_snmppoll That's odd. There seem to be two places in the code where MySQL generates that error. One is preceded by this comment: /* This should never happen */ And the other by this one: /* This can only happen if thread is killed by shutdown */ So I'm a bit suspicious. In one of those cases, it jumps to error handler that flushes the delayed inert queue for that table, so you'd lose data. In the other, it does not. Hmm. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 98 days, processed 2,070,154,310 queries (242/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Why does safe_mysqld runs as root?
Get on out there with your bad self Michael !! :) ~KB -Original Message- From: Michael T. Babcock [mailto:mbabcock;fibrespeed.net] Sent: Tuesday, November 12, 2002 3:23 PM To: David Kramer Cc: '[EMAIL PROTECTED]' Subject: Re: Why does safe_mysqld runs as root? David Kramer wrote: By boot sequence Im guessing you mean my different run levels? rcX.d? Im guessing that I need to set the user value within the start-up script it self rather than my.cnf??? I thought my.cnf was the config file that the start-up scripts reference, by me setting a the attribute user=mysql under [mysqld], mysql should start under the user mysql? I'm going to say this again: - The user that mysqld runs as is configured in my.cnf as per the online documentation at http://www.mysql.com/doc/en (great stuff; read it lots). - The user that launches safe_mysqld is just that; the user that launches safe_mysqld. Most boot scripts run as root, making root the user that launches safe_mysqld; don't try to make it more complicated than it is. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: /tmp/mysql.sock question (newbie)
You can do this in /etc/my.cnf ~K Black -Original Message- From: [EMAIL PROTECTED] [mailto:CZachary;wiley.com] Sent: Monday, November 11, 2002 10:05 AM To: [EMAIL PROTECTED] Subject: /tmp/mysql.sock question (newbie) Hello all, I would like to change where the /tmp/mysql.sock file gets created. How to I get it to create the file in another location? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Commands out of sync
Can you post about 30 seconds worth of the vmstat 1 command when the system is busy? Thanks ~Kelly W. Black -Original Message- From: [EMAIL PROTECTED] [mailto:kahnmatt;engin.umich.edu] Sent: Monday, November 11, 2002 11:09 AM To: [EMAIL PROTECTED] Subject: Commands out of sync Hi- I'm running MySQL 3.23.51 with PHP 4.2.3 on a Slackware 8.1 system with Dual Athlon MP 1.2's and 1 GB RAM. We run a busy site, approximately 3 million page views/day, almost all of which hit the database. The problem is that we are getting the error Commands out of sync. You cannot perform this command now. I have read the unhelpful section of the MySQL manual, and done several google searches, none of which have helped. None of our queries are very complex, nor does this error seem to occur with any consistency. Sometimes it happens when we're busy, sometimes when we're slow, etc. We never use anything like mysql_query_unbuffered(), only mysql_query(), mysql_select_db(), and mysql_pconnect(). Has anyone encountered this error before? Is there any known situation where this should occur? Any help is greatly appreciated. Matt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: /tmp/mysql.sock question (newbie)
None that I am aware of. Some systems have differed locations. However, some programs expect the file mysql.sock to be in the /tmp dir. I don't know if it's good form or style, but on systems where the sock is located in some out of the way place I just link it with ln -s /path/to/mysql.sock /tmp/mysql.sock and things seem to work fine. ~K Black -Original Message- From: Adolfo Bello [mailto:adolfobello;bisapi.com] Sent: Monday, November 11, 2002 1:32 PM To: Black, Kelly W [PCS] Cc: Mysql Lists Subject: RE: /tmp/mysql.sock question (newbie) I am also a newbie so don't misunderstand my question. Is there any performance or functionality reason to change mysql.sock location? Adolfo On Mon, 2002-11-11 at 14:37, Black, Kelly W [PCS] wrote: You can do this in /etc/my.cnf ~K Black -Original Message- From: [EMAIL PROTECTED] [mailto:CZachary;wiley.com] Sent: Monday, November 11, 2002 10:05 AM To: [EMAIL PROTECTED] Subject: /tmp/mysql.sock question (newbie) Hello all, I would like to change where the /tmp/mysql.sock file gets created. How to I get it to create the file in another location? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql starts under XFS on boot up???
XFS is X server Font Server, and is unrelated. ~Kelly W. Black -Original Message- From: David Kramer [mailto:DKramer;reflect.com] Sent: Monday, November 11, 2002 1:51 PM To: [EMAIL PROTECTED] Subject: Mysql starts under XFS on boot up??? I noticed that Mysql starts up under XFS, Im not even sure what XFS is? Im running RH 7.3 and mysql 3.23.40. I performed the install based on RPM packages not from the Packaged OS install. Jsut curious why this is happening. Also I noticed that my safe_mysqld daemon is running under root: root 976 1 0 Nov10 ?00:00:00 /bin/sh /usr/bin/safe_mysqld --d I have changed all of my binaries and other various mysql files over to mysql own and grp. I have set [mysqld] in my my.cnf file to use user=mysql. Do I need to possibly create a setting in my.cnf file for [safe_mysqld] user=mysql??? Any thoughts would be greatly appreciated. Thanks, DK David Kramer Software Developer Reflect.com Direct: 415.369.4856 Cell: 650.302.7889 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: /tmp/mysql.sock question (newbie)
It would be advisable to modify the script so that it only looks for files on a targeted basis. Many files in /tmp are important, for example orbit, cron, etc. In my opinion I would only remove all file under the /tmp if I was experiencing some sort lock file problem or something. ~Kelly W. Black -Original Message- From: [EMAIL PROTECTED] [mailto:CZachary;wiley.com] Sent: Monday, November 11, 2002 1:48 PM To: Adolfo Bello Cc: Black, Kelly W [PCS]; Mysql Lists Subject: RE: /tmp/mysql.sock question (newbie) There is a cleanup script run via cron that removes files from /tmp. When I try to stop the db, I get an error message that the socket cannot be found. |-+ | | Adolfo Bello | | | adolfobello@bisa| | | pi.com | | || | | 11/11/2002 04:32 | | | PM | | || |-+ --- ---| | | | To: Black, Kelly W [PCS] [EMAIL PROTECTED] | | cc: Mysql Lists [EMAIL PROTECTED] | | Subject: RE: /tmp/mysql.sock question (newbie) | --- ---| I am also a newbie so don't misunderstand my question. Is there any performance or functionality reason to change mysql.sock location? Adolfo On Mon, 2002-11-11 at 14:37, Black, Kelly W [PCS] wrote: You can do this in /etc/my.cnf ~K Black -Original Message- From: [EMAIL PROTECTED] [mailto:CZachary;wiley.com] Sent: Monday, November 11, 2002 10:05 AM To: [EMAIL PROTECTED] Subject: /tmp/mysql.sock question (newbie) Hello all, I would like to change where the /tmp/mysql.sock file gets created. How to I get it to create the file in another location? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqlhotcopy point-in-time recovery
Michael I have a nice perl program I wrote that I use to dump from MySql to a tab delimited ASCII file. I would pass it along if you think it would help... ~Kelly W. Black -Original Message- From: Michael T. Babcock [mailto:mbabcock;fibrespeed.net] Sent: Monday, November 11, 2002 2:38 PM To: Heikki Tuuri Cc: [EMAIL PROTECTED] Subject: Re: mysqlhotcopy point-in-time recovery Heikki Tuuri wrote: you cannot use mysqlhotcopy to back up InnoDB type tables. Only the .frm files are in the database directory, while the data is in ibdata files. mysqlhotcopy does not copy ibdata files or ib_logfiles, and if it would do that, the copies could be corrupt because of writes the database meanwhile does to these files. Shouldn't it be renamed to myisamhotcopy then? ... if it doesn't work with MySQL as a whole, the tool should be renamed. Plus, a tool that does in fact work with MySQL as a whole would be nice. SQL, QUERY -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Error connecting to Mysql via Apache/PHP - Access Denied (Newbie)
Several things. Don't use root to connect remotely. Run mysqld as a userid on the server not root. You may need to grant on mysql database for more than localhost. Such as (Note this grants all privileges to all databases. See mysql docs for restricted access) GRANT * ON *.* TO 'userid' IDENTIFIED BY 'password'; GRANT * ON *.* TO 'userid@%' IDENTIFIED BY 'password'; GRANT * ON *.* TO 'userid@realhostname' IDENTIFIED BY 'password'; DONT FORGET TO - FLUSH PRIVILEGES; Also be sure the file /etc/hosts has this line 127.0.0.1 localhost Oh and I have to say this for the list- sql, query ~Kelly W. Black -Original Message- From: Mertens Bram [mailto:bram-mertens;linux.be] Sent: Friday, November 08, 2002 9:55 AM To: mysql mailing list Subject: Re: Error connecting to Mysql via Apache/PHP - Access Denied (Newbie) On Fri, 2002-11-08 at 16:51, [EMAIL PROTECTED] wrote: I am trying to connect to my Mysql DB via Apache and PHP, but I get the following error: Warning: MySQL Connection Failed: Access denied for user: 'root@jws-portald' (Using password: YES) in helloSql.php on line 3 Could not connect Can you try to access a db as normal user? Or is there a reason you want to connect to the db as root? I use a MySQL user without password and I connect with a command like: $db = mysql_connect(localhost, M8ram, ); mysql_select_db(db_name,$db); Perhaps you have to check the 'case' of your username? HTH -- # Mertens Bram M8ram [EMAIL PROTECTED] Linux User #249103 # # Red Hat Linux 7.3 KDE 3.0.0-10 kernel 2.4.18-3 i686 128MB RAM # - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MysQL Installation
Mysqladmin is seperate rpm ~K Black -Original Message- From: SpyProductions Support Team [mailto:support;spyproductions.com] Sent: Friday, November 08, 2002 11:13 AM To: [EMAIL PROTECTED] Subject: MysQL Installation I just installed the most recent version of MySQL with an RPM (through webmin interface), but it doesn't seem to have installed a mysqladmin.anyone know why? :) Thanks! -Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Installation
Oh, okay. Sorry about that, the RedHat rpm's puts the admin binary in the server package, but MySql.com has it in the client's: $ rpm -qlp MySQL-client-3.23.53a-1.i386.rpm | grep admin /usr/bin/mysqladmin /usr/share/man/man1/mysqladmin.1.gz sql, query ~Kelly W. Black PS IN the future check out http://www.rpmfind.net/ -Original Message- From: SpyProductions Support Team [mailto:support;spyproductions.com] Sent: Friday, November 08, 2002 12:51 PM To: Black, Kelly W [PCS] Cc: [EMAIL PROTECTED] Subject: RE: MysQL Installation Well hells bells! Would that be the 'Client programs (i386) (2.3M)' mentioned on the download page? :) Thanks, Mike -Original Message- From: Black, Kelly W [PCS] [mailto:kblack05;sprintspectrum.com] Sent: Friday, November 08, 2002 2:17 PM To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: MysQL Installation Mysqladmin is seperate rpm ~K Black -Original Message- From: SpyProductions Support Team [mailto:support;spyproductions.com] Sent: Friday, November 08, 2002 11:13 AM To: [EMAIL PROTECTED] Subject: MysQL Installation I just installed the most recent version of MySQL with an RPM (through webmin interface), but it doesn't seem to have installed a mysqladmin.anyone know why? :) Thanks! -Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Installation
-Mike I know this is OT a bit, but now I need to either re-compile PHP to get mysql to work or maybe upgrade from an RPM since I am on 4.1.2 - or do I need to do this at all? Not sure where PHP came into the picture here. Let's not confuse phpMyAdmin with MySqladmin. Different animals all together. For Mysqladmin you will simply need to download the rpm (rpmfind.net for MySqlClient) or build Mysql from scratch with source (how I like it done) Note:I have had nothing but trouble from the RedHat default Mysqld installs, so now (by default) as soon as the server is set up the first thing I do is disable the mysqld in services and re-compile both PHP and MySql (4.0 MAX of course) on the system without even asking. If you are talking about getting mysql working with PHP you need to compile PHP from source with (everyone flog me with wet cron jobs if I am wrong :) (in pseudo) ./configure --with-mysql=/usr/local/thelocationofyourmyslq.hfile and make. Note that if compiling PHP from source you should use the completed configure (see docs) something like (PLEASE KNOW THIS WILL VARY MACHINE TO MACHINE RESEARCH THIS) (This is a snapshot from my Davis, Ca. server and will NOT work with your PHP configure...) './configure' '--with-apxs=/usr/www.0727/bin/apxs' '--with-db' '--with-mysql=/usr/local/mysql' '--with-msql=/usr/local/Hughes' '--with-xml' '--with-gd=/usr/local' '--enable-ftp' '--with-imap-ssl=/usr/home/colossus/pine4.33/imap' '--with-jpeg-dir=/usr/lib' '--with-zlib' '--with-openssl=/usr/local/ssl' '--with-png-dir=/usr/lib' '--enable-magic-quotes=yes' '--enable-memory-limit' '--enable-trackvars=yes' If this didn't confuse you let me know I will try to confuse you again. ~Kelly W. Black sql, query -Original Message- From: SpyProductions Support Team [mailto:support;spyproductions.com] Sent: Friday, November 08, 2002 2:26 PM To: Black, Kelly W [PCS] Cc: [EMAIL PROTECTED] Subject: RE: MySQL Installation I know this is OT a bit, but now I need to either re-compile PHP to get mysql to work or maybe upgrade from an RPM since I am on 4.1.2 - or do I need to do this at all? Even at rpmfind.net is seems hard to distinguish what it is I need. Is there anyway to get a 'complete' PHP rpm somewhere? Thanks! -Mike -Original Message- From: Black, Kelly W [PCS] [mailto:kblack05;sprintspectrum.com] Sent: Friday, November 08, 2002 5:12 PM To: '[EMAIL PROTECTED]' Cc: [EMAIL PROTECTED] Subject: RE: MySQL Installation Oh, okay. Sorry about that, the RedHat rpm's puts the admin binary in the server package, but MySql.com has it in the client's: $ rpm -qlp MySQL-client-3.23.53a-1.i386.rpm | grep admin /usr/bin/mysqladmin /usr/share/man/man1/mysqladmin.1.gz sql, query ~Kelly W. Black PS IN the future check out http://www.rpmfind.net/ -Original Message- From: SpyProductions Support Team [mailto:support;spyproductions.com] Sent: Friday, November 08, 2002 12:51 PM To: Black, Kelly W [PCS] Cc: [EMAIL PROTECTED] Subject: RE: MysQL Installation Well hells bells! Would that be the 'Client programs (i386) (2.3M)' mentioned on the download page? :) Thanks, Mike -Original Message- From: Black, Kelly W [PCS] [mailto:kblack05;sprintspectrum.com] Sent: Friday, November 08, 2002 2:17 PM To: '[EMAIL PROTECTED]'; [EMAIL PROTECTED] Subject: RE: MysQL Installation Mysqladmin is seperate rpm ~K Black -Original Message- From: SpyProductions Support Team [mailto:support;spyproductions.com] Sent: Friday, November 08, 2002 11:13 AM To: [EMAIL PROTECTED] Subject: MysQL Installation I just installed the most recent version of MySQL with an RPM (through webmin interface), but it doesn't seem to have installed a mysqladmin.anyone know why? :) Thanks! -Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -Original Message- From: SpyProductions Support Team [mailto:support;spyproductions.com] Sent: Friday, November 08, 2002 2:26 PM To: Black, Kelly W [PCS] Cc: [EMAIL PROTECTED] Subject: RE: MySQL Installation I know this is OT a bit, but now I need to either re-compile PHP to get mysql to work or maybe upgrade from an RPM since I am on 4.1.2 - or do I need to do this at all? Even at rpmfind.net is seems hard to distinguish what it is I need. Is there anyway to get a 'complete' PHP rpm somewhere? Thanks! -Mike -Original Message- From: Black, Kelly W [PCS] [mailto:kblack05;sprintspectrum.com] Sent: Friday, November 08, 2002 5:12 PM To: '[EMAIL PROTECTED]' Cc: [EMAIL PROTECTED] Subject: RE: MySQL Installation Oh, okay. Sorry
mysql list question
This may be a (hmmmpf) dumb question or maybe the docs say, but I cannot find it. When posting this list regarding sql, query, and other info :) does the list server modify the posts to a maximum width? (For all using terminals to browse?) If so what is the line width? (80 characters?) I wanted to be within the bounds of 'good reading'. Thanks! Regards, Kelly Black Sprint PCS 18200 Von Karman Irvine, Ca. 92612 949-623-5417 Linux was very clearly the answer, but what was the question again? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Host not allowed to connect problem
Make sure /etc/hosts has this line 127.0.0.1 localhost ~Kelly W. Black -Original Message- From: Kevin Passey [mailto:kpassey;kdpsoftware.co.uk] Sent: Thursday, November 07, 2002 4:38 AM To: 'Timothy Venn'; Mysql (E-mail) Subject: RE: Host not allowed to connect problem Hello Tim, Are you getting this regardless of what you put in user,host and db tables. If you are I am getting the same thing. Any access to my database is denied except from the console. How did you install - from the rpm's ?? I will watch this thread as I am at a complete standstill with MySQL on Linux - it runs fine on windows (not what you want to hear). Regards Kevin -Original Message- From: Timothy Venn [mailto:tim;optimus.co.za] Sent: 07 November 2002 09:42 To: [EMAIL PROTECTED] Subject: Host not allowed to connect problem Hi, I am having a problem with Mysql 3.23.39 for solaris 2.6. I can do anything from the console command line but any connections from any other hosts causes a host xxx.xxx.xxx.xxx not allowed to connect to this mysql server. I have updated the host table to include a % for all hosts. Any suggestions Regards Tim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: DateTime comparation problems
Try using dashes. -mm-dd -Original Message- From: Andrew Sitnikov [mailto:sitnikov;infonet.ee] Sent: Wednesday, November 06, 2002 9:39 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: DateTime comparation problems Hello bugs, SELECT ... FROM table o WHERE o.date = 2002090100 AND o.date=2002110624 MySQL 3.23.xx - work (returns 0 rows) MySQL 4.0.4 - not work (returns 0 rows) P.S. also in 4.0.4 not work BETWEEN in this case (but as i can see it will be fixed in next release) Best regards, Andrew Sitnikov e-mail : [EMAIL PROTECTED] GSM: (+372) 56491109 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem importing data from Access
I have seen this when Windows was involved. Windows word and note pads have a different new line character than Un*x type systems. Perhaps that's what's foiling the sql query. ~K Black -Original Message- From: Gelu Gogancea [mailto:ggelu;arctic.ro] Sent: Wednesday, November 06, 2002 10:54 AM To: Schroeder, Bradley (Contractor); [EMAIL PROTECTED] Subject: Re: Problem importing data from Access Hi, Are you sure that .txt file is OK? Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Schroeder, Bradley (Contractor) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 06, 2002 7:06 PM Subject: Problem importing data from Access When I import data from the text file I exported out of Access it works correctly except that MySql seems to append and/or prepend some sort of mystery character to the fields it imports. So, I can get the data into the database, but I can't get it out (since the SQL statement can't match the mystery character). I'm using the graphical interface to import data since it fails at the command line even though my version of MySql is a later one than the required 3.22.15. The GUI shows the mystery character as a bold pipe |. The command line seems to show a problem with the size of the field. The select * from table statements look like this on the command line: +--+ | Col 1 | +--+ | data | |ta| |dat| +--+ instead of this: +--+ | Col 1 | +--+ | data | | data | | data | +--+ My guess is that there is a problem with the end of line character being exported to the text file from Access. I chose \n as the end of line character to separate records, but maybe it is something else. Any ideas? Thanks, Brad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem importing data from Access
This is incorrect. !google new line character linux windows -Original Message- From: Gelu Gogancea [mailto:ggelu;arctic.ro] Sent: Wednesday, November 06, 2002 11:33 AM To: Black, Kelly W [PCS]; Schroeder, Bradley (Contractor); [EMAIL PROTECTED] Subject: Re: Problem importing data from Access Hi, Is no difference but in *nix some text editors (like vi for example) put only the LineFeed(0Ah) character and not CarriageReturn and LineFeed like in Windows(0Dh,0Ah). Almost sure is something wrong with the .txt file.Few days a go i have a similar situation and it was from the the dump/.txt file.Data in the .txt file must be in the same order with the fields/columns from MySQL table. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Black, Kelly W [PCS] [EMAIL PROTECTED] To: 'Gelu Gogancea' [EMAIL PROTECTED]; Schroeder, Bradley (Contractor) [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, November 06, 2002 9:04 PM Subject: RE: Problem importing data from Access I have seen this when Windows was involved. Windows word and note pads have a different new line character than Un*x type systems. Perhaps that's what's foiling the sql query. ~K Black -Original Message- From: Gelu Gogancea [mailto:ggelu;arctic.ro] Sent: Wednesday, November 06, 2002 10:54 AM To: Schroeder, Bradley (Contractor); [EMAIL PROTECTED] Subject: Re: Problem importing data from Access Hi, Are you sure that .txt file is OK? Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Schroeder, Bradley (Contractor) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, November 06, 2002 7:06 PM Subject: Problem importing data from Access When I import data from the text file I exported out of Access it works correctly except that MySql seems to append and/or prepend some sort of mystery character to the fields it imports. So, I can get the data into the database, but I can't get it out (since the SQL statement can't match the mystery character). I'm using the graphical interface to import data since it fails at the command line even though my version of MySql is a later one than the required 3.22.15. The GUI shows the mystery character as a bold pipe |. The command line seems to show a problem with the size of the field. The select * from table statements look like this on the command line: +--+ | Col 1 | +--+ | data | |ta| |dat| +--+ instead of this: +--+ | Col 1 | +--+ | data | | data | | data | +--+ My guess is that there is a problem with the end of line character being exported to the text file from Access. I chose \n as the end of line character to separate records, but maybe it is something else. Any ideas? Thanks, Brad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Thread Thrashing and 3.23.53a
Note also there are methods to control loads under MySql. You can use limits from the environment shell to keep the load averages down. People may say this is not in good form, or style, but it works for me. There are several methods you can use within the my.cnf to streamline the MySql server performance. set-variable= key_buffer=16K set-variable= max_allowed_packet=1M set-variable= thread_stack=64K set-variable= table_cache=4 set-variable= sort_buffer=64K set-variable= net_buffer_length=2K If tweaking those doesn't seem to help then limit the shell that MySql is running in. shellulimit=4096 shellexport ulimit shellmysqld restart There are many methods along these lines, so please research it. Also , perhaps the loads are a function of the sql query. You might be able to use a Perl wrapper, and get your loads down. I do this by using sleep within the Perl program. Perform query. sleep 10; perform query. sleep 10; Hope this helps, ~Kelly W. Black -Original Message- From: heath boutwell [mailto:heathboutwell;yahoo.com] Sent: Tuesday, November 05, 2002 7:19 AM To: [EMAIL PROTECTED] Subject: RE: Thread Thrashing and 3.23.53a After a weekend of pretty heavy usuage (150-200 queries per sec) I can say the thread thrashing has been fixed with the latest binary. Our load averages however, are still 2-3x what they were with the older binary. Even when mysql isn't being queried at all (when apache is shut down) the load average hovers around 1.3 or so. We never saw load averages higher than 3 or so under max usage with the older binary but we saw 7 or higher over the weekend. Very strange. The end result is that I will either try compiling from source or go back to an older binary since I can't diagnose the cause of the higher load averages. Thanks to everyone on the list for their help and suggestions. __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: using libmysqld - can't connect to remote DB as client.
I think your problem might be here - you have db_connect(my_blah_database); Try using something like my $dbh = DBI-connect(DBI:mysql:$dbname:hostname, $dbuser, $dbpasswd) || die can't connect: $DBI::errstr\n; Also make sure you include the || die can't connect: $DBI::errstr\n; as in this way you can get some useful error output about the connect. Not certain but at least I am trying to help :) Oh and don't forget sql query! :) ~Kelly W. Black -Original Message- From: Steven Webb [mailto:scumola;yahoo.com] Sent: Tuesday, November 05, 2002 11:20 AM To: Mysql list Subject: Re: using libmysqld - can't connect to remote DB as client. Still nobody has answered my question. Has anyone here even tried linking with libmysqld.a before? - Steve --- Steven Webb [EMAIL PROTECTED] wrote: Nobody answered my question, so I felt like I should ask again ... I'm using libmysqld to embed a mysql server in my C app. However, I'd also like to connect to other remote mysql databases as well, but the mysql_real_connect command doesn't seem to be working when trying to connect to a remote database in the same app as the libmysqld stuff. The connect works, but I think that it's connecting to my local database and not the remote one. Here's a little code: mysql_server_init(sizeof(server_args) / sizeof(char *), server_args, server_groups); // Here's the embedded database. This works. one = db_connect(NULL); db_do_query(one, CREATE DATABASE my_blah_database); mysql_close(one); one = db_connect(my_blah_database); db_do_query(one, create table blank (num int)); mysql_close(one); /* This must be called after all other mysql functions */ mysql_server_end(); // Here's the remote connection - it connects to the same local machine, not the remote machine. two = db_remote_connect (remote_machine,remote,remote,dbname); db_do_query(two, SHOW DATABASES); Has anyone tried this. I'm using the 4.0.4 beta source tree. __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL - Windows vs MySQL Linux
I have had problems with this kind of statement: date = DATE Try date = 'DATE' or even date =curdate(); Note you can subtract date = curdate()-1; I have encountered no problems with upper/lower case in Linux and Mysql. ~Kelly W. Black -Original Message- From: John Ragan [mailto:jragan;arkansas.net] Sent: Tuesday, November 05, 2002 11:22 AM To: Mysql (E-mail); Kevin Passey Subject: Re: MySQL - Windows vs MySQL Linux you've insured that you're not simply encountering a problem with case sensitivity ? windows is not case sensitive. Hi all, I have a problem - I've built some JSP which runs ok on a windows install of MySQL, but on a Linux install of MySQL only the update part works. Here is what it does - SELECT date, clicks FROM bsafeLinks WHERE date = DATE INSERT INTO bsafeLinks (date,clicks) VALUES (CURDATE(),'0') UPDATE bsafeLinks SET clicks = clicks + 1 WHERE date = CURDATE() The insert is conditioned on the select returning no data. As I said it works on windows but not on Linux. Can anybody point me at anything obvious that I have missed. Thanks in advance. Kevin Passey -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: using libmysqld - can't connect to remote DB as client.
So use the C syntx instead. :) -Original Message- From: Steven Webb [mailto:scumola;yahoo.com] Sent: Tuesday, November 05, 2002 12:21 PM To: Black, Kelly W [PCS]; Mysql list Subject: RE: using libmysqld - can't connect to remote DB as client. Ok, but I'm writing this in C, not perl. - Steve --- Black, Kelly W [PCS] [EMAIL PROTECTED] wrote: I think your problem might be here - you have db_connect(my_blah_database); Try using something like my $dbh = DBI-connect(DBI:mysql:$dbname:hostname, $dbuser, $dbpasswd) || die can't connect: $DBI::errstr\n; Also make sure you include the || die can't connect: $DBI::errstr\n; as in this way you can get some useful error output about the connect. Not certain but at least I am trying to help :) Oh and don't forget sql query! :) ~Kelly W. Black -Original Message- From: Steven Webb [mailto:scumola;yahoo.com] Sent: Tuesday, November 05, 2002 11:20 AM To: Mysql list Subject: Re: using libmysqld - can't connect to remote DB as client. Still nobody has answered my question. Has anyone here even tried linking with libmysqld.a before? - Steve --- Steven Webb [EMAIL PROTECTED] wrote: Nobody answered my question, so I felt like I should ask again ... I'm using libmysqld to embed a mysql server in my C app. However, I'd also like to connect to other remote mysql databases as well, but the mysql_real_connect command doesn't seem to be working when trying to connect to a remote database in the same app as the libmysqld stuff. The connect works, but I think that it's connecting to my local database and not the remote one. Here's a little code: mysql_server_init(sizeof(server_args) / sizeof(char *), server_args, server_groups); // Here's the embedded database. This works. one = db_connect(NULL); db_do_query(one, CREATE DATABASE my_blah_database); mysql_close(one); one = db_connect(my_blah_database); db_do_query(one, create table blank (num int)); mysql_close(one); /* This must be called after all other mysql functions */ mysql_server_end(); // Here's the remote connection - it connects to the same local machine, not the remote machine. two = db_remote_connect (remote_machine,remote,remote,dbname); db_do_query(two, SHOW DATABASES); Has anyone tried this. I'm using the 4.0.4 beta source tree. __ Do you Yahoo!? Y! Web Hosting - Let the expert host your web site http://webhosting.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: loading files containing fixed-length fields....Mr. Dice showed me the way.
Take a look at meta syntactics. For example (using perl) my $dbh = DBI-connect('DBI:mysql:SD2:localhost', 'root','') or die Couldn't connect to database $!\n; my $SQL =EOT; insert into table(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,c ol14,col15,col16,col17) values (?, ?, ?, ?, ?,?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?,?) EOT ; my $cursor = $dbh-prepare($SQL) or die can't make cursor; while ( ) { # your data is fed into the program via STDIN my @data=split; $cursor-execute(@data); } $cursor-finish; $dbh-disconnect(); The question marks are replaced by real data on the fly from STDIN. Thanks to Richard Dice for this interesting wrinkle in my brain. sql,query foo ~Kelly W. Black -Original Message- From: Daniel Kasak [mailto:dkasak;nusconsulting.com.au] Sent: Tuesday, November 05, 2002 2:49 PM To: Alain Motasim; [EMAIL PROTECTED] Subject: Re: loading files containing fixed-length fields Alain Motasim wrote: Hello, I would like to load text files containing fixed-length fields into a MySQL table by specifying position indices like when loading files into an Oracle database, with a syntax close to this: LOAD DATA INFILE'c:\mydata.dat' append INTO MYTABLE ( ID POSITION(1:10), NAME POSITION(11:20)) I have browsed the URL http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#LOA D_DATA but I haven't found anything similar. Is there a chance I can load this kind of file inline with MySQL? Thanks for your help. Best regards, Almo I'm pretty sure the only way to do this is to define the size of each field in the table definition in MySQL, eg: create table ImportedStuff ID mediumint(10), Name varchar(10) etc Then you use something like: load data infile '/path/to/file.txt' into table ImportedStuff fields terminated by '' lines terminated by '' The fields terminated by '' and lines terminated by '' when used together tells mysql to use a fixed-width import, with the widths specified by the table definition you're importing into. I know this is a bit clumsy, but it works and it's all we've got... If you can't change the spec of your table you're importing into, try creating a tmp table with the right spec, importing into that, then appending from that into the destination table. And maybe someone can write some code which automates this work around so we can support the Oracle-style import from above (don't look at me, I'm VB and SQL only...). Dan -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: loading files containing fixed-length fields
Also see http://hotwired.lycos.com/webmonkey/backend/databases/tutorials/tutorial1.ht ml It helped me a lot here... sql query by the man ~Kelly W. Black -Original Message- From: Daniel Kasak [mailto:dkasak;nusconsulting.com.au] Sent: Tuesday, November 05, 2002 2:49 PM To: Alain Motasim; [EMAIL PROTECTED] Subject: Re: loading files containing fixed-length fields Alain Motasim wrote: Hello, I would like to load text files containing fixed-length fields into a MySQL table by specifying position indices like when loading files into an Oracle database, with a syntax close to this: LOAD DATA INFILE'c:\mydata.dat' append INTO MYTABLE ( ID POSITION(1:10), NAME POSITION(11:20)) I have browsed the URL http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#LOA D_DATA but I haven't found anything similar. Is there a chance I can load this kind of file inline with MySQL? Thanks for your help. Best regards, Almo I'm pretty sure the only way to do this is to define the size of each field in the table definition in MySQL, eg: create table ImportedStuff ID mediumint(10), Name varchar(10) etc Then you use something like: load data infile '/path/to/file.txt' into table ImportedStuff fields terminated by '' lines terminated by '' The fields terminated by '' and lines terminated by '' when used together tells mysql to use a fixed-width import, with the widths specified by the table definition you're importing into. I know this is a bit clumsy, but it works and it's all we've got... If you can't change the spec of your table you're importing into, try creating a tmp table with the right spec, importing into that, then appending from that into the destination table. And maybe someone can write some code which automates this work around so we can support the Oracle-style import from above (don't look at me, I'm VB and SQL only...). Dan -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Cannot Log into MySQL
If you have installed MySql in some non-standard location, or if the original installation was from the OS install, it may be that you need to link the socks. I have done this, others will probably complain. I had this problem on many systems newly installed. I fixed it with ln -s /var/lib/path/to/mysql.sock /tmp/mysql.sock ~Kelly W. Black -Original Message- From: Paul DuBois [mailto:paul;snake.net] Sent: Friday, November 01, 2002 6:26 PM To: CM Miller; [EMAIL PROTECTED] Subject: Re: Cannot Log into MySQL At 16:27 -0800 11/1/02, CM Miller wrote: Still workin' at it here. Ok, I've added to my PATH /usr/local/MySQL/mysql-3.23.-52-pc-linux-gnu-i686/bin and at least I'm getting this now $ mysql Error 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) Sounds like the server isn't running. I'm not sure what it is trying to tell me, btw, I'm running MySQL on the same machine that I'm trying to log into, so I don't know if this makes a difference or not. thanks again -Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql dieing
Typically if your OS runs, the mysql will too. You should only be worried with hardware if you are running large database apps on a slow system. -Original Message- From: Stephen Hitchner [mailto:steve;number41media.com] Sent: Friday, November 01, 2002 9:40 PM To: Mysql Subject: mysql dieing Hi I'm new to this list so forgive me if I break some rules with this first post. I have been receiving random mysql errors over the past few days where the server just dies. The server dies at various times, under various loads. I have included as much information as I can from various places on the server. I spent a bit of time looking around for possible known errors with little luck. I think I have narrowed this problem to hardware, but I was just wondering if anyone has experienced this. I'm using: FreeBSB 4.6 Mysql 3.23.51 Any help would be great! cheers steve / * mysqlbug */ From: kosh To: [EMAIL PROTECTED] Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:Number41Media Admin Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-3.23.51-max (Official MySQL-max binary) Environment: machine, os, target, libraries (multiple lines) System: FreeBSD mail-sql.x.com 4.6-RELEASE FreeBSD 4.6-RELEASE #0: Tue Jun 11 06:14:12 GMT 2002 [EMAIL PROTECTED]:/usr/src/sys /compile/GENERIC i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.3 20010315 (release) [FreeBSD] Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions - fno-r tti' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 132 Jun 10 21:18 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Aug 21 05:27 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 579412 Jun 10 21:18 /usr/lib/libc.so.4 Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL-max binary' --with-extra-charsets=complex --with-server-suffix=-max -- enable-thread-safe-client --enable-local-infile --enable-assembler --with-na med-z-libs=not-used --disable-shared --with-berkeley-db --with-innodb 'CFLAGS=-O3 -fno-omit-frame-pointer' CXX=gcc CC=gcc 'CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions - fno-rtti' Perl: This is perl, version 5.005_03 built for i386-freebsd /*** * mysql mail-sql.x.err file */ mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=8388600 record_buffer=131072 sort_buffer=2097144 max_used_connections=16 max_connections=100 threads_connected=5 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 225791 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 021101 21:03:55 mysqld restarted 021101 21:06:49 bdb: Log sequence error: page LSN 70:7230244; previous LSN 375 1164233 021101 21:06:50 bdb: Log sequence error: page LSN 70:7378020; previous LSN 380 8564133 021101 21:06:50 bdb: Log sequence error: page LSN 70:7621572; previous LSN 383 3442355 021101 21:07:34 bdb: ./rogers_mail_berkeley_ah/customer_recipients.db: close: 1 blocks left pinned 021101 21:07:34 bdb: ./rogers_mail_berkeley_ah/customers.db: close: 2 blocks left pinned mysqld in free(): warning: chunk is already free 021101 21:07:34 Can't init databases 021101 21:07:34 mysqld ended /*** * My Mysql Log */ 01Nov2002 21:04:05 SELECT: SELECT customers.id as id, customers.account as account, customers.salutation as salutation, customers.firstname as firstname, customers.lastname as lastname, customers.address1 as address1, customers.address2 as address2, customers.city as city, customers.postalcode as
RE: rounding behavior
Use the ROUND statement ROUND(sum(CLICKS),sum(IMPS)/sum(CLICKS)*100.0,0),2); -Original Message- From: David Garamond [mailto:davegaramond;icqmail.com] Sent: Sunday, November 03, 2002 2:31 AM To: [EMAIL PROTECTED] Subject: rounding behaviour hi, mysql select 1/29; +--+ | 1/29 | +--+ | 0.03 | +--+ 1 row in set (0.00 sec) mysql select 100.0*1/29; ++ | 100.0*1/29 | ++ | 3.448 | ++ 1 row in set (0.00 sec) mysql select 1/29*100.0; ++ | 1/29*100.0 | ++ | 3.45 | ++ 1 row in set (0.00 sec) i am slightly puzzled by mysql's behaviour in the first case. this seems to be a float division, but why does mysql rounds it to two digits after decimal? if this is supposed to be an integer division, wouldn't it be better/more predictable for mysql to return 0? this behaviour could cause subtle problem/errors because people really didn't expect this kind of behaviour. for example, i just found out today, after weeks of operation, that my sql expression: SELECT ...,if(sum(CLICKS),sum(IMPS)/sum(CLICKS)*100.0,0) as CTR FROM T generates CTR that are rounded to two digits after decimal (yes, IMPS and CLICKS are integer fields). however, after i change the expression to this: SELECT ...,if(sum(CLICKS),100.0*sum(IMPS)/sum(CLICKS),0) as CTR FROM T all is well. -- dave - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Interesting Challenge
Hi sql query wizards! I need some help. Is there a way I can take this query here = mysql SELECT cell, sector, - sum(att) as att, - sum(lc) as lc, - sum(csh) as csh, - ROUND((SUM( lc + csh ) * 100 ) / (SUM(att) - SUM(tccf + bpp + bpc + suf)),2) AS drops, - sum(tccf) as tccf, - sum(bpp) as bpp, - sum(bpc) as bpc, - sum(suf) as suf, - ROUND(SUM( tccf + bpp + bpc + suf) *100 / SUM(att),2) AS blocks, - sum(mou) as mou - FROM ss - WHERE release=CURDATE() - GROUP BY cell - HAVING sector=1 AND (cell=148 or cell=3); +--++--+--+--+---+--+--+--+--+-- --+--+ | cell | sector | att | lc | csh | drops | tccf | bpp | bpc | suf | blocks | mou | +--++--+--+--+---+--+--+--+--+-- --+--+ |3 | 1 | 734 | 12 |6 | 2.52 | 21 |0 |0 |0 | 2.86 | 1501 | | 148 | 1 | 2746 | 93 | 30 | 4.59 | 63 |0 |0 |1 | 2.33 | 4672 | +--++--+--+--+---+--+--+--+--+-- --+--+ And have it display the two rows as a total sum together in one row? I have been struggling with this and could really use some help. Thanks in advance for any ideas. Regards, Kelly Black Linux was very clearly the answer, but what was the question again? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: (beginner) mysql connection problem!
You might also need to add hostname. It depends on what GRANT sql statement was used to add the user id. Something like mysql -u userid -p -h hostname dbasename should work. If not, check that mysqld is in fact running. You can use ps -aux | grep mysql Regards, ~Kelly W. Black -Original Message- From: David Wu [mailto:dwu;stepup.ca] Sent: Monday, November 04, 2002 11:59 AM To: [EMAIL PROTECTED] Subject: (beginner) mysql connection problem! Hi everyone, On my local machine I had mysql installed, and I was able to log in and doing a test on it. but today as I am trying to login using mysql or mysql -u root -p, I got the error message saying; ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2), I had not touch the setting of the mysql at all. What should I do to solve this problem?? Thank you all for your help. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql DATETIME substraction problem/?
More appropriately, use curdate()feature from within sql. select * where date= curdate()-1; for example ~Kelly W. Black -Original Message- From: Benjamin Pflugmann [mailto:benjamin-mysql;pflugmann.de] Sent: Monday, November 04, 2002 1:06 PM To: Jim Hogan Cc: [EMAIL PROTECTED] Subject: Re: mysql DATETIME substraction problem/? Hello. On Sun 2002-11-03 at 10:44:43 -0800, [EMAIL PROTECTED] wrote: Hello! I am working on an analysis that is very much dependent on calculating time differences in seconds. The most simple example: I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ seconds (INT). The data in these DATETIMES looks fine -MM-DD HH:MM:SS as expected and actual differences between time_end and time_begin are usually on the order of 20-120 seconds. I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; You may not use +- on timestamps (DATETIME or TIMESTAMP) directly. For calculating differences between dates in seconds UNIX_TIMESTAMP() is probably the most useful, i.e. UPDATE mytable SET elapsed_seconds = UNIX_TIMESTAMP(time_end) - UNIX_TIMESTAMP(time_begin); Your method used the numer representation of the dates (e.g. 19971231235959) and did an integer substration, which will not take into account that seconds and minutes wrap at 60 and so on. [...] http://www.mysql.com/doc/en/Date_and_time_functions.html several times and did not see a different function of syntax for the UPDATE that I want to do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 I can simply use +/- operators Read that again. It only says that for using INTERVAL, i.e. SELECT 1997-12-31 23:59:59 + INTERVAL 1 SECOND; is allowed instead of SELECT DATE_ADD(1997-12-31 23:59:59, INTERVAL 1 SECOND); By way of troubleshooting, I created 6 other elapsed_time variables using small, medium and big int, and float, double and decimal to see if the result was different, but the result was the same for each. [...] That's because your problem does not stem from your storage. Btw, you can easily see that by using SELECT time_end-time_begin FROM mytable LIMIT 20; Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Interesting Challenge
This simply returns me to the documentation. Thanks -Original Message- From: James Northcott [mailto:jnorthcott;dpmg.com] Sent: Monday, November 04, 2002 12:13 PM To: Mysql (E-mail) Subject: RE: Interesting Challenge mysql SELECT cell, sector, If you only want one row, then selecting cell doesn't make any sense. Cell is different in each row you've selected. If you only want one row, don't select cell. - sum(att) as att, - sum(lc) as lc, - sum(csh) as csh, - ROUND((SUM( lc + csh ) * 100 ) / (SUM(att) - SUM(tccf + bpp + bpc + suf)),2) AS drops, - sum(tccf) as tccf, - sum(bpp) as bpp, - sum(bpc) as bpc, - sum(suf) as suf, - ROUND(SUM( tccf + bpp + bpc + suf) *100 / SUM(att),2) AS blocks, - sum(mou) as mou - FROM ss - WHERE release=CURDATE() - GROUP BY cell Group by cell means Give me a total for each cell. If you only want one row, you don't need a group by at all; if you want one row per sector, you should group by sector. - HAVING sector=1 AND (cell=148 or cell=3); This doesn't belong in the Having clause. This needlessly slows you query down. This can go in the where clause. See http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#SEL ECT. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql shared library.
Try using mysqlshow variables; Some variables can be exported to the sql server via the environment table. For example: shellulimit=2046 shellexport ulimit shellmysqld restart Hope this helps, ~Kelly W. Black -Original Message- From: Nissim Lugasy [mailto:lugasy;lerc.nasa.gov] Sent: Monday, November 04, 2002 11:21 AM To: [EMAIL PROTECTED] Subject: mysql shared library. Hi, I'm trying to access mysql shared library. mysql_init() function returns the address of MYSQL structure . I need to know what the structure looks like.? can all mysql structure parameters be configured in /etc/my.cfg ? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Interesting Challenge
Thanks!!! I appreciate all the help I can get. I am trying to validate what appears to be a working query, and will post back to the list as soon as I can confirm it works. I think many others will benefit from my working this out.. Thanks again and all my best! ~Kelly W. Black -Original Message- From: James Northcott [mailto:jnorthcott;dpmg.com] Sent: Monday, November 04, 2002 2:29 PM To: Black, Kelly W [PCS] Subject: RE: Interesting Challenge I have tried with and without having. Neither works. If you try running the query without cell, or sector, the result is an sql query error. I would remove both cell and sector from select, and move the having stuff into the where clause. Why don't you try just: select sum(att) as att from ss where release=CURDATE() and sector=1 AND (cell=148 or cell=3) I will try that link...thanks for the input. ~Kelly W. Black -Original Message- From: James Northcott [mailto:jnorthcott;dpmg.com] Sent: Monday, November 04, 2002 12:13 PM To: Mysql (E-mail) Subject: RE: Interesting Challenge mysql SELECT cell, sector, If you only want one row, then selecting cell doesn't make any sense. Cell is different in each row you've selected. If you only want one row, don't select cell. - sum(att) as att, - sum(lc) as lc, - sum(csh) as csh, - ROUND((SUM( lc + csh ) * 100 ) / (SUM(att) - SUM(tccf + bpp + bpc + suf)),2) AS drops, - sum(tccf) as tccf, - sum(bpp) as bpp, - sum(bpc) as bpc, - sum(suf) as suf, - ROUND(SUM( tccf + bpp + bpc + suf) *100 / SUM(att),2) AS blocks, - sum(mou) as mou - FROM ss - WHERE release=CURDATE() - GROUP BY cell Group by cell means Give me a total for each cell. If you only want one row, you don't need a group by at all; if you want one row per sector, you should group by sector. - HAVING sector=1 AND (cell=148 or cell=3); This doesn't belong in the Having clause. This needlessly slows you query down. This can go in the where clause. See http://www.mysql.com/documentation/mysql/bychapter/manual_Refe rence.html#SEL ECT. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: newbie: creating database error
Try using shellmysqladmin -u root -p -h hostname databasetobecreated password: ~Kelly W. Black -Original Message- From: Admin-Stress [mailto:meerkapot;yahoo.com] Sent: Monday, November 04, 2002 2:41 PM To: [EMAIL PROTECTED] Subject: newbie: creating database error After playing around with 'test' database, then I would like to create my own database. I logged in into mysql using root account and created a database. But, when I logged in using regular user, I got his error : ERROR 1044: Access denied for user: '@localhost' to database 'card' What necessary to do to create database? I tried to create a database using regular user, still got same error. I did change create_priv='Y' in user table from mysql database for this regular user. Anyone know how the basic steps to create database? or any documentation/website resource would be appreciated. Thanks. kapot __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Newbie help needed with mysql
Make sure you use the complete line of syntax mysqladmin -u username -p -h hostname create databasename password: (enter the password to that userid here) Oh yeah sql query . ~Kelly W. Black -Original Message- From: john [mailto:john;cllug.org] Sent: Monday, November 04, 2002 3:53 PM To: [EMAIL PROTECTED] Subject: Newbie help needed with mysql When I start safe_mysqld , I get a command prompt back again and ps aux shows this: mysql30269 0.0 0.1 10580 1024 pts/1S17:38 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/rock.pid mysql30271 0.0 0.1 10580 1024 pts/1S17:38 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/rock.pid mysql30272 0.0 0.1 10580 1024 pts/1S17:38 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/rock.pid Next I tried this: mysqladmin create testDB and I got this error: root@rock:/archives# mysqladmin create testDB mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (111)' Check that mysqld is running and that the socket: '/var/run/mysql/mysql.sock' exists! I havent found any info on this online as I did a search for: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (111) Both at the list archive at mysql.com and with google. I bet I could refine my search, but I dont know where to start. Please help. Thanks. John [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How much data can MySQL push out?
The government has a white paper on this. Just !google benchmark MySQL -Original Message- From: Benji Spencer [mailto:ben.spencer;moody.edu] Sent: Thursday, October 31, 2002 9:36 AM To: Benjamin Pflugmann Cc: [EMAIL PROTECTED] Subject: Re: How much data can MySQL push out? Sorry, I do not have much experience with MySQL on Suns (at least not in pushing it to the limits). On an Athlon 700Mhz selecting 1 random rows out of 6, I get over 330MB/sec (1000 queries/sec) on localhost and about 5.5MB/sec via a 100MBit TCP connection using the mysql command line client like this: this helps A LOT. MySQL doesn't have to be on the Sun Box. What OS where you using? We could put MySQL on a Windows box. I don't know that I could convince them to put it on a Linux box though. --- Ben Spencer Web Support [EMAIL PROTECTED] x 2288 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: UPS (Was: Mysql in Innodb)
Almost all modern unix type systems come with the powerd daemon. -Original Message- From: Jan Steinman [mailto:Jan;Bytesmiths.com] Sent: Thursday, October 31, 2002 10:46 AM To: [EMAIL PROTECTED] Subject: UPS (Was: Mysql in Innodb) From: gerald_clark [EMAIL PROTECTED] A UPS is of little use if you dont have software installed to shut the computer down when AC power is lost. That may be true of un-attended operation, but if someone is around to shut down when the UPS starts making noise, Bob's your uncle! But such software is standard issue with most UPS's these days, no? -- SQL SQL SQL SQL SQL SQL SQL SQL : Jan Steinman -- nature Transography(TM): http://www.Bytesmiths.com : Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services : Buy My Step Van! http://www.Bytesmiths.com/van - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php