Re: 4.0.2 Replication Bug...
On Sat, Mar 02, 2002 at 09:57:58PM -0800, Jeremy Zawodny wrote: Murphy's law strikes! Just a few hours ago I blasted the relay logs on my 4.0.2 slave. It ran out of disk space! I'll rsync the slave and build a fresh MySQL from the bitkeeper tree and let you know. Sasha, I re-synced my 4.x slave and updated with the latest BitKeeper code and compile-pentium-debug. It is chugging along well now, but I've noticed something odd. When the machine is pounding away on updates (over 300/sec), it can take a long time to get a response to SHOW SLAVE STATUS. I get one, but it can take between 5 and 30 seconds: ---snip--- mysql show slave status \G *** 1. row *** Master_Host: db.finance.yahoo.com Master_User: repl Master_Port: 3306 Connect_retry: 15 Master_Log_File: binary-log.042 Read_Master_Log_Pos: 211593316 Relay_Log_File: db3-relay-bin.001 Relay_Log_Pos: 288092868 Relay_Master_Log_File: binary-log.042 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 211155824 1 row in set (24.79 sec) ---snip--- Any ideas? This is on FreeBSD 4.3 if that matters. Thanks, Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 25 days, processed 867,644,812 queries (389/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: Info about MySQL drivers
Hi First of all this question belongs on the [EMAIL PROTECTED] mailing list. Secondly, you should not be sending this kind of a question to the MySQL developers Thirdly, the QMYSQL3 driver is part of the QtV3.0.x distribution. I presume you have the X-11 Free Edition of Qt, so run ./configure --help for a list of options. The compilation of the driver is a separate step At 12:19 05/03/2002 +0530, [EMAIL PROTECTED] wrote: hello!! I would like to know about the Database support with QT, specially MySQl, also from where can I download these drivers (QMYSQL3) Regds, Punita - 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 Regards John, Former MySQL Developer - [EMAIL PROTECTED] http://www.rygannon.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
InnoDB issues - tables not found
Hi, Newbie here, so please be kind... I decided to try out the foreign keys (REFERENCES tabel(column) in CREATE TABLE) and I hit two problems. Maybe it's my clumsiness with search specification, but I couldn't find answers in the archive. Oh, I tried it on InnoDB tables created just for this test. So here goes: First problem: SHOW CREATE does _not_ use the same syntax I did when creating tables - it omits REFERENCES... completely. This is not a big deal, I can just add those in the scripts if I need them, but it is somewhat worrisome; Second problem - and this is a big one - after I looked around in those tables, inserting, selecting and updating data, I disconnected from the DB, disconnected from the server and shut down the client. Until then I _could_ see the table structures, the fact they were InnoDB type, column definitions, and so on. I did also see free space and so on. When I came back after dinner and connected back... no such luck. Any attempt to get any rows out (like SELECT * FROM `table_name`) gives me: [FLOW11 as tomek] ERROR 1016: Can't open file: 'table_name.InnoDB'. (errno: 1) I can't see structure, number of rows, free space, table type, size, nothing. Logged out and in using different (sufficiently privileged) names - no cigar. Looked through the hostname.err log - nothing there, as well. Disconnected the client, shut down the MySQL - nothing seems wrong. It started back without complaint as well - but I still can't see my tables... I checked mysql database -everything is working as expected, things show up... In a way I hope it is a FAQ, in which case I would appreciate a gentle nudge. In case it's not, though, Is there something glaring I did/didn't do? MySQL is 4.0.1-alpha-log on Linux 2.2.17 installed from RPM downloaded from MySQL site. Here is how I created one of the tables in question (in a new database): CREATE TABLE `ALBUM` ( `Ix` int(11) NOT NULL auto_increment, `Title` varchar(255) binary default NULL, `Released` date default NULL, `Billing` int(11) REFERENCES PERFORMER(Ix), `Tracks` int(11) REFERENCES TRACK(Ix), `Remarks` blob, `Live` enum('Live','Studio','Both','Unknown') default 'Unknown', `Compilation` enum('Yes','No') default No, `RecordedFrom` date default NULL, `RecordedTo` date default NULL, `Company` varchar(255) binary default NULL, `ID` tinytext, PRIMARY KEY (`Ix`) ) TYPE=InnoDB; The rest of them were similar. Speak up, sages, I'm hanging on Your... well, fingers, in this case ;) -- 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
Never enabled LOCAL_INFILE
Description: 2.23.49 can not set local_infile to enable. We can not use LOAD DATA LOCAL INFILE ... ; configure script is almost strange '$enablewal' not '$enableval'. How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:Yukihiro KAWADA Organization: FUJI PHOTO FILM Co.,Ltd. Electronic Imaging Products Div. MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-3.23.49 (Source distribution) Server: mysqladmin Ver 8.23 Distrib 3.23.49, for sun-solaris2.7 on sparc 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.49 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 4 days 16 min 4 sec Threads: 1 Questions: 3691 Slow queries: 0 Opens: 13 Flush tables: 1 Open tables: 7 Queries per second avg: 0.011 Environment: System: SunOS funnel 5.7 Generic_106541-07 sun4u sparc SUNW,Ultra-1 Architecture: sun4 Some paths: /usr/gnu/bin/perl /usr/gnu/bin/make /usr/gnu/bin/gmake /usr/gnu/bin/gcc /usr/ucb/cc GCC: Reading specs from /usr/gnu/lib/gcc-lib/sparc-sun-solaris2.7/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' LIBC: -rw-r--r-- 1 bin bin 1693556 Oct 22 1999 /lib/libc.a lrwxrwxrwx 1 root root 11 Oct 25 1999 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1115304 Oct 22 1999 /lib/libc.so.1 -rw-r--r-- 1 bin bin 1693556 Oct 22 1999 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Oct 25 1999 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin bin 1115304 Oct 22 1999 /usr/lib/libc.so.1 Configure command: ./configure --with-unix-socket-path=/var/tmp/mysql.sock --with-low-memory --with-mit-threads=yes --without-perl --enable-thread-safe-client --with-berkeley-db --enable-local-infile --with-innodb Perl: This is perl, version 5.005_03 built for sun4-solaris - 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: InnoDB issues - tables not found
Tomasz, are you running on Windows? Please use innodb_table_monitor as explained in section 9.1 of http://www.innodb.com/ibman.html, and also look into section 15.1. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Tomasz Korycki [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Tuesday, March 05, 2002 10:30 AM Subject: InnoDB issues - tables not found Hi, Newbie here, so please be kind... I decided to try out the foreign keys (REFERENCES tabel(column) in CREATE TABLE) and I hit two problems. Maybe it's my clumsiness with search specification, but I couldn't find answers in the archive. Oh, I tried it on InnoDB tables created just for this test. So here goes: First problem: SHOW CREATE does _not_ use the same syntax I did when creating tables - it omits REFERENCES... completely. This is not a big deal, I can just add those in the scripts if I need them, but it is somewhat worrisome; Second problem - and this is a big one - after I looked around in those tables, inserting, selecting and updating data, I disconnected from the DB, disconnected from the server and shut down the client. Until then I _could_ see the table structures, the fact they were InnoDB type, column definitions, and so on. I did also see free space and so on. When I came back after dinner and connected back... no such luck. Any attempt to get any rows out (like SELECT * FROM `table_name`) gives me: [FLOW11 as tomek] ERROR 1016: Can't open file: 'table_name.InnoDB'. (errno: 1) I can't see structure, number of rows, free space, table type, size, nothing. Logged out and in using different (sufficiently privileged) names - no cigar. Looked through the hostname.err log - nothing there, as well. Disconnected the client, shut down the MySQL - nothing seems wrong. It started back without complaint as well - but I still can't see my tables... I checked mysql database -everything is working as expected, things show up... In a way I hope it is a FAQ, in which case I would appreciate a gentle nudge. In case it's not, though, Is there something glaring I did/didn't do? MySQL is 4.0.1-alpha-log on Linux 2.2.17 installed from RPM downloaded from MySQL site. Here is how I created one of the tables in question (in a new database): CREATE TABLE `ALBUM` ( `Ix` int(11) NOT NULL auto_increment, `Title` varchar(255) binary default NULL, `Released` date default NULL, `Billing` int(11) REFERENCES PERFORMER(Ix), `Tracks` int(11) REFERENCES TRACK(Ix), `Remarks` blob, `Live` enum('Live','Studio','Both','Unknown') default 'Unknown', `Compilation` enum('Yes','No') default No, `RecordedFrom` date default NULL, `RecordedTo` date default NULL, `Company` varchar(255) binary default NULL, `ID` tinytext, PRIMARY KEY (`Ix`) ) TYPE=InnoDB; The rest of them were similar. Speak up, sages, I'm hanging on Your... well, fingers, in this case ;) -- 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 - 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
mi_rkey function
Hello ... Is there anybody out there.., who can tell me how to create the parameteres for the mi_rkey function in the myisam library. I've tried to create the key by concatenating the key segments. Apparently that dosn't work. I get errcode 120,(Key not found ??). I'm trying to migrate from INFORMIX c-isam to MySQL. Besides: Have I understood the manual right, when I asume that descending key are not allowed(only ascending) ?? Klaus G. Weyhe JMA A/S - 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: LOAD DATA INFILE and how to ignore garbage lines at end ofload file?
Just use delimiters in Oracle... then use same delimiters on the import... simple any lines or blank spaces etc would be ignored. Also turn feedback off in Oracle so you don't get all the headers etc... -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: 04 March 2002 16:33 To: Richard Bolen; MySQL Mailing List (E-mail) Subject: Re: LOAD DATA INFILE and how to ignore garbage lines at end ofload file? At 10:58 -0500 3/4/02, Richard Bolen wrote: I'm exporting data from Oracle and importing it into MySQL. The problem is Oracle puts garbage lines at the end of it's output files. As you've noted, the problem is Oracle. If you're using Unix, you could use tail to see how many of these lines there are and wc to count the total number of lines in the file. With that information, you can construct the proper value of n and use head -n to get only the initial part of the file that contains the non-garbage lines. Or you could reverse the order of the lines in the file (expensive?) and then use IGNORE n LINES in your LOAD DATA statement to ignore the first n lines. Better if you can get Oracle just to suppress these lines in the first place. Perhaps someone else will have a suggestion how to do that. Lines like 300 rows selected and input truncated to 9 chars as well as empty lines. When MySQL loads these files, I'm getting rows inserted for the empty lines at the end of these files. Can I get MySQL to ignore empty lines at the end of these files? or if anyone has Oracle experience can I get it to suppress the output of these line? Thanks, Rich Rich Bolen Senior Software Developer GretagMacbeth Advanced Technologies Center 79 T. W. Alexander Drive - Bldg. 4401 - Suite 250 PO Box 14026 Research Triangle Park, North Carolina 27709-4026 USA Phone: 919-549-7575 x239, Fax: 919-549-0421 http://www.gretagmacbeth.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: InnoDB issues - tables not found
At 04:18 2002-03-05, you wrote: Tomasz, are you running on Windows? No, Linux 2.2.17 Please use innodb_table_monitor as explained in section 9.1 of http://www.innodb.com/ibman.html, and also look into section 15.1. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ Speed up adding of features to MySQL/InnoDB through support contracts See http://www.innodb.com for the online manual and latest news on InnoDB -Original Message- From: Tomasz Korycki [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Date: Tuesday, March 05, 2002 10:30 AM Subject: InnoDB issues - tables not found Hi, Newbie here, so please be kind... I decided to try out the foreign keys (REFERENCES tabel(column) in CREATE TABLE) and I hit two problems. Maybe it's my clumsiness with search specification, but I couldn't find answers in the archive. Oh, I tried it on InnoDB tables created just for this test. So here goes: First problem: SHOW CREATE does _not_ use the same syntax I did when creating tables - it omits REFERENCES... completely. This is not a big deal, I can just add those in the scripts if I need them, but it is somewhat worrisome; Second problem - and this is a big one - after I looked around in those tables, inserting, selecting and updating data, I disconnected from the DB, disconnected from the server and shut down the client. Until then I _could_ see the table structures, the fact they were InnoDB type, column definitions, and so on. I did also see free space and so on. When I came back after dinner and connected back... no such luck. Any attempt to get any rows out (like SELECT * FROM `table_name`) gives me: [FLOW11 as tomek] ERROR 1016: Can't open file: 'table_name.InnoDB'. (errno: 1) I can't see structure, number of rows, free space, table type, size, nothing. Logged out and in using different (sufficiently privileged) names - no cigar. Looked through the hostname.err log - nothing there, as well. Disconnected the client, shut down the MySQL - nothing seems wrong. It started back without complaint as well - but I still can't see my tables... I checked mysql database -everything is working as expected, things show up... In a way I hope it is a FAQ, in which case I would appreciate a gentle nudge. In case it's not, though, Is there something glaring I did/didn't do? MySQL is 4.0.1-alpha-log on Linux 2.2.17 installed from RPM downloaded from MySQL site. Here is how I created one of the tables in question (in a new database): CREATE TABLE `ALBUM` ( `Ix` int(11) NOT NULL auto_increment, `Title` varchar(255) binary default NULL, `Released` date default NULL, `Billing` int(11) REFERENCES PERFORMER(Ix), `Tracks` int(11) REFERENCES TRACK(Ix), `Remarks` blob, `Live` enum('Live','Studio','Both','Unknown') default 'Unknown', `Compilation` enum('Yes','No') default No, `RecordedFrom` date default NULL, `RecordedTo` date default NULL, `Company` varchar(255) binary default NULL, `ID` tinytext, PRIMARY KEY (`Ix`) ) TYPE=InnoDB; The rest of them were similar. Speak up, sages, I'm hanging on Your... well, fingers, in this case ;) -- 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 -- 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: date format
Hi, My opinion: You can create char field type on table (i.e. char(10)) and when you wish to do some date comparison you can use the next mySQL functions:LEFT,MID,RIGHT,TRIM (is not only in VB) I.E: mydate-field in a_table:char(10); date on this field: '10.01.2001','30.01.2000',...etc. select * from a_table where RIGHT(mydate,4)'2002'; select * from a_table where MID(mydate,4,2)'01'; ...etc Good luck, Gelu G.NET SOFTWARE COMPANY - Original Message - From: razvan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 05, 2002 7:46 AM Subject: date format Hi, please help me with this one! I now the date_format function, it converts the standard format sql (-mm-dd) to any format, but my problem is that i want to convert from another format (eg. dd/mm/) to the standard format, in this way i can use insert with my format. Can I use another format for date when creating a table? thanks! _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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: InnoDB issues - tables not found
At 04:18 2002-03-05, you wrote: Tomasz, are you running on Windows? Please use innodb_table_monitor as explained in section 9.1 of http://www.innodb.com/ibman.html, and also look into section 15.1. Best regards, Re: 9.1: Well, can't find innodb* anywhere on the system Re: 15.1: The second problem seems to be mine, the one about orphaned' .frm files. That would seem to account for me seeing a tables list, but nothing about them. Obviously, the .frm files did exist. Couldn't drop the tables or the database (Unknown table ...,...,...). Let me repeat: everything was just fine until I quit mysql. When I logged back in - with the same command, I use ksh and 'set -o vi' - that's when the issues started. I got out of this trouble by shutting down the server, manually removing all InnoDB files (including .frm), then starting it again - which created the IDB files. I then used my script to create database and all the tables. And everything was fine until I logged out. I now logged back in and everything is still OK: I can desc tables, and so on. Just one issue: when I do SHOW CREATE ..., it still doesn't show any REFERENCES ... that I had when I created the tables. Either I'm exceptionally thick, or the docs really say that InnoDB has the capability to use the foreign keys... I took special care to reference only tables that already existed, in case it's a concern. Any idea? Also about how long will the tables stay healthy? I don't care about those 5 records I put in each, but to really test, I need to put a bit more... snip! THX! -- 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
Japanese shift-JIS messages
Didn't see anything on this specific question in a quick search of the database. If I just missed it, I apologize. What I have in mind is being able to read the Japanese error messages on an MSWindows box. So I wanted to make a mysql\share\japanese_sjis directory and save the two errmsg files in it in shift-jis. I had no problems loading errmsg.txt into a text editor as euc and saving as shift-jis. But errmsg.sys is not text. Just for fun, I tried using my text editor to filter that, too, but mysql of course refuses to start up with that specified in my my.ini file. Maybe I could re-compile something? Or run a filter on something? If this might be possible, I'd appreciate a pointer in the right direction. Joel Rees Alps Giken Kansai Systems Develoment Suita, Osaka - 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
num max fieds in table in mysql database
Hi What is the number max of fields in a table in mysql database ? Samuel - 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: mi_rkey function
Hi! On Mar 05, Klaus Weyhe wrote: Hello ... Is there anybody out there.., who can tell me how to create the parameteres for the mi_rkey function in the myisam library. I've tried to create the key by concatenating the key segments. Apparently that dosn't work. I get errcode 120,(Key not found ??). I'm trying to migrate from INFORMIX c-isam to MySQL. Besides: Have I understood the manual right, when I asume that descending key are not allowed(only ascending) ?? Several points - 1. you'd better send this email to [EMAIL PROTECTED], it was created for questions like this 2. take a look at mi_test?.c examples - there're multi-seg keys there. 3. descending keys are allowed. There's flag HA_REVERSE_SORT 4. To migrate from c-isam there's no need to use MyISAM functions directly. Take a look at MySQL HANDLER command - it provides direct access to low-level isam functions, bypassing SQL optimizer. You'll get client-server architecture, and - most probably - higher performance with it (due to cache issues). Btw, this HANDLER comand was created precisely to simplify c-isam - MySQL migration. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - 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: storing files in mySQL
Why not rather have pointers to the files on a directory and store this in a db field ? Saves you a lot of DB space ? Ant -Original Message- From: Dang Nguyen [mailto:[EMAIL PROTECTED]] Sent: 04 March 2002 09:05 To: Mysql List (E-mail) Subject: storing files in mySQL Hi everyone, I'd like to know how to load files, such as MS-Word documents or PDF documents, in a mySQL database. I've setup a blob type in a table, but where do I go from there? The purpose of this is to store files uploaded from a web page and processed with Java servlets. Then, the files should be retrievable (displayed or downloaded) back to a client browser. My environment: Apache Web server 1.3.x on Solaris 2.8 with Java servlets environment. Thanks for any help, Dang Nguyen - 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
BETWEEN... LIKE query
Hi, I have a form with two fields surname_from and surname_to, I want to be able to do a select query where the results will lie between these to values. That's easy enough but I also want to include all results where the record field (surname) matches the first part of the fields - in effect a BETWEEN query incorporating the LIKE function. For example select * from consumers surname BETWEEN '$surname_from' AND '$surname_to' assume $surname_from = A and $surname_to = B would only return results where the surname is A or greater but less than B, but I would like to include all records where records also start with B. For example it would return A Allan Anderson Appleby B But I want it to return A Allan Anderson Appleby B Black Brown Butterworth Has anyone any ideas? Thanks in anticipation Craig Shepherd - 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: extra max() function possibly very useful?
Hi! Richard == Richard Clarke [EMAIL PROTECTED] writes: Richard Because that wouldn't give the correct results. Richard I want the top 5 rows for EACH id. Richard A short example would be, Richard mytable: Richard IdValHits Richard 1 a 10 Richard 1 b 15 Richard 1 c 17 Richard 2 q 200 Richard 2 r 205 Richard 2 s 101 Richard 2 t 50 Richard 3 zz 10 Richard 3 yy 20 Richard 3 xx 30 Richard 3 ww 40 Richard 3 uu 50 Richard select max(2,hits) from mytable Richard Id Richard 1 b 15 Richard 1 c 17 Richard 2 q 200 Richard 2 r 205 Richard 3 ww 40 Richard 3 uu 50 Richard This is the top two rows for EACH id... Richard without this functionality i have to do Richard select distinct cid from mytable; Richard foreach(cid) { Richard select * from mytable where cid='$cid' order by hits desc limit 2; Richard } You may be able to use something like the following (not tested) trick to do this: SET @b=0; SELECT *,(@a:= IF(@b=id,@a+1,1)) as cnt, @b:=id from mytable order by id having cnt5; Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ 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: BETWEEN... LIKE query
* Craig Shepherd select * from consumers surname BETWEEN '$surname_from' AND '$surname_to' assume $surname_from = A and $surname_to = B would only return results where the surname is A or greater but less than B, but I would like to include all records where records also start with B. Append a high ascii value to the $surname_to, like '{', so that your query will be: select * from consumers surname BETWEEN 'A' AND 'B{' -- Roger - 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: BETWEEN... LIKE query
So if I did something like $temp = $surname_from.{; select * from consumers surname BETWEEN '$surname_from' AND '$temp' it should do the trick? -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED]] Sent: 05 March 2002 11:49 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: BETWEEN... LIKE query * Craig Shepherd select * from consumers surname BETWEEN '$surname_from' AND '$surname_to' assume $surname_from = A and $surname_to = B would only return results where the surname is A or greater but less than B, but I would like to include all records where records also start with B. Append a high ascii value to the $surname_to, like '{', so that your query will be: select * from consumers surname BETWEEN 'A' AND 'B{' -- Roger - 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
How to convert Database ?
Dear sir, I use MySQL-3.23.35 before that running on FreeBSD-4.2. Now I use MySQL-4.0.1 running on FreeBSD-4.5. I want to convert Database ... from MySQL-3.23.35 that store at /usr/local/mysql/data to MySQL-4.0.1 that store at /usr/local/mysql/var. Anyone have tools or have another method to convert it ? Please let me know more detail. thank you very much. regards, Somsak. - 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: BETWEEN... LIKE query
Why you don't try: BETWEEN '$surname_from' AND concat($surname_to,'z') ? Makis -Original Message- From: Craig Shepherd [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 1:30 PM To: [EMAIL PROTECTED] Subject: BETWEEN... LIKE query Hi, I have a form with two fields surname_from and surname_to, I want to be able to do a select query where the results will lie between these to values. That's easy enough but I also want to include all results where the record field (surname) matches the first part of the fields - in effect a BETWEEN query incorporating the LIKE function. For example select * from consumers surname BETWEEN '$surname_from' AND '$surname_to' assume $surname_from = A and $surname_to = B would only return results where the surname is A or greater but less than B, but I would like to include all records where records also start with B. For example it would return A Allan Anderson Appleby B But I want it to return A Allan Anderson Appleby B Black Brown Butterworth Has anyone any ideas? Thanks in anticipation Craig Shepherd - 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: InnoDB frightens me...
Hi! Tobias Hi! Tobias I'm in the same position as Steve: Considering switching from MyISAM to InnoDB because of the Tobias row-level locking capabilities. My application has quite a lot of updates/inserts mixed with Tobias selects, and is starting to suffer from the table-locking policy... Tobias And like Steve, I'm also scared! :) cut Tobias Maybe some of this confusion and fear could be avoided with a section in the InnoDB documentation Tobias that describes InnoDB from a MyISAM-point-of-view :) Tobias Explaining how all common things, principles and routines with MyISAM works with InnoDB. I'm sure a Tobias lot of people have very good knowledge of MyISAM and comes from the same direction as I do... I have forwarded this to our docmentation team and we shall try to do this in the near future. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ 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: SQL_BUFFER_RESULT and SQL_CALC_FOUND_ROWS in same SELECT broken
[EMAIL PROTECTED] writes: Description: *** against BK as of set 1.1144 *** It appears that putting SQL_BUFFER_RESULT and SQL_CALC_FOUND_ROWS in the same SELECT statement is a really bad idea! SELECT FOUND_ROWS() after SELECT SQL_BUFFER_RESULT SQL_CALC_FOUND_ROWS either returns a huge number (too big) or zero. Removing SQL_BUFFER_RESULT makes FOUND_ROWS() return the correct answer. How-To-Repeat: mysql SELECT SQL_BUFFER_RESULT SQL_CALC_FOUND_ROWS * FROM Downloads WHERE (match (Name) against ('+fokker' in boolean mode) or match (Description) against ('+fokker' in boolean mode)) ORDER BY Name LIMIT 0, 10; normal, correct output Hi! Thank you for your bug report. Here comes a temporary patch that fixes that problem. Final patch will come in 4.0.2 : = sql/sql_select.cc 1.154 vs edited = *** /tmp/sql_select.cc-1.154-19781 Fri Mar 1 18:58:05 2002 --- edited/sql/sql_select.ccTue Mar 5 13:45:29 2002 *** *** 187,193 TABLE *tmp_table; int error, tmp_error; boolneed_tmp,hidden_group_fields; ! boolsimple_order,simple_group,no_order, skip_sort_order; Item::cond_result cond_value; SQL_SELECT *select; DYNAMIC_ARRAY keyuse; --- 187,193 TABLE *tmp_table; int error, tmp_error; boolneed_tmp,hidden_group_fields; ! boolsimple_order,simple_group,no_order, skip_sort_order, buffer_result; Item::cond_result cond_value; SQL_SELECT *select; DYNAMIC_ARRAY keyuse; *** *** 201,206 --- 201,207 /* Check that all tables, fields, conds and order are ok */ select_distinct=test(select_options SELECT_DISTINCT); + buffer_result=test(select_options OPTION_BUFFER_RESULT) !test(select_options + OPTION_FOUND_ROWS); tmp_table=0; select=0; no_order=skip_sort_order=0; *** *** 546,553 need_tmp= (join.const_tables != join.tables ((select_distinct || !simple_order || !simple_group) || ! (group order) || ! test(select_options OPTION_BUFFER_RESULT))); // No cache for MATCH make_join_readinfo(join, --- 547,553 need_tmp= (join.const_tables != join.tables ((select_distinct || !simple_order || !simple_group) || ! (group order) || buffer_result)); // No cache for MATCH make_join_readinfo(join, -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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
Newbie can't start mysql - PLEASE HELP
I think I did something wrong. It was working OK (not great, but OK). Now I can't start mysqld. (Error 2002 can't connect to MySQL server through socket /var/lib...). I don't know if this matters, but for some reason I deleted a .pid file that I thought was making me not able to start mysqld and now I can't do anything. When I try to just enter mysql I get a cannot access message. PLEASE HELP - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB frightens me...
Thanks - that would be great! I'm sure a lot of people will find this very helpful. Regards, Tobias - Original Message - From: Michael Widenius [EMAIL PROTECTED] To: Tobias Lind - Telia Internet [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, March 05, 2002 12:36 PM Subject: Re: InnoDB frightens me... Hi! Tobias Hi! Tobias I'm in the same position as Steve: Considering switching from MyISAM to InnoDB because of the Tobias row-level locking capabilities. My application has quite a lot of updates/inserts mixed with Tobias selects, and is starting to suffer from the table-locking policy... Tobias And like Steve, I'm also scared! :) cut Tobias Maybe some of this confusion and fear could be avoided with a section in the InnoDB documentation Tobias that describes InnoDB from a MyISAM-point-of-view :) Tobias Explaining how all common things, principles and routines with MyISAM works with InnoDB. I'm sure a Tobias lot of people have very good knowledge of MyISAM and comes from the same direction as I do... I have forwarded this to our docmentation team and we shall try to do this in the near future. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ 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: LOAD DATA LOCAL INFILE in Alpha 3.23.49]
Thomas Birchmire writes: I have the same problem using Linux RedHat 7.2. Just what do I have to configure my binary MySQL : Ver 8.23 Distrib 3.23.49a, for pc-linux-gnu on i686? The rest of MySQL works as advertised. Regards Tom Birchmire loca-infile is not a variable. Therefore, you either start a server with --local-infile or have in your /etc/my.cnf : [mysqld] local-infile -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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: Deleting duplicate records
That works fine, but I was wondering if there was another way to do it because I receive one text file everyday and the are always duplicate records. We just change to MySQL and I have to load this text file everyday to the database. Thanks again!! Nato -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Monday, March 04, 2002 4:32 PM To: Natividad Castro; [EMAIL PROTECTED] Subject: RE: Deleting duplicate records At 16:29 -0500 3/4/02, Natividad Castro wrote: Thank you very much!! that was very helpful. By the way, is there any other way to get rid of duplicate records from a text file? What's wrong with the command Jeremy suggested? Thanks again Nato -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Monday, March 04, 2002 3:00 PM To: Natividad Castro Cc: [EMAIL PROTECTED] Subject: Re: Deleting duplicate records On Mon, Mar 04, 2002 at 02:48:06PM -0500, Natividad Castro wrote: I'm new using mysql. I'm trying to load data from a text file to one of my table. This text file contains duplicate records. My question is how do you tell mysql to delete duplicate records and load just the ones that are not duplicate? I already tried the REPLACE and IGNORE key words but is I dont get the result that I want. Any idea If the data is in a text file, elimiate the duplicates before MySQL is even involved: cat myfile.txt | sort | uniq nodups.txt Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 25 days, processed 842,341,115 queries (386/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
mysql: relocation error: mysql: undefined symbol: ctype_latin1
Hi! I played with the locales, and mysql console # mysql returns with: mysql: relocation error: mysql: undefined symbol: ctype_latin1 What did I miss? I'm in need of resolving this problem fast, a whole site depends on it! asd - 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
Why v3.23.49 does not build on RedHat 6.2 ?????
Hi everybody... I've searched thrugh the lists (IMHO the search engine is slow and the output sucks: what about a date column at the beginning of each row in the post list and in search results?) and the manual. This is the 3rd time I post about this problems but it looks like nobody can give me an answer ... I wonder if it is possible at all to build rpm MySQL 3.23.49 binaries for RedHat 6.2. Is it possible? I've not been able to build rpm for my platform on three different machines. I've tried to rpm --rebuild MySQL-3.23.49-1.src.rpm After many lines of compiling log I get the following: [... snip ...] lo dbug.lo -lz -lcrypt -lnsl -lm rm -fr .libs/libmysqlclient.la .libs/libmysqlclient.* .libs/libmysqlclient.* ar cru .libs/libmysqlclient.a libmysql.o net.o violite.o password.o get_password.o errmsg.o my_init.o my_static.o my_malloc.o my_realloc.o my_create.o my_delete.o mf_tempfile.o my_open.o mf_casecnv.o my_read.o my_write.o errors.o my_error.o my_getwd.o my_div.o mf_pack.o my_messnc.o mf_dirname.o mf_fn_ext.o mf_wcomp.o typelib.o safemalloc.o my_alloc.o mf_format.o mf_path.o mf_unixpath.o my_fopen.o my_symlink.o my_fstream.o mf_loadpath.o my_pthread.o my_thr_init.o thr_mutex.o mulalloc.o string.o default.o my_compress.o array.o my_once.o list.o my_net.o charset.o hash.o mf_iocache.o my_seek.o my_pread.o mf_cache.o getopt.o getopt1.o getvar.o my_lib.o strmov.o strxmov.o strnmov.o strmake.o strend.o strnlen.o strfill.o is_prefix.o int2str.o str2int.o strinstr.o strcont.o strcend.o bchange.o bmove.o bmove_upp.o longlong2str.o strtoull.o strtoll.o llstr.o ctype.o ctype-big5.o ctype-czech.o ctype-euc_kr.o ctype-gb2312.o ctype-gbk.o ctype-sjis.o ctype-tis620.o ctype-ujis.o dbug.o ranlib .libs/libmysqlclient.a creating libmysqlclient.la (cd .libs rm -f libmysqlclient.la ln -s ../libmysqlclient.la libmysqlclient.la) make[2]: Leaving directory `/usr/src/redhat/BUILD/mysql-3.23.49/libmysql' Making all in client make[2]: Entering directory `/usr/src/redhat/BUILD/mysql-3.23.49/client' gcc -DUNDEF_THREADS_HACK -I./../include -I../include -I./.. -I.. -I..-O3 -DDBUG_OFF -O3-felide-constructors -fno-exceptions -fno-rtti -fno-implicit-templates -fno-exceptions -fno-rtti -fpermis sive -I/usr/local/mysql-glibc/include -c mysql.cc In file included from mysql.cc:48: /usr/include/curses.h:200: warning: `ERR' redefined /usr/include/sys/ucontext.h:74: warning: this is the location of the previous definition cc1plus: Invalid option `-fpermissive' In file included from client_priv.h:19, from mysql.cc:28: ../include/global.h:685: abstract declarator used as declaration make[2]: *** [mysql.o] Error 1 make[2]: Leaving directory `/usr/src/redhat/BUILD/mysql-3.23.49/client' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/src/redhat/BUILD/mysql-3.23.49' make: *** [all-recursive-am] Error 2 error: Bad exit status from /var/tmp/rpm-tmp.7029 (%build) RPM build errors: Bad exit status from /var/tmp/rpm-tmp.7029 (%build) What's wrong? --- Gabriele Carioli Management Innovative Tools S.p.A. Via Giuditta Tavani Arquati, 6 47100 Forlì (FC) - ITALY (EU) tel. 0039.0543.21834 fax. 0039.0543.379215 http://www.mit.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
Bug with multi-table DELETE
Hello, I noticed a bug today in multi-table delete which corrupts my tables and indices. In the 4.0.2 changelog in the online-manual I saw a reference to a bugfix for multi-table deletes, but since I can't yet download a binary of 4.0.2 for windows I can't yet check if this is the fix for 'my' bug. I haven't yet made a comprehensive testcase for the bug, but here's the query: DELETE document_headers FROM document_headers, document_contents WHERE document_headers.did = document_contents.did AND document_headers.dcid = document_contents.dcid AND document_contents.did = %(did)s AND document_contents.version = %(version)s The error is: ERROR 1034: Incorrect key file for table: 'document_headers'. Try to repair it Before the delete-statement the table works; after the delete it needs to be repaired. After I repair it and retry the query, the same error returns. The error seems to occurs only when there actually are rows to delete; when there's nothing to delete there's no error. In attempting to repair the table I got the following messages: C:\mysql\data\linksdbmyisamchk document_headers Checking MyISAM file: document_headers Data records: 11 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 myisamchk: error: Found 10 keys of 11 - check record links myisamchk: error: Keypointers and record positions doesn't match MyISAM-table 'document_headers' is corrupted Fix it using switch -r or -o C:\mysql\data\linksdbmyisamchk -r document_headers - recovering (with sort) MyISAM-table 'document_headers' Data records: 11 - Fixing index 1 - Fixing index 2 myisamchk: Error on delete of 'document_headers.MYD' (Errcode: 13) myisamchk: error: 13 when fixing table MyISAM-table 'document_headers' is not fixed because of errors Try fixing it by using the --safe-recover (-o) or the --force (-f) option C:\mysql\data\linksdbmyisamchk -o document_headers - recovering (with keycache) MyISAM-table 'document_headers' Data records: 11 myisamchk: error: Can't create new tempfile: 'document_headers.TMD' MyISAM-table 'document_headers' is not fixed because of errors Try fixing it by using the --safe-recover (-o) or the --force (-f) option C:\mysql\data\linksdbmyisamchk -r document_headers - recovering (with sort) MyISAM-table 'document_headers' Data records: 11 myisamchk: error: Can't create new tempfile: 'document_headers.TMD' MyISAM-table 'document_headers' is not fixed because of errors Try fixing it by using the --safe-recover (-o) or the --force (-f) option C:\mysql\data\linksdbmyisamchk -o document_headers - recovering (with keycache) MyISAM-table 'document_headers' Data records: 11 myisamchk: error: Can't create new tempfile: 'document_headers.TMD' MyISAM-table 'document_headers' is not fixed because of errors Try fixing it by using the --safe-recover (-o) or the --force (-f) option C:\mysql\data\linksdbmyisamchk -f document_headers Checking MyISAM file: document_headers Data records: 11 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed and last repair failed - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check record links - recovering (with sort) MyISAM-table 'document_headers' Data records: 11 - Fixing index 1 - Fixing index 2 (The %(did)s and %(version)s things are python string-interpolations, similar to %s in c printf. The python MySQLdb library fills in the proper values. When I run the query in the mysql-client with proper values filled in it gives the same error.) Does this ring a bell? Is this the same problem as which is fixed in 4.0.2? Or do you want me to submit more data on this problem? With regards, --Tim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how can I use 'C' programs accessing a MySql
The following form was submitted via MySQL.com feedback engine: Name: Alexandre Adam E-mail address: [EMAIL PROTECTED] Company: sansoft informatica Position/Title: user Type of interest: for personal use State or province: SP Country: Brazil Support contract: no Enter Your Questions, Comments, Feedback or Your Message here: I'm trying to understand how can I use 'C' programs accessing a MySql DataBase, please, could you send me a small program or so that creates a Table or two and make some changes on them. I read so many examples but all of them only contain parts of the code. please... help...what library I must include... Sincerely Alexandre Adam Sao Paulo - SP - BRAZIL I wrote some code but nothing works... please read it below. #include /usr/include/mysql/mysql.h # include stream.h int main () { { Printf (Initializing...\n); } if (mysql_create_db (mysql, DB_TEST)) { fprintf (stderr, Failed to create DataBase, ERR! %x\n, mysql_error( mysql)); } return 0; } - 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: Never enabled LOCAL_INFILE
[EMAIL PROTECTED] writes: Description: 2.23.49 can not set local_infile to enable. We can not use LOAD DATA LOCAL INFILE ... ; configure script is almost strange '$enablewal' not '$enableval'. How-To-Repeat: Hi! Thank you for your report. We have fixed a bug and fix will come in 3.23.50. The problem is that in configure.in we forgot to add: AC_SUBST(ENABLE_LOCAL_INFILE) We have now fixed this in the MySQL 3.23 source tree.. The fix is to add [client] local-infile=1 to /etc/my.cnf. If it is not secret, what are you using MySQL for at Fuji Film ?? -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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
Proxy server problems
Hi I have recently had some problems with web forms not updating the website ( use PHP and the insert page should first enter the form data into a table and then update the status field in a second table correlating to the entry just made), hoever, only the first sql seemed to go through and the 2nd table never got the updated status field. It bothered me as when I test the system it worked 100%, yet some customers complained about the fact that their entries are not registered. Then I noticed that one of them had a proxy server, and it started me thinking that that could be the possible problem. So, my question is; can an outdated proxy wreak havoc with my mysql/php pages, and if so, is there a way that I can force a webpage NOT to go through a proxy? The solution will have to be software and server side based as I cannot rely on the cooperation of the end user for a solution. I know the solution probably has more to do with PHP, but I guess the PHP guru's will frequent this list as well??? - 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
[AUTH] MySQL authentication using LDAP.
Hello all, I have a weird request. Having lots of servers (with many MySQL ones), I try to centralize most of all authentication processes by using LDAP. This is possible with Apache, ProFTPd and such but I was wondering if we could do this with MySQL. I know this is special because this is generaly the contrary we want : being able to authenticate thru a MySQL server. I dont really know how this could be done. I just want MySQL to send the login and password to an LDAP server, this one responding 'yes' or 'no'. Maybe it could be possible/better to export all the mysql database... I even thought of a MySQL pam module which could permit /etc/passwd to be the authentication source. I could reach my needs by next using nsswitch-ldap. All suggestions/ideas are welcomed even if you find this idea completely stupid (which is probably true). Thanx ! Yann. - 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: [AUTH] MySQL authentication using LDAP.
No quite the same thing but We use Radius Radiator with MySQL and it works very well. Hope to have all the Cisco stuff getting its password from hear to soon... No direct connect but close? Simon -Original Message- From: Yann Michaud [mailto:[EMAIL PROTECTED]] Sent: 05 March 2002 13:56 To: [EMAIL PROTECTED] Subject: [AUTH] MySQL authentication using LDAP. Hello all, I have a weird request. Having lots of servers (with many MySQL ones), I try to centralize most of all authentication processes by using LDAP. This is possible with Apache, ProFTPd and such but I was wondering if we could do this with MySQL. I know this is special because this is generaly the contrary we want : being able to authenticate thru a MySQL server. I dont really know how this could be done. I just want MySQL to send the login and password to an LDAP server, this one responding 'yes' or 'no'. Maybe it could be possible/better to export all the mysql database... I even thought of a MySQL pam module which could permit /etc/passwd to be the authentication source. I could reach my needs by next using nsswitch-ldap. All suggestions/ideas are welcomed even if you find this idea completely stupid (which is probably true). Thanx ! Yann. - 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: storing files in mySQL
This is how we did it with PHP Remove the mysql packet size limitation. By default, mysql only accepts packets that are smaller than 1 MB. You have to restart your database with some parameters, which will remove the limitations. /usr/local/bin/safe_mysqld -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O record_buffer=1M -O max_allowed_packet=24M If you are using Unix, check out your init-tree and change the corresponding startup file. See this page for more detail... http://www.phpbuilder.com/columns/florian19991014.php3?page=2 Patrick Egan Egan Consulting 15 Elvina Gardens Toronto, Ontario M4P 1X7 T] 416 630 4982 F] 416 488 7187 C] 416 726 4832 Pager] 416 377 9031 E] [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] W] www.eganconsulting.com http://www.eganconsulting.com -Original Message- From: Anton [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 6:13 AM To: 'Mysql List (E-mail)' Subject: RE: storing files in mySQL Why not rather have pointers to the files on a directory and store this in a db field ? Saves you a lot of DB space ? Ant -Original Message- From: Dang Nguyen [mailto:[EMAIL PROTECTED]] Sent: 04 March 2002 09:05 To: Mysql List (E-mail) Subject: storing files in mySQL Hi everyone, I'd like to know how to load files, such as MS-Word documents or PDF documents, in a mySQL database. I've setup a blob type in a table, but where do I go from there? The purpose of this is to store files uploaded from a web page and processed with Java servlets. Then, the files should be retrievable (displayed or downloaded) back to a client browser. My environment: Apache Web server 1.3.x on Solaris 2.8 with Java servlets environment. Thanks for any help, Dang Nguyen - 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: Why v3.23.49 does not build on RedHat 6.2 ?????
hi, the source rpm for mysql 3.23.49 contains build instructions for gnu compilers 2.95 or newer. redhat 6.2 comes with a compiler that cannot handle the permissive flag (i think its ecgs-2.91). therefore the build fails. one solution could be to install a more recent compiler version and try to recompile again. be aware that actual rpm's will be created by rpm version 4, whereas redhat 6.2 has rpm version 3 installed which possibly cannot handle version-4-rpm's (although there had been a rpm-3 version which could _read_ rpm-4 packages - i think it was 3.0.5.) besides that, there may be glibc issues. redhat 6.2 has glibc 2.1.3 installed. i don't know if actual gcc or mysql versions work ok if compiled against this 'old' glibc. afaik, redhat does _not_ recommend to update the glibc version installed... if you dont have very specific reasons to stick with redhat 6.2 in my opinion it could be simpler just to install a later redhat version... not sure if this really helps... bye michael Gabriele Carioli wrote: Hi everybody... I've searched thrugh the lists (IMHO the search engine is slow and the output sucks: what about a date column at the beginning of each row in the post list and in search results?) and the manual. This is the 3rd time I post about this problems but it looks like nobody can give me an answer ... I wonder if it is possible at all to build rpm MySQL 3.23.49 binaries for RedHat 6.2. Is it possible? I've not been able to build rpm for my platform on three different machines. I've tried to rpm --rebuild MySQL-3.23.49-1.src.rpm - 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
data from service into MySQL
I'm retrieving data from a data service. This is the ENTIRE source code of the URL they let me receive: symbolRHAT/symbol price6.82/price date3/4/2002/date time3:59pm/time change0.32/change change_percent/change_percent open6.59/open high6.99/high low6.53/low volume932400/volume How can I insert the data values into fields in MySQL ? Thanks Craig [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: storing files in mySQL
On Mon, 4 Mar 2002, Dang Nguyen wrote: I'd like to know how to load files, such as MS-Word documents or PDF documents, in a mySQL database. I've setup a blob type in a table, but where do I go from there? The purpose of this is to store files uploaded from a web page and processed with Java servlets. Then, the files should be retrievable (displayed or downloaded) back to a client browser. My environment: Apache Web server 1.3.x on Solaris 2.8 with Java servlets environment. MS-Word and PDF files are essentially binary files (very much like images). I suggest leaving them as they are in a separate directory. These MS-Word and PDF files may be converted to plain ASCII using filters (mswordview, pdftotext). The resulting ASCII versions are then loaded into a table with one column of type TEXT along with a VARCHAR pointer to the filename. Create a fulltext index on the TEXT field. A fulltext search for some keywords will return a sorted list of filenames. These files can be returned to the client. This scenario works well for me on a collection of some 15'000 HTML documents (I used w3m as a filter to convert to ASCII). All conversions are done with a simple shell script calling the filters. Thomas Spahni - 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: Bug with multi-table DELETE
Leeuw van der, Tim writes: Hello, I noticed a bug today in multi-table delete which corrupts my tables and indices. In the 4.0.2 changelog in the online-manual I saw a reference to a bugfix for multi-table deletes, but since I can't yet download a binary of 4.0.2 for windows I can't yet check if this is the fix for 'my' bug. I haven't yet made a comprehensive testcase for the bug, but here's the query: DELETE document_headers FROM document_headers, document_contents WHERE document_headers.did = document_contents.did AND document_headers.dcid = document_contents.dcid AND document_contents.did = %(did)s AND document_contents.version = %(version)s The error is: ERROR 1034: Incorrect key file for table: 'document_headers'. Try to repair it Hi! Are you using 4.0.1 ??? If that is so, then the above bug was fixed and will appear in 4.0.2. 4.0.2 should be release in two weeks. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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
very tough query
ok, here are the 3 tables i have that are related: mysql desc poll_questions; ++-+--+-++-- --+ | Field | Type| Null | Key | Default| Extra | ++-+--+-++-- --+ | poll_id| int(9) unsigned | | PRI | NULL | auto_increment | | aim_screenname | varchar(16) | | MUL || | | question | varchar(255)| | || | | date_created | date| | | -00-00 | | ++-+--+-++-- --+ mysql desc poll_answers; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | answer_id | int(12) unsigned | | PRI | NULL| auto_increment | | poll_id | int(9) unsigned | | | 0 || | answer| varchar(255) | | | || +---+--+--+-+-++ mysql desc poll_votes; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | aim_screenname | varchar(16) | | PRI | | | | answer_id | int(12) unsigned | | PRI | 0 | | | time_voted | timestamp(12)| YES | | NULL| | | voter_ip | varchar(15) | | | | | ++--+--+-+-+---+ I'm pretty sure this is completely normalized. I have no problem joining the 3 tables to do SELECTS...but what happens when I want to delete a poll? I can easily elete from the poll_questions and poll_answers tables, but how do I delete the necessary rows in the poll_votes table? I tried doing a DELETE query just like a SELECT, but it didn't work: DELETE FROM poll_votes AS v, poll_answers AS a, poll_questions AS q WHERE (q.poll_id = 1) AND (v.answer_id = a.answer_id) AND (a.poll_id = q.poll_id); Please help! 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: Many processes in SHOW PROCESSLIST;
At 10:24 PM 3/4/2002 , you wrote: On Mon, Mar 04, 2002 at 01:47:42PM -0600, BD wrote: Jeremy, PMFJI, but has anyone done any testing to see if persistent connections with MySQL and PHP is actually faster in practice? What's PMFJU? Pardon me for jumping in Without testing, I suspect that it is faster but that the gains are very small. If this was Oracle, it'd be a whole different story. The connection overhead in MySQL is minimal. Has anyone created a table showing the connection times of the various databases? Like MySQL, PostgreSQL, Oracle, Interbase etc.? Then you could say Oracle was 4xMySQL, or 3xInterbase etc.. I think it would give developers a better understanding whether they need persistent connections or not. At least it would show them the overhead involved in connecting to a database. Brent _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: how can I use 'C' programs accessing a MySql
The best book for c programming I found is the O'Reilly book, MySQL mSQL . It has a good example of using the C API and even builds some simple C++ classes. The book, MySQL by Paul DuBois is the best overall MySQL book I found. Plus, Paul is a regular responder to this list, so I would buy his book, too. As far as creating tables (or just about anything else) the thing to remember that about all the C API does is pass the command you want to the SQL interpreter and then execute your command. So, if you want to create a table, you would pass a CREATE statement into mysql_query (or mysql_real_query) EXACTLY like you would have entered it into the mysql client. These is lots is some contributed software out there for the C/C++ API, but they all have problems when it comes to inserting and updating records. The issues is in understanding exactly how MySQL returns stuff in the result set. I have attached some C++ classes (and a test program) to this document that should get you started. They deal with the type saftey issue, updating different data types and different size strings, etc. You will also need to go to boost.org and get some code to install something called lexical_cast, too. Zip file removed to post to list. Email me if you want the classes Hello MySQL employees - If you want to post this zip file on your contributed software section, you have my permission to do so. These would have saved me months of effort and I know someone else can use them, too. Have A Great Scouting Day Ken Hylton 7826 Falcon Ridge Drive San Antonio, Texas 78239-4032 210-646-9508 (home) 210-949-7261 (work) 210-949-7254 (fax) 210-287-6756 (cell) [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] mailto:[mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 9:01 AM To: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Subject:how can I use 'C' programs accessing a MySql The following form was submitted via MySQL.com feedback engine: Name: Alexandre Adam E-mail address: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Company: sansoft informatica Position/Title: user Type of interest: for personal use State or province: SP Country: Brazil Support contract: no Enter Your Questions, Comments, Feedback or Your Message here: I'm trying to understand how can I use 'C' programs accessing a MySql DataBase, please, could you send me a small program or so that creates a Table or two and make some changes on them. I read so many examples but all of them only contain parts of the code. please... help...what library I must include... Sincerely Alexandre Adam Sao Paulo - SP - BRAZIL I wrote some code but nothing works... please read it below. #include /usr/include/mysql/mysql.h # include stream.h int main () { { Printf (Initializing...\n); } if (mysql_create_db (mysql, DB_TEST)) { fprintf (stderr, Failed to create DataBase, ERR! %x\n, mysql_error( mysql)); } return 0; } - Before posting, please check: http://www.mysql.com/manual.php http://www.mysql.com/manual.php(the manual) http://lists.mysql.com/ http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php 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:
Re: [Re: LOAD DATA LOCAL INFILE in Alpha 3.23.49]]
I tried putting --local-infile in my server script and local-infile in the /etc/my.cnf file. I also placed a number or logging cammands in the startup script hoping to catch lower level messages. There appears to be little effect on anything. Regards, Tom Birchmire -- script and log stuff Startup Script using --local-infile #! /bin/sh cd /usr/local/mysql/ ./bin/safe_mysqld --user=mysql --local-infile --warnings --log-long-format MYSQL Session with error mysql select * from pet - ; +--++-+--++---+ | name | owner | species | sex | birth | death | +--++-+--++---+ | Puffball | Diane | hamster | f| 1999-03-30 | NULL | | Fluffy | Harold | cat | f| 1993-02-04 | NULL | +--++-+--++---+ 2 rows in set (0.03 sec) mysql load data local infile /home/tomb01/data/pet.txt into table pet; ERROR 1148: The used command is not allowed with this MySQL version mysql quit Sinisa Milivojevic [EMAIL PROTECTED] wrote: Thomas Birchmire writes: I have the same problem using Linux RedHat 7.2. Just what do I have to configure my binary MySQL : Ver 8.23 Distrib 3.23.49a, for pc-linux-gnu on i686? The rest of MySQL works as advertised. Regards Tom Birchmire loca-infile is not a variable. Therefore, you either start a server with --local-infile or have in your /etc/my.cnf : [mysqld] local-infile -- - 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
a grant problem.
Matthew, Tuesday, March 05, 2002, 12:05:46 AM, you wrote: MD a while ago I had problems with users on mysql. MD I am trying to login as root from the local host using password and I get MD access denied using password=YES Usually it means that you are using wrong password for your user. Take a look at: http://www.mysql.com/doc/A/c/Access_denied.htm You can find info about causes of 'Access denied' errors. MD the host column of my user table is set to % and I have done a flush MD privileges MD and from other certain hosts I cannot login as root remote MD my user table looks like MD host=% and everything else is set to Y MD Why is this ??? MD Matt -- For technical support contracts, goto https://order.mysql.com/ 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
Error 2002 can't connect to MySQL server through socket /var/lib...
dlyles, Tuesday, March 05, 2002, 4:54:30 AM, you wrote: d This is the message I am getting. I am currently running MySQL (or trying d to) on a Cobalt Raq3. Is your MySQL server running? d I recently uninstalled and reinstalled, but am unable d to remove this message. I can't start mysql or do anything. That's the d message I keep getting. Any ideas? If your MySQL is not running, you should start mysqld. If your MySQL server is running then you should check location of the socket file and check the socket path in your my.cnf file. Check your privileges on the socket file and on the dir that contains socket file. You can find the description of this error at: http://www.mysql.com/doc/C/a/Can_not_connect_to_server.html Please also tell us, how exactly did you installed MySQL on Cobalt server as there are some specific details. Have you compiled MySQL by yourself or took a binary distribution? -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to convert Database ?
Somsak, Tuesday, March 05, 2002, 2:03:57 PM, you wrote: SR Dear sir, SR I use MySQL-3.23.35 before that running on FreeBSD-4.2. SR Now I use MySQL-4.0.1 running on FreeBSD-4.5. SR I want to convert Database ... SR from SR MySQL-3.23.35 that store at /usr/local/mysql/data SR to SR MySQL-4.0.1 that store at /usr/local/mysql/var. SR Anyone have tools or have another method to convert it ? You can use mysqldump. You can find info about mysqldump and examples of usage at: http://www.mysql.com/doc/m/y/mysqldump.html If you want to change data dir in MySQL you should specify path to new data dir in my.cnf: [mysqld] datadir=path_to_your_dir SR Somsak. -- For technical support contracts, goto https://order.mysql.com/ 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
date format
razvan, Tuesday, March 05, 2002, 7:46:31 AM, you wrote: r Hi, please help me with this one! r I now the date_format function, it converts the standard format sql r (-mm-dd) r to any format, but my problem is that i want to convert from another format r (eg. dd/mm/) to the standard format, in this way i can use insert with r my format. r Can I use another format for date when creating a table? r thanks! No, you can't. You can find info about date and time types, examples of usage in the manual in the section 6.2.2. Date and Time Types: http://www.mysql.com/doc/D/a/Date_and_time_types.html -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FW: Help w/UDFs, compress_gz(string,level) uncompress_gz(string)
I just discovered that @lists.mysql.com accepts no attachments... Please disregard the comments below regarding the code being attached, and instead please find it at this URL: http://www.10east.com/~hightowe/mysqld_udf_zlib.cc -- Lester Hightower [EMAIL PROTECTED] -- Forwarded message -- Date: Tue, 5 Mar 2002 10:47:40 -0500 (EST) From: Lester Hightower [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Help with UDFs, compress_gz(string,level) uncompress_gz(string) On December 18, 2001 I emailed [EMAIL PROTECTED] regarding a pair of user defined functions, compress_gz() and uncompress_gz() that I wrote. That email indicated that, after a few days of testing and cleaning up the code, that I would contribute it. During said testing I discovered that the code is not thread-safe. The code works perfectly as long as it is only used by one thread; it reliably fails, and takes the mysqld with it, if used by 1 threads concurrently. I must first admit that I do very little C programming now-a-days, and even far less multi-threaded programming. That being the case, I am afraid that I am ill-equipped to debug this code, at least not in a timely enough fashion to allow me to ever actually get it done... My suspicion is that my problem might be in zlib. The zlib docs say that it is thread-safe, so maybe I am compiling it incorrectly. I asked a friend, who felt absolutely positive that he had a thread-safe zlib, to try this code. His results differed from mine -- the mysqld did not crash, but he produced dramatic table corruption. This code is just not that complex, and it surprises me that it has been such a pain. I really need this functionality and hope that maybe someone else on this list, with more experience in this area, either could use this functionality also (and can spend some time finding the problem), or can just look at the code for a minute and, by experience, just see the problem. ...and please email a patch to me! :) The code is attached as an Unix ASCII text file, mysqld_udf_zlib.cc. Thanks in advance to anyone who might be able to help. Sincerely, -- Lester Hightower [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
C API BLOB type field lengths returned
Howdy - I am using the C API and am returning a result set from a table with a BLOB type item in it. When I populate the BLOB fields initially (with all the same data) the length is returned properly when I issue SELECT * FROM table and decode the result set column metadata. Meaning, that if I put , This Blob's for you! in the column, the value of MYSQL_FIELDS.length = 65535 (max of BLOB type) and MYSQL_FIELDS.max_length = 21. When I update a few rows and put, That's OK man, but you still can't have my BLOB! in a few columns, the values for the length of the fields are returned funny, or at least, I don't understand why they are returning they way they are. Meaning the value of the new column length are all set to MYSQL_FIELDS.length = 65535 (again, max of BLOB type) and MYSQL_FIELDS.max_length = 49. Problem is, this is even on the unchaged rows! Yuk! A little experimentation showed that if I select on only unchanged rows, the length is returned as 21. But, if the result set has one of the changed rows in it, the length of all blob fields is returned as 49, when (at least to me) it should be 21 or 49. In other words, it returns as the BLOB max_length value the value of the longest blob in the result set, NOT the max_length of each row. I inserted (and updated) some records in the table with BLOB contents It's all about the BLOB and I see the same result. It returns not the length of the BLOB for the row, but the length of the longest BLOB field in the result set. We are running MySQL 3.23.46-Max on RedHat 7.2 Question: 1) How do I get the actual length of the BLOB column in the row, without storing a separate column to maintain BLOB length manually? Have A Great Scouting Day Ken Hylton 7826 Falcon Ridge Drive San Antonio, Texas 78239-4032 210-646-9508 (home) 210-949-7261 (work) 210-949-7254 (fax) 210-287-6756 (cell) [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] mailto:[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: Slient install - Win32 Mysql
Hi, I tried it too, got the same result. I looked into the setup.iss file that setup -r created, and found these lines that looked different from the others: [SetupType-0] Result=301 szDir= The SdAskDestPath dialogs has: [SdAskDestPath-0] szDir=C:\mysql Result=1 So I thought I'd give it a try and change the SetupType section like this: [SetupType-0] Result=1 szDir=C:\mysql And now it works! Seems that Result=1 and/or szDir=C:\mysql is the right thing :-) Jan R Andersson -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: den 4 mars 2002 21:08 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Slient install - Win32 Mysql I have the same problem (silent install). In details: I run setup.exe with command line options -r to create response file needed for silent installation in InstallShield. By default it is created in the windows directory with name setup.iss. Then I move this setup.iss to the same folder as setup.exe and try to run setup.exe -s to perform the silent setup, but it fails. The log file contains: [InstallShield Silent] Version=v4.90.000 File=Log File [ResponseResult] ResultCode=-3 According to IS documentation: -3 Required data not found in the Setup.iss file. Even if I set verbose this is all I have. I didn't find the source for the InstallShield project in the source distribution (I searched for *.ipr, *.rul). What a pity! With the IS project source in hand it would be a piece of cake to handle this situation. BTW this silent setup thing is working with another software (I tryed it with J2RE 1.4.0 - I had to collect the exracted stuff from the temp directory during installation but then it worked all right). Maybe there's a bug in the InstallShield version used (it is an old 5.x version). Maybe there's a bug in the IS project for MySql. Maybe I'm doing something wrong. But I guess it makes *no sense* that everyone writes his/her own silent setup for MySql when there is an official one already. Thanks, Attila - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error 2002 can't connect to MySQL server through socket /var/lib...
Funny I still have the same exact problem Egor does. I start mysql with safe_mysqld, same only server just ends. I do safe_mysqld nothing, I use mysqld -r root -p password Nothing I do, that is listed helps. I still have no mysql.sock, and the server never starts. Mark - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 05, 2002 11:00 AM Subject: Error 2002 can't connect to MySQL server through socket /var/lib... dlyles, Tuesday, March 05, 2002, 4:54:30 AM, you wrote: d This is the message I am getting. I am currently running MySQL (or trying d to) on a Cobalt Raq3. Is your MySQL server running? d I recently uninstalled and reinstalled, but am unable d to remove this message. I can't start mysql or do anything. That's the d message I keep getting. Any ideas? If your MySQL is not running, you should start mysqld. If your MySQL server is running then you should check location of the socket file and check the socket path in your my.cnf file. Check your privileges on the socket file and on the dir that contains socket file. You can find the description of this error at: http://www.mysql.com/doc/C/a/Can_not_connect_to_server.html Please also tell us, how exactly did you installed MySQL on Cobalt server as there are some specific details. Have you compiled MySQL by yourself or took a binary distribution? -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [Re: LOAD DATA LOCAL INFILE in Alpha 3.23.49]]
Thomas Birchmire writes: I tried putting --local-infile in my server script and local-infile in the /etc/my.cnf file. I also placed a number or logging cammands in the startup script hoping to catch lower level messages. There appears to be little effect on anything. Regards, Tom Birchmire As I said, a fix will appear in 3.23.50, but if you build from source, this is a patch that fixes a problem : --- 1.160/configure.in Wed Feb 13 21:53:22 2002 +++ 1.161/configure.in Tue Mar 5 15:46:28 2002 @@ -591,6 +591,8 @@ [ ENABLED_LOCAL_INFILE=no ] ) +AC_SUBST(ENABLE_LOCAL_INFILE) + # Use Paul Eggerts macros from GNU tar to check for large file support. MYSQL_SYS_LARGEFILE --- 1.65/libmysql/libmysql.cWed Feb 13 19:09:26 2002 +++ 1.66/libmysql/libmysql.cTue Mar 5 15:46:28 2002 @@ -1022,7 +1022,7 @@ /* Only enable LOAD DATA INFILE by default if configured with - --with-enabled-local-inflile + --enable-local-infile */ #ifdef ENABLED_LOCAL_INFILE mysql-options.client_flag|= CLIENT_LOCAL_FILES; -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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
Can you skip ORDER BY get rows back in inserted order ?
I have a table like so : CREATE TABLE album_rank( album_id INT NOT NULL, rank INT NOT NULL, KEY(album_id) ) I want to query on the album_id get the results ordered by rank but I want to avoid doing an ORDER BY in the query because of the filesort that it usually triggers so I pre-ordered the rows in my table by inserting them in the order I wanted with a CREATE TABLE ordered SELECT * FROM unordered ORDER BY album_id, rank ASC For some reason I get the data back in a different order I've tried ORDER BY album_id,rank ASC DESC in case it's a FILO or FIFO Is there some way that I can avoid doing an ORDER BY and get my rows back ordered by album_id, rank they way I inserted them ?
Re: 4.0.2 Replication Bug...
On Tuesday 05 March 2002 01:17 am, Jeremy Zawodny wrote: When the machine is pounding away on updates (over 300/sec), it can take a long time to get a response to SHOW SLAVE STATUS. ?I get one, but it can take between 5 and 30 seconds: My first inclination was to blame FreeBSD threads, but then I decided I need to gather some more hard evidence before I could do it. Is SHOW SLAVE STATUS the only command that is that slow when this happens? Try SHOW PROCESSLIST, SHOW VARIABLES, SHOW STATUS, SHOW MASTER STATUS. -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - 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: very tough query
Hi Darren, On Tue, 5 Mar 2002 09:42:50 -0800 Daren Cotter [EMAIL PROTECTED] wrote: mysql desc poll_questions; ++-+--+-++- ---+ | Field | Type| Null | Key | Default| Extra | ++-+--+-++- ---+ | poll_id| int(9) unsigned | | PRI | NULL | auto_increment | | aim_screenname | varchar(16) | | MUL || | | question | varchar(255)| | || | | date_created | date| | | -00-00 | | ++-+--+-++- ---+ mysql desc poll_answers; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | answer_id | int(12) unsigned | | PRI | NULL| auto_increment | | poll_id | int(9) unsigned | | | 0 || | answer| varchar(255) | | | || +---+--+--+-+-++ mysql desc poll_votes; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | aim_screenname | varchar(16) | | PRI | | | | answer_id | int(12) unsigned | | PRI | 0 | | | time_voted | timestamp(12)| YES | | NULL| | | voter_ip | varchar(15) | | | | | ++--+--+-+-+---+ DELETE FROM poll_votes AS v, poll_answers AS a, poll_questions AS q WHERE (q.poll_id = 1) AND (v.answer_id = a.answer_id) AND (a.poll_id = q.poll_id); I haven't tried it yet, but maybe you can try: DELETE poll_questions, poll_answers, poll_votes FROM poll_questions, poll_answers, poll_votes WHERE (poll_questions.poll_id = poll_answers.poll_id) AND (poll_answers.answer_id = poll_votes.answer_id) AND (poll_questions.poll_id = 1); --bk - 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: storing files in mySQL
On Tuesday 05 March 2002 10:05, Thomas Spahni wrote: There are some good reasons for wanting to store data directly in the database, sometimes. For instance I built an application recently that used blobs. The reason is simplicity and security. In a web application it is nice to just dump the files into a directory and point to them, but consider that if you are building NOTHING but a database server, you don't want to have to support and manage security for another protocol, like ftp or http. In addition in my case it would have made the client code much more complicated since the client could already deal with an ODBC data source. I would have had to code in an entire new piece of functionality to deal with the ftp or http protocol, then figure out how to engineer it so the client code would automatically go and get the data, etc. Plus now I can't just manage data security with SQL GRANT instructions. On top of that huge directories are very slow in most file systems. We had over 24 million records! That would mean that some elaborate scheme would have to be developed, like MD5 hash the filename and walk a series of directory levels down based on characters in the hash, etc. Since data is getting added and removed constantly there would then have to be a way to manage that tree to keep it from getting unbalanced etc. In the final analysis this would have been pretty close to the equivalent of building our own b-tree index! Given that database already have highly optimized indexes, it seemed more sensible to use the existing functionality of blobs and indexes. I think what all this points out is that MySQL developers might want to consider how to better support this type of application in the future. Currently HUGE tables of large blobs ARE very awkward. Our blob tables are terabytes in size, and the corruption of one record can force the tape restore from lower regions... Not to mention the horrors of backing the thing up, or any operation that mysql tries to perform by copying the table (of which there are several I believe). Maybe a special table handler module could be written that would cater to the needs of this type of application, plus a few special tools for doing repairs and such. Just an idea :o). On Mon, 4 Mar 2002, Dang Nguyen wrote: I'd like to know how to load files, such as MS-Word documents or PDF documents, in a mySQL database. I've setup a blob type in a table, but where do I go from there? The purpose of this is to store files uploaded from a web page and processed with Java servlets. Then, the files should be retrievable (displayed or downloaded) back to a client browser. My environment: Apache Web server 1.3.x on Solaris 2.8 with Java servlets environment. MS-Word and PDF files are essentially binary files (very much like images). I suggest leaving them as they are in a separate directory. These MS-Word and PDF files may be converted to plain ASCII using filters (mswordview, pdftotext). The resulting ASCII versions are then loaded into a table with one column of type TEXT along with a VARCHAR pointer to the filename. Create a fulltext index on the TEXT field. A fulltext search for some keywords will return a sorted list of filenames. These files can be returned to the client. This scenario works well for me on a collection of some 15'000 HTML documents (I used w3m as a filter to convert to ASCII). All conversions are done with a simple shell script calling the filters. Thomas Spahni - 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: very tough query
Thanks much for the try, but I get an error: mysql DELETE - poll_questions, poll_answers, poll_votes - FROM - poll_questions, poll_answers, poll_votes - WHERE - (poll_questions.poll_id = poll_answers.poll_id) - AND (poll_answers.answer_id = poll_votes.answer_id) - AND (poll_questions.poll_id = 1); ERROR 1064: You have an error in your SQL syntax near 'poll_questions, poll_answers, poll_votes FROM poll_questions, poll_answers, ' at line 2 -Original Message- From: Batara Kesuma [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 8:27 AM To: [EMAIL PROTECTED] Cc: Daren Cotter Subject: Re: very tough query Hi Darren, On Tue, 5 Mar 2002 09:42:50 -0800 Daren Cotter [EMAIL PROTECTED] wrote: mysql desc poll_questions; ++-+--+-++- ---+ | Field | Type| Null | Key | Default| Extra | ++-+--+-++- ---+ | poll_id| int(9) unsigned | | PRI | NULL | auto_increment | | aim_screenname | varchar(16) | | MUL || | | question | varchar(255)| | || | | date_created | date| | | -00-00 | | ++-+--+-++- ---+ mysql desc poll_answers; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | answer_id | int(12) unsigned | | PRI | NULL| auto_increment | | poll_id | int(9) unsigned | | | 0 || | answer| varchar(255) | | | || +---+--+--+-+-++ mysql desc poll_votes; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | aim_screenname | varchar(16) | | PRI | | | | answer_id | int(12) unsigned | | PRI | 0 | | | time_voted | timestamp(12)| YES | | NULL| | | voter_ip | varchar(15) | | | | | ++--+--+-+-+---+ DELETE FROM poll_votes AS v, poll_answers AS a, poll_questions AS q WHERE (q.poll_id = 1) AND (v.answer_id = a.answer_id) AND (a.poll_id = q.poll_id); I haven't tried it yet, but maybe you can try: DELETE poll_questions, poll_answers, poll_votes FROM poll_questions, poll_answers, poll_votes WHERE (poll_questions.poll_id = poll_answers.poll_id) AND (poll_answers.answer_id = poll_votes.answer_id) AND (poll_questions.poll_id = 1); --bk - 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: RE: storing files in mySQL
Thanks to everyone who responded to my query. Your responses have given me a bit to think about. Dang Nguyen - 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: very tough query
From: Daren Cotter [EMAIL PROTECTED] ...what happens when I want to delete a poll? Do it machanically. That is, do a query in advance to find the data that needs to be deleted, then go through and delete it. The logic for joining a delete can be written, but MySQL doesn't support it. (Hey folks, does anything else support joined deletes?) By the way, since poll_votes has a PK of aim_screenname/answer_id, folks can give each answer for a given poll once. As apposed to just one answer per poll. --- Rodney Broom Programmer: Desert.Net - 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 you skip ORDER BY get rows back in inserted order ?
At 8:16 -0800 3/5/02, Sam Lam wrote: I have a table like so : CREATE TABLE album_rank( album_id INT NOT NULL, rank INT NOT NULL, KEY(album_id) ) I want to query on the album_id get the results ordered by rank but I want to avoid doing an ORDER BY in the query because of the filesort that it usually triggers so I pre-ordered the rows in my table by inserting them in the order I wanted with a CREATE TABLE ordered SELECT * FROM unordered ORDER BY album_id, rank ASC. For some reason I get the data back in a different order. I've tried ORDER BY album_id,rank ASC DESC in case it's a FILO or FIFO. Is there some way that I can avoid doing an ORDER BY and get my rows back ordered by album_id, rank they way I inserted them ? No. That is the nature of relational databases. You might want to consider adding an AUTO_INCREMENT column, then inserting in the order you want them back. Then you can retrieve in that order by using an ORDER BY on the AUTO_INCREMENT column. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error 2002 can't connect to MySQL server through socket /var/lib...
Intrex, Tuesday, March 05, 2002, 6:09:01 PM, you wrote: I Funny I still have the same exact problem Egor does. I start mysql with I safe_mysqld, same only server just ends. I do safe_mysqld nothing, I use I mysqld -r root -p password I Nothing I do, that is listed helps. I still have no mysql.sock, and the I server never starts. Look at /var/lib/mysql/your_host.err file. Is there any cause of an error? I Mark I - Original Message - I From: Egor Egorov [EMAIL PROTECTED] I To: [EMAIL PROTECTED] I Sent: Tuesday, March 05, 2002 11:00 AM I Subject: Error 2002 can't connect to MySQL server through socket /var/lib... dlyles, Tuesday, March 05, 2002, 4:54:30 AM, you wrote: d This is the message I am getting. I am currently running MySQL (or I trying d to) on a Cobalt Raq3. Is your MySQL server running? d I recently uninstalled and reinstalled, but am unable d to remove this message. I can't start mysql or do anything. That's I the d message I keep getting. Any ideas? If your MySQL is not running, you should start mysqld. If your MySQL server is running then you should check location of the socket file and check the socket path in your my.cnf file. Check your privileges on the socket file and on the dir that contains socket file. You can find the description of this error at: http://www.mysql.com/doc/C/a/Can_not_connect_to_server.html -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can you skip ORDER BY get rows back in inserted order ?
Sam, Tuesday, March 05, 2002, 6:16:17 PM, you wrote: SL I have a table like so : SL CREATE TABLE album_rank( SL album_id INT NOT NULL, SL rank INT NOT NULL, SL KEY(album_id) SL ) SL I want to query on the album_id get the results ordered by rank but I SL want to avoid doing an ORDER BY in the query because of the filesort SL that it usually triggers so I pre-ordered the rows in my table by SL inserting them in the order I wanted with a CREATE TABLE ordered SL SELECT * FROM unordered ORDER BY album_id, rank ASC. SL For some reason I get the data back in a different order. I've tried SL ORDER BY album_id,rank ASC DESC in case it's a FILO or FIFO. SL Is there some way that I can avoid doing an ORDER BY and get my SL rows back ordered by album_id, rank they way I inserted them ? Regularly no. The order of entries in tables changes during the work. -- For technical support contracts, goto https://order.mysql.com/ 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: very tough query
Well after reviewing the DELETE section of the manual, I see nothing to suggest that it supports multiple-table deletes... # Nathan - Original Message - From: Daren Cotter [EMAIL PROTECTED] To: Nathan [EMAIL PROTECTED]; Daren Cotter [EMAIL PROTECTED] Sent: Tuesday, March 05, 2002 12:37 PM Subject: RE: very tough query Here's what happens: ERROR 1064: You have an error in your SQL syntax near ' poll_answers, poll_votes where (poll_questions.poll_id = poll_answers.poll_id) ' at line 1 -Original Message- From: Nathan [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 9:28 AM To: Daren Cotter Subject: Re: very tough query I haven't read this thread but I thought you couldn't have anything besides a priority between DELETE and FROM... What happens with: mysql DELETE FROM - poll_questions, poll_answers, poll_votes - WHERE - (poll_questions.poll_id = poll_answers.poll_id) - AND (poll_answers.answer_id = poll_votes.answer_id) - AND (poll_questions.poll_id = 1); # Nathan - Original Message - From: Daren Cotter [EMAIL PROTECTED] To: Batara Kesuma [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: Daren Cotter [EMAIL PROTECTED] Sent: Tuesday, March 05, 2002 11:42 AM Subject: RE: very tough query Thanks much for the try, but I get an error: mysql DELETE - poll_questions, poll_answers, poll_votes - FROM - poll_questions, poll_answers, poll_votes - WHERE - (poll_questions.poll_id = poll_answers.poll_id) - AND (poll_answers.answer_id = poll_votes.answer_id) - AND (poll_questions.poll_id = 1); ERROR 1064: You have an error in your SQL syntax near 'poll_questions, poll_answers, poll_votes FROM poll_questions, poll_answers, ' at line 2 -Original Message- From: Batara Kesuma [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 8:27 AM To: [EMAIL PROTECTED] Cc: Daren Cotter Subject: Re: very tough query Hi Darren, On Tue, 5 Mar 2002 09:42:50 -0800 Daren Cotter [EMAIL PROTECTED] wrote: mysql desc poll_questions; ++-+--+-++- ---+ | Field | Type| Null | Key | Default| Extra | ++-+--+-++- ---+ | poll_id| int(9) unsigned | | PRI | NULL | auto_increment | | aim_screenname | varchar(16) | | MUL || | | question | varchar(255)| | || | | date_created | date| | | -00-00 | | ++-+--+-++- ---+ mysql desc poll_answers; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | answer_id | int(12) unsigned | | PRI | NULL| auto_increment | | poll_id | int(9) unsigned | | | 0 || | answer| varchar(255) | | | || +---+--+--+-+-++ mysql desc poll_votes; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | aim_screenname | varchar(16) | | PRI | | | | answer_id | int(12) unsigned | | PRI | 0 | | | time_voted | timestamp(12)| YES | | NULL| | | voter_ip | varchar(15) | | | | | ++--+--+-+-+---+ DELETE FROM poll_votes AS v, poll_answers AS a, poll_questions AS q WHERE (q.poll_id = 1) AND (v.answer_id = a.answer_id) AND (a.poll_id = q.poll_id); I haven't tried it yet, but maybe you can try: DELETE poll_questions, poll_answers, poll_votes FROM poll_questions, poll_answers, poll_votes WHERE (poll_questions.poll_id = poll_answers.poll_id) AND (poll_answers.answer_id = poll_votes.answer_id) AND (poll_questions.poll_id = 1); --bk - 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: Can you skip ORDER BY get rows back in inserted order ?
Is there some way that I can avoid doing an ORDER BY and get my rows back ordered by album_id, rank they way I inserted them ? No That is the nature of relational databases Is this because the index cache in ram may not be loaded in order ? You might want to consider adding an AUTO_INCREMENT column, then inserting in the order you want them back Then you can retrieve in that order by using an ORDER BY on the AUTO_INCREMENT column My rank column is the order that I want them back in Would you agree that sorting small row sets on the client with say PHP is more efficient than asking a busy MySQL server with many clients to do ORDER BY so that FILESORT or TEMPORARY can be avoided ? thanks, - Sam PS : I bought your book by the way Great read On Tuesday, March 5, 2002, at 09:12 AM, Paul DuBois wrote: At 8:16 -0800 3/5/02, Sam Lam wrote: I have a table like so : CREATE TABLE album_rank( album_id INT NOT NULL, rank INT NOT NULL, KEY(album_id) ) I want to query on the album_id get the results ordered by rank but I want to avoid doing an ORDER BY in the query because of the filesort that it usually triggers so I pre-ordered the rows in my table by inserting them in the order I wanted with a CREATE TABLE ordered SELECT * FROM unordered ORDER BY album_id, rank ASC For some reason I get the data back in a different order I've tried ORDER BY album_id,rank ASC DESC in case it's a FILO or FIFO Is there some way that I can avoid doing an ORDER BY and get my rows back ordered by album_id, rank they way I inserted them ? No That is the nature of relational databases You might want to consider adding an AUTO_INCREMENT column, then inserting in the order you want them back Then you can retrieve in that order by using an ORDER BY on the AUTO_INCREMENT column
Re: win start problem
Ok, I'm an idiot. I haddatadir = c:/_data/mysql/ when I should have had datadir = c:/_data/mysql/data/ I had some help though (I can't place all the blame on myself, ego issues ya know). I find this paragraph utterly confusing in MySQL Ref. Manual version 4.0.2-alpha See Node Windows prepare environment #- [mysqld] basedir=the_install_path #e.g. c:/mysql datadir=the_data_path #e.g. c:/mysql/data or d:/mydata/data If the data directory is other than the default c:\mysql\data, you must cut the whole \data\mysql directory and paste it on the your option new directory, e.g.: d:\mydata\mysql. #- And, many of the user comments listed here: http://www.mysql.com/doc/N/T/NT_start.html seem to indicate there were problems when trying to _install_ in a non-standard location. There _may_ be. But, it seems the datadir path is respected (works) fine (although I didn't try a path name with spaces) Miguel, Thanks again for your time. Thanks to your persistance I have MySQL installed with the data where I want it. :) -Ryan [Chop] I need to confess you, that until now I didn't have in my mind to use a directory name like __work. However I created the f:\__work dir (on my machine I have the Win2K on F:), cut/paste the whole directory \mysql\data to \__work and modified the my.ini for to have the datadir like: datadir=f:\__work\data (notice that you did c:\__work\data\mysql). With the changes above, I ran the server without any problem. Regards, Miguel I get the same error. Note: I did copy the c:\mysql\data tree (with all files to the non-standard location and modified the my.ini). Anyway, I went back to the original machine and got it working by pointing back to the standard c:\mysql\data dir for all files I don't like this but, it works Thanks Anyway For Your Response, -Ryan [Chop] - 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 you skip ORDER BY get rows back in inserted order ?
At 9:52 -0800 3/5/02, Sam Iam wrote: Is there some way that I can avoid doing an ORDER BY and get my rows back ordered by album_id, rank they way I inserted them ? No. That is the nature of relational databases. Is this because the index cache in ram may not be loaded in order ? No, it's because relational engines consider a bunch of rows as a set, and order is not a property that sets have. Thus, such engines make no guarantee about the order in which they'll return a set, unless you specify an ORDER BY clause to provide sorting instructions. You might want to consider adding an AUTO_INCREMENT column, then inserting in the order you want them back. Then you can retrieve in that order by using an ORDER BY on the AUTO_INCREMENT column. My rank column is the order that I want them back in. Then use it for sorting. Won't that give you the result you want? Would you agree that sorting small row sets on the client with say PHP is more efficient than asking a busy MySQL server with many clients to do ORDER BY so that FILESORT or TEMPORARY can be avoided ? That's subject to empirical test. :-) In general, the MySQL server's built to be able to sort efficiently, so you may as well let it do the sorting. Sorting on the client may not gain you anything if the web server and the MySQL server are both running on the same host, which is common. In that case, the work's all being done on the same machine anyway? thanks, - Sam. PS : I bought your book by the way. Great read. Thanks. Which book? The doorstop, or the MySQL/Perl book? On Tuesday, March 5, 2002, at 09:12 AM, Paul DuBois wrote: At 8:16 -0800 3/5/02, Sam Lam wrote: I have a table like so : CREATE TABLE album_rank( album_id INT NOT NULL, rank INT NOT NULL, KEY(album_id) ) I want to query on the album_id get the results ordered by rank but I want to avoid doing an ORDER BY in the query because of the filesort that it usually triggers so I pre-ordered the rows in my table by inserting them in the order I wanted with a CREATE TABLE ordered SELECT * FROM unordered ORDER BY album_id, rank ASC. For some reason I get the data back in a different order. I've tried ORDER BY album_id,rank ASC DESC in case it's a FILO or FIFO. Is there some way that I can avoid doing an ORDER BY and get my rows back ordered by album_id, rank they way I inserted them ? No. That is the nature of relational databases. You might want to consider adding an AUTO_INCREMENT column, then inserting in the order you want them back. Then you can retrieve in that order by using an ORDER BY on the AUTO_INCREMENT column. - 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
Query help please!
I need help writing query that would give me parent categories of catID from categories table. For example, if catID=030 then it should give me: Birthday | Special Birthday | Special Birthday If catID=028 Birthday | General Birthday | NULL desc categories +---+--++ | catID | parentID | catName| +---+--++ | 001 | 000 | Birthday | | 002 | 000 | Get Well | | 003 | 000 | Special Occasions | | 038 | 029 | 40th | | 037 | 029 | 30th | | 036 | 029 | 21st | | 035 | 029 | 16th | | 029 | 001 | Special Birthday | | 028 | 001 | General Birthday | | 030 | 029 | Inspirational | | 045 | 001 | Children's Birthday| | 046 | 045 | 1st| +---+--++ Pinkesh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql won´t connect
Hi I installed MySql on Win2k and everything worked fine, than I also installed Oracel, mysql refused to work, than I deinstalled Oracle and did a new mysql setup. But when I try to start mysql, with mysql\binmysql an error occurs, and mysql can´t connect to localhost. Where is the problem? Thanx in advance -- GMX - Die Kommunikationsplattform im Internet. http://www.gmx.net - 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
connecting to all databases using one odbc connection..PLEASE HELP
I asked yesterday but got no responses...anyone have any ideas how I can use one odbc connection but have access to all databases on the MSSQL server??? Thanks, Eddie - 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: Query help please!
Do you have another table with all the birthdates in it? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 12:05 PM To: [EMAIL PROTECTED] Subject: Query help please! I need help writing query that would give me parent categories of catID from categories table. For example, if catID=030 then it should give me: Birthday | Special Birthday | Special Birthday If catID=028 Birthday | General Birthday | NULL desc categories +---+--++ | catID | parentID | catName| +---+--++ | 001 | 000 | Birthday | | 002 | 000 | Get Well | | 003 | 000 | Special Occasions | | 038 | 029 | 40th | | 037 | 029 | 30th | | 036 | 029 | 21st | | 035 | 029 | 16th | | 029 | 001 | Special Birthday | | 028 | 001 | General Birthday | | 030 | 029 | Inspirational | | 045 | 001 | Children's Birthday| | 046 | 045 | 1st| +---+--++ Pinkesh - 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: Query help please!
One more thing... What are you using to pull the data? PHP? Perl? Etc? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 12:05 PM To: [EMAIL PROTECTED] Subject: Query help please! I need help writing query that would give me parent categories of catID from categories table. For example, if catID=030 then it should give me: Birthday | Special Birthday | Special Birthday If catID=028 Birthday | General Birthday | NULL desc categories +---+--++ | catID | parentID | catName| +---+--++ | 001 | 000 | Birthday | | 002 | 000 | Get Well | | 003 | 000 | Special Occasions | | 038 | 029 | 40th | | 037 | 029 | 30th | | 036 | 029 | 21st | | 035 | 029 | 16th | | 029 | 001 | Special Birthday | | 028 | 001 | General Birthday | | 030 | 029 | Inspirational | | 045 | 001 | Children's Birthday| | 046 | 045 | 1st| +---+--++ Pinkesh - 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
help with 49a version
I just installed mysql 3.23.49a on RedHat 7.2 and when I rety to start it it shows errors like @hostname@ command not found when I start it using symlink and it won't start at all if I start it from /etc/init.d/mysql.server start, error about /tmp/mysql.sock. On the same machine 3.23.47 was working with no issues. What can I do about 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: Can you skip ORDER BY get rows back in inserted order ?
Sam, I am constantly coding with PHP and MySQL. As far as: Would you agree that sorting small row sets on the client with say PHP is more efficient than asking a busy MySQL server with many clients to o ORDER BY so that FILESORT or TEMPORARY can be avoided ? I have learned to let MySQL do anything DB related. Generally, whenever you can have MySQL do the work you are better off than letting PHP do it - unless you need some type of error checking or logging. Rob -Original Message- From: Sam Iam [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 9:53 AM To: Paul DuBois Cc: MySQL List Subject: Re: Can you skip ORDER BY get rows back in inserted order ? Is there some way that I can avoid doing an ORDER BY and get my rows back ordered by album_id, rank they way I inserted them ? No. That is the nature of relational databases. Is this because the index cache in ram may not be loaded in order ? You might want to consider adding an AUTO_INCREMENT column, then inserting in the order you want them back. Then you can retrieve in that order by using an ORDER BY on the AUTO_INCREMENT column. My rank column is the order that I want them back in. Would you agree that sorting small row sets on the client with say PHP is more efficient than asking a busy MySQL server with many clients to do ORDER BY so that FILESORT or TEMPORARY can be avoided ? thanks, - Sam. PS : I bought your book by the way. Great read. On Tuesday, March 5, 2002, at 09:12 AM, Paul DuBois wrote: At 8:16 -0800 3/5/02, Sam Lam wrote: I have a table like so : CREATE TABLE album_rank( album_id INT NOT NULL, rank INT NOT NULL, KEY(album_id) ) I want to query on the album_id get the results ordered by rank but I want to avoid doing an ORDER BY in the query because of the filesort that it usually triggers so I pre-ordered the rows in my table by inserting them in the order I wanted with a CREATE TABLE ordered SELECT * FROM unordered ORDER BY album_id, rank ASC. For some reason I get the data back in a different order. I've tried ORDER BY album_id,rank ASC DESC in case it's a FILO or FIFO. Is there some way that I can avoid doing an ORDER BY and get my rows back ordered by album_id, rank they way I inserted them ? No. That is the nature of relational databases. You might want to consider adding an AUTO_INCREMENT column, then inserting in the order you want them back. Then you can retrieve in that order by using an ORDER BY on the AUTO_INCREMENT column. - 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: connecting to all databases using one odbc connection..PLEASE HELP
On Tuesday 05 March 2002 11:11 am, Edward Peloke wrote: I asked yesterday but got no responses...anyone have any ideas how I can use one odbc connection but have access to all databases on the MSSQL server??? I have not tried this but have you considered using the use command? Or even perhaps specifying the database name along with the table name? - 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: connecting to all databases using one odbc connection..PLEASE HELP
It won't let you since Windows ODBC only lets you create ONE DSN per DATABASE -Original Message- From: Edward Peloke [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 12:11 PM To: MySql Subject: connecting to all databases using one odbc connection..PLEASE HELP I asked yesterday but got no responses...anyone have any ideas how I can use one odbc connection but have access to all databases on the MSSQL server??? Thanks, Eddie - 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: storing files in mySQL
...and so I'm tempted for the third time to bring up my issue which has to date resulted in no response: BLOB columns causing various, seemingly random out of memory errors on FreeBSD. Is this problem too low-level for the list? I see plenty of responses to questions about simple queries, even PHP code examples -- but it seems that more serious questions warrant no reply. If nobody can help me, could someone at least point me in the right direction? I've exhausted all my search options, from list archives to bug reports (turns out mysqlbug just sends an e-mail to this list, and I've provided most of that information in my e-mails anyway), and found no references to this issue. For consistency, I'll quote my second e-mail: I'm having an issue with BLOB columns (MEDIUMBLOB and LONGBLOB, more specifically) in a table that's intended to store files (with filename, content-type and length columns). Generally, the problem seems to be that MySQL thinks that it's out of memory when dealing with arbirarily large values, either during an attempted INSERT/UPDATE (using LOAD_FILE() or the contents of the file as a string) or during a mysqldump. The problem has manifested on 2 x86/FreeBSD development boxes running the ports installation of 3.23.46, as well as an OSX box running 3.23.47. The exact same operations succeed on our production server, a Sparc/Solaris box running 3.23.36. The my.cnf's are identical on all 3 machines: a copy of the my-large.cnf with a modified max_allowed_packet value of '16M' (which should suit a query that INSERT's a file at least 7MB in size, with escaped characters, etc.). The specific errors encountered are: - when attempting an INSERT INTO binaries (contents) VALUES ([str]), where [str] is the contents of a file over ~2MB, the INSERT fails with Out of memory: (xxx bytes needed). Subsequent attempts *sometimes* result in the MySQL server has gone away error. The same errors occur when replacing [str] with LOAD_FILE([filename]). - when attempting to dump a database containing values of LONGBLOB columns over ~1MB, mysqldump fails with the same Out of memory error above. - when attempting to feed a dump into a database using the mysql DB dump.sql method (or source'ing it from the SQL prompt), values in the same range cause mysql to die with the error MySQL server has gone away. I should also mention that the errors are pretty erratic: the same operation often yields different errors on each attempt. A recent mysqldump succeeded on one FreeBSD box, but the attempt to restore it on the other one failed (MySQL server has gone away). Several times, attempts to INSERT large values have resulted in corrupt tables, or NULL values without any error. Has anyone else experienced any of these symptoms? Our production server (which routinely handles files in excess of 5MB) hasn't exhibited this issue yet, and it's running a practically ancient version of MySQL. An installation of the same version (3.23.36) on one of the FreeBSD boxes showed no change. We've checked the physical memory and df on all 3 boxes that we've experienced the problems, and none of them even come close to being out of memory or disk space. We even checked safe_mysqld to see if it calls limit or ulimit... which it doesn't. I'm totally stumped. There are some good reasons for wanting to store data directly in the database, sometimes. I'd like to think so, too... but this problem is keeping us from storing anything larger than 2MB in a column that's supposed to be able to store 16MB worth of data. If we can't make this work, then we'll be forced to abandon MySQL, and I would really hate to do that! cheers, -- shawn allen | [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: Default record order...
Hi. I guess your problem comes from the fact that SQL has no concept of internal order. If you do not specify an ORDER BY clause, the order or records returned is undefined, i.e. random. Of course, MySQL has some kind of internal order depending on many factors, but you may not rely on it. And neither you may rely on the fact that a PRIMARY KEY influences the internal order. You have to use an ORDER BY clause if you want to get a sorted result (of course, you want a key to speed up the ORDER BY clause). As said, the (sorting) behaviour without ORDER BY is undefined according to the specification and may change without notice. Bye, Benjamin. On Mon, Mar 04, 2002 at 09:56:53PM -0700, [EMAIL PROTECTED] wrote: Greetings! If you do not have an index on any column in a table, how does mysql handle repeated queries (i.e. SELECT * FROM report;) I am noticing that if I run the following three queries, I get different results for the third query: SELECT * FROM report; SELECT * FROM report ORDER BY lastname; SELECT * FROM report; [...] p.s. This is for my understanding. I solved my actual problem by simply placing a primary key on the appropriate columns - as I should have from the beginning! G). [...] -- [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
SELECT this IF that
I want to select and join data from two tables if the same id exists in both tables. If the id only exists in one table I want MySQL to return a string like sorry, no records found. How can I do this? I'm guessing it's something like: mysql SELECT tbl1.this, tbl2.that FROM tbl1, tbl2 WHERE IF (tbl1.id = tbl2.id, return the data, no record); (Obviously that's not a real query). ** This e-mail and any files transmitted with it may contain privileged or confidential information. It is solely for use by the individual for whom it is intended, even if addressed incorrectly. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited. Thank you for your compliance. - 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: Default record order...
Benjamin, Benjamin Pflugmann wrote: Hi. I guess your problem comes from the fact that SQL has no concept of internal order. If you do not specify an ORDER BY clause, the order or records returned is undefined, i.e. random. That's what I was figuring. I asked because I wanted it confirmed. Thank you. Of course, MySQL has some kind of internal order depending on many factors, but you may not rely on it. And neither you may rely on the fact that a PRIMARY KEY influences the internal order. You have to use an ORDER BY clause if you want to get a sorted result (of course, you want a key to speed up the ORDER BY clause). Hmmm. Perhaps I'm misled by the default behaviour of other database engines, but I was taught that the primary key was stored in the database to optimize search/insert/delete - which meant *sorted*. That is why you don't want a large (complex) primary key on tables that must run fast - the overhead of sorting each insert/delete negatively affects performance. Or so I was taught, anyway (back in the dark ages - primative data structures and all that G). Cheers, -Richard As said, the (sorting) behaviour without ORDER BY is undefined according to the specification and may change without notice. Bye, Benjamin. On Mon, Mar 04, 2002 at 09:56:53PM -0700, [EMAIL PROTECTED] wrote: Greetings! If you do not have an index on any column in a table, how does mysql handle repeated queries (i.e. SELECT * FROM report;) I am noticing that if I run the following three queries, I get different results for the third query: SELECT * FROM report; SELECT * FROM report ORDER BY lastname; SELECT * FROM report; [...] p.s. This is for my understanding. I solved my actual problem by simply placing a primary key on the appropriate columns - as I should have from the beginning! G). [...] -- [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: difference between two times
oops, good point. thanks. On Mon, 4 Mar 2002, Roger Baklund wrote: * Nathan Cowles $query2 = SELECT SEC_TO_TIME(TIME_TO_SEC('$lastout') - TIME_TO_SEC('$firstin')); $result2 = mysql_db_query($database, $query, $connection) or die (Error in query: $query. . mysql_error()); You are assigning the query to $query2, but you execute $query... ;) -- Roger Nathan Cowles StormNet Communications 530.897.4069 - 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 stops responding and hangs querys
Description: mysqld just stops responding. 'mysqladmin ping' hangs without any message How-To-Repeat: Cannot reproduce manually, happens during normal operation Fix: Asking You :) Submitter-Id: submitter ID Originator:ilm.ee Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-3.23.48 (Source distribution) Server: /usr/local/mysql/bin/mysqladmin Ver 8.23 Distrib 3.23.48, for unknown-freebsdelfKALAH on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.48 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 2 min 46 sec Threads: 1 Questions: 986 Slow queries: 0 Opens: 11 Flush tables: 1 Open tables: 5 Queries per second avg: 5.940 Environment: System: FreeBSD saturn.zzz.ee KALAH_FIREWALL-02 FreeBSD KALAH_FIREWALL-02 #6: Tue Feb 19 12:53:03 GMT 2002 [EMAIL PROTECTED]:/usr/src/sys/compile/SYNERSF i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: gcc version 2.7.2.3 Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='-O3 -DDBUG_OFF -fno-implicit-templates -DMYSQLD_NET_RETRY_COUNT=100' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1052246 Apr 4 2000 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Dec 15 2000 /usr/lib/libc.so - libc.so.3 -r--r--r-- 1 root wheel 520532 Apr 4 2000 /usr/lib/libc.so.3 Configure command: ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Default record order...
Richard, I guess your problem comes from the fact that SQL has no concept of internal order. If you do not specify an ORDER BY clause, the order or records returned is undefined, i.e. random. That's what I was figuring. I asked because I wanted it confirmed. Thank you. Of course, MySQL has some kind of internal order depending on many factors, but you may not rely on it. And neither you may rely on the fact that a PRIMARY KEY influences the internal order. You have to use an ORDER BY clause if you want to get a sorted result (of course, you want a key to speed up the ORDER BY clause). Hmmm. Perhaps I'm misled by the default behaviour of other database engines, but I was taught that the primary key was stored in the database to optimize search/insert/delete - which meant *sorted*. That is why you don't want a large (complex) primary key on tables that must run fast - the overhead of sorting each insert/delete negatively affects performance. Or so I was taught, anyway (back in the dark ages - primative data structures and all that G). As others have said, this is not part of the relational model. However you are correct - back in the 'good old days' we could rely upon hierarchical databases to do this, and I'm fairly sure that the early 'SQL' DBMSes also used to do this because they physically separated the Primary Key and the 'dependent part' of the row, so that any 'straight' listing would come out in PK sequence. =dn - 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: InnoDB frightens me...
You can use logical lock - set a STATUS int 0 (probably the ID of user) to indicate someone started to edit it interactively to block other user to edit the same record. Before you write it back to disk, read it again in a buffer to keep all data possible changed by batch updates. Or separate the table to 2 tables, one for each kind of update. Also I use a kind of transactions before I know that exists, I don't subtract from items directrly but I put it in a field named keeps. If user change his mind or something goes wrong, I only subtruct from keeps fields.Otherwise I subtract from items and keeps. Of course I have create another temp file with the invoice. I use these methods several years now mainly with C-tree from Faircom, they work fine. But to tell you the true, I use Delphi with Interbase for this kind of application on the net. I don't know if it's possible with PHP. Can someone directs me to the avantages of Inno base comparing wtih default MySQL? Row locking is the bigest one as I can read here? Thanks Makis -Original Message- From: Tobias Lind - Telia Internet [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 2:46 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: InnoDB frightens me... Thanks - that would be great! I'm sure a lot of people will find this very helpful. Regards, Tobias - Original Message - From: Michael Widenius [EMAIL PROTECTED] To: Tobias Lind - Telia Internet [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, March 05, 2002 12:36 PM Subject: Re: InnoDB frightens me... Hi! Tobias Hi! Tobias I'm in the same position as Steve: Considering switching from MyISAM to InnoDB because of the Tobias row-level locking capabilities. My application has quite a lot of updates/inserts mixed with Tobias selects, and is starting to suffer from the table-locking policy... Tobias And like Steve, I'm also scared! :) cut Tobias Maybe some of this confusion and fear could be avoided with a section in the InnoDB documentation Tobias that describes InnoDB from a MyISAM-point-of-view :) Tobias Explaining how all common things, principles and routines with MyISAM works with InnoDB. I'm sure a Tobias lot of people have very good knowledge of MyISAM and comes from the same direction as I do... I have forwarded this to our docmentation team and we shall try to do this in the near future. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ 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: SELECT this IF that
Leif, I want to select and join data from two tables if the same id exists in both tables. If the id only exists in one table I want MySQL to return a string like sorry, no records found. How can I do this? I'm guessing it's something like: mysql SELECT tbl1.this, tbl2.that FROM tbl1, tbl2 WHERE IF (tbl1.id = tbl2.id, return the data, no record); (Obviously that's not a real query). Close though! I'm assuming that tbl2's population is a sub-set of that in tbl1 (otherwise what will you do when tbl2 throws up a row that has no tbl1 equivalent). Herewith a solution using my own tblNms: SELECT Jfull.Id, Jfull.Word, IF( Jsubset.Word '', Jfull.Word, 'no record' ) AS Data FROM Jfull LEFT OUTER JOIN Jsubset USING ( Id ) Regards, =dn - 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: Default record order...
Hi. On Tue, Mar 05, 2002 at 12:10:47PM -0700, [EMAIL PROTECTED] wrote: [...] Of course, MySQL has some kind of internal order depending on many factors, but you may not rely on it. And neither you may rely on the fact that a PRIMARY KEY influences the internal order. You have to use an ORDER BY clause if you want to get a sorted result (of course, you want a key to speed up the ORDER BY clause). Hmmm. Perhaps I'm misled by the default behaviour of other database engines, but I was taught that the primary key was stored in the database to optimize search/insert/delete - which meant *sorted*. That is why you don't want a large (complex) primary key on tables that must run fast - the overhead of sorting each insert/delete negatively affects performance. Or so I was taught, anyway (back in the dark ages - primative data structures and all that G). [...] It seems that what you were taught was the internal working of whatever database you were tought about. MySQL (with MyISAM tables) definitely doesn't sort the data records according to the primary key. [1] You loose a little bit of time on writing records, but only as much as it needs to store a new leaf withi an B-Tree. That's not much. Bye, Benjamin. [1] But of course, you can explicitly sort the data of a MyISAM table with myisamchk resp. ALTER TABLE ... ORDER BY - but that's a different story and not the default -- [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: 4.0.2 Replication Bug...
On Tue, Mar 05, 2002 at 09:19:35AM -0700, Sasha Pachev wrote: On Tuesday 05 March 2002 01:17 am, Jeremy Zawodny wrote: When the machine is pounding away on updates (over 300/sec), it can take a long time to get a response to SHOW SLAVE STATUS. ?I get one, but it can take between 5 and 30 seconds: My first inclination was to blame FreeBSD threads, but then I decided I need to gather some more hard evidence before I could do it. Is SHOW SLAVE STATUS the only command that is that slow when this happens? Yes. Try SHOW PROCESSLIST, SHOW VARIABLES, SHOW STATUS, SHOW MASTER STATUS. Tried all of them and they're fast. In other news, the server hit another duplicate key problem. This time I was able to do this: slave stop; set sql_slave_skip_counter = 1; slave start; And it worked! So the new code seems to be better in that respect. :-) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 26 days, processed 894,010,106 queries (394/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
MySQL 3.23 Lost Connection
Hello. I have compiled/rebuilt the MySQL 3.23.49 Source RPM under kernel 2.4.17, and glibc 2.1. Using gcc version 2.95.4 20010319 (prerelease). I had 2.96 on there and headed the warnings and downgraded to 2.95. Randomly I get the Lost Connection using PHP 4.1.2... Any idea why? Thanks Adam - 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 this IF that
Um, if there aren't any records in tbl1.id that match tbl2.id, it will return zero records. I am not away of the ability to output strings as custom errors, but you will get an Empty Set result. You can use that result in whatever front-end language you are using to output anything you like. Does that not fit in with your plans? # Nathan - Original Message - From: Forer, Leif [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 05, 2002 11:56 AM Subject: SELECT this IF that I want to select and join data from two tables if the same id exists in both tables. If the id only exists in one table I want MySQL to return a string like sorry, no records found. How can I do this? I'm guessing it's something like: mysql SELECT tbl1.this, tbl2.that FROM tbl1, tbl2 WHERE IF (tbl1.id = tbl2.id, return the data, no record); (Obviously that's not a real query). ** This e-mail and any files transmitted with it may contain privileged or confidential information. It is solely for use by the individual for whom it is intended, even if addressed incorrectly. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited. Thank you for your compliance. - 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
Does Mysql have alias?
I was wondering if Mysql has aliases, that is, a short command representing a long MySql command. Say I want to do a show status, so I map an alias to ss and do ss when I want to do a show status. - 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 3.23 Lost Connection
Hello. I have compiled/rebuilt the MySQL 3.23.49 Source RPM under kernel 2.4.17, and glibc 2.1. Using gcc version 2.95.4 20010319 (prerelease). I had 2.96 on there and headed the warnings and downgraded to 2.95. Randomly I get the Lost Connection using PHP 4.1.2... Any idea why? Thanks Adam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Why v3.23.49 does not build on RedHat 6.2 ?????
hi! the source rpm for mysql 3.23.49 contains build instructions for gnu compilers 2.95 or newer. redhat 6.2 comes with a compiler that cannot handle the permissive flag (i think its ecgs-2.91). A-HA!!! Thanks a lot! therefore the build fails. one solution could be to install a more recent compiler version and try to recompile again. I'll try this solution before upgrading my system be aware that actual rpm's will be created by rpm version 4, whereas redhat 6.2 has rpm version 3 Yes, but v4 version has been made available. Actually I'm running 4.0.2 besides that, there may be glibc issues. redhat 6.2 has glibc 2.1.3 installed. i don't know if actual gcc or mysql versions work ok if compiled against this 'old' glibc. Binary rpms released by the mysql team were working on my system till v3.23.48. I Hope I'll be able to compile this new version against glibc 2.1 . if you dont have very specific reasons to stick with redhat 6.2 in my opinion it could be simpler just to install a later redhat version... Unfortunately my server 'lives' in another town but It looks like it will receive a visit soon. :o) not sure if this really helps... It did! Now I know the reason why the build fails! - 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: Does Mysql have alias?
At 15:34 -0500 3/5/02, Chuck Roberts wrote: I was wondering if Mysql has aliases, that is, a short command representing a long MySql command. Say I want to do a show status, so I map an alias to ss and do ss when I want to do a show status. No. - 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: InnoDB frightens me...
I sure would, thanks Monty and Tobias. In particular some sample update/insert/delete db code for MyISAM vs. InnoDB, (especially in the case where you're not adding transactions, just trying to take advantage of row-locking.) Assuming something changes, of course. The other thing is a page on replication issues. Steve Rapaport. ] -Original Message- ] From: Tobias Lind - Telia Internet [mailto:[EMAIL PROTECTED]] ] Sent: Tuesday, March 05, 2002 1:46 PM ] To: [EMAIL PROTECTED] ] Cc: [EMAIL PROTECTED] ] Subject: Re: InnoDB frightens me... ] ] ] Thanks - that would be great! ] I'm sure a lot of people will find this very helpful. ] ] Regards, ] Tobias ] ] ] - Original Message - ] From: Michael Widenius [EMAIL PROTECTED] ] To: Tobias Lind - Telia Internet [EMAIL PROTECTED] ] Cc: [EMAIL PROTECTED] ] Sent: Tuesday, March 05, 2002 12:36 PM ] Subject: Re: InnoDB frightens me... ] ] ] ] Hi! ] ] Tobias Hi! ] Tobias I'm in the same position as Steve: Considering ] switching from MyISAM to InnoDB because of ] the ] Tobias row-level locking capabilities. My application has ] quite a lot of updates/inserts mixed ] with ] Tobias selects, and is starting to suffer from the ] table-locking policy... ] Tobias And like Steve, I'm also scared! :) ] ] cut ] ] Tobias Maybe some of this confusion and fear could be avoided ] with a section in the InnoDB ] documentation ] Tobias that describes InnoDB from a MyISAM-point-of-view :) ] ] Tobias Explaining how all common things, principles and ] routines with MyISAM works with InnoDB. ] I'm sure a ] Tobias lot of people have very good knowledge of MyISAM and ] comes from the same direction as I ] do... ] ] I have forwarded this to our docmentation team and we shall try to do ] this in the near future. ] ] Regards, ] Monty ] ] -- ] For technical support contracts, goto https://order.mysql.com/ ] __ ___ ___ __ ]/ |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] ] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO ] /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ] ___/ 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: MySQL 3.23 Lost Connection
Happens randomly among all my scripts. Mysql is being started with: /usr/sbin/mysqld --basedir=/ --datadir=/usr/local/lib/mysql --user=mysql --pid-file=/usr/local/lib/mysql/mysqld.pid --skip-locking -O max_connect_errors=1 -O back_log=30 -O max_connections=580 -O table_cache=512 -O Machine has a gig of ram. Very little traffic. adam -Original Message- From: Fournier Jocelyn [Presence-PC] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 3:52 PM To: Adam Helfgott Subject: Re: MySQL 3.23 Lost Connection Hi, Perhaps a mysql thread which runs out of memory ? Regards, Jocelyn Fournier - Original Message - From: Adam Helfgott [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 05, 2002 9:32 PM Subject: MySQL 3.23 Lost Connection Hello. I have compiled/rebuilt the MySQL 3.23.49 Source RPM under kernel 2.4.17, and glibc 2.1. Using gcc version 2.95.4 20010319 (prerelease). I had 2.96 on there and headed the warnings and downgraded to 2.95. Randomly I get the Lost Connection using PHP 4.1.2... Any idea why? Thanks Adam - 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 this IF that
DN- I'm not sure what you mean by tbl2's population is a sub-set of that in tbl1. tbl1 contains user id nums and hours each user spent working on a project. tbl2 contains user id nums and the first and last names that correspond to each user id. There are user id nums in tbl1 that do not exist in tbl2 (probably because the users are no longer work here). Hope that helps clarify. The query you suggested is not working the way I hoped it would. I just want to select the hours from tbl1 and the first and last name from tbl2 where the user id from tbl1 is the same as the user id from tbl2. If, the user id in tbl1 does not exist in tbl2 I want MySQL to return 'no record'. NATHAN- Because I'm selecting multiple user ids in the query, some of which exist in both tables, some of which do not, MySQL only returns the entries that exist in both tables with a SELECT tbl1.id, tbl1.data, tbl2.name FROM tbl1, tbl2 WHERE tbl1.id = tbl2.id; That means I'm not getting all the data from tbl1 and I need to get all the data from tbl1 whether or not the id # exists in tbl2. Hope this helps clarify. ** This e-mail and any files transmitted with it may contain privileged or confidential information. It is solely for use by the individual for whom it is intended, even if addressed incorrectly. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited. Thank you for your compliance. - 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: InnoDB frightens me...
On Tue, Mar 05, 2002 at 09:58:13PM +0100, Steve Rapaport wrote: I sure would, thanks Monty and Tobias. In particular some sample update/insert/delete db code for MyISAM vs. InnoDB, (especially in the case where you're not adding transactions, just trying to take advantage of row-locking.) Assuming something changes, of course. The other thing is a page on replication issues. If you're not using transactions, the code is identical. Really. It's easy. :-) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 26 days, processed 896,354,110 queries (394/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
timezones aren't being properly handled
I have a several Redhat machines with MySQL 3.23.* installed on it. When I upgrade any of them to MySQL 3.23.49 the timezone stops being recognized. I have verified that MySQL 3.23.46 and 3.23.47 work properly by downgrading some of our servers. 3.23.49 always uses UTC according to 'mysqladmin variables' even though the system timezone is set to EST. This bug is known to affect Redhat 7.2 and Redhat 7.1. -- tani hosokawa river styx internet - 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 this IF that
Couldn't you return the data you need by using an outer join? The rows in tbl1 that aren't in tbl2 would have null values in the name fields. You could order by the name fields. Or, you could migrate the data to Oracle and use DECODE to put No record in the name fields that are null. :) --Walt -Original Message- From: Forer, Leif [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 1:58 PM To: 'DL Neil'; 'Nathan'; [EMAIL PROTECTED] Subject: RE: SELECT this IF that DN- I'm not sure what you mean by tbl2's population is a sub-set of that in tbl1. tbl1 contains user id nums and hours each user spent working on a project. tbl2 contains user id nums and the first and last names that correspond to each user id. There are user id nums in tbl1 that do not exist in tbl2 (probably because the users are no longer work here). Hope that helps clarify. The query you suggested is not working the way I hoped it would. I just want to select the hours from tbl1 and the first and last name from tbl2 where the user id from tbl1 is the same as the user id from tbl2. If, the user id in tbl1 does not exist in tbl2 I want MySQL to return 'no record'. NATHAN- Because I'm selecting multiple user ids in the query, some of which exist in both tables, some of which do not, MySQL only returns the entries that exist in both tables with a SELECT tbl1.id, tbl1.data, tbl2.name FROM tbl1, tbl2 WHERE tbl1.id = tbl2.id; That means I'm not getting all the data from tbl1 and I need to get all the data from tbl1 whether or not the id # exists in tbl2. Hope this helps clarify. ** This e-mail and any files transmitted with it may contain privileged or confidential information. It is solely for use by the individual for whom it is intended, even if addressed incorrectly. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited. Thank you for your compliance. - 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: InnoDB frightens me...
Sure seems that way to me. Same SQL engine no matter what table type you use. InnoDB didn't scare me. Pretty friendly to use. :) --Walt -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 2:09 PM To: [EMAIL PROTECTED] Cc: Tobias Lind - Telia Internet; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: InnoDB frightens me... On Tue, Mar 05, 2002 at 09:58:13PM +0100, Steve Rapaport wrote: I sure would, thanks Monty and Tobias. In particular some sample update/insert/delete db code for MyISAM vs. InnoDB, (especially in the case where you're not adding transactions, just trying to take advantage of row-locking.) Assuming something changes, of course. The other thing is a page on replication issues. If you're not using transactions, the code is identical. Really. It's easy. :-) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 26 days, processed 896,354,110 queries (394/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DROPing DATABASE can halt a replication slave.
Description: A slave replication only one of many databases from a master will fail with a error 'Can't read dir of './otherdb/' (Errcode: 2) on query 'DROP DATABASE otherdb' if the slave was not replicating that database. How-To-Repeat: Create a master with 2 databases (gooddb otherdb). Setup replication, allowing both databases to be replicated. Create a slave to replicate from the master with the line replicate-do-db=gooddb in my.cnf. After all is up and running on the master do a DROP DATABASE otherdb The slave will get the above error, even though it wasn't repicating that database it will try and remove the directory. Fix: Submitter-Id: submitter ID Originator:James Housley Organization: MySQL support: none Synopsis: DROPing DATABASE can halt a replication slave. Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.49 (FreeBSD port: mysql-server-3.23.49) Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.49, for portbld-freebsd4.4 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.49-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 9 days 7 hours 59 min 22 sec Threads: 2 Questions: 11134282 Slow queries: 53 Opens: 784 Flush tables: 1 Open tables: 256 Queries per second avg: 13.808 Environment: System: FreeBSD dt4-sfo.downloadtech.net 4.4-STABLE FreeBSD 4.4-STABLE #0: Sat Nov 24 13:02:08 PST 2001 [EMAIL PROTECTED]:/usr/src/sys/compile/DG i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.3 20010315 (release) [FreeBSD] Compilation info: CC='cc' CFLAGS='-O -pipe ' CXX='cc' CXXFLAGS='-O -pipe -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1205812 Nov 24 13:55 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 9 Nov 24 13:55 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 573100 Nov 24 13:55 /usr/lib/libc.so.4 Configure command: ./configure --localstatedir=/var/db/mysql --without-perl --without-debug --without-readline --without-bench --with-mit-threads=no --with-libwrap --with-low-memory '--with-comment=FreeBSD port: mysql-server-3.23.49' --enable-assembler --with-berkeley-db --with-innodb --with-mysqld-ldflags=--static --prefix=/usr/local i386-portbld-freebsd4.4 Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php