RE: Addendum to the MySQL 3.23.54 release
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sat, 14 Dec 2002, Richard Pijnenburg wrote: Does this also affects the 3.23.54 source? Or only the binary packages? Yes, this affected the source archives as well - sorry for omitting this in the addendum. We've now placed updated 3.23.54a packages into the Download section - it should be corrected in these. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE9/YhmSVDhKrJykfIRArm1AJ93Lc/4dvvlt4ngAg51mii9lChh0ACfa1zY ffQ829dUeYCLs0NePrlkP8w= =TdWZ -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with replication
Now is ok ... thank you very much! --- Jeremy Zawodny [EMAIL PROTECTED] ha scritto: On Wed, Dec 11, 2002 at 05:03:51PM +0100, Natale Babbo wrote: Hi all, I have a problem with replication between two servers. I created user repl on the master, I restored the master's dump on the slave, I changed both my.cnf settings but I got this error from slave .err file: MySql: ready for connections 021211 16:56:12 InnoDB: Started 021211 16:56:12 Slave I/O thread: connected to master 'repl@sviluppo01:3306', replication started in log 'FIRST' at position 4 021211 16:56:12 Error updating slave list: Query error 021211 16:56:12 Slave I/O thread exiting, read up to log 'FIRST', position 4 If your master is MySQL 4.0.x, make sure that the repl user has the REPLICATION SLAVE privilege. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 1 days, processed 37,721,449 queries (412/sec. avg) __ Yahoo! Foto: salva, ritocca, condividi e ordina stampe professionali http://it.yahoo.com/mail_it/foot/?http://it.photos.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: OS X 10.2.2: MySQL is running but I can not acces it with the terminal
Paul, nice to here from you, Could you please tell me where probably to find (directory / file) the line where PATH has to be set? Some month ago I created my databases and php-scripts on a small partition with OS X 10.1.4. Here I installed the package from Marc Lyanage including php 4 but I do not remeber the hints and small things to keep an eye to. Thereafter I changed my partition which runs now with OS X 10.2.2. I copied some visible directories within the Finder but did not install anything. I found the directory /usr/local/mysql/ and assumed everything to be ok because all databases could be accessed with Internet-Explorer and they work as always. In the StartupItems I found two files: my.conf which is called by the file MySQL. The configuration-file under my old partition should contain the right PATH - but what should be the name of this file and where should it be located. Thanks and kind regards Helmuth Lutz At 21:43 +0100 12/15/02, Helmuth Lutz wrote: Does anybody have an idea: - WHY does my currently installed MySQL 3.23.46 start at System-Start? Becasue something or someone installed the proper startup stuff under System/Library/StartupItems. - WHY do the currently installed db's work within Internet-Explorer? I have no idea what that is supposed to mean. - WHY does the terminal not know the commands: mysql or mysqladmin? Not as user neither as root The terminal response: [hlutz:/usr/local/mysql] hlutz# mysqladmin -u root user -p version mysqladmin: Command not found. Because your PATH doesn't include the directory where these programs are located. Any help is welcome. Kind regards Helmuth Lutz - 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: confirm subscribe to mysql@lists.mysql.com
On 16 Dec 2002 10:09:43 - [EMAIL PROTECTED] wrote: Hi! This is the ezmlm program. I'm managing the [EMAIL PROTECTED] mailing list. To confirm that you would like [EMAIL PROTECTED] added to the mysql mailing list, please send an empty reply to this address: [EMAIL PROTECTED] Usually, this happens when you just hit the reply button. If this does not work, simply copy the address and paste it into the To: field of a new message. This confirmation serves two purposes. First, it verifies that I am able to get mail through to you. Second, it protects you in case someone forges a subscription request in your name. Some mail programs are broken and cannot handle long addresses. If you cannot reply to this request, instead send a message to [EMAIL PROTECTED] and put the entire address listed above into the Subject: line. --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, send a message to the address in the List-Unsubscribe header of messages from the list. If you don't know how to display headers and haven't changed E-mail addresses since subscribing, you'll be successful with an e-mail to: [EMAIL PROTECTED] Send mail to the following for info and FAQ for this list: [EMAIL PROTECTED] [EMAIL PROTECTED] Similar addresses exist for the digest list: [EMAIL PROTECTED] [EMAIL PROTECTED] To get messages 123 through 145 (a maximum of 100 per request), mail: [EMAIL PROTECTED] To get an index with subject and author for messages 123-456 , mail: [EMAIL PROTECTED] They are always returned as sets of 100, max 2000 per request, so you'll actually get 100-499. To receive all messages with the same subject as message 12345, send an empty message to: [EMAIL PROTECTED] The messages do not really need to be empty, but I will ignore their content. Only the ADDRESS you send to is important. You can start a subscription for an alternate address, for example [EMAIL PROTECTED], just add a hyphen and your address (with '=' instead of '@') after the command word: [EMAIL PROTECTED] To stop subscription for this address, mail: [EMAIL PROTECTED] In both cases, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete your subscription. If despite following these instructions, you do not get the desired results, please contact my owner at [EMAIL PROTECTED] Please be patient, my owner is a lot slower than I am ;-) --- Enclosed is a copy of the request I received. Return-Path: [EMAIL PROTECTED] Received: (qmail 5141 invoked from network); 16 Dec 2002 10:09:43 - Received: from pcow035o.blueyonder.co.uk (HELO blueyonder.co.uk) (195.188.53.121) by www.mysql.com with SMTP; 16 Dec 2002 10:09:43 - Received: from pcow035o.blueyonder.co.uk ([127.0.0.1]) by blueyonder.co.uk with Microsoft SMTPSVC(5.5.1877.757.75); Mon, 16 Dec 2002 10:09:41 + Received: from fruitnfibre (unverified [62.31.78.139]) by pcow035o.blueyonder.co.uk (Content Technologies SMTPRS 4.2.9) with SMTP id [EMAIL PROTECTED] for [EMAIL PROTECTED]; Mon, 16 Dec 2002 10:09:41 + Date: Mon, 16 Dec 2002 10:10:13 + From: Intrallect Developer [EMAIL PROTECTED] To: [EMAIL PROTECTED] Message-Id: [EMAIL PROTECTED] Organization: Intrallect Ltd X-Mailer: Sylpheed version 0.8.5 (GTK+ 1.2.10; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Return-Path: [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
Segmentation fault when using MySQL++
Hi all,
I am now writing an C++ application with the use of
MySQL. When I follow the tutorial in MySQL++ manual,
the application go into segmentation fault during
execution. What's the mistake I have made?
The followings are the codes and the error messages
during execution.
Codes:
#include iostream
#include iomanip
#include sqlplus.hh
using namespace std;
main()
{
cout Zeroth endl;
Connection con(db, host, name, password);
cout First endl;
Query query = con.query();
cout Second endl;
query select * from test;
cout Third endl;
Result res = query.store();
cout Fourth endl;
cout Query: query.preview() endl;
cout Fifth endl;
}
Error Message during Execution:
Zero
First
Second
Third
Segmentation fault
And the information from gdb about segmentation fault
is follow:
#0 0x4005f42f in SQLQuery::reset (this=0xbfffef48)
at sql_query1.hh:30
30 if (n = size()) insert(end(),(n+1) -
size(), );
I have tried both Red Hat 8.0 with g++ 3.2, MySQL
4.0, sqlplus 1.7.9, and Red Hat 7.3 with g++ 2.96,
MySQL 3.23, sqlplus 1.7.9
Thanks so much!
Regards,
Leo
__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: INSERT confirmation? PHP
Steve Lefevre wrote:
I'm designing a user database for PHP 4 and MySQL 3.32 or whatever.
Users enter a class and it's workshops for their students.
The 'Class' table has fields 'Name' and 'ID'.
ID is the primary key and its an auto-incremented integer
The 'Workshop' table has a field ID, Number, ClassID, Date, etc.
The 'ClassID' should be the value of the ID field of its parent class.
After I use an insert to create the Class data, how do I get the appropriate
ClassID for the 'Workshop' table inserts? I could assume to use the very
latest one, but that *might* break, as this is a multi-user database.
Is there a way I can issue an insert statement, and get a return of the ID
value it got?
You can remove auto-increment and write a function that returns the max
id like
function getMaxId() {
$sql= mysql_query(select max(ID) as max_id from Class);
$rs = mysql_fetch_array($sql);
$id = $rs[max_id];
$id++;
return $id;
}
$ID = getMaxId();
Giannis
-
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: INSERT confirmation? PHP
Steve Lefevre wrote:
I'm designing a user database for PHP 4 and MySQL 3.32 or whatever.
Users enter a class and it's workshops for their students.
The 'Class' table has fields 'Name' and 'ID'.
ID is the primary key and its an auto-incremented integer
The 'Workshop' table has a field ID, Number, ClassID, Date, etc.
The 'ClassID' should be the value of the ID field of its parent class.
After I use an insert to create the Class data, how do I get the
appropriate
ClassID for the 'Workshop' table inserts? I could assume to use the very
latest one, but that *might* break, as this is a multi-user database.
Is there a way I can issue an insert statement, and get a return of
the ID
value it got?
You can remove auto-increment and write a function that returns the max
id like
function getMaxId() {
$sql= mysql_query(select max(ID) as max_id from Class);
$rs = mysql_fetch_array($sql);
$id = $rs[max_id];
$id++;
return $id;
}
$ID = getMaxId();
Giannis
-
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: global autocommit setting
Jari, I am Cc:ing this to [EMAIL PROTECTED], so that other readers can follow the discussion. There is still no my.cnf option to set AUTOCOMMIT=0 globally though users have requested it a lot. I think that 4.1 will support a SQL script which is automatically executed for all new connections. That is naturally the most versatile method of session initialization. Then you could write the SQL command SET AUTOCOMMIT=0 to that script. I am Cc:ing this to the MySQL AB developer who is responsible for global variables. He can inform us of the status of 4.1. If the 4.1 SQL script method will be delayed, then I have to consider adding a new option autocommit=0 to my.cnf. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com Original Message - From: Jari Nurminen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 16, 2002 9:08 AM Subject: global autocommit setting I've read some discussion about a global autocommit setting i.e. set a database default behaviour to autocommit=false, so that the application doesn't have to use SET AUTOCOMMIT=0 method for every session. This is actually what you would expect when using transactional databases like InnoDB. It seems that this feature is/will be included in MySQL, and now my question is, is it already there? For example Heikki from Innodb wrote in July 2002 (http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:115791) that it would be in v4.0 around September but I failed to see an announcement if it is already there ... Cheers and thanks in advance, - Jari Nurminen - 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.54 for Solaris 7
Hello, will a binary package of MySQL 3.23.53 for Solaris 2.7 be released? Regards, David OBando - 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
question!!
hi everybody! i have a problem with mysql, i am trying to create a new database as follows: $ cd /usr/local/mysql $ ./bin/mysql -u root mysql -p enter Password: sql create database cardholder; but i get the following error message: ERROR 1006: Can't create database 'cardholder'. (errno: 28) can anybody help me with this asasp? i'll be very greatful. thanks to all. - 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
Long text with index in InnoDB
Hi everybody, I'm thinking about switching to innodb, but face the following problem: I've got a column with texts longer than 256 chars and I need an index on it (for having acceptable times for a query). So i can't use varchar and indexes are not possible in InnoDB in text/blob, so I can't use this either. Is there a solution, i.e. is there a data type for text longer 256 chars and with indexing in InnoDB ? Thanks for your answers Stefan -- Stefan Kuhn M. A. MPI of Chemical Ecology, Winzerlaer Str. 10, Beutenberg Campus, 07745 Jena, Germany Tel: +49(0)3641 571261 - Fax: +49(0)3641 571202 --- -- Stefan Kuhn M. A. MPI of Chemical Ecology, Winzerlaer Str. 10, Beutenberg Campus, 07745 Jena, Germany Tel: +49(0)3641 571261 - Fax: +49(0)3641 571202 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: question!!
[root@www tmp]# perror 28 Error code 28: No space left on device You don't have any more space avaiable on your hdd . - Original Message - From: Touria Zaddaoui [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 16, 2002 1:49 PM Subject: question!! hi everybody! i have a problem with mysql, i am trying to create a new database as follows: $ cd /usr/local/mysql $ ./bin/mysql -u root mysql -p enter Password: sql create database cardholder; but i get the following error message: ERROR 1006: Can't create database 'cardholder'. (errno: 28) can anybody help me with this asasp? i'll be very greatful. thanks to all. - 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.54 for Solaris 7
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 16 Dec 2002, Obando, David DE - EV wrote: will a binary package of MySQL 3.23.53 for Solaris 2.7 be released? Unfortunately we are currently unable to provide binaries for Solaris 2.7 - - our build host is currently down due to a hardware problem. It's currently unsure, when it will be back up. Sorry for the inconvenience! Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE9/cVBSVDhKrJykfIRArHUAJwOtFvWKeSd9iqqoe/iLC06J0+PtgCcD78D yCpaD8h1NPH8wS13rsLnOtc= =C6a5 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Long text with index in InnoDB
Stefan, - Original Message - From: Stefan Kuhn [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, December 16, 2002 2:11 PM Subject: Long text with index in InnoDB Hi everybody, I'm thinking about switching to innodb, but face the following problem: I've got a column with texts longer than 256 chars and I need an index on it (for having acceptable times for a query). So i can't use varchar and indexes are not possible in InnoDB in text/blob, so I can't use this either. Is there a solution, i.e. is there a data type for text longer 256 chars and with indexing in InnoDB ? the TODO list for InnoDB-4.0.x is December 2002: AWE support for Windows. January 2003: prefix indexes for TEXT and BLOB. Thus, the support is coming soon. Thanks for your answers Stefan -- Stefan Kuhn M. A. MPI of Chemical Ecology, Winzerlaer Str. 10, Beutenberg Campus, 07745 Jena, Germany Tel: +49(0)3641 571261 - Fax: +49(0)3641 571202 Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB 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
MySQL Clusters
I have been given a task to design a MySQL Backup server on which the client requests should fall back in case the main MySQL Server is either busy, the network connection to MySQL Server has broken. I can imagine this should be possible only if a proxy server is in between the Clients and the MySQL Servers. And the proxy deciding on the Load Balancing. I have read an article Oracle Clusters some time back but i don't know whether such feature is available in MySQL. I using 3.23.41 Max Version on a Mandrake Linux 8.2. Its a RPM installed server. i hope the gurus out here to help me. I've to report on this urgently. Regards Shomal - 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: bug in JDBC driver
Hi ! Some times my application is hang-up. The stack trace contains the following: at java.net.SocketInputStream.socketRead(Native Method) at java.net.SocketInputStream.read(SocketInputStream.java:86) at java.io.BufferedInputStream.fill(BufferedInputStream.java:186) at java.io.BufferedInputStream.read(BufferedInputStream.java:204) at org.gjt.mm.mysql.MysqlIO.readPacket(Unknown Source) at org.gjt.mm.mysql.MysqlIO.clearAllReceive(Unknown Source) at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(Unknown Source) at org.gjt.mm.mysql.MysqlIO.sqlQuery(Unknown Source) at org.gjt.mm.mysql.Connection.execSQL(Unknown Source) at org.gjt.mm.mysql.Connection.execSQL(Unknown Source) at org.gjt.mm.mysql.Statement.executeQuery(Unknown Source) at org.gjt.mm.mysql.jdbc2.Statement.executeQuery(Unknown Source) It seems that it is not infinite loop (i.e. there was only once org.gjt.mm.mysql.jdbc2.Statement.executeQuery). Do you have any idea what is wrong ? I think that the reason of bug is in the BufferedInputStream (see on Sun BugParade http://developer.java.sun.com/developer/bugParade/bugs/4479751.html). MySql client need to read N bytes from input stream. BufferedInputStream tries to fill its buffer and try to read from server more than N bytes (to fill entire buffer). The MySql server has exactly N bytes to send. The server sends N bytes and waits for a request. But the client doesn't receive response, since it (or end part of it) is in the BufferedInputStream buffer. I might be wrong, but I usually see the same stack trace. I need to restart my application. Best, Dmitry. - 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
PHP and MySQL 4.0.x
I am trying to setup PHP 4.2.x wth MySQL 4.0.x Somewhat new to this, I installed PHP via the redhat RPM. PHP is being loaded as a module to httpd. The error I keep getting is that httpd can't find libmysqlclient.so.10 and MySQL 4.0.x rpm installed libmysqlclient.so.11. I tried making a symlink from 11 to 10, but that just caused httpd to crash. Is there a newer php mysql.so libary? Does anyone have any ideas? sean. - 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
FULLTEXT index time
I'm testing the FULLTEXT indexing on a TEXT field on an Athlon/500, 256MB RAM, SCSI-3 disks, FreeBSD 4.6 and MySQL 3.23.46 with my.cnf.large setup. My table has 26 records and it seems to take too long to complete the task. It's currently running since yestarday. Is this processing time normal or not? Are fulltext indexing speed tests available? mysql show processlist; +-+--+---+-+-+---+-- +-+ | Id | User | Host | db | Command | Time | State | Info| +-+--+---+-+-+---+-- +-+ | 16 | root | localhost | gufiweb | Query | 87924 | Repair with keycache | alter table gufiall add fulltext body(body) | | 138 | root | localhost | gufiweb | Query | 0 | NULL | show processlist| +-+--+---+-+-+---+-- +-+ 2 rows in set (1.74 sec) Gianluca Sordiglioni DB Manager Vox2Web Srl tel. 0245054533 - 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 - Upgrading from 3.23.53 to 3.23.54
Hi, I have installed MySQL 2.23.53 release and I want to upgrade to MySQL 2.23.54... how can I do this? Have i got to reinstall? Is there any script? Any suggest? Thanks in advance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
3.23.55
Hi people, what is the status of version 3.23.55? It's already available for download from www.mysql.com, but the links on the official download page all points to 3.23.54a... regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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
more about using sets
Hi, all -- I'm still trying to get a good handle on how sets can be useful to me. I have three scenarios so far. 1) A set of states (US Mail type, not turing type :-) I can pick from the list of states when entering address data, and storing the set entry should take less space than storing even a 2-char string. 2) A set of ccard types (MC, Visa, AmEx) It's easy to have a pick list to avoid misspellings and such 3) A set of pay scale levels (master, journeyman, apprentice, intern) Each staff member needs to be at a certain scale so that the software knows how much to pay him or her per session. We don't want to make up pay levels that aren't in our list. For each of these, is a set the way to go, and is it saving me anything? In the third case, I want to restrict the level in the personnel table to one of the defined levels. Do I just use a set in the field definition and then list from there (and then it gets messy if we add a new level) or do I create a jobscalelevels table and define the levels in there and then set the personnel.level field to match and forget about the idea of a set? TIA HAND Happy Holidays mysql query, :-D -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! - 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: Automatically totalling columns.
On Sunday 15 December 2002 17:08, Beauford wrote: I have two tables, one with peoples names in it and one with data associated to these people. What I need to do is to be able to automatically total certain items in the data table that correspond with a name in the first table and then update the name table.. i.e. This is a crude example, but you get the picture. When I add an item to the data table I want to have it so it automatically gets added to the correct person in the name table. I also have a third table which references these two tables. Can this be done from within MySQL. Automatically - nope. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Root user password changing
On Saturday 14 December 2002 23:07, tlr7425 at garnet dot acns dot fsu dot edu wrote: how exactly do I change the Root user's password for mysql? like this? : mysqladmin -u root -p'newpasswd' ? (without a space between -p and 'newpasswd' ?) and, if that is correct, I guess I'd then be presented with a prompt for the old passwd, right? Then, the new one would take effect? mysqladmin -u root -p password newpassword and you get a prompt for an old password. For more info take a look at: http://www.mysql.com/doc/en/Passwords.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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.host error.
On Monday 16 December 2002 08:51, Matthieu Lalonde wrote: Hi, I'm trying to get mysql workin' and have had the same prob on both OS X sever and NetBSD... here is what's spit out of the .err file: 021215 22:33:00 mysqld started 021215 22:33:02 /usr/libexec/mysqld: Table 'mysql.host' doesn't exist 021215 22:33:02 mysqld ended and when i run safe_mysqld: [hete:local/mysql/data] xsmurf# safe_mysqld --u=mysql [1] 1040 [hete:local/mysql/data] xsmurf# Starting mysqld daemon with databases from /var/mysql 021216 01:48:37 mysqld ended [1]Done safe_mysqld --u=mysql I have ran the install_db script and they all seam to be there... looked on the doc page of mysql.com but I couldn't find any pointers... Look in the error log files. Check that MySQL is owner of the data dir. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: What is wrong with this Alter statement?
On Tuesday 17 December 2002 09:43, C. Reeve wrote: From the MySQL Manual: ALTER TABLE tbl_name MODIFY [COLUMN] create_definition [FIRST | AFTER column_name] I have also tried CHANGE instead of MODIFY. My Statement: alter table manager change column w1 decimal(8,2) not null after moves; The problem is the AFTER statement, if I omit it I have no problem. Now I'm probably reading this wrong but in any event I need my columns to be in a certain order and their not. Without deleting them and re-adding them (and the data) - how do I get them in the order I need? i.e w1, w2, w3, w4 .. w25 not w13, w21, w2, w9 w4, w1. (I have no idea how they even got messed up as I created them in order. Up arrow, change number, up arrow, change numberetc, etc. What version of MySQL do you use? AFTER keyword in CHANGE or MODIFY is supported only since 4.0.1 version. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: What is relay-bin log?
On Mon, 2002-10-21 at 14:25, Michael Widenius wrote: Which log files is it that you want to delete ? (Please give an example for this) If you have a problem that old relay logs are not deleted, try to upgrade to MySQL 4.0.4 ; We recently fixed a bug in this code. This is still happening with 4.0.5a (Std, with InnoDB). # grep relay /service/svc-mysql/run --relay-log=/mysql/logs/relay \ --relay-log-info-file=/mysql/logs/relay.info \ --relay_log_space_limit=500M \ # ls -al (...) -rw-rw1 mysqlmysql541557913 Dec 16 17:04 relay.001 (...) # mysqladmin processlist (...) | 2 | system user | none | | Connect | 539| Waiting for relay log space to free | (...) # mysqladmin shutdown nothing happens # svc -t /service/svc-mysql nothing still # svc -k /service/svc-mysql ; rm relay.* this works ... # mysql --version mysql Ver 12.16 Distrib 4.0.5a-beta, for pc-linux (i686) # /usr/local/libexec/mysqld --version /usr/local/libexec/mysqld Ver 4.0.5a-beta for pc-linux on i686 -- Nicolas MONNET (Tech) [EMAIL PROTECTED] Carpe Diem - 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: Using MS Access as front end to mysql database
Just be careful because the connection can be looked at and if there is sensitive data going through someone who wants to can get their hands on it. The data travelling from the server to the client is not encripted and as far as I know there are not any hosts that will offer MySQL with the SSL support as it is not 100% working yet. Just something to think about. JFernando ** sql ** -Original Message- From: Michael She [mailto:[EMAIL PROTECTED]] Sent: December 14, 2002 16:33 To: JamesD Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Using MS Access as front end to mysql database Yup, agreed. MySQLODBC works over TCP/IP fine. I'm currently tunneling through a Linksys router to a database I have at home. If your provider is unwilling to compromise, it maybe time to look for another host. There are plenty of MySQL and MS SQL hosts who allow TCP/IP connections. At 11:56 AM 12/14/2002 -0800, JamesD wrote: they can do it. they just dont want to. firewall rules can be established to allow connects to the mysql port from only the ip's assoc with your remote offices, etc etc the answer is you can do what you want. You just need a business partner who can service his customer properly. mysqlODBC can do what you want. where mysql is located is irrelevant, in the grand sense. you just point to an ip and a port. etc etc James Danforth -Original Message- From: Donna Flanders [mailto:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 6:59 AM To: [EMAIL PROTECTED] Subject: Using MS Access as front end to mysql database We have been informed by our web hosting site that we cannot connect remotely to our mysql database using any kind of connection other than a script from a web page or phpMyAdmin. It is our intent to have our data stored in a mysql database on the web and use MS Access as the front end to the database. Our reasons are - we have an existing Access database with a small number of users at different locations that need to be entering data, running reports and queries. We also have a large number of agencies that would hit the database. It was our intent to migrate the data tables to mysql and leave MS Access as the front end. When we asked our web host about this, they responded this way: Due to security concerns you would not be able to connect to our mysql server thru an external terminal directly as a mysql client. You would be able to use mysql services only thru Web sevices i.e either thru php scripts or thru phpmyadmin in Control panel--Advanced Menu--Sql Databases _ My questions are - Is this true of all web hosts or just the one we are using? Our current hosting plan is a shared hosting plan. Do we have to have a dedicated database server to do this? Knowing that we want to keep MS Access as the front end, are there other options we have not explored? - 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 -- Michael She : [EMAIL PROTECTED] Mobile : (519) 589-7309 WWW Homepage : http://www.binaryio.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: ADODB XML Update Recordset
How are you working with them? They are working form me doing the following: In one side this is what I do. Set rst = dbConn.Execute(SELECT STATMENT) strFileName = C:\\Path\file.xml rst.Save strFileName, 1 'adPersistXML Then to read it back I just do this? strFileName = C:\\Path\file.xml rst.Open strFileName That works for me HTH JFernando ** sql ** -Original Message- From: Karl Stubsjoen [mailto:[EMAIL PROTECTED]] Sent: December 15, 2002 00:46 To: [EMAIL PROTECTED] Subject: ADODB XML Update Recordset Has anyone any luck performing batch update with a persisted ADO XML recordset? I'm trying to save data from one MySQL server and then upload/insert to another. I'm trying to get these persisted XML recordsets to work, but having no luck. Any ideas? Karl - 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: Using MS Access as front end to mysql database
Depending on your OS, you might be able to implement security through the network layer via IPSec. I run mySQL between two Windows2k servers, with the mySQL server requiring the web server (in this case, a remote client to the mySQL server) to establish an IPSec tunnel in order to connect. It works very well. However, this perhaps is only a good solution where the servers/workstations involved are static and well defined. mysql query Mike Grabski -Original Message- From: Fernando Grijalba [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 10:17 AM To: MySQL Help Subject: RE: Using MS Access as front end to mysql database Just be careful because the connection can be looked at and if there is sensitive data going through someone who wants to can get their hands on it. The data travelling from the server to the client is not encripted and as far as I know there are not any hosts that will offer MySQL with the SSL support as it is not 100% working yet. Just something to think about. JFernando ** sql ** -Original Message- From: Michael She [mailto:[EMAIL PROTECTED]] Sent: December 14, 2002 16:33 To: JamesD Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Using MS Access as front end to mysql database Yup, agreed. MySQLODBC works over TCP/IP fine. I'm currently tunneling through a Linksys router to a database I have at home. If your provider is unwilling to compromise, it maybe time to look for another host. There are plenty of MySQL and MS SQL hosts who allow TCP/IP connections. At 11:56 AM 12/14/2002 -0800, JamesD wrote: they can do it. they just dont want to. firewall rules can be established to allow connects to the mysql port from only the ip's assoc with your remote offices, etc etc the answer is you can do what you want. You just need a business partner who can service his customer properly. mysqlODBC can do what you want. where mysql is located is irrelevant, in the grand sense. you just point to an ip and a port. etc etc James Danforth -Original Message- From: Donna Flanders [mailto:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 6:59 AM To: [EMAIL PROTECTED] Subject: Using MS Access as front end to mysql database We have been informed by our web hosting site that we cannot connect remotely to our mysql database using any kind of connection other than a script from a web page or phpMyAdmin. It is our intent to have our data stored in a mysql database on the web and use MS Access as the front end to the database. Our reasons are - we have an existing Access database with a small number of users at different locations that need to be entering data, running reports and queries. We also have a large number of agencies that would hit the database. It was our intent to migrate the data tables to mysql and leave MS Access as the front end. When we asked our web host about this, they responded this way: Due to security concerns you would not be able to connect to our mysql server thru an external terminal directly as a mysql client. You would be able to use mysql services only thru Web sevices i.e either thru php scripts or thru phpmyadmin in Control panel--Advanced Menu--Sql Databases _ My questions are - Is this true of all web hosts or just the one we are using? Our current hosting plan is a shared hosting plan. Do we have to have a dedicated database server to do this? Knowing that we want to keep MS Access as the front end, are there other options we have not explored? - 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 -- Michael She : [EMAIL PROTECTED] Mobile : (519) 589-7309 WWW Homepage : http://www.binaryio.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
Inventory Query
Hello list. Could you please suggest me a one-line query to solve a problem of inventory ? Three table: Products - | id | description | - | 01 | bread| | 02 | milk | | 03 | coffee | - Purchases - | prod_id | quantities | - | 01| 10 | | 02| 5 | | 03| 3 | - Sellings - | prod_id | quantities | - | 01| 3 | | 01| 1 | | 02| 1 | | 02| 1 | - The query should return - | prod_id | description | purchases | sellings | inventory | - |01 | bread|10 | 4 | 6 | |02 | milk |5 | 2 | 3 | |03 | coffee |3 | 0 | 3 | - Thank you very much for your help. Gigi - 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
Primary Key Question
I understood that MySQL didn't internally keep up with the relationships between tables... like MS Access... And that it was up to the programmer to referential integrity... But I noticed in phpMyAdmin that the offer the option of defining a column in a table as 'Primary' Am I confused on this or is this something that phpMyAdmin takes does ?? thanks for any help. tmb __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Compile 3.23.53 error on HPUX 11.11
Hi, I just send the one again, hope to find someone to help me out with MySQL compile. I am using HPUX 11.11, gcc 3.1. Thanks! Zengfa 1. CFLAGS=-fomit-frame-pointer -O3 -fpic CXX=gcc CXXFLAGS=-felid-constructors -fno-exceptions -fno-exceptions -fno-rtti -O3 ./configure --disable-shared Thank you for choosing MySQL! 2. make Making all in sql Making all in share No suffix list. ../../extra/comp_err czech/errmsg.sys Usage: ../../extra/comp_err [-?] [-I] [-V] fromfile[s] tofile *** Error exit code 255 Stop. *** Error exit code 1 Stop. *** Error exit code 1 Stop. *** Error exit code 1 Some config.log: The file is too big, I can see all. configure:2216: gcc -c -fomit-frame-pointer -O3 -fpic conftest.c 5 conftest.c:2: parse error before me configure:2219: $? = 1 configure: failed program was: #ifndef __cplusplus choke me #endif configure:2404: checking for C++ compiler version configure:2407: gcc --version /dev/null 5 gcc (GCC) 3.1 configure:5494: gcc -o conftest -fomit-frame-pointer -O3 -fpic -D__STDC_EXT__ conftest.c -ldir 5 command line:1:1: warning: __STDC_EXT__ redefined command line:1:1: warning: this is the location of the previous definition /usr/ccs/bin/ld: Can't find library for -ldir collect2: ld returned 1 exit status configure:5497: $? = 1 configure: failed program was: #line 5475 configure #include confdefs.h /* Override any gcc2 internal prototype to avoid an error. */ #ifdef __cplusplus extern C #endif /* We use char because int might match the return type of a gcc2 builtin and then its argument prototype would still apply. */ char opendir (); command line:1:1: warning: __STDC_EXT__ redefined command line:1:1: warning: this is the location of the previous definition configure:6818: parse error before ')' token configure: In function `main': configure:6822: parse error before ')' token configure:6832: $? = 1 configure: failed program was: #line 6810 configure #include confdefs.h configure: In function `main': configure:7291: `int8' undeclared (first use in this function) configure:7291: (Each undeclared identifier is reported only once configure:7291: for each function it appears in.) configure:7291: parse error before i configure:7301: $? = 1 configure: program exited with status 1 configure: In function `main': configure:10120: `bogus' undeclared (first use in this function) configure:10120: (Each undeclared identifier is reported only once configure:10120: for each function it appears in.) configure:10120: parse error before endian configure:10132: $? = 1 __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: more about using sets
David T-G wrote:
I'm still trying to get a good handle on how sets can be useful to me.
You're thinking of ENUMs. SETs are for when you want multiple things
selected out of a list. So perhaps, favorite colours:
You have a column Colours
SET('black','purple','blue','green','yellow','orange','red','white');
Then you can do a query like:
UPDATE FavoriteColours SET Colours = ('yellow','blue','green') WHERE ID = 4;
(The 'SET' keyword in the UPDATE command is purely coincidental)
Anyhow, ENUMs are the same as having a second table with a foreign key
relation to the current table and SETs are the same as having a table
that you double-join against to match up multiple values.
PS 'same' here means it has pretty much the same functionality as ...
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock
-
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: Fwd: Re: Bug in auto_increment
Paul DuBois wrote: manner. AUTO_INCREMENT columns are for use only with *positive* integers, and any attempt to use them otherwise will eventually cause you grief. Just to point out, a lot of people get confused about the definition of positive integers, especially in how it relates to zero. A link to Dr. Math might help, fwiw: http://mathforum.org/dr.math/faq/faq.integers.html :) However, I'd be careful to point out in the docs (and I haven't checked lately, so it may be there) that this does in fact mean that the smallest value an AUTO_INCREMENT column should hold is the positive integer value 1. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. (SQL) http://www.fibrespeed.net/~mbabcock - 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: Problem opening/finding default database when running mysqld through 'safe_mysqld ' command...
On Friday 13 December 2002 09:16, Chris Wagner wrote: Firstly, I don't believe this is a bug, but I really can't figure it out. I've installed MySQL through the Red Hat 8 distribution CDs. When I run safe_mysqld, I have the following message: [root@localhost bin]# safe_mysqld [1] 25488 [root@localhost bin]# Starting mysqld daemon with databases from /var/lib/mysql 021213 01:53:09 mysqld ended I check the log file, /var/log/mysqld.log, and it says: /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) The log file changes each time I execute safe_mysqld, therefore I know this is the log file it is using. I have run the mysql_install_db script and it placed all of the default stuff (including host.frm) in /var/lib/mysql/mysql/. The permissions are all checked for 'User' and I am logged in as root. I've tried setting all the permissions completely for everyone and tried copying all the default database files to /var/lib/mysql/. Is user 'mysql' owner of the MySQL data dir ? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Major upgrade
On Friday 13 December 2002 19:48, Ewan \\MEB\\ Oughton wrote: I just became the database admin on this server, my first job was to upgrade the mysql install, it was running 3.22.30, I upgraded it to 3.23.54 Seemed to go OK, just want to check... I moved all the databases from var in the old install to data in the new install... all the databases seem to be there and user logins would appear to work fine... has there been any cases of loss of data? I have a full dump of all the data if it turns out i have to propogate the data that way instead... In general, usage of mysqldump is more recommended than direct coping of table files. And of course you better use mysqldump if upgrading since 3.22 as the table files format is changed. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL 3.23.54 for Solaris 7
I am running the Solaris 8 binary on all of my Ultra Sparc II Solaris 7 servers with no problems at this time, however I am not doing any of the advanced features, such as MySQL-Max or replication. Nickg -Original Message- From: Lenz Grimmer [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 6:21 AM To: Obando, David DE - EV Cc: '[EMAIL PROTECTED]' Subject: Re: MySQL 3.23.54 for Solaris 7 -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 16 Dec 2002, Obando, David DE - EV wrote: will a binary package of MySQL 3.23.53 for Solaris 2.7 be released? Unfortunately we are currently unable to provide binaries for Solaris 2.7 - - our build host is currently down due to a hardware problem. It's currently unsure, when it will be back up. Sorry for the inconvenience! Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE9/cVBSVDhKrJykfIRArHUAJwOtFvWKeSd9iqqoe/iLC06J0+PtgCcD78D yCpaD8h1NPH8wS13rsLnOtc= =C6a5 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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 Memory Configuration
Hello, I am setting up a mySQL box in my development environment and have noticed that even under little usage mysql will consume a couple hundred MB's of swap space even though I have almost 1GB of apparent free physical memory. I am using the 'my-large.cnf' configuration file and otherwise haven't modified anything. The server is a 400Mhz sparc w/ 2GB ram and 1 SCSI disk. Can someone please provide some guidance as to whether this is normal or am I not configuring something properly. Thanks, Jamie Sullivan EOS, Inc. 717 South Grand Avenue West Springfield, IL 62704 217-241-6000 ext.104 [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
Query Problem...
I have this SQL Query: SELECT club . * , MAX( story.stid ) , story.longheadline FROM club, story WHERE club.sid = story.siteid AND club.div = 'epl' and club.test = 0 and story.synd = '1' GROUP BY club.sid ORDER BY club.clubname ASC The problem is that its not displaying the 'story.longheadline' that correlates with the MAX(story.stid) Any clues? I'm lost Cheers John Wards - 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: Primary Key Question
On Monday 16 December 2002 18:12, tmb wrote: I understood that MySQL didn't internally keep up with the relationships between tables... like MS Access... And that it was up to the programmer to referential integrity... But I noticed in phpMyAdmin that the offer the option of defining a column in a table as 'Primary' Am I confused on this or is this something that phpMyAdmin takes does ?? Yes, you can define column as a primary key. It uniquely references to a particular record in the table. So values must be unique and non-null. What exactly confused you? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Automatically totalling columns.
This is called storing derived data and it's a Bad Thing. (tm) It denormalizes your schema. The real problem is that derived data stored in a field has a tendency to get out of sync with the data it is derived from. Therefore you end up writing programs that wipe it and put it back in sync and all kinds of nastiness. it's just easier to compute the data on the fly, when you need it. What you really need is a select statement to gather this data together any time you want to use it. There are obvious exceptions. (say for speed purposes) but you should always strive for a normalized schema. Example: Tables--- Person --- personID (PK) === name --- purchase --- purchaseID (PK) === personID (FK) name amount --- Data--- person --- 1 Fred 2 Barney 3 Wilma 4 Betty purchase --- 1 1 milk 4.50 2 1 eggs 2.00 3 3 diamonds 10,000.00 4 1 gun 25.00 Select person.name, sum(purchase.amount) as total from person left join purchase on person.personID = purchase.personID group by personID order by name; Now, by executing this statement you can see how much each person has purchases without storing these totals in the database. HTH, =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Beauford [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 15, 2002 9:08 AM To: MySQL List Subject: Automatically totalling columns. Hi, I have two tables, one with peoples names in it and one with data associated to these people. What I need to do is to be able to automatically total certain items in the data table that correspond with a name in the first table and then update the name table.. i.e. This is a crude example, but you get the picture. When I add an item to the data table I want to have it so it automatically gets added to the correct person in the name table. I also have a third table which references these two tables. Can this be done from within MySQL. Names Data Col. 1 Col 2 Col. 1Col. 2 Fred$2.24Milk$1.00 Wilma $4.25Bread $1.24 Barney $1.00Eggs $2.19 Betty$3.19Butter $3.25 - 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: What is relay-bin log?
On Mon, 2002-12-16 at 16:20, Nicolas MONNET (Tech) wrote: (...) Nevermind. This was due to an error in the replication; but nonetheless, it is impossible to shutdown mysqld cleanly if it's waiting for relay logs to be freed. -- Nicolas MONNET (Tech) [EMAIL PROTECTED] Carpe Diem - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Access to MySQL
Hello, Is there a small program that would transfer the contents of an Access database to MySQL? TIA, Yves Arsenault Carrefour Infotech 5,promenade Acadian Charlottetown, IPE C1C 1M2 [EMAIL PROTECTED] (902)368-1895 ext.242 ICQ #117650823 - 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 Memory Configuration
On Mon, Dec 16, 2002 at 10:43:28AM -0600, Jamie Sullivan wrote: Hello, I am setting up a mySQL box in my development environment and have noticed that even under little usage mysql will consume a couple hundred MB's of swap space even though I have almost 1GB of apparent free physical memory. I am using the 'my-large.cnf' configuration file and otherwise haven't modified anything. The server is a 400Mhz sparc w/ 2GB ram and 1 SCSI disk. Can someone please provide some guidance as to whether this is normal or am I not configuring something properly. What OS are you using? Some versions of the Linux kernel had problems that caused it to needlessly swap out MySQL memory when there was lots of memory still available. Here was some of my original discussion on the topic: http://jeremy.zawodny.com/blog/archives/000132.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 1 days, processed 55,530,181 queries (426/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
Need Help phpMyAdmin !!
Hello every one At the first, I'm Sorry for my bad english. I want to install phpMyAdmin for managing My MySql databases, I Downloaded ths zip file frome here: http://sourceforge.net/project/showfiles.php?group_id=23067 the file name is phpMyAdmin-2.3.3pl1-php3.zip I unzip this file on my Mysql Default folder: C:\inetpub\Mysql but I dont know how to make after for display the MySQL Admin HTML page: http://localhost/phpmyadmin/index.php Some one have a proposition for me. I am just a newbbie in MySQL and PHP development. Thanks. -- |-Rachid Abdelkhalak |-Account Manager |-MTDS S.A. |-tel +212.3.767.4861 |-fax +212.3.767.4863 |-14, rue 16 novembre |-Rabat, Kingdom of MoroccO - 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 Memory Configuration
At 09:20 AM 12/16/2002 -0800, you wrote: On Mon, Dec 16, 2002 at 10:43:28AM -0600, Jamie Sullivan wrote: Hello, I am setting up a mySQL box in my development environment and have noticed that even under little usage mysql will consume a couple hundred MB's of swap space even though I have almost 1GB of apparent free physical memory. I am using the 'my-large.cnf' configuration file and otherwise haven't modified anything. The server is a 400Mhz sparc w/ 2GB ram and 1 SCSI disk. Can someone please provide some guidance as to whether this is normal or am I not configuring something properly. What OS are you using? Some versions of the Linux kernel had problems that caused it to needlessly swap out MySQL memory when there was lots of memory still available. Here was some of my original discussion on the topic: http://jeremy.zawodny.com/blog/archives/000132.html Jeremy The system is Solaris 8. I have even attempted to significantly increase the sizes in my.cnf to no avail. Thanks, Jamie Sullivan EOS, Inc. 717 South Grand Avenue West Springfield, IL 62704 217-241-6000 ext.104 [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
FLUSH TABLES WITH READ LOCK data corruption (the followup)
Hello, Still on this database consistency problem with FLUSH TABLES WITH READ LOCK... I've tried upgrading to MySQL 4.0.5a binary distribution (instead of source), but the problem does not disappear. So I've made a little test. I open too shells. - Shell 1: I launch the MySQL client. Then type : mysql FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (1.00 sec) I don't close the client. - Shell 2: I go the MySQL data directory and type: myisamchk -Ts */*.MYI I get: myisamchk: MyISAM file isis_international/delta_flow.MYI myisamchk: warning: 1 clients is using or hasn't closed the table properly MyISAM-table 'isis_international/delta_flow.MYI' is usable but should be fixed I abort after this first error as checking 20 GB of database would be too long. - Shell 1: mysql UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql CHECK TABLE isis_international.delta_flow; +---+---+--++| + Table | Op| Msg_type | Msg_text | +---+---+--++| + isis_international.delta_flow | check | warning | 1 clients is using or hasn't closed the table properly | | isis_international.delta_flow | check | status | OK | +---+---+--++2 + rows in set (0.06 sec) This confirms that FLUSH TABLES WITH READ LOCK has not closed all tables properly. Some data has been lost and/or corrupted on the way. By the way : the system has SCSI drives and ECC memory. I've never experienced any data corruption outside of the FLUSH TABLES WITH READ LOCK problem. System: Linux *.fr 2.4.19 #3 SMP Fri Aug 9 15:18:06 CEST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/ccGCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.3 2.96-113) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Jul 18 13:18 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so -rw-r--r--1 root root 2310808 Apr 15 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 15 2002 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' Is there something I can do to investigate further into this ? Thank you Antoine. - 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 speed up the Left Join on big tables?
Hi,
A simple left join on two big table took 5 mins to
finish.
Here is the explain
mysql explain select count(*) from newSequence s left
join newSequence_Homolog h on s.Seq_ID = h.Seq_ID;
+---++---+-+-+--+-+-+
| table | type | possible_keys | key | key_len |
ref | rows| Extra |
+---++---+-+-+--+-+-+
| s | index | NULL | PRIMARY | 50 |
NULL | 2684094 | Using index |
| h | eq_ref | PRIMARY | PRIMARY | 50 |
s.Seq_ID | 1 | Using index |
+---++---+-+-+--+-+-+
2 rows in set (0.00 sec)
here are the two tables' definitaion
mysql describe newSequence;
+-++--+-++---+
| Field | Type
| Null | Key | Default| Extra |
+-++--+-++---+
| Seq_ID | varchar(50)
| | PRI || |
| GenBank_Acc | varchar(10)
| YES | MUL | NULL | |
| Organism| varchar(50)
| | MUL || |
| Seq_Type| enum('EST','GSS','EST Contig','EST
Singlet','GSS Contig','GSS Singlet','GSS Plasmid
Contig','Protein') | | MUL | EST| |
| Seq_Length | int(11)
| | | 0 | |
| Seq_Title | text
| | MUL || |
| Comment | text
| YES | MUL | NULL | |
| Entry_Date | date
| | | -00-00 | |
+-++--+-++---+
8 rows in set (0.00 sec)
There are 2684094 records on this table.
mysql describe newSequence_Homolog;
+--+-+--+-+-+---+
| Field| Type| Null | Key |
Default | Extra |
+--+-+--+-+-+---+
| Seq_ID | varchar(50) | | PRI |
| |
| Homolog1_PID | varchar(20) | YES | MUL | NULL
| |
| Homolog1_Desc| varchar(50) | YES | MUL | NULL
| |
| Homolog1_Species | varchar(50) | YES | | NULL
| |
| Homolog2_PID | varchar(20) | YES | MUL | NULL
| |
| Homolog2_Desc| varchar(50) | YES | MUL | NULL
| |
| Homolog2_Species | varchar(50) | YES | | NULL
| |
| Homolog3_PID | varchar(20) | YES | MUL | NULL
| |
| Homolog3_Desc| varchar(50) | YES | MUL | NULL
| |
| Homolog3_Species | varchar(50) | YES | | NULL
| |
+--+-+--+-+-+---+
10 rows in set (0.00 sec)
There are 357944 records in this tables.
I've already copied
/usr/share/doc/mysql-server-3.23.49/my-huge.cnf as
/etc/my.cnf
Is there any other thing I can do to improve the speed
of join? I really hate to merge the two tables
together. I am running MySQL3.23.49 on redhat
linux7.3. My MySQL Server has 4 GB memory.
Eventually, I need to do (select *) instead of the
above select count(*)
mysql explain select * from newSequence s left join
newSequence_Homolog h on s.Seq_ID = h.Seq_ID;
+---++---+-+-+--+-+---+
| table | type | possible_keys | key | key_len |
ref | rows| Extra |
+---++---+-+-+--+-+---+
| s | ALL| NULL | NULL|NULL |
NULL | 2684094 | |
| h | eq_ref | PRIMARY | PRIMARY | 50 |
s.Seq_ID | 1 | |
+---++---+-+-+--+-+---+
2 rows in set (0.00 sec)
Thanks!
Qunfeng Dong
__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
InnoDB exits process when files are read-only
Hi all, I'm testing the fail-safety of InnoDB with MySQL, and I find that when the data files are read-only, the process exits early instead of returning an error code. In the file innobase\os\os0file.c, the function os_file_handle_error() does not recognize the error code 5, and so it terminates the process. We are using Embedded MySQL, so when the process terminates, our EXE goes down. It would be ideal if the function could return an error code so that we could continue. Is there anything we can do? Sincerely, Matt Solnit [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: Inventory Query
Try this: SELECT t1.id AS prod_id, t1.description AS Description,SUM(t2.quantities) AS Purchases, SUM(t3.quantities) AS Sellings, (Purchases-Sellings) AS Inventory FROM Products t1 INNER JOIN Purchases t2 ON t1.id=t2.prod_id INNER JOIN Sellings t3 ON t1.id=t3.prod_id GROUP BY t1.id,t1.description -Original Message- From: Gigi Di Leo [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 12:03 PM To: '[EMAIL PROTECTED]' Subject: Inventory Query Hello list. Could you please suggest me a one-line query to solve a problem of inventory ? Three table: Products - | id | description | - | 01 | bread| | 02 | milk | | 03 | coffee | - Purchases - | prod_id | quantities | - | 01| 10 | | 02| 5 | | 03| 3 | - Sellings - | prod_id | quantities | - | 01| 3 | | 01| 1 | | 02| 1 | | 02| 1 | - The query should return - | prod_id | description | purchases | sellings | inventory | - |01 | bread|10 | 4 | 6 | |02 | milk |5 | 2 | 3 | |03 | coffee |3 | 0 | 3 | - Thank you very much for your help. Gigi - 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: Fwd: Re: Bug in auto_increment
On Sun, 15 Dec 2002 17:38:49 -0600, Paul DuBois wrote: INSERT INTO gebaeude_daten VALUES (0, 'Bank Stufe 1', 1000, 30, 900, 1); What happens if you use a value of NULL instead of 0? Inserting a field with 0 tells the table to generate a value on its own, like it's supposed to. From your post you appear to be trying to assign a value to the auto_increment which defeats its purpose. I would suggest tracking the current ID for this table in another table, then inserting it yourself. Remember to lock the second table to prevent two sessions from using the same number. Every time I change the table definition with ALTER, the 0 becomes the next auto-index. The user comments on this page point out that setting a value for an auto-increment field with ALTER TABLE will reset the counter to the provided value. http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html - Steve Yates - Does Microsoft mean small and limp? ~ Taglines by Taglinator - www.srtware.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: Need Help phpMyAdmin !!
I Installed succefully MySQL, but i want to have a graphical interface to create a new databases and manage my existing database. i readed in a asp-php.net (french web site) http://www.asp-php.net/tutorial/asp-php/iis_et_mysql.php?page=1 the steps for installing phpmyadmin, you cas see the web site and if you have an idea for me thanks a lot -- |-Rachid Abdelkhalak |-Account Manager |-MTDS S.A. |-tel +212.3.767.4861 |-fax +212.3.767.4863 |-14, rue 16 novembre |-Rabat, Kingdom of Morocc On Mon, 16 Dec 2002, Adolfo Bello wrote: I guess your question is more about configuring IIS (or PWS) than about MySQL. Anyway, you have to create a web site in IIS pointing to your phpMyAdmin directory and define in it the default document (index.html. default.php or whatever) Adolfo -Original Message- From: Rachid Abdelkhalak [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 1:28 PM To: [EMAIL PROTECTED] Subject: Need Help phpMyAdmin !! Hello every one At the first, I'm Sorry for my bad english. I want to install phpMyAdmin for managing My MySql databases, I Downloaded ths zip file frome here: http://sourceforge.net/project/showfiles.php? group_id=23067 the file name is phpMyAdmin-2.3.3pl1-php3.zip I unzip this file on my Mysql Default folder: C:\inetpub\Mysql but I dont know how to make after for display the MySQL Admin HTML page: http://localhost/phpmyadmin/index.php Some one have a proposition for me. I am just a newbbie in MySQL and PHP development. Thanks. -- |-Rachid Abdelkhalak |-Account Manager |-MTDS S.A. |-tel +212.3.767.4861 |-fax +212.3.767.4863 |-14, rue 16 novembre |-Rabat, Kingdom of MoroccO - 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: Access to MySQL
Search the archive! Short answer: export to .txt and import in mysql or use ODBC B. At 13:20 16-12-2002 -0400, Yves Arsenault wrote: Hello, Is there a small program that would transfer the contents of an Access database to MySQL? TIA, Yves Arsenault - 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
R: Inventory Query
Thank you for your attention, Adolfo. Sorry, it doesn't work. It looks like quantities are added more times into the SUM function. gigi -Messaggio originale- Da: Adolfo Bello [mailto:[EMAIL PROTECTED]] Inviato: lunedì 16 dicembre 2002 19.04 A: Gigi Di Leo; [EMAIL PROTECTED] Oggetto: RE: Inventory Query Try this: SELECT t1.id AS prod_id, t1.description AS Description,SUM(t2.quantities) AS Purchases, SUM(t3.quantities) AS Sellings, (Purchases-Sellings) AS Inventory FROM Products t1 INNER JOIN Purchases t2 ON t1.id=t2.prod_id INNER JOIN Sellings t3 ON t1.id=t3.prod_id GROUP BY t1.id,t1.description -Original Message- From: Gigi Di Leo [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 12:03 PM To: '[EMAIL PROTECTED]' Subject: Inventory Query Hello list. Could you please suggest me a one-line query to solve a problem of inventory ? Three table: Products - | id | description | - | 01 | bread| | 02 | milk | | 03 | coffee | - Purchases - | prod_id | quantities | - | 01| 10 | | 02| 5 | | 03| 3 | - Sellings - | prod_id | quantities | - | 01| 3 | | 01| 1 | | 02| 1 | | 02| 1 | - The query should return - | prod_id | description | purchases | sellings | inventory | - |01 | bread|10 | 4 | 6 | |02 | milk |5 | 2 | 3 | |03 | coffee |3 | 0 | 3 | - Thank you very much for your help. Gigi - 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
synopsis of the problem (one line) no mit-pthreads directory
Description: mit-pthreads directory missing How-To-Repeat: When attempting to compile mysql-3.23.54 on NetBSD 1.5.2, I get the following error: Configuring MIT Pthreads cd: can't cd to mit-pthreads And in fact, the mit-pthreads directory is not there. However, I would like to use the --with-mit-threads option. I have downloaded the source from a couple of sources, but am still unable to find the directory. Any thoughts or help? Fix: Submitter-Id: submitter ID Originator:Felicia Neff Organization: MySQL support: [none | licence | email support | extended email support ] none Synopsis: no mit-pthreads directory Severity: serious Priority: medium Category: mysql Class: Release: mysql-3.23.46 (Source distribution) Environment: System: NetBSD juggler.panix.com 1.5.4_ALPHA NetBSD 1.5.4_ALPHA (PANIX-STD) #0: Wed Sep 18 15:16:50 EDT 2002 [EMAIL PROTECTED]:/devel/NO-BACKUPS/release-1.5-20020917/src/sys/arch/i386/compile/PANIX-STD i386 Some paths: /usr/local/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version egcs-2.91.66 19990314 (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: -rw-r--r-- 1 root wheel 1208916 Nov 19 19:52 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 15 Sep 17 14:00 /usr/lib/libc.so - libc.so.12.62.1 lrwxr-xr-x 1 root wheel 15 Sep 17 14:00 /usr/lib/libc.so.12 - libc.so.12.62.1 -rwxr-xr-x 1 root wheel 618436 Nov 19 19:53 /usr/lib/libc.so.12.62.1 Configure command: ./configure --prefix=/pkg/mysql-3.23.46 --localstatedir=/var/mysql --with-named-z-libs=z '--with-named-curses-libs=-lcurses -ltermcap' --without-readline --without-debug --without-bench --with-low-memory --with-mit-threads --with-libwrap --without-berkeley-db --enable-shared=NO --disable-shared Perl: This is perl, version 5.004_03 - 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
Duplicating records with a changed field?
Is it possible to duplicate a record in a table into the same table with a changed field? I have a single user who now has authority over an additional database. I wanted to duplicate the records in myslq.db for this user, changing the Db field to the name of the additional database. I searched the mailing list archives, and found a reference to this quote from the INSERT...SELECT manual page: The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query because it's forbidden in ANSI SQL to SELECT from the same table into which you are inserting. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using subselect clauses, the situation could easily be very confusing!) It seems to me that this absolutely forbids it. Is there a work around? another method? [I'm aware that I could have just typed the whole thing in an INSERT statement more quickly and with fewer keystrokes that typing this note, but I'm the curious type.] Thanks for your thoughts. -Kevin Zembower - E. Kevin Zembower Unix Administrator Johns Hopkins University/Center for Communications Programs 111 Market Place, Suite 310 Baltimore, MD 21202 410-659-6139 - 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: Need Help phpMyAdmin !!
I guess your question is more about configuring IIS (or PWS) than about MySQL. Anyway, you have to create a web site in IIS pointing to your phpMyAdmin directory and define in it the default document (index.html. default.php or whatever) Adolfo -Original Message- From: Rachid Abdelkhalak [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 1:28 PM To: [EMAIL PROTECTED] Subject: Need Help phpMyAdmin !! Hello every one At the first, I'm Sorry for my bad english. I want to install phpMyAdmin for managing My MySql databases, I Downloaded ths zip file frome here: http://sourceforge.net/project/showfiles.php? group_id=23067 the file name is phpMyAdmin-2.3.3pl1-php3.zip I unzip this file on my Mysql Default folder: C:\inetpub\Mysql but I dont know how to make after for display the MySQL Admin HTML page: http://localhost/phpmyadmin/index.php Some one have a proposition for me. I am just a newbbie in MySQL and PHP development. Thanks. -- |-Rachid Abdelkhalak |-Account Manager |-MTDS S.A. |-tel +212.3.767.4861 |-fax +212.3.767.4863 |-14, rue 16 novembre |-Rabat, Kingdom of MoroccO - 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
Server Connection Lost
Have been running mysql 3.23.43 on a RH 6.2 system and things have worked fine for the last year. However, we run a mysqldump daily and this failed yesterday. Looking into the problem I found that mysqld is having some problems. running: mysqldump --defaults-file=default file -A dump file results: mysqldump: Got error 2013: Lost connection to MySQL server during query when trying to connect on the server I see an error: Number of processes running now: 1 mysqld process hanging, pid pid - killed 021216 time mysqld restarted did a search for this type of error but, didn't see any that resemble it. Anyone have any ideas of how to solve this problem? - 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
ÍøÂçÓªÏú,¿ìÀ´×¬300Íò
ÄúºÃ! ÍøÂçÓªÏú,¿ìÀ´×¬300Íò. Ö©ÖëÍøÐÅÏ¢´úÀíÁªÃËÊǹúÄÚµÚÒ»¼ÒÒÔ¡°¶à²ã´ÎÐÅÏ¢ÍøÂçÓªÏú¡±Îª¾ÓªÄ£Ê½µÄºÏ·¨µÄÕý¹æ×¢²áÍøÕ¾£¬ËüµÄ×ܲ¿ÔÚÕã½£¬ÆäÕýÒÔÒ»ÖÖոС¢¼òµ¥¡¢ÓÐȤµÄ·½Ê½À´ÈÃÿһλ»áÔ±×ã²»³ö»§Ò²¿ÉÒÔ׬µ½¾Þ¶î²Æ¸»£¬³ÉΪÕæÕýµÄSOHO Ò»×å¡£ ¡¡ ÇëÏÈÈ¥×¢²áΪÃâ·Ñ»áÔ±°É Http://www.cnyilang.com ²é¿´ÒøÐжÔÕ˵¥µØÖ·: Http://www.cnyilang.com (¼ÓÈëÁ½ÖÜ׬1300Ôª) ³ÉΪ¸ß¼¶»áÔ±ºó: 1. °ïÄúÉè¼ÆÒ»¸öÃâ·ÑÍøÕ¾£¬Ò»¸ö×ÔÒѵÄÉÌÎñÍøÕ¾¡£¸úÎÒµÄHttp://www.cnyilang.comÒ»Ñù,¿ÉÒÔ¶ÔËü½øÐб༣¬Õ³Ìù¹ã¸æ... 2.ÏíÓÐ1¶àÌõ¸÷ÀàʵÓÃÐÅÏ¢¡¢Ãâ·ÑÏÂÔØÈí¼þºÍµçÓ°µÈµÈµÄÐÅÏ¢·þÎñ. 3.°ïһЩÍøÕ¾×ö¹ã¸æ£¬Í¨¹ý±ðÈ˵ĵã»÷»ò´úÀíÉÌÆ·µÄÏúÊÛ¶îÀ´Ìá³É. 4.ͨ¹ýÍøÓÑÔÚÄúµÄÍøÕ¾ÖмÓÈ룬³ÉΪÕýʽ»áÔ±À´×¬È¡»áÔ±µÄÈë»á·ÑÌá³É.·¢Õ¹20¸ö»áÔ±,Äã¿ÉµÃµ½330ÍòÔª. ½øÈ¥ÇÆÇÆ°É Http://www.cnyilang.com ÁíÍ⣬»¹¿ÉÒÔÉêÇë6MÃâ·ÑÓÊÏ䣬»ú»áÄѵã¡£¡ ¾ÔË×ö, 1.±¾¹¤×÷¼ûЧ¿ì, ³ÉΪÕýʽ»áÔ±ºó,Ö»Òª°´ÎÒÃǵÄÐû´«·½·¨¹¤×÷,3Ìì¾Í¿É¼û³É¹û²úÉúÏÂÏß. 2.Ч¹ûÖ±½Ó, ´ÓÄãµÄÒøÐп¨ÉÏÖ±½Ó¿´µ½½ð¶îÿÌì´ó·ù¶ÈÔö³¤, 3.ºó¾¢×ã, Äã4²ãÒÔÄÚµÄËùÓÐϼ¶»áÔ±¶¼ÔÚŬÁ¦¹¤×÷À©´óÓû§Èº,ËûÃÇÿ·¢Õ¹Ò»¸ö,¾Í»ã¸øÄãµÄ20ÔªÅàѵ·Ñ.ÓÐʱÄãʲô¶¼²»¸É,ÄãµÄÒøÐп¨»¹ÒòËûÃǶøÔ´Ô´²»¶ÏµÄ³äÇ®,³öºõ³£ÈËÔ¤ÁÏ. 4.ÊʺÏÈËȺ¹ã·º, ÒµÓàʱ¼ä¸ã´´ÊÕ(ÓÈÆäÊǼÒÍ¥Ö÷¸¾,ѧÉúÇÚ¹¤¼óѧ),²»Ó°ÏìѧϰºÍ¹¤×÷,ÒµÓàʱ¼äÊÇÈËȺÉÏÍø×î¶àʱ¼ä,·¢Ìù´«²¥·¶Î§¹ã.ѧÉúÔÚÍø°ÉֻҪÿÌìÍíÉÏ·¢Ò»Ð¡Ê±Ìù,È»ºóµÈ´ýÈËÃÇ×Ô¶¯¼ÓÈëϼ¶»áÔ±¸øÄã»ãÇ®¼´¿É.ÕâÖÖÇÚ¹¤¼óѧ¿ÉÊÇÀ´µÄ¿ì,Ö»ÐèÒªÄãÄÃÉí·ÝÖ¤°ìÒ»¸ö½¨ÐÐÁú¿¨,Ͷ×Ê80Ôª,¾ÍÕâô¼òµ¥. Ïêϸ½éÉÜ,¼ÓÈëµØÖ·: Http://www.cnyilang.com Ö Àñ! ÓʼþÄÚÈÝÓëÒÔÏÂÎÄ×ÖÎÞ¹Ø= ÓÅÁªÍøÂç http://www.chinamysql.com רҵÌṩ¸÷ÀàÐéÄâÖ÷»ú£¬²»ÂúÒâ¿É»ñÍË¿î¡£ Ç¿ÊÆÌײͣº100MÖ÷»úËͶ¥¼¶ÓòÃû£¬ËÍ10¸ö10MÆóÒµÓÍÏ䣬¼ÓËÍ20¸ö¶þ¼¶ÓòÃû£¬½öÐè318Ôª/Ä꣡ ÈýÁú֤ȯͶ×Ê http://3long.sayba.com ΪÄúÌṩרҵÀí²Æ·þÎñ¡£ÏÖÔÚ¹ºÂòÖ»Ðè588Ôª£¬ÔùËͼÛÖµ³¬¹ý1800ÔªµÄÀñÆ·£¡ ˵°ÉÍøÉÏÉÌ³Ç http://shop.sayba.com È«Êdz§ÉÌÖ±ÏúµÄÐÂÆ·»òÕÛÉÏÕÛµÄÉÌÆ·£¬ÊÇÄúÍøÂ繺ÎïµÄºÃÈ¥´¦£¡ ʹÓü«ÐÇÓʼþȺ·¢£¬ÎÞÐëͨ¹ýÓʼþ·þÎñÆ÷£¬Ö±´ï¶Ô·½ÓÊÏ䣬ËٶȾø¶ÔÒ»Á÷£¡ Èí¼þÏÂÔØÍøÖ·£ºhttp://www.lovexin.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
MySQL 3.23.54a redhat package is not working
Hi When i try to install MySQL 3.23.54a linux, rpm it says that MD5 checksum is wrong I have tried to download the package from another mirror with the same error. So is it a bug or do i have something wrong in by system? Med vänliga hälsningar Jonas Eriksson Service och Support, Webkonsulterna Telefon: 063-180 888 Fax: 063-180 889 Webbplats: http://www.webkonsulterna.se - 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
Incorrect permissions to docs. directory on Linux
Description: On Linux, the rpm package dumps the user docs. (manuals) under /usr/share/docs/packages/MySQL/. The permissions for the MySQL directory are set to 766 and so the contents of the directory (ie the manuals) are not readable. The permissions need to be 755. How-To-Repeat: Get RPM package from website and install. Try opening the manual_toc.html from web browser. Fix: Persmissions for /usr/share/docs/packages/MySQL/ should be set 755. Submitter-Id: submitter ID Originator:Mayank Vasa Organization: MySQL support: none Synopsis: Incorrect permissions to docs. directory on Linux Severity: non-critical Priority: low Category: mysql Class: doc-bug Release: mysql-3.23.54 (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.54, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.54 Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 21 min 50 sec Threads: 1 Questions: 6 Slow queries: 0 Opens: 7 Flush tables: 1 Open tables: 1 Queries per second avg: 0.005 Environment: System: Linux miku.telocity.com 2.4.18-18.8.0 #1 Thu Nov 14 00:10:29 EST 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Oct 26 12:33 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 5 16:12 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 15:59 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 15:50 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--without-innodb' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' 'CXX=gcc' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
server-bin files
Hello, I found SERVER-bin.* files from today in my MySQL directory, where are databases stored. What are theese files and why was created. SERVER-bin.001 SERVER-bin.002 SERVER-bin.index Owner mysql, group mysql. Thanks Radek -- S pozdravem, Bc. Radek Kreja Starnet, s. r. o. [EMAIL PROTECTED] +420 608 470980 http://www.ceskedomeny.cz http://www.skdomeny.com http://www.starnet.cz - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Access to MySQL
Yves, I am always open for cleaner method, but what I did was in MySQL created a table 'member' for data. In Access exported as .txt make sure tab sequential. Back in MySql I made database active that had 'member' And did a: mysql load data infile c:\\mysql\\tmp.txt into member; it actually went in quite cleanly. I am sure there are probably better ways, I am always willing to learn. But this is what worked for me today. Regards, Chuck -Original Message- From: Yves Arsenault [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 12:21 PM To: 1Mysql Subject: Access to MySQL Hello, Is there a small program that would transfer the contents of an Access database to MySQL? TIA, Yves Arsenault Carrefour Infotech 5,promenade Acadian Charlottetown, IPE C1C 1M2 [EMAIL PROTECTED] (902)368-1895 ext.242 ICQ #117650823 - 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
Large Like Queries
Hi, I was just wondering if anyone has any tricks for speeding up huge queries that require a LIKE comparison, and also a wild card at the front and back of the search term. For example: SELECT my_id FROM my_table WHERE my_value LIKE '%ABCDEFG%' LIMIT 50; I know that in a query such as this, mysql does not utilize indexing, so I was just curious how some others have attempted to speed up this kind of thing up Thanks, Chris - 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 / Calculate question
Hi, I have one table with an id1 column and a number column, these numbers correspond to another table with an id2 column and a name column and a total column. I also have a reference table with ties the two together (an id1 and id2 column). I want to be able to select all numbers in the first table that correspond to the name in the second column and add them together and place them in the total column of the second table, but whatever I have done has failed. Can anyone suggest a way of doing this? TIA Table1Table2Reference id1 number id2nametotalid1id2 1101John 6011 1202Mary 9022 130etc. etc. 240 250 etc. (MySQL, 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: more about using sets
I agree entirely. SETS and ENUMS should be avoided by any normal user (frankly, I think they should be deprecated). They are not portable and it's just horrific to be changing data with an ALTER statement. Foreign Key relationships (even if they aren't real as in standard MySQL) are the way to go. Get InnoDB and use foreign keys before it's too late and you're stuck with a hodge podge system. -Original Message- From: Harald Fuchs [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 11:39 AM To: [EMAIL PROTECTED] Subject: Re: more about using sets In article [EMAIL PROTECTED], David T-G [EMAIL PROTECTED] writes: Hi, all -- I'm still trying to get a good handle on how sets can be useful to me. I have three scenarios so far. 1) A set of states (US Mail type, not turing type :-) I can pick from the list of states when entering address data, and storing the set entry should take less space than storing even a 2-char string. 2) A set of ccard types (MC, Visa, AmEx) It's easy to have a pick list to avoid misspellings and such 3) A set of pay scale levels (master, journeyman, apprentice, intern) Each staff member needs to be at a certain scale so that the software knows how much to pay him or her per session. We don't want to make up pay levels that aren't in our list. For each of these, is a set the way to go, and is it saving me anything? In the third case, I want to restrict the level in the personnel table to one of the defined levels. Do I just use a set in the field definition and then list from there (and then it gets messy if we add a new level) or do I create a jobscalelevels table and define the levels in there and then set the personnel.level field to match and forget about the idea of a set? I avoid SETs whenever possible - i.e. always unless storage/performance is extremely important. They give you nothing which you can't do with a separate value table and foreign keys, but they compromize portability. - 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 !!!
Hello All, Great list ! MySQL = Incredible Product ! I've been trying to come up with a SQL query to perform the following... Table - Contact ClientID Activity1 Activity2 Activity3 Activity4 Time1 Time2 Time3 Time4 Date I need to get a total of all the activities and their associated time between two dates and group them by ClientID I came up with SELECT ActivityCode1, SUM(Time1) FROM Contact WHERE (Date BETWEEN '2002-01-01' AND '2002-12-15') GROUP BY ActivityCode1, ORDER BY ClientID If I execute the above for each of the 4 ActivityCodes Time and add them manually I get the correct results, but I'm wondering if it could be done with one SQL statement. any suggestions are greatly appreciated !! Marko Palikko - 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 Question
I have 2 tables in our MySQL database like this: TABLE: customers +--+---+--+-+-+- ---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+- ---+ | custid | mediumint(8) unsigned | | PRI | NULL| auto_increment | | name | tinytext | YES | | NULL| | | email| tinytext | YES | | NULL| | | company | tinytext | YES | | NULL| | +--+---+--+-+-+- ---+ TABLE: shipment +-+---+--+-+-+-- --+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- --+ | shipmentid | mediumint(10) | | PRI | NULL| auto_increment | | origin | mediumint(10) | YES | | NULL| | | destination | mediumint(10) | YES | | NULL| | | quoteid | mediumint(10) | YES | | NULL| | | customerid | mediumint(10) | YES | | NULL| | | carrierid | mediumint(10) | YES | | NULL| | +-+---+--+-+-+-- --+ I need to construct a query to find out what customers in the database have not booked shipments with us. That means there would be no records in the shipment table for a given customer id. I can easily get a count of how many shipments are there for customers that have booked (via count() and group), but this one is eluding me.. Any help or suggestions on where to get more info would be appreciated. Thanks, Darren Young The Freight Depot - 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 speed up the Left Join on big tables?
Qunfeng,
A simple left join on two big table took 5 mins to
finish.
These lines tell about the cause of the problem:
| table | type | possible_keys | key | key_len |
| s | index | NULL | PRIMARY | 50 |
MySQL has no key (index) which it can use to speed up the search on the
first table, newSequence (alias s). So, it has to scan all of the rows:
ref | rows| Extra |
NULL | 2684094 | Using index |
MySQL will still use the primary key, _trying_ to be faster than without.
Does the Seq_ID have to be VARCHAR? This column type isn't very easy to
index, especially without a length specification.
As you cannot have CHAR ( 3) and VARCHAR in one table, I would suggest you
split up table newSequence into two tables (one fixed-length (i.e. without
VARCHAR/TEXT columns), the other variable-length). This will speed up
count() queries (and others) amazingly.
If you can use something like INT instead of CHAR, it's even faster.
If, for any reason, you have to stick to VARCHAR, you should index the
column separately. Leave the primary key as is, but add another key (index)
like that:
CREATE INDEX make_it_fast ON newSequence (Seq_ID(10));
This will only make sense if the first 10 characters can tell the difference
between different records. If not, you can experiment setting the index size
to 20, 30, ...
I hope this will give you some ideas on how you can improve performance.
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
CEO / Geschäftsleitung iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3
- Original Message -
From: Qunfeng Dong [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, December 16, 2002 6:42 PM
Subject: How can I speed up the Left Join on big tables?
Hi,
A simple left join on two big table took 5 mins to
finish.
Here is the explain
mysql explain select count(*) from newSequence s left
join newSequence_Homolog h on s.Seq_ID = h.Seq_ID;
+---++---+-+-+--+-+-
+
| table | type | possible_keys | key | key_len |
ref | rows| Extra |
+---++---+-+-+--+-+-
+
| s | index | NULL | PRIMARY | 50 |
NULL | 2684094 | Using index |
| h | eq_ref | PRIMARY | PRIMARY | 50 |
s.Seq_ID | 1 | Using index |
+---++---+-+-+--+-+-
+
2 rows in set (0.00 sec)
here are the two tables' definitaion
mysql describe newSequence;
+-+-
---+--+-++--
-+
| Field | Type
| Null | Key | Default| Extra |
+-+-
---+--+-++--
-+
| Seq_ID | varchar(50)
| | PRI || |
| GenBank_Acc | varchar(10)
| YES | MUL | NULL | |
| Organism| varchar(50)
| | MUL || |
| Seq_Type| enum('EST','GSS','EST Contig','EST
Singlet','GSS Contig','GSS Singlet','GSS Plasmid
Contig','Protein') | | MUL | EST| |
| Seq_Length | int(11)
| | | 0 | |
| Seq_Title | text
| | MUL || |
| Comment | text
| YES | MUL | NULL | |
| Entry_Date | date
| | | -00-00 | |
+-+-
---+--+-++--
-+
8 rows in set (0.00 sec)
There are 2684094 records on this table.
mysql describe newSequence_Homolog;
+--+-+--+-+-+---+
| Field| Type| Null | Key |
Default | Extra |
+--+-+--+-+-+---+
| Seq_ID | varchar(50) | | PRI |
| |
| Homolog1_PID | varchar(20) | YES | MUL | NULL
| |
| Homolog1_Desc| varchar(50) | YES | MUL | NULL
| |
| Homolog1_Species | varchar(50) | YES | | NULL
| |
| Homolog2_PID | varchar(20) | YES | MUL | NULL
| |
| Homolog2_Desc| varchar(50) | YES | MUL | NULL
| |
| Homolog2_Species | varchar(50) | YES | | NULL
| |
| Homolog3_PID | varchar(20) | YES | MUL | NULL
| |
| Homolog3_Desc| varchar(50) | YES | MUL | NULL
| |
| Homolog3_Species | varchar(50) | YES | | NULL
| |
+--+-+--+-+-+---+
10 rows in set (0.00 sec)
There are 357944 records in this tables.
I've already copied
Re: SQL Question
Darren, I need to construct a query to find out what customers in the database have not booked shipments with us. That means there would be no records in the shipment table for a given customer id. If I get this right, it should be: SELECT custid, name FROM customers LEFT JOIN shipment ON custid = customerid WHERE shipmentid IS NOT NULL or: SELECT custid, name FROM customers INNER JOIN shipment ON custid = customerid Regards, -- Stefan Hinz [EMAIL PROTECTED] CEO / Geschäftsleitung iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Darren Young [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 16, 2002 11:24 PM Subject: RE: SQL Question I have 2 tables in our MySQL database like this: TABLE: customers +--+---+--+-+-+- ---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+- ---+ | custid | mediumint(8) unsigned | | PRI | NULL| auto_increment | | name | tinytext | YES | | NULL| | | email| tinytext | YES | | NULL| | | company | tinytext | YES | | NULL| | +--+---+--+-+-+- ---+ TABLE: shipment +-+---+--+-+-+-- --+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- --+ | shipmentid | mediumint(10) | | PRI | NULL| auto_increment | | origin | mediumint(10) | YES | | NULL| | | destination | mediumint(10) | YES | | NULL| | | quoteid | mediumint(10) | YES | | NULL| | | customerid | mediumint(10) | YES | | NULL| | | carrierid | mediumint(10) | YES | | NULL| | +-+---+--+-+-+-- --+ I need to construct a query to find out what customers in the database have not booked shipments with us. That means there would be no records in the shipment table for a given customer id. I can easily get a count of how many shipments are there for customers that have booked (via count() and group), but this one is eluding me.. Any help or suggestions on where to get more info would be appreciated. Thanks, Darren Young The Freight Depot - 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: Large Like Queries
Chris, SELECT my_id FROM my_table WHERE my_value LIKE '%ABCDEFG%' LIMIT 50; I know that in a query such as this, mysql does not utilize indexing, so I was just curious how some others have attempted to speed up this kind of thing up Maybe this answer won't help too much, but I experienced that MySQL _will_ use indexes, even if a LIKE comparison has a match pattern like %...% (actually, the discussion is about the leading %...). In a 1.2 million records table, I found that a search like that took about 15 seconds without an index, and 2.4 seconds after we created an index on the column in question. I called this effect pre-sorted comparison, but maybe someone else will have a better explanation. Regards, -- Stefan Hinz [EMAIL PROTECTED] CEO / Geschäftsleitung iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Chris Stark [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 16, 2002 10:25 PM Subject: Large Like Queries Hi, I was just wondering if anyone has any tricks for speeding up huge queries that require a LIKE comparison, and also a wild card at the front and back of the search term. For example: SELECT my_id FROM my_table WHERE my_value LIKE '%ABCDEFG%' LIMIT 50; I know that in a query such as this, mysql does not utilize indexing, so I was just curious how some others have attempted to speed up this kind of thing up Thanks, Chris - 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: How can I speed up the Left Join on big tables?
Dear Stefan,
Thanks for your help. I didn't know MySQL doesn't
automatically create index on primary key (I probably
should create UNIQUE index on them now).
About not mixing char and varchar in one table, I
don't find that info in the on-line documents. I could
successfully create a test table
create table testTable(
Seq_ID char(20),
Title varchar(100)
);
Qunfeng
--- Stefan Hinz, iConnect (Berlin)
[EMAIL PROTECTED] wrote:
Qunfeng,
A simple left join on two big table took 5 mins to
finish.
These lines tell about the cause of the problem:
| table | type | possible_keys | key |
key_len |
| s | index | NULL | PRIMARY |
50 |
MySQL has no key (index) which it can use to speed
up the search on the
first table, newSequence (alias s). So, it has to
scan all of the rows:
ref | rows| Extra |
NULL | 2684094 | Using index |
MySQL will still use the primary key, _trying_ to be
faster than without.
Does the Seq_ID have to be VARCHAR? This column type
isn't very easy to
index, especially without a length specification.
As you cannot have CHAR ( 3) and VARCHAR in one
table, I would suggest you
split up table newSequence into two tables (one
fixed-length (i.e. without
VARCHAR/TEXT columns), the other variable-length).
This will speed up
count() queries (and others) amazingly.
If you can use something like INT instead of CHAR,
it's even faster.
If, for any reason, you have to stick to VARCHAR,
you should index the
column separately. Leave the primary key as is, but
add another key (index)
like that:
CREATE INDEX make_it_fast ON newSequence
(Seq_ID(10));
This will only make sense if the first 10 characters
can tell the difference
between different records. If not, you can
experiment setting the index size
to 20, 30, ...
I hope this will give you some ideas on how you can
improve performance.
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
CEO / Geschäftsleitung iConnect GmbH
http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3
- Original Message -
From: Qunfeng Dong [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, December 16, 2002 6:42 PM
Subject: How can I speed up the Left Join on big
tables?
Hi,
A simple left join on two big table took 5 mins to
finish.
Here is the explain
mysql explain select count(*) from newSequence s
left
join newSequence_Homolog h on s.Seq_ID = h.Seq_ID;
+---++---+-+-+--+-+-
+
| table | type | possible_keys | key |
key_len |
ref | rows| Extra |
+---++---+-+-+--+-+-
+
| s | index | NULL | PRIMARY |
50 |
NULL | 2684094 | Using index |
| h | eq_ref | PRIMARY | PRIMARY |
50 |
s.Seq_ID | 1 | Using index |
+---++---+-+-+--+-+-
+
2 rows in set (0.00 sec)
here are the two tables' definitaion
mysql describe newSequence;
+-+-
---+--+-++--
-+
| Field | Type
| Null | Key | Default| Extra |
+-+-
---+--+-++--
-+
| Seq_ID | varchar(50)
| | PRI || |
| GenBank_Acc | varchar(10)
| YES | MUL | NULL | |
| Organism| varchar(50)
| | MUL || |
| Seq_Type| enum('EST','GSS','EST Contig','EST
Singlet','GSS Contig','GSS Singlet','GSS Plasmid
Contig','Protein') | | MUL | EST|
|
| Seq_Length | int(11)
| | | 0 | |
| Seq_Title | text
| | MUL || |
| Comment | text
| YES | MUL | NULL | |
| Entry_Date | date
| | | -00-00 | |
+-+-
---+--+-++--
-+
8 rows in set (0.00 sec)
There are 2684094 records on this table.
mysql describe newSequence_Homolog;
+--+-+--+-+-+---+
| Field| Type| Null | Key |
Default | Extra |
+--+-+--+-+-+---+
| Seq_ID | varchar(50) | | PRI |
| |
| Homolog1_PID | varchar(20) | YES | MUL |
NULL
| |
| Homolog1_Desc| varchar(50) | YES | MUL |
NULL
| |
|
Re: SQL Question
On 16 Dec 2002, at 23:50, Stefan Hinz, iConnect (Berlin wrote: I need to construct a query to find out what customers in the database have not booked shipments with us. That means there would be no records in the shipment table for a given customer id. If I get this right, it should be: SELECT custid, name FROM customers LEFT JOIN shipment ON custid = customerid WHERE shipmentid IS NOT NULL Darren wants the customers who do *not* have shipments, so it should be ... WHERE shipmentid IS NULL. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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 xfr Windows MySQL db to Linux ?
Will MySQL/Windows db files work on a Linux box? I'm guessing they will, but then someone questioned me. I'm creating a MySQL/PHP db prototype on a Windows box running Apache... but it will end up on a Linux/Apache server some day. Thanks for any help. tmb __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
innodb/mysql slow returning anything other than primary key
I've run into an interesting problem. I have a large innodb table (2274962 rows, 46 columns, 2 datafiles - 4.5GB total). When I run a query that uses a clustered_index in the where clause and the data I'm selecting is not the primary key of the table, it takes up to 2 1/2 minutes to return zero results (the result is correct). If I select the primary key, it takes 2 1/2 seconds to return zero results. For example: create table xray ( trans_id int not null, customer_id char(25) not null, customer_last_name char(25), UNIQUE INDEX trans_idx (trans_id), INDEX cus_id (customer_id ) ); type=INNODB; SELECT customer_last_name FROM XRAY WHERE customer_id = '12345'; This takes ~ 2 1/2 minutes to return 0 results. SELECT trans_id FROM XRAY WHERE customer_id = '12345'; This takes ~ 2 1/2 SECONDS. From what I understand, clustered indexes contain the primary key as well as the indexed data and return that key as a pointer to the row where the data being selected is located. This explains why selecting the key is so quick, BUT if no values are found in the index that match the where clause, why is it taking so long? I've run explain and it's telling me the database is using the correct index. I've even added USE INDEX to sql query with no improvement. The machine is an AMD Athlon XP 2000 with 1GB of ram. Mysql version is 3.23.53 OS = Linux 2.4.18-SGI_XFS_1.1enterprise Thanks for any ideas or suggestions! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to xfr Windows MySQL db to Linux ?
Technically, the files can work. But the best way (IMHO) is to use mysqldump out of the Win32 box, copy that file to the Linux box and pipe it in. Make sure you use options in mysqldump to do complete table creation, etc. Gerald Jensen - Original Message - From: tmb [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 16, 2002 5:30 PM Subject: How to xfr Windows MySQL db to Linux ? Will MySQL/Windows db files work on a Linux box? I'm guessing they will, but then someone questioned me. I'm creating a MySQL/PHP db prototype on a Windows box running Apache... but it will end up on a Linux/Apache server some day. Thanks for any help. tmb __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - 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 to xfr Windows MySQL db to Linux ?
I have done that for months on Windows2000/XP. A MySQL database accessed by a PHP application which runs under Apache. Without any problem, I have now that working environment under Mandrake 9. I regenerated the db under Linux with a dump from the Windows db. Adolfo On Mon, 2002-12-16 at 19:30, tmb wrote: Will MySQL/Windows db files work on a Linux box? I'm guessing they will, but then someone questioned me. I'm creating a MySQL/PHP db prototype on a Windows box running Apache... but it will end up on a Linux/Apache server some day. Thanks for any help. tmb __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Adolfo Bello [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: more about using sets
Adam, I agree entirely. SETS and ENUMS should be avoided by any normal user (frankly, I think they should be deprecated). They are not portable and it's just horrific to be changing data with an ALTER statement. To add some more to this discussion, a customer of ours is having problems with something as simple as male/female coding in databases. This attribute is coded as m/f, 1/0, true/false, etc. What's making it a real big problem is, that it's coded in table _structures_ instead of in relational lookup tables. The smart guy who put ENUM (and SET, if that's ANSI-SQL) into the SQL standard should be punished with a life sentence, cleaning up our customer's mess ;-) Regards, -- Stefan Hinz [EMAIL PROTECTED] CEO / Geschäftsleitung iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Adam Nelson [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, December 16, 2002 10:47 PM Subject: RE: more about using sets I agree entirely. SETS and ENUMS should be avoided by any normal user (frankly, I think they should be deprecated). They are not portable and it's just horrific to be changing data with an ALTER statement. Foreign Key relationships (even if they aren't real as in standard MySQL) are the way to go. Get InnoDB and use foreign keys before it's too late and you're stuck with a hodge podge system. -Original Message- From: Harald Fuchs [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 11:39 AM To: [EMAIL PROTECTED] Subject: Re: more about using sets In article [EMAIL PROTECTED], David T-G [EMAIL PROTECTED] writes: Hi, all -- I'm still trying to get a good handle on how sets can be useful to me. I have three scenarios so far. 1) A set of states (US Mail type, not turing type :-) I can pick from the list of states when entering address data, and storing the set entry should take less space than storing even a 2-char string. 2) A set of ccard types (MC, Visa, AmEx) It's easy to have a pick list to avoid misspellings and such 3) A set of pay scale levels (master, journeyman, apprentice, intern) Each staff member needs to be at a certain scale so that the software knows how much to pay him or her per session. We don't want to make up pay levels that aren't in our list. For each of these, is a set the way to go, and is it saving me anything? In the third case, I want to restrict the level in the personnel table to one of the defined levels. Do I just use a set in the field definition and then list from there (and then it gets messy if we add a new level) or do I create a jobscalelevels table and define the levels in there and then set the personnel.level field to match and forget about the idea of a set? I avoid SETs whenever possible - i.e. always unless storage/performance is extremely important. They give you nothing which you can't do with a separate value table and foreign keys, but they compromize portability. - 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
No descending index ?
Hello mysql, I am currently building news portal with php and mysql, and quite surprised knowing mysql doesn't support descending index. Why ? I think this feature is really needed. For example I want to show the latest news (from last updates, not always everyday). With ascending index, mysql doesn't use it. My current trick is crawling back (ie 1 month) and forward (if any in that month), but this is really annoying. Any plan / trick for this ? TIA. Tertanda, Oguds [36856104] - 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: No descending index ?
Hi, Can you just SELECT x, y, z FROM table WHERE condition ORDER BY timestamp_column DESC; ? -Dan On Tue, 17 Dec 2002, Mpu Gondrong wrote: Hello mysql, I am currently building news portal with php and mysql, and quite surprised knowing mysql doesn't support descending index. Why ? I think this feature is really needed. For example I want to show the latest news (from last updates, not always everyday). With ascending index, mysql doesn't use it. My current trick is crawling back (ie 1 month) and forward (if any in that month), but this is really annoying. Any plan / trick for this ? TIA. Tertanda, Oguds [36856104] - 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, Dan Goodes PlanetMirror Admin http://planetmirror.com/ [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: No descending index ?
Mpu Gondrong wrote: Hello mysql, I am currently building news portal with php and mysql, and quite surprised knowing mysql doesn't support descending index. Why ? I think this feature is really needed. For example I want to show the latest news (from last updates, not always everyday). With ascending index, mysql doesn't use it. My current trick is crawling back (ie 1 month) and forward (if any in that month), but this is really annoying. Any plan / trick for this ? TIA. Tertanda, Oguds [36856104] ??? What's wrong with using: order by MyIndex DESC -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: No descending index ?
On Tue, Dec 17, 2002 at 07:37:17AM +0700, Mpu Gondrong wrote: Hello mysql, I am currently building news portal with php and mysql, and quite surprised knowing mysql doesn't support descending index. Why ? I think this feature is really needed. For example I want to show the latest news (from last updates, not always everyday). With ascending index, mysql doesn't use it. My current trick is crawling back (ie 1 month) and forward (if any in that month), but this is really annoying. Any plan / trick for this ? TIA. I'm not quite sure what you're after, but MySQL 4.0 has support for reading indexes in reverse order in some circumstances. This greatly speeds up some queries that were slow in 3.23. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 1 days, processed 66,924,046 queries (426/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
mailing list problem?
I just got a message from the ezmlm program telling me that the mysql digests have been bouncing and it is going to remove me from the list. In fact, the digests have been coming through just fine. Any ideas on what it could be referring to? Did anyone else get such a message? Bill -- Bill Rausch, Software Development, UNIX, Mac, Windows Numerical Applications, Richland, WA 509-943-0861 x302 - 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: No descending index ?
Mpu Gondrong wrote: Selasa, 17/12/2002 7:38:52, Daniel menulis: DK What's wrong with using: order by MyIndex DESC If I have 100 rows and mysql calculates with desc got 90 or more (30%) rows, index won't be used. I have thousand rows, and just want to get the last row. Without index, this is horrible. Or Am I wrong ? Tertanda, Oguds [36856104] You can use max() to get the biggest Primary Key value, and then select the row with that primary key. Or you can use: select * from MyTable order by MyIndex DESC limit 1 -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
innodb transaction not started
hi! i'm running mysql-max version 3.23.52-max on a windows xp machine with the apache web server. i'm trying to get innodb transactions to work using a php application. here is the transaction i am trying to run: set autocommit=0; begin work; select * from for update; update ...; commit; to check if the above code is working, i open 2 clients, one client will try accessing the locked record after the select statement has been executed by the other. if i execute the test in the database console itself, record locking works. but when i execute the transaction via php commands, no record locking is evident, but the select and update statement gets executed and no error messages are generated from the database. i need to get the record locked. any help on this matter will be greatly appreciated! thanks! liza ortega - 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: mailing list problem?
Hi Bill, I did too. Did you get the following error? CNAME lookup failed temporarily. (#4.4.3) I'm not going to try again; this message has been in the queue too long. Fraser. mysql query _ Fraser Stuart Logistics IT 77-85Phone: +61 2 9335 1235 Roberts Rd Mobile: +61 419 233 732 Greenacre NSW [EMAIL PROTECTED] Australia 2190 www.toll.com.au _ | -Original Message- | From: Bill Rausch [mailto:[EMAIL PROTECTED]] | Sent: Tuesday, 17 December 2002 11:52 AM | To: [EMAIL PROTECTED] | Subject: mailing list problem? | | | I just got a message from the ezmlm program telling me that the | mysql digests have been bouncing and it is going to remove me | from the list. | | In fact, the digests have been coming through just fine. Any ideas | on what it could be referring to? Did anyone else get such a message? | | Bill | | -- | Bill Rausch, Software Development, UNIX, Mac, Windows | Numerical Applications, Richland, WA 509-943-0861 x302 | | - | 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[2]: No descending index ?
Selasa, 17/12/2002 7:47:38, Jeremy menulis: JZ I'm not quite sure what you're after, but MySQL 4.0 has support JZ for reading indexes in reverse order in some circumstances. This JZ greatly speeds up some queries that were slow in 3.23. I'm using MySQL 4.0.4-beta. For example: EXPLAIN SELECT * FROM news where date = '2002-12-31' order by date desc table type possible_keys key key_len ref rows Extra news ALL date NULL NULLNULL 14138 where used Or is that normal ? Any sample 'circumstances' ? TIA. Tertanda, Oguds [36856104] - 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: mailing list problem?
I also got that msg and I did seem to receive all the emails from the list. Qunfeng --- Bill Rausch [EMAIL PROTECTED] wrote: I just got a message from the ezmlm program telling me that the mysql digests have been bouncing and it is going to remove me from the list. In fact, the digests have been coming through just fine. Any ideas on what it could be referring to? Did anyone else get such a message? Bill -- Bill Rausch, Software Development, UNIX, Mac, Windows Numerical Applications, Richland, WA 509-943-0861 x302 - 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 __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ALTER a auto_increment column
Guru's Problem is type SMALLINT needs to be MEDIUMINT (MyISAM). column: id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT My solution is to use the following ALTER statement: ALTER TABLE messages CHANGE id id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT My question is: what happens to the sequence of numbers in this primary key? The manual states that if you don't change the AUTO_INCREMENT column, the sequence number will not be affected. It also says that if you drop a AUTO_INCREMENT column and then add another AUTO_INCREMENT column, MySQL will resequence the new column. Well I'm kinda in-between these two extremes. I want to increase the width but keep the sequence. As usual, this is a primary key which will affect other tables (foreign keys). The total rows affected is 14 million. BTW I expected the rows would be around 50. It's now near 60k. So much for my foresight. Thanks a ton David Filter: MySQL, SELECT, QUERY --- David E Lopez email: [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: mailing list problem?
On Mon, Dec 16, 2002 at 04:51:38PM -0800, Bill Rausch wrote: I just got a message from the ezmlm program telling me that the mysql digests have been bouncing and it is going to remove me from the list. In fact, the digests have been coming through just fine. Any ideas on what it could be referring to? Did anyone else get such a message? Yes, I got a similar message today. Checking with my ISP, they indicate no problems with their DNS server. The exmlm message indicated that the problem was being unable to find my ISP host: [EMAIL PROTECTED]: CNAME lookup failed temporarily. (#4.4.3) andy - 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: What is wrong with this Alter statement?
At 2:43 -0500 12/17/02, C. Reeve wrote: From the MySQL Manual: ALTER TABLE tbl_name MODIFY [COLUMN] create_definition [FIRST | AFTER column_name] I have also tried CHANGE instead of MODIFY. My Statement: alter table manager change column w1 decimal(8,2) not null after moves; The problem is the AFTER statement, if I omit it I have no problem. Now I'm probably reading this wrong but in any event I need my columns to be in a certain order and their not. Without deleting them and re-adding them (and the data) - how do I get them in the order I need? Why do you need them in a certain order? So that SELECT * will return them in a given order? It's better not to rely on that behavior. i.e w1, w2, w3, w4 .. w25 not w13, w21, w2, w9 w4, w1. (I have no idea how they even got messed up as I created them in order. Up arrow, change number, up arrow, change numberetc, etc. I'm ranting, but this is really ticking me. TIA - 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 optimization
I'm looking for recomendations on improving system performance. Using the SQL command below on a MySQL server I find result times averaging three seconds. Being very inexperienced with database programming I would appreciate any comments on whether this is expected behaviour, or where my design might improve. The details of my system are below. The data I'm looking for starts with tblItems which holds a list of Items we're looking to buy. I want the name of the item from tblStock, and statistics on prices we've found in our history (if they exist). I've never done a multi table join before and this is what I've come up with: 300 Mhz, 32 MB RAM CPU: 65% idle, RAM: 12MB Free OS: Slackware Linux 8.1 (2.4.18) Mysqld Ver 3.23.53a for pc-linux-gnu on i686 mysql select fldName, fldQuantity, fldTotalCost, tblntItems.fldObjectID, avg(fldQuote), count(fldQuote), max(fldQuote), min(fldQuote) from tblStock, tblntItems left join tblntQuotes on tblntItems.fldObjectID = tblntQuotes.fldObjectID where tblntItems.fldObjectID = tblStock.fldObjectID group by fldObjectID; mysql show fields from tblStock; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | fldObjectID | int(11) | YES | | NULL| | | fldName | varchar(255) | YES | | NULL| | | fldCost | int(11) | YES | | NULL| | | fldRetail | int(11) | YES | | NULL| | | fldUpdate | varchar(255) | YES | | NULL| | +-+--+--+-+-+---+ 5 rows in set (0.00 sec) aprox 7000 items. mysql show fields from tblntQuotes; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | fldObjectID | int(11) | YES | | NULL| | | fldDate | date| YES | | NULL| | | fldSource | int(11) | YES | | NULL| | | fldQuote| double | YES | | NULL| | +-+-+--+-+-+---+ 4 rows in set (0.00 sec) approx 130 records. mysql show fields from tblntItems; +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | fldObjectID | int(11) | YES | | NULL| | | fldQuantity | int(11) | YES | | NULL| | | fldTotalCost | double | YES | | NULL| | +--+-+--+-+-+---+ 3 rows in set (0.00 sec) approx 100 records. Regards, John Glenn - 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[2]: No descending index ?
Hi, I assume it's because MySQL returns more than 30% of the totally of the rows. On one of my table, with 4.0.6-gamma : mysql SELECT COUNT(*) FROM news; +--+ | COUNT(*) | +--+ | 4985 | +--+ 1 row in set (0.00 sec) mysql SELECT COUNT(*) FROM news WHERE datec '2000-12-31' order by datec DESC; +--+ | COUNT(*) | +--+ | 778 | +--+ 1 row in set (0.01 sec) mysql EXPLAIN SELECT * FROM news WHERE datec '2000-12-31' order by datec DESC; +---+---+---+---+-+--+--+--- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+---+---+---+-+--+--+--- --+ | news | range | datec | datec | 8 | NULL | 761 | Using where | +---+---+---+---+-+--+--+--- --+ 1 row in set (0.00 sec) 30 %, so it uses index mysql SELECT COUNT(*) FROM news WHERE datec '2001-12-31' order by datec DESC; +--+ | COUNT(*) | +--+ | 2998 | +--+ 1 row in set (0.01 sec) mysql EXPLAIN SELECT * FROM news WHERE datec '2001-12-31' order by datec DESC; +---+--+---+--+-+--+--+- + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+- + | news | ALL | datec | NULL |NULL | NULL | 4985 | Using where; Using filesort | +---+--+---+--+-+--+--+- + 1 row in set (0.00 sec) 30%, so no index used So the result of your explain seems normal for me. Regards, Jocelyn - Original Message - From: Mpu Gondrong [EMAIL PROTECTED] To: Jeremy Zawodny [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 1:13 AM Subject: Re[2]: No descending index ? Selasa, 17/12/2002 7:47:38, Jeremy menulis: JZ I'm not quite sure what you're after, but MySQL 4.0 has support JZ for reading indexes in reverse order in some circumstances. This JZ greatly speeds up some queries that were slow in 3.23. I'm using MySQL 4.0.4-beta. For example: EXPLAIN SELECT * FROM news where date = '2002-12-31' order by date desc table type possible_keys key key_len ref rows Extra news ALL date NULL NULLNULL 14138 where used Or is that normal ? Any sample 'circumstances' ? TIA. Tertanda, Oguds [36856104] - 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: more about using sets
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi again -- Well, the general consensus seems to be to stay violently away from SETs and ENUMs :-) I'll certainly give that a shot. I still have to figure out how to make sure that our credit card types and skill levels don't get corrupted (MC, MasterCard, mastercard, ...), but I guess that gets enforced in the software interface, right? Thanks again HAND Happy Holidays mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE9/o7yGb7uCXufRwARAiydAKCbAOb/tOdioAWx8vG2k2aX1saIygCg0Kx/ xJVcxeAOLDQTB/YHEqd0P6E= =1iKe -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ODBC query freezes server
Hi all, I am having a problem with MySQL and I hope some nice guru can help me. I am running MySQL server v3.23.49a on Red Hat Linux v7.1. I also need to run queries on Microsoft Access running on my Win2K machine, using a MyODBC connection over the Internet. However when I run certain queries my whole MySQL server hangs until the query is complete (up to 10 minutes). Thus my entire website hangs while I run reports. :( My queries use around 5 tables with 100,000 rows in total. Perhaps my query is locking too many tables or using too many threads? I am certain that I am NOT using the maximum # of MySQL network connections (it is set to 1,000 and netstat reports 100 open connections). Is there a way to limit resources so that a single query doesn't bring my server to its knees? Any and all replies are much appreciated. Thanks in advance! Barry _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail - 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: ODBC query freezes server
B F wrote: Hi all, I am having a problem with MySQL and I hope some nice guru can help me. I am running MySQL server v3.23.49a on Red Hat Linux v7.1. I also need to run queries on Microsoft Access running on my Win2K machine, using a MyODBC connection over the Internet. However when I run certain queries my whole MySQL server hangs until the query is complete (up to 10 minutes). Thus my entire website hangs while I run reports. :( My queries use around 5 tables with 100,000 rows in total. Perhaps my query is locking too many tables or using too many threads? I am certain that I am NOT using the maximum # of MySQL network connections (it is set to 1,000 and netstat reports 100 open connections). Is there a way to limit resources so that a single query doesn't bring my server to its knees? Any and all replies are much appreciated. Thanks in advance! Barry Access will pull the entire tables across the internet and then query the data locally. This is what's taking all the time. Probably Access is requesting a lock until it gets the entire recordset(s). Set up pass-through queries from Access (or better still use ADO / VB). To set up a pass-through query, design the query, switch to SQL mode, copy the text (Access sometimes deletes it at this next stage) and click Query == SQL Specific == Pass Through. Then edit the query properties, and set up your connection options to MySQL. You may have to edit the query to get rid of Access' additions (eg [] around everything). This will make MySQL perform the query and send the results to Access, instead of having Access use MySQL as a dumb data storage device. The second option (ADO / VB) is a bit more complicated but more versatile. I won't go into the details here, but you can search on the 'net for ADO - you'll find plently of examples. Note that using Access to query MySQL over the internet isn't exactly secure. Someone correct me if I'm wrong here, but doesn't this send clear-text passwords across the internet? -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How can I duplicate a mysql template database?
I need to use PHP to duplicate the schema of a mysql database. This
seems like it should be a simple task -- taking a 'template' db and
cloning it with a new name.
I've tried things like:
$filename = myDB.sql;
$fd = fopen ($filename, r);
$sql = fread ($fd, filesize ($filename));
fclose ($fd);
And
$lines = file($filename);
foreach ($lines as $line_num = $line) { $sql .= $line;
}
And
$sql .= CREATE TABLE IP_Dept (;
$sql .= IP_Addr int(10) unsigned NOT NULL default
'0',;
$sql .= DeptID int(10) unsigned NOT NULL default
'0';
$sql .= );;
$sql .= CREATE TABLE ResolveTable (;
$sql .= IP_Addr int(10) unsigned NOT NULL default
'0',;
$sql .= Name char(255) NOT NULL default '',;
$sql .= Custom char(1) default NULL,;
$sql .= Global char(1) default 'Y',;
$sql .= OSVersion char(255) default NULL,;
$sql .= RowID int(10) unsigned NOT NULL
auto_increment,;
$sql .= Display enum('Yes','No') NOT NULL default
'Yes',;
$sql .= PRIMARY KEY (RowID);
$sql .= );;
echo PRE.$sql./PREP;
$result = mysql_query($sql,$linkI);
But ALL of them fail! Ugh!!! Can I not stack commands like that? Is
there some way to read in a .sql file via PHP? The problem is that my
web pages are on a web server and the db is on a mysql server which are
different machines, so calling a system() or other execute style
function won't work for me.
I figured, Ah! Why don't I just make a template db on the server and
issue some SQL command to 'clone' that and rename it. You'd think that
was pretty straight forward, but I can't find any examples or commands
to do this seemingly trivial task. Ideas?
-
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]: No descending index ?
Selasa, 17/12/2002 9:14:39, Jocelyn menulis: 30%, so no index used JF So the result of your explain seems normal for me. Sooo..., back to my first thread, could I use index for such query (datec = '...') ? Query resulting 30% is very seldom (so index is mostly never used), and very weird if ascending index used for descending query. I'm not talking about returning correct result set (either order by desc, limit, etc), but avoid table scan. This is my first serious mysql application, as usually i'm using Interbase where has descending index feature. Well..., any hope ? Btw, thanks for all replies. Tertanda, Oguds [36856104] - 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: mailing list problem?
Yes, exactly. I think I did find that I was missing digest number 2201 so maybe that is what it was referring to. Bill At 12:01 +1100 12/17/02, Fraser Stuart wrote: Hi Bill, I did too. Did you get the following error? CNAME lookup failed temporarily. (#4.4.3) I'm not going to try again; this message has been in the queue too long. Fraser. mysql query _ Fraser Stuart Logistics IT 77-85Phone: +61 2 9335 1235 Roberts Rd Mobile: +61 419 233 732 Greenacre NSW [EMAIL PROTECTED] Australia 2190 www.toll.com.au _ | -Original Message- | From: Bill Rausch [mailto:[EMAIL PROTECTED]] | Sent: Tuesday, 17 December 2002 11:52 AM | To: [EMAIL PROTECTED] | Subject: mailing list problem? | | | I just got a message from the ezmlm program telling me that the | mysql digests have been bouncing and it is going to remove me | from the list. | | In fact, the digests have been coming through just fine. Any ideas | on what it could be referring to? Did anyone else get such a message? | | Bill | | -- | Bill Rausch, Software Development, UNIX, Mac, Windows | Numerical Applications, Richland, WA 509-943-0861 x302 | | - | 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 | | -- Bill Rausch, Software Development, UNIX, Mac, Windows Numerical Applications, Richland, WA 509-943-0861 x302 - 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 duplicate a mysql template database?
Are you first creating a new database first with mysql_create_db()
before trying to run your schema? What error message do you get? The
code you've posted is only creating a table.
Also, rather than opening and reading in an external file or doing all
those $sql .= lines, you might have an easier time using heredoc syntax,
which would let you do something like
$sql = SQL
CREATE TABLE IP_Dept (
IP_Addr int(10) unsigned NOT NULL default
DeptID int(10) unsigned NOT NULL default
...etc
SQL;
$result = mysql_query($sql,$linkI);
(hopefully $linkI is your mysql connection) This way you don't have to
bother with all that quoting.
-bill
Daevid Vincent wrote:
I need to use PHP to duplicate the schema of a mysql database. This
seems like it should be a simple task -- taking a 'template' db and
cloning it with a new name.
I've tried things like:
$filename = myDB.sql;
$fd = fopen ($filename, r);
$sql = fread ($fd, filesize ($filename));
fclose ($fd);
And
$lines = file($filename);
foreach ($lines as $line_num = $line) { $sql .= $line;
}
And
$sql .= CREATE TABLE IP_Dept (;
$sql .= IP_Addr int(10) unsigned NOT NULL default
'0',;
$sql .= DeptID int(10) unsigned NOT NULL default
'0';
$sql .= );;
$sql .= CREATE TABLE ResolveTable (;
$sql .= IP_Addr int(10) unsigned NOT NULL default
'0',;
$sql .= Name char(255) NOT NULL default '',;
$sql .= Custom char(1) default NULL,;
$sql .= Global char(1) default 'Y',;
$sql .= OSVersion char(255) default NULL,;
$sql .= RowID int(10) unsigned NOT NULL
auto_increment,;
$sql .= Display enum('Yes','No') NOT NULL default
'Yes',;
$sql .= PRIMARY KEY (RowID);
$sql .= );;
echo PRE.$sql./PREP;
$result = mysql_query($sql,$linkI);
But ALL of them fail! Ugh!!! Can I not stack commands like that? Is
there some way to read in a .sql file via PHP? The problem is that my
web pages are on a web server and the db is on a mysql server which are
different machines, so calling a system() or other execute style
function won't work for me.
I figured, Ah! Why don't I just make a template db on the server and
issue some SQL command to 'clone' that and rename it. You'd think that
was pretty straight forward, but I can't find any examples or commands
to do this seemingly trivial task. Ideas?
-
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
