Mysql 4.0.9 and Jconnector 3.0.5
Hello. Iam porting an application that previously run on InstantDB to Mysql. This application makes use of transaction and isolation level(Read Committed). I previously tried using mysql 3.23.54 max with the old mm.mysql driver but encountered a problem in setting the Isolation level. I got hold of the new release of Mysql ver 4.0.9 and downloaded Connector J 3.0.5 for the driver. I got pass the IsolationLevel exception I had before.. but somehow.. in a part of the program where I had to make a subquery inside a delete statement.. I ecountered an Exception. I have this for my code: String col = 4028809b:60a386:f302bed65b:-7df1 sql = DELETE FROM table1 WHERE timer IN (SELECT id FROM table2 WHERE col = ?); PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1, col); int rows = ps.executeUpdate(); Exception was... java.sql.SQLException: Syntax error or access violation, message from server: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM table2 WHERE col = '4028809b:60 Somehow.. the string was truncated. I tried displaying the string prior to the sql statement and it return the correct value. I think It has to do with the driver's implemetation of ? and preparedStatement.setString() coz when I tried not using ? and setString it passed thru it correctly. However, there are numerous sql statement that makes use of ? and setString. So, changing them all is not a good idea. Ist there a workaround for this? Can someone shed light regarding my error? Is this a bug in the driver? or am im just misled. YOur insight will be most welcome. TIA _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Properly shutting down mysql
Hi. On Mon 2003-01-27 at 16:31:48 -0800, [EMAIL PROTECTED] wrote: If I manually shut down mysql, I usaully use the mysqladmin shutdown command. However, I noticed that the red hat way of stopping mysql in its init scripts is by issuing a kill command to the pid. U, is this wise? What about if the tables aren't written to disk? kill only sends the process a request to terminate (in opposite to kill -9 which kills it instantly). MySQL catches this, therefore it is usually save to use kill this way. But mysqladmin is recommended, because in opposite to kill it also works - if you have no way to become root or the user MySQL is running under, but a database account with shutdown privilege, - you are working from a remote computer or - MySQL is running under Windows. Whereas kill has the advantage that you don't have to - have a database account handy or - know about mysqladmin (think admin vs. DBA: every admin knows kill and can expect that any reasonable program written to run as daemon will react gracefully on kill) Isn't the proper way to issue a mysqladmin shutdown? As you see, both kill and mysqladmin shutdown have their advantages. So RedHat probably choses the way they do because they want to avoid the hassle of assuring to have a database account with shutdown privilege and password somewhere. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can I set a default value to a function?
On Mon, Jan 27, 2003 at 10:04:07PM -0500, Carlin Anderson wrote: I am running mysql 3.23, and want to know if I can set the default value of a column to to_days(now()), or the equivelant. I have tried this, and the default gets translated to 0. You cannot. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 0 days, processed 6,057,449 queries (306/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: smallest dumpfiles in Windows in one step?
On Mon 2003-01-27 at 21:45:54 -0500, [EMAIL PROTECTED] wrote: Stefan -- ...and then Stefan Hinz, iConnect (Berlin) said... % ... % backing up my MySQL databases on a Win2K box at home to a Linux box in ... % hand, I have only 128 MBit upstream from my Win machine, and maybe you % will have a faster network connection. I use: % % c:\mysql\bin mysqldump --all-databases --add-drop-table mysql -h % myoffice -u username -p is wrong, of course, because it will try to direct the output into a file named mysql. | is what is used to pipe between programs. % This will take some time, and it's most probably not the smartest thing % one could do, but it works nicely. Ouch! You're mostly, or perhaps even only, dealing with simple text, so why not take a moment to compress it before you send it over your tiny drinking straw of a 'net pipeline... mysqldump ... | gzip -9 | ssh myoffice gunzip | mysql -u username -p pass That wouldn't work, would it? I am quite sure the pipes are all interpreted by your local shell, so that would effectively mean to send the data via ssh to the remote machine for unzipping and then processing the output that got sent back via a local mysql. ;) Surely you have the cygwin toolkit and ssh for Win, right? And you could use build-in compression of ssh. So presuming a working ssh on the Microsoft Windows machine, something like this hopefully works: mysqldump ... | ssh -C myoffice mysql -u username -p pass % HTH, Ditto :-) ... :-) Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
my.ini
Hi, This is my 'my.ini' file where I specified 'language=spanish' but the sql errors appear in English yet. I don't see another line after this that changes the value. Can somebody help me? # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 language=spanish #socket=MySQL skip-locking set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M server-id = 1 # Uncomment the following if you want to log updates #log-bin # Uncomment the following rows if you move the MySQL distribution to another # location basedir = c:/mysql/ datadir = c:/mysql/data/ # Uncomment the following if you are NOT using BDB tables #skip-bdb # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=4M #set-variable = bdb_max_lock=1 # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:400M innodb_data_home_dir = c:\ibdata innodb_log_group_home_dir = c:\iblogs innodb_log_arch_dir = c:\iblogs set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-max-nt.exe ___ Yahoo! Móviles Personaliza tu móvil con tu logo y melodía favorito en http://moviles.yahoo.es - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql 4.0.9 and Jconnector 3.0.5
avaj avaj wrote: Hello. Iam porting an application that previously run on InstantDB to Mysql. This application makes use of transaction and isolation level(Read Committed). I previously tried using mysql 3.23.54 max with the old mm.mysql driver but encountered a problem in setting the Isolation level. I got hold of the new release of Mysql ver 4.0.9 and downloaded Connector J 3.0.5 for the driver. I got pass the IsolationLevel exception I had before.. but somehow.. in a part of the program where I had to make a subquery inside a delete statement.. I ecountered an Exception. I have this for my code: String col = 4028809b:60a386:f302bed65b:-7df1 sql = DELETE FROM table1 WHERE timer IN (SELECT id FROM table2 WHERE col = ?); PreparedStatement ps = connection.prepareStatement(sql); ps.setString(1, col); int rows = ps.executeUpdate(); Exception was... java.sql.SQLException: Syntax error or access violation, message from server: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM table2 WHERE col = '4028809b:60 Somehow.. the string was truncated. I tried displaying the string prior to the sql statement and it return the correct value. I think It has to do with the driver's implemetation of ? and preparedStatement.setString() coz when I tried not using ? and setString it passed thru it correctly. However, there are numerous sql statement that makes use of ? and setString. So, changing them all is not a good idea. Ist there a workaround for this? Can someone shed light regarding my error? Is this a bug in the driver? or am im just misled. YOur insight will be most welcome. MySQL don't support subqueries, as far as I know. I think that subquery support is planned for to 4.1.x series... Until then I think you will have to rewrite your code. Cheers, Pål Arne -- +--+ Pål Arne Hoff | | Product Development Manager | | rampco AS |rampco| Tlf: 22 11 45 83 Fax: 22 11 45 81 +--+ http://www.rampco.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL error (Errcode: 13)
Dhanashri, ALTER TABLE `timesheet` ADD `phase_desc` VARCHAR( 50 ) Error on rename of '.\etime\timesheet.MYI' to '.\etime\#sql2-64c-19.MYI' (Errcode: 13) I had the same kind of problem with ALTER TABLE and MySQL 4.0.5 under Win2K. Errorcode 13 means it's a permission problem. I never solved this problem, but it disappeared an never returned after I upgraded to 4.0.5. I you do not want to upgrade to version 4 (for whatever reason, as it runs stable as rock), you might want to use the workaround I used: CREATE TABLE timesheetCopy SELECT * FROM timesheet; DROP TABLE timesheet; RENAME TABLE timesheetCopy TO timesheet; ALTER TABLE timesheet ADD phase_desc VARCHAR(50); For some strange reason, I never encountered permission errors with copied tables, only with the original ones. My phpMySQL version is 3.23.55-nt Well, that's rather your MySQL version. See above what I said about upgrading. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Dhanashri Peramanu [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, January 27, 2003 10:25 PM Subject: MySQL error (Errcode: 13) Anybody have an idea about the following error (Errcode: 13)? I am simply trying to add one more field in the table timesheet. My phpMySQL version is 3.23.55-nt Thanks. -Dhanashri Peramanu -- -- -- -- -- Error SQL-query : ALTER TABLE `timesheet` ADD `phase_desc` VARCHAR( 50 ) MySQL said: Error on rename of '.\etime\timesheet.MYI' to '.\etime\#sql2-64c-19.MYI' (Errcode: 13) -- -- -- -- - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: question about primary key
On Monday 27 January 2003 10:27, Vicente Valero wrote: I want to create a table with a numeric primary key as index in my sql. I want this index will be automatic. I've tried an auto_increment column, but if insert a non-correlative value then all new entries continue from this non-correlative value (1,2,3,4,1026,1027,...). It is possible use the first value not-used in the column automatically (1,2,3,1026,4,5,1000,6,7,...)? No, you can't do this automatically. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Can I set a default value to a function?
On Tuesday 28 January 2003 05:04, Carlin Anderson wrote: I am running mysql 3.23, and want to know if I can set the default value of a column to to_days(now()), or the equivelant. I have tried this, and the default gets translated to 0. Nope. Default column value must be constant. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
TARGETED UNIQUE SEARCH ENGINE TRAFFIC
WE DELIVER TARGETED UNIQUE SEARCH ENGINE TRAFFIC TO YOUR WEB SITE BOTTOM LINE IF YOUR POTENTIAL CUSTOMERS CAN'T FIND YOUR WEB SITE YOU JUST LOST MONEY! IT'S THAT SIMPLE! Improving search engine TRAFFIC means: MORE HITS MORE BUSINESS MORE SUCCESS The most important advertising dollar spent should be for targeted search engine TRAFFIC! 1. Approximately 84% of all Internet users start with a search engine query. 2. Anyone who comes to your site from a major search engine is 100 times more likely to become a customer because they were specifically looking for your product, goods or services. 3. Search engine traffic gives you substantially more for your advertising dollar than banners or anything else. Moreover, having a Banner Ad on a web site doesn't mean your Ad was seen or even targeted to your market. Can't Get Your Companies Web Site Indexed by the Search Engines? Unfortunately, this is all too common of a Problem. You're not the only one frustrated about the length of time it takes to be listed, or all the pitfalls involved. It takes anywhere from 2 days to as much as 3 months to be listed on all the search engines. WHEN DO YOU SUBMIT? Engines at any time delay their indexing for maintenance and many other reasons. We Know exactly how each search engine works, and we know when and where to submit and what to submit. Search engines are changing daily and we study them each day. Your competitors ARE @ the mercy of OUR Marketing TEAM. Over 7 Years in the search engine / traffic wars and We have Masters words like: MP3 - BOOKS - WEB SITE HOSTING - MARKETING - - FREE WEB SITES - CASINO - - CASINO REVIEWS - BALLS - LOGOS - ART - ATTORNEY'S - NEW CAR PARTS - OLD CAR PART - - NETWORK MARKETING - WATER FILTERS - - SCALES - AND THE LIST GOES ON FOR EVER - - If you've submitted your site and come to find no listing, what do you do now? Contact: THE TRAFFIC TECHNICIANS. You need US in your corner, WE will take CONTROL of the submission cycle of your domain and add your site to places you never heard of and yet millions go there. We offer three price plans: These Prices are good for a limited time! All Marketing plans come with a free web site review. This web site review tells you what's wrong, missing or out of place On your website. This review is a must when reaching for top Ranking in the top Search Engines and Online Directories. DON'T delay act NOW, Today!!! People are looking for your company right now!!! WE WILL PUT YOUR WEB SITE IN FRONT OF THEM!! We increase your site's exposure and get your domain more traffic! WE submit your domain to 290 (two hundred ninety) of the top Search Engines and Online Directories! Plus a hole lot more. . . . . . Here are the Top places we submit your web site to: Top 190 General Engines Top 100 Online Directories Top 1,499 Free For All Links Top 9 News Media Top 4 Hot Sites Top Nine Awards Sites Plus these Award Sites (they change monthly) 3 Categories: Personal, Business, General (44 total) Top 7 Online Malls Top Miscellaneous Searches and When ANY New places to market come online we submit your site to them also at no charge. It's simple - WE get you indexed in more places and drive more traffic to your domain! It's easy to get started!! Just fax or mail the following to: FAX: 1- (602) 392-8288. US MAIL: Cyber Co 9163 W Union Hills Dr #105-14 Peoria, Az 85382 WE WILL CONTACT YOU IN 2 BUSINESS DAYS AND WE DO NOT START THE WORK UNTIL WE SPEAK WITH YOU!! -- Order Form -- PAYMENT TYPES: CHECK - CREDIT CARD - MONEY ORDER All orders must be completely filled out! Company Name: Address: City: State: Zip: Contact Name: Contact Telephone: Contact Fax: Contact E-mail Address: Web site Address: 3 to 5 of your Top Keywords Only: Questions: Check the Marketing Plan you want below: Market plan #1 448 US DOLLARS, covering 12 months of our submission service with 2 hours free Consultation to improve website ready-ness. Plus a website and keyword review. Market plan #2 177 US DOLLARS this covers 3 months of our submission service, Plus a website and keyword review. Market plan #3 89 US DOLLARS this covers 1 months of our submission service. Credit Card #. Exp: date Name On Card. Address Zip On Card: By Signing Dating Here: Date:___ I understand that I am order the marketing plan check above and circled here: Plan # 1 2 3 for the amount of $___.00. AND I UNDERSTAND Cyber Co Tech WILL CONTACT ME IN 2 BUSINESS DAYS AND NO WORK WILL START UNTIL THEY SPEAK WITH THE CONTACT NAME LISTED ABOVE!! O-*-p-*-t - O-*-u-*-t send us an email 2 [EMAIL PROTECTED]?subject=SEnot Thank You [^(PO1:KJ)_8J7BJK9^:}H*TG0BK5NKIYs5] - Before posting, please check: http://www.mysql.com/manual.php
Real size of disc space used for database?
Hi All! I have strange problem. DB MySQL 3.23.55, MyISAM table about 70 mln. record. Index file approx 50 Mb. I have delete about 40 mln. records and run optimize table. show table status get me size for this table approx 300Mb. But file on harddisk approx 1Gb. Does MySQL don't waste unused space from datafile? Dump for this table ~ 2GB :(( MAybe try delete table and restore it from dump? WBW, Stas. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: question about primary key
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Vincente, et al -- ...and then Vicente Valero said... % % Hello, I need help. Hi! That's why the list is here :-) % % I want to create a table with a numeric primary key as index in my sql. I Good. % want this % index will be automatic. I've tried an auto_increment column, but if insert Also good. % a non-correlative value then all new entries continue from this % non-correlative value (1,2,3,4,1026,1027,...). It is possible use the first Aha! There's your problem. Don't specify values into an auto_increment column; let the database handle it itself. If you need to track some other sort of id, like 1026 or 1000, then put that in another column (which might be empty if there's no value to track). Don't overload the primary key, which should simply be a unique row identifier, by asking it to hold row data as well. % value not-used in the column automatically (1,2,3,1026,4,5,1000,6,7,...)? Nope. % % Thank you, % Vicente HTH HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+NnNRGb7uCXufRwARApd7AJ9bF2tJJZgcS5WIw5Xxj03iVcXthACg4CDc QUDmZnRb8FcXWaJrUB5Xe84= =KQeg -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: smallest dumpfiles in Windows in one step?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Benjamin, et al -- ...and then Benjamin Pflugmann said... % % On Mon 2003-01-27 at 21:45:54 -0500, [EMAIL PROTECTED] wrote: % % ...and then Stefan Hinz, iConnect (Berlin) said... % % ... % % c:\mysql\bin mysqldump --all-databases --add-drop-table mysql -h % % myoffice -u username -p % % is wrong, of course, because it will try to direct the output into % a file named mysql. | is what is used to pipe between programs. Agreed. I wasn't going to mess with that side detail, though; I figured that if he were actually doing this he couldn't have that character there, and if it were just theoretical then it wasn't important anyway :-) % ... % why not take a moment to compress it before you send it over your tiny % drinking straw of a 'net pipeline... % %mysqldump ... | gzip -9 | ssh myoffice gunzip | mysql -u username -p pass % % That wouldn't work, would it? I am quite sure the pipes are all % interpreted by your local shell, so that would effectively mean to Oh, you're quite right. Slap some quotes around it like ... gunzip ... pass and we're in much better shape. % send the data via ssh to the remote machine for unzipping and then % processing the output that got sent back via a local mysql. ;) But it's *good* to run the data through that remote gunzip filter. Besides, think of the MRTG output ;-) % % Surely you have the cygwin toolkit and ssh for Win, right? % % And you could use build-in compression of ssh. So presuming a working % ssh on the Microsoft Windows machine, something like this hopefully works: % % mysqldump ... | ssh -C myoffice mysql -u username -p pass I haven't played with ssh compression to know how well it works (and I should probably have used bzip2 in my example to squeeze out any extra little bit), and I *have* seen problems with ssh installs that do not compress, but I agree that that looks right (and more elegant :-) to me. % % % HTH, % % Ditto :-) % % ... :-) *grin* % % % Benjamin. % % -- % [EMAIL PROTECTED] HTH HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE+NnYJGb7uCXufRwARAkAEAKC2sBP64ZY589lM5TP0MM5G8GM5wwCg59l5 qdMX/g+LwfnraPdedAsWpJg= =1Xc7 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: my.ini
Vicente, language=spanish You could try language=c:/mysql/share/spanish. With my 4.0.7 on Win2K, both works. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Vicente Valero [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 8:59 AM Subject: my.ini Hi, This is my 'my.ini' file where I specified 'language=spanish' but the sql errors appear in English yet. I don't see another line after this that changes the value. Can somebody help me? # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 language=spanish #socket=MySQL skip-locking set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M server-id = 1 # Uncomment the following if you want to log updates #log-bin # Uncomment the following rows if you move the MySQL distribution to another # location basedir = c:/mysql/ datadir = c:/mysql/data/ # Uncomment the following if you are NOT using BDB tables #skip-bdb # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=4M #set-variable = bdb_max_lock=1 # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:400M innodb_data_home_dir = c:\ibdata innodb_log_group_home_dir = c:\iblogs innodb_log_arch_dir = c:\iblogs set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-max-nt.exe ___ Yahoo! Móviles Personaliza tu móvil con tu logo y melodía favorito en http://moviles.yahoo.es - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
3.23 thread error - help/advice please
I tweaked my.cnf on a server. Changed wait_timeout from 80 to 75 and increased key_buffer to 1280M from 1024M max_connections is set to 500 Max_used_connections was reported at 370 (from 'mysqladmin variables') thread_cache is set to 48 I ran 'mysqladmin flush-hosts' as the mysql root user and got the following output: /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug' Problem seems to have disappeared once I reverted back to my old settings (and I don't get the regular [every 10min] php pconnect errors). Max_used_connections is now at 439. System is MySQL 3.23.51 built from source, Rh 7.3, custom 2.4.19 kernel. What I'm unsure about is the errors from php pconnect and the inability to flush hosts when I hadn't hit any connection limits. I have over 2GB RAM free (as reported by 'top') Any ideas/suggestions? _ Chat online in real time with MSN Messenger http://messenger.msn.co.uk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with SELECT, JOIN and WHERE query
Hi, I got a difficult problem with a SELECT query: In my application there are two tables, one for all users (user_profile) one for a user-in-team relation. I want to know, which user is still not in a speciffic team. Users can be in more than one team, but we don't care about that. Example: user_profile: +-+ | user_id | +-+ |1000 | |1001 | |1002 | |1003 | |1004 | |1005 | |1006 | |... | +-+ team_member: +-+-+ | team_id | user_id | +-+-+ | 1 |1000 | | 1 |1004 | | 1 |1005 | | 2 |1006 | | 2 |1003 | | 2 |1000 | | 2 |1001 | | 2 |1005 | | 3 |1001 | | 3 |1005 | | 3 |1002 | | 3 |1003 | | ... |... | +-+-+ Who is not in team no. 2? Result: +-+ |1002 | |1004 | +-+ Thanks for any hint Kind regards Heiko Mundle -- +++ GMX - Mail, Messaging more http://www.gmx.net +++ NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT UPDATE
Hi everybody. I got a really weird behaviour from Mysql, while doing some select and update queries. I made a select, something like: SELECT Something FROM WHERE and the result was not empty. But when I tried to make an update: UPDATE..WHERE , with THE SAME WHERE clause, result was empty. I'm sure that it's not reguarding permissions, and rows were not updated yet, that is UPDATE returned: Matching rows = 0 Updated rows = 0. A MATCH() search was used in the queries. Does anybody have ideas. Thanks Daniele - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql 4.0.9 and Jconnector 3.0.5
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 avaj avaj wrote: [snip] java.sql.SQLException: Syntax error or access violation, message from server: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM table2 WHERE col = '4028809b:60 Somehow.. the string was truncated. I tried displaying the string prior to the sql statement and it return the correct value. I think It has to do with the driver's implemetation of ? and preparedStatement.setString() coz when I tried not using ? and setString it passed thru it correctly. However, there are numerous sql statement that makes use of ? and setString. So, changing them all is not a good idea. MySQL doesn't always return the complete statement during error messages, only the part that is relevant. MySQL 4.0.9 does not support subselects (4.1 does), so the parser is complaining about the SELECT in your IN clause, not the value you are setting. -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+NpH7tvXNTca6JD8RArGqAJ9h4sQB9oShVfz/URrcp5ZZRDzVFgCgxJjR Z8jTv+pTEQ9HmlBQ7Wj8Oxw= =UHAm -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem starting MySQL with chroot option
When I try to start the server with the following options mysqld --chroot=/servers/mysql/ --user=mysql (options that are not relevant) I get an error [1] 379 Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists! 030128 13:02:21 Aborting 030128 13:02:21 ./mysqld: Shutdown Complete but when I start the server without --chroot option it works fine. I tryed to copy /etc/passwd and /etc/shadow to /servers/mysql but it didn't work. How can I start server with chroot option?? I'm running MySQL 3.23.53 compiled from source (installed in /servers/mysql). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: simple problem, no tcp port openning - Solved
after digging around more, i found Debian's MySQL has 'skip-networking' in the /etc/mysql/my.cnf further down On Monday 27 January 2003 17:08, you wrote: installed mysql onto debian using a deb file. it is running locally fine, but it isn't openning a TCP port for remote connections to it. a few lines from /etc/mysql/my.cnf [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 /var/log/mysql.log /usr/sbin/mysqld, Version: 3.23.49-log, started with: Tcp port: 0 Unix socket: /var/run/mysqld/mysqld.sock status: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.49, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version3.23.49-log Protocol version 10 ConnectionLocalhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 7 min 21 sec mysqladmin variables -p | port| 0 -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Access XP crashes regularly when linked to MySQL via MyODBC
Hi All, My MySQL Server is 3.23.53-max-nt. I'm linking to it via MyODBC 3.51.05.00 and accessing its tables via Access XP linked tables. For some reason, Access XP crashes regularly in this setup. No meaningful error messages, just crashes and reloads after a repair. Even more odd (and I only have subjective observation to back this up), it appears to only crash when left unattended for a period of time. Although, maybe I have that impression because when left alone for a period of time equates to running long enough to have encountered a problem? Is this a known problem with MySQL 3.23.53-max-nt / MyODBC 3.51.05.00 / Access XP? Regards and best wishes, Murray Wells - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with SELECT, JOIN and WHERE query
Hi, a query like this should do the job select user_profile.user_id from user_profile left join team_member on (team_member.user_id=user_profile.user.id and team_member.team_id=2) where team_member.user_id is NULL; Dobromir Velev [EMAIL PROTECTED] www.websitepulse.com - Original Message - From: heiko mundle [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 16:08 Subject: Help with SELECT, JOIN and WHERE query Hi, I got a difficult problem with a SELECT query: In my application there are two tables, one for all users (user_profile) one for a user-in-team relation. I want to know, which user is still not in a speciffic team. Users can be in more than one team, but we don't care about that. Example: user_profile: +-+ | user_id | +-+ |1000 | |1001 | |1002 | |1003 | |1004 | |1005 | |1006 | |... | +-+ team_member: +-+-+ | team_id | user_id | +-+-+ | 1 |1000 | | 1 |1004 | | 1 |1005 | | 2 |1006 | | 2 |1003 | | 2 |1000 | | 2 |1001 | | 2 |1005 | | 3 |1001 | | 3 |1005 | | 3 |1002 | | 3 |1003 | | ... |... | +-+-+ Who is not in team no. 2? Result: +-+ |1002 | |1004 | +-+ Thanks for any hint Kind regards Heiko Mundle -- +++ GMX - Mail, Messaging more http://www.gmx.net +++ NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT UPDATE
Did the update work? I would expect it did and is just reported that you have not added or taken away any rows. Simon -Original Message- From: Daniele Lo Re [mailto:[EMAIL PROTECTED]] Sent: 28 January 2003 14:24 To: [EMAIL PROTECTED] Subject: SELECT UPDATE Hi everybody. I got a really weird behaviour from Mysql, while doing some select and update queries. I made a select, something like: SELECT Something FROM WHERE and the result was not empty. But when I tried to make an update: UPDATE..WHERE , with THE SAME WHERE clause, result was empty. I'm sure that it's not reguarding permissions, and rows were not updated yet, that is UPDATE returned: Matching rows = 0 Updated rows = 0. A MATCH() search was used in the queries. Does anybody have ideas. Thanks Daniele - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
error 1129
We have been receiving this error frequently from our mysql server about our webserver. 1129: Host 'ip-adress' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts' This is getting anyoing. Can someone tell me if there is a way to disable this feature for a certain list of host. I would like to leave the max_connect_errors variable to 10 so that unlegetimate host are blocked but not the legetimate one? Can someone help me with this? Thanx to everyone in advance! =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Patrick Ouellet - [EMAIL PROTECTED] Administrateur des serveurs reseaux Informatique - Poste 130 Microtec Technologies inc. =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= First they ignore you. Then they laugh at you. Then they fight you. Then you win. -Mahatma Gandhi =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with SELECT, JOIN and WHERE query
If you have mySql 4 use a sub-query select user_id from user_profile where user_id not in (select user_id from team_member where team_id = 2) Hi, I got a difficult problem with a SELECT query: In my application there are two tables, one for all users (user_profile) one for a user-in-team relation. I want to know, which user is still not in a speciffic team. Users can be in more than one team, but we don't care about that. Example: user_profile: +-+ | user_id | +-+ |1000 | |1001 | |1002 | |1003 | |1004 | |1005 | |1006 | |... | +-+ team_member: +-+-+ | team_id | user_id | +-+-+ | 1 |1000 | | 1 |1004 | | 1 |1005 | | 2 |1006 | | 2 |1003 | | 2 |1000 | | 2 |1001 | | 2 |1005 | | 3 |1001 | | 3 |1005 | | 3 |1002 | | 3 |1003 | | ... |... | +-+-+ Who is not in team no. 2? Result: +-+ |1002 | |1004 | +-+ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 3.23 thread error - help/advice please
Hi, i had a problem like this, too. in my case it wasnt mysql restricting the number of threads but my OS (Linux/Debian). Usually mysql does not run as root, but as user mysql or something else. Debian restricts normal users to a number of running processes to prevent an overloaded. i solved my problem by raising this value, in my case with the tool ulimit. i call it with the appopriate paramter in safe_mysqld. there is already a ulimit call in safe_mysqld to raise thie number of allowed open files. i added my ulimit right after that line... hope that helps... regards, philipp - Original Message - From: my5ql _ [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 3:04 PM Subject: 3.23 thread error - help/advice please I tweaked my.cnf on a server. Changed wait_timeout from 80 to 75 and increased key_buffer to 1280M from 1024M max_connections is set to 500 Max_used_connections was reported at 370 (from 'mysqladmin variables') thread_cache is set to 48 I ran 'mysqladmin flush-hosts' as the mysql root user and got the following output: /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug' Problem seems to have disappeared once I reverted back to my old settings (and I don't get the regular [every 10min] php pconnect errors). Max_used_connections is now at 439. System is MySQL 3.23.51 built from source, Rh 7.3, custom 2.4.19 kernel. What I'm unsure about is the errors from php pconnect and the inability to flush hosts when I hadn't hit any connection limits. I have over 2GB RAM free (as reported by 'top') Any ideas/suggestions? _ Chat online in real time with MSN Messenger http://messenger.msn.co.uk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT UPDATE
It may be possible since you used a MATCH ... AGAINST() search your hits were zero. Let me know if you do not understand my reply. -Original Message- From: Daniele Lo Re [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 8:24 AM To: [EMAIL PROTECTED] Subject: SELECT UPDATE Hi everybody. I got a really weird behaviour from Mysql, while doing some select and update queries. I made a select, something like: SELECT Something FROM WHERE and the result was not empty. But when I tried to make an update: UPDATE..WHERE , with THE SAME WHERE clause, result was empty. I'm sure that it's not reguarding permissions, and rows were not updated yet, that is UPDATE returned: Matching rows = 0 Updated rows = 0. A MATCH() search was used in the queries. Does anybody have ideas. Thanks Daniele - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sql query using select and row functions
I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error with mysqlImport
Hi I'm trying an import of a 'comma delimited' file and I get this error. The command -=-=--=-=-=-=-= mysqlimport -c user_id,user_id_lower,passwd_lower,fname,lname,address1,city,state,zip,country,email,email_lower,user_num,team_name,team_city,team_canonical,public_email,league_name,league_pwd,league_type,num_teams,league_canonical,team_id,league_id,head2head,roto,strategy -d -uusername -ppassword -hthehost.domain.com --fields-terminated-by=@@@ --lines-terminated-by=\n --debug --ignore SbxPlusDB SbxPromoList.csv The error -=-=-=-=-= mysqlimport: Error: The used command is not allowed with this MySQL version, when using table: SbxPromoList Notes -=-=-=-= - I have table in the SbxPlusDB called SbxPromoList - Connecting with proper username, password, hosat work no problem. I've just erased them here for security sake. - Also, this command work on another DB using the exact same version. - Version of DB is 3.23 MAX. All tables are InnoDB. Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help with SELECT, JOIN and WHERE query
SELECT u.user_id FROM user_profile u LEFT JOIN team_member t ON u.user_id = t.user_id WHERE t.team_id 2 -Original Message- From: heiko mundle [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 8:08 AM To: [EMAIL PROTECTED] Subject: Help with SELECT, JOIN and WHERE query Hi, I got a difficult problem with a SELECT query: In my application there are two tables, one for all users (user_profile) one for a user-in-team relation. I want to know, which user is still not in a speciffic team. Users can be in more than one team, but we don't care about that. Example: user_profile: +-+ | user_id | +-+ |1000 | |1001 | |1002 | |1003 | |1004 | |1005 | |1006 | |... | +-+ team_member: +-+-+ | team_id | user_id | +-+-+ | 1 |1000 | | 1 |1004 | | 1 |1005 | | 2 |1006 | | 2 |1003 | | 2 |1000 | | 2 |1001 | | 2 |1005 | | 3 |1001 | | 3 |1005 | | 3 |1002 | | 3 |1003 | | ... |... | +-+-+ Who is not in team no. 2? Result: +-+ |1002 | |1004 | +-+ Thanks for any hint Kind regards Heiko Mundle -- +++ GMX - Mail, Messaging more http://www.gmx.net +++ NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 3.23 thread error - help/advice please
What kind of limit did you set? I think I've found the area in safe_mysqld you referred to. Is this correct? -- # If we are root, change the err log to the right user. touch $err_log; chown $user $err_log if test -n $open_files then ulimit -n $open_files fi if test -n $core_file_size then ulimit -c $core_file_size fi fi - From: Philipp [EMAIL PROTECTED] To: my5ql _ [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: Re: 3.23 thread error - help/advice please Date: Tue, 28 Jan 2003 17:15:06 +0100 Hi, i had a problem like this, too. in my case it wasnt mysql restricting the number of threads but my OS (Linux/Debian). Usually mysql does not run as root, but as user mysql or something else. Debian restricts normal users to a number of running processes to prevent an overloaded. i solved my problem by raising this value, in my case with the tool ulimit. i call it with the appopriate paramter in safe_mysqld. there is already a ulimit call in safe_mysqld to raise thie number of allowed open files. i added my ulimit right after that line... hope that helps... regards, philipp - Original Message - From: my5ql _ [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 3:04 PM Subject: 3.23 thread error - help/advice please I tweaked my.cnf on a server. Changed wait_timeout from 80 to 75 and increased key_buffer to 1280M from 1024M max_connections is set to 500 Max_used_connections was reported at 370 (from 'mysqladmin variables') thread_cache is set to 48 I ran 'mysqladmin flush-hosts' as the mysql root user and got the following output: /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug' Problem seems to have disappeared once I reverted back to my old settings (and I don't get the regular [every 10min] php pconnect errors). Max_used_connections is now at 439. System is MySQL 3.23.51 built from source, Rh 7.3, custom 2.4.19 kernel. What I'm unsure about is the errors from php pconnect and the inability to flush hosts when I hadn't hit any connection limits. I have over 2GB RAM free (as reported by 'top') Any ideas/suggestions? _ Chat online in real time with MSN Messenger http://messenger.msn.co.uk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Use MSN Messenger to send music and pics to your friends http://messenger.msn.co.uk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Connect to Mysql from java(linux)
Hi: I am new to Mysql. I have installed mysql3.23.55 on linux RH7.1. Now mysql is running successfully on my system. When I try connecting thru java(mysql-connector-java-3.0.1) I am getting an Access Denied error. It says mysql@machinename access denied. Now i tried from command line: mysql -u mysql -p -h localhost. This works, but when i give:mysql -u mysql -p -h machinename it doesnt work. Why is that? The user mysql has all priviliges granted. Thanks for any help on this. vim __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with SELECT, JOIN and WHERE query
Hi, I got a difficult problem with a SELECT query: In my application there are two tables, one for all users (user_profile) one for a user-in-team relation. I want to know, which user is still not in a speciffic team. Users can be in more than one team, but we don't care about that. What you want is a left join with a =null condition.. BTW recommend unique column names, it makes this shorter since you wouldn't need the table names.. SELECT user_profile.user_id FROM user_profile LEFT JOIN team_member ON user_profile.user_id = team_member.user_id WHERE team_member.user_id = null Example: user_profile: +-+ | user_id | +-+ |1000 | |... | +-+ team_member: +-+-+ | team_id | user_id | +-+-+ | 1 |1000 | Who is not in team no. 2? Result: +-+ |1002 | |1004 | +-+ Thanks for any hint Kind regards Heiko Mundle William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Configure prob with FreeBSD/Linuxthreads
I've been trying to install MySQL 4.0.9 on FreeBSD 4.7, and have been getting stuck in the configure phase. I'd be grateful for any suggestions. I'm running FreeBSD 4.7 on a single-processor 1.4GHz PIII, using gcc 2.95.4, and trying to compile with Linuxthreads. I've been using the instructions Jeremy Zawodny posted in his blog at http://jeremy.zawodny.com/blog/archives/000458.html . I modified Jeremy's command to eliminate some of the obvious things I didn't need, and started with: CFLAGS='-O -pipe -march=pentiumpro -D__USE_UNIX98\ -D_REENTRANT -D_THREAD_SAFE -DHAVE_BROKEN_REALPATH \ -I/usr/local/include/pthread/linuxthreads' CXX=cc\ CC=cc CXXFLAGS='-O -pipe -march=pentiumpro \ -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE \ -DHAVE_BROKEN_REALPATH -I/usr/local/include/pthread/linuxthreads \ -felide-constructors -fno-rtti -fno-exceptions' ./configure \ --with-mit-threads=no \ --enable-assembler \ '--with-named-thread-libs=-DHAVE_GLIBC2_STYLE_GETHOSTBYNAME_R\ -D_THREAD_SAFE -DHAVE_BROKEN_REALPATH\ -I/usr/local/include/pthread/linuxthreads -L/usr/local/lib \ -llthread -llgcc_r' --enable-thread-safe-client \ --with-libwrap --with-raid This dies relatively early with: checking for C compiler default output... configure: error: C compiler cannot create executables I've tried re-configuring with every possible variant, and it seems that the problem happens when CFLAGS has both the linuxthreads flag and the D_THREAD_SAFE flag. If the latter is omitted, configuration will proceed past this point. Since I don't know much about the whole compilation process, I'd be grateful for any suggestions as to what to try to get this to work properly. Also, once past this, if I omit all the flags on ./configure, it finishes configuration properly; if I include them, it will die later on with: checking size of char... configure: error: cannot compute sizeof (char), 77 I haven't experimented to see which configure flag might be causing this, but if anyone has any ideas, I'd be grateful. Thanks. Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql dump for remote db
Does anyone have a suggestion as to how I could run a periodic mysql dump for a db that is hosted remotely - IE a web host. I do know that this host does not support crons. Any help is appreciated. thanks MS sql, query - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 2:27 AM Subject: re: question about primary key On Monday 27 January 2003 10:27, Vicente Valero wrote: I want to create a table with a numeric primary key as index in my sql. I want this index will be automatic. I've tried an auto_increment column, but if insert a non-correlative value then all new entries continue from this non-correlative value (1,2,3,4,1026,1027,...). It is possible use the first value not-used in the column automatically (1,2,3,1026,4,5,1000,6,7,...)? No, you can't do this automatically. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: question about date range
You may want to strongly consider converting the data to a timestamp field type, but that's for down the road. You want to do you search like any other range search you would do. select * from orderheadr where orderid between 2003012400 and 2003012499 You're saying you want to search on a range, but you are coding your query like searching for a similarity. On Friday, January 24, 2003, at 12:33 PM, Chuck Barnett wrote: Hi, I inherited a db that has a varchar(30) column that holds a date/time stamp in the form of MMDDHHmmss (ex: 20030124093952) Well I want to select a range based on the first 8 characters(MMDD). -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Please explain the EXPLAIN to me
Hi folks, I used the EXPLAIN-statement to optimize one of my querys (Its been the first time I used it...). I don't understand one of the results given by SELECT. At first, my statement: SELECT zutaten.id, zutaten.name, hersteller.name, einheiten.lang, zutaten_gruppen.name FROM zutaten JOIN hersteller JOIN einheiten JOIN zutaten_gruppen WHERE zutaten.name LIKE '%$suchbegriff%' AND einheiten.id = zutaten.einheit_id AND hersteller.id = zutaten.hersteller_id AND zutaten_gruppen.id = zutaten.gruppe_id This one should give me to each 'zutaten' matching the LIKE-statement the 'einheiten', 'hersteller' and 'zutaten_gruppen' names. Well, actually it does :) But when I use the EXLAIN-statement on this SELECT-statement there is a thing I don't understand: tabletype possible_keys key key_len ref rows Extra zutaten ALLNULL NULLNULLNULL 11 where used hersteller ALLPRIMARY NULLNULLNULL 3 where used einheiteneq_ref PRIMARY PRIMARY 1 zutaten.einheit_id 1 zutaten_gruppen eq_ref PRIMARY PRIMARY 2 zutaten.gruppe_id 1 Why does MySQL not use the PRIMARY to find the entrys matching hersteller.id = zutaten.hersteller_id? And why does it tell me where used (and the last two don't)? Here are the table structures (Reduced to the fields used in the statement) CREATE TABLE zutaten ( id smallint(5) unsigned NOT NULL auto_increment, name varchar(80) NOT NULL default '', einheit_id tinyint(3) unsigned NOT NULL default '1', hersteller_id smallint(5) unsigned NOT NULL default '1', gruppe_id smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (id), FULLTEXT KEY name (name) ) TYPE=MyISAM; CREATE TABLE zutaten_gruppen ( id smallint(5) unsigned NOT NULL auto_increment, name varchar(50) NOT NULL default '', PRIMARY KEY (id), KEY name (name) ) TYPE=MyISAM; CREATE TABLE hersteller ( id smallint(5) unsigned NOT NULL auto_increment, name varchar(60) NOT NULL default '', PRIMARY KEY (id) ) TYPE=MyISAM; CREATE TABLE einheiten ( id tinyint(3) unsigned NOT NULL auto_increment, kurz varchar(5) NOT NULL default '', PRIMARY KEY (id), KEY kurz (kurz) ) TYPE=MyISAM; Sorry for the not self-explanatory table- and fieldnames. regards Marcus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Weird locking/blocking issues
Heya Folkth: I have built mysql (4.0.9) on freebsd (4.7-RELEASE) with linuxthreads (2.2.3) and still encounter the 'strange threading' behavior described in J. Zawodny's blog. Here's the situation... 1/ there are three threads which lock and then write to two different tables...the locks are gotten using 'low_priority write'; the writing threads are continually waiting for locks (i.e two are waiting, one is executing); 2/ On a different thread I do a select count(*) from one of the tables; Sometimes the select returns pretty quick witht the count; however, frequently the selecting thread will simply hang waiting to get the lock--if I look at the thread in [top] the thread will be taking a LOT of cpu time; if I look at the offending thread in the processlist it has a cycling status of 'waiting for tables', 'NULL', 'Locked', 'System Lock' and 'Reopen Tables'. It's my understanding that the selecting thread should be given the table lock as soon as the writing thread that has the write lock has finished. *All three writing threads keep processing* while the select thread is waiting: I can see their debug output in my terminals. Note that this behavior occurs whether I compile with Linuxthreads or not, also I tried skip-external-locking to see if the 'system lock' status disappears, but it doesn't. Anyone else found this? Known issue? Peter ^_^ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 3.23 thread error - help/advice please
Hi, yes, thats where i added the line. i used ulimit -u. ulimit is a build-in bash command. man bash says: -u The maximum number of processes available to a single user My section looks like this: if test -n $open_files then ulimit -n $open_files ulimit -u 1505# - That limits to 1505 processes. fi if test -n $core_file_size then ulimit -c $core_file_size fi fi If you want a connection limit of 500 set it to something more than 500, because the main process, which will not handle a connection, has to be counted. You can also add something like this before and after the above section to verify: ulimit -a /tmp/mysql-ulimit.txt That will print all ulimit restrictions to /tmp/mysql-ulimit.txt. Regards, Philipp - Original Message - From: my5ql _ [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 6:05 PM Subject: Re: 3.23 thread error - help/advice please What kind of limit did you set? I think I've found the area in safe_mysqld you referred to. Is this correct? -- # If we are root, change the err log to the right user. touch $err_log; chown $user $err_log if test -n $open_files then ulimit -n $open_files fi if test -n $core_file_size then ulimit -c $core_file_size fi fi - From: Philipp [EMAIL PROTECTED] To: my5ql _ [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: Re: 3.23 thread error - help/advice please Date: Tue, 28 Jan 2003 17:15:06 +0100 Hi, i had a problem like this, too. in my case it wasnt mysql restricting the number of threads but my OS (Linux/Debian). Usually mysql does not run as root, but as user mysql or something else. Debian restricts normal users to a number of running processes to prevent an overloaded. i solved my problem by raising this value, in my case with the tool ulimit. i call it with the appopriate paramter in safe_mysqld. there is already a ulimit call in safe_mysqld to raise thie number of allowed open files. i added my ulimit right after that line... hope that helps... regards, philipp - Original Message - From: my5ql _ [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 3:04 PM Subject: 3.23 thread error - help/advice please I tweaked my.cnf on a server. Changed wait_timeout from 80 to 75 and increased key_buffer to 1280M from 1024M max_connections is set to 500 Max_used_connections was reported at 370 (from 'mysqladmin variables') thread_cache is set to 48 I ran 'mysqladmin flush-hosts' as the mysql root user and got the following output: /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug' Problem seems to have disappeared once I reverted back to my old settings (and I don't get the regular [every 10min] php pconnect errors). Max_used_connections is now at 439. System is MySQL 3.23.51 built from source, Rh 7.3, custom 2.4.19 kernel. What I'm unsure about is the errors from php pconnect and the inability to flush hosts when I hadn't hit any connection limits. I have over 2GB RAM free (as reported by 'top') Any ideas/suggestions? _ Chat online in real time with MSN Messenger http://messenger.msn.co.uk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Use MSN Messenger to send music and pics to your friends http://messenger.msn.co.uk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
file-descriptor limits for linux
Hi, Does anyone know what the story is for file-descriptor limits on Linux ? I read in http://www.xenoclast.org/doc/benchmark/HTTP-benchmarking-HOWTO/node7.html that root needs to set /proc/sys/fs/file-max to a high value in order that ulimit -n will work. However, on a Debian Woody box (2.4 kernel), this doesn't seem to be necessary. As root, I can set the value as high as I want (up to about 63000) regardless of the value in /proc/sys/fs/file-max. Also, these are per-process limits. What is the overall machine limit - assuming there is one ? These questions are related to MySQL, SQL, etc. Honest. == Martin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Access XP crashes regularly when linked to MySQL via MyODBC
I have seen this behavior reported several times in regards to the MySQL being accessed via MyODBC and MS Access, (various versions). Are you detailing that Access crashes and you have to repair the MySQL tables? If so, is there an error being written to the MySQL error log? -Original Message- From: M Wells [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 9:19 AM To: [EMAIL PROTECTED] Subject: Access XP crashes regularly when linked to MySQL via MyODBC Hi All, My MySQL Server is 3.23.53-max-nt. I'm linking to it via MyODBC 3.51.05.00 and accessing its tables via Access XP linked tables. For some reason, Access XP crashes regularly in this setup. No meaningful error messages, just crashes and reloads after a repair. Even more odd (and I only have subjective observation to back this up), it appears to only crash when left unattended for a period of time. Although, maybe I have that impression because when left alone for a period of time equates to running long enough to have encountered a problem? Is this a known problem with MySQL 3.23.53-max-nt / MyODBC 3.51.05.00 / Access XP? Regards and best wishes, Murray Wells - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
In eWeek: Rhode Island uses MySQL
Hi all, Just read my copy of eWeek, and it seems that a state government has used MySQL for a public access rules and regulations database: http://www.eweek.com/article2/0,3959,808852,00.asp quote: Rhode Island put itself on the cutting edge of hot-technology uptake last year when it became one of the first state governments to get beyond traditional government conservatism and implement open-source technology. The gamble is paying off: The bill for the state's rules and regulations database came in at $40,000only $6,000 of which was hardware costsand took one consultant four months working only two days a week to complete. Not a big user, but good to see a state government openly use MySQL. Mike Hillyer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sql query using select and row functions
Why not just reverse your order by clause and use Limit 5? Mike Hillyer -Original Message- From: Christopher Lyon [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 9:26 AM To: [EMAIL PROTECTED] Subject: sql query using select and row functions I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problems creating fulltext index
Hello everyone, I'm running Ver 8.39 Distrib 4.0.9-gamma, for intel-linux on i686. I'm trying to create a fulltext index on an existing myisam table and running up against an error. From what I can tell, I'm using the alter table command correctly, but I keep getting errno 140. Can anyone point out my error? mysql alter table materials add fulltext title (title,variant_title,ss_title,term_1,term_2,term_3,term_4, term_5,abstract,notes,personal_name_1,personal_name_2,personal_name_3,person al_name_4, personal_name_5,personal_name_6); ERROR 1005: Can't create table './lincs/#sql-6d12_7.frm' (errno: 140) # perror 140 Error code 140: Unknown error 140 140 = Wrong create options mysql show create table materials; CREATE TABLE `materials` ( `rec_ID` mediumint(9) NOT NULL auto_increment, `file_name` varchar(100) default NULL, `user_name` varchar(250) default NULL, `user_email` varchar(250) default NULL, `date_created` timestamp(14) NOT NULL, `date_modified` timestamp(14) NOT NULL, `inputting_agency` varchar(100) default NULL, `inputting_state` varchar(100) default NULL, `title` text, `variant_title` text, `edition` varchar(100) default NULL, `ss_title` text, `ss_volume` varchar(100) default NULL, `personal_name_1` varchar(250) default NULL, `personal_name_2` varchar(250) default NULL, `personal_name_3` varchar(250) default NULL, `corporate_name_1` varchar(250) default NULL, `corporate_name_2` varchar(250) default NULL, `electronic_availability` varchar(250) default NULL, `publisher` text, `pub_place` varchar(100) default NULL, `pub_date` varchar(100) default NULL, `pub_phone` varchar(100) default NULL, `pub_fax` varchar(100) default NULL, `pub_TDD` varchar(100) default NULL, `pub_email` varchar(100) default NULL, `pub_URL` varchar(250) default NULL, `special_collection` text, `material_type` text, `physical_media` varchar(100) default NULL, `language` text, `tool_for` text, `beneficiary` text, `skill_level` text, `term_1` text, `term_2` text, `term_3` text, `term_4` text, `term_4_source` varchar(100) default NULL, `term_5` text, `term_5_source` varchar(100) default NULL, `notes` text, `abstract` text, `funding_info` text, `desc_extent` text, `desc_dimensions` varchar(100) default NULL, `desc_materials` varchar(100) default NULL, `system_requirements` varchar(100) default NULL, `ISBN` varchar(100) default NULL, `ISSN` varchar(100) default NULL, `EDRS` varchar(100) default NULL, `GPO` varchar(100) default NULL, `local_id` varchar(100) default NULL, `phys_location` varchar(100) default NULL, `phys_address` varchar(250) default NULL, `phys_phone` varchar(100) default NULL, `phys_fax` varchar(100) default NULL, `phys_TDD` varchar(100) default NULL, `phys_email` varchar(100) default NULL, `phys_URL` varchar(250) default NULL, `dist_name` varchar(100) default NULL, `dist_address` varchar(250) default NULL, `dist_phone` varchar(100) default NULL, `dist_fax` varchar(100) default NULL, `dist_TDD` varchar(100) default NULL, `dist_email` varchar(100) default NULL, `dist_URL` varchar(100) default NULL, `dist_availability` text, `dist_details` text, `pub_month` char(3) default NULL, `pub_year` varchar(6) default NULL, `state_locality` varchar(100) default NULL, `uniq_id` varchar(100) default NULL, `region` varchar(100) default NULL, `personal_name_4` varchar(250) default NULL, `personal_name_5` varchar(250) default NULL, `personal_name_6` text, PRIMARY KEY (`rec_ID`) ) TYPE=MyISAM Duncan --- Duncan Salada Titan Systems Corporation 301-925-3222 x375 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sql query using select and row functions
On 1/28/03 8:26 AM, Christopher Lyon [EMAIL PROTECTED] wrote: I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? The easiest way is probably to add an ORDER BY field DESC into your SQL statement. If you use an autoincrement field you could use that otherwise add a field and make it TIMESTAMP. Hth/h - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sql query using select and row functions
Try ordering the records backwards, e.g ORDER BY id DESC and then limit 0, 5 HTH JFernando * sql * -Original Message- From: Christopher Lyon [mailto:[EMAIL PROTECTED]] Sent: January 28, 2003 11:26 To: [EMAIL PROTECTED] Subject: sql query using select and row functions I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sql query using select and row functions
Do you mean the last five rows in the database or the last five rows entered into the database? Either way, Last five rows select [someColumn] from [someTable] ORDER BY [someColumn] DESC LIMIT 0, 5 If the total number of rows is known select [someColumn] from [someTable] ORDER BY [someColumn] LIMIT (totalNum - 5), -1 Or if you have a timestamp and you need the last five entered select [someColumn] from [someTable] ORDER BY timestamp DESC LIMIT 0, 5 I hope this helps. -Original Message- From: Christopher Lyon [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 10:26 AM To: [EMAIL PROTECTED] Subject: sql query using select and row functions I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sql query using select and row functions
I would think they would be the same no? It turns out in the database that they are the same. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 12:23 PM To: Christopher Lyon; [EMAIL PROTECTED] Subject: RE: sql query using select and row functions Do you mean the last five rows in the database or the last five rows entered into the database? Either way, Last five rows select [someColumn] from [someTable] ORDER BY [someColumn] DESC LIMIT 0, 5 If the total number of rows is known select [someColumn] from [someTable] ORDER BY [someColumn] LIMIT (totalNum - 5), -1 Or if you have a timestamp and you need the last five entered select [someColumn] from [someTable] ORDER BY timestamp DESC LIMIT 0, 5 I hope this helps. -Original Message- From: Christopher Lyon [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 10:26 AM To: [EMAIL PROTECTED] Subject: sql query using select and row functions I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sql query using select and row functions
In a relational database your data can be stored differently than the way the data is entered. ... If all is the same, one of the three examples should work for you. -Original Message- From: Christopher Lyon [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 2:28 PM To: Victor Pendleton; [EMAIL PROTECTED] Subject: RE: sql query using select and row functions I would think they would be the same no? It turns out in the database that they are the same. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 12:23 PM To: Christopher Lyon; [EMAIL PROTECTED] Subject: RE: sql query using select and row functions Do you mean the last five rows in the database or the last five rows entered into the database? Either way, Last five rows select [someColumn] from [someTable] ORDER BY [someColumn] DESC LIMIT 0, 5 If the total number of rows is known select [someColumn] from [someTable] ORDER BY [someColumn] LIMIT (totalNum - 5), -1 Or if you have a timestamp and you need the last five entered select [someColumn] from [someTable] ORDER BY timestamp DESC LIMIT 0, 5 I hope this helps. -Original Message- From: Christopher Lyon [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 10:26 AM To: [EMAIL PROTECTED] Subject: sql query using select and row functions I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql/innob optimizer problem with != and selecting other than primary key
I ran into a problem a few weeks ago with a query that would take up to 2 minutes to return 0 rows found when selecting anything other than the primary key (0 rows is correct). When I selected just the primary_key, the query would take less than 3 seconds. The 2 columns involved in the where part of the query are in a 2 column clustered index which aparently was being ignored when selecting anything other than the primary key and a != in where part of the query. The actual table is 48 columns * 2,457,684 rows. See below for better explanation. NOTES - order_status can have 3 possible values (N, T, Y) create table customer_orders ( order_idint NOT NULL AUTO_INCREMENT, customer_id char(8) NOT NULL, customer_session_id varchar(30) NOT NULL, order_statuschar(1) DEFAULT 'N' NOT NULL, cus_last_name varchar(20) NOT NULL, cus_first_name varchar(20) NOT NULL, cus_address1 varchar(50) NOT NULL, cus_address2 varchar(50), cus_zip varchar(10), UNIQUE INDEX ORD_ID_IDX (order_id), INDEX CUS_ID_SESSION_IDX (customer_id, customer_session_id), INDEX CUS_ID_ORD_STAT_IDX (customer_id, order_status), INDEX CUS_LAST_IDX(cus_last_name), INDEX CUS_FIRST_IDX(cus_first_name) ) TYPE=INNODB; SELECTING ONLY THE PRIMARY KEY - select order_id from customer_orders where customer_id = 'timma and order_status != 'Y' and order_status != 'T'; query time 3 seconds SELECTING MORE THAN THE PRIMARY KEY - select order_id, cus_last_name, cus_first_name from customer_orders where customer_id = 'timma and order_status != 'Y' and order_status != 'T'; query time =~ 2 minutes SELECTING MORE THAN THE PRIMARY KEY BUT USING = INSTEAD OF != select order_id, cus_last_name, cus_first_name from customer_orders where customer_id = 'timma and order_status = 'N'; query time 3 seconds Hope this helps! mysql, query -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Configure prob with FreeBSD/Linuxthreads
On Tue, Jan 28, 2003 at 12:21:37PM -0500, Jesse Sheidlower wrote: I've been trying to install MySQL 4.0.9 on FreeBSD 4.7, and have been getting stuck in the configure phase. I'd be grateful for any suggestions. I'm running FreeBSD 4.7 on a single-processor 1.4GHz PIII, using gcc 2.95.4, and trying to compile with Linuxthreads. I've been using the instructions Jeremy Zawodny posted in his blog at http://jeremy.zawodny.com/blog/archives/000458.html . I modified Jeremy's command to eliminate some of the obvious things I didn't need, and started with: [snip] This dies relatively early with: checking for C compiler default output... configure: error: C compiler cannot create executables Out of curiosity, which version of gcc are you using? All my work has been with 2.95.3. I'm not sure if it matters, but it'd eliminate one variable... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 0 days, processed 25,785,970 queries (358/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Server crash selecting from merge table
Description: Certain queries on merge tables reliably fail or crash the server as the script below demonstrates. How-To-Repeat: Run this script: #! /bin/bash # This script demonstrates a bug in the way MySQL handles merge tables. The bug depends on # the index; it doesn't manifest if there is no index or if the fields are indexed separately. mysql -vv EOF USE test; DROP TABLE IF EXISTS a; DROP TABLE IF EXISTS b; DROP TABLE IF EXISTS c; # Create two tables with the same definition. CREATE TABLE a ( ID INT NOT NULL, DateDATE NOT NULL, KEY (ID, Date) ); CREATE TABLE b ( ID INT NOT NULL, DateDATE NOT NULL, KEY (ID, Date) ); # Populate the tables. INSERT INTO a (ID, Date) VALUES (1, '2002-01-01'), (1, '2002-01-02'), (1, '2002-01-03'); INSERT INTO b (ID, Date) VALUES (1, '2002-02-01'), (1, '2002-02-02'), (1, '2002-02-03'); # Create a merge table combining the previous two. CREATE TABLE c ( ID INT NOT NULL, DateDATE NOT NULL, KEY (ID, Date) ) TYPE=MERGE UNION=(a, b); # This works. SELECT MIN(Date) FROM c WHERE ID = 1; # This fails by returning NULL. SELECT MAX(Date) FROM c WHERE ID = 1; # Going straight to the component tables works though. SELECT MAX(Date) FROM a WHERE ID = 1; SELECT MAX(Date) FROM b WHERE ID = 1; # The different results from these might be a clue. EXPLAIN SELECT MIN(Date) FROM c WHERE ID = 1; EXPLAIN SELECT MAX(Date) FROM c WHERE ID = 1; # This crashes the server. SELECT MIN(Date), MAX(Date) FROM c WHERE ID = 1; EOF Fix: Submitter-Id: submitter ID Originator:Rob Steele Organization: FatKat, Inc. MySQL support: none Synopsis: Selecting from merge table crashes server Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.54 (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.54, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.54-Max Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 7 min 44 sec Threads: 1 Questions: 1 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 0 Queries per second avg: 0.002 Environment: System: Linux warren.fatkat.com 2.4.18-19.8.0 #1 Thu Dec 12 05:39:29 EST 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Oct 2 19:15 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 5 19:12 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 18:59 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 18:50 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Oct 2 19:53 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--without-innodb' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' 'CXX=gcc' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Configure prob with FreeBSD/Linuxthreads
In the last episode (Jan 28), Jesse Sheidlower said: I've been trying to install MySQL 4.0.9 on FreeBSD 4.7, and have been getting stuck in the configure phase. I'd be grateful for any suggestions. I modified Jeremy's command to eliminate some of the obvious things I didn't need, and started with: CFLAGS='-O -pipe -march=pentiumpro -D__USE_UNIX98\ -D_REENTRANT -D_THREAD_SAFE -DHAVE_BROKEN_REALPATH \ -I/usr/local/include/pthread/linuxthreads' CXX=cc\ CC=cc CXXFLAGS='-O -pipe -march=pentiumpro \ -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE \ -DHAVE_BROKEN_REALPATH -I/usr/local/include/pthread/linuxthreads \ -felide-constructors -fno-rtti -fno-exceptions' ./configure \ --with-mit-threads=no \ --enable-assembler \ '--with-named-thread-libs=-DHAVE_GLIBC2_STYLE_GETHOSTBYNAME_R\ -D_THREAD_SAFE -DHAVE_BROKEN_REALPATH\ -I/usr/local/include/pthread/linuxthreads -L/usr/local/lib \ -llthread -llgcc_r' --enable-thread-safe-client \ --with-libwrap --with-raid This dies relatively early with: checking for C compiler default output... configure: error: C compiler cannot create executables Configure errors almost always log more info in config.log. You'll see something like this: configure:2015: checking for C compiler default output configure:2018: gccconftest.c 5 [errors errors errors] configure:2021: $? = 1 configure: failed program was: [test program] configure:2054: result: no Find the matching lines in your config.log and let us see them. checking size of char... configure: error: cannot compute sizeof (char), 77 Same here -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Configure prob with FreeBSD/Linuxthreads
On Tue, Jan 28, 2003 at 01:17:30PM -0800, Jeremy Zawodny wrote: On Tue, Jan 28, 2003 at 12:21:37PM -0500, Jesse Sheidlower wrote: I've been trying to install MySQL 4.0.9 on FreeBSD 4.7, and have been getting stuck in the configure phase. I'd be grateful for any suggestions. I'm running FreeBSD 4.7 on a single-processor 1.4GHz PIII, using gcc 2.95.4, and trying to compile with Linuxthreads. I've been using the instructions Jeremy Zawodny posted in his blog at http://jeremy.zawodny.com/blog/archives/000458.html . I modified Jeremy's command to eliminate some of the obvious things I didn't need, and started with: [snip] This dies relatively early with: checking for C compiler default output... configure: error: C compiler cannot create executables Out of curiosity, which version of gcc are you using? As I posted a few lines up, it's gcc 2.95.4 ;-) After I sent the original message, I tried to play around with the configure variables, and discovered that it only worked by eliminating the entire '--with-named-thread-libs=-DHAVE_GLIBC2_STYLE_GETHOSTBYNAME_R\ -D_THREAD_SAFE -DHAVE_BROKEN_REALPATH\ -L/usr/local/lib \ -llthread -llgcc_r' group; I tried removing each one individually and it failed each time with the checking size of char... configure: error: cannot compute sizeof (char), 77 error. I've since given up, installed with the exact configure line shown in the MySQL docs, FreeBSD section, and it worked perfectly, so I'm worrying about moving my grant tables from 3.23.49 and so forth, instead of getting Linuxthreads to workBut I'd be happy to try to get this fixed, especially if it will help others. Best, Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with SELECT, JOIN and WHERE query
WHERE team_member.user_id IS NULL William R. Mussatto wrote: Hi, I got a difficult problem with a SELECT query: In my application there are two tables, one for all users (user_profile) one for a user-in-team relation. I want to know, which user is still not in a speciffic team. Users can be in more than one team, but we don't care about that. What you want is a left join with a =null condition.. BTW recommend unique column names, it makes this shorter since you wouldn't need the table names.. SELECT user_profile.user_id FROM user_profile LEFT JOIN team_member ON user_profile.user_id = team_member.user_id WHERE team_member.user_id = null - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql dump for remote db
The biggest problem is if you want to do this from a remote pc, that pc will need access to the server itself and the mysql client tools. It sounds as if this pc is not on your LAN and I doubt the hosting service will be willing to expose there database to the internet so you can do a backup. If you do have access to the db from your computer you should be able to schedule something as simple as: mysqldum -u user -ppass -h host database file to do your backup. Other then that I'm not sure how you could do this since you already stated your service wont schedule cron jobs. -Nick Mark Stringham said: Does anyone have a suggestion as to how I could run a periodic mysql dump for a db that is hosted remotely - IE a web host. I do know that this host does not support crons. Any help is appreciated. thanks MS sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql dump for remote db
On Tue, Jan 28, 2003 at 10:19:19AM -0700, Mark Stringham wrote: Does anyone have a suggestion as to how I could run a periodic mysql dump for a db that is hosted remotely - IE a web host. I do know that this host does not support crons. Any help is appreciated. If you have access to a host that runs cron, run mysqldump with the -h option. i.e. mysqldump -hmysql.example.com -usomeuser -p database name Cheers! -- Zak Greant [EMAIL PROTECTED] | MySQL Advocate | http://zak.fooassociates.com Using and Managing MySQL MySQL Training: Stockholm, February 24-28, 2003 Visit http://mysql.com/training for more information Sincere Choice: Supporting a Fair Software Market (http://sincerechoice.org) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sql query using select and row functions
On 1/28/03 12:26 PM, Christopher Lyon [EMAIL PROTECTED] wrote: I do have a timestamp field would I be better off using that? Granted it might not be the same amount of rows every time but how would that work? Select * from tbl_name where TIMESTAMP ( ) -3 hours; Well, that is not what you asked for you just wanted to get the last N records inserted into the database which is different from what you asked now. Your original question is best answered with having an autoincrement field in your table and do a: SELECT field FROM table WHERE whatever = don'tknow ORDER BY autoincrement_field DESC LIMIT N; Using the timestamp field to retrive all records inserted within the last n years/months/weeks/days/hours/minutes/second requires some string conversion of the timestamp. Your best bet in that case is to look into the Date and Time Functions: Check comments also! http://www.mysql.com/doc/en/DATETIME.html http://www.mysql.com/doc/en/Date_and_time_functions.html After reading through the excellent documentation it should be rather straight forward how to do it. Hth/h SQL, QUERY, TABLE P.s. I also strongly recommend to keep the discussion on the list for your benefit (there are much better experts on the mysql list than me) but also for the benefit of others with a similar problem (although there is a rather big lag from posting to appearing on the list). The list archives also getting way to little attention. -Original Message- From: R. Hannes Niedner [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 11:50 AM To: Christopher Lyon; MySQL Mailinglist Subject: Re: sql query using select and row functions On 1/28/03 8:26 AM, Christopher Lyon [EMAIL PROTECTED] wrote: I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? The easiest way is probably to add an ORDER BY field DESC into your SQL statement. If you use an autoincrement field you could use that otherwise add a field and make it TIMESTAMP. Hth/h - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication error: 'Could not initialize master info'
Hi list, My original post concerning this issue is below - it was a week or so ago. No replies were forthcoming, but I'm pleased and a bit embarrassed (so simple) to say that I have figured it out, and my slave is now replicating from the master. The problem was that the mysql data directory was owned by root, not mysql. While the database directories within the data directory were owned by mysql, the data directory itself was root:root 755. Changing the data directory to be owned by mysql obviously allowed the master.info file to be written there when I did the 'change master to...' command. Original post here: * Hi there, I'm trying to set up replication between two RH7.2 Linux servers running MySQL 3.23.54a (mysql RPMs). I've followed the instructions in the MySQL online manual (section 4.10 Replication in MySQL), and when I do the following command on the slave: change master to master_host='db1', master_user='not shown here', master_password='not shown here', master_log_file='db1-binary-log.004', master_log_pos=38456522; , mysql tells me: ERROR: Could not initialize master info I can't find anything in the error log, and I've set up the replication user on the master as outlined in the manual. Both master and slave are currently using databases with ISAM tables rather than MyISAM tables (although the 'mysql' database is, of course, MyISAM). I'm not using InnoDB tables. Can anyone give me an idea of how to troubleshoot this one? Thanks, Guy. * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql dump for remote db
Please don't hijack threads. In the last episode (Jan 28), Mark Stringham said: Does anyone have a suggestion as to how I could run a periodic mysql dump for a db that is hosted remotely - IE a web host. I do know that this host does not support crons. Just cron mysqldump -h webhostmachine from another machine. -- Dan Nelson [EMAIL PROTECTED] sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access XP crashes regularly when linked to MySQL via MyODBC
M Wells wrote: Hi All, My MySQL Server is 3.23.53-max-nt. I'm linking to it via MyODBC 3.51.05.00 and accessing its tables via Access XP linked tables. For some reason, Access XP crashes regularly in this setup. No meaningful error messages, just crashes and reloads after a repair. Even more odd (and I only have subjective observation to back this up), it appears to only crash when left unattended for a period of time. Although, maybe I have that impression because when left alone for a period of time equates to running long enough to have encountered a problem? Is this a known problem with MySQL 3.23.53-max-nt / MyODBC 3.51.05.00 / Access XP? Yeah I can confirm this problem. We use MySQL-4.0.x and MyODBC-3.51.x - whatever's the latest. I have had a few discussions in the MyODBC mailing list about it. It seems to be a problem with Access XP. I also noticed that it seems to happen when Access is left inactive for 5 minutes or more. This lead me to believe that MySQL is closing the client connection after a period of time and Access isn't handling it very gracefully. However the period of time isn't fixed, and sometimes it can stay open for an hour without crashing. I don't know what's up, but I'm sure it's Access' fault. The crash I get is one of those hardcore Microsoft appologises for the inconvenience. Would you like to send a bug report. Yes / No ones. They know they are to blame... I've called M$ tech support about it, and they only want to hear about it after I give them $AUS290. Since it's not really a show-stopper for us - just bloody annoying - I've left it at that. They (M$) say they will refund the $290 if they find that the problem is actually a bug in their software. But I don't really trust them on that. I'd rather give the money to TheKompany for a product like http://www.thekompany.com/products/rekall/ which is like Access only I assume it doesn't crash so much, and it uses Python instead of visual basic. It's not particularly advanced yet, but as I said, I'd rather give money to them to improve their product than give it to M$ so they can rehash the same trash (and add a few bugs while they're at it) every 18 months. One dodgy workaround I've considered is to have a hidden form in Access that opens when the database opens. Put a timer event on it to run the OnTimer() code every 5 minutes or so. And the code just refreshes the form which is linked to a dummy table in MySQL. I haven't tried this myself but I have a feeling it will work. Tell me how you get on anyway. It you need an Australian to back up your crash reports I'm more than willing to add my name to the complaints list yet again ;-) -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help - Convert Date from longtext to MySQL date format
Hi I have a few tables in a database Z, namely table a b c In table a, the columns are my_date - longtext num - int(11) eg: mysql select * from a; +--+--+--+ | my_date | x| +--+--+ | 08/06/2002 |1 | | 08/07/2002 |2 | +--+--+--+ 2 rows in set (0.00 sec) Tables b and c have the same table structure and data type format. Q: How do I convert 08/06/2002 to 2002-08-06 format without having to reinput all my data from scratch ? Any help is FULLY appreciated. Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
restart or not?
When granting someone permissions are these permissions dynamic or do I have to reload mysql? Also we have a developer who stated he cannot access the database remotely. I've granted him privileges as follows since he works on the entire system both locally and remotely. There is only one database in the system. I've set a password for access to the system also. grant all privileges on *.* to joseph@% identified by 'his_password' with grant option; grant all privileges on *.* to joseph@localhost identified by 'his_password' with grant option; GRANT RELOAD,PROCESS ON *.* TO joseph@localhost; GRANT USAGE ON *.* TO joseph@localhost; -- Jon Miller [EMAIL PROTECTED] MMT Networks Pty Ltd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with SELECT, JOIN and WHERE query
Victor, good shot! I thought of this one in the first place: SELECT u.user_id FROM user_profile u LEFT JOIN team_member t ON u.user_id = t.user_id WHERE t.team_id 2 But unfortunately, it will yield: +-+-+ | user_id | team_id | +-+-+ |1000 | 1 | |1001 | 3 | |1002 | 3 | |1003 | 3 | |1004 | 1 | |1005 | 1 | |1005 | 3 | +-+-+ 7 rows in set (0.00 sec) Where it should yield: +-+ |1002 | |1004 | +-+ A subselect would be most simple: SELECT u.user_id FROM user_profile u WHERE user_id NOT IN (SELECT user_id FROM team_member t WHERE t.team_id = 2) It's too late am I'm too tired to rewrite this query so it will work in MySQL = 4.0.9. With MySQL 4.1 (alpha out now!!!) we have subselects :) Good night, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'heiko mundle' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, January 28, 2003 5:30 PM Subject: RE: Help with SELECT, JOIN and WHERE query SELECT u.user_id FROM user_profile u LEFT JOIN team_member t ON u.user_id = t.user_id WHERE t.team_id 2 -Original Message- From: heiko mundle [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 8:08 AM To: [EMAIL PROTECTED] Subject: Help with SELECT, JOIN and WHERE query Hi, I got a difficult problem with a SELECT query: In my application there are two tables, one for all users (user_profile) one for a user-in-team relation. I want to know, which user is still not in a speciffic team. Users can be in more than one team, but we don't care about that. Example: user_profile: +-+ | user_id | +-+ |1000 | |1001 | |1002 | |1003 | |1004 | |1005 | |1006 | |... | +-+ team_member: +-+-+ | team_id | user_id | +-+-+ | 1 |1000 | | 1 |1004 | | 1 |1005 | | 2 |1006 | | 2 |1003 | | 2 |1000 | | 2 |1001 | | 2 |1005 | | 3 |1001 | | 3 |1005 | | 3 |1002 | | 3 |1003 | | ... |... | +-+-+ Who is not in team no. 2? Result: +-+ |1002 | |1004 | +-+ Thanks for any hint Kind regards Heiko Mundle -- +++ GMX - Mail, Messaging more http://www.gmx.net +++ NEU: Mit GMX ins Internet. Rund um die Uhr für 1 ct/ Min. surfen! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sql query using select and row functions
On Tue, 2003-01-28 at 12:26, Christopher Lyon wrote: I am trying to do an sql query and am trying to select the last x rows from the database. I see the limit function but that seems like that is from the first row down. I want to start from the last row to the first row. So, selecting the last 5 rows for instance? Can this be done? SELECT * FROM blah-blah ORDER BY whatever DESC LIMIT 0,5 -- __ / \\ @ __ __@ Adolfo Bello [EMAIL PROTECTED] / // // /\ / \\ // \ // Bello Ingenieria S.A, ICQ: 65910258 / \\ // / \\ / // // / //cel: +58 416 609-6213 /___// // / _/ \__\\ //__/ // fax: +58 212 952-6797 www.bisapi.com //pager: www.tun-tun.com (# 609-6213) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Access XP crashes regularly when linked to MySQL via MyODBC
Have you done all the office (or at least Access) XP updates? If not, it is possible that one of them my resolve your crash problem. I have been testing Access XP with the same versions of MyODBC and MySQL for a few weeks now and have yet to see a crash. About the only problem I have ran into is MySQL shutting down, for no apparent reason, every so often. I have just installed .55, so I'm waiting to see if it is still a problem. -- Loren McDonald [EMAIL PROTECTED] -Original Message- From: M Wells [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 28, 2003 10:19 AM To: [EMAIL PROTECTED] Subject: Access XP crashes regularly when linked to MySQL via MyODBC Hi All, My MySQL Server is 3.23.53-max-nt. I'm linking to it via MyODBC 3.51.05.00 and accessing its tables via Access XP linked tables. For some reason, Access XP crashes regularly in this setup. No meaningful error messages, just crashes and reloads after a repair. Even more odd (and I only have subjective observation to back this up), it appears to only crash when left unattended for a period of time. Although, maybe I have that impression because when left alone for a period of time equates to running long enough to have encountered a problem? Is this a known problem with MySQL 3.23.53-max-nt / MyODBC 3.51.05.00 / Access XP? Regards and best wishes, Murray Wells - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-mysql- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Configure prob with FreeBSD/Linuxthreads
On Tue, 28 Jan 2003, Jesse Sheidlower wrote: I've been trying to install MySQL 4.0.9 on FreeBSD 4.7, and have been getting stuck in the configure phase. I'd be grateful for any suggestions. I'm running FreeBSD 4.7 on a single-processor 1.4GHz PIII, using gcc 2.95.4, and trying to compile with Linuxthreads. I've been using the instructions Jeremy Zawodny posted in his blog at http://jeremy.zawodny.com/blog/archives/000458.html . I modified Jeremy's command to eliminate some of the obvious things I didn't need, and started with: Try this, this is what I used (I didn't need things like innodb, but needed static linkings): king as jdisher# more foo CFLAGS=-O3 -pipe -mcpu=pentiumpro -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -DHAVE_BROKEN_REALPATH -I/usr/local/include/pthread/li nuxthreads CXXFLAGS=-O3 -pipe -mcpu=pentiumpro -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -DHAVE_BROKEN_REALPATH -I/usr/local/incl ude/pthread/linuxthreads -felide-constructors -fno-rtti -fno-exceptions ./configure --with-mit-threads=no --enable-assembler --with out-innodb --with-named-thread-libs=-DHAVE_GLIBC2_STYLE_GETHOSTBYNAME_R -I/usr/local/include/pthread/linuxthreads -L/usr/local/lib -llthread -llgcc_r --enable-thread-safe-client --with-extra-charsets=none --without-debug --with-client-ldflags=-all-static --with- mysqld-ldflags=-all-static Put it all on one line, in a plaintext file, and sh filename. Sorry for the formatting nightmare. FYI, that successfully built 3.23.54 -AND- 4.0.9 with linuxthreads on FreeBSD 5-RELEASE. I'm still testing, however, as I'm not overly impressed with the performance increase (MySQL 3.23.54 on a single Athlon MP2200+ performs a job of ours faster than a dual Athlon MP2200+ (same hardware, SMP vs NonSMP kernel)). I dunno. -j - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php