Re: Mysql running as root
Hi Russ! Am Mi, den 16.02.2005 schrieb Russ um 18:58: I have two questions: 1) I'm still having trouble getting mysql to run as user mysql. All the file and folders have been changed to be owned by user mysql but phpadmin shows my sql running as root. When I log on as root and look at the users, I do not see a mysql user. Do I need to create this user and what permissions does it need? How can the files and folders be owned by a user if they do not exist? I feel you need to provide more information: a) On which operating system do you run MySQL? b) You write log on as root and look at the users, what do you mean: - look at the users running processes (like the Unix ps command), or - look at the users defined in the system (Unix: /etc/passwd file) ? c) Which is the mechanism you use to start MySQL? 2) When my windows environment died I was unable to dump mysql. I was able to recover the Data files for both tables I had (.myd, .myi and .frm) Also for the host database,etc. can these be copied to my new Linux enviroment or is it best to rebuild everything? I leave that to others to answer, but again you might add info: windows environment refers to some x86 / Pentium / Athlon machine, probably 32 bit, as there is no other hardware for this. But what is the hardware base of your Linux enviroment - there are many different possibilities here! Transfer of binary data may well be possible within the same byte sex (little endian or big endian) and (pointer) size (32 or 64 bit), but probably not if one of these fundamental decisions is changed (like from x86, little endian 32 bit, to PPC, as this is big endian, or to Alpha, as this is 64 bit). HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment=0
Hello. Use the NO_AUTO_VALUE_ON_ZERO sql mode. See: http://dev.mysql.com/doc/mysql/en/server-sql-mode.html Philippe Rousselot [EMAIL PROTECTED] wrote: hi, I am migrating a DB having a table with a UID not_null autoincrement the original table starts at UID=0 I cannot migrate this table autmaticaly as the line for UID=0 is automatically transformed into UID=1 and therefore I get an error message for the next line (UID=1) as being already into the table. I thought to be smart first by creating the table with no autoincrement and second altering the table to have UID unique and autoincremet but MySQL does not seem to like that I cannot modify the table to have it starting this UID=1 what can I do ? Thanks in advance Philippe -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD INDEX INTO CACHE problem
Hello. Please send us output of the following statements: SHOW CREATE TABLE your_table; SHOW VARIABLES; SHOW STATUS; What output does the following command produce: myisamchk -d -v your_table HMax [EMAIL PROTECTED] wrote: Hello there, We have a problem with the LOAD INDEX INTO CACHE command which is supposed to be fixed in version 4.1.10 It may be fixed, but then we don't get the way to make it work. We want to load all the indexes of one of our big table into the main key cache. This table is myISAM, and has all sort of indexes, including UNIQUE AND FULLTEXT. When we try to load the indexes into cache, we have the following error : Indexes use different block size Operation Failed Now we created a small test table with 2 rows : A integer, primary key, and a varchar(100) filled 10 times with MD5 values of NOW(). When the varchar row is not indexed, the command works fine, but when we index it, LOAD INDEX INTO CACHE returns the same error. This means this is not the FULLTEXT which create the problem. Key buffer block size is set to 1024. Tried to change it to 2048 but won't do. Any help would be greatly appreciated. Otherwise, I'll post a bug report. Thank you -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is DELETE QUICK supported in 3.22.32?
Hello. Look likes no, because the grammar of DELETE statement doesn't provide this option. See sql/sql_yacc.yy source file. Jim Hoadley [EMAIL PROTECTED] wrote: Is DELETE QUICK supported in MySQL version 3.22.32? -- Jim __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dual Xeon EM64T fedora3 x86_64
Hello. Try -m command line option to see all threads. Paul Chinen - NB [EMAIL PROTECTED] wrote: Hello, I just installed MySQL-server-4.0.23-0.x86_64.rpm on Dual Xeon 3.2 64 bit 8G of RAM with Fedora3 x86_64. I noticed (doing a ps ax|grep mysql) that there is only one mysqld process running. Is this normal? BR Paul -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql running as root
Hello. Please tell us, in what way do you launch your MySQL server. Send your configuration file. sql running as root. When I log on as root and look at the users, I do not see a mysql user. Do I need to create this user and what permissions does it Log on into the server and use 'ps aux' command to check under what user account mysqld running is. Look into /etc/passwd file to check that mysql user is present on your system. the host database,etc. can these be copied to my new Linux enviroment or is it best to rebuild everything? I think you should, dump your data from the live windows server with the mysqldump program (if you don't have such, setup it and copy tables to its data directory). And then, import your data from the dump file into the new Linux server. Russ [EMAIL PROTECTED] wrote: I have two questions: 1) I'm still having trouble getting mysql to run as user mysql. All the file and folders have been changed to be owned by user mysql but phpadmin shows my sql running as root. When I log on as root and look at the users, I do not see a mysql user. Do I need to create this user and what permissions does it need? How can the files and folders be owned by a user if they do not exist? 2) When my windows environment died I was unable to dump mysql. I was able to recover the Data files for both tables I had (.myd, .myi and .frm) Also for the host database,etc. can these be copied to my new Linux enviroment or is it best to rebuild everything? Thanks in advance for any help!! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE Opposite
Hello. Use 'SELECT INTO OUTFILE ...' See: http://dev.mysql.com/doc/mysql/en/select.html shaun thornburgh [EMAIL PROTECTED] wrote: Hi, The following function loads data from a file: http://dev.mysql.com/doc/mysql/en/load-data.html Is there a function like this that I can use to save the results of a query to a CSV file for the user of my PHP application to donwload? Thanks for your help -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication issue: I/O thread dies immediately after START SLAVE with no logged errors
Hello. Have you done a FLUSH PRIVILEGES after granting the rights to the slave user (not needed if you used GRANT clause). I didn't see this statement in your previous message? Do you execute a 'RESET SLAVE' on your slave host, when you make another attempt to setup the replication? Does replication work without replicate-xxx rules? Tierney Thurban [EMAIL PROTECTED] wrote: In response to Gleb Paharenko and Bruce Dembecki: Gleb Paharenko wrote: Please send us an output of SHOW MASTER STATUS ans SHOW SLAVE STATUS. Can you reproduce the problem using official binaries? I have included the output of those two commands below. I will try to reproduce the problem using non-debian-specific binaries. (As a side note, I originally attempted to set these servers up with version 4.0.23-4 (debian package) before reinstalling with version 4.1.9-2 (debian package), and I appeared to have the same problem in both cases.) Bruce Dembecki wrote: Looking at your my.cnf files I don't see where you've told the slave what server to connect to. The slave needs to know what server is the master. This is usually accomplished by including a couple of lines in my.cnf. [...] If the master.info file exists and is blank or doesn't include enough information, delete it. If it's there and looks right, include it's contents in your next mail here (you can blank out the username/password info). I used the CHANGE MASTER TO command interactively, rather than setting the master in the config file. I assumed that this would be sufficient, and from the log on the master (see my original email) it does appear that the slave is logging in, at least very briefly. I've included the contents of my master.info file below. It seems appropriate to me, but I've never seen one before. :) Thanks, Tierney mysql SHOW MASTER STATUS\G *** 1. row *** File: mysql-bin.01 Position: 79 Binlog_Do_DB: replicated Binlog_Ignore_DB: mysql,test 1 row in set (0.00 sec) mysql SHOW SLAVE STATUS\G *** 1. row *** Slave_IO_State: Master_Host: 192.168.0.19 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.01 Read_Master_Log_Pos: 79 Relay_Log_File: training1-relay-bin.07 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.01 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: replicated Replicate_Ignore_DB: mysql,test Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 79 Relay_Log_Space: 4 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL 1 row in set (0.00 sec) # cat master.info 14 mysql-bin.01 79 192.168.0.19 username password 3306 60 0 Tierney Thurban [EMAIL PROTECTED] wrote: Hi all. Sorry if you get this twice -- it was posted to mysql-replication earlier, but it doesn't look like that list is really used. I'm having a problem with my replication setup. This is my first time setting up replication, so this may be a simple problem. I'm using one master and one slave, both running debian-testing, and they both have brand new 4.1.9 mysql installs (via apt-get). The problem is that each time I do a START SLAVE, the I/O thread dies almost immediately. I can see it running only if I do START SLAVE; SHOW SLAVE STATUS\G on a single line. The master's log shows the following each time I START SLAVE or START SLAVE IO_THREAD: 6 Connect slave@IP on 6 Query SELECT UNIX_TIMESTAMP() 6 Query SHOW VARIABLES LIKE 'SERVER_ID' 6 Query SELECT @@GLOBAL.COLLATION_SERVER 6 Query SELECT @@GLOBAL.TIME_ZONE 6 Query SHOW SLAVE HOSTS 6 Quit There are no error messages in the .err file on either server, even with --log-warnings on both. I've added everything appropriate that I've been able to find to my.cnf on each server (see below). If anyone has any suggestions, please let me know -- I've been looking through docs and mailing lists for quite some time now, with no luck. Thanks, Tierney Here's what I did to set up
Re: select last row
Hello. This is a frequently asked question in the list. See for example: http://lists.mysql.com/mysql/176753 Mulley, Nikhil [EMAIL PROTECTED] wrote: Hi All, I have a table which is being continuosly updated, I just wanted to know = how to output only the last row with the select statement. can anyone please tell me howto. thanks, Nikhil -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.7 Character set problem ( Ithink?)
Hello. The value of the character_set_system in this case doesn't have affect in this case. You table has latin2 as default character set, and all your character_set_xxx variables have a latin1 value. Ian Gibbons [EMAIL PROTECTED] wrote: On 12 Feb 2005 at 14:09, Gleb Paharenko wrote: Hello. Please tell us, what output the following statement produces: SHOW VARIABLES LIKE '%char%'; Hi Gleb, mysql SHOW VARIABLES LIKE '%char%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.08 sec) I assume the character_set_system being utf8 is the problem, but how do I change it? You can use hexademical values for inserting the data. See: http://dev.mysql.com/doc/mysql/en/hexadecimal-values.html I've taken a look at this and it could prove very useful. I wish I had the time to read the whole manual! Thanks Ian -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: collate latin1_general_ci Error
Hello. I just installed a new software package that included a MySQL upgrade. What exact version of MySQL do you use? Also, I noticed that when I create tables, I no longer have a choice for InnoDB, so I've been choosing MyISAM. If I leave it at default, what kind of table would I get? In what way do you create you tables? version for the right syntax to use near 'collate latin1_general_ci NOT NULL default '', `IDRealm` varc Please, send a piece of your file where the error occurs. David Blomstrom [EMAIL PROTECTED] wrote: I just installed a new software package that included a MySQL upgrade. Everything was working fine until I tried to import one of my tables to my online database and got this error: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'collate latin1_general_ci NOT NULL default '', `IDRealm` varc What does this mean, and what's the solution? Also, I noticed that when I create tables, I no longer have a choice for InnoDB, so I've been choosing MyISAM. If I leave it at default, what kind of table would I get? Actually, I'm leaning towards MyISAM anyway. I thought InnoDB were generally considered superior, but when my computer crashed, I lost most of my InnoDB tables but none of my MyISAM tables. Thanks. __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query performance
If you do I suggest you also include relevant table definitions and possibly a little sample data (plus an indication of total table sizes) and expected output, this will greatly assist anyone who my be able to help. Oh yes, and don't forget to state the version of MySQL you are running. Coz On Wed, 16 Feb 2005 18:22:11 -0700, Ryan McCullough [EMAIL PROTECTED] wrote: Can I post a query to this list and ask for help optimizing it? -- Ryan McCullough mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
EXPLAIN: Select tables optimized away
MySQL 4.1.10 What does Select tables optimized away mean ? mysql explain SELECT COUNT(*) AS total FROM members_twining_main; | 1 | SIMPLE | NULL | NULL | NULL | NULL |NULL | NULL | NULL | Select tables optimized away | Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: EXPLAIN: Select tables optimized away
Gabriel, I think it means that this count can be done from an index, so there's no need to access the actual table at all. Andy -Original Message- From: Gabriel PREDA [mailto:[EMAIL PROTECTED] Sent: 17 February 2005 11:16 To: mysql@lists.mysql.com Subject: EXPLAIN: Select tables optimized away MySQL 4.1.10 What does Select tables optimized away mean ? mysql explain SELECT COUNT(*) AS total FROM members_twining_main; | 1 | SIMPLE | NULL | NULL | NULL | NULL |NULL | NULL | NULL | Select tables optimized away | Gabriel PREDA -- 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: EXPLAIN: Select tables optimized away
Hi Counts are extremely fast and since you have no WHERE statement, it takes the count value straight from the internals and does not look at the tables or an index if I remember correctly. Mike - Original Message - From: Gabriel PREDA [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: February 17, 2005 6:15 AM Subject: EXPLAIN: Select tables optimized away MySQL 4.1.10 What does Select tables optimized away mean ? mysql explain SELECT COUNT(*) AS total FROM members_twining_main; | 1 | SIMPLE | NULL | NULL | NULL | NULL |NULL | NULL | NULL | Select tables optimized away | Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 2005-02-14 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Filtering a date/time out of timestamp
I have been trying to figure out a way to filter a date out of timestamp to no success. How do I filter out specific critieria that I need using now()? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Filtering a date/time out of timestamp
[snip] I have been trying to figure out a way to filter a date out of timestamp to no success. How do I filter out specific critieria that I need using now()? [/snip] http://www.mysql.com/substring WHERE now() = substring(dateTimeColumnName, 1, 8) should get it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: EXPLAIN: Select tables optimized away
O I see... I read this in MySQL Manual... I believe it's something like SHOW TABLE STATUS LIKE 'table_name' And then extract the number of rows... I never saw Select tables optimized away and it confused me ! Gabriel PREDA - Original Message - From: O'K Web Design [EMAIL PROTECTED] Subject: Re: EXPLAIN: Select tables optimized away Hi Counts are extremely fast and since you have no WHERE statement, it takes the count value straight from the internals and does not look at the tables or an index if I remember correctly. Mike - Original Message - From: Gabriel PREDA [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: February 17, 2005 6:15 AM Subject: EXPLAIN: Select tables optimized away MySQL 4.1.10 What does Select tables optimized away mean ? mysql explain SELECT COUNT(*) AS total FROM members_twining_main; | 1 | SIMPLE | NULL | NULL | NULL | NULL |NULL | NULL | NULL | Select tables optimized away | Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filtering a date/time out of timestamp
Thanks to both of you, here is my string: select * from test where date(now())=substring(date,1,10); :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Left join results - can we make it produce at most 1 row?
Hi All, I have two tables in a children's program registration system, parent and child. 1 row in the parent table corresponds to 1 or more rows in the child table. Right now, when I do SELECT * FROM parent LEFT JOIN child ON parent.ID = child.parentID I get multiple rows for each parent if they registered more than one child. This is normal and expected I know, but I am wondering is there a way to return the children on the *same* row, so that the first bit of the row is the parent's information, and the second part of the row is something like child1.fname, child1.lname, child2.fname, child2.lname, etc. I'm not really tied to LEFT JOIN if I have to use another syntax. I am also not concerned about speed, because this query would be run at most a few times per week by an admin. It can be inefficient - as long as it won't crash the server! :-) Any help? Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left join results - can we make it produce at most 1 row?
Joshua Beall wrote: Hi All, I have two tables in a children's program registration system, parent and child. 1 row in the parent table corresponds to 1 or more rows in the child table. Right now, when I do SELECT * FROM parent LEFT JOIN child ON parent.ID = child.parentID I get multiple rows for each parent if they registered more than one child. This is normal and expected I know, but I am wondering is there a way to return the children on the *same* row, so that the first bit of the row is the parent's information, and the second part of the row is something like child1.fname, child1.lname, child2.fname, child2.lname, etc. I'm not really tied to LEFT JOIN if I have to use another syntax. I am also not concerned about speed, because this query would be run at most a few times per week by an admin. It can be inefficient - as long as it won't crash the server! :-) Any help? Sincerely, -Josh Use GROUP BY with GROUP_CONCAT: http://dev.mysql.com/doc/mysql/en/group-by-functions.html HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left join results - can we make it produce at most 1 row?
Wolfram Kraus [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Use GROUP BY with GROUP_CONCAT: http://dev.mysql.com/doc/mysql/en/group-by-functions.html Thanks, I'll take a look and see if I can figure that out. I wasn't aware of the GROUP_CONCAT function; it looks like that might be just what I need. Any examples that apply to my situation would be greatly appreciate! :-) Sincerely, -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Left join results - can we make it produce at most 1 row?
Joshua Beall wrote: Wolfram Kraus [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Use GROUP BY with GROUP_CONCAT: http://dev.mysql.com/doc/mysql/en/group-by-functions.html Thanks, I'll take a look and see if I can figure that out. I wasn't aware of the GROUP_CONCAT function; it looks like that might be just what I need. Any examples that apply to my situation would be greatly appreciate! :-) Sincerely, -Josh Untested!!! (No 4.1 available here) SELECT GROUP_CONCAT(child1.fname) FROM parent LEFT JOIN child ON parent.ID = child.parentID GROUP BY(parent.ID) You may also need some string functions: http://dev.mysql.com/doc/mysql/en/string-functions.html HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql heartbeat
Hi Pete, Zabbix looks pretty nice... does it allow you to get insight into currently running apps, such as checking on the health of individual tables within a db? ~mathew Pete Moran wrote: A monitoring solution which can monitor mysql as well as pretty much any service on nix and win platforms is zabbix (http://www.zabbix.com), its very easy to setup and personally I think its excellent. It may be overkill if you just want to check a db is up though -Original Message- From: Mathew Ray [mailto:[EMAIL PROTECTED] Sent: Thursday, 17 February 2005 2:35 AM To: mysql@lists.mysql.com Subject: mysql heartbeat Has anyone used or built a db monitoring tool for MySQL? I have come across various system-level heartbeat tools, but I am specifically looking for a solution that will work on Win2K Server and RedHat boxes, as we have several servers that each run different instances of mysql. No replication or clustering here, just looking to get notification and execute some php if a specific db or table isn't accessible. I have a rough plan for making my own, but I thought I would check here to see if anyone had a suggestion... Thanks, ~mathew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Subquery problems
hello all, I am a newbe working with MySQL. I have a problem that I can't figure out how to do. I have 2 tables that I want to take out data from, the tables looks like below. tableA == ID VARCHAR(12) NAMEVARCHAR(255) CREATED TIMESTAMP tableB == USER_ID VARCHAR(12) ID VARCHAR(12) SUB_DATETIMESTAMP CREATED TIMESTAMP Okay here is how it looks in them. tableA ID NAMECREATED 01 boat20050117103245 02 car 20050213133418 03 mc 20050214015902 04 bike20050217081232 tableB USER_ID ID SUB_DATECREATED 100002 20050214135412 20050213133418 100104 20050215143410 20050213124415 100204 20050213133418 20050214133418 100303 20050213133418 20050213133418 Now to my problem, I want to get out the ID,NAME from tableA and then from tableB get the count of how many that that choosen the specific alternative in the ID column. The problem is also that I only want to specify the ID once in the sql-question. My answer I want to have from my question should be something like this: 01 boat 0 02 car 1 03 mc 1 04 bike 2 Would be greatful if somebody could help! -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select where multiple joined records match
I am guessing the long duration is caused by having to do complete table scans. How big is your dataset? What about creating another index in resource_goals that includes GoalNumber and ResourceID? Perhaps even Subject, Grade, and NumericGrade As I learned just a couple days ago, making sure your JOIN conditions and WHERE clause can refer to an index can speed up queries 1000x or more. Try tacking an EXPLAIN before your select and see how many rows MySQL things are being examined... optimally these should be very low, which indexes may be able to help with. Thanks, Mathew AM Thomas wrote: Hi there, I'll answer your questions below, but I should tell you that it looks like even three or four joins seem to make this too slow. Theoretically, the user could select up to 15 or so GoalNumber values, 12 for Grade, and about 20 possibilities for Subject - clearly it wouldn't be useful to specify that many items in a search, but that's the theoretical maximum; 4 or 5 values isn't unreasonable, though. Four ANDed goal numbers plus a subject and a grade slowed the search (on the shared commercial web host I'm using) into the 3 minute range, and that's with a regular join, not a left join. This is the SELECT that took about 3 minutes (3 trials, simplifying slightly each time, simplest given here): select r.id from resources as r join resource_goals as g0 on (r.id=g0.ResourceID) join resource_goals as g1 on (r.id=g1.ResourceID and g1.GoalNumber=1) join resource_goals as g2 on (r.id=g2.ResourceID and g2.GoalNumber=2) join resource_goals as g3 on (r.id=g3.ResourceID and g3.GoalNumber=3) join resource_goals as g4 on (r.id=g4.ResourceID and g4.GoalNumber=4) where ((g0.Subject='Social_Studies') and (g0.Grade='4th')) group by r.id; The fastest time was 2 min 48 sec. Last time (simplest query) was 3 min 2 sec. I'm really running out of time on this project, so I just went ahead and made the user interface such that users can only select one subject, grade, and/or goal number at a time. It's probably a sound decision from a usability perspective, so I'm not too sad. If I decide to make this work in the future, I'd probably have to just do a SELECT for each ANDed field, get the list of resource id's for each SELECT, then find the intersection of the lists in Perl. If I could speed this up with some kind of indexing, I'd love to know about it. The GROUP BY phrase is because I wanted just one row per resource. It seemed like I'd get a row for each condition/resource (didn't test it with the final ). I'm actually doing SELECT * FROM... in my code, and not using the resource_goals information in my output (that's a separate view at present, generated by different Perl code). It seems to work fine without the NOT NULL parts, you're right. I was wondering about that, but was sleepy enough at the time that I didn't trust my thinking. Sorry about not including my table defs :-( . I guess I was just hoping for a general approach, and didn't realize that anyone would be interested enough to read all that detail and provide and exact solution for me. Of course, now I realize that it would have simplified our discussion. Anyway, late but not never, and for help to whoever finds this in the list archives someday, here are my table defs (you're right - the goal number is a TINYINT): (Below is an abridged version of the resources table ; it also contains about 60 more TINYINT fields which are essentially used as booleans, some of which I hope to eliminate. Yes, I could have used SET or something, but I didn't for various reasons.) CREATE TABLE resources ( id INT UNSIGNED PRIMARY KEY, Title TEXT, ResourceType_THJHArticle TINYINT, ResourceType_NIEArticle TINYINT, DataEntryName TINYTEXT, Date DATETIME, Notes TEXT, Made_Keywords TEXT); CREATE TABLE resource_goals ( goal_id INT UNSIGNED PRIMARY KEY, ResourceID INT, Grade TINYTEXT, Subject TINYTEXT, GoalNumber TINYINT, NumericGrade TINYINT); Thanks a bunch for your help; I'm finding this more interesting than I thought I would. On Wed, 16 Feb 2005 11:08:20 -0500, Michael Stassen [EMAIL PROTECTED] wrote: AM Thomas wrote: Guarded exclamations of success! This seems to be working for me so far - thank you! Here's an actual example of a working query on my database; the field names are a little different (sorry, I was trying to make my earlier example more readable). The main change, though, is that I did plain 'join' instead of 'left join', which seems to make it much faster. It was pretty slow at first. Yes, LEFT JOIN does extra work, and it wasn't needed here. mysql select r.id, ga.Grade, ga.GoalNumber, gb.GoalNumber from resources as r join resource_goals as ga on r.id=ga.ResourceID and ga.Grade='4th' and ga.GoalNumber='1' join resource_goals as gb on r.id=gb.ResourceID and gb.Grade='4th' and
Re: Subquery problems
Joppe A [EMAIL PROTECTED] wrote on 02/17/2005 09:45:31 AM: hello all, I am a newbe working with MySQL. I have a problem that I can't figure out how to do. I have 2 tables that I want to take out data from, the tables looks like below. tableA == ID VARCHAR(12) NAMEVARCHAR(255) CREATED TIMESTAMP tableB == USER_ID VARCHAR(12) ID VARCHAR(12) SUB_DATETIMESTAMP CREATED TIMESTAMP Okay here is how it looks in them. tableA ID NAMECREATED 01 boat20050117103245 02 car 20050213133418 03 mc 20050214015902 04 bike20050217081232 tableB USER_ID ID SUB_DATECREATED 100002 20050214135412 20050213133418 100104 20050215143410 20050213124415 100204 20050213133418 20050214133418 100303 20050213133418 20050213133418 Now to my problem, I want to get out the ID,NAME from tableA and then from tableB get the count of how many that that choosen the specific alternative in the ID column. The problem is also that I only want to specify the ID once in the sql-question. My answer I want to have from my question should be something like this: 01 boat 0 02 car 1 03 mc 1 04 bike 2 Would be greatful if somebody could help! -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm Here's one way to do it (there are others) but this works on older server versions because it doesn't use a subquery. SELECT A.ID, A.NAME, COUNT(B.USER_ID) FROM tableA A LEFT JOIN tableB B ON B.ID = A.ID GROUP BY a.ID, A.NAME; Suggested additional reading: http://dev.mysql.com/doc/mysql/en/select.html http://dev.mysql.com/doc/mysql/en/join.html http://dev.mysql.com/doc/mysql/en/group-by-functions.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Subquery problems
Joppe A wrote: hello all, I am a newbe working with MySQL. I have a problem that I can't figure out how to do. I have 2 tables that I want to take out data from, the tables looks like below. tableA == ID VARCHAR(12) NAMEVARCHAR(255) CREATED TIMESTAMP tableB == USER_ID VARCHAR(12) ID VARCHAR(12) SUB_DATETIMESTAMP CREATED TIMESTAMP Okay here is how it looks in them. tableA ID NAMECREATED 01 boat20050117103245 02 car 20050213133418 03 mc 20050214015902 04 bike20050217081232 tableB USER_ID ID SUB_DATECREATED 100002 20050214135412 20050213133418 100104 20050215143410 20050213124415 100204 20050213133418 20050214133418 100303 20050213133418 20050213133418 Now to my problem, I want to get out the ID,NAME from tableA and then from tableB get the count of how many that that choosen the specific alternative in the ID column. The problem is also that I only want to specify the ID once in the sql-question. My answer I want to have from my question should be something like this: 01 boat 0 02 car 1 03 mc 1 04 bike 2 Would be greatful if somebody could help! Try something like this: SELECT ID,NAME,COUNT(*) FROM tableA LEFT JOIN tableB ON tableA.ID = tableB.ID GROUP BY ID,NAME -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Subquery problems
Roger Baklund wrote: Try something like this: SELECT ID,NAME,COUNT(*) FROM tableA LEFT JOIN tableB ON tableA.ID = tableB.ID GROUP BY ID,NAME Nope, sorry, that won't work, ID exists in both tables thus it must be prefixed with table name or alias: SELECT tableA.ID,... GROUP BY tableA.ID,... But Shawn Green allready gave you the answer, so I suppose you are in control of things. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
Hal Vaughan wrote: I've tried this by running 2 instances of mysqld, the first with no arguments, and the second like this: mysqld --port=3307 --datadir=/dbtest/mysql I have to run mysqld directly -- not through safe_mysqld (which /etc/init.d/mysql calls). If I run it through safe_mysqld, I can run only one instance at a time, it will exit without running a new instance if it detects one already running. FWIW, `/etc/init.d/mysql` and `safe_mysqld` are just shell scripts. Hence you can copy and change them easily to run multiple versions or instances of most software... -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
On Thursday 17 February 2005 10:18 am, Hassan Schroeder wrote: Hal Vaughan wrote: I've tried this by running 2 instances of mysqld, the first with no arguments, and the second like this: mysqld --port=3307 --datadir=/dbtest/mysql I have to run mysqld directly -- not through safe_mysqld (which /etc/init.d/mysql calls). If I run it through safe_mysqld, I can run only one instance at a time, it will exit without running a new instance if it detects one already running. FWIW, `/etc/init.d/mysql` and `safe_mysqld` are just shell scripts. Hence you can copy and change them easily to run multiple versions or instances of most software... I see that, but even when I bypass them, I can run 2 instances of mysqld, it shows up in the task list as 2 separate tasks, but they both use the data directory specified in the last instance I run. I'm trying to get 2 different instances of mysqld running at the same time, each using a different port and different data directory. Hal -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD INDEX INTO CACHE problem
Hello there. OK I'll paste the results of commands you asked right after my reply, because we found out where the problem comes from. The myisamchk command showed that the index on the VarChar has a block size of 2048 instead of 1024. However, when I turn this index to a FULLTEXT one, the block size is back to 1024, and the LOAD INDEX INTO CACHE works. Now this is a problem because our huge table needs both our FULLTEXT indexes and some on VARCHAR fields too. At least we know where it comes from. Now, is there a fast solution ? We were waiting for this bug correction to study a release date for our application :/ Thank you, and here is the results : SHOW CREATE TABLE `tbltest`; CREATE TABLE `tbltest` ( `testid` int(10) unsigned NOT NULL auto_increment, `testvalue` varchar(100) NOT NULL default '', PRIMARY KEY (`testid`), KEY `BOB` (`testvalue`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 SHOW VARIABLES; +-+-+ | Variable_name | Value | +-+-+ | back_log| 50 | | basedir | D:\mysql\4.1\ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client| utf8| | character_set_connection| utf8| | character_set_database | utf8| | character_set_results | utf8| | character_set_server| utf8| | character_set_system| utf8| | character_sets_dir | D:\mysql\4.1\share\charsets/| | collation_connection| utf8_general_ci | | collation_database | utf8_general_ci | | collation_server| utf8_general_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | D:\mysql\4.1\Data\ | | date_format | %Y-%m-%d| | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000| | expire_logs_days| 0 | | flush | OFF | | flush_time | 1800| | ft_boolean_syntax | + -()~*:| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit| 20 | | ft_stopword_file| (built-in) | | group_concat_max_len| 1024| | have_archive| NO | | have_bdb| NO | | have_compress | YES | | have_crypt | NO | | have_csv| NO | | have_example_engine | NO | | have_geometry | YES | | have_innodb | DISABLED| | have_isam | NO | | have_ndbcluster | NO | | have_openssl| NO | | have_query_cache| YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink| YES | | init_connect| | | init_file | | | init_slave | | | innodb_additional_mem_pool_size | 2097152 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | |
MySQL and DNS problem
Hello everyone, We have a few MySQL servers (4.1.8) running on RedHat ES3. We're having problems when trying to use hostnames in the grant command. Example: create database blah; grant all on blah.* to 'user'@'host' identified by ''; (also have used the FQDN instead of just host) When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' Our DNS servers have correct forward and reverse entries for all of our machines. I read the docs about MySQL and DNS, but I still can't figure this out. Thanks, Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: is DELETE QUICK supported in 3.22.32?
I've determined it is not supported. Thx. -- Jim --- Jim Hoadley [EMAIL PROTECTED] wrote: Is DELETE QUICK supported in MySQL version 3.22.32? -- Jim __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
Hal Vaughan wrote: I see that, but even when I bypass them, I can run 2 instances of mysqld, it shows up in the task list as 2 separate tasks, but they both use the data directory specified in the last instance I run. Uh, that doesn't really make sense -- a *running* instance isn't going to switch data directories because another process started up :-) Sounds like something in the way you're invoking this is causing the first instance to be restarted, and then it's using the second set of parameters. My preference to do this is to create a complete new config file, say /etc/alt.cnf, and use that to configure the second process. That way you can be relatively sure there aren't any overlapping resources. HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
http://dev.mysql.com/doc/mysql/en/multiple-unix-servers.html There may be some helpful notes there. How are you determining which datadir mysqld is using when it's actually running? Are you logging in with mysql or mysqladmin? Make sure that if you are doing so, that you're actually logging in to each server separately, and not just looking at the variables for the same server that you've logged into twice. At the very least, try passing --port and --datadir and any other necessary options on command line for both mysqld processes. Command line options should override all others, so if it works then, you know you have a problem with your cnf file or environment variables. Kristen Hal Vaughan wrote: On Thursday 17 February 2005 10:18 am, Hassan Schroeder wrote: Hal Vaughan wrote: I've tried this by running 2 instances of mysqld, the first with no arguments, and the second like this: mysqld --port=3307 --datadir=/dbtest/mysql I have to run mysqld directly -- not through safe_mysqld (which /etc/init.d/mysql calls). If I run it through safe_mysqld, I can run only one instance at a time, it will exit without running a new instance if it detects one already running. FWIW, `/etc/init.d/mysql` and `safe_mysqld` are just shell scripts. Hence you can copy and change them easily to run multiple versions or instances of most software... I see that, but even when I bypass them, I can run 2 instances of mysqld, it shows up in the task list as 2 separate tasks, but they both use the data directory specified in the last instance I run. I'm trying to get 2 different instances of mysqld running at the same time, each using a different port and different data directory. Hal -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code.
Re: Mysql running as root
On Wednesday 16 February 2005 08:48 pm, Gleb Paharenko wrote: Hello. Please tell us, in what way do you launch your MySQL server. Send your configuration file. sql running as root. When I log on as root and look at the users, I do not see a mysql user. Do I need to create this user and what permissions does it Log on into the server and use 'ps aux' command to check under what user account mysqld running is. Look into /etc/passwd file to check that mysql user is present on your system. the host database,etc. can these be copied to my new Linux enviroment or is it best to rebuild everything? I think you should, dump your data from the live windows server with the mysqldump program (if you don't have such, setup it and copy tables to its data directory). And then, import your data from the dump file into the new Linux server. Russ [EMAIL PROTECTED] wrote: I have two questions: 1) I'm still having trouble getting mysql to run as user mysql. All the file and folders have been changed to be owned by user mysql but phpadmin shows my sql running as root. When I log on as root and look at the users, I do not see a mysql user. Do I need to create this user and what permissions does it need? How can the files and folders be owned by a user if they do not exist? 2) When my windows environment died I was unable to dump mysql. I was able to recover the Data files for both tables I had (.myd, .myi and .frm) Also for the host database,etc. can these be copied to my new Linux enviroment or is it best to rebuild everything? Thanks in advance for any help!! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- I got my data working. I couldn't dump it since my whole windows environment went belly up. Everytime I tried and program or command my system hung. Had to do a complete format and install XP only to be able to support my wife's system. I'm now 100% Linux except for TurboTax. I will look into your other suggestions. Russ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and DNS problem
On Thursday, February 17, 2005 09:41, Ian Meyer wrote: Hello everyone, We have a few MySQL servers (4.1.8) running on RedHat ES3. We're having problems when trying to use hostnames in the grant command. Example: create database blah; grant all on blah.* to 'user'@'host' identified by ''; (also have used the FQDN instead of just host) When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' Our DNS servers have correct forward and reverse entries for all of our machines. I read the docs about MySQL and DNS, but I still can't figure this out. I know you said you have correct reverse entries, but just as a test if you run 'host 192.168.2.103' on the mysql host, does it give back the hostname you used in your grant? Thanks, Ian -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and DNS problem
Tom Crimmins wrote: On Thursday, February 17, 2005 09:41, Ian Meyer wrote: Hello everyone, We have a few MySQL servers (4.1.8) running on RedHat ES3. We're having problems when trying to use hostnames in the grant command. Example: create database blah; grant all on blah.* to 'user'@'host' identified by ''; (also have used the FQDN instead of just host) When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' Our DNS servers have correct forward and reverse entries for all of our machines. I read the docs about MySQL and DNS, but I still can't figure this out. I know you said you have correct reverse entries, but just as a test if you run 'host 192.168.2.103' on the mysql host, does it give back the hostname you used in your grant? This was run on the database server: [EMAIL PROTECTED] imeyer]$ host 192.168.2.103 103.2.168.192.in-addr.arpa domain name pointer x.x.com. [EMAIL PROTECTED] imeyer]$ host x.x.com x.x.com has address 192.168.2.103 The error message MySQL shows the IP address. Thanks, Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
On Thursday 17 February 2005 10:56 am, Hassan Schroeder wrote: Hal Vaughan wrote: I see that, but even when I bypass them, I can run 2 instances of mysqld, it shows up in the task list as 2 separate tasks, but they both use the data directory specified in the last instance I run. Uh, that doesn't really make sense -- a *running* instance isn't going to switch data directories because another process started up :-) That's what's happening. I have no idea why. Here's what I'm doing now: (each command is in a separate console) mysqld --port=3307 --datadir=/dbtest/mysql mysqld --port=3306 --datadir=/var/lib/mysql mysql --port=3307 mysql --port=3306 When I do this, then do a show databases; in either new instance of mysql (both are open at the same time, in separate consoles), I get the databases in the last version of mysqld that I specified. Sounds like something in the way you're invoking this is causing the first instance to be restarted, and then it's using the second set of parameters. I do a ps-ax and get this (only mysqld tasks pasted in): 13391 pts/10 S 0:00 mysqld --port=3307 --datadir=/dbtest/mysql 13401 pts/10 S 0:00 mysqld --port=3307 --datadir=/dbtest/mysql 13402 pts/10 S 0:00 mysqld --port=3307 --datadir=/dbtest/mysql 13403 pts/10 S 0:00 mysqld --port=3307 --datadir=/dbtest/mysql 13450 pts/9S 0:00 mysqld --port=3306 --datadir=/var/lib/mysql 13451 pts/9S 0:00 mysqld --port=3306 --datadir=/var/lib/mysql 13452 pts/9S 0:00 mysqld --port=3306 --datadir=/var/lib/mysql 13453 pts/9S 0:00 mysqld --port=3306 --datadir=/var/lib/mysql My preference to do this is to create a complete new config file, say /etc/alt.cnf, and use that to configure the second process. That way you can be relatively sure there aren't any overlapping resources. I'll try that, in case config options can trump a command line. Hal HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select only values 0
Dear All, from a single row of a table, I have to select only the column, which have a value larger '0' into an outfile. How can I manage it with 'select'? Thanks, Jan Virus checked by G DATA AntiVirusKit Version: AVK 15.0.2975 from 09.02.2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
Hal Vaughan wrote: mysqld --port=3307 --datadir=/dbtest/mysql mysqld --port=3306 --datadir=/var/lib/mysql mysql --port=3307 mysql --port=3306 When I do this, then do a show databases; in either new instance of mysql (both are open at the same time, in separate consoles), I get the databases in the last version of mysqld that I specified. Aha. Light bulb :-) My preference to do this is to create a complete new config file, say /etc/alt.cnf, and use that to configure the second process. That way you can be relatively sure there aren't any overlapping resources. I'll try that, in case config options can trump a command line. They don't, but it's what you're *not* specifying here -- the two processes are sharing the default /tmp/mysql.sock socket, I'll wager. So the last daemon started is listening on it... I think you'll find creating two config files will make it a *lot* easier to get this going :-) FWIW! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and DNS problem
On Thursday, February 17, 2005 10:26, Ian Meyer wrote: Tom Crimmins wrote: On Thursday, February 17, 2005 09:41, Ian Meyer wrote: Hello everyone, We have a few MySQL servers (4.1.8) running on RedHat ES3. We're having problems when trying to use hostnames in the grant command. Example: create database blah; grant all on blah.* to 'user'@'host' identified by ''; (also have used the FQDN instead of just host) When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' Our DNS servers have correct forward and reverse entries for all of our machines. I read the docs about MySQL and DNS, but I still can't figure this out. I know you said you have correct reverse entries, but just as a test if you run 'host 192.168.2.103' on the mysql host, does it give back the hostname you used in your grant? This was run on the database server: [EMAIL PROTECTED] imeyer]$ host 192.168.2.103 103.2.168.192.in-addr.arpa domain name pointer x.x.com. [EMAIL PROTECTED] imeyer]$ host x.x.com x.x.com has address 192.168.2.103 The error message MySQL shows the IP address. You don't happen to have skip-name-resolve in your my.cnf do you? I'm sure you probably already checked that. I think the grant will create a warning anyway if you try to give a hostname with this option enabled. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and DNS problem
Tom Crimmins wrote: On Thursday, February 17, 2005 10:26, Ian Meyer wrote: Tom Crimmins wrote: On Thursday, February 17, 2005 09:41, Ian Meyer wrote: Hello everyone, We have a few MySQL servers (4.1.8) running on RedHat ES3. We're having problems when trying to use hostnames in the grant command. Example: create database blah; grant all on blah.* to 'user'@'host' identified by ''; (also have used the FQDN instead of just host) When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' Our DNS servers have correct forward and reverse entries for all of our machines. I read the docs about MySQL and DNS, but I still can't figure this out. I know you said you have correct reverse entries, but just as a test if you run 'host 192.168.2.103' on the mysql host, does it give back the hostname you used in your grant? This was run on the database server: [EMAIL PROTECTED] imeyer]$ host 192.168.2.103 103.2.168.192.in-addr.arpa domain name pointer x.x.com. [EMAIL PROTECTED] imeyer]$ host x.x.com x.x.com has address 192.168.2.103 The error message MySQL shows the IP address. You don't happen to have skip-name-resolve in your my.cnf do you? I'm sure you probably already checked that. I think the grant will create a warning anyway if you try to give a hostname with this option enabled. I definitely don't.. I even went through all the variables using 'show variables;' and found nothing in there that could attribute to my problem. This is becoming really baffling.. I may try a source compile aside from this and try that to see if there is anything different. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql running as root
On Wednesday 16 February 2005 08:48 pm, Gleb Paharenko wrote: Hello. Please tell us, in what way do you launch your MySQL server. Send your configuration file. sql running as root. When I log on as root and look at the users, I do not see a mysql user. Do I need to create this user and what permissions does it Log on into the server and use 'ps aux' command to check under what user account mysqld running is. Look into /etc/passwd file to check that mysql user is present on your system. the host database,etc. can these be copied to my new Linux enviroment or is it best to rebuild everything? I think you should, dump your data from the live windows server with the mysqldump program (if you don't have such, setup it and copy tables to its data directory). And then, import your data from the dump file into the new Linux server. Russ [EMAIL PROTECTED] wrote: I have two questions: 1) I'm still having trouble getting mysql to run as user mysql. All the file and folders have been changed to be owned by user mysql but phpadmin shows my sql running as root. When I log on as root and look at the users, I do not see a mysql user. Do I need to create this user and what permissions does it need? How can the files and folders be owned by a user if they do not exist? 2) When my windows environment died I was unable to dump mysql. I was able to recover the Data files for both tables I had (.myd, .myi and .frm) Also for the host database,etc. can these be copied to my new Linux enviroment or is it best to rebuild everything? Thanks in advance for any help!! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com I am using SuSE 9.1 Pro. I started mysql in the runlevel editor of YaST. It start when I boot the system. below are the results of the two items you asked me to look at: ps aux mysql 5471 0.0 0.4 21584 2400 ?S 07:38 0:00 /usr/sbin/mysqld /etc/passwd mysql:x:60:2:MySQL database admin:/var/lib/mysql:/bin/false This is what I see in phpMyAdmin. Welcome to phpMyAdmin 2.5.6 MySQL 4.0.18 running on localhost as [EMAIL PROTECTED] thanks again for your help. -- Russ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN Problem
I have the following 2 tables: CREATE TABLE `division_info` ( `id` int(11) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `spots` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `division` (`division`), KEY `spots` (`spots`) ) TYPE=MyISAM CREATE TABLE `team_info` ( `id` int(14) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `application` varchar(9) NOT NULL default 'No', PRIMARY KEY (`id`), KEY `division` (`division`), ) TYPE=MyISAM I'm running the following query: SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division This query runs fine. However, it only returns divisions where there is at least 1 ACCEPTED team. I also need to show divisions where there are spots but not teams have yet been ACCEPTED. A little direction would be appreciated. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Different Database Groups On the Same Computer
On Thursday 17 February 2005 11:48 am, Hassan Schroeder wrote: Hal Vaughan wrote: mysqld --port=3307 --datadir=/dbtest/mysql mysqld --port=3306 --datadir=/var/lib/mysql mysql --port=3307 mysql --port=3306 When I do this, then do a show databases; in either new instance of mysql (both are open at the same time, in separate consoles), I get the databases in the last version of mysqld that I specified. Aha. Light bulb :-) My preference to do this is to create a complete new config file, say /etc/alt.cnf, and use that to configure the second process. That way you can be relatively sure there aren't any overlapping resources. I'll try that, in case config options can trump a command line. They don't, but it's what you're *not* specifying here -- the two processes are sharing the default /tmp/mysql.sock socket, I'll wager. So the last daemon started is listening on it... Bingo! I tried adding --socket=/var/run/mysqld/mysqld6.sock for the process running with port 3306 and mysqld7.sock for the process running on port 3307. It works perfectly! Thanks! This will really help me out in getting rid of some extra boxen that are too slow and need to be retired to the parent's e-mail computers. Making the change for the one routine in my Perl modules that creates the connection to the database is trivial, too. Thanks to all who have helped with this. Hal I think you'll find creating two config files will make it a *lot* easier to get this going :-) FWIW! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select only values 0
You can't conditionally select columns with a mere select statement.. In whatever langauge you are using to process (perl, Java, whatever) you are going to have to select the entire row and use locig to pick out the columns that you want - michael dykman On Thu, 2005-02-17 at 11:44, Jan Bartholdy wrote: Dear All, from a single row of a table, I have to select only the column, which have a value larger '0' into an outfile. How can I manage it with 'select'? Thanks, Jan Virus checked by G DATA AntiVirusKit Version: AVK 15.0.2975 from 09.02.2005 -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and DNS problem
On Thu, 2005-02-17 at 10:41, Ian Meyer wrote: Hello everyone, We have a few MySQL servers (4.1.8) running on RedHat ES3. We're having problems when trying to use hostnames in the grant command. Example: create database blah; grant all on blah.* to 'user'@'host' identified by ''; (also have used the FQDN instead of just host) When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' Our DNS servers have correct forward and reverse entries for all of our machines. I read the docs about MySQL and DNS, but I still can't figure this out. Thanks, Ian Instead, try determining what the ips the host names resolve to (not the other way around) If you are GRANTing to [EMAIL PROTECTED] , you want to make sure that when the machine 'foo' connects, it is connecting as the same ip address the 'foo' resolves to when the server looks it up for example, $ host foo might translate to foo.domain.com - 20.20.20.21 [external ip] but foo is connecting as 192.168.1.21 [internal ip] and Mysql will reject the connection. -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN Problem
On Thu, 2005-02-17 at 12:08, Albert Padley wrote: I have the following 2 tables: CREATE TABLE `division_info` ( `id` int(11) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `spots` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `division` (`division`), KEY `spots` (`spots`) ) TYPE=MyISAM CREATE TABLE `team_info` ( `id` int(14) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `application` varchar(9) NOT NULL default 'No', PRIMARY KEY (`id`), KEY `division` (`division`), ) TYPE=MyISAM I'm running the following query: SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division This query runs fine. However, it only returns divisions where there is at least 1 ACCEPTED team. I also need to show divisions where there are spots but not teams have yet been ACCEPTED. A little direction would be appreciated. Thanks. Al Padley SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT == OUTER == JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and DNS problem
Michael Dykman wrote: On Thu, 2005-02-17 at 10:41, Ian Meyer wrote: Hello everyone, We have a few MySQL servers (4.1.8) running on RedHat ES3. We're having problems when trying to use hostnames in the grant command. Example: create database blah; grant all on blah.* to 'user'@'host' identified by ''; (also have used the FQDN instead of just host) When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' Our DNS servers have correct forward and reverse entries for all of our machines. I read the docs about MySQL and DNS, but I still can't figure this out. Thanks, Ian Instead, try determining what the ips the host names resolve to (not the other way around) If you are GRANTing to [EMAIL PROTECTED] , you want to make sure that when the machine 'foo' connects, it is connecting as the same ip address the 'foo' resolves to when the server looks it up for example, $ host foo might translate to foo.domain.com - 20.20.20.21 [external ip] but foo is connecting as 192.168.1.21 [internal ip] and Mysql will reject the connection. This is all internal, so that isn't an issue. See my 2nd or 3rd reply for additional `host` information for the hosts I'm trying to connect with. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql running as root
The line: MySQL 4.0.18 running on localhost as [EMAIL PROTECTED] refers to the hostname and user you log into *mysql* with. It does not tell you what user mysql is running under on the OS. The user and hostname that phpMyAdmin logs in under are changed in the phpMyAdmin config.inc.php file. Kristen Russ wrote: On Wednesday 16 February 2005 08:48 pm, Gleb Paharenko wrote: Hello. Please tell us, in what way do you launch your MySQL server. Send your configuration file. sql running as root. When I log on as root and look at the users, I do not see a mysql user. Do I need to create this user and what permissions does it Log on into the server and use 'ps aux' command to check under what user account mysqld running is. Look into /etc/passwd file to check that mysql user is present on your system. the host database,etc. can these be copied to my new Linux enviroment or is it best to rebuild everything? I think you should, dump your data from the live windows server with the mysqldump program (if you don't have such, setup it and copy tables to its data directory). And then, import your data from the dump file into the new Linux server. Russ [EMAIL PROTECTED] wrote: I have two questions: 1) I'm still having trouble getting mysql to run as user mysql. All the file and folders have been changed to be owned by user mysql but phpadmin shows my sql running as root. When I log on as root and look at the users, I do not see a mysql user. Do I need to create this user and what permissions does it need? How can the files and folders be owned by a user if they do not exist? 2) When my windows environment died I was unable to dump mysql. I was able to recover the Data files for both tables I had (.myd, .myi and .frm) Also for the host database,etc. can these be copied to my new Linux enviroment or is it best to rebuild everything? Thanks in advance for any help!! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com I am using SuSE 9.1 Pro. I started mysql in the runlevel editor of YaST. It start when I boot the system. below are the results of the two items you asked me to look at: ps aux mysql 5471 0.0 0.4 21584 2400 ?S07:38 0:00 /usr/sbin/mysqld /etc/passwd mysql:x:60:2:MySQL database admin:/var/lib/mysql:/bin/false This is what I see in phpMyAdmin. Welcome to phpMyAdmin 2.5.6 MySQL 4.0.18 running on localhost as [EMAIL PROTECTED] thanks again for your help.
Re: JOIN Problem
Would you mind giving me some additional explanation about outer join? In the mysql reference book I just found one line saying left outer join syntax exists only for compatibility with odbc. thanks! From: Michael Dykman [EMAIL PROTECTED] To: Albert Padley [EMAIL PROTECTED] CC: \MySQL List\ mysql@lists.mysql.com Subject: Re: JOIN Problem Date: Thu, 17 Feb 2005 12:20:44 -0500 On Thu, 2005-02-17 at 12:08, Albert Padley wrote: I have the following 2 tables: CREATE TABLE `division_info` ( `id` int(11) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `spots` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `division` (`division`), KEY `spots` (`spots`) ) TYPE=MyISAM CREATE TABLE `team_info` ( `id` int(14) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `application` varchar(9) NOT NULL default 'No', PRIMARY KEY (`id`), KEY `division` (`division`), ) TYPE=MyISAM I'm running the following query: SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division This query runs fine. However, it only returns divisions where there is at least 1 ACCEPTED team. I also need to show divisions where there are spots but not teams have yet been ACCEPTED. A little direction would be appreciated. Thanks. Al Padley SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT == OUTER == JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with SUM and DECIMAL field
Hi everyone, I've recentely upgarded my servers from MySQL 3.23.49 to MySQL 4.1.9. Now I have a problem when using the SUM operator on DECIMAL field. The value returned by the SUM operator when used with DECIMAL field has a coma (,) as decimal separator while it used to be have a dot (.) . If I query my table to display the DECIMAL fields (SELECT * FROM ...) I get a dot as decimal separator as expected. This doesn't look like a big issue but it prevents Connector/J from retieving the data properly. Connector/J can't parse the value of the field as it's expecting a dot as decimal separator. Here is the stack trace : java.sql.SQLException: Bad format for BigDecimal '12660,95' in column 1( . () . SUM(AMFTPF)()). at com.mysql.jdbc.ResultSet.getBigDecimal(ResultSet.java:493) I'm using Connector/J 3.0.16. One last thing : this seems to happen only on AIX. I have tried on Linux and Windows XP and everything works as expected. Is there something wrong with some of my database parameters or is this a bug ? Please let me know if someone is interested by a testcase. Reagrds, Julien LAFONTAINE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and DNS problem
On Thu, 2005-02-17 at 12:21, Ian Meyer wrote: Michael Dykman wrote: On Thu, 2005-02-17 at 10:41, Ian Meyer wrote: Hello everyone, We have a few MySQL servers (4.1.8) running on RedHat ES3. We're having problems when trying to use hostnames in the grant command. Example: create database blah; grant all on blah.* to 'user'@'host' identified by ''; (also have used the FQDN instead of just host) When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' Our DNS servers have correct forward and reverse entries for all of our machines. I read the docs about MySQL and DNS, but I still can't figure this out. Thanks, Ian Instead, try determining what the ips the host names resolve to (not the other way around) If you are GRANTing to [EMAIL PROTECTED] , you want to make sure that when the machine 'foo' connects, it is connecting as the same ip address the 'foo' resolves to when the server looks it up for example, $ host foo might translate to foo.domain.com - 20.20.20.21 [external ip] but foo is connecting as 192.168.1.21 [internal ip] and Mysql will reject the connection. This is all internal, so that isn't an issue. See my 2nd or 3rd reply for additional `host` information for the hosts I'm trying to connect with. your right, I had noticed the reverse lookup but didn't see the forward.. I note that the lookup finds an address for the fully qualified domain name, not just the lowest-level.. if host resolves to host.domain.com resolves to 'someip' and you are granting to host, it might be that mysql (very wisely and safely) chooses not to attempt the resolution with additional qualifications (domain.com). Perhaps if your GRANT was to the fully qualified name MySQL could match it to the incoming address unambiguously? -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and DNS problem
On Thursday 17 February 2005 09:41 am, Ian Meyer wrote: When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' The 192.168.2.103 is your tip that its not using a host. grant [EMAIL PROTECTED] and things will work. Then you can solve why its not resolving. Jeff pgpmTQHtRMU79.pgp Description: PGP signature
Re: JOIN Problem
Albert Padley [EMAIL PROTECTED] wrote on 02/17/2005 12:08:31 PM: I have the following 2 tables: CREATE TABLE `division_info` ( `id` int(11) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `spots` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `division` (`division`), KEY `spots` (`spots`) ) TYPE=MyISAM CREATE TABLE `team_info` ( `id` int(14) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `application` varchar(9) NOT NULL default 'No', PRIMARY KEY (`id`), KEY `division` (`division`), ) TYPE=MyISAM I'm running the following query: SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division This query runs fine. However, it only returns divisions where there is at least 1 ACCEPTED team. I also need to show divisions where there are spots but not teams have yet been ACCEPTED. A little direction would be appreciated. Thanks. Al Padley You are very, very close. You used the LEFT JOIN (correct choice) but you eliminated all of the rows from your division table without any accepted teams when you said WHERE application='ACCEPTED'. That's why you aren't getting a good count across all of your divisions. What I think you were trying to do was to tell how many teams have accepted within a division, across all divisions. That means you want to count 'ACCEPTED' teams but not teams that do not exist or teams that have some other application status, right? I have reworked your query a bit and I think I answered the question you had and I also tried to demonstrate how to get at some other information at the same time. SELECT d.division AS 'division' , d.spots as 'spots' , COUNT(t.division) AS 'total_team_count' , SUM(IF(t.application = 'ACCEPTED',1,0)) as 'teams_accepted' , SUM(IF(t.application 'ACCEPTED',1,0)) as 'teams_not_accepted' FROM division_info d LEFT JOIN team_info t ON d.division = t.division GROUP BY d.division, d.spots Using the aggregating functions like COUNT() and SUM() in this way, we are building a crosstab query (also called a pivot table). There are many other articles in this thread's archive that can help you understand how to build those types of queries with MySQL. By eliminating your WHERE clause and moving your condition into a SUM(IF()), we allowed all of the rows from the RIGHT side of the LEFT JOIN (even those with all null values) to appear in the results and thanks to the IF() we only count (by adding up the 1's) those rows with the values we want to find. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MySQL and DNS problem
Michael Dykman wrote: On Thu, 2005-02-17 at 12:21, Ian Meyer wrote: Michael Dykman wrote: On Thu, 2005-02-17 at 10:41, Ian Meyer wrote: Hello everyone, We have a few MySQL servers (4.1.8) running on RedHat ES3. We're having problems when trying to use hostnames in the grant command. Example: create database blah; grant all on blah.* to 'user'@'host' identified by ''; (also have used the FQDN instead of just host) When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' Our DNS servers have correct forward and reverse entries for all of our machines. I read the docs about MySQL and DNS, but I still can't figure this out. Thanks, Ian Instead, try determining what the ips the host names resolve to (not the other way around) If you are GRANTing to [EMAIL PROTECTED] , you want to make sure that when the machine 'foo' connects, it is connecting as the same ip address the 'foo' resolves to when the server looks it up for example, $ host foo might translate to foo.domain.com - 20.20.20.21 [external ip] but foo is connecting as 192.168.1.21 [internal ip] and Mysql will reject the connection. This is all internal, so that isn't an issue. See my 2nd or 3rd reply for additional `host` information for the hosts I'm trying to connect with. your right, I had noticed the reverse lookup but didn't see the forward.. I note that the lookup finds an address for the fully qualified domain name, not just the lowest-level.. if host resolves to host.domain.com resolves to 'someip' and you are granting to host, it might be that mysql (very wisely and safely) chooses not to attempt the resolution with additional qualifications (domain.com). Perhaps if your GRANT was to the fully qualified name MySQL could match it to the incoming address unambiguously? Tried the FQDN like 'user'@'thishost.domain.com' and it still didn't work. I'll do the source deal after lunch and let y'all know what the story is. Thanks so far for your help. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN Problem
The idea of the OUTER JOIN is that it find at least one row for the joined table, even if the conddtion for that tables fails.. the resulting row will have all nulls except for the connecting fields. it guarantees that every row of division_info which is returned by the first part of the query is represented in the final data set even if there is no correcponding team_info which matches the join condition. I'm didn't notice what version of MySQL you are running and I'm not 100% sure this is supported under MySQL 3.23 (for example) but it certainly works on my 4.1.. the outer join has been part of ANSI-SQL syntax for at least 10 years I think. On Thu, 2005-02-17 at 12:30, mel list_php wrote: Would you mind giving me some additional explanation about outer join? In the mysql reference book I just found one line saying left outer join syntax exists only for compatibility with odbc. thanks! From: Michael Dykman [EMAIL PROTECTED] To: Albert Padley [EMAIL PROTECTED] CC: \MySQL List\ mysql@lists.mysql.com Subject: Re: JOIN Problem Date: Thu, 17 Feb 2005 12:20:44 -0500 On Thu, 2005-02-17 at 12:08, Albert Padley wrote: I have the following 2 tables: CREATE TABLE `division_info` ( `id` int(11) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `spots` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `division` (`division`), KEY `spots` (`spots`) ) TYPE=MyISAM CREATE TABLE `team_info` ( `id` int(14) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `application` varchar(9) NOT NULL default 'No', PRIMARY KEY (`id`), KEY `division` (`division`), ) TYPE=MyISAM I'm running the following query: SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division This query runs fine. However, it only returns divisions where there is at least 1 ACCEPTED team. I also need to show divisions where there are spots but not teams have yet been ACCEPTED. A little direction would be appreciated. Thanks. Al Padley SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT == OUTER == JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and DNS problem
Jeff Smelser wrote: On Thursday 17 February 2005 09:41 am, Ian Meyer wrote: When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' The 192.168.2.103 is your tip that its not using a host. grant [EMAIL PROTECTED] and things will work. Then you can solve why its not resolving. Jeff I wish we could do that, however, it's not an option as we use DHCP.. so the IP's change, yet the hostname does not. Besides, that's just a cheap way to avoid fixing the problem when it should work to begin with. Our access tables are ridiculously messy as you can guess. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN Problem
Michael Dykman [EMAIL PROTECTED] wrote on 02/17/2005 12:20:44 PM: On Thu, 2005-02-17 at 12:08, Albert Padley wrote: I have the following 2 tables: CREATE TABLE `division_info` ( `id` int(11) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `spots` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `division` (`division`), KEY `spots` (`spots`) ) TYPE=MyISAM CREATE TABLE `team_info` ( `id` int(14) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `application` varchar(9) NOT NULL default 'No', PRIMARY KEY (`id`), KEY `division` (`division`), ) TYPE=MyISAM I'm running the following query: SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division This query runs fine. However, it only returns divisions where there is at least 1 ACCEPTED team. I also need to show divisions where there are spots but not teams have yet been ACCEPTED. A little direction would be appreciated. Thanks. Al Padley SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT == OUTER == JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division -- - michael dykman - [EMAIL PROTECTED] begin flame Michael, Please be so kind as to explain WHY you thought your answer was DIFFERENT than the originally posted query? The OUTER keyword is optional in MySQL. That means that LEFT JOIN and LEFT OUTER JOIN are parsed as the same token. Look at the problem again, remembering that the team_info table is the OUTER table of the JOIN, and see if you can spot the problem. I'll give you another hint, it's a SQL logic issue, not a SQL grammar issue. end flame Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: What is the max length of IN() function?
Donny Simonton wrote:
In our case we were using words, and phrases, so we would have something
like:
IN ('a', 'apple', 'apple car', 'car', 'c') etc...
We found that once it hits about 200 or so entries the query went from 0.00
seconds to about 2-3 seconds. Sometimes much more.
I would guess that it has more to do with the amount of your key
space that the list ranges over than with the absolute number of
entries. Try comparing IN ('a', 'z') (or something similar)
with IN ('a', 'aa', 'aaa', 'aab', [...], 'aaaz') (with
lots of entries, all between 'a' and 'ab', or another small
range). MySQL can use a range of the index for the second, even
though there are lots of entries, but not for the first, which
may force it to scan the whole index.
--
Keith Ivey [EMAIL PROTECTED]
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN Problem
On Feb 17, 2005, at 10:34 AM, [EMAIL PROTECTED] wrote: Albert Padley [EMAIL PROTECTED] wrote on 02/17/2005 12:08:31 PM: I have the following 2 tables: CREATE TABLE `division_info` ( `id` int(11) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `spots` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `division` (`division`), KEY `spots` (`spots`) ) TYPE=MyISAM CREATE TABLE `team_info` ( `id` int(14) NOT NULL auto_increment, `division` varchar(50) NOT NULL default '', `application` varchar(9) NOT NULL default 'No', PRIMARY KEY (`id`), KEY `division` (`division`), ) TYPE=MyISAM I'm running the following query: SELECT division_info.division AS 'division', COUNT(team_info.division) AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN team_info ON division_info.division = team_info.division WHERE application='ACCEPTED' GROUP BY division_info.division This query runs fine. However, it only returns divisions where there is at least 1 ACCEPTED team. I also need to show divisions where there are spots but not teams have yet been ACCEPTED. A little direction would be appreciated. Thanks. Al Padley You are very, very close. You used the LEFT JOIN (correct choice) but you eliminated all of the rows from your division table without any accepted teams when you said WHERE application='ACCEPTED'. That's why you aren't getting a good count across all of your divisions. What I think you were trying to do was to tell how many teams have accepted within a division, across all divisions. That means you want to count 'ACCEPTED' teams but not teams that do not exist or teams that have some other application status, right? I have reworked your query a bit and I think I answered the question you had and I also tried to demonstrate how to get at some other information at the same time. SELECT d.division AS 'division' , d.spots as 'spots' , COUNT(t.division) AS 'total_team_count' , SUM(IF(t.application = 'ACCEPTED',1,0)) as 'teams_accepted' , SUM(IF(t.application 'ACCEPTED',1,0)) as 'teams_not_accepted' FROM division_info d LEFT JOIN team_info t ON d.division = t.division GROUP BY d.division, d.spots Using the aggregating functions like COUNT() and SUM() in this way, we are building a crosstab query (also called a pivot table). There are many other articles in this thread's archive that can help you understand how to build those types of queries with MySQL. By eliminating your WHERE clause and moving your condition into a SUM(IF()), we allowed all of the rows from the RIGHT side of the LEFT JOIN (even those with all null values) to appear in the results and thanks to the IF() we only count (by adding up the 1's) those rows with the values we want to find. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn, Thanks. This was just right. Once again, you have gone beyond the initial question and not only provided the correct answer, but an explanation that helps me better understand the why behind the query. Much appreciated. Thanks again. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data Types in Mysql
Dear,I need to MySQL data type or another. But i dont know name of their header file. Please guide me.. Yours,Mohsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and DNS problem
You can specify a wildcard in the host IP, eg. grant select on mydb.* to 'someuser'@'192.168.2.%' ... which you can use to get around your DHCP issue until host lookups are fixed. -Original Message- From: Ian Meyer [mailto:[EMAIL PROTECTED] Sent: Thursday, February 17, 2005 11:46 AM To: mysql@lists.mysql.com Subject: Re: MySQL and DNS problem Jeff Smelser wrote: On Thursday 17 February 2005 09:41 am, Ian Meyer wrote: When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' The 192.168.2.103 is your tip that its not using a host. grant [EMAIL PROTECTED] and things will work. Then you can solve why its not resolving. Jeff I wish we could do that, however, it's not an option as we use DHCP.. so the IP's change, yet the hostname does not. Besides, that's just a cheap way to avoid fixing the problem when it should work to begin with. Our access tables are ridiculously messy as you can guess. Ian -- 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 and DNS problem
On Thursday 17 February 2005 11:46 am, Ian Meyer wrote: I wish we could do that, however, it's not an option as we use DHCP.. so the IP's change, yet the hostname does not. Besides, that's just a cheap way to avoid fixing the problem when it should work to begin with. Our access tables are ridiculously messy as you can guess. You have a server thats on DHCP? Well, your problem is dhcp I am sure.. But good luck with that horror flick. What is this world coming to. Jeff pgpZsHHBXfcXQ.pgp Description: PGP signature
Header files data types
Dears,I must use data type in C++,same MySQL type. But i don't name of their header files. Please guide me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and DNS problem
Jeff Smelser wrote: On Thursday 17 February 2005 11:46 am, Ian Meyer wrote: I wish we could do that, however, it's not an option as we use DHCP.. so the IP's change, yet the hostname does not. Besides, that's just a cheap way to avoid fixing the problem when it should work to begin with. Our access tables are ridiculously messy as you can guess. You have a server thats on DHCP? Well, your problem is dhcp I am sure.. But good luck with that horror flick. Our servers IPs are static, as is the DNS in /etc/resolv.conf What is this world coming to. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and DNS problem
John Trammell wrote: You can specify a wildcard in the host IP, eg. grant select on mydb.* to 'someuser'@'192.168.2.%' ... which you can use to get around your DHCP issue until host lookups are fixed. Host lookups aren't broken using the `host` command.. only when MySQL goes to look them up, which is the problem. -Original Message- From: Ian Meyer [mailto:[EMAIL PROTECTED] Sent: Thursday, February 17, 2005 11:46 AM To: mysql@lists.mysql.com Subject: Re: MySQL and DNS problem Jeff Smelser wrote: On Thursday 17 February 2005 09:41 am, Ian Meyer wrote: When trying to connect, it fails with the message: 'MySQL Error Number 1045 Access denied for user 'user'@'192.168.2.103' (using password: YES' The 192.168.2.103 is your tip that its not using a host. grant [EMAIL PROTECTED] and things will work. Then you can solve why its not resolving. Jeff I wish we could do that, however, it's not an option as we use DHCP.. so the IP's change, yet the hostname does not. Besides, that's just a cheap way to avoid fixing the problem when it should work to begin with. Our access tables are ridiculously messy as you can guess. Ian -- 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 and DNS problem
On Thursday 17 February 2005 12:29 pm, Ian Meyer wrote: You have a server thats on DHCP? Well, your problem is dhcp I am sure.. But good luck with that horror flick. Our servers IPs are static, as is the DNS in /etc/resolv.conf Then you need to make sure its compiled to the right glibc.. Since its a binary. Jeff pgpG4TIEO5elB.pgp Description: PGP signature
Weird @ in column name
I am running IntranetMySQL (ie. MySQL 4.1.9) and I am getting the strangest error: mysql use devmail Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select type_name from faq_type; ++ | @ | ++ | Build/Compile/Link Errors | | ContactUs System | | Continuous Build System| | DevServices Scripts Issues | | General Package Issues | | General Tools Issue| | Other | | PackageBuilder Issue | | Perforce License Request | | Perforce Usage | | Third-Party Request| | Versionset Creation/Update | | WebTools | ++ 13 rows in set (0.00 sec) mysql use ssimail Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select type_name from faq_type; +---+ | type_name | +---+ | Merchant Integration Compliance Evaluation (MICE) | | Merchant Integration Test Environment (MITE) | | Other SSI Projects| | Pro Merchant Self Service Integration Projects| +---+ 4 rows in set (0.01 sec) Notice how the column anme is returned as @. Has anyone ever seen anything like this before. Those tables are exaclty equivalent. They were created from the same .ddl file. --Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql running as root
Hi Russ! Am Do, den 17.02.2005 schrieb Russ um 18:05: [[...]] I am using SuSE 9.1 Pro. I started mysql in the runlevel editor of YaST. It start when I boot the system. below are the results of the two items you asked me to look at: ps aux mysql 5471 0.0 0.4 21584 2400 ?S07:38 0:00 /usr/sbin/mysqld /etc/passwd mysql:x:60:2:MySQL database admin:/var/lib/mysql:/bin/false So user mysql is defined (by the entry in /etc/passwd), and the MySQL server process is running as this (Linux) user. All correct. This is what I see in phpMyAdmin. Welcome to phpMyAdmin 2.5.6 MySQL 4.0.18 running on localhost as [EMAIL PROTECTED] This is a database user, _not_ the Linux user. I agree the duplicate use of the name root is irritating. It seems you need not worry. HTH, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UNIX_TIMESTAMP function
How to select datetime using UNIX_TIMESTAMP excluding Saturday and Sunday? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UNIX_TIMESTAMP function
SELECT unix_timestamp(mydatetime), ... from mytable where
DAYNAME(mydatetime) is not in('Saturday','Sunday');
On Thu, 2005-02-17 at 16:34, Jerry Swanson wrote:
How to select datetime using UNIX_TIMESTAMP excluding Saturday and Sunday?
--
- michael dykman
- [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query problem
Hi I have a problem with a query that has many joined tables. The query brings back 80 records instead of just one. Any suggestions on how I can overcome this? Many thanks Richard Query below:- SELECT * FROM (main_data INNER JOIN main_data_facilities ON main_data_facilities.RecNo = main_data.RecNo) INNER JOIN main_data_meals ON main_data_meals.RecNo = main_data.RecNo) INNER JOIN main_data_non_smoking ON main_data_non_smoking.RecNo = main_data.RecNo) INNER JOIN main_data_payment_types ON main_data_payment_types.RecNo = main_data.RecNo) INNER JOIN main_data_pets_welcome ON main_data_pets_welcome.RecNo = main_data.RecNo) INNER JOIN main_data_special_dietary_requirements ON main_data_special_dietary_requirements.RecNo = main_data.RecNo) INNER JOIN counties ON counties.ID = main_data.County) INNER JOIN countries ON countries.ID = main_data.Country) INNER JOIN facilities ON facilities.ID = main_data_facilities.ID) INNER JOIN meals ON meals.ID = main_data_meals.ID) INNER JOIN non_smoking ON non_smoking.ID = main_data_non_smoking.ID) INNER JOIN payment_types ON payment_types.ID = main_data_payment_types.ID) INNER JOIN pets_welcome ON pets_welcome.ID = main_data_pets_welcome.ID) INNER JOIN special_dietary_requirements ON special_dietary_requirements.ID = main_data_special_dietary_requirements.ID AND main_data_meals.RecNo = main_data_facilities.RecNo WHERE Name LIKE 'sandy' OR ( main_data.Display_In_Search = 1 ) AND ( main_data.Expiry_Date = CurDate() ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Header files data types
Depnding on how your mysql was installed, you should have mysql.h on your system somewhere; perhaps in /usr/include/mysql or elsewhere depending on installation parameters.. If not, you can download libraries and headers appropriate to your system version from http://dev.mysql.com/downloads/ On Thu, 2005-02-17 at 13:12, Mohsen Pahlevanzadeh wrote: Dears,I must use data type in C++,same MySQL type. But i don't name of their header files. Please guide me. -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
one long lived connection or many short lived?
Hi, I apologize in advance, I am sure this question has been asked dozens of times, but my searches came up empty. I am building an IRC based application bot (using libmysql) .. that will take commands from users (!mybugs, !mybugs KEY !newbugs, etc), do SQL queries and of course dump formatted results back to the channel. Currently its setup to open a single DB connection at initialization and use that connection over and over... would it be better or worse to have it open/close a connection for each command in the way a web app would? Some things to keep in mind :) - IRC server/services bot is half way across the US from the mySQL server (connection reliability?) - queries will likely be spaced out by hours or days (idle disconnect?) - when one person does a query, its almost assured that there will be 10 more within 10 seconds :) - I would *like* to do SSL (still studying how to do that), so that will most certainly increase the mysql_real_connect() time? - Does the client library maintain a pool of connections or something like that? Thanks in advance, Tommy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data Types in Mysql
in the docs - column types: http://dev.mysql.com/doc/mysql/en/column-types.html Respectfully, Ligaya Turmelle Mohsen Pahlevanzadeh wrote: Dear,I need to MySQL data type or another. But i dont know name of their header file. Please guide me.. Yours,Mohsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select only values 0
Dear All, from a single row of a table, I have to select only the column, which have a value larger '0' into an outfile. How can I manage it with 'select'? Thanks, Jan SELECT CASE can do that sort of thing for you. Here's a simplistic example: CREATE TABLE `test` ( `i1` int, `i2` int, `i3` int ); INSERT INTO `test` (`i2`) VALUES (2); SELECT CASE WHEN `i1` 0 THEN 'Field 1' WHEN `i2` 0 THEN 'Field 2' WHEN `i3` 0 THEN 'Field 3' ELSE 'No match' END AS `iMatch` FROM `test`; +-+ | iMatch | +-+ | Field 2 | +-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need older version of mysql (current version seeminly corrupts FTS tables)
I have replaced one server with another, and the new one has everything new (RHEL 3, newest updates) and MySQL 4.0.23 (old one was RH9 and MySQL 4.0.18). We now get table corruptions constantly (it only takes a minute before several tables get marked as crashed). I'd like to revert to the 4.0.18 version (which I stopped updating after having some other issue, which I can no longer remember). Where can I download it Anyhow, with the errors, I get these in my application's log (mysql does not log any errors): Duplicate entry 'Some text here ---f' for key 3 Incorrect key file for table: 'table_messages_1'. Try to repair it Key 3 is the FTS key. The others are a UNIQUE KEY (#1) and a KEY(#2). I'd like to make sure it is not a mysql version issue. I have seen similar behavior in the 4.1 series, and don't want to try it now (and do all the table conversions -- possibly each way if it fails). Thanks! -steve-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.9 - Requesting Optimization Tips
I'm wondering if anyone can provide any tips as to how to conserve resources. Currently, I see 10 instances of mysqld running. Each instance is approximately 18MB. For my application of MySQL, I don't require a large amount of resources allocated to mysqld. 7085 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.05 mysqld 7086 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7087 mysql 20 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7088 mysql 24 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7089 mysql 24 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7090 mysql 20 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7091 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7092 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7093 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7094 mysql 15 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld I found some information on mysql.com. I basically reduced some of the startup options by half. key_buffer_size=32M back_log=25 table_cache=32 net_buffer_length=1M max_allowed_packet=3M query_cache_limit = 1M query_cache_size = 16M query_cache_type = 1 read_buffer_size=2M read_rnd_buffer_size=8M -- Regards, Matt Florido -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.9 - Requesting Optimization Tips
At 19:21 -0800 2/17/05, Matt Florido wrote: I'm wondering if anyone can provide any tips as to how to conserve resources. Currently, I see 10 instances of mysqld running. Each instance is approximately 18MB. For my application of MySQL, I don't require a large amount of resources allocated to mysqld. If you're running Linux, there's nothing to optimize here. These are threads of the same process, not 10 different processes. 7085 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.05 mysqld 7086 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7087 mysql 20 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7088 mysql 24 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7089 mysql 24 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7090 mysql 20 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7091 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7092 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7093 mysql 16 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld 7094 mysql 15 0 90060 17m 2608 S 0.0 3.5 0:00.00 mysqld I found some information on mysql.com. I basically reduced some of the startup options by half. key_buffer_size=32M back_log=25 table_cache=32 net_buffer_length=1M max_allowed_packet=3M query_cache_limit = 1M query_cache_size = 16M query_cache_type = 1 read_buffer_size=2M read_rnd_buffer_size=8M -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.9 - Requesting Optimization Tips
Paul DuBois wrote: At 19:21 -0800 2/17/05, Matt Florido wrote: I'm wondering if anyone can provide any tips as to how to conserve resources. Currently, I see 10 instances of mysqld running. Each instance is approximately 18MB. For my application of MySQL, I don't require a large amount of resources allocated to mysqld. If you're running Linux, there's nothing to optimize here. These are threads of the same process, not 10 different processes. Paul, thanks for the fast response. Ah...so this is ignorance on my part. You're saying mysqld is not running separate processes even though Top reports separate PID. Instead of each thread consuming appx. 18MB, the entire process is consuming just 18MB? -- Regards, Matt Florido -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.9 - Requesting Optimization Tips
At 19:44 -0800 2/17/05, Matt Florido wrote: Paul DuBois wrote: At 19:21 -0800 2/17/05, Matt Florido wrote: I'm wondering if anyone can provide any tips as to how to conserve resources. Currently, I see 10 instances of mysqld running. Each instance is approximately 18MB. For my application of MySQL, I don't require a large amount of resources allocated to mysqld. If you're running Linux, there's nothing to optimize here. These are threads of the same process, not 10 different processes. Paul, thanks for the fast response. Ah...so this is ignorance on my part. You're saying mysqld is not running separate processes even though Top reports separate PID. Instead of each thread consuming appx. 18MB, the entire process is consuming just 18MB? Yes, that's right. On my Linux box, I typically have several versions of MySQL running. Right now there are 249 mysqld processes, but I certainly don't have 249 servers going all at once. It's really only 21. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help.....stored procedure
Hi All, Im using MySQL 5.0 and my problem is I am not able to call a procedure more than once..Its working fine during 1st call..From the 2nd call onwards it vil give some error like ERROR 1172 (42000): Result consisted of more than one row IA sinilar problem can be found in http://bugs.mysql.com/bug.php?id=2687; My procedure is for delete the least recently used records from a table Can anyone has some solutions??? Expecting responses Thank U Regards, Lakshmi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
