10 minutes seems excessive...
Hello all... Source table has approximately 23,000,000 rows. The explain below shows we'll be working with 2,707,366 of those rows just inserting them into a table. Why does this take 10 minues to run? System specifications below all that. * Precompiled binary from mysql.com * Completely idle machine * Completely idle disk * Not I/O bound during query. * Mysqld maxing one cpu out reading data, but not writing to any temp tables on disk for quit some time. * Started with mysql-huge reference config. 'explain select * from below': == table: source_table type: range possible_keys: PRIMARY,MONTH_DAY key: PRIMARY key_len: 1 ref: NULL rows: 2707366 Extra: Using where CREATE TABLE tmp.seperate_disk_partition SELECT * FROM source_table WHERE month_day IN (14, 7, 31, 24) Query OK, 2862629 rows affected (9 min 37.36 sec) Records: 2862629 Duplicates: 0 Warnings: * Sun 420, 4-400mhz, 4gb ram. 100gb EMC attached via fiber channel. Server version4.0.18-standard Protocol version 10 ConnectionLocalhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 7 days 9 hours 6 min 50 sec Threads: 1 Questions: 3103 Slow queries: 89 Opens: 352 Flush tables: 1 Open C compiler:gcc (GCC) 3.3 C++ compiler: gcc (GCC) 3.3 Environment: machine, os, target, libraries (multiple lines) System: SunOS f10212-06.adc1.level3.com 5.8 Generic_108528-22 sun4u sparc SUNW,U Architecture: sun4 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server Perf Increase
hello list, i have high performance dell server. i want to increase Read_Buffer_Size server variable to 5 MB. i have found docs on this in mysql site. like this mysqld_safe --key_buffer_size=64M --table_cache=256 \ --sort_buffer_size=4M --read_buffer_size=1M where i type that command i am getting status message as Mysqld is already running. how can i change those variables to take effect permanently. thanks, Kumar Yahoo! India Matrimony: Find your partner online. http://yahoo.shaadi.com/india-matrimony/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server Variable Parameters
hello list, i have high performance dell server. i want to increase Read_Buffer_Size server variable to 5 MB. i have found docs on this in mysql site. like this mysqld_safe --key_buffer_size=64M --table_cache=256 \ --sort_buffer_size=4M --read_buffer_size=1M where i type that command i am getting status message as Mysqld is already running. how can i change those variables to take effect permanently. thanks, Kumar Yahoo! India Matrimony: Find your partner online. http://yahoo.shaadi.com/india-matrimony/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT duplicate rows
Is there a way to use a SELECT statement (or any other, for that matter) that will look at every table in a database and return every row whose first 3 columns are duplicated in at least one other row in any of the tables? Essentially, a command to find duplicate entries in the database . . . Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR ON INSERT DATA FROM FILE
adrian Greeman [EMAIL PROTECTED] wrote: I just tried to load edited data from a text file separated by | characters into a table in a simple data base I was using PhpMyAdmin version 2.5.6 and MySQL 4.0.18 running on Windows XP - I thought it was all set up right and configured properly - and earlier was able to populate the table with some initial data. Now I was trying to add more from a simple text file using the LOAD DATA LOCAL INFILE 'C:\\WINDOWS\\TEMP\\php3E.tmp' INTO TABLE `headlines` FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' instruction which phpMyAdmin creates. It refused to do it and returned the error message: #1148 - The used command is not allowed with this MySQL version I feel there must be a simple explanation LOAD DATA LOCAL should be enabled for client and for the server: http://dev.mysql.com/doc/mysql/en/LOAD_DATA_LOCAL.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: long string
[EMAIL PROTECTED] wrote: hi, I am trying to enter a genome sequence into mysql database. I have created a database in mysql as: create table sequence(seq blob); the length of my sequence is approx. 170. this is the error i get whe i run my python script: OperationalError: (2006, 'MySQL server has gone away') i am not sure what is wrong. Check values of wait_timeout, max_allowed_packet variables: http://dev.mysql.com/doc/mysql/en/Gone_away.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: spatial woes - again
hi - Ive posted this before but not had any answers... Im trying to load spatial data as OGC WKT into a geometry field but a number of rows consistently fail. I can se no reaspn for this other than their length - does anyone know what the size limit for insertion into a geometry field is (Im looking at polygons failing anywhere between 140K to 50K)... Ive tried on msql on both a unix box and a win xp machien and get the same failures so I guess its something that Im missing ?? thoughts appreciated james, edinburgh To err is human... to programme is folly
Re: how to speed up a simple query? can anyone help with an ideea?
Creating a combined index can help MySQL in using this index for both the where condition and the order by clause. Try the query with an index on cat,date and with date,cat; maybe one will be faster than the other. This partially solved my problem. Thanks a lot. However I am facing a new problem here. The query where I am ordering by a column is much more slowly than the same query NOT using SQL_CALC_FOUND_ROWS select SQL_CALC_FOUND_ROWS * from table by date desc - this query takes about 1.2 s select * from table by date desc - this query takes about 0.1 s Using explain I have noticed that the first query is using filesort and I cannot figure out why exactly... I could use count() to figure out the total number of results for the navigation and this would be defintelly much faster since now as I have tested but I was thinking if anyone else had this problem before and if you have found any work around... After some checkings In have discovered that this is more or less a bug http://bugs.mysql.com/bug.php?id=1274 But after checking very ddpe here and trying all possibilities I think this is a little different... Let me know if anyone wants to see the table structure... Thanks, Arthur -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Perf Increase
Kumar [EMAIL PROTECTED] wrote: i have high performance dell server. i want to increase Read_Buffer_Size server variable to 5 MB. i have found docs on this in mysql site. like this mysqld_safe --key_buffer_size=64M --table_cache=256 \ --sort_buffer_size=4M --read_buffer_size=1M where i type that command i am getting status message as Mysqld is already running. how can i change those variables to take effect permanently. You can restart MySQL server with new value of read_buffer_size. If version of MySQL server = 4.0.3 you can set variable at runtime using SET statement: http://dev.mysql.com/doc/mysql/en/Server_system_variables.html http://dev.mysql.com/doc/mysql/en/Dynamic_System_Variables.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT duplicate rows
Yes, there is a way. It's called joins. :) I don't remember the exact syntax off the top of my head, but the approach is thus: Do a self join on the table and select records that match in their first three columns, but do not have the same primary key (you *do* have primary keys on your table, don't you?). If you don't add one for this excercise. j- k- On Tuesday 20 April 2004 11:22 pm, John Mistler said something like: Is there a way to use a SELECT statement (or any other, for that matter) that will look at every table in a database and return every row whose first 3 columns are duplicated in at least one other row in any of the tables? Essentially, a command to find duplicate entries in the database . -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR ON INSERT DATA FROM FILE
Thanks for the answer - but I am already the root user and have phpMyAdmin using the root also (because this is just a single computer for testing work and there are not any bit security issues). - Original Message - From: Remi Mikalsen [EMAIL PROTECTED] To: adrian Greeman [EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 1:47 AM Subject: Re: ERROR ON INSERT DATA FROM FILE Hello Adrian. I'm not very experienced with MySQL, but I believe that you need special privileges to load data from a file. Normally, other than the root user will not have this kind of privilege. Without it you will probably have to ask for someone with do it for you, or give you permissions to do it. If you have access to the root account, then try it from there! Remi Mikalsen On 21 Apr 2004 at 0:17, adrian Greeman wrote: I just tried to load edited data from a text file separated by | characters into a table in a simple data base I was using PhpMyAdmin version 2.5.6 and MySQL 4.0.18 running on Windows XP - I thought it was all set up right and configured properly - and earlier was able to populate the table with some initial data. Now I was trying to add more from a simple text file using the LOAD DATA LOCAL INFILE 'C:\\WINDOWS\\TEMP\\php3E.tmp' INTO TABLE `headlines` FIELDS TERMINATED BY '|' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' instruction which phpMyAdmin creates. It refused to do it and returned the error message: #1148 - The used command is not allowed with this MySQL version I feel there must be a simple explanation May I ask if anyone can help??? Regards Adrian PS I am only on digest so please be patient if it takes a while for futher dialogue on this -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Abraço, Remi Mikalsen E-Mail: [EMAIL PROTECTED] URL: http://www.iMikalsen.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT duplicate rows
Thanks for the response, Joshua. I am so very new to MySQL, that I am afraid I require more guidance. Is there a way to join ALL tables in a database rather than just one table to itself, or one particular table to another? SELECT * FROM allTables WHERE column1=column1 AND column2=column2 AND column3=column3; I know this syntax is off the mark--it should specify: table1.column1=table2.column1, etc. However, I need it to match columns on all of the tables in the database (of which there are many), rather than just two. Any ideas? Thanks, John on 4/21/04 12:57 AM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Yes, there is a way. It's called joins. :) I don't remember the exact syntax off the top of my head, but the approach is thus: Do a self join on the table and select records that match in their first three columns, but do not have the same primary key (you *do* have primary keys on your table, don't you?). If you don't add one for this excercise. j- k- On Tuesday 20 April 2004 11:22 pm, John Mistler said something like: Is there a way to use a SELECT statement (or any other, for that matter) that will look at every table in a database and return every row whose first 3 columns are duplicated in at least one other row in any of the tables? Essentially, a command to find duplicate entries in the database . -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT duplicate rows
Well, doing on all tables at once woule probably bring the server to its knees due to the cartesian product producing a VERY large temporary table. You can do it on two tables at once like this (if my memory serves): SELECT * from mytable as t1, mytable as t2 WHERE t1.column1 = t2.column1 AND t1.column2 = t2.column2 AND t1.column3 = t2.column3 AND t1.id t2.id Of course, you can extend that to as many tables as you want, but the syntax and performance complications quickly arise. I would recommend comparing all your tables to one another, two at a time. A quick perl or C script should accomplish this quickly. For 10 tables, that is only 45 queries. Not bad. j- k- On Wednesday 21 April 2004 12:56 am, John Mistler said something like: Thanks for the response, Joshua. I am so very new to MySQL, that I am afraid I require more guidance. Is there a way to join ALL tables in a database rather than just one table to itself, or one particular table to another? SELECT * FROM allTables WHERE column1=column1 AND column2=column2 AND column3=column3; I know this syntax is off the mark--it should specify: table1.column1=table2.column1, etc. However, I need it to match columns on all of the tables in the database (of which there are many), rather than just two. Any ideas? Thanks, John on 4/21/04 12:57 AM, Joshua J. Kugler at [EMAIL PROTECTED] wrote: Yes, there is a way. It's called joins. :) I don't remember the exact syntax off the top of my head, but the approach is thus: Do a self join on the table and select records that match in their first three columns, but do not have the same primary key (you *do* have primary keys on your table, don't you?). If you don't add one for this excercise. j- k- On Tuesday 20 April 2004 11:22 pm, John Mistler said something like: Is there a way to use a SELECT statement (or any other, for that matter) that will look at every table in a database and return every row whose first 3 columns are duplicated in at least one other row in any of the tables? Essentially, a command to find duplicate entries in the database . -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL: Table locking problems when non-index keys used
Hi Friends, We are using mysql 4.0.17 with innodb option. In a query, when a WHERE clause contains a non-indexed columns, it locks the entire table instead of row lock. Is there any solution apart from building index on each query key ? Is there a solution in any of the later versions ? With Best Regards, Ravi Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments.
Re: Randomly selecting from table
If you want to guarantee that the selections are different, rand() doesn't quite do it, as you will get a repeated value with the appropriate probability. You will need to keep a record of what values have already been seen. Then, use something like select ... from my_table left join my_records_used using (my_id) where my_records_used.my_id is null order by rand() limit 1 Date: Tue, 20 Apr 2004 13:08:01 -0700 (PDT) Subject: Re: Randomly selecting from table From: Daniel Clark [EMAIL PROTECTED] To: Eve Atley [EMAIL PROTECTED] Guess you could use the rand() function and look for a matching row id!?! Is it possible to randomly select from all entries in a table, but have it be 2 different ones each time? If so, what documentation should I be looking at? I am using PHP and MySQL together, if this helps. Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB SHOW STATUS
hi try this URL: http://www.innodb.com/ibman.php -arun. --- Emmett Bishop [EMAIL PROTECTED] wrote: Howdy all, Quick question about what I'm seeing in the BUFFER POOL AND MEMORY section... I've configured the innodb_buffer_pool_size to be 128M and when I do a show variables like 'innodb%' I see | innodb_buffer_pool_size | 134217728 | So that looks good. However, I see the following in the BUFFER POOL AND MEMORY section of the output from the innodb monitor: -- BUFFER POOL AND MEMORY -- Total memory allocated 152389988; in additional pool allocated 1048576 Buffer pool size 8192 Free buffers 0 Database pages 7947 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 20345325, created 9857, written 763089 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 Why does it say the buffer pool size is only 8M? Shouldn't it be 128M? Also, could someone explain the hit rate? I remember seeing in someone's recent post that the 1000/1000 is good, but I don't know what that means. Can someone suggest a good resouce that explains the contents of Innodb show status in detail. The page on www.mysql.com gives a very cursory overview of the output. Cheers, Tripp __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Yahoo! India Matrimony: Find your partner online. http://yahoo.shaadi.com/india-matrimony/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi-user / transactions question..
Jeremy Smith said: A typical auction lasts around 90 minutes, and consists of a whole lot of furious refreshing of the auction board by the participants since each player only has a 2 minute clock. You might wish to look into using a different mechanism for this so you can push the latest bids to the user instead of depending on their refresh. Java applets can do this, and Flash Shared Objects make this very easy. They have the obvious drawbacks (accessibility, plugins, etc.), but when used properly they can significantly reduce the server load and bandwidth consumption. Since there is so much clicking going on, and my php code and mysql calls (including the transactions that decide which new player should be nominated to the board) obviously happen with each user click, is there any way to hide the guts of the work so that when they click refresh all they are doing is viewing the state of the respective tables at that time. Have transactions update summary tables (HEAP?) and query those summary tables. In other words, I don't need 4 people simultaneously calling the functions that decide which player should be nominated next, awarded the player that was won to the necessary roster, deducting money, etc. With four people calling the function at once, I am forced to lock up the rows and rollback 3 of the transactions. Is there any way to have them all call the same function that only gets executed once? There are ways, but they are generally implemented in the middelware, not the database. The best you can do in the database is use the summary tables to detect and abort conflicting transactions as early as possible. Also, please read http://www.catb.org/~esr/faqs/smart-questions.html#id2912983 Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Slow user authentication cross server...
Sorry for the re-post but there was no response and this is starting to become a big problem... The basics are that connecting to mysql from another machine the initial connection is taking 20 seconds and showing up in the process list as unauthenticated user for this period. Any help appreciated. -- Dave - Original Message - From: David Scott [EMAIL PROTECTED] To: MYSQL list [EMAIL PROTECTED] Sent: Wednesday, April 14, 2004 12:13 PM Subject: Slow user authentication cross server... Hi peeps, The setup I have is 2 windows 2000 servers, server A = IIS5 server B = IIS5 + MySQL. The servers are connected by a 2nd network card in each machine and can see each other fine. When I connect to any database on server B using ASP executed on server B its lightning fast, but connecting to the same database on server B from server A the initial connection takes around 20 seconds (well I timed it 18.8 seconds to connect then 0.5 seconds to query any other table but if left for 60 seconds it goes back to 18.8 seconds) I looked at the process list using mySQL CC and it shows that when I request the connection from server A the process comes up as: id: 459 user: unauthenticated user host: XXX.XXX.XXX.XXX:1265 db: [NULL] command: Connect Time: [NULL] State: login Info: [NULL] And then after 20 seconds: id: 459 user: masterServer host: XXX.XXX.XXX.XXX:1265 db: _tools command: Sleep Time: 5 Info: [NULL] I even created the user masterServer and set its host to the IP of that machine but no joy. My question is, how do I reduce this user authentication time when connecting from a remote server? Driving me nuts -- David Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: genome sequence
Hi Liz, a column of type BLOB takes a maximum of 65535 bytes; try MEDIUMBLOB or even LONGBLOB. Regards, Thomas Spahni On Tue, 20 Apr 2004 [EMAIL PROTECTED] wrote: hi, I am trying to enter genome sequences of length 170 and more into mysql database. I have created a table sequence like: create table sequence(seq blob); i am using python scripts to put the sequence into this field. the python GUI gives me this error: OperationalError: (2006, 'MySQL server has gone away') I am not able to enter the sequence. what is wrong??? Liz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication in 5.0.0-alpha
Hi, collegues On Sat, Apr 17, 2004 at 05:19:16PM +0300, Oleg P. Philon wrote: Have I use precompiled binaries from ftp.mysql.com? Ya, I have to Is there working setups with 5.0.0 and replication? Again yes, in mein setup at last Auf Wiederlesenophil aka - -- Oleg P. Philon http://gomelug.agava.ru/articles Linux Lab, Gomel, Belarus mailto:ophil(at)gomelug.agava.ru http://anticommunist.narod.ru mailto:anticommunist(at)narod.ru -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.0-alpha-max-debug running on localhost as ODBC@localhost
Marvin Cummings [EMAIL PROTECTED] wrote: I'm not actually getting an error. I'm simply unable to access any of my databases while logged in as [EMAIL PROTECTED] I'd like to change this and specify what account is used to establish a connection. How and where do I make this change in MySQL? ODBC is default username that is used on Windows. Create a new user account and use it to connect to the MySQL server: http://dev.mysql.com/doc/mysql/en/Adding_users.html -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Friday, April 16, 2004 10:22 AM To: [EMAIL PROTECTED] Subject: Re: MySQL 5.0.0-alpha-max-debug running on localhost as [EMAIL PROTECTED] Marvin Cummings [EMAIL PROTECTED] wrote: This appears when I attempt to open phpMyAdmin. It doesn't matter what account I set in the config.inc.php file, I continue to get this error. How do I change this so that another account logs in to the localhost? I unfortunately had to reboot my server and this became the result. I notice that I can log in to MySQLCC and the command line as root without a problem. What error do you get? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query help [resend]
Hi, I have two tables- books and copies every book has an id in the books table every copy of a book has the books id and a copy id in the copies table (1 row per copy) I want a list of all the books that don't have any copies meaning all the book id's in books that don't match any book id's in copies. how can I do this? thanks yonah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Website
I should have been clearer. I can't reach the website. I can get to lists.mysql.com with no problem except for the fact that images won't pull form www.mysql.com but I definitely come to a grinding halt when I try to reach www.mysql.com. I can't do a tracert because the university has shut that off here. But I guess it is working for everyone else. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 6:31 PM To: Lehman, Jason (Registrar's Office) Subject: Re: MySQL Website - Original Message - From: Lehman, Jason (Registrar's Office) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 11:53 AM Subject: MySQL Website Does anyone know what is going on with the MySQL website? It appears to be undergoing a major redesign. The sections appear to be organized differently and the style sheets have also changed. Or did you have something else in mind? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Website
On Wed, Apr 21, 2004 at 08:08:29AM -0400, Lehman, Jason (Registrar's Office) wrote the following: I should have been clearer. I can't reach the website. I can get to lists.mysql.com with no problem except for the fact that images won't pull form www.mysql.com but I definitely come to a grinding halt when I try to reach www.mysql.com. I can't do a tracert because the university has shut that off here. But I guess it is working for everyone else. www.trace-route.org ;) snip -- Joseph A. Nagy, Jr. http://joseph-a-nagy-jr.homelinux.org Political Activist Extraordinaire Peace, Life, Liberty The only fallacy is the inaction on our part to stave off the worst of horrors, the stripping of personal freedom. -- Joseph A. Nagy, Jr. January 2004 pgp0.pgp Description: PGP signature
Re: MySQL Website
Lehman, Jason (Registrar's Office) wrote: I should have been clearer. I can't reach the website. I can get to lists.mysql.com with no problem except for the fact that images won't pull form www.mysql.com but I definitely come to a grinding halt when I try to reach www.mysql.com. I can't do a tracert because the university has shut that off here. But I guess it is working for everyone else. I'm experiencing similar problems - using both Mozilla and IE. 'wget' eventually got the HTML but it took nearly 2 minutes. The headers don't suggest anything strange. This is also a University site with 'traceroute' disabled and everything accessed through a cache. www.netcraft.com's site analysis also doesn't suggest anything untoward. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 6:31 PM To: Lehman, Jason (Registrar's Office) Subject: Re: MySQL Website - Original Message - From: Lehman, Jason (Registrar's Office) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 11:53 AM Subject: MySQL Website Does anyone know what is going on with the MySQL website? It appears to be undergoing a major redesign. The sections appear to be organized differently and the style sheets have also changed. Or did you have something else in mind? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Website
It appears to be the web server. I can reach mysql.com just fine in a traceroute, but can't get a HEAD or webpage to come up! traceroute to mysql.com (66.35.250.190), 30 hops max, 38 byte packets 1 cdm-208-180-236-1.cnro.cox-internet.com (208.180.236.1) 18.963 ms 10.260 ms 12.200 ms 2 cdm-208-180-1-50.cnro.cox-internet.com (208.180.1.50) 7.622 ms 9.933 ms 9.904 ms 3 cdm-208-180-1-73.cnro.cox-internet.com (208.180.1.73) 17.948 ms 17.666 ms 14.908 ms 4 dllsbbrc01-gew0402.ma.dl.cox-internet.com (66.76.45.145) 128.870 ms 182.677 ms 91.958 ms 5 dllsdsrc01-gew0303.rd.dl.cox.net (68.1.206.5) 23.685 ms 26.633 ms 22.810 ms 6 dllsbbrc01-pos0101.rd.dl.cox.net (68.1.0.144) 23.805 ms 26.595 ms 27.092 ms 7 12.119.145.125 (12.119.145.125) 79.373 ms 78.874 ms 75.386 ms 8 gbr6-p30.dlstx.ip.att.net (12.123.17.54) 75.101 ms 79.933 ms 74.823 ms 9 tbr2-p013701.dlstx.ip.att.net (12.122.12.89) 82.161 ms 80.284 ms 77.678 ms 10 ggr2-p390.dlstx.ip.att.net (12.123.17.85) 78.322 ms 75.077 ms 81.961 ms 11 dcr2-so-4-0-0.Dallas.savvis.net (208.172.139.225) 76.214 ms 77.886 ms 76.674 ms 12 dcr2-loopback.SantaClara.savvis.net (208.172.146.100) 108.356 ms 105.723 ms 112.343 ms 13 bhr1-pos-0-0.SantaClarasc8.savvis.net (208.172.156.198) 95.535 ms 88.560 ms 84.063 ms 14 csr1-ve243.SantaClarasc8.savvis.net (66.35.194.50) 88.678 ms 86.770 ms 85.408 ms 15 66.35.212.174 (66.35.212.174) 89.425 ms 89.129 ms 98.684 ms 16 mysql.com (66.35.250.190) 87.200 ms 85.178 ms 87.600 ms Thanks, Brad Teale Universal Weather and Aviation, Inc. mailto:[EMAIL PROTECTED] -Original Message- From: Peter Burden [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 7:35 AM To: Lehman, Jason (Registrar's Office) Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: MySQL Website Lehman, Jason (Registrar's Office) wrote: I should have been clearer. I can't reach the website. I can get to lists.mysql.com with no problem except for the fact that images won't pull form www.mysql.com but I definitely come to a grinding halt when I try to reach www.mysql.com. I can't do a tracert because the university has shut that off here. But I guess it is working for everyone else. I'm experiencing similar problems - using both Mozilla and IE. 'wget' eventually got the HTML but it took nearly 2 minutes. The headers don't suggest anything strange. This is also a University site with 'traceroute' disabled and everything accessed through a cache. www.netcraft.com's site analysis also doesn't suggest anything untoward. -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 6:31 PM To: Lehman, Jason (Registrar's Office) Subject: Re: MySQL Website - Original Message - From: Lehman, Jason (Registrar's Office) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 11:53 AM Subject: MySQL Website Does anyone know what is going on with the MySQL website? It appears to be undergoing a major redesign. The sections appear to be organized differently and the style sheets have also changed. Or did you have something else in mind? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help
I got a response off the list suggesting writing a function to go over the query results- it's not hard but I'd rather do this in sql if possible. I came up with this: select books.bookid,books.title,copies.copyid from books left join copies on books.bookid=copies.bookid where copies.copyid=NULL; this didn't work even though without the where clause I got exactly what I wanted- the left join filled in the entries that didn't have copies with a null copyid. what did I do wrong? thanks yonah Yonah Russ wrote: Hi, I have two tables- books and copies every book has an id in the books table every copy of a book has the books id and a copy id in the copies table (1 row per copy) I want a list of all the books that don't have any copies meaning all the book id's in books that don't match any book id's in copies. how can I do this? thanks yonah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help [resend]
Yonah Russ [EMAIL PROTECTED] wrote: Hi, I have two tables- books and copies every book has an id in the books table every copy of a book has the books id and a copy id in the copies table (1 row per copy) I want a list of all the books that don't have any copies meaning all the book id's in books that don't match any book id's in copies. how can I do this? Use LEFT JOIN. For example: SELECT .. FROM book_table LEFT JOIN copy_table ON book_table.id=copy_table.book_id WHERE copy_table.book_id IS NULL; http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multi-user / transactions question..
I appreciate the reply, I do plan on expanding this to make the use of flash and a flash server at some point, but I want to offer the option of HTML auctions as well. I will look into summary tables as it is not something I am familiar with. Btw, I'm not sure why you sent me a link on How To Ask Questions The Smart Way. That seemed rather insulting, especially without explaining what you thought was so problematic about the way I framed my question. Jeremy -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 5:35 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Multi-user / transactions question.. Jeremy Smith said: A typical auction lasts around 90 minutes, and consists of a whole lot of furious refreshing of the auction board by the participants since each player only has a 2 minute clock. You might wish to look into using a different mechanism for this so you can push the latest bids to the user instead of depending on their refresh. Java applets can do this, and Flash Shared Objects make this very easy. They have the obvious drawbacks (accessibility, plugins, etc.), but when used properly they can significantly reduce the server load and bandwidth consumption. Since there is so much clicking going on, and my php code and mysql calls (including the transactions that decide which new player should be nominated to the board) obviously happen with each user click, is there any way to hide the guts of the work so that when they click refresh all they are doing is viewing the state of the respective tables at that time. Have transactions update summary tables (HEAP?) and query those summary tables. In other words, I don't need 4 people simultaneously calling the functions that decide which player should be nominated next, awarded the player that was won to the necessary roster, deducting money, etc. With four people calling the function at once, I am forced to lock up the rows and rollback 3 of the transactions. Is there any way to have them all call the same function that only gets executed once? There are ways, but they are generally implemented in the middelware, not the database. The best you can do in the database is use the summary tables to detect and abort conflicting transactions as early as possible. Also, please read http://www.catb.org/~esr/faqs/smart-questions.html#id2912983 Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query help
I suspect you want 'IS NULL' rather than '= NULL'. :) I always find it best to think of NULL as undefined value rather than no value - which is why you need to check for it especially (using IS rather than = or other operators). Cheers, Matt -Original Message- From: Yonah Russ [mailto:[EMAIL PROTECTED] Sent: 21 April 2004 14:47 To: MySQL List Subject: Re: query help I got a response off the list suggesting writing a function to go over the query results- it's not hard but I'd rather do this in sql if possible. I came up with this: select books.bookid,books.title,copies.copyid from books left join copies on books.bookid=copies.bookid where copies.copyid=NULL; this didn't work even though without the where clause I got exactly what I wanted- the left join filled in the entries that didn't have copies with a null copyid. what did I do wrong? thanks yonah Yonah Russ wrote: Hi, I have two tables- books and copies every book has an id in the books table every copy of a book has the books id and a copy id in the copies table (1 row per copy) I want a list of all the books that don't have any copies meaning all the book id's in books that don't match any book id's in copies. how can I do this? thanks yonah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error when dumping DBs.
JR [EMAIL PROTECTED] wrote: I am getting the following error when trying to dump my DBs. /usr/bin/mysqldump: Got error: 1103: Incorrect table name '/home/jr/backups/20040420/wcp.sql' when doing LOCK TABLES Version is: 4.0.18-standard These DBs where on another box running the same version. MySQL was shutdown on both the old and new box, the DB directory structure was copied to a new server. MySQL was restarted and everything works fine as far as I can tell except I can't dump the DBs. What command do you use to dump tables? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Website
I have not been able to access the mysql.com server for about a day and a half now from my office. From home, it's fine. There have been rare occasions in the past when our provider had dropped (or very slow) connectivity with certain nodes on the Internet. My understanding there is limited, but I know that there are really only a handful of actual back-bone providers out there for the 'Net, and if a main provider has problems with one of those points, it can take down (or slow) access to vast geographical areas. I believe that's what's going on with our provider at present. However, getting them to troubleshoot it is another matter altogether. It usually starts with Did you restart your modem? and degrades from there. I can get to every other site that I normally visit without problems. Still no MySQL as of 9:14am EST. Lou - Original Message - From: Yves Goergen [EMAIL PROTECTED] To: Lehman, Jason (Registrar's Office) [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 6:00 PM Subject: Re: MySQL Website On 20.04.2004 17:53 (+0100), Lehman, Jason (Registrar's Office) wrote: Does anyone know what is going on with the MySQL website? No, it's accessible as usual. But with Firefox, only at the second try. Could also be a browser problem, I'm using an older nightly build. -- Yves Goergen [EMAIL PROTECTED] BlackBoard Internet Newsboard System -- blackboard.unclassified.de Free (GPL), easy to use and install, secure, innovative! (PHP+MySQL) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Website - TRACERT
Here's my tracert: 325 ms25 ms29 ms 65-86-11-209.client.dsl.net [65.86.11.209] 423 ms29 ms24 ms unknown.Level3.net [209.247.230.161] 528 ms25 ms26 ms so-5-0-0.bbr2.Chicago1.Level3.net [4.68.112.209] 623 ms25 ms25 ms so-7-0-0.edge1.Chicago1.Level3.net [209.244.8.14] 725 ms26 ms24 ms bpr1-ge-7-0-0.ChicagoEquinix.savvis.net [208.174.226.61] 827 ms25 ms24 ms dcr2-so-4-3-0.Chicago.savvis.net [208.175.10.237] 988 ms90 ms87 ms dcr2-loopback.SantaClara.savvis.net [208.172.146.100] 1088 ms88 ms87 ms bhr1-pos-0-0.SantaClarasc8.savvis.net [208.172.156.198] 1186 ms87 ms90 ms csr1-ve243.SantaClarasc8.savvis.net [66.35.194.50] 1289 ms91 ms91 ms 66.35.212.174 13 *** Request timed out. 14 *** Request timed out. 15 *** Request timed out. - Original Message - From: Lou Olsten [EMAIL PROTECTED] To: Yves Goergen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 9:14 AM Subject: Re: MySQL Website I have not been able to access the mysql.com server for about a day and a half now from my office. From home, it's fine. There have been rare occasions in the past when our provider had dropped (or very slow) connectivity with certain nodes on the Internet. My understanding there is limited, but I know that there are really only a handful of actual back-bone providers out there for the 'Net, and if a main provider has problems with one of those points, it can take down (or slow) access to vast geographical areas. I believe that's what's going on with our provider at present. However, getting them to troubleshoot it is another matter altogether. It usually starts with Did you restart your modem? and degrades from there. I can get to every other site that I normally visit without problems. Still no MySQL as of 9:14am EST. Lou - Original Message - From: Yves Goergen [EMAIL PROTECTED] To: Lehman, Jason (Registrar's Office) [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 6:00 PM Subject: Re: MySQL Website On 20.04.2004 17:53 (+0100), Lehman, Jason (Registrar's Office) wrote: Does anyone know what is going on with the MySQL website? No, it's accessible as usual. But with Firefox, only at the second try. Could also be a browser problem, I'm using an older nightly build. -- Yves Goergen [EMAIL PROTECTED] BlackBoard Internet Newsboard System -- blackboard.unclassified.de Free (GPL), easy to use and install, secure, innovative! (PHP+MySQL) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BETWEEN
I too cannot get the site correctly. I am using IE. If I wait long enough (about 2 min) text will appear but the images never seem to make it. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Andy Eastham [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 10:12 AM To: Mysql List Subject: RE: BETWEEN Max, You can measure the elapsed time by writing a linux shell script to do the inserts, then use the linux time command to run it. However, the user and system times displayed will not include the amount of cpu time used by the db server. Do it a few times and vary the number of inserts to build an accurate picture. Alternatively, you may be able to do this easier in version 4.1, where you can use %f in time_format to get milliseconds, so hopefully now() retrieves milliseconds too(?): select time_format(now(), '%H:%i:%s.%f'); to get timestamps to the nearest millisecond. Of course, getting the timestamp takes a finite amount of time, which you may want to measure. Andy -Original Message- From: Boyd E. Hemphill [mailto:[EMAIL PROTECTED] Sent: 20 April 2004 14:29 To: 'Max Michaels'; 'mysql' Subject: RE: BETWEEN Max: Thanks for the tip. Unfortunately I am not using a FreeBSD environment. My options are to either run a WinXP client remotely or to run something Linux based in a terminal emulator (Putty). Any suggestions would be appreciated. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Max Michaels [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 20, 2004 7:07 AM To: 'Boyd E. Hemphill'; 'mysql' Subject: RE: BETWEEN Hello: I am trying to measure the difference between a single insert statement of 10,000 rows and 10,000 insert statements. It is easy for me to see the single statement takes about 2 seconds. However I can come up with no good way to get the total time for individual statements. Can anyone provide a suggestion? Thanks in advance. Try super-smack. It works great for this type of testing. http://jeremy.zawodny.com/mysql/super-smack/ Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Client Program Questions
At 16:03 -0500 4/20/04, Dirk Bremer (NISC) wrote: I have a couple of questions concerning the MySql client program. 1. Are the option for the client program, i.e. --auto-rehash, etc. documented anywhere? I searched the included HTML file and could not find a reference to the client options. What does the --auto-rehash option do? Heck, what do all of the rest of the options do? mysql --help http://dev.mysql.com/doc/mysql/en/mysql.html 2.When in the client program, what do the clear, ego, go, and rehash commands do? Is there documentation for these commands? http://dev.mysql.com/doc/mysql/en/mysql_Commands.html 3. Speaking specifically of Win2K platforms, what would be required to install just the client program for other users to be able to access the database server program residing on a different machine? Install MySQL normally, then throw away everything you don't need? -- Paul DuBois, MySQL Documentation Team Paul, I once spent a pleasant afternoon in Madison waiting on a rescheduled flight after a driving trip from Madison to LaCrosse and back. Note that the first link that you posted does not work, at least not with the current problems the website is experiencing. Concerning the second question I asked and the link that you provided, it still doesn't answer my question. For example: help(\h)Display this help. ? (\?)Synonym for `help'. clear (\c)Clear command. connect (\r)Reconnect to the server. Optional arguments are db and host. ego (\G)Send command to mysql server, display result vertically. exit(\q)Exit mysql. Same as quit. go (\g)Send command to mysql server. notee (\t)Don't write into outfile. print (\p)Print current command. prompt (\R)Change your mysql prompt. quit(\q)Quit mysql. rehash (\#)Rebuild completion hash. source (\.)Execute a SQL script file. Takes a file name as an argument. status (\s)Get status information from the server. tee (\T)Set outfile [to_outfile]. Append everything into given outfile. use (\u)Use another database. Takes database name as argument. Re the ego command, what commands can it send to the server and why would you want to use ego? Same for the clear and go command. For the third question, I direct my response to a wider audience and suggest that there should be a client-only installation download available. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Hot Backup Tool
The good, bad, and the ugly of it? We're debating if we should use it on our INNODB tables or use a script to do a hotbackup. On Tue, Apr 20, 2004 at 01:54:37PM -0400, McConnell, Ann M. wrote: Does anyone have any experience with Innodb Hot Backup Tool? Yes. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 219 days, processed 4,118,140,114 queries (216/sec. avg)
Re: MySQL Website
On 21 Apr 2004 at 9:14, Lou Olsten wrote: I have not been able to access the mysql.com server for about a day and a half now from my office. From home, it's fine. There have been rare occasions in the past when our provider had dropped (or very slow) connectivity with certain nodes on the Internet. My understanding there is limited, but I know that there are really only a handful of actual back-bone providers out there for the 'Net, and if a main provider has problems with one of those points, it can take down (or slow) access to vast geographical areas. I believe that's what's going on with our provider at present. However, getting them to troubleshoot it is another matter altogether. It usually starts with Did you restart your modem? and degrades from there. I can get to every other site that I normally visit without problems. Still no MySQL as of 9:14am EST. Hi, No luck with www.mysql.com but http://dev.mysql.com/ works fine. Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Website
FYI... Just connected to the www.mysql.com site this morning with no problems :) -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] 1.985.320.1191 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Ian Gibbons wrote: On 21 Apr 2004 at 9:14, Lou Olsten wrote: I have not been able to access the mysql.com server for about a day and a half now from my office. From home, it's fine. There have been rare occasions in the past when our provider had dropped (or very slow) connectivity with certain nodes on the Internet. My understanding there is limited, but I know that there are really only a handful of actual back-bone providers out there for the 'Net, and if a main provider has problems with one of those points, it can take down (or slow) access to vast geographical areas. I believe that's what's going on with our provider at present. However, getting them to troubleshoot it is another matter altogether. It usually starts with Did you restart your modem? and degrades from there. I can get to every other site that I normally visit without problems. Still no MySQL as of 9:14am EST. Hi, No luck with www.mysql.com but http://dev.mysql.com/ works fine. Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Renaming a column
Hi, I am hoping to get some advice on a problem I have. I have a table that have 109 million records and is about 30 G in size. I need to rename a column, because it seem that localtime have become a reserved word. The alter table statement makes mysql generate a tmp table and regenerate the index. And with a 30G data file and a 20G index file this takes some time. I have tried the following: 1. first I did a ALTER TABLE xxx CHANGE.. 2. next I copied the #sql-zxzxxcxcssd.frm file 3. then I killed the ALTER TABLE command 4. In the end I stopped mysqld and switched the old .frm file with the new (from 2.) And it seem to work. My question is if any body now / can think of any reason not to change the column name this way. Waiting for the index to rebuild takes at least a week, and I don't want to wait. I have only changed the column name, not the type. And the column is not part of any index. Hope to hear from you! Best regards, Gunnar
Re: INNODB SHOW STATUS
Arun, Unfortunately, that link contains the exact same info as does the mysql.com page. There's almost no explaination there of what any of the output means. I'm sure that it's really useful stuff but I don't have a starting point. Can someone point me in the right direction (a book or website) or explain briefly what I'm seeing in the BUFFER POOL AND MEMORY section? Brief intro, I set the innodb_buffer_pool_size to 128M in the my.cnf file. A quick peek at show variables confirms this value... -- BUFFER POOL AND MEMORY -- Total memory allocated 152389988; in additional pool allocated 1048576 Buffer pool size 8192 Free buffers 0 Database pages 7947 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 20345325, created 9857, written 763089 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 Why does it say the buffer pool size is only 8M? Shouldn't it be 128M? Also, could someone explain the hit rate? I remember seeing in someone's recent post that the 1000/1000 is good, but I don't know what that means. Cheers, Tripp __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing App Settiings
I am developing a php/mysql web application, and I am facing the task of storing application settings. Because it is web based, I will need to read the app settings on a per-session basis. I see one of two choices here - store each setting as a separate record in the table, with the value always stored as a string, or store one record with a separate column with the appropriate data type for each setting. How have others done this? What method is the fastest/most flexible/easiest to maintain and extend? --Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB SHOW STATUS
On Tue, 20 Apr 2004, Emmett Bishop wrote: Howdy all, Quick question about what I'm seeing in the BUFFER POOL AND MEMORY section... I've configured the innodb_buffer_pool_size to be 128M and when I do a show variables like 'innodb%' I see | innodb_buffer_pool_size | 134217728 | So that looks good. However, I see the following in the BUFFER POOL AND MEMORY section of the output from the innodb monitor: -- BUFFER POOL AND MEMORY -- Total memory allocated 152389988; in additional pool allocated 1048576 Buffer pool size 8192 Free buffers 0 Database pages 7947 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 20345325, created 9857, written 763089 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 Why does it say the buffer pool size is only 8M? Shouldn't it be 128M? Also, could someone explain the hit rate? I remember seeing in someone's recent post that the 1000/1000 is good, but I don't know what that means. Can someone suggest a good resouce that explains the contents of Innodb show status in detail. The page on www.mysql.com gives a very cursory overview of the output. Buffer pool size, free buffers, database pages, and modified database pages are in 16k pages. The buffer pool hit rate simply says the fraction of page reads satisfied from the innodb buffer cache, in this case 1000/1000 == 100%. Unfortunately, I'm not really aware of a better reference. Perhaps some of this is explained in High Performance MySQL, but I don't have a copy yet. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication problem
Hi, i'm new to this list, but i use mysql for years an are very happy with it. However, today i ran into a problem that i couldn't find a solution for: I set up database replication with a master and one slave, and it works fine so far. I rewrote my application (web based written in php) so that it executes all queries that insert, delete or update rows are executed on the master, and all other queries on the slave. Fine. But what, if the master fails? I want users to be able to continue working on the slave, and this works fine for webpages that just do select statements. I thought that, in case of the master being down, i could execute all data-modifying queries on the slave, and also store these queries in a special table or file, and re-execute them later on the master if the master becomes available again. The problem is, that i then may get Duplicate entry ... for key ... errors on the slave, if a record was already inserted into a table with unique keys, and that the sql-thread on the slave then exits. Is it possible to make the slave ignore such errors (i found no option for this in the docs) and just stupidly continue replication, or does anyone have a better idea how to set up such a scenario? Bye, Georg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie - Load Data Command and File Location
I'm working through a self study book and I am trying to learn how to do a load data local infile. I downloaded a sample database and I have the txt file but each time I type in the command LOAD DATA LOCAL INFILE 'member.txt' INTO TABLE member; I get the following message ERROR: File 'member.txt' not found (errcode 2) I have tried to move the txt file to the data and bin folders but with no luck. Also, I'm am struggling with learning how to change locations. Sorry to be a mutt
Re: Problem retrieving Unicode data
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Stormblade wrote: Thanks. I'd read that already but I re-read it and noticed this part here: When a client connects, it sends to the server the name of the character set that it wants to use. The server sets the character_set_client, character_set_results, and character_set_connection variables to that character set. (In effect, the server performs a SET NAMES operation using the character set.) So basically on the client end when I make a connection I need to tell it to use a character set. I'll have to check my documentation. I'm connecting via JDBC so I'm guessing it must be part of the connection string. Stormblade, If you're using UTF-8 data and JDBC, and you _haven't_ set the _server_ charset to UTF-8, you'll need to add 'characterEncoding=UTF-8' as a property to your JDBC connection string. You'll also probably want to use the latest nightly snapshot of either Connector/J 3.0 or 3.1 as they have code to support the behavior of Unicode charsets in MySQL-4.1.2. See http://downloads.mysql.com/snapshots.php for more information. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD4DBQFAhpS0tvXNTca6JD8RAlc7AKCRIHqxyro+5lc721Q4rrPhGwnElQCVGvtL OQlzSKgNZfr5xema2CA5vA== =Pt0L -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
files stored in fields
Please excuse a very simple inquiry from a near beginner If I wish to store a complete Word file or similar (Open Office perhaps) or an archive file - is that possible and what kind of field do I need for it? How do you insert a file into a table if so? I know that you can store pictures - what field do I need for those and again how to insert? Regards Adrian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Modelling specialized M:N relationships?
I'm actually i deep doubts with this. Price is not a thing itself, good point. But in my case, it was the best way i found out to model. Suppose you got a company the rents a place (just a big room, or a party house) for any kinda envents you'd to make. The company is clear an entity, and the price would probably be its attribut, despite the following problem: This company charges diferent prices depending on the kind of party you'd like to make on its party House. You might have a company that charges you the following. - Wedding party U$100 - Under Graduation party (prom) U$200 - high school grad party (hs prom) U$ 300 Beyond that, there are the ordinary companies that have only one basic price for any kind of party (they don't change their prices depending on the parties). That's why i decided to model it as an entity, but i'm not sure in two aspects. - Should i have a M:N or a 1:N relation between these entities (note that this is actually not that trivial, maybe modelled both ways) - Should i just have a column in my price entity to specify the kinda party (like PARTY_TYPE) or just have 3 or more specific columns for the party type (lik PRICE_WEDDING, PRICE_GRADUATION, PRICE_HIGH_SCHOOL). Well, that's it ... If u could give an advise... Thanks in advance, ltcmelo --- Robert J Taylor [EMAIL PROTECTED] escreveu: Leandro Melo wrote: Hi, i have a M:N relationship between PRODUCT and PRICE. Is Price an Entity or an Attribute? I'm not accustomed to using price as a Thing. What is it that causes a Product to have one, exactly, or more prices? Is it its relationship to some other thing? Or is it a special kind of Product? Except during a couple years under US President Nixon, Prices are usually not things in themselves... can you flesh out what you are trying to model a little more? 1 product may be associated to N prices and 1 prices may belong to N products. I got special cases the some kinda product MUST have only 1 price associated with it. I don't what would be the best way to model this flag for the special case. So far, i got 3 tables. - PRODUCT (PRODUCT_ID as PK) - PRICE (PRICE_ID as PK) - PRODUCT_PRICE (with fields PRODUCT_ID and PRICE_ID) Where should i put this flg or should tie the relations??? Let's see what relationship to a another entity or classifying attribute of Product might invoke the rule on one-and-only-one or M:N. Then we'll know how better to proceed. Thanks, ltcmelo __ HTH, Robert Taylor [EMAIL PROTECTED] begin:vcard fn:Robert Taylor n:Taylor;Robert adr;dom:;;9 Via Latigo;Rancho Santa Margarita;CA;92688 email;internet:[EMAIL PROTECTED] tel;work:949 677 0321 tel;cell:949 677 0321 x-mozilla-html:FALSE url:http://rjamestaylor.com version:2.1 end:vcard __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Distribution and Shared Libraries
Why doesn't the MySQL binary distribution include the shared libraries? (At least, the Mac OS X version does not) I'd prefer to use this distribution, but I have to roll my own in order to get shared libs to use with PHP, Perl (for mytop), etc. - Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
reuse a results set
I have a form that does the following ? while ($speaker = db_fetch_object($qid_speaker)) { ? option value=? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? } ? /select $qid_speaker = db_query(SELECT first_name, last_name FROM people WHERE speaker = 'Y' ORDER BY last_name); I am using this in a form select drop down box. What I need to do is reuse this results set in another drop down box. but I can not figure out how to do this. If I use the mysql_free-result($qid_speaker) and then try to do the query again I get a mysql_fetch_object(): 12 is not a valid MYSQL result resource. TIA Scott Swaim Quality Corps, Inc.
Re: Problem retrieving Unicode data
On Wed, 21 Apr 2004 10:35:16 -0500, Mark Matthews wrote: Stormblade, If you're using UTF-8 data and JDBC, and you _haven't_ set the _server_ charset to UTF-8, you'll need to add 'characterEncoding=UTF-8' as a property to your JDBC connection string. I have set the default charset to utf8. When I view my variables they all show that utf8 as their value. My URL is as follows: jdbc:mysql://localhost/mydb?useUnicode=trueamp;characterEncoding=UTF8 I tried setting useUnicode to false but it didn't have any effect. I also tried just putting in the rather than amp; but that also didn't have any effect. I also tried saying UTF-8 as opposed to UTF8 but still nothing. You'll also probably want to use the latest nightly snapshot of either Connector/J 3.0 or 3.1 as they have code to support the behavior of Unicode charsets in MySQL-4.1.2. See http://downloads.mysql.com/snapshots.php for more information. I did try the latest 3.1. It still behaved in the same manner and what was worse, it broke another page of mine. I started getting an error when I tried to display that page. It said: Unknown type '0 in column 9 of 16 in binary-encoded result set. I did a google on this and found others having that problem and it was driver related. http://article.gmane.org/gmane.comp.db.mysql.java/3347 So since it didn't fix my issue and caused another I went back. That page was handled differently than my others. That page used JSTL queries to get it's result set. My other pages did it from a Java bean. Using Java I could query and retrieve all the date from that table but the JSTL queries gave that error. But apparently it's a bug in the driver so I have been wary of those drivers. I can try the nightly snapshots but this is bad because this project will need to be production ready and if it needs to use trial stuff like this... I'm gonna try all this week to get this working but I can't really afford to spend more time than that. My client got busy so I got some breathing room. I already know that using SQLServer and their JDBC driver works just fine. But the whole purpose of the project was to convert everything to a lower cost alternative. The app has already been converted from ColdFusion to JSP and I also wanted to go from SQLServer to MySQL since most of the hosting I've seen offer MySQL with the most basic service and SQLServer only with their higher more expensive plans. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: files stored in fields
adrian Greeman said: Please excuse a very simple inquiry from a near beginner If I wish to store a complete Word file or similar (Open Office perhaps) or an archive file - is that possible and what kind of field do I need for it? How do you insert a file into a table if so? I know that you can store pictures - what field do I need for those and again how to insert? Regards Adrian Yes you would need to use a 'blob' type field. However, please consider writing the information to the file system and then storing the address on the file system in the database. Last time I checked fixed length database records process much faster. What computer language are you using to do the work. That would help in answering the rest of the question. -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication problem
Add this in your my.cnf slave file: slave-skip-errors = 1062 Marc. -Message d'origine- De : Georg Horn [mailto:[EMAIL PROTECTED] Envoyé : mercredi 21 avril 2004 17:31 À : [EMAIL PROTECTED] Objet : Replication problem Hi, i'm new to this list, but i use mysql for years an are very happy with it. However, today i ran into a problem that i couldn't find a solution for: I set up database replication with a master and one slave, and it works fine so far. I rewrote my application (web based written in php) so that it executes all queries that insert, delete or update rows are executed on the master, and all other queries on the slave. Fine. But what, if the master fails? I want users to be able to continue working on the slave, and this works fine for webpages that just do select statements. I thought that, in case of the master being down, i could execute all data-modifying queries on the slave, and also store these queries in a special table or file, and re-execute them later on the master if the master becomes available again. The problem is, that i then may get Duplicate entry ... for key ... errors on the slave, if a record was already inserted into a table with unique keys, and that the sql-thread on the slave then exits. Is it possible to make the slave ignore such errors (i found no option for this in the docs) and just stupidly continue replication, or does anyone have a better idea how to set up such a scenario? Bye, Georg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - Load Data Command and File Location
Chris Stevenson wrote: I'm working through a self study book and I am trying to learn how to do a load data local infile. I downloaded a sample database and I have the txt file but each time I type in the command LOAD DATA LOCAL INFILE 'member.txt' INTO TABLE member; I get the following message ERROR: File 'member.txt' not found (errcode 2) I have tried to move the txt file to the data and bin folders but with no luck. Also, I'm am struggling with learning how to change locations. Include the full path to the file. Sorry to be a mutt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: files stored in fields
Sure.. checkout this article: http://php.dreamwerx.net/forums/viewtopic.php?t=6 Very fast mysql storage implementation in PHP, port the design to whatever lanaugage suits you. On Wed, 21 Apr 2004, adrian Greeman wrote: Please excuse a very simple inquiry from a near beginner If I wish to store a complete Word file or similar (Open Office perhaps) or an archive file - is that possible and what kind of field do I need for it? How do you insert a file into a table if so? I know that you can store pictures - what field do I need for those and again how to insert? Regards Adrian -- 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]
mySqlDump grant command
I am sure that i used mysqldump to create a script that had both CREATE TABLE and GRANT commands, but now I can't figure out how. Carl K http://www.personnelware.com/carl/resume.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restrictions on inserting ???
Hi, is there a way i can make a restriction for inserting data on a table? I'll expose this idea with a simple (not real) example. Suppose i got table EMPLOYEE, wich has an attribute called FLG_HAS_DEPENDENTS. I also have a table called DEPENDENTS, wich has data for the dependents of some employees. Although, i can only let the inserting of dependents information for the employees_id that have the FLG_HAS_DEPENDENTS checked. I know this is part of my business logic, but i'd like to make one more checking before inserting on db. Thanks, ltcmelo = beginner __ Yahoo! Messenger - Fale com seus amigos online. Instale agora! http://br.download.yahoo.com/messenger/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Confused by max and group by
I'm having trouble with max() and group by. It seems pretty simple. I hope someone can point out my mistake. I want to select the max index of a group. In other words, I want to find the last record added for each group. The problem I'm having is that the columns of the resulting rows are mixed with different records. I get the expected indexes returned, but the fields appear to be from another record and not the fields associated with the index. I ran this query: SELECT max(myindex), myval, mycat FROM `mytest` GROUP BY mycat; and I get the following results: +--+---+---+ | max(myindex) | myval | mycat | +--+---+---+ | 3| one | A | | 9| one | B | +--+---+---+ But I was expecting this: +--+---+---+ | max(myindex) | myval | mycat | +--+---+---+ | 3| one | A | | 9| three | B | +--+---+---+ This is my test data. CREATE TABLE `mytest`( `myindex` int(11) NOT NULL default '0', `myval` varchar(40) NOT NULL default '', `mycat` varchar(40) NOT NULL default '', PRIMARY KEY (`myindex`) ) TYPE=MyISAM; INSERT INTO `mytest` VALUES (1, 'one', 'A'); INSERT INTO `mytest` VALUES (2, 'two', 'A'); INSERT INTO `mytest` VALUES (3, 'three', 'A'); INSERT INTO `mytest` VALUES (4, 'one', 'B'); INSERT INTO `mytest` VALUES (5, 'two', 'B'); INSERT INTO `mytest` VALUES (6, 'three', 'B'); INSERT INTO `mytest` VALUES (7, 'one', 'B'); INSERT INTO `mytest` VALUES (8, 'two', 'B'); INSERT INTO `mytest` VALUES (9, 'three', 'B'); Yours, Noah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query question
I'm trying to select specified data from a field in a table. The field from which the data has to come contains the following: 'something;else;anything;everything;name;my' (and so on), it's a long text. I need in the case just 'my' from the field, thus between the ';'. This time there are only two characters, but I can't say by forehand how many letters there will be. The only thing that's sure it comes after 'name', so I have the following query: SELECT SYS_IDX, LEFT(RIGHT(C, LENGTH(C)-LOCATE(';',C,LOCATE('name',C))),10) FROM A; This return's up to ten characters after the name, somethimes this is to much, sometimes to many. Does anybody knows how to go from here. I'm using mysql 3.21 AC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySqlDump grant command
At 11:57 -0500 4/21/04, Carl Karsten wrote: I am sure that i used mysqldump to create a script that had both CREATE TABLE and GRANT commands, but now I can't figure out how. mysqldump doesn't generate GRANT statements. Perhaps you are thinking of some other program. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Confused by max and group by
You aren't making any mistakes, it's just not possible to do. You can't rely on which row MySQL will return when using a GROUP BY clause. The standard method would be to do something like this: CREATE TEMPORARY TABLE mytemptable SELECT max(myindex) as myindex, mycat FROM `mytest` GROUP BY mycat; then SELECT myval, myotherrows, mycat FROM `mytemptable` LEFT JOIN mytest USING(myindex,mycat) note: I haven't tested the above code, it's just an example of the theory Chris -Original Message- From: Noah Spurrier [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 10:35 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Confused by max and group by I'm having trouble with max() and group by. It seems pretty simple. I hope someone can point out my mistake. I want to select the max index of a group. In other words, I want to find the last record added for each group. The problem I'm having is that the columns of the resulting rows are mixed with different records. I get the expected indexes returned, but the fields appear to be from another record and not the fields associated with the index. I ran this query: SELECT max(myindex), myval, mycat FROM `mytest` GROUP BY mycat; and I get the following results: +--+---+---+ | max(myindex) | myval | mycat | +--+---+---+ | 3| one | A | | 9| one | B | +--+---+---+ But I was expecting this: +--+---+---+ | max(myindex) | myval | mycat | +--+---+---+ | 3| one | A | | 9| three | B | +--+---+---+ This is my test data. CREATE TABLE `mytest`( `myindex` int(11) NOT NULL default '0', `myval` varchar(40) NOT NULL default '', `mycat` varchar(40) NOT NULL default '', PRIMARY KEY (`myindex`) ) TYPE=MyISAM; INSERT INTO `mytest` VALUES (1, 'one', 'A'); INSERT INTO `mytest` VALUES (2, 'two', 'A'); INSERT INTO `mytest` VALUES (3, 'three', 'A'); INSERT INTO `mytest` VALUES (4, 'one', 'B'); INSERT INTO `mytest` VALUES (5, 'two', 'B'); INSERT INTO `mytest` VALUES (6, 'three', 'B'); INSERT INTO `mytest` VALUES (7, 'one', 'B'); INSERT INTO `mytest` VALUES (8, 'two', 'B'); INSERT INTO `mytest` VALUES (9, 'three', 'B'); Yours, Noah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - Load Data Command and File Location
At 11:29 -0400 4/21/04, Chris Stevenson wrote: I'm working through a self study book and I am trying to learn how to do a load data local infile. I downloaded a sample database and I have the txt file but each time I type in the command LOAD DATA LOCAL INFILE 'member.txt' INTO TABLE member; I get the following message ERROR: File 'member.txt' not found (errcode 2) If you type the filename like that for LOAD DATA LOCAL, the file must be located in the same directory where you're running the client program. If the file is in some other directory, name the full path to the file. (If you're on Windows, specify '\' characters in the pathname as '/' or as '\\'.) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
If() syntax question
Is it possible to do something like this? If(select * from xxx, if record found..update it, if record not found ..insert it) Thanks, Don
RE: If() syntax question
From: Don Dachner [mailto:[EMAIL PROTECTED] Is it possible to do something like this? If(select * from xxx, if record found..update it, if record not found ..insert it) Try the REPLACE INTO syntax: http://dev.mysql.com/doc/mysql/en/REPLACE.html -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
On 21-Apr-2004 Alex croes wrote: I'm trying to select specified data from a field in a table. The field from which the data has to come contains the following: 'something;else;anything;everything;name;my' (and so on), it's a long text. I need in the case just 'my' from the field, thus between the ';'. This time there are only two characters, but I can't say by forehand how many letters there will be. The only thing that's sure it comes after 'name', so I have the following query: SELECT SYS_IDX, LEFT(RIGHT(C, LENGTH(C)-LOCATE(';',C,LOCATE('name',C))),10) FROM A; This return's up to ten characters after the name, somethimes this is to much, sometimes to many. Does anybody knows how to go from here. LEFT(SUBSTRING_INDEX(foo, ';', 1), 10) Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
grant problem!!!
hi, I am having problems with mysql. 1. I downloaded mysql on to my system. 2.installed it 3. ran the server using the command prompt C:\mysql\bin\mysqld --console it gave me the results that i could start using the service. 4. i started mysql on another command prompt. what i am trying to do is i have to enter a huge genome sequence into a field. In order to do tht i have to change the global and session variables in mysql. i can change the session variables but i am not able to change the global variables. I gave the command: set global max_allowed_packet=4; it said: Access denied. You need the SUPER privilege for this operation. Then i created a database genome_db . then at the command promp i typed grant all privileges on genome_db.* to root@localhost identified by ' '; i got the result as: Access denied for user: @'localhost' to database 'genome_db' dont know what to do:( confused plzzz help Liz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem retrieving Unicode data
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Stormblade wrote: On Wed, 21 Apr 2004 10:35:16 -0500, Mark Matthews wrote: Stormblade, If you're using UTF-8 data and JDBC, and you _haven't_ set the _server_ charset to UTF-8, you'll need to add 'characterEncoding=UTF-8' as a property to your JDBC connection string. I have set the default charset to utf8. When I view my variables they all show that utf8 as their value. My URL is as follows: What does the JDBC connection _itself_ show? Execute the following query on a java.sql.Statement, and tell us what comes back, as the values you have posted earlier (from a connection from the 'mysql' command-line client) are irrelevent in this situation: SHOW VARIABLES LIKE 'character%' jdbc:mysql://localhost/mydb?useUnicode=trueamp;characterEncoding=UTF8 I tried setting useUnicode to false but it didn't have any effect. I also tried just putting in the rather than amp; but that also didn't have any effect. I also tried saying UTF-8 as opposed to UTF8 but still nothing. You'll also probably want to use the latest nightly snapshot of either Connector/J 3.0 or 3.1 as they have code to support the behavior of Unicode charsets in MySQL-4.1.2. See http://downloads.mysql.com/snapshots.php for more information. I did try the latest 3.1. It still behaved in the same manner and what was worse, it broke another page of mine. I started getting an error when I tried to display that page. It said: Unknown type '0 in column 9 of 16 in binary-encoded result set. HmmmThat was fixed in the _server_ after 4.1.1 was released, so if you're not using a compile from source that was pulled from our archives, then you will have to wait until 4.1.2 comes out. I did a google on this and found others having that problem and it was driver related. http://article.gmane.org/gmane.comp.db.mysql.java/3347 You missed the rest of the thread, then, it is actually _server_ related, and was fixed for MySQL-4.1.2 (not yet released, but available as source code): http://article.gmane.org/gmane.comp.db.mysql.java/3350 So since it didn't fix my issue and caused another I went back. That page was handled differently than my others. That page used JSTL queries to get it's result set. My other pages did it from a Java bean. Using Java I could query and retrieve all the date from that table but the JSTL queries gave that error. But apparently it's a bug in the driver so I have been wary of those drivers. Did you ever look and see what encoding your JSPs are set at? If it's not UTF-8, you will see this behavior, because JSTL will use the encodings of your JSPs. It is strange (but not dismisable) that it would work from stock java bean and not a JSTL tag and that this would be caused by a _driver_ bug. I can try the nightly snapshots but this is bad because this project will need to be production ready and if it needs to use trial stuff like this... MySQL-4.1 is an ALPHA. The JDBC driver is an ALPHA, and you are using features that are only in the ALPHA. For this reason you are going to have to put up with using nightly snapshots and/or doing your own builds if you want the latest bug fixes. I'm gonna try all this week to get this working but I can't really afford to spend more time than that. My client got busy so I got some breathing room. I already know that using SQLServer and their JDBC driver works just fine. But the whole purpose of the project was to convert everything to a lower cost alternative. The app has already been converted from ColdFusion to JSP and I also wanted to go from SQLServer to MySQL since most of the hosting I've seen offer MySQL with the most basic service and SQLServer only with their higher more expensive plans. I understand your frustation, however your comparison to SQL Server's JDBC driver which is GA to an ALPHA version of MySQL and its state of issues is not an apples-to-apples comparison. As far as I know, the issues you are having with our software are all fixed, and are available in nightly snapshots, but not a release. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAhsaEtvXNTca6JD8RAnM9AKCouV0dzDUoxj9ZBaeJc2xkTzBGCwCgvqC5 M+kjrvPBKdVxArVCJKIj4RY= =iXL6 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant problem!!!
[EMAIL PROTECTED] wrote: hi, I am having problems with mysql. 1. I downloaded mysql on to my system. 2.installed it 3. ran the server using the command prompt C:\mysql\bin\mysqld --console it gave me the results that i could start using the service. 4. i started mysql on another command prompt. what i am trying to do is i have to enter a huge genome sequence into a field. In order to do tht i have to change the global and session variables in mysql. i can change the session variables but i am not able to change the global variables. I gave the command: set global max_allowed_packet=4; it said: Access denied. You need the SUPER privilege for this operation. Then i created a database genome_db . then at the command promp i typed grant all privileges on genome_db.* to root@localhost identified by ' '; User 'root'@'localhost' by default has all privileges. i got the result as: Access denied for user: @'localhost' to database 'genome_db' dont know what to do:( confused You are connected as anonymous user. Use -u option of mysql client to specify username and connect to the MySQL server as a root: mysql -uroot -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Gripe with MySQL
Peter J Milanese said: The lack of error checking on the server side means better performance in my opinion. Does it? Or does client side error checking use server resources as well? Lets suppose that we want to validate a new message that is entered into a thread on a messageboard. Then we need to check that the thread we want to insert the message into actually exists. If we want to do that client side we need to open a transaction, query the threads table, insert the new message and commit. If we check it server side we just create a foreign key to the threads table, insert the message and if it gives a foreign key error we handle the exception. Which one would put a bigger load on the server? Naturally running an expensive regex on the input to make sure the message does not contain any cross site scripting code is something you would do on the client, but validating *relations* is something you should do in your *relational* database. I would have to agree that error checking does belong on the client side (at least from my experience), and it can be a lot more productive as a database, and not a workflow tool. As an application developer, I don't trust the employees to be able to enter data correctly. That is why I validate the input they send me. As a DBA, I don't trust application developers to be able to validate relations correctly. That is why I validate the input they send me. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BLOB data and mysql_escape_string
I'm attempting to put an image into a BLOB field. I'm using Visual Basic 6 with ADO, although I wouldn't think that's making a difference. I create a string that contains the binary representation of the file. At this point the string is 5064 characters long. I call mysql_escape_string and the string becomes 5350 characters long. That seems normal as there will be a lot of characters in the binary data that need to be escaped. After selecting it out of the database, the size of the data retrieved is 5023 bytes. Clearly this is a problem when trying to re-create the file. Any suggestions as to why? Is there a function other than mysql_escape_string that should be used with binary data? Do I have to write my own? If so, what bytes need to be escaped? I assumed just NULL, ', \ and ...but didn't bother writing one since mysql_escape_string seemed to do what I wanted. Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confused by max and group by
I think what is happening is that you are getting the max value for one field, but the first values for the other fields. Try ordering you group by: SELECT max(myindex), myval, mycat FROM `mytest` GROUP BY mycat DESC; On Apr 21, 2004, at 1:35 PM, Noah Spurrier wrote: I'm having trouble with max() and group by. It seems pretty simple. I hope someone can point out my mistake. I want to select the max index of a group. In other words, I want to find the last record added for each group. The problem I'm having is that the columns of the resulting rows are mixed with different records. I get the expected indexes returned, but the fields appear to be from another record and not the fields associated with the index. I ran this query: SELECT max(myindex), myval, mycat FROM `mytest` GROUP BY mycat; and I get the following results: +--+---+---+ | max(myindex) | myval | mycat | +--+---+---+ | 3| one | A | | 9| one | B | +--+---+---+ But I was expecting this: +--+---+---+ | max(myindex) | myval | mycat | +--+---+---+ | 3| one | A | | 9| three | B | +--+---+---+ This is my test data. CREATE TABLE `mytest`( `myindex` int(11) NOT NULL default '0', `myval` varchar(40) NOT NULL default '', `mycat` varchar(40) NOT NULL default '', PRIMARY KEY (`myindex`) ) TYPE=MyISAM; INSERT INTO `mytest` VALUES (1, 'one', 'A'); INSERT INTO `mytest` VALUES (2, 'two', 'A'); INSERT INTO `mytest` VALUES (3, 'three', 'A'); INSERT INTO `mytest` VALUES (4, 'one', 'B'); INSERT INTO `mytest` VALUES (5, 'two', 'B'); INSERT INTO `mytest` VALUES (6, 'three', 'B'); INSERT INTO `mytest` VALUES (7, 'one', 'B'); INSERT INTO `mytest` VALUES (8, 'two', 'B'); INSERT INTO `mytest` VALUES (9, 'three', 'B'); Yours, Noah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: reuse a results set
I have a form that does the following ? while ($speaker = db_fetch_object($qid_speaker)) { ? option value=? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? } ? /select $qid_speaker = db_query(SELECT first_name, last_name FROM people WHERE speaker = 'Y' ORDER BY last_name); I am using this in a form select drop down box. What I need to do is reuse this results set in another drop down box. but I can not figure out how to do this. If I use the mysql_free-result($qid_speaker) and then try to do the query again I get a mysql_fetch_object(): 12 is not a valid MYSQL result resource. TIA Scott Swaim Quality Corps, Inc.
reuse results set
I have a form that does the following ? while ($speaker = db_fetch_object($qid_speaker)) { ? option value=? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? } ? /select $qid_speaker = db_query(SELECT first_name, last_name FROM people WHERE speaker = 'Y' ORDER BY last_name); I am using this in a form select drop down box. What I need to do is reuse this results set in another drop down box. but I can not figure out how to do this. If I use the mysql_free-result($qid_speaker) and then try to do the query again I get a mysql_fetch_object(): 12 is not a valid MYSQL result resource. TIA Scott Swaim Quality Corps, Inc.
Re: Searching the Docs
I use the online docs extensively as I am still very much in learning mode with MySQL. However, I've been frustrated recently because it appears I cannot search for an EXACT string literal, which brings me back a ton of hits I don't want. For example, I'm trying to search for the dynamic system variable called convert_character_set, but it returns results with convert or set etc., when I only want to see hits for the exact string. Is there a search type I can use, or some quoting system, or anything that will allow me to search in this manner? I've got several dynamic variables that I cannot find definitions for: convert_character_set error_count slave_compressed_protocol sql_big_tables sql_low_priority_updates sql_max_join_size sql_slave_skip_counter warning_count Thanks, Lou
Escape characters
When issuing commands through the terminal (in Mac OS 10.3) to MySQL, I understand that if you surround a variable with \\` it will allow for characters such as - and space. Will it also allow for all other non-alphanumeric characters such as / and * and , etc.? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem retrieving Unicode data
On Wed, 21 Apr 2004 14:07:48 -0500, Mark Matthews wrote: I have set the default charset to utf8. When I view my variables they all show that utf8 as their value. My URL is as follows: What does the JDBC connection _itself_ show? Execute the following query on a java.sql.Statement, and tell us what comes back, as the values you have posted earlier (from a connection from the 'mysql' command-line client) are irrelevent in this situation: SHOW VARIABLES LIKE 'character%' I did this and got the following results: (I formatted them) character_set_server = utf8 character_set_system = utf8 character_set_database = utf8 character_set_client = utf8 character_set_connection = utf8 character-sets-dir = d:\program files\MySQL\share\charsets/ character_set_results = utf8 Something I have noticed and not sure if it's a problem but I went into the character-sets-dir directory. I found many xml files corresponding to character sets but I didn't find any utf8.xml. Could this be the problem or indicative of the problem? The database seems to handle utf8 just fine even without this file though. I did try the latest 3.1. It still behaved in the same manner and what was worse, it broke another page of mine. I started getting an error when I tried to display that page. It said: Unknown type '0 in column 9 of 16 in binary-encoded result set. HmmmThat was fixed in the _server_ after 4.1.1 was released, so if you're not using a compile from source that was pulled from our archives, then you will have to wait until 4.1.2 comes out. I see. I did a google on this and found others having that problem and it was driver related. http://article.gmane.org/gmane.comp.db.mysql.java/3347 You missed the rest of the thread, then, it is actually _server_ related, and was fixed for MySQL-4.1.2 (not yet released, but available as source code): http://article.gmane.org/gmane.comp.db.mysql.java/3350 Gotcha. Did you ever look and see what encoding your JSPs are set at? If it's not UTF-8, you will see this behavior, because JSTL will use the encodings of your JSPs. It is strange (but not dismisable) that it would work from stock java bean and not a JSTL tag and that this would be caused by a _driver_ bug. The JSPs are set to UTF-8. When I was using the SQLServer database if I didn't have it set then yes I got question marks displayed but when I examined the string itself it was unicode. There were no literal question marks there but unicode chars. But in this case there are literal question marks in the string so some type of conversion is taking place before I get to display it. I can try the nightly snapshots but this is bad because this project will need to be production ready and if it needs to use trial stuff like this... MySQL-4.1 is an ALPHA. The JDBC driver is an ALPHA, and you are using features that are only in the ALPHA. For this reason you are going to have to put up with using nightly snapshots and/or doing your own builds if you want the latest bug fixes. You know, I had forgotten this. When I downloaded it I saw somewhere that it said I should use this for all new development and so that's what I was doing but I'd forgotten that it was an alpha. I'm thinking that for right now for this project I should not be doing this. Even if I get the latest and get this working it's unlikely that I'll find a host that is running it. So while the programmer in me wants to get this working for my own edification I think I need to either go back to SQLServer or to MySQL 4.0. The only thing that I'd be concerned about with doing that is at this stage it'll mean a new troubles as I find out I can't do this or that in that version. So you make a good point and I think that I simply made a poor choice in terms of choosing to use 4.1 at this point and time. I still want to use MySQL for my own personal use but for this current project I think I need to wait for releases. Thanks much for you help. I guess before I go any further I need to update the database to see if the problems I'm having right now are fixed or not in the latest versions. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Escape characters
Wouldn't it be faster to simply try this than wait on people here to notice your question and write a reply? Have you checked the manual? It may answer your question too Rhino - Original Message - From: John Mistler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 4:38 PM Subject: Escape characters When issuing commands through the terminal (in Mac OS 10.3) to MySQL, I understand that if you surround a variable with \\` it will allow for characters such as - and space. Will it also allow for all other non-alphanumeric characters such as / and * and , etc.? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Searching the Docs
Lou, I normally download the html version of the manual and have a little search feature just for it. And you are correct, I've looked for a few of your examples and can't find any information on them besides you can turn them on or off basically. Not in the mood to bust out the code to figure out exactly what they do though. Sorry. Donny -Original Message- From: Lou Olsten [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 3:35 PM To: [EMAIL PROTECTED] Subject: Re: Searching the Docs I use the online docs extensively as I am still very much in learning mode with MySQL. However, I've been frustrated recently because it appears I cannot search for an EXACT string literal, which brings me back a ton of hits I don't want. For example, I'm trying to search for the dynamic system variable called convert_character_set, but it returns results with convert or set etc., when I only want to see hits for the exact string. Is there a search type I can use, or some quoting system, or anything that will allow me to search in this manner? I've got several dynamic variables that I cannot find definitions for: convert_character_set error_count slave_compressed_protocol sql_big_tables sql_low_priority_updates sql_max_join_size sql_slave_skip_counter warning_count Thanks, Lou -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem
hi, I reinstalled mysql again and did what the website said. C:\ C:\mysql\bin\mysql -u root mysql mysql DELETE FROM user WHERE Host='localhost' AND User=''; mysql FLUSH PRIVILEGES; mysql QUIT C:\ C:\mysql\bin\mysqladmin -u root password newpwd C:\ C:\mysql\bin\mysqladmin -u root -h host_name password newpwd instead of newpwd i wrote zilnoy instead of hostname i wrote zilsys then i said grant all privileges on genome_db.* to [EMAIL PROTECTED] identified by 'zilnoy'; i am getting an error like : Access denied to user: '@localhost' to database 'genome_db' what should i do??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Confused by max and group by
The problem you are running into is that you are getting the max of one field and grouping by another. But then you want to get a third field that changes within the grouping. Perhaps this might work SELECT myindex, myval, mycat FROM `mytest` GROUP BY mycat ORDER BY myindex DESC; On Apr 21, 2004, at 4:47 PM, Noah Spurrier wrote: Unfortuantely, that didn't do it. I tried both DESC and ASC. I got the same incorrect result. This is too bad. It SEEMS like it should work... -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql using big two tables in BSD
Hi, I'm new to mysql. I have two big tables ,tableA4GB and tableB1GB. both tables around 10 million rows,each. tableA has following two cols. user_id(varchar(255) PRIMARY KEY user_data1(MEDIUMTEXT) entries looks like user1 xyz user2 x1.. tableB has following cols user_data2:varchar(50) user_id(varchar(255)). tableB is indexed on (user_data2,user_id) and on user_id. there is no primary key in tableB since,it can have entries like A user1 B user1 A user2 C user2. I NEED to SELECT data from both tables as follwoing SELECT tableA.userid,tableA.user_data1 from tabelA,tableB where tableB.user_data2=myinput AND tableB.user_id=tableA.user_id It was good while the table size was small,but since the table size is big and growing,the query is becoming slow. I'm using mysql_use_result(). to get the result. Any suggestion either on client query or server tuning will be helpful. thanks = Don't worry about the world coming to an end today. It's already tomorrow in Australia. ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°¤º° Do You Yahoo ! ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø¤º°¤º° __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Conference Presentations
Does anyone know if the presentations from the User Conference are available online? Thanks dp
first LIMIT then ORDER
Is there possibility to first LIMIT and then ORDER records? Using simple query I can first ORDER and then LIMIT like that: SELECT id, name FROM table ORDER BY name LIMIT 20,10 Maybe should I use procedures ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1030: Got error 127 from table handler
Hi I have been using MySQL server version 4.0.14 for the last six months in our project and it was running just fine. We use Linux AS 3.0 (kernel version 2.4.21-4.0.1.EL #1) However, since last week we started to get the following error : ERROR 1030: Got error 127 from table handler ( please look below). This happens when we are doing some load testing on our application server and there are around 2 records in the table. We are doing only inserts and deletes from the table at the total rate of around 90 operations per second. Its has happened numerous time since the last few days. Could anyone please let me know why this is happening and how do i go about solving this. If I do repair table table name, it says that all the records are corrupted since the pointer is outside data segment. Then it deletes all the rows. However what I am looking for is a prevention of this problem instead of a cure thanks a lot bhaskar [EMAIL PROTECTED] ~]$ mysql -uccd_user -pccd_user Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 63458 to server version: 4.0.14-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use ccd_chkp_db; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select * from USER_CACHE; ERROR 1030: Got error 127 from table handler mysql desc USER_CACHE; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | info_type | tinyint(3) unsigned | | | 0 | | | user_addr | varchar(64) | | PRI | | | | domain| varchar(255) | | PRI | | | | ip_version| tinyint(3) unsigned | | | 0 | | | ip_addr | tinyblob | | | | | | port | smallint(5) unsigned | | | 0 | | | security_key_k1 | tinyblob | | | | | | security_key_k2 | tinyblob | | | | | | reg_ttl | int(10) unsigned | | | 0 | | | cseq | int(10) unsigned | | | 0 | | | call_id | varchar(255) | | | | | | timestamp | int(10) unsigned | | | 0 | | | vocoder | smallint(5) unsigned | | | 0 | | | protocol_ver | tinyint(3) unsigned | | | 0 | | | sdb_flag | tinyint(3) unsigned | | | 0 | | | qsk_ttl | smallint(5) unsigned | | | 0 | | | time_zone | smallint(5) unsigned | | | 0 | | | home_carrier_id | tinyint(3) unsigned | | | 0 | | | current_carrier_id| tinyint(3) unsigned | | | 0 | | | region_id | tinyint(3) unsigned | | | 0 | | | omd | varchar(38) | | | | | | PNOffset | smallint(5) unsigned | | | 0 | | | sid | smallint(5) unsigned | | | 0 | | | nid | smallint(5) unsigned | | | 0 | | | restriction_size | int(10) unsigned | | | 0 | | | restrictions | mediumblob | YES | | NULL| | | request_uri_user_addr | varchar(16) | YES | | NULL| | | request_uri_domain| varchar(253) | YES | | NULL| | | subscriberId | varchar(64) | | | | | +---+--+--+-+-+---+ 29 rows in set (0.04 sec)
RE: Confused by max and group by
Well, in traditional SQL you'd use a sub-query for this type of operation. In MySQL 4.1+ you could do this: SELECT t1.myindex, t1.myval, t1.mycat FROM mytest t1 WHERE myindex = (SELECT max(t2.myindex) FROM mytest t2 WHERE t2.mycat=t1.mycat); once again, just illustrating the theory, it may not work as is. SQL is really *meant* to have sub-queries, so the temp table solution is just a work around until sub-queries make it into the production version of MySQL. At least, that's how I understand it anyway. Chris -Original Message- From: Noah Spurrier [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 1:46 PM To: Chris Cc: [EMAIL PROTECTED] Subject: Re: Confused by max and group by This seems bizarre. Although I am the SQL neophyte and it is perhaps not my right to whine about the mysteries of SQL, but this seem very surprising and nonintuitive. In general, it seems like there is no reason to select multiple fields if one of the fields uses the max() function because the other resulting fields are meaningless (in that they are unrelated to the field returned by max()). Your temp table solution makes sense. I'll use that. Yours, Noah On Wednesday 21 April 2004 11:07 am, Chris wrote: You aren't making any mistakes, it's just not possible to do. You can't rely on which row MySQL will return when using a GROUP BY clause. The standard method would be to do something like this: CREATE TEMPORARY TABLE mytemptable SELECT max(myindex) as myindex, mycat FROM `mytest` GROUP BY mycat; then SELECT myval, myotherrows, mycat FROM `mytemptable` LEFT JOIN mytest USING(myindex,mycat) note: I haven't tested the above code, it's just an example of the theory Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
first LIMIT then ORDER
Is there possibility to first LIMIT and then ORDER records? Using simple query I can first ORDER and then LIMIT like that: SELECT id, name FROM table ORDER BY name LIMIT 20,10 Maybe should I use procedures ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re-using a results set
I have a form that does the following ? while ($speaker = mysql_fetch_object($qid_speaker)) { ? option value=? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? } ? /select $qid_speaker = db_query(SELECT first_name, last_name FROM people WHERE speaker = 'Y' ORDER BY last_name); I am using this in a form select drop down box. What I need to do is reuse this results set in another drop down box. but I can not figure out how to do this. If I use the mysql_free-result($qid_speaker) and then try to do the query again I get a mysql_fetch_object(): 12 is not a valid MYSQL result resource. TIA Scott Swaim Quality Corps, Inc.
reuse a results set
I have a form that does the following ? while ($speaker = db_fetch_object($qid_speaker)) { ? option value=? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? } ? /select $qid_speaker = db_query(SELECT first_name, last_name FROM people WHERE speaker = 'Y' ORDER BY last_name); I am using this in a form select drop down box. What I need to do is reuse this results set in another drop down box. but I can not figure out how to do this. If I use the mysql_free-result($qid_speaker) and then try to do the query again I get a mysql_fetch_object(): 12 is not a valid MYSQL result resource. TIA Scott Swaim Quality Corps, Inc.
sorry for the multiple post. my error
RE: re-using a results set
This is a PHP question, not MySQL. http://www.php.net/mysql_data_seek -Original Message- From: Scott Swaim [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 2:47 PM To: Mysql Subject: re-using a results set I have a form that does the following ? while ($speaker = mysql_fetch_object($qid_speaker)) { ? option value=? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? } ? /select $qid_speaker = db_query(SELECT first_name, last_name FROM people WHERE speaker = 'Y' ORDER BY last_name); I am using this in a form select drop down box. What I need to do is reuse this results set in another drop down box. but I can not figure out how to do this. If I use the mysql_free-result($qid_speaker) and then try to do the query again I get a mysql_fetch_object(): 12 is not a valid MYSQL result resource. TIA Scott Swaim Quality Corps, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: re-using a results set
From: Scott Swaim [mailto:[EMAIL PROTECTED] I have a form that does the following ? while ($speaker = mysql_fetch_object($qid_speaker)) { ? option value=? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? } ? /select $qid_speaker = db_query(SELECT first_name, last_name FROM people WHERE speaker = 'Y' ORDER BY last_name); I am using this in a form select drop down box. What I need to do is reuse this results set in another drop down box. but I can not figure out how to do this. If I use the mysql_free-result($qid_speaker) and then try to do the query again I get a mysql_fetch_object(): 12 is not a valid MYSQL result resource. You've now posted this four times today. Was that on purpose? I can only assume so, as some people have absolutely no sense of netiquette whatsoever. Why, you ask, has no one replied with the infinite wisdom to solve your problem? I, for one, haven't because your original email is at least vaguely unintelligible. Have I asked for more information so that I might be more helpful? No, I've been busy. Posting over and over and over again, though, seems to have worked, so readers, take note -- if at first you don't succeed, try, try again until you annoy someone into listening. *sigh* That said, sir, could you possibly provide more details for your situation? Your code is a bit jumbled. We have no idea what pv() does, for instance. Nor db_query(). These are not standard PHP functions, so you'll have to forgive us if we're all a bit clueless and unwilling to help. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: re-using a results set
Thank you Chris for that answer. I did not know if it was a mysql problem or php problem. Again I apologize (especially to Mike Johsnon) for the multiple request. I was getting a return error on the list and I thought it was a configuration error on my part. I did not mean to upset anyone. Thank you again for the answer and I will try to have better netiquette in the future. - Original Message - From: Chris [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 4:54 PM Subject: RE: re-using a results set This is a PHP question, not MySQL. http://www.php.net/mysql_data_seek -Original Message- From: Scott Swaim [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 2:47 PM To: Mysql Subject: re-using a results set I have a form that does the following ? while ($speaker = mysql_fetch_object($qid_speaker)) { ? option value=? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? } ? /select $qid_speaker = db_query(SELECT first_name, last_name FROM people WHERE speaker = 'Y' ORDER BY last_name); I am using this in a form select drop down box. What I need to do is reuse this results set in another drop down box. but I can not figure out how to do this. If I use the mysql_free-result($qid_speaker) and then try to do the query again I get a mysql_fetch_object(): 12 is not a valid MYSQL result resource. TIA Scott Swaim Quality Corps, Inc. -- 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 4.0 and Unicode
Ok, what is the recommended way of handling Unicode data with version 4.0. I believe I read that it doesn't support utf8 so how do people with version 4.0 deal with it? Do they store is as a binary object perhaps? Looking for techniques and ideas so I'll be able to see how much work is involved working with 4.0. Also foreign keys. Do the innob tables of 4.0 support foreign keys? -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Stormblade (Shaolin Code Warrior) Software Developer (15+ Years Programming exp.) My System: http://www.anandtech.com/mysystemrig.html?rigid=1683 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reuse a results set
I generally set an array if I'm going to reuse a result. In your example, I would do something like the following: ? $i=0 ? ? while ($speaker = db_fetch_object($qid_speaker)) { $speakerName[i]=sprintf(%s %s,$speaker-first_name, $speaker-last_name); i++; ? option value=? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? } ? /select Then the next time I wanted a drop box with the same result set: ? $j=0; while ($j$i) { printf(option value='%s' %s,$speakerName[j], $speakerName[j]); } ? I hope this helps... Pat... Patrick Sherrill CocoNet Corporation SW Florida's 1st ISP 825 SE 47th Terrace Cape Coral, FL 33904 - Original Message - From: Scott Swaim [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 11:34 AM Subject: reuse a results set I have a form that does the following ? while ($speaker = db_fetch_object($qid_speaker)) { ? option value=? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? pv($speaker-first_name) ? ? pv($speaker-last_name) ? ? } ? /select $qid_speaker = db_query(SELECT first_name, last_name FROM people WHERE speaker = 'Y' ORDER BY last_name); I am using this in a form select drop down box. What I need to do is reuse this results set in another drop down box. but I can not figure out how to do this. If I use the mysql_free-result($qid_speaker) and then try to do the query again I get a mysql_fetch_object(): 12 is not a valid MYSQL result resource. TIA Scott Swaim Quality Corps, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Conference Presentations
Yes, I'm anxious to get my hands on some of the presentations myself. I think I saw a couple of brief mentions of a url but it wasn't written down anywhere. I'd also like to see some of the pictures I saw David snapping everywhere. :) --- David Perron [EMAIL PROTECTED] wrote: Does anyone know if the presentations from the User Conference are available online? Thanks dp = Robert Reed 512-869-0063 home 512-818-2460 cell __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Conference Presentations
You don't mean at the Discovery Cove near the Jimmy Buffet cover band do you? Speaking of which, Lars... there's an English expression that uses the word Cluster...but has nothing to do with databases... :) (Kidding! Just kidding!) Robert Reed wrote: Yes, I'm anxious to get my hands on some of the presentations myself. I think I saw a couple of brief mentions of a url but it wasn't written down anywhere. I'd also like to see some of the pictures I saw David snapping everywhere. :) --- David Perron [EMAIL PROTECTED] wrote: Does anyone know if the presentations from the User Conference are available online? Thanks dp = Robert Reed 512-869-0063 home 512-818-2460 cell __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting tables to innoDB
Hello Mysqlers, I have a few small DB's (less than 10,000 entries per table) that track computer inventory in a mid-sized organization. Currently, all of the tables are MyISAM. Is there any compelling reason to convert these to InnoDB? We're not having any performance or size issues... everything works great right now. I'm just hearing how InnoDB is the only way to go today, any tips on whether or not to switch? Thanks, Brad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Syntax problems in MySQL v. 4.1.1-alpha-standard-log
Apologies if this is an easy one, I'm stumped! I'm having some trouble with syntax running MySQL from the command line. I'm running MySQL 4.1.1-alpha from the OS X standard installer package on a G5 running panther. I have logged into MySQL as the root user, providing the correct password, and I wish to see a list of the databases I have available, so I enter the following command: mysql show databases; I get back the following: ERROR: No Query Specified If I try again, adding a 'Like' clause, things clear up: mysql show databases like '%'; ++ | Database (%) | ++ | cme_admin | | cme_course_administration | | cme_course_finances| | cme_course_info| | cme_faculty_info | | cme_public | | cme_user_data | | mysql | | test_course_administration | | test_course_finances | | test_course_info | | test_faculty_info | ++ This same type of problem is cropping up in a number of different settings. For example, I can use the test_course_info database: mysql use test_course_info; Database changed But trying to show the tables in this database suffers from another syntax problem: mysql show tables from test_course_info; ERROR 12 (HY000): Can't read dir of './test_course_/' (Errcode: 2) It seems that the full name of my database is not being read for some reason. So I try just to show tables, after all, I am using the proper database, right? mysql show tables; ERROR: No query specified So what if I try the same as before, and use wildcards? mysql show tables like 'tbl%'; +---+ | Tables_in_test_course_info (tbl%) | +---+ | tblcoursecomments | | tblcoursedaterepeatexceptions | | tblcoursedaterepeats | | tblcoursedates| | tblcoursedatesubgroups| | tblcoursefaculty | | tblcourselocations| | tblcourses| | tblcoursesponsors | | tblcoursetypes| | tbldaterepeatperiod1s | | tbldaterepeatperiod2s | | tbldays | | tblensessions | | tbllocations | | tblregfromaccess | | tblsponsors | +---+ 17 rows in set (0.00 sec) Similar problems are showing up with things as simple as a select query: mysql select * from tblcourses; ERROR 1096 (HY000): No tables used BUT I JUST LOOKED There is in fact a table called tblcourses in the databases. What am I doing wrong here? So, lets try to see the columns in this table: mysql show columns from tblCourses; ERROR: No query specified mysql show columns from tblCourses like '%'; **Big list of columns deleted to save space** One of the colums is called course_id (it's the primary key, in fact). Lets try to select that column: mysql select course_id from tblCourses; ERROR 1054 (42S22): Unknown column 'course_' in 'field list' Once again, we seem to be missing everything after the final underscore in the column name. I'm stumped as to why these queries are not working. Does anyone have suggestions for me? Thanks, Cris Cris Ewing CME and Telehealth Web Services University of Washington School of Medicine Work Phone: (206) 685-9116 Home Phone: (206) 365-3413 E-mail: [EMAIL PROTECTED] *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Uninstall mysql
Hi, I tried to uninstall mysql from win Xp.however it did not remove everything completely.I tried to remove the files manually but specifically it does not let me delete mysqld.exe.it says access denied. Can anyone help me?I would to reinstall mysql. S. __ Do you Yahoo!? Yahoo! Photos: High-quality 4x6 digital prints for 25¢ http://photos.yahoo.com/ph/print_splash -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems adding table to Crystal Reports using ODBC Connection
The only other thing I can think of off the top of my head is Crystal's very poor handling of prefix and postfix data in sub-reports. If you are using sub-reports make sure a field in the table in question is at least referenced on the main report. Sorry I couldn't be more help. Pat... - Original Message - From: Valère Palhoriès [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 6:50 PM Subject: Re: Problems adding table to Crystal Reports using ODBC Connection Patrick, Yes I am. If I create a Linked Table from Access 2003 I can see the table. Additionally, I drop the Crystal Query into SQLyog and execute it and it works fine. Of course I modify the SQL Query slightly to be compliant with SQLYog! -- Val From: Patrick [EMAIL PROTECTED] To: Valère Palhoriès [EMAIL PROTECTED] Subject: Re: Problems adding table to Crystal Reports using ODBC Connection Date: Wed, 21 Apr 2004 18:48:27 -0400 Are you able to see the table with any other ODBC products (like Access?). Pat... - Original Message - From: Valère Palhoriès [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 6:45 PM Subject: Re: Problems adding table to Crystal Reports using ODBC Connection Patrick, Yes, I have tried the Verify Database in Crystal and it comes back with Database is up to date. I've also tried to RESET the Query from the View SQL Query and it's correct! I'm at a loss! -- Val From: Patrick [EMAIL PROTECTED] To: Valère Palhoriès [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Problems adding table to Crystal Reports using ODBC Connection Date: Wed, 21 Apr 2004 18:42:41 -0400 Have you tried 'Verify Database' in Crystal? Pat... Patrick Sherrill CocoNet Corporation SW Florida's 1st ISP - Original Message - From: Valère Palhoriès [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 21, 2004 5:26 PM Subject: Problems adding table to Crystal Reports using ODBC Connection Hello, I have reports designed in Crystal Reports 8.5 that are connected to MySql DB via an ODBC Connection. The reports run great and are accessed without a problem through our VB application. We have recently added a table to our database that needs to be added in addition to already existing tables on some of the reports. I Add the table, update the SQL Query and insert the field on the report. When I try to preview the report I get the following error message: ODBC error: [MySql][ODBC 3.51 Driver][mysqld-4.0.16-nt]Unknown table 'tablename' in field list. I tried the same thing with several other reports and get the same results. The last table I add always gives the same message. I've tried re-creating the report from scratch and it works fine when I do. I tried to add another table onto the report I created from scratch and get the same error message. Anyone know what's going on here? I'm trying to move towards the TTX concept but that will take me a couple of weeks. In the mean time, I have to get this problem resolved! Any help or light you can shed on this matter would be so much appreciated. Thanks -- Val _ Lose those love handles! MSN Fitness shows you two moves to slim your waist. http://fitness.msn.com/articles/feeds/article.aspx?dept=exercisearticle=et _pv_030104_lovehandles -- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ MSN Toolbar provides one-click access to Hotmail from any Web page - FREE download! http://toolbar.msn.com/go/onm00200413ave/direct/01/ _ Watch LIVE baseball games on your computer with MLB.TV, included with MSN Premium! http://join.msn.com/?page=features/mlbpgmarket=en-us/go/onm00200439ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bajar mysql
De donde puedo bajar la ultima versión de mysql.