RE: Jeremy's MySQL Book
Hello all, Please advise location of the toc. Kind reagrds Emmanuel -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: 10 January 2002 16:59 To: Rick Emery Cc: '[EMAIL PROTECTED]' Subject: Re: Jeremy's MySQL Book On Thu, Jan 10, 2002 at 08:32:03AM -0600, Rick Emery wrote: Jeremy, Late last year, you were creating the Table of Contents for a book about MySQL...how's it coming? I know that many on this list were anxious to get a sneak peak, and perhaps suggest additional topics, such as advantages and conditions for using INNODB and BDB tables versus MYISAM. And how to install, configure and use MySQL ODBC drivers. Perhaps even how to access MS Access databases through MySQL. Hi Rick, I'll more information about the book on the web site I'm setting up for it (advanced-mysql.com). The domain is being registered today, so it'll be up over the weekend. In the meantime, here's the last TOC outline I had put together. Comments are welcome, of course. ---snip--- Title: Advanced MySQL (tentative) Chapter 0: Introduction Goals of this book * explain how mysql works under the hood * understand why it is fast what can slow it down (at all levels) * discuss real-world problems and solutions * expose good practices for use in large or demanding environments * discuss MySQL's weaknesses and how to work around them What you need to know (and why) * basic sql * mysql installation and administration * a scripting language (such as perl or python) What you won't read about (and why) * programming languages * the mysql api * extending or embedding mysql * clustering * commercial load-balancing solutions (there's a book on that) Chapter Overview Chapter 1: Configuration Basics (see Questions below) my.cnf, MySQL's Configuration File MySQL's Startup Options SHOW VARIABLES and SHOW STATUS Chapter 2: MySQL's Table Types (or Handlers?) MySQL's Modular Architecture * core services - sql parser - functions - logging * table handlers - data access - functionality differences * design benefits Locking and Concurrency * table vs. page vs. block vs. row (granularity) * read vs. write (exclusivity) * multi-versioning Transactional vs Non-Transactional * acid features - atomicity - consistency - isolation - durability * tradeoffs - concurrency - performance * simulating transactions with table locks (maybe a sidebar?) * isolation levels The Table Handlers * MyISAM (compressed MyISAM, and MERGE too) * HEAP * BDB (Berkeley DB) * InnoDB * Gemini * (possibly include a table which summarizes the features of all?) Selecting the Right Table Type Chapter 3: Indexes What are Indexes? * unique / non-unique indexes * unique non-indexes (in 4.x according to monty) * primary keys * partial indexes * compound (multi-part) indexes * packed keys How MySQL Stores and Maintains Indexes * storage requirements * common prefixes and space compression * where indexes are stored (for each table type) * hash indexes on heap tables * the myisam key buffer and innodb/gemini buffer pools * performance impact: updates vs. reads - discuss delayed key writes for myisam When Does MySQL Use an Index? * the most specific index * exact matches * ranges * the 40% rule * left-most prefixes * finding unused indexes * order by and group by queries * distinct queries * queries using only indexed columns When MySQL Never Uses and Index * regular expressions * wildcard prefix matches Understanding and Maintaining Your Indexes * Index File Sizes * SHOW KEYS * Cardinality * ANALYZE TABLE * Index Performance Counters * Index Buffers Full-text Indexing Chapter 4: Making Queries Fast How MySQL Processes Queries * query cache (new in 4.0 tree) * parsing * optimization planning * execution Identifying Slow Queries * the slow query log * using mysqldumpslow * using explain_log (new in 4.0) Using and Understanding EXPLAIN * single table select with index * single table select with no index * multi-way joins Influencing MySQL's Choices * straight joins * index hints in queries * analyze
Error 1030: Got error 124 from table handler
Description: When I query using SELECT d1.document_id as document_id_1, d2.document_id as document_id_2, sum(d1.weight * d2.weight) as similarity FROM dw_merge as d1 INNER JOIN dw_merge as d2 ON d1.word_id=d2.word_id WHERE d1.document_id=5 AND d1.weight 0.05 AND d2.weight 0.05 GROUP BY document_id_1, document_id_2 ORDER BY similarity DESC limit 0,50; dw_merge table is a MERGE table, it reference to 100 tables. The total number of records of the 100 tables is about 400,000 records. How-To-Repeat: the table schema of the 100 tables is CREATE TABLE document_word ( document_id int(11) DEFAULT '0' NOT NULL, word_id int(11) DEFAULT '0' NOT NULL, freq int(6) DEFAULT '0' NOT NULL, weight float(7,5) DEFAULT '0.0' NOT NULL, PRIMARY KEY (document_id, word_id), KEY word_id (word_id), KEY document_id (document_id), KEY weight (weight) ); Fix: Submitter-Id: submitter ID Originator:Herb Jiang Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-4.0.1-alpha (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 4.0.1-alpha, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.1-alpha Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 18 min 28 sec Threads: 2 Questions: 136 Slow queries: 0 Opens: 137 Flush tables: 1 Open tables: 130 Queries per second avg: 0.123 Environment: System: Linux herb-nb 2.4.14-2HJ #6 ¶g¥| 11¤ë 15 21:42:14 CST 2001 i686 pld 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/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-99) 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 13 12¤ë 14 10:58 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1290876 10¤ë 13 02:30 /lib/libc-2.2.4.so -rw-r--r--1 root root 27316592 10¤ë 13 02:17 /usr/lib/libc.a -rw-r--r--1 root root 178 10¤ë 13 02:17 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-berkeley-db --with-innodb --enable-assembler --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/info --includedir=/usr/include --mandir=/usr/man --with-embedded-server --enable-thread-safe-client '--with-comment=Official MySQL RPM' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Com_select larger than questions bug?
Hi! Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes: Sinisa Ken Menzel writes: From show status (in mysql 4.0.1) | Com_select | 192446| | Questions| 121881| Why are there more selects than the total number of questions? Is that correct? There are of course Insert, deletes and other Com_ variables as well. I can provide the whole list! Thanks, Ken - Ken Menzel ICQ# 9325188 www.icarz.com [EMAIL PROTECTED] Sinisa Hi! Sinisa This is a bug that we shall soon sort out ... Sinisa You are evidently using our BK tree ... This come from a wrong merge with between the 3.23 and 4.0 tree. Here is a fix for this: (/my/mysql) bk diffs -c sql/sql_parse.cc = sql/sql_parse.cc 1.208 vs edited = *** /tmp/sql_parse.cc-1.208-15803 Thu Jan 3 00:46:43 2002 --- edited/sql/sql_parse.cc Sat Jan 12 12:45:16 2002 *** *** 1216,1222 #endif } - thread_safe_increment(com_stat[lex-sql_command],LOCK_thread_count); /* Skip if we are in the slave thread, some table rules have been given and the table list says the query should not be replicated --- 1216,1221 I will push this to the 4.0 tree later today. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Bug: BINARY, NULL, and GROUP BY
Hours after I left this mailing list, I found a bug
in 3.23.47. Since it's so easy to reproduce the bug here,
I'll let this example speak for itself:
CREATE TABLE t1 (txt varchar(10) default NULL);
INSERT INTO t1 VALUES ('aaa');
INSERT INTO t1 VALUES (NULL);
SELECT BINARY txt AS t FROM t1 GROUP BY t;
On my Linux systems, and MySQL compiled with gcc 2.95.3,
this invariably leads to a segfault. Removing the non-null
row from t1 solves the problem, as does removing the row
with null. Without BINARY everything works.
I'm no longer on the list, so if you reply to this mail
with comments or questions that you think I should read,
please be sure to include my address.
//Carl
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Bug: BINARY, NULL, and GROUP BY
At 12:13 12/01/2002 +0100, Carl Troein wrote:
Hi!
Thanks you for the bug report I was be able to repeat
in the 4.0.1 Windows release too.
Regards,
Miguel
CREATE TABLE t1 (txt varchar(10) default NULL);
INSERT INTO t1 VALUES ('aaa');
INSERT INTO t1 VALUES (NULL);
SELECT BINARY txt AS t FROM t1 GROUP BY t;
On my Linux systems, and MySQL compiled with gcc 2.95.3,
this invariably leads to a segfault. Removing the non-null
row from t1 solves the problem, as does removing the row
with null. Without BINARY everything works.
I'm no longer on the list, so if you reply to this mail
with comments or questions that you think I should read,
please be sure to include my address.
//Carl
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Miguel A. Solórzano [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer
/_/ /_/\_, /___/\___\_\___/ Mogi das Cruzes - São Paulo, Brazil
___/ 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: Multi-table delete/update
hi! Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes: Sinisa Becky McElroy writes: Two questions regarding multi-table operations: 1) I've got multi-table delete working with a couple of BDB tables, where it's properly deleting from one or both tables, and all possibilities of the delete criteria are being exersized. After the multi-table delete command, I can 'select *' from the first table referenced in my multi-table delete just fine, but when I do 'select *' for the second table, I get ERROR 1032: can't find record in 'tblname' If I do it again, then I get the query output just fine for the second table from that point on. (I'm using MySQL-Max-4.0.1-2 on Red Hat linux 6.1) It's looking like a bug. Has anyone else encountered this? 2) Does anyone know when multi-table update will be available? Thanks. -- Becky McElroy Sinisa Hi! Sinisa Regarding the above error with BDB tables, there was a bug in 4.0.0, Sinisa which should have been fixed in 4.0.1. Becky, as you are already using 4.0.1: Any change you could do a test case for us so that we could repeat this problem? This would enable us to fix this for 4.0.2 Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Win 2000 MyCC problem (database)
When i try to login to database (success) it breakes down. Maybe you fix it? Dmitri - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Spam - a possible cause ?
Matthew, Posting simple questions, even different people repeatedly posting simple questions is NOT spam. It may be a problem for some people, but it is not what this discussion is about. Spam is people not interested in MySQL at all posting a Business opportunity (Dear mysql, Join today for FREE and a chance to win a $100 Shopping Spree at The DHS Club Outlet Center!) or Golden Investment Opportunity or Great Growth Potential stock scams. At 01:07 2002-01-12, you wrote: I am an experienced programmed and have worked on Unix and with Oracle and Informix for many years. I personally am struggling to get my head around Mysql (getting there now though) I think part of the reason I am posting simple questions is. -- Marjolein Katsma HomeSite Help - http://hshelp.com/ - Extensions, Tips and Tools The Bookstore - http://books.hshelp.com/ - Books for webmasters and webrookies - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: BINARY, NULL, and GROUP BY
* Carl Troein
CREATE TABLE t1 (txt varchar(10) default NULL);
INSERT INTO t1 VALUES ('aaa');
INSERT INTO t1 VALUES (NULL);
SELECT BINARY txt AS t FROM t1 GROUP BY t;
FYI:
I tried this on my win2000 3.23.30-gamma server, it crashed.
If I remove BINARY or GROUP BY t, it does not crash.
--
Roger
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: howto: Rebuild a FullText index
Hi! On Jan 07, Jason Kushmaul wrote: I have changed a fulltext variable ft_min_word_len from the default 4 to 1, and the manual says to rebuild my fulltext indexes once again. I verified that this variable changed with SHOW VARIABLES and it did change. I have tried optimize table, analyze table, repair table which all finished without errors and very quickly, but When I try a new simple query I can see that the index was not rebuilt. I search for the word '+the*' which shows up in about every record. I didn't get any records back earlier because my ft_min_word_len was too high. but I still do not get any records. Does anyone know what I am doing wrong? Sorry, it's our fault. When the manual was written OPTIMIZE TABLE would be enough, since then it was optimized itself... Anyway, ALTER TABLE tablename TYPE=MyISAM; is enough. We'll update the manual. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: check for server start
Aaron Brick writes: hi all, in debian, the /etc/init.d/mysql script waits for the appearance of /var/run/mysqld/mysqld.pid to conclude that the server has started. since the red hat init script does not do that, my program's own installer has to. however we have had a report that waiting for the pidfile is not enough; that the server wasn't ready for connections even after the file appeared. is that possible, and how can we (in sh) better determine when the server is up and ready? thanks, aaron brick. /\ | Aaron Brick (415) 206 - 4685 | | [EMAIL PROTECTED] Room 3501C, SFGH | Programmer Analyst, Functional Genomics Core Sandler Center for Basic Research in Asthma Lung Biology Center, Department of Medicine San Francisco General Hospital University of California, San Francisco Absolutely the best way to do it is: mysqladmin ping -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fulltext search
Hi!
On Jan 09, Pawan Tejpal SA Intellectual Property Ptejpal Knowhow Informatics NIC wrote:
Hello
Recently we have installed mysql ver 4.0.1(from
binaries).We loaded about 1.6 million records in a table.
One of the fields 'ti' is text field. We created fulltext
index on this field.
on giving following :-
1. select ti from p8387 where match ti against ('computer');
1701 rows were returned.
2. select ti from p8387 where match ti against
('processor');
5608 rows were returned.
3. select ti from p8387 where match ti against
('+computer+processor');
it still returns 5608 rows.
when we gave the following :
4. select ti from p8387 where match ti against
('computer') and match ti against ('processor')
only 31 rows were returned
Can anyone tell me what is the problem with statement no. 3
does mysql 4.0.1 still not support this type of fulltext
search.
Pl. help
Kind Regards
P Tejpal
See the manual.
To use boolean operators in fulltext search you have to use
... AGAINST (... IN BOOLEAN MODE)
Yes, it's new to 4.0.1, but as boolean fulltext search in 4.0.0,
was not documented at all - it was kind of hidden feature -
we took the liberty of changing the syntax.
Regards,
Sergei
--
MySQL Development Team
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is there a QNX port?
Therrel Griffin writes: Just wanted to know if anyone has compiled MySQL under QNX, and if they did, how they did it. Any help would be appreciated. Therrel MySQL can be built on QNX 6.* with GNU tools. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query syntax: multiple foreign keys
I'm new to the list, to mysql and to dynamic website programming. I'm not new to programming, had my nose in Access97 for the last few years, off and on. So I'm used to being coddled with sql and can't find a syntax that works for this situation: (I'm using phpMyAdmin to work with the database and test the queries. The server is remote.) 3 tables: Persons, QualityA, QualityB Table Persons contains integer foreign keys for the other two tables. I need a query that for a certain Person in table Persons, returns QualityA.Name and QualityB.Name. If I use: SELECT A.Name, B.Name, P.ID FROM Persons P, QualityA A, QualityB B WHERE P.ID = thatGuy, P.A_ID = A.A_ID, P.B_ID = B.B_ID; I get strange results NameNameID B Quality Bnbsp;QualitythatGuy instead of NameNameID A Quality B Quality thatGuy To be specific, for the A.Name column of the results, I get the correct value for the B.Name; for the B.Name column, I get the same value, but it replaces the space char in the value with nbsp; ?! I've tried many different combinations, but either get similar weird results or error messages. If I throw out the QualityB table, I can get it to work... SELECT A.Name, P.ID FROM Persons P, QualityA A WHERE P.ID = thatGuy, P.A_ID = A.A_ID; NameID A Quality thatGuy Note: I use e-mail on a different machine/platform than I use for the dynamic website programming, so I'm writing from memory, using different identifiers for this posting than the actual ones used in my database. I have limited time to work on this project and will greatly appreciate a hand getting over this wall. I've searched for hours on the net, and tried the IRC channels, but everything is written for one or two table queries. James Birkholz Admin of Posen-L mailing list website - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 admin with no password
Trond Eivind Glomsrød writes: We used to do that, but got complaints: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=51711 It wreaked havoc on bin-logs. OTOH, we need to be able to have scripts controlling the DB as root, while not needing to have a passwordless DB account. -- Trond Eivind Glomsrød Red Hat, Inc. Thank you for your report. We will take a look at this. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help please, patter matching problem
mysql and Perl Can someone tell me what's wrong with this code: $sth=$dbh-prepare(SELECT Contacts FROM Info WHERE Name=?); $sth-execute(%$sname%); $dname = $sth-fetchrow_array; $names is supposed to be a substring. So if I want to search for a middle name or just a first name or even a last name. Any help is greately appreciated. Daniel. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Restoring database from hotcopy
Hi Travis, I have the database files, now how do I put them back into mysql? mysqlhotcopy simply copies the data files to another directory. To restore them, terminate the mysql server and then copy the files back to the mysql data directory (e.g. /var/lib/mysql/db name). Note that mysqlhotcopy fails to backup all data if you use table handlers that use different files to store the data (like InnoDB does). Jens - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error 1030: Got error 124 from table handler
[EMAIL PROTECTED] writes: Description: When I query using SELECT d1.document_id as document_id_1, d2.document_id as document_id_2, sum(d1.weight * d2.weight) as similarity FROM dw_merge as d1 INNER JOIN dw_merge as d2 ON d1.word_id=d2.word_id WHERE d1.document_id=5 AND d1.weight 0.05 AND d2.weight 0.05 GROUP BY document_id_1, document_id_2 ORDER BY similarity DESC limit 0,50; dw_merge table is a MERGE table, it reference to 100 tables. The total number of records of the 100 tables is about 400,000 records. How-To-Repeat: the table schema of the 100 tables is CREATE TABLE document_word ( document_id int(11) DEFAULT '0' NOT NULL, word_id int(11) DEFAULT '0' NOT NULL, freq int(6) DEFAULT '0' NOT NULL, weight float(7,5) DEFAULT '0.0' NOT NULL, PRIMARY KEY (document_id, word_id), KEY word_id (word_id), KEY document_id (document_id), KEY weight (weight) ); Hi! If you repair all tables contained within MERGE will the above SELECT still return the same error ?? This is very important for us to know. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query syntax: multiple foreign keys
James, I'm new to the list, to mysql and to dynamic website programming. I'm not new to programming, had my nose in Access97 for the last few years, off and on. So I'm used to being coddled with sql and can't find a syntax that works for this situation: (I'm using phpMyAdmin to work with the database and test the queries. The server is remote.) 3 tables: Persons, QualityA, QualityB Table Persons contains integer foreign keys for the other two tables. I need a query that for a certain Person in table Persons, returns QualityA.Name and QualityB.Name. If I use: SELECT A.Name, B.Name, P.ID FROM Persons P, QualityA A, QualityB B WHERE P.ID = thatGuy, P.A_ID = A.A_ID, P.B_ID = B.B_ID; I get strange results Name Name ID B Quality Bnbsp;Quality thatGuy instead of Name Name ID A Quality B Quality thatGuy To be specific, for the A.Name column of the results, I get the correct value for the B.Name; for the B.Name column, I get the same value, but it replaces the space char in the value with nbsp; ?! I've tried many different combinations, but either get similar weird results or error messages. If I throw out the QualityB table, I can get it to work... SELECT A.Name, P.ID FROM Persons P, QualityA A WHERE P.ID = thatGuy, P.A_ID = A.A_ID; Name ID A Quality thatGuy Note: I use e-mail on a different machine/platform than I use for the dynamic website programming, so I'm writing from memory, using different identifiers for this posting than the actual ones used in my database. I have limited time to work on this project and will greatly appreciate a hand getting over this wall. I've searched for hours on the net, and tried the IRC channels, but everything is written for one or two table queries. Change the WHERE clause so that the three comparisons are joined in one logical expression/result: SELECT A.Name, B.Name, P.ID FROM Persons P, QualityA A, QualityB B WHERE P.ID = thatGuy AND P.A_ID = A.A_ID AND P.B_ID = B.B_ID; =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: help please, patter matching problem
* Dan Can someone tell me what's wrong with this code: $sth=$dbh-prepare(SELECT Contacts FROM Info WHERE Name=?); $sth-execute(%$sname%); You should use the LIKE operator: Name LIKE %roger% URL: http://www.mysql.com/doc/S/t/String_comparison_functions.html -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query syntax: multiple foreign keys
In a message dated 1/12/02 10:48:45 AM Central Standard Time, [EMAIL PROTECTED] writes: ---snip--- SELECT A.Name, B.Name, P.ID FROM Persons P LEFT JOIN QualityA A USING(A_ID) LEFT JOIN QualityB B USING(B_ID) WHERE P.ID = thatGuy; ---snip--- That doesn't work, get an error as it tries to find B_ID in the A table. I tried swapping the partners on the first LEFT JOIN... SELECT A.Name, B.Name, P.ID FROMQualityA A LEFT JOIN Persons P USING(A_ID) LEFT JOIN QualityB B USING(B_ID) WHERE P.ID = thatGuy but that gives me more strange results (using my example IDs:) NameNameResearcher A Quality AQuality thatGuy B Quality Bnbsp;QualitythatGuy Here is the actual query and results, using the IDs that I am really using: SELECT L.Name, C.Name, R.Researcher FROM Languages L LEFT JOIN Researchers2Languages R USING(Language) LEFT JOIN Competencies C USING(Competence) WHERE R.Researcher = 39 LIMIT 0, 30 NameNameResearcher English English 39 Can readCannbsp;read 39 The correct results would be L.Name C.Name R.Researcher English Native speaker 39 German Can read39 Trying to think of anything that might be use, I'll mention that I'm using SMALLINT for the foreign key Language and TINYINT for the Competency FK. In this situation, I *could* hard code C data into the A table, but I use similar constructs all the time and need to get a handle on this. If I need to describe the conditions better, I will be happy to. Thanks in advance for any suggestions. James Birkholz Admin of Posen-L mailing list website - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query syntax: multiple foreign keys
* =James Birkholz= In a message dated 1/12/02 10:48:45 AM Central Standard Time, [EMAIL PROTECTED] writes: ---snip--- SELECT A.Name, B.Name, P.ID FROM Persons P LEFT JOIN QualityA A USING(A_ID) LEFT JOIN QualityB B USING(B_ID) WHERE P.ID = thatGuy; ---snip--- That doesn't work, get an error as it tries to find B_ID in the A table. Sorry, my bad. USING() depends on the table immediately preceeding it. Try this: SELECT A.Name, B.Name, P.ID FROM Persons P LEFT JOIN QualityA A ON A.A_ID=P.A_ID LEFT JOIN QualityB B ON B.B_ID=P.B_ID WHERE P.ID = 1; I tried swapping the partners on the first LEFT JOIN... SELECT A.Name, B.Name, P.ID FROMQualityA A LEFT JOIN Persons P USING(A_ID) LEFT JOIN QualityB B USING(B_ID) WHERE P.ID = thatGuy but that gives me more strange results (using my example IDs:) Name NameResearcher A Quality AQuality thatGuy B Quality Bnbsp;QualitythatGuy If the above does not help, post the output of desc Persons, desc QualityA, desc QualityB and select * ... from the same three tables. -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Input Needed: Replication issues...
Hi! Two-way replication is complex, and even more difficult is coping with broken communications. MySQL only supports one-way replication (master - slaves). Auto-inc column values are communicated in the binlog to the slaves. Some ideas: - Generate primary keys where you concatenate the id of the database site before the id number: Paris0234, NewYork0123 Writing SELECT queries will be difficult. - Write your own replication script like Kyle has done (maybe Kyle could use the 'query-log' of MySQL instead of the 'update-log'?). Communicate changes to the databases in human-readable SQL strings between the database nodes. But you must be very careful in your application to take into account that the two nodes will not be identical, because changes to them will be recorded in different orders. The problem of conflicting primary keys during a communication break can be solved with the above trick. - Transactions might help a little bit if you want to make several changes to the database as one atomic operation (= transaction). 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 . On Thursday 10 January 2002 17:22, Matthew Walker wrote: Where I work, we're at the point of needing to maintain two database servers, one inhouse, and the other at another physical location. We need to replicate between the servers, to keep them both up to date, as both will be getting updated. This leaves us with the problem of resolving primary key conflicts if the servers should ever lose touch with eachother for a while. We're looking at InnoDB, and wondering if it would be able to resolve those sorts of issues using transactions. Input please? We looked closely at native MySQL replication, but after a few questions about the handling of auto increment fields (we use them like row IDs all over the place), we decided that MySQL's replication was broken for our application. We have more than one live database and they replicate to each other. We write our own replication system using our own checkpointing and code. We start with the update logs (we're not happy about the future removal of the update logs because of this). I've looked through the MySQL source to see if I can figure out enough of the format of the binary log to see if I can make some Perl code that will parse it. It is really easy to use the update log since all SQL end with a semicolon as the last character on a line. Since Perl tends to be line-oriented, it is easy to find this. Here's what we do: - we run a special server process on each database. This replicator server will spool update log files on demand. - on each machine, we run a special client process. This process connects to the replicator server (not MySQL's), gets the update log information and puts it into the local database. If necessary, we can rewrite any SQL (we don't). The server process puts a special checkpoint comment between each SQL statement. The checkpoint comments has a special header and trailer so that we can recognize it. It contains the name of the file that is currently being spooled and the byte offset in that file. This information is stored on the client side in a special checkpoint file. If the client process needs to be restarted or the network drops or something causes replication to quit, it will restart where it left off based on the checkpoint file. This functionality is basically identical to the native MySQL replication. The reasons for using the update log and not native MySQL replication are these: - we can have more that two servers coupled together. - we use auto increment fields all over. With native mySQL replication, these fields are filled in with values on the source side. Thus, we get collisions on the target side. We have no need for the row IDs to match on each machine as every row has another unique ID field. We do need the row IDs for purging old data and other things that do not rely on the unique ID field. - we can do SQL rewriting if we really need to. These problems led us to write our own replication. If you have the luxury of redesigning your database or of designing with MySQL's native replication in mind (we did not), then I would use the native version. Best, Kyle - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Start problems
Per Moisdon a écrit : I'm sorry but I can't connect to mysql. I work on Linux SuSe 7.2 and Mysql version is 3.23.37. I installed it with Yast2 from the package of the SuSe distribution. I can't start the daemon and of course I can't connect (localhost) the server. I join the error file. Sorry for the quality of English. Thank you in advance. per Moisdon [EMAIL PROTECTED] 020112 20:33:42 mysqld started /usr/sbin/mysqld: File './linux-bin.index' not found (Errcode: 13) 020112 20:33:43 Could not use linux-bin for logging (error 13) 020112 20:33:43 /usr/sbin/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 020112 20:33:44 /usr/sbin/mysqld: Normal shutdown 020112 20:33:44 mysqld ended - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help please, patter matching problem
No closing quotes in prepare. Also, I've never seen code that's looks quite like that; this may be cleaner: $sth=$dbh-prepare(SELECT Contacts FROM Info WHERE Name=%$sname%); $sth-execute(); - Original Message - From: Dan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 12, 2002 9:49 AM Subject: help please, patter matching problem mysql and Perl Can someone tell me what's wrong with this code: $sth=$dbh-prepare(SELECT Contacts FROM Info WHERE Name=?); $sth-execute(%$sname%); $dname = $sth-fetchrow_array; $names is supposed to be a substring. So if I want to search for a middle name or just a first name or even a last name. Any help is greately appreciated. Daniel. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: help please, patter matching problem
Oops! And switch = to like and add quotes: $sth=$dbh-prepare(SELECT Contacts FROM Info WHERE Name like '%$sname%' ); $sth-execute(); - Original Message - From: Douglas Forrest [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Saturday, January 12, 2002 4:16 PM Subject: Re: help please, patter matching problem No closing quotes in prepare. Also, I've never seen code that's looks quite like that; this may be cleaner: $sth=$dbh-prepare(SELECT Contacts FROM Info WHERE Name=%$sname%); $sth-execute(); - Original Message - From: Dan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, January 12, 2002 9:49 AM Subject: help please, patter matching problem mysql and Perl Can someone tell me what's wrong with this code: $sth=$dbh-prepare(SELECT Contacts FROM Info WHERE Name=?); $sth-execute(%$sname%); $dname = $sth-fetchrow_array; $names is supposed to be a substring. So if I want to search for a middle name or just a first name or even a last name. Any help is greately appreciated. Daniel. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
Very large mysqld processes
Maybe it's just me, but I find that my mysqld proccesses consume quite a lot of memory. 7584 KB each to be exact. I've installed MySQL using the RPM on mysql.com (and I don't use the MAX version). I haven't made any config file, just installed it and let it run. Can I do anything about it? It's a bit annoying, since the machine runs approx. 29 mysqld processes at any time. I'm using Redhat 7.0 - Carsten - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Very large mysqld processes
In the last episode (Jan 12), Carsten Gehling said: Maybe it's just me, but I find that my mysqld proccesses consume quite a lot of memory. 7584 KB each to be exact. 7 MB is nothing. I'm amazed you get any performace at all out of that, since it can't have any cache. Is this reported from ps, or top? SIZE or RSS? Can I do anything about it? It's a bit annoying, since the machine runs approx. 29 mysqld processes at any time. Are you sure you mean 29 separate mysqld instances, each started in a different datadir and listening on a different port/socket, or are you just confused by Linuxes pthreads implementation? -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Very large mysqld processes
- Original Message - From: Dan Nelson [EMAIL PROTECTED] Sent: Saturday, January 12, 2002 11:23 PM In the last episode (Jan 12), Carsten Gehling said: Maybe it's just me, but I find that my mysqld proccesses consume quite a lot of memory. 7584 KB each to be exact. 7 MB is nothing. I'm amazed you get any performace at all out of that, since it can't have any cache. Is this reported from ps, or top? SIZE or RSS? It's reported from top Can I do anything about it? It's a bit annoying, since the machine runs approx. 29 mysqld processes at any time. Are you sure you mean 29 separate mysqld instances, each started in a different datadir and listening on a different port/socket, or are you just confused by Linuxes pthreads implementation? Ehm... Heh heh... You're absolutely right. After sending this mail, I did a calculation on the number of processes, and the memory consumed by each process. It didn't match up with the total amount of memory used. It was then that I realized, it's just threads which of course is also the reason why all of them use the same amount. :-) Sorry for that :-) Then maybe you can tell me how I should tune the server? You know make it use more memory, and thereby performing better? It's probably a FAQ, if so just post a URL to a guide. Thanks, - Carsten - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
What's this in my netstat?
Hi- Anyone know what web.mysql.com is doing? It's from a netstat on a RH 7.2 running mysql 3.23.47 myhost.mydomain:4570 web.mysql.com:auth TIME_WAIT Thanks- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select statement help...
Hello, I've been trying to figure this out but I feel like I'm just banging my head on the wall. I hope this is the right forum to pose this question. I have a table of flights which has several fields Table: flights Fields: depart, depart_time, arrive, arrive_time, flight_no, flight_group I'm trying to do a search on depart and arrive and show only the records which have matching flight_group Please help, Thanks, - Tom Jones [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: Select statement help...
Hello, I've been trying to figure this out but I feel like I'm just banging my head on the wall. That must be quite painful... I hope this is the right forum to pose this question. It's certainly one of the better ones, yes. I have a table of flights which has several fields Table: flights Fields: depart, depart_time, arrive, arrive_time, flight_no, flight_group I'm trying to do a search on depart and arrive and show only the records which have matching flight_group You're not providing a lot of information. Is it something like this, you're looking for: SELECT t1.depart_time, t2.arrive_time FROM flights AS t1, flights AS t2 WHERE t1.flight_group = t2.flight_group; / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ot: dates times etc
off topic topic of mysql databaseperl i need to know how to build a linux timestamp from a date given to me in the following format 01-18-99 (m-d-y) i am using mysql to store this as an int(11). i know php has a mktime function, does anyone know if perl has something similar? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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's this in my netstat?
On Sat, 12 Jan 2002 17:06:36 -0600, jerry wrote: Hi- Anyone know what web.mysql.com is doing? It's from a netstat on a RH 7.2 running mysql 3.23.47 myhost.mydomain:4570 web.mysql.com:auth TIME_WAIT Thanks- this is the list sending you mail -z sql mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query syntax: multiple foreign keys
I'm going to send direct from the programming computer, using a different e-mail address. Ok, I reply to the list, but CC to you. Think I found your problem... I did find that I had one invalid foreign key value in table A, but my problem remains... I tried this: Database PosenL running on localhost Showing records 0 - 2 (2 total) SQL-query : [Edit] SELECT L.Name, C.Name, R.Researcher FROM Researchers2Languages R LEFT JOIN Languages L ON L.Language = R.Language LEFT JOIN Competencies C ON C.Competence = R.Competence WHERE R.Researcher = 39 LIMIT 0, 30 Name Name Researcher Native speaker Native speaker 39 Can read Can read 39 Note that I'm still getting C table values in the B value column, and still getting the nbsp on the B column values ok... you are obviously using some kind of front-end tool, not the mysql client. The nbsp; is a html character, in a browser it looks like a space character. It is used in html to prevent a linebreak at that position. Some frontend tools may have a problem with multiple fields with the same name. Try this: SELECT L.Name as LName, C.Name as CName ... Here are the dumps: I took a quick look, seems ok. I think the above name issue is your problem. You should always try your queries in the mysql client, to eliminate problems related to the front-end tool or script. -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query syntax: multiple foreign keys
I'm going re-post this query. Roger Backlund had been attempting to help me but I've either stumped him or he's busy having a life :) Besides, my first posting had several problems, since I was working from memory. Finally, in the process of preparing this query, I found a small change that makes it work, but I would like to know why that change is necessary. Also, I don't want to have to use unique column names throughout the database, just to make mySQL queries work correctly. What I did that finally made the query work correctly was to modify the column names in the two tables that had the same column name. This is the syntax that works: SELECT Languages.LName, Competencies.CName, Researchers2Languages.Researcher FROM (Languages INNER JOIN Researchers2Languages ON Languages.Language = Researchers2Languages.Language) INNER JOIN Competencies ON Researchers2Languages.Competence = Competencies.Competence WHERE Researchers2Languages.Researcher = 39; results in : LNameCName Researcher English Native speaker39 German Can read 39 But with: SELECT Languages.Name, Competencies.Name, Researchers2Languages.Researcher FROM (Languages INNER JOIN Researchers2Languages ON Languages.Language = Researchers2Languages.Language) INNER JOIN Competencies ON Researchers2Languages.Competence = Competencies.Competence WHERE Researchers2Languages.Researcher = 39; (The difference is in the two column names [.LName vs .Name, .CName vs .Name] in the first lines.) results in: Name Name Researcher Native speaker Native.nbsp.speaker 39 Can readCan.nbsp.read 39 I have no problem doing that if I don't include the Competencies table. But as soon as I add the third table, the results get weird, primarily putting the Competency.Name in the column assigned to Language.Name and to the column assigned to Competency.Name, but converting any spaces into nbsp ; Can anyone explain why mysql produced this weird result, even though the columns with the same name were qualified with the table names? TIA, James Birkholz Admin of Posen-L mailing list website (Here is some more info, if it helps:) This is for a genealogy website. I have 3 tables involved: a Researcher2Languages linking table that contains 0 or more records for every record in a Researcher table (which is not directly involved in this query). Researcher2Languages contains foreign keys to the two other tables; Languages, which is a lookup list of 200+ modern spoken languages (autonumber primary key); and Competencies, a lookup list of how well someone knows the language (currently three possible values, 1-3, also autonumber primary key). Environment: remote connection to a Linux/Apache/MySQL/php server administered by someone else. Using phpMyAdmin to test queries. I presume that if it works there, I can then write some php code to use it. I've tried creating a functioning mirror of the database and query in Access97 (which I'm fairly familiar with) and trying to use the sql query in phpMyAdmin, but it won't run without modifying the syntax Database PosenL running on localhost Showing records 0 - 2 (2 total) SQL-query : [Edit] SELECT L.Name, C.Name, R.Researcher FROM Researchers2Languages R LEFT JOIN Languages L ON L.Language = R.Language LEFT JOIN Competencies C ON C.Competence = R.Competence WHERE R.Researcher = 39 LIMIT 0, 30 Name Name Researcher Native speaker Native.nbsp.speaker 39 Can read Can.nbsp.read 39 Note that I'm still getting C table values in the B value column, and still getting the nbsp on the B column values Here are the dumps: desc Researchers2Languages Field Type Null Key Default Extra Researcher smallint(6) MUL 0 Language smallint(6) 0 Competence tinyint(4) YES NULL desc Languages FieldTypeNull Key Default Extra Language smallint(6) PRI NULL auto_increment Name varchar(25) MUL ShortNamechar(3) Rank tinyint(4) MUL 0 Westernchar(1) desc Competencies Field Type Null Key Default Extra Competencetinyint(4) PRI NULL auto_increment Name varchar(15) Researcher Language Competence 39 1 1 39 2 3 Language Name ShortName Rank Western 1 English ENG1 Y 2 German GER2 Y 3 Polish POL3 Y 4 FrenchFRE4Y (remaining 200+ records left out) Competence Name 1 Native speaker 2 Can translate 3 Can read Hope you can spot something... Thanks! - Before posting, please
Table recovery challenge
I'm facing an interesting data recovery challenge after a malicious hack last week. It seems all the hacker did was log in to the machine and rm -rf / and despite being assured backups were taken off-site daily it seems that the only surviving full backup of the MySQL data files is from August last year. We do have an incremental backup for the day before the hack, but what use is that without the previous increment? I don't need to tell you how pleased I am... We've been able to use e2undel to recover all the files from the partition where MySQL data was but identifying them is a horrendous task. By grepping I've been able to find the .frm files and spot which tables they relate to. Using file and some very useful additions to /usr/share/magic I found on the net I can also spot the .ISM files, though can't tell which tables they belong to. Finding the .ISD files though seems impossible... file reports them as just data along with loads of other stuff, and I've tried looking for patterns in the inode numbers to help identify them, to no avail. Has anyone attempted an operation like this before, and if so did you have any success? Is there anything within a .ISM or .ISD file to say which table it relates to? Or can anyone suggest a way of automating combining a known .frm file with a suspected .ISD file and using isamchk to see if it can be rebuilt? TIA Chris Newman [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: Query syntax: multiple foreign keys
Wow, our posts crossed in the mail and you suggested the same approach that I discovered independently! (Great minds think alike, right?) I'm working remotely, the server is many states away, and I don't have a local developement environment. I'm modifying the actual working site, though I try to develop new modules in segregated files and rename the or cut and paste the changes into the live files. I don't have the resources to create a local development. As I've said, I'm using phpMyAdmin with a browser working remotely. I'll see if the query works without the renaming modification after I build some php code to use it. I'm curious about why the front-end can introduce this problem. The query should be the same. The results from mysql should be the same. The display of the results, I can sort of understand, but why just the one column? I wonder what other surprises phpMyAdmin has in store for me. (I'll be using a lot of queries that self-reference the same table: Places where a record could be for a town, that belongs to another record for a county, that belongs to another record for a state, etc..) Thanks for the assistance! James At 7:01 PM -0500 1/12/02, Roger Baklund wrote: ---snip--- ok... you are obviously using some kind of front-end tool, not the mysql client. The nbsp; is a html character, in a browser it looks like a space character. It is used in html to prevent a linebreak at that position. Some frontend tools may have a problem with multiple fields with the same name. Try this: SELECT L.Name as LName, C.Name as CName ... Here are the dumps: I took a quick look, seems ok. I think the above name issue is your problem. You should always try your queries in the mysql client, to eliminate problems related to the front-end tool or script. -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Installation Problems
I'm having trouble with the installation of MySQL. The database was installed on the C drive. After installation, I performed the following tests to ensure that the database was properly installed: I execute the mysqlshow command and the names of the test and mysql databases were displayed. The errors at the bottom of this message were generated in Windows NT 4.0 (Service Pack 6) after just rebooting Windows three or four times. I never executed mysqld. I read the documentation at the following URL: http://www.mysql.com/doc/C/o/Communication_errors.html. But it did not help. I would appreciate any help with this problem. MySql: ready for connections 011218 13:10:54 MySql: Shutdown Complete 011218 13:10:54 MySql: Normal shutdown 011218 13:10:34 Aborted connection 1 to db: 'unconnected' user: 'ODBC' host: `localhost' (Unknown error) - see http://www.mysql.com/doc/C/o/Communication_errors.html MySql: ready for connections 011215 18:21:43 MySql: Shutdown Complete 011215 18:21:43 MySql: Normal shutdown 011215 18:21:42 Aborted connection 1 to db: 'unconnected' user: 'ODBC' host: `localhost' (Unknown error) - see http://www.mysql.com/doc/C/o/Communication_errors.html MySql: ready for connections 011215 12:17:26 MySql: Shutdown Complete - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Database Size Limit
The size limitation is becauseof the operating system parameters. In order to use tables larger then 3 GB, use either Redhat 7.2 or Solaris 8. These operating systems allow file sizes greater then 2 GB. For the most part I achievedtables sizes using these operating system of greater the 50 GB. Benjamin Arai [EMAIL PROTECTED] On Fri, 11 Jan 2002, Demirchyan Oganes-AOD098 wrote: Hello everyone, I guess I have the similar question, that has been brought up. I have 36 InnoDB tables, and I have allocated two 2GB partitions for my data. Provided I have very big hard drive, how many partitions at 2GB each could I allocate? As many as my hard drive can handle? I also have questions with regards, to a table size. In my case it will be (4GB)/36 bytes per table? Is it distributed uniformly, or some tables can grow bigger on the expense of the others (if some only have limited data, and others keep having new data inserted into them). Sincerely, Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Database Size Limit
I disagree. The 4GB slowdown is usualy caused by indexing problems associated with the OS having increased overhead when looking up and inserting data. I have eliminated this slowdown in Solaris and Linux be re-indexing the entire database once it becomes larger then 4GB. This fixes the problem from what I could tell. I would also like to note that if there was a pointer issue when switching to 64 bit pointers, it would only effect perfomance on computer with less then sufficent amounts of processing power to calculate the larger pointers. Benjamin Arai [EMAIL PROTECTED] On Fri, 11 Jan 2002, James Montebello wrote: This is speculation: They're slower because many operations have to be done using 64 bit values rather than 32 bit values. You set the flag by setting the max data size when creating the table. You can also alter this after the table is created with ALTER TABLE. The doc suggests you're setting the actual maximum size, but if you set the size to even 1 byte past 4GB (at least on Solaris), it immediately flips to a value way above 4GB, which is probably the Solaris limit (may be 2TB, I forget). Not sure what it does on Linux. We saw a significant performance decrease on Solaris when we tried to use max table sizes above 4GB, and assume it's because of an internal switch from 32-bit pointers to 64-bit pointers. May not make nearly as much of a performance difference on a real 64-bit processor and OS. james montebello On Fri, 11 Jan 2002, Aaron Brick wrote: Yes and no. With 3.23, the MyISAM format will, by default, have a 4GB limit (32 bits). You can set flags on the table to allow a 64-bit table, and you'll generally hit an OS or physical limitation long before you run out of space. The downside of the 64-bit version is it's considerably slower. Better to split large datasets up into sub-4GB units. why are operations on the 4GB tables slower? and, incidentally, where is that flag set? thanks, aaron brick. /\ | Aaron Brick (415) 206 - 4685 | | [EMAIL PROTECTED] Room 3501C, SFGH | Programmer Analyst, Functional Genomics Core Sandler Center for Basic Research in Asthma Lung Biology Center, Department of Medicine San Francisco General Hospital University of California, San Francisco - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Installation Problems
At 16:46 12/01/02 -0800, Mark Peterson wrote: I'm having trouble with the installation of MySQL. The database was installed on the C drive. cut Please follow the steps below: - The instructions below assume that you have opened a prompt screen at c:\mysql\bin directory. - After a boot, open the Task Manager (ALT+CTRL+DEL) and verify if exists a process for an MySQL server (mysqld.exe, mysqld-max.exe, mysqld-nt.exe, mysqld-max-nt.exe, mysqld-opt.exe). If yes, stop it with the following command at the prompt: net stop mysql (if the server was started as service) or mysqladmin shutdown (if the server was started as standalone mode). For NT server is necessary to make twice this command due a bug which should be fixed in the next release. - If you had installed the service before, type the command: mysqld-max-nt --remove - Type: mysqld-max-nt --install - Go to c:\winnt directory and create a file named my.ini. Edit the lines below: [mysqld] basedir=c:\mysql datadir=c:\mysql\data skip-innodb Save the file and close it. - Start the server: net start mysql NOTE If you want the support of InnoDB tables (I recommend to test), you should need to read the Manual and remove the line skip-innodb from the my.ini file. Regards, Miguel -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel A. Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Mogi das Cruzes - São Paulo, Brazil ___/ 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: Select statement help...
On Sat, 12 Jan 2002, Tom Jones wrote: Table: flights Fields: depart, depart_time, arrive, arrive_time, flight_no, flight_group I'm trying to do a search on depart and arrive and show only the records which have matching flight_group SELECT depart, arrive FROM flights WHERE flight_group = 'foo' assuming that depart and arrive hold the names of locations and that you want to show all those departure and arrival locations for flights where group foo has travelled. If you want all fields then you can also use the following shorthand notation but if you are using Oracle 8 and JDBC then don't do this because there is a bug in that driver which causes it not to understand the * notation in SELECT statements but I think this would work with the MySQL JDBC driver (mmmysql.sourceforge.net) if you were to use it. SELECT * FROM flights WHERE flight_group = 'foo' After all if you are going to build a database you will most likely want to build an interface for it. If you want to build a web interface then PHP is probably your best choice but first you need to get comfortable with the mysql client. Bye, Neil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
db hosting
Hello everyone, I currently have a webserver and a database server that is running red hat 7.2 and I am learning MySQL. I was just wondering, does anyone want to host their database on my server, it's free and strictly for training purposes. The reason for this is because I want to learn how to manage databases. Please let me know if you want to host your database for practice and/or learning. My email is [EMAIL PROTECTED] I'm sure most people on this list already has a means of hosting their database, but this is a just in case email. Thank you, Henry // // Henry Umansky// // [EMAIL PROTECTED] // // www.pitt.edu/~hmust2 // // - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
db hosting
Hello everyone, I currently have a webserver and a database server that is running red hat 7.2 and I am learning MySQL. I was just wondering, does anyone want to host their database on my server, it's free and strictly for training purposes. The reason for this is because I want to learn how to manage databases. Please let me know if you want to host your database for practice and/or learning. My email is [EMAIL PROTECTED] I'm sure most people on this list already has a means of hosting their database, but this is a just in case email. Thank you, Henry // // Henry Umansky// // [EMAIL PROTECTED] // // www.pitt.edu/~hmust2 // // - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Input Needed: Replication issues...
On Fri, Jan 11, 2002 at 08:08:51AM -0800, Kyle Hayes wrote: We write our own replication system using our own checkpointing and code. We start with the update logs (we're not happy about the future removal of the update logs because of this). I've looked through the MySQL source to see if I can figure out enough of the format of the binary log to see if I can make some Perl code that will parse it. It is really easy to use the update log since all SQL end with a semicolon as the last character on a line. Since Perl tends to be line-oriented, it is easy to find this. [snip] Several questions: (1) Do you use transactions at all, or is this MyISAM only? (2) Have you considered publishing the code? (3) Do I understand correctly, that you have your own primary keys which have nothing to do with MySQL's unique IDs? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 10 days, processed 248,268,346 queries (280/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Very large mysqld processes
On Sat, Jan 12, 2002 at 11:42:57PM +0100, Carsten Gehling wrote: Then maybe you can tell me how I should tune the server? You know make it use more memory, and thereby performing better? It's probably a FAQ, if so just post a URL to a guide. Hmm. You might be interesting in reading this: http://www.linux-mag.com/2001-06/mysql_01.html There's a server performance tuning follow-up article in the December 2001 issue, but it's not on the Web yet. You might be able to find one somehwere. Otherwise, I *think* it'll be on-line in a few weeks. I might have a PDF copy I can send you, but I'll have to dig a bit. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 10 days, processed 248,346,511 queries (280/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Suggestions - FullText ??
I'm able to index 600,000 records in about 5 to 6 minutes on a dual PII 450 running Win2K and MySQL 4.0.0a Similar stucture ID Title Description Indexing Title and Description. On Wed, 2002-01-09 at 20:26, Jon Shoberg wrote: I have a simple table with A LOT of data, 2.1M rows. this is purely a development/test/learning endeavor. The table structure is: ID- auto-increment url - varchar(255) topic - varchar(255) about - tinytext Anyone use fulltext indexing under this scenario? The DB server is a P3-866 with 256mb, any guesses how long indexing will take? Its pupose could guiding search functionality on a website. Any comments/suggestions on using FULLTEXT ? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
