how to query this
Hello, sorry I'm not good at SQL statement. I have a table, whose stru is like: idvalue 1 33 2 987 3 10 4 22 ... I want to get the max value and the corresponding id, using this sql: select max(value),id from table; but it won't work. so what's the correct syntax? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to query this
Thanks all the info. Just got what I wanted. 2009/9/10 Wolfgang Schaefer scha...@gmail.com: John Daisley schrieb: SELECT MAX(value), id FROM table GROUP BY id; I guess what Slackli had in mind was more something like this: SELECT id, value FROM table WHERE value = (SELECT max(value) FROM table); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
optimize mysql table's physical storage
Hello, My mysql table has been created for long time, it increases day by day, and become huge. Right now a full scan to the table for the first time is very slow. So I was thinking to optimize it. This table is stored in many non-sequential disk fragments I think. I want to make this table to be stored in disk with the sequential fragments.That will increase the scan speed. Is there any tool to do it? Thanks in advance. --ken Get the name you always wanted with the new y7mail email address. www.yahoo7.com.au/y7mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with a sql statement
hello, I try to execute this sql in mysql shell,but got error as: mysql select * from (select uin,count(*) as dd from active_users where date = date_add(curdate(),interval -30 day) group by uin) where dd =3; ERROR 1248 (42000): Every derived table must have its own alias But I can execute the sql statement of select uin,count(*) as dd from active_users where date = date_add(curdate(),interval -30 day) group by uin successfully. How to fixup it? thanks! Get the name you always wanted with the new y7mail email address. www.yahoo7.com.au/y7mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with a sql statement
Add an alias for the subquery select * from ( select ) my_alias where dd = 3; Better, use a having clause and eliminate the subquery. Odds are it will be more efficient in MySQL. How to replace the original one with a having statement? Thanks again. Get the name you always wanted with the new y7mail email address. www.yahoo7.com.au/y7mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql connection problems
Hello members, I have two mysqld run on the same host (redhat linux OS with 2.4 kernel). the two mysqld are in different versions, one is 4.0.20,another is 5.0.45. the mysql 4.0.20 uses /etc/my.cnf as its config file,listening on default 3306 port. the mysql 5.0.45 uses /etc/mysql5.cnf as its config file,listening on 3307 port. I start them on command line: /usr/local/mysql/bin/mysqld_safe # for mysql4 /opt/mysql5/bin/mysqld_safe --defaults-file=/etc/mysql5.cnf # for mysql5 All run fine.I didn't see exceptions in mysql's error logs. But, when I try to connect to mysql5, with the command, mysql -uroot -P3307 -h127.0.0.1 Sometime I login it successfully,but most time I can't. The connection seems be blocked. (I don't run any iptables or firewall on this host). This let me really be confused. please help. Thanks! --Ken National Bingo Night. Play along for the chance to win $10,000 every week. Download your gamecard now at Yahoo!7 TV. http://au.blogs.yahoo.com/national-bingo-night/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql old 4.* query fails on 5.*
Anyone know whats wrong here? Try as ... From (Klienter AS K, Tid As Td, Personal AS P) JOIN Uppdrag AS U ON K.Klient_ID = U.Klient_ID ... or ... From Tid As Td, Personal AS P, Klienter AS K JOIN Uppdrag AS U ON K.Klient_ID = U.Klient_ID ... This is the problem I had in one of my queries. The join is on the last table on the left side. Either use parentheses or put K table last. HTH, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
mysqlcheck -h$host -u$user -p$pass --analyze $dbname I wish that was the case! I tried analyze table ... and optimize table ..., which I presume would be the same. It did not help. I also ran mysqlcheck just to see if it will make a difference. Nope! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to rewrite SQL query for new MySQL LEFT JOINS
I hope someone can clue me in what a syntax of query that produces the same would look like for MySQL 5.0.12 Old query meant to list most recent message from each thread, e.g. select * from messages left join messages as messages_ on messages.thread = messages_.thread and messages.created messages_.created where messages_.id is null It worked in 4.x but does not work in the new syntax. How should I rewrite it to get the same result? TIA, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to rewrite SQL query for new MySQL LEFT JOINS
It worked in 4.x but does not work in the new syntax. How should I rewrite it to get the same result? OK, that was a lie. It works in 5.x as well. I should learn to describe my problem more accurately as well as RTFM :-( The correct description of the query in question would have been: select * from messages, someothertable left join messages as messages_ on messages.thread = messages_.thread and messages.created messages_.created where messages_.id is null Since there was a comma (inner) join there, the left join applied only to 'someothertable' and not to message table as intended. As I learned, in 5.0.12+, parentheses matter! Duh! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
I had perfectly working complex queries both with LEFT JOIN and without and they were returning results in under a second. After upgrade to 5.0.x, the same queries would return results in 20-30 second range. Through trial and error, I discovered that in case of SELECT ... FROM table1, table2 ... ORDER BY table2.column will be very slow (10s of seconds), while the same query ordered by table1.column will be in under 1 second. If I switch the order of tables in FROM, the same will hold true in reverse order. Is this a bug or I missed something in my googling? More importantly, what can I do change this--I need those sorts! :-( I got same results with 5.0.22, 5.0.27 (Linux). TIA, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
possible you had set up some query cache in 4, but not currently in 5? may not be optimized, but yes, query cache is enabled, all 25 MB of it. :-) how did you 'upgraded' your data? regrettably, in-place. interestingly, I was recovering after server crash that chopped of a table. after upgrading the server (in-place), I re-read the corrupt table from script dumped by mysqlbinlog. it is THAT table that is causing me grief. I thought it was some missing indices, but I have indices on all columns I use in WHERE. what means this exactly? in reverse ordered tables, query is fast on second or on first table order? 'select ... from table1, table2, table3 ... order by table1.column' is FAST 'select ... from table1, table2, table3 ... order by table2.column' is SLOW did your tried an EXPLAIN? yes, thanks for reminding me to use it. I compared the two; the slow one uses temporary table and filesort; the fast one does not. Both use where and all select types are SIMPLE. So, with your help, I know why it is slow. Is there a way to out of this without downgrading the server? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!
When you upgraded from 4.1 to 5.0, did you do an in-place upgrade, or mysqldump your data and then re-import? As replied to Sebastian's post, in-place. Try using either mysqldump or mysql-administrator to dump out your data to an .sql file. Then re-import all of your data into 5.x. You will see a significant difference in your query speeds once you do this. Will this still hold true, even if I dump data out of MySQL 5 and re-import it, or do I need to downgrade first? As to your query cache, make sure that it's on (on by default) and, based on your tables, either your MyISAM key_buffer_size or your InnoDB buffer_pool_size are correct for your server's amount of RAM. I guess I will have to check if 8MB is good on 500MB RAM. I did some research back when messing with 4.1, so a good time to do it again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT data FROM two tables into outfile
Hello list: Need some help with the following query: mysql SELECT header.date_in,header.pid,header.status,body.body_data from header,body where header.date_in='1170705152' and body.date_in=header.date_in and header.pid='26878' and body.pid=header.pid into outfile '/tmp/mysql/117070515226878'; Though it doesn't look pretty, it returns the right data (my SQL needs a lot of work). Now the thing that I would like to accomplish is to ONLY write the data from the body.body_data table into the outfile. Is that possible? If so, how...? Thanks in advance, RV -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Questions about using mysqlimport to update a table.
I need to update a table with the contents of a CSV file regularly, I've used mysqlimport to load all the initial data, but I have a problem with using it for updates. The data in the CSV file does not contain all of the data in the table, there is a field that is updated by another application as well. I need to be able to get updates to the data that is from the CSV file without deleting the data that is not present from those rows. If I run it with --ignore, I don't get the updates to rows that are already present, and if I run it with --replace, I lose the data that wasn't contained within the CSV file. I was really hoping to be able to use mysqlmport for this, since I need to schedule these updates fairly regularly and would like to be able to automate that process. Is there something I am missing that will make this work, or do I need to go about it in another way? Thanks
Re: Zip Code Distance
On Tue, 2006-08-29 at 16:30 -0400, Jesse wrote: Does anyone have any ideas? One technique is to calculate set distances (5,10,25,50) between the zip codes in advance and stick the results in a table. Enjoy, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_incrment seed number
On Thu, 2006-07-06 at 13:41 -0700, Chuck Holzwarth wrote: Is there a way to set the auto_increment start number? I am trying to set up a development and test system and the application that is writing to the tables is confused as to which MySQL it is writing to. I don't have any contrtol over the other app but since the key value is taken back to the other app, I can determine which areas are having the problem if I start the dev instance at 1 and start the test instance at 100,000. http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
On Tue, 2006-04-25 at 00:43 -0700, Daevid Vincent wrote: So for a sanity check, I decided to look online and punch in some to see what the real lat/long should be. Well, different sites give different values, and not only are they slightly off, but sometimes they're _positive_ or _negative_!? UGH! Not sure what your confusion is. It is a matter of notation. The negative value represents West where it is negative (as would be the East; note how there is no W mentioned there). So read about their presentation formats. I think 4 decimal points are plenty, especially if they follow the rounding rules. :-) Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4 (positve vs. negative longitude)
... err, as would be South... N+, S-, E+, W- Ed :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OK, need a little inspiration here...
I am stomped and not sure how to get results from the particular type of query. While I am not sure, if this is an appropriate place to ask, if nothing else perhaps someone will direct me to a more appropriate forum. I am trying to figure out how to return the latest record in each group of records identified by some hash, e.g. (^ are rows I want), ^ 000543 | 20031019140457 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000542 | 20031018214128 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000525 | 20031018210622 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000513 | 20031017010947 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000512 | 20031017010804 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | ^ 000431 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 | | 000417 | 20031012135916 | 73641d1f174a502951db06653525af125dd4df46 | ^ 000191 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | 000188 | 20030920110057 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | ^ 000161 | 20030917094352 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | | 000152 | 20030917084805 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | | 000151 | 20030915095857 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | | 000148 | 20030915094718 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | The result I want is: | 000543 | 20031019140457 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000431 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 | | 000191 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | 000161 | 20030917094352 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | I tried GROUP BY on the hash, but then MySQL grabs the earliest record. (Anyway to influence the sorting order before GROUP BY is applied?) E.g., | 000512 | 20031017010804 | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | | 000417 | 20031012135916 | 73641d1f174a502951db06653525af125dd4df46 | | 000188 | 20030920110057 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | 000148 | 20030915094718 | b8b993464ec303bbff704f582e4f7b5b9ea100bf | Alternatively, I also tried MAX(date), which of course is incorrect, because while returning the latest date for the hash--good!, it looses touch with the remaining columns of the record, i.e, ids and dates are mismatched, | outside provided sample | 33ebbda70ab8f58ae0f59c70ee7158a7fc32b42b | ^^^ ignore | 000417 | 20031012150600 | 73641d1f174a502951db06653525af125dd4df46 | | 000188 | 20030920135647 | dc4d1f4aefdbbe2eaa82b0e5629b6767e7175e0f | | outside provided sample | b8b993464ec303bbff704f582e4f7b5b9ea100bf | ^^^ ignore, again record outside the provided sample Am I missing some kind of magic somewhere or do I need to resort to extra columns and/or extra steps to get what I want? I am using 4.1.16 on Linux with 512 MB of RAM, so not sure if using intermediate tables would be a well performing solution, but if there is one, I still want to hear about it. :-) TIA, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
Great, :) But do you know how to write a good select query using this design? For example if i want to select all TV with widescreen and inch greater than 28? select * from PRODUCT_SPECS (where SPEC_ID=1 and VALUE=YES) (and SPEC_ID=5 and VALUE=28) this doesnt feel right... From: SGreen at unimin dot com Date: December 10 2005 3:29am Subject: Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??= --=_alternative 000D3711852570D3_= Content-Type: text/plain; charset=US-ASCII MUCH BETTER!! Sorry I doubted you. However you have to remember that unless you declare a second numeric column in your PRODUCT_SPECS table then everything will be treated as strings. Sorting will be as strings, comparisons will be as strings, and any attempt to use them as numbers while they are strings will invalidate any indexes. I would suggest a second DECIMAL column on your PRODUCT_SPECS table or be prepared for performance hits whenever you need numeric ordering. If you compare them alphabetically, 8 comes after 1 so 8 is greater than 10, 100, 1000, 20, 30, or any other word that starts with a letter smaller than 8. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 05:47:41 PM: Oh, is it really such a bad design? Here is some more. What is substring matches, and why do i need them? TBL_PRODUCTS ID PRODUCTNAME 1 SAMSUNG TV 2 PHILIPS DVD-PLAYER 3 PHILIPS TV 4 MAXTOR DMAX 5 LaCie HARDDIVE - TBL_SPECS ID DETAIL 1 Widescreen 2 VCD 3 DiVX 4 Capacity 5 Inch - PRODUCT_SPECS PRODID SPECID VALUE 1 1 YES 1 5 32 2 2 NO 2 3 3.11 3 1 NO 3 5 28 4 4 80 5 4 120 - Thanks again for your help! Quoting [EMAIL PROTECTED]: This sounds like a simple case of bad design. You need to be able to locate specific values for various product descriptions but they are all mangled together into just one field. You end up trying to do substring matches and all hell breaks loose and performance hits the skids. My suggestion is to somehow re-process your value column into separate specific columns or child tables, one for each distinct value held in the value field. I can identify the potential values of `hdd_size`, `monitor_size`, `monitor_resolution`,`hw_port`,`cd_supp_format` Your data is unmanageable in its present format and you need to scrub and massage it into shape before what you have will be marginally useful. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:56:21 PM: Hi again :) The table contains a column named value and is in the format varchar(255). This column contains specifications for different computer products. There is also a id column so i know which product it belongs to. value(varchar) 80 17 1024x768 USB DiVX For example, the first value 80 tells me with som joins that the product maxtor diamondmax has 80Gb capacity. And that a Philips DVD-player supportsDiVX for the last value in this example. Now i want to select all harddrvies with a capacity greater or equal to 80. Doing a select value from tbl where value =80 order by value DESCwill give som unexpected results. If you have 80, 120, 250 in the database the result will be: 80 250 120 I don't really know how to solve this other than to use CAST(value as SIGNED). Maybe i could rebuild the database but i don't know how a good databasedesign for this would look like :) Thanks for you help! Quoting [EMAIL PROTECTED]: I misunderstood, I thought you were looking for a way of converting your numbers-as-strings into a native numeric format. Please describe you situation better: What language are you using to build your application. Are you composing the SQL statement client-side or server-side? What kind of SQL statement are you trying to execute? Your table structures (the output of SHOW CREATE TABLE ... works very well) and some sample data would also help. Sorry for the confusion! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:28:44 PM: Hello, thanks for your help! I dont really get it :) You suggestion is to have a seperate column with the name numericvalue and insert userinput into that and add a zero, right? Could you explain more, why when how will this help me :) Quoting [EMAIL PROTECTED
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
Thanks ALOT! So you would say that this is good database design and that this method and query is the best way to store and retrive product specifications? Quoting [EMAIL PROTECTED]: Is this good enough? SELECT prodid, count(1) matches FROM PRODUCT_SPECS WHERE (where SPEC_ID=1 and VALUE=YES) OR(and SPEC_ID=5 and VALUE=28) GROUP BY prodid HAVING matches=2; This query form is flexible enough so that if, for instance, you just wanted to rank products based on how well they match a set of criteria, you could leave out the HAVING clause and replace it with ORDER BY matches DESC. If you only cared about matching a 3 of 4 query terms, your WHERE clause would list all 4 terms but your HAVING clause would only check for 3 of them. Normally, these results would be cached into a temporary table and re-used in other places (for speed) but it is possible to join in several other tables if you need them and still get decent results from a single statement. IMHO, Not only is this database design a flexible storage system but the ability to easily poll for partial matches and easily determine gross matching rankings makes it useful for many applications. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/19/2005 08:28:36 AM: Great, :) But do you know how to write a good select query using this design? For example if i want to select all TV with widescreen and inch greater than 28? select * from PRODUCT_SPECS (where SPEC_ID=1 and VALUE=YES) (and SPEC_ID=5 and VALUE=28) this doesnt feel right... From: SGreen at unimin dot com Date: December 10 2005 3:29am Subject: Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??= --=_alternative 000D3711852570D3_= Content-Type: text/plain; charset=US-ASCII MUCH BETTER!! Sorry I doubted you. However you have to remember that unless you declare a second numeric column in your PRODUCT_SPECS table then everything will be treated as strings. Sorting will be as strings, comparisons will be as strings, and any attempt to use them as numbers while they are strings will invalidate any indexes. I would suggest a second DECIMAL column on your PRODUCT_SPECS table or be prepared for performance hits whenever you need numeric ordering. If you compare them alphabetically, 8 comes after 1 so 8 is greater than 10, 100, 1000, 20, 30, or any other word that starts with a letter smaller than 8. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 05:47:41 PM: Oh, is it really such a bad design? Here is some more. What is substring matches, and why do i need them? TBL_PRODUCTS ID PRODUCTNAME 1 SAMSUNG TV 2 PHILIPS DVD-PLAYER 3 PHILIPS TV 4 MAXTOR DMAX 5 LaCie HARDDIVE - TBL_SPECS ID DETAIL 1 Widescreen 2 VCD 3 DiVX 4 Capacity 5 Inch - PRODUCT_SPECS PRODID SPECID VALUE 1 1 YES 1 5 32 2 2 NO 2 3 3.11 3 1 NO 3 5 28 4 4 80 5 4 120 - Thanks again for your help! Quoting [EMAIL PROTECTED]: This sounds like a simple case of bad design. You need to be able to locate specific values for various product descriptions but they are all mangled together into just one field. You end up trying to do substring matches and all hell breaks loose and performance hits the skids. My suggestion is to somehow re-process your value column into separate specific columns or child tables, one for each distinct value held in the value field. I can identify the potential values of `hdd_size`, `monitor_size`, `monitor_resolution`,`hw_port`,`cd_supp_format` Your data is unmanageable in its present format and you need to scrub and massage it into shape before what you have will be marginally useful. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:56:21 PM: Hi again :) The table contains a column named value and is in the format varchar(255). This column contains specifications for different computer products. There is also a id column so i know which product it belongs to. value(varchar) 80 17 1024x768 USB DiVX For example, the first value 80 tells me with som joins that the product maxtor diamondmax has 80Gb capacity. And that a Philips DVD-player supportsDiVX for the last value
CAST as SIGNE=?ISO-8859-1?Q?D,_bad_id=E9a??=
in an application i have written there is the need to do a search from mysql using numbers that are stored in a varchar column. it is not possible to store only the results with numbers in a seperate column. so i was looking at CAST(), is this a big performance loss? is there some way of benchmarking different queries easy? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
Hello, thanks for your help! I dont really get it :) You suggestion is to have a seperate column with the name numericvalue and insert userinput into that and add a zero, right? Could you explain more, why when how will this help me :) Quoting [EMAIL PROTECTED]: Assuming that your text data is in the column `userinput` and you want the integer values to be in the column `numericvalue`, this statement will populate the `numericvalue` column all at once: UPDATE `odd_data_table` SET `numericvalue` = `userinput` + 0; You are better off checking for type-correctness before you enter data into the database than you are trying to correct it after the input. However, I have had to do just this kind of conversion on many occasions (old data, bad batch inputs, text file bulk loads, etc.) so I know techniques like this still have their place. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 03:30:17 PM: in an application i have written there is the need to do a search from mysql using numbers that are stored in a varchar column. it is not possible to store only the results with numbers in a seperate column. so i was looking at CAST(), is this a big performance loss? is theresome way of benchmarking different queries easy? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
Hi again :) The table contains a column named value and is in the format varchar(255). This column contains specifications for different computer products. There is also a id column so i know which product it belongs to. value(varchar) 80 17 1024x768 USB DiVX For example, the first value 80 tells me with som joins that the product maxtor diamondmax has 80Gb capacity. And that a Philips DVD-player supports DiVX for the last value in this example. Now i want to select all harddrvies with a capacity greater or equal to 80. Doing a select value from tbl where value =80 order by value DESC will give som unexpected results. If you have 80, 120, 250 in the database the result will be: 80 250 120 I don't really know how to solve this other than to use CAST(value as SIGNED). Maybe i could rebuild the database but i don't know how a good databasedesign for this would look like :) Thanks for you help! Quoting [EMAIL PROTECTED]: I misunderstood, I thought you were looking for a way of converting your numbers-as-strings into a native numeric format. Please describe you situation better: What language are you using to build your application. Are you composing the SQL statement client-side or server-side? What kind of SQL statement are you trying to execute? Your table structures (the output of SHOW CREATE TABLE ... works very well) and some sample data would also help. Sorry for the confusion! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:28:44 PM: Hello, thanks for your help! I dont really get it :) You suggestion is to have a seperate column with the name numericvalue and insert userinput into that and add a zero, right? Could you explain more, why when how will this help me :) Quoting [EMAIL PROTECTED]: Assuming that your text data is in the column `userinput` and you want the integer values to be in the column `numericvalue`, this statement will populate the `numericvalue` column all at once: UPDATE `odd_data_table` SET `numericvalue` = `userinput` + 0; You are better off checking for type-correctness before you enter data into the database than you are trying to correct it after the input. However, I have had to do just this kind of conversion on many occasions (old data, bad batch inputs, text file bulk loads, etc.) so I know techniques like this still have their place. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 03:30:17 PM: in an application i have written there is the need to do a search from mysql using numbers that are stored in a varchar column. it is not possible to store only the results with numbers in a seperate column. so i was looking at CAST(), is this a big performance loss? is theresome way of benchmarking different queries easy? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
Oh, is it really such a bad design? Here is some more. What is substring matches, and why do i need them? TBL_PRODUCTS ID PRODUCTNAME 1 SAMSUNG TV 2 PHILIPS DVD-PLAYER 3 PHILIPS TV 4 MAXTOR DMAX 5 LaCie HARDDIVE - TBL_SPECS ID DETAIL 1 Widescreen 2 VCD 3 DiVX 4 Capacity 5 Inch - PRODUCT_SPECS PRODID SPECID VALUE 1 1 YES 1 5 32 2 2 NO 2 3 3.11 3 1 NO 3 1 28 4 4 80 5 4 120 - Thanks again for your help! Quoting [EMAIL PROTECTED]: This sounds like a simple case of bad design. You need to be able to locate specific values for various product descriptions but they are all mangled together into just one field. You end up trying to do substring matches and all hell breaks loose and performance hits the skids. My suggestion is to somehow re-process your value column into separate specific columns or child tables, one for each distinct value held in the value field. I can identify the potential values of `hdd_size`, `monitor_size`, `monitor_resolution`,`hw_port`,`cd_supp_format` Your data is unmanageable in its present format and you need to scrub and massage it into shape before what you have will be marginally useful. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:56:21 PM: Hi again :) The table contains a column named value and is in the format varchar(255). This column contains specifications for different computer products. There is also a id column so i know which product it belongs to. value(varchar) 80 17 1024x768 USB DiVX For example, the first value 80 tells me with som joins that the product maxtor diamondmax has 80Gb capacity. And that a Philips DVD-player supportsDiVX for the last value in this example. Now i want to select all harddrvies with a capacity greater or equal to 80. Doing a select value from tbl where value =80 order by value DESCwill give som unexpected results. If you have 80, 120, 250 in the database the result will be: 80 250 120 I don't really know how to solve this other than to use CAST(value as SIGNED). Maybe i could rebuild the database but i don't know how a good databasedesign for this would look like :) Thanks for you help! Quoting [EMAIL PROTECTED]: I misunderstood, I thought you were looking for a way of converting your numbers-as-strings into a native numeric format. Please describe you situation better: What language are you using to build your application. Are you composing the SQL statement client-side or server-side? What kind of SQL statement are you trying to execute? Your table structures (the output of SHOW CREATE TABLE ... works very well) and some sample data would also help. Sorry for the confusion! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 04:28:44 PM: Hello, thanks for your help! I dont really get it :) You suggestion is to have a seperate column with the name numericvalue and insert userinput into that and add a zero, right? Could you explain more, why when how will this help me :) Quoting [EMAIL PROTECTED]: Assuming that your text data is in the column `userinput` and you want the integer values to be in the column `numericvalue`, this statement will populate the `numericvalue` column all at once: UPDATE `odd_data_table` SET `numericvalue` = `userinput` + 0; You are better off checking for type-correctness before you enter data into the database than you are trying to correct it after the input. However, I have had to do just this kind of conversion on many occasions (old data, bad batch inputs, text file bulk loads, etc.) so I know techniques like this still have their place. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Test USER [EMAIL PROTECTED] wrote on 12/09/2005 03:30:17 PM: in an application i have written there is the need to do a search from mysql using numbers that are stored in a varchar column. it is not possible to store only the results with numbers in a seperate column. so i was looking at CAST(), is this a big performance loss? is theresome way
Re: CAST as S=?ISO-8859-1?Q?IGNED,_bad_id=E9a??=
Yes the ordering is the only problem i have seen so far but i´m concerned that the = ? might cause problems to? It seems to work but since it orders the results wrong can i be sure that it will always do this correct for me? In the ordering it seems like mysql thinks that 80Gb is larger than 120 and 250. And my concerne is that i might be situations where it thinks that 80 should be returned when doing a =120. Quoting Michael Stassen [EMAIL PROTECTED]: Test USER wrote: Hi again :) The table contains a column named value and is in the format varchar(255). This column contains specifications for different computer products. There is also a id column so i know which product it belongs to. value(varchar) 80 17 1024x768 USB DiVX For example, the first value 80 tells me with som joins that the product maxtor diamondmax has 80Gb capacity. And that a Philips DVD-player supports DiVX for the last value in this example. Now i want to select all harddrvies with a capacity greater or equal to 80. Doing a select value from tbl where value =80 order by value DESC will give some unexpected results. If you have 80, 120, 250 in the database the result will be: 80 250 120 I don't really know how to solve this other than to use CAST(value as SIGNED). Maybe i could rebuild the database but i don't know how a good databasedesign for this would look like :) Is the ordering your only concern? Your value column is a string, so your results are ordered alphabetically rather than numerically. If all you want is numeric ordering, you need to tell mysql to treat value as a number in the order by: SELECT value FROM tbl WHERE value =80 ORDER BY value+0 DESC; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
IN(INT VS CHAR)
When using IN should i design the database to use int's or is the performance
equal?
WHERE col IN('test','test2','test3')
vs
WHERE col IN(1,2,3)
-
FREE E-MAIL IN 1 MINUTE!
- [EMAIL PROTECTED] - http://www.pc.nu
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump dumping a table but not all columns?
i´m using mysqldump to dump some tables and then load it into another mysql server with mysql command. but can i specify what columns in the tables to dump? i´m using something like this now mysqldump -h localhost -u root db tbl | mysql -h xxx.xxx.xxx -u login -pass -w db - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE (url)
Can't get this to work, but i would like to specify LOAD DATA to use an INFILE from an URL. For example LOAD DATA LOCAL INFILE 'http://www.testserver.com/data.csv' But i get an error message saying file not found. Anyone know if this is even possible ? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE (url)
Hi thanks for your answer! I would like this to be done via cron 3-4 times a day. Quoting Peter J Milanese [EMAIL PROTECTED]: I have never seen this. Mysql would have to do a wget of the file then dump it. Last I knew it wasn't a web browser. There may be a way to do the wget inline though, or at least write something in shell or perl to do it. Is this cron'd or something, or a one time thing? - Sent from my NYPL BlackBerry Handheld. - Original Message - From: Test USER [EMAIL PROTECTED] Sent: 12/01/2005 04:55 AM To: mysql@lists.mysql.com Subject: LOAD DATA INFILE (url) Can't get this to work, but i would like to specify LOAD DATA to use an INFILE from an URL. For example LOAD DATA LOCAL INFILE 'http://www.testserver.com/data.csv' But i get an error message saying file not found. Anyone know if this is even possible ? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Possible ways to sort numbers stored in a varchar column?
Thanks i also found the function CAST which works ok. So now i have three options: CAST LPAD Adding zero Any other sugestions are welcome! Quoting [EMAIL PROTECTED]: Test USER [EMAIL PROTECTED] wrote on 11/23/2005 07:36:43 PM: Is the only way to sort numbers stored in a varchar column to use lpad? Are there any other columntypes that allow both characters and numbers that can sort numbers correct? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu You can try adding zero to the column. It's a function acting on a value so any chance of using an index is eliminated but you can get your columns as numbers that way. To answer your second question, no. Fields are either strings or numbers but never both. Shawn Green Database Administrator Unimin Corporation - Spruce Pine - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible ways to sort numbers stored in a varchar column?
Is the only way to sort numbers stored in a varchar column to use lpad? Are there any other columntypes that allow both characters and numbers that can sort numbers correct? - FREE E-MAIL IN 1 MINUTE! - [EMAIL PROTECTED] - http://www.pc.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copy users (was: Grant Tables problem )
David Lloyd wrote .. Hi, I have a problem connecting to the mysql server. I installed a new server with mysql 4.1.9, apache2, php4 on freebsd5.3 and have some websites running on it using the mysql server. Last friday I had a crash of one off my other servers and I copied the websites and db's to this new server. (old server was mysql 3). After that the mysql server didn't startup anymore. I then chown'ed the data dir to mysql:mysql. After that I was able to start the server with --skip-grant-tables . But it didn't start with out the --skip-grant-tables option. So I found out to use mysql_install_db to create new grant tables. After that the server starts with and without --skip-grant-tables. But only with the --skip-grant-tables option, websites can connect to the databases. I can't find any real errors in the logs and I can't find anything on the web or forum. I run mysql_fix_privilege_tables and installed a root passwd. Hopefully somebody can help me on this. I wonder if putting: [mysql] old-passwords ...in your my.cnf (I always use /etc/my.cnf because trying to guess where the 'real' data directory is, is painful). http://dev.mysql.com/doc/mysql/en/password-hashing.html DSL Well the above article did me search in another way (thanks for that). I found out the users are not copied with the db's. I copied about 20 db's 2 weeks ago from one server to another and everything went just fine. Probably the problem is now that the old server had the db's in /usr/home/mysql/data and on the new server in /var/db/mysql (default). How do I copy those users? Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysqldump error 1017: errno: 24 - help?
Yep, that seems to have done it, at least when I'm testing it. I'm pretty sure the problem is fixed, but the next couple of days of automated backups will tell. Thanks! On Tue, 2005-01-11 at 00:43, Gleb Paharenko wrote: Hello. Try '--open-files-limit=8192' at least. Check the real value of open_file_limits with such statement: show variables like '%open_f%'; You can find some recommendations for SuSe Linux at: http://dev.mysql.com/doc/mysql/en/Linux-post-install.html Mysql user [EMAIL PROTECTED] wrote: Hi.. I've got an ISP, and all of our customers have databases in our mysql system. My backup command is: mysqldump --force --opt -A -p' | gzip -c /var/sqlbackup/mysqldump-`date +\%A`.sql.gz This has worked fine for some time. Now, however, I get an error message: mysqldump: Got error: 1017: Can't find file: './usr_web22_1/invoices_va.frm' (errno: 24) when using LOCK TABLES On different runs, it reports different files, even right after restarting mysqld. I've looked at the mysql documentation for this error; it's at http://dev.mysql.com/doc/mysql/en/Not_enough_file_handles.html I've looked at the startup script, put in '--open-files-limit=1024', restarted mysql, and the error is the same. I've looked at the table_cache and max_connections system variables, which are 64 and 100, respectively. My question is: what should I do now? Since table_cache and max_connections are far less than 1024, do I reduce them even further? Is there something else I should be looking at? This is mysql-3.23.52-106 on SuSE Linux 8.1, running on a dual PIII 866Mhz system, with 512M of ram and 1G of swap, on a RAID-1 pair of 17G hard drives. Thanks! -- 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 user [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqldump error 1017: errno: 24 - help?
Hi.. I've got an ISP, and all of our customers have databases in our mysql system. My backup command is: mysqldump --force --opt -A -p' | gzip -c /var/sqlbackup/mysqldump-`date +\%A`.sql.gz This has worked fine for some time. Now, however, I get an error message: mysqldump: Got error: 1017: Can't find file: './usr_web22_1/invoices_va.frm' (errno: 24) when using LOCK TABLES On different runs, it reports different files, even right after restarting mysqld. I've looked at the mysql documentation for this error; it's at http://dev.mysql.com/doc/mysql/en/Not_enough_file_handles.html I've looked at the startup script, put in '--open-files-limit=1024', restarted mysql, and the error is the same. I've looked at the table_cache and max_connections system variables, which are 64 and 100, respectively. My question is: what should I do now? Since table_cache and max_connections are far less than 1024, do I reduce them even further? Is there something else I should be looking at? This is mysql-3.23.52-106 on SuSE Linux 8.1, running on a dual PIII 866Mhz system, with 512M of ram and 1G of swap, on a RAID-1 pair of 17G hard drives. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP/MySQL Problem
Hi Guys, I have a problem with MySQL in conjunction with PHP so I also decided to post here: I have a PHP script that contains two consecutive MySQL queries, something like this: Query 1: Delete some rows from Table A Query 2: Insert some rows into Table A The problem is, only Query 2 seems to be executed. Query 1 is not executed at all. I tried running the script with only Query 1 and it worked. However, when I put back Query 2, then the problem comes back. What can be causing this problem? How do I solve this problem? Any help would be appreciated. Thanks! __ Do you Yahoo!? Check out the new Yahoo! Front Page. www.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on adding values together
Hi I have a hockey pool database and I want to be able to add each weeks totals (goals, assists, etc.) for the players on each team. Example: Team 1 may have Hossa, Redden, and Forsberg. If each of them scored 2 goals and 2 assists for week one, I want to be able to get the total of 12. Team two has three different players and they may score 3 goal and three assists for a total of 18. Then team 3, 4, 5, etc. I have three tables, one with the teams (I call it manager) and one for the players (called roster). I have a reference table that links the manger and roster tables together to determine what players are on what teams. Any help is appreciated. CR
MySQL Cluster Software
I recently saw and article that says MySQL will be shipping its cluster software starting April 14th during the Users Conference Expo this year. Does anyone have any information about this? My company is considering using the Emic clustering software. Has anyone had experience with that? Will the MySQL branded one be better? Thanks! Tom ONeill InfoWorld Article http://www.infoworld.com/article/04/03/12/HNmysqlcluster_1.html
Re: MySQL+Apache Optimization
Questions - Number of queries sent to the server. See http://www.mysql.com/doc/en/SHOW_STATUS.html for more info MySQL user From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: MySQL+Apache Optimization Date: Wed, 7 Jan 2004 15:12:19 +0530 Hello , What are Questions in Mysql , I mean is there something know as Questions in Mysql, I got a Script in PHP called as testload.php and it shows me this output : total processes are 55 Mysql Status is Uptime: 1839 Threads: 55 Questions: 175421 == What does this Stands For ??? Slow queries: 0 Opens: 142 Flush tables: 1 Open tables: 136 Queries per second avg: 95.389 Any comments will appreciated. Thank you, Vishal. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:35 PM Subject: Re: MySQL+Apache Optimization Hello, Thank you for the Reply, I'll follow you suggestions and will post the results here, Also I use connect only and not aothers. Thank you again. ;) Vishal. - Original Message - From: my5ql _ [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:06 PM Subject: MySQL+Apache Optimization Can Anyone please tell me why I am not able to get the Load Down and the site Load Faster.The server load is always above 5.00 Thanks in Advance. Vishal. Try the following changes: Apache: Turn KeepAlives off + increase your MaxRequestsPerChild. Apache is probably spending too much time recreating httpd processes. (I presume Apache MySQL are running on the same box). Also are your using mysql_connect() or pconnects? MySQL: Try increasing your table cache. Does your thread cache need to be that high? Maybe you should increase the thread concurrency first. According to the my.cnf, you can increase this based on the amount of CPUs, you start off by setting it to 24-32? Your max_connections settings is high and that will take up a fair amount of file descriptors. I suspect your table cache isn't big enough (try 2048 as a starting point, see http://www.mysql.com/doc/en/Table_cache.html for more info) (Not intending to hijack this thread), but I'm going through a similar issue with my Dual Xeon, 6GB RAM and RAID5 SCSI, but I'm running out of file-descriptors and max_connections=520 I hope my suggestions are useful and it'll be interesting to see whether they help... httpd.conf === KeepAlive Off MaxRequestsPerChild 9000 my.cnf == [mysqld] table_cache = 2048 thread_cache_size = 256 thread_concurrency= 32 _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL in Hebrew/my.cnf
Noamn [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I asked about a week ago how to get mySQL to index correctly in Hebrew, and the best answer that I received was to define a my.cnf file as follows [mysqld] set-variable = default-character-set = hebrew I created the file /etc/my.cnf using the root account, stopped the mysql daemon then restarted. The daemon failed immediately. I tried this a few more times, then reluctantly came to the conclusion that there is something wrong with the /etc/my.cnf file, so I deleted it and successfully started the daemon. Is there something special which I need to define regarding the file's permissions? check in the /usr/local/share/mysql directory that you got the Hebrew charset definition file - hebrew.conf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Column Types Changing
Someone told me that it is possible that MySQL will automatically change column types in certain situations. For example the a table with a char(5) type field might dynamically change to a varchar(5) type field. So a static length column to a variable length column. Is this possible? If so what are the circumstances that would cause MySQL to alter the table structure on its own? This doesn't really make sense to me. -Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
copy data between very large tables
Hi, We copy data from one table to another using: insert into TBL1 select * from TBL 2; The current database hangs and the process never finish when copying huge tables (around 25million rows). Looking at the processlist it states that the process stays in closing table or wait on cond status. We run Mysql version 3.23.52 on a Sun solaris 7 machine and the database directory is located on a NetApp. We configured the my.cnf file to skip-locking and we are not locking any tables explicitly. Any thoughts will be extremely welcome. Thanks Ben Leon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using Temporary
Could anyone tell me the difference between the following two explains? It seems the first takes longer to execute. This first query is like so... select m.*, mi.age from members m, members_addtl_info mi where m.nick like '%anynickname%' AND m.nick = mi.nick order by nick desc, account_login_last desc limit 0, 21 +---++---+-+-+-++--- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+-++--- --+ | mi| ALL| PRIMARY | NULL|NULL | NULL| 740053 | Using temporary; Using filesort | | m | eq_ref | PRIMARY | PRIMARY | 15 | mi.nick | 1 | where used | +---++---+-+-+-++--- --+ 2nd query seems alot fasteer. select m.* from members m where m.nick like '%bigsh523%' order by nick desc, account_login_last desc limit 0, 21 +---+--+---+--+-+--++--- -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--++--- -+ | m | ALL | NULL | NULL |NULL | NULL | 736939 | where used; Using filesort | +---+--+---+--+-+--++--- -+ Any ideas why? What is the difference between Using Temporary and where used? Thanks! TOM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
encrypt() call on Win32
While I found some information on this subject: http://groups.google.com/groups?q=mysql+win32+encrypthl=enlr=ie=UTF-8oe=UTF-8selm=be3g14%24ulu%241%40FreeBSD.csie.NCTU.edu.twrnum=1 I can't make much sense of it. Is there a *clear* way to have encrypt() call working on mySQL running on Win32? thanks
EXPLAIN - Question..
Hello everyone. I have copied the results from and explain on a query that I want to use. And I am wondering if anyone could tell me if these results are bad or good? If everything below is coming up garbled for you I will basically I am using 7 tables and the rows for 6 of the seven are 1 and the the seventh has 190892916 effected. +---++--+-+-+--+---+-+ | table | type | possible_keys| key | key_len | ref | rows | Extra | +---++--+-+-+--+---+-+ | bl| ALL| PRIMARY,receiver | NULL|NULL | NULL | 190892916 | where used; Using temporary; Using filesort | | bm| eq_ref | PRIMARY,sender | PRIMARY | 4 | bl.id | 1 | | | m | eq_ref | PRIMARY,account_login_last_index | PRIMARY | 15 | bl.receiver | 1 | where used | | mi| eq_ref | PRIMARY | PRIMARY | 15 | m.nick | 1 | | | be| eq_ref | PRIMARY | PRIMARY | 15 | m.nick | 1 | where used; Using index; Not exists | | ms| eq_ref | PRIMARY | PRIMARY | 15 | bm.sender | 1 | | | si| eq_ref | PRIMARY | PRIMARY | 15 | ms.nick | 1 | | | z | eq_ref | PRIMARY | PRIMARY | 10 | m.zip,ms.zip | 1 | | +---++--+-+-+--+---+-+
hpux Unresolved symbol Abort(coredump)
Description: Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables /usr/lib/dld.sl: Unresolved symbol: _GLOBAL__FI_libstdc___sl_5_0 (code) from /u sr/local/mysql/libexec/mysqld /usr/lib/dld.sl: Unresolved symbol: _GLOBAL__FD_libstdc___sl_5_0 (code) from /u sr/local/mysql/libexec/mysqld /usr/local/mysql/bin/mysql_install_db[292]: 12966 Abort(coredump) Installation of grant tables failed! How-To-Repeat: /usr/local/mysql/bin/mysql_install_db Fix: not known Submitter-Id: submitter ID Originator:MySQL Database User Organization: Hauni Maschinenbau AG MySQL support: none Synopsis: install mysql hpux 11.0 Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.54 (Source distribution) Environment: System: HP-UX khs034 B.11.11 U 9000/800 2006554011 unlimited-user license Some paths: /usr/contrib/bin/perl /usr/bin/make /usr/local/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/local/lib/gcc-lib/hppa2.0n-hp-hpux11.00/3.1/specs Configured with: ./configure : (reconfigured) ./configure : (reconfigured) ./configure Thread model: posix gcc version 3.1 Compilation info: CC='gcc' CFLAGS='-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' CXX='g++' CXXFLAGS='-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' LDFLAGS='-L/usr/local/lib' LIBC: lrwxr-xr-x 1 root root 8 Jun 3 2002 /lib/libc.0 - ./libc.1 -r-xr-xr-x 1 binbin1863680 Nov 14 2000 /lib/libc.1 -r-xr-xr-x 1 binbin1785856 Nov 14 2000 /lib/libc.2 -r--r--r-- 1 binbin2473300 Nov 14 2000 /lib/libc.a lrwxr-xr-x 1 root root15 Jun 3 2002 /lib/libc.sl - /usr/lib/libc.2 lrwxr-xr-x 1 root root 8 Jun 3 2002 /usr/lib/libc.0 - ./libc.1 -r-xr-xr-x 1 binbin1863680 Nov 14 2000 /usr/lib/libc.1 -r-xr-xr-x 1 binbin1785856 Nov 14 2000 /usr/lib/libc.2 -r--r--r-- 1 binbin2473300 Nov 14 2000 /usr/lib/libc.a lrwxr-xr-x 1 root root15 Jun 3 2002 /usr/lib/libc.sl - /usr/lib/libc.2 Configure command: ./Configure '--with-pthread' '--prefix=/usr/local/mysql' '--exec-prefix=/usr/local/mysql' '--with-named-thread-libs=-lpthread' '--with-low-memory' 'CC=gcc' 'CFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CPPFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CXXFLAGS=-D_HPUX_SOURCE -D__hpux__ -D_REENTRANT' 'CXX=g++' 'LDFLAGS=-L/usr/local/lib' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql won't start but compiled correctly
HI, Can anyone figure this out ? I have been trying this out for quite some time. It compiles fine but when I run root@love(scripts)# ./mysql_install_db Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables ld.so.1: /usr/local/mysql/libexec/mysqld: fatal: libstdc++.so.3: open failed: No such file or directory Killed Installation of grant tables failed! Do I just get a copy of a library which is so rare to get and don't know why anyone used these libraries. Please advice. Thank you - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT DISTINCT w/LEFT JOIN segfault in 4.0.3
Description: A specific query in the format of: SELECT DISTINCT a.*, b.* FROM privatemessage LEFT JOIN b ON (b.x = a.y); Is causing a segfault. This worked fine in the 3.23 series. How-To-Repeat: DROP TABLE IF EXISTS privatemessage; CREATE TABLE privatemessage ( privatemessageid int(10) unsigned NOT NULL auto_increment, folderid smallint(6) NOT NULL default '0', userid int(10) unsigned NOT NULL default '0', touserid int(10) unsigned NOT NULL default '0', fromuserid int(10) unsigned NOT NULL default '0', title varchar(250) NOT NULL default '', message mediumtext NOT NULL, dateline int(10) unsigned NOT NULL default '0', showsignature smallint(6) NOT NULL default '0', iconid smallint(5) unsigned NOT NULL default '0', messageread smallint(6) NOT NULL default '0', readtime int(10) unsigned NOT NULL default '0', receipt smallint(6) unsigned NOT NULL default '0', deleteprompt smallint(6) unsigned NOT NULL default '0', multiplerecipients smallint(6) unsigned NOT NULL default '0', PRIMARY KEY (privatemessageid), KEY userid (userid) ) TYPE=MyISAM; INSERT INTO privatemessage VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0); DROP TABLE IF EXISTS user; CREATE TABLE user ( userid int(10) unsigned NOT NULL auto_increment, usergroupid smallint(5) unsigned NOT NULL default '0', username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '', email varchar(50) NOT NULL default '', styleid smallint(5) unsigned NOT NULL default '0', parentemail varchar(50) NOT NULL default '', coppauser smallint(6) NOT NULL default '0', homepage varchar(100) NOT NULL default '', icq varchar(20) NOT NULL default '', aim varchar(20) NOT NULL default '', yahoo varchar(20) NOT NULL default '', signature mediumtext NOT NULL, adminemail smallint(6) NOT NULL default '0', showemail smallint(6) NOT NULL default '0', invisible smallint(6) NOT NULL default '0', usertitle varchar(250) NOT NULL default '', customtitle smallint(6) NOT NULL default '0', joindate int(10) unsigned NOT NULL default '0', cookieuser smallint(6) NOT NULL default '0', daysprune smallint(6) NOT NULL default '0', lastvisit int(10) unsigned NOT NULL default '0', lastactivity int(10) unsigned NOT NULL default '0', lastpost int(10) unsigned NOT NULL default '0', posts smallint(5) unsigned NOT NULL default '0', timezoneoffset varchar(4) NOT NULL default '', emailnotification smallint(6) NOT NULL default '0', buddylist mediumtext NOT NULL, ignorelist mediumtext NOT NULL, pmfolders mediumtext NOT NULL, receivepm smallint(6) NOT NULL default '0', emailonpm smallint(6) NOT NULL default '0', pmpopup smallint(6) NOT NULL default '0', avatarid smallint(6) NOT NULL default '0', avatarrevision int(6) unsigned NOT NULL default '0', options smallint(6) NOT NULL default '15', birthday date NOT NULL default '-00-00', maxposts smallint(6) NOT NULL default '-1', startofweek smallint(6) NOT NULL default '1', ipaddress varchar(20) NOT NULL default '', referrerid int(10) unsigned NOT NULL default '0', nosessionhash smallint(6) NOT NULL default '0', autorefresh smallint(6) NOT NULL default '-1', messagepopup tinyint(2) NOT NULL default '0', inforum smallint(5) unsigned NOT NULL default '0', ratenum smallint(5) unsigned NOT NULL default '0', ratetotal smallint(5) unsigned NOT NULL default '0', allowrate smallint(5) unsigned NOT NULL default '1', PRIMARY KEY (userid), KEY usergroupid (usergroupid), KEY username (username), KEY inforum (inforum) ) TYPE=MyISAM; INSERT INTO user VALUES (33,6,'Kevin','0','[EMAIL PROTECTED]',1,'',0,'http://www.stileproject.com','','','','',1,1,0,'Administrator',0,996120694,1,-1,1030996168,1031027028,1030599436,36,'-6',0,'','','',1,0,1,0,0,15,'-00-00',-1,1,'64.0.0.0',0,1,-1,0,0,4,19,1); SELECT DISTINCT privatemessage.*, user.* FROM privatemessage LEFT JOIN user ON (user.userid = privatemessage.touserid); Fix: Unknown I'm happy to try any patches or poke around in GDB if needed though. Submitter-Id: Kevin Day Originator:Kevin Day Organization: Stile Project, Inc. MySQL support: none Synopsis: SELECT DISTINCT w/LEFT JOIN segfault in 4.0.3 Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.3-beta (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.37 Distrib 4.0.3-beta, for unknown-freebsdelf4.6 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.3-beta Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 6 sec Threads: 1 Questions: 1 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 0 Queries per second avg: 0.167
last_insert_id() query
Hi, I have a big problem with last_insert_id() query. I am adding records to a table with about half a million records in it. The insert takes well under a second. I then call last_insert_id as I need to make a link to another table. The last_insert_id takes around 2 minutes!!! Yes *minutes*! Has anybody any idea what might be going wrong here. The auto increment field is definitely a primary key and any other searches on the table are quite quick. Cheers - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mystery files
Hi, I my mysql server database directory (that is the directory that holds the .err and .pid files) so strange files have apeared. They look line machine-name-bin.001 machine-name-bin.002 etc machine-name-bin.index Some of them are huge! They seem like binary files, but are full readable SQL commands that could be from my applications. What is going on can I delete them? Cheers Howard Miller - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Server Installation Problem
Sounds as though you haven't got Perl installed, or more particularly the DBI modules for MySql. If you on Linux, almost certainly to be found on your Linux CDs. HM Suresh R. Soni writes: Hi All, I am getting following error msg when I try to install MySQL using rpm -i My* error: MySQL-3.23.49a-1.i386.rpm cannot be installed error: failed dependencies: data-showtable is needed by Msql-Mysql-DBI-perl-bin-1.1823-1 DBI-perl-bin is needed by Msql-Mysql-DBI-perl-bin-1.1823-1 MySQL-DBI-perl-bin is needed by MySQL-bench-3.23.49a-1 Thankx in advance. Suresh R. Soni. __ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Explanation of error message
Hi, In my mysql .err file I get a lot of the following error... Aborted connection 121031 to db: ..connection details... (Got an error reading communication packets) What does this mean, and is it bad, and what do I do about it? Any help appreciated. Howard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error message
Hi, In my mysql .err file I get a lot of the following error... Aborted connection 121031 to db: ..connection details... (Got an error reading communication packets) What does this mean, and is it bad, and what do I do about it? Any help appreciated. Howard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table RAID
I have a very large mysql table (1.5G) and so will need to implement the table RAID option soon. How do I pick the CHUNKSIZE and number of chunks values? Howard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimization And Memory Size
You have written the following: I have a mysql database table that is currently 1.5G in size with well over a million records. It is running on a twin pentium 3 1G processor machine with SuSE Linux version 1.4. Recently inserts have become VERY slow (several seconds). As I am adding around 7K new fields a day, this is becoming a big problem. I recently increased the machine memory from 512K to 2G at some expense! It made no difference. I am using the HUGE version of my.cnf as shipped. Anybody got any ideas how to speed things up. Why did the memory increase not help at all??? Howard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table RAID
Hi, Thanks I DID read the documentation. BUT there documentation gives no information about how to choose values for these settings. There are no clues at all. What are good values, and/or what is the effect on performance etc. of the values? Howard Egor Egorov writes: mysql, Tuesday, March 19, 2002, 4:27:40 PM, you wrote: mmlu I have a very large mysql table (1.5G) and so will need to implement the mmlu table mmlu RAID option soon. mmlu How do I pick the CHUNKSIZE and number of chunks values? You can read about RAID_TYPE option, RAID_CHUNKSIZE and RAID_CHUNKS in MySQL documentation in the chapter 6.5.3 CREATE TABLE Syntax at: http://www.mysql.com/doc/C/R/CREATE_TABLE.html mmlu Howard -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Update based on SELECT CRITERIA
Is there any way to do an Update...Select like an InsertSelect? For instance can I fill one table with data from another table. The columns do not match exactly so a table copy won't do much good. But the data retrieved in the select command is compatible with the new table fields. sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
error messages
Hi! Where can I find the MySQL error messages ? I have error message can't find file host.MYD errno: 2 Is it permission problems? Lacko - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Broken 'even number' rounding function
Description: Odd numbers round properly at the half (3.5) but even numbers don't (4.5). How-To-Repeat: mysql select round(15.5); +-+ | round(15.5) | +-+ | 16 | +-+ 1 row in set (0.01 sec) mysql select round(16.5); +-+ | round(16.5) | +-+ | 16 | +-+ 1 row in set (0.00 sec) Fix: If you only want the integer part without the fraction, you could add a small factor like '0.01' or smaller if the case warrants. mysql select round(16.5 + 0.01); ++ | round(16.5 + 0.01) | ++ | 17 | ++ 1 row in set (0.08 sec) But this slows down processing somewhat. Submitter-Id: submitter ID Originator:User Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-3.23.26-beta (Source distribution) Environment: System: FreeBSD guessware.dyndns.org 4.2-RELEASE FreeBSD 4.2-RELEASE #2: Wed Aug 29 12:51:11 PDT 2001 [EMAIL PROTECTED]:/usr/src/sys/compile/GUESSWARE i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.2 19991024 (release) Compilation info: CC='cc' CFLAGS='-O -pipe' CXX='c++' CXXFLAGS=' -O -pipe' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1169076 Nov 20 2000 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Aug 6 05:43 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 559196 Nov 20 2000 /usr/lib/libc.so.4 Configure command: ./configure --localstatedir=/var/db/mysql --without-perl --without-debug --without-readline --without-bench --with-mit-threads=no --enable-assembler --prefix=/usr/local i386--freebsd4.2 Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fail to compile on Solaris 8 Intel
Hi, I keep running into the following error trying to compile (running ./configure) mysql -3.23.38 on Solaris 8 x86: checking return type of sprintf... configure: error: can not run test program while cross compiling I'm using gcc 2.95.3. Has anyone compiled succesfully on sol8 x86 and is willing to give me some tips? Thanks up front. John - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Parse error?
You should be using echo in place of print. echo OK 2; Robert Henkel Shouldn't you have on line 7 print (OK 2 ); and not print (OK 2 ) Im not a PHP person but thats what I noticed in your code. And if ; terminates a command that can't be helping magic words sql database -Original Message- From: MTF [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 16, 2001 2:13 PM To: [EMAIL PROTECTED] Subject: Parse error? I am new to MySQL and am using Paul DuBois's book to MySQL guide me. (Great Book!) I am attempting to use PHP, My scripts always connect to the MySQL server OK, and the Databases to, but my queries always fail with the following message: OK 1 OK 2 Parse error: parse error in /home/httpd/html/test10.php3 on line 8 (I place the OK 1 etc to help me find problems) This is the PHP3 script ?php $link = mysql_pconnect (localhost, test, test) or die (Could not connect); print (OK 1 ); mysql_select_db (samp_db) or die (Could not select database); print (OK 2 ) $query = SELECT COUNT(*) FROM president; $result = mysql_query ($query) or die (Query failed); Print (OK 3 ) ? Using PHP I can create drop databases all OK but every query I've tried gives me a parse error and I've looked trough all my books and can't find any mention of what they are and how you fix them. Thanks Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get your FREE download of MSN Explorer at http://explorer.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MYSQL not starting properly
I had the same problem on with Red Hat 7 using mysql-3.23.33. I installed from RPM and source. Never could get the darn thing working. I just scrapped .33 and downloaded .36. Works like a charm now. DG Description: I am having a problem starting the mysql daemon from the command line. I am using the following command from /usr/local/mysql :- bin/safe_mysqld -u root The resultant error file /usr/local/mysql/data/bon.err, has the following entries:- mysqld started on Thursday April 26 14:24:48 BST 2001 010426 14:24:48 /usr/local/mysql/bin/mysqld: Table 'mysql.host' doesn't exist 010426 14:24:48 /usr/local/mysql/bin/mysqld: Normal shutdown 010426 14:24:48 /usr/local/mysql/bin/mysqld: Shutdown Complete mysqld ended on Thursday April 26 14:24:48 BST 2001 Any clues ? How-To-Repeat: Use above command Fix: Unknown Submitter-Id: submitter ID Originator:Mark Leedham Organization: Startle plc 18-21 Cavaye Place Chelsea London UK SW10 9PT +44 (0)207 341 0947 MySQL support: none Synopsis: mysql not starting properly Severity: critical Priority: high Category: mysql Class: support Release: mysql-3.23.33 (Official MySQL binary) Environment: machine System: SunOS bon 5.7 Generic_106541-14 sun4u sparc SUNW,Ultra-80 Architecture: sun4 Some paths: /usr/bin/perl /usr/local/bin/make /usr/local/bin/gcc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.7/2.8.1/specs gcc version 2.8.1 Compilation info: CC='gcc' CFLAGS='-O3 ' CXX='gcc' CXXFLAGS='-O3 -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rw-r--r-- 1 bin bin 1732880 Dec 15 00:43 /lib/libc.a lrwxrwxrwx 1 root root 11 Sep 30 2000 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1147500 Dec 15 00:44 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1732880 Dec 15 00:43 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Sep 30 2000 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1147500 Dec 15 00:44 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --enable-assembler --disable-shared _ Get your FREE download of MSN Explorer at http://explorer.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
test suite skips all tests
Hello, I didn't receive any responses, so I thought I would try resending this. I just installed mysql (from source) on three machines, and I have encountered the same problem on all of them. Everything appears to work fine (including the benchmark tests), but when I attempt to run the test suite (the script mysql-test-run under the source directory), I get something that ends like this: update [ skipped ] varbinary [ skipped ] variables [ skipped ] warnings [ skipped ] Ending Tests Shutting-down MySQL daemon Master shutdown finished Slave shutdown finished All 0 tests were successful. Not very helpful. :) I looked at mysqld-slave.err, but it didn't mean much to me (maybe someone here can see the problem). I have included one page of it below. Thanks for your help. The machines consist of basic Red Hat clones. I had not yet installed a root password when I tried to run the tests. /tmp/mysql-3.23.33/sql/mysqld: ready for connections 010303 1:41:46 Slave: connected to master '[EMAIL PROTECTED]:9306', replication started in log 'master-bin.001' at position 73 010303 1:41:46 /tmp/mysql-3.23.33/sql/mysqld: Normal shutdown 010303 1:41:46 Slave thread exiting, replication stopped in log 'master-bin.001' at position 73 010303 1:41:46 /tmp/mysql-3.23.33/sql/mysqld: Shutdown Complete User time 0.01, System time 0.00 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 95, Physical pagefaults 381, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0 /tmp/mysql-3.23.33/sql/mysqld: ready for connections 010303 1:41:47 Slave: connected to master '[EMAIL PROTECTED]:9306', replication started in log 'master-bin.001' at position 73 010303 1:41:48 Aborted connection 3 to db: 'unconnected' user: 'root' host: `localhost' (Got an error writing communication packets) 010303 1:41:48 /tmp/mysql-3.23.33/sql/mysqld: Normal shutdown 010303 1:41:48 Slave thread exiting, replication stopped in log 'master-bin.001' at position 73 010303 1:41:48 /tmp/mysql-3.23.33/sql/mysqld: Shutdown Complete User time 0.01, System time 0.00 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 97, Physical pagefaults 381, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0 /tmp/mysql-3.23.33/sql/mysqld: ready for connections 010303 1:41:49 Slave: connected to master '[EMAIL PROTECTED]:9306', replication started in log 'master-bin.001' at position 73 010303 1:41:49 /tmp/mysql-3.23.33/sql/mysqld: Normal shutdown 010303 1:41:49 Slave thread exiting, replication stopped in log 'master-bin.001' at position 73 010303 1:41:49 /tmp/mysql-3.23.33/sql/mysqld: Shutdown Complete User time 0.02, System time 0.00 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 97, Physical pagefaults 381, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0 /tmp/mysql-3.23.33/sql/mysqld: ready for connections 010303 1:41:51 Aborted connection 2 to db: 'unconnected' user: 'root' host: `localhost' (Got an error writing communication packets) 010303 1:41:51 /tmp/mysql-3.23.33/sql/mysqld: Normal shutdown 010303 1:41:51 /tmp/mysql-3.23.33/sql/mysqld: Shutdown Complete User time 0.01, System time 0.01 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 95, Physical pagefaults 383, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0 __ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices. http://auctions.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
test suite skips all tests
Hello, I just installed mysql (from source) on three machines, and I have encountered the same problem on all of them. Everything appears to work fine (including the benchmark tests), but when I attempt to run the test suite (the script mysql-test-run under the source directory), I get something that ends like this: update [ skipped ] varbinary [ skipped ] variables [ skipped ] warnings [ skipped ] Ending Tests Shutting-down MySQL daemon Master shutdown finished Slave shutdown finished All 0 tests were successful. Not very helpful. :) I looked at mysqld-slave.err, but it didn't mean much to me (maybe someone here can see the problem). I have included one page of it below. Thanks for your help. The machines consist of basic Red Hat clones. I had not yet installed a root password when I tried to run the tests. /tmp/mysql-3.23.33/sql/mysqld: ready for connections 010303 1:41:46 Slave: connected to master '[EMAIL PROTECTED]:9306', replication started in log 'master-bin.001' at position 73 010303 1:41:46 /tmp/mysql-3.23.33/sql/mysqld: Normal shutdown 010303 1:41:46 Slave thread exiting, replication stopped in log 'master-bin.001' at position 73 010303 1:41:46 /tmp/mysql-3.23.33/sql/mysqld: Shutdown Complete User time 0.01, System time 0.00 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 95, Physical pagefaults 381, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0 /tmp/mysql-3.23.33/sql/mysqld: ready for connections 010303 1:41:47 Slave: connected to master '[EMAIL PROTECTED]:9306', replication started in log 'master-bin.001' at position 73 010303 1:41:48 Aborted connection 3 to db: 'unconnected' user: 'root' host: `localhost' (Got an error writing communication packets) 010303 1:41:48 /tmp/mysql-3.23.33/sql/mysqld: Normal shutdown 010303 1:41:48 Slave thread exiting, replication stopped in log 'master-bin.001' at position 73 010303 1:41:48 /tmp/mysql-3.23.33/sql/mysqld: Shutdown Complete User time 0.01, System time 0.00 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 97, Physical pagefaults 381, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0 /tmp/mysql-3.23.33/sql/mysqld: ready for connections 010303 1:41:49 Slave: connected to master '[EMAIL PROTECTED]:9306', replication started in log 'master-bin.001' at position 73 010303 1:41:49 /tmp/mysql-3.23.33/sql/mysqld: Normal shutdown 010303 1:41:49 Slave thread exiting, replication stopped in log 'master-bin.001' at position 73 010303 1:41:49 /tmp/mysql-3.23.33/sql/mysqld: Shutdown Complete User time 0.02, System time 0.00 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 97, Physical pagefaults 381, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0 /tmp/mysql-3.23.33/sql/mysqld: ready for connections 010303 1:41:51 Aborted connection 2 to db: 'unconnected' user: 'root' host: `localhost' (Got an error writing communication packets) 010303 1:41:51 /tmp/mysql-3.23.33/sql/mysqld: Normal shutdown 010303 1:41:51 /tmp/mysql-3.23.33/sql/mysqld: Shutdown Complete User time 0.01, System time 0.01 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 95, Physical pagefaults 383, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 0, Involuntary context switches 0 __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
followup to lockup in test suite on SGI
Description: On several occasions I reported problems with MySQL and our Webmail installation (using the program IMP). All MySQL processes would lock. No process would be shown to execute. The Only thing to correct the problem was to kill MySQL and restart Apache. People at MySQL pointed at a faulty lpthread library, but having no access to the latest SGI bug list, I could not verify. I try several things as - use --skip-locking - use low-priority-thread - don't use persistent connections in php.ini - etc... Lockups continued. This was back in december. Later at the beginning of the year, I decided to try the new versions with the test suite. I tried on different SGI boxes we have here. Not one could complete the tests. They would all lock at one point or another, waiting for a signal. Recently, I decided to test with a different user than root. I reported the results in february (re: lockup in test suite on SGI). All tests worked without problem on all servers tried. It has now been more than a week without any lockup in our production webmail environment. I upgraded to MySQL 3.23.33 last week. How-To-Repeat: Start Mysql with root user or with the CAP_SCHED_MGT privilege. Do a high volume of select/insert/replace/delete (3 or 4 requests per sec). Eventually mysqld will lock. All threads then appear to be locked on the active_sessions table. Killing the oldest thread (and the second one some times) clears the lock. This seem to appear on SGI IP19 multiprocessors architecture with IRIX 6.5.x system. Fix: Run MySQL as a non-root user and DO NOT give it the CAP_SCHED_MGT privilege. I found that it is true you need the CAP_SCHED_MGT privilege to run mysqld with the user parameter when launched by safe_mysql as root, but you don't need it if you run safe_mysql under the "su" command. The matter is closed for me as this works. I now found MySQL very stable. Thanks. Submitter-Id: submitter ID Originator:Super-User Organization: Universite de Montreal MySQL support: none Synopsis: lockups when root on SGI Severity: critical Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.33 (Source distribution) Environment: System: IRIX64 acces 6.5 04191225 IP19 Some paths: /usr/sbin/perl /sbin/make /usr/bin/cc Compilation info: CC='cc' CFLAGS='' CXX='CC' CXXFLAGS='' LDFLAGS='' LIBC: -r-xr-xr-t1 root sys 2332864 Aug 31 16:10 /lib/libc.so.1 lrwxr-xr-x1 root sys 19 Nov 7 15:09 /usr/lib/libc.so - ../../lib/libc.so.1 lrwxr-xr-x1 root sys 19 Nov 7 15:12 /usr/lib/libc.so.1 - ../../lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql --without-berkeley-db Perl: This is perl, version 5.004_04 built for irix-n32 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Configure script fails trying to get size of char type in Solaris 8
Description: running ./configure with no options works fine until the test program to determine the size of the char data type is compiled and run. I've compiled and run the sample program manually, and added a printf() statement to show the result that's placed into the temporary file using the fprint() line, and the result is '1'. I don't fully-understand how the configure script works, but something is not accepting the value placed in the temporary file. How-To-Repeat: All I need to do is re-run the configure script and it always fails at the same point. The very same thing happens with the configure script for version 3.23.32 also. My hardware and operating system types are shown below. Fix: Not sure how to fix it, but I think the problem is in the configure script itself. gcc builds the test program without any errors, and it runs fine, producing the correct output result. Submitter-Id: submitter ID Originator:Super-User Organization: Core Matrix Foundation MySQL support: [none | licence | email support | extended email support ] Synopsis: seems to be a compatibility issue with Solaris 8 - maybe Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-3.22.32 (Source distribution) Environment: System: SunOS jedi 5.8 Generic sun4m sparc SUNW,SPARCstation-20 Architecture: sun4 Some paths: /usr/bin/perl /usr/ccs/bin/make /usr/local/bin/gcc /usr/ucb/cc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.8/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' Configure command: ./configure --with-unix-socket-path=/var/tmp/mysql.sock --with-low-memory --with-mit-threads=yes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
