Query mych slower when using index (Innodb)
Hi, I have the following tables :
CREATE TABLE articoli (
codice varchar(16) PRIMARY KEY,
descrizione varchar(255),
marca varchar(255) NOT NULL,
misure varchar(32),
pesofloat DEFAULT 0 NOT NULL,
disponibilita int,
timestamp int NOT NULL,
prezzo int,
INDEX marca(marca)
) TYPE=InnoDB;
(~ 500K records)
CREATE TABLE keywords (
keyword varchar(128) NOT NULL,
codice varchar(16) NOT NULL,
timestamp int NOT NULL,
soundekwvarchar(32) NOT NULL,
PRIMARY KEY(keyword, codice),
) TYPE=InnoDB;
(~ 677K records).
and the following query:
SELECT DISTINCT a.* FROM articoli a, keywords k WHERE a.codice = k.codice
AND keyword IN ('tubo') GROUP BY a.codice HAVING count(k.keyword)=1 limit 1, 26;
that yields: 26 rows in set (11.95 sec)
explain SELECT DISTINCT a.* FROM articoli a, keywords k WHERE a.codice =
k.codice AND keyword IN ('tubo') GROUP BY a.codice HAVING
count(k.keyword)=1 limit 1, 26;
+---++---+-+-+--++--+
| table | type | possible_keys | key | key_len | ref | rows |
Extra|
+---++---+-+-+--++--+
| k | range | PRIMARY | PRIMARY | 128 | NULL | 437735 |
where used; Using index; Using temporary |
| a | eq_ref | PRIMARY | PRIMARY | 16 | k.codice | 1 |
|
+---++---+-+-+--++--+
2 rows in set (4.21 sec)
If I add an index:
alter table keywords add index codice(codice);
'Query OK, 677829 rows affected (1 min 32.58 sec)
Records: 677829 Duplicates: 0 Warnings: 0'
and I rerun the same query it yields:
26 rows in set (1 min 33.77 sec)
almost 8 times slower!!
Explain says:
explain SELECT DISTINCT a.* FROM articoli a, keywords k WHERE a.codice =
k.codice AND keyword IN ('tubo') GROUP BY a.codice HAVING
count(k.keyword)=1 limit 1, 26;
+---+--+++-+--++-+
| table | type | possible_keys | key| key_len | ref | rows |
Extra |
+---+--+++-+--++-+
| a | ALL | PRIMARY| NULL |NULL | NULL | 412345 |
Using temporary |
| k | ref | PRIMARY,codice | codice | 16 | a.codice | 1 |
where used; Using index |
+---+--+++-+--++-+
2 rows in set (0.00 sec)
As far as I remember keywords.codice shouldn't be of any use to speed up
the query because it's the second key of the PK, instead explain lists it
in possible_keys.
Please, can anyone advice me on how to make this query faster?
Thanks in advance,
Nico
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: GUI for MySql
What GUIs exist for MySql and which is the best to use? http://www.bitbybit.dk/mysqlfaq/faq.html#ch7_1_0 lists most of them... / 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
Re: Will MySQL latest version support subqueries
Hi Madhuri, Subquery is in the immediate todo list of Mysql. It should be soon available. The alternative is to use temporary tables. You can read about temporary table in the Mysql manual. Section 3.5.4. would be helpful. Anvar. At 10:58 PM 30/10/2001 -0800, you wrote: hi, I have a clarification from u. Actually I am using MySQL 3.23.39 version. Does MySQL 4.0 or any latest version supports subQueries / subQueries with IN operator. How do I come over this problem. Plase do suggest if any alternatives available. Expecting great response from u. Awaiting quick and effective response, thanks and regards, Madhuri __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.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: Can't create a table with MySQLGUI
I'm newly acquinted with MySQL today, and made a very first attempt to create a table with MySQLGUI. However, the Commands-Tables-Create Tables menu option doesn't seemed implemented -- nothing happens. Is this something that hasn't been implemented, or am I not using the tool correctly? I'm using the Win32 Static Binary of MySQLGUI 1.7.5-2. Can you please help? Scott, Did you first create a database? 1 log in to MySQL/the tool (the Relational Database Management System) 2 create a database (the 'container' for a set/group of tables) 3 create a table (the relation/rows and columns) 4 insert data (the contents) 5 select, etc... (the reason for it all) =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: GUI for MySql
Powerful MySQL Manager for Win32. http://www.mysqlmanager.com Regards, Vadim - Original Message - From: Joe Fan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 31, 2001 9:44 AM Subject: GUI for MySql What GUIs exist for MySql and which is the best to use? Come visit http://www.joe-fan.com For the Fans, by the Fans ! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: BUG: Need option to disable tty check in mysql client
Shankar Unni writes: Please treat this as a fairly urgent problem. It's simple to fix, but the effects of the bug are a severe loss of functionality under Win32. Several options in the mysql client (--batch, --silent, etc.) have no corresponding options to reverse that behavior (e.g. --no-batch, --no-silent, etc.), which is generally accepted option style. [skip] Thanks much in advance, -- Shankar Unni. Thank you for your recommandations, we shall definitely implement, some of those, especially the one on TTY check. -- 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
rearranging id's
Hello, is there an easy way of rearranging id's in a table after deleting one or more entry/entries (other than creating a new tbl and dropping the old one). I appreciate any hint. Thank you. --- Ali Reza Sajedi http://www.bupnet.de/info/mitarbeiter.php3?id=1action=einzelheit Webmaster - Web Developer BUP Am Leinekanal 4 37073 Göttingen Tel.: 0551 - 54 707 61 Fax: 0551 - 54 707 17 www.bupnet.de E-Mail: [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
How Can I Set CurrentDate As Default Date
hello, I got problem when I tried to have CURRENTDATE as DEFAULT date. For this I gave sql statement as CREATE TABLE XX(..,fldDate DATETIME DEFAULT NOW() NOT NULL, ..); NOW() CURDATE() both will give Current date. But I am getting syntax error. If i give some Date like 10/31/2001 it is taking as default. thanks in advance for any advice. regards, sreedhar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: installations
Nathan A. Saint Clair writes: I'm looking for instructions on installing the mysql GUI interface on a unix system. Is that possible? Thank you, Nathan Of course it is possible. What Unix system. There are binaries for Linux and some other Unix systems ready so that you can download the one for your Unix. If there is not one, you have to download a source, and follow instructions in INSTALL file, which imply download and build of several libraries. -- 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
How to make apache webserver on a Linux box connect to a remote mySQLdatabase on a separate Sun box
Can someone pls advise me on how to make a website running in an apache webserver on a Linux box connect to a remote mySQL database on a separate Sun box? Thank you. - (on foxtrot) Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply email. Please advise immediately if you or your employer does not consent to email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of the WPP Group shall be understood as neither given nor endorsed by 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
ANNOUNCE: moomps-0.9
Moomps is a monitoring daemon which works using configuration (dashboard) files created by the moodss (Modular Object Oriented Dynamic SpreadSheet) graphical application. The configuration files contain the definition of thresholds, which when crossed, trigger the sending of email alert messages. This is the initial release (beta quality). Moomps is actually part of moodss, now at release 15.0. When running, moomps periodically checks the threshold conditions and eventually send email alerts to the adresses defined for those thresholds in the configuration files, which can load any number of modules (existing, such as system, MySQL database, network types, or custom written by you in either Tcl, Perl, Python or C). Those modules are actually also used by the moodss graphical monitoring application. More documentation on moomps, moodss and existing modules can be found at: http://jfontain.free.fr/moomps.htm http://jfontain.free.fr/moodss.htm You may find it now at my homepage: http://jfontain.free.fr/moodss-15.0.tar.bz2 (contains moomps) http://jfontain.free.fr/moomps-0.9-1.i386.rpm Enjoy and please let me know what you think. -- Jean-Luc Fontaine - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: rearranging id's
Ali writes: Hello, is there an easy way of rearranging id's in a table after deleting one or more entry/entries (other than creating a new tbl and dropping the old one). I appreciate any hint. Thank you. Drop the column and recreate it. It really is an incredibly dumb thing to do, but if having nicely consecutive values is more impostant than referential integrity, be my guest. :-) (If it's your only table I guess it doesn't matter, but who uses a relational database without relations?) //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication problem (with temporary heap tables ?)
Hi, MySQL V. 3.23.33 with binary log and replication turned on: this is what i do on the master (in a perl script): === create temporary table TEMP_HITS ( ... ) TYPE=HEAP; Insert several things into TEMP_HITS; Select from TEMP_HITS and insert into target table TMP_HITLISTE; do something with TMP_HITLISTE (see bin log excerpt below); eventually rename TMP_HITLISTE to hitliste; After that my database actions end but in the bin log (see below at the end of the bin log excerpt) the statement 'drop table MY_SHOP.t;' comes up which, of course, cannot be replicated because such a table doesn't exist! As you can tell from the thread id it is my script that allegedly issued it! = QUESTIONS: where does this statement come from (is it a clean up action for the temporary table TEMP_HITS ?)? Why is it propagated into the bin log? from the bin log on the master: === # at 71691730 #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 error_code=0 use MY_SHOP; DROP TABLE IF exists hitliste; # at 71691794 #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 error_code=0 use MY_SHOP; create index i_hitl_pt on TMP_HITLISTE (produkt_typ); # at 71691881 #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 error_code=0 use MY_SHOP; create index i_hitl_pt_rang on TMP_HITLISTE (pt_rang); # at 71691969 #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 error_code=0 use MY_SHOP; create index i_hitl_abs_rang on TMP_HITLISTE (abs_rang); # at 71692059 #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 error_code=0 use MY_SHOP; ALTER TABLE TMP_HITLISTE ADD PRIMARY KEY (artikelnummer,produkt_typ); # at 71692162 #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 error_code=0 use MY_SHOP; RENAME TABLE TMP_HITLISTE TO hitliste; # at 71692234 #011031 3:09:03 server id 1 Query thread_id=56341 exec_time=0 error_code=0 use MY_SHOP; drop table MY_SHOP.t; the err log on the slave: = 45219 011031 2:17:41 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log '3306_bin_log.001' at position 71445219 ERROR: 1051 Unknown table 't' 011031 3:09:04 Slave: error running query 'drop table MY_SHOP.t' 011031 3:09:04 Error running query, slave aborted. Fix the problem, and Regards, Frank. -- Dr. Frank Ullrich, Netzwerkadministration Verlag Heinz Heise GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Migrate from mssql7 to Mysql
How can i migrate a database from mssql7 to Mysql? - Moreno Riccardi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't create a table with MySQLGUI
Scott Seong writes: Hi, I'm newly acquinted with MySQL today, and made a very first attempt to create a table with MySQLGUI. However, the Commands-Tables-Create Tables menu option doesn't seemed implemented -- nothing happens. Is this something that hasn't been implemented, or am I not using the tool correctly? I'm using the Win32 Static Binary of MySQLGUI 1.7.5-2. Can you please help? Thank you, Scott Seong Hi! Create Table is on TODO -- 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
Auto_increment
HI everybody. I saw, when i delete all records from the table where id is primary key and auto_increment, the first number for id is'nt 1 but next in a row (the last was 5 the next will be 6). Can i change this behavior ? Mybe in file my.ini ? The goal is, that when i delete all records from the table the id field begin with 1, when i insert a record. I use 3.23.43-max on win98, the table is myisam type. regards. I. Piasecki. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: MySQLGUI hangs on certain commands
[EMAIL PROTECTED] writes: MySQL is working perfectly on my Linux box. I downloaded MySQLGUI, and ran it. It connects fine, I can view a table, etc.. But, when I click on the GRANT/REVOKE menu, it hangs on the Windows side. When I go to the Linux side and issue SHOW PROCESSLIST, there are as many processes connected as I have ports open. When I forcibly kill the MySQLGUI, and issue SHOW PROCESSLIST, it's back to 'normal'. The above happens as SOON as a I click the GRANT/REVOKE menu item! I can view the mysql.user table (by using the view table button), but, it dies when I go to the GRANT/REVOKE menu item. David Thanks. I will check it out. -- 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: Replication problem (with temporary heap tables ?)
= 45219 011031 2:17:41 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log '3306_bin_log.001' at position 71445219 ERROR: 1051 Unknown table 't' 011031 3:09:04 Slave: error running query 'drop table MY_SHOP.t' 011031 3:09:04 Error running query, slave aborted. Fix the problem, and This bug was fixed in version 3.23.37 magic words: database,table -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Uphagena 2, 80-237 Gdansk, tel/fax. (58) 5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LIMIT
Hi! I'm new to linux, PHP and MySQL. I'm having a problem using LIMIT and ORDER BY in the same query. When I use this: SELECT uid,name,uname,email,local,tel FROM users order by name LIMIT 10 , 19 And mysql is returning 14 results. I'm testing, so I replaced the names for numbers to undestand what was going on. The 14 results returned had the following values: Name - 11 | - 12 | - 13| - 14| - 15| - 16| - 17| - 18| - 19| - 20 | - 21 | - 22 | - 23 | - 24| - I've read some questions of people with similar problems, and some of them suggested that upgrading the mySQL version would do it. Because I'm new to linux I don't no exacly how to upgrade. Does anyone have another suggestion to the LIMIT problem? Is there a step by step tutorial to upgrade MySQL? Thank's Joao - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: rearranging id's
Jari Mäkelä writes: It is easier to have consequential data before starting to make the relations if you are forced to work on fixing up the follies/ages old tables of other people True, true. What I can't understand is why so many people insist on wanting to renumber things continously. Could it be that they're trying to store some information in a column whose only purpose should be to provide rows with unique identities? I've done similar things in the past, but it's really a bad idea to refer to things by some name or number that's not guaranteed never to change. And just think of the mess you get when you decide to change the type of a column that you've used as a foreign key. It's bad enough to have to choose between the four int types. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 LOAD DATA INFILE
I suggest writing a PERL script/filter which accepts the text file and sends a comma-delimited file to standard-out. This file could then be sucked in by mysql -Original Message- From: Michael Benbow [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 30, 2001 7:48 PM To: [EMAIL PROTECTED] Subject: Problem with LOAD DATA INFILE Hello all, I have a problem. I am able to get a copy of a text file which resides on another server for a task I am completing. What I need to do is import this text into a database table on a nightly basis, and I am trying to set this up via PHP so it is all automated. I have one problem though, and that is the format of the text file. The text file is not comma delimited or tab delimited, but it is a fixed format with each column spaced out evenly. I created a table with the same lengths as each field from the text file then I used the following line: LOAD DATA INFILE '/path/to/file.txt' INTO TABLE table_name IGNORE 1 LINES; This inserted the first field correctly but all other fields are NULL. If the file is tab delimited or comma delimited I'd have no problems, but since it isn't I am completely stuck... PLEASE HELP ME!!! Thank you, Michael. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
database synchronization
Hello guru, It is not appropriate to me to use standard replication mechanism of MySQL. because the interactions between master server and slave server will be too busy. Is any possibility 1. to replicate data over certain period of time ( I need period 24hours) 2. some how to replicate latest state of DB rather than process all queries. (In my case the same record can be updated several times. So it would be prefferable to get copy of the latest modification. Thanks in advance, Slava - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
unicode - utf8
Does mysql support the utf-8 (Unicode) character set? If so, where can I find it. Thanks, 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
Running Query
I did a dump of a table from another database table and wanted to run the .sql file in another database. I used PHPadmin to do this and used phpadmin to run the .sql file against the other database I have this is the error from mysql: SQL-query : CREATE TABLE tblmembers ( ID int(11) default NULL, FirstName text, Initial text, LastName text, Address1 text, Address2 text, City text, State text, Zip text, HomePhone text, WorkPhone text, Fax text, Email text, Userid text, Password text, Registered datetime default NULL, Modified datetime default NULL, Modified By text ) TYPE=MyISAM MySQL said: You have an error in your SQL syntax near 'By text ) TYPE=MyISAM' at line 19 Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: java.util.Date, jdbc, and DATETIME
This question was asked back in 1999 and wasn't answered, so hopefully someone will answer it now. I am using JBoss and they provide a JDBC to mySQL type mapping that maps java.util.Dates to a jdbc-type of DATE and mySQL type of DATETIME. Well, only the date portion (10-31-2001) is being stored and the time is set to 00:00:00. It is a very simple question, why isn't the time being stored? I am using the MM jdbc driver. Is it the driver code? The reason could be one of 2 possible. The type of the column,and how you set the date.. since you say the column is of type DATETIME that is notthe problem. If you are using prepared statements in jdbc then the problem couldbe that you are using the function stmt.setDate(column, date); try use stmt.setTimestamp(column, date) instead.. since setDate is for setting dates, not date AND time :-) other jdbc:s this might set a datetime also, but not the mm.mysql jdbc.. if this is a bugor just a stricter handlingI do not know.. but that is most likely your problem /Christian Andersson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: java.util.Date, jdbc, and DATETIME
On Wed, Oct 31, 2001 at 09:35:49AM -0500, [EMAIL PROTECTED] wrote: : This question was asked back in 1999 and wasn't answered, so hopefully : someone will answer it now. : : I am using JBoss and they provide a JDBC to mySQL type mapping that maps : java.util.Dates to a jdbc-type of DATE and mySQL type of DATETIME. Well, : only the date portion (10-31-2001) is being stored and the time is set to : 00:00:00. It is a very simple question, why isn't the time being stored? I : am using the MM jdbc driver. Is it the driver code? Do something like this: statement.setTimestamp( 4, new Timestamp( myDate.getTime() ) ); The JDBC standard is for setDate() to store only DATE information. To set the entire timestamp, you need to use setTimestamp, which takes a java.sql.Timestamp instance. You can still fetch with getDate( int ), though. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [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: Running Query
By is not a legal data type -Original Message- From: Todd Williamsen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 9:17 AM To: [EMAIL PROTECTED] Subject: Running Query I did a dump of a table from another database table and wanted to run the .sql file in another database. I used PHPadmin to do this and used phpadmin to run the .sql file against the other database I have this is the error from mysql: SQL-query : CREATE TABLE tblmembers ( ID int(11) default NULL, FirstName text, Initial text, LastName text, Address1 text, Address2 text, City text, State text, Zip text, HomePhone text, WorkPhone text, Fax text, Email text, Userid text, Password text, Registered datetime default NULL, Modified datetime default NULL, Modified By text ) TYPE=MyISAM MySQL said: You have an error in your SQL syntax near 'By text ) TYPE=MyISAM' at line 19 Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Phrase based fulltext searching
A relatively straightforward way round this - and a number of other problems
with FULLTEXT - is to combine a full text search with a LIKE criterion to
narrow the results. Example (assuming that you are searching a table called
Documents on a TEXT field called Term):
SELECT * FROM Documents WHERE MATCH (Term) AGAINST ('search phrase') AND
Term LIKE '%search phrase%'
This, in my experience, appears to be nearly as fast (in perceived terms) as
the straight full text search because the LIKE criterion, whilst slow on
large databases if used with a % wildcard at the beginning (which prevents
use of any conventional indexes), is only applied here on the set matching
the fulltext criterion. It should therefore only be really slow in extreme
conditions where the number of entries matching the fulltext criterion is
very high. Make sure you write the query in this order, though - I would by
no means guarantee that MySQL will optimise it if you write:
SELECT * FROM Documents WHERE Term LIKE '%search phrase%' AND MATCH (Term)
AGAINST ('search phrase')
If optimisation does not take place, this query could easily be several
orders of magnitude slower than the correctly sequenced version!
Roger Bennett
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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: java.util.Date, jdbc, and DATETIME
That is the problem. I changed the mapping for java.util.Date to a JDBC type of TIMESTAMP, which is mapped to a mySQL type of DATETIME. I am now getting the desired results. Thanks, Michael -Original Message- From: Philip Molter [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 10:17 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: java.util.Date, jdbc, and DATETIME On Wed, Oct 31, 2001 at 09:35:49AM -0500, [EMAIL PROTECTED] wrote: : This question was asked back in 1999 and wasn't answered, so hopefully : someone will answer it now. : : I am using JBoss and they provide a JDBC to mySQL type mapping that maps : java.util.Dates to a jdbc-type of DATE and mySQL type of DATETIME. Well, : only the date portion (10-31-2001) is being stored and the time is set to : 00:00:00. It is a very simple question, why isn't the time being stored? I : am using the MM jdbc driver. Is it the driver code? Do something like this: statement.setTimestamp( 4, new Timestamp( myDate.getTime() ) ); The JDBC standard is for setDate() to store only DATE information. To set the entire timestamp, you need to use setTimestamp, which takes a java.sql.Timestamp instance. You can still fetch with getDate( int ), though. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [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: Running Query
No, The column name should have been Modified By data type should have been text Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 -Original Message- From: Rick Emery [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 9:20 AM To: 'Todd Williamsen'; '[EMAIL PROTECTED]' Subject: RE: Running Query By is not a legal data type. Did you mean By text, without the word Modified? -Original Message- From: Todd Williamsen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 9:17 AM To: [EMAIL PROTECTED] Subject: Running Query I did a dump of a table from another database table and wanted to run the .sql file in another database. I used PHPadmin to do this and used phpadmin to run the .sql file against the other database I have this is the error from mysql: SQL-query : CREATE TABLE tblmembers ( ID int(11) default NULL, FirstName text, Initial text, LastName text, Address1 text, Address2 text, City text, State text, Zip text, HomePhone text, WorkPhone text, Fax text, Email text, Userid text, Password text, Registered datetime default NULL, Modified datetime default NULL, Modified By text ) TYPE=MyISAM MySQL said: You have an error in your SQL syntax near 'By text ) TYPE=MyISAM' at line 19 Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Running Query
By is not a legal data type. Did you mean By text, without the word Modified? -Original Message- From: Todd Williamsen [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 9:17 AM To: [EMAIL PROTECTED] Subject: Running Query I did a dump of a table from another database table and wanted to run the .sql file in another database. I used PHPadmin to do this and used phpadmin to run the .sql file against the other database I have this is the error from mysql: SQL-query : CREATE TABLE tblmembers ( ID int(11) default NULL, FirstName text, Initial text, LastName text, Address1 text, Address2 text, City text, State text, Zip text, HomePhone text, WorkPhone text, Fax text, Email text, Userid text, Password text, Registered datetime default NULL, Modified datetime default NULL, Modified By text ) TYPE=MyISAM MySQL said: You have an error in your SQL syntax near 'By text ) TYPE=MyISAM' at line 19 Thank you, Todd Williamsen, MCSE home: 847.265.4692 Cell: 847.867.9427 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: ROW_FORMAT=compressed has no effect on db file sizes
At 12:18 PM 10/30/2001 -0600, Dan Nelson wrote: In the last episode (Oct 30), Bennett Haselton said: I created one table with the command: CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE, id INT UNSIGNED NOT NULL); and another one with the command: CREATE TABLE pet2 (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE, id INT UNSIGNED NOT NULL) ROW_FORMAT=compressed; From the manual: :`ROW_FORMAT' Defines how the rows should be stored. Currently : this option only works with MyISAM tables, which : supports the `DYNAMIC' and `FIXED' row formats. : *Note MyISAM table formats::. :When you `CREATE' or `ALTER' a table you can for tables that :doesn't have `BLOB''s force the table format to `DYNAMIC' or :`FIXED' with the `ROW_FORMAT=#' table option. In the future you ^ I guess this is the operative phrase here; it's planned for the future but it doesn't work yet (and presumably the reason MySQL doesn't give you errors when you enter these commands is for compatibility reasons). :will be able to compress/decompress tables by specifying :`ROW_FORMAT=compressed | default' to `ALTER TABLE'. *Note CREATE :TABLE::. -- 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
Load data infile
Hi I'm working on importing csv datafile returns Warnings, after carefully checking data I noticed that there are double quotes () inside the records. mysql LOAD DATA INFILE '/home/sql/data.csv' INTO TABLE csvdata FIELDS TERMINATED BY ',' ENCLOSED BY '\' LINES TERMINATED BY '\n'; Query OK, 2 rows affected (1.58 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 572 Example of line that fails: data,data,some text and text\n Any quick solutions, regarding mysql import? Thanks sql,table,database,query,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
Query mych slower when using index (Innodb)
Hi!
nsabbi == nsabbi [EMAIL PROTECTED] writes:
nsabbi Hi, I have the following tables :
cut
nsabbi explain SELECT DISTINCT a.* FROM articoli a, keywords k WHERE a.codice =
nsabbi k.codice AND keyword IN ('tubo') GROUP BY a.codice HAVING
nsabbi count(k.keyword)=1 limit 1, 26;
nsabbi
+---++---+-+-+--++--+
nsabbi | table | type | possible_keys | key | key_len | ref | rows |
nsabbi Extra|
nsabbi
+---++---+-+-+--++--+
nsabbi | k | range | PRIMARY | PRIMARY | 128 | NULL | 437735 |
nsabbi where used; Using index; Using temporary |
nsabbi | a | eq_ref | PRIMARY | PRIMARY | 16 | k.codice | 1 |
nsabbi |
nsabbi
+---++---+-+-+--++--+
nsabbi 2 rows in set (4.21 sec)
nsabbi If I add an index:
cut
nsabbi explain SELECT DISTINCT a.* FROM articoli a, keywords k WHERE a.codice =
nsabbi k.codice AND keyword IN ('tubo') GROUP BY a.codice HAVING
nsabbi count(k.keyword)=1 limit 1, 26;
nsabbi
+---+--+++-+--++-+
nsabbi | table | type | possible_keys | key| key_len | ref | rows |
nsabbi Extra |
nsabbi
+---+--+++-+--++-+
nsabbi | a | ALL | PRIMARY| NULL |NULL | NULL | 412345 |
nsabbi Using temporary |
nsabbi | k | ref | PRIMARY,codice | codice | 16 | a.codice | 1 |
nsabbi where used; Using index |
nsabbi
+---+--+++-+--++-+
nsabbi 2 rows in set (0.00 sec)
cut
From the optimizer point of few, it does the right thing because the
number of examined row combinations goes down from 437735 to 412345.
It's probably the rows-in-range estimator in InnoDB that needs some
more tuning.
What is the output from the following queries:
shows index from keywords;
shows index from articoli;
select count(*) from keywords where keyword IN ('tubo');
select count(distinct codice),count(*) from keywords;
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
Problem adding table
I've been trying to add the following table to my database, and I keep getting the error below. CREATE TABLE mod_userpage_data ( id INT(5) NOT NULL default '0', title VARCHAR(200) NULL, data LONGTEXT NULL, PRIMARY KEY (id) ); ERROR 1064 at line 1: You have an error in your SQL syntax near 'data LONGTEXT NULL, PRIMARY KEY (id) )' at line 4 Any help solving this would be appreciated. RW Wood - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: bad practice to have a primary key field whose value changes?
At 02:32 PM 10/30/2001 -0700, Steve Meyers wrote: What would be ideal would be to use auto-incremented numeric fields as primary key fields, and then have a special field in each table designated as the user-friendly field. That way, when you want to view the contents of a table, the table viewing algorithm can take each field marked as a foreign key, go to that table, look up the user-friendly string for that row, and display that instead. This would satisfy the requirements in both paragraphs above. Is there already a way to do this, and if not, which of the two options above do people usually use? -Bennett Generally people do not put user-friendly fields in tables. That's what joins are for :) I guess what I wanted was a list of bookmarkable links that I could click on, which would show me a list of all the users in a table, all the news sites, etc. What I probably need is to put the non-user-friendly fields in the tables, and then come up with a way to store join queries, so that I can bookmark the results. I could just create a form that submits a query through GET data, and then bookmark the results of the form submission. Thanks Steve and Gregert :) -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 make apache webserver on a Linux box connect to a remote mySQL database on a separate Sun box
I think you are mistaken, you do not connect the Apache you connect your script ie php, perl, python to talk to your remote database, by specifying the dns database and password in your config file which should be on your apache server. You must ensure you have the specific odbc modules installed ie mysql-python if you are using python. hth norman -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 12:38 PM To: [EMAIL PROTECTED] Subject: How to make apache webserver on a Linux box connect to a remote mySQL database on a separate Sun box Can someone pls advise me on how to make a website running in an apache webserver on a Linux box connect to a remote mySQL database on a separate Sun box? Thank you. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Listing ignored words for fulltext searches
It would be nice if mysql had a function that would return a list of words that it's going to ignore when I do a full-text search. Its hard for me to explain to users of my site the difference between your words didn't match anything, and the database ignored your query because the words were too common. If there was a way to return the ignored words, it would make life a lot easier. 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
Linux VM on MySQL Servers...
Hi there, After reading some debate under way in kernel lists, i'm curious regarding performance of MySQL under the two diferent VM currently in the kernels (Linus and Alan Cox kernels). Has anyone any info regarding this? Cheers, Luis Ferro - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 adding table
is data a reseverd word in mysql? On Wed, 31 Oct 2001, Richard W. Wood wrote: I've been trying to add the following table to my database, and I keep getting the error below. CREATE TABLE mod_userpage_data ( id INT(5) NOT NULL default '0', title VARCHAR(200) NULL, data LONGTEXT NULL, PRIMARY KEY (id) ); ERROR 1064 at line 1: You have an error in your SQL syntax near 'data LONGTEXT NULL, PRIMARY KEY (id) )' at line 4 Any help solving this would be appreciated. RW Wood - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Load data infile
Use optionally: mysql LOAD DATA INFILE '/home/sql/data.csv' INTO TABLE csvdata FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\' LINES TERMINATED BY '\n'; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 9:33 AM To: [EMAIL PROTECTED] Subject: Load data infile Hi I'm working on importing csv datafile returns Warnings, after carefully checking data I noticed that there are double quotes () inside the records. mysql LOAD DATA INFILE '/home/sql/data.csv' INTO TABLE csvdata FIELDS TERMINATED BY ',' ENCLOSED BY '\' LINES TERMINATED BY '\n'; Query OK, 2 rows affected (1.58 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 572 Example of line that fails: data,data,some text and text\n Any quick solutions, regarding mysql import? Thanks sql,table,database,query,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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 adding table
There may be a space before the word data as in mod_userpage_ data -Original Message- From: Richard W. Wood [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 9:44 AM To: [EMAIL PROTECTED] Subject: Problem adding table I've been trying to add the following table to my database, and I keep getting the error below. CREATE TABLE mod_userpage_data ( id INT(5) NOT NULL default '0', title VARCHAR(200) NULL, data LONGTEXT NULL, PRIMARY KEY (id) ); ERROR 1064 at line 1: You have an error in your SQL syntax near 'data LONGTEXT NULL, PRIMARY KEY (id) )' at line 4 Any help solving this would be appreciated. RW Wood - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: LIMIT
And the problem is? João Rosa - Dimensão Global wrote: Hi! I'm new to linux, PHP and MySQL. I'm having a problem using LIMIT and ORDER BY in the same query. When I use this: SELECT uid,name,uname,email,local,tel FROM users order by name LIMIT 10 , 19 And mysql is returning 14 results. I'm testing, so I replaced the names for numbers to undestand what was going on. The 14 results returned had the following values: Name - 11 | - 12 | - 13| - 14| - 15| - 16| - 17| - 18| - 19| - 20 | - 21 | - 22 | - 23 | - 24| - I've read some questions of people with similar problems, and some of them suggested that upgrading the mySQL version would do it. Because I'm new to linux I don't no exacly how to upgrade. Does anyone have another suggestion to the LIMIT problem? Is there a step by step tutorial to upgrade MySQL? Thank's Joao - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Migrate from mssql7 to Mysql
You can use a pretty cool software from http://www.datanamic.com/ First one being ImporterScript http://www.datanamic.com/importerscripts/index.html then dump your datastructure from your m$sql7 into Dezign http://www.datanamic.com/dezign/index.html then use this to change the database to MySQL. Run and you will get an .sql for MySQL which would build your tables. You would then need to import the data. Maybe there is an easier option. Norman -Original Message- From: Riccardi Moreno [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 12:58 PM To: [EMAIL PROTECTED] Subject: Migrate from mssql7 to Mysql How can i migrate a database from mssql7 to Mysql? - Moreno Riccardi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Problem adding table
From: rc [EMAIL PROTECTED] is data a reseverd word in mysql? No. On Wed, 31 Oct 2001, Richard W. Wood wrote: I've been trying to add the following table to my database, and I keep getting the error below. I copy-pasted your SQL and it worked. I'm using 3.23.38. --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem adding table
No it is not. Reserved word list: http://www.mysql.com/doc/R/e/Reserved_words.html I think Richard's problem is the NULL. You could do default NULL, but I think NULL by itself doesn't mean anything to mysql (in a create table, anyway). - Original Message - From: rc [EMAIL PROTECTED] To: Richard W. Wood [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 31, 2001 9:06 AM Subject: Re: Problem adding table is data a reseverd word in mysql? On Wed, 31 Oct 2001, Richard W. Wood wrote: I've been trying to add the following table to my database, and I keep getting the error below. CREATE TABLE mod_userpage_data ( id INT(5) NOT NULL default '0', title VARCHAR(200) NULL, data LONGTEXT NULL, PRIMARY KEY (id) ); ERROR 1064 at line 1: You have an error in your SQL syntax near 'data LONGTEXT NULL, PRIMARY KEY (id) )' at line 4 Any help solving this would be appreciated. RW Wood - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
MySQLGUI?
Hi Can MySQLGUI start and stop the daemon? /Henrik - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 synchronization
On Wed, Oct 31, 2001 at 04:28:27PM +0200, Vyacheslav I. Raytsin wrote: Hello guru, It is not appropriate to me to use standard replication mechanism of MySQL. because the interactions between master server and slave server will be too busy. Is any possibility 1. to replicate data over certain period of time ( I need period 24hours) What do you mean by that, exactly? 2. some how to replicate latest state of DB rather than process all queries. (In my case the same record can be updated several times. So it would be prefferable to get copy of the latest modification. Check out LOAD TABLE FROM MASTEER in the manual. 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 55 days, processed 1,238,707,726 queries (258/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
Multiple Join Headache
I am having some problems with using join. here's the tables: t1 t2 MachineId MachineNameMachID Service_Name 111mach1 111DHCP 112SNMP t4 Service_cat Service_name Network DHCP EmailExchange Network SNMP t4 Sevice_cat Doc Network document Emaildoc Trainig doc t4 SoftwareId Soft_Name MachineID 112 Exchange 111 I am getting all confused with too many tables. The goal is to make a list of Machine and things running in to it. The database has two different table for services and software. First issue, not all services run in the machine, so thats one left join . I figured that out. But then how would I handle both the service and software table? Is there anyway in SELECt statement to tell, if service name is DHCP look into service table , if service name is exchange look into software table. Can i use something like this service.DHCP , soft.Exchange. Or Am i making this more complicated? Any hint will be great! Thanks _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sage v6 to mysql
Has anyone played with extracting data from sage version 6 on Windows to MySQL? What is the easyest way of extracting data from sage? Their support will not help sing my contract with them expired and I have decided to use a different product. Regards Ajdin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't set max_connections on debian version
thanks, I finnally figured out how to set it on the command line and modified safe_mysql script to do it. Thanks On Wed, 31 Oct 2001, Wouter de Jong wrote: Date: Wed, 31 Oct 2001 09:19:14 +0100 From: Wouter de Jong [EMAIL PROTECTED] To: William R. Mussatto [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Can't set max_connections on debian version On Tue, Oct 30, 2001 at 11:31:12AM -0800, William R. Mussatto wrote: Thanks but it doesn't work... Below is the extract of the configuration file it does not matter whether I include spaces around the = or not, starting using mysql_safe results in a failed There is no entry in the error log. Commenting out the line results in a successful restart. I am probably missing something basic but... set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K max_connections = 200 Use this : set-variable= max_connections=200 Since it's a variable : Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 -- Met vriendelijke groet/With kind regards, Wouter de Jong [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 Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 turn beeping off
I've been lurking here for a while and have been itching to ask this... Does anyone use mysqlfront to manage and work with MySQL on Windows? I love it and can not live without it now. My other favorite is Advanced Query Tool and the combination is fantastic! Any thoughts?? (and it does not beep at me...) Allen -Original Message- From: Benjamin J Pracht [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 11:22 AM To: [EMAIL PROTECTED] Subject: Re: How to turn beeping off I confess to using Windows NT (my job requires it) and running the command line utility mysql.exe. Since I make mistakes, it likes to tell me that by beeping at me. I'd like to use some control panel utility, but I to mute the speaker, but I don't have one. I'd really love to cut the speaker cable, but it's not my machine and I can't find where it's plugged into, either. Since I'm not the only newbie, I'm surprised more people haven't asked this before. Any thoughts, besides switching to Linux? Ben Pracht Retail Store Solutions (919)301-5775 (T/L 352-5775) 919-301-5848 (Fax) [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
Multiple Join Headache
I am having some problems with using join. here's the tables: t1 t2 MachineId MachineNameMachID Service_Name 111mach1 111DHCP 112SNMP t4 Service_cat Service_name Network DHCP EmailExchange Network SNMP t4 Sevice_cat Doc Network document Emaildoc Trainig doc t4 SoftwareId Soft_Name MachineID 112 Exchange 111 I am getting all confused with too many tables. The goal is to make a list of Machine and things running in to it. The database has two different table for services and software. First issue, not all services run in the machine, so thats one left join . I figured that out. But then how would I handle both the service and software table? Is there anyway in SELECt statement to tell, if service name is DHCP look into service table , if service name is exchange look into software table. Can i use something like this service.DHCP , soft.Exchange. Or Am i making this more complicated? Any hint will be great! Thanks _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Migrate from mssql7 to MySQL
Since this is the first mention of the datanamic tools I have seen, I have a question. In using DeZign to layout the structure and then generate scripts to build the tables, what happens when you just want to make a few changes to the tables? Do you have to re-generate the whole thing? Do you add the pieces manually or are there provisions to keep the database and the model in sync? A colleague built a huge database with lots of linked tables. The importer will be GREAT for mapping the whole thing out. The demo is such a tease Thanks! Allen -Original Message- From: Norman Khine [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 11:35 AM To: Riccardi Moreno Cc: Mysql Subject: RE: Migrate from mssql7 to Mysql You can use a pretty cool software from http://www.datanamic.com/ First one being ImporterScript http://www.datanamic.com/importerscripts/index.html then dump your datastructure from your m$sql7 into Dezign http://www.datanamic.com/dezign/index.html then use this to change the database to MySQL. Run and you will get an .sql for MySQL which would build your tables. You would then need to import the data. Maybe there is an easier option. Norman -Original Message- From: Riccardi Moreno [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 12:58 PM To: [EMAIL PROTECTED] Subject: Migrate from mssql7 to Mysql How can i migrate a database from mssql7 to Mysql? - Moreno Riccardi - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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
JSP newbie needs to run MySQL-JDBC.
Hello Everyone,
I need to run MySQL using JSP.
I am a JSP newbie, actually a Java newbie too. I am more of a PHP/MySQL
guy.
I have successfully installed Allaire JRun 3.0 on Win98 using PWS.
JRun has an application management console and I cannot seem to run
MySQL.
I am totally unfamiliar with how JRun, JDBC and MM.MySQL runs and the
error message I get is:
The following error occurred while testing the data source
connection
Communication link failure: java.io.EOFException
I will enumerate everything I have done to see where I have gone wrong:
1. I am on a LAN and my IP address is 192.186.0.2. So on PWS, my
localhost is basically 192.168.0.2. Everything is working fine with PWS
and could use it with PHP and MySQL, thus I know MySQL is working fine
too.
2. I installed Allaire JRun and everything is working fine with regards
to running JSP pages.
3. I have read in the book, core JSP, that there are some JSP engines
that doesn't use the classpath and uses it's own classpath, thus I added
this on the Java Settings of the Default Server under Classpath. Prior
to this, copied the org directory of the extracted files in
mm.mysql-2.0.6.1.jar..jar to c:\progra~1\mm.sql in the Windows 98
directory structure. So my classpaths in JRun looks like this:
{jrun.rootdir}/servers/lib
{jrun.server.rootdir}/lib
{jrun.rootdir}/../../mm.mysql
Where all Jrun files are at c:\progra~1\allaire\jrun
I got mm.mysql-2.0.6.1.jar..jar from SourceForge.com as my MySQL JDBC
driver.
4. After doing this, I went to the JDBC Data Sources of the Default
Server of JRun Application Management Console. And I added a new entry
with the following:
Name: MySQL
Display Name: MySQL
Driver: org.gjt.mm.mysql.Driver
URL:
jdbc:mysql://192.168.0.2:80/studiosusa?user=rootpassword=axnolnms
Description: Studios USA database
Pooling: Yes
Timeout: 30 Minutes
Interval: 30 Seconds
Username: root
Password: axnolnms
Vendor Argument: blank, I placed nothing.
Btw, before doing this, I already created a database named studiosusa
and the username and password to my MySQL is root and axnolnms.
5. I started running the MySQL engine whose version is 3.22.21.
6. I clicked on the Test button on Allaire JRun's JDBC settings and
that's when I got the error:
The following error occurred while testing the data source
connection
Communication link failure: java.io.EOFException
Now, where did I go wrong? And how do I run MySQL on the JSP engine
Allaire JRun 3.0?
I don't know if this is a MM.MySQL problem.
Benj Arriola
[EMAIL PROTECTED]
+63 (917)-831-8345
Action Online Co. Ltd.
www.action.com.ph
+63 (2)-417-3629
+63 (2)-417-5107
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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
Alternative for UNION
I had a view on sqlserver which i am trying to convert to work with mysql. It uses a union which i believe does not work with mysql. What is another alternative for union. Sql with union: select a.asset_id,a.material_id,b.material_id as parent_material_id from lib_asset_tbl a inner join lib_asset_tbl b on a.parent_id=b.asset_id union select asset_id,material_id,'' from lib_asset_tbl where virtual_flag='N' Help is appreciated, Regards, Harpreet Kaur Software Developer Crispin Corporations Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 turn beeping off
Any thoughts, besides switching to Linux? Linux beeps also, as evidenced by this intentionally incorrect query (had to get query in there so the message wouldn't bounce!): mysql select * from junk; ERROR 1146: Table 'SBS4_run_db_25_10_2001__16hr8min36sec.junk' doesn't exist (and a beep) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 on SQL Statement
Hello, I've been working on this for a long time now and I cannot figure this out. Can someone help me get this sql statement without erroring or why it doens't work. Here is the SQL statement... SELECT DISTINCT ItemMaster.ParentNum, ItemMaster.Desc1, ItemMaster.Price FROM BillOfMat AS BillOfMat_1 LEFT JOIN (BillOfMat LEFT JOIN ItemMaster ON BillOfMat.CompNum = ItemMaster.ParentNum) ON BillOfMat_1.CompNum = BillOfMat.ParentNum WHERE (((ItemMaster.ParentNum) Like 200*) AND ((BillOfMat_1.Kw)='25') AND ((BillOfMat_1.Engine)='G') AND ((BillOfMat_1.Fuel)='NL')); ..And here is the error I get. ERROR 1064: You have an error in your SQL syntax near '(BillOfMat LEFT JOIN ItemMaster ON BillOfMat.CompNum = ItemMaster.ParentNum) O' at line 3 Thank you for your help --Mike __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.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
Alternative for UNION in MYSQL
I had a view on sqlserver which i am trying to convert to work with mysql. It uses a union which i believe does not work with mysql. What is another alternative for union. Sql with union: select a.asset_id,a.material_id,b.material_id as parent_material_id from lib_asset_tbl a inner join lib_asset_tbl b on a.parent_id=b.asset_id union select asset_id,material_id,'' from lib_asset_tbl where virtual_flag='N' Help is appreciated, Regards, Harpreet Kaur Software Developer Crispin Corporations Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 adding table
On Wednesday 31 October 2001 10:44 am, Richard W. Wood wrote: I've been trying to add the following table to my database, and I keep getting the error below. CREATE TABLE mod_userpage_data ( id INT(5) NOT NULL default '0', title VARCHAR(200) NULL, data LONGTEXT NULL, PRIMARY KEY (id) ); ERROR 1064 at line 1: You have an error in your SQL syntax near 'data LONGTEXT NULL, PRIMARY KEY (id) )' at line 4 Richard, Could you possibly have some strange characters in your source file (assuming that you are using one)? Maybe something that an editor left behind. I tried your example, verbatim and it worked on both an Alpha system and an Intel system running RedHat 7.1 and MySQL 3.23.38 (see below): Logging to file 'test.results' mysql use test; Database changed mysql create table mod_userpage_data ( - id INT(5) NOT NULL default '0', - title VARCHAR(200) NULL, - data LONGTEXT NULL, - PRIMARY KEY (id) - ); Query OK, 0 rows affected (0.00 sec) mysql show tables; +---+ | Tables_in_test| +---+ | mod_userpage_data | +---+ 1 row in set (0.00 sec) mysql describe mod_userpage_data; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(5) | | PRI | 0 | | | title | varchar(200) | YES | | NULL| | | data | longtext | YES | | NULL| | +---+--+--+-+-+---+ 3 rows in set (0.00 sec) mysql quit mysqladmin Ver 8.20 Distrib 3.23.38, for redhat-linux-gnu on alpha 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.38 Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 1 day 59 min 37 sec Threads: 2 Questions: 42 Slow queries: 0 Opens: 14 Flush tables: 1 Open tables: 3 Queries per second avg: 0.000 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Authors Needed
Hi, I am looking for experienced writers who would like to write a complete and authoritative reference on MySQL. If you are interested in writing such a book, please contact me at [EMAIL PROTECTED] To learn more about the books we publish, please visit our Web site at www.osborne.com. McGraw-Hill/Osborne Media, a unit of McGraw-Hill Education, is a leading publisher of self-paced computer training materials, including user and reference guides, best-selling series on computer certification, titles on business technology, and high-level but practical titles on networking, programming, and Web development tools. McGraw-Hill/Osborne Media is the official press of Oracle, Corel, Global Knowledge, J.D. Edwards, Intuit, and RSA Security Inc., and has a strategic publishing relationship with ComputerWorld. McGraw-Hill/Osborne Media is focusing on consumer support, emerging technologies, and innovative applications for developing future computer books. McGraw-Hill Education is a division of The McGraw-Hill Companies (NYSE: MHP), a global information services provider meeting worldwide needs in financial services, education, and business-to-business information through leading brands such as Standard Poor's and BusinessWeek. The Corporation has more than 300 offices in 32 countries. Sales in 2000 were $4.3 billion. Additional information is available at www.mcgraw-hill.com. Thanks, Chris Johnson Acquisitions Editor, Education Training Osborne/McGraw-Hill 2600 Tenth Street, Sixth Floor Berkeley, CA 94710 P: 510-549-6607 F: 510-549-6603 www.osborne.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
Off the shelf MySQL products
Hello everyone, We'll probably be using MySQL with our software. But in order to do that it has to be aproved by FDA( Food and Drug Administration). Are there any off the shelf products that will have all the MySQL capabilities bundled up in it? I think that way our software will be approved by the FDA. Any other suggestions would be appreciated. Regards, 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
MySQLfont Tools
On Wednesday 31 October 2001 12:00 pm, Schmidt, Allen J. wrote: I've been lurking here for a while and have been itching to ask this... Does anyone use mysqlfront to manage and work with MySQL on Windows? I love it and can not live without it now. My other favorite is Advanced Query Tool and the combination is fantastic! Any thoughts?? (and it does not beep at me...) Allen I do. Love it. I actual only use Windows as a requirement for a consulting job. I have MySQL installed on several machines (Alpha and Intel-based) and Linux as wellas NT. MySQLfront is a great tool for daily tasks, etc. I must confess to preferring the text-based approach, probably the habit of years of big DB work on Tandem systems, where GUI tools were (and are) few and far between. I have not heard of the Advanced Query Tool, which company makes it? Regards, Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Mysqlfront Tools
Advanced Query Tool - uses the MySQL odbc connection to get to the remote MySQL server. http://www.querytool.com The group that produces it is really great and has added several things I have suggested. Great support. Easy to use. Hope you like it too. We are switching out Zope installation over to Linux from Windows2000 soon and so our MySQL will be moved as well. I have no Linux experience so just as long as I can get to my Zope management screens and MySQL I will be OK... Allen -Original Message- From: Tony [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 12:42 PM To: [EMAIL PROTECTED] Cc: Schmidt, Allen J. Subject: MySQLfont Tools On Wednesday 31 October 2001 12:00 pm, Schmidt, Allen J. wrote: I've been lurking here for a while and have been itching to ask this... Does anyone use mysqlfront to manage and work with MySQL on Windows? I love it and can not live without it now. My other favorite is Advanced Query Tool and the combination is fantastic! Any thoughts?? (and it does not beep at me...) Allen I do. Love it. I actual only use Windows as a requirement for a consulting job. I have MySQL installed on several machines (Alpha and Intel-based) and Linux as wellas NT. MySQLfront is a great tool for daily tasks, etc. I must confess to preferring the text-based approach, probably the habit of years of big DB work on Tandem systems, where GUI tools were (and are) few and far between. I have not heard of the Advanced Query Tool, which company makes it? Regards, Tony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 adding table
The problem was in fact an illegal column name in one of the tables that a Content Management program tried to create. (phpWebSite) The program seems to be well done, but somehow they missed a beat on one of the modules that it creates (or doesn't because of the illegal name) I tracked it down about the same time as someone on the list made the same suggestion, and when it was changed, the table created with no problem Thanks. RWW - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multiple Join Headache
Is there anyway in SELECt statement to tell, if service name is DHCP look into service table , if service name is exchange look into software table. Can i use something like this service.DHCP , soft.Exchange. You could use a UNION and then specify both queries if you're using mysql 4.0 ie select ..join with tablea where servicename=dhcp UNION select ...join with tableb... where service name=exchange. 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
Re: Request for Mysql GUI.
Anything you could want to know, and more, can be found here: http://www.mysql.com/downloads/gui-mysqlgui.html Mike [EMAIL PROTECTED] wrote: Hello, I have the Mysql 3.23.27-beta version installed on my Windows NT PC and I am able to create the database and query from the command line and from a Perl script. I was reading the readme file which comes with the installation about a GUI tool for Win32 version. I am typing an excerpt from the file as follows: - Most things appears to work nicely on Win32. We use the MySQL benchmarks and crash-me to check each version before releasing it. - The distributed default MySQL version is compiled with debugging information so one can get a trace file with: mysqld --debug. After one has verified mysqld works correctly one can switch to the optimized version: mysqld-opt or mysql-nt. - There are two versions of the 'mysql' command line tool; 'mysql', compiled on native Win32, which offers very limited text editing capabilities and 'mysqlc', compiled with the Cygnus gnu compiler and libraries, which offers readline editing. - The default permission is that anyone can use any database that starts with 'test' from any host and anyone running on the same machine as mysqld is running can take it down.mysqladmin shutdown. - Normally one should add a password for the 'root' user and take mysqld down with: mysqladmin --user=root --password=your_password shutdown In this case one should remove the entry with host='localhost' and user='' from the user database. The above is most easly done with: shell mysql mysql mysql delete from user where host='localhost' and user=''; mysql quit shell mysqladmin reload shell mysqladmin -u root password your_password - This distribution includes the a prototype of a the GUI tool, MySQLManager, by James Pereria. This has still some rough edges, but it can serve as a great base for further development! - MySQLWinAdmn is a Delphi program, by David B. Mansel, to administrate MySQL databases. (Binary only). We are waiting for an updated version that will work with the current libmysql.dll - This distribution includes two small helper programs: - mysqlshutdown ; If one installs this program on Win95 / Win96 in the Programs/Startup folder then it will automaticly take down the mysqld daemon on reboot - mysqlwatch ; If you have problems on NT that MySQL dies (this should only happen when you testing/developing MySQL), this service will automaticly restart the mysqld service if notices that the mysqld server isn't running. (Thanks to Irena for the above programs!) If you would like to work on the MySQL win32 version, you can find a list of open issues in the reference manual. We are not that used with the Win32 environment so many of the issues may be real easy to solve for someone familiar with Win95/Win98/NT): If you want to help us develop MySQL for Win#/NT please mail to [EMAIL PROTECTED] to get help to set up your environment! All questions that are specific to the windows version should be posted to this list! Please remember to include all relevant information that may help solve your problem. I would appreciate if I could get help on the GUI tool and appropriate info on how to acquire the tool. Thanks. Manisha Basu. __ Your favorite stores, helpful shopping tools and great gift ideas. Experience the convenience of buying online with Shop@Netscape! http://shopnow.netscape.com/ Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.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
A space is contained in colum??
I am a newbie. When I create a table, one colum contains a space, how can I caeate it correctly? like: CREATE TABLE mytable( name CHAR(60) NOT NULL, Email VARCHAR(50) NOT NULL, Thrid colum CHAR(60), ///Here there is a space? PRIMARY KEY (Email) ); When I use ' or , it doesnot work. Zhidian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Left join w/o on or using join_condition
My mind is not working with me this morning... According to the manual about join syntax: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#JOIN One should be able to do a LEFT JOIN without a join_condition, eg. an ON or a USING: table_reference LEFT [OUTER] JOIN table_reference However a query like: SELECT T1.*, T2.* FROM tbl1 T1 LEFT JOIN tbl2 T2 WHERET1.a=1AND T1.b=2AND T2.a=2 AND T2.b=T1.b; Gives me a ERROR 1064: You have an error in your SQL syntax near 'WHERE... What am I doing wrong? You used to be able to do that. I know the conditional can be moved into a ON( ... ) but I do not want to do that for compatibility with other DBs. MySQL 3.23.41 --Bill - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 on SQL Statement
You can't use brackets on left joins like that. If you want to do two left joins, you use: LEFT JOIN table1 ON conds LEFT JOIN table2 ON conds That's why you're getting the error that mysql displayed, it doesn't expect a bracket. Chris SELECT DISTINCT ItemMaster.ParentNum, ItemMaster.Desc1, ItemMaster.Price FROM BillOfMat AS BillOfMat_1 LEFT JOIN (BillOfMat LEFT JOIN ItemMaster ON BillOfMat.CompNum = ItemMaster.ParentNum) ON BillOfMat_1.CompNum = BillOfMat.ParentNum WHERE (((ItemMaster.ParentNum) Like 200*) AND ((BillOfMat_1.Kw)='25') AND ((BillOfMat_1.Engine)='G') AND ((BillOfMat_1.Fuel)='NL')); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Alternative for UNION
Harpreet wrote: I had a view on sqlserver which i am trying to convert to work with mysql. It uses a union which i believe does not work with mysql. What is another alternative for union. Sql with union: select a.asset_id,a.material_id,b.material_id as parent_material_id from lib_asset_tbl a inner join lib_asset_tbl b on a.parent_id=b.asset_id union select asset_id,material_id,'' from lib_asset_tbl where virtual_flag='N' MySQL 4.0 supports unions. b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A space is contained in colum??
You use the ` char as a quote. (the backwards quote, right below the ESC key on your keyboard with ~) Chris When I create a table, one colum contains a space, how can I caeate it correctly? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: JSP newbie needs to run MySQL-JDBC.
Name: MySQL Display Name: MySQL Driver: org.gjt.mm.mysql.Driver URL: jdbc:mysql://192.168.0.2:80/studiosusa?user=rootpassword=axnolnms I would assume that here is your problem, most likely mysql is not running on port 80 on your machine and you should change it to 3306, so the connect string looks like this: jdbc:mysql://192.168.0.2:3306/studiosusa?user=rootpassword=axnolnms Best regards, -- HTTP is a stateless protocol, and the Internet is a stateless development environment -- Igor Fedulov E-mail : [EMAIL PROTECTED] Work Ph: 773.775.1595 Home Ph: 773.281.8938 Cell Ph: 773.580.5935 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 are MYSQL_RES limits ? -- update
Some other useful information on this problem: I have tried the operation in two other ways, both based on a version of the program with only one connection using the API and emiting the UPDATE statments on standard output: 1. output piped onto mysql -- failure :-( 2. output saved on a file, then input to mysql -- success :-) This fits my diagnosis that it has to do with concurrent access to the same table. Thanks, -- , M A R I O data miner, LIACC, room 221 tel 351+226078830, ext 121 A M A D O Rua Campo Alegre, 823 fax 351+226003654 A L V E S P-4150 PORTO, Portugalmob 351+939354002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Off the shelf MySQL products
for windows environment: http://sourceforge.net/projects/phptriad/ for linux: I've had complete success with Red Hat 7.1 -Original Message- From: Demirchyan Oganes-AOD098 [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 11:43 AM To: '[EMAIL PROTECTED]' Subject: Off the shelf MySQL products Hello everyone, We'll probably be using MySQL with our software. But in order to do that it has to be aproved by FDA( Food and Drug Administration). Are there any off the shelf products that will have all the MySQL capabilities bundled up in it? I think that way our software will be approved by the FDA. Any other suggestions would be appreciated. Regards, 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: MySQL Authors Needed
Subject: MySQL Authors Needed Hi, I am looking for experienced writers who would like to write a complete and authoritative reference on MySQL. Thanks, Chris Johnson Paul DuBois has already done a bang-up job. His book, MySQL (New Riders), is the bible as far as I'm concerned, and I think alot of people here would agree. Jay Fesco Magical Mystery Words: database,sql,query,table - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Alternative for UNION
I am using version 3.23 and dont think will be moving up to 4.0. Can i somehow create a temporary table and add all the rows i need and do a select query from that temp table. Neone done this before. Thanks for ur responses and time, Regards, Harpreet Kaur Software Developer Crispin Corporations Inc. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bill Adams Sent: Wednesday, October 31, 2001 1:23 PM To: [EMAIL PROTECTED] Cc: 'Mysql' Subject: Re: Alternative for UNION Harpreet wrote: I had a view on sqlserver which i am trying to convert to work with mysql. It uses a union which i believe does not work with mysql. What is another alternative for union. Sql with union: select a.asset_id,a.material_id,b.material_id as parent_material_id from lib_asset_tbl a inner join lib_asset_tbl b on a.parent_id=b.asset_id union select asset_id,material_id,'' from lib_asset_tbl where virtual_flag='N' MySQL 4.0 supports unions. b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: deadlock - Innodb Monitor
Heikki, thanks for the reply. Regarding the trace output, My test has been bombarding the db server with 15 simultaneous clients, each generating up to 1000 transaction sets. I still do not get any output from Innodb Monitor. I created table innodb_monitor in the database that I'm accessing. Should it be created instead in the database mysql?? Is there any sample that I can look at regarding avoiding deadlock, like for the semaphore rows, and the predefined order of accessing tables that you mentioned? Thanks, Gisella - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 on SQL Statement
Change: LEFT JOIN (BillOfMat LEFT JOIN ItemMaster ON BillOfMat.CompNum =ItemMaster.ParentNum) ON BillOfMat_1.CompNum = BillOfMat.ParentNum to: LEFT JOIN BillOfMat ON BillOfMat_1.CompNum = BillOfMat.ParentNum LEFT JOIN ItemMaster ON BillOfMat.CompNum =ItemMaster.ParentNum You cannot do embedded joins -Original Message- From: Mike Mike [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 11:24 AM To: [EMAIL PROTECTED] Subject: Help on SQL Statement Hello, I've been working on this for a long time now and I cannot figure this out. Can someone help me get this sql statement without erroring or why it doens't work. Here is the SQL statement... SELECT DISTINCT ItemMaster.ParentNum, ItemMaster.Desc1, ItemMaster.Price FROM BillOfMat AS BillOfMat_1 LEFT JOIN (BillOfMat LEFT JOIN ItemMaster ON BillOfMat.CompNum = ItemMaster.ParentNum) ON BillOfMat_1.CompNum = BillOfMat.ParentNum WHERE (((ItemMaster.ParentNum) Like 200*) AND ((BillOfMat_1.Kw)='25') AND ((BillOfMat_1.Engine)='G') AND ((BillOfMat_1.Fuel)='NL')); ..And here is the error I get. ERROR 1064: You have an error in your SQL syntax near '(BillOfMat LEFT JOIN ItemMaster ON BillOfMat.CompNum = ItemMaster.ParentNum) O' at line 3 Thank you for your help --Mike __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.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: A space is contained in colum??
What do you mean there's a space? You mean after the comma in CHAR(60), ? And you you provided is not what you entered into mysql, because Thrid colum CHAR(60), won't work; colum is not a legal data type -Original Message- From: I. TS [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 12:19 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: A space is contained in colum?? I am a newbie. When I create a table, one colum contains a space, how can I caeate it correctly? like: CREATE TABLE mytable( name CHAR(60) NOT NULL, Email VARCHAR(50) NOT NULL, Thrid colum CHAR(60), ///Here there is a space? PRIMARY KEY (Email) ); When I use ' or , it doesnot work. Zhidian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Left join w/o on or using join_condition
Try going with simply: SELECT T1.*, T2.* FROM tbl1 T1, tbl2 T2 WHERET1.a=1AND T1.b=2 AND T2.a=2 AND T2.b=T1.b; -Original Message- From: Bill Adams [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 12:21 PM To: Mysql List Subject: Left join w/o on or using join_condition My mind is not working with me this morning... According to the manual about join syntax: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#JOI N One should be able to do a LEFT JOIN without a join_condition, eg. an ON or a USING: table_reference LEFT [OUTER] JOIN table_reference However a query like: SELECT T1.*, T2.* FROM tbl1 T1 LEFT JOIN tbl2 T2 WHERET1.a=1AND T1.b=2AND T2.a=2 AND T2.b=T1.b; Gives me a ERROR 1064: You have an error in your SQL syntax near 'WHERE... What am I doing wrong? You used to be able to do that. I know the conditional can be moved into a ON( ... ) but I do not want to do that for compatibility with other DBs. MySQL 3.23.41 --Bill - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: deadlock - Innodb Monitor
Gisella, Heikki, thanks for the reply. Regarding the trace output, My test has been bombarding the db server with 15 simultaneous clients, each generating up to 1000 transaction sets. this is strange. When you start the server from the command promp, does it then print something to the command prompt window? If you look at the mysqld error log, does it contain something? Does mysqlshow table status from yourdatabasename; show the innodb_monitor table as created and of type InnoDB? What if you drop the table innodb_monitor and create it again? I still do not get any output from Innodb Monitor. I created table innodb_monitor in the database that I'm accessing. Should it be created instead in the database mysql?? No, it does not matter in which database you create it. Here is a sample session I ran in NT: mysql client: C:\mysql4\binmysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.37-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql create table innodb_monitor(a int) type = innodb; Query OK, 0 rows affected (4.01 sec) mysqld server: ... C:\trybuild\client_debugmysqld --standalone 011031 9:04:17 InnoDB: Started mysqld: ready for connections = 011031 20:54:27 INNODB MONITOR OUTPUT = -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 4, signal count 4 Mutex spin waits 15, rounds 260, OS waits 2 RW-shared spins 4, OS waits 2; RW-excl spins 0, OS waits 0 TRANSACTIONS Trx id counter 0 697601 Purge done for trx's n:o 0 0 undo n:o 0 0 Total number of lock structs in row lock hash table 0 ---TRANSACTION 0 697600, OS thread id 179 creating index, active, runs or sleeps , has 3 lock struct(s), undo log entries 3 FILE I/O I/O thread 0 state: wait windows aio for n pages I/O thread 1 state: wait windows aio for n pages ... Is there any sample that I can look at regarding avoiding deadlock, like for the semaphore rows, and the predefined order of accessing tables that you mentioned? A semaphore row can be: CREATE TABLE semrows (a INT NOT NULL, b INT, PRIMARY KEY (a)) TYPE = INNODB; INSERT INTO semrows VALUES (1, 0); ... UPDATE semrows SET b = b + 1 WHERE a = 1; The UPDATE sets an exclusive lock on the row. If every transaction does this UPDATE at the start, then the row acts as a semaphore which serializes all transactions. About predefined access order: try to think a natural order in you will access your tables. But that is easier said than done. Applications have to be programmed so that they issue a transaction again if a deadlock is encountered. (3.23.44 will roll back the whole trx at deadlock, .43 only rolled back the SQL statement.) Thanks, Gisella Regards, Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[off topic] open source jobsite
Hi, We have just launched a non-profit open source jobsite. Check it out at http://www.freeusall.com/ It's built on MySQL 4.0 (Alpha), Perl and Apache. We'd appreciate any feedback you might have. kind regards, Mark Maunder. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Update JDBC Driver (gweMysql Driver)
Hi! Sinisa == Sinisa Milivojevic [EMAIL PROTECTED] writes: Sinisa TAKAHASHI, Tomohiro writes: Hi, All I Fixed two bugs. 1. If LoginUser has empty password, Driver can not connect to MySQL. 2. Connection Pool does not work correctly. ;-) Check this URL out for new Driver. :-) http://www.t3-jpn.com/jdbc/jdbcmysql.html Sinisa Thank you so much. Tomohiro, could you be kind and put a copy kind a put a link from the MySQL software portal to your page ? This way it will be easier for other MySQL users to find your software! http://www.mysql.com/portal/software/html/index.html It would also be nice if you could add some small documentation on your site how your driver compares to the mm JDBC driver. Regards, Monty PS: If anyone on this list really likes some MySQL utility they are using and wants to recommend this to other MySQL users, please add this to the MySQL portal or add a comment to the existing entry. This will make it much easier for new MySQL users to choose which packages they should try out! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Left join w/o on or using join_condition
Rick Emery wrote: Try going with simply: SELECT T1.*, T2.* FROM tbl1 T1, tbl2 T2 WHERET1.a=1AND T1.b=2 AND T2.a=2 AND T2.b=T1.b; Well, if I resort to that then the documentation is wrong. Here is what I am really trying to do. Say you have a table and data: DROP TABLE IF EXISTS tbl2; CREATE TABLE tbl1 ( a int, c char, val int ); INSERT INTO tbl1 VALUES ( 1, 'R', 10 ), ( 2, 'R', 11 ), ( 3, 'R', 12 ), ( 1, 'T', 20 ), ( 3, 'T', 21 ) ; Here is a ugly, self-join select that gives me the output that I want: SELECT T1.*, T2.* FROM tbl1 T1 LEFT JOIN tbl1 T2 ON( T1.a=T2.a AND ( T1.cT2.c OR ( T1.c='R' AND T2.c='T' ))) WHERE T1.c='R' ; +--+--+--+--+--+--+ | a| c| val | a| c| val | +--+--+--+--+--+--+ |1 | R| 10 |1 | T| 20 | |2 | R| 11 | NULL | NULL | NULL | |3 | R| 12 |3 | T| 21 | +--+--+--+--+--+--+ 3 rows in set (0.00 sec) What I really want is a simpler select like informix allows, e.g.: SELECT T1.*, T2 FROM tbl1 T1, OUTER tbl1 T2 WHERE T1.a=T2.a AND T1.c='R' AND T2.c='T'; My real query is much worse than this. Any suggestions? --Bill -Original Message- From: Bill Adams [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 12:21 PM To: Mysql List Subject: Left join w/o on or using join_condition My mind is not working with me this morning... According to the manual about join syntax: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#JOI N One should be able to do a LEFT JOIN without a join_condition, eg. an ON or a USING: table_reference LEFT [OUTER] JOIN table_reference However a query like: SELECT T1.*, T2.* FROM tbl1 T1 LEFT JOIN tbl2 T2 WHERET1.a=1AND T1.b=2AND T2.a=2 AND T2.b=T1.b; Gives me a ERROR 1064: You have an error in your SQL syntax near 'WHERE... What am I doing wrong? You used to be able to do that. I know the conditional can be moved into a ON( ... ) but I do not want to do that for compatibility with other DBs. MySQL 3.23.41 --Bill - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Adams TriQuint Semiconductor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Alternative for UNION
Can i somehow create a temporary table and add all the rows i need and do a select query from that temp table. Yeah, that's the easiest way to do it. Create temporary table A. insert into A select insert into A select select * from A If you want to mimic 'insersect' features and such, just add unique constrainst to your table so that you don't insert duplicate data twice from your multiple inserts. 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
Re: Load data infile
Hi I didn't see any difference between using optionally or not. Any solutions to make the two that fails work? (see test case below) Using 3.23.41. Thanks test.csv: Record 1,some data,Works Record 2,some data,Works Record 3,some 'data',Works Record 4,some data,Works Record 5,somedata,Fails Record 6,somedata,Works Record 7,some data,Fails Record 8,'some'data,Works Record 9,somedata,Works CREATE TABLE quotetest ( name text, content text, description text ); mysql LOAD DATA INFILE '/home/sql/test.csv' INTO TABLE quotetest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\' LINES TERMINATED BY '\n'; Query OK, 9 rows affected (0.00 sec) Records: 9 Deleted: 0 Skipped: 0 Warnings: 2 mysql select * from quotetest; +--++-+ | name | content| description | +--++-+ | Record 1 | some data | Works | | Record 2 | some data| Works | | Record 3 | some 'data'| Works | | Record 4 | some data | Works | | Record 5 | somedata,Fails | NULL| | Record 6 | somedata | Works | | Record 7 | some data,Fails | NULL| | Record 8 | 'some'data | Works | | Record 9 | somedata | Works | +--++-+ 9 rows in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Configuring MySQL 4.0.0 with SSL shortens idle timeout horribly
Description: Configuring MySQL 4.0.0 with --with-ssl causes the server to act as though interactive_timeout and/or wait_timeout were set to approximately 6 seconds, although the variables seem to have normal values. How-To-Repeat: Configure, build and install the server, including --with-ssl. (See below for the configuration but without --with-ssl included.) Start mysqld_safe --user=mysql -l There are no my.cnf or .my.cnf files anywhere on this system. There's nothing in the databases except the default grant tables. Start the mysql client. Type show databases;. Wait 7 seconds. Type show databases; again. Client will complain about the connection being lost and reconnect. Server is *NOT* dumping core and restarting, as connection number keeps going up and there are no kernel logs of core dumps. Same problem with a select on one of the grant tables. show variables indicates that wait_timeout and interactive_timeout are set to the normal defaults, 28800 (?seconds?). Deinstall and repeat above leaving out --with-ssl. No disconnects this time. Note: I haven't got nearly far enough to actually try to use SSL. FreeBSD 4.3, Openssl 0.9.6, gcc 2.95.3 Fix: Don't use --with-ssl. Submitter-Id: submitter ID Originator:Gordon Burditt Organization: Internet America MySQL support: none [none | licence | email support | extended email support ] Synopsis: Configuring SSL into mysqld shortens timeout horribly Severity: non-critical Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.0-alpha (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.22 Distrib 4.0.0-alpha, for -freebsd4.3 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.0-alpha-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 7 min 22 sec Threads: 1 Questions: 4 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 0 Queries per second avg: 0.009 Environment: System: FreeBSD rat.hammy.lonestar.org 4.3-RELEASE FreeBSD 4.3-RELEASE #3: Tue Jun 26 19:32:45 CDT 2001 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/RAT i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.3 [FreeBSD] 20010315 (release) Compilation info: CC='cc' CFLAGS='-O -pipe ' CXX='c++' CXXFLAGS='-O -pipe -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1174570 Jun 23 17:34 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Jun 5 00:42 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 561580 Jun 23 17:34 /usr/lib/libc.so.4 Configure command: ./configure --localstatedir=/var/db/mysql --without-perl --without-debug --without-readline --without-bench --with-mit-threads=no --with-libwrap --with-vio --without-isam --with-low-memory --enable-assembler --prefix=/usr/local i386--freebsd4.3 Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: A space is contained in colum??
When I create a table, one colum contains a space, how can I caeate it correctly? like: CREATE TABLE mytable( name CHAR(60) NOT NULL, Email VARCHAR(50) NOT NULL, Thrid colum CHAR(60), ///Here there is a space? PRIMARY KEY (Email) ); MySQL does not allow spaces in column names. Try: ThridColum CHAR(60), - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: deadlock - Innodb Monitor
Heikki, I looked at the mysql error log and all the stuff was there. I was expecting it on the standard output as the documentation said. Thanks again. I'll have to restart the server again since the innodb monitor output on the error log says that I'm not running a debugable version, so there is no information about locking. So, I should issue mysqld-max-nt --debug --standalone Nothing was printed on stdout when I issued mysqld-max-nt --standalone Gisella - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Load data infile
Hi, your test.csv file has some flaws in records 5 and 7 because the OPTIONALLY ENCLOSED BY char= isn't matched for each column. Changing those offending CSV lines to be instead something like: Record 5,\some\data\,Fails Record 7,some data\,Fails and then modifying your LOAD to be something like: LOAD DATA INFILE '/home/sql/test.csv' INTO TABLE quotetest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '\\' LINES TERMINATED BY '\n'; should do it. The reason you use the ENCLOSED BY clause is to take into account data like: id,Name,phone 12,Last,First,555-555- Since the 2nd column actually contains a comma you need to use OPTIONALLY ENCLOSED BY to contain it like: id,Name,phone 12,Last,First,555-555- If the s aren't matched LOAD gets confused as to what delemits a column. -Joe -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 31, 2001 12:48 PM To: [EMAIL PROTECTED] Subject: Re: Load data infile Hi I didn't see any difference between using optionally or not. Any solutions to make the two that fails work? (see test case below) Using 3.23.41. Thanks test.csv: Record 1,some data,Works Record 2,some data,Works Record 3,some 'data',Works Record 4,some data,Works Record 5,somedata,Fails Record 6,somedata,Works Record 7,some data,Fails Record 8,'some'data,Works Record 9,somedata,Works CREATE TABLE quotetest ( name text, content text, description text ); mysql LOAD DATA INFILE '/home/sql/test.csv' INTO TABLE quotetest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\' LINES TERMINATED BY '\n'; Query OK, 9 rows affected (0.00 sec) Records: 9 Deleted: 0 Skipped: 0 Warnings: 2 mysql select * from quotetest; +--++-+ | name | content| description | +--++-+ | Record 1 | some data | Works | | Record 2 | some data| Works | | Record 3 | some 'data'| Works | | Record 4 | some data | Works | | Record 5 | somedata,Fails | NULL| | Record 6 | somedata | Works | | Record 7 | some data,Fails | NULL| | Record 8 | 'some'data | Works | | Record 9 | somedata | Works | +--++-+ 9 rows in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Re: Load data infile
I didn't see any difference between using optionally or not. Any solutions to make the two that fails work? (see test case below) Using 3.23.41. That's a problem with your input data. You can't have the character that you're using to surround the fields within the field, unless you escape it. If you can re-export the data, use a surrounding character that's not in the dataset to enclose your fields, or make sure that every instance of the surrounding character (and the escape char) are escaped by some other character (ie ESCAPED BY '\') Chris database,sql,query,table - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: deadlock - Innodb Monitor
At 10:48 31/10/2001 -0800, Gisella Saavedra wrote: Hi! Take a look at /mysql/data/mysql.err for to see the output for innodb_monitor. I guess that you need to stop the server, for open this file. Regards, Miguel Heikki, thanks for the reply. Regarding the trace output, My test has been bombarding the db server with 15 simultaneous clients, each generating up to 1000 transaction sets. I still do not get any output from Innodb Monitor. I created table innodb_monitor in the database that I'm accessing. Should it be created instead in the database mysql?? Is there any sample that I can look at regarding avoiding deadlock, like for the semaphore rows, and the predefined order of accessing tables that you mentioned? Thanks, Gisella - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: deadlock - Innodb Monitor
Gisella, Heikki, I looked at the mysql error log and all the stuff was there. I was expecting it on the standard output as the documentation said. it should come to the standard output. I am sending a copy of this email to Miguel of MySQL AB. He should check why mysqld started as --standalone redirects stdout to mysql.err. When mysqld is run as a service it should go to mysql.err, but not when run as standalone. By the way, are you sure you did not have two mysqlds running in your computer? What does the processlist in the Task Manager say? Thanks again. I'll have to restart the server again since the innodb monitor output on the error log says that I'm not running a debugable version, so there is no information about locking. So, I should issue It actually means 'no information about semaphore locking'. You will get the user lock info with innodb_lock_monitor not with plain innodb_monitor. mysqld-max-nt --debug --standalone Nothing was printed on stdout when I issued mysqld-max-nt --standalone Gisella Regards, Heikki - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 3.23.43 fails merge and some rpl tests on HPUX 10.20
Since I sent this, I've noticed two things: 1. Somewhere in the process of reporting and working around problems in the build process, I lost a few recommended CXXFLAGS. I started over from scratch, so mysqlbug now reports Compilation info: CC='gcc' CFLAGS='-DHPUX -I/opt/dce/include' CXX='gcc' CXXFLAGS='-DHPUX -I/opt/dce/include -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' (Everything else is the same). The test results remain identical, however. 2. I noticed the following in mysql-test/var/tmp/mysqltest-time: /scr/src/mysql-3.23.43/client/mysqltest: At line 6: query 'load data infile '../../std_data/words.dat' into table t1' failed: 1085: The file '../../std_data/words.dat' must be in the database directory or be readable by all after I ran test rpl01. So, I ran `chmod -R o+r std_data`, then reran the failed tests. With this change, tests rpl01, rpl04, and rpl07 now pass. Test merge still fails, however, and the remaining rpl tests (2, 11, 14, mystery22, and sporadic_master) still hang. I'd suggest that configure/make should ensure that all needed permissions are correct. Michael On Tue, 30 Oct 2001, Michael Stassen wrote: Description: Configured and built mysql 3.23.43 without errors on a 9000/782 running HPUX 10.20. All tests pass except: merge [ fail ] rpl01 [ fail ] rpl02 hangs rpl04 [ fail ] prl07 [ fail ] rpl11 hangs rpl14 hangs rpl_mystery22 hangs rpl_sporadic_master hangs Tests rpl 1, 4, and 7 fail with no additional output. The remaining rpl tests seem to just hang, with no output after the lines TEST USER SYSTEM ELAPSEDRESULT I let rpl 2 run for 7 hours, aborted 11 after 30 minutes, and gave up on the rest after about 5 minutes each. Merge fails with: - $./mysql-test-run --local merge Installing Test Databases Removing Stale Files Installing Master Databases 011030 18:04:33 ../sql/mysqld: Shutdown Complete Installing Slave Databases 011030 18:04:33 ../sql/mysqld: Shutdown Complete Starting MySQL daemon Loading Standard Test Databases Starting Tests TEST USER SYSTEM ELAPSEDRESULT merge [ fail ] Below are the diffs between actual and expected results: --- *** r/merge.result Wed Oct 3 19:21:17 2001 --- r/merge.reject Tue Oct 30 18:04:41 2001 *** *** 6,15 2 table 3 t2 a b ! 3 t1 ! 3 t2 ! 2 table ! 2 table 1 Testing 1 Testing table typepossible_keys key key_len ref rowsExtra --- 6,15 2 table 3 t2 a b ! 1 Testing ! 1 Testing ! 1 Testing ! 1 Testing 1 Testing 1 Testing table typepossible_keys key key_len ref rowsExtra *** *** 106,114 incr othr incr othr 1 10 ! 2 24 ! 4 33 ! 3 53 count(*) 10 count(*) --- 106,114 incr othr incr othr 1 10 ! 1 10 ! 1 10 ! 1 10 count(*) 10 count(*) --- Please follow the instructions outlined at http://www.mysql.com/doc/R/e/Reporting_mysqltest_bugs.html to find the reason to this problem and how to report this. Aborting. To continue, re-run with '--force'. Ending Tests Shutting-down MySQL daemon Master shutdown finished Slave shutdown finished - I've seen this exact output reported by others (running Solaris 2.5.1 and Linux, if I recall correctly), but have not seen any resolution. On a side note, mysql-test-run was determined to use /bin/diff, which doesn't support '-a' on my system, rather than /usr/local/bin/diff. I had to edit the script to set DIFF in order to get the output above. I didn't see any mention of the need to do this, or a better method, in the documentation. How-To-Repeat: build for hpux 10.20, run 'make test' or run './mysql-test-run --local testname' in mysql-test. Fix: Unknown Submitter-Id:submitter ID Originator: Michael Stassen Organization: Indiana University MySQL support: none Synopsis:3.23.43 fails merge and some rpl tests on HPUX 10.20 Severity:serious Priority:medium Category:mysql Class: sw-bug Release: mysql-3.23.43 (Source distribution) Environment: System: HP-UX dr B.10.20 A 9000/782 2015374635 two-user license Some paths: /usr/local/bin/perl /usr/local/bin/make
Re: Load data infile
At 2:47 PM -0500 10/31/01, [EMAIL PROTECTED] wrote: Hi I didn't see any difference between using optionally or not. Any solutions to make the two that fails work? (see test case below) Using 3.23.41. Thanks The problem is that your CSV file isn't legal CSV. You'll need to tell your application to generate correct CSV format. test.csv: Record 1,some data,Works Record 2,some data,Works This line is illegal. Record 3,some 'data',Works Record 4,some data,Works So is this one. Record 5,somedata,Fails And this one. Record 6,somedata,Works And this one. Record 7,some data,Fails And this one. Record 8,'some'data,Works And this one. Record 9,somedata,Works And this one. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load data infile
At 10:32 AM -0500 10/31/01, [EMAIL PROTECTED] wrote: Hi I'm working on importing csv datafile returns Warnings, after carefully checking data I noticed that there are double quotes () inside the records. mysql LOAD DATA INFILE '/home/sql/data.csv' INTO TABLE csvdata FIELDS TERMINATED BY ',' ENCLOSED BY '\' LINES TERMINATED BY '\n'; Query OK, 2 rows affected (1.58 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 572 Example of line that fails: data,data,some text and text\n Any quick solutions, regarding mysql import? It's not surprising that it fails. It's not valid CSV. Thanks sql,table,database,query,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
synopsis of the problem (one line)
sdsd Description: How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:root Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-4.0.0-alpha (Source distribution) Environment: System: Linux localhost.localdomain 2.4.9-7 #1 Thu Oct 18 13:47:25 EDT 2001 i686 unknown 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/i586-mandrake-linux/2.96/specs gcc version 2.96 2731 (Linux-Mandrake 8.0 2.96-0.48mdk) Compilation info: CC='gcc' CFLAGS='-fomit-frame-pointer -fexpensive-optimizations' CXX='gcc' CXXFLAGS='-fomit-frame-pointer -fexpensive-optimizations -mcpu=pentiumpro -march=pentiumpro -DCPU=pentiumpro' LDFLAGS='-s' LIBC: lrwxrwxrwx1 root root 13 Oct 11 23:02 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1216268 Feb 21 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26366908 Feb 21 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Feb 21 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/opt/mysql --localstatedir=/opt/mysql/data --with-unix-socket-path=/opt/mysql/tmp/mysql.sock --with-mysqld-user=mysql --with-charset=latin1 --with-extra-charsets=latin1_de,croat --with-embedded-server --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --without-innodb --enable-thread-safe-client --enable-assembler --enable-large-files - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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)
sdsd Description: How-To-Repeat: Fix: Submitter-Id: submitter ID Originator:root Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: Severity: Priority: Category: mysql Class: Release: mysql-4.0.0-alpha (Source distribution) Environment: System: Linux localhost.localdomain 2.4.9-7 #1 Thu Oct 18 13:47:25 EDT 2001 i686 unknown 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/i586-mandrake-linux/2.96/specs gcc version 2.96 2731 (Linux-Mandrake 8.0 2.96-0.48mdk) Compilation info: CC='gcc' CFLAGS='-fomit-frame-pointer -fexpensive-optimizations' CXX='gcc' CXXFLAGS='-fomit-frame-pointer -fexpensive-optimizations -mcpu=pentiumpro -march=pentiumpro -DCPU=pentiumpro' LDFLAGS='-s' LIBC: lrwxrwxrwx1 root root 13 Oct 11 23:02 /lib/libc.so.6 - libc-2.2.2.so -rwxr-xr-x1 root root 1216268 Feb 21 2001 /lib/libc-2.2.2.so -rw-r--r--1 root root 26366908 Feb 21 2001 /usr/lib/libc.a -rw-r--r--1 root root 178 Feb 21 2001 /usr/lib/libc.so Configure command: ./configure --prefix=/opt/mysql --localstatedir=/opt/mysql/data --with-unix-socket-path=/opt/mysql/tmp/mysql.sock --with-mysqld-user=mysql --with-charset=latin1 --with-extra-charsets=latin1_de,croat --with-embedded-server --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --without-innodb --enable-thread-safe-client --enable-assembler --enable-large-files - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Q about mysql_install_db under FreeBSD ports
Hi all, Is there any good reason for ``mysql_install_db'' to use the -s option to hostname instead of using FQDN? Every time I install mysql 3.23 out of ports, it fails running that script and I have to edit it manually and remove the -s option. # grep /bin/hostname work/mysql-3.23.36/scripts/mysql_install_db hostname=`/bin/hostname -s`# Install this too in the user table Thanks! :) Atle - Flying Crocodile Inc, Unix Systems Administrator - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
